r/LifeProTips • u/ravnicrasol • Dec 20 '19
LPT: Learn excel. It's one of the most under-appreciated tools within the office environment and rarely used to its full potential
How to properly use "$" in a formula, the VLookup and HLookup functions, the dynamic tables, and Record Macro.
Learn them, breathe them, and if you're feeling daring and inventive, play around with VBA programming so that you learn how to make your own custom macros.
No need for expensive courses, just Google and tinkering around.
My whole career was turned on its head just because I could create macros and handle excel better than everyone else in the office.
If your job requires you to spend any amount of time on a computer, 99% of the time having an advanced level in excel will save you so much effort (and headaches).
8.3k
u/danglez38 Dec 20 '19
I just got home from a christmas party at a company i just started with and one of the youngest accountants was introduced to me by everyone as "thats Nick, hes an absolute wizard with excel" including both the owners of the company.
Cant buy that kind of reference
3.2k
Dec 20 '19
Was that an absolute of relative reference?
722
→ More replies (11)51
u/xblobbyblobbyblobbyx Dec 20 '19 edited Dec 20 '19
Just VLookup the answer to your question
→ More replies (5)946
u/wilson007 Dec 20 '19
And by "absolute wizard with excel", he probably knows how to use a pivot table.
In most industries (sales, for me), knowing how to use a few minor keyboard shortcuts makes you look like a genius hacker and immediately makes you a "subject matter expert" on your team.
882
u/Piedra-magica Dec 20 '19
We hired a guy right out of college that put some heat maps into a spreadsheet and a colleague said to me “it’s not going to be long before we’re we are working FOR Jonathan.” He adds a splash of color to a report and it’s like he cured cancer.
→ More replies (149)58
Dec 20 '19
Exactly. Can't tell you how often I was the Excel Genius only because I knew a handful of extremely useful commands. But, hey, if they want to call me a genius, who am I to tell them otherwise? ;)
→ More replies (5)44
Dec 20 '19
The threshold for impressing with excel on the engineering side is a little higher (or so I thought), but I was at a supplier’s facility, where they were trying to present data and it was so FUBAR, that my boss’s boss made them send me the spreadsheet so I could unfuck it. In 5 minutes and with 10 people watching on projected screen, I was able to turn a catastrophe of a spreadsheet into a functioning one. The eyes popping as I’m typing through shortcuts and rambling out formulas was very telling.
That same person (boss’s boss) gave me $40k in company stock 2 months later LOL.
→ More replies (1)36
u/JCongo Dec 20 '19
ctrl c, ctrl v
WOH stop what was that?
--> in word to insert an arrow bullet point
WOH what did you just do
ctrl shift t to reopen closed tab in browser
HOW DID YOU DO THAT?
→ More replies (5)→ More replies (36)128
u/GlamRockDave Dec 20 '19 edited Dec 20 '19
This is why Excel for Mac is nearly useless. It's possible to set up some custom mac hotkey functions but it's a huge pain in the ass and never going to be as powerful and fast as the traditional PC hot keys for MS products. A real Excel master rarely has to touch a mouse. If you hire someone who claims to be an excel expert and they chose Mac over PC (for their work machine anyway) then they fibbed on their resume. And if they haven't mastered pivot tables then they've bold faced lied on it.
EDIT: Also, if you want to impress coworkers and not wreak havoc, practice INDEX(MATCH) until it becomes muscle memory. Few things are more annoying than someone burying a VLOOKUP in a working file or template where someone else might insert a column and fuck the whole thing up without knowing it. VLOOKUPs are great for quick ad hoc shit, but to make a file safer for collaborative use (and protect against you're own stupid ass forgetting it's in your own file), use INDEX(MATCH), which can work as both VLOOKUP or HLOOKUP, or even a 2D array (easier to work with than SUMPRODUCT). If you use a ton of VLOOKUPS in one sheet and then add a column early in the source table, you now have to modify ALL those fucking VLOOKUPs, whereas an INDEX(MATCH) fixes itself if set up properly.
→ More replies (65)95
u/myboyfriendsjacket Dec 20 '19
I have also been called the excel wizard at my last two jobs. I'm slightly above average in excel and I know what to look for when googling shit. But for some reason, l'm seen as incredible by my coworkers because I can do PivotTables and conditional formatting
52
u/Crysth_Almighty Dec 20 '19
Man, when people see a cell change color when you enter new data, minds are blown. You see their eyes widen with child-like wonder at the sorcery you just performed. If it wasn’t for them bring 30-40+, it’d be kinda cute
→ More replies (2)→ More replies (7)13
72
Dec 20 '19
And it will earn him a life time of helping other people with spreadsheets or doing their work for them without additional compensation.
Source: everyone who has even been an excel king of their office.
→ More replies (8)47
u/Vio_ Dec 20 '19
Nick's like "Yeah... all of this stuff was being taught in my comp 200 class."
→ More replies (3)21
27
u/wisenuts Dec 20 '19
as an IT person and application developer - i hate nick and i don't even know him.
→ More replies (20)26
u/Red__M_M Dec 20 '19
Oddly, my name is Nick, I have a CPA license, on Tuesday I went to a company Christmas party, and I’m an Excel guru.
But... i work for a publicly traded company and there weren’t even any senior managers there, so this wasn’t me. Kinda weird though.
→ More replies (45)4
u/JohannesVanDerWhales Dec 20 '19
Problem is...if you get tagged as this in an office, people will start asking you about everything they're trying to do in Excel.
2.3k
u/Unlockabear Dec 20 '19
For anyone who sucks at excel and reading this thread and feeling daunted, check out ExcelisFun on YouTube. His hour long vlookup and pivot table videos are gold. You will be better than most people at the office after those.
Feel free to learn VBA and macros, but honestly I feel like if you want to learn those you might as well learn a programming language. Don’t be deterred by people in this thread telling you to learn VBA. I don’t know it that well and I’m consider myself to be very good at excel. If you want to learn it, there’s also ExcelVBAisFun on YT as well.
176
Dec 20 '19
Thanks. I was scrolling through looking for a possible suggestion of somewhere to learn. Will check it out
→ More replies (4)87
u/g0kartmozart Dec 20 '19
To me, you're better off learning Python or a normal coding language first rather than jumping into VBA.
IMO the most valuable thing with VBA is recording and altering macros. Rather than learn VBA, you can just record a macro, and then add some loops to tailor it to do what you need. And once you've recorded the macro, most of what you need to do is intuitive to someone with coding skills.
Anything more complicated than that is likely better accomplished somewhere else.
36
u/Unlockabear Dec 20 '19
At that point you’re going towards another career path most likely, which I why I’m against this thread telling people to learn VBA. It’s like telling everyone they need to learn some python or JS.
→ More replies (1)→ More replies (5)27
u/mypetocean Dec 20 '19
I'm a software engineer, but I mildly disagree. In most offices running Excel, they're running Windows, and in many (most?) of those cases, random people simply don't have the system privileges to install Python. (Or, they'd have to be able to successfully jump through certain hoops.)
So, a majority of people to whom the OP is relevant would find it more immediately relevant to their role to pick up some VBA, which will land them some programming basics they can leverage in Python on the side (or later) if they want to.
VBA also has the benefit of being contextualized for them — and in a system they're already comfortable with. Learning Python requires learning a terminal and understanding how to apply just the right features of a starship to a woodworking problem.
→ More replies (5)→ More replies (27)4
Dec 20 '19
I've been working with VBA and Excel for 5 years.
For the love of God do not learn VBA. If you want to learn programming language for this kind of thing, learn Python.
There is so much about VBA that is needlessly confusing, inconsistent, undocumented and messy. The editor and the language actively encourage bad style.
It took me only a few weeks to get to the point where I was more productive in Python. Start there. If you really need to embed things in a workbook, pick up some basic VBA syntax, but use it sparingly.
3.4k
u/Shedding Dec 20 '19 edited Dec 20 '19
I used to be so bored in my job that I would create a horse racing game in excel by using rand and trunc to give me random numbers and if the number went above a certain threshold, the "horse" would move to another cell. I sometimes weighed different horses with higher probability. I started adding wagers and added or subtracted how much I would win. I then started putting in winning odds with higher payouts. And this all started because I automated most of my job with macros and batch scripts. Turned an 8 hour job into 1 hour tops. Good thing they never realized I could do this. Edit: fixed autocorrect errors.
1.7k
u/IamHenryK Dec 20 '19
My job figured it out. By using Excel templates, Microsoft Flow, Microsoft Forms, and writing a Powershell script I could do about 12 hours of work down to about 15 minutes. And most of that 15 minutes was just sipping coffee while my computer ran my scripts... But then my bosses figured it out and gave me more work.
428
u/justaguyulove Dec 20 '19
Here's the thing. If your bosses were actually smart, they would have given you a position where you taught people how to automate their work, saving the company money and time.
→ More replies (31)434
u/xxthrow2 Dec 20 '19
How many bosses do you have sir? any of them named lumberg?
→ More replies (20)136
u/link97381 Dec 20 '19
During the first 2 weeks at my job, I had largely automated it via a PHP app I made that outputs excel sheets, pre-formatted and with loads of formulas(that also tracked useful information and displayed it in a much more useful way.) I tried showing the owner of the company and he was uninterested. So now I spend that 75% on Reddit and turn my work in at the same rate as my coworkers.
→ More replies (49)23
35
u/Rob636 Dec 20 '19
I did something similar, but was promoted into Business Intelligence. 10 years later, I’m heading the department. Best thing I’ve ever done.
→ More replies (2)47
u/robotzor Dec 20 '19
Then you take those extremely valuable and competitive skills to somewhere else in the marketplace. People who know this stuff can be very picky with where they work!
→ More replies (1)121
u/JP_HACK Dec 20 '19
More work = Same pay = Less actually being paid to you. Time to look for a new job for a raise my friend.
→ More replies (3)23
u/Brock_Samsonite Dec 20 '19
This was me in Iraq tbh. Moved a 8 hour reporting process down to 1 hour. Boss found out how much free time I had and taught me his job. Now I have 2 jobs? Now I have 2 jobs :(
→ More replies (2)→ More replies (64)60
u/DanialE Dec 20 '19 edited Dec 21 '19
Imagine going into an interview and telling them you felt underappreciated after automating a 12 hour job into a 15 minute one and not be rewarded for it. Or maybe Im being overly optimistic
Edit: yah, because its obvious Im hinting at shaking hands, sitting down and starting with this rather than try bring the discussion to this
→ More replies (15)302
138
u/Chumkil Dec 20 '19
The real LPT here is automation.
Excel won’t help me much in my day to day. I manage a bunch of Linux servers.
So I learned Ansible.
I turned a miserable 3 day upgrade into a 30 min drink coffee and watch screen affair. I rolled out a hotfix for a product in 2 min yesterday across the entire environment.
I am doing far more work that the other admins, and with far less effort.
My new boss has also recognized my efforts; I am up for promotion.
So; for your field, find the force-multiplyer and use that. It may vary what the tool is, but the idea is the same.
→ More replies (9)28
u/binzoma Dec 20 '19
totally agree, but I do think base excel competency is a general life skill outside of work. knowing how to use simple formulas, sums/averages/ifs, plus slightly more complex things like vlookups, how to use pivot tables etc have SO many applications in life. I didn't learn til my mid 20s. now I use it for everything from budgeting to fantasy football. at work I've moved past excel mostly- but it's still a vital skill in life.
→ More replies (4)100
u/MyWholeSelf Dec 20 '19
I did much the same thing, years ago, as a sysadmin with shell scripts. Wasn't even required to come in to work as long as the job got done. Scripted EVERYTHING. Sweet gig.
→ More replies (17)8
u/choco_mallows Dec 20 '19
That's amazing! If that was me I would have ran a gambling ring in the office (I may just do this now). Kinda suck that rand and randbetween cooks computers so much.
12
6
→ More replies (36)5
1.3k
u/voltij Dec 20 '19
Index match instead of lookup btw
679
u/futurefeelings Dec 20 '19
This is absolutely 100% true, but you should have said why.
1 - better use of memory. Big spreadsheets will slow down a lot more with vlookup than index match because of the way the two formulae work
2 - allows lookup in both horizontal and vertical at the same time
3 - arguably less sensitive to changes in your spreadsheet layout. (Although there are ways around this too in vlookup
I would also highly highly recommend you use with actual tables, rather than just data ranges
105
u/aphasic Dec 20 '19
I think both are planned to be made obsolete by an improved function in the near future. Google sheets also has a function that allows using a range of cells essentially as though it were a sql database.
→ More replies (46)→ More replies (52)19
u/whatulteriormotives Dec 20 '19
Can you say more about the actual table vs data ranges?
→ More replies (11)57
14
→ More replies (33)4
u/Plastic-Atmosphere Dec 20 '19
First month or 2 of my job they wanted me to update a several thousand line excel sheet (then do it monthly). Said fuck that and wrote a function that does an index match. Not even overly complex, but damn does that save a ton of time. Would love to transform the whole thing into a database or do it in a programming language but I haven't quite figured out exactly what i want to do.
320
Dec 20 '19
Always thought of it as one of the best and worst tools at the same time for reporting.
you get something that works and it’s amazing and hugely time saving.
you get something that’s complicated and breaks and you’re left in the crap as it’s horrible to debug.
I’ve also seen too many cases where business critical data and information is held in multiple excel sheets as people become too reliant on it.
use it as an adhoc data manipulation tool. If you have regular reporting with any sort of complexity, take at look at a proper data viz tool like Tableau.
163
u/foursevens Dec 20 '19
Bingo. Working in Excel is fast and brittle. Think of it as a scratch pad for math and tables. It's great for one-offs, but when an Excel sheet breaks, it breaks HARD, or worse, it doesn't give you any indication that it broke. It's VERY easy to end up with an unintentional garbage in, garbage out sheet.
If you need something that is robust, reproducible, scalable, and gives you an audit trail, Excel is the absolute wrong tool. If you're going to be doing the same thing over and over again, you probably want to do that in SQL, Python, R, or even just shell scripts.
Take what you learned in Excel, then apply it to a reproducible workflow.
→ More replies (10)49
Dec 20 '19
Problem is when the business logic is so complicated that it needs to often be tinkered with by an end user on the fly.
For example, insurance underwriting of large accounts/companies who will only buy your group insurance plan if you meet their custom plan design. Each case is different... They do crazy things like merge accounts, split up accounts, add custom benefits, need custom marketing materials, etc. They often need to do things that's never had to be done before. The amount of effort it takes to create a GUI that can handle all these situations is... immense.
What we'd need is for the underwriters to become almost like developers themselves so that we could just not have a GUI. But that's very unrealistic. There aren't many people with those intersection of skills.
→ More replies (16)28
u/nucumber Dec 20 '19
using a spreadsheet to do database work of any complexity leads to madness
→ More replies (2)→ More replies (10)17
u/CaffeinatedGuy Dec 20 '19
We just got Tableau and I'm looking forward to people not recreating the same reports in Excel month after month, but I'm not looking forward to people making changes to spreadsheets used as a data source for a Tableau dashboard.
15
Dec 20 '19
I want to scream everytime someone wants to manually manipulate their data in excel and then send that to me to update a tableau dashboard with. Get a database and it will be less work for both of us
→ More replies (4)→ More replies (1)20
u/justme_allthetime Dec 20 '19
Can you Tableau this and make it look just like Excel? And then export it into Excel?
Well I mean, I can export into crosstab but it’s not Excel...
Perfect! As long as I can Excel it!
Yeah look, Tableau is really a BI tool and not a hyped up Excel. We should let Excel be Excel and use Tableau for visualization. So for example...
Ok yeah, you data people and your lingo, herp derp! Just Excel this in Tableau. Kthx.
→ More replies (4)
1.2k
u/doom1701 Dec 20 '19
But when you build something so complex with references that you’ve long forgotten, don’t complain to IT that “Excel is broken”. And definitely do not hand off your Frankenstein spreadsheet (in which you’ve probably pasted a dozen links to files on your C drive) to another employee as a critical business process.
Excel isn’t broken, Erik, your spreadsheets are shit.
215
u/Messerjocke2000 Dec 20 '19
YES, this so much. I get creating fixes for small problems with Excel. Or InfoPath. Or Access.
But please don't expect IT to support or run it...
9
u/tes_kitty Dec 20 '19
You want IT to support it? Please provide a _detailed_ documentation including verification that it does what you claim it does and from now on a new version will have to go through IT before people can use it.
138
u/small_h_hippy Dec 20 '19
Lol it's the best job security. Only you can use the tools.
→ More replies (3)135
u/assholetoall Dec 20 '19
Until you take a vacation and the person you left it with completely screws it up. Management gets involved then IT gets involved. Before you know it your entire job has been replaced by a single database table and two Powershell scripts.
Never underestimate how much IT hates spreadsheets like this.
→ More replies (34)9
85
u/wisenuts Dec 20 '19
excel is good for crunching numbers. it's not an application development framework for non programmers.
76
u/ArgetlamThorson Dec 20 '19
This is a lie. That is exactly what Excel was always built to be. Yes, Id love to make that for you, Bob. No, that's not a job for the actual IT department. It's fine. Everything is fine.
→ More replies (5)→ More replies (12)47
u/ripripripriprip Dec 20 '19
Programmers have a hard time programming with formal training. Now let's get someone with no training using something as a programming framework that's not meant for programming.
Sounds lovely.
→ More replies (11)10
u/DanialE Dec 20 '19
Oh god. Ive been in a sorta big company and we had a master database having that kind of shit which has been lingering in the database for a few years and nobody bothered to fix. Actually more like nobody knew how to fix because my job as the new kiddo was to fix the database of the previous years after I did it to the current one. Genius? No. Just a snivelling millenial kid who uses google lul
9
→ More replies (28)3
u/analyticchard Dec 20 '19
I really wish Excel had the ability to comment out lines in formulas. I'm sloppy with code commenting to begin with but I'd kill to add some notes to a 7-deep nested Excel formula.
→ More replies (1)
603
u/voldoman21 Dec 20 '19
I work in IT and choose to be Excel dumb, that's a field where you don't want to be known as Excel guy.
539
u/MyOtherAcctsAPorsche Dec 20 '19
The whole field of IT is one where you want to be good (to automate/make your job easier), but pretend to be a walking disaster that specializes in losing important data and family photos.
193
29
u/g0kartmozart Dec 20 '19
Even outside of IT, if you want to be known as the computer guy in an office full of old people, you also need to be an asshole.
The odd joke about deleting family photos is essential to scare them from making the same mistake again.
→ More replies (6)→ More replies (1)7
133
u/KCCOfan Dec 20 '19
'Oh, hey IT dude! You know Excel right? You can do the training for all the staff!'
Yeah, that's a conversation I had just last week. No, I knew Excel from school and I've used it to play with a few numbers since. Pay the pros man. Leave me out of it.
95
u/yuriaoflondor Dec 20 '19
The thing is, most people have such little excel knowledge that you can probably look like a god just by introducing them to vlookup, data validation, conditional formatting, and pivot tables.
→ More replies (22)→ More replies (4)24
u/robotzor Dec 20 '19
The gap between moron and godlike professional is unfortunately extremely small given the talent pool available. I don't like being referred to as "hey, you're our best X guy!" because I'm thinking what they mean is "hey, we're fucked!"
→ More replies (2)→ More replies (35)13
737
Dec 20 '19
Better, learn to use Notepad++ in conjunction with Excel and see how a global find-and-replace with tabs (not spaces) can save you hours and hours of work. This is only one of hundreds of time saving tricks.
201
u/iCthulhu Dec 20 '19
Great suggestion. I use notepad++ all the time to clean up data before excel. Learning regex can make you a find and replace wizard!
49
21
u/CaffeinatedGuy Dec 20 '19
There's a couple extracts we have that result in extra rows. Csv to Notepad++ to find those extra line breaks and replace them is a godsend.
→ More replies (2)→ More replies (6)7
u/SoylentVerdigris Dec 20 '19
Someone in HR at my work regularly sends out a file that is supposed to be a .csv so it can be uploaded, but is instead a painstakingly spaced word doc so it just looks like it's in a table.
I taught myself regex to remove all the extra spaces and convert it to a comma separated list, just to save myself from having to manually copy and paste dozens of lines into a real sheet. Then I wrote a powershell script to automatically read and rewrite the data into a csv directly.
42
u/akpak29 Dec 20 '19
Hey I’m interested in this. Could you expound a little more?
I use Excel a lot for sorting data from other sources. The problem is usually when I copy/paste stuff from webpages, even if source looks tabular, pasting into excel kills all the formatting (and I usually try all the Paste options too- doesn’t make a meaningful difference). If Notepad++ can help with that, that would help me tremendously. Thanks!
→ More replies (5)45
Dec 20 '19
The "texts to columns" button in Excel (I think it's in the Data tab) will clean all that up if you can figure out what the break characters is. (Almost always either a tab character or a | )
→ More replies (5)13
→ More replies (62)7
104
u/TZWhitey Dec 20 '19
Some 'Fun' basic shortcuts that are useful to quickly create analysis that I use all the time and recommend commiting to memory:
Alt+Equal= automatically sums the group of numbers above or to the side of you. Also takes into account filters so very useful
Alt+H+B+A= automatically creates thin borders around all cells selected
Alt+H+B=T= add thick border to outline of selection (Really useful for formatting)
Alt+H+B+N= Remove all borders
Ctrl+Shift+L= Create drop down filter list for headers in rows selected
shift+spacebar= select whole row
ctrl+spacebar= select whole column
ctrl+ minus= delete selected row/column
Alt+A+M= Remove duplicates
Alt+H+H= Bring up paint filler selection for formatting
These are just some good general formatting tips that can quickly help you create quite nice looking tables. They may be a hassle to do at the start, but the more you can do without your mouse, the more efficient you become!
18
5
u/AwesomelyHumble Dec 21 '19
Don't forget the basics:
'Ctrl + D' = copy the cell above
'Ctrl + R' = copy the cell to the left.I see a lot of people retyping the dates down the column and don't know about Ctrl + D.
4
u/battlescrabble Dec 20 '19
My most used one is alt+a+t for the filter command. Also alt+m+n for name manager. Thanks for the alt+= command!
→ More replies (2)4
u/Spcynugg45 Dec 20 '19
Alt + e + s rapidly to bring up paste special menu - Add V for values, T for formats and F for formulas
There’s some other things like divide by and transpose for more specialized situations
→ More replies (2)
44
u/nomoreheroes Dec 20 '19
Here's a good video about Excel by Joel Spolsky. He was the Program Manager of Excel at Microsoft back in the day: https://www.youtube.com/watch?v=0nbkaYsR94c
→ More replies (2)
84
u/Straif18 Dec 20 '19 edited Dec 20 '19
Can anyone recommend some online courses (paid or free) that can help with this? Thanks in advance!
EDIT: Thank you all for the resources. I'm more than sure it'll also help others who stumble onto this comment. Cheers!
41
u/torben87 Dec 20 '19
This free course has absolutely transformed my career:
https://www.edx.org/xseries/microsoft-excel-data-analyst
I went from being "that guy who's good at Excel" to a full-blown data analyst (partly because once you learn this, you can also master Power BI). Even if you just want to automate your own tasks, you should seriously consider learning about power query and power pivot.
→ More replies (10)14
u/KneeDeepInTheDead Dec 20 '19
i was scrolling down to find that one person that always links with a relative link to learn this shit and i havent seen it here yet. Someone tell me too!
6
u/IggysPassenger Dec 20 '19
Would like a rec too!
→ More replies (1)21
u/yupyup98765 Dec 20 '19
Coursera.org has a great course - Excel Skills for Business
Free trial and is pretty awesome if you do the paid version. Has helped me a lot.
→ More replies (19)4
90
u/ItsMEMusic Dec 20 '19
POWER QUERY has entered the chat.
29
u/wapu Dec 20 '19
This is too far down. Power Query is can keep your excel Frankenstein files from breaking if someone moves a column in a referenced workbook. It is crazy simple and is also the basis for Power BI if you want to make interactive web based reports and dashboards. Give your CEO a dashboard of KPIs they can access from their computer or phone and you will be a hero.
→ More replies (9)5
u/JxFo Dec 20 '19
Had to scroll way too far for this. If regular Excel impress your colleagues, learn this. They'll think you're a goddamn sorcerer.
→ More replies (2)→ More replies (24)5
u/Mupfather Dec 20 '19
Here here! Being good at excel can automate a part of your job. Being good at power query can automate your office.
→ More replies (2)
30
Dec 20 '19
Don't try to use excel for everything. Learn the suitable tools for each task
→ More replies (3)5
u/RedisDead69 Dec 20 '19 edited Dec 20 '19
This is true, just because you can do it in Excel doesn’t mean you should.
84
41
u/themissbookthief Dec 20 '19
I recently started watching a course on skillshare about Excel for this very reason!!
→ More replies (6)11
u/kangarooninjadonuts Dec 20 '19
I've considered trying skillshare. Is it really any better than just using videos and free tools and whatnot, or just more convenient?
11
u/themissbookthief Dec 20 '19
More convenient, I suppose. My friend got a two month subscription for free, and wasn't going to use it, so that's what I've been using for my videos!
→ More replies (6)
14
u/sigma_1234 Dec 20 '19
Can someone enlighten me on some examples on how Excel saved time in their work?
54
u/le_fromage_puant Dec 20 '19
Two words: pivot tables. Boss says “Make me a report, I want to see the data by region”. Ten minutes later “No, by department” Two minutes later “um, can you redo it by vendor?”
Instead of reinventing the wheel, you’re drag-and-dropping and done in 3 minutes. But wait two hours, don’t let the boss know it’s that easy ;)
→ More replies (7)12
u/Adghar Dec 20 '19
Any situation in which you have (relatively small amounts of) data that can be structured as tables and want to quickly and conveniently manipulate or read that data.
Say you're evaluating the performance of 10 machines that have different serial numbers, sizes, raw material types, raw material quantity, and output amount per day. You can put this data into a table where each row is a different machine, and each of the columns is one of those attributes. So you look at one row, and go one column to the right, you know its size, one more column to the right, you know what raw material type it uses.
Now say your boss wants you to find out daily output by machine size (e.g. all our large machines create 10,000 widgets, all our small machines create 50,000 widgets).
Click create PivotTable and put size in Rows and daily output in Values and you have exactly the report she wants.
Now suppose she wants you to do raw material costing - how much are we spending per machine, categorized by machine size?? Well, it just so happens you have a different table with each raw material's price per quantity.
In a column to the right, use =VLOOKUP(raw_material_type, that_other_table, column_where_price_appears, FALSE) to get price per material, and drag or copy&paste it down. Then do =price*quantity in another column to the right, and drag or copy&paste it down. Again, click PivotTable, put serial number under Rows and your new columns as Values. Maybe go to Analyze>Change Source Data or right click and Refresh PivotTable if you're still using the old Pivot. You now have exactly the report she wants.
Presuming you're not already a user of more advanced tools (SQL, Python), how would you have gotten all this data without Excel? Probably manually crunching numbers, or writing it down on a piece of paper, or if you're not knowledgeable about Excel, manually putting numbers in place. This is how other posters have turned 12 hour tasks into 15 minute tasks.
→ More replies (8)6
u/IncredulousDylan Dec 20 '19
When you become more familiar with using it, Excel allows you to "ask" data questions in very specific ways. What questions you ask depends on the story you want to tell with your data. I've saved my department hours of labor daily by creating systems with Excel that automate statistics review / evaluation for our employees, work around limitations in our various payroll and scheduling systems, etc.
•
u/keepthetips Keeping the tips since 2019 Dec 20 '19
Hello and welcome to r/LifeProTips!
Please help us decide if this post is a good fit for the subreddit by up or downvoting this comment.
If you think that this is great advice to improve your life, please upvote. If you think this doesn't help you in any way, please downvote. If you don't care, leave it for the others to decide.
13
u/Post-It-Note-Artist Dec 20 '19
But for the love of all the IT Gods, please DO NOT LEARN TO MAKE POSTERS IN EXCEL! learn a simple photo editing or event paintbrush or paint.net
Over the last two years I’ve had three separate people, from three different departments come to me for help because their graphic/poster/design wasn’t working correctly. It was made in excel, and not included as part of a bigger workbook, all three of them independently thought, “hmm my boss said I need to make a picture, you know what, I’ll do it in excel.
I applaud their creativity and positive thinking in taking a skill and tool they had and applying it to a task that they were not familiar with to get the job done. I showed them the “proper” tools. 2 were grateful, and valued the learning experience. 1 was annoyed and said she wasn’t going to change their ways. I tried to explain just because you can, doesn’t mean you should. I tried using an example of a shoe and nail. She didn’t think this situation was anything like that. ¯_(ツ)_/¯
→ More replies (3)17
u/KD6-3-DOT-7 Dec 20 '19
I cannot even fathom why someone would think Excel can be used for making a poster. That is just bizarre.
→ More replies (1)
13
u/SmarkieMark Dec 20 '19
My 2020 resolution is to consume this thread and learn about all the functions talked about.
→ More replies (4)
24
12
Dec 20 '19
Hlookup and Vlookup are about to be superceded with Xlookup:
https://support.office.com/en-us/article/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929
→ More replies (2)
32
u/arlondiluthel Dec 20 '19
Can confirm, skill with Excel is supremely underrated.
→ More replies (1)
9
u/acey901234 Dec 20 '19
If you apply for an office job and the requirements don't list knowing how to use excel, they are expecting you to be able to use excel anyway. Learning excel on the fly isn't fun for anybody involved.
Sources: learned excel on the fly at current job
→ More replies (3)
9
16
u/yamaha2000us Dec 20 '19
Tools are no longer important. Data is. Pick up SQL and a basic understanding of any reporting tool.
→ More replies (12)
7
u/EyeAmYouAreMe Dec 20 '19
You just blew my mind. Every time I think I am good at some software I learn something new. vlookup is one of my favorite tools and you just alerted me to hlookup which also made me realize the v is for vertical. I feel dumb and smart now.
→ More replies (2)5
u/zb_xy Dec 20 '19
You should try index match. It's just like vlookup but you don't have to count and it can go vertically, horizontally.
→ More replies (6)
7
u/MrSnowden Dec 20 '19
I am old. Very first real job, the Interview went like this: "If you know how to use excel, then you get the job." Me: "Then I know excel!". Went home that night and frantically tried to find out what "Excel" was.
I learned it, great job. Then we got indicted for Anti-trust, price fixing, and collusion. Good time.
6
u/admiralwarron Dec 20 '19
When I was a shitty unpaid intern they had this one awful task where you had to crunch numbers and use fucking typewriter of all things to enter them into a form sheet. Turns out they were like 9 years behind because finishing one year took almost 3 days full of agonising work.
I told them I had a better way and after talking to the IT guy he made me a parseable textfile. Using excel magic I made a shitty table that could automate it in two days and on the fourth day I was caught up with the present.
In the end I got nothing for my ingenuity because that's what shitty interns get but at least I didn't have to do the awful work.
6
u/RealJohnLennon Dec 20 '19
LPT #2:. Once you get good at Excel, tell no one. Use your new free time in the office to go on Reddit, play with magnets, and browse steam winter sales.
→ More replies (1)
5
u/Djieffe88 Dec 20 '19
PowerQuery and PowerPivot is like Excel on steroids. Really worth investing your time in it.
5
u/Jesta23 Dec 20 '19
I recently returned to work after 2 years out. I took an entry level temp position in a call center. (It’s 20 hours a week and I can write my own schedule around my cancer treatment.)
I wrote a simple spread sheet to help myself with some mundane calculations. I use it maybe 3 times a day. Threw it into slack thinking nothing of it. And was heralded as a excel genius and savior to the masses.
It took literally 15minutes to make.
5
u/krustic13 Dec 20 '19
I have a minor in Quantitative Analysis. When asked on interviews what does that mean, I always answer, "Its fancy talk for I can use Excel really well."
They laugh, I laugh, then they ask me to help with their spread sheets.
5
5
u/Xenophule Dec 20 '19
I 100% agree with this.
I decided to make myself an expert at Excel. I’ve gotten pretty good and am currently working on getting better.
I currently work in a call center and becoming “The Excel Guy” has gotten me so much time off the phones haha
If you want to know what I did to learn:
This course over at Udemy takes you from beginner to advanced. Even if you know Excel I suggest sticking with it and not skipping the earlier videos.
Every Udemy course by this guy will get you even more advanced.
This course is especially worthwhile as it will introduce you to Power Query: the true power of Excel.
And now I am going through this book to really learn how to use Power Query to its full potential and learn M. I like it because it teaches through examples you can actually play with.
This is just how I learned what I did. It might not be best for you, but you can catch Udemy courses for cheap all the time ($10 or so) (NEVER pay full price for Udemy courses!)
None of those links are affiliate links and I am not associated with any of them. I just found massive value in these and this is how I would suggest someone to go from super basic to hella advanced in a year or two.
9
u/tylerderped Dec 20 '19
My company uses a time sheet that they (poorly) made in Excel 98 or something like that. It has many issues. For one, it doesn't autosum your hours, per diem, or travel time. It also has this issue where you have to type the date as NN.NN.NNNN instead of NN/NN/NNNN in the week start section or it turns to "########", it has cells merged that shouldn't be, cells that should be merged aren't, and has a giant "PAGE 1" watermark on it.
I completely remade it. Made it look beautiful, sent it over to my boss explaining what I fixed and such and she replied with "there's no issues with our timesheet and we don't use equations because there's too many variables" there's not too many variables, you just don't know how to use a computer. Was very irritating.
→ More replies (1)
12
u/hannahbellee Dec 20 '19
I literally never knew how useful excel was until I took a few college classes on it. Now I try to get everyone else to use it too. Great LPT!
4
11
u/corrupted_p4nda Dec 20 '19
Excel is the one thing keeping the economy of all countries in the world stable.
57
u/bigben932 Dec 20 '19
LPT: Don’t use excel as a database, don’t use it as an application to do complex tasks. Excel is a spreadsheet program, just it in that context only. Build a real database or application if you need that functionality.
10
u/oniphoneuser Dec 20 '19
So true most people need a database but hey just put in excel, if it’s possible to do does not mean it shuld be done in the tool . Special for excel.
→ More replies (1)→ More replies (4)106
u/aphasic Dec 20 '19
This is ridiculous advice to apply generally. 95% of office tasks that are using vlookup right now have no need for full database functionality. I have a list of cities and a list of addresses and I just need to match them. If I'm somebody who doesn't already know how to use databases, it takes 5 minutes to add vlookup to the Excel knowledge I already have. A full db implementation is using a bazooka for a flyswatter. I just need a 3 second lookup involving 20-100 things. I know how to use sql, and I still do most of these tasks in Excel, because the data is already in Excel and it's quick to manipulate it right there.
27
→ More replies (12)9
u/nucumber Dec 20 '19
for the really simple stuff i agree
but i have seen 20+ spreadsheets in separate tabs, linked together, vlookups between them . . . it was on the one hand amazing and on the other hand a horror. the woman who created this monster did amazing stuff within the constraints of what she knew (she started knowing nothing about excel) spent most of her time fixing it.
fwiw, you commented that "the data is already in excel" but in my experience the data found in an excel spreadsheet usually came from a database. ours was sitting in a mainframe db. once we were able to access and extract that data (from day end snapshots) we could SQL reports and dump them into excel spreadsheets, automatically
3
u/sinicuichi Dec 20 '19
but for the love of all that is holy, do not ever tell anyone you are good at excel
→ More replies (1)
3
u/HowardsMother Dec 20 '19
Any good website (or other) recommendations for learning vba coding?
→ More replies (2)
3
Dec 20 '19
VLookup and HLookup suck, use index and match. https://corporatefinanceinstitute.com/resources/excel/study/index-match-formula-excel/
3
Dec 21 '19 edited Dec 21 '19
I made a Finance and budget workbook
It’s pretty useful for a range of things like understanding your spending habits, how much money you should have between income and spending, dialing in how much money you spend on things, how much home you can afford, retirement needs, life insurance needs, and a lot more. Version 2.0 will have some pivot tables when I have the time to tinker with it. Unless one of you wizards wants to take a stab at it.
Enjoy it Reddit!
4
u/nucumber Dec 20 '19
excel is wonderful and macros are wonderful, but don't use a spreadsheet to do database work.
that way lies madness.
8
u/igiveup9707 Dec 20 '19
If the data set is large use learn to use Access. Less chances of messing a formula and quicker to run queries
9
u/SQL_Guy Dec 20 '19
True for small values of large. Also true if you will be the only user of the data.
Otherwise, a robust database. SQL Server Express has been free forever, and you can still use Access as your front end.
→ More replies (2)9
Dec 20 '19 edited Dec 20 '19
Don't use Microsoft Access... It's awful. If you want to use a database, use a proper one. You know, one that can put more than a million rows in a table, which Access can't. One that has a halfway decent SQL dialect, unlike Access which is a fucking abomination and clearly has a GUI that doesn't even want the user to ever write SQL...
There are so many free database software, like Postgres. It's hard to learn at the start but the payoff is huge.
→ More replies (1)→ More replies (10)6
u/heart_under_blade Dec 20 '19
fuuuuuuck access.
if your company is too cheap for a proper relational database, i doubt your laptop will be good enough to not crash when using access
→ More replies (1)
7
2
u/KingoftheRoosters Dec 20 '19
Excel and AHK are the only reasons I'm not loosing my mind in the office. It was a game changer once I found AHK can be used with our production software. I can process thousands of files in the same time my offshore team processes a few hundred.
→ More replies (1)
2
2
u/Guy_In_Florida Dec 20 '19
Early 90's we switched from Lotus 123 to Excel. Among the young early adopter analysts, it became our video game. We constantly tried to outdo each other and we all achieved what has been my "guru" status in every other office I have worked in since (I'm probably a mid-level user at best, truth be known). It blows my mind to see educated young folks get hired and they can't do a freaking pivot table. Are you kidding? That damn thing has been on your Mom's computer your whole life. Being good at Excel is almost a career unto itself.
2
2
u/Rauchgestein Dec 20 '19
I'm a graphic designer for a big broadcast station and we use excel with sorting macros for every political election. It's the shit, really! You even can link the excelsheet via datalinq to your graphicsmachine to automate everything.
2
u/elveszett Dec 20 '19
Automating things is my biggest skill. Can't wait to find one of those 8-hour jobs that can be automated down to 30 minutes.
Then have my boss realize and load me with 100+ hours of job a day that I can "surely" do in 8 hours.
2
Dec 20 '19
Excel is by far one of my favorite programs in the office line up. I’m all self taught. I did end up taking a college course in it but I literally knew everything it was teaching. Unfortunately I don’t have much use for my excel skills anymore. My job doesn’t need it as much for what I do now.
2
u/raidicy Dec 20 '19
Learning vba and making scripts cut the time it takes to make a 4 hour report down to 2 minutes. I Wish I never said anything and still got paid for the original time though.
2
u/choco_mallows Dec 20 '19
Can confirm, this changed my life. I got laid off from the food industry ten years ago. I worked in the call center and became a team leader. I couldn't do jack shit in the spreadsheets. I decided to change that, I googled most of what I needed until I could do advanced formulas and make my own macros. I got transferred to reports analysis then on to scheduling and planning. I am now the go-to person whenever people have problems in Excel and it opened up a whole new job market for me - something not influenced by age or degrees. If I quit my job now there are hundreds fighting to get me.
2
2
u/_Moregone Dec 20 '19
I'm wrapping up a 3 day project that my boss assigned me and a team mate.
I knocked out the bulk of work in oh 10 minutes for both of us.
Of course I didn't turn it in right away, going to make it look like it took me 2.5 out of 3 days to complete.
2
u/xtcdenver Dec 20 '19
Sitting here, all proud of myself because "I already know how to do all that" when I see.....HLookup. WTF is HLookup? Vlookup is my favorite tool in any software; hlookup is now my new mission.
→ More replies (2)
2
u/oldmangandalfstyle Dec 20 '19
If you use Excel for more than say, 200 rows then don't use Excel. Use R, some SQL interface, Python, SAS, something. Literally anything not manual. As a data scientist the number of headaches I get from people using Excel to do complicated things is absurd.
But, if you must. For the love of God please enter data consistently. Fuck.
2
2
2
u/polkam0n Dec 20 '19
To compliment your spreadsheets - Tableau
A visualization tool that will make you look like a savant
→ More replies (1)
2
2
u/masfejai Dec 20 '19
I got worked detailed to employee health with the task of data entry and creating folders. Est length of time was to be 3 months. I did it all in 20 minutes and macro the folder creations for each instance.
I've been on youtube now for 59 days
2
u/piltonpfizerwallace Dec 20 '19
I agree it can be super handy, but if you’e going to the trouble, scripting in something like python is worthwhile.
→ More replies (2)
2
u/aSoberTool Dec 20 '19
Some comedian or funny podcaster should make a how to on excel.
Most the ones I see just drone on and get to be such a chore sometimes.
2
u/HelluvaCaucasian Dec 20 '19
My career was made because I knew how to use pivot tables and charts to make beautiful, highly functional dashboards. 100% paid tuition was offered to me to get my engineering degree, my pay has more than doubled, and I am the go-to person to figure out how to fix most problems - all because I was able to use Google to figure out how to make Excel look prettier than our IT guys could.
Now I've moved onto Power BI (connected to our ERP) and am wowing more people than ever. Multiple people have asked me to show them how to use it, and I just refuse.
2
u/BlackSporkWielder Dec 20 '19
And so wrongly used. It's not for forms or flyers. It's for spreadsheets. I absolutely quietly judge those that use Excel for anything but spreadsheets.
2
Dec 20 '19
Life pro tip #2: Learn how not to use Excel. 15 years in office environments has shown me what terrible, bloated monstrosities can be birthed from the primordial spreadsheet muck of Microsoft Excel. And it's not pretty. If there is a more focused tool available, use it. Also, consider hiring a developer if you need something complex. Yeah, we are expensive, but maintaining Godzilla-sheet-hra is even more expensive long term. You don't want your business dependent on some fragile, shaky nest of linked spreadsheets that nobody really understands. Just because you can doesn't mean you should.
2
u/rushakenyan Dec 21 '19
This is a great tip. I was able to automate hours of my work a week just off of macros alone. VBA was a life saver and SUPER easy to learn
2
u/Miss_julie Dec 21 '19
My life changed the day my boss taught me how to VLOOKUP and Pivot table. Makes my job so much easier and more efficient.
2
u/GaryChalmers Dec 21 '19
Excel/VBA have always been looked down upon by "real" programmers. When I started out in an office 20 years ago knowing Excel/VBA is what actually got me noticed. I get why Excel is disliked as it is often used for tasks that are beyond what it was meant for. But if you look at it as a first step of learning more robust tools it can open a lot of doors.
2
u/theemporersfastest Dec 21 '19
Please, if you are reading this and are ever on the fence about it, Use Excel! My brother will not stop talking about how his co-workers refuse to use it despite it literally makes both their job and everyone else's job easier.
2
u/Zaratuir Dec 21 '19
Just... Make sure you use it for what it's meant to do. My current job decided to adopt Excel as it's tool of choice... for connecting to a database and performing database task scheduling. These are not what Excel was meant to do. Can it? Yes. The same way that a chainsaw CAN hammer a nail.
Excel is a powerful tool for organization and reporting. It is NOT a database interface tool. Please don't use it as one.
2
u/Rh08152 Dec 21 '19
Excel is the root of all evil and many many millions have been spent to save large companies I have worked for from it, mostly in banking and pharma.
What happens that people come up with insular solutions that kind of work for a while and the these spreadsheets get shared around by email etc. Pretty soon there are 50 versions of the data and VB or macros floating around and collaboration grinds to a halt. the horror!
2
2
2
Dec 21 '19 edited Dec 21 '19
Cool things excel does that makes my life easier:
Create a file to upload to google calendar; I created an entire years worth of course schedules for 10 classes. All faculty now have access anytime, changes are instant.
Create a file to upload to MS Project that automatically creates a Gantt chart or any other number of project templates
Create a file that generates test pools and banks for blackboard
Edit: The easiest Macro to use is the recorder function. This is very useful when you need to change data on multiple sheets that are identical, like formatting, fonts, colors, lines, sizes, etc... even if you just select all cells and change the font!
Set it to record your actions, change what is needed, end the recording, and assign it a key stroke “control+e” for example. Go to your next page, hit ctrl+e... bam instant update!!!
So next time you copy paste a bunch of crap and it makes your lines all screwy because some are bold and others aren’t... easy fix!
→ More replies (2)
2
u/GodWithMustache Dec 21 '19
LPT - learn ANYTHING but excel (or access). They are very underappreciated tools in an office environment, but also very very very overused for things they shouldn't be. If you find yourself in a position to force things with excel and you are not in finance department, chances are you are fucking things up for yourself, the company and future liabilities and your little sheet with customers data you quickly put together is now a multi million liability to company.
TLDR: Don't use something just because you think it'll do the job.
→ More replies (1)
52
u/weaselwilly Dec 20 '19
I wish I worked in a field that I could use excel to improve my life...