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

694
questions

**0**

votes

**1**answer

27 views

### Excel pivot table filter when data is missing

I'm creating an Excel sheet for complaints, each new complaint goes in a new row. I want to count how many times each complaint was answered compared to its deadline. I have a column which adds on the ...

**0**

votes

**0**answers

20 views

### Create Pivot Table in Excel (Office 365 Version on Mac) using data on Multiple Sheets without using Add-ins

I am using Excel 16.37 (Office 365 Version) on a Mac and I would like to create a pivot table using data from multiple sheets (preferably without using VBA Script).
Each sheet contains data for a ...

**0**

votes

**2**answers

37 views

### Create a pivot table depending of two dates

I have an Excel table with statistics on issues handled by my team:
|Date opened|Date closed|Description|...|
|-----------|-----------|-----------|---|
|2020-03-05 |2020-04-23 | foo |...|
|2020-...

**0**

votes

**1**answer

31 views

### Is there a way to filter-out the last item in an Excel PivotTable field using the context menu filter when grand totals are not visible in the table?

In a pivot table, you can right-click an item in a field, choose "Filter">"Hide Selected items" and that specific item, or selected items, will be filtered out of the view.
I find this very useful, ...

**0**

votes

**1**answer

37 views

### Automatically fill formula adjacent to pivottable on update

Create a column adjacent to a pivottable whose rows contain a formula.
Fill the formula down to the bottom of the pivottable.
Modify the pivottable source so that refreshing it changes the number of ...

**0**

votes

**0**answers

32 views

### Why is 1st January 2020 formatted strangely in Excel Pivot Table?

I have a pivot table report containing data for 1st-7th January 2020 with date being used as row names. In the raw data, the dates in the data themselves are all formatted like 01-JAN-2020, 02-JAN-...

**1**

vote

**2**answers

48 views

### Excel Pivot Table Formula

I have an excel sheet that contains data like that shown below. I have the pivot table that shows departmental time and percent and an external value of total cost.
How do I create a formula that ...

**0**

votes

**0**answers

19 views

### How do you refer to a specific column in a pivot table in a formula outside of the pivot table to find various summary stats for that column?

So I have a pivot table with different columns of numerical data. If I want to find summary statistics of one of the columns (i.e. average, standard deviation, etc.), how would I refer to it in a ...

**0**

votes

**0**answers

18 views

### How to move specific cells in a row from one worksheet to another based on Month in Month Due column

I have been googling for a while trying to find a solution for how to copy specific cells in a row from one sheet to another. I have only been finding ways to copy entire rows. I have a workbook and ...

**0**

votes

**1**answer

31 views

### Trying to find the largest value from the given data, per day and then try and map the largest data with date and time onto a graph

Scenario:
A machine is processing a number of items randomly throughout the day, every day i.e. 54x items are produced at 17:00 and then 207x items are processed at 18:00 etc (Screenshot provided)
...

**0**

votes

**0**answers

9 views

### Pivot Chart - space X-axis values evenly over a range

I have a number of completion values that range between 0% and 100%. I am using a Pivot Chart to represent these graphically, as shown below.
Is there any way I can get the x-axis values to space ...

**0**

votes

**0**answers

23 views

### Excel - MAX date problems

I have column A as bin #, column B as PO #, and column C as expiration date. Each bin has multiple PO #s, i.e column A has 40 cells as bin 1. I am trying to use a pivot table to show the max ...

**0**

votes

**0**answers

44 views

### Excel Pivot - How to apply banded rows or columns based on a higher level?

While working with an Excel pivot table that has 2 fields selected for columns, I can use the Banded Columns checkbox under Pivot Table Style Options to apply alternating colors to columns.
When ...

**0**

votes

**1**answer

30 views

### How to select the n th row in every field?

enter image description hereI have a pivot table with lots of rows and fields in it, so I want to select/copy the 1st row of every field
I want to select the 1st row(County) of every State.

**1**

vote

**2**answers

51 views

### Transforming a table into matrix in Excel

So I basically have something like the situation in the picture where I need to transform the table in A into a matrix like in B. The row/column headers are not necessary, just the Matrix. I would be ...

**0**

votes

**0**answers

16 views

### How to make a value field show the latest value in a pivot table

I have a table like this:
And would like a pivot table like this:
Except instead of showing the sum of amount group by Region in the value field, I want to show the latest value based on date. For ...

**0**

votes

**0**answers

45 views

