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 that table and column names which makes your formulas easier to interpret. I'll show you how to use table references in this movie. In this workbook, I have an Excel table, and you can see it's in columns A through D, and it shows monthly sales broken down by year, quarter, month, and of course, the sales value.
Let's say that I want to find the sales for November of 2017, and that value is in cell D12. To create that reference, all I need to do is type a standard formula. So in cell G1 which, for me, is already selected, I'll type an equal sign, and then I will click cell D12, press return, and I get the value. Now let's say that I want to find the sum of all sales in the table, and to do that using table references.
Well, the first thing you need to know is the name of the table. So, I will click any table cell. I clicked B4, could be any one, and then I'll go to the "table" contextual tab on the ribbon, and I will look at the far left side of the ribbon tab, and in the table name box, I see that the name of this table is "SalesData," so that's what I will use in my formula. Now, I'll click cell G2, where I want to do the summary, type an equal sign, and then I will use sum as my function followed by a left parenthesis, and now I can use a reference.
The table name is "SalesData," and you can see that after I typed S-A-L that it's highlighted in the formula auto-complete list, so I'll press tab. Now, I want to get the values from the sales column, so I will type a left square bracket and then "sales," and you can see that the name of the "sales" column appears, so I'll press tab. Type a right square bracket to close out the table reference, in this case the table column reference, followed by a right parenthesis to close out, return, and I get the total sales value.
The great thing about using tables in Excel is that if you add a row to a table, because the table reference refers to the entire sales column, the formula result is updated automatically. So, I'll use my mouse to scroll down to the last cell in the table. That's cell D25. Then, I'll click there. Then, I will press the tab key to create a new row. Then, I'll type 2019, quarter number one, January, and let's say the sales were 500,000.
So, 500 followed by three zeros and return. Now, when I scroll back up, I see that my total sales has increased by 500,000. Referring to Excel tables in your formulas makes them easier for you and your colleagues to understand. However, if you work with colleagues in Google Sheets, you should know that Sheets does not have the Excel Table capability, and it coverts table formulas to cell references.
That means if you add data to a list in Google Sheets, you will need to update your formula so that it includes the new values in the summary.
- Creating workbooks
- Manipulating cell data
- Using core functions and formulas
- Formatting worksheet elements
- Creating PivotTables
- Collaborating with others
- Working with charts
- Adding images and shapes
- Saving workbooks
- Printing files
Skill Level Beginner
1. Get Started with Excel
2. Manage Workbooks
3. Work with Worksheets, Cells, and Cell Data
4. Sort, Filter, and Manage Worksheets
5. Summarize Data Using Formulas and Functions
6. Format Worksheet Elements
7. Work with Charts
8. Work with External Data and Objects
9. Explore PivotTables
10. Review and Share Your Spreadsheets
- 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.