0

I have a basic table - list of locations, x types of trees, with the value being the number of trees planted in each location.

Location Tree 1 Tree 2
A 123 456
B 789 012

What I want is for the locations and trees to be in the same column and the values in a second column.

Location Number
Loc. A
---Tree 1 123
---Tree 2 456
Loc. B
---Tree 1 789
---Tree 2 012
karl
  • 860
Smax
  • 1
  • 1
  • 1

2 Answers2

1

You can use Power Query to unpivot your existing data. Then save it as a Pivot Table Report, and enter the Location and Tree as Rows, and the Numbers as Values

If you make changes in your original data, a simple refresh of the query will refresh the Pivot table.

  • Select some cell in your original table
  • Data => Get&Transform => From Table/Range
  • Select the "Location" column (or a single cell in that column)
  • Transform => Unpivot other columns
  • Change the Column Headers to Tree and Number
  • Home => Close and Load => Pivot Table Report (and select either a new sheet or someplace on an existing sheet
  • Now just drag Location to Rows; Tree to Rows; Number to Columns
  • Rename the existing columns to taste
  • Under Pivot options, you can deselect to show subtotals and totals.

enter image description here

Ron Rosenfeld
  • 8,673
0

To do this with a PivotTable, you need to slightly tweak your data as follows:

data

Once the data is prepared, select the data -> Insert -> PivotTable. Enter Location and Tree in the Rows field and Number in the Values field, respectively. This gives the following output (you can easily turn off the sub- and grand total if not needed):

output

karl
  • 860