Microsoft excel spreadsheets

About Databases

Most data is available in database format. For example, sales data could look like this in many databases:

Date	Client	Product	Quantity	Amount
12-19-2009	29187	190879	330	3456.87
10-17-2009	28984	290675	456	4675.34
11-28-2009	39652	276543	3,224	4325.87
12-15-2009	67589	876564	543	2345.65

In database language there are 5 fields (columns) in this table and 4 records (lines).

The database within an accounting program could look like this:

Date	Account	DT/CT	Amount	Comment
12-19-2009	10546	DT	330.46	Invoice 234543
12-19-2009	35467	CT	330.46	Invoice 234543
12-28-2009	10546	CT	3,224.69	Invoice A435
12-28-2009	42354	DT	3,224.69	Invoice A435

In database language there are also 5 fields (columns) in this table and 4 records (lines).

A database is a set of data organized so that it is easily accessible for a computer and its programs. When the data is available in the format above you can efficiently use the powerful analytical functionalities of Excel, the functions, formulas and programming language (VBA) to design useful automated reports.

We call our reporting approach the "Datasheet Approach". The data sits on one sheet (entered manually, copied/pasted or imported using Excel functions or functionalities). Then the report or reports are developed using SUMPRODUCT on other sheets.

In this section you will learn more about Excel and databases, datamarts and data cubes by reading the 4 articles below:

Lesson 1: Database, Datamarts and Data Cubes

Lesson 2: Excel and Databases

Lesson 3: Excel as Database

Lesson 4: Excel or Access