When you set up an Excel Table (or just “Table”) Excel names each column of data automatically. Spreadsheet Database 2: Excel TablesĮxcel 2007 introduced a major new feature: Excel Tables. …and then, in the Create Names dialog, ensure that only Top Row is specified then choose OK. In New Excel, choose Formulas, Defined Names, Create From Selection….In Classic Excel, choose Insert, Name, Create….Like most databases, each field consists of only one column of data.Īs a shorthand description, I call this a vertical database because its dates are shown vertically. This figure shows a vertical Simple-Table Excel database. Simple-Table Spreadsheet Databases, Vertical Style That way, you always know that all formulas that use the table will “see” your new data. 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. To illustrate, this database is on a worksheet named Horiz, and it uses these four range names: And those range names all reference only the gray border cells-never cells with data. 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. Then you always insert new data between those rows and columns. With a Gray-Cell Database, you anchor your range names and formulas in the gray rows and columns. Those border cells serve a critical purpose. The gray rows and columns are the most obvious feature of Simple Tables. And the Codes column could contain General Ledger account numbers, SKUs, employee codes, whatever. Typically, the date columns use monthly “buckets,” but they could use any time period required. Horizontal spreadsheet databases have their dates in one title row, as shown in the figure below. Simple-Table Spreadsheet Databases, Horizontal Style This type of database can come in two styles: horizontal and vertical. Spreadsheet Database 1: Simple Tablesįor more than 20 years, until Excel 2007, Excel users could use only Simple Tables as a spreadsheet database.

Then in future posts, I’ll go into greater detail. In this article, I’ll introduce each type of spreadsheet database. 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. Even so, these databases work like a real database for your Excel formulas. PivotTables with a Tabular Report Layout, introduced in Excel 2010.ĭatabase 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.Excel Tables, introduced in Excel 2007.Simple (or “Gray Cell”) Tables, which I’ve used since Excel 2.0.Excel offers three general ways to arrange data in your spreadsheet so you can use it as a database with your worksheet formulas:

Xel 3 used