The most productive way to flow data to your Excel reports and analyses is to use Excel formulas to flow data from an Excel-Friendly Database.
An Excel-Friendly Database is an organized collection of data from which worksheet formulas can return values. Here are five Excel-friendly databases.
To my knowledge, five types of Excel-Friendly Databases (EFDs) exist:
- Gray Cell Tables, which I’ve used since Excel 2.0.
- Excel Tables, introduced in Excel 2007.
- Pivot Tables with the Tabular Report Layout, which was introduced in Excel 2010.
- The Power Pivot Data Model, which is the least Excel-friendly, introduced in Excel 2010.
- Excel-Friendly OLAP Products, which are offered by third parties.
Database experts likely would object to my calling the first four of these databases. However, from an Excel perspective, the Wikipedia definition is good enough: “A database is an organized collection of data, stored and accessed electronically.”
If your reports and analyses can get their data from one of these types of EFDs, you can improve your Excel productivity significantly.
This article will describe the first four of these EFDs. (I’ll leave OLAP for another time.) This article is the second of a four-part series. The other three articles are:
- The Excel-Friendly Database Strategy
- How to Set Up a Pivot Table as an Excel-Friendly Database
- Two Functions You MUST Know to Return Values from Excel Tables
Workbook Database 1: Gray Cell Tables
For more than 20 years, until Excel 2007, Excel users could use only Gray Cell Tables as a workbook database. This type of database can come in two styles: horizontal and vertical.
Gray-Cell-Table Workbook Databases, Horizontal Style
Horizontal workbook databases have their dates in one title row, as shown in the figure below. This structure often is used for budgeting, because it’s easiest for people to fill in and it’s easy for Power Query to consolidate.
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 update your report each period, you just update the database, change the report date in one cell of your report, 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 Gray Cell Tables. Those border cells serve a critical purpose. Here’s how:
When you set up a Gray Cell 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 Data, and it uses these four range names:
- Codes =Data!$A$3:$A$10
- Desc =Data!$A$3:$A$10
- Dates =Data!$C$2:$G$2
- Data =Data!$C$3:$G$10
Here’s the critical requirement for using this design: When you add rows or columns of data to a Gray Cell 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.
Gray-Cell-Table Workbook Databases, Vertical Style
This figure shows a vertical Gray-Cell-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 the Formulas, Defined Names group, choose Create From Selection. In the Create Names dialog, ensure that only Top Row is specified; and then choose OK.
Workbook Database 2: Excel Tables
Excel 2007 introduced a major new feature: Excel Tables. And in Excel 2010, Microsoft introduced Power Query (named Get & Transform Data in Excel 2016), which can import data to Tables from a massive number of sources.
One advantage of Power Query (PQ) is that it can transform data before loading it into a Table. So, for example, you could set up horizontal Gray Cell tables to use as budget forms and then use Power Query to import the data from all budget workbooks into one Table with one command.
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.
However, because those Table names have certain limitations, it’s often a good idea to assign and use standard range names to your Tables.
To create this Table, set up data arranged as you see in this example; select the headers and data; in the Insert, Tables group, choose Table; in the Create Table dialog, ensure that My table has headers is checked; and 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 Gray Cell Table, you use names, not cell references. For example, if you wanted to sum the Amount column, your formulas would be:
- Gray Cell 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.
Workbook 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 for reporting and analysis, 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 worksheets.
On the other hand, Pivot Tables have always seemed to be merely a report generator bolted to Excel. They offer many reporting capabilities, but only one worksheet function—GETPIVOTDATA—to allow worksheet formulas 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 and analyses.
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 workbook database.
In How to Set Up a Pivot Table as an Excel-Friendly Database, I explain how to set up Pivot Tables as a workbook database.
Workbook Database 4: Power Pivot’s Data Model
Power Pivot brings a version of Analysis Services to Excel. Power Pivot’s single database in every workbook is called the Data Model.
Power Pivot brings both advantages and disadvantages to Excel. The advantages are:
High Capacity. Microsoft tells us that the 32-bit version of Excel can contain 500 – 700 megabytes, and that the 64-bit version is limited only by computer memory. The Data Model can contain a maximum of 2,147,483,647 Data Tables, and a table can contain a maximum of 1,999,999,997 rows.
Relational Abilities. Power Pivot essentially brings a relational data source to Excel. You can define relationships among the tables for reporting and analysis.
However, the primary disadvantage is a big one: Power Pivot gives you only limited access to your data. In Excel, you can access your data only by using Pivot Tables or CUBE formulas—and CUBE formulas are weird!
In Excel, Power Query (Get & Transform Data) can NOT query your Data Model.
Branching out from Excel, Power BI’s version of Power Query can import a copy of your Data Model. And you can upload your workbook to SharePoint.
There is one partial Excel solution to Power Pivot’s stingy access to your data. You can set up Pivot Tables that can work much like Excel Tables—as I explained in the previous section. With this approach, you can use a wide variety of worksheet functions to return data from your pivots to your reports and analyses.
In How to Set Up a Pivot Table as an Excel-Friendly Database, I explain how to set up Pivot Tables as a workbook database.
