A growing number of Excel users in business are linking their reports and analyses to Excel tables. By doing so, they can update them in seconds rather than hours.
Users typically rely on two types of tables for their data: vertical tables and horizontal tables. Although each has its advantages, using two types of tables can create a challenge when you need data in one format but it’s currently in the other.
However, using three Excel worksheet functions will allow you to move data easily between these table formats.
A Vertical Excel Table
If you have Excel 2007 or above, and you work in a company with much data, you’ve probably seen Excel Tables that look something like this one.
(I’ve hidden most of the rows and/or columns in the middle of most of the tables on this page, which will allow you to see all four corners of the tables.)
I call this a Vertical Table because the dates are in a column, which is vertical, of course.
The general format of this table is typical of what you get with data that comes from a relational database. It’s got fields for dates, a key code, and an amount…each in long columns.
Notice that this table has no description for the Codes, and it has no additional information about each Code. This is because relational databases—and also Power Pivot for Excel—are much more efficient if their tables are tall and skinny like this.
A Vertical Dimension Lookup Table
To get the additional information for the Codes, you must pull in data from a dimension lookup table, somewhat like this one.
This table shows two kinds of information about each Code. First, it shows the description. (The descriptions for your own data will be more informative, of course.)
Second, this table has a Multiplication (“Mult”) column, which typically is used in two ways.
First, some external data—like economic and stock market data—comes in units of thousands or millions. Therefore, if you intend to use this data internally, it’s always a good idea to convert it to ones in your reports. Here, the source data for Code C009 always is in thousands, so multiplying it by 1,000 in your reports will convert the data into ones.
Second, if the data contains debits and credits—with debits as positive numbers and credits as negative numbers—it’s often useful to multiply the values for each General Ledger account by its natural sign. That is, you multiply accounts that should be debits by 1 and accounts that should be credits by -1. When you do so, every account with its natural sign becomes a positive number, which often makes the accounts easier to use in reports.
Of course, data exports from relational files often include descriptions and other dimensional data. But this isn’t always a good thing. First, because many descriptions could be repeated many times, your workbook becomes much larger than it otherwise would be.
Second, this data often can be incorrect, with misspelled descriptions, strange abbreviations, unusual upper- and lower-case text, and out-of-date information. And third, some dimensional data you need probably won’t be included, like the Multiplication column shown above.
Therefore, even if you get information about codes and other key items with the data you download from your relational database, it’s often a good idea to maintain your own dimensional lookup tables like the one above.
A Horizontal “Gray-Cell” Table
Before Microsoft introduced Excel Tables, gray-cell tables like this were all we could use in Excel.
This table contains the same data as the top two tables. You can confirm this by comparing its top-left and bottom-right numbers with the top and bottom numbers in the first table.
This table’s design offers several advantages over a vertical table. First, the design combines the data from both vertical tables above into one horizontal table. So it’s often easier to set up and maintain.
Second, if you need to enter data manually, or copy and paste it from some other source, this layout often is easier to manage than is a relational-style table like the top Excel Table.
However, if your source data comes from a relational-style CSV file, or a data import, updating this table can take extra work.
How to Set Up Formulas to Populate a Gray-Cell Database from an Excel Table
Suppose that each month you download data that looks something like the blue table at the left below, and you want to use this data to populate the gray table at the right. More specifically, suppose you want to set up the data for Dec-2012, as shown in these tables.
Here are the steps:
1. Insert a new column in the gray-cell table to contain the new month’s data. Here, for example, I inserted the column BA to contain the data for December, 2012.
2. Copy the date cell from the preceding column to the new column and then update the date as needed. Here, for example, I copied cell AZ2 to cell BA2, and then changed the date to 12/1/2012.
3. Add the new month’s data to the bottom of the blue source table, which is in a different worksheet but in the same workbook as the gray data table. Typically, you’ll just copy the data from a data import or from a CSV file that you’ve opened in Excel, and paste in the first empty row below the bottom of the blue Table.
When you paste the data, the Table should expand automatically to include the new data. If it doesn’t, select any cell in the Table, then choose Data Tools, Design, Properties, Resize Table, and then, in the Resize Table dialog, specify the new bottom row for your table.
4. You’ll use the SUMIFS function to retrieve numeric data from the Table. If you’re not familiar with this function, you might take a minute to follow the link and read up on it. When you do so, you’ll find that this is the syntax for the SUMIFS function:
SUMIFS(sum_range, criteria_range, criteria, …)
- sum_range Required. One or more cells to sum in a row or column, including numbers, range names, or cell references that contain numbers. Blank and text values are ignored.
- criteria_range Required. The first range in which to evaluate the associated criteria. Error values in the criteria_range are ignored.
- criteria Required. The criteria in the form of a number, expression, cell reference, or text that define which cells in the Criteria_range1 argument will be added. For example, criteria can be expressed as 99, “>99”, B4, “sales”, or “99”.
- … Optional. Repeated pairs of criteria_range and criteria arguments, to a total of 127 pairs.
5. Enter the formula in the cell shown for the formula below:
BA4: =SUMIFS(TableAct[Amount], TableAct[Codes], $B4, TableAct[Date], BA$2)
Here…
- sum_range is TableAct[Amount]. TableAct is what I named my blue Excel Table, and Amount is the name of the column in that table where I’ll find the numbers I need. That is, I want the SUMIFS function to return data from the table’s Amount column.
- criteria_range1 is TableAct[Codes]. This is the first criteria column in my Table that I decided to use.
- criteria1 is $B4, which has a value of C001. That is, in this row I wanted the SUMIFS function to return data only where “Code C001” is found in TableAct[Codes].
- criteria_range2 is TableAct[Date].
- criteria2 is BA$2, which has a value of 12/1/2012. That is, in this column I wanted the SUMIFS function to return data only where 12/1/2012 is found in TableAct[Date].
5. Copy this formula down the column as shown in the figure above.
Of course, if you’re updating an existing table, you probably would just copy the column of formulas from an adjacent column.
How to Set Up Formulas to Populate a Gray-Cell Database with Dimension Data
The previous set of formulas updated the numbers in the gray-cell database. But we obviously can’t use sum formulas to populate the description field in the gray-cell table here.
Instead, we use the INDEX and MATCH functions.
(In this particular example, we also could use the VLOOKUP function. But because INDEX-MATCH is much more flexible than VLOOKUP, and often faster, I’ve never used VLOOKUP for real work.)
Suppose you have the gray-cell table shown above and you want to enter a formula in cell C4 to return the correct description from the blue dimension table at the left. Enter this formula for the cell shown:
C4: =INDEX(TableDim[Desc], MATCH($B4, TableDim[Code], 0))
Here, the INDEX function returns data from the Desc column of the blue TableDim table shown in the figure above. The MATCH function specifies which row-index number the INDEX function should return.
To calculate the correct row-index number, MATCH looks for the value entered in cell B4 (which is “C001”) in the Code field of the TableDim table. Because the third argument is zero, the data doesn’t need to be sorted, and MATCH will return #N/A if “C001” isn’t found. But as it turns out, C001 is the first item found, so MATCH returns the value 1.
Therefore, INDEX returns the first description found in the Desc column of the blue table: “C001 Act Desc”.
A somewhat similar formula returns the Mult value:
D4: =IF(INDEX(TableDim[Mult], MATCH($B4, TableDim[Code], 0))=0,””,
INDEX(TableDim[Mult], MATCH($B4, TableDim[Code], 0)))
(Although this formula is shown on two lines, you enter it in one line, of course.)
In this formula, the first INDEX-MATCH section returns the Mult value for row 4. If that value equals zero, as it usually does, the formula returns a null string; otherwise, the formula returns the non-zero Mult value.
How to Set Up Formulas to Populate an Excel Table from a Gray-Cell Table
We also can use SUMIFS to go in the other direction. That is, we can use it to populate an Excel Table from a Gray-Cell Database.
To set up the blue table for the first time, we first need to set up the values for the Date and Codes columns. Doing so takes longer to explain than it takes to set up the formula in column C.
To set up the Date and Codes values…
1. In a new worksheet, enter the three column headings shown in row 2 of the blue table at the right above.
2. Copy the area with the 29 Codes from the gray-cell table and paste the area to cell B3 in the blue table.
3. Enter the date 1/1/2009 in cell A3, and copy it down the column as far as the 29 Codes extend.
4. Because the gray-cell database has 48 months of data and there are 29 Codes for each month, the blue table will have 1392 (48 x 29) rows of data. Therefore, set up a temporary column of numbers in column C as a guide, a column that counts from 1 to 1392.
To do so, enter the value 1 in cell C3, then press Ctrl+Shift+Down-Arrow to select all cells from C3 to the bottom of your spreadsheet. Now choose, Home, Editing, Fill, Series. In the Series dialog, specify a Stop Value of 1392, and then press OK…which gives you the column of numbers to use as a guide.
5. Copy and paste the 29 Codes in column B to the first cell below the last Code. This should fill the range B32:B60.
6. In the first cell below the last Jan-2009 in the table, enter the formula for the cell shown…
A32: =DATE(YEAR(A3),MONTH(A3)+1,1)
This formula returns February, 2009. Copy the formula down its column to the range A33:A60…ending adjacent to the Codes you just pasted.
7. Copy the range A32:B60 and then paste it parallel to the column of counters you set up. Here’s an easy way to do this:
- After pressing Ctrl+C to copy the range, select one of the counter cells in column C.
- Press Ctrl+Down-Arrow to “slide” to the bottom of that column of numbers.
- Press the Left-Arrow key twice to move to column A in that bottom row.
- Press Ctrl+Shift+Up-Arrow to select all cells above that active cell to the last row of formulas in column A.
- Hold down only the Shift key and then press the Down-Arrow key once, so that only empty cells are selected.
- Press Ctrl+V to paste to the area A61:B1394.
8. Change the column of date values and date formulas to date values. To do so, select the entire column of dates; press Ctrl+C to copy them; press Ctrl+Alt+V to launch the Paste Special dialog; choose Values and then press OK.
Now you can enter the formula that returns the appropriate value from the gray-cell database. Here, we use an INDEX-MATCH-MATCH formula for the cell shown:
A3: =INDEX(eg.ActData, MATCH($B3, eg.ActCodes, 0), MATCH($A3, eg.ActDates, 0))
This formula returns data from the database that was set up for the Kyd War Room dashboard templates. That database uses the range names eg.ActCodes, eg.ActCodes, and eg.ActDates to specify the areas that the Data, Codes, and Dates can be found.
The INDEX formula returns the value in the eg.ActData range as specified by two MATCH functions. The first MATCH function returns the row-index number for the specified Code value, and the second MATCH function returns the column-index number for the specified Date value.
Finally, now that the ordinary table is complete, you can change it into an Excel Table. To do so, select any cell in the table, then choose Insert, Tables, Table; in the Create Table dialog, make sure that My table has headers is checked; then choose OK.
How to Check Your Work
The first time I set up these tables and formulas I “round-tripped” them. That is, I started with the gray-cell table, set up the blue Excel Table with formulas linked to the gray-cell table, then I set up a second gray-cell table with formulas linked to the blue Excel Table.
Finally I set up a reconciliation table that compared both beginning and ending gray-cell tables. By doing so, I made sure that all formulas worked in both directions.
