Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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
1answer
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
1answer
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
0answers
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
0answers
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
2answers
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
2answers
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
0answers
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
0answers
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
0answers
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
1answer
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
0answers
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
1answer
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
0answers
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
1answer
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
2answers
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
1answer
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
1answer
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
1answer
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
1answer
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
2answers
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
0answers
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
0answers
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
0answers
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
0answers
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
1answer
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
0answers
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
1answer
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
1answer
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
0answers
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
1answer
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
0answers
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
0answers
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
1answer
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
0answers
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
1answer
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
1answer
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
1answer
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
0answers
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
1answer
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)...