### Create a pie chart for different items with filter

I need to create a pie chart for sold and not sold for 4 items. Items (pen, pencil, book, box) should be in filter. If I click any item my chart should show sold and not sold number.
data.csv:
Item,...

**0**

votes

**1**answer

19 views

### Pivot table column for % of Type Count

Let's say I have a pivot table with
Column A = Type
Column B = Total Count of All Types (Type A, Type B, etc.)
Column C = Total Count of All Types, Shown as % of Grand Total
I would a Column D that ...

**0**

votes

**0**answers

34 views

### How to add a row of data (from a 2x32 matrix) to a pivot table (generated from a 11x770 matrix)?

I have the following pivot table, which shows the maximum activities (values) of various radionuclides (columns) measured on various dates (rows).
Pivot Table
This pivot table was generated from an ...

**0**

votes

**0**answers

14 views

### PivotChart - Milestone baseline vs actual dates

Good afternoon,
I have an Excel data table with a large number of milestones listed. Against each there are baseline, forecast and actual dates.
I would like to chart these as 3 lines in Excel (...

**0**

votes

**0**answers

45 views

### Creating a training log for Excel

I've got a dataset which contains several fields. It's basically to say "this individual from this company has passed/failed/not started this course on this date".
Dataset example:
Because this data ...

**0**

votes

**0**answers

69 views

### Pivot Table column that is the difference of 2 columns in the previous row

I need to add a custom column to a PivotTable whose value is the difference between values under two columns in the previous row. I tried adding a Calculated Field but I don't know how to reference ...

**0**

votes

**0**answers

47 views

### excel pivot table - refer outside pivot table if else

My Excel spreadsheet looks like this
I created from this data pivot table:
I need to add another column "bonus" where value will be calculated on this criteria.
So result should be (but bonus ...

**0**

votes

**0**answers

30 views

### Excel Pivot table show column A value associated with MAX of column B

So I have the following data:
Group Name Units Ordered
G1 Amy 52
G1 Marc 108
G1 Antony 76
G2 James 52
G2 Hal 12
What I'd ...

**0**

votes

**0**answers

21 views

### Excel Pivot Table Query - Matching Data from 2 Seperate Tables

I am doing some work that has 2 sets of data from the same data range say 01/11/2018 - 31/10/2019.
First set of DATA lists me numerous row entries per day through the year listing an entry by channel ...

**0**

votes

**0**answers

25 views

### Excel Pivot Table update source table column name

I have a Pivot table with a table as a source data, some times i need to change the column name in the source data table which makes the Pivot Table lose this column. Is there any way to make the ...

**0**

votes

**1**answer

60 views

### Subtract constant from Excel PivotTable values

I have an Excel Pivot Table that aggregates Values via the Product summary function. I want to subtract 1 from all of the displayed values.
For example, if this is the pivot table containing Values =...

**0**

votes

**0**answers

32 views

### Maintaining weighted average proportions in Excel when there is null data

I am not sure how to recalculate weights in Excel, preferably in a PivotTable, when there is null data (i.e., survey administrators didn't write anything).
Please refer to this workbook, where I have ...

**1**

vote

**2**answers

32 views

### Identify multiple row instances in Pivot Table

There are some data in an excel file for which I have made a pivot table. In the attached example, there are some labels viz. Data 1 & Data 3 which have multiple values unlike the others which ...

**0**

votes

**1**answer

31 views

### Excel pivot table sort by values

I've done quite a bit of searching but cant find working instructions on how to sort a pivot table in Excel by a field that has been placed in the Values area.
The best I've been able to come up ...

**0**

votes

**1**answer

880 views

### Excel pivot table counts empty cells as having a value

I am baffled by this weird behavior of pivot tables.
Imagine an example: at my work we have companies and servers. I make Excel spreadsheets with various information like what is backed up and what is ...

**0**

votes

**0**answers

30 views

### Excel Pivot table only counting first column [duplicate]

The Issue
I'm trying to count instances of a number in each column. For a simgle column of data, this works fine, but when I add more columns, the count always reflects the first column.
Attached is ...

**0**

votes

**0**answers

20 views

### Excel pivot table: how to get grand average when counting text fields

My boss wants to have the average registrations for each countries for our courses.
I have four fields in my table : "Course", "registration-ID", "Country", "year".
In the pivot I created, I added "...

**0**

votes

**0**answers

66 views

### Pivot tables using external data source. Need asking for data source window to show up each time

I have a workbook that has multiple pivot tables driven by external data source from a separate workbook. The goal is to send the two files to other users to enter data and generate pivot tables. (I ...

**0**

votes

**0**answers

20 views

### Filling blank values in pivot table with most recent value until reaching a new value

I am attempting to turn a table of prices over time into a pivot table that can be used to create reports. My issue is that per our process, if a price remains unchanged on a certain date, it is not ...

**1**

vote

**3**answers

183 views

### Add cumulative counts to a pivot table in excel

I have data in excel that looks like this:
Shop | Division | Launch Year
-------+-------------+-------------
Shop 1 | Division 1 | 2005
Shop 2 | Division 1 | 2006
Shop A | Division 2 | 2005
...

**0**

votes

**0**answers

11 views

### Pivot table with Query shows 0

I have an excel 2010 document with 13 sheets, one for each month and one overview sheet. On the overview sheet, I have a pivot table cetralizing data from the other 12 sheets. The data for the pivot ...

**1**

vote

**0**answers

28 views

### Calculated Fields Looking at Separate Worksheet?

I'm trying to figure out calculated fields in Pivot Tables in Excel and I haven't really found a clear answer to how to reference another sheet within the calculated field. Basically, my setup has a ...

**0**

votes

**0**answers

664 views

### Creating Multiple Date Ranges in a Pivot Table

While I know its possible for a pivot table to create a date range to showcase values, is it possible to have 3 date ranges? I would like to have 3 columns with date ranges 1. 1/4/19 - 5/25/19 2. 5/26/...

**0**

votes

**1**answer

104 views

### What Excel function to categorize, sort or lookup values within a cell

I am trying to create a workbook (non-VBA) for collating materials required for student experiments. At the moment, I have a list of all the experiments and their materials. Below is a excerpt of the ...

**0**

votes

**1**answer

30 views

### Enrich source data for use in a pivot table

In Excel we use one worksheet, the SourceSheet where data from another system is copied into by my co-workers. The amount of rows can vary, the columns are always the same.
The data is used in ...

**0**

votes

**0**answers

31 views

### Office365 Excel 2016. Windows 10. How to make Pivot Table Chart only list rows where a specific field in the table has data

As the title says. Here's some screenshots if I'm being to vague:
Below is the table of my data that I make a Pivot table out of
Table
And here is the Pivot Table:
Pivot table
Now as you can see, ...

**0**

votes

**1**answer

23 views

### How to highlight the rows relating to the “Count Of” in a pivot table?

I have a table with values, and next to it a pivot table showing count of certain fields (same sheet).
I want to be able to click the field or count value on the pivot table (either number or field ...

**0**

votes

**0**answers

23 views

### Change Excel Slicer from “OR” to “AND”

I'm trying to graph the number of people who have a certain skill set, using a slicer. The problem that I'm running into is that the slicer tool, by default, will show me ALL of the records that ...

**0**

votes

**0**answers

22 views

### How to dynamically change grouped fields on Pivot Table

Using this guide I have managed to convert a long date to a separate Months, Days and Hours but when I change the months filter to a single month on the x-axis, the days filter still show all days of ...

**0**

votes

**2**answers

52 views

### Easiest method of displaying how many instances for each hour?

I have a call log which has thousands of calls in it. Each call is an entry in a CSV file which includes a datetime stamp.
What I would like to be able to do, is easily view how many calls there are ...

**0**

votes

**2**answers

45 views

### is this possible in Excel, with pivot table?

I have a data set that looks like this
Is it possible to get a single pivot table that do the summary like below, basically counting the No. of users who has replied Yes/No for Option A/B

**0**

votes

**0**answers

17 views

### Pivot Table (Data Model) cannot calculate formula

I have 2 tables that would want to be joint as pivot. I have put in data model and both of the table are in the pivot table. But when I want to calculate fields in Analzye>Fields,Items,Sets>Calculated ...

**0**

votes

**0**answers

25 views

### Pivot table, difference between “CATEGORIES” with same “VALUES”

I am trying to combine two separate pivot charts into one, showing the difference between the data.
Here is the first chart's data and field setup:
Second chart's data:
What I'm looking for is a ...

**1**

vote

**0**answers

13 views

### Add calculated field to Pivot Table where one of the formula inputs is the count of all records?

I can't figure out what formula I should use to create the following pivot table:
I have a table that contains tasks, assignees, and % complete for each task. I want to create a pivot table that ...