LPT: Take a few days to familiarize yourself with a spreadsheet app (e.g., MS Excel, Google Sheets). The uses are endless for school, the office, life, and other people will think you're a wizard.

Submitted by tophswanson t3_116q2c3 in LifeProTips

In high school and college no one told me how revolutionary spreadsheet tech is. It wasn't until I got to an accounting job out of college that I really learned how to use formulas, links, vlookup, summary tables, etc...

At my environmental compliance job, I automated chemical calculations so we just had to copy/paste results from a contractor, and then the report we needed to send into the state would pop up at the end. Spent 2 hours on a template to save myself 1.5 hr of work each month.

In grad school for engineering, several of my peers were crunching data manually, whereas I could spend 20 min setting up some formulas and graphs, and then copy/paste data in for different trials and be done with my analysis within an hour.

I tutor in math/science on the side, and try to get my HS students to check it out. When I showed one of them how to do an averaging formula, it blew his mind because he had been typing everything into a calculator manually.

At my current engineering job, there was no component tracker to keep tabs on what equipment we had available, where it was, what features it had, etc...I spent 2 days inventorying our components and created a 10 tab spreadsheet with a data entry tab where if you take out a component for testing, you change the status from "System #4" to "bench" in a drop-down menu, and if you check the "System #4" tab, the list of components inside it automatically updates to show if a component is present and what serial # is there.

I have a spreadsheet to inventory my floss colors for cross stitching, another one for calculating disc golf scores when we play, one for finance, etc...

You don't have to be a math genius, you don't need to be an expert and spend a year learning to use it. There are tons of free tutorials online and once you get the basics, you will see a use for it EVERYWHERE. It can also help your career by being able to demonstrate ways you contribute by cutting down time spent on tasks and your initiative in solving problems. Literally cannot emphasize enough how much I love spreadsheets.

1,766

Comments

You must log in or register to comment.

jj-reeder t1_j97w1ug wrote

If you know vlookup alone, you can make a LOT of people believe you’re a genius.

269

textris t1_j98qeoo wrote

INDEX MATCH ftw!

78

Good-Astronomer-1138 t1_j98y572 wrote

This has been replaced by XLOOKUP now

68

tomistruth t1_j99qiki wrote

Example code for people who rarely work in excel?

8

thequicknessinc t1_j9a4cj1 wrote

=XLOOKUP(value to look up,range/array to lookup value in, range/array to return)

16

tomistruth t1_j9a60l0 wrote

Thx! Noted.

5

Good-Astronomer-1138 t1_j9a8hqd wrote

You can also denote a value for blanks and select a search order at the end : so like xlookup(value, search range, return range, “”, 1) would default all blanks to blank and sort in the default way

3

PatrickKieliszek t1_j98xyao wrote

Index Match is good if your data set is large and you're worried about execution time.

For small data I almost exclusively use xlookup.

14

geekgodzeus t1_j99h77s wrote

I recently started using SUMIFS and it has been a godsend .

9

JethroFire t1_j9960df wrote

Fuuck, I came here to say this and you beat me to it. I like Index and Match, too. Also MaxIfs and MinIfs are super cool functions.

4

nucumber t1_j9akyn9 wrote

you can go far with xlookup, which gives some database functionality to a spreadsheet. the problem is you can go too far, trying to manipulate too much data from too many spreadsheets.... all those linked tabs etc

at that point you should learn Access and maybe SQL to handle the mass of data

1

hacktheself t1_j97yufz wrote

let’s be specific:

excel is used in so many places you would not believe.

i like pages for my personal stuff but excel is so embedded within the business and government world. if

78

Katzeye t1_j981o8p wrote

I heard an interview economist say “we would be shocked how much of the worlds economy runs on excel”.

49

popejubal t1_j98pxqf wrote

Knowing the phrase “pivot tables” wasn’t the sole reason I was hired for my current job, but it was a big factor.

31

Trickycoolj t1_j99em7v wrote

