**0**

votes

**0**answers

3 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

32 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

30 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

41 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

44 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

27 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

19 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

22 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

46 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

24 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

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

**0**

votes

**1**answer

19 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

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

**0**

votes

**0**answers

17 views

### Why is my excel 2013 pivot table not sorting properly on % differnce

I have a pivot with the following:
Categories running down the rows
Columns with a count in for each category for each quarter last year, say number of jobs in each category for each quarter.
When ...

**1**

vote

**0**answers

45 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

59 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

**1**answer

49 views

### LibreOffice pivot: How to count 1 per line?

I have a list of fruit occurrences like this:
banana
banana
apple
orange
apple
And I want LibreOffice to give me a number of occurrences per fruit, for instance:
banana | 2
apple | 2
orange | 1
...

**1**

vote

**0**answers

51 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

60 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

67 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

55 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

237 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

91 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

33 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

157 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

148 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

**0**answers

154 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

**1**answer

59 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

63 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

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

**2**

votes

**0**answers

34 views

### Display value in associated row in pivot table

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

**0**

votes

**0**answers

162 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

79 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

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

**0**

votes

**0**answers

133 views

### Hiding a column from a pivot table without removing it from the chart

I have a pivot table with two columns: number of users who visited a website (impressions) and number of users who registered on the site (regs). The rows are for dates.
I want to visualize the ...

**2**

votes

**1**answer

1k views

### How to change Excel Pivot table “Report Filter”s values cell formatting

My Excel is in Finnish, but don't let that bother you...
First Report Filter "Kupi" has only number values in my source table, for example 643203, 3533, 253244, etc. How ever in Pivot's "Report ...

**3**

votes

**0**answers

378 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

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

**2**

votes

**0**answers

90 views

### How to sort time column by value instead of alphabetically

I'm creating a pivot table by connecting to an SSAS tabular model (Data -> From Other Sources -> From Analysis Services) .
The model has a "time" column that I want to sort by. The default (database) ...

**0**

votes

**0**answers

20 views

### Mircosoft access shows part of dataset as empty in pivot chart

i have a dataset which doesn't have any empty cells. But when creating a pivot chart of a query there is a column named empty (leer -german). The datasets within this column are missing in the other ...

**0**

votes

**0**answers

30 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

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

**1**

vote

**1**answer

64 views

### Calculate frequency across columns

I have meeting attendance data currently formatted as lists of ID numbers of the members present at each meeting:
9/21/13 | 9/22/13 | 9/23/13
123456 | 408437 | 123456
408437 | 349343 | 349343
...

**0**

votes

**0**answers

66 views

### Using variables to conditionally format Pivot Table in Excel 2007

I have data in a Pivot Table which I need to conditionally format. How can you do this using one column as a variant to determine the colour of the corresponding cell (e.g. Region and then Month, then ...

**0**

votes

**0**answers

67 views

### Excel 2013 Pivot Table Datasource without filename

I'm building a Excel spreadsheet containing some Pivot Table based on a table inside the document.
It's working file as long as the document path and filename is not modified.
However, if I copy ...

**0**

votes

**0**answers

79 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)
...

**0**

votes

**0**answers

96 views

### calculated field to a pivot table that shows the difference between data of different years and diffferent columns?

How can I add a calculated field to a pivot table that shows the difference between two series of data of different years and columns ?
My underlying data has project_name, Year, Actuals, Forecast .
...

**1**

vote

**1**answer

2k views

### Excel pivot table categorical variables the same in multiple columns (histogram)

I am relatively new to pivot tables, and there is one thing that I just can't figure out how to do.
I have a worksheet that has several columns of categorical data. Each column (Groups 1, 2, 3) has ...

**1**

vote

**1**answer

71 views

### Aggregating Columns In Excel

I have data consisting of a date and a list of purchases, something like this (commas
represent separate columns):
**date,product1,product2,product3**
08/11/13,oranges,apples,chips
...

**2**

votes

**0**answers

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