Excel As Database

Excel As Database ?
On this page, I present the limits of Excel as a database and it's functionalities in that field. There are sections on general tips, on filters, and on formulas.

I also illustrate an Data Management System that includes Excel and that can be a excellent solution if you have less than 60,0000 employees, less than 60,000 sales per year, less than 60,000 employees, less than 60,000 items for sale and less than 60,000 accounts in your accounting system. From this database, you can design an accounting system, an inventory system, a billing system, etc. You will be able to maintain such a system internally and adapt it to the changes in your organization.

Excel is not a database application but it is the best reporting tool on the market. You can import data from any database or datamart and create reports and analysis to satisfy your needs for a small fraction of the cost that you would incur if you would use the reporting applications of the database. You will save a lot of time and be able to train many users becoming less dependant on the limited resources of your IS department.
If you want to develop a database or a datamart in Access,
visit my page on the subject.
Even if many users can open the same workbook at the same time (in read only mode), the limit of Excel as a database resides in the fact that only one user can enter data in a workbook at one time. If two to 15 users must enter data simultaneously, you may want to move to Access as a database. If more than 20 users must enter data simultaneously, you will choose one of the big databases (SQL Server, Sybase, Oracle, dB, etc).



An Excel workbook may comprise 256 sheets with 256 columns and 65,000 rows each which amount to 4,294,967,296 cells. Each cell can hold 32,767 characters each or a formula 1,024 characters long. With a 500 mhz CPU and 128 Mg of RAM, Excel can be used as a database. The great power of Excel is its conviviality to design reports, analysis and charts.


Fundamental DATABASE Notion

A DATABASE is a set of data
which reading is convivial
for a MACHINE.

A REPORT (or form) is a set of data
which reading is convivial
for a HUMAN.

Very rarely will you be able to organize data so that reading them is convivial to both machines and humans.

To become an expert in database a good background in computer science is essential. To become an expert in reporting you need a good background in accounting and management.

Excel Database

Note: Select a different format for the title cells as oppose to the other cells of the table so that Excel understands that it is working with a database and not just a table with no title cells. Excel will consider your table as a Database if in the first cell of each column the font is bold and the inferior border is also bold. Once these conditions are met, all items of the "Data" sub-menu become accessible (Filters, Sort, Form, Sub-Total, etc...).

You know how to use the AUTOFILTER, you place the cursor within the database and go to the menu "Data/Filter/AutoFilter" and little arrows appear in the title cell of every field. If you want these dropdown menus to appear only for certain fields, select the desired column or columns before you go to the menu bar to use "Data/Filter/AutoFilter". (Thanks Darrel Steen)


Advanced Filters

Advanced filters are one of the most important tool to be used with databases. Here are a few tips on these filters. Let's say we have a database with the following fields:

First Name
Name
Age
City
Sex
Weight


The criteria range to filter members by the name of Thomas would be:
Name

Thomas


The criteria range to filter the women of Montreal would be:
Sex
City

W
Montreal

The criteria range to filter the women of Montreal and of Toronto would be:
Sex
City

W
Montreal

W
Toronto

The criteria range to filter men between ages of 50 and 59 would be:
Sex
Age
Age

M
>=50
<60>=50
<60

W

Other Formulas Useful with Excel as a Database

SUBTOTAL
The function SUBTOTAL allows (among other operations) to count, to sum or to calculate the average of non-filtered elements of a database. The function requires two arguments, the first is a number between "1" and "11" that specifies the operation to be performed (for ex. "1" is for average, "2" is for count and "9" is for sum) and, the second is the range covered by the function.
=SUBTOTAL(9,B2:B45)
This formula will sum the values in range (B2:B45) for records that are not filtered when a filter is applied on the database.

A Data Management System with Excel and Access

I have designed this system for a division of the Ministry of Supplies and Services (Government of Canada).

In this system, I don't use Microsoft Query and the data are LIVE. Many users can view the different reports at the same time (invoice, contract, statistics, report1) but only one user can enter data at one time.

The workbooks with a single sheet contain the data. They are the tables of this Data Management System. They can host as much as 60,000 records each (lines of data).

The different reports (unlimited numbers) are linked to the "tables" with array formulas and Index/Match formulas. When a report is opened, a dialog window allows the user to refresh the data. On of the great advantage of Excel Reports is that they can be printed and, unlike other reports designed within database, they can be viewed on line. These reports are invoices, contracts, analysis, summary and detailed tables and charts.

There is no convivial or ergonomic form to enter data in "Tables" in Excel. Even the automatic "Form" in Excel is rather limited. So I have created an Access application so that I could design convivial and ergonomic forms to enter the data in the "tables". The Excel workbooks with a single sheet are connected to the Access application through the "Link Tables" function.

This Data Management System is easy and inexpensive to build, easy to maintain and very very friendly. Training on Access is minimal and Excel is a development environment within which your users are probably already familiar. You might want to hire a Data Analyst to make sure that the Excel workbooks with a single worksheet (your tables) are well designed as far as what they contain and how the information is organized.

Excel As Database