My husband was going through a temp agency for work during the 2008 recession. They asked him to take an excel test. At the end he says “no pivot tables? Lookups?” The agency was like “um we’ll just mark your skill as ‘excel guru’”

18

gargravarr2112 t1_j9a6e3a wrote

When you consider Excel auto-formats data basically as it likes, whether it makes sense or not, this explains a LOT about the world's economy...

6

gargravarr2112 t1_j9a6aeh wrote

Remy's 0th Law of Software Development:

> At the end of the day, all the user really wants is Excel.

1

pedrodteixeira t1_j98ccba wrote

I never used Excel in Uni.

It's my main app at work.

They should have taught me Excel.

55

trymypi t1_j98qpid wrote

I put a class together for exactly this last semester. Most students were pretty happy but one thought it would be better for me to have taught Photoshop haha. (There's already a class for this)

8

Trickycoolj t1_j99ep42 wrote

Tell me you didn’t take basic Economics, Finance or Accounting at Uni without telling me you didn’t take Econ, Finance or Accounting.

3

shastaxc t1_j9aad45 wrote

Kinda not really the point. Excel should be taught in gen ed

5

Asisreo1 t1_j9aapoe wrote

I took macro-econ in uni, but they never taught me excel. The most excel lwarning I got was from engineering finances class.

3

Aitorgmz t1_j9an0x1 wrote

The whole point of this post was to highlight Excel is just not useful on those fields lol

1

bassbahl t1_j97yhdo wrote

Spent some time in the last year learning various filters, array formulas, and other ways to automate things I do every day. Such a massive time saver. I show things to others and like you said I’m looked at like a wizard.

Then I show them simple databases like Airtable…

Cheers fellow spreadsheet enthusiast!

44

heated4life t1_j9aofyd wrote

How's you learn? I looked up YouTube videos but they were so dry and seemed outdated

4

bassbahl t1_j9apcke wrote

I learned through a lot of videos, reading tutorials, sort of knowing what I want, and trial and error. Yes, a lot of the videos are dry, but so are logic formulas. Honestly the simple ones are sometimes the best. Nobody yelling at me to “smash that subscribe button”.

3

Snake_eyes_12 t1_j981vue wrote

Spreadsheet applications and programs is part of the reason personal computers took off in the first place. What use to take hours and a team to do can be done in minutes by 1 person.

20

[deleted] t1_j99z5cr wrote

[deleted]

0

tuhn t1_j9a6gpe wrote

Python is much harder. Also you can't just run Python everywhere.

Being an Excel wizard takes about 2 weeks of studying, being a Python wizard takes years.

2

Dohnakun t1_j9bl9tc wrote

> Also you can't just run Python everywhere.

Sure you can. Meanwhile Excel...

1

jlav18 t1_j98cm22 wrote

I used to use to manually crunch the data (took a while to do) now using self taught pivot tables and VLookup what used to take all day, now takes me 30 mins to do…

I don’t know what to do with my free time now and feel weird like I am doing “less work” and tend to sit at my desk with nothing to do… am I cheating the work place hour system?

17

roodypoo29 t1_j98jo7p wrote

Seems like you're winning the workplace hourly system

22

Malumeze86 t1_j99hso0 wrote

Just don't tell the Workplace Hourly System that you're idle or they'll find you more work to do.

5

sometimeagreatnotion t1_j98ytma wrote

I think if teachers and professors started creating tasks/work that surround excel usage it’s a great way to learn by doing and showing the value of excel both in high school and Uni/college. Its such a key skill employers are looking for and not too difficult to pick up on your own.

It is amazing how so many people marvel or claim “you’re a wizard” when you can demonstrate basic and intermediate excel functions that help automate work tasks, and you’re like, “uh no I just watched a video to learn how to do it.” But also I’ve also been amazed at the resistance to learn how to continue using excel in more effective and advanced ways by co-workers.

16

Septopuss7 t1_j99pv3b wrote

I told my boss that I could take a picture of our handwritten recipes with my phone, copy it into a word file, convert them (in our case, increase the yields), PLUS print them out, all in a few minutes, and he looked at me like I was a complete idiot who was wasting his time.

5

shastaxc t1_j9aakw9 wrote

Yeah you might wanna get a new job. Having a short-sighted boss is a pretty clear indication that the place is never going to expand and flourish.

2

PM_ME_FUNFAX t1_j97yjdk wrote

Oh yeah, at my last job I automated time tracking with production vs downtime... Every minute has to be accounted for. All I did was have it do the math with inputs and a few vlookups for other spreadsheets on the company network. Saves me, and eventually a lot of people, a whole lot of time. Haven't worked there in years and the operators are still using it.

15

TwinkleSprinkle27 t1_j99azh8 wrote

Recommendations, please!!!

(…ideally, free & geared more toward beginners)

What is the best explainer or “how-to” guide for Excel’s most useful functions?

11

Septopuss7 t1_j99pja0 wrote

YouTube, 100%

5

Lordlillefugl t1_j9a5leh wrote

And whenever you have a problem just google it. It’s definitely someone figuring out the solution already.

3

Limp_Distribution t1_j987gjc wrote

There are whole games programmed in Excel. It’s an incredibly powerful tool, if you know how to use it.

9

sadiesaysit t1_j984xb8 wrote

I never learned the skills but I’ve been wanting so bad to learn but confused on whether to learn Excel, googlesheets (isn’t that a thing?), or something else. What would I be using it for? I don’t know but let’s go with life and business information. What specific resources, YouTube or tutorials would you recommend?

8

Just-Take-One t1_j98p8b0 wrote

Excel Web is good enough for most users. You don't even need to learn any fancy commands, just simple mathematics is, again, good enough for most users. Google sheets will have all the same basic commands, just in a slightly different interface.

I've always found a personal budget to be a good learning tool. Write some expense labels on the rows, months in the columns, fill in a dollar amount in the middle and have a total column at the end. Formulas all follow the same general layout "=A1+B1+C1+D1". You can use mathematical functions too like "Sum" and "Average" etc which would look like "=SUM(A1:D1)".

Basically, just use it as a fancy calculator while you're learning.

14

sadiesaysit t1_j98sd75 wrote

Thanks for your advice! I’ll start with a simple budget and start playing around with it.

4

shastaxc t1_j9abnf1 wrote

I agree on the budget idea. I was just updating mine yesterday. I like to tag each expense with the account name that will pay it. I split my expenses to different accounts for different reasons, like shared expenses with my partner get paid from a joint account. I also think it's not fair to split joint expenses 50/50 because we make very different salaries so we split it according to the proportion of our post-tax income. So I have a formula that does all the math and then tells me how much money I need to deposit into the joint account each paycheck to cover those expenses, and same for my partner. I get paid twice a month whereas she gets paid every 2 weeks, so even if we split the bills 50/50 our deposit amounts per paycheck would be different.

It does a lot of other stuff too. This could all be done as quickly by hand the first time, but where spreadsheets really shine is reusing them later. Now, whenever I need to change an expense amount (prices for stuff change over time), add/remove expenses, or change income (which happens every year), all the info I need is recalculated instantly.

2

VDubDJ t1_j98h2gw wrote

W3schools is free and has a bunch of great intro stuff

5

Jill_X t1_j9a4ils wrote

Excel is probably the most wide-spread tool in offices.

My favourite is Google Spreadsheets / Docs, as it runs on almost any device with a browser and in an app on my Android phone. It also allows multiple users to view, comment on and edit the same document.

Then you have open office / libre office, which are open source clones of excel. They however use open document file formats, which are not supported by Excel.

Overall, they all work very similarly with slight differences in how to format formulas.

So, learning any of them gets you half-way to knowing the other.

2

32BP t1_j99dzit wrote

A lot of my professional success comes down to knowing one or two tricks with Excel. (HLOOKUP and VLOOKUP).

7

schmancie-2 t1_j9a58wt wrote

Hlookup?

2

tuhn t1_j9a6p7m wrote

Horizontal lookup (hlookup) instead of Vertical lookup (vlookup).

It just looks from columns instead of rows.

3

Aganihm1 t1_j99gv06 wrote

> Hagrid, I've plotted the amount of spells learned against the salaries of the corresponding teachers and I think Hogwarts could really cut down on costs.

You're a Wizard, Harry!

6

mikelwrnc t1_j98kr97 wrote

But also learn when you need a more advanced tool. Do not use it for anything but the most trivial data analysis for example. Move to R or Python for that.

5

BIessthefaII t1_j998l3s wrote

Now I need a YSK of where to go to better learn them

5

tinkersdamn t1_j9a31pj wrote

Right? Like 'Google some videos' gets me nowhere. I need a curriculum.

Edit: I just google 'best indie online courses to learn excel', read the first six articles (Eg 'Top 10 courses to learn Excel) and there was literally not one single duplicate among them. The consensus does seem to be that you really don't need to pay to learn.

3

GardenRave0416 t1_j98ad4q wrote

Can confirm, Google sheets was a game changer!

4

PopularLeek t1_j99dftc wrote

Better, don't tell people when you automate something and you've got yourself tons of spare time to do whatever you like.

4

Virt_McPolygon t1_j99jv6r wrote

Pretty sure I saw an LPT once saying if you're great at Excel, don't tell people at work or you'll end up constantly making sheets for people, and helping others fix theirs.

4

Pioneer64 t1_j99k1id wrote

can somebody make a list of everyday tasks the average person does that can be enhanced by excel - i can see its use for personal budgeting but everything else seems job specific

4

jbritchkow t1_j9brdkk wrote

Off the top of my head:

Splitting a check at dinner Financial projections and other related budgeting extensions Video game stat calculator Video game collection index

2

gargravarr2112 t1_j9a66eb wrote

Equally, do not create an "application" in it that becomes "business-critical" after you share it with your colleagues.

Sincerely,

Every IT department who has to support these things, ever.

4

Skalion t1_j99kx40 wrote

Excel Wizard speaking here, i totally agree, the amount of people asking for excel stuff is real. At least Kost of the time they don't ask the most simple addition and look ups but rather advanced stuff. sure i might not have the answer right away, but give me 10minutes and I'll get it done.

Next step VBA, super easy to get into with excel and saved me tons of work to just automate some repetitive stuff

3

Heyhihello04 t1_j9a4489 wrote

Where I work you're punished with more work if you admit to knowing how to use these programs because you're suddenly the only person in the office who knows how to use them right so everyone comes to you to do anything that involves these programs to no benefit to you.

2

4Ozonia t1_j9a59f6 wrote

Debating whether to buy Office for our new laptop, or just learn Google Sheets better. Retired, so this is for home things like Christmas card lists, and other spreadsheets. I will look on YouTube to see how to sort, add, etc.?

2

wristpins t1_j9a87jq wrote

I will warn you, people will pawn their excel work off on you. I showed a coworker my sheet that had simple stuff like sumifs or match, now I get asked a bunch of stuff. It's cool people want to learn, but people really have very little understanding of excel outside basics

2

keepthetips t1_j97tizj wrote

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.

1

esp211 t1_j9a11st wrote

Anything that has to do with numbers, I use Sheets. I think in terms of spreadsheets and I have all of our finances on one document.

I planned out our life to age 100 with all expected income, investments, pension, social security, etc. including pessimistic and optimistic outcomes.

Knowing how to to do spreadsheets is probably the single best skill in any job.

1

PvtPill t1_j9a5cev wrote

It’s absolutely necessary to know excel stuff nowadays and no one will think you are a wizard. Excel is even required for a lot of jobs where I live..

1

aqxea2500 t1_j9a6i2i wrote

When I went to trade school I remember taking a spreadsheet class, and was honestly amazed at what you can do with excel. I seriously feel like an idiot for not learning it better in school.

1

Blocky_Master t1_j9aaqvj wrote

at this point just use notion tho

1

carlgorn t1_j9adz8p wrote

Are we at the point in idiocracy where using excel and word makes you a wizard?

1

Bean_Juice_Brew t1_j9aep8f wrote

Let's keep this thing going then, what are your top ten formulas? I've seen junk articles online with nothing useful

1

randomnumber46 t1_j9afc9d wrote

I can get through a task in less than 10 mins that takes some of my coworkers over an hour thanks to a couple of spreadsheets I made over the course of maybe a day or two.

There is no great secret, we just need to complie a lot of information in a specific format from lots of different places. A spreadsheet can just do that in seconds and also removes a lot of human error. Honestly the amount of time I must have saved by not having to remember to use the US date format probably more than makes up for the time it took to make the spreadsheet.

1

zeke1220 t1_j9ain90 wrote

How did you get to grad school without your peers having experience with a spreadsheet program? I learned how to use Excel so I could profit on the WoW auction house more effectively.

1

christurnbull t1_j9ak57l wrote

Sidenote:

Excel was not intended for "big data". It doesn't have good ram management, and doesn't play very will with multiple cores/threads.

Listen when excel tells you that you are outgrowing its abilities.

1

GomezFigueroa t1_j9akh3g wrote

Also, if you ever want excel to do something for you just google a description of the problem or the outcome you want + excel. Odds are you will find several step by steps. Your boss will think you’re amazing.

1

kamilman t1_j9an2wx wrote

Can confirm. I was dubbed "Excel Guru" at one of my jobs and held this title ever since. And it was just basic stuff most of the time.

Then again, I did build a full database for disciplinary dossiers for the National chamber of bailiffs in Belgium, even built a fancy pants search sheet in the Excel file where you got all the information on a dossier by simply typing the code of the dossier (and specifying the language in case of dossiers before 2022).

But they let me go and they had cold sweats even when Excel was being mentioned, so I don't even know if my little baby is still used by them today...

1

ericds1214 t1_j9ao34r wrote

Beyond just spending a few days familiarizing yourself, use Excel for as much in your daily life as you can. Budget tracking, chore charts if you have kids, whatever you can think of in or out of work. The more you use it, the more you'll see opportunities to improve your Excel skills and make life easier.

1

AldermanAl t1_j9aq8dv wrote

Greatest office tool of all time, but easy to be relied upon to heavily and become end user computing files that have a high risk input error. Then everything you have been reporting becomes inaccurate with one person's formula screw-up.

Always store a gold copy of any business important excel that is password protected and unable to be changed so that you can do regular comparisons to ensure the in use copy is the same as the gold.

1

dream_weasel t1_j9arcbi wrote

Sure learn it but don't let it be your ownly tool.

Learn to program early and stop abusing the MS suite.

1

Muffstic t1_j9aso1s wrote

Wait until you guys find out about power query

1

tthrivi t1_j9asu4o wrote

Even better, learn a programming language like python! Can do the same things as excel but much more powerful!

1

orangepalm t1_j9atmvk wrote

Anytime know a quality spreadsheet for mobile? So many times I've been out and about and thought I could fix this if I had a spreadsheet.

1

Canjie_Pheasant t1_j9dy7l6 wrote

I use LibreOffice Calc for personal stuff.

1

carjo78 t1_j9a2k8t wrote

Do not become the "excel bod" in the office though. Thats the guys who have everything on a spreadsheet and will bore anyone to death about em.

0

thequicknessinc t1_j9a5wkt wrote

Yikes…

0

carjo78 t1_j9a73s7 wrote

Lol. Im dealing with one right now. I may have to hit him with my keyboard....

1

thequicknessinc t1_j9a8ugh wrote

No, yikes that you talk about your co-workers like that.

0

Aitorgmz t1_j9anobw wrote

Yep, saying a co-worker is annoying is totally out of limits /s

0

thequicknessinc t1_j9au769 wrote

You figure that a stranger would be more likely to relate with you or judge your character when sharing disparaging thoughts on people they don’t know? By all means, do what you feel you must, it’s no consequence to me.

0

colouredmirrorball t1_j9a5b0g wrote

LPT: don't learn spreadsheet apps, otherwise you'll have to work with spreadsheet apps every day!

0