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.
Simple-Table 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.
The gray rows and columns are the most obvious feature of Simple Tables. Those border cells serve a critical purpose.
Here’s why the gray rows are necessary:
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.
Simple-Table Spreadsheet Databases, Vertical Style
This figure shows a vertical Simple-Table 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
In How to Set Up a Pivot Table as a Spreadsheet Database I explain the next development step.
