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.
816
questions
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 ...
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 |...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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'...
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 ...
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 ...
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 ...
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 ...
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 ...
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?...
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 ...
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 ...
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/...
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 ...
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. ...
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 ...
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 ...
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 ...
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-...
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, ...
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 ...
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 ...
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 ...
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)
...
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 ...
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 ...
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.
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 ...
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,...
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 ...
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 ...
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 ...
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 ...
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 =...
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 ...
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 ...
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 ...
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 ...
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 ...
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 "...
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
...
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 ...
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 ...