Excel Tables help organize your data and they are packed with useful features that facilitate analyzing information. In this lesson we will start with a small subset of data to demonstrate how Excel Tables function. In this post we will explain how they work as a preview to the course available on ASM titled Company Data Analysis in Excel.
Creating an Excel Table: Excel provides a few options to create a table. Once you have highlighted the data you want to convert to an Excel Table, create the table with one of the two approaches bulleted below.
- Keyboard Shortcut: Ctrl + T
- Excel Ribbon: Insert > Table (Keyboard Shortcut: Alt + N + T)
Whichever option you choose, Excel will prompt you to confirm the cell range selected for the table. In the image below you will see that the “Create Table” dialog box includes a check for “My table has headers.” This should be checked for any data set with headers across the top.
The next step is to name the table, and this can be done by selecting Table Design in the Excel Ribbon, and then changing the name under Table Name (see red rectangle). In this exercise, I have used the name “Small_Table” to introduce the formulas that follow.
Excel Table Formulas: Tables use a unique formula syntax, which makes it easy to refer to parts of the data by the header name. This feature is known as “structured references,” and it makes writing formulas that reference table data surprisingly easy.
For example, if you want to sum all cells under the “Total Revenue” column, you could use the =SUM function to reference both the table name (“Small_Table”) and column header (“Total Revenue”). Per the image below you will note that the column header must be placed in brackets.
Adding criteria to the formula is easily accomplished with the =SUMIFS function. If you want to sum all cells under the “Total Revenue” column for the first month of 2023, you could add the date as criteria. See the image that follows for an example.
The =SUMIFS function allows for a lot of additional criteria, which makes it very easy to work with much larger data sets. For a more thorough demonstration please see the mini-course titled “Company Data Analysis in Excel” (available as part of the ASM+ tier).
Download Excel File: Excel Tables Introduction_Small Table