What's the general consensus on pivot tables? Apparently my manager hates them.

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

πŸ‘︎ 12
πŸ’¬︎
πŸ‘€︎ u/KalTheMandalorian
πŸ“…︎ Jan 21 2022
🚨︎ report
Pivot table grouping has Starting At, Ending At, and Number of Days greyed out, but can still group by years/Months

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

πŸ‘︎ 9
πŸ’¬︎
πŸ‘€︎ u/benjymouse42
πŸ“…︎ Jan 20 2022
🚨︎ report
When refreshing a Pivot table, how do I fix [Expression.Error] The Key didn't match any rows in the table?

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?

πŸ‘︎ 3
πŸ’¬︎
πŸ‘€︎ u/Guess-Individual
πŸ“…︎ Jan 26 2022
🚨︎ report
For sharepoint excel how do I set it so you only see your changes? This spreadsheet is strictly a pivot table with slicer.

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?

πŸ‘︎ 16
πŸ’¬︎
πŸ“…︎ Jan 19 2022
🚨︎ report
Pivot Table or Other

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.

Username training 1 desc 1 Expired
training 2 desc 2 Current
username 1 training 1 desc 1 Expired
πŸ‘︎ 5
πŸ’¬︎
πŸ‘€︎ u/Rocknbob69
πŸ“…︎ Jan 29 2022
🚨︎ report
Combining forecast & actuals in one pivot table

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.

πŸ‘︎ 17
πŸ’¬︎
πŸ‘€︎ u/ThatGuyOverYonder
πŸ“…︎ Jan 22 2022
🚨︎ report
Pivot Table sum incorrect even after refresh/no filter

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!

πŸ‘︎ 2
πŸ’¬︎
πŸ‘€︎ u/waterwings91
πŸ“…︎ Jan 25 2022
🚨︎ report
Calculate Z-Scores (=STANDARDIZE) in a Pivot Table

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:

Name Subject Exam Mark (%)

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!!

πŸ‘︎ 9
πŸ’¬︎
πŸ‘€︎ u/Melack70
πŸ“…︎ Jan 24 2022
🚨︎ report
How to plot a pivot table with 1500 rows so that it becomes readable.

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

πŸ‘︎ 3
πŸ’¬︎
πŸ‘€︎ u/depressedpear1
πŸ“…︎ Jan 23 2022
🚨︎ report
Re-arranging a pivot table to display Values Vertically across the row

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

πŸ‘︎ 3
πŸ’¬︎
πŸ‘€︎ u/Valodyjb
πŸ“…︎ Jan 21 2022
🚨︎ report
Is there a way to sort ordinal data in a pivot table?

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.

πŸ‘︎ 5
πŸ’¬︎
πŸ‘€︎ u/StrokesAndSpokes
πŸ“…︎ Jan 30 2022
🚨︎ report
Excel pivot tables: Whenever I enter new data, the pivot table displays it at the top, I want it at the bottom

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.

πŸ‘︎ 3
πŸ’¬︎
πŸ‘€︎ u/Happy_Trails4u
πŸ“…︎ Jan 20 2022
🚨︎ report
Query join related tables and pivot

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?

πŸ‘︎ 5
πŸ’¬︎
πŸ‘€︎ u/newtrarat
πŸ“…︎ Jan 17 2022
🚨︎ report
Pivot tables should be taught in freshman year imo
πŸ‘︎ 4k
πŸ’¬︎
πŸ‘€︎ u/Rhedogian
πŸ“…︎ Oct 11 2021
🚨︎ report
Pivot table giving wrong count of text values

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

πŸ‘︎ 5
πŸ’¬︎
πŸ‘€︎ u/savvyjiuju
πŸ“…︎ Jan 10 2022
🚨︎ report
Pivot Table - two data columns/rows having similar values - seeing them together for pivot?

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

