# Tagged Questions

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

13 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

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

**1**

vote

**0**answers

52 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

**0**answers

74 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

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

**1**

vote

**1**answer

883 views

### PivotTable — how to sum the result of a countif formula by group?

Current Setup
A table (Table1) that has the following fields:
Organization Name
Person Name
Several fields that users can put an "X" into (each represents a "module" that users would need access ...

**2**

votes

**2**answers

161 views

### Combine PivotTable with prices to calculate billings by person

I have the following: date, name, item, unit, quantity, deliverer, job in a pivot table using name and job in the row field; item in the column field and quantity as the value field.
My item's ...

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

**2**

votes

**1**answer

185 views

### Referenced values in a pivot table

I have a workbook that is being used by management to review data.
One sheet has a pivot table with some quite specific information.
The pivot table has multiple fields being pivoted in the rows. ...

**0**

votes

**1**answer

710 views

### Calculating a CAGR in Excel 2007 Pivot Table

I'm trying to calculate a compound average growth rate or CAGR in an Excel 2007 pivot table.
I am familiar with the CAGR formula: ((End Value / Start Value)^(1/N)) -1, where N is the number of ...

**0**

votes

**1**answer

619 views

### Conditionally formatting a cell in a pivot table based on a range of cells grouped by a Column Label

Within my pivot table I currently have cells conditionally formatted- Value=1, Green and Value=0, Yellow. I would like to conditionally format based upon all the values within a row label underneath a ...

**2**

votes

**1**answer

445 views

### Excel2007 Pivot Table: Get Percentage with respect to number of different values in a field

I have a pivot table based on the answers of N persons to M different questions.
Simple Example (in reality their might be more categories for answering than "yes" and "no" and there might also be ...

**2**

votes

**2**answers

7k views

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

**0**

votes

**1**answer

966 views

### Refreshing Pivot Table clears Values and Column Labels fields

When I attempt to refresh an Excel Pivot table (by right click .. Refresh), all of the Column Labels & Values fields disappear, leaving only Row Label fields and an empty Pivot Table. How can I ...

**0**

votes

**1**answer

234 views

### Excel Pivot table: Calculated field based on only the first row of a group

I've got the following data and pivot table:
The Total column in the pivot table is the sum of the following calculated field:
=start-TIME(7, 30, 0)
I know that this calculation is wrong for ...

**1**

vote

**1**answer

74 views

### Excel 2007: The empty look of a pivot table

How is it that in Excel 2007, an empty pivot table sometimes looks like this:
and sometimes it looks like this:
So what's the difference? How can I make it always look like the first one?

**2**

votes

**1**answer

5k views

### Excel pivot table: How to hide zero values

I've got two tables and two pivot tables for each of them respectively. In the first table, the duration of each task is calculated on a column called "duration", but in the second one, there is no ...

**1**

vote

**2**answers

1k views

### Excel pivot table: grand total of hours

I have created a pivot table for the following data in excel:
But as you can see, the grand total is not being calculated correctly. What should I do to fix it? My second question is, how could I ...

**2**

votes

**3**answers

2k views

### Can an Excel 2007 Pivot Table merge data from different tables like a database?

I want to analyze data with a pivot table in Excel 2007.
I tried to construct an easy example to explain my question below:
I have a table (Table2) with data sorted by Person.
I want to analyze ...

**1**

vote

**0**answers

386 views

### Excel Pivot Table - Change default font

Is it possible to change the default font for a Pivot Table?
We have a report automation module in our application that populates an Excel spreadsheet with a result set from an Oracle SQL query.
...

**4**

votes

**2**answers

649 views

### Counting “0” Stock Values Generated by Pivot Table

I have an inventory ledger. It looks like this:
Tran Type Txn Date Item Quantity
Opening Balance 6/30/12 Item1 4
Opening Balance 6/30/12 Item2 7
Shipping ...

**3**

votes

**2**answers

2k views

### How to combine multiple rows of data of a single customer in a cell as comma separated values?

I have a PIVOT table report like the one below:
Customer Name Category Date Met
-----------------------------------
Customer 1 Core 9
10
Customer 2 ...

**1**

vote

**3**answers

11k views

### Adding a cumulative sum column to a pivot table

I have a pivot table where I am representing a series of data across a number of categories as follows:
Sum of amount Area
Status Priority Project Department 1 ...

**1**

vote

**1**answer

435 views

### Excel 2007 pivot table where columns are custom values not directly in table (sort of)

Sorry for the slightly confusing title but I am not 100% how to phrase this one. So I have an excel sheet that has some survey results. There are 3 questions which can be answered from 1-5. I.e. 5 ...

**2**

votes

**1**answer

165 views

### Combining more than 10 tables data in to one table

In Excel 2003 I was able to combine more than 10 tables' data in one pivot table through "Multiple Consolidation Range". However I couldn't seem to find the same option to do it in Excel 2007. Any ...

**3**

votes

**3**answers

4k views

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

**0**

votes

**2**answers

1k views

### Insert rows into a protected data table

I have a protected sheet that I've created that has tables which has some columns that I would like protected. However, I need the end user to retain the ability to insert rows into the table. How do ...

**0**

votes

**1**answer

340 views

### Combine two or more tables into a third separate table

I have an excel workbook that has three pivot tables in it. What I am wanting to do is create a fourth table that combines the data from all three of the other tables.
Essentially I want to ...

**4**

votes

**1**answer

5k views

### Grouping dates by week in a Pivot Table

I am trying to create a Pivot table to report on data that comes from an Access Query. Basically the data that comes back is in the following format:
TradeDate StoreID DeptCode ReasonCode ...

**1**

vote

**2**answers

6k views

### Auto refresh pivot table on internal data change

I've a worksheet that has some data (sheet1) and another worksheet that has a pivot table(sheet2).
How do I make sure that when I update the data in sheet1 the sheet2 pivot table is updated?
a. How ...

**0**

votes

**1**answer

2k views

### Excel 2007: Move the connection of an existing pivot table from a data connection to an Excel table

I've created a workbook that has a pivot table in it that has the data sourced from an external connection.
Now I'm trying to expand the functionality of it, and want to switch it to an Excel table, ...

**1**

vote

**1**answer

2k views

### Excel Pivot: How can I show the % of the sub-total against the Grand total?

I have 15 colleagues who have answered 'numerous questions' over the years in a questionnaire. The questions used to be standardised and all colleagues had the same questions. Unfortunately standards ...

**1**

vote

**0**answers

525 views

### Pivot Tables: how can I stop my columns from disappearing if they are empty?

I've ticked 'preserve cell formatting on update', but if one of the columns doesn't have an entry then it disappears. Is there any way I can keep it from disappearing even if it's blank? I suspect not ...

**1**

vote

**2**answers

994 views

### Excel 2007 Pivot Tables: Overlapping issue hampers my summary sheet

I've created a Workbook that has 5 Pivot Tables (PT). I want to make a summary sheet that holds all these PT's, but when they expand the 'not allowed to overlap issue' causes me updating problems - ...

**1**

vote

**1**answer

807 views

### Excel 2007 Pivot Tables: Refreshing upsets my manual formatting

How can I stop a refresh on Pivot Tables from ruining my manually configured columns widths? I'd like to be able to make the columns fixed. The columns will always be the same ones, but the data ...

**0**

votes

**1**answer

530 views

### Pivot tables stopped working after upgrade to Office 2007

An excel document with several pivot and lookup tables that previously worked under Office XP and 2003 has stopped working after upgrading to office 2007 (and linked stuff doesn't update any more ...

**0**

votes

**1**answer

752 views

### Pivot tables: How can I total the subtotal?

My SUBTOTAL row shows Person A needs £115, Person D £234 and Person G £789, but how do I SUM those totals and get it to show the total on the same ROW?
The Rows are subscription names.
The Value ...

**2**

votes

**2**answers

2k views

### How best to manage my growing data in Excel?

This isn't a question about formulas or features in Excel. I'm debating the correct/best way to manage the growing amount of data 'I have to' manage in Excel (I produce PIVOT tables/reports for my ...

**1**

vote

**1**answer

13k views

### Combining multiple Excel 2007 tables into one PivotTable

I have two tables, in separate Excel worksheets in the same file, which I'd like to combine. Is this possible?
The tables look like so:
Project | Subproject | Hours
PAlpha | SPOne | 5
PAlpha ...

**1**

vote

**2**answers

899 views

### Pivot table - calculations across totals

I have a Pivot table in Excel, where I have two fields in the totals area (say, I calculate the Countof both A and B).
Is it possible to do calculations on these totals instead of just displaying ...

**4**

votes

**5**answers

29k views

### How can I add a calculated field to a pivot table that shows the difference between two series of data?

My underlying pivot table has the following columns - ProjectName, Type, Year, Budget. The data shows information for 2009 and 2010 for the same ProjectName and Type. I can pivot this to get a table ...

**1**

vote

**1**answer

675 views

### External data for pivot table

I need to use XML file (preferably not local, but pointed by URL) as data source for Excel 2007 pivot table.
But "Use external data source" radio button is disabled on the "Change pivot data source" ...

**4**

votes

**4**answers

1k views

### MSFT Excel pivot table links to external data

Question1: What is the best online forum to ask MSFT Excel questions of the following variety?
Question2: How can I link an excel pivot-table to a potentially changing source table without having to ...