Create Variable-Length, Dynamic Reports Linked to Excel Tables

Excel Tables are a powerful feature introduced in Excel 2007. Not only can you report from them directly, you can use them as a source of data for dynamic reports, including variable-length accordion reports.

The first task is quick and easy; the second is a lot more interesting. Let’s look at both.

Report Directly from Excel Tables

Suppose you have a table like this and you want to generate a call-assignments report for each sales person.

The easiest approach is to report directly from the Table. For example, If you wanted to report on Alyson, you could click on the Sales Person filter and choose Alyson from the list, giving you this result:

You could print her report, and then do the same for Peter.

However, you often won’t want to report directly from the Table. For example…

• You might need to include formulas to show information that adds to the data in the Table. (You add calculated columns to Tables, of course, but if those calculations require many lookups, a long Table can take a long time to recalculate.)
• You might need to report multiple rows of information in your report for every row in the Table.

In these cases, you would need to create a dynamic report that returns data from the Table, which is a much more interesting task.

Introducing Dynamic ‘Accordion’ Reports

The following figure shows three reports generated by the same worksheet. As you can see, the reports vary in length…expanding and contracting like an accordion. I call them accordion reports for this reason.

I displayed the first report automatically by choosing “*” from a list. I displayed the second by choosing “Alyson”, and the third by choosing “Peter”. All three reports get their data from the two Excel Tables above.

The following instructions show you how to build this report from scratch.

These reports also are dynamic because they change dynamically in response to the criteria you specify in one or more cells.

Taking the first peek under the hood, here’s what the report worksheet looks like with the wildcard selected as the CurSeller criteria:

Notice that…

• The Call Assignments Log  at the top of this page has six assignments. This report also has six assignments, but each assignment uses two rows.
• This report shows the Sales Person in column D, which won’t be true when a specific sales person is selected…as shown in the next figure below.
• Rows 18 and 19 contain formulas that allow for additional call assignments to be added. You could copy those two rows down the spreadsheet as far as you want.

Here’s the same report with Alyson selected:

With Alyson selected, the report structure automatically changes in two ways. First, the contents of column D disappear. Second, only Alyson’s assignments appear, leaving blank rows at the bottom of the report.

Let’s see how to create this magic…

Create the Log and Clients Tables

The first step is to create the Log Table in a new workbook. To do so, enter the dates and text shown here.

Then, to convert the simple table into an Excel Table in Excel 2007 or after, select the table and choose Insert, Tables, Table. In the Create Table dialog, make sure My table has headers is checked, and then choose OK.

By default, the first table is named Table1. To assign the name Log to this table, select any cell in the Table, then in Table Tools, Design, Properties, enter the name Log as the Table Name.

Similarly, we’ll need a Client Details table:

Assign the name Clients to this Table.

Set Up the Control Sheet

The second step is to set up the Control Sheet. To start, create a new worksheet in your report workbook and name it Control.

The figure at the right shows the entire contents of the Control sheet.

The TopRow cell returns the top row number of the Call Assignments Log. Here’s the formula for the cell shown:

