Tagged Questions
2
votes
1answer
18 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
106 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
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
305 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 ...
2
votes
1answer
271 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
275 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
249 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
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
203 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 ...
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
149 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
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 ...
0
votes
3answers
322 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
145 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?
0
votes
1answer
481 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 ...
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 |
+------+----+-----+
...
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
...
1
vote
0answers
267 views
Transform two-dimensional tables to one-dimensional list in Excel
I am facing the task of querying about 20 parties for numerical data. I will do this via an Excel template. The Excel file is a table with a x (2...51) and a y (B...M) dimension. Data is to be filled ...
0
votes
0answers
247 views
Excel 2010 group cells pivot tables
I have a pivot table with data and date is the column label. I group it by month and year. In the table I have cells where the way of display is "% of difference" and the previous one. The year label ...
4
votes
2answers
392 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 ...
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 ...
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 ...
0
votes
1answer
3k views
How can I use multiple pivot tables on the same sheet without an overlap error?
I have multiple pivot tables on the same sheet. Since each and every one of them have a dependent size due to the data, it causes the error:
A pivot table can not overlap another pivot table.
Is ...
0
votes
1answer
217 views
Relocate filters on an Excel worksheet with a Pivot Table
In my worksheet where I have my pivot table I have many different filters to chose between.
To the eye it doesn't really look nice and I want to be able to maybe split that long list of filters into ...
0
votes
0answers
37 views
Showing the right form of total I want in a pivot table
I have a pivot table that shows how many condoms have been handed out and on how many distinct occasions.
So the value in the pivot table is a number between 1 and 30 (no. of condoms handed out at ...
0
votes
0answers
219 views
Excel 2010 Pivot Table joined columns
I'm doing a little pivot table worksheet over voter to help a family member running for office. Each voter record is a row in a source worksheet with columns "VUID" (voter ID), "Address", "Election ...
0
votes
2answers
646 views
Filter data in sheets from a master sheet
I have a 'master sheet' with lots of furniture data in it, in column A there are the suppliers names. What I would like is to be able to have my master sheet with all the info and then sub sheets ...
0
votes
1answer
302 views
Pivot Table grand total across columns
I'm using Excel 2010 and Power Pivot. I'm trying to calculate confidence and velocity for a development team. I'm extracting some information from our time and defect system each day and building a ...
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 ...
0
votes
1answer
189 views
Pivot table using comma separated cells
I need to mimic pivot tables for comma separated cells, and output into comma separated cells as well, grouping by country. I have about 100 rows of this data, so I cannot convert to column or rows. ...
0
votes
2answers
1k views
Pivot Table Sum Positive And Negative Numbers Regardless Of Sign in Row Label Range
I am using Excel 2010, Pivot Table feature.
I would like to sum up the range of agent total sales. The problem is some of the sales person has the credit note. It means it shows negative in the ...
