Managing Data
How to Read and Update Excel Tables Using SUMIFS and INDEXMATCH
With a few Excel formulas, you can move data between Excel's two
most widely used table designs...and automatically transfer data
from your source files into a more useable form.
by Charley Kyd, MBA Microsoft Excel MVP, 20052014
The Father of Spreadsheet Dashboard Reports

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
lowercase text, and outofdate
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 "GrayCell" Table
My
Kyd War Room dashboard templates use this horizontal
table. Because of the gray row and column borders, I call
it a graycell table.
This table contains the same data
as the top two tables. You can confirm this by comparing its
topleft and bottomright 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 relationalstyle table like the top Excel Table.
However, if your source data comes from a relationalstyle
CSV file, or a data import, updating this table can take
extra work.
Where Should Your Tables Reside?
If you have
Kyd War Room, I recommend that you make a copy of the
Dash_Data_Act.xlsx workbook for use in the following
discussion. If you like the results, you can swap out the new
version for the old.
By taking this approach, your dashboards and other reports still
can return data from the graycell database, which could get its
data using formulas linked to an Excel Table...a table that
you update
each month with a quick copy and paste.
How to Set Up Formulas to Populate a GrayCell 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 Dec2012, as shown in
these tables.
Here are the steps:
1. Insert a new column in the graycell 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 GrayCell Database with
Dimension Data
The
previous set of formulas updated the numbers in the graycell
database. But we obviously can't use sum formulas to populate the
description field in the graycell table here.
Instead, we use the
INDEX
and MATCH
functions.
(In this particular example, we also could use the
VLOOKUP
function. But because INDEXMATCH is much more flexible than
VLOOKUP, and often faster, I've never used VLOOKUP for real
work.)
Suppose you have the graycell 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 rowindex number the INDEX function
should return.
To calculate the correct rowindex 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 INDEXMATCH 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 nonzero Mult value.
How to Set Up Formulas to Populate an Excel Table from a
GrayCell 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 GrayCell
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 graycell 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 graycell 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+DownArrow 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 Jan2009 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+DownArrow to "slide" to the bottom of that
column of numbers.
 Press the LeftArrow key twice to move to column A in
that bottom row.
 Press Ctrl+Shift+UpArrow 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 DownArrow 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 graycell database. Here, we use an
INDEXMATCHMATCH 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
rowindex number for the specified Code value, and the
second MATCH function returns the columnindex 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
"roundtripped" them. That is, I started with the graycell
table, set up the blue Excel Table with formulas linked to the
graycell table, then I set up a second graycell table with
formulas linked to the blue Excel Table.
Finally I set up a reconciliation table that compared both
beginning and ending graycell tables. By doing so, I made sure
that all formulas worked in both directions.
Take Your Next Steps
If you're looking for additional help with
this topic, I can help you in three ways. To learn
more, see
Excel Training, Coaching, and Consulting.
Tags: #excel, #powerpivot, #Power Pivot, #SUMIFS, #INDEXMATCH, #INDEX, #MATCH, #dashboard, #database,
#Excel Table, #graycell table, #tables
