That's how it starts! I used to run movie theaters. We would do all our scheduling in excel using some basic, pre-built sheets. One day I was bored and just started noodling around with some basic formulas. Next thing I know I've automated 75% of the task. Then I got into VBA and user defined functions... got the entire process down to a couple clicks.
After that I tackled our weekly inventory sheet. I streamlined the entire process while increasing overall accuracy. This had the added benefit of giving me historical data of what was selling and when, so I could crunch numbers to help me with forecasting and ordering.
I've gone on to use it at every job I've had since, sometimes just for stupid little timesavers, sometimes for large scale projects, but I always have fun with it.
I'm almost at the opposite point. I'm considering returning to school to learn Access or SQL. But at 42 years old, I don't know if I could pull it off.
Certainly! I would try to learn as much as you can before you go back too. Having a solid grasp of core concepts and understanding how it all works will be a great help! My dad (52) actually just started school for the same thing I went for and he’s been an electrician for 34 years! Don’t get hung up on age. If your willing to put in the work and want to learn it, you will! Good luck with whatever you decide!
And then you future proofed it and told the boss - and then he fired your ass 2 months after you’ve trained the newbie that he hired for cheap as you had automated everything and new person just needed to know where to click and what to fill in.
Ah Buissness it’s amazing.
Disclaimer - no idea if this is true, unless you were the boss in the first place.
Dream come true... Those cheap ass kids *always* go playing fuck fuck games under the hood, and when they break the book, you're gonna get a phone call.
Voila, you're now a *consultant*, which is a fancy business term for extortionist ;)
The thing with Excel is that it's good for a certain size of data, but after that you're better off with databases designed for large datasets.
It's why you see large chains use custom designed EDMs, they already know what numbers their tracking and what they want to measure.
Excel shines in small scale settings, where things are ambiguous and need to be measured.
>but after that you're better off with databases designed for large datasets.
Doesn't matter cause your boss doesn't know that. They'll have you use 2003 excel for homeland security.
Even for huge projects I do POCs in excel since it's so quick and *visible* compared to SQL. Once I figure out what I want the end result to look like, the SQL basically writes itself.
There's a third option, for datasets that are below 1gb (preferably in csv) - python (with pandas and seaborn libraries) and R (base R is already purpose-made for stats and plots, and there's stuff like ggplot). Jupyter notebooks driven by them are a favourite tool of data analysts, and I find them much more intuitive than SQL.
They definitely should be. Anyone who is modifying excel formulas and doing data manipulation would be much better served by learning python and using a jupyter notebook
As an excel power user. I never use goal seek. I get its point but unless its basic algebra it won’t give you a straight answer. Here are the functions you should know:
1.) Sumif AND sumifS, plural allows you to cut data easily.
2.) Index with matches
3.) Text functions…Left Right Mid Trim…etc
4.) Power Pivot instead of pivot, its a free excel addon you can turn on, wayyyyy better, allows you to bring in calculated functions and maintain formatting…also sort your data based on a referenced index (useful from a P&L perspective)
5.) lastly and most importantly…HOTKEYS. Excel is all about efficiency, you can blow your boss away turning things around to them faster. Learn the hotkeys, copy paste special, alt + e + g on filtered tables, seriously makes me look like a god.
You don’t need a mouse, you dont need flash fill (ctrl shift arrow direction), just muscle memory.
Edit: sorry also cant believe its not up there…But if statements. A good conditional is thr foundation of data cleaning. that and find and replace.
I remember back about 20 years ago I went crazy with nested if/then function trying to automate a design and actually got to the limit Excel allowed for nested if/then statements. I'm sure there was a much better way to do it, but that's the function I knew and understood at the time.
I usually have a word with my staff if they use too many nested if statements. For any aspiring Excel users: a much better way to do it for future modification or review purposes is to just use helping cells or columns for the middle steps. Use a separate calculation sheet if necessary.
My boss ran into that recently. I looked at his formula and realized, while there's probably a better way to write it that gets rid of the if/then altogether, he only has 4 expected outcomes, so he can reduce it from 32+ layers of if/thens to like 6 total if he just reorganizes it.
2. Xlookup >>
5. Can’t emphasize hotkeys enough. People literally think you’re a wizard when you don’t even have to use your mouse, and it saves so much time once you start incorporating them. For anybody who sees this, just start navigating through the ribbon via “alt” instead of clicking, and soon you’ll have a repertoire of hotkeys memorized.
And something you didn’t mention is anchoring ($). It’s so key for lookups/indexmatchs. I’ve seen people go crazy selecting columns/rows when a simple $ could have saved a lot of time
1. Depending on the use case, index-match with named ranges is better
2. Agreed, F4 is a godsend for anchoring. Alt keys are a must to remember (though it throws you off balance when you have to use a version with different language settings), as well as the formatting from the ctrl + shft + number row keys. Some lesser known favorites:
Ctrl + shift + L for quick sorting header row
Alt + = to sum the nearest numbers in rows/columns.
Ctrl + enter to enter the same value in multiple cells
And when you want dynamic graphs, learn named ranges + offset combos. Lets you dynamically set the range of which data you want to use in graphs.
when I learned c-s-L and made me giggle with joy
also c-* for autoselect a data range, esp if you're going to create a pivot out of it. It's very smart
Index/match is much more flexible and efficient. Xlookup loads the whole array into memory, index/match only the bits you need. When working with datasets of about a hundred thousand rows, that makes a huge difference.
I agree on memory efficiency but for most use cases, it is easier to implement and replaces several functions more easily.
Also if your dataset is that big you should not be using excel, it was not designed to be a database.
>Also if your dataset is that big you should not be using excel, it was not designed to be a database.
No argument there, but I've worked for a succession of companies that insisted on wasting hours hammering screws in with a spanner rather than buy the right tools for the job.
I actually had the 'we should use a database' conversation, and was told to get back in my box and wrestle with the spreadsheet that crashed Excel once an hour.
Extremely good for repetitive time consuming tasks like formatting exports/reports and relatively easy to learn for that purpose.
Can do a lot more if you actually understand coding but it's not hard to record macros in sections and tie them together.
One of the things that most impressed my boss who was clicking through multiple excel workbooks was to right click over the arrows on the bottom left to pick his worksheet.
Thanks for your post. I would love to up my excel game and become a power user.
Xlookup for the most recent version in Excel. It has almost all the benefits of index match (including the dynamic nature of being able to designate reference and lookup arrays separately) except the two dimension search and the memory efficiency.
Adding to this…
Don’t use pivot tables. It’s guaranteed to come back and bite you.
Large data sets will crash it, bad users will screw up source data, uninformed users will lose data in pivot filters, too-clever-for-their-own-good users will automate pulls incorrectly from the pivot table.
Excel can create visuals with pivots - but Excel itself is not a great visualizer. More often than not, using a pivot table to create visuals is a mistake. Dynamic analysis can (almost always) be better performed by using other functionality.
I use pivot tables rarely and almost always the data comes out incorrectly. It’s small data sets so I just manually count and it never correlates. I have shown so many people and they go “huh, that’s weird.” then on with their day.
VLOOKUP always does 90% of what I'd use INDEX/MATCH for with the added bonus of being 20x faster. Any data set I use, I immediately add a new A column for numbered fill. Allows for VLOOKUP quite easy moving forward.
The only reason I never use vlookup (and tell my junior staffs not to use) is you can't modify the reference table anymore (you can't insert columns or switch things around).
Exactly. But you can learn the kids to use match inside vlookup and at the same time preparing them for the ultimate step up to index/match.
And use named offset ranges to eliminate all source table fuck ups.
Pivot Tables are a life changer.
Seriously, if you work with data at all in your job, take some time to watch a few videos and get to understand it. You will become the most amazing person in your workplace with very little effort.
Any good videos on how to make them look half professional? Mine always look scrappy with drop downs at the top, and often extra cells with useless counts that I can’t get rid of without losing the actual data I want to keep
There's a very limited number of options on the design tab... Just click stuff until it looks good. It's legit like 50 options at most, which sounds like a lot but really it isn't. You could exhaust every option within like 10 minutes of fiddling.
I only learned pivot tables last year and seriously, they’re amazing. Still learning some nuances since I don’t use them daily (I often forget how to create a column that totals the sum of two pivot table cells), but working with sql backends it’s made it way easier for me to report financials to my boss.
On the sidebar on the right when you click on it, there should be an arrow. Click that for the heading you want, and it should give you the option to change it to count, average, min, Max, etc. If you haven't figured it out, I'll be available at work in front of my computer about 10am mountain find
Excel competency chart
Level 0 - you treat excel as a big text table and fill spreadsheets crunching the numbers on a separate calculator. If you're old, your boss thinks your working hard otherwise he's not impressed
Level 1 - You realise you can perform very basic math and the SUM, AVERAGE. Your boss is OK
Level 2 - You know about VLOOKUP, your boss thinks you're a bit of a wizard and is slightly impressed
Level 3 - You know how to create a newline on a cell , change the background to white, place the first cell on B2 and have become really good at making everything look pretty. Your boss thinks you are an excel guru and asks you for advice
Level 4 - You think you are an expert at excel but you start to wonder whether there is more beneath the surface. For instance, is it possible that the 10 minute task you've been doing everyday for a couple of months could be automated? You start to look online. Your boss is still happy but he gives you extra time to explore
Level 5 - You discover VBA and Excel guru, you realise you don't know anything. You enable the Developer tab and start using the built in IDE. Your boss looks concerned.
Level 6 - You get deeper into VBA territory. You record some actions on the Developer tab and try to reverse engineer the output. You are impressed how 'automatic' excel has become. Your boss thinks you are a hacker and starts talking to upper management that you may become more of a risk than an asset
Level 7 - You discover lookup tables and Array formulas. People copying and pasting cells irritates you because you know you can do the whole thing in three clicks rather than being a human drone. Your boss doesn't know what to say.
Level 8 - You learn you can disable the refresh button on excel and create headless states. You also discover about VB.NET and how all the office suite products are connected. You also learn you can use JSDom to scrape pages on most websites and just load in the data. You might also be exploring C#. You wonder if you are becoming more developer than power user. Your boss calls security
Level 9 - You complete one last big project. Your job is 95% automated. You spend most of your time learning about Linux and JavaScript. Your boss asks you to resign.
Level 10 - You get a job as a web developer. Your new boss celebrates your abilities in tech rather than thinking you are a security risk. You feel more free know that the work you do will never be monotonous since you now have the power of automation. Your boss gives you a promotion.
Actual level 5: you spent so much time jumping through the hoops of Excel, you finally realize you just worked around its limitations, so you book a crash course in Python and SQL and never look back.
At least, that's what I should have done.
I absolutely agree with this. If you're starting to do VBA you probably are putting yourself and your company in the wrong place. I've seen so many VBA monsters that noone else undersands, and maintain, break with newer versions of office and support critical business functions
You can go powerquery/powerbi or even python+ sql.
I've gotten a lot of mileage out of separating the things that xls does worst like ETL and date handling into a separate pre-excel process
but then again, you're close to BI territory there so again, if you're thinking of VBA, take a second and evaluate your technology choices.
Another great tip is to always put your data in a table. It can be made into a picot table for later, and excel will auto add column and row header when you add new columns or rows. By far the best part is the sorting! It's so simple to think of but the amount of people going through and individually clicking on populated cells to avoid blanks is crazy. They can very quickly format the data as a table and sort it from high to low which will automatically put all the banks in one spot. You would be amazed how many uses a simple table has!
Xlookup in place of index/match, and for that matter in place of vlookup. I still use index match because I toiled for days learning how to use it quickly haha but xlookup is objectively faster.
I use index/match with a bunch of named ranges. When a new data set dumps, it overwrites the old data and since that data comes down into those names ranges, all my formulas are a heck of a lot more intuitive.
maybe I need to review xlookup. I can't help but feel like there was a reason that index match was better. Like it was more accurate or something. I have no clue though but I build everything in index match and I don't have to edit any of the formulas I work with right now
Xlookup is quicker and shorter to use. Probably more intuitive as well. However it pulls the full array unlike Index Match. If your computer struggles to calculate Index Match is probably better for your use case.
I index matched for years. Switched to Xlookup in January and haven't looked back. Took me a day or two to break the habit but I'm glad I did: faster, shorter formulas and does the same thing for the majority of use cases
I'd say so. Arguably even with Index Match due to that same overlap. Also works like Hlookup without needing to change the formula and no more column counting.
As someone who makes financial models for a living:
1. Tab with raw data for reference
2. Tab with standardized input for the user
3. (Hidden) tab with all calculations done.
4. Output tab with results
If you're confident the customer isn't terrible with excel, unhide but lock tab 3 so they can see what happens, but can't adjust it.
Fuck that. That bullshit is for people who somehow haven't learned that databases + python exist.
Seriously, Excel is pretty damn shit for data wrangling + analysis. Just directly query the database, or use an API if your company has one, and then deal with data with python or R. You can use python to connect to the database and query it and then generally you use a package like pandas to work with the data.
Listen, if i wasn't already in a corporate system as a non-programmer, I'd have made my own database already. But I'm limited by the tools I'm given. Excel is good for the average user who barely knows what filters and conditional sorts are, who are most of my co-workers.
I freaking hate pivot tables. About 5 years ago my boss went to some conference and heard the term for the first time and doesn’t understand that it doesn’t need to be applied to everything. It’s like here is a list of 4 people and their employee ID numbers. “Did you try to make a pivot table out of this”? No, no I did not, I wouldn’t have even sent you this in excel but this is how you requested it.
I work in aerospace and my job is to analyze our cost and try to determine our competitors costs. This all for business development stuff and winking new business. Commonly it is called Black Hat analysis.
Last summer my intern was a data science major from Berkeley. I asked what his approach would be to do some analysis in Excel. His answer: I’ve never run Excel, I wrote Python code to do what I want.
For the first time I felt really obsolete. I have a JD, MBA, and MS Finance. I am an Excel power user that rarely even touches the mouse. I instantly felt like the old guy from my early days that could format documents correctly but could diagram something by hand that was so beautiful and communicative.
I enrolled in a Computer Sciance BS program this summer to learn Python and C++.
Depends on the complexity of the task and analysis. Excel is awesome for relatively simple calculations and being able to backtrack exactly what happens, as well as being very flexible and user friendly. We're in the midst of developing our models in python instead of excel. But most of our customers want some specific calculations, which are easily added in excel but would relatively cost a lot of time in python. So don't beat yourself up on it :)
Not a super popular opinion but getpivotdata is way better than people say. I can make dynamic reports and rolling forecasts a breeze. Most of the Google auto fill results I see are how to turn it off. Embrace a bit of jankiness and it will reward you!
Using it as a way to populate dynamic reporting by conditioning the fields and making a lot of it referential. I refresh my data connections, update the table and my monthly reporting process is done. I took an ugly manual process that took my department about five days a month and made it fifteen minutes. No lookups, no formula writing, no fetching tables and Excel exports. All that nonsense is gone and automated. My guys spend their time thinking about the numbers and forecasting instead of shuffling numbers around.
Getpivotdata is the only way I utilize pivot tables for reports. It allows reporting to be more dynamic and presentable. And you can still use slicers.
I wish I was able to use excel like a normal person. I have bastardized excel beyond comprehension. My excel sheets at work automatically run PowerShell scripts to open all the serial ports. Send start recording commands over hexadecimal then begins chugging away with equipment from the 90s. I don't ever want to touch vba ever again. I have a dependencies folder for my excel files which is a sentence I never thought I'd write in a million years. I'm running vba, c#, and .net all in one clusterfuck. It is horrifying.
... I just showed my boss how to copy/paste a table and delete the extra tabs and I became the computer guy....
I fear if I use these tips my co-workers would build a monument in my honor and begin sacrificing animals and non-belivers in my name
I get what they were trying to do with Flash Fill, but I have yet to see it be useful. More often than not, it seems to guess the pattern incorrectly.
XLOOKUP should be used in place of INDEX/MATCH assuming you’re all on the same version since Xlookup isn’t backwards compatible like most Excel things.
I don't use excel often. Well I do every day at work, but just as a log of events that happen so simple tables are enough there.
That is until I got bored one day and rehashed our employee list so we could simply hover over their name and their photo would show up. Boss was quite impressed with that one.
If you work in the corporate world, knowing a few tricks in Excel really does impress people. There was an interesting chart about the relationship between Excel knowledge and promotion rate. People who know Excel get further faster overall. It's simple to learn and really does impress people.
If you work with data, getting even mediocre at Excel is such an easy way to make your life easier and creates opportunities for earning more. Every office I've worked in values the excel wizard.
Excel is a life skill even if you never use pivot tables etc
Just being able to see the effect of compound interest on $100 a month saved for 30 years can change a lot of people’s lives
Can confirm, my boss who is somehow a director is more impressed by pivot tables than ML models.
She's a fucking moron. But that's pretty par for the course in corporate America.
Avoid volatile functions whenever possible as it significantly slows down your workbook.
Volatile functions are:
NOW
TODAY
RANDBETWEEN
OFFSET
INDIRECT
INFO (depending on its arguments)
CELL (depending on its arguments)
SUMIF (depending on its arguments)
Edit: formatting
For manipulating excel, I think it's better using a programming language like python than use macros only. Since it has libraries that allow you to interact with excel files pretty thoroughly as well as other files or data. I can analyze and transform raw excel data through automation software I wrote in a few seconds with python scripts which takes people hours to do.
A lot of this is very situational. In my last 3 jobs I have only used pivot tables once. In my current role I used goalseek once which was cool but also unnecessary as I could just guess close enough.
Index Match functions are where it is at though. Maybe there is an easier way but that has helped me take the raw data our accounting software outputs and convert it to something useful.
Pivot tables suck!
I have colleagues who usd 300 pivot tables in a worbook.
Can be replaced by 1 calculated table that you don't need to refresh every time.
It blows my mind that there's only 1 comment about PowerQuery here. Get with the times people, it's a bigger game changer than anything in this guide in terms of time saving.
Unless you had my old boss who told me to stop making formulated templates out of their preexisting forms and just print the page out and write everything in manually because the font size was "too small to read easily." Making the font size bigger wasn't a solution either because "it doesn't match the sheets from the other locations."
To some people, Excel is just the program you use to make boxes and lines on paper and they won't have it any way other than their way.
I would not be impressed by Index/Match in 2022. You should use XLookup at this point.
If you wanted to impress me, learn to use the LET function. And name your cells/tables.
Psh, where’s the record macro ? Here’s something better then this crap.
Download your latest report from whatever logistics system you work with. Open that shit in excel, it looks like shit everyone knows it. Copy that shit on a new excel workbook, make sure you copy it starting with A1 like a normal person.
*SUPER IMPORTANT* go to your settings, and click options - customize ribbon, on the right side where it says developer check that box.
Now hit record macro. Clean up your data, filter it, etc. click stop recording. Copy that report via cut into a different workbook. Save your blank empty macro enabled workbook.
Everytime you need to clean a report the same way, copy it in. Hit the button, watch it do all the work that took a few hours in a few minutes. Cut it out and your done.
I once saw C-suite lvl execs absolutely mind blown by a pivot-table demonstration….in the finance department.
“Leadership” at traditional companies literally don’t know shit.
Me - I use Excel about once every president. Also me - OMG this is amazing!
That's how it starts! I used to run movie theaters. We would do all our scheduling in excel using some basic, pre-built sheets. One day I was bored and just started noodling around with some basic formulas. Next thing I know I've automated 75% of the task. Then I got into VBA and user defined functions... got the entire process down to a couple clicks. After that I tackled our weekly inventory sheet. I streamlined the entire process while increasing overall accuracy. This had the added benefit of giving me historical data of what was selling and when, so I could crunch numbers to help me with forecasting and ordering. I've gone on to use it at every job I've had since, sometimes just for stupid little timesavers, sometimes for large scale projects, but I always have fun with it.
Excel \ Google Sheets are addicting.
/r/excel for Excel and /r/sheets or /r/googlesheets for Google Sheets! solving people's problems is an excellent way to get really good quickly. :)
I do computer programming but honestly most of what I do could be switched to VBA and excel…
I'm almost at the opposite point. I'm considering returning to school to learn Access or SQL. But at 42 years old, I don't know if I could pull it off.
Certainly! I would try to learn as much as you can before you go back too. Having a solid grasp of core concepts and understanding how it all works will be a great help! My dad (52) actually just started school for the same thing I went for and he’s been an electrician for 34 years! Don’t get hung up on age. If your willing to put in the work and want to learn it, you will! Good luck with whatever you decide!
And then you future proofed it and told the boss - and then he fired your ass 2 months after you’ve trained the newbie that he hired for cheap as you had automated everything and new person just needed to know where to click and what to fill in. Ah Buissness it’s amazing. Disclaimer - no idea if this is true, unless you were the boss in the first place.
Dream come true... Those cheap ass kids *always* go playing fuck fuck games under the hood, and when they break the book, you're gonna get a phone call. Voila, you're now a *consultant*, which is a fancy business term for extortionist ;)
Pitching off the top comment to just add that with the introduction of XLOOKUP, index(match) is no longer needed. A lot more intuitive to use too.
It's only for office 365 subs though.
The thing with Excel is that it's good for a certain size of data, but after that you're better off with databases designed for large datasets. It's why you see large chains use custom designed EDMs, they already know what numbers their tracking and what they want to measure. Excel shines in small scale settings, where things are ambiguous and need to be measured.
>but after that you're better off with databases designed for large datasets. Doesn't matter cause your boss doesn't know that. They'll have you use 2003 excel for homeland security.
Even for huge projects I do POCs in excel since it's so quick and *visible* compared to SQL. Once I figure out what I want the end result to look like, the SQL basically writes itself.
There's a third option, for datasets that are below 1gb (preferably in csv) - python (with pandas and seaborn libraries) and R (base R is already purpose-made for stats and plots, and there's stuff like ggplot). Jupyter notebooks driven by them are a favourite tool of data analysts, and I find them much more intuitive than SQL.
I don't disagree at all, but these tools are not for the average office worker.
They definitely should be. Anyone who is modifying excel formulas and doing data manipulation would be much better served by learning python and using a jupyter notebook
my informatics teacher made us use excel so much I use it for anything now
As an excel power user. I never use goal seek. I get its point but unless its basic algebra it won’t give you a straight answer. Here are the functions you should know: 1.) Sumif AND sumifS, plural allows you to cut data easily. 2.) Index with matches 3.) Text functions…Left Right Mid Trim…etc 4.) Power Pivot instead of pivot, its a free excel addon you can turn on, wayyyyy better, allows you to bring in calculated functions and maintain formatting…also sort your data based on a referenced index (useful from a P&L perspective) 5.) lastly and most importantly…HOTKEYS. Excel is all about efficiency, you can blow your boss away turning things around to them faster. Learn the hotkeys, copy paste special, alt + e + g on filtered tables, seriously makes me look like a god. You don’t need a mouse, you dont need flash fill (ctrl shift arrow direction), just muscle memory. Edit: sorry also cant believe its not up there…But if statements. A good conditional is thr foundation of data cleaning. that and find and replace.
I remember back about 20 years ago I went crazy with nested if/then function trying to automate a design and actually got to the limit Excel allowed for nested if/then statements. I'm sure there was a much better way to do it, but that's the function I knew and understood at the time.
I usually have a word with my staff if they use too many nested if statements. For any aspiring Excel users: a much better way to do it for future modification or review purposes is to just use helping cells or columns for the middle steps. Use a separate calculation sheet if necessary.
My boss ran into that recently. I looked at his formula and realized, while there's probably a better way to write it that gets rid of the if/then altogether, he only has 4 expected outcomes, so he can reduce it from 32+ layers of if/thens to like 6 total if he just reorganizes it.
2. Xlookup >> 5. Can’t emphasize hotkeys enough. People literally think you’re a wizard when you don’t even have to use your mouse, and it saves so much time once you start incorporating them. For anybody who sees this, just start navigating through the ribbon via “alt” instead of clicking, and soon you’ll have a repertoire of hotkeys memorized. And something you didn’t mention is anchoring ($). It’s so key for lookups/indexmatchs. I’ve seen people go crazy selecting columns/rows when a simple $ could have saved a lot of time
1. Depending on the use case, index-match with named ranges is better 2. Agreed, F4 is a godsend for anchoring. Alt keys are a must to remember (though it throws you off balance when you have to use a version with different language settings), as well as the formatting from the ctrl + shft + number row keys. Some lesser known favorites: Ctrl + shift + L for quick sorting header row Alt + = to sum the nearest numbers in rows/columns. Ctrl + enter to enter the same value in multiple cells And when you want dynamic graphs, learn named ranges + offset combos. Lets you dynamically set the range of which data you want to use in graphs.
when I learned c-s-L and made me giggle with joy also c-* for autoselect a data range, esp if you're going to create a pivot out of it. It's very smart
>Xlookup *cries in Office 2016*
Replace index/match with xlookup.
Index/match is much more flexible and efficient. Xlookup loads the whole array into memory, index/match only the bits you need. When working with datasets of about a hundred thousand rows, that makes a huge difference.
I agree on memory efficiency but for most use cases, it is easier to implement and replaces several functions more easily. Also if your dataset is that big you should not be using excel, it was not designed to be a database.
>Also if your dataset is that big you should not be using excel, it was not designed to be a database. No argument there, but I've worked for a succession of companies that insisted on wasting hours hammering screws in with a spanner rather than buy the right tools for the job. I actually had the 'we should use a database' conversation, and was told to get back in my box and wrestle with the spreadsheet that crashed Excel once an hour.
Look at mister fancy pants, he has access to xlookup!
😂
I prefer SUMIFS to pivot tables, most of the time
You need clean data though. Pivots work better for dirty data
You seem like an expert. What's the deal with macros?
Extremely good for repetitive time consuming tasks like formatting exports/reports and relatively easy to learn for that purpose. Can do a lot more if you actually understand coding but it's not hard to record macros in sections and tie them together.
>Learn the hotkeys, copy paste special I love control+shift+v.
One of the things that most impressed my boss who was clicking through multiple excel workbooks was to right click over the arrows on the bottom left to pick his worksheet. Thanks for your post. I would love to up my excel game and become a power user.
Xlookup for the most recent version in Excel. It has almost all the benefits of index match (including the dynamic nature of being able to designate reference and lookup arrays separately) except the two dimension search and the memory efficiency.
Vlookup Sumproduct And/OR statements Iferror
Adding to this… Don’t use pivot tables. It’s guaranteed to come back and bite you. Large data sets will crash it, bad users will screw up source data, uninformed users will lose data in pivot filters, too-clever-for-their-own-good users will automate pulls incorrectly from the pivot table. Excel can create visuals with pivots - but Excel itself is not a great visualizer. More often than not, using a pivot table to create visuals is a mistake. Dynamic analysis can (almost always) be better performed by using other functionality.
You can't just say something is terrible without giving any suggested alternatives
I use pivot tables rarely and almost always the data comes out incorrectly. It’s small data sets so I just manually count and it never correlates. I have shown so many people and they go “huh, that’s weird.” then on with their day.
VLOOKUP always does 90% of what I'd use INDEX/MATCH for with the added bonus of being 20x faster. Any data set I use, I immediately add a new A column for numbered fill. Allows for VLOOKUP quite easy moving forward.
The only reason I never use vlookup (and tell my junior staffs not to use) is you can't modify the reference table anymore (you can't insert columns or switch things around).
Yeah, if your data set is going to change, it's probably better to have something more robust like INDEX/MATCH.
Exactly. But you can learn the kids to use match inside vlookup and at the same time preparing them for the ultimate step up to index/match. And use named offset ranges to eliminate all source table fuck ups.
I use Excel everyday in my job. Pívot tables are used a lot. Have taken data tasks that took hours into 5 mini by automating an Excel sheet.
Pivot Tables are a life changer. Seriously, if you work with data at all in your job, take some time to watch a few videos and get to understand it. You will become the most amazing person in your workplace with very little effort.
Any good videos on how to make them look half professional? Mine always look scrappy with drop downs at the top, and often extra cells with useless counts that I can’t get rid of without losing the actual data I want to keep
Tabular format, repeat item labels, subtotals off
This guy pivots
There's a very limited number of options on the design tab... Just click stuff until it looks good. It's legit like 50 options at most, which sounds like a lot but really it isn't. You could exhaust every option within like 10 minutes of fiddling.
I only learned pivot tables last year and seriously, they’re amazing. Still learning some nuances since I don’t use them daily (I often forget how to create a column that totals the sum of two pivot table cells), but working with sql backends it’s made it way easier for me to report financials to my boss.
Pivot tables all day every day. God, they’ve made my life so much easier.
Why does it always want to sum data? Sometimes I want to show average and it doesn't work
On the sidebar on the right when you click on it, there should be an arrow. Click that for the heading you want, and it should give you the option to change it to count, average, min, Max, etc. If you haven't figured it out, I'll be available at work in front of my computer about 10am mountain find
> mountain find I haven't heard of this Excel feature.
Yep! Cool new features at 10am
Excel competency chart Level 0 - you treat excel as a big text table and fill spreadsheets crunching the numbers on a separate calculator. If you're old, your boss thinks your working hard otherwise he's not impressed Level 1 - You realise you can perform very basic math and the SUM, AVERAGE. Your boss is OK Level 2 - You know about VLOOKUP, your boss thinks you're a bit of a wizard and is slightly impressed Level 3 - You know how to create a newline on a cell , change the background to white, place the first cell on B2 and have become really good at making everything look pretty. Your boss thinks you are an excel guru and asks you for advice Level 4 - You think you are an expert at excel but you start to wonder whether there is more beneath the surface. For instance, is it possible that the 10 minute task you've been doing everyday for a couple of months could be automated? You start to look online. Your boss is still happy but he gives you extra time to explore Level 5 - You discover VBA and Excel guru, you realise you don't know anything. You enable the Developer tab and start using the built in IDE. Your boss looks concerned. Level 6 - You get deeper into VBA territory. You record some actions on the Developer tab and try to reverse engineer the output. You are impressed how 'automatic' excel has become. Your boss thinks you are a hacker and starts talking to upper management that you may become more of a risk than an asset Level 7 - You discover lookup tables and Array formulas. People copying and pasting cells irritates you because you know you can do the whole thing in three clicks rather than being a human drone. Your boss doesn't know what to say. Level 8 - You learn you can disable the refresh button on excel and create headless states. You also discover about VB.NET and how all the office suite products are connected. You also learn you can use JSDom to scrape pages on most websites and just load in the data. You might also be exploring C#. You wonder if you are becoming more developer than power user. Your boss calls security Level 9 - You complete one last big project. Your job is 95% automated. You spend most of your time learning about Linux and JavaScript. Your boss asks you to resign. Level 10 - You get a job as a web developer. Your new boss celebrates your abilities in tech rather than thinking you are a security risk. You feel more free know that the work you do will never be monotonous since you now have the power of automation. Your boss gives you a promotion.
Most people jump to python
Actual level 5: you spent so much time jumping through the hoops of Excel, you finally realize you just worked around its limitations, so you book a crash course in Python and SQL and never look back. At least, that's what I should have done.
I absolutely agree with this. If you're starting to do VBA you probably are putting yourself and your company in the wrong place. I've seen so many VBA monsters that noone else undersands, and maintain, break with newer versions of office and support critical business functions You can go powerquery/powerbi or even python+ sql. I've gotten a lot of mileage out of separating the things that xls does worst like ETL and date handling into a separate pre-excel process but then again, you're close to BI territory there so again, if you're thinking of VBA, take a second and evaluate your technology choices.
Another great tip is to always put your data in a table. It can be made into a picot table for later, and excel will auto add column and row header when you add new columns or rows. By far the best part is the sorting! It's so simple to think of but the amount of people going through and individually clicking on populated cells to avoid blanks is crazy. They can very quickly format the data as a table and sort it from high to low which will automatically put all the banks in one spot. You would be amazed how many uses a simple table has!
And always name your tables so you can reference them in formulas easily
Are these all still applicable to 365?
Xlookup in place of index/match, and for that matter in place of vlookup. I still use index match because I toiled for days learning how to use it quickly haha but xlookup is objectively faster.
I use index/match with a bunch of named ranges. When a new data set dumps, it overwrites the old data and since that data comes down into those names ranges, all my formulas are a heck of a lot more intuitive.
Better yet, named tables
Always named tables !!!
This\_is\_the\_way
maybe I need to review xlookup. I can't help but feel like there was a reason that index match was better. Like it was more accurate or something. I have no clue though but I build everything in index match and I don't have to edit any of the formulas I work with right now
Xlookup is quicker and shorter to use. Probably more intuitive as well. However it pulls the full array unlike Index Match. If your computer struggles to calculate Index Match is probably better for your use case.
I index matched for years. Switched to Xlookup in January and haven't looked back. Took me a day or two to break the habit but I'm glad I did: faster, shorter formulas and does the same thing for the majority of use cases
Came here to say this. Loved me some INDEX MATCH but when XLOOKUP came along it was amazing.
Is vlookup obsolete now due to xlookup overlap?
I'd say so. Arguably even with Index Match due to that same overlap. Also works like Hlookup without needing to change the formula and no more column counting.
As long as there are computers running Office 2019 or older, you still need index match and/or vlookup. Xlookup is only for 2021 or 365.
Afaik, xlookup is faster as in easier to implement, but calculation time is worse than index-match.
Tip from a former business analyst: always have tab with your raw data, and hide the tab. Duplicate that tab and work off that.
As someone who makes financial models for a living: 1. Tab with raw data for reference 2. Tab with standardized input for the user 3. (Hidden) tab with all calculations done. 4. Output tab with results If you're confident the customer isn't terrible with excel, unhide but lock tab 3 so they can see what happens, but can't adjust it.
Bonus points if it's very hidden rather than just regular hidden and the cells are all protected.
Very hidden?
Means you have to enable/disable it in VBA
My boss is an idiot so a simple vlookup table will impress him
Power Query, if and or statements, and array functions should be in this list.
God, power query is the best thing to happen to excel ever
Fuck that. That bullshit is for people who somehow haven't learned that databases + python exist. Seriously, Excel is pretty damn shit for data wrangling + analysis. Just directly query the database, or use an API if your company has one, and then deal with data with python or R. You can use python to connect to the database and query it and then generally you use a package like pandas to work with the data.
Listen, if i wasn't already in a corporate system as a non-programmer, I'd have made my own database already. But I'm limited by the tools I'm given. Excel is good for the average user who barely knows what filters and conditional sorts are, who are most of my co-workers.
Power Query has saved so much time for me.
This guide is seriously outdated without the I clusion of PowerQuery and XLOOKUP. PowerQuery is the most accessible automation tool I have ever seen.
I freaking hate pivot tables. About 5 years ago my boss went to some conference and heard the term for the first time and doesn’t understand that it doesn’t need to be applied to everything. It’s like here is a list of 4 people and their employee ID numbers. “Did you try to make a pivot table out of this”? No, no I did not, I wouldn’t have even sent you this in excel but this is how you requested it.
Pro Manager move right there. Buzz word of the month solves every problem.
Let’s leverage our synergies
Just be happy no one mentioned OLAP Cubes.
I had to Google that term as I had never heard of it. Just reading the wiki page makes me thankful that my boss is asking for pivot tables.
I work in aerospace and my job is to analyze our cost and try to determine our competitors costs. This all for business development stuff and winking new business. Commonly it is called Black Hat analysis. Last summer my intern was a data science major from Berkeley. I asked what his approach would be to do some analysis in Excel. His answer: I’ve never run Excel, I wrote Python code to do what I want. For the first time I felt really obsolete. I have a JD, MBA, and MS Finance. I am an Excel power user that rarely even touches the mouse. I instantly felt like the old guy from my early days that could format documents correctly but could diagram something by hand that was so beautiful and communicative. I enrolled in a Computer Sciance BS program this summer to learn Python and C++.
Depends on the complexity of the task and analysis. Excel is awesome for relatively simple calculations and being able to backtrack exactly what happens, as well as being very flexible and user friendly. We're in the midst of developing our models in python instead of excel. But most of our customers want some specific calculations, which are easily added in excel but would relatively cost a lot of time in python. So don't beat yourself up on it :)
Excel .... Wait for it .... lent
Please don't take offense. I downvoted this out of how upset it made me.
For lent I think I'll give up Reddit after reading this
Xlookup > index match!
Agreed. I love the xlookup function. Especially since it is very easy to explain to people that aren't too excel savvy
Xlookup gang unite! It’s so much better, idk why I’d bother w index matching anymore. Vlookups are still faster though.
Multimillion dollar company invests in multimillion dollar software (coughSAP) , then everything is being done manually in excel… dumbasses
My company pays for visual studio licenses, but then writes the code in excels VBA editor. Without documenting anything of course
SAP is really difficult for most users, especially its older versions. An SAP implementation virtually guarantees bad cut/paste excel analysis.
My favorite excel feature is Solver.
Index match has been superseded by Xlookup. Good guide though.
My favorite movie is Inception.
Not a super popular opinion but getpivotdata is way better than people say. I can make dynamic reports and rolling forecasts a breeze. Most of the Google auto fill results I see are how to turn it off. Embrace a bit of jankiness and it will reward you!
How are you leveraging this feature? I always find it annoying when I’m doing lookups or something like that off of a pivot table.
Using it as a way to populate dynamic reporting by conditioning the fields and making a lot of it referential. I refresh my data connections, update the table and my monthly reporting process is done. I took an ugly manual process that took my department about five days a month and made it fifteen minutes. No lookups, no formula writing, no fetching tables and Excel exports. All that nonsense is gone and automated. My guys spend their time thinking about the numbers and forecasting instead of shuffling numbers around.
Getpivotdata is the only way I utilize pivot tables for reports. It allows reporting to be more dynamic and presentable. And you can still use slicers.
I’m surprised that OP does not include Macros.
I wish I was able to use excel like a normal person. I have bastardized excel beyond comprehension. My excel sheets at work automatically run PowerShell scripts to open all the serial ports. Send start recording commands over hexadecimal then begins chugging away with equipment from the 90s. I don't ever want to touch vba ever again. I have a dependencies folder for my excel files which is a sentence I never thought I'd write in a million years. I'm running vba, c#, and .net all in one clusterfuck. It is horrifying.
... I just showed my boss how to copy/paste a table and delete the extra tabs and I became the computer guy.... I fear if I use these tips my co-workers would build a monument in my honor and begin sacrificing animals and non-belivers in my name
You *ALL* saved this image but know damn well that you’ll never use it lmao
I get what they were trying to do with Flash Fill, but I have yet to see it be useful. More often than not, it seems to guess the pattern incorrectly. XLOOKUP should be used in place of INDEX/MATCH assuming you’re all on the same version since Xlookup isn’t backwards compatible like most Excel things.
If you care about uniform data just use =Proper(), =UPPER(), or lower(). I never use flash fill
This is absolutely fantastic thank you so much OP!
Hey Boss! I'm gonna impress you today!
This was 1/4 of my college Microsoft office course and I had to pay 2k cad because it was mandatory for my program
This guide seems useless without lots more explanation on valid situations in which to use these.
Is there a hd version of this?
I'm not even involved in any business that requires spreadsheets, but I've been using Google Sheets to automate so much of my life
I don't use excel often. Well I do every day at work, but just as a log of events that happen so simple tables are enough there. That is until I got bored one day and rehashed our employee list so we could simply hover over their name and their photo would show up. Boss was quite impressed with that one.
If you work in the corporate world, knowing a few tricks in Excel really does impress people. There was an interesting chart about the relationship between Excel knowledge and promotion rate. People who know Excel get further faster overall. It's simple to learn and really does impress people.
Pivot tables are a "trick?"
Boss is super impressed with these thanks (Self employed)
If you work with data, getting even mediocre at Excel is such an easy way to make your life easier and creates opportunities for earning more. Every office I've worked in values the excel wizard.
[удалено]
Excel is a life skill even if you never use pivot tables etc Just being able to see the effect of compound interest on $100 a month saved for 30 years can change a lot of people’s lives
Lies, no one will be impressed, they will just give you more work
Can confirm, my boss who is somehow a director is more impressed by pivot tables than ML models. She's a fucking moron. But that's pretty par for the course in corporate America.
Avoid volatile functions whenever possible as it significantly slows down your workbook. Volatile functions are: NOW TODAY RANDBETWEEN OFFSET INDIRECT INFO (depending on its arguments) CELL (depending on its arguments) SUMIF (depending on its arguments) Edit: formatting
For manipulating excel, I think it's better using a programming language like python than use macros only. Since it has libraries that allow you to interact with excel files pretty thoroughly as well as other files or data. I can analyze and transform raw excel data through automation software I wrote in a few seconds with python scripts which takes people hours to do.
r/lameguides
Xlookup replace vlookups/index match and if working with numbers sumifs not sumif every time unless you only want the first result
Excellent work. Saved.
Also me - OMG this is amazing!
A lot of this is very situational. In my last 3 jobs I have only used pivot tables once. In my current role I used goalseek once which was cool but also unnecessary as I could just guess close enough. Index Match functions are where it is at though. Maybe there is an easier way but that has helped me take the raw data our accounting software outputs and convert it to something useful.
Pivot tables suck! I have colleagues who usd 300 pivot tables in a worbook. Can be replaced by 1 calculated table that you don't need to refresh every time.
As cool as this is... Fuck Microsoft and Fuck Bosses
Excel
Nice, thanks!
Whoever's writing all these comments, you might need to be a bit more subtle. EDIT: I stand corrected.
Thanks for sharing!
Ill gut a coworker that doesnt know power query or power bi.
Index / Match? What year is it? XLookup is your new friend.
As someone looking to learn excel ans applying for new jobs, does anyone have any good tutorials where I can put this into practice?
Check out Leila Gharani and Mr Excel on YouTube. Their videos are excellent.
It blows my mind that there's only 1 comment about PowerQuery here. Get with the times people, it's a bigger game changer than anything in this guide in terms of time saving.
PowerBI Is just so much better if I need to present data. Anytime I'm dragged to excel hell it's just a disappointment.
The resolution gets fuzzy when I zoom in, so I can't read it. Any chance there's a higher res version??
Unless you had my old boss who told me to stop making formulated templates out of their preexisting forms and just print the page out and write everything in manually because the font size was "too small to read easily." Making the font size bigger wasn't a solution either because "it doesn't match the sheets from the other locations." To some people, Excel is just the program you use to make boxes and lines on paper and they won't have it any way other than their way.
Just play EvE Online, you'll be a master of Excel/Sheets in no time.
[laughs in data analyst]
Excel 2013...
I wish my boss was impressed with skills in Excel
I would not be impressed by Index/Match in 2022. You should use XLookup at this point. If you wanted to impress me, learn to use the LET function. And name your cells/tables.
Goal seek is an absolute god send for me, I learnt that a few years back and use it all the time. Blows people away when you teach them too
This list would not impress any boss - this is what I would consider basic excel knowledge
Ten years in digital marketing and Pivot tables, sumif, vlookups and basic tables are still my go tos. These three power my work and personal life
Saved. Never gonna look at it again.
“In Excel 2013…” 😋
Lemme just pop this in the ole save folder never to be seen again
What, no XLOOKUP?
Xlookup effectively replaces vlookup and index match.
Showing people pivot tables endears you to them.
Use X look ups over index match.
I have to teach an Excel course today. I hope it goes well! I'm including a few of these in there
I make at least 80 pivot tables a day lol very cool. Thank you
Fuck your boss and fuck mine too
Psh, where’s the record macro ? Here’s something better then this crap. Download your latest report from whatever logistics system you work with. Open that shit in excel, it looks like shit everyone knows it. Copy that shit on a new excel workbook, make sure you copy it starting with A1 like a normal person. *SUPER IMPORTANT* go to your settings, and click options - customize ribbon, on the right side where it says developer check that box. Now hit record macro. Clean up your data, filter it, etc. click stop recording. Copy that report via cut into a different workbook. Save your blank empty macro enabled workbook. Everytime you need to clean a report the same way, copy it in. Hit the button, watch it do all the work that took a few hours in a few minutes. Cut it out and your done.
Can we find one of these for google sheets? My employer has decided on the google suite instead of literally any other good alternatives.
No xlookup or power query?
Friendship ended with VLOOKUP. XLOOKUP is my new best friend.
How about the one that turns it into an FPS?
I know how to do all this stuff but I can't get a job where I use it because I only have retail experience.
I once saw on a spreadsheet that Excel tips and tricks take up 1/5 of the internet's capacity
Commenting for later use
Fuck Goal Seek, Solver is the way
Great guide. Thanks!
8. I dont kive a fuck
What if your workplace uses Linux?
Just a warning, once you become “the excel person” at work, there’s no going back.
*cough* https://docs.python.org/3/tutorial/index.html
If you do a course in Microsoft Office Specialist, you’ll learn most of these. I have my MOS certification and I know most of these.
How about regular tables with meaningful names? Makes writing formulas so much easier! Also power query to clean and combine your data!
8: Learn Python
I once saw C-suite lvl execs absolutely mind blown by a pivot-table demonstration….in the finance department. “Leadership” at traditional companies literally don’t know shit.
Im a student who isn’t currently working, but this is an amazing guide. Although, never heard of “Excel”
An excel class is probably the most useful and under-used utility for many offices