Tagged Questions

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.

learn more… | top users | synonyms (1)

0
votes
0answers
12 views

Sub Total previous 12 months and Current 12 months within a pivot table

I am a basic Pivot table user. I am trying to review current 12 months and previous 12 months. I would like to sub total or group the previous and current 12 months within the table. Can you ...
0
votes
1answer
65 views

How to get count in pivot table that does NOT count duplicate entries

I am setting up a pivot table, and I would like for it to give me the count of the number of unique combinations of two fields, not just the total count. That is, each combination should only be ...
1
vote
0answers
10 views

Pivot Table overlapping error

I have one Pivot Table (that used to have numerous PT's) in a sheet. When I try to refresh, 'A Pivot Table cannot overlap another Pivot Table' error appears. As there is only one PT in the sheet, ...
1
vote
1answer
62 views

Use data from a drop down list to populate table

I am trying to make an employee recognition tracking tool. I would like to have in sheet 1 a drop down list of all the employees in the hotel and a drop down list of the month. When an employee ...
1
vote
1answer
28 views

Is it possible to make a pivot chart a subset of pivot table?

I have a pivot table and pivot chart and they are both working fine. However, I have a couple of series in the pivot table that I don't want to show in the pivot chart. I also do not want to expand ...
0
votes
0answers
32 views

Calculated Field in Pivot Table - Reference Previous Value

I am trying to summarize data within a pivot table that was formerly calculated in excel. The format is much nicer and I believe it can be done, I just need some help. Overview of data: I have ...
0
votes
2answers
42 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
31 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
112 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
104 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
51 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 ...
2
votes
1answer
42 views

Can I use table relationships in formulas?

Are table relationships only for pivot tables & charts, or can they be used in formulas too? The introduction of tables made it easy to write really readable sumifs like this: ...
0
votes
0answers
24 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
61 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

Excel pivot table importing formula rather than value

I'm just starting to use pivot tables and I'm running into this simple issue. I have a load of data imported from a database. In one of the columns I use a lookup to convert a number into a string. ...
0
votes
1answer
375 views

Excel Pivot Table - How to preserve Filter and Sorting when Data get refreshed

I have a pivot table (Excel 2010). Every Time when I update the data and make a refresh, the sorting and filters of the Pivot Table get lost repsectively I need to explicitely to modify the filter ...
0
votes
0answers
18 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
139 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
169 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 ...
1
vote
1answer
118 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
118 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
85 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
1answer
126 views

Filtering the most recent date only in Excel 2013 Pivot Table

I have a pivot table which contains a list of customer. Under each customer is a list of dates, currently expanded. I only want to show the most recent date for each customer. Is there any way to ...
0
votes
0answers
28 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
58 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
32 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
19 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
55 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
48 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
52 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
50 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
328 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. ...
0
votes
1answer
45 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 ...
1
vote
1answer
41 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 ...
1
vote
1answer
257 views

calculated column in pivot table from power pivot

From SQL server I have a query that gives me daily transaction numbers for few companies per day, something like DATE Company Total Tax year 2013-04-24 ABC 50 2013 ...
0
votes
0answers
46 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
23 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
161 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
642 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
269 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 ...
3
votes
1answer
101 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
160 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') ...
1
vote
1answer
163 views

Listing/grouping variables in PowerPivot table field list

I have data with 60+ variables and I would like to group the variables into different collapsible fields in a pivot table, similar to having different tables in the pivot field list - ie, instead of ...
1
vote
0answers
133 views

Extracting and collating data from multiple columns in Excel 2003

First of all I have tried a lot but failed to get a solution for the following problem and I hence request all the superusers to help me with their best if possible.... I tried unpivoting but it did ...
0
votes
1answer
294 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 ...
3
votes
1answer
749 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
2k 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 ...
5
votes
1answer
1k 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
704 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
35 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 ...