Excel as Database

Excel is a spreadsheet application but can also be a very interesting database. In fact Excel is the cheapest and the simplest database program on the market and you don't need any special complex training to work with it. But there are two limits to using Excel as a database.

The first one is that only one person at a time can enter data into the database.

The second limit is a matter of quantity of data. Each sheet in an Excel workbook comprises 65,000 rows (1,000,000 rows in Excel 2007) and 256 columns. An Excel workbook can comprise up to  256 sheet. We are talking about 4,292,608,000 cells.

These limits are  not a factor for most small enterprises where a single person is responsible for the database. It is also not a factor for departmental databases in large enterprises. Very valuable data at the department level are  not stored in the centralized mega database because they have no " corporate" significance but a departmental personal database can be created and the data used for very critical decisions.

Excel needs to recognize your set of data as an Excel database or you will not have access to any of the database functionalities from the " Data" menu item (Sort, Filter, Form, Subtotal, Pivot Table).

So anybody can develop a database in Excel. You don't need to develop complex forms to enter data, you don't need to develop queries you just use the filters to analyse and SUMPRODUCT, INDEX/MATCH and SUBTOTAL formulas to develop by yourself reports that have the layout that you need when you need them.

As for the analysis and the reports whatever the database you NEED Excel.  A lot of my clients have large centralized databases (EssBase, Oracle, SAP, Sybase, SQL Server...) or large centralized accounting, financial or manufacturing applications (JDE, SAP, Oracle, PeopleSoft, SmartStream...) but the data is analyzed and the reports are developed using Excel. They become really efficient in analyzing and reporting when their employees learn about SUMPRODUCT and INDEX/MATCH formulas. They adopt this approach because Excel is the most user friendly analyzing and reporting application so they don't need to train a few analysts on other reporting applications creating a bottleneck at the report development level. Adopting Excel also allows all the analyst and decision makers to develop significant analysis and reports improving the bottom line of the whole corporation.

Learn more on Excel
See the list of small downloads

There are  five sections in this website

Section 1: About Databases: 4 lessons
In this section we review the basic notions on databases and evaluate Excel as a database. You will then see how Excel can be used downstream from most centralized databases and data management programs. Finally we will discuss the "upgrade" from Excel to Access..
Section 2: Excel and External Data: 5 Lessons
There are many ways to get data into your Excel Workbooks and there are many ways to easily extract data from all the databases in your organization and the Internet. This section is centered on querying data
Section 3: Analyzing Data with Excel: 8 lessons
Once you have the data in the right formats within your Excel workbook it is time to use the powerful analytical tools of Excel including the database functionalities (sort, filter, subtotals), the integrated calculator, the comparative analysis tools and the mysterious and not so complicated pivot tables. 
Section 4: Reporting with Excel: 7 lessons
The report is a communication tool and you can not accept to compromise on its presentation. Excel is the best reporting tool allowing you to easily develop popular dashboards and to share these reports with multiple colleagues.
Section 5: Other Functionalities in Excel: 3 lessons
In this fifth section you will review the useful CTRL and ALT keystrokes, the conditional formatting, the drop-down lists and other interesting functionalities.