Mastering Excel Tables: How to Make and Use Them Like a Pro

1. Introduction: My name is Al Chen, I'm a Solutions Architect at Coda, a tool that brings the best of spreadsheets, docs, applications together. I've been an Excel power-user for 10 plus years, taught over 10,000 MBA students. These days, I spend my time creating tutorials and videos about Excel spreadsheets and other related tools. Today, I'll be teaching you how to use Excel tables a relatively unknown feature within Excel. Once you will learn the power of Excel tables, you'll never look back. I'll be teaching you how to create Excel tables, how to create structure references, and also how to format your table, so they look very nice and pretty for your team and for your colleagues. I'm really excited for students to take this class because once you'll learn the basic features and functions of Excel tables, you'll see the power it unlocks for the rest of your dashboards, your data analysis, tasks, and other things related to excel spreadsheets. Just by learning this one simple feature, it will hopefully changed the way you approach Excel in the future. Once you're done taking this class, I hope you'll participate in the class projects. It involves analyzing flight delay data from the Department of Transportation from 2015, turning it into an Excel table, and also doing analysis on this data by creating a dashboard, creating a report for the end user, and also writing a few insights about what you can gather from analyzing the data after you use Excel tables. I really hope that you'll post your findings on the data within the project gallery. Let's get started right away with finding the benefits of Excel tables. 2. The Benefits of Excel Tables: In this lesson, I'm going to be showing you the benefits of Excel tables by doing a quick demo of a table I've built in the class workbook, where I'll show you how to filter and sort, add a summary row to your data, and also add new data to the Excel table. I'll be exploring these topics in more in-depth later on in the class. We are looking at an Excel table right now. In step 1, we're just going to click on the table below and scroll down past the first header row, which is row 11. As I scroll past, you'll notice that the columns will become sticky in the top toolbar or the ribbon. This is one of the nice features of Excel tables where you can see the columns up here as you scroll past. It's like freezing your first row in a way if you've done that before in your Excel files. I can also quickly filter and sort my data just by clicking on the "Dropdown" here in one of these columns, and I can click on "Ascending" or "Descending" and filter stuff as you have done before in your Excel files. This is one of the default features that come with your Excel table. Let's say I want to do a summary row in step 3. I can click inside my table, click the ''Table tab'' in the ribbon, and then you notice that the total row check boxes uncheck by default. If I click on this, you'll see at row 39, there is a summary row which you can basically summarize your data by averages, sums, counts, all these typical metrics. I'm going to uncheck this for now. One of the most powerful features with tables is listed here in step 4. I want you to try adding a new row of data to the table. If I just click over here and hit "Enter" and start typing in something like S510, and this is a movie, if I want to type in another row S200, you notice that the table automatically adds the formatting to the new rows of data, and these rows of data get included in the table as an object. Now, one thing I want you to think about is imagine having to create this table using a conditional formatting. You'd have to do a lot of really interesting formulas and functions to get these banded rows to get colored like this. The nice thing about Excel tables is that Excel takes care of all that conditional formatting for you, so that when you add new rows of data they automatically get color coded for the data to stand out. Finally, I want you try putting a formula, any formula in column K, where it says, My Formula. I'm going to write equals hit a cell in perhaps the same row here, and you notice that it gives you these references, the column, instead of a traditional battleship type of reference, we have A1 and B5. It's a little different from what you will see, but we'll talk more about what this is and why it's really powerful in a future lesson on structured references. Just to recap, we walked through some of the benefits and features of Excel tables, we showed you how to make your tables a little less error prone by having this conditional formatting that gets easily applied, and also we walked through really quickly how you can filter and sort tables. One key takeaway from this lesson is to just start playing around with the table in the class workbook. In the next lesson, we're going to talk about how to create your own table from scratch so you can start doing some analysis. 3. Creating an Excel Table: We'll be creating an Excel table from scratch using a list of restaurants in the class workbook. Let's get started. We have a list of restaurants here with the location, the sales, the franchising status, and you can learn more about this dataset by clicking on this URL I have here in this cell. But let's walk through step-by-step how to create a table and use some of the features in this table. Set one we're going to click "Insert" and then "Table", and we're going to name this table, Restaurants. I want you to first select all your data. It's about 50 rows. Then go to Insert Table. By default, it's going to say my table has headers. Normally, you want to keep this as checked because our table does have headers, you can see here in row 10. I'm going to click, "Okay", and this is how Excel creates a table for you by default. You notice you get the banded color rows, and you also get these drop-downs for quick filtering and sorting. You see what the benefits here of the table is when you hit Enter and you start typing again a new restaurant and say this is Arby's, Excel will automatically expand that table to include that new row of data. If I add new rows of data, Taco Bell, you'll see how the Excel table will automatically color code the rows for you so you don't have to worry about the conditional formatting as you would normally do with a normal list of data. Now I'm going to delete some data from the table and try navigating around the empty cells in step 2. Let's just delete a few of these rows. You notice that this is all gone, but Excel still thinks that these rows are probably table. Now, before we delete these rows, I want you to navigate around by pressing "Command" or "Control" with the arrow keys. You notice that the behavior is a little different from what you would expect. You get to go to the edges of the table, instead of when there's a new cell with a value. I'm just going to select these rows, right-click, and then press "Delete", delete table rows, and those rows will get deleted from my table so they won't be counted as empty rows. We deleted the empty rows, and now I want you to do some other options for Excel tables like we can convert this table to a Pivot Table if we want by clicking on "Table" in the ribbon, and then clicking on "Summarize with PivotTable". I'm going to use existing worksheet in this case and the range is going to be cell J14. Once I click on "Okay" for the Pivot Table, you'll see that this gets created over here, so we have the option to create a Pivot Table if we want. If at any point in time you want to convert your Excel table back to a normal range of data, all you have to do is click on the "Table" ribbon again, and one of the options in the toolbar is convert to range. If I click on "Convert to Range", Excel will automatically remove the drop-downs for me, but the conditional formatting will still stay on the table, so you can remove that by clearing formatting if you'd like. I'm going to undo that convert to range. One last thing I want to do, I forgot to mention is in step 1 is we want to rename this table to restaurants in the top left. If I click on a cell within the table and I click on the "Table" tab in the ribbon again, you notice by default, Excel gave me a table name of Table4. I'm going to rename this to Restaurants and hit "Enter" so that in the future, if I ever want to reference this table, I can just call it Restaurants instead of Table4 or whatever Excel gives me as the default. In this lesson, we walked through some of the benefits of using an Excel table and how to create an Excel table, how we can delete rows and apply some quick formatting to the tables. In the next lesson, we're going to walk through more in-depth how to filter, sort, and create calculated columns on your Excel table. 4. Filtering and Sorting an Excel Table: We'll be diving deeper into filtering and sorting your Excel table, and also how to add a new formula to your table, which are called calculated columns. Let's dive in. We have the same table of restaurants also as an Excel table. If I click on "Table" here you'll see that this table is called Rest2. As you've seen before, you can sort by any of these columns by clicking "Ascending" or "Descending", and I can just add this sort really quickly to any column I want. I want you to now in step two, is to add a summary row to this table and find the average sales, which is in column D in our list of restaurants. If you go to Table, click on "Total Row' and you notice at the very bottom in row 61 we get this dark blue row for our totals. In the sales column, I want you to click on the drop-down and just click on "Average". Now we can just find the average sales across all these restaurants, which is about $34 million. That's a really nice way to just quickly get the average sum count, just like you might in a traditional pivot table, but in an Excel table, it's handled through this very last row at the bottom of your table. In step three, we're going to create a slicer based on the Franchising column. Now slicer just like a nice visually pleasing way for your team, your colleagues, to quickly interact with your Excel table, I'm going to click on my "Table" here, make sure you're on the Table tab in the ribbon and click on "Insert Slicer". You'll get this menu here where you can select which column you want to slice on. I'm just going to click on "Franchising", hit "Okay", and you'll get this little menu where you can pick and choose Yes or No. You notice that when you click on Yes or No, this Franchising column automatically gets filtered to that specific value. That's a really nice feature to have with your slicers. If I click on this option right here, this checkmark option in the slicer, I can now select multiple options in my slicer box. I'm just going to remove the slicer for now, hit "Delete" on that, and go back to my main table view here. Finally, we're going to write a formula in the first empty column to the right, which is column I, to calculate the average sales per store. Let me just undo this filter here. If I click on cell I10 and write average sales, you notice Excel automatically adds that column as a new column to my table, and if I start writing equals in cell I11, equals sales, which is column D divided by column F, which is locations, look at the formula that gets created, sales divided by locations, let's hit "Enter" and see what happens. The formula automatically gets filled down to the last row in my table, so I don't have to do any double-clicking or dragging my formula all the way down. Formulas in tables automatically get applied to the entire column all in one go, so you don't have to worry about all this extra manual steps for creating the formula. You may have done something similar to this, which in an Excel table it's called a calculated column. You may have done something similar in a pivot table, which is called a calculated field. We're just doing the [inaudible] in an Excel table. Stepping back for a little bit, if you had to build out this formula and build out this table from the scratch, and to also get the summary data here in this row 61, you'd have to do a complicated list of formulas. You might be using a COUNT A formula to select all the cells here that have an empty value. The nice thing about this total row is that it automatically gets added to bottom of the table, you don't have to worry about adding all these additional formulas that just make your life a little harder to do. We talked about how to do some more filtering, sorting, and creating calculated columns in an Excel table. Now, the Excel table formula that you saw in the column for average sales looks tricky, so we're going to dive deeper into that in the next lesson on structured references. 5. Formulas within Structured References: We'll be diving deeper into structured references. I'll be walking through different ways you can create formulas with structured references within your Excel tables. A quick note about these structured references is that when you're within the table itself, like we have with average sales, you reference the column by putting this ampersand plus the column name surrounded by brackets. This is different from when you do equals, cell K5 or something like that, or K9. In Excel tables, you reference the entire column so that's why when you write a formula, the formula fills down through the entire column on its own. If you are outside of the table, like we have here in step 1, we're going to write equals. I'm going to reference the sales column again. You notice that I have to first put the table name Rest3, followed by the structured reference, which is @Sales. This will give me only the value for that row, even though it's referencing the entire column. This is one thing to get used to as you're referencing cells and rows in your table. Just to confirm, I'll click on "Table" here, indeed called Rest3. Finally, in step 2, I want you to do one more exercise with referencing this table is write equals and try selecting the entire table with the headers. You notice that it says Rest3 #All. This is telling Excel you're referencing the entire table at a time. If you only want to reference the data, you can just write equals and just select everything without the header and you're just going to see, it says Rest3, but you can also write [#Data and you'll get the same result. This will give me an error because I'm representing the table in one cell so that's not going to work. I want you to write a SUM formula on the locations column outside of the table using what we just learned with formulas and auto fills with table references. Let's write the formula right here, equals sum. The name of our table is Rest3. I can click on any of these structured references here, Rest3 and let's try to reference just the locations column. I can write left bracket. The minute you write a left bracket, it gives you the option to select any of the columns in your table. I can now click on "Location" click on right parenthesis. What I'm doing here is I shall need to write locations with an S to reference this column, column F. What I'm saying here is sum everything in the locations column within my Rest3 table. Hit "Enter" and this is 1,735. I'm just going to format this to be a number. You might be wondering why is this easier than just writing equals sum and then selecting all the cells right here? Traditionally you do that with a normal list of data, but the nice thing about these structured references is if I go down here till the last row of my table and add a new row, 51, Taco Bell, and add a value to my Locations column, let's say at 100 or 1,000, this number will automatically get updated in my sum because this structured reference expands and shrinks based on what is in your table. That's one of the most powerful features of Excel tables is as new data gets added to your table, the structured references will update. Imagine having to do this with a COUNT formula which you might be using today to count all the values in your column. The structure references makes it a much cleaner, less error-prone way to reference all the values in your table. Finally, we're going to do a little exercise in creating some summary stats here in column M and N. I want you to write a SUMIFS formula to summarize all the sales for these restaurants based on locations with 75 grade or plus, 50 or 75 for restaurants that have franchises and with no franchises. Let's start writing it out. I can write equals sumifs. I'm just going to zoom in here so you can see all my data, equals sumifs. I'm going to use my cursor to suck my range. My sum range is just going to be my sales data, hit comma, and then my first criteria is going to be locations. I want all locations that are [inaudible]. I'm going to delete this row here just to make sure we have the correct location data. I'm going to click "Delete Rows". You might be wondering how am I going to put in the criteria? Normally in my Excel file I put in these little helper columns. I'm some to write the number 1,000, 75, 50, 25, and 0. This way I can tell the SUMIFS formula what to reference as my criteria. Let's go ahead and write the formula out equals sumifs. My sum range is going to be sales again, comma, my criteria range is going to be locations that are less than 1,000. In this case, I had to put an ampersand less than 1,000, comma, but greater than or equal to 75 for this row, I'll just put in the double quote there, ampersand. Then I also want to see only stores that have a franchise in this case. I can put comma franchising, yes. Just to recap, I'm taking the sales, summing sales if locations are less than 1,000 but greater than or equal to 75, hit "Enter". Now we built out my formula, hit "Enter". If I just fill this formula down, it automatically will give me the value because I have my criteria and column L dynamically reference. I'm going to copy the same formula and put it into these cells right here. But the main thing is I'm going to change the franchising here to no because these are cells. I want my report here to have no franchising. I'm now going to sum these values really quick just to make sure the total sales is what I expect it to be, which is 1.689. Again, if I go down to my sales column here and write sum, this is indeed 1.689. This is how you would use the SUMIFS formula with dynamic cell references for our locations, but using structured references to actually pull the data from the Excel table. We saw how you can reference the columns in your table using structured references and we also showed how you can use structured references in some of the most popular formulas you use today like SUM and SUMIFS. We also saw how by writing your formulas with structured references, your formulas are a little easier to read and much easier to debug, especially if your colleagues are picking up the formulas or your Excel file from you. We'll now discuss how you can use structured references from your Excel tables with one of the most popular formulas you're using today, which is VLOOKUP. 6. VLOOKUP and Structured References: We'll be using the VLOOKUP formula now to look up an Excel table to bring the data into our main table of data. Working off of our same lists of restaurants now, you're seeing columns L and M, I have the city name plus the population number of that specific city. In step 1, I want you to turn that list of cities and population into a Excel table named Pop. Let's select these cells. You can click "Insert table" or one shortcut I like to use is this pressing Control T, which just brings up the create table menu for you. My table has headers hit "okay" and now we have this as a table. Just to be sure to click on "Table" and remember, we want to rename this table name to be Pop for our population. We have two tables now in this worksheet, we have Rest312, and then we have our Pop table, and you can see this in the top left of our worksheet. I want you now in step 2 is to add a new column to our main table and write a VLOOKUP to look up the population of our city based on column C, which is our location data. Let's call this population and just like we've seen before, when you add in a new column, the Excel table automatically picks up that column as being part of the table. Let's start writing out the VLOOKUP formula that you're very used to VLOOKUP. We want to look up the location column from our restaurant's table and notice how that's structured reference just turns into a hat symbol location. Our table array is you can just literally just write Pop and notice how when I write Pop, it highlights the cells in my Pop table. I could also write left bracket hashtag all and this references the entire table plus the headers, which I don't really need, but it won't really impact my formula. Let's just leave it as Pop, 2, 0 because I want to return the second column, which is the population and when I hit "Enter", it automatically fills down the formula for my entire list of restaurants. We have a lot of not applicable because not every single city in my restaurants table is in our location table. But let's say I want to add a new row of data to my Pop table, which is step 4. Let's say I want to add the population for Louisville, Kentucky. I'm just going to copy this for now through a paste here and let's say that Louisville is 50,000. Notice how that this row in my restaurants table gets automatically filled because my VLOOKUP formula is always doing a lookup to my Pop table and the minute I add a city to my pop table, it automatically expands the structured reference inside my lookup, which is in this column right here. We can do the same thing with index match, which you may be using before. I can write index and I want to select my entire population here. I want to match this location column within my Pop city location hit "Enter", and I actually want to do an exact match, which is why it's giving me values in every single cell. Let's do comma zero. It's should be working like this but, oh, I'm sorry, it's within this match. This will be giving you the exact same results as the VLOOKUP but notice how we're using the structured reference in multiple tables now. We're looking at the population from a Pop table cause it's a value you want to pullback. We're matching the location column, which is this column, and then we're comparing that with the city column, which is this one Pop table. One last thing to note about social references is that when you see the @ symbol, that is also a way to tell Excel this is referencing this row in my table, which is why you only see Seattle, Washington, but not the entire column which would just be location by itself. This would still work but to be more specific, you want to put that @ symbol to make sure you're referencing just that row of data. We looked at how you can use the VLOOKUP formula, a really common formula used today, with structure references from your Excel tables to dynamically lookup data so that when you add a new data to your lookup table, to your main stats table, that they don't automatically gets refreshed and pulled in correctly. We saw how you can use structured references and formulas, now we're going to go into a more advanced use case of using structured references with a drop-down list and data validation in the next lesson. 7. Dropdown Lists and Data Validation: We're going to build a Dropdown List using Excel tables, and we'll show you how these flexible options can auto-expand using the table. With our same lists of restaurants, we have this separate list of data here called KnownFor. We just have some various attributes that we want to tag our restaurants with. But I want to turn this list of attributes into a dropdown list we can use in our main table of restaurants. In step 1, let's turn the Known For list into a table called Known For. Right up here, we're going to insert table and my table has headers. Then OK. In the table name, we want to call this KnownFor. We have this as our main table for selecting attributes. I want you to now go to Formulas and then go to Define Name and give this table a define name of types. We don't want to include the headers. Now the table name and define names are two separate concepts. This is something that gets a little tricky when building dropdown list with Data Validation. We're going to go to "Formulas", "Define Name" and you notice here I have a bunch of defined names already, which includes tables. I'm going to click on "Plus", and let's call this types. What is the range of cells here? You can just write KnownFor because that's going to be our list of our table. You notice how when I click on table here, it still says KnownFor, if I go back to formulas define name, you'll see that if I click on types, it references my range of cells, which is KnownFor. Now, let's go to step 3. I want to create a new column in my main table called KnownFor and then go to Data Validation, which you've done before probably and create a dropdown list, where we're going to use the types define name. Just to recap, we have a Define Name called types, which references our KnownFor table. Let's click on "Column J" Known For and now let's go to "Data" "Data Validation". We're going to change this allowed to list and what is our source is going to be? You may be tempted to write equals, KnownFor as our list, but we have to actually use our define name called types, which references our KnownFor table. Let's write equals types, hit "OK", and let's see what happens in this dropdown. You notice that we get all of the values from our KnownFor table. I can also just drag this formula down and you can click on the dropdown, you can see all these values. One thing to keep in mind is if you click on Formulas, Define Name and in my KnownFor table reference here, if you had accidentally selected the entire table here like this, you notice how the reference changes from KnownFor to KnownFor All KnownFor. I know this looks complicated, but the reason why this is problematic is if you hit okay, this will now include the KnownFor column header inside the dropdown, which you don't want to have happen. That's why I'm going to redefine this just to be KnownFor. This way you only have the selectable options in the KnownFor table. If I drag this all the way down to the rest of my table, I now have this attribute which I can then select from my KnownFor table. Another caveat about dropdown list, which you may have seen before is if I change this from smoothies to smoothies-YM, you notice how all my sandwiches still remain sandwiches. I have to actually click on the Dropdown and rename this to sandwiches-YM. Just to make sure if you are correcting any values here in your KnownFor dropdown list, you have to manually change it here in your main table. Now, of course, the benefit of using Excel tables in our dropdown list here, is if I add a new value to my KnownFor table, let's say I call this pancakes, I now get pancakes as a selectable option in my dropdown list, which is really nice to see because I don't have to worry about the formula referencing the proper cell references in this column. I can just reference the table via the define name. Now, one more step here is, we can simplify this a little bit by using the indirect function and our source field for the data validation. Let's delete all this for now and let's say I click on Data Validation. In this source field, instead of writing types, which references this define name, which references this table, I can actually write the indirect function and write the table name which is KnownFor. Then put in the column name, which is ("KnownFor[KnownFor]") What I'm telling Excel here is give me the KnownFor table, which is this and the column name is Known For, hit "OK" and this will also give me the dropdown list for my data validation. Just another way to not having to use a Define Name, which then references the table, if you want just to shortcut it and just reference the table itself by using this indirect workaround. To recap, think about what you would have to do to create this dropdown validation with a dynamically auto expanding list using something like the count function or the offset function. The benefits of this Define Name Now is that you can use this anywhere in your Excel file where you want to reference this specific table. Again, going back to the previous lesson, think about how your table names and your define names can be variables that reference all different tables in your Excel file. We built a dropdown list using the Data Validation feature in Excel. We use Excel tables as the selectable options in our dropdown list. We've done a variety of different things in Excel tables with structured references and formulas. Now let's dive into formatting our tables so they look really nice and pretty for our team and for our colleagues. 8. Formatting Excel Tables: We'll talk about quick ways you can format your tables so they look really nice and pretty and well-formatted for your team and for your colleagues. We have a new dataset in this lesson. It's a list of Bitcoin prices by date in 2013. As you can see, the price was obviously a lot lower back then. We have the open, high, low, close, and the spread for these various daily Bitcoin prices. In step 1, I want you to click on the drop-down in the table formatting section to create your own custom table format. If I click on the table, Table tab in the ribbon, and if I click on this drop-down here you can see a variety of different formatting options you can apply to your table based on what you need for your reports, your dashboards and also what your colleagues and teammates might find useful for them. Now, one thing that's really beneficial about these formattings is that you can customize this even further by creating a custom table format by right-clicking on table format in this ribbon, click on Duplicate, and then you can click on, let's call this AlBitcoinTable. The reason why I might want to customize my table in this specific scenario is that maybe I want to highlight just like the weekend Bitcoin prices, which is day of the week six and seven, and then highlight the weekday prices which are day of the week one through five. My first row stripe, that's like the first color-coded row. I can change this instead of being a size of one, changes to a size of five. Then in my second row stripe, I can change this from being a size of one to a size of two. What this does is instead of alternating colors every row, it alternates colors every five rows, and then two rows, five rows, two rows correspond to weekdays and weekends. Furthermore, you can also click on format here to really just go and format the color of the specific band if you like. I'm just going to hit "Okay" for now. If I click on the drop-down again here in my table formats, I can click on this custom format. You notice how the formatting now changes from every other row to be color-coded, to be first five rows, then two rows to really highlight the weekend and weekday prices of Bitcoin from 2013. Finally, one more thing about tables formatting is sometimes you might want to color code specific parts of your columns. Let's go back to our regular table formatting here, which is something like this. If you click on first column, you notice how it automatically highlights the first column of data to be called out. Last column just highlights last column and then banded columns. If you have a specific type of formatting where you have banded color coding. Let's click on this one again. Notice how by default it has banded rows. If I click on banded columns and unchecked banded rows, highlights every other column instead of rows. Just some more additional formatting you can apply to your table is based on your team's needs. We walk through how you can customize your table formatting with some of Excel's built in formatting for Excel tables, but also how you can customize the alternating number of row colors for your Excel table. We learned a lot about ways you can use Excel tables for formulas, to structured references, to formatting. Now, let's wrap everything up into your project for this class. 9. Final Thoughts: Congratulations, you've taken this class on Excel tables, and we learned everything on how to create Excel tables, how to use structured references in your everyday Excel formulas like VLookup, some SUMIFS. How you can apply them to drop-down lists and data validation so that your tables in Excel, and your dashboards, and reports are easier to read, your formulas are easier to debug. Your colleagues will be much happier when they're looking at your Excel files. I hope you'll take all the learnings from this class and apply them to the class project, where you'll be analyzing a dataset from the Department of Transportation on flight delays. I hope you'll share your findings and screenshots of your reports in the project gallery for everyone to see. Thank you so much for taking this class, and I look forward to seeing your projects.