0
votes
1answer
13 views

Create an “inverse” pivot table

I have data in the following format Project Person1 Person2 Person3 A Joe Alice Bob B Bob Tom Joe C Alex Alice Tom D Bob Doug Susan How can I ...
2
votes
1answer
15 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 ------------------------------------------------------ ...
0
votes
1answer
20 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 my ...
0
votes
0answers
31 views

Updating multiple pivot tables with slicers

I have a worksheet with about 15 different pivot tables, about 80% of them with multiple slicers. I've added rows to the data source sheet, however when I try to update the data source on the pivot ...
0
votes
1answer
37 views

How can I use pivot table grouping by dates incremented by a day at a time?

I have some data which has been extracted from staff timesheets which provides number of hours worked by a member of staff on a given day. I need to summarise this information grouping by days but ...
0
votes
0answers
21 views

Difference Between Values in Two Filtered Tables

I have a pivot table with two variables and a filter containing two distinct options. I would like to take the absolute difference between the tables created by filtering for each of these two ...
0
votes
1answer
44 views

Adding calculated columns to Excel Pivot without details

Is there a way to add a calculated field that would apply to total column only, without adding unnecessary detail for each column item? Consider the following example. The data is as follows: Year ...
0
votes
0answers
13 views

Named Views for Excel Pivot Table?

While I'm an advanced Excel user and developer of addins, I apologize that I'm not very familiar with pivot tables. I have a simple pivot table worksheet based on a simple set of data in another ...
-1
votes
2answers
66 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 ...
1
vote
1answer
94 views

Excel Pivot Table - Average per day

I have a table of data Date Item Quantity Operator 01/01/2014 Item1 3 John 02/01/2014 Item1 7 Bob 02/01/2014 Item2 4 John 03/01/2014 Item1 2 ...
0
votes
1answer
53 views

Excel: How to extract group of the same rows and their values into columns?

How can I extract the second table from the first one? I need to extract those titles that have exactly three costs (in the following example: f1 and f3). I tried using pivot tables but could not ...
1
vote
1answer
59 views

Excel Error Bars From Pivot Table

When Excel generates Standard Error error bars, it takes into account the number of data points. If a chart was generated from a pivot table, does Excel use the number of points in the pivot table, 1, ...
1
vote
2answers
53 views

Count unique values in column B with respect to column A

I have two columns in Microsoft Excel, A and B. The input is displayed below A B ABC 1 ABC 1 ABC 2 ABC 2 ABC 2 DEF 1 DEF 3 DEF 3 CDE 1 CDE 2 CDE 3 CDE 4 I ...
0
votes
0answers
25 views

Reference a very large pivot table, multiple time, with complex formulas

I would like to use the data from a big pivot table I created to do more complex computation and pivot tables. I know I can reference a cell within my pivot table using the GETPIVOTDATA function, but ...
0
votes
0answers
30 views

How do I lock one slicer, in a workbook with many slicers?

I have a spreadsheet that I have created with many slicers and pivot tables to view data more easily. I would like to pass this to my clients, but I only want them to be able to view information ...
0
votes
1answer
22 views

Finding Highest Value Combination From Excel Pivot Table Filter

I'm not sure if I am using the correct technique for this so please forgive me if I am way off. I have a large range of data from previous email campaigns. I have categorised the campaigns with ...
0
votes
0answers
15 views

Average over all pivot rows in excel

Say I put into pivot a number of bananas eaten by the monkey, sliced by months and days. How to add a column containing average number of bananas eaten by the monkey over all period of time? ...
0
votes
3answers
48 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
34 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
39 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
0answers
36 views

Excel 2D-lookup on a PivotTable with changing coloumns and rows

I want to do a Vlookup on a 2D pivot table but neither the coloumns and rows of the pivot are fixed and are subject to change due to ever changing data source. How can I perform a accurate vlookup on ...
0
votes
1answer
125 views

How to force time series in pivot chart — force break in line

