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
1 answer
4k views

Excel: Averaging 2 columns with a PivotTable

Data example: Year Activity L H 2019 x 5 9 2015 z 1 0 2015 x 5 4 2020 z 4 5 2020 y 7 3 Using a pivot table, I can average L by "Activity" and average by "Activity" looks ...
mechengr02's user avatar
1 vote
2 answers
2k views

Sql Group By with Max equivalent in Excel

My excel sheet has this data as an example: type |make |price -------------------- cars |honda |500 cars |acura |600 cars |ford |550 trucks |toyota |350 trucks |ford |475 trucks |...
boomhauer's user avatar
  • 145
0 votes
0 answers
78 views

Date error in GetPivotData in MS Excel

I have a row with dates, 2018-01-01, 2018-02-01 ... 2020-12-01, that I want to reference to, from a GETPIVOTDATA formula. I've managed to reference to another cells for Gross Profit, AM, Region, and ...
Filip's user avatar
  • 23
0 votes
1 answer
652 views

excel pivot table with different aggreagate functions

I have data set like GROUP, ELEMENT, TIME groupA, subA, .5 groupA, subB, .7 groupB, subA, .05 groupB, subB, .07 groupA, subA, .7 groupA, subB, .9 groupB, subA, .03 groupB, subB, .05 i want to create ...
vlad_tepesch's user avatar
1 vote
1 answer
136 views

Pivot table showing the Difference From a different column in the Previous year

If I have a pivot table from the below table... +------+-------+--------+-------------+ | Year | Fruit | Actual | Requirement | +------+-------+--------+-------------+ | 2020 | Apple | 10 | 11 ...
Alex's user avatar
  • 526
1 vote
2 answers
569 views

Adding End of year balance for year each on Pivot table

I am putting together a report about yearly expenses which keeps a running balance and I would like to show the end of year balance, with yearly expenses and debits for each year in a pivot table. I ...
kl344's user avatar
  • 11
0 votes
1 answer
452 views

Pivot table with multiple tables

