It may be necessary to rearrange the data before creating a flexible pivot table. For instance, a pivot table won’t function well if the data contains a separate column for each month’s sales. You need to “unpivot” your data first. Here are two quick methods to unpivot Excel data along with what that means.
First, a reminder that the highly regarded Excel dashboard course taught by Mynda Treacy has a registration deadline of today, Thursday, August 18th. You’ll learn how to create awesome dashboards, quickly and efficiently, and you’ll pick up other key Excel skills too, so don’t miss out – it may be a few months before the course opens again.
Your data already resembles a pivot table report if there is a separate column for each month’s sales. Each row contains sales data for 12 months rather than having a row for each month. Although it looks good on the worksheet, it won’t function properly in a pivot table.
We must “unpivot” the data if it already resembles a pivot table in order to place each sales amount on a separate row. All the dates should be in one column, and all the sales amounts should be in another, rather than having a column for each month. The pivot table can then display the data in separate columns after being summarized, or it can simply display a sum.
For more information see Create, load, or edit a query in Excel. Select the columns you do want to unpivot. To select more than one column contiguously or discontiguously, press Shift+Click or CTRL+Click on each subsequent column. Select Transform > Unpivot Only Selected Columns.
Why unpivot data in Excel?
You might unpivot your data in Excel for a number of reasons, including:
What does it mean to unpivot data in Excel?
To “unpivot” data in Excel is to change it from columns to rows, where the column headings are one row and the rows of data below the columns are their own combined row. It offers a fresh perspective on data, especially numerical data, and links it to various column headings. When different data types are chosen for the columns, the varying data is converted to a common data type for easier reading. Unpivots, also known as flattening the data, take data that is dispersed widely across columns and turn it into tall, slender rows instead.
You can unpivot the data to view it differently, for instance, if you have six columns of beverage names and the rows beneath the column headings include prices for the beverage at various franchise locations. The names of the beverages are listed in a row under a new column when you unpivot this data, with names occasionally repeating depending on the number of prices associated with that beverage. Instead of being dispersed across columns, the prices are arranged in their own new row next to the names of the associated beverages.
How to unpivot data in Excel
Here are three strategies to think about when unpivoting data in Excel:
Power Query
Consider these steps when using Excel’s power query, also referred to as the get and transform method, to unpivot data:
Click any cell in the dataset and select the “Insert” tab from the top toolbar to insert your data into a table. Under the “Tables” section, select “Table. ” A box appears labeled “Create Table. Make sure the cell range in this box is accurate and make any necessary changes. If your table has column headings, check the “My table has headers” box if it isn’t already checked, and then click the “OK” button. This creates an Excel table from any tabular data you have in the range you chose.
You can unpivot your data once it has been transformed and placed in an Excel table. Start by clicking any information in a table’s cell. Click on the “Data” tab in the top toolbar. Choose “From Table / Range” from the “Get & Transform Data” section when you find it. When the “Create Table” box appears, click “OK. ” This opens a query editor to unpivot your data.
Right-click the first column in the “Query Editor” and select “Unpivot Other Columns.” This unpivots the data in your Excel table’s other columns. If you prefer, think about renaming the new columns to more accurately reflect the data.
After you unpivot your data, you can choose to verify that your data types are accurate, but doing so is not required. To do this, select the “Transform” tab after clicking on the cell you want to verify. The data type for that cell is displayed in a field with the label “Data Type.”
Get and Transform
Even without Power Query installed, you can still unpivot data with Get & Transform by taking the following steps:
In your Excel table, click on any cell with data. Select “From Table” in the Get & Transform section by clicking the “Data” tab in the top toolbar. This opens the “Query Editor” in your table.
Select the columns you want to combine into a single new column in the “Query Editor” To do this, click on the group’s first desired column. Next, while still holding down the “Shift” key, click and hold the group’s final column.
Right-click any selected column heading after choosing the columns you want to combine. In the drop-down menu that appears, select “Unpivot Columns. The combined rows appear in a column titled “Values,” and the heading names of the chosen columns appear in a column titled “Attributes.” Click the “Close and Load” button if you want the updated data to appear in a new sheet.
The unpivoted data remains connected to the original data even after you create a new setup for your data. After refreshing the table, any updates or new information you’ve added to the original data automatically appear in the unpivotted section. To ensure the data is received, you might want to save your work each time you make a change.
Creating a macro
A macro is a tool you create that you add to the top toolbar and can use to create custom instructions. If you intend to unpivot multiple data files similarly, creating a macro is an option. Consider the steps listed below to unpivot data using reusable macros:
On Excel, the “Developer” tabs disabled by default setting. Go to the top toolbar and select the “File” tab to enable it. Find “Options” and select “Customize Ribbon. “Check the box next to “Developer” in both the “Main Tabs” and “Customize the Ribbon” sections. “.
Find the “Code” group in the top toolbar’s “Developer” tab, then click “Record Macro.” You can enter a title for your macro here in the “Macro Name” section, describe what the macros are intended to do or are used for in the “Description” section, and then create a keyboard shortcut in the “Shortcut Key” section by clicking “OK. These steps are optional, but if you want to remember why you created the macro or in case others use it, consider adding information to the title and description.
Once finished, you can write the unpivot data macro instructions. Once finished, click “Stop Recording” on the “Developer” tab. Once your macro has been created, you can find it in the “Developer” tab and edit it whenever you want by choosing “Edit.”
In the top toolbar, click the “Developer” tab. Go to the “Code” section and click “Macros. “A list of the created macros appears, sorted by titles. Select the macros you plan to use and click “Run. “.
When the macro executes, you are prompted to enter a split character. This is how it combines your columns temporarily. Use a character that is not found in any of your current column headings; if it works, you can leave it as the provided default character. The number of label columns in your data is then listed in a field called “Label Columns.” Click “OK. “.
It appears that there are other options if you told your macro to create the data in the current sheet. If you told your macro to create data in a new sheet, the original data remains the same but a new sheet with your unpivoted data appears. If you select option two, updating the original data won’t update the unpivoted data automatically. Consider creating a macro for both scenarios if necessary.
Please note that Indeed is not affiliated with any of the businesses mentioned in this article.
Excel Unpivot Data with Multiple Headers (Multiple Row levels into Columns with Power Query)
FAQ
How do I Unpivot data in Excel without Power Query?
If you right-click one of the columns you’ve chosen, an options menu will appear. Click the Unpivot columns… menu item near the bottom. The Power Query window’s data will convert the columns into separate, distinct rows.
How do I Unpivot columns to rows in Excel?
You can also apply other transformations here before going back to Excel. Select the columns you want to melt or reshape (ctrl or shift-select, don’t drag). Click “Unpivot Columns” in the “Transform” tab. Click “Close & Load” in the “Home” tab. By doing so, a new table or query object with the desired outcome will be created in Excel.
How do you melt in Excel?
One method for converting data from a short/wide to a tall/slim format is an unpivot transformation. The varying data is converted to a common data type when the source columns’ data types are different so that the source data can be included in a single column in the new data set.
What is Unpivot data?
One method for converting data from a short/wide to a tall/slim format is an unpivot transformation. The varying data is converted to a common data type when the source columns’ data types are different so that the source data can be included in a single column in the new data set.
