Support NeoGAF

[NecrosaroIII]

I've always enjoyed Excel as a program, but the most advanced thing I knew how to do was drop down menus and pivot tables. I recently learned how to do vlookup and IF formulas.

I want to continue to learn more advanced techniques for Excel. Any good website resources? Next I want to learn how to apply AND.

 

[Ozzy Onya A2Z]

Keep at it champ



More serious note, Excel Is Fun YouTube channel. Generally real world and very well organised content for what you want to learn. It's a pity the guy's voice is annoying AF but solid resource with learning playlists that make logical learning sense.

 

[isual]

true masters of excel don't use a mouse; they just use the keyboard and shortcuts.

 

[NecrosaroIII]

That kid is bad ass. Love that kid.

 

[Papa]

It’s pretty good. Not great for any iterative process though. Sure VBA can get you most of the way there but it’s really slow and is better handled by other programs like R and MatLab.

 

[Cunth]

is it for numbers and shit

 

[Foamy]

I like turtles!

 

[Max_Po]

is it for numbers and shit

I can highlight a list of numbers and hit Auto Sum..

 

[Maiden Voyage]

Once I found out you can expand drop-down selections automatically by naming tables, it made my life easy. Enabling developer mode & coding macros is incredibly helpful as well.

I just use google whenever I have a question.

 

[Amory]

OMG NERD

 

[Papa]

INDEX-MATCH is a far more flexible, generic form of VLOOKUP and HLOOKUP that can search in any direction

 

[SpartanN92]

Everything in life can be summed up by a good excel spreadsheet

 

[Strife In a Teacup]

I just use google whenever I have a question.

Yup, that's how I find all these handy formulas. Like turning those annoying "dates" like 20200810 that come on reports but won't format as dates. So use something like =TEXT(A2,"0000\/00\/00")+0 and turn it into a serial number then format that column as dates.

 

[Weiji]

Use INDEX(,MATCH(,,0))

it’s better then vlookup

also if statement based array functions are really useful for lookups when you need to use horizontal and vertical variables.