I have what I think should be a fairly straightforward situation. I have a table like this: and I want to plot the counts of start dates and end dates per month, for every month in a sequence (but in ...
David Doria's user avatar
0 votes
1 answer
865 views

Using Excel to summarize Credit and Debit category columns and a single value column

I'm working on a personal worksheet to summarize my adventures on the altcoins universe. I would like to keep track of: how much real money I deposited on each exchange the current altcoin amount in ...
Rubens Farias's user avatar
0 votes
0 answers
124 views

How do I ensure an excel table that uses a foreign key is reordered fully when the primary table is reordered?

Data is in correct order, before first table is rearranged The initial state of the tables, where all of the data is in the correct order. Note how the Fruit names in the second table come straight ...
Swogget's user avatar
  • 101
0 votes
1 answer
587 views

Merging 1 Column with multiple rows per row label

I have a pivot table that has different columns. First column is Part Numbers, next column is part name. There are a random number of lines for each Part Number I copy and paste this into another ...
Brittany Braly-Konold's user avatar
0 votes
1 answer
443 views

Pivot table data filter showing unused dates

Normally when I create a pivot table and filter on a date field, it only shows me dates which are in my data - this is useful and relevant because my data might only come through weekly for example. I'...
James's user avatar
  • 121
0 votes
1 answer
1k views

Filter out or Hide all rows with blank aggregated column values

I'm looking for a way to filter out or hide all rows that contain a blank value for the aggregated COLUMN values. For example, I need a way to mass hide (through sorting the column values across the ...
geominded's user avatar
  • 101
0 votes
1 answer
52 views

Plotting combined column charts in Excel

I have data like as shown below in csv file for 3 terms such as A, B, C. I am trying to plot them using a column chart. Basically what my data indicates (ex: Terms A) is out of 200 items, 150 belongs ...
The Great's user avatar
  • 149
0 votes
1 answer
67 views

Grouping non-date data in a usable way for Excel Pivot Tables

I've got a large list of various department labels and associated data, and am wondering how best to consolidate this down to something simpler for reporting. An excerpt of the data is in the below ...
David Metcalfe's user avatar
0 votes
1 answer
488 views

Excel PivotTable Date Grouping in 24-hr Center

I am attempting to analyze data of agents working in my company that runs 24/7. I was going to construct a pivot table to show the daily operations of each individual. This fit most of my needs ...
Brian Silva's user avatar
0 votes
1 answer
1k views

Grouping dates by week in an Excel pivot table which has distinct count

My raw data looks like this: raw_data I want to group the dates by week, showing the number of distinct shows viewed by each viewer every week. The data should look like this. ExpectedOutput I've ...
Scarlett Louis's user avatar
0 votes
1 answer
76 views

Excel Compute LTV Lifetime Value

I have a simple spreadsheet of transactions that contains ONE common field: email address. How could I create a pivot table or something that has a Total of all the transactions under an email address?...
Viktor's user avatar
  • 285
2 votes
1 answer
15k views

Pivot Table - Add multiple columns that share the same set of values as rows

I'm trying to make a pivot table from data where there is one column I want to pivot on (Hair Color) and then a lot of columns I want to calculate counts for. These count columns all have a common ...
zeke's user avatar
  • 123
0 votes
1 answer
2k views

Pivot table counting blank cells in formula error

This problem bugs me every year and every year I can't remember how to fix it! My data is fine, but my pivot table reads it wrong. My IF statement says that if the code is PP or PA, to count this and ...
Brendy's user avatar
  • 3
0 votes
1 answer
1k views

Convert List into Grid

I want to convert a List similar to the following into a grid (as below) so that I can generate pivot tables... Is there an easy way to do this with VBA? List: Date Name Assignments 6/1/...
user1191513's user avatar
0 votes
0 answers
719 views

To list all the available fields in a huge Excel pivot table

What is way to export a list of each table and its fields from a pivot table? I have a large pivot table, reading external source, with dozens of tables and more than a hundred fields. There are many ...
Mark K's user avatar
  • 161
0 votes
1 answer
5k views

Refresh data when opening the file option not working on Excel pivot table

We have an Excel file with a pivot and a data tab. It is stored on the server, and when the user downloads it, a process fills the data tab and changes the pivot's data source to the newly added data. ...
Annosz's user avatar
  • 131
0 votes
2 answers
72 views

Microsoft Excel - I am looking to do a vlookup using only part of the string in the columns

I am looking for help with a formula that only takes the last name(column has both first and last name) as a lookup_value and uses that as the key to find a name in a table where the column also has a ...
mbadataguy's user avatar
0 votes
1 answer
324 views

Pivot table filtered on a field but slicer shows all fields?

I'm using Excel 2010 and have created a pivot table based on a table of base data (which is not itself filtered). The pivot table is filtered in the 'Pivot Table Field List' and successfully displays ...
Steven Quinn's user avatar
0 votes
1 answer
1k 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 ...
GK_'s user avatar
  • 25
0 votes
2 answers
4k 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-...
Jean-Pierre's user avatar
1 vote
1 answer
842 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, ...
FlexYourData's user avatar
  • 6,840
0 votes
1 answer
5k 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 ...
feetwet's user avatar
  • 1,685
1 vote
2 answers
529 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 ...
bob's user avatar
  • 113
0 votes
0 answers
22 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 ...
InvisibleInk's user avatar
0 votes
1 answer
43 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) ...
GrandStar's user avatar
0 votes
0 answers
476 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 ...
MVUser's user avatar
  • 1
0 votes
0 answers
4k 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 ...
e-mre's user avatar
  • 1,755
0 votes
1 answer
77 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.
Rustam Ibrahimli's user avatar
2 votes
2 answers
17k 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 ...
Oscar Ungsgård's user avatar
0 votes
0 answers
123 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,...
Saravana Kumar's user avatar
0 votes
1 answer
111 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 ...
user1154659's user avatar
0 votes
0 answers
40 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 ...
Brandon's user avatar
0 votes
0 answers
812 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 ...
Ram Sidharth's user avatar
0 votes
0 answers
288 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 ...
jzeef's user avatar
  • 101
0 votes
1 answer
754 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 =...
feetwet's user avatar
  • 1,685
0 votes
0 answers
74 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 ...
crazybox200's user avatar
1 vote
2 answers
1k 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 ...
Irvin's user avatar
  • 53
0 votes
1 answer
5k 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 ...
alexherm's user avatar
  • 101
2 votes
2 answers
26k 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 ...
Vitas's user avatar
  • 907
0 votes
0 answers
174 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 ...
Troyseph's user avatar
  • 121
0 votes
0 answers
776 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 "...
Ben Cor's user avatar
  • 11
1 vote
3 answers
2k 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 ...
Aleks G's user avatar
  • 472
1 vote
0 answers
36 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 ...
Lepp's user avatar
  • 11
0 votes
1 answer
135 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 ...
PainfulTruth's user avatar

1 2 3
4
5
17