Pivot table Puns

So now I need to do the same reconciliation, but with formula instead of a pivot table.

When a date in a pivot table is right clicked and Group is selected, the fields for Starting At:, Ending At:, and Number of days are greyed out. It is not a date format issue as the dates can still be grouped by Months, Years, and Quarters. Has anyone else experienced this?

Edit: adding picture and to say I am using Excel 365

https://preview.redd.it/etvxc6aa8uc81.png?width=568&format=png&auto=webp&s=a67ac3b0ed6a111319f02204a0536ff89c1af778

I have a pivot table in Excel that is connected to a folder with several workbooks. Every month I refresh it without any problems. This month when I refreshed it I got an error saying:

[Expression.Error] The Key didn't match any rows in the table

I spent a lot of time online reading about this and tried different solutions but nothing worked. I haven't changed anything in the particular workbook that's giving me the error. Also, all the workbooks have the same Table name and the same columns.

Any idea how to solve this issue?

I need to share this with lots of people and I wanted the user to only see what they are doing so multiple people could be in at once but I can't get it to work. Any ideas?

I have some columnar data where there are user names repeated for trainings they are due or have expired. I would like to have the user name shown once on the left and subsequent rows for trainings repeated down until the next user.

I want to construct a sheet that helps me forecast & track my personal spending.

The forecast will be a table estimating the spend per month per category (e.g. $500 on groceries in February; $100 on fuel, etc.). Months would be the column headers, and categories will be the row headers.

Complementing this would be a table that is an extract of my bank statement, where I will categorize each line item (e.g. credit received on 16th January is my salary; I spent $20 at the supermarket on 18th January, etc.).

Naturally the categories between the the two tables will be aligned.

My question is, how do I get a monthly view through power pivot which combines the forecast & actual spending? E.g. The columns up to January 2022 will be the actuals, and the columns from February 2022 onwards will be the forecast?

I am anticipating four tables right now: the date table, the category table, forecast data, & actual data.

I have a pivot table that is not summing correctly.

The sum for the column when selected is $43,619.76 but the pivot table shows $43,231.39.

I have tried removing the column from the pivot table and re-adding, tried refreshing, removing all filters, etc.

I have drilled down on the pivot table and both there are 39 entries in the pivot table and in my original table selection, so I'm not missing a number and didn't pick up an extra or anything.

I've noticed that there is a difference in the data picked up (ex. $567.23 on the original table is showing up as $562.18 in the pivot table drill down, same for $3403.36 -> $3373.06).

This is happening with every row in this table and I don't know why it's picking up the wrong number.

Any help would be great.

Thanks!

I've got a bunch of clean exam mark data from a whole cohort of pupils and I'd like to look at how the z-scores vary per pupil per subject. The data for is arranged as follows:

Using a Pivot Table, I can easily get the average and standard deviation per subject, but I'd really like to get the z-score per pupil per subject. Any ideas on how I might do this?

It doesn't have to be in a Pivot Table, I'd be happy to add this to the main table of data and then pull it out again through a Pivot Table.

Thanks!!

When i am trying to plot the graph it is way too small and the individual elements are not even readable. Is there any solution to it like can i add a scroll chart in pivot table with 2 row headings (date and then its 24hr distribution) or a button to zoom in and out of it.

https://drive.google.com/file/d/1sxafsQ5KXglvvtv9DvsX-gF20eUWSi1n/view?usp=drivesdk

I need help changing my pivot table to display data a certain way, I have tried fiddling with which fields I put things in and the options but it doesn't quite do what I need.It doesnt have to be a pivot table, it's just that pivot table is the only way I could think of to do this (Besides manually).

This is the original data list, as you can see some rows repeat because it is associated with multiple names.

When I do a pivot table, the best it does is the rows 3,4,5. But now columns repeat. I want it to look like rows 8,9,10. Where if multiple names match, it just in the same column.

The currently populated fields if that helps

How can I achieve this. Or is my only option to do this manually?Thank you for your help.

Edit: Added pivot table fields

Assuming we have some ordinal data such as grades (A+, A, A-, B+, B, B-, etc.) is there a way to sort this data in a pivot table as sorted above, where A+ is at the top and B- or even F is at the bottom without doing so manually?

