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.

Filter by
Sorted by
Tagged with
0
votes
1answer
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
0answers
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
2answers
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
1answer
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
1answer
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
0answers
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
2answers
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
0answers
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
0answers
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
1answer
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
0answers
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
0answers
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
0answers
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
1answer
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
2answers
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
0answers
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
0answers
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
1answer
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
0answers
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
1answer
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
0answers
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
2answers
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
1answer
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
1answer
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
0answers
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
0answers
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
0answers
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
0answers
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
3answers
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
0answers
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
0answers
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
0answers
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
1answer
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
1answer
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
0answers
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
1answer
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
0answers
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
0answers
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
2answers
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
2answers
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
0answers
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
0answers
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
0answers
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 ...

1
2 3 4 5
14