Search other Excel resources (AccountingWeb, Beancounters Guide, IT Counts):

Automate Excel with tables

Introduction

We've covered the new Excel Table feature already in lunchtime learning:

Lists and tables: Excel's most underrated feature?

Lists and tables 2: data entry

This time, we are going to look at using two linked tables to create a set of summary data that could be used as an intermediate step in a management reporting workbook.

If you are very comfortable with the use of PivotTables, then you might find that you can obtain a similar result more directly using a PivotTable, but the use of 'normal' Excel tables can be more predictable than the use of a more dynamic PivotTable and the structured language Table references are easier to work with than the GETPIVOTDATA() function. Equally, if you are happy working with relational databases, then it might be best to do the work in a database and just use Excel to link to the final result. Assuming that we are going to go the Tables route, we are going to create two external data ranges on two separate sheets. In Excel 2007/10 external data ranges are automatically created as tables. Our first range will link to a table of Orders in the Microsoft Northwind database:

and our second range to a list of products:

In both cases we have used the following option to enable us to select the fields we need in each external data range: Data ribbon, Get External Data group, Form Other Sources, From Microsoft Query. We have then navigated to the Northwind sample database, chosen the table or query we need to use and chosen the individual fields from that table. We have used the Table Tools, Design screen to give the name Invoices to the Orders table and Products to the Products table. We are going to create a summary of order totals for each of our products. Because both our orders table and products table come from the same database, the two should be 'in sync' with each other. Adding a column heading adjacent to our existing Products table will automatically extend it to incorporate the new column and a formula we enter on the first row will automatically be extended down to the bottom or the column. The formula is created using structured table references either by selecting the required range or cell or by typing them in with the aid of AutoComplete:

We have used the Excel 2007/10 function SUMIFS() rather than SUMIF() to make it easier to add additional criteria if we need to. Our formula is: =SUMIFS(Invoices[ExtendedPrice],Invoices[ProductID],[@ProductID])

• The first argument is our sales total range - the ExtendedPrice column in our Invoices table
• The second argument is our first criteria range - the ProductID column in the Invoices table
• The third argument is our first criterion - the ProductID on this row (@) in the Products table.

As this is automatically copied to all the other rows in the column, it will generate an order subtotal for each product. We have used a simple SUM() formula to incorporate some check totals:

We'll extend it a bit by adding three further columns and a short set of three country names. We'll then extend our formula to include the additional criteria referring to the Country cells D$3, E$3 and F$3:

If you try and create these formula by entering the Country1 formula in D7 and then dragging it to F7 you will notice one of the potentially irritating aspects of table structured references. They are relative. F7 will end up as:

=SUMIFS(Invoices[OrderID],Invoices[Salesperson],[@Total],Invoices[OrderDate],F$3)

The columns references have been shifted along as though they were relative cell references. To avoid this, enter the formula in D7 then select D7:F7 and use the 'fill right' keyboard shortcut to fill the formula with absolute references:

 =SUMIFS(Invoices[ExtendedPrice],Invoices[ProductID],[@ProductID],Invoices[Country],F$3)

If we were now to enter more data into our Northwind database, including creating a couple of extra products with orders, then refresh our tables, the products table will automatically include the new products, the invoices table will include all new orders and all our formulae will automatically be copied to our new rows, so we will automatically get the new subtotals for our new products. You will also see another feature of tables - the table headings are displayed in the column heading area when they scroll off the screen:

Get lots more Microsoft Office hints and tips