Excel Tips & Tricks ITT

Help Support Talkbeer:

Joined
Apr 10, 2013
Messages
8,917
Location
Squat Rack
Auto KPIs?? Where is dis? My excel use has decreased in favor of SharePoint but I love a good spreadsheet
Oh, no. I hate SharePoint. Maybe we just do a poor job of supporting it...
The KPIs are great. Conditional formatting has the options and they can be applied to pivot tables where they become especially useful.
 
Joined
Dec 21, 2013
Messages
8,991
Location
FIB
Index match or GTFO

I am a huge fucking Excel nerd and use Excel like four hours a day. HIT ME UP BOYS
Interesting trick. Not sure I'd need it that often but the easier dragging advantage has me intrigued.

----------------

Generally, I don't use a lot of fancy Excel magic because the bar for Excel wizard is so low. I can manipulate the location of rows and columns without screwing up the formulas within them. That alone puts me way above the vast majority of the people I have met in the working world. I also mastered the basics of borders and formatting and the use of the Sorting functions. Those three things together make me appear as a god to most people.

I do a lot of simple things at work. Most commonly:

- VLOOKUPing things to combine tables.
- Using Remove Duplicates to clean up data.
- Using Text-to-Columns to clean up data.
- Basic arithmetic/count formulas up the wazoo.
- CONCATENATE. Everything.

I also shy away from most of the more complicated stuff (macros etc.) because I need confirmation that it will be useful often or will be faster than a one-time non-automated process. I hate when a coworker spends all day trying to automate a one-time task, spending 1.5x the time it would take to have just done the damn thing directly. And then their macro fails and they declare the task impossible to reasonably complete.
 
Joined
Apr 10, 2013
Messages
8,917
Location
Squat Rack
This one time... I wrote an If-and sting that was so long...

Excel was like "homie don't play that. rewrite that shizzz. too many god damn variables."

OH SNAP!
Recently been needing to do more statement embedding like this. Working on one now containing =if(or...(and...))

I will likely need help with it soon!
 
Joined
Jul 24, 2013
Messages
7,678
Location
TalkBeer.com
Interesting trick. Not sure I'd need it that often but the easier dragging advantage has me intrigued.
Yeah, I mean, VLOOKUP is fine, but besides the incredibly low bar of remembering the syntax of the command, there's literally no reason not to use INDEX MATCH, or, in other words, there's nothing you can do with VLOOKUP you can't do with INDEX MATCH. But there ARE a few big benefits:
  • Less likely to make an error in the column you're trying to pick, as you actually select the column rather than counting columns (people have totally destroyed entire analyses they did for me this way)
  • Especially in a very large spreadsheet (hundreds/thousands of records), the performance of INDEX MATCH is way superior - doesn't take much time at all to calculate as it doesn't have to run through the whole array
  • Your lookup column does not have to be to the left of the array you're trying to work with (although, now that I say this, I am wondering if I can use a negative number as my VLOOKUP parameter - I have been using Excel daily for probably half a decade and never have even considered this as an option. I am going to feel stupid if this works)
 
Joined
Apr 12, 2013
Messages
5,669
Location
Chicago Burbs
Yeah, I mean, VLOOKUP is fine, but besides the incredibly low bar of remembering the syntax of the command, there's literally no reason not to use INDEX MATCH, or, in other words, there's nothing you can do with VLOOKUP you can't do with INDEX MATCH. But there ARE a few big benefits:
  • Less likely to make an error in the column you're trying to pick, as you actually select the column rather than counting columns (people have totally destroyed entire analyses they did for me this way)
  • Especially in a very large spreadsheet (hundreds/thousands of records), the performance of INDEX MATCH is way superior - doesn't take much time at all to calculate as it doesn't have to run through the whole array
  • Your lookup column does not have to be to the left of the array you're trying to work with (although, now that I say this, I am wondering if I can use a negative number as my VLOOKUP parameter - I have been using Excel daily for probably half a decade and never have even considered this as an option. I am going to feel stupid if this works)
Pro-tip, if you've got a shit ton of columns in an array for a v-lookup you can just insert a row at the very top and drag numbers across to number the columns. It doesn't effect the data at all and you can instantly know column BP is 68 instead of trying to count all that bullshit.
 
Joined
Apr 5, 2013
Messages
318
Location
Buffalo, New York
Your lookup column does not have to be to the left of the array you're trying to work with (although, now that I say this, I am wondering if I can use a negative number as my VLOOKUP parameter - I have been using Excel daily for probably half a decade and never have even considered this as an option. I am going to feel stupid if this works)
Please report back with your findings.
 
Joined
Jul 24, 2013
Messages
7,678
Location
TalkBeer.com
Pro-tip, if you've got a shit ton of columns in an array for a v-lookup you can just insert a row at the very top and drag numbers across to number the columns. It doesn't effect the data at all and you can instantly know column BP is 68 instead of trying to count all that bullshit.
Yeah, I did that back when I used vlookups, but it's just another thing you have to do.
 
Joined
Apr 10, 2013
Messages
8,917
Location
Squat Rack
Yeah, I mean, VLOOKUP is fine, but besides the incredibly low bar of remembering the syntax of the command, there's literally no reason not to use INDEX MATCH, or, in other words, there's nothing you can do with VLOOKUP you can't do with INDEX MATCH. But there ARE a few big benefits:
  • Less likely to make an error in the column you're trying to pick, as you actually select the column rather than counting columns (people have totally destroyed entire analyses they did for me this way)
  • Especially in a very large spreadsheet (hundreds/thousands of records), the performance of INDEX MATCH is way superior - doesn't take much time at all to calculate as it doesn't have to run through the whole array
  • Your lookup column does not have to be to the left of the array you're trying to work with (although, now that I say this, I am wondering if I can use a negative number as my VLOOKUP parameter - I have been using Excel daily for probably half a decade and never have even considered this as an option. I am going to feel stupid if this works)
Will be trying INDEX MATCH for the first time today.
 
2
Group Builder
Top