# Tagged Questions

**-1**

votes

**0**answers

16 views

### How do I add a column in a pivot table?

I have a pivot table with a timescale, i.e. account names from top to bottom and months (jan to jun) from left to right. Now the last column gives me the sum of the 6 monthly figures which i need.
I ...

**0**

votes

**3**answers

37 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

15 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

16 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

**1**answer

12 views

### Filtering out (blank) within a PivotFiled from all pivottables in a workbook that have the same pivotfields

I have a bunch (14) of pivottables in two worksheets (7 in each worksheet).
I have a refresh button on the sheet that refreshes all pivottables using the following code:
Sub Refresh_Pivots()
Dim pt ...

**0**

votes

**1**answer

90 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

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

**0**

votes

**0**answers

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

**2**

votes

**1**answer

178 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

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

**2**

votes

**1**answer

336 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

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

**0**

votes

**1**answer

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

**0**

votes

**1**answer

27 views

### Getting #NUM! for outer nests of IF statement division

I have some sales figures for which I have values in pounds, units, and dollars. From PowerPivot, I'm creating a pivot with Periods columns (Current Week, MTD, QTD, YTD) and sub-columns of "Measure" ...

**2**

votes

**1**answer

21 views

### Group and Max Excel Table

I have a table like so;
Component | Time
1 | 2014-03-13 13:52:12
1 | 2013-05-20 12:20:20
2 | 2014-03-13 13:53:43
etc., etc.
There can be x times for a single component. ...

**1**

vote

**0**answers

76 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

119 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

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

**3**

votes

**2**answers

65 views

### Count first occurrence of one criterion based on a second criterion

I am trying to write an excel formula (or formulas) to count the first occurrence of a value in a given year for each Person in my table. I've searched for related questions on Superuser to no avail. ...

**0**

votes

**1**answer

96 views

### Getting average of data with multiple entries for a single date using Pivot table in Excel

I have tried this, but I am unsuccessful.
I have the data dump in an excel sheet (Excel 2010). The first column has month. The second has the dates. The third column has numerical data. I have applied ...

**0**

votes

**1**answer

94 views

### How to convert pivot results into continuous list of days?

I have flow measurements per 15 minutes. There are days with no data. I have used pivot table to get the daily average of flow. How can I convert pivot results into continuous list of days as ...

**1**

vote

**1**answer

368 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

53 views

### Filter excel pivot table using date range

I am exporting an MS Project file into an Excel spreadsheet. I would like to be able to pivot my tasks by assignee, by task etc.
I would like to be able to filter the tasks such that if the start ...

**0**

votes

**1**answer

276 views

### Calculating Annualized Turnover in a Pivot Table or PowerPivot

I'm trying to calculate employee turnover where the numerator is annualized only for the current incomplete year.
The closest I have gotten is TER/HC as a calculated Field in a pivot table. This ...

**1**

vote

**0**answers

212 views

### Pivot Tables - Filter Behavior, Limiting Filter Drop Downs Based on Previous Filters

I am working in excel 2010, and I have a question about pivot table filter capabilities / limitations. I tend to use complex, extensive pivot tables for data analysis/data accuracy on huge datasets, ...

**0**

votes

**1**answer

62 views

### How to delete days with no data?

I have the flow data for 49 years for 15 min interval. I've used pivot table to get the daily average value of flow.
After copy-pasting from pivot table and using filter to remove the blank flow ...

**0**

votes

**1**answer

79 views

### How to delete the headers in the Pivot Tables?

After creating the Pivot Table shown below
I want to get a continuous list of dates with the year included as shown below
In order to get the data organized as shown above, I have copied and ...

**0**

votes

**2**answers

1k 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 ...

**0**

votes

**0**answers

175 views

### How do I change dates in a pivot table using VBA?

How do I automate the checking/unchecking of dates in a pivot table?
I have a pivot table from which I need to extract data based on a date range (last seven days, previous seven days). The ...

**1**

vote

**1**answer

214 views

### Chart Categories: How To Combine X-Axis Categories

The Task
I'm putting together a dashboard wherein I'm building several charts. I've attempted to use a pivot chart (2D line chart) to pull data from two different pivots (with the same source data). ...

**0**

votes

**0**answers

138 views

### Excel VBA: Is it quicker to refresh all PivotTable caches than all PivotTables?

I'm new to all this VBA/macro stuff, and while I was looking for code to refresh all pivot tables in my workbook, I came across some information about PivotTable caches.
I have ~14 PivotTables in ...

**3**

votes

**0**answers

478 views

### Excel 2010 crashes: Excel.exe 0xc0000005 when trying to highlight from pivot table

One of our users uses pivot tables a lot. She often called me about Excel crashing from time to time without being able to reproduce the problem. Today we have found a way to reproduce the crash every ...

