Simple but effective, part 4 – further Table extensions

Excel Tables – so much more than just a pretty format

When Tables were first introduced into the Windows versions of Excel in Excel 2007, many people saw them as another Excel formatting ‘gimmick’. This view was supported by the inclusion of the Format as Table command in the Styles group of the Excel Home Ribbon tab. However, it soon became apparent that Tables were much more important than that, and that they had a significant role to play in many aspects of automating Excel.

Having looked at different methods of turning ranges of cells into Tables in part 1, we saw how Tables expand to incorporate new rows and columns in part 2, where we looked at automatic expansion, and part 3 where we saw how to expand tables manually. At the end of part 3 we started to see the role Table expansion can play in automating the creation of a summarised report.

This time, we will explore further automation techniques including the use of Data Validation and Dynamic Arrays.

Using Tables to make data processing even more automatic

The example at the end of the previous part showed how summary totals based on Table columns could be automated so that they would remain correct as rows were added to, or removed from, the Table. The formulas that we used were based on using the SUMIFS() function to summarise a sales value column based on the entries in a product item column, but the approach depended on no new product item descriptions being added to the Table. We are going to adapt our example to remove this restriction:

First of all, we will assume that we are going to enter the data we require into our Excel Table. The issue of whether Excel is the right application to use for entering data has been discussed in the community on several occasions, but Tables do make it a bit more practical to enter simple data into a spreadsheet. We have already seen that certain cell attributes such as consistent formulas and consistent formatting will automatically be copied down to new rows. Data Validation is another cell property that can be copied automatically to new rows in this way.

We have reorganised our previous example to include our summary as a Table. We have then added Data Validation to the Item column of our Sales data entry Table. The Data Validation uses a list, and the source of that list is the Item column of our summary Table. Because the source of our Data Validation is a Table column on the same worksheet, as we add items to our summary Table, they will be added to the dropdown in the Item column of the data entry Table. Note that if the cells to which Data Validation is applied are on a different worksheet to the source Table, the Data Validation source will not be updated in the same way:

As we have seen in previous parts, the SUMIFS() formula in the Total Sales column will also be copied down as rows are added to our summary Table.

Because the Data Validation ensures that only items in the summary Table can be entered in the data entry Table, then the summary should automatically include all the items in the data entry Table.

Dynamic Arrays

A Dynamic Array formula spills into as many cells as required to display the values derived from a reference to a range of cells or values created by a function. Dynamic Array formulas work well with Tables as their source as the result will automatically spill into additional cells if required as the dimensions of the source reference changes. However, a Dynamic Array cannot itself be part of a Table. In this example, we have used the UNIQUE() Dynamic Array function to create a dynamic list of all the distinct items in the Item column of the Sales data entry Table.

=UNIQUE(Sales[Item])

The UNIQUE() function creates a list of all the unique or distinct items in a reference and that list then spills into as many rows as required.

We can then use the # operator with the SUMIFS() function to add a separate column that automatically adjusts as the number of items changes. The # operator expands a reference to include all the cells that a Dynamic Array formula in that cell refers to. As the list created by our UNIQUE() formula expands or contracts our SUMIFS() formula will also spill to the same number of rows, automating our summary.

=SUMIFS(Sales[Price],Sales[Item],D9#)

One of two new Dynamic Array aggregation functions, currently available in the Insider Excel update channel, will make this even easier. The new GROUPBY() Dynamic Array formula achieves all of this in a single formula:

=GROUPBY(Sales[Item],Sales[Price],SUM)

This is just a simple example of the use of GROUPBY(). The function also includes additional arguments to cope with the inclusion of header rows, multiple levels of grouping, sorting and the inclusion of filters.

Next time…

Next time we will start to explore some of the options that are available in the Table Design Ribbon tab.

Conclusion

You can explore Tables, the SUMIFS() function, Data Validation, Dynamic Array formulas and a great deal more, in the ICAEW archive:

• Browse the archive