You can raise your Excel productivity significantly and slash your Excel errors—if you keep your data in an Excel database.
Microsoft, Oracle, IBM, and others sell REAL database products. So what’s an Excel database?
If all those other database products were big-rig diesel trucks of various capacities and designs, Excel’s database would be a two-seat Jeep.
Big-rig databases can haul a massive amount of data down the information highway, which Excel can’t do. But Excel can deliver a relatively small payload nearly anywhere, quickly and inexpensively, which big-rig databases can’t do.
An Excel database is typically a spreadsheet with rows and columns of data—organized and formatted in a way that allows spreadsheet formulas to use the data easily. But it also could be a Power Pivot Data Model or a well-designed tabular range, as you’ll see shortly.
The following figures show you how Excel can use its various databases. But if you’ve not read the short article, An Introduction to Excel Plumbing, you might want to start there.
Excel Database 1: Excel Tables
Excel Tables are the most-frequently used Excel database. Strategically, here’s how they typically should fit into your productive reporting system:
That is, Power Query retrieves, shapes, and cleans your source data and writes it to Excel Tables. Then, formulas retrieve, transform, and summarize that data for your reports, analyses, and so on.
To illustrate, this figure illustrates a working example from my Recovery Tracker dashboard and training:
Here, the Source Data is the Federal Reserve Economic Database (FRED). Power Query downloads, shapes, and cleans the data, then writes it to two Excel Tables. As soon as the Tables are updated, the data flows through staging tables to 27 charts, four of which are shown here. (The red dotted line shows where each measure was in February 2020, just before the Covid recession struck.)
To see the full Recovery Tracker dashboard or other examples, just click the link or the figure above.
Excel Tables have at least four significant advantages for your Excel work.
1. They store your data in rows and columns, which allows SUMIFS, COUNTIFS, and similar functions to aggregate your data easily.
2. Tables are self-naming, a feature that makes your Excel formulas become somewhat self-documenting.
3. We can use the controls in a Table’s header area to sort or filter the data easily. Among other benefits, this makes it easier to debug your SUMIFS formulas.
4. We can set up calculated columns in Excel Tables.
This is why Excel Tables are Excel’s most commonly used database.
Excel Database 2: Pivot Tables
It might surprise you to learn that you also can use Pivot Tables as your database. And when you do so, you can use any Excel worksheet function you can use with Tables—without needing to use the GETPIVOTDATA function at all.
Here’s the plumbing for pivot data:
To use pivots like this, you must…
- Set up your pivot with a Tabular layout, as shown at the right.
- Use dynamic range names to reference the columns of your table.
My Excel productivity training explains this process.
Notice in the plumbing diagram that we can use pivots with Data Models.
The Power Pivot Data Model offers an alternate location to store data in our workbooks. One advantage for doing so is that—theoretically—there’s no limit to the number of rows of data we can store in a Data Model. Another advantage is that the Data Model compresses its data, so that it requires significantly less space in a saved file, and in memory.
However, Microsoft gives us only two ways to retrieve a Data Model’s data in Excel. We can use Pivot Tables, as the previous plumbing diagram shows, or we can use CUBE functions.
Excel Database 3: The Data Model with CUBE Functions
When we use the Power Pivot Data Model we can set up formulas to return our data to either Excel Tables or directly to our reports, analyses, and so on, as you can see here:
However, with the Data Model, we only can use formulas that rely on any of Excel’s seven CUBE functions: CUBEKPIMEMBER, CUBEMEMBER, CUBEMEMBERPROPERTY, CUBERANKEDMEMBER, CUBESET, CUBESETCOUNT, and CUBEVALUE.
That’s a challenge, because Excel’s CUBE functions are weird. They’re like no other function in Excel—which is why they’re challenging to learn. And that’s why I cover CUBE functions in my Excel Productivity training.