How to Restructure Data in Microsoft Excel

Manually reshaping data in Microsoft Excel can take time and will likely lead to errors. Fortunately, the program offers many tools and functions to transform your data in seconds. Here are seven data-shaping tricks you must know.

1

Power Query: Flatten Your Data for Easy Analysis

Excel’s Power Query Editor is usually my go-to place for restructuring my data. Whether to merge or split cells, add or remove columns, or combine worksheets, it’s usually the most efficient method. However, one of the biggest benefits of Power Query is the ability to transform data so that all comparative values are contained within a single column—also known as flattening data.

In the screenshot below, various columns contain accounting data. Ideally, all the financial figures should be in one column, so that they’re easier to filter and compare. In other words, each row should contain a company, a year, and a value, meaning you can easily sort and filter the data by these three variables. This restructuring step in Power Query is called unpivoting the data.

First, select any cell in the data, and in the Data tab on the ribbon, click “From Table/Range.”

If your data isn’t already formatted as an Excel table, a dialog box will appear prompting you to fix this. This is so that the Power Query Editor can interpret your data more easily.

Then, in the Power Query Editor, select the headers of all the columns you want to unpivot. In this example, you would select the “2020” header, press Shift, and then select the “2024” header.

Next, in the Transform tab on the ribbon, expand the “Unpivot Columns” drop-down menu, and click “Unpivot Only Selected Columns.”

Now, as we hoped, each row contains a company, a year, and a value (collectively known as a record), and each column contains consistent types of data (also known as fields).

Finally, tidy up your data by double-clicking the column headers to rename them and clicking the number format icons to ensure each column contains the correct data type.

Your data is now successfully reshaped, so when you click “Close And Load” in the Home tab, the unpivoted table is sent to a new worksheet in your Excel workbook.

Now, you can perform analyses that were impossible before you flattened the data, like sorting the company profits into descending order or filtering the data by year.

2

PivotTables: Reshape Your Data Into Specified Columns, Rows, and Values

PivotTables are the ideal tool in Excel for reshaping large datasets into structures that make analysis, comparisons, and visualization much easier.

Take this Excel table as an example, where each row contains various details about a product sold. Specifically, let’s imagine that you want to compare the profits made in each department by country.

Doing this manually by filtering the department and country columns and extracting the profits would take too much time.

Instead, convert your data into a PivotTable. Select any cell in the table, and in the Insert tab on the ribbon, click the top half of the split “PivotTable” button.

Then, in the PivotTable From Table Or Range dialog box, make sure the correct range is selected, decide whether you want the PivotTable to appear in the active or a new worksheet, and click “OK.”

At this point, an area for constructing your PivotTable appears in the location you specified (in my example, I opted to create a new worksheet). Now, in the PivotTable Fields pane, click and drag the fields you want to display into the relevant areas. In this case, the aim is to compare the profits made in each department by country, so the departments need to be displayed as individual rows, the countries as columns, and the profits as values.

As you move the fields into the different areas of the PivotTable Fields pane, you’ll see the PivotTable taking shape.

Grand totals are displayed by default in Excel’s PivotTables. To remove these, select any cell in the PivotTable, open the contextual “Design” tab, and in the Grand Totals drop-down menu, select “Off For Rows And Columns.”

As a result of this straightforward process, the data is now reshaped in a way that allows you to quickly compare profits by country and department, and your original dataset remains unaltered.

The beauty of PivotTables is that you can easily choose different or additional fields in the PivotTable Fields pane. For example, here, I’ve added the Product field to the Rows area for a more detailed breakdown.

3

Transpose: Flip the Orientation of Your Data

There are many reasons why you might want to switch the rows and columns in your dataset, like ensuring each column is a field and each row is a record, preparing the data for further analysis using XLOOKUP or SUMIFS, or changing how the data appears in Excel charts.

Let’s say you want to flip this array so that the top row contains countries and the first column contains car brands.

Given the time it would take to transpose the data manually, Excel offers three ways to do it in seconds.

Use the Dynamic TRANSPOSE Function

Availability: The TRANSPOSE function is only available to those using Excel 2016 or later, Excel for Microsoft 365, Excel for the web, or the Excel mobile app.

To flip this data using the TRANSPOSE function, select the cell where you want the top-left cell of the transposed result to go, and type:

