How to Report and Analyze Variable-Length Databases in Excel

Andrew Bates left a comment in Set Up Range Names to Connect Reports to Excel Databases, Part 1, asking how to report and analyze variable-length data. The problem he describes is really common. I used to have it a lot when I worked with data exported as text files and imported into Excel. But once I worked out a solution, it saved me a lot of time.

Andrew mentioned that he uses tables in Excel 2007. But the following solution works with all versions of Excel:

Here’s a database that roughly illustrates what Andrew’s asking. He would like to set up formulas that can retrieve data from this database easily.

Row 4 contains text that I assigned as range names in row 5.

Row 5 is very important. It’s a border row that contains key range names, and it marks the last row before the rows of data begin.

To assign the text in row 4 as names in row 5, select the range A4:C5, and…

• In New Excel, choose Formulas, Defined Names, Create From Selection.
• In Classic Excel, choose Insert, Name, Create.

Then in the dialog, make sure that only Top Row is checked and then choose OK.

Next, add a few rows of data, as shown in the figure.

Enter the NumRows text in cell A2, and assign the name NumRows to cell B2. Then enter this formula for the cell shown:

B2:   =COUNT(C:C)

You might need to vary this formula somewhat, depending on your data. You need to make sure that the formula doesn’t refer to its own column. And the formula needs to count data in a column that never will have blanks within the rows of data. Remember that COUNT counts only numeric data, including dates, and that COUNTA counts both text and numbers.

Now here comes the magic:

Define range names that use what we’ve done so far to reference only the rows with data in each of the three columns. Here are the names you define:

• aaCustomer
  =OFFSET(Customer,1,0,NumRows)
• aaSales
  =OFFSET(Sales,1,0,NumRows)
• aaDate
  =OFFSET(Date,1,0,NumRows)

I recently started to designate dynamic range names like these with an initial “aa”. This groups dynamic names together in the sorted list of names, which makes them easier to manage. If also helps me to use them in formulas. To illustrate, I can quickly check that the name “Customer” was assigned to cell A5; therefore, the name of the column of data beneath that cell is “aaCustomer”.

In the definition of each name, the OFFSET function uses these arguments to return a reference:

=OFFSET(reference,rows,cols,height,width)

To illustrate, the aaCustomer name returns a reference that’s offset from the Customer-name reference by one row and zero columns. And it has the height specified by NumRows. Because we didn’t specify a width, the reference has the width of Customer, which is one column. (Even though it’s not necessary in this case, just to be safe I usually would specify a width of 1. I didn’t do so in this case to illustrate that the height and width arguments are optional.)

To test any of these names, aaDate for example, press F5, type in aaDate as the reference, then press Enter. If you have three rows of data, Excel should select the first three rows below the gray cell named Date.

Now you can use simple formulas to learn information about this data:

• The number of customers is equal to the value of NumRows.
• The sum of YTD sales is =SUM(aaSales).
• The count of customers within the past 12 months is:
  =COUNTIF(aaDate,”>”&DATE(YEAR(NOW())-1,MONTH(NOW()),DAY(NOW())))

If you have New Excel, the functions COUNTIFS and SUMIFS allow you to use multiple criteria. If you have Classic Excel, SUMPRODUCT provides similar capabilities. My article Use Excel’s SUMPRODUCT To Summarize Worksheet Data explains the process. Because COUNTIFS and SUMIFS support only AND criteria, if you’ve got New Excel you’ll need to use SUMPRODUCT to specify an OR criteria.

Tagged as: Excel databases, Excel range names, External references