πŸ‘︎ 3
πŸ’¬︎
πŸ‘€︎ u/cartoon_soldier
πŸ“…︎ Jan 25 2022
🚨︎ report
Pivot Table with a ton of details, macro buttons for sorting options? Any opinions on this?

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

πŸ‘︎ 9
πŸ’¬︎
πŸ‘€︎ u/VeeBeeA
πŸ“…︎ Jan 06 2022
🚨︎ report
Creating Pivot Table Using Multiple Tabs of Data

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!

πŸ‘︎ 2
πŸ’¬︎
πŸ‘€︎ u/lilabelle12
πŸ“…︎ Jan 16 2022
🚨︎ report
How do I share a large dataset in Excel so people can use pivot tables for custom views?

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.

πŸ‘︎ 3
πŸ’¬︎
πŸ‘€︎ u/sawmillionaire
πŸ“…︎ Jan 14 2022
🚨︎ report
Changing external data source for all pivot tables at the same time using VBA

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

πŸ‘︎ 2
πŸ’¬︎
πŸ‘€︎ u/laganini10
πŸ“…︎ Jan 04 2022
🚨︎ report
Bought an allegedly very old table. The top pivots up (spring latch underneath). Can anyone date it? reddit.com/gallery/sezfoc
πŸ‘︎ 6
πŸ’¬︎
πŸ‘€︎ u/newwavegoodbye
πŸ“…︎ Jan 28 2022
🚨︎ report
Pivot table not recognizing dates in the way I need them to

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?

πŸ‘︎ 2
πŸ’¬︎
πŸ‘€︎ u/corago513
πŸ“…︎ Jan 16 2022
🚨︎ report
Looking for resource - Creating a data model and a pivot table

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.

πŸ‘︎ 2
πŸ’¬︎
πŸ‘€︎ u/Mountain_Tax_6264
πŸ“…︎ Jan 25 2022
🚨︎ report
Just started at a new company. They told me to avoid pivot tables because they’re too difficult to work with.

FML

πŸ‘︎ 283
πŸ’¬︎
πŸ‘€︎ u/Shfifty_Five_55
πŸ“…︎ Oct 14 2021
🚨︎ report
Is there a way to have individual users only see their changes on sharepoint excel? This is for a pivot table/slicer

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.

πŸ‘︎ 2
πŸ’¬︎
πŸ“…︎ Jan 19 2022
🚨︎ report
I know this new character trend is overrated BUT after doing a pivot table with all the data (mbti, enneagram, socionics) of these characters from PDB, I find the same results I was assigned with the cognitive function study
πŸ‘︎ 2
πŸ’¬︎
πŸ‘€︎ u/tiakiwi
πŸ“…︎ Jan 28 2022
🚨︎ report
How to return parent total from a pivot table where the value above a certain number?

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

πŸ‘︎ 2
πŸ’¬︎
πŸ‘€︎ u/xyzabc123410000
πŸ“…︎ Jan 02 2022
🚨︎ report
Pivot table combining and summing similar but not exact rows and printing "3" to the table

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

πŸ‘︎ 3
πŸ’¬︎
πŸ‘€︎ u/Essthrice223
πŸ“…︎ Dec 30 2021
🚨︎ report
Filtering specific dates in a pivot table

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?

πŸ‘︎ 3
πŸ’¬︎
πŸ‘€︎ u/poopsmith3000
πŸ“…︎ Jan 05 2022
🚨︎ report
Copying a worksheet with a pivot table pulling in from an external source, copied worksheet will refresh with new data

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?

πŸ‘︎ 2
πŸ’¬︎
πŸ‘€︎ u/tytds
πŸ“…︎ Jan 13 2022
🚨︎ report
How to do Pivot Table % of Grand Total (Entire Table)

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

πŸ‘︎ 2
πŸ’¬︎
πŸ‘€︎ u/Haigfish
πŸ“…︎ Jan 27 2022
🚨︎ report

Please note that this site uses cookies to personalise content and adverts, to provide social media features, and to analyse web traffic. Click here for more information.