One major benefit of using Excel tables is that you can refer to table columns in your formulas using the table and column names, which makes your formulas easier for you to interpret.
- [Narrator] Excel tables provide a structure within your workbooks that you can use to store your data. One major benefit of using Excel tables is that you can refer to table columns in your formulas using the table and the column names which makes your formulas easier for you to interpret. I will show you how to do that in this movie. My sample file is the Table References Workbook, and you can find that in the Chapter Five folder of the exercise files collection. This workbook contains a single worksheet and in it I have a table that shows monthly sales data for two years.
And if I scroll down you can see all that data going down to row 25. This table has a name. So if I were to click any cell on the table then the table contextual tab appears on the ribbon I'll click it's tab and you can see that at the far left we have a table name box and this table is called Sales Data. So if I want to refer to the data in this table I can use the table name and the sales data is very descriptive.
I'll start by referring to a specific cell. Let's say the November 2017 sales which appears here in cell D12. To start I will click in cell G1, that's where I want to create my formula and then I'll type an equal sign, and if I want I can simply click cell D12, so I'll click that, and you see that I'm referring only to cell D12. I'm not referring to part of the overall structure of the table.
So in this case the reference is completely normal exactly like you'd see elsewhere, now press Return and I get the value. But now let's say that I want to find the total of all sales. In other words the total of all values in the sales column. In cell G2, which was selected after I pressed Return I'll type an equal sign, then I'll press Sum, which is the function I want to use followed by a left parenthesis. Now I can start typing in the name of my table.
And if you remember that was Sales Data, so I'll type in Sales, and as soon as I type in the SA, you can see in the formula auto complete list that all I have is the Sales Data table name. I'll press Tab to accept it through formula auto complete. Now I need to refer to the sales column. To do that I'll type a left square bracket and then Sales. You can see that Sales pops up in formula auto complete. This is slightly different from the Windows version but after I type capital S, I get Sales, press Tab, I have Sales, type a right square bracket and you can see that the Sales column is selected.
Right parenthesis to close, press Return, and I have my total. One great thing about Excel tables is that they expand it to include new data. So if I were to add another row, say for January 2019 to the table then the total sales formula that I created in cell G2 would incorporate that data as well.
- Creating workbooks
- Manipulating cell data
- Sorting, filtering, and managing worksheets
- Using core functions and formulas
- Formatting worksheet elements
- Creating and managing conditional formats
- Working with charts
- Adding images and shapes
- Working with PivotTables
- Exporting workbooks
Skill Level Beginner
What you should know1m 11s
1. Getting Started with Excel
2. Managing Workbooks
3. Working with Worksheets, Cells, and Cell Data
4. Sorting, Filtering, and Managing Worksheets
5. Summarizing Data Using Formulas and Functions
6. Formatting Worksheet Elements
7. Working with Charts
8. Working with External Data and Objects
9. Exploring PivotTables
10. Reviewing and Sharing Spreadsheets
Further information1m 2s
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.Cancel
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.