# Tagged Questions

**0**

votes

**1**answer

13 views

### Create an “inverse” pivot table

I have data in the following format
Project Person1 Person2 Person3
A Joe Alice Bob
B Bob Tom Joe
C Alex Alice Tom
D Bob Doug Susan
How can I ...

**2**

votes

**1**answer

15 views

### Pivot table with two headers in MS Excel

I'd soo appreciate if someone could help.
My initial table looks like this:
Brand Period_1 Period_1 Period_2 Period_2
------------------------------------------------------
...

**0**

votes

**1**answer

20 views

### 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 ...

**0**

votes

**0**answers

31 views

### Updating multiple pivot tables with slicers

I have a worksheet with about 15 different pivot tables, about 80% of them with multiple slicers. I've added rows to the data source sheet, however when I try to update the data source on the pivot ...

**0**

votes

**1**answer

37 views

### How can I use pivot table grouping by dates incremented by a day at a time?

I have some data which has been extracted from staff timesheets which provides number of hours worked by a member of staff on a given day. I need to summarise this information grouping by days but ...

**0**

votes

**0**answers

21 views

### Difference Between Values in Two Filtered Tables

I have a pivot table with two variables and a filter containing two distinct options. I would like to take the absolute difference between the tables created by filtering for each of these two ...

**0**

votes

**1**answer

44 views

### Adding calculated columns to Excel Pivot without details

Is there a way to add a calculated field that would apply to total column only, without adding unnecessary detail for each column item?
Consider the following example.
The data is as follows:
Year ...

**0**

votes

**0**answers

13 views

### Named Views for Excel Pivot Table?

While I'm an advanced Excel user and developer of addins, I apologize that I'm not very familiar with pivot tables.
I have a simple pivot table worksheet based on a simple set of data in another ...

**-1**

votes

**2**answers

66 views

### Power Pivot - Average time per item

I'm trying to calculate on average, how long it takes to make each item.
Here is the data table:
Date Item Quantity Operator
01/01/2014 Item1 3 John
01/01/2014 Item2 5 ...

**1**

vote

**1**answer

94 views

### Excel Pivot Table - Average per day

I have a table of data
Date Item Quantity Operator
01/01/2014 Item1 3 John
02/01/2014 Item1 7 Bob
02/01/2014 Item2 4 John
03/01/2014 Item1 2 ...

**0**

votes

**1**answer

53 views

### Excel: How to extract group of the same rows and their values into columns?

How can I extract the second table from the first one? I need to extract those titles that have exactly three costs (in the following example: f1 and f3). I tried using pivot tables but could not ...

**1**

vote

**1**answer

59 views

### Excel Error Bars From Pivot Table

When Excel generates Standard Error error bars, it takes into account the number of data points. If a chart was generated from a pivot table, does Excel use the number of points in the pivot table, 1, ...

**1**

vote

**2**answers

53 views

### Count unique values in column B with respect to column A

I have two columns in Microsoft Excel, A and B. The input is displayed below
A B
ABC 1
ABC 1
ABC 2
ABC 2
ABC 2
DEF 1
DEF 3
DEF 3
CDE 1
CDE 2
CDE 3
CDE 4
I ...

**0**

votes

**0**answers

25 views

### Reference a very large pivot table, multiple time, with complex formulas

I would like to use the data from a big pivot table I created to do more complex computation and pivot tables. I know I can reference a cell within my pivot table using the GETPIVOTDATA function, but ...

**0**

votes

**0**answers

30 views

### How do I lock one slicer, in a workbook with many slicers?

I have a spreadsheet that I have created with many slicers and pivot tables to view data more easily.
I would like to pass this to my clients, but I only want them to be able to view information ...

**0**

votes

**1**answer

22 views

### Finding Highest Value Combination From Excel Pivot Table Filter

I'm not sure if I am using the correct technique for this so please forgive me if I am way off.
I have a large range of data from previous email campaigns. I have categorised the campaigns with ...

**0**

votes

**0**answers

15 views

### Average over all pivot rows in excel

Say I put into pivot a number of bananas eaten by the monkey, sliced by months and days.
How to add a column containing average number of bananas eaten by the monkey over all period of time?
...

**0**

votes

**3**answers

