Contents
Let’s assume that you got this “beauty” as a data source:
A nice-looking table, which, nevertheless, is terribly inconvenient to work with. Imagine, for example, what you need for this table:
- Calculate the difference between actual and plan for each product for each year.
- Compare the same quarters for different years.
- Calculate the total or average fact for each product.
- Compare sales of orange juice of different brands.
Any of the above actions will require a cardinal shoveling of the table, entering a large number of formulas, manually copying them, etc.
Therefore (Captain Evidence!) this does not need to be done by formulas. It will be much easier and faster to implement this with the help of pivot tables – a flexible, powerful and much more suitable tool for such a task.
One problem is that in Microsoft Excel you cannot build a pivot table if it has a multiline header, which is exactly what we have in our example.
We will be rescued by Power Query – a powerful add-in built into Excel, starting from the 2016 version (and for Excel 2010 and 2013 it can be downloaded for free from the Microsoft website).
Step 1. Load data into Power Query
The easiest way in our case is to select the entire range with data (from cell B2 to the end of the table) and give it a name on the tab Formulas – Set Name (Formulas — Create name). Let’s call it original Data, For example:
Then, checking that the entire table is selected, select on the tab Data (or tab Power Queryif you installed it as a separate add-in for Excel 2010-2013), the command From Table/Range (From Table/Range). Our “beautiful” table will be loaded into the Power Query query editor:
Note that the merged cells have split and the resulting empty cells are now filled null – a special word that means in Power Query “emptiness” or the absence of something.
Also Power Query tried to automatically recognize the data types in each column by adding in the right pane Applied steps (Applied Steps) step modified type (Changed Type). It’s too early to do this, so this step can be safely deleted by clicking on the cross to the left of its name:
Step 2. Convert the table to a flat one
Further transformations will consist of several operations:
- First, let’s get rid of extra empty lines (see lines 9 or 26 for example) with the command Home — Delete lines — Delete empty lines (Home — Remove rows — Remove empty rows):
If necessary, you can also get rid of any other extra lines (for example, lines with totals, etc.) using the filter by unchecking the corresponding checkbox.
- To fill the voids with null, select the first two columns (while holding the Ctrl) and select on the tab Transformation Command Fill – Down (Transform — Fill — Down):
- Now we need to temporarily glue the first three columns into one. To do this, select them and select the command Transform – Text Column – Merge Columns (Transform — Merge Columns), specifying as a temporary separator any character that does not occur in our data, such as a semicolon:
- Now let’s transpose the table in the same place on the tab Transformationby pressing the button Transpose in the upper left corner (Transform — Transpose). The rows and columns in our data will be swapped:
- Let’s delete empty columns, which turned into empty rows after transposition, using the command Home — Delete lines — Delete empty lines (Home — Remove rows — Remove empty rows).
- Fill in the empty cells in the first two columns with years and quarters with the command Transform – Fill – Down (Transform — Fill — Down), as we did before.
- Raise the text from the first line to the header by selecting on the tab Home Command Use first line as headers (Home — Use first row as headers). The category, name and type glued together with a semicolon will replace the standard Column1,2,3…
- Now select the first three columns and, by clicking on the heading of any of them, select the command Uncollapse other columns (Unpivot other columns). All columns except the selected ones will turn into new rows, where the column name and numeric value will fall into different cells:
- It remains to separate the sticky category, name and type in the column Attribute using the command Home — Split Column — By Delimiter (Home — Split column — By delimiter):
- Well, for greater beauty, let’s rename the resulting columns by double-clicking on the headings:
On this, bringing the initial data in order has been successfully completed and you can proceed to building a pivot table.
Step 3. Building a summary
On the Гlava tab Power Query select command Close and load – Close and load in… (Home — Close & Load — Close & Load to…):
In the next window, you can go in two ways:
If you have an option in this window PivotTable Report (Pivot Table Report) – feel free to select it and click on OK. A new sheet will create a pivot table based on our data from Power Query.
If you have an older version of Excel, then this option may not be available in this window. Then you have to “go around”:
- Choose an option Just create a connection (Create connection only),
- After returning to Excel on the tab Insert press the button summary table (Insert — Pivot Table)
- In the window for creating a summary, select the option Use external data source (Use external data source).
- In the window that opens, select our connection from Power Query:
In both cases, the result will be a summary, built according to our original beautiful, but inconvenient table:
Now our data can be twisted-twisted-analyzed in any way you need easily and beautifully 🙂
- Crosstab redesign to flat
- Pivot table across multiple data ranges
- Set up calculations in a PivotTable