-1
votes
0answers
16 views

How do I add a column in a pivot table?

I have a pivot table with a timescale, i.e. account names from top to bottom and months (jan to jun) from left to right. Now the last column gives me the sum of the 6 monthly figures which i need. I ...
0
votes
3answers
37 views

In Excel PivotTables, how do I show items that occur in one category but not another?

Simplified version of my data: item category number apples fruit 7 orange fruit 2 tomato fruit 3 tomato vege 9 zuchini vege 2 tomato vege 17 ...
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
16 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
12 views

Filtering out (blank) within a PivotFiled from all pivottables in a workbook that have the same pivotfields

I have a bunch (14) of pivottables in two worksheets (7 in each worksheet). I have a refresh button on the sheet that refreshes all pivottables using the following code: Sub Refresh_Pivots() Dim pt ...
0
votes
1answer
90 views

How to change Excel Pivot table column to text format for vlookup purposes

I have a column in my data that is ID numbers, basically. Made a pivot table out of the data. I'm trying to do a vlookup into the pivot that ignores the first number in the data like so: ...
0
votes
1answer
42 views

Set default expand/colapse state on pivot tables

The Setup I have a pivot table in tabular form pulling data from an Analysis Services Cube. I want to calculate the number of days between two dates, but the setup will only allow me to pull in all ...
0
votes
0answers
78 views

Listing/grouping variables in pivot table field list

I have data with 60+ variables and I would like to group the variables into different collapsible fields in pivot table similar to having different tables in the pivot field list instead of having all ...
2
votes
1answer
178 views

Excel 2010 Pivot Formatting - Can't indent and wrap fields?

When working with pivot tables in Excel 2010, I want to show my pivot in compact form, with each field being indented as you move through the data, like so: However, some of my data strings are ...
1
vote
0answers
241 views

How to add custom columns to Pivot Table (similar to Grand Total)?

