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.
2
votes
1answer
21 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
1answer
33 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", ...
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, ...
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
60 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
22 views
Find Source File using Pivot table in Excel
I have a sheet with Pivot Table.
How do I find the source file?
If I double click on A Row, I get source file of that particular data.
But I require the Base Source File.
Please help
1
vote
1answer
78 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 ...
0
votes
1answer
107 views
How can I edit the legend in an excel pivot chart 2010
How can I edit the text of the legend in an Excel pivot chart in office 2010?
It is a workbook that only has a pivot chart with no pivot table.
Cheers
1
vote
1answer
41 views
Pivottable selection using slicer, is there any way to get source data?
I am using a Slicer to filter data in a Pivot table.
If I double click in the Pivot table I get the source data for that row.
But I would like to get the source data for the entire Pivot table,
...
0
votes
1answer
204 views
Adding 80% horizontal goal line to pivot chart
I have a curve pivot chart to which I'd like to add a goal line with a static 80% horizontal line.
I've seen some people suggest that one could merely add a column with 60% to the dataset. My dataset ...
1
vote
1answer
50 views
Excel_macro_pivot table_analyzing data
I have two problems with my data in Excel.
I have this table, so for every day in the year I have got 24 values, that´s 365 columns.
I need to write a macro, that transform the data vertically, ...
0
votes
1answer
29 views
Excel pivot diagram is way too wide
Every time I refresh my pivot diagram, the diagram expands and becomes as wide as four times the screen width.
How do I stop it from acting crazy?
0
votes
1answer
97 views
Single source worksheet, and multiple pivot tables using filtered data
How does one manage to create a workbook, where one worksheet is the master source data, but multiple pivot tables need to be created with the same set of "row labels" and "summed values", but ...
0
votes
1answer
122 views
Sort Excel Pivot Table By Month
I am trying to sort data in a pivot table. I am hoping to end up with a table that has row labels with my data types, and then 12 monthly columns with a count of each type in each column. So far I ...
0
votes
1answer
306 views
Excel: How to hide/remove scrollbars from Pivot Table Slicers?
Slicers are a fantastic tool new to Excel from 2010, allowing you to quickly switch your selections when analysing data using a pivot table.
Sometimes there are too many options to fit in the slicer ...
1
vote
0answers
54 views
Excel (pivot) diagram to connect dots in a curvediagram no matter if an entry is missing
I have this pivot diagram, and I want to make a nice curve diagram, but I have some different entry points. For instance I have an entry for 3 of my series on 2008-november, but not for the fourth, ...
1
vote
1answer
49 views
Pivot with mode of service time values
I have table like this:
ApplicationName ServiceName Time
Application Service1 1
Application Service1 1
Application Service1 1
Application Service1 ...
0
votes
1answer
217 views
Data in pivot table not sorting by date correctly
Why is the 1/5/2013 data at the top rather than the bottom of my pivot table? The data itself sorts correctly.
Sum of FTE Column Labels
Row Labels 162 163 166 168 Grand Total
...
0
votes
1answer
27 views
Blank Excel PivotChart
My Pivot Table has everything I want however,I want my chart to show how many Issues have the status of severe, mild, or moderate.
But instead it is blank.
What should I do?
2
votes
1answer
273 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
...
0
votes
1answer
277 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 ...
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.
0
votes
1answer
251 views
MS Excel 2010 - Multiple PivotTables using the same filter
I have the need to place multiple PivotTables on the same sheet in Excel that all come from the same dataset. I would like all of the Pivot Tables on the sheet to use the same "Page Filter". How can ...
0
votes
1answer
183 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 ...
0
votes
1answer
84 views
Creating Crosstabs in Excel
I have a survey asking about mobile device usage, but people have the option of checking more than one device...so folks may check iPad and Android phone, or folks can only check iPhone, etc.
I am ...
0
votes
1answer
160 views
Forecasting growth in pivot tables
I currently have the following (simplified) raw data:
Metric Value Year
Units 500 2009
Cost 1200 2009
Units 600 2010
Cost 1500 2010
Units 1000 2011
Cost 1600 ...
0
votes
2answers
204 views
pivot table / chart default sorting
What is default sorting for excel 2010 pivot table and charts I have a "Year month Week" column in my data sheet which I am using as row label, excel pivot table renders it arbitrarily
like in data ...
1
vote
0answers
195 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 ...
1
vote
2answers
2k 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
187 views
Pull Data from Pivot Table to Grid on Different Worksheet
I am trying to make an Excel Workbook that contains the statistics of an XBox Tournament that our office is doing. Winner of the whole Tournament wins the XBox! It's fairly simple rules. Everyone will ...
1
vote
1answer
151 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 ...
0
votes
1answer
271 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
vote
0answers
155 views
insert calculated field in pivot table based on previous row of one column
I want to create a calculated field BookCnt_Diff which would be row1-row2 and if RPT DT = grand total then 0.. this data is the result of a query which looks back 6 months so I want to be able to ...
-1
votes
1answer
379 views
'Invalid procedure call or argument' when adding a Pivot Table through VBA [closed]
I am getting an error when trying to add a Pivot Table using the following VBA code. The variables pivot_name and position are both Strings -
Debug.Print "Adding Pivot Table - "
Debug.Print " ...
0
votes
3answers
324 views
How may I retrieve data from an Excel table based on a variable number of criteria?
I have the following salary data for example:
Country State 2012 2013 -> 2027
======= ===== ==== ====
China Other 1000 1100
China Shanghai 1310 1400
China Tianjin ...
0
votes
1answer
146 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 ...
3
votes
1answer
162 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 ...
0
votes
0answers
239 views
Consolidation of multiple worksheets on PivotTable.. without using the wizard?
I have a 60mb Excel file containing around 2.1 million rows of information split across monthly worksheets (i.e. October contains 150,000 etc).
I can't amalgamate these in to a single worksheet ...
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?
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 ...
1
vote
2answers
199 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
2answers
905 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 ...
0
votes
1answer
484 views
“Cannot group that selection” error when creating pivot table
I have some data from my friend.
The first column is time, e.g. 01:02:59
The second is a number, e.g. 5
I want to create a pivot table and then group the time to be sorted by hours (between ...
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 ...
1
vote
0answers
229 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.
...
0
votes
1answer
231 views
Create % of unfiltered total in Excel
I'm trying to calculate a % of total from the unfiltered total rather than the filtered total. So I have four rows like this unfiltered:
Column1 Column2 Column3
------- ------- -------
Blah ...
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
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 ...
2
votes
0answers
215 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 |
+------+----+-----+
...