=TRANSPOSE(

Then, select the whole array, including the column headers, and close the parentheses, so that your formula looks like this:

=TRANSPOSE(A1:H8)

The TRANSPOSE function works exactly the same way with Excel tables as with regular ranges. The only difference you’ll notice is that the formula will contain the table name (structured references), rather than direct cell references. Remember to select the column headers when identifying the array!

When you press Enter, the array is transposed.

Because you used a formula, the result is dynamic, meaning any changes in the original dataset will be reflected in the transposed array. To make the result static, select all the cells, press Ctrl+C to copy them, and press Ctrl+Shift+V to paste them as values. You can then delete the original dataset if you wish.

Use the Static Paste Special Transpose Tool

Another way to transpose your dataset in Microsoft Excel is to use Paste Special.

This method only works with regular ranges, not Excel tables. To convert an Excel table to a regular range, select any cell in the table, and click “Convert To Range” in the Table Design tab.

Select all the data, including the column headers, and press Ctrl+C to copy it. Alternatively, right-click the selected array and click “Copy.”

Then, go to the cell where you want the transposed result to be displayed, and press Ctrl+Alt+V. On the other hand, click the “Paste” drop-down arrow in the Home tab on the ribbon, and select “Paste Special.”

Whichever route you take, the Paste Special dialog box appears. There, check “Transpose,” and click “OK.”

Now, the data is transposed as an independent array, meaning you can delete the original dataset without worrying about how that will affect the new one.

Transpose in the Power Query Editor

Even though Excel’s Power Query Editor has a tool dedicated to transposing data, there’s a better way to perform this data reshaping action. Indeed, using the Transpose tool in Power Query removes existing column headers, causing more problems than it solves. Instead, use the Pivot and Unpivot tools.

First, load the dataset in the Power Query editor by selecting one of the cells containing data and clicking “From Table/Range” in the Data tab on the ribbon.

If your data isn’t formatted as an Excel table, Excel will prompt you to fix this. This is because Power Query works better with formatted tables than with regular ranges.

Next, select the headers of all the columns you want to turn into rows, and click “Unpivot Columns” in the Transform tab.

Then, select the column you want to turn into the header row, and click “Pivot Column.”

Now, in the Pivot Column dialog box that appears, select “Value,” and click “OK.”

The original dataset is now successfully transposed, so you can click “Close And Load” in the Home tab to send the newly shaped dataset to a new worksheet in the existing workbook.

Because tables generated through Power Query are linked to the source data, you can click “Refresh” in the Query tab at any time to make sure it displays the most up-to-date information.

4

PIVOTBY: Group and Aggregate Your Figures

Availability: The PIVOTBY function is only available to those using Excel 2021 or later, Excel for Microsoft 365, Excel for the web, or the Excel mobile app.

Excel’s PIVOTBY function lets you group your figures without altering the source dataset. It produces similar results to PivotTables, but the key difference is that you determine all the parameters within the confines of a single function.

The PIVOTBY function has four required fields. These are used to tell Excel which variables you want to be displayed in rows, which variables you want to be displayed in columns, where to find the values, and how you want to aggregate your data:

=PIVOTBY(a,b,c,d)

where

• a are the cells containing the category or categories you want to appear as row headers down the left-hand side of your result,
• b are the cells containing the category or categories you want to appear as column headers across the top of your result,
• c are the values that will appear in the center of your result, according to the rows and columns you selected for arguments a and b, and
• d is the Excel function (or function you’ve created yourself through LAMBDA) that defines how the data will be aggregated.

The PIVOTBY function also has several optional arguments that let you specify whether you want to include headers and totals, sort your data, and specify additional parameters. However, to keep this example straightforward, I’ll use the required arguments only.

Let’s say you want to reshape this table (called Sports_Viewers) so that you can quickly analyze the number of viewers by sport and year.

To do this, in cell F1, type:

=PIVOTBY(Sports_Viewers(Sport),Sports_Viewers(Year),Sports_Viewers(Viewers),SUM)

where

• Sports_Viewers(Sport) is the column in the named table that contains the six different sports. These will be the row headers down the left-hand side of your result.
• Sports_Viewers(Year) represents the table’s Year column, and these will be the column headers along the top of the result.
• Sports_Viewers(Viewers) is the column showing the number of people who have watched those sports in those years, so this data will be the focal part of the result.
• SUM tells Excel you want the total columns to add the figures together.

Instead of typing the structured references manually, if you select the cells in the table using your mouse, Excel will input these for you.

When you press Enter, the result combines the totals for each sport in each year.

Because you’ve only used the mandatory arguments in the formula, by default, Excel automatically adds totals to both the rows and columns of the restructured data, and the result is sorted into alphabetical order by argument a.

You could go one step further and include a column that divides each sport into the four different regions. To do this, you’ll need to include both the sport and region in argument a:

=PIVOTBY(Sports_Viewers((Sport):(Region)),Sports_Viewers(Year),Sports_Viewers(Viewers),SUM)

To group non-adjacent columns into an argument, use the CHOOSECOLS function.

5

WRAPCOLS and WRAPROWS: Turn a One-Dimensional Column or Row Into a Two-Dimensional Array

Availability: The WRAPCOLS and WRAPROWS functions are only available to those using Excel for Microsoft 365, Excel for the web, or the Excel mobile app.

The WRAPCOLS and WRAPROWS functions in Excel are great for turning single columns or rows of data into a 2D array.

The syntaxes for these two functions are the same:

=WRAPCOLS(a,b,c)

and

=WRAPROWS(a,b,c)

where

• a (required) is the 1D range you want to wrap,
• b (required) is the maximum number of values in each column for WRAPCOLS, or the maximum number of values in each row for WRAPROWS, and
• c (optional) is the value to fill any leftover cells after the wrap has been performed. If omitted, leftover cells are filled with the #N/A error.

Suppose you want to divide this list of names into teams, and each team can only have a maximum of seven players. In other words, each column can contain no more than seven names.

To do this, go to a blank cell, and type:

=WRAPCOLS(A1:A48,7,"")

where

• A1:A48 is the 1D range containing the values you want to wrap into columns,
• 7 is the maximum number of values in each column, and
• “” tells Excel to return a blank in any leftover cells.

Notice how the order of the result is column-based. In other words, the first seven names are in the first column of the result, the next seven are in the second column, and so on.

You can then either manually type or use a formula to add team names above the resultant data. In this example, I’ve used SEQUENCE and COUNTA.

Now let’s imagine that, rather than defining the maximum number of players per team, you want to limit the number of teams overall. Specifically, you want to divide the players into nine teams, meaning each row can contain no more than nine names. To do this, type:

=WRAPROWS(A1:A48,9,"")

where

• A1:A48 is the range,
• 9 is the maximum number of values in each row, and
• “” tells Excel to return a blank in any leftover cells.

This time, the order of the result is row-based, meaning the first nine names run across the first row, the second nine run across the second row, and so on.

6

TOCOL and TOROW: Convert an Array Into a Single Column or Row

Availability: The TOCOL and TOROW functions are only available to those using Excel for Microsoft 365, Excel for the web, or the Excel mobile app.

The TOCOL and TOROW functions do the exact opposite of the WRAPCOLS and WRAPROWS functions—they turn a 2D array into a 1D column or row.

Their syntaxes are identical:

=TOCOL(a,b,c)

and

=TOCOL(a,b,c)

where

• a (required) is the array to convert,
• b (optional) is a number that tells Excel whether to keep all values (0—the default), ignore blanks (1), ignore errors (2), or ignore blanks and errors (3), and
• c (optional) is TRUE if you want Excel to order the result by column (otherwise, it orders the result by row by default).

Imagine you have a spreadsheet that lists the players in each team.

You now want to list these players in a single column. To do this, in a blank cell, type:

=TOCOL(A2:I7)

Notice how the above formula only includes argument a. This is because there’s no need to define what happens to blank cells (because they’re at the end of the array), and ordering by row (the default option for argument c) works well for this dataset.

On the other hand, to rearrange the same array into a single row, type:

=TOROW(A2:I7)

Again, I’ve omitted arguments b and c because, in this instance, they’re unnecessary, and there’s no need for me to overcomplicate the formula.

As with any function that returns an array, the TOCOL and TOROW formulas must be typed into a cell not formatted as an Excel table.

7

VSTACK: Stack Arrays on Top of Each Other

Availability: The VSTACK function is only available to those using Excel for Microsoft 365, Excel for the web, or the Excel mobile app.

Microsoft Excel’s VSTACK takes two or more arrays and stacks them on top of each other. It’s particularly handy for combining separate but related arrays into one dataset for easier analysis and comparison.

The syntax for the VSTACK function is very straightforward:

=VSTACK(a,b...)

where a, b, and so on are the individual arrays you want to stack on top of each other. The order in which you select the arrays is the order in which they’ll be stacked, with the first array stacked on top of the second, the second on top of the third, and so on.

This Excel worksheet contains the scores of each team across three leagues. Now that the season has finished, you want to use VSTACK to merge all the teams’ scores into one continuous array.

To do this, first, in a blank cell, type:

=VSTACK(

Then, select the arrays you want to stack, each separated by a comma. So, in this example, I’ll select cells A2 to A6, add a comma, then cells A9 to C13, then add a comma, then cells A16 to A20. Finally, I’ll close the parentheses, and press Enter to commit the formula.

=VSTACK(A2:C6,A9:C13,A16:C20)

Now, I can enter the column headers manually or copy them from one of the three arrays I’ve stacked.

The VSTACK function works well with other data-organization functions. For example, the whole VSTACK formula above can form the first argument of a SORT formula:

=SORT(VSTACK(A2:C6,A9:C13,A16:C20),3,-1)

where 3 indicates that the data is to be sorted by the third column (score), and -1 sorts that column in descending order.

You can also use the same principle with the HSTACK function, which, rather than stacking arrays on top of each other, stacks arrays next to each other.

---

In the process of reshaping your figures in Excel, take some time to make sure that your data is structured in the best way possible. For example, each column should be a consistent field, each row should be an individual record, every cell should contain only one data point, and always avoid having blank rows and columns in an array.