# 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.

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.

747
questions

0
votes

1
answer

16
views

I have turned number of items to retain field to none and also tried the VBA macro in the link below. Neither worked and when I refresh my pivot table the filter still keeps items not being used. The ...

0
votes

1
answer

27
views

I want to create a pivot graph that shows the variation (%) in number of road accidents from year to year+1. I need it to be pivot because I want it to be refreshable if data change.
My data is a list ...

0
votes

3
answers

50
views

In an Excel 2016 pivot table:
Is there a keyboard shortcut we can use to expand or collapse all rows at a given level? For example, CTRL + click the plus sign to expand all rows?
I'm aware that it's ...

0
votes

1
answer

52
views

I have a table in Excel 2016 (source: Microsoft Query/ODBC):
Raw data:
Construction Type Location Sort
Reconstruction Main St - Red St to Blue St 1
...

0
votes

1
answer

32
views

I have a database that has got dirty data.
For example, the No. of days should be an integer, however people fill in doubles like 2.3 or 5.2...
My pivot table sample data from this database.
The ...

0
votes

0
answers

10
views

This is related to this question, but as I am a brand new user (yay) and cannot comment (unfortunately), I had to start a new question...
So, is there any way to get a % of column total using a ...

0
votes

1
answer

38
views

I had a Salary data month wise now i want to calculate the difference between last 2 months salary field wise
below is the sample data
Required Sample output of single employee.
I have tried using ...

0
votes

1
answer

24
views

I would like to convert this data from table to matrix format where the names in the dest column become columns and the number values corresponds to the total but the origin column stands still
origin ...

0
votes

1
answer

21
views

I have an excel document and inside this document, there are two sheets: 1. Actual Project Hours 2. Planned Project Hours. The actual project hours is broken down into projects, resources, hours taken ...

0
votes

1
answer

43
views

I am not sure if this type of question was posted here before because I myself am not sure how to ask it. So here goes, I have a massive three-dimensional dataset that looks somewhat like the table ...

1
vote

1
answer

30
views

I have a pivot table and values separated by a signal flag (0 or 1). I many different signal types (columns) to use.
Question: Instead of creating a new pivot table for each signal, is there a '...

0
votes

1
answer

30
views

After creating a pivot table based on ticker symbol on new sheet, I am getting first three entries (MON, MAR, NOV) that are not sorted when ascending sort option applied.

0
votes

1
answer

38
views

I'm trying to figure out some pivot table magic here, where I can have the top-level row-field be a custom formula for each category. So, for example, with data like this:
Edit: Providing in CSV ...

0
votes

1
answer

159
views

I’m creating an interactive dashboard containing pivot charts for different product sales at my work. The dashboard also contains a slicer that controls ALL charts in the dashboard since all are ...

0
votes

0
answers

6
views

Have a list of results in a pivot that want to shift them horizontally so I can insert them to the database. Basically I want this(See Image 1):
Image 1
To become this(See Image 2):
IMage2

0
votes

1
answer

17
views

I have 10x different large Excel tables, tracking technician competencies in this format:
I know that if I select the region, it won't be useful for Pivot data. Any suggestions on how to keep track of ...

0
votes

1
answer

20
views

From a list of contracts I want to learn which month the invoices are due. The invoice frequency is different per contract, ranging from monthly (12x) to quarterly (4x) and yearly (1x). Also the start ...

0
votes

0
answers

17
views

I inherited a staffing list, which I'd like to use as input for a pivot table.
The list is sorted by project and not by staff member, which would make my life a little easier. Previously a staffing ...

0
votes

0
answers

19
views

need assistance. I need to exclude 3 policies from a pivot table. Just from the FullPolicy column. I tried to hide it, but it freezes the page and gets stuck there. What is the easiest way?

0
votes

1
answer

22
views

I have some data that looks like this:
Item
Mass
CGx
CGy
CGz
Option
A123
10
4.2
0.1
0.5
AB01
C789
24
2.2
0.0
0.2
CD02
B456
12
3.8
0.0
0.7
AB01
D123
26
1.2
1.0
0.2
CD02
If I want to get the total ...

1
vote

0
answers

13
views

How can I group columns in Clustered column chart for year over year quarterly data(pivot table)? In the example, I wish to have 2016 and 2017 values for a particular month grouped together for proper ...

0
votes

1
answer

40
views

I have two tables of data in Excel that were exported from our HR system. One contains estimated/planned workhours, the other contains actual worked hours. Each line has a date, hours worked, and the ...

0
votes

1
answer

54
views

In a pivot table you can apply a conditional formatting to a group of value rather than to a cell or to the whole field thank to the "Applies to" option of the "Edit rule" window. ...

0
votes

1
answer

34
views