48 views

### In Excel PivotTables, how do I show items that occur in one category but not another?

Simplified version of my data:
item category number
apples fruit 7
orange fruit 2
tomato fruit 3
tomato vege 9
zuchini vege 2
tomato vege 17
...

**1**

vote

**1**answer

34 views

### How to remove one column of a calculated item in Pivot?

I have two columns. One with data from this year and the other with data from previous year. I now have added a calculated item in Pivot to display the % delta of these two years. Now when doing this ...

**0**

votes

**0**answers

39 views

### How to Group and Layout Pivot Table when Values is in the Row Labels Field?

I'm attempting to present large amounts of data in a report using a Pivot Table to allow for dynamic filtering/categorization. The data in the table is of the form:
Year Month Category Region ...

**0**

votes

**0**answers

36 views

### Excel 2D-lookup on a PivotTable with changing coloumns and rows

I want to do a Vlookup on a 2D pivot table but neither the coloumns and rows of the pivot are fixed and are subject to change due to ever changing data source. How can I perform a accurate vlookup on ...

**0**

votes

**1**answer

125 views

### How to force time series in pivot chart — force break in line

I have a housing transaction dataset that looks like this:
geography | date | housing type | sq.ft. | sale price | price/sq.ft.
I can make a pivot chart showing the change in, say, price/sq.ft. ...

**1**

vote

**1**answer

31 views

### Excel Cross Referencing

Got an interesting problem that seemed trivial at first...
I have a table in the following format:
USER | QUESTION | ANSWER
========================
1 | 1 | A
1 | 2 | B
1 | 3 ...

**0**

votes

**0**answers

31 views

### Excel: can I create a pivot table that checks two columns for the same value?

Currently, I have an Excel worksheet with a table that contains ~5,000 rows. The structure works like this:
The difficulty I'm having is that "Theme" and "Theme2" have the same possible values. So ...

**0**

votes

**0**answers

19 views

### Displaying images from database in a pivot table

I've got a table with sales data on MySql server. I can connect to it from Excel and generate a pivot table just fine. One of the fields in the table contains URL to an image of a product. Is there a ...

**0**

votes

**1**answer

94 views

### Treating multiple fields as a single pivot table row label and a value source for subtotals in Excel

I have a tabular extract from a DB that has the following fields from orders and order line items:
order customer (PK1)
order serial no (PK1)
order detail 1
order detail 2
...
order line nr (PK2)
...

**0**

votes

**1**answer

341 views

### How to change Excel Pivot table column to text format for vlookup purposes

I have a column in my data that is ID numbers, basically. Made a pivot table out of the data.
I'm trying to do a vlookup into the pivot that ignores the first number in the data like so:
...

**0**

votes

**1**answer

142 views

### Set default expand/colapse state on pivot tables

The Setup
I have a pivot table in tabular form pulling data from an Analysis Services Cube.
I want to calculate the number of days between two dates, but the setup will only allow me to pull in all ...

**2**

votes

**1**answer

57 views

### Auto formatting new pivot rows to data bars

I have a pivot table that takes data from database and displays it as a data bars. Problem is, that the data is updated daily - every day new row is added so the pivot table is growing. Unfortunately ...

**0**

votes

**0**answers

72 views

### Add custom field (hyperlink) to a Pivot Table

I've made a Pivot Table that has one column called Issue Id.
I would like to add a column to this table with the following formula:
=HYPERLINK(CONCATENATE("website.com/", 'Issue Id'), 'Issue Id')
...

**0**

votes

**0**answers

110 views

### Listing/grouping variables in pivot table field list

I have data with 60+ variables and I would like to group the variables into different collapsible fields in pivot table similar to having different tables in the pivot field list instead of having all ...

**0**

votes

**1**answer

244 views

### How can I use indirect references for a pivot table's data source?