B2:  =ROW(Log[#All])

To create this formula, I typed…
=row(
…then I selected the entire Log Table; I typed the closing parentheses; then I pressed Enter. Excel set up the Table reference as shown in the formula, even though I specified a large area. This is because Excel’s ROW function returns the top row when we specify a multi-row range.

The NumRows cell uses the ROWS function to return the number of rows of data currently in the Log Table. Here’s the formula for the cell shown:

B3:  =ROWS(Log[#All])-1

Assign the two names in column A as range names in column B. To do so…

1. Select the range A2:B3,
2. Choose Formulas, Defined Names, Create From Selection.
3. In the dialog, make sure that only Left column is checked, and then choose OK.

To set up the Sellers list, enter the text as shown and format it as you wish. Then select the range A5:A8, and launch the Create From Selection dialog again. This time, however, make sure only Top row is checked.

Create the Report

Start by adding a new worksheet to your report workbook. Name the sheet Report.

Now let’s set up the list box in cell B1, as shown in the report here (and repeated several times below):

With cell B1 selected, choose Data, Data Tools, Data Validation, Data Validation. Then, in the Data Validation dialog, choose List in the Allow listbox, and enter =Sellers as the Source. Then choose OK.

Now, the only data you can enter in cell B1 is the content of the Sellers list that you entered in the Control sheet.

Using Create Names, assign the range name CurSeller to cell B1.

To build the actual report, begin by entering the labels in the range F3:F4.

To center the labels over three columns as shown below, do NOT merge the cells! Instead, select the range F3:H4, choose the  for Alignment (shown by the red arrow below) to launch the Format Cells dialog with the Alignment tab displayed.

For the Horizontal Text Alignment setting in the dialog, choose Center Across Selection. Then choose OK.

Enter the labels in the range F5:H5, then enter the formulas and data for each column.

Column F contains sequence numbers that repeat for each group of rows in the report. Because each group of call-assignment information uses two rows, the same sequence number appears twice. The formulas in column H rely on these numbers.

Enter the values shown for cells F6 and F7. Then enter this formula…

F8:  =F6+1

…and copy it down the column as shown.

Column G counts each row in each group of rows. That is, the first row in a group is 1 and the second row is 2. The conditional formatting formulas rely on these numbers.

To enter these numbers, enter the values shown in cells G6 and G7, then copy the range G6:G7 down the column as shown.

Column H contains the most complex formulas in the report. The purpose of these numbers is to identify the rows in the Log Table that contain information specified by the CurSeller filter.

Here’s the formula for cell H6. I’ve broken it into seven lines so it’s easier to read and explain, but enter it in one line as you normally would.

LIne 1:    H6:  =IF($F6>NumRows,””,
LIne 2:            IFERROR(
LIne 3:            IF(CurSeller=”*”,$F6,
LIne 4:            AGGREGATE(15,6,(1/(Log[SalesPerson]=CurSeller))*
LIne 5:                ROW(Log[SalesPerson]),$F6)
LIne 6:            -TopRow),
LIne 7:            “”))

Line 1: If the sequence number in column F of the current row is greater than the number of rows in the log, return a null string (“”). Otherwise…

Line 2 & Line 7: If there’s an error in the following formula, return the null string in Line 7. Otherwise, return the contents of the following formula.

Line 3: If the user has selected the wildcard “*” in the CurSeller cell, we need to display this row of data. Therefore, display the sequence number in column F.

Lines 4 & 5: Use the AGGREGATE function to return the smallest row number that has data for Alyson (assuming her name is chosen).

When this formula is copied to cell H8, the $F6 will change to $F8, which has a value of 2. So in that cell, the AGGREGATE function will return the second-smallest row number that has data for Alyson. When it’s copied to H10, it will return the third-smallest, and so on.

In Line 4, the section (Log[SalesPerson]=CurSeller)) generates a temporary array of TRUE and FALSE values. When the array is divided into 1, it returns an array of values equal to 1 and #DIV/0! errors, which is just what we want in this case.

Then, when we multiply by the row numbers returned by Line 5, we get an array of row numbers and #DIV/0! errors.

Because the 6 in AGGREGATE’s second argument tells the function to ignore errors, and because the 15 in its first argument tells the function to use the SMALL function, AGGREGATE returns the smallest non-error row number (as specified by the value in cell F6), which is the first row where Alyson’s name is found.

(To better understand how the AGGREGATE formula works in this instance, study Examples 8 through 11 on the AGGREGATE function page.)

Line 6: The row number returned in Line 4 is the worksheet’s row number; but we need the row index number within the Log Table. To get that number, we subtract the row number returned by the TopRow cell in the Control sheet.

Copy the formula down column H as shown.

Create the Body of the Report

Here’s the report, repeated again from above:

With the three control columns in place, we now can return the text shown in the report. So enter the formulas as shown…

A3:  =”Call Assignments for “&IF(CurSeller=”*”,”All Sellers”,PROPER(CurSeller))

This formula returns the text of the report title.

The range A4:C5 contains the labels shown. Just enter them.

Cell D4 contains the formula:

D4:  =IF(CurSeller=”*”,”Sales Person”,””)

This formula returns a null string when the CurSeller criteria equals “*”.

In row 6, enter…

A6:  =IF($H6=””,””,INDEX(Log[Client],$H6))
B6:  =IF($H6=””,””,INDEX(Log[Date],$H6))
C6:  =IF($H6=””,””,INDEX(Log[CallType],$H6))

Column H serves as a switch. If the specified cell in this column (as identified by the INDEX function) contains a null string (“”), we want the worksheet to display nothing…neither numbers, nor text, nor formatting. These formulas return the Client’s name, Date, and Call Type when cell H6 isn’t empty.

D6:  =IF(OR(CurSeller<>”*”,$H6=””),””,INDEX(Log[SalesPerson],$H6))

Cell D6 adds one additional test. It returns a null string if the CurSeller is the wildcard or if cell H6 is empty; otherwise it returns the Sales Person.

The next three formulas return data from the Client Detail Table:

A7:  =IF($H6=””,””,INDEX(Clients[Company],MATCH($A6,Clients[Client],0)))
B7:  =IF($H6=””,””,INDEX(Clients[Phone],MATCH($A6,Clients[Client],0)))
C7:  =IF($H6=””,””,INDEX(Clients[PastSales],MATCH($A6,Clients[Client],0)))

The three formulas are similar. They grab the Client’s name in cell A6, look up the row number for the Client in the Client Details Table, and then respectively return the Company, Phone, and PastSales in that row.

After you enter the formulas in rows 6 and 7, copy the formulas downward to row 19.

Format Your Report

For easy reference, here’s the full report again:

Assign any fill colors and fonts you wish to rows 3 through 5.

Now let’s assign the conditional formatting, which makes the report work a lot like magic. As you assign the formats, keep in mind that the formulas in the report are handling the text. That is, if a row or column is supposed to be blank, the formulas in the affected cells already are displaying null strings.

This report uses four conditional formats…

Conditional Format 1

This format controls the second row in each group of two rows in the body of the report. Here’s how to assign it:

1. Select the range A6:D19, making sure that cell A6 is the active cell.
2. Choose Home, Styles, Conditional Formatting, New Rule.
3. In the New Formatting Rule dialog, choose Use formula to determine which cells to format.
4. In the edit box labeled Format values where this formula is true:, enter:
   =AND($G6=2,$H6<>””)
   (Note: The row number in this and the other conditional-format formulas below MUST be the same row number as the active cell.)
5. In the New Formatting Rule dialog, choose Format to launch the Format Cells dialog.
6. In the Border tab, specify a bottom border.
7. Choose OK until all dialogs have been dismissed.

Conditional Format 2

This format controls the alternating blue and white fill colors in the body of the report. Here are the steps:

1. Select the range A6:D19, making sure that cell A6 is the active cell.
2. Choose Home, Styles, Conditional Formatting, New Rule.
3. In the New Formatting Rule dialog, choose Use formula to determine which cells to format.
4. In the edit box labeled Format values where this formula is true:, enter:
   =AND(ISODD($F6),$H6<>””)
5. In the New Formatting Rule dialog, choose Format to launch the Format Cells dialog.
6. In the Fill tab, specify a light blue fill.
7. Choose OK until all dialogs have been dismissed.

Conditional Format 3

This format is one of two that hide the contents of column D:

1. Select the range D4:D5, making sure that cell D4 is the active cell.
2. Choose Home, Styles, Conditional Formatting, New Rule.
3. In the New Formatting Rule dialog, choose Use formula to determine which cells to format.
4. In the edit box labeled Format values where this formula is true:, enter:
   =CurSeller<>”*”
5. In the New Formatting Rule dialog, choose Format to launch the Format Cells dialog.
6. In the Fill tab, specify No Color.
7. In the Border tab, specify a black Left border.
8. Choose OK until all dialogs have been dismissed.

Conditional Format 4

This format is the other one that hides the contents of column D:

1. Select the range D6:D19, making sure that cell D6 is the active cell.
2. Choose Home, Styles, Conditional Formatting, New Rule.
3. In the New Formatting Rule dialog, choose Use formula to determine which cells to format.
4. In the edit box labeled Format values where this formula is true:, enter:
   =CurSeller<>”*”
5. In the New Formatting Rule dialog, choose Format to launch the Format Cells dialog.
6. In the Fill tab, specify No Color.
7. In the Border tab, specify no borders.
8. Choose OK until all dialogs have been dismissed.

You now should have a working accordion report.