Also…teach them to fill in their formulas when they add rows or columns
Nothing ruins my day faster than relying on the “totals” field, only to realize that some a$$hole didn’t fill in the formulas, and now the figures I entered into the tax software are wrong. Now I have to go full in the formulas, fix the spreadsheet, and re-do the entire tax return.
yes. Concatenate is a good one.
An easy way to do that so you don’t have to remember concatenate is to just type =reference cell&reference cell (e.g. =B21&B42). If you want a space between B21 and B42 then you just do =B21&" "&B42
^+1
Right on, was shocked starting at my new firm how few people were really strong with designing sheets. Most people just update & do simple tasks, being able to get experience working your way through designing an making sheets as simple to follow as possible are huge. Having a focus on trying to make the sheet so someone familiar with the field could follow along relatively quickly is usually one of the mosti mportant things in my view.
can powerpivot really handle 100+ million rows of data? Like furreal?
Sometimes the issue isn’t the row but the number of variables or columns. A single row in a sheet can have multiple variables. So it’s best to drop useless columns first. Create a unique id. Remove duplicates. lol I used to clean data. This is how I programmed.
Pivot table does all that basic stuff for you. But sas is more flexible and you can do more things.
^ I’m fairly certain excel has a hard limit of ~1 million rows irrespective of how long your data is. One of its main limitations else everyone would be using it unless their job overlaps with backend software engineering
Hotkeys turns you into a hackermann. I don’t use them but it looks a lot faster (and more efficient) if proficient at it.
VBA is the guy who builds a sick muscle car from scratch, and regular functions and top level tricks like hotkeys are the guy who admires it at car shows, then speeds away in his Corolla, surreptitiously parked in the back.
I think they say power pivot handles unlimited rows. I think technically it can handle 2 billion per table. And the reason people moved on from Excel is that powerpivot is relatively new and not widely known, at least in my experience. Really need 64 bit excel to use that many rows as well
Something like DAX formulas?
INDEX MATCH MATCH. Never knew of this before. Thanks.
Haven’t created an excel sheet in many years. In these newer excel versions is it more practical for two+ people to work on the same spreadsheet and then merge their changes to a single file?
My subordinate has the messiest messiest Excel sheets that he sends to me. He’s so naive that when I ask him about it, he’s like ‘what do you mean’. I feel like i’m trying to teach a monkey to use sticks sometimes.
Is this the guy who sucks and you want to fire, or is it another guy who sucks at just Excel?
Yes, it is the guy who my boss wanted to fire. I was trying to not get him fired. My boss goes back and forth. He’s still here.