#1 Cause of Pivot Table Headaches

Untitled Document

With Excel’s pivot table tool, users can quickly consolidate, analyze and summarize large amounts of data.

In some cases, however, users are frustrated to find information missing from their pivot table’s results. Most problems with pivot tables can be traced to one source: the source data used in the pivot table.

The good news is most massive pivot table headaches can be solved with a few simple steps up front.

7 Pivot Table Source Data Rules

Here are 7 rules that will lead to fewer pivot table headaches.

Rule 1 – Unique, Descriptive Headings

Each column in your dataset must have a unique heading. For example, you should not have two column headings named sales. If your source data contains a lot of columns, it is particularly important that your headings are as descriptive as possible.


Pivot Table Data - Headings Incorrect

Pivot Table Data – Headings Incorrect


Rule 2 – No Gaps

The headings must be in the top row of your source data, and data should be immediately below the headings.

Rule 3 – Clean Up Your Formatting

If it is important to use data in a specific way, make sure it is formatted properly. For example, if you have dates in your source data that are formatted as text, it is best to convert them to dates before creating your pivot table.

Rule 4 – Don’t Mix Formats

All data in a column should have the same format. This is especially true when dealing with dates: the biggest problem I see is mixing dates (11/11/16) and text (TBD)  in the same column. If you want to maximize pivot table’s powerful abilities to analyze and group date information, make sure that a column that contains dates doesn’t also contain text.

Pivot Table Data - Incorrect Formats

Pivot Table Data – Incorrect Formats


Rule 5 – Blank Rows Are A No-No

Avoid blank rows in your source data.

Rule 6 – Leave Totaling to the Experts

Remove all totals from your source data – pivot tables do a much better job.

Rule 7 – Start with a Table

Instead of basing your pivot table on raw source data, convert your data to an Excel table before creating the pivot table. If your data changes constantly, this quick step will save you a lot of time.  Creating a table makes your data source dynamic –  all you have to do is hit the refresh button to grab the most recent records. Without a dynamic data source you have to use the Change Data Source command to make sure your pivot table is accessing current data.

pivot table data correct headings format

Pivot Table Data – Corrected


The key to getting reliable answers from Excel’s extremely powerful pivot table tool is making sure your data is properly formatted before you begin.

Interested in learning about pivot tables? Learn more about our Pivot Tables and PowerPivot courses.

Do you have an Excel question?

Comment below and it just might make it to a blog post!

About the Author

Jackie Kiadii and her team of subject matter expert (SME) trainers provide Microsoft software training that delivers measurable results – increasing productivity, reducing inefficiency, and improving the bottom line. Topics include: Project, Access, Excel, Excel VBA, PowerPoint. Upcoming Excel courses are on our calendar, click here.

Need onsite group training? Call 770.498.7333 or email us. Our courses can be customized to your needs. Jackie has provided software training for employees of The Coca-Cola Company, Emory University, Novelis, and more. This training is available for groups of 4 or more students.

Atlanta Computer Training on Facebook Atlanta Computer Training - LinkedIn Software Tips on Twitter

Click to Grab Your Excel Ebook – Free!

Get the free Excel ebook

12 Tips to Empower Excel Users

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>