2022 Developer Survey is open! Take survey.

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
16 views

How do you clear filter cache in pivot tables in Excel?

I have turned number of items to retain field to none and also tried the VBA macro in the link below. Neither worked and when I refresh my pivot table the filter still keeps items not being used. The ...
user avatar
0 votes
1 answer
27 views

Pivot calculated field of variation

I want to create a pivot graph that shows the variation (%) in number of road accidents from year to year+1. I need it to be pivot because I want it to be refreshable if data change. My data is a list ...
user avatar
0 votes
3 answers
50 views

Pivot Table: Expand/collapse all rows using keyboard shortcut

In an Excel 2016 pivot table: Is there a keyboard shortcut we can use to expand or collapse all rows at a given level? For example, CTRL + click the plus sign to expand all rows? I'm aware that it's ...
user avatar
0 votes
1 answer
52 views

Pivot Table: Sort row by hidden field

I have a table in Excel 2016 (source: Microsoft Query/ODBC): Raw data: Construction Type Location Sort Reconstruction Main St - Red St to Blue St 1 ...
user avatar
0 votes
1 answer
32 views

Is it possible to filter out dirty data before it gets into the pivot table in excel automatially?

I have a database that has got dirty data. For example, the No. of days should be an integer, however people fill in doubles like 2.3 or 5.2... My pivot table sample data from this database. The ...
user avatar
0 votes
0 answers
10 views

DAX formulae to reflect "show % of..."

This is related to this question, but as I am a brand new user (yay) and cannot comment (unfortunately), I had to start a new question... So, is there any way to get a % of column total using a ...
user avatar
  • 3
0 votes
1 answer
38 views

Excel Pivot display multiple columns

I had a Salary data month wise now i want to calculate the difference between last 2 months salary field wise below is the sample data Required Sample output of single employee. I have tried using ...
user avatar
0 votes
1 answer
24 views

Convert from table to matrix format with Excel 2010

I would like to convert this data from table to matrix format where the names in the dest column become columns and the number values corresponds to the total but the origin column stands still origin ...
user avatar
0 votes
1 answer
21 views

Excel Pivot Table: Show aggregated data in one column and the actual data in another column

I have an excel document and inside this document, there are two sheets: 1. Actual Project Hours 2. Planned Project Hours. The actual project hours is broken down into projects, resources, hours taken ...
user avatar
0 votes
1 answer
43 views

MS Excel - How can I create a pivot table for 3 dimensional data with no numerical values employing OR logic

I am not sure if this type of question was posted here before because I myself am not sure how to ask it. So here goes, I have a massive three-dimensional dataset that looks somewhat like the table ...
user avatar
1 vote
1 answer
30 views

Excel Pivot Table - Change Columns using Filter

I have a pivot table and values separated by a signal flag (0 or 1). I many different signal types (columns) to use. Question: Instead of creating a new pivot table for each signal, is there a '...
user avatar
  • 11
0 votes
1 answer
30 views

Sorting of pivot table: Not sorted correctly

After creating a pivot table based on ticker symbol on new sheet, I am getting first three entries (MON, MAR, NOV) that are not sorted when ascending sort option applied.
user avatar
0 votes
1 answer
38 views

custom per-category formulae over summarised rows

I'm trying to figure out some pivot table magic here, where I can have the top-level row-field be a custom formula for each category. So, for example, with data like this: Edit: Providing in CSV ...
user avatar
0 votes
1 answer
159 views

Excel 365 - Workaround for greyed out calculated fields when creating pivot table from data model

I’m creating an interactive dashboard containing pivot charts for different product sales at my work. The dashboard also contains a slicer that controls ALL charts in the dashboard since all are ...
user avatar
0 votes
0 answers
6 views

How to shift Pivot results to horizontal

Have a list of results in a pivot that want to shift them horizontally so I can insert them to the database. Basically I want this(See Image 1): Image 1 To become this(See Image 2): IMage2
user avatar
0 votes
1 answer
17 views

How to best reorganize for Pivot Table?

I have 10x different large Excel tables, tracking technician competencies in this format: I know that if I select the region, it won't be useful for Pivot data. Any suggestions on how to keep track of ...
user avatar
0 votes
1 answer
20 views

Reusing row values in pivot table

From a list of contracts I want to learn which month the invoices are due. The invoice frequency is different per contract, ranging from monthly (12x) to quarterly (4x) and yearly (1x). Also the start ...
user avatar
0 votes
0 answers
17 views

Pivot Table instead of SUMIF function

I inherited a staffing list, which I'd like to use as input for a pivot table. The list is sorted by project and not by staff member, which would make my life a little easier. Previously a staffing ...
user avatar
0 votes
0 answers
19 views

Exclude 3 policies from pivot

need assistance. I need to exclude 3 policies from a pivot table. Just from the FullPolicy column. I tried to hide it, but it freezes the page and gets stuck there. What is the easiest way?
user avatar
0 votes
1 answer
22 views

How can I apply specific functions to pivot tables in Excel

I have some data that looks like this: Item Mass CGx CGy CGz Option A123 10 4.2 0.1 0.5 AB01 C789 24 2.2 0.0 0.2 CD02 B456 12 3.8 0.0 0.7 AB01 D123 26 1.2 1.0 0.2 CD02 If I want to get the total ...
user avatar
  • 178
1 vote
0 answers
13 views

Clustered column chart for year over year quarterly data, improper spacing(pivot table)

How can I group columns in Clustered column chart for year over year quarterly data(pivot table)? In the example, I wish to have 2016 and 2017 values for a particular month grouped together for proper ...
user avatar
0 votes
1 answer
40 views

How to conditionally merge tables in Excel

I have two tables of data in Excel that were exported from our HR system. One contains estimated/planned workhours, the other contains actual worked hours. Each line has a date, hours worked, and the ...
user avatar
0 votes
1 answer
54 views

In a pivot table, how to apply conditional formatting by label instead of by group

In a pivot table you can apply a conditional formatting to a group of value rather than to a cell or to the whole field thank to the "Applies to" option of the "Edit rule" window. ...
user avatar
0 votes
1 answer
34 views

Can you use Pivot table report filters as an OR statement rather than AND?

