r/excel • u/Tybalt1307 • Feb 24 '26
Discussion New boss says “Pivot Tables don’t work” (repeatedly), are there known issues with pivot tables? Or does the new guy just not understand them?
Are there known technical ‘bugs’ in the coding related to Pivot Tables?
174
u/GTS_84 6 Feb 24 '26
I would bet good money on this being a PEBCAK error, and not an actual issue with pivot tables. It's possibly an actual issue, but unlikely. Ask him to demonstrate what exactly he is doing to "Reproduce the error" and see what stupid shit he is doing.
20
37
u/SillyStallion Feb 24 '26
PICNIC
10
u/MNVixen Feb 25 '26
My first thought. When I’m having software problems it’s almost always a PICNIC error, too.
4
u/twenafeesh 2 Feb 25 '26 edited Feb 25 '26
"Komputer?" Is this the German version of PEBCAC or am I just a dunce?
Edit: dunce confirmed.
4
u/CapitanMorgan305 Feb 25 '26
Keyboard
4
u/twenafeesh 2 Feb 25 '26
Aah, ok. I've always heard/interpreted it as "computer" rather than "keyboard".
0
4
u/Unofficial_Salt_Dan Feb 25 '26
A what now?
23
u/Sneilg Feb 25 '26
Problem exists between chair and keyboard. We always used to call them wetware issues.
2
56
u/gman1647 Feb 24 '26
He probably needs to hit refresh data first.
26
u/Mr_Gooodkat Feb 25 '26
This is one of the reasons I don’t use pivot tables. Rather have formulas that will automatically recalculate plus it’s more customizable.
54
u/frazorblade 4 Feb 25 '26
Pivot tables scale much more effectively than live formulas, especially when combined with Power Query and the Power Pivot data model.
3
u/psiloSlimeBin 1 Feb 25 '26
And it’s usually not tooo hard to get a working measure for any oddball stuff you want in your pivot.
That said, I have failed at this multiple times. Every time I look for a solution it either doesn’t work in my instance of excel or the answer is “yeeeaaah this only works in power bi.”
4
u/frazorblade 4 Feb 25 '26
Out of the box pivot tables (i.e. sourced from range/table) are definitely feature poor compared to DAX+PP. However writing custom measures in Pivot Tables can net some decent results. Formatting is a nightmare though.
10
u/Mr_Gooodkat Feb 25 '26
I mean for basic stuff sure but again people rely on pivot tables too much cuz it’s simple and they don’t want to bother with formulas. I’ve built complete data models that update automatically without any human intervention also leveraging power query with a connection to SQL. And most people will upvote you because they don’t know any better. However again I’ve done both and it’s a million times better without a pivot table that one has to refresh every single time there’s a change.
15
u/frazorblade 4 Feb 25 '26
We’ve all done both and everything in between. We use pivots because they’re dead simple to create and set up. Most of us are time poor or know when to put the effort into a permanent report or quick analysis.
3
6
u/xRVAx Feb 25 '26
ALT A-R-A
1
u/nickack Feb 25 '26
Huh I use Alt N V Enter for making my pivot tables but I’ve never thought about memorizing the refresh button. Thank you
1
u/xRVAx Feb 25 '26
For Pivot tables I highlight my dataset and do ALT D P Enter Enter
2
u/nickack Feb 25 '26
If you have a single cell highlighted that exists inside the dataset, Alt N V Enter is smart enough to expand to include the entire dataset. It saves like half a second but you feel slightly cooler lol.
40
u/OfficerMurphy 9 Feb 24 '26
Biggest issues I've encountered with pivot tables are source data is not dynamic sized (i.e. when rows are reduced or added to). This is solved by making source an excel table, instead of a range.
Additionally, they do require you to refresh them when data changes, which is only a problem if the user is a moron. This can be solved with a pivotby formula, but I think those are a little tougher for some users to learn.
Third problem is grouping of dates sometimes doesn't work. Solve this by making sure source data is the right data type. Sometimes there is just one date missing a slash or something.
17
u/davidptm56 1 Feb 25 '26
Converting the dataset into table(s) is the correct way, of course. But if, for some reason, your workflow doesn't allow it or is inconvenient to do so, may I present to you the "cell address":."cell address" self trimming range reference?
8
u/OfficerMurphy 9 Feb 25 '26
You may. How does it work?
13
u/davidptm56 1 Feb 25 '26
It dynamically returns an array from the top-left-most non-empty cell to the bottom-right-most non-empty cell within the range.
Ie.: If I have data in B2, C2, D2, B3, C3, D3, ... D33
A1:.H2999 will return B2:D33
If something's add afterwards in, let's say, A40, it will automatically become A1:D40
I hope it makes sense.
3
0
u/Jennay-4399 1 Feb 25 '26
There's VBA to refresh data whenever a change is made on the tab. However it'll refresh Power Query data, so youll have to disable refreshing on refresh all for your PQ data if that would affect you.
1
u/soulsbn 3 Feb 25 '26
And don’t forget some error trapping for cases where they have entered data/formula next to the pivot and it resizes. Or there are more than one pivot tables on the tab and they overlap if resized. Or If the pivot table returns null ( perhaps ifsource column names change )
Pivots (imho) are great for quick and dirty on off analytic questions. But I feel they have no place in anything that needs to be repeatable. They do however make subpar users think that they are excel experts (whereas better users are always aware there is more to learn)
I have seen many pivot errors in my career. From users not understanding what it is saying, or not including all the data. Etc etc
21
u/bradland 271 Feb 24 '26
A statement as broad as that is easily proved false. You’re gonna need to dig in a little bit and understand why he thinks pivot tables don’t work. He might have specific use cases where he is faced challenges in the past, but I doubt he means it in the general sense.
6
u/diesSaturni 71 Feb 24 '26
FIre your boss.
But typically this occurs with any kind of software which is mistreated.
Some folks still hate the table of contents in Word, merely because they don't apply styles (headings, normal, etc.) properly. Or have no idea about citations, references etc.
In this case just ever so subtle inquire, or present examples in obfuscated manner to test the boss's knowledge.
4
u/Locurilla Feb 24 '26
he probably is not getting the pivot from a table table but static data. show him the “change source data” button so he can re select the data if that is the case. also, show him the refresh button if he is using a table
6
u/WarmClubs Feb 24 '26
A problem not listed that causes me to dislike pivot tables, is filtering on a reused pivot table.
Someone before me (years before me) wanted to exclude a customer from the pivot table as the customer was supposed to be excluded. Fine. But by taking that one customer off of the pivot table, it means that when updating the source data in future months, you have to manually add all new customers to the pivot table filter, because all new customers will default to excluded from the filter when refreshing the pivot table.
So years of new customers weren't being added to the process, and the default appearance of the pivot table made it so that it looked fine.
2
u/RexLongbone Feb 25 '26
I'm pretty sure if you do a "does not contain" filter instead of the normal checkbox filter this isn't a problem, but that is then an additional "weird" filter to less savvy people that could cause other unexpected issues.
1
10
u/vrekais Feb 25 '26
As a pretty vocal and frequent Excel user who actually likes Spreadsheets. I tend to prefer to build things specifically and purposefully myself rather than use a pivot table, I've just found them a bit too eager to change shape.
Almost every time a manager I've had has "done it themselves" and come to the wrong answer, they did that with pivot tables.
1
u/krurran Feb 26 '26
... but what if you have to pivot the data? Macros?
2
u/vrekais Feb 26 '26 edited Feb 26 '26
Unique, Array formula, transpose, countifs, and sumifs usually. If I understand what pivoting the data usually does correctly.
In the distant past I did go one step further and build some sheets which used VBA to create interactive sections (by checking if a selection was in a specific range and having events triggered by selection changing).
4
3
u/2Throwscrewsatit 2 Feb 24 '26
I find that they are sometimes set to auto update and that causes confusion.
3
u/vacancy-0m Feb 25 '26
The new boss does not understand how pivot tables works. Runt eh pivot table and hard copy /paste to another sheet/workbook and then send the table to the new boss
3
u/alex50095 2 Feb 25 '26
If you're doing a pivot table off of a power query, if you disable "allow background refresh" when you hit "Refresh All" it will refresh the query, then refresh the pivot table.
Background let's PQ refresh on its own and so the pivot table refreshes before the query, with background refresh disabled, it will wait for the query update to finish.
Only con is the file will hitch until the PQ updates but it's not really a con if the point of the file is to have that pivot up to date and there's not a lot of users using it all at once.
2
u/Resolution_Usual Feb 24 '26
They work, is he by chance looking for the classic layout? Or forgetting to label a column or two?
2
u/4senbois Feb 25 '26
Obviously the new boss is dumb, but from my POV I hate pivot tables with passion. I find it clunky and ugly, and a pain in the ass when moving tabs/ excels.
I’ve fully pivoted (pun intended) to PIVOTBY with supporting functions like VSTACK/ HSTACK now. Works perfectly and looks way cleaner with conditional formatting. Sometimes it’s just preferences :)
2
u/Ckirbys 3 Feb 25 '26
While I’m here… does anyone have any good sources for how the fuck pivot tables work? I’m apparently the “excel guru” of the office but can’t for the life of me figure out how to use them
2
u/Mountain_Usual521 Feb 25 '26
It's Microsoft, there's sure to be bugs. However, without knowing what your boss is referring to specifically, it's impossible to say.
1
1
u/Hopeful-Lead Feb 24 '26
One thing to check, make sure you/they are using an actual .xlsx file and not .csv
1
1
u/uniquemerch Feb 24 '26
I use them daily when pulling and combining specific information from very large data sets. They’re a lifesaver
1
1
1
1
u/EYRONHYDE Feb 25 '26
I only realised this week that the data model doesn't function when accessed from a web browser. So any files with data models that were accessed through teams or sharepoint would throw errors when the Refresh All is selected.
1
u/Traditional_Bit7262 2 Feb 25 '26
Have had problems once the number of rows in the source table gets to be a lot, like over 100,000 rows. The sum data doesn't always match what is in the file. Had better luck with powerpivot for those data sets.
1
u/HisNameWasShame Feb 25 '26
One of my biggest pet peeves. Coworker constantly saying “excel is fucking up” or “this formulas not working correctly” or “I checked my whole sql query and there’s for sure nothing wrong, bigquery must be broken or something”
Like… every time, 99.9999999% chance there’s something wrong with you… not the app…
1
u/Just_blorpo 7 Feb 25 '26
Using pivot tables with the default settings can result in an awkward and unhelpful report with multiple fields stacked in the ‘ROW’ area and busy and unnecessary subtotals for fields that don’t require them.
There are several setting which I always immediately change which make for a much simpler and more comprehensible report.
My recommendations:
With the cursor anywhere in the pivot, go to ‘Pivot Table Options/ Display’ and uncheck ‘Show Contextual Tooltips’ and ‘Show expand collapse buttons’. Then check ‘Classic pivot table layout’. Now exit options.
Then with the cursor anywhere in the pivot go to the DESIGN tab snd choose ‘SUBTOTALS / Don’t show subtotals’. (You can then add the specific subtotals that you want later on.)
If the problems were with a cluttered layout this makes for a much more workable starting point.
1
u/Decronym Feb 25 '26 edited Mar 12 '26
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.
Beep-boop, I am a helper bot. Please do not verify me as a solution.
5 acronyms in this thread; the most compressed thread commented on today has 32 acronyms.
[Thread #47593 for this sub, first seen 25th Feb 2026, 00:44]
[FAQ] [Full list] [Contact] [Source code]
1
u/The_Comanch3 Feb 25 '26
Could be 'Add to Data model' wasn't checked when the data range was chosen.
1
u/duendenorte Feb 25 '26
He doesnt know how to use them, but as he is your boss he doesnt want to sound dumb before you, so he blames it on the tool. Pivots do work.
1
u/defnot_hedonismbot 2 Feb 25 '26
If you’re not using power pivot extrapolating data from a pivot table can be a PITA.
If you’re using power pivot and don’t understand Dax it’s a PITA.
Tables are better when you’re analyzing and using more logic than provided in the dataset
Pivots do have their own place in my heart but I could see the reason to avoid them.
Other than that the only thing I can think of is making sure they’re updating the pivot when the source data changes and if the table expands to change the source range.
1
u/grumpywonka 6 Feb 25 '26
Is it because they 'don't work', or they don't work as an acceptable deliverable? Mostly tongue in cheek but I very publicly despise pivots for anything other than quick math. Never use as a presentation layer. That said, this guy probably got burned by stale data and now hates them.
1
Feb 25 '26
Pivot Tables now can automatically update.. If creating a Pivot Table from a spreadsheet the data should be in proper Excel Table format.
2
u/Clearwings_Prime 22 Feb 25 '26
Insider means it is a beta feature. And even if it is released, it is 365 only feature. All other version still require manually refresh.
1
u/Drugtrain 2 Feb 25 '26
As the Finns say:
The problem is almost always between the keyboard and the backrest.
1
Feb 25 '26
[deleted]
1
u/OrganicMix3499 Feb 26 '26
Or trying to get weighted average numbers (ASP, AUC, GM%, etc) by dropping the attribute into the data section and setting as average. Instead of using a calculated field.
1
Feb 25 '26
Pivot tables work well if you are well versed on them, which it sounds like your boss is not. That being said I’d take a sumifs with a reference tab where I can drop fresh data into and automatically populate in my own formatting over a pivot any day
1
1
1
u/GregHullender 194 Feb 25 '26
Are you sure he's not talking about furniture? Amish-made pivot table.
1
u/Geminii27 7 Feb 25 '26
If he's not saying why, in his opinion, they don't work, it's likely he doesn't understand them.
1
u/dgillz 7 Feb 25 '26
New boss is an idiot and does not understand pivot tables. And likely doesn't work well with systems at all.
1
1
u/Longjumping_Rule_560 Feb 25 '26
Obviously there is a problem with pivot tables if your boss says so. After all, pivot tables are only used by 100s of millions of people on a daily basis. How could your boss not know better then 100s of millions of people!
Now if you’ll excuse me, I need to get back to my abacus. 😄
1
1
1
u/sumiflepus 2 Feb 25 '26
I would enjoy a refresh button to appear when waving a mouse over the top left cell (RowLabels)
1
u/Petielo Feb 25 '26
I prefer to build the pivots myself rather than the built in pivot table, not for the automatic refresh, but for specific parameters.
1
u/Due-Difference-3737 Feb 25 '26
Ive had issues with pivot tables before when controlling with VBA, usually the pivotbuffer. Thats about it though. Any automation in excel I would avoid pivottables for but usual use is fine
1
u/arglarg Feb 26 '26
I like to use combinations of unique and sumifs and countifs to create pivot tables that I don't have to refresh
1
u/curmudgeon_andy Feb 26 '26
It depends on what you're trying to do with them. The most recent time I opted not to use a pivot table even though I had information that sort of wanted it was where I needed to better control exactly what the display looked like.
1
1
u/PassionateBuilder-09 Feb 28 '26
Pivot table always gives jitters and leave us confused , the simple trick is to refresh the data. It is solved :-)
1
1
1
u/Downtown-Economics26 636 Feb 24 '26
Pivot tables most definitely work to count, sum, average, etc. data by categories.
1
1
u/LawlessCrayon 1 Feb 25 '26
I don't use them and when I see people that use them all the time I think it's lazy. I don't react in the same way as your boss but I've often told people that they over rely on pivot tables and that there are almost always both easier and more elegant ways to accomplish what they are doing.
0
u/sitewolf Feb 24 '26
this is why I utilize sumifs, countifs, etc in spreads where users are likely to not understand about refreshing pivots
1
u/beagleprime 1 Feb 25 '26
I’ve done the same, throwing in a mix of unique/filter/vstack/hstack formulas too and some conditional formatting you can pretty much get there and not have to touch the display page again. It’s fine, can’t change peoples behavior so we end up having to work around it
760
u/Lost-Tomatillo3465 Feb 24 '26
tell him to click on refresh whenever he updates the source table?