5 Ways To Display Excel Pivot Table Rows Side By Side

When working with Excel pivot tables, it's common to want to compare data from different categories or time periods. By default, pivot tables display data in a hierarchical structure, with each category or time period stacked on top of the other. However, there are several ways to display Excel pivot table rows side by side, making it easier to compare data.

In this article, we'll explore five ways to display Excel pivot table rows side by side, including using the built-in pivot table features, formulas, and third-party add-ins.

Method 1: Using the PivotTable Options

The first method is to use the built-in pivot table options to display rows side by side. To do this:

1. Select the pivot table cell that you want to modify.
2. Go to the "Analyze" tab in the ribbon.
3. Click on the "Options" button in the "PivotTable" group.
4. In the "PivotTable Options" dialog box, click on the "Layout & Format" tab.
5. Under the "Layout" section, select the "Repeat All Item Labels" checkbox.
6. Click "OK" to apply the changes.

This will repeat all item labels in the pivot table, allowing you to display rows side by side.

Example

Suppose we have a pivot table that shows sales data by region and product. By default, the pivot table displays the data in a hierarchical structure, with each region stacked on top of the other.

Region	Product	Sales
North	Product A	100
North	Product B	200
South	Product A	50
South	Product B	150

By using the "Repeat All Item Labels" option, we can display the rows side by side, making it easier to compare sales data between regions.

Region	Product A	Product B
North	100	200
South	50	150

Method 2: Using Formulas

Another way to display Excel pivot table rows side by side is to use formulas. This method involves creating a new table that uses formulas to reference the pivot table data.

1. Create a new table next to the pivot table.
2. In the first row of the new table, enter the formula =A2 (assuming the pivot table is in column A).
3. In the second row of the new table, enter the formula =B2 (assuming the pivot table is in column B).
4. Copy the formulas down to the rest of the rows in the new table.

This will create a new table that displays the pivot table data side by side.

Example

Using the same example as above, we can create a new table that displays the sales data side by side.

Region	Product A	Product B
North	=A2	=B2
South	=A3	=B3

By copying the formulas down to the rest of the rows in the new table, we can display the sales data side by side.

Method 3: Using Power Query

Power Query is a powerful tool in Excel that allows you to manipulate and transform data. We can use Power Query to display Excel pivot table rows side by side.

1. Select the pivot table cell that you want to modify.
2. Go to the "Data" tab in the ribbon.
3. Click on the "From Table/Range" button in the "Get & Transform Data" group.
4. In the "Power Query Editor" window, select the "Transform" tab.
5. Click on the "Pivot" button in the "Transform" group.
6. In the "Pivot" dialog box, select the fields that you want to display side by side.
7. Click "OK" to apply the changes.

This will create a new table that displays the pivot table data side by side.

Example

Using the same example as above, we can use Power Query to display the sales data side by side.

Region	Product A	Product B
North	100	200
South	50	150

Method 4: Using Third-Party Add-ins

There are several third-party add-ins available that can help you display Excel pivot table rows side by side. These add-ins often provide a range of features and functions that can enhance your pivot table experience.

1. Search for "pivot table add-ins" in your favorite search engine.
2. Browse through the results and find an add-in that meets your needs.
3. Download and install the add-in.
4. Follow the instructions provided with the add-in to display your pivot table rows side by side.

Some popular third-party add-ins for pivot tables include Power Pivot, PivotPal, and OLAP PivotTable Extensions.

Example

Using the same example as above, we can use a third-party add-in to display the sales data side by side.

Region	Product A	Product B
North	100	200
South	50	150

Method 5: Using VBA Macros

VBA macros are a powerful tool in Excel that allows you to automate tasks and create custom solutions. We can use VBA macros to display Excel pivot table rows side by side.

1. Open the Visual Basic Editor by pressing "Alt + F11" or by navigating to "Developer" > "Visual Basic" in the ribbon.
2. In the Visual Basic Editor, insert a new module by clicking "Insert" > "Module".
3. Paste the following code into the module:

Sub DisplayRowsSideBySide()
    Dim pt As PivotTable
    Set pt = ActiveSheet.PivotTables(1)
    pt.ClearAllFilters
    pt.PivotFields("Region").Orientation = xlRowField
    pt.PivotFields("Product").Orientation = xlColumnField
End Sub

4. Run the macro by clicking "Run" > "DisplayRowsSideBySide" in the ribbon.

This will create a new table that displays the pivot table data side by side.

Example

Using the same example as above, we can use a VBA macro to display the sales data side by side.

Region	Product A	Product B
North	100	200
South	50	150

In conclusion, there are several ways to display Excel pivot table rows side by side. Whether you use the built-in pivot table options, formulas, Power Query, third-party add-ins, or VBA macros, you can create a table that displays your data in a clear and concise manner.

We hope this article has been helpful in showing you how to display Excel pivot table rows side by side. If you have any questions or need further assistance, please don't hesitate to ask.

Excel Pivot Table Image Gallery