1
vote
1answer
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
0answers
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
1answer
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
0answers
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
0answers
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
0answers
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
1answer
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
2answers
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
1answer
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
1answer
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
1answer
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
1answer
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
1answer
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
2answers
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
1answer
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
1answer
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
1answer
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
1answer
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
2answers
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
3answers
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
0answers
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
2answers
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
2answers
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
3answers
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
1answer
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
1answer
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
3answers
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
2answers
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
1answer
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
1answer
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
2answers
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
1answer
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
1answer
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
0answers
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
2answers
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
1answer
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
1answer
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
1answer
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
2answers
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
1answer
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
2answers
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
5answers
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
1answer
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
4answers
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 ...