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.
793
questions
-1
votes
0
answers
6
views
How to sort multi-level PivotTable by first level?
I have a PivotTable that I am trying to sort. The table has four levels in the Rows area, and I would like to search by the first level and the first level only. The levels represent SKUs, order ...
- 221
-1
votes
0
answers
21
views
How do I do a vlookup on filtered data where it returns data from visible cells only?
I am trying to reference only the data in visible cells using this formula:
As you can see in the below pic, it's referencing a cell that is hiding.
Is there a way to get the formula to only ...
- 9
0
votes
0
answers
6
views
Pivot to identify same contract type in multiple cells
Per attached example how can I use pivots to filter by all rows and columns with C01 and C02 contract for a unique client for example
https://i.stack.imgur.com/oc04S.jpg
1
vote
2
answers
40
views
How do I use a pivot table to display all projects that are missing a certain task, but only in certain circumstances?
I'm using Excel pivot tables to create a report that will be shared with others. I don't have any VBA or PowerQuery or PowerBI or coding experience, but am decent with Pivot Tables.
My source data for ...
0
votes
0
answers
4
views
Why does the data point appear in my pivot table if the table it is referencing doesn't contain the same data point?
The pivot table contains the data point:
The pivot table is referencing this table:
As you can see, the data point is not in the table. This table is connected to a query. I am guessing the reason ...
- 9
0
votes
0
answers
20
views
How to count data within a Pivot table
Is there a quick/easy way of counting the different fields that are listed within a Pivot table.
For example, if I had data which listed different car manufacturers in an inventory and the color of ...
- 1
0
votes
0
answers
30
views
How to change change Excel PivotCache from external source to table if external source includes measures
I've inherited a worksheet that contains an external connection via PowerQuery, which is connected in turn to 20 or so PivotTables and four or five common slicers. Users have complained about speed ...
- 1
0
votes
1
answer
36
views
Permanent PivotTable filter
Is there a way to make a PivotTable filter permanent?
I.e., prevent or discourage users from removing a filter from a PivotTable, since bringing in that intentionally excluded data would cause ...
- 137
0
votes
0
answers
18
views
Excel Pivot Table - How to show custom selection of categories as proportion of grand total
I'm struggling to structure my pivot table or input data in order to allow users to select a custom grouping of Categories (using a slicer), and then show that Selection as a proportion of the grand ...
0
votes
0
answers
16
views
Displaying 2 colums in pivot chart in seperate groups
I have 2 value columns Vehicle In and Vehicle Out. When i create a pivot chart out of it (clustered Column). It displays as usual like this in|out in|out. But is there a way to make it display all the ...
0
votes
0
answers
24
views
Pivot Table shows #NUM! error for standard deviation population
I'm trying to understand why for certain categories there are #NUM! errors showing up in my pivot table. It doesn't make sense to me, because when I use standard =stdev.p formula I'm getting value of ...
- 101
0
votes
0
answers
87
views
In an Excel PivotTable, how can I sort by multiple row fields?
In Excel PivotTables, it's easy to sort, say, the rows by the major row field: There's a "Row Labels" column with a menubutton on the side, you click it, and you get sort options, just like ...
0
votes
1
answer
62
views
Add new worksheet(s) to existing pivot table
Please note: I will post here, according to the suggestions from Stack Exchange site
On a dedicated worksheet, I have a pivot table summarizing data from another worksheet in the same workbook.
Now, ...
0
votes
2
answers
25
views
Excel - how to use pivot table to transpose glossary
I'm very new to excel and I think I need to use a pivot table for this, but not entirely sure how.
If there is a better way to do this, I would love to learn how.
I have:
| pages| | |
|:--...
0
votes
1
answer
78
views
Excel Pivot Tables - create monthly averages based on slicer date range
I am building a personal finances worksheet. I have a solid pivot table that shows profit/loss for a given date range.
Question 1: How can I show a column (or a separate sheet) that shows monthly ...
- 101
0
votes
1
answer
43
views
How to make my PivotTable into a heatmap, persistently?
We can easily make an Excel table into a heatmap by applying a conditional formatting rule on the rectangle of relevant data cells. And - this can also be done for a PivotTable.
However, whenever you ...
0
votes
0
answers
38
views
Summarizing distinct values (target yield over several years) from multiple columns in pivot table in Excel
I want to help our orchard manager better predict fruit yields and set targets for yields. I have tried accomplishing this in pivot tables and have been unable to achieve the desired result.
I think ...
- 3
0
votes
0
answers
21
views
Filterby multiple varying values in excel pivot table, without manually selecting
So I have a database connection that pulls data into a pivot table in excel, however I have a column called "Customer" I want to filter on, but i want to filter on specific customers (i want ...
0
votes
0
answers
10
views
Excel Pivot Table Display Details of Existing Row
I have some data that looks like this (example drastically reduced in size). Two "Top Level" parts that contain some "Materials". Two of those materials are used in both the top ...
0
votes
1
answer
29
views
Can I use a pivot table to count cell values in columns and group the result by column?
I have several ranches I want to manage with common tasks across each ranch. I've made a table to track my progress:
Tasks
Dry Valley
Rocking Horse
Whisperwind
Herd Cattle
Done
ToDo
In Progress
Put ...
1
vote
0
answers
59
views
Excel how to group up data by percentages
I have a graph which many Series, but I only need the first 5 and I want to group up the rest as a Series of its own called "others".
Here you can see how all the smaller data below the top ...
0
votes
0
answers
44
views
Exclude values from mean in excel PivotTable
I'm trying to calculate the average salary in a PivotTable, but i only want to include the people that have a salary, so that those with the value 0 is excluded.
My data is already grouped, and a ...
- 1
2
votes
0
answers
31
views
Change PivotChart source from one PivotTable (external data source) to another PivotTable (table/range)
I have a PivotChart in Excel 2016 that is currently based on PivotTable-A.
PivotTables A and B are identical, other than the fact that they have different data sources:
A's data source is an ...
- 137
2
votes
0
answers
130
views
Change PivotTable source from external data source connection to table/range
In Excel 2016, I have several PivotTables (and corresponding PivotCharts) that use external data sources. The data sources were created via Microsoft Query (an Excel mechanism) and have complex SQL ...
- 137
0
votes
0
answers
10
views
hi, im trying to create a pivot table that has documents received in a particular week and documents replied in that particular week. data source
im trying to create a pivot table that has documents received in a particular week and documents replied in that particular week. In data source, documents replied column has some empty cells since ...
- 1
0
votes
1
answer
167
views
How do I create a calculated field in a pivot table that simply divides two other columns in the pivot table?
I have these data in Excel:
I created a pivot table that is supposed to calculate the percent of each state's counties that qualify for a certain government program (this is artificial data, though).
...
- 784
1
vote
1
answer
31
views
Force bar chart to show missing years within 10-year range (system year + 9)
I have a table in Excel 2016:
YEAR_ AMOUNT
---------- ----------
2024 100
2025 200
2025 300
2026 400
2027 500
2028 ...
- 137
0
votes
0
answers
15
views
Excel Pivot Table with blanks?
i have a problem.
i have alot of data.
the data consists of each item in each other over a period of time.
so the data looks something like this:
customer1, ordernr, item1, amount
customer1, ordernr, ...
- 1
2
votes
2
answers
47
views
What type of dataset am I working with here in Excel? What word or term can best describe it?
I am beginning to delve into data analysis via Excel and I would like to learn more about the terminology in terms of some data I'm playing with.
I've created a sample data set for mock invoice ...
- 21
0
votes
1
answer
26
views
EXCEL: What type of dataset am I working with overall? How to manipulate pivot tables to answer complex relationships between fields?
I am beginning to delve into data analysis via excel and I would like to learn more about the terminology in terms of some data I'm playing with.
I've created a sample data set for mock invoice ...
- 21
1
vote
2
answers
58
views
Excel: Create truly blank cells from formula so that they wont show up in a pivot table?
So I've had a look at similar answers but they're not working for me. Basically I have a lot of related charts of monthly data, and I'm wanting to plot them on a chart that only shows up the current ...
- 11
-1
votes
1
answer
83
views
Compare two sheets and show a summary with only rows where a specific column has changed for that primary key, without altering source data
I'm trying to generate a report that will regularly compare current raw data with that of previous reporting period and only show rows (primary keys) where a certain column's value has changed between ...
- 113
1
vote
1
answer
74
views
Create scatter plot using two arrays of data in Excel [duplicate]
I'm an aspiring eSports rookie who has been tracking his training progress for some time now. I have two tables in an Excel worksheet: The first is Accuracy, with rows being date and columns being ...
- 13
0
votes
1
answer
57
views
How to transpose data (pivot table?) so that I get a single row for each type of element - summing different variant of that
here is example data I have, and a result I want to get:
excel screenshot
Name
Amount
Material
W01
100
AA
W01
100
BB
Z01
200
CC
Z02
15
AA
Z02
150
BB
Z02
350
DD
Z02
15
EE
Name
Layers
W01
AA ...
0
votes
1
answer
48
views
Making a collated transaction report using a pivot table
I'm trying to summarize donations to a non-profit and collate names, yearly contributions, and contact info on a nice clean report, one row per user. I got the yearly sums working using a PivotTable, ...
0
votes
1
answer
21
views
How to Sum Task-Specific Timesheet Table by Date?
I am manually filling a timesheet in an Excel table, each row being for a project task, for example:
project task; time, date, notes; time, date, notes
I would like to output a table of: Date, Sum of ...
- 1
0
votes
1
answer
531
views
MS Excel: generate a table with the number of rows depending on the input variables
So I want to make a matlab/r-like tool in excel that mimics more code driven calculations than the more common excel worksheet applications typically contain
This workbook should contain:
input sheet
...
- 103
0
votes
0
answers
71
views
Running Sum with Non-Zero Start Value in Excel Pivot Table?
Is there a way to have a running sum on a pivot table begin at a non zero value? I have a column that is a list of balance changes, and would like to be able to specify a starting balance (i.e. ...
- 1
0
votes
1
answer
109
views
Reuse an existing external datasource connection for PivotTable (without creating a duplicate connection)
In Excel 2016, I can create a connection to an Oracle database (via ODBC) using Microsoft Query or the Connection Wizard.
Creating a connection using one of those methods works as expected. The ...
- 137
0
votes
1
answer
72
views
Handle when the user tries to write something directly on a Pivot Table
What trigger can I use to handle the message below (that comes up when a user tries to write something in a Pivot Table) by opening a form?
My idea would be that the user would try to write something ...
- 119
0
votes
0
answers
51
views
Getting Pivot Table to show average OR weighted average values in same column field
all! I've a bit of a complex issue I'm trying to solve, and am hoping it's possible. Apologies for the long post. I'll be happy to provide more details upon request.
tldr - Giant pivot table has a lot ...
- 1
0
votes
0
answers
1k
views
Run Excel Macro when Slicer is Clicked
I have a PivotTable connected to a Horizontal Bar Chart. When I filter the content to be displayed in the Chart (by using the Slicer), the Chart did not resize according to the entries displayed. This ...
1
vote
1
answer
96
views
Bar chart reduce number of labels by creating Others
pretty new in excel and I'm currently working on a pivot table and want to create a bar chart.
I have around 15 row labels and 2 columns- row labels and percentages that looks something like this:
...
- 11
0
votes
2
answers
408
views
Make columns into row subgroups in Pivot Table
I have a basic table - list of locations, x types of trees, with the value being the number of trees planted in each location.
Location
Tree 1
Tree 2
A
123
456
B
789
012
What I want is for the ...
- 1
0
votes
2
answers
1k
views
How do I merge two rows into one where each row fills in the blanks of another?
How would I go about taking data in the following format of multiple rows:
ID
Type 1
Type 2
Type 3
1
Green
1
Blue
and merge them into one row such as:
ID
Type 1
Type 2
Type 3
1
Green
Blue
0
votes
0
answers
36
views
Conditional formatting within a pivot table to create a dynamic table
So I've been googling and googling and I'm confused. I'm quite new to pivot tables, and I've used one to pull in entire columns from a datasheet. (There's multiple tables across the workbook but they'...
0
votes
0
answers
29
views
Microsoft Excel: Conditional Formatting to a PivotTable with changing results
I'm attempting to format PivotTable results that stem from different slicers. Essentially this comes from a raw data 'dump' which will then auto-populate into the results PivotTable based on what ...
0
votes
1
answer
990
views
Excel Pivot Charts - Creating different charts (with different grouping) from the same data
I would like to create (at least) two pivot charts sourced from the same data that display the data in different groupings; one will be grouped by years, the other grouped by months. I am able to ...
0
votes
2
answers
2k
views
Excel: PivotTable - Need to divide running total field by Grand Total of a count field
I feel like I have to be making this more complicated than it needs to be...
I have a pivot table that is arranged by date. I have one field whose values are the sum of students registered for a class....
- 1
0
votes
1
answer
88
views
Get the count or sum of count of each individual items used in multi-select dropdown in google sheets
I have created a multi-select drop-down of items a,b,c, & d. Let's say I enter a,b in C1 and a,b,c in C2 and a,b,c,d in C3. Now, I want to get the total count of each item I have selected. Here, ...
