Managing Excel Data
Introducing Excel’s Three Types of Spreadsheet Databases
Excel offers at least three ways to set up data so your reports
and analyses can use it easily as a reliable data
source.
by Charley Kyd, MBA Microsoft Excel MVP, 20052014
The Father of Spreadsheet Dashboard Reports

Excel offers three general ways to arrange data in your
spreadsheet so you can use it as a database with your worksheet
formulas:
 Simple (or "Gray Cell") Tables, which I've used since Excel 2.0.
 Excel Tables, introduced in Excel 2007.
 PivotTables with a Tabular Report Layout, introduced in Excel
2010.
Database experts likely would be offended by my calling any
of these a "database." After all, these three spreadsheet
databases are easy to set up and use...not at all like a "real"
database. Even so, these databases work like a real database for
your Excel formulas. If your reports and analyses can get their
data from one of these types of spreadsheet databases, you can
improve your reporting and analyses significantly.
In this article, I'll introduce each type of spreadsheet
database. Then in future posts, I'll go into greater detail.
Spreadsheet Database 1: Simple Tables
For more than 20 years, until Excel 2007, Excel users could
use only Simple Tables as a spreadsheet database. This type of
database can come in two styles: horizontal and vertical.
SimpleTable Spreadsheet Databases, Horizontal Style
Horizontal spreadsheet databases have their dates in one
title row, as shown in the figure below.
Typically, the date columns use monthly "buckets," but they
could use any time period required. And the Codes column could
contain General Ledger account numbers, SKUs, employee codes,
whatever.
To illustrate the power this database offers, check out my Excel
dashboard templates, which get their data from this type of
Excel database. To update your report each period in those
templates, you just update the database, change the report date
in one cell, then recalculate Excel. If you rely on Excel
databases for your other reports and analyses, all your reports
could update just as easily.
The gray rows and columns are the most obvious feature of
Simple Tables. Those border cells serve a critical purpose.
Here's how:
When you set up a Simple Table, you define range names for
all key ranges, and then your formulas use only those range
names to reference the table—never cell references. And those
range names all reference only the gray border cells—never cells
with data.
To illustrate, this database is on a worksheet named Horiz, and
it uses these four range names:
 Codes =Horiz!$A$3:$A$10
 Desc =Horiz!$B$3:$B$10
 Dates =Horiz!$C$2:$G$2
 Data =Horiz!$C$3:$G$10
Here's the critical requirement for using this design: When
you add rows or columns of data to a Simple Table, you always
insert the rows and columns between the gray cells. That way,
you always know that all formulas that use the table will "see"
your new data.
SimpleTable Spreadsheet Databases, Vertical Style
This figure shows a vertical SimpleTable Excel database.
Like most databases, each field consists of only one column of
data.
As a shorthand description, I call this a vertical database
because its dates are shown vertically.
Here, the names are easy to assign. Just select the range
A2:D12. And then...
 In Classic Excel, choose Insert, Name, Create...
 In New Excel, choose Formulas, Defined Names, Create
From Selection...
...and then, in the Create Names dialog, ensure that only Top
Row is specified; then choose OK.
Spreadsheet Database 2: Excel Tables
Excel 2007 introduced a major new feature: Excel Tables.
When you set up an Excel Table (or just "Table") Excel names
each column of data automatically. Also, when you add adjacent
rows or columns to a Table, Excel expands the Table to include
this new data, also automatically.
This is in contrast to Simple Tables, where we must take the
manual steps needed to add border cells and define range names.
To create this Table, set up data arranged as you see in this
example; select the headers and data; choose Insert, Tables,
Table; in the Create Table dialog, ensure that "My table has
headers" is checked; then choose OK.
Excel automatically formats the table and names it, much as
it names new worksheets. That is, it assigns names like Table1,
Table2, etc. And like worksheets, you can rename each Table.
In formulas, when you reference a column of data that's in
either a Table or a Simple Table, you use names, not cell
references. For example, if you wanted to sum the Amount column,
your formulas would be:
 Simple Table: =SUM(Amount)
 Excel Table: =SUM(Table1[Amount])
Of course, if you rename Table1, the reference to Table1 in
the second formula would update automatically.
Spreadsheet Database 3: A Tabular Layout for Pivot Tables
Pivot Tables offer several significant benefits, which is why
they're so popular. But they also have significant limitations,
which is why I seldom have used them in the past.
The benefits are obvious. Pivot Tables offer a powerful ability
for Excel users to explore relational data in Excel and to
return sorted, summarized, and filtered slices of the data to
spreadsheets. I don't know of any other product that offers such
power.
On the other hand, from my perspective, Pivot Tables have
always seemed to be merely a report generator bolted to Excel.
They offer many reporting capabilities, but only one spreadsheet
function—GETPIVOTDATA—to allow worksheet functions to use
PivotTable data. Therefore, Excel users—again in my opinion—have
always had to work much harder than we should to use data from
one or more Pivot Tables in standard Excel reports.
But finally, in Excel 2010, Microsoft added most of the
features Excel users need to use Pivot Tables as a truly useful
source of data for standard reporting and analysis. Because we
can work around the missing features, we finally can use a
collection of Pivot Tables as a powerful and massive spreadsheet
database.
Take Your Next Steps
First, in
How to Set Up a Pivot Table as a Spreadsheet Database
I explain the next development step.
Second, 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.
