Prepare Data for Excel Pivot Tables | MyExcelOnline

Before you work with the Excel Pivot Tables, you will need to arrange your dataset first in the right format and prepare data for Pivot Tables. There are a couple of principles that I will demonstrate to you here:

  • Tabular Format – your data is organized as a list with labeled columns
  • No Gaps – no blank columns and no blank rows
  • Tables – use this for structured referencing, Excel automatically updates as more rows and columns are added

Then you will be able to harness the full power of Pivot Tables!

Exercise Workbook:

DOWNLOAD EXCEL WORKBOOK


Let us go over the principles one by one! Here is our data set:

Prepare Data for Excel Pivot Tables | MyExcelOnline

You can see that it has a couple of issues. There are 2 columns without label headers:

Prepare Data for Excel Pivot Tables | MyExcelOnline

Then we also have 1 blank column and 2 blank rows. Let us get this data cleaned up!

Prepare Data for Excel Pivot Tables | MyExcelOnline

STEP 1: Let me quickly demonstrate to you first that we cannot use this set of data to create a Pivot Table.

Select the entire table of data and Go to Insert > Tables > PivotTable

Prepare Data for Excel Pivot Tables | MyExcelOnline

STEP 2: Click OK

Prepare Data for Excel Pivot Tables | MyExcelOnline

We get this error message because our data is not Pivot Table ready. Let us fix that!

Prepare Data for Excel Pivot Tables | MyExcelOnline

 

Tabular Format

STEP 3: Let us follow the Tabular Format principle. For the blank column headers, add SALES YEAR and SALES QTR:

Prepare Data for Excel Pivot Tables | MyExcelOnline

 

No Gaps

STEP 4: Now for the No Gaps principle, delete the blank column D and delete the blank rows 4 and 8.

prepare data

Our data is now looking good!

Prepare Data for Excel Pivot Tables | MyExcelOnline

 

Tables

STEP 5: Go to Insert > Tables > Table to convert this into an Excel Table

Prepare Data for Excel Pivot Tables | MyExcelOnline

STEP 6: Click OK

Prepare Data for Excel Pivot Tables | MyExcelOnline

We have our Excel Table ready! You get a lot more feature here like sorting, filtering, Table Styles to name a few.

Prepare Data for Excel Pivot Tables | MyExcelOnline

Now that you have prepared for data, let’s try and create a Pivot Table again!

 

STEP 7: Go to Insert > Tables > PivotTable

Prepare Data for Excel Pivot Tables | MyExcelOnline

STEP 8: Click OK to insert the Pivot Table into a new worksheet.

Prepare Data for Excel Pivot Tables | MyExcelOnline

And we are all set!

Prepare Data for Excel Pivot Tables | MyExcelOnline

 

HELPFUL RESOURCE:

Make sure to download our FREE PDF on the 101 Best Excel Tips & Tricks:

Prepare Data for Excel Pivot Tables | MyExcelOnline

You can learn more about how to use Excel by viewing our FREE Excel webinar training on FormulasPivot TablesPower Query, and Macros & VBA!

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn