A Pivot Table is a table of data that can pivot or rotate around a specific point in that data, whether it be a data type or category.
5
votes
1answer
1k views
How to filter source sheet in Excel pivot table drill down?
I record my expenses in an Excel spreadsheet. In a second sheet I have a pivot table that allows me to group my expenses by month and by category to see the totals. If I double click on a cell, a ...
5
votes
4answers
2k views
Median in Pivot Table in Excel 2010?
Astonishing that this functionality is not present in such an ancient application
Is there a known workaround?
I'm on about the part where you can change the aggregation type for a value field:
It ...
5
votes
2answers
847 views
Percentile of order in excel pivot-table
So I have a spreadsheet with orders that I have organized into a pivot-table that shows the right things etc.
Where there are currently column totals (summing all the rows before), I want to add ...
4
votes
2answers
987 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 ...
4
votes
2answers
390 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
3k views
Excel - PivotTable average of count
I need to create a PivotTable that will display the average of the count of rows for each item category/subcategory. To be specific, let's say I have the following list of fruit consumption:
Orange ...
3
votes
1answer
161 views
Excel Pivot Chart - historical Data Filter values stay visible?
I'm using Excel with a SQL data connection to generate a Pivot Chart.
The issue I'm having involves the data filters at the top of each column, allowing you to filter the visible values. Upon ...
3
votes
2answers
1k views
Grouping labels and concatenating their text values (like a pivot table)
I've got a spreadsheet with data like this:
Product | Attribute
----------+----------
Product A | Cyan
Product B | Cyan
Product C | Cyan
Product A | Magenta
Product C | Magenta
Product B | Yellow
...
3
votes
1answer
2k 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 ...
3
votes
2answers
2k views
Show (or filter) only positive grand total in pivot table
I created a Pivot table and have both negative and positive values in my Grand Total column.
My intention is to show only positive Grand Total values.
Thank you to all for your guidance.
EDIT by ...
3
votes
3answers
2k 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). ...
3
votes
5answers
20k 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 ...
3
votes
3answers
1k views
How to calculate a sum of cells based on a certain value in another cell
I'm responsible for creating the monthly metrics for the Helpdesk team that I'm on. I'm using Excel 2007. One report that I'm responsible for creating deals with tickets that we had to return to the ...
3
votes
1answer
451 views
Pivoting concurrent start and end times into timeslots with Excel 2010 PivotTable
I've got a table of processes and their respective start and end times (calculated from the start time and duration), listed by the process ID:
Run ID Duration Start Time End Time
200878 464 ...
2
votes
1answer
42 views
How to make a pivot table with two sets of columns, each filtered differently
Suppose I have the following Excel data:
+------+-------+--------+
| Item | User | Status |
+------+-------+--------+
| MTB | Bob | A |
| QCD | Bob | A |
| MTB | Alice | B |
| ...
2
votes
1answer
264 views
How do you format the date filter selection box for Excel Pivot Table
I created a PivotTable in Excel 2013 and used a date value as a filter. When I want to filter by a range I have to select the individual days. The option looks like:
1/1/2013
1/2/2013
1/3/2013
...
2
votes
3answers
1k views
Excel Pivot Table Date Range as Columns
I have the following data which I wish to pivot:
Student JoinDate ReleaseDate
John 01/05/2011 05/05/2011
James 02/05/2011 04/05/2011
I would like to create a pivot table which ...
2
votes
2answers
1k 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 ...
2
votes
1answer
57 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. ...
2
votes
2answers
895 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 ...
2
votes
1answer
718 views
In Excel, how to group data by date, and then do operations on the data?
I have Excel 2003. My data is like this:
01/10/2010 0.99
02/10/2010 1.49
02/10/2010 0.99
02/10/2010 0.99
02/10/2010 0.99
03/10/2010 1.49
03/10/2010 1.49
03/10/2010 0.99
etc. In fact it is a list of ...
2
votes
1answer
32 views
Grouping a field in one pivot tables groups the same field in another table
I've got a data sheet, which basically contains an amount and a date.
I've created several pivot tables, each on one sheet. I have given names to those tables, such as "tab_days", "tab_weeks", ...
2
votes
1answer
159 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 ...
2
votes
2answers
696 views
Excel pivot table - how to sort strings as numbers
My row data has numeric values as strings so of course it sorts as strings. How can I force it to sort as numbers? It seems I can't use a formula in the pivot table.
2
votes
1answer
1k 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 ...
2
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 ...
2
votes
1answer
1k views
What's the difference between PowerPivot and regular pivot tables?
I keep reading how PowerPivot lets Excel process "millions of rows" (!!). I'm already doing this with a regular old pivot table hooked up to a (relational) SQL database. What does PowerPivot do ...
2
votes
0answers
5 views
Excel formula return that pivot tables will handle like cleared cells?
What combination of techniques with cell formulas and pivot table configuration will enable the drill-down into the pivot table to only show rows containing the 'desireable' valued cells, ignoring the ...
2
votes
0answers
87 views
Excel 2003 Freezes When Worksheet with PivotTable Selected
All of the sudden, my Excel 2003 began an odd behavior today. Whenever I click on a worksheet tab that has a PivotTable on it, I become unable to click on any other tabs or on the menu with the ...
2
votes
0answers
213 views
Excel Pivot Table with external Data Source parameter is not saved with workbook
I have a pivot table in an excel 2007 workbook that links to an external data source. The query is along the lines of "exec procedure @param=?" (using an ODBC connection to sql server 2005). I am ...
2
votes
1answer
85 views
How can I create an Excel PivotTable to calculate the net position of trades?
Data worksheet tracking trades from one person to another:
+------+----+-----+
| From | To | Amt |
+------+----+-----+
| A | B | 10 |
+------+----+-----+
| B | C | 5 |
+------+----+-----+
...
2
votes
1answer
128 views
Percent of Hidden Values in PivotTable? Excel
I have a Percent of Parent Column field that works fine. But in the axis of values, I only want one shown to make the table much more compact. Essentially imagine if you had a table showing the ...
2
votes
0answers
130 views
How do I capture filter information from a DataPilot table?
In OpenOffice Calc, how do I capture filter information from a DataPilot table?
Specifically, I have a DataPilot table which I graph on a chart, filtering on a field not appearing in the table.
I ...
2
votes
1answer
445 views
Divide pivot table data by an arbitrary column in another table
I have this data from a pivot table:
Countries P1 P2 Total
Country 1 10 69
Country 2 36 2 92
Country 3 21 24 100
Country 4 22 77
Country 5 13 79
Country 6 12 1 ...
2
votes
1answer
1k views
How to calculate unweighted averages in Excel PivotTable?
I often make PivotTables in which each row contains a number of per-person average measures. I then want to look at the unweighted column average for each measure, and usually make some kind of chart ...
1
vote
1answer
61 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?
1
vote
2answers
6k 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
3answers
2k views
Microsoft Excel - Count the number of values each name has in a single column
I have a CSV file containing 100,000 rows.
Each row consists of information under a number of columns, such as "Date", "Page Accessed", "Page Type", "IP", "User Surname", "User Forename", "User ...
1
vote
2answers
8k views
Excel data source reference is not valid
I am trying to create a pivot from a range but keep getting the error Data source reference is not valid
I have Googled this and tried all of the following.
Select from Table instead from range.
...
1
vote
1answer
2k views
Excel PivotTable - Categories and Subcategories?
I'm trying to do a fairly simple pivot table on a range of person financial transactions. Each transaction is a row in the source table, and has the following columns: Type, Description, Amount, ...
1
vote
1answer
651 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 ...
1
vote
1answer
33 views
How to group daily data by month
Have a somewhat unusual Excel question:
I have a column of data that only has values of -1, 1, or 0:
| X |
| 1 |
| 0 |
|-1 |
| 1 |
| 0 |
| 1 |
I also have daily dates associated with each row, ...
1
vote
1answer
146 views
find the next due date after today within a group in an Excel PivotTable
I have got a table set up in one sheet with "transactions". Each row contains a name of a vendor, the amount owed or paid depending on transaction type, and the due date/transaction date. Here is some ...
1
vote
3answers
553 views
How to easily change columns into rows in Excel?
I have a huge Excel table that I need to transform into paragraphs for a Word report, and I can't find an efficient way to do it
The source looks like this:
And I would ultimately need something ...
1
vote
2answers
465 views
Can I group all rows with a percentage of column total less than 1% in an Excel 2010 pivot table?
This is how my data looks right now, based on hours of work logged per project in 2011:
Proj. Hrs %
A 15.6%
C 7.3&
...
X 6.1%
D 5.3%
Q 1.8%
F 1.6%
H 0.7%
Total 100%
...
1
vote
3answers
9k views
Excel 2007 | Remove blank fields from pivot tables
Every time I create a pivot table (available for all Excel versions) I get one or several blank fields. How can I get rid of them?
One workaround I used was to select the blank field, right click | ...
1
vote
1answer
77 views
pivotslicer connected to multiple pivotcharts
Can I connect my pivotslicer to multiple pivotcharts, but on different category levels, so If for example I have a categorytree that goes:
Department
--> Section
If I choose "Section A" Then I can ...
1
vote
1answer
64 views
Excel: How to position image dynamically in Excel?
Is there a way to display an image always at the bottom of the Pivot Table in Excel? Basically I am wondering if there is a way to create an image 'footer' for a pivot table.
1
vote
2answers
197 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 ...
1
vote
2answers
1k views
Excel 2010 Pivot Table: How can I expand/collapse a subset of records beneath a distinct set of records?
Consider the following set of data in a table called Employee:
Name Sup Q1Sales Q2Sales Q1Atte Q2Atte
John Joe Good Bad Bad Bad
Mary Joe Good Bad Bad Bad
Bob ...