I am trying to utilise Pivot tables to pull together a list of items where a certain value appears in either of 2 columns (in the attached photo this is "Staff 1" and "Staff 2" ...

0
votes

0
answers

52
views

My Vlookup formula isn't subtracting the info it pulls from my pivot table and I can't figure out why.
I've run tests in other sheets and get the same results (works with some data but not others). I'...

0
votes

2
answers

145
views

I have a pivot table, see below. How can I filter this pivot table to where Sum of Not Compliant is not 0. I cannot add filters to any of the Sum columns in this take. The final column in this pivot ...

0
votes

1
answer

35
views

Microsoft Excel names pivot tables and provides means to rename it - 'PivotTable Name'.
The Microsoft Excel function
GETPIVOTDATA(data_field, pivot_table, [field1, item1], ...)
has a parameter '...

1
vote

0
answers

13
views

Suppose you have the following 2 tables from 2 different sources of data
Table 1
Table 2
I want to create a custom pivot table off these two tables, which will look like the following
Note that the ...

0
votes

0
answers

39
views

Suppose I have the following table from one data source
There are two types of row fields, one for region and another row below it for category. The sum of the categories is the total # of accounts ...

0
votes

1
answer

37
views

I have a pivot table as follows.
In Cell I4, the header is Row Labels, and there is a dropdown icon on the right, which is good.
Now, I would like to add such a dropdown icon in Cell J4 where the ...

0
votes

2
answers

103
views

Hi people better at Excel than me! I appreciate any help you can offer. I’ll try to be brief:
I have a 3-column table. Month (as 1,2,3,etc…), Salesperson Name, and Sales. Each row is how many $ in ...

0
votes

1
answer

58
views

I am trying add conditional formatting to the Rows section of a pivot table based on the results found in the values section of the pivot. I have already set the conditional formatting in the values ...

3
votes

1
answer

68
views

I wanted to change the Row Fields used in the Pivot Table, I was wondering if a slicer could do this.
Normally I would create two pivot tables along with two separate charts but in my actual dataset, ...

0
votes

0
answers

19
views

I have a pivot table which I have converted into a pie chart. The pie chart data is of managers and how many colleagues are assigned to them that given day.
I mark the colleagues as they enter the ...

0
votes

1
answer

87
views

My pivot table has a problem.
As you can see,
the first column has a heading of "Row Labels" (in cell A10).
I want it to say "Months".
Also, I'd like to have a horizontal line ...

0
votes

1
answer

128
views

I am struggling with an Excel PivotTable problem although I am not sure that a PivotTable is the best solution. I have a vertical table that has the format shown below, that I am trying to pivot to a ...

1
vote

0
answers

31
views

I am teaching a course in Microsoft Excel, using Excel 2019. As part of an exam in this course, I ask students to create pivot tables. I will then need to grade these exams.
Each student submits an ...

0
votes

0
answers

44
views

I created a pivot table a few days ago and cannot recreate the conditions in a new pivot table that should be similar. It is based off distance & time results from Google's Distance Matrix API, ...

0
votes

0
answers

24
views

I'm using Pivot Table and Slicer to filter data from a raw data source. Our employees work rotating shifts. Between those shifts there are overlap hours. I created a file to track their performance ...

0
votes

0
answers

79
views

In my Pivot table (columns - count) - I am trying to count where ever there are no blanks - these are strings (not numbers). So wherever there is a non-blank value - count - if only 100 cells have a ...

0
votes

1
answer

361
views

I am having a terrible time trying to display some key/value pairs in Excel as columns a specific way.
Note: I have found a way to accomplish this in Python so this question is mostly for the sake of ...

0
votes

0
answers

9
views

I have created a field on my pivot table to calculate the duration between my start date and end date columns using the Fields, Items and Sets button.
I now want to do some analysis on the calculated ...

0
votes

0
answers

117
views

My pivot table is sourcing data from a dynamic table where the number of rows can increase or decrease based on certain conditions.
As such, the range for my data source is something like $A:$M so ...

0
votes

1
answer

36
views

From a pivot table, I am attempting to take each row of data and format it transposed with the headers from the pivot table present in each instance. Here is a link to a Google Sheet that has a sample ...

0
votes

1
answer

125
views

I'm trying to create a pivot table that represents a parent-child assembly list. It works nearly perfectly except for one issue. Anywhere there is a blank, a full row with the very little info it has ...

0
votes

0
answers

12
views

Attached the sample where I want pivot table to show this sequence, but the date and text (Column C,E,F,G) doesn't populate appropriately at value field.
Raw data will have empty column at date column ...

0
votes

0
answers

39
views

I've created a macro that adds two sheets, each with a pivot table in cell A1 filtered by the current weeknum or current month.
The pivot table filtered by current weeknum displays the week number at ...

0
votes

1
answer

42
views

I have a load of data of the nature:
ID
Flavour
1
apple
1
orange
2
pear
2
orange
2
cucumber
3
apple
...
...
I need to identify the most common groups of flavours (ie. there are 36 IDs with ...

0
votes

2
answers

1k
views

I have a workday schedule in which I will count how many times I do each activity across the week. Here's an example:
The pivot table only allows me to count from MWF or TT but not from both without ...

0
votes

0
answers

252
views

I have a spreadsheet I've been building for a couple of months now. Here is a sample of it for testing:
https://docs.google.com/spreadsheets/d/1wj4T_05zOhPSdtNY4eDu17Y2pbCJgpS5PXPtJbviql0/edit?usp=...