Excel and Database

Section 1: Databases and Excel

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 of data).

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
12-28-2009	10546	CT	3,224.69
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 of data). As you can see the 4th field in records 2 and 3 are empty. These lines of data are still condidered as records. A record is a line of data with at least one field containing a value

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 (the most important being SUMPRODUCT), formulas and programming language (Macros in 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:

Lesson 1: Database, Datamarts and Data Cubes

Lesson 2: Excel and Databases

Lesson 3: Excel as Database

Lesson 4: Excel or Access

Database 101 Excel and External Data Analyzing Data with Excel Reporting with Excel Other Functionalities
Email Tutorials