How to Format Your Tabular Data Properly for MS Excel Pivot Table

Pivot Table is a great feature of MS Excel. It allows you to analyze your data and find interesting relationships and patterns that may not be so obvious originally. It’s a great data mining tool that’s built right into MS Excel 2010.
However, before you can make your data shine through the magic touch of a Pivot Table, your data must be presented in proper TABULAR FORM.

To explain what that involves, let’s show to you what your data table should NOT look like:

Here we see data about fish sales by quarters in the Atlantic region.

The table looks great and easy to read, right? But you won’t get any good results if you were to convert all this into a Pivot Table.

It has 4 major defects, as shown above. Let’s explain:

(1) A good tabular table should have separate COLUMN HEADERS for all columns. This does not have it.

(2) You need to present FISH BRANDS in their own rows or columns. The way fish labels are presented here in merged cells is not acceptable.

(3) There cannot be any empty rows (6, 11, 16) in tabular data. Eliminate them.

(4) There cannot be any empty columns (C) in tabular data. Eliminate it.

Here is how the corrected data looks like in proper tabular format:

Now this data is ready to be converted into a Pivot Table. We’ve explained how you can do that in another post.