I am trying to utilise Pivot tables to pull together a list of items where a certain value appears in either of 2 columns (in the attached photo this is "Staff 1" and "Staff 2" ...
user avatar
0 votes
0 answers
52 views

Vlookup formula isn't subtracting for certain cells and not others

My Vlookup formula isn't subtracting the info it pulls from my pivot table and I can't figure out why. I've run tests in other sheets and get the same results (works with some data but not others). I'...
user avatar
0 votes
2 answers
145 views

excel, pivot table, filter, sum, need to filter to where non-zero

I have a pivot table, see below. How can I filter this pivot table to where Sum of Not Compliant is not 0. I cannot add filters to any of the Sum columns in this take. The final column in this pivot ...
user avatar
  • 117
0 votes
1 answer
35 views

Microsoft Excel: Make GetPivotData() refer a pivot table using its name

Microsoft Excel names pivot tables and provides means to rename it - 'PivotTable Name'. The Microsoft Excel function GETPIVOTDATA(data_field, pivot_table, [field1, item1], ...) has a parameter '...
user avatar
  • 151
1 vote
0 answers
13 views

Create a custom pivot table off two different pivot tables

Suppose you have the following 2 tables from 2 different sources of data Table 1 Table 2 I want to create a custom pivot table off these two tables, which will look like the following Note that the ...
user avatar
  • 21
0 votes
0 answers
39 views

Apply different filters to specific column values in a pivot table

Suppose I have the following table from one data source There are two types of row fields, one for region and another row below it for category. The sum of the categories is the total # of accounts ...
user avatar
  • 21
0 votes
1 answer
37 views

How to add a dropdown icon in a header of a pivot table?

I have a pivot table as follows. In Cell I4, the header is Row Labels, and there is a dropdown icon on the right, which is good. Now, I would like to add such a dropdown icon in Cell J4 where the ...
user avatar
  • 1,025
0 votes
2 answers
103 views

Excel: How can I get the most recent 3-month average, but not use blank months?

Hi people better at Excel than me! I appreciate any help you can offer. I’ll try to be brief: I have a 3-column table. Month (as 1,2,3,etc…), Salesperson Name, and Sales. Each row is how many $ in ...
user avatar
0 votes
1 answer
58 views

Excel: Conditional formatting in ROWS section of pivot table based on result in Values field

I am trying add conditional formatting to the Rows section of a pivot table based on the results found in the values section of the pivot. I have already set the conditional formatting in the values ...
user avatar
  • 3
3 votes
1 answer
68 views

Changing Row Fields in pivot Table of Excel

I wanted to change the Row Fields used in the Pivot Table, I was wondering if a slicer could do this. Normally I would create two pivot tables along with two separate charts but in my actual dataset, ...
user avatar
0 votes
0 answers
19 views

Data on a Pivot chart to change on ticking people off a register

I have a pivot table which I have converted into a pie chart. The pie chart data is of managers and how many colleagues are assigned to them that given day. I mark the colleagues as they enter the ...
user avatar
0 votes
1 answer
87 views

Pivot table column name and horizontal bar in Pivot Chart

My pivot table has a problem.  As you can see, the first column has a heading of "Row Labels" (in cell A10).  I want it to say "Months". Also, I'd like to have a horizontal line ...
user avatar
0 votes
1 answer
128 views

Excel PivotTable without summation of Values

I am struggling with an Excel PivotTable problem although I am not sure that a PivotTable is the best solution. I have a vertical table that has the format shown below, that I am trying to pivot to a ...
user avatar
  • 111
1 vote
0 answers
31 views

How do I extract design information from a pivot table in an external worksheet (properties) (Excel)

I am teaching a course in Microsoft Excel, using Excel 2019. As part of an exam in this course, I ask students to create pivot tables. I will then need to grade these exams. Each student submits an ...
user avatar
0 votes
0 answers
44 views

How can I get my pivot table to display values with time format correctly?

I created a pivot table a few days ago and cannot recreate the conditions in a new pivot table that should be similar. It is based off distance & time results from Google's Distance Matrix API, ...
user avatar
0 votes
0 answers
24 views

Filtering Away Certain Duplicate Values in Pivot Data Source

I'm using Pivot Table and Slicer to filter data from a raw data source. Our employees work rotating shifts. Between those shifts there are overlap hours. I created a file to track their performance ...
user avatar
0 votes
0 answers
79 views

Excel Pivot table not to count blanks

In my Pivot table (columns - count) - I am trying to count where ever there are no blanks - these are strings (not numbers). So wherever there is a non-blank value - count - if only 100 cells have a ...
user avatar
  • 131
0 votes
1 answer
361 views

I want to display key/value pairs as columns in Excel

I am having a terrible time trying to display some key/value pairs in Excel as columns a specific way. Note: I have found a way to accomplish this in Python so this question is mostly for the sake of ...
user avatar
  • 3
0 votes
0 answers
9 views

Pivot Table Calculation

I have created a field on my pivot table to calculate the duration between my start date and end date columns using the Fields, Items and Sets button. I now want to do some analysis on the calculated ...
user avatar
0 votes
0 answers
117 views

How to remove a specific field value from Pivot Table

My pivot table is sourcing data from a dynamic table where the number of rows can increase or decrease based on certain conditions. As such, the range for my data source is something like $A:$M so ...
user avatar
0 votes
1 answer
36 views

How can you format each row from a pivot table to display transposed with the headers repeating themselves?

From a pivot table, I am attempting to take each row of data and format it transposed with the headers from the pivot table present in each instance. Here is a link to a Google Sheet that has a sample ...
user avatar
0 votes
1 answer
125 views

How to remove blanks from drop down in Excel Pivot Table

I'm trying to create a pivot table that represents a parent-child assembly list. It works nearly perfectly except for one issue. Anywhere there is a blank, a full row with the very little info it has ...
user avatar
  • 101
0 votes
0 answers
12 views

Pivot Table not able to read date and text

Attached the sample where I want pivot table to show this sequence, but the date and text (Column C,E,F,G) doesn't populate appropriately at value field. Raw data will have empty column at date column ...
user avatar
0 votes
0 answers
39 views

Pivot filter shows multiple items, only 1 selected

I've created a macro that adds two sheets, each with a pivot table in cell A1 filtered by the current weeknum or current month. The pivot table filtered by current weeknum displays the week number at ...
user avatar
0 votes
1 answer
42 views

Excel: Identifying common groups of properties for IDs

I have a load of data of the nature: ID Flavour 1 apple 1 orange 2 pear 2 orange 2 cucumber 3 apple ... ... I need to identify the most common groups of flavours (ie. there are 36 IDs with ...
user avatar
0 votes
2 answers
1k views

How to make a pivot table that counts from multiple columns?

I have a workday schedule in which I will count how many times I do each activity across the week. Here's an example: The pivot table only allows me to count from MWF or TT but not from both without ...
user avatar
  • 101
0 votes
0 answers
252 views

Google Sheet suddenly "Unable to load file" if I expand my pivot table on desktop

I have a spreadsheet I've been building for a couple of months now. Here is a sample of it for testing: https://docs.google.com/spreadsheets/d/1wj4T_05zOhPSdtNY4eDu17Y2pbCJgpS5PXPtJbviql0/edit?usp=...
user avatar
  • 356

1
2 3 4 5
15