learn offset( for extra complexity

learn indirect l( so you can manipulate sub variables inside you formulas and make them co dependent

 

[nbcjr]

Excel really is unrivaled. Open office will never reach it.

 

[nbcjr]

Once I found out you can expand drop-down selections automatically by naming tables, it made my life easy. Enabling developer mode & coding macros is incredibly helpful as well.

I just use google whenever I have a question.

Use INDEX(,MATCH(,,0))

it’s better then vlookup

also if statement based array functions are really useful for lookups when you need to use horizontal and vertical variables.

learn offset( for extra complexity

learn indirect l( so you can manipulate sub variables inside you formulas and make them co dependent

care to give an example on the INDEX and the naming tables usage?

 

[StreetsofBeige]

If you want to get hardcore, do macros/VBA stuff.

For me, I don't get into that so my usage is probably in the middle range like most other people who use Excel daily.

Some other useful shit when mucking around with large data dumps, charts or pivot tables. Below are some beginner tips for anyone reading:

- Conditional formatting cells colours

- Doing CTRL-SHIFT-END or CTRL-SHIFT-arrow key to quickly get to the end of a row or column where the last data cell is

- In Files --> Options there's so many random buttons ad choices to turn off and on. I always go to ADVANCED and choose that Get Rid of Zeros button so my sheets are clean of cells showing a 0

-For pivot tables, go its Options tab and there's a bunch of nice features that makes life easier and nicer like checking off autofit width, specifying what number should show up (like a zero) if there's an error, and in the Options --> Data tab, you can clean things up like Making Items to Retain = None. If your file is getting massive, you can make it smaller by not Saving Source Data. And don't Refresh the Pivot on opening. Creating your formulas is awesome too

- When adding buttons to the ribbon, I always make sure to have the Select Visible Cells button shown, so when I copy cells from a filtered selection using it, it only copies the shown cells (not the hidden cells). NOt sure if there are default, but Undo and Redo too.

- I've never bothered needing to make them or use them, but some people have shown data sets using Slicers which was nice

 

[JBat]

Excel is the well intentioned friend that tries super hard to be helpful but only gets in the way. The auto formatting drives me insane. Please stop dropping leading zeroes. No I don't want certain numbers separated by a dash to be a date. Please for the love of God let me open a second spread sheet in a new instance without having to jump through hoops.

 

[Dark Star]

Excel is my life at work. I started learning Power BI recently, too. Very helpful for the visualization aspect of reporting data.

 

[Vicetrailia]

Excel is good, I need to get into vba.

 

[NecrosaroIII]

Use INDEX(,MATCH(,,0))

it’s better then vlookup

also if statement based array functions are really useful for lookups when you need to use horizontal and vertical variables.

learn offset( for extra complexity

learn indirect l( so you can manipulate sub variables inside you formulas and make them co dependent

Man, you weren't kidding.

It's so much easier doing
=If (b3=0,INDEX(CLIENTS!B2:AA100,(MATCH(B1,CLIENTS!A2:A100,0),MATCH(B5, CLIENTS!I1:K1,0)),0)

Rather than

=IF(B3=0,IF($B$5="CAR",VLOOKUP(B1,CLIENTS!$A$1:$AA$100,9,0),IF(B5="CARGO VAN",VLOOKUP(B1,CLIENTS!$A$1:$AA$100,10,0),IF(B5="TRUCK",VLOOKUP(B1,CLIENTS!$A$1:$AA$100,11,0)))),0)

 

[Weiji]

you can use named ranges to create nested drop downs via data validation

so like first drop down vegetables / fruits: second drop down depends on the input from the first.

really useful for user forms with large amounts of potential input combinations

you can also use formulas to highlghlight rows in conditional formatting to let the user know what to to enter,either sequentially or based if statement / other criteria.

 

[Raphael]

I've always enjoyed Excel as a program, but the most advanced thing I knew how to do was drop down menus and pivot tables. I recently learned how to do vlookup and IF formulas.

I want to continue to learn more advanced techniques for Excel. Any good website resources? Next I want to learn how to apply AND.

If, vlookups and pivots will get the job done 90% of the time lol. Learn as you go, just be of a mindset 'how can i make this easier', you can do everything simpler. After you are comfortable with formulas you can then jump into VBA. That is where the True magic happens and you become a wizard, but VBA is a Grand strategy game compared to excel's civilization.

 

[Strife In a Teacup]

If, vlookups and pivots will get the job done 90% of the time lol. Learn as you go, just be of a mindset 'how can i make this easier', you can do everything simpler. After you are comfortable with formulas you can then jump into VBA. That is where the True magic happens and you become a wizard, but VBA is a Grand strategy game compared to excel's civilization.

=IFERROR before vlookups is nice as well. with the ),"" after the vlookup to clear all the N/As and such, or whatever custom message you want if you don't want it blank.

 

[jackets with pockets]

Excel excels.

 

[DKehoe]

Just be careful if you become known as "the excel guy" in your office. I had that happen before and you end up with so many people coming to you for help.

 

[captainraincoat]

wait till u go down this rabbit hole
doom in excel

DOOM-like 3D engine made out of MS excel formulas

A DOOM-like 3D engine has been created only using Microsoft excel formulas.

www.tweaktown.com

 

[Maiden Voyage]

care to give an example on the INDEX and the naming tables usage?

If you name a table and use that name as the list input, the drop-down menu will automatically add any additional items as they are added to the table. Typically, people just enter a range and need to babysit the list input every time they add an item. Another thing I see is people doing more empty lines in the list range, creating a bunch of empty options for the drop-down menu.

 

[Weiji]

=IFERROR before vlookups is nice as well. with the ),"" after the vlookup to clear all the N/As and such, or whatever custom message you want if you don't want it blank.

To add to this comment, make a table of data, write formulas to new columns that manipulate the underlying data, and then pivot your table‘s new columns.

As your formula knowledge increases you’ll be able to create better and better pivots.

Of course your foolish bosses will think “it’s just a pivot table” but they won’t be able to understand or recreate it from the same dataset

 

[Strife In a Teacup]

=COUNTIF and =COUNTIFS are nice also. Combined those with templates/macros and you can make tables real quick.

I'm not as fancy as Weiji above, but I'm still learning (I like Excel a lot actually). Mostly just youtube/google my way to my patchwork form of whatever it is, but it works.

Edit: Also use =CONCATENATE combined with (TEXT inside to preserve dates, which is nice. Like =CONCATENATE(C35, (TEXT(A35, "mm/dd/yyyy"")), F35, G35 to combine it together and keep the date cell value kept as date format.

 

[EverydayBeast]

Microsoft's foundations are in programs like Excel.

 

[rob305]

Use INDEX(,MATCH(,,0))

it’s better then vlookup

also if statement based array functions are really useful for lookups when you need to use horizontal and vertical variables.

learn offset( for extra complexity

learn indirect l( so you can manipulate sub variables inside you formulas and make them co dependent

Index match is way slower than vlookups though. If youre doing thousands of them it takes forever versus vlookups will refresh in a matter of seconds. If you need multiple criteria for vlookups and you dont want to use index match you can just use hidden helper columns. Not elegant but its going to run way faster

 

[Weiji]

=COUNTIF and =COUNTIFS are nice also. Combined those with templates/macros and you can make tables real quick.

I'm not as fancy as Weiji above, but I'm still learning (I like Excel a lot actually). Mostly just youtube/google my way to my patchwork form of whatever it is, but it works.

How do you think I learned everything I know? No one taught me. Youtube / google is an amazingly underutilized resource.

anyone here use automated okta logins?

did you know you can make those yourself in excel with inspect element and a login url?

Enter a username and password in excel with auto login script in vba.

it’s a beautiful thing.

 

[Weiji]

Index match is way slower than vlookups though. If youre doing thousands of them it takes forever versus vlookups will refresh in a matter of seconds. If you need multiple criteria for vlookups and you dont want to use index match you can just use hidden helper columns. Not elegant but its going to run way faster

I’m pretty terrible with resource allocation I use a lot of questionably necessary array functions and then regret it two years later.

 

[Strife In a Teacup]

How do you think I learned everything I know? No one taught me. Youtube / google is an amazingly underutilized resource.

Yup, the guy who taught me learned the same way. So I kept learning the same way. Has worked out pretty well so far. Gotten me into learning some basic, although very helpful, VBA macros. As well as graphs/pivots/etc. So I just keep digging into it and playing with data and trying different things.

I think with Excel it's really important just to know the question to ask. Once you know that, then it's just getting the formula for that question and there you go. Then of course you have all the snazzy refinements and stuff to make it faster. Which at least for me is born out of things taking too long and me hating repetition so I look for ways to make it faster.

 

[ValiantTraveler]

I remember getting really good at Excel back when I used to work in a consulting company. Of course I had to learn it all by myself, no one around me had a strong grasp of Excel so Google was my only teacher. Being able to organize huge volumes of data (over 10,000 lines) and using formulas to extract insight from it was critical for one particular project. Some tasks were simply impossible to do if not automated.

So yeah, Excel is awesome.

 

[ResilientBanana]

I've always enjoyed Excel as a program, but the most advanced thing I knew how to do was drop down menus and pivot tables. I recently learned how to do vlookup and IF formulas.

I want to continue to learn more advanced techniques for Excel. Any good website resources? Next I want to learn how to apply AND.

concatenate is a godly command.

 

[HellforLeather]

 

[DeepEnigma]

Keep at it champ



More serious note, Excel Is Fun YouTube channel. Generally real world and very well organised content for what you want to learn. It's a pity the guy's voice is annoying AF but solid resource with learning playlists that make logical learning sense.

Sweet, he can come do my spreadsheets and workbooks for me.

 

[NecrosaroIII]

concatenate is a godly command.

I've only used it for superficial things. What do you use it for?

 

[aerts1js]

I love excel, acess, power bi, all that. Pretty much automated a large part of my work flow via acess.

 

[Maiden Voyage]

concatenate is a godly command.

I just use &.

 

[Airola]

The best thing in Excel by far:

 

[SurprisedPikachu]

It’s pretty good. Not great for any iterative process though. Sure VBA can get you most of the way there but it’s really slow and is better handled by other programs like R and MatLab.

I love me some MatLab. I don't fuck around with R nearly as much as I probably should, but it's not really relevant to anything that I do.

 

[godhandiscen]

Wait until you try AirTable

The platform to build next‒gen apps - Airtable

Airtable is a low‒code platform to build next‒gen apps. Move beyond rigid tools, operationalize your critical data, and reimagine workflows with AI.

airtable.com

 

[Majima Everywhere System]

Take the red pill and unlock that developer tab. You will become pretty good in bed and at work once you have Excel VBA emailing CSV files in the subject lines

Or use power bi to import and manipulate salesforce data. I had those little fucking spreadsheets running themselves off the computer in the corner and emailing the reports out/uploading

 

[Tesseract]

it's fucking misery, i use it because i have to

otherwise i'd write up my own index and kill myself

 

[TheMan]

Well made excel tables have saved me so much over the years it’s cray

 

[quickwhips]

Excel is cool but i really like Powershell.