For business users of Microsoft Excel Free guides and templates

Managing Excel Data

What the Heck is an Excel Database?

Excel databases are simple lists or tables of data that your reports and analyses can use easily. This introduction will get you started.


by Charley Kyd, MBA
Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports

You can improve your Excel reports and analyses significantly if you keep your data in an Excel database.

Microsoft, Oracle, IBM, and others sell REAL database products. So where does an Excel database fit in this group?

Big-Rig Trucks vs Two-Seat Jeeps

If all those other database products were big-rig diesel trucks of various capacities, Excel’s database would be a two-seat Army Jeep.

Big-rig databases can haul a massive amount of data down the information highway, which is something that Excel never could do. But Excel can deliver a relatively small payload nearly anywhere, quickly and inexpensively. This is something those big-rig databases can’t do.

An Excel database is merely a spreadsheet with rows and columns of data, organized and formatted in a way that spreadsheet formulas can use the data easily.

Excel databases can have two orientations.

Horizontal Excel Databases

Horizontal databases have periodic dates in one row of a spreadsheet, as shown here. The periods typically are months, but they could be any period you want.

This type of database typically has a single key field in a column, a field that could contain GL codes, employee numbers, product codes, SKUs, stock ticker symbols, country codes, and so on. Adjoining columns could contain descriptions and other useful information.

Each worksheet in a horizontal database contains one number for any specific date and code in a key field. So you might have one file for Actual sales performance by product code, another for Target financial performance by GL code, another for weekly unique visitors by URL, and so on.

This structure is much like an OLAP database, but it has only two dimensions, not dozens. If you use any of my dashboard products, you’ll recognize this database design.

Typically, you'll use INDEX-MATCH formulas to return data from horizontal databases, but if you need to select among several types of codes—like General Ledger Account and Department and Region—you'll probably need to use SUMIFS instead.

Vertical Excel Databases

Vertical databases resemble relational files. They have dates in one column of a spreadsheet, and the dates don’t need to be periodic. Other columns in the database can contain descriptions, codes, values, and so on.

I typically use two types of vertical databases. This figure shows one type, a simple (or "gray cell") database. The other type is an Excel Table, a technology that was introduced in Excel 2007.

Vertical databases weren’t practical in early versions of Excel, because Excel had so few rows to work with in a spreadsheet. But because New Excel can contain more than a million rows of data, vertical databases now are practical.

To retrieve data from a vertical database, you'll typically use the SUMIFS function or the SUMPRODUCT function. SUMPRODUCT is more powerful than SUMIFS, but SUMIFS calculates more quickly.

The Related area at the top of this page provides links to more information about Excel databases.

 

Introducing Excel’s Three Types of Spreadsheet Databases

How to Set Up a Pivot Table as a Spreadsheet Database

The Two Functions You MUST Know to Return Values from Excel Tables and Databases

Free Excel Dashboards


Charley's SwipeFile charts