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
2answers
26 views

Division calculation within pivot table values in excel

Please see example below. The row headings (redacted) are defect types of a product. The column headings are the name of the products (redacted) and just below are the total sales of that product. ...
0
votes
3answers
47 views

Creating “regular” chart and a pivot table on data with gaps

I have a lot of data in an excel file that I am trying to display in 2 charts: Regular graph chart (all data) Pivot table (averages) The problem is that I have a lot of gaps in my data that i dont ...
0
votes
0answers
8 views

Trying to summarise multiple lines of data by latest date (LinkedIN Export)

I want to create a list which shows the latest date that someone sent me a message on Linked In. I have exported the data, in the following format, and there are multiple entries for individuals who ...
0
votes
0answers
22 views

Excel chart to match pivot table

So I have 2 different data sources and want them in the same format. (A) is using a pivot table to summarize monthly averages from daily flow data, (B) is a table that already has the monthly average ...
0
votes
1answer
31 views

How to properly format data to use in a pivot table

I am trying to format some data that I have into a pivot table, but I am having trouble understanding how I need to format both the original data and the pivot table to get the results that I want. I ...
0
votes
1answer
46 views

Excel pivot table recursive hierarchy

This is my input data (sample) Excel pivot table currently gives me this However I want an output like this Can pivot table be configured to give that output? Or is there any other tool I can use ...
0
votes
0answers
22 views

Pivot table - display items with no data

