A pivot table can handle data that is in the form of a rectangle. Your database can be stored in either a worksheet or an external database file. Worksheet database can be in table form or just a normal range.
Generally, database fields can contain two types of information:
- Data: Data type fields contain values or data to be summarized. In our Bank-accounts.xlsx file, the Amount field is a data field.
- Category: Category fields describe the data fields. In our Bank-accounts.xlsx example, the Date, Weekday, AcctType, OpenedBy, Branch, and Customer fields are category fields because they describe the data in the Amount field.
In summary, numeric fields are regarded as data fields, and text fields are regarded as category fields in a database.
Note: When a database table is appropriate for a pivot table, then it is called “normalized”.
A single database table can have any number of data fields and category fields. When we create a pivot table, we usually summarize one or more of the data fields. We use the values in the category fields as rows, columns, or filters.
Note: There are exceptions also. You may find that the Excel PivotTable feature works even for databases that don’t contain any numerical data fields.
Download Sample File
What database pivot table can work with or not
Not Normalized Database
The following Figure shows a database that is not appropriate for a pivot table. Although this database contains descriptive information about each value, it does not consist of normalized data. Actually, this database resembles a pivot table summary.
Read More: Reverse Pivot Tables – Unpivot Summary Data
The following Figure shows the same data, but it is normalized. This data range contains 78 rows of data. Sales data of the first six months in thirteen states are placed in 78 rows. Notice that each row contains category information for the sales value (data field). This table is appropriate for a pivot table and contains all information necessary to summarize the information by region or quarter or month.
A pivot table from the above data range
The following Figure shows a pivot table created from the normalized data. This pivot table actually identical to the non-normalized data shown in our first figure under the Non-Normalized heading. Working with normalized data will give you ultimate flexibility in designing reports.
Happy Excelling 🙂