When I create this pivot table, if I attach numerical grades to this I can sort perhaps where the numerical grades follow, but I am interested in seeing if there is a way where I can use the ordinal data only to essentially stack rank these values.

Using Excel 2019 for reference and do not have much experience with Excel.

Any thoughts are appreciated.

Hi All.

One of my pivot tables has the months for 2021 and when I enter the months for 2022, it appears at the top of the pivot table. I need it at the bottom.

Hi guys, I was hoping someone could help me with a query. I have 2 related tables. tcp_EmployeeWork.CostCode and tcp_Company.CustomFieldValue. The columns from the CostCode table I need are FullName and Status. The column I need from the CustomFieldValue table is Value and I need the Value to pivot as there are 1 to many relations. The relation between the tables is costcoderecordid from the customfieldvalue table and record Id from the costcode table. I've tried to write the query a few different ways and keep getting some errors. Ambiguous column on the RecordID column, etc. I'm just having some trouble with this query. Can anyone help?

I'm trying to create a pivot chart for a small range of data. Every time I create a pivot table, the count is wrong for some of the columns. You can see in the screenshot that the count for "Satisfied" is correct for some columns and incorrect for others.

I used Power Query to remove blank rows, then just made a pivot table with chart for the range including headers. I've done this two separate times in case the problem was the source data, but the problem persists. Several pivot tables made in the same way in another workbook appear to have counted perfectly (although now I'm thinking I should go back and do a manual recount).

Can anyone figure out where my problem is? Did I screw up something basic, or have I run into one of those insane corner cases? Fingers crossed for it just being my lack of experience.

Edit: I just tried changing the legend input to the "Topics covered" column and it changed things to a different wrong count. So I'm narrowing it down, but I still don't know how to ensure that "Legend" is populated by an input that would create the correct count... or if I'm just running in a circle next to the actual problem.

https://preview.redd.it/mvwm1sp7dxa81.png?width=1845&format=png&auto=webp&s=75f72d6980cd11fed3f7852a2d8deca47ec7138c

I was wondering if I have an excel table where two columns have the same values on the pivot I can combine them to see the data together?

For example:

A table such as below:

https://i.imgur.com/pKo3zCN.png

I want to Pivot on it to see how many times is a type of cuisine being served across both lunch and dinner. If I put in a Pivot, the result I get is as: https://i.imgur.com/4RSZLIf.png

Instead I would want to see 1 row for American and then the count of when American cuisine is being done for Lunch and Dinner separately. Like:

Row Labels Count of Dinner Place Count of Lunch Place

American 1 1

French 1 1

Italian 0 1

Hopefully this made sense.

Thanks

My boss says he wants all this information in one table and I'm trying to reduce the chaos. Image below.

I'm not expanding the Item Desc. on this image, but the numbers get even more annoying to read. I was thinking of creating sorting buttons.

Sort Plant 1 - Shipped Largest to Smallest, Sort Plant 1 - Produced Largest to Smallest, etc.

Recording the macro, I would have to expand and sort each Row field, I'm not sure if that would mess up his place in the report (what he's expanded/collapsed).

I'm not exactly sure what my question is, any opinions/advice I guess. I think this was mostly cathartic to vent.

https://preview.redd.it/o5twvaspd4a81.png?width=657&format=png&auto=webp&s=46956d6f9ca286c9557618aae9d290e3795e2708

Reposted for an updated flair: I’m trying to create a report in which I would be able to combine all the monthly data from all tabs within the excel sheet into a pivot table to get a summary of all the usage per quarter/year. So my excel sheet consists of the following for your information:

• Each tab of the excel sheet is labeled by the month the data was pulled (Jan, Feb, Mar, etc.)
• Each monthly tab data has each row listed by user name and the columns after the first column contains around 5+ different usage metrics (total shares, last login, etc.) based on each user
• Each of the data in the columns are all numbers with exception to the user name and I want to sum up all the numbers in the summary pivot table

How can I efficiently and easily get this done on a quarterly and yearly basis?

Thank you!

I put together a large file containing sales data for my company going back to 2018. I want to be able to share a blank pivot table across the org that is working off this data source so that people can slice through data as they see fit. Is there a best practice for this?

