Most Excel users know their way around a pivot table. A pivot table can quickly summarize basic statistical information (with sums or averages, for example) and display that data in a more meaningful format. However, before you can start pivoting your Excel data, you may need to rearrange your data —in other words, you need to “unpivot data” in your spreadsheet.
Unpivoting data in Excel means to transform your data from columns to rows, where column headings are one row and the rows of data below the columns become their own separate, combined row.
It provides a new way to view data and its association with different column headings. When the data types of columns selected are different, the varying data is converted to a common data type for better readability. Unpivoting is sometimes referred to as flattening the data and takes data that is spread out wide across columns and makes them tall rows instead.
Why Unpivot Excel Data?
Unpivoting data gives you more options for data structure and makes your report more interactive without requiring the use of additional calculated columns – freeing up your data and making it easier to use and manage. Other reasons to unpivot data in Excel include:
- Discover how many times a certain column heading matches particular data
- Find how many data points match each other or differentiate from each other
- Calculate large amounts of numerical data faster
How to Unpivot Data in Excel
What does unpivot look like in Excel? Consider a basic example: a set of students each take a test every month for their algebra class. You want to be able to more closely analyze the performance of each student. However, you can’t do so by having a separate column for each month, which is the way that this data is most commonly tracked. When you build a pivot table from that data, there will be nine different fields with test scores—one for each month of the school year—and the pivot table won’t be able to calculate an annual total.
To unpivot data in excel, we need to make the data look longer instead of wider by creating an aggregated “month” column, as shown in the image below. With all of the months in one column, another column is created for the corresponding test scores of each student for each month.
So, how do we get started? First, we need to convert our data into a pivot table. The fastest way to do this is simply by pressing [CTRL] + T.
After we have our table, under “Get and Transform Data” group found in the “Data” tab, we’re going to click “From Table/Range.”
This will prompt Excel to open the Power Query Editor. Once in the Power Query Editor, we’re going to right-click on the “Name” column and select “Unpivot Other Columns.”
Upon doing so, you’ll now notice that the data is in the format that we want—a separate column has been created for the months and for the test scores. Now, all we have to do is re-name these new columns by right-clicking on the top of the columns.
Once we’re done renaming the columns, we’ll simply click “Close and Load” in the top left corner of the screen. This will create another sheet for us to use as the basis of our Excel pivots. If we decide to update our data and add, for example, the summer months if the students decided to take summer courses, we would only have to update our data in the original tab and right click and “Refresh” our data after the unpivot in the new tab.
The Next Step: Pivots
Now, we’ve achieved our goal: unpivot Excel data so that it’s in an acceptable format to create a pivot table. We’re not going to get into the specific “how-tos” of creating a pivot table in this article, but the most important step you can take before creating a pivot table is understanding what question you want to ask of your data and why. Creating a pivot table does little good if it’s not answering a pertinent question to your business. For example, based upon our student data, we might want to understand the average student test score per month to see if the students had difficulty with particular subjects.
The unpivot data and pivots functions are not limited to Excel, of course. Pivot SQL is a common function across many industries with SQL servers. The process of a pivot SQL looks a lot different than an Excel pivot since it relies on a querying language, but the end goal is the same.
The Move Away from Excel Pivot and Unpivot Data Functions
At Alteryx, we love a good pivot table. Excel unpivot and pivot are, by all accounts, the most powerful functions in Excel. But dealing in gigabytes worth of data with Excel can be unwieldy or even prohibitive. That’s why Designer Cloud has built a product that offers familiar pivot-table functionality, but with a superior visual interface and intelligent interactions. This means you get the multi-faceted view of the pivot table, without the unintuitive process. It also means you get the aggregation power of knowing how to unpivot data in Excel without the awkward practice of having to make multiple pivot tables.
As data lineage has become more important, we have seen a move away from unpivoting data in Excel, as the application makes it virtually impossible to track all of the transformations that have been made, when, and by whom. With Alteryx, every change is clearly listed at the right of your screen at all times.
Two areas where Alteryx sees the ubiquitous use of Excel dissipating most is in financial services and banking. The financial services industry historically has relied mostly on Excel as a basis for informing industry trends and critical calculations, like the Allowance for Loan and Lease Losses (ALLL).
One of the most significant quarterly financial statement estimates, the ALLL, shows how much a bank has in reserve to offset bad debts, and it has an enormous impact on earnings and capital. ALLL is also a calculation that has been derived almost exclusively using Excel.
Excel became institutionalized because it’s powerful and relatively easy. However, finance and other sectors are realizing that Excel has more than just dataset size limitations. It is estimated that almost 90% of spreadsheets have errors, made worse by the lack of controls as spreadsheets pass between coworkers. In fact, it was a relatively simple Excel cut and paste formula error in 2012 that caused JP Morgan to miscalculate a fund’s VaR risk profile to the tune of a $2B loss.
Above size limitations, nowhere is tracking and data lineage more important than in the financial services sector, where scrutinous audits and regulatory reporting rule. Alteryx makes tracking data simple, enabling real-time inventories of key data.
How Designer Cloud Transforms the Unpivot Excel Data Command
While Alteryx offers a predictive and visually pleasing way to execute a pivot transform, here we will focus on how to unpivot data in Excel. The unpivot Excel command is used most often when moving data from the data-entry stage to the analysis stage (e.g. normalizing data). Data entry is easiest when one assigns one row for each key (First Name) and one column for a data point (Hours Worked). This format makes for easy recording of data; however, aggregation and summation become far more difficult.
Designer Cloud reforms the data by merging one or more columns into key and value columns. Keys are the names of input columns, and value columns are the cell values from the source. Rows of data are duplicated, once for each input column. The unpivot Excel data column can be applied to multiple data columns in the same transform. All columns are un-pivoted into the same key and value columns. With data arranged this way, new aggregations can be made and new insights found. Designer Cloud’s solution for unpivot Excel function woes is truly for anyone who needs to organize their data efficiently and accurately.
Beyond the actual function of a pivot or unpivot Excel command, Designer Cloud has the built-in functionality to export directly to a number of useful destinations, including dashboarding solutions like Tableau. Designer Cloud was built with Tableau users in mind: We know pivot/unpivot is an essential function, and we’ve made it easier and quicker to see your results, without employing the messiness of Excel.
So if you are wondering how to unpivot data in Excel, get started with a free trial of Alteryx today.
