# Questions tagged [pivot-table]

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.

**0**

votes

**1**answer

9 views

### Excel: Add 3rd “values” column to show percentage of one column against another

Please see attached - how would I get another column in to show the 3rd column as a percentage of the 2nd column?
I tried https://www.contextures.com/xlPivot10.html but couldn't see one that would ...

**0**

votes

**1**answer

12 views

### Grouping Date/Time by hour in pivot table not working as expected

I have one Excel table appended to another (over 1.1 million rows so can't fit all of the data on one sheet) via a query in excel. I am using this append query as the source for a pivot table.
When ...

**0**

votes

**0**answers

23 views

### Excel - Combining multiple columns into one field

I have massively simplified the data and changed the industry so I'm sorry if what I'm talking about sounds a little silly, the prices involved definitely make no sense.
The main function of my ...

**0**

votes

**0**answers

14 views

### Possible to group rows but keep originals in Microsoft Excel pivot tables?

I've created a pivot table with a number of states and their associated data; let's say the 50 US states. I'd like to group together certain of these states, but also have the original states rows ...

**0**

votes

**2**answers

57 views

### Pivot table cannot create calculated item

I've encountered a problem with pivot tables in Excel.
I want to create calculated item, but it does not allow me, as it says This Pivot report field is grouped
But it's not grouped in any way.
...

**0**

votes

**2**answers

46 views

### Filtering By Values in Excel Pivot Table

I have a spreadsheet containing columns for Timestamp, Company, UserID. Each row in the spreadsheet indicates that a given user from a particular company has performed an action.
I've created a Pivot ...

**0**

votes

**0**answers

47 views

### Displaying non heirarchal fields in pivot tables EXCEL [on hold]

I have three tables each with three column headings:
Table 1 column headings: "Name", "Permission", "Inv" ---
Table 2 column headings: "Name", "Permission", "lim" ---
Table 3 column headings: "...

**1**

vote

**0**answers

32 views

### Excel - Pivot table where multiple fields are seen as a single field

I have data similar to the below
Forename | Surname | ID | Role
-------------------------------
Jo | Bloggs | 1 | A
Jo | Bloggs | 1 | B
Jo | Bloggs | 1 | C
Mary | Bloggs |...

**0**

votes

**0**answers

27 views

### Create a measure to calculate a variable number of columns

I have an ODATA data source that, simplified, has the following columns:
Name
LifetimeBudget
FY2016Budget
FY2017Budget
...
FY2070Budget
I want to create a pivot table that will display ...

**0**

votes

**1**answer

20 views

### Difference between two averages in excel power pivot

I have the following data table in excel, showing how many apples i collected in the first 12 hours of the day, and how many oranges i collected in the last 12 hours of the day, for the 1st, 2nd and ...

**0**

votes

**0**answers

28 views

### How to use group table data by start and end dates

After unpivoting my table using Power Query, I have this table below.
My next objective is to calculate the task allocation for each person by month in a single pivot table.
However, the start and ...

**1**

vote

**1**answer

54 views

### Crosstab of every possible combination of columns in Excel or SPSS

For a research, I have the following dataset in Excel (simplified for illustration purposes):
Article | Boolean1 Boolean2 Boolean3 Boolean4|
------------------------------------------------...

**0**

votes

**0**answers

35 views

### How do I drill down by value in a pivot table?

I have a pivot table that sums a boolean column (so 1 = yes, 0 = no). I want to be able to double-click in a cell and get just those rows that have a value of 1 for the column. Instead, when I double-...

**0**

votes

**1**answer

23 views

### Pivot Table: group by date and show the date for each row listed

I'm able to group by date and then all the rows are, as expected, grouped by date. What I'd like is that for every row on which data is listed, it also shows the date associated with that entry - is ...

**1**

vote

**2**answers

187 views

### Excel chart formatting lost when Refresh All or individual Right Click on Data > Refresh

I have 4 pivot charts that rely on data that is refreshed from a connection.
When I click refresh all I lose all the formatting I set up (colours/borders/line and bar and 2nd axis choices)
I have ...

**0**

votes

**1**answer

81 views

### Excel: How to set up data for Feedback Analysis

I'd greatly appreciate any help on how to set up data correctly in Excel for this analysis (excel professional plus 2016). I'm hoping to be able to analyse feedback collected from training ...

**0**

votes

**0**answers

9 views

### Getting a multi-property item to display once in a pivot table per instance

So I'm trying to run a frequency analysis - how often does an item show up?
Items can show up in a few ways:
Type Make Color Count
1 A Blue 5
1 B Blue 3
2 ...

**0**

votes

**0**answers

31 views

### Excel Pivot table - Slicer with only unique features

I have a relatively big database (200 rows and 1000 columns), where one of the columns which is going to be used for filtering (through slicer), has multiple values (e.g., Reason 1, Reason 3) while ...

**0**

votes

**0**answers

9 views

### Use pivot table as a source for making excel tables

Can i use a pivot table as a source for creating a table in excel? If somehow i can create a table from pivot table, i can insert new fields in between the table and can perform some calculations ...

**0**

votes

**0**answers

20 views

### Adding fields in pivot table

I have a pivot table data of some materials dispatched to certain sites. I want to insert two columns in pivot table which calculates the percentage of dispatched quantity against total scope, and ...

**0**

votes

**0**answers

15 views

### Excel pivot table treating money as a string when filtering

I'm connecting to a data model in Excel and one of my dimensions is a decimal datatype - Excel seems to know this because it sorts it correctly when it's displayed in the pivot table. I can't find a ...

**0**

votes

**0**answers

27 views

### Excel: get row labels with range and frequency

Suppose I have a single column of whole numbers from 10 to 29. I want to create a pivot table that looks like this:
I'm having trouble getting these values, though. How can I get a range for each row ...

**0**

votes

**1**answer

224 views

### Excel pivot values column to Text

I have data in Excel pivot and my text data in value column shows number instead of Text . Any one can help me please. In below image of pivot value field red circled:

**0**

votes

**1**answer

27 views

### My excel is missing pivot table in the ribbon

Here is how my excel ribbon looks like:
How can I add pivot table to it?
Regards,

**1**

vote

**1**answer

60 views

### Combine none-unified data in excel based on row-id?

I have the need to combine different excel sheets of data into one single master file.
I have one master file with e-mail addresses and several excel-files from our CRM with data in different ...

**-1**

votes

**2**answers

71 views

### Can Excel Pivot Tables delimit data and perform calculations on the data?

I'm trying to create a document for work to track a lot of information and provide feedback based on the inputs. I have nearly everything I need complete, except the results that occur. I have ...

**0**

votes

**0**answers

42 views

### Can a subtotal Sum only unique values in a Pivot table?

I created a Pivot table in Excel. The field setting for the data's values is set to Max.
I would like the pivot table subtotal to Sum those Max values, but when I change the subtotal field setting to ...

**0**

votes

**0**answers

23 views

### Selecting multiple Excel “Tables” for creating a single pivot table

I have two large sheets in two separate workbooks - 500-600 rows and 8 columns - same headers. It is difficult to mark such a large and dynamic range. But the data is in Excel Table format. I could ...

**1**

vote

**0**answers

105 views

### How do I add custom, dynamic time period averages to Excel Pivot Table?

I have an Excel worksheet that has overtime (OT) data for each person in the company summarized per pay period. My objectives in Excel are as follows:
Show a current pay period summary of OT hours by ...

**1**

vote

**0**answers

109 views

### How to plot timeline chart with start and end date columns

I have a table like this:
| Resource | % | Start | End |
|-------------|-----|------------|------------|
| John | 0.1 | 10/1/2018 | 11/15/2018 |
| Paul | 0.2 | 8/20/...

**1**

vote

**1**answer

143 views

### How can I assign an asterisk to a cell which has been formatted as 'Currency' and is also used as raw data for a Pivot Table?

I have a spreadsheet full of raw data, where I have created a Pivot Table to help organise and manage said raw data.
For the purposes of this question, I would like to use the below image to help ...

**0**

votes

**0**answers

20 views

### PivotChart Expand/Collapse elements in the legend

I have a pivot chart (stacked bars) showing data about different Teams, grouped in Departments, evaluated over time (months/quarters/years).
I want to be able to see the data either of the single ...

**1**

vote

**1**answer

45 views

### Pivot Table won't show correct data relating to the row names

I'm trying to create a pivot table to tell me how many people are coming to different parts of a bachelor party. When I select the "Row Labels", I select that of "Coming Zorb" as they are the same ...

**0**

votes

**1**answer

35 views

### Excel Calculate Date Differences between Orders Per Customer

I have a data-set with Account Names, Order IDs and Close Dates. I would like to see the number of days between orders, per customer, however Excel will not allow me to sort by date in my Pivot Table ...

**0**

votes

**0**answers

52 views

### Is it possible to distribute between start and end date a quantity with Pivot Tables?

This is my first question here.
I have a bulk data sheet (in Excel) where the columns look more or less like this:
Task name - Task originator - Start Date - End Date - Hours
I would like to have ...

**0**

votes

**1**answer

171 views

### Unnest row labels from pivot table

My goal is to use the results of my pivot table as a raw data set for further manipulation, however I can't figure out how to easily break out my nested row labels and copy down the parent value for ...

**0**

votes

**0**answers

23 views

### Excel: In a Pivot Table filter the same value over multiple column

Is is possible in a Pivot Table to filter a value over multiple columns?
I tried advance filter but it is an ugly solution for me as it doesn't keep all the Pivot Table feature list total count, etc. ...

**0**

votes

**0**answers

70 views

### Pivot table not updating available columns from data model source

I have four tables in an Excel 2013 workbook generated from four different ODC files.
Using Power Query, I added all four tables to the Data Model and created a common list in a query. I created ...

**0**

votes

**1**answer

57 views

### Add vlookup multiplier to pivot table

Starting from a table like:
Stock Action Shares
AAPL BUY 100
AAPL BUY 200
MSFT BUY 100
MSFT BUY 200
MSFT SELL -100
and:
Stock Price
AAPL 105.5
MSFT 221.2
I'd like to ...

**0**

votes

**0**answers

136 views

### How to split comma-separated values for Pivot Table?

I have data which has the following format:
Electricity
Electricity, Cooling
Electricity, Heat
Electricity, Heat, Cooling
I want to use this data in a Pivot Table so I can count the number of items. ...

**0**

votes

**1**answer

747 views

### Can a Pivot table transpose values

Starting from a table like this:
Name Grade Subj Course
Dave 9 Math Algebra I
Dave 9 Eng Eng I
Dave 9 Sci Biology
Ed 10 Math Algebra 2
Ed ...

**1**

vote

**0**answers

148 views

### Pivot chart losing format after refresh

I have hundreds of pivot charts and every time i refresh the data i keep losing my data labels on the charts. I'm interested if it's possible to not lose the data labels or for a way to apply my chart ...

**0**

votes

**0**answers

35 views

### Displaying chart legend as a list

I'm trying to find a way in pivot charts to display the legend as a list. Depending on the current month data sometimes there are more/less entries with different data which completely ruins my legend ...

**0**

votes

**0**answers

13 views

### Pivot Table Consolidation - Max Number of references allowed

I was wondering how many max ranges or references to excel workbooks/sheets i can use for Pivot Table Consolidation?
I need to gather data for a whole financial year and I guess This excel command ...

**0**

votes

**0**answers

76 views

### How to Extract Unique Values From Multiple Columns With Pivot Table?

I have 2 columns in Excel. Some of the content repeats in each and vice versa.
e.g
A - 1 2 3 5 6
B - 1 2 4 5 7
I want to compare these two columns and make a new column or list with the unique ...

**0**

votes

**0**answers

44 views

### Pivot grouping disappears when workbook is closed & reopened

I have a workbook containing multiple pivot tables across different sheets.
I spent some time on grouping down these tickets into overall headers.
For example, the following rows:
Meeting 01/01/...

**0**

votes

**1**answer

54 views

### Microsoft Excel - Pivot Table

In an Excel pivot table,
I have September data with items A, B & C; the pivot table looks like below:
September Data
A 20
B 30
C 40
However for October data I only have items B &...

**0**

votes

**1**answer

40 views

### Excel Pivot - I don't want (blank) written in my empty cells

I have created a pivot from data which had many empty cells. After creating the pivot all those cells are displayed as "(blanks)". I want it to remain empty.
I found an answer here https://www....

**0**

votes

**0**answers

17 views

### Bug resolution time graph

I'm documenting the amount of time it takes for a bug to be resolved but displayed in histogram/binomial format to see what the average amount of time it takes to resolve a bug. I was using pivot ...

**0**

votes

**1**answer

46 views

### How to create a text based Crosstab (pivot) in Excel?

Suppose I have a list of issues that have the following:
Priority: 1-5 (how urgent something is)
Impact: 1-5 (how many people will be affected by the problem)
Desc: 15 chars (a very short description)...