Our data isn’t stored in a table, and it currently looks like this. If it already is, you can skip this step. Store the crosstab data in a tableįirst, we need to ensure that our crosstab data is stored in a table. The four easy steps we’ll use to unpivot our crosstab data are: At the time I’m writing this, it is available from the link below.
If you are using a different version, you may need to first download the free Power Query add-in from the Microsoft site.
The unpivot command is available without any additional downloads in Excel 2016 for Windows. Now that we have our bearings and can visually see our objective, let’s work through the details. Here is another example that shows students and the trips they have attended.Īnd one more example that tracks who is assigned to various tasks. If you need to transpose instead of unpivot, check out this Excel University blog post instead. Transposing the data would place departments in rows and accounts in columns. Note: please note that unpivoting the data is not the same as transposing it. We want to unpivot the data, converting it from a crosstab format into a tabular format. We can easily convert tabular data into a crosstab format using a PivotTable. Here is an example of the same data stored in a crosstab style format: Here is an example of flat, tabular data: Objectiveīefore we dig into the mechanics, let’s be sure we are clear about the data formats and our objective. Thanks to Patrick who submitted this question. There are many ways to accomplish just about any Excel task, but in this post, I’ll demonstrate how to quickly unpivot the data.
When data is stored in a crosstab style format instead, Excel users have to spend a bit of time preparing the data for use. Excel easily summarizes flat, tabular data.
0 kommentar(er)
