Skip to content

Excel Tables Make Your Formulas Better

If you are an avid reader of the Spreadsheet Life blog, then you probably remember me mentioning the power and usefulness of Excel tables.

Either way, it’s time to talk about tables again!

This article will focus on how tables can make your formulas easier to write and more intuitive. If this sounds like something you’d like to learn, then read on!

Tables Make For More Intuitive Formulas

Take a look at this formula.

=SUM(C2:C9)

Without being able to see the spreadsheet this formula comes from, can you tell what it’s doing?

Aside from deducing that this formula is calculating the sum of something, it’s impossible to guess what that sum might represent.

If you are curious, here is the spreadsheet that goes along with the formula.

You can now see that this formula calculates the total number of pages from a list of books.

Here is the same formula as before, but this time using references from an Excel table.

=SUM(Book[Pages])

Even without viewing the spreadsheet data, you can instantly infer that this formula is adding up the pages for a list of books.

Furthermore, this becomes even more obvious when provided with a view of the spreadsheet.

As you can see, the formula referencing the table is much easier to read and understand compared to the formula using generic references.

Tables Make Formulas Easier to Write

Let’s say you have a list of authors that goes along with your list of books.

The goal is to craft a formula that counts the number of books for each author. That seems simple enough.

All you have to do is use the COUNTIF formula and use your mouse to select the proper cell references.

Once that’s done, all that’s left is to use the fill handle to copy the formula down for all of your authors.

Now, what happens when you work with the same data in Excel tables instead?

This time, you can build our COUNTIF formula using table references. This is much easier to do for three reasons.

  1. It is much easier to type in these references
  2. It is much easier to select these references
  3. These references are much easier to read and recognize

But wait, there’s more. When you finally finish your COUNTIF formula, it is immediately copied down to the rest of the cells in the table. You don’t even have to use the fill handle.

As you can see, the formula referencing Excel tables took much less effort to write.

Hopefully, these examples do a good job of demonstrating that Excel tables really do go a long way in making your formulas better overall.

Of course, tables are not always an option. But when they are, it is a good idea to use them!

This Post Has One Comment

  1. This is so handy especially when I’m sharing Excel documents with people at work. My favorite thing is when I update the formula in one cell in the column of a table, it updates the rest of them for me! Excel Tables are definitely a hidden gem!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top