Excel: Clean Data Already in Excel

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.


Problem: My data is already in Excel. I need to transform the data. I need to unpivot the month data.

  1. Unpivot this data.

    Follow these steps:

    1. Select one cell inside your data set.
    2. First, convert your data set to a table using Ctrl+T.
    3. Choose Power Query, From Table.
    4. In the Query Editor, click on the Jan heading to select that column.
    5. Shift+Click on the Dec column to select all the columns between Jan and Dec.
    6. On the Transform tab, select Unpivot Columns.
    7. Optionally, rename the Attribute column to be Month.
    8. On the Home tab, choose Close and Load.

    You will see a new worksheet with the results.

  2. If the underlying data changes, you can refresh this query.

For more resources for Microsoft Excel