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

832
questions

0
votes

0
answers

25
views

### MS Excel: manually inserted data sticking to dynamic data

Is there a way to have dynamic data along with manual data so that the manual input sticks / stays in the same row with the external input even after the source changes?
In other words,
Have a source ...

0
votes

0
answers

19
views

### Link two tables across two sheets with the same column

In one sheet I have the following data:
ID
DOB
Address
2343234
20/1/1990
3454324
11/12/1986
In another sheet I have more columns with the same key column ID
ID
Score 1
Score 2
2343234
67
33
...

-2
votes

1
answer

35
views

### Rows have a data type and a Y/N field - How to summarize how many Yes / No there are per data type? [closed]

I have a table that I want to analyze by data type, which is a field in my row entries. Each row also has one (actually multiple) boolean fields - I would now like to summarize, for each category, how ...

0
votes

1
answer

38
views

### How to add a cell reference to Date Filter in Excel Pivot Tables

I have a date in cell C3, I want the Pivot table to only show expenses from that day. I tried entering the cell reference in this field to create a custom filter but it does not work. I also tried ...

0
votes

0
answers

41
views

### Can't get Grand Average in place of Grand Total in Pivot Table

My question is exactly the same as this one, but, for some reason I don't get, the answer doesn't work (or I don't read it right). Maybe something changed since then.
Let's say I have this. I get a ...

0
votes

1
answer

39
views

### In a pivot table - how to summarize by specific value

Here is a table:
I want to create a pivot table that will show me for each name, how much percent of "A" the name have, for this example:
Dan 100%
Ron 40%
Dafna 14.29%
Can you explain me ...

0
votes

0
answers

28
views

### I would like to automatically filter a large dataset for reference elsewhere based on the filters selected in a PivotTable

I would like to take this dataset and put it into a pivot table where it will be sorted into the items I want to select. I then want that to update the table with the dataset so that I can reference ...

1
vote

0
answers

27
views

### Display only one column for one of the values in pivot table [closed]

In my pivot table there is one column labeled 'Week' and two values: 'Sales and 'KPI'. I would like to display 'Sales' for all weeks but show the 'KPI' only for the last week. Is there a way to ...

0
votes

0
answers

21
views

### Subtracting value from pivot table (only when the data is available)

The data in my pivot table changes every week. Sometimes the value for certain data will not be available during certain week and available in other week. For now I had to manually check the data and ...

0
votes

1
answer

57
views

### How to filter out the top 5 values of my data on my pivot table on a chart

I noticed that after filtering for the top 5 values, a blank one shows up and, when I go to filter out the blank, this doesn't work. My table and chart revert back to the original chart where all ...

0
votes

0
answers

32
views

### Pivot Table - Display empty rows

There are time pivot tables do not have a full set of data. As a result Microsoft ignores those rows. The example below doesn't display the rows 1,2,3.
I would like to have it display a "0" (...

0
votes

0
answers

47
views

### Microsoft Excel pivot table

I want same data type to be categorized in one line, for e.g PO should be in same line giving me a total count of 43, instead it differentiates the PO into 2, pls how can i fix this

0
votes

0
answers

33
views

### sharing excel dashboard with BI through whatsApp

i have an excel dashboard with a slicer and when I am trying to share the file it self through WhatsApp , it is making error about the connections and the Bi when the user try to click the slicer , is ...

0
votes

1
answer

120
views

### How do you merge cells in a pivot table?

I have an excel sheet where there is a header referring to 3 columns each.
Lets assume for instance this is "Pets owned"
Each row is the data for a person and we can look down the cells to ...

0
votes

0
answers

36
views

### Want to create a mega pivot table from data from a matrix with 11 items, and 4 possible answers

I am trying to analyse data from a survey. In my survey I asked a 'matrix' question, with 11 items, and users could give one of 4 possible answers for each item.
for example:
carrots - i like it | i ...

0
votes

0
answers

104
views

### How to average only the values of unique rows in a Pivot Table/Power Pivot

I have a data set that has three columns - a serial number, a category, and a price. Certain serial numbers are listed multiple times in the data set with different categories, but each one always has ...

0
votes

0
answers

180
views

### How to refer to the range of values of an Excel pivot table from a formula

I've got a pivot table with FieldR in Rows and FieldC in Columns, both with some values selected and others filtered out. I've got Sum of FieldV in Values, with values shown as % of Column Total.
I ...

0
votes

2
answers

189
views

### Pivot table in excel: get difference of two rows in original table as a value

I have a huge table that looks something like this:
Period
Retailer
Area
Section of people
Goods type
Metric 1: Quantity sold
q1
some dude
area1
1
doll
111
q2
some dude
area1
1
doll
100
q1
some ...

1
vote

2
answers

161
views

### How to paste all PowerPoint slides to Word?

I need to paste all PowerPoint slides into a table in Word, but each slide must be in a separate block?
Is this possible or must I copy and paste each slide on their own?

0
votes

0
answers

192
views

### Excel Pivot Table On Separate Row

Is there anyway I can make the Contract Value Covered it's own separate row instead of being a column?
enter image description here

1
vote

1
answer

111
views

### Excel Pivot Table showing same count as the first column

I'm a beginner at Excel and am currently practicing pivot tables.
The data that I'm dealing with is this table:
In the ROW Labels, I have added the field "Quiz and Assignment Mark Range" ...

0
votes

1
answer

127
views

### Joining a pivot table and a normal table to create a graph

I am having a hard time building dynamically a graph that results of joining together a pivot table summarizing some data, and a data field from another table, both sharing a common primary key/column....

0
votes

0
answers

42
views

### How can I apply conditional formatting to rows that get added to a pivot table when new data is added? Specifically on MacOS

The question is simple, and has been solved in multiple posts such as this one for PCs. Unfortunately on a Mac, that setting does not appear when editing the conditional formatting. This is how it ...

0
votes

1
answer

345
views

### Excel: Pivot Table with "% of Parent Row Total"

I would like to build a pivot table in Excel in which the columns group by date (month) and there is only one column which calculates the percentages of parent totals. Is there a way to do that ...

0
votes

0
answers

9
views

### How do I make a cells value equal the subtotal of a filterable column in a pivot table without including the autocalculated total value at the bottom

I'm trying to link a cell to display the subtotal of a column within a pivot table. I want to be able to filter using a slicer and have the number change along with the data, however it keeps adding ...

0
votes

0
answers

22
views

### Excel - populate pivottable axis dates for trendline

I have the following pivot table, showing products in the following level of details (LOD)
1- Year
2- name
3- type
The following picture shows them at LOD 1:
I want the chart to populate the missing ...

0
votes

0
answers

41
views

### Automatically split one Excel row into two for pivot tabling purposes

I have a series of accounting entries where each row has entries for multiple years. Think:
Entry # | 2021 | 2022 | 2023 | 2024
xxxx $1.50 $2.50 $3.50 $2.50
yyyy $1.50 $2.25 $7.50 ...

0
votes

0
answers

34
views

### Formula to identify where there is a value for '0' and '1' per date in Excel pivot table

I am analysing camera trap data looking at nocturnal denning sites. I have formatted my data so that I have a '0' value and an '1' per date when there is an animal entering and leaving a den site on ...

0
votes

0
answers

24
views

### How to apply separate conditional formatting rules for different Pivot Table Row Lable sections

I have a pivot table that has two sections, a High Priority and Normal Priority section that needs different sets of conditional formatting rules, due to the High Priority section having higher ...

0
votes

1
answer

198
views

### How to open nested tables from power query, in new sheets?

In excel, I have a power query which loads a table with nested tables. In the query editor window, I can click the Table and drill down which is great.
Problem is, when I do save and close, and return ...

0
votes

1
answer

102
views

### Create a hierarchy between tables following relationship

I am confused with pivot tables, and it's a recurring issue! Say I have put 2 tables in my data model (classes and students):
tables classes and students
My model is this one:
enter image description ...

0
votes

3
answers

315
views

### Using Excel to separate complex many-to-many relationships

I have a set of data in excel that follows the below format:
Table displaying data and its attributes/relationships
Image displaying relationships between High, Medium and Low level attributes
I need ...

0
votes

0
answers

94
views

### How to sum before average in a Pivot Table

I have a big report that contains the following columns:
timestamp,hostname,type,count
I have a line every 5 minutes for each node that represents the transaction the node managed, both of type "...

0
votes

1
answer

119
views

### How to create a sorted table summary of the count of a categorical column in Excel?

I have a dataset like:
Animal fruit
cat apple
dog grape
cat cherry
cat pear
dog cherry
horse melon
cat lemon
I would like to create a summary table counting each occurance ...

0
votes

0
answers

120
views

### How to add values under the same group in Pivot Table?

I have a Pivot table that has several fields as attached in the picture. I would like to add all the quantities under the same ID 1, I have tried to moved this field into "Values" are but I ...

3
votes

1
answer

394
views

### Filter Across Multiple Row Labels in Pivot Table

I want to do the following transformation in excel based on a bunch of sent/received messages:
Sender A
Sender B
Sender C
Receiver A
Receiver B
Receiver C
0
1
0
0
0
1
0
1
0
0
0
1
0
0
1
0
1
0
0
1
...

0
votes

0
answers

49
views

### Trying to make a find and update function for Excel

I tried to use a VBA code in SharePoint and it did not work. I also tried using the workaround of power automate desktop but my work does not allow the desktop version because of I.T protections...
My ...

0
votes

1
answer

54
views

### Different default sorting in pivot table in excel

I'm importing data from external source, and it usually goes like W1, W2, W3 .... W10, W11 and so on.
When I choose to make pivot table they get sorted like W1, W10, W11, W2 and so on.
Is there a way ...

1
vote

0
answers

110
views

### Excel Pivot Table - Calculated field returning "1" for all rows

Column C (Final Contracts) in My Pivot Table is a count of "Leads.LeadID" in My Data per User Name. Column I (Test) in My Pivot Table is a calculated field with the formula =count(Leads....

0
votes

1
answer

7k
views

### Combine Rows in Excel Using Pivot Table

I believe that I can do this in a PivotTable but I am having trouble figuring out exactly how to set it up. I have data in the following format:
Initial Condition
Event
Impacted Project
% Over ...

0
votes

1
answer

274
views

### Apply conditional formatting on the columns of a pivot table, where the values are shown as a percentage

My data includes the revenue from different products(around 6-7 products) marketed by different salespersons(around 5). I have a pivot table with the product labels in the row and sum of revenue of ...

0
votes

1
answer

348
views

### How to un-pivot data in a table on Excel for Mac

I've read the suggested results pertaining to un-pivoting a table using the method from a Jon Walkenbach article, but I'm on a Mac, and can't get the desired result.
My scenario is exactly the same as ...

0
votes

1
answer

327
views

### Create a box-and-whisker plot for (category, value) categorical data?

In MS Excel (and LO Calc), you can easily create a box-and-whisker plot if your data is arranged like so:
foo
bar
baz
1
78
634
4
56
449
9
34
315
16
21
809
and you'll get three boxes, one for ...

0
votes

0
answers

203
views

### Hide the 100% column when using the "Show value as % Of another base item" in Excel Pivot Chart

I'm doing some fairly light reporting in Excel and have run into a problem I can't seem to figure out on my own.
I have data in a format similar to this:
ID
Location
QuestionText
Value
1
AAL
Sick ...

1
vote

1
answer

492
views

### PivotTable Name Purpose

In the PivotTable Analyze tab, you are given the option to give a PivotTable a name, much like a regular table. However, unlike a regular table, this name may not be referenced in any formulas (such ...

1
vote

1
answer

28
views

### Reformatting table in Excel - Can PivotTables cut it?

Suppose I have a table in an Excel worksheet which looks like this:
Day
TimeOfDay
Color
Foo
1
Morning
Green
450
1
Morning
Blue
682
1
Evening
Green
253
1
Evening
Blue
413
2
Morning
Green
150
2
...

0
votes

0
answers

81
views

### Creating a technical excel data sheet to display efficient data

I am creating a table in excel that holds milestone achievement data with their respective amount. This table has multiple milestones that have only one amount, meaning that a set of milestone ...

0
votes

1
answer

87
views

### Every students' score plus their group score in Excel power pivot

There are students' scores based on their usual performance (scoreName), and group scores based on their group performance (scoreGroup). The total scores equal to their 'scoreName' plus their own ...

0
votes

0
answers

421
views

### #DIV/0! error in pivot table calculated field when values exist

The calculated field works fine in monthly total, but displays an error in the grand total.
It should show 7.8% from 373,908 divided by 4,778,270.

0
votes

1
answer

1k
views

### Excel Pivot Table: how to combine data from two tables?

Let's say we have two tables:
ID (FK) | Col1
1 | A
1 | B
2 | C
ID (PK) | Col2
1 | AA
2 | BB
I need to get following result in report (Pivot Table):
ID | Col2 | ...