**0**

votes

**1**answer

182 views

### Creating pivot tables from multiple worksheets with power query, how to copy query for 20 workbooks?

I have 20 different workbooks, each with 12 sheets (Jan-Dec). I need to summarize certian data from each workbook, spliting quartly results.
It is a sales log for 20 locations, with each sales person ...

**0**

votes

**0**answers

34 views

### How do I specify a static set of rows when creating a pivot table in Excel?

I have a list of various types of expenses incurred at different locations. Each facility has a specific mix of expense types.
I have attempted to enable the "show items with no data" option on the ...

**3**

votes

**3**answers

5k views

### Excel 2010 PowerPivot: How to 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 ...

**0**

votes

**0**answers

81 views

### excel pivot table structure in 2007/2010

In Excel 2010 my pivot tables have the following structure:
Report filter (All)
(blank line)
Row Labels Average Value
(data)
In 2007, the same file looks like
Report filter (All)
...

**2**

votes

**0**answers

587 views

### Pivot table : absolute value of grand total in Excel

My grant total is generated by summing values of COLUMNS of ITEMS for a given SUBJECT (For each SUBJECT I have a list of ITEMS and I want to make a grand total of the values of these ITEMS). The ...

**0**

votes

**1**answer

28 views

### Is it possible to reference a values column in a calculated field

I've created a pivot table in excel that has two value fields. Both are SUMs of their target columns. I want to create a calculated field that divides the first value field by the second.
Is that ...

**2**

votes

**1**answer

257 views

### Excel 2010 Pivot table to calculate average profit/ loss

I want to calculate average profit/loss in Excel pivot table. Say the source data is in a Excel table and it looks like
Date Net income
1/1 5
1/1 -3
1/1 7
1/2 -10
1/2 4
My solution ...

**0**

votes

**0**answers

120 views

### Can I paste multiple filters to a pivot table?

Is there a way in Excel to paste in multiple items as pivot table filters? If I have a list of 15 codes out of a larger set of possible values, it's tedious and error-prone to scroll around to find ...

**0**

votes

**1**answer

146 views

### Pivot table on filtered data

There doesn't be a way in Excel to run a pivot table on an Excel sheet that already has columns filtered. Thus far, I've had to manually copy and paste the sheet into another sheet and then run a ...

**0**

votes

**2**answers

661 views

### Excel use columns in one table to make another table

I have an csv data dump which is a flattening of related tables from a sql database. So to simplify it looks like this.
| col1 | col2 | col3 | col4 |
+------+------+------+------+
| A | Ad | B ...

**2**

votes

**1**answer

250 views

### How to make a pivot table with two sets of columns, each filtered differently

Suppose I have the following Excel data:
+------+-------+--------+
| Item | User | Status |
+------+-------+--------+
| MTB | Bob | A |
| QCD | Bob | A |
| MTB | Alice | B |
| ...

**0**

votes

**1**answer

139 views

### Find Source File using Pivot table in Excel

I have a sheet with Pivot Table.
How do I find the source file?
If I double click on A Row, I get source file of that particular data.
But I require the Base Source File.
Please help

**1**

vote

**1**answer

476 views

### pivotslicer connected to multiple pivotcharts

Can I connect my pivotslicer to multiple pivotcharts, but on different category levels, so If for example I have a categorytree that goes:
Department
--> Section
If I choose "Section A" Then I can ...

**0**

votes

**1**answer

4k views

### How can I edit the legend in an excel pivot chart 2010

How can I edit the text of the legend in an Excel pivot chart in office 2010?
It is a workbook that only has a pivot chart with no pivot table.
Cheers

**1**

vote

**1**answer

72 views

### Pivottable selection using slicer, is there any way to get source data?

I am using a Slicer to filter data in a Pivot table.
If I double click in the Pivot table I get the source data for that row.
But I would like to get the source data for the entire Pivot table,
...

**0**

votes

**1**answer

2k views

### Adding 80% horizontal goal line to pivot chart

I have a curve pivot chart to which I'd like to add a goal line with a static 80% horizontal line.
I've seen some people suggest that one could merely add a column with 60% to the dataset. My dataset ...

**1**

vote

**1**answer

67 views

### Excel_macro_pivot table_analyzing data

I have two problems with my data in Excel.
I have this table, so for every day in the year I have got 24 values, that´s 365 columns.
I need to write a macro, that transform the data vertically, ...

**0**

votes

**1**answer

2k views

### Excel: How to hide/remove scrollbars from Pivot Table Slicers?

Slicers are a fantastic tool new to Excel from 2010, allowing you to quickly switch your selections when analysing data using a pivot table.
Sometimes there are too many options to fit in the slicer ...