# Tagged Questions

**0**

votes

**1**answer

25 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

39 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

10 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

53 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

81 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

44 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

42 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

41 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

24 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

25 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

20 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

13 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

29 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

34 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

31 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

80 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

30 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

27 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

17 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

73 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

277 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

104 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

47 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

54 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

103 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

204 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

355 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

904 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

692 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

359 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

31 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

162 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

45 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

80 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

50 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

65 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

85 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

152 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

121 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

738 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

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

**0**

votes

**0**answers

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

**0**

votes

**0**answers

145 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

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

**3**

votes

**1**answer

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