Microsoft Excel Tables – Creating Maintainable Spreadsheets

I’m a huge fan of Excel and like many people use it for a whole range of purposes, some of which it most definitely wasn’t designed for!

One of the strengths of Excel is that it is fairly easy to knock up a quick spreadsheet for a given task and often these become part of the standard toolset, gaining far more importance than intended.

Research studies have found that around 80% of spreadsheets analysed have some form of error and often these were being used to control large scale operations.

Part of the problem is not transitioning from the quick and dirty to the long term tool.  I’m sure we’ve all come back to a spreadsheet and found the easily remembered =b5*c5-f5*0.443 type of cell.  That’s before we get into lookups and links to other sheets.

One way to address some of these issues is to make use of names rather than simply relying on numbers.  The second is to remove as much repetitive typing as possible.

To demonstrate I’ll be using the Table feature on a simple budget monitoring spreadsheet to go through some simple examples of where taking a little bit of time up front makes life much easier.

To start I will create a standard range of numbers as follows:

Rather than applying any formatting to this, instead we select range and then click on Format as a Table from the ribbon and then select the design we want from the menu.

You want to make sure you select the “Table has headers” checkbox as we will be using those headers later.

You will now have a formatted table of data and a new tab on the ribbon for Table which will appear whenever you select the table.

You will have noticed that because you selected headers there are filter arrows on each column and these can be quite annoying so uncheck the option for Filter Button .

Now we can get to work.

First off, give the table a meaningful name; I’ve called this on tblBasicBudget.  Having a prefix such as tbl just makes it easier to find all the tables should the design get more complex.

I now want to add some more features to my table.  First off a simple profile column, i.e. the allocation spread evenly over 12 months.  As a Table we can add columns either by right clicking and selecting insert to left or right or by clicking the cell next to the final column and typing a new header value. Having done this and named the column “Profile” I can enter the calculation into the first data cell.  In this case it would traditionally be entered into cell O4 as =B4/12.

(As a quick aside, the Table works very much how tables in Word work so you can insert rows at any point by selecting a row and using the Insert menu, or to extend the table from the end, select the last row of the last column and hit Tab.)

The first thing to notice is that when you enter the formula into O4 is that it copies down into the remaining rows.  This automation prevents any copy paste errors or typos.  Providing you get the formula in O4 correct the rest will follow.

However, the purpose of this article is to limit the amount of “Excel speak” so we can start by reworking the O4 cell. Remember earlier when we created the table with headers?  Well Excel now has those headers as objects that it is aware of.  This does mean that you can’t have duplicate header names but that probably isn’t a bad thing.

To access these objects, click back into O4 and type =[ and you will be presented with a list of all the headings in the table.  You can either move up and down the list using the arrow keys and then hit Tab on the one you want or click the desired option.  By autofilling in this way you remove a possible source of typos but of course you can manually type (or copy and paste) these as with other formulas.

In this case what we want to end up with is =[@Allocation]/12 and hit return.  Again this will be copied down to the other cells.

The [] tells Excel that we are referring to the current table and the @ to the current row.  If you miss the @ it can use all the rows which isn’t what we want!

We are now going to add some more columns as described above.  We create the following:

• column P as Actual
• column Q as %
• column R as Status

We’ll deal with Q and R later.  For now we want to have the Actual column reflect the expenditure to date.  Again in traditional formula that would be =sum(c4:n4) entered into P4 but we will instead use names.  Into P4 enter:

=sum([@Jan]:[@Dec])

Already you can see that this is far clearer to see what you are calculating.  In fact, in an earlier draft of this article I had incorrectly typed the old style formula above as =sum(a4:n4) which sort of made my point!

In order to proceed we need to make the spreadsheet actually do something useful so back in O4 we are going to make this now a cumulative profile rather than the flat one as follows:

=( [@Allocation]/12) * COUNT( [@Jan]:[@Dec])

The use of names again makes the intent behind the formula a lot clearer than using cell references.

To finish this part, click into Q4 and enter:

=[@Actual]/[@Profile]

Format the cells Q4:Q10 as % and we are ready to move on.

We mentioned before that to add a new row we simply select the last cell of the table and hit Tab.  If we do that it automatically copies the formulas and formatting, again reducing the chance of errors.

To start to explore some of the power of names I have created a new Sheet in the Workbook and created another table, tblStatus, which has three columns; Status, Description and Lower Limit.  This contains four rows of Green, Amber, Red and Black and a % threshold that applies.

Into column R I now enter:

=lookup( [@[%]], tblStatus[Lower Limit], tblstatus[Status])

This tells Excel to lookup the value in each row of the % column and reference it to the Status table.  Note that by using tables and names there is no need to specify that this is in a separate Sheet and makes the whole formula much more readable.  No more =Sheetx!$b$4:$b$8 to deal with.  It also means that if we add, subtract or reorder the table it will still work.

It is also worth noting that you can use the names outside of a table as well, so if we choose an arbitrary cell, say A18 and enter a formula such as =MAX(tblBasicBudget[Actual]) we will get the desired answer, and again if we add rows to the table later this will automatically adjust to include those new data.

For completeness we apply some conditional formatting:

and we end up with:

Conclusion

There are lots more examples of using names in Excel but hopefully by demonstrating the ease of using Tables it is clear how this helps reduce errors by automating repetitive formula entry and makes formulas much easier to understand, particularly if you revisit a spreadsheet some time later or indeed inherit one from someone else and want to understand what it does.