All Questions
Tagged with microsoft-excel-2007 pivot-table
56
questions
0
votes
1
answer
32
views
How to add a cell reference to Date Filter in Excel Pivot Tables
I have a date in cell C3, I want the Pivot table to only show expenses from that day. I tried entering the cell reference in this field to create a custom filter but it does not work. I also tried ...
- 23k
0
votes
2
answers
12k
views
How to get count in pivot table that does NOT count duplicate entries
I am setting up a pivot table, and I would like for it to give me the count of the number of unique combinations of two fields, not just the total count. That is, each combination should only be ...
CommunityBot
- 1
0
votes
1
answer
4k
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 ...
CommunityBot
- 1
73
votes
12
answers
138k
views
How to "unpivot" or "reverse pivot" in Excel?
I have data that looks like this:
Id | Loc1 | Loc2 | Loc3 | Loc4
---+------+------+------+-----
1 | NY | CA | TX | IL
2 | WA | OR | NH | RI
And I want to convert it to this:
Id | ...
- 175
0
votes
2
answers
18k
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 ...
- 1
0
votes
1
answer
618
views
Get count of categories present in a column in separate columns in pivot table in excel
I have a pivot table which looks like
Now I want to get count of each category in State column in a separate column. Something like this.
I tried creating a pivot table, but the format is not what I ...
- 473
1
vote
1
answer
6k
views
How to keep pivot table from expanding into occupied rows and columns
I have a pivot table with other calculations surrounding it. My issue is that if more row/column groupings are added to the pivot table, it extends and reaches the cells where these other calculations ...
CommunityBot
- 1
0
votes
1
answer
2k
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 ...
CommunityBot
- 1
1
vote
2
answers
2k
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 - ...
- 27.6k
12
votes
3
answers
173k
views
Have Pivot Chart show only some columns in pivot table
I have a data table. In the table I have some dollar amounts. I also have formulas that use lookups to group these dollar amounts into groups in the data itself.
I then have a pivot table where ...
- 21.6k
4
votes
5
answers
67k
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 ...
- 7,485
0
votes
1
answer
109
views
Update table starting from today's date in Excel
I have an excel worksheet similar to the following -
Col A - Clients name,
Col B - Col D - Cient's info such as address, Phone no etc and
Col E - Date of next meeting.
I want to create a table ...
- 9,264
0
votes
1
answer
1k
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 ...
- 7,485
3
votes
2
answers
4k
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 ...
CommunityBot
- 1
4
votes
3
answers
41k
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 ...
0
votes
1
answer
37k
views
Excel: How to Group Dates Per Week in Line Graph
I have data that looks like this:
Actual End Date ----CP
2016-04-22------------4
2016-04-26------------3
2016-04-26------------9
2016-04-22------------7
2016-04-26------------6
2016-04-26-------...
CommunityBot
- 1
0
votes
1
answer
2k
views
In an excel pivot table, how can I only select rows that has a particular value in it?
Here's the pivot table I have :
Mat Cust A B C D E F G H I J
1 P 0% 39% 18% 19% 0% 0% 0% 24% 0% 0%
2 Q 0% 52% 21% 19% 0% 0% 0% 7% 0% 0%
3 ...
- 3,651
0
votes
1
answer
1k
views
Pivot table with multiple, non hierarchical, columns
Let's say my source data looks like this:
Brand Model Color Region Sales
---------------------------------------
Ford Focus Black N/A 23
Ford Focus White N/A 2
...
- 426
8
votes
2
answers
42k
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). ...
- 1
5
votes
1
answer
41k
views
Pivot table on filtered data
Is there a way 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 filter on that.
Is ...
CommunityBot
- 1
10
votes
3
answers
65k
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 ...
- 2,908
1
vote
1
answer
734
views
Excel 2007 pivot table where columns are custom values not directly in table (sort of)
I have an Excel spreadsheet that has some survey results.There are 3 questions which can be answered on a scale of 1-5, i.e. 5 strongly agree - 1 strongly disagree. The columns are Gender and the ...
CommunityBot
- 1
2
votes
1
answer
498
views
How can I use Excel to monitor my payable?
Here's what I wanted to happen, I have five columns
Supplier Amount Payment Supplier1 Supplier2
When I have a receipt of my payable, I'll encode the name of supplier and it's amount. When ...
CommunityBot
- 1
0
votes
2
answers
337
views
Month by Month Pivot Table
I have a list of dates and data that go back a year. In Excel, I made a pivot table that shows all these dates. In the columns label under data filters I see a lot of weekly, monthly, yearly ...
- 156
1
vote
0
answers
1k
views
Filtering values on Excel Pivot Chart
I'm importing data into Excel Pivot Chart from SQL Server. I'm trying to filter out Values (decimal type on SQL) below a certain threshold.
For this, I click PivotChart filter and see that filter may ...
0
votes
1
answer
3k
views
Filter a pivot table based on the values in specific columns
I have a pivot table where there are multiple column labels:
group
subgroup
subsubgroup
status
The last label, status, has only two possible values, ACTIVE and DORMANT. I need to show only those ...
- 2,602
1
vote
1
answer
4k
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 to)
...
- 673
6
votes
4
answers
73k
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 ...
CommunityBot
- 1
2
votes
1
answer
190
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". I cannot find the option in Excel 2007. Any ideas?
- 1,826
2
votes
1
answer
13k
views
Pivot table with two headers in MS Excel
I'd soo appreciate if someone could help.
My initial table looks like this:
Brand Period_1 Period_1 Period_2 Period_2
------------------------------------------------------
...
- 23k
2
votes
3
answers
8k
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 ...
- 130k
-1
votes
2
answers
1k
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 ...
- 2,602
1
vote
1
answer
2k
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 ...
- 18.1k
2
votes
1
answer
2k
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. ...
- 23
1
vote
0
answers
276
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 ...
- 623
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 ...
CommunityBot
- 1
2
votes
1
answer
2k
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 ...
- 413
2
votes
2
answers
1k
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 ...
- 6,170
4
votes
4
answers
2k
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 ...
CommunityBot
- 1
1
vote
2
answers
19k
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 ...
- 230k
0
votes
1
answer
978
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 what I ...
- 6,170
1
vote
1
answer
108
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?
- 800
4
votes
2
answers
1k
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 ...
CommunityBot
- 1
1
vote
0
answers
2k
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.
...
2
votes
0
answers
802
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 ...
- 6,170
4
votes
1
answer
10k
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 ...
- 18.9k
0
votes
1
answer
649
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 ...
- 18.7k
0
votes
1
answer
367
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 ...
CommunityBot
- 1
2
votes
2
answers
8k
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 ...
- 41.3k
0
votes
1
answer
4k
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,596