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.
818
questions
0
votes
1
answer
2k
views
Unable to add CubeField/PivotField as a Data Field in VBA
I'm working on a very long Macro for my job and I am just about finished, but this is the last part and no matter what I do, I can't seem to figure out the fix. In this PivotTable, I need to have "...
- 11
4
votes
1
answer
27k
views
Show the latest value using pivot table in Excel
I have this table
and I want to get the latest -by date- "latest Sum" value of every "From".
I expect something like :
2018-02-03 | person1 | 9200
2018-02-05 | person2 | 2600
I created a pivot ...
- 635
1
vote
0
answers
88
views
Excel Pivottable wrongcalculation
I have some data from a database and want to do some analysis with the data. But there are some problems with my resulting pivot table. See the following snippet with some example data (only the ...
- 111
0
votes
1
answer
2k
views
Excel 2010 Pivot table Grouping by days of week
Hi I am trying to group days of weeks for my pivot table, but because they appear in multiple times a week. I am unsure of which format to use in order to get my pivot table to work.
My data looks ...
1
vote
0
answers
367
views
Calculate the difference between two field counts as a part of the pivot table
This issue is driving me mad after a couple hours and I cannot find a solution for it. This solution is close but I don't have a third parameter like year to key on.
Here is the data I have in the ...
- 505
4
votes
2
answers
34k
views
Creating a graph that groups data by date and category
I'm looking at a CSV file from Twilio showing outbound text messages. I have three messaging services, each with a distinct phone number. Each row in the report represents a message, and has a ...
- 559
1
vote
1
answer
2k
views
Pivot table grouping by week - can't get it to start on Monday
In Excel 2016, I'm trying to set up a pivot table which has a Date-format column and group it by weeks.
Ungrouped, the data looks like this:
I'm then trying to group it using the following settings:
...
- 153
1
vote
0
answers
177
views
Student voting system in Excel: Combination Slicer and Histogram
I am trying to create an interactive chart with an Excel workbook (2016) to display the results of student voting. My workbook has several columns, but the two relevant here are "student name" and "...
- 11
1
vote
1
answer
19k
views
Remove filter from ROW LABELS on pivot table Excel
How can I, or is it even possible to remove the access to a filter on the Row Labels on a pivot table, see image for REF.
Or even just hide it from View
My pivot tablse set up only has Row Labels ...
- 7,435
2
votes
1
answer
1k
views
Filter PivotTable on date range with blank rows showing
I have a table of data that looks like this:
Created Severity Tickets Submitted
11/17/17 High 1
11/28/17 High 1
12/3/17 High 1
12/4/17 High 1
12/27/17 High 1
...
- 115
2
votes
1
answer
88
views
How to add from pivot table
I have a table in Excel, and I've managed to create a pivot table, which includes "grouping".
What I'm trying to do, is sum the group
My pivot table data looks like
What I'd like to have is a sum ...
- 1,933
0
votes
0
answers
94
views
Excel Pivot table: how to show and rearrange only some of the totals?
I have a table in Excel (from a PowerPivot source, not sure it's relevant) with these fields:
product, type, volume. Type can be only A or B.
I need to create a pivot table that groups by product and ...
0
votes
1
answer
55
views
How to creat this Pivot table correctly in Excel 2016
I wanted to do a pivot table as shown below.
Clearly it is not correct, as Business is the most popular subject and it shouldnt have any "<0 or (blank)".
I am assuming that the problem here is ...
- 494
0
votes
1
answer
4k
views
Plotting values instead of sums in a pivot chart
I have this pivot table:
The data is arranged according to the following hierarchy:
Line -> Machine -> Year -> Week (1 to 52)
For each week there is a value for Score (%), Execution (%), Duration (%...
- 453
2
votes
1
answer
6k
views
Grand Total Not Displaying Correctly for Pivot Table's Calculated Field
I have the calculated field Break Lateness to determine how late an agent is from Break 1 by using the following formula:
IF( 'Break 1' > TIME(0,15,0) , 'Break 1' - TIME(0,15,0), TIME(0,0,0) )
...
- 125
2
votes
1
answer
224
views
Use a Pivot Table to summarise a ledger
I have a basic ledger in the format:
Credit Currency | Credit Amount | Debit Currency | Debit Amount
================+===============================================
LTC | 0.4 |...
- 369
1
vote
0
answers
61
views
Referencing a Pivot Table Value, reaching arbitrary limit?
I've come across a really strange case with a formula I'm using to reference a pivot table where I'd love your help.
Currently I have a report that is a simple table, as seen here:
The report draws ...
- 11
1
vote
1
answer
816
views
Reference Cell in "GetPivotData" Function
My function is working correctly when I manually enter the "CustomerAccoutNumber":
=GETPIVOTDATA("[Measures].[Sum of SalePrice]",
[2017Sales.xlsx]GMBlitzOct!$A$6,"[2017Sales].[CustomerAccountNumber]...
-1
votes
1
answer
33
views
Excel Data Model: Treat Headings from Different Tables as One
What I am trying to achieve is to have a single Excel file where two different users/teams drop data into a Table on a daily basis. This table should then create a pivot automatically.
The challenge ...
- 1
7
votes
2
answers
156k
views
Is it possible to set an Excel Pivot Table filter value to a cell reference?
I'm trying to make a Pivot Table more dynamic in Excel by filtering not on checked values from a list (as is the default method) but to filter on a value contained in a cell reference. Is this ...
- 175
2
votes
1
answer
11k
views
How to remove a duplicate value on slicer in Excel
I am trying to build a pivot table to compare some data.
I first made a Excel sheet for my colleagues to enter their respective data, expecting that discrepancies when they enter the data, I used ...
- 121
1
vote
3
answers
38k
views
Save a custom pivot table style EXCEL
I went to the effort of creating a custom pivot table design, to match my companies work colours.
I'm looking for a way to save a custom table style (not Workbook) as a template, so it can be used ...
- 7,435
0
votes
1
answer
614
views
Slicer only corresponds to table not my chart - Mac Excel 16
I have inserted a pivot table based on the data range I want to use. From there, I have selected the data in the table and inserted a chart. I then insert a slicer, but when I select the category it ...
- 45
2
votes
2
answers
4k
views
Cannot group field in Excel PivotTable
I know this question has been asked before, and the usual answer is "your dates are not all stored as dates!" I highly doubt this is the case here.
I'm using PowerPivot which pulls data from a ...
- 121
0
votes
1
answer
553
views
Excel 2010 Pivot Table - Percentage of Other Column
I have reviewed multiple other posts and other sources online, but am still coming up short.
I want to add a column which shows a percentage of 1 column to another. Here is what is going on.
My raw ...
- 190
0
votes
1
answer
2k
views
In an excel pivot table, how can I only select rows that has a particular value in it?
Here's the pivot table I have :
Mat Cust A B C D E F G H I J
1 P 0% 39% 18% 19% 0% 0% 0% 24% 0% 0%
2 Q 0% 52% 21% 19% 0% 0% 0% 7% 0% 0%
3 ...
1
vote
0
answers
196
views
Synchronize custom group names across multiple related pivot tables
I have multiple pivot tables based on a master where I specify custom groups on a field.
The groupings themselves mirror on all the related pivot tables throughout the sheets but the naming I ...
- 11
0
votes
1
answer
2k
views
How to prevent multiple PivotTableUpdate triggers
I am building a dashboard in which 3 sliders control 10 pivot tables on a second data sheet. I also have a map drawing on the pivot data. This is populated by a macro that needs to be triggered each ...
- 1
0
votes
1
answer
6k
views
Add Slicer Selections to sheet in a list - Excel VBA
I have a pivot table With a slicer that a user can make multiple selections in. I am trying to list the selected values in the slicer so they can then be joined together in another cell using ...
- 28
0
votes
1
answer
1k
views
Excel - what is a workaround for a pivot-style summary of two groups of linked column data?
In Excel, I am building a report with a dashboard that incorporates a map of the country. This map is made up of objects that represent the administrative divisions of the country, and through VBA, ...
- 1
1
vote
1
answer
819
views
(Joining 3 tables) Pivot Table using Excel 2016
I think this is a many-to-many relationship and Excel currently doesn't support this using Pivot Tables. Nonetheless, I wanted to see if there could be an alternative that could be achieved while ...
- 111
0
votes
0
answers
344
views
Excel - Pivot table drill down using SUMIF
Not sure if this is possible, maybe with VBA but I can't work it out.
I have a pivot table that I want to be able to filter totals to separate totals less than/greater than a value. (eg all totals >5)...
- 28
0
votes
1
answer
133
views
Dynamic Dates in a Pivot Table
Is there any way that I can create a pivot table which only shows the previous 4 weeks data dynamically.
I can create a dates filter for this month or last month etc, but if I want to create the ...
0
votes
0
answers
1k
views
Excel - Pivot Table Calculated Field Count of Dates Field Error
I have a pivot table that is counting the amount of records from two date data type columns. One column will have a value for the full size of the sample, while the other will either have a value or ...
- 103
0
votes
0
answers
714
views
Row label group range as percentage, not decimal
I have an excel 2013 pivot table that looks like this:
When i am grouping the rows, % changes to decimal:
Is there a way to express groups' ranges in % ?
Regards,
- 1
0
votes
1
answer
1k
views
Pivot table with multiple, non hierarchical, columns
Let's say my source data looks like this:
Brand Model Color Region Sales
---------------------------------------
Ford Focus Black N/A 23
Ford Focus White N/A 2
...
- 960
6
votes
2
answers
39k
views
Not able to sort columns
I have a pivot table and I would like to sort some columns. But the "sort" function is not working at all. When I click "sort from A to Z" nothing happens. I am just able to sort the first column of ...
- 61
6
votes
1
answer
30k
views
Working with Averages in Pivot Table (Calculated Field)
I have the following data :
customer_id customer_name id date contract_value costs profit_extra_work
200027 Company A 00.161.559 08/07/2015 499.5 50.55 0
...
- 419
0
votes
2
answers
3k
views
PivotTable Change Data Source broken in latest version (1706)?
Create a sheet "2015":
Create a sheet "pivot" und sum the amount:
Create a sheet "2016" with different data
Try to select the data from "2016" for the pivot table and hit the OK button and nothing ...
- 101
1
vote
1
answer
40
views
Add columns to Excel Pivot
Suppose I have a list of citizen grouped in a pivot:
Citizen | Count
Chinese 153
Italian 245
French 236
I would like to add a new column to show the country in this way:
Citizen | Country | ...
- 191
1
vote
0
answers
62
views
Excel:can I simplify creation of following pivot table and chart somehow?
I have following table that lists all projects: ProjectId, DateCompleted.
I want to create a table/chart that shows a total of projects completed each year/month.
I used =TEXT(DateCompleted,"MMM") ...
- 11
0
votes
0
answers
1k
views
Pivot Chart made on Windows Excel not working on Mac
I've made a Pivot Table / Chart in Excel on Windows 10.
See screenshot below.
I've sent the Excel document to a person who opened it on a Mac (see photo below). When she opens the file on the Mac, ...
- 161
0
votes
1
answer
443
views
Excel SUM from two tables based on ID in both tables
I have 2 tables in Excel:
Table1 with ID, PointsA ... &
Table2 with ID, PointsB ...
ID from Table1 and Table2 have duplicates and some in Table1 are in Table2 and vice versa but not all of ...
- 101
1
vote
1
answer
100
views
Cleaning trailing zeros (.00) from dates so the Group function works in a pivot table
Using Excel 2010, I am getting a
Cannot group that selection
error when attempting to group dates. The date data came to me in an .xls file with this format: 2016-01-07 5:00 PM in a single ...
- 11
0
votes
1
answer
20k
views
Disconnect link between copied charts in Excel
I created a Pivot Chart in Excel and added a filter.
I would like to create copies of the chart with different filter selected in each one.
But it seems there is a link between them, and when I change ...
- 719
0
votes
3
answers
1k
views
In Excel 2010, how can I show the values in one column for one value in another column?
I have a table of over 4K rows. The columns are: Site, Object, AD Group, SP Group
I need to be able to display a summary of this data which shows the values in the "SP Group" column for each unique ...
- 183
0
votes
1
answer
3k
views
Getting a pivot table to treat two columns as one
I am fairly new to pivot tables, but with a complex problem, I think.
The scenario is that each application is graded by two assessors, a main and secondary. So let us assume that one assessor is ...
- 1
3
votes
1
answer
3k
views
Can I automatically sort a grouped and stacked pivot chart by a sum of values without showing this on the chart?
I am working with data in groups in Excel. I currently have a pivot table and chart set up with:
Rows: symptom name grouped into one of four international severity grades
Columns: four datasets - ...
- 585
0
votes
1
answer
140
views
PivotTable calculated field using BETA.INV not saving properly
I am creating some PivotTables and I'm trying to add some calculated fields that use the BETA.INV worksheet function. I have no problem doing this and they work correctly. I can even save the ...
- 1,351
0
votes
0
answers
2k
views
pivot table: "Summarize Values By" -> Sum in Excel 2016 - keyboard shortcut?
When you create a pivot table, Excel often defaults to counting numeric values instead of using "Sum". I often have to change it manually via "Summarize Values By" -> Sum. In Excel 2010 I used to be ...
- 541