Using Data Tables in Excel

Data tables in Excel are basically simple, multi-modal inserts that can allow you to filter and sort through data (press the downward arrows on the table header). Download our short data tables Excel sheet.

Excel Data Tables.xlsx

Download XLSX • 18KB

You can do a lot of that manually though (insert filter for example or '=FILTER), which is generally preferred for financial statements, as data tables can be annoying to work with as they get linked across models. I do like them for generally standalone datasets like inventories however and they offer at least main advantages over manual data with applied filters/sorting.

The first is that the data tables will update their ranges automatically. For example, in the sheet provided, I have total revenue as the sum of the revenue column in the table. When I add new items to the end of the column, the total revenue gets updated (note that for a normal data set this will happen if data is added in range or between the first and last).

The second is that its much easier to add slicers, which will allow you to isolate columns. Of course, if you're savvy you do effectively the same thing with '=XLOOKUP or '=LARGE/SMALL for size rankings.

Anyways, I don't use data tables very much, but laymen will undoubtedly find uses for them.

If you found this helpful or enjoyable, please consider subscribing to our newsletter for more.  

You can reach out to us here for consulting services or here to discuss booking a workshop to learn useful skills and help you get more familiar with us.