I have a housing transaction dataset that looks like this: geography | date | housing type | sq.ft. | sale price | price/sq.ft. I can make a pivot chart showing the change in, say, price/sq.ft. ...
1
vote
1answer
31 views

Excel Cross Referencing

Got an interesting problem that seemed trivial at first... I have a table in the following format: USER | QUESTION | ANSWER ======================== 1 | 1 | A 1 | 2 | B 1 | 3 ...
0
votes
0answers
31 views

Excel: can I create a pivot table that checks two columns for the same value?

Currently, I have an Excel worksheet with a table that contains ~5,000 rows. The structure works like this: The difficulty I'm having is that "Theme" and "Theme2" have the same possible values. So ...
0
votes
0answers
19 views

Displaying images from database in a pivot table

I've got a table with sales data on MySql server. I can connect to it from Excel and generate a pivot table just fine. One of the fields in the table contains URL to an image of a product. Is there a ...
0
votes
1answer
94 views

Treating multiple fields as a single pivot table row label and a value source for subtotals in Excel

I have a tabular extract from a DB that has the following fields from orders and order line items: order customer (PK1) order serial no (PK1) order detail 1 order detail 2 ... order line nr (PK2) ...
0
votes
1answer
341 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
142 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 ...
2
votes
1answer
57 views

Auto formatting new pivot rows to data bars

I have a pivot table that takes data from database and displays it as a data bars. Problem is, that the data is updated daily - every day new row is added so the pivot table is growing. Unfortunately ...
0
votes
0answers
72 views

Add custom field (hyperlink) to a Pivot Table

I've made a Pivot Table that has one column called Issue Id. I would like to add a column to this table with the following formula: =HYPERLINK(CONCATENATE("website.com/", 'Issue Id'), 'Issue Id') ...
0
votes
0answers
110 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 ...
0
votes
1answer
244 views

How can I use indirect references for a pivot table's data source?

(This question is mostly about Excel, but it's also relevant for LibreOffice Calc, so I welcome answers about either.) I have this sheet with many rows, some of which only become relevant at certain ...
2
votes
1answer
424 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
1k 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 ...
4
votes
1answer
863 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
429 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 ...
1
vote
1answer
32 views

How can I summarize cells based on date in other cell?

I have one Excel sheet with columns "project name", "project date", and "effort estimation". On a second sheet I want to summarize those projects according to the months of the year. So I have ...
0
votes
1answer
210 views

Can't apply value filter to excel pivot table without affecting subtotals - Subtotal filtered items greyed out

I have a pivot table containing a list of assemblies and the costs of the parts, with the subtotals equating the total cost of the assembly. I want to filter out the parts of negligible cost (say ...
0
votes
1answer
48 views

How can I in a pivot filter detail to show new accounts in current month with sales where all other months show no sales?

I have a pivot table which is huge with raw data has more than 800,000 lines. I have customers in the row labels and month or year in the column labels. Each month this pivot tablr grows adding on ...
2
votes
1answer
81 views

Excel 2013 table formatting

I currently have a table that looks like this: Company | 2013 Total | 2013 Gross $'s | 2013 Gross % | 2012 Total | etc.. | | | | | ...
0
votes
1answer
53 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 ...
1
vote
0answers
68 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 ...
1
vote
0answers
88 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
169 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
144 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 ...
1
vote
1answer
1k views

Sum two columns of a pivottable into a third column

excel file pivottable screenshot I have an Excel spreadsheet (above, with screenshot). There are three columns of data, A, B, and C. A fourth column, Combined, simply contains a formula that SUMs A ...
1
vote
1answer
870 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
305 views

“Reference is not valid” error when refreshing pivot table

I have a .xlsm file on a network drive (source.xlsm for example) with a table that serves as the source for pivot tables that live on various other files (localfile.xlsm for this example). When I try ...
0
votes
2answers
2k 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 ...
1
vote
0answers
42 views

Display value in associated row in pivot table [closed]

I need to display a value in the same row of data as my minimum value in a pivot table. For example, my pivot table looks like: Row Labels Min of 247 1950 255.42 1951 250.46 1952 ...