If you have been just introduced to Excel, the first things you should learn are the basics. These include formulas like SUM(), how to format cells, and other basic ways to format results. The second thing that you need to know is data tables.
So why are Excel data tables so important? Many spreadsheets don’t use data tables and run just fine. The answer is that using data tables changes the way that many formulas work with data in Excel.
Gaining an understanding of data tables is important for eventually understanding:
- SUMIFS (and other similar formulas)
- Pivot tables.
- DAX / Power Pivot
The Data You Need for a Data Table
The first thing that you will need before creating a data table is the sort of data that belongs in a data table. This data is referred to as tidy data; meaning that data reflects a single observation and has a descriptive column name. (There are other elements – but these are the basics!)
Cells that are being used as inputs for other areas, like the example below, should not be used as a data table.
Creating Excel Data Tables
To create the data table, select Table under the Insert menu.
When asked where the data is for the table, select all the data from the range containing values for the table.
Generally, this will just be a continuous set of data, though it’s OK if there are some cells that are empty. There are other tools available to make sure that the data is correct!
Finally, check the box that the table has headers. This is important in making sure that the names can be used by formulas!
Once you click OK, the table will be complete! You’ll know that the data has been converted to a table when:
- There are drop down filters available in all the columns.
- The data appears differently; the colours are different each row (this is called Banded Rows.
Using Excel Data Tables in a Formula
Now that we have a data table, we will contrast using the data table vs. using a normal range for the COUNTIF() formula.
This formula counts the number of times a selected criteria is met within a data set.
Using COUNTIF() without a data table
When using the COUNTIF() formula, you need to specify a range – in this case, the range is F2 through F13. Next, specify the criteria, and you will get the number of wins in the data set – in this case, 4.
There is nothing wrong with using a range; however, there is no description of what the data in F2 through F13 represents.
There is also no control over the formula; if someone were to add the game which occurred on April 10, the formula would not pick up that value. The formula could be modified to include more rows but could be at risk of using data not intended for the initial formula.
The best way to ensure that only the required data is included in your formula is to use a data table.
Using COUNTIF() with a data table
The first major difference with using a data table, is that as you enter the formula, it offers to select the table for you. Since the table that we created was the first table on the worksheet, it defaults to Table1.
After selecting the table, you can enter a left bracket [ in order to bring up the list of fields within the table. Instead of having to select the range containing the cells, you can select based on the name of the column!
The rest of the formula can be entered the same way as when a table was not used – but using the table makes it much easier to work with the formula!
Being able to use formulas with readable column names is one of the ways that using Excel data tables is easier than just using ranges.
Tables are for more than just making formulas easier to read! There are some ways that Excel interacts with data that require a data table including:
- Data automation using Power Automate.
- Modifying information using Power Apps.
- Analyzing information using Power Query or Power BI.
These functions will be explained in later posts; until then, enjoy data tables!