I have a problem... I want to make a pivot table with multiple items in the row section (projects, revenue-type, and 2 more), together with a filter (revenue-type). I then want to display all values (...
0
votes
0answers
14 views

Error “Cannot group that selection” in pivot table

I was trying to group the time in a pivot table and sort them by "Hours", and the error "Cannot group that selection" pop up when I did that. I know that this error could occur ...
1
vote
2answers
48 views

Pivot auto date filter in vba

I have issue while filter today date in VBA. Because the format changed for each user. How to set it as universal format? My code: 'FILTER TODAY DATE IN PIVOT Private Sub Worksheet_Activate() On ...
0
votes
1answer
23 views

Referencing a Cell in DAX Formula Excel

I have imported a large data set into the data model in Excel and am working on a pivot table from it. I want to create a measure that can reference a cell in the worksheet. Currently I have =SUM(...
0
votes
2answers
40 views

Pivot Table to count number of products at multiple locations

This is my data sheet below and I am trying to create a pivot table to count the number of products at each location desired end state I used the location 1 as the row and add the counts of the ...
0
votes
1answer
26 views

Cannot Reference Pivot Table headers due to header formatting

I am writing a SUMPRODUCT function that references headers in a pivot table for my report on a separate sheet. The header that is giving me an issue is the year, i.e. 2020, 2021, etc. I have tried ...
0
votes
2answers
31 views

Need to create a pivot table but many years across with monthly data

Sorry if this has been asked before. I have a month wise product wise sales report spanning across 5 years. Its only the quantity that is used in this data set. Its in excel and rows are starting with ...
0
votes
0answers
18 views

Calculated Column based on year over year increase

Pivot Table Settings Picture Example Pivot table row:[Picture here][2] https://i.stack.imgur.com/0qtey.png Basically, I have a pivot table that is calculating the percentage of cancelled policies per ...
0
votes
1answer
29 views

How to group dates in pivot table day wise?

I want to present my data like this: My dataset looks like this: I tried group selection in pivot table tools but couldn't get the results I want. I could only get this:
2
votes
1answer
40 views

Count 2 or more columns using Pivot table

How do I get the pivot table showing the total number of colours available in all the shops. Something like: Black - total count White - Total Count Blue - Total Count
0
votes
0answers
17 views

Scenario Summary in Excel

I have a problem with Scenario Summary in Excel for Mac. The results does not make sense when I try this, as the differences between the scenarios are too small. When I try to do the same procedure ...
0
votes
1answer
25 views

Get count of categories present in a column in separate columns in pivot table in excel

I have a pivot table which looks like Now I want to get count of each category in State column in a separate column. Something like this. I tried creating a pivot table, but the format is not what I ...
0
votes
0answers
27 views

How to convert a pivot chart to a (pivot) table?

I've made a number of pivot column charts. Here's an example: (I'm sorry for the bad outlining of the image.) I'd like to convert these charts to a table like this: Question: is there a way to ...
0
votes
1answer
11 views

How does one ensure only relevant data points are included in a pivot chart in Excel?

I'm currently working with pivot charts in Excel for the first time and I'm having a bit of trouble with it. The following image provides an example of what is not going entirely according to plan: ...
0
votes
0answers
11 views

Excel Pivottable Source

I see some other discussions about data source but none I see covers my problem. I developed a VBA program about 10 years ago for an organization I am a member of. Every once in a while I need to make ...
1
vote
1answer
15 views

LibreOffice equivalent to SQL “count(*) group by some_column”

I have a spreadsheet A | B ----------- alice | 100 bob | 100 carol | 200 dave | 100 edith | 200 I want to basically count(*) group by B, the result being 100 | 3 200 | 2 How to do this in ...
2
votes
0answers
32 views

Excel Pivot Table Calculated Field depending on Column Name

Is there a way to create a calulated pivot field, which calculates an average value of some columns depending on the the column name? I have there a pivot table which has year columns and I want to ...
0
votes
0answers
27 views

Excel/VBA - Fill color of a cell in pivot table according to column value in source table

I want to make a pivot table to control clients' payments. This pivot table has as source a table in another sheet, like this (I made a simpler table just to use as example): Source table I want the ...
0
votes
1answer
58 views

Pivot table counts blank cells as data

I created a pivot table which is drawing data from couple of columns filled with formulas. The formulas start with iferror and in cases of error displays blank (""). However, the pivot table ...
0
votes
2answers
58 views

Merge filters in a pivot table (Excel)

I have a very simple pivot table that has multiple filters. Example of source worksheet: Pivot table source The pivot table just lists all the Employee names (e.g. Employee A) and provides 5 filters ...
0
votes
0answers
12 views

Microsoft Excel 2010 - Pivot Table - Using Data Points as Y axis

I am trying to create a pivot table like the one below and can't seem to get it to work. I want to have the vendor names on the y axis and the category on the x axis. Can you help me?
0
votes
0answers
14 views

Creating a pivot table with data based on drop down values

So I have 3 sheets in my spreadsheet. One sheet contains the drop down values, one is the processed data that I want to convert into a pivot table and the other is a spreadsheet of my raw data. ...
0
votes
0answers
115 views

Mac excel shortcut for pivotchart wizard

I saw somewhere that the pivot wizard on the Mac Excel disappeared with the 2016 update. Even if the wizard is gone, maybe there still is a keyboard shortcut that allows you to create a pivot table ...
0
votes
1answer
179 views

how do I fix these issues Pivot tables and/or the IF formula in Excel 365?

I am managing a waitlist spreadsheet with many tab that requires monthly data to be extracted e.g. number of referrals, wait from assessment to intervention in wks etc. People move from the referral ...
0
votes
1answer
18 views

How to filter and clean the pivot table in excel?

I have a table in excel. I want to create a pivot table where: Take the final value of capital left on that day if there are more than one trade on the same day. In my example below, since there are ...
0
votes
1answer
56 views

Pivot Table with Multiple Columns having the same Categorical Values

I'm hoping to build a pivot table that has multiple columns all using the same type of categories. However, I don't want them to create a hierarchy since I just want a summary for each column. Here's ...
0
votes
1answer
66 views

Quickly select multiple fields from “PivotTable Field List”

Is there a way to quickly select multiple fields from the "PivotTable Field List"? (operation in PivotTable only, not touching the raw data) Taking dataset “Tokyo Metro Stops” as an example. ...
1
vote
1answer
22 views

PivotTable Grouping multiple colums with qualitative data

I have a large table, with for each row 1 to 3 qualitative data in three columns (Same list of possible values in each column). I would like to export the data into a PivotTable that merges these ...
0
votes
1answer
41 views

Pivot table averge by total number of months

I have an excel data table which have these colomns, Purchae date Item name Quantity Sipplier name Now i want to get average of quantity by total number of months. I.e quanty sum is 50 and this is ...
1
vote
2answers
311 views

How do I filter a pivot table by a date range?

My client has a pivot table and he'd like to be able to filter by date range. The data has start dates and end dates: He'd like to be able to put in a filter for 10/1/2020 and have the pivot table ...
0
votes
2answers
23 views

pivoting unique values in excel

I have data with insurance policies and I want to pivot on the amount of policies sold per month. A pivot table summarizes the count of the rows of the policies, even though there are duplicate ID ...
0
votes
0answers
12 views

Merge Tables with similar columns that need to be rounded

I have data coming from 2 different devices sometimes in ms sometimes in seconds sometimes in minutes and from the other device always on a second basis. I am trying to align the 2 data sources in ...
0
votes
1answer
28 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 ...
0
votes
2answers
285 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
0answers
33 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
1answer
22 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 ...
0
votes
1answer
82 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
2answers
57 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
1answer
23 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
1answer
64 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
0answers
36 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
1answer
69 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
0answers
32 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
1answer
33 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 ...

1
2 3 4 5
15