(This question is mostly about Excel, but it's also relevant for LibreOffice Calc, so I welcome answers about either.)
I have this sheet with many rows, some of which only become relevant at certain ...

**2**

votes

**1**answer

424 views

### 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 ...

**1**

vote

**0**answers

1k views

### How to add custom columns to Pivot Table (similar to Grand Total)?

I have data which I pivot as Row Labels and Column Labels which gives me something like this:
And what I would like to do is add columns like Grand Total (i.e. which applies to all columns as a ...

**4**

votes

**1**answer

863 views

### How to Make a PivotTable out of “hierarchical” Excel spreadsheet data

I would like to create a PivotTable out of "hierarchical" data contained in an Excel 2010 worksheet. The data is hierarchical in the sense of a parent/child relationship in a database where, ...

**0**

votes

**1**answer

429 views

### 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 ...

**1**

vote

**1**answer

32 views

### How can I summarize cells based on date in other cell?

I have one Excel sheet with columns "project name", "project date", and "effort estimation".
On a second sheet I want to summarize those projects according to the months of the year.
So I have ...

**0**

votes

**1**answer

210 views

### Can't apply value filter to excel pivot table without affecting subtotals - Subtotal filtered items greyed out

I have a pivot table containing a list of assemblies and the costs of the parts, with the subtotals equating the total cost of the assembly. I want to filter out the parts of negligible cost (say ...

**0**

votes

**1**answer

48 views

### How can I in a pivot filter detail to show new accounts in current month with sales where all other months show no sales?

I have a pivot table which is huge with raw data has more than 800,000 lines. I have customers in the row labels and month or year in the column labels.
Each month this pivot tablr grows adding on ...

**2**

votes

**1**answer

81 views

### Excel 2013 table formatting

I currently have a table that looks like this:
Company | 2013 Total | 2013 Gross $'s | 2013 Gross % | 2012 Total | etc..
| | | | |
...

**0**

votes

**1**answer

53 views

### How to prevent all-filtered-out row label values from appearing in a pivot table?

I have a pivot table in MS Excel. For the same of example, suppose the basic data has 3 columns:
Name: A string.
CountMe valued either 0 or 1.
Year an integral number.
Tokens the data we're ...

**1**

vote

**0**answers

68 views

### Make Excel 2007 pivot tables default to Classic View

When I launch a new pivot table in Excel 2007 (.xlsx file format) the defaults appear to be:
New view (not Classic view)
Autofit column widths on update = on
Subtotals = on (top of group)
I end up ...

**1**

vote

**0**answers

88 views

### Using getpivot in excel to create flexible reports based on user inputs

I am trying to make a report in Excel that sits on a very large set of data (set up in proper pivot form). I want to be able to select a value for a few fields (or select a value from 1 field but ...

**1**

vote

**1**answer

169 views

### Excel pivot tables: reverse running total

Is it possible to create a reverse running total in an Excel 2010 pivot table?
Using the basic example below, I have a list of number of sales per month (B), along with a running total (C). Without ...

**1**

vote

**0**answers

144 views

### How do I find which Pivot Tables has an error in Excel 2010?

I'm receiving an error:
cannot open PivotTable source file ...XLSX SHEET...
while refreshing all data. I have many Pivot Tables, and I can't find that one with the error, while refreshing ...

**1**

vote

**1**answer

1k views

### Sum two columns of a pivottable into a third column

excel file
pivottable screenshot
I have an Excel spreadsheet (above, with screenshot). There are three columns of data, A, B, and C. A fourth column, Combined, simply contains a formula that SUMs A ...

**1**

vote

**1**answer

870 views

### How do I create a PivotChart from a subset of PivotTable data?

As part of a dashboard, I've created a PivotChart from a PivotTable in Excel 2010, but the PivotTable includes extra columns that are cluttering up the chart. But when I adjust the values list on the ...

**0**

votes

**0**answers

305 views

### “Reference is not valid” error when refreshing pivot table

I have a .xlsm file on a network drive (source.xlsm for example) with a table that serves as the source for pivot tables that live on various other files (localfile.xlsm for this example).
When I try ...

**0**

votes

**2**answers

2k views

### Count and percentage with Excel pivot table

I currently have three sets of columns:
Several columns with values of either true or false to indicate if a certain action has taken place.
"Helper" columns which interpret TRUE as 1 and FALSE as 0 ...

**1**

vote

**0**answers

42 views

### Display value in associated row in pivot table [closed]

I need to display a value in the same row of data as my minimum value in a pivot table.
For example, my pivot table looks like:
Row Labels Min of 247
1950 255.42
1951 250.46
1952 ...