Skip to content

Prep Your Pivot Table Data

These days, many people who use Excel also use pivot tables.

That isn’t a bad thing and can even be a good thing. Pivot tables are very powerful and quite useful. Plus, they are easy to construct, style, and update.

These are all excellent features to have available when working with such a fantastic tool.

However, one of the biggest misuses of pivot tables that I see takes place even before the pivot table is created.

In fact, it all starts with the source data.

Use Clean Data for your Pivot Table

The first and most important thing you should focus on before creating your pivot table is ensuring you have clean source data.

Now, what do I mean by clean data?

Well, when preparing your data for summarization by pivot table, it should check three major boxes.

  • The data should be in tabular format
  • The data should have no empty rows or columns
  • The data should have distinct column headers for each column

If your data checks these three boxes, then your data is ready to be summarized by a pivot table.

Transform the Data into an Excel Table

Now, most people stop after they have cleaned their data according to the three points I just mentioned. However, there is one extra step that I highly recommend you take.

Before you select the data and insert a pivot table, you should first convert the data range into a standard Excel table.


Now, why should you do this? What’s the big reason?

It is best to turn your source data into an Excel table because it will be much easier to update your pivot table when new data is added in the future.

An Excel table will expand automatically to include new data. On top of that, that new data is then automatically included in your pivot table’s calculations once you refresh the table.

However, if you do not use an Excel table, then you would have to go back and re-select the source data to include it in your pivot table calculations.

This can be quite tedious, and it can also be a source of unwanted errors.

Therefore, it is always best to start by turning your source data into an Excel table before creating a pivot table.

The Big Takeaway

To sum up this entire article, one of the best ways to improve your pivot tables is to take the following steps before you create your pivot table in the first place.

  1. Ensure your source data is in tabular format
  2. Ensure your source data has no empty rows or columns
  3. Ensure your source data has distinct column headers
  4. Ensure your source data is turned into an Excel table

Follow these steps, and your pivot tables will be that much better!

This Post Has One Comment

  1. I’ve resisted using Excel Tables for too long! After I started using them, my Excel life has been so much better. I can attest that it’s super helpful when working with pivot tables! It really is a game changer.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top