I have data which I pivot as Row Labels and Column Labels which gives me something like this: And what I would like to do is add columns like Grand Total (i.e. which applies to all columns as a ...
2
votes
1answer
336 views

How to Make a PivotTable out of “hierarchical” Excel spreadsheet data

I would like to create a PivotTable out of "hierarchical" data contained in an Excel 2010 worksheet. The data is hierarchical in the sense of a parent/child relationship in a database where, ...
0
votes
1answer
162 views

Excel: How to clear all renamed PivotTable labels

When using PivotTables in Excel, it's often useful to overtype certain PivotTable labels with new names. If you have a long list of items you've relabelled, it can be easy to lose track of what the ...
0
votes
1answer
42 views

How to prevent all-filtered-out row label values from appearing in a pivot table?

I have a pivot table in MS Excel. For the same of example, suppose the basic data has 3 columns: Name: A string. CountMe valued either 0 or 1. Year an integral number. Tokens the data we're ...
0
votes
1answer
27 views

Getting #NUM! for outer nests of IF statement division

I have some sales figures for which I have values in pounds, units, and dollars. From PowerPivot, I'm creating a pivot with Periods columns (Current Week, MTD, QTD, YTD) and sub-columns of "Measure" ...
2
votes
1answer
21 views

Group and Max Excel Table

I have a table like so; Component | Time 1 | 2014-03-13 13:52:12 1 | 2013-05-20 12:20:20 2 | 2014-03-13 13:53:43 etc., etc. There can be x times for a single component. ...
1
vote
0answers
76 views

Using getpivot in excel to create flexible reports based on user inputs

I am trying to make a report in Excel that sits on a very large set of data (set up in proper pivot form). I want to be able to select a value for a few fields (or select a value from 1 field but ...
1
vote
1answer
119 views

Excel pivot tables: reverse running total

Is it possible to create a reverse running total in an Excel 2010 pivot table? Using the basic example below, I have a list of number of sales per month (B), along with a running total (C). Without ...
1
vote
0answers
78 views

How do I find which Pivot Tables has an error in Excel 2010?

I'm receiving an error: cannot open PivotTable source file ...XLSX SHEET... while refreshing all data. I have many Pivot Tables, and I can't find that one with the error, while refreshing ...
3
votes
2answers
65 views

Count first occurrence of one criterion based on a second criterion

I am trying to write an excel formula (or formulas) to count the first occurrence of a value in a given year for each Person in my table. I've searched for related questions on Superuser to no avail. ...
0
votes
1answer
96 views

Getting average of data with multiple entries for a single date using Pivot table in Excel

I have tried this, but I am unsuccessful. I have the data dump in an excel sheet (Excel 2010). The first column has month. The second has the dates. The third column has numerical data. I have applied ...
0
votes
1answer
94 views

How to convert pivot results into continuous list of days?

I have flow measurements per 15 minutes. There are days with no data. I have used pivot table to get the daily average of flow. How can I convert pivot results into continuous list of days as ...
1
vote
1answer
368 views

How do I create a PivotChart from a subset of PivotTable data?

As part of a dashboard, I've created a PivotChart from a PivotTable in Excel 2010, but the PivotTable includes extra columns that are cluttering up the chart. But when I adjust the values list on the ...
0
votes
0answers
53 views

Filter excel pivot table using date range

I am exporting an MS Project file into an Excel spreadsheet. I would like to be able to pivot my tasks by assignee, by task etc. I would like to be able to filter the tasks such that if the start ...
0
votes
1answer
276 views

Calculating Annualized Turnover in a Pivot Table or PowerPivot

I'm trying to calculate employee turnover where the numerator is annualized only for the current incomplete year. The closest I have gotten is TER/HC as a calculated Field in a pivot table. This ...
1
vote
0answers
212 views

Pivot Tables - Filter Behavior, Limiting Filter Drop Downs Based on Previous Filters

I am working in excel 2010, and I have a question about pivot table filter capabilities / limitations. I tend to use complex, extensive pivot tables for data analysis/data accuracy on huge datasets, ...
0
votes
1answer
62 views

How to delete days with no data?

I have the flow data for 49 years for 15 min interval. I've used pivot table to get the daily average value of flow. After copy-pasting from pivot table and using filter to remove the blank flow ...
0
votes
1answer
79 views

How to delete the headers in the Pivot Tables?

After creating the Pivot Table shown below I want to get a continuous list of dates with the year included as shown below In order to get the data organized as shown above, I have copied and ...
0
votes
2answers
1k views

Count and percentage with Excel pivot table

I currently have three sets of columns: Several columns with values of either true or false to indicate if a certain action has taken place. "Helper" columns which interpret TRUE as 1 and FALSE as 0 ...
0
votes
0answers
175 views

How do I change dates in a pivot table using VBA?

How do I automate the checking/unchecking of dates in a pivot table? I have a pivot table from which I need to extract data based on a date range (last seven days, previous seven days). The ...
1
vote
1answer
214 views

Chart Categories: How To Combine X-Axis Categories

The Task I'm putting together a dashboard wherein I'm building several charts. I've attempted to use a pivot chart (2D line chart) to pull data from two different pivots (with the same source data). ...
0
votes
0answers
138 views

Excel VBA: Is it quicker to refresh all PivotTable caches than all PivotTables?

I'm new to all this VBA/macro stuff, and while I was looking for code to refresh all pivot tables in my workbook, I came across some information about PivotTable caches. I have ~14 PivotTables in ...
3
votes
0answers
478 views

Excel 2010 crashes: Excel.exe 0xc0000005 when trying to highlight from pivot table

One of our users uses pivot tables a lot. She often called me about Excel crashing from time to time without being able to reproduce the problem. Today we have found a way to reproduce the crash every ...
0
votes
1answer
182 views

Creating pivot tables from multiple worksheets with power query, how to copy query for 20 workbooks?

I have 20 different workbooks, each with 12 sheets (Jan-Dec). I need to summarize certian data from each workbook, spliting quartly results. It is a sales log for 20 locations, with each sales person ...
0
votes
0answers
34 views

How do I specify a static set of rows when creating a pivot table in Excel?

I have a list of various types of expenses incurred at different locations. Each facility has a specific mix of expense types. I have attempted to enable the "show items with no data" option on the ...
3
votes
3answers
5k views

Excel 2010 PowerPivot: How to show items with no data?

In standard Excel pivot tables, there is an option for fields that allow you to force display of all items even if there are no results for your current selection. Here's the option: However, using ...
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) ...
2
votes
0answers
587 views

Pivot table : absolute value of grand total in Excel

My grant total is generated by summing values of COLUMNS of ITEMS for a given SUBJECT (For each SUBJECT I have a list of ITEMS and I want to make a grand total of the values of these ITEMS). The ...
0
votes
1answer
28 views

Is it possible to reference a values column in a calculated field

I've created a pivot table in excel that has two value fields. Both are SUMs of their target columns. I want to create a calculated field that divides the first value field by the second. Is that ...
2
votes
1answer
257 views

Excel 2010 Pivot table to calculate average profit/ loss

I want to calculate average profit/loss in Excel pivot table. Say the source data is in a Excel table and it looks like Date Net income 1/1 5 1/1 -3 1/1 7 1/2 -10 1/2 4 My solution ...
0
votes
0answers
120 views

Can I paste multiple filters to a pivot table?

Is there a way in Excel to paste in multiple items as pivot table filters? If I have a list of 15 codes out of a larger set of possible values, it's tedious and error-prone to scroll around to find ...
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 ...
0
votes
2answers
661 views

Excel use columns in one table to make another table

I have an csv data dump which is a flattening of related tables from a sql database. So to simplify it looks like this. | col1 | col2 | col3 | col4 | +------+------+------+------+ | A | Ad | B ...
2
votes
1answer
250 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 | | ...
0
votes
1answer
139 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
476 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
4k 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
72 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
2k 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
67 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
2k 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 ...