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
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
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 – 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.
Click to Grab Your Excel Ebook – Free!
