- Excel tables provide a structured way to store data within your workbooks. 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. In the columns A through D over on the left side of the worksheet, you can see that I have a table. I have four columns, Year, Quarter, Month, and Sales, and my goal is to create a formula that finds the total of the values in the Sales column.
So I could type a formula such as equal sum left parantheses and then identify the cell range of D2 and type a colon and scroll down and I see that my last row with data is row 25, so I'll scroll back up. So I have D2 to D25, right parentheses and I get 7.5 million. Now this is a perfectly legitimate way of creating a formula. But, if I click cell G2 and take a look at the formula on the formula bar I see that I'm finding the sum of D2 to D25 but it doesn't tell me anything about the data.
Now this is a simple worksheet so I can just look to the left and see, Okay, it's sales data. And it's easy enough to figure out on its own. But if I want to make the formula a little bit more readable, for example, for data that is separated widely from the formula that summarizes it, I can use what's called a table or structured reference. To do that I first need to find out what the name of this Excel table is. So I'll click any cell in the table, I'll just click C4 for convenience, and then I will go up to the ribbon and click the Table contextual tab.
On that tab if I look all the way to the left I can see the Table Name box. And that tells me that the name of this table is Sales Data. So now I have enough information to go forward and create my formula. So once again I'll click cell G2, type an equal sign, and I'll type sum and a left parentheses. But now I can type in the name of the table and that was Sales Data. As soon as I type S-A-L I see Sales Data in the formula auto complete list so I'll press the Tab key.
Now I need to refer to the column, and that columns name is Sales. So I'll type a left square bracket and then S-A-L-E-S. Or I could have used the formula auto complete value. Then I'll type a right square bracket to close out that reference and a right parentheses to close out the formula. Now when I press Enter I get exactly the same result, but when I click cell G2 and look at the formula bar, I see a much more informative formula.
Instead of having to figure out what cells D2 through D25 represent, I know that it's sales data and that it's probably a number.
Author
Updated
2/18/2016Released
8/7/2015- Customizing the Ribbon
- Formatting worksheets, cells, and cell data
- Sorting and filtering data
- Working with formulas
- Detecting formula errors
- Creating charts
- Importing data
- Inserting objects and graphics
- Using PivotTables
- Recording macros
- Sharing workbooks
Skill Level Beginner
Duration
Views
Q: This course was updated on 02/18/2016. What changed?
A: We updated one tutorial, "Managing objects using the Selection pane." The new Selection pane, released in a January 2016 Office update, allows Excel for Mac users to more easily rearrange worksheet and slideshow objects.
Related Courses
-
Introduction
-
Welcome59s
-
-
1. Getting Started with Excel
-
Getting help in Excel3m 11s
-
2. Managing Workbooks
-
Setting workbook properties2m 45s
-
3. Working with Worksheets, Cells, and Cell Data
-
Creating named ranges5m 58s
-
Creating an Excel table5m 9s
-
4. Sorting, Filtering, and Managing Worksheets
-
Sorting worksheet data3m 6s
-
Creating a custom sort order4m 49s
-
Filtering worksheet data3m 55s
-
Managing worksheets5m 4s
-
-
5. Summarizing Data Using Formulas and Functions
-
Adding a formula to a cell3m 56s
-
6. Analyzing Data and Formulas
-
Rounding cell values4m 14s
-
Managing scenarios7m 1s
-
7. Formatting Worksheet Elements
-
Managing text alignment4m 46s
-
Copying cell formats3m 29s
-
Managing cell styles4m 10s
-
Managing Office themes5m 45s
-
8. Working with Charts
-
Creating pie charts2m 25s
-
Creating line charts3m 11s
-
Creating XY (scatter) charts2m 38s
-
Creating stock charts2m 42s
-
Adding trendlines to charts3m 31s
-
Creating sparkline charts4m 17s
-
9. Working with External Data
-
Using hyperlinks4m 18s
-
10. Working with Objects
-
Adding and adjusting images4m 58s
-
Manipulating text boxes3m 20s
-
Creating SmartArt graphics4m 19s
-
Creating WordArt2m 55s
-
11. Exploring PivotTables
-
Applying a PivotTable style2m 26s
-
12. Reviewing and Sharing Your Spreadsheets
-
Checking spelling2m 55s
-
Managing workbook comments3m 29s
-
Exporting to other formats2m 48s
-
Protecting a workbook3m 23s
-
-
13. Automating Workbooks Using Macros
-
Running an existing macro4m 31s
-
Recording a macro2m 46s
-
Adding comments to a macro2m 23s
-
-
Conclusion
-
Next steps1m 8s
-
- 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.
CancelTake 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.
Share this video
Embed this video
Video: Referring to Excel table data in formulas