Super User Stack Exchange
Super User Stack Exchange

Questions tagged [pivot-table]

A Pivot Table is a table of data that can pivot or rotate around a specific point in that data, whether it be a data type or category.

Otherwise called crosstab, PivotTable, PowerPivot, DataPivot, DataPilot and other names. Pivot tables can combine data or provide summary views that may be powerful for analysis purposes. They may also allow drill-down from the summary to show details underlying a selected entry.

See the MS Office KB or wikiHow for Excel usage.

795 questions
73
votes
12 answers

How to "unpivot" or "reverse pivot" in Excel?

I have data that looks like this: Id | Loc1 | Loc2 | Loc3 | Loc4 ---+------+------+------+----- 1 | NY | CA | TX | IL 2 | WA | OR | NH | RI And I want to convert it to this: Id | LocNum | Loc ---+--------+---- 1 | 1 | NY 1 | …
devuxer
  • 3,951
  • 6
  • 31
  • 33
31
votes
3 answers

Grouping labels and concatenating their text values (like a pivot table)

I've got a spreadsheet with data like this: Product | Attribute ----------+---------- Product A | Cyan Product B | Cyan Product C | Cyan Product A | Magenta Product C | Magenta Product B | Yellow Product C | Yellow Product A | Black Product B |…
p0lar_bear
  • 589
  • 2
  • 6
  • 19
20
votes
7 answers

Median in Pivot Table in Excel 2010?

Astonishing that this functionality is not present in such an ancient application Is there a known workaround? I'm on about the part where you can change the aggregation type for a value field: It has sum, min, max, avg etc but not median
adolf garlic
  • 2,007
  • 12
  • 37
  • 49
17
votes
2 answers

LibreOffice pivot table group by month

Im having data like this: Date Customer Amount Month 09-01-12 A 20 =A2 (formatted so it shows month name) 10-01-12 B 10 =A3 (formatted so it shows month name) 18-01-12 B 25 etc 05-02-12 A 15…
Lennart
  • 277
  • 1
  • 2
  • 8
12
votes
4 answers

Excel 2010 PowerPivot: How do I show items with no data?

In standard Excel pivot tables, there is an option for fields that allow you to force display of all items even if there are no results for your current selection. Here's the option: However, using the PowerPivot add-in for Excel 2010 this option…
Andi Mohr
  • 4,225
  • 4
  • 32
  • 46
11
votes
3 answers

Have Pivot Chart show only some columns in pivot table

I have a data table. In the table I have some dollar amounts. I also have formulas that use lookups to group these dollar amounts into groups in the data itself. I then have a pivot table where my row field is the dollar group. And, in the…
GeoffDS
  • 311
  • 1
  • 4
  • 12
10
votes
3 answers

How to rotate text in axis category labels of Pivot Chart in Excel 2007?

I'm using a pivot chart in Excel to display data. My problem is: I can not influence the orientation of the text in the "axis" (don't know how to call it correctly, I'm sorry). As an example: In the following chart I'd like the text marked in yellow…
MostlyHarmless
  • 1,868
  • 7
  • 29
  • 49
8
votes
5 answers

Is it possible to copy & paste a pivot table in a spreadsheet?

I have a spreadsheet where I use some pivot tables to summarize and highlight some relevant data. Since I have to use multiple pivot tables, all with nearly the same data input but with slight differences from each other, I was thinking to create…
Sekhemty
  • 9,166
  • 18
  • 60
  • 97
8
votes
2 answers

How to preserve sort order in Excel Pivot-table?

I have a worksheet with data that is sorted in certain order (data is coming from external source, so there are four "ORDER BY" columns that create the desired sort order but are hidden on worksheet). However when pivot-table is created, it changes…
Meringros
  • 437
  • 5
  • 8
  • 16
7
votes
2 answers

Grand Average in place of Grand Total in Pivot Table

In a pivot table I want to change the Grand Total field to Grand Average. For example. Row Labels Day1 Day2 Day3 GrandTotal 1 10 20 30 60 2 15 25 35 75 3 20 30 40 …
gurpreet
  • 81
  • 1
  • 1
  • 2
7
votes
2 answers

Excel 2010 Pivot Formatting - Can't indent and wrap fields?

When working with pivot tables in Excel 2010, I want to show my pivot in compact form, with each field being indented as you move through the data, like so: However, some of my data strings are actually very long, and this significantly reduces the…
Gaffi
  • 428
  • 2
  • 6
  • 18
7
votes
6 answers

Excel: How to clear all renamed PivotTable labels

When using PivotTables in Excel, it's often useful to overtype certain PivotTable labels with new names. If you have a long list of items you've relabelled, it can be easy to lose track of what the original labels were. The only method I've found…
Andi Mohr
  • 4,225
  • 4
  • 32
  • 46
7
votes
2 answers

Show (or filter) only positive grand total in pivot table

I created a Pivot table and have both negative and positive values in my Grand Total column. My intention is to show only positive Grand Total values. Thank you to all for your guidance. EDIT by Doug: Here's the image of the source data:
Chris
  • 123
  • 2
  • 2
  • 7
7
votes
3 answers

How can I use multiple pivot tables on the same sheet without an overlap error?

I have multiple pivot tables on the same sheet. Since each and every one of them have a dependent size due to the data, it causes the error: A pivot table can not overlap another pivot table. Is there any smart way to get around this? I need them…
Maria
  • 91
  • 2
  • 2
  • 4
7
votes
2 answers

Percentile of order in excel pivot-table

So I have a spreadsheet with orders that I have organized into a pivot-table that shows the right things etc. Where there are currently column totals (summing all the rows before), I want to add something that says where the 20, 50, and 70th…
soandos
  • 24,206
  • 28
  • 102
  • 134
1
2 3
…
52 53