Excel as a Database

Excel is a spreadsheet application but can also be a very interesting database application. In fact, Excel is the cheapest and the simplest database application 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 and the most important limit is that only one person at a time can enter data into the database although many users can access simultaneously an Excel database residing on a local network or on the Internet in "read only" mode. They can read or extract data to develop analysis and reports.

The second limit is a matter of quantity of data. An Excel workbook can comprise up to 256 sheets each having 256 columns and 65,500 rows that can contain data and formulas. 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 information at the department level is not stored in the centralized mega database because it has no "corporate" significance, a departmental database can be created and the data used for very critical decisions.

Because Excel databases are managed by a single user, you don't really need to create forms to enter new data in the database. The database manager can enter data directly in the database using the "Tab" key to navigate sideways. If you really want a form, just click anywhere within the database then go to the menu bar "Data/Form" and an automatic form allows you to populate the database.

To analyze the data, no need for queries, you can sort and filter the data using the powerful functions offered to you on the menu bar ("Data/Sort", Data/Filter"), You can also generate automatic sub-totals, create pivot tables and charts.

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.