Data Conditioning: Structuring Data for Pivot Table

There are times when you want to do a slice and dice analysis through pivot table but the raw source data is not in a structure ready as source for the pivot table. That's when one needs to do some modifications to ensure the data is normalized, with similar data in a single column.

Example of raw data in a format which is not ready for pivot-table to be converted to the appropriate structure

To perform the above data restructuring, there are usually 2 ways to go about achieving it.

1) For the above scenario would be to select and fill up all blank cells with the value directly above it using the "Find >>> Go to Special" method which is very clearly documented over at Contextures article on "Fill Blank Cells Manually"

2) The second method would involve the creation of "Helping Columns" as per below : Pull down the IF ,ISBLANK Formula (PS: There maybe other different but similar scenarios which could be solved by using IF in a similar fashion)

For REPORTS submitted in an Already Pivoted Manner

Last but not least, you may sometimes receive reports in an already "Pivoted" manner as per below but what you require is the original normalized structure. For this, you may use the attached "Unpivot" file to convert such reports back to the normalized data structure.

Possible applications include conversion of payroll file and Fixed Asset Schedules into journal entries for uploading to system.

An already "Pivoted" manner

Required Output Structure

Download File

Excellent video below from https://www.myonlinetraininghub.com/about on similar topics done through more modern techniques using Power Query