Excel is amazing.

NecrosaroIII

NecrosaroIII

Banned
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.
 
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 said:
is it for numbers and shit
Click to expand...

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


giphy.gif
 
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.
 
Maiden Voyage said:
I just use google whenever I have a question.
Click to expand...

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.
 
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
 
Maiden Voyage said:
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.
Click to expand...
Weiji said:
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
Click to expand...

care to give an example on the INDEX and the naming tables usage?
 
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
 
Last edited:
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.
 
Excel is my life at work. I started learning Power BI recently, too. Very helpful for the visualization aspect of reporting data.
 
Last edited:
Weiji said:
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
Click to expand...

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)
 
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.
 
NecrosaroIII said:
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.
Click to expand...
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.
 
Last edited:
Raphael said:
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.
Click to expand...

=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.
 
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.
 
nbcjr said:
care to give an example on the INDEX and the naming tables usage?
Click to expand...

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.
 
Strife In a Teacup said:
=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.
Click to expand...

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 ;)
 
=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.
 
Last edited:
Weiji said:
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
Click to expand...
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
 
Strife In a Teacup said:
=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.
Click to expand...

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.
 
rob305 said:
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
Click to expand...

I'm pretty terrible with resource allocation I use a lot of questionably necessary array functions and then regret it two years later.
 
Weiji said:
How do you think I learned everything I know? No one taught me. Youtube / google is an amazingly underutilized resource.
Click to expand...

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.
 
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.
 
Last edited:
NecrosaroIII said:
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.
Click to expand...
concatenate is a godly command.
 
matt404au said:
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.
Click to expand...
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.
 
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
 
Last edited:
You must log in or register to reply here.

Similar threads

Isn't it amazing everything is still a WWW.
29
2K
Drew1440 replied
Litrpgs are pretty amazing
5
209
SScorpio replied
Murderbot is an excellent Apple TV+ show
18
514
L*][*N*K replied
Furiosa is amazing, it's a shame it probably killed the franchise
Opinion  2
67
4K
_Idea_of_Evil_ replied
Amazing remake of Raiders of the Lost Ark in the early 80’s
4
992
Evil Calvin replied
Top Bottom