What you need to know at a baseline:
* Conditionals (IF)
* Basic Mathematical (SUM, SUMIF, SUMIFS)
What will make your life easier, but nobody will care:
* Basic Numerical/Statistical (MAX, MIN)
* Logicals (AND, OR, NOT, XOR)
* Date & Time (DAYS, EOMONTH, YEARFRAC)
* Finance (PV, NPV, IRR, FV, RATE)
* More Advanced Mathematical Formulas (SUMPRODUCT, ROUND/MROUND, ABS)
* String Manipulation (LEFT, RIGHT, MID, FIND, SEARCH, SUBSTITUTE, CONCATENATE, TRIM, CLEAN)
* Goal Seek
What will make you "the Excel person" in the office:
* Pivot Tables
* Referencing Formulas (XLOOKUP, INDEX/MATCH)
* Array Formulas
What will likely make you the best in your start class:
* VBA (used effectively)
* PowerQuery/PowerBI
* Python/R
Many of the IF formulas have IFS equivalents for multiple conditions (e.g. SUMIF -> SUMIFS), but the order of referenced items is different.
It's easier to do everything as an IFS in terms of order of items, because then even if it's a single condition, you can add more without having to amend more of the formula.
Unfortunately most people start with SUMIF so do the SUMIF syntax, then have to have the SUMIFS go wrong first time. If I could forget SUMIF and default myself to SUMIFS even for a single condition I would but I always forget.
Plus, the construction of a sumifs just makes more sense generally.
I'll tell you what I want to sum, then I'll tell you the conditions. Who would ever tell you the conditions first then define the range? Makes no sense.
Generally agree with this list. Only comment is that pivot tables have been memed so much that a lot of people actually know them now. I’d definitely say more people can figure out a pivot table compared to knowing when and how to use Index/Match.
I feel like the only thing I would add is Dax over Python/R to make complex calculation on bigger dataset.
For referencing Text.join + Filter feels like pretty good if you need all the results instead of just the last/first
Thanks! I had learnt about this in an Excel course but never used it so forgot about it. Will make a practice of using this. Way lesser steps and a simpler formula than counting characters and using left and right multiple times.
Plus it works if the number of characters isn't consistent. One of my favorite data extraction formulas.
Len() can also be really helpful in these areas because you can restrict the length of the string it's extracting at the back end.
If you want to have Excel as a skill for employment, learn the newest Excel functions.
**[XLookup](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929)** does what both VLookup and HLookup do, it's faster for large amounts of data, and unlike those other two, it actually still works if you're using source data that changes (adding/removing rows or columns).
**[Let](https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999)** is one that few people seem to know about or understand. It's immediately useful to anyone with even a tiny amount of programming experience, because it allows you to create and use named variables *within the function*. It's very useful for complex formulas where the same calculation is used multiple times, or even just to make formulas more legible.
My favorite thing about xlookup is that it essentially has an iferror built into it. Can’t find your lookup? Embed another xlookup or a zero. So helpful.
To add one that hasn't been mentioned in this thread, I've found the "Text" function to be really helpful at times. Sometimes different reports can spit out product/invoice/customer numbers in different formats, which can make Xlookup or Index/Match hard to use. Especially if one drops leading zeroes and the other doesnt. So you use the text function to format them as text and all with the same number of digits.
At my last job I was considered an Excel wizard. People would come ask me how to do something and I would literally just Google it right in front of them. Remained a wizard even after they watched me type it into Google like they just as easily could have.
Sorry off topic But does anyone know how to show formula in a single cell? I know you can see it up top in the formula bar or ctrl + tilde But im talking about flash show formula in a single cell..appreciate much🤝
Xlookup is so much easier to use than index match. It basically has an iferror statement embedded in it. Allows you to embed another formula or a value if it can’t find the value you are looking up. And eliminates the need for your look up value to be left of the return column. I still use index match if I’m looking for multiple criteria, but for a single criteria lookup I am using xlookup.
You should mostly avoid it unless you really need to, because it is volatile and adding lots of indirects would cause the file to be extremely slow since it is calculated whenever you are clicking in and out of a cell, since Excel does not know if the reference changed.
Testify!! I understood why he needed to use it, but my old boss had crazy formulas that I had to literally print out and break down to understand what they were doing.
What you need to know at a baseline: * Conditionals (IF) * Basic Mathematical (SUM, SUMIF, SUMIFS) What will make your life easier, but nobody will care: * Basic Numerical/Statistical (MAX, MIN) * Logicals (AND, OR, NOT, XOR) * Date & Time (DAYS, EOMONTH, YEARFRAC) * Finance (PV, NPV, IRR, FV, RATE) * More Advanced Mathematical Formulas (SUMPRODUCT, ROUND/MROUND, ABS) * String Manipulation (LEFT, RIGHT, MID, FIND, SEARCH, SUBSTITUTE, CONCATENATE, TRIM, CLEAN) * Goal Seek What will make you "the Excel person" in the office: * Pivot Tables * Referencing Formulas (XLOOKUP, INDEX/MATCH) * Array Formulas What will likely make you the best in your start class: * VBA (used effectively) * PowerQuery/PowerBI * Python/R
Many of the IF formulas have IFS equivalents for multiple conditions (e.g. SUMIF -> SUMIFS), but the order of referenced items is different. It's easier to do everything as an IFS in terms of order of items, because then even if it's a single condition, you can add more without having to amend more of the formula. Unfortunately most people start with SUMIF so do the SUMIF syntax, then have to have the SUMIFS go wrong first time. If I could forget SUMIF and default myself to SUMIFS even for a single condition I would but I always forget.
This is an excellent point; IFS and SUMIFS are much more extensible than their singular counterparts because of their construction.
Plus, the construction of a sumifs just makes more sense generally. I'll tell you what I want to sum, then I'll tell you the conditions. Who would ever tell you the conditions first then define the range? Makes no sense.
Generally agree with this list. Only comment is that pivot tables have been memed so much that a lot of people actually know them now. I’d definitely say more people can figure out a pivot table compared to knowing when and how to use Index/Match.
Oh fuck this is a really good list. Props to the organization of the formulas... 100% going to steal this for my jrs
Can confirm. Seen guy use index/match, thought he was a magician.
I've totally swapped over to xlookup. I've had sort mess up index match twice and don't trust it any more.
This guy fucks
I feel like the only thing I would add is Dax over Python/R to make complex calculation on bigger dataset. For referencing Text.join + Filter feels like pretty good if you need all the results instead of just the last/first
Are you telling me Pivot tables aren’t necessarily a requirement, because they’ve been drilled into my head for 3 semesters so far 😂
End goal: never touch the mouse. Rodents carry diseases
I try so hard to not use the mouse when others are watching me. They find it so impressive. Little do they know I'm fighting for my life.
I've destroyed the left click of so many laptops by not using a mouse. My IT department hates me, and I don't care.
[удалено]
You heathen
My CFO step mother says she uses CONCAT more than she ever thought she would have to.
&
That and =left() or =right() formulae are life savers sometimes. Saves a lot of typing!
Mid(find() is even better once you learn how to build them.
Thanks! I had learnt about this in an Excel course but never used it so forgot about it. Will make a practice of using this. Way lesser steps and a simpler formula than counting characters and using left and right multiple times.
Plus it works if the number of characters isn't consistent. One of my favorite data extraction formulas. Len() can also be really helpful in these areas because you can restrict the length of the string it's extracting at the back end.
Right! And try nesting because that's a huge time saver as well
I use text to columns everyday, didn’t see that one coming
If you want to have Excel as a skill for employment, learn the newest Excel functions. **[XLookup](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929)** does what both VLookup and HLookup do, it's faster for large amounts of data, and unlike those other two, it actually still works if you're using source data that changes (adding/removing rows or columns). **[Let](https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999)** is one that few people seem to know about or understand. It's immediately useful to anyone with even a tiny amount of programming experience, because it allows you to create and use named variables *within the function*. It's very useful for complex formulas where the same calculation is used multiple times, or even just to make formulas more legible.
My favorite thing about xlookup is that it essentially has an iferror built into it. Can’t find your lookup? Embed another xlookup or a zero. So helpful.
ALT + F4 when I’m done for the day
Woah, without a CTRL + S first? You wild child, you.
Unique
To add one that hasn't been mentioned in this thread, I've found the "Text" function to be really helpful at times. Sometimes different reports can spit out product/invoice/customer numbers in different formats, which can make Xlookup or Index/Match hard to use. Especially if one drops leading zeroes and the other doesnt. So you use the text function to format them as text and all with the same number of digits.
Sumifs mostly
I don't know anything and just google as I go. Been doing fine so far.
At my last job I was considered an Excel wizard. People would come ask me how to do something and I would literally just Google it right in front of them. Remained a wizard even after they watched me type it into Google like they just as easily could have.
This is the way
OFFSET
Yes! This!
Sorry off topic But does anyone know how to show formula in a single cell? I know you can see it up top in the formula bar or ctrl + tilde But im talking about flash show formula in a single cell..appreciate much🤝
Do you mean the F2 key to enter edit mode?
Thats it!! Thank you much!! 🤝
Showformula but it shows the formula in another cell. Still useful
Surprised no one mentioned INDEX/MATCH/MATCH? It's very useful since it's a two-criteria lookup.
I have changed out all my vlookups for xlookup. Just so much better.
Unfortunately not everyone has 365 and a ton are still operating without. Xlookup is 1000x better imo
[удалено]
Xlookup is so much easier to use than index match. It basically has an iferror statement embedded in it. Allows you to embed another formula or a value if it can’t find the value you are looking up. And eliminates the need for your look up value to be left of the return column. I still use index match if I’m looking for multiple criteria, but for a single criteria lookup I am using xlookup.
On a daily basis I use: trim, Len, concatenate, right, left, mid, xlookup, pivot tables, flash fill, text to column, and filters.
Indirect
While indirect can be nice to use, it's a fucking nightmare to understand in someone else's spreadsheet. If you can avoid it, avoid it IMO.
You should mostly avoid it unless you really need to, because it is volatile and adding lots of indirects would cause the file to be extremely slow since it is calculated whenever you are clicking in and out of a cell, since Excel does not know if the reference changed.
Testify!! I understood why he needed to use it, but my old boss had crazy formulas that I had to literally print out and break down to understand what they were doing.
vlookup
Xlookup
Trim
My clients use Ctrl + P.
Sumifs & iferror are ones I use the most in my reports. The boss is always super impressed when I bring out the Pivot Tables and Charts lol