In this series we are going to examine some of the capabilities of Excel Tables. We will be concentrating on the practical use of Tables to improve spreadsheet productivity. This time we focus on the use of Data Validation and Dynamic Arrays to automate the creation of summary reports.
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…
Conclusion
You can explore Tables, the SUMIFS() function, Data Validation, Dynamic Array formulas and a great deal more, in the ICAEW archive:
Archive and Knowledge Base
This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.