Currently, I have the pivot table in a separate workbook from the raw data file and both saved on my OneDrive. I am then sharing out the pivot table file for people to use as they see fit.

Is there a better way to do this so that the file works a lot faster and it easier to share out? I won't have IT resourced on this so more advanced solutions are probably not in the mix for now.

Hello,

I have an excel workbook which has about 30 pivot tables in it that are all connected to the same external excel file (data source).

I have been reading a lot about how to change multiple pivot tables at the same time using VBA and it can be done if the data is in the same file, however when an external data source is used than I cannot use "ChangePivotCache" line of code in VBA anymore.

Anybody has any idea on how I could solve this issue using VBA macros or any other way for that matter.

Would really appreciate your help!
Thanks a lot!

FYI, this is the best I found so far:
https://stackoverflow.com/questions/2522749/updating-data-source-on-multiple-pivot-tables-within-excel

Hi, I am trying to do a count by date, i.e. how many kits were handed out on 12/21, how many on 12/22 up through today. This data is being exported from SmartSheet, if that matters. When I run the pivot table, the dates will only sort my month, quarter, year, not the actual date. I have tried using a date format and the general format and no luck wither time. I feel like I have done this before, so I cannot figure out what I am doing incorrectly this time. Any suggestions?

My data has monthly snapshots of various counts based on status(5 status) for each city (12 cities). I have to build a pivot table showing counts of a status by city. Then I need to show the totals and then month over month change percentage.

Could someone please point me to a good resource where I can understand how the data should be modelled to be able to achieve this?

Thanks in advance.

FML

We have a different spreadsheet/table that when you make changes it asks if you only want to see your changes but on my pivot table with slicer when someone else goes in it never asks that. Anyone have any ideas on how to fix this? Lots of people will be in this spreadsheet at once.

Hi,

I have some trouble in extracting the parent name of some data that the secondary row fits a certain criteria.

So i get a pivot table in this format (in the image attached), with 2 rows. The first row with a name and the secondary row with a fruit name. The Columns are both values in the pivot table based on the same cata with the volume and the other as a percentage of the parent total which is set as the "name".

Lets say want to return a list of names that have the percentage of "Apple" above 15% and would expect to return consisting of Sue, mick and ritchie (preferably also the volume and the percentage but not essential). How would i go about doing this? I have tried filtering the values of the table but because the "Name" and "Fruit" are in the same column, it is making it tricky for me.

Any ideas?

Thanks

https://preview.redd.it/29fr136ryc981.png?width=774&format=png&auto=webp&s=6a8d4245bf1a588881bc1c6e1bad304a5e2f1501

I am using pivot tables to summaries a large materials export from a cad package into manageable BOM's for procurement. I am running into an issue where possibly 3 distinct item descriptions are being combined that have very similar names. does anyone know why a pivot table would combiner and summaries different data types?

This issue only happens when I am dealing with the base data set, 20k rows. If i do a test with a snippet of the overall data this issue resolves itself.

Possible items being combined

Pivot table with unintended summarized rows

I have a pivot table with about 500 people, the dates they entered our program, and other various information. Currently the pivot table includes dates from 2016-2022, but I only need the data from 2020-2021. I can filter the dates but this results in me needing to individually click each date ( as far as I know ) which results in hundreds of clicks. Is there a faster way to filter this information without creating a new pivot table?

I created a pivot table which pulls in a table I created using PQ, the source of the data is from Salesforce. I wanted to create multiple worksheets based on this Salesforce data, but having to insert a new worksheet, add a new pivot table, and choose the external source, means it will create copies of my query, which will slow down refresh times when refreshing the data every day.

Instead of doing this method, I create copies of my original worksheet with the pivot table, and modify the copies based on what data we need for that copy. Upon refreshing, will the copied pivot tables also update the data, since copies of the query are not being created?

Hi all. I am trying to make a contingency table that can show each table value as a % of the table total, column total, and row total. When I select "% of grand total", it only gives me the % of that column. Do you know how I can do this? THanks

https://preview.redd.it/rmoirbbfa8e81.png?width=1920&format=png&auto=webp&s=39bb66fcff8fadc9148c836c9316513dc44a54e3