7 ms·
I'm "the excel guy" for a lab of about 100 people, and it is indeed a challenge sometimes. Mostly I just yell at them and tell them to stop using excel sheets a
by onychomys 8y ago
I'm "the excel guy" for a lab of about 100 people, and it is indeed a challenge sometimes. Mostly I just yell at them and tell them to stop using excel sheets as makeshift databases. Then I yell at them some more and make them switch from formulas (which, being written in reverse Polish, are terrible to debug) over to macros. Then I yell at them for recording macros instead of writing them themselves. Then I just do it for them. It's not a ton of fun, but the money is good and the yelling is cathartic.
- da_chicken 8y agoMy most dreaded words with Excel are: > Can you help me with VLOOKUP? Followed shortly by: > I'm having trouble with a Pivot chart.
- kchoudhu 8y agoI don't get people who have trouble with Pivot tables. Literally drag and drop things until you get what you need.
- da_chicken 8y agoPeople who have trouble with pivot tables almost universally have not organized their data in a way that a pivot table can use. They have a vision in their head of how they want the chart to look, and they can't get it to fit because the interface doesn't allow it or their data isn't organized correctly.
- function_seven 8y agoIf you're doing a simple Pivot, sure. But here are the things I run into constantly. 1. When I group by days (i.e. group by 7 day chunks), why does Excel refuse to sort chronologically? 2. How do I make my Grand Total column add up all the subtotals when those subtotals are averages of the underlying records? (It will only do an overall average) 3. Why can't I collapse just this row without collapsing the counterpart rows inside other groups? 4. Why can't I make a chart of just this part of the Pivot Table (rather than the whole thing)? 5. Every time I refresh the PivotTable, new column headers are a different font size/alignment/wrap. I have to manually update the formatting each time. Why? (It won't let me set font formatting in the PivotTable Styles) 6. My PivotTable won't let me add a new item to an existing group. It instead adds a new level to the group hierarchy and the whole thing blows up. 7. Whenever I try to hide the last row in a certain PivotTable, I get an error message: "You cannot hide this selection." Why?? If I move the row somewhere else, it then lets me hide it.
- intended 8y agoThis guy excel troubleshoots. God so many of those are infuriating the first time you encounter them. On the flip side once you get the hang of it, you know right quick how you need your data set up.
- PeterisP 8y agoAre there actual answers to these questions, or are they all "tough luck. now that you have an expert's confirmation that it's impossible, stop trying and go on with your life" ?
- function_seven 8y agoMost are "tough luck" ones, a few are expert-help required. The most common way to solve a pivot table problem is to add a column to the underlying data. Kind of a "manual pre-pivot" in a lot of cases. There aren't direct solutions to the list of problems, but there usually is a workaround. Some of the problems (like being unable to format dates when grouping by days) are just "tough luck" I don't know what's going on with the inability to filter out the last item of a pivot table. One of these days I'll chase that down. I can't be the only one.
- shostack 8y agoOr, if you're fairly confident the pivot is fairly static and you just need to tack something on, then don't be afraid to do it if a custom calculation isn't solving it for you. It surprises people but there's really no reason you can't do that if you just need it to work and/or look a certain way.
- function_seven 8y agoI often end up doing that, but it feels “dirty”, you know? And I also underestimate how long-lived my report will be, and get stuck having to move those sideline calculations out of the way when I need to refresh the pivot (“This will overwrite existing data.”) Which brings up another gripe: why can’t the Pivot allow me to push those rows or columns down/over instead of clobbering them?
- crooked-v 8y agoHave you considered trying to get people to use something like Jupyter or R Notebook?
- throwanem 8y ago"Now you have two problems."
- crooked-v 8y agoDebugging R code, even badly-written R code, seems like it'd be less hassle than debugging layers of formulas and auto-written macros.
- irrational 8y agoCan you help me with RegEx in Excel?
- intended 8y agoCurious- what’s the issue you are trying to solve ? There are Addins which give regex support as I recall. Usually though it’s a matter of find/right and other hackery to make it work.
- irrational 8y agoSorry that was a joke. You know, "Some people, when confronted with a problem, think 'I know, I'll use regular expressions.' Now they have two problems."
- peatmoss 8y agoPast a low complexity bar, I feel like R is a much easier answer than Excel. Excel can get pretty hairy pretty fast. As you mention below, debugging even terrible R code is easier than debugging Excel.
- ams6110 8y ago
- PascLeRasc 8y agoThere needs to be an Excel "pro mode" where the mouse is disabled and you have to do all navigation by keyboard. People would become so much more efficient that way.
- jstanley 8y agoIf "pro mode" is optional, it would be no more effective than the existing "pro mode" of simply taking your hand off the mouse.
- function_seven 8y agoI'd use it. If there was a pro mode buried in a sub-dialog of the options panel, I would turn it on and see if I could make it for 10 minutes. Then 15, then 20 and so on. Instead of reaching for the mouse when I wanted to give up, I'd have to click on [File] > [Options] > [Advanced] and clear the "Enable pro mode" checkbox. Wait... no. I'd have to enter [ALT], F, T, ↓, ↓, ↓, ↓, ↓, [ALT]+R (to get to Pr̲o Mode), then [SPACEBAR] :)
- cm2187 8y agoAnd where hitting F1 doesn't launch some useless help app.
- froindt 8y agoI physically removed the F1 key from my keyboard. Particularly useful since I use F2 to edit cells. I mess up a lot when I help other people on their computers though.
- intended 8y agoHaha, that’s how analysts were trained at one of my old firms - they just removed the mouse and forced everyone to get work done in short deadlines. One of the rare times a gaming habit and keyboard skills was an advantage.
- PascLeRasc 8y ago
- asdkhadsj 8y agoDoes it get in the way of your real job? Or is that your real job? edit: by real, I mean the main duty of your job / what you were hired for. No implication meant haha
- cpeterso 8y ago> I just yell at them and tell them to stop using excel sheets as makeshift databases. What do you recommend to them as an alternative for makeshift databases? There could be a nice product opportunity for a lightweight database web app that is like Microsoft Access or a spreadsheet without formulas. Airtable probably fits that bill, but even it might be more complicated than many people need.
- mr_toad 8y agoIn a small organisation use PostgreSQL. In a large organisation (assuming the IT department won’t let people create ad-hoc databases, which is normal), use SAS, R, Python or whatever tool is available.
- thibautg 8y agoPostgreSQL should be accessible by default to anyone. And Excel should require approbation from higher management and IT after a painful bureaucratic procedure. Unfortunately it’s always the other way around. Don’t get me wrong: I really like Excel. But I don’t encode any data in Excel sheets anymore. Now I store everything in a PostgreSQL DB and use ODBC for native access and Pivot tables.
- Too 8y agoSqllite is probably the easiest makeshift database. There are tons of applications for graphically viewing it and with python which comes with a built in sqllite-module programmatic access is so easy. Otherwise nowadays with Docker almost any DB could be spun up locally within seconds.