Data appropriate for a pivot table

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:

  1. Data: Data type fields contain values or data to be summarized. In our Bank-accounts.xlsx file, the Amount field is a data field.
  2. 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.

Data appropriate for a pivot table

Data fields and Category fields are shown. Text type fields are Category fields and numeric fields are regarded as Data fields.

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

Download this sample file to work with the following examples.

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.

Data appropriate for a pivot table

This data range is not normalized. It resembles a pivot table summary.

Read More: Reverse Pivot Tables – Unpivot Summary Data

Normalized Database

The following Figure shows the same data, but it is normalized. This data range contains 78 rows of data. Sales data of 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.

Data appropriate for a pivot table.

Normalized data. Appropriate for pivot table.

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.

Data appropriate for a pivot table

Pivot Table created from normalized data

Happy Excelling 🙂


Hello! Welcome to my Excel blog! I am conducting deep dives into the world of Excel. Please join with me and explore Excel deeply. Keep in mind this African proverb: "If you want to go fast, go alone, If you want to go far, go together." Let's together explore Excel deeply! ☕

1 Comment

      Leave a reply