Clean Data Set for Pivot Table | MyExcelOnline

When working with Excel Pivot Tables, you will need to ensure that you have a clean data set. So there are 2 things that I want to show you:

  • When your data is not clean
  • How to clean your data.

Exercise Workbook:

DOWNLOAD EXCEL WORKBOOK


 

STEP 1: Here is our data set, select the data and go to Insert > Tables > PivotTable

Clean Data Set for Pivot Table | MyExcelOnline

STEP 2: Select Existing Worksheet and pick a cell inside the same worksheet to insert our Pivot Table. Click OK.

Clean Data Set for Pivot Table | MyExcelOnline

STEP 3: Drag the DATA column into Rows and Values

You will notice that how come IN123C104Z is appearing as 3 rows? This is caused by our dirty data!

Clean Data Set for Pivot Table | MyExcelOnline

STEP 4: If you inspect the data table closely, you will see that they have extra spaces. This is what’s causing our Pivot Table to have weird outputs!

clean data set

STEP 5: Let us clean those extra spaces! Select the entire column of your data and go to Data > Data Tools > Text to Columns

Clean Data Set for Pivot Table | MyExcelOnline

STEP 6: Click Next

Clean Data Set for Pivot Table | MyExcelOnline

STEP 7: Make sure Space is ticked and select Finish. This will remove the extra spaces as a result.

Clean Data Set for Pivot Table | MyExcelOnline

STEP 8: Right click anywhere on your Pivot Table and select Refresh to reflect our data cleanup.

Clean Data Set for Pivot Table | MyExcelOnline

Now that’s looking much better!

Clean Data Set for Pivot Table | MyExcelOnline

HELPFUL RESOURCE:

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

Clean Data Set for Pivot Table | 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