Search code examples
excelrowstransposedata-cleaning

How can I turn part of the Excel data to columns to get a desired output?

For eg - Say I have data in the following format -

Current Format
enter image description here

I would need the data to be formatted in the following format for ease of use -

Required Format
enter image description here

Of course the data contains a lot more records - I'm looking for an easy way to transpose data in this way for large sets of data.

Any help will be appreciated :)

Solution

  • This is very easy with PowerQuery. It is inbuilt for Excel 2016 and a freely available add in for Version from 2010 to 2013.

    You would set your data up as a table excluding the first row which contains the text Number of Cases (Ctrl + T whilst bring up window to create table)

    Create table

    Then from the data tab (Excel 2016) or the Powerquery tab (earlier versions) select data from table and use your newly created table as source.

    Highlight the last 3 columns and right click > unpivot columns

    Unpivot

    Double click in headers section and rename Attribute as Type and Value as Number of Cases

    Rename headers

    Top left select close and load to

    Close and load to

    Select table and load to new worksheet (or existing)

    Load to

    View result

    enter image description here