Look up how to do whatever you want to do, whenever it's something more complicated than you already know how to, then remember it for next time. Repeat.
I've been an excel consultant for many many years, and have done everything from training to designing systems for mass use, to flaggin failing trades to investment banks to working out what the weighting formula should be for stock market indices in excel. I will now dispense my wisdom in a boiled down form. If you want my actual method for learning the answer is practice, and having a goal I needed to reach.
Good excel can be summarised into 2 categories, formula, presentation and a third bullshit category of executive fads (this is litterally the trick they saw last week it is usuall not as useful as they think but you will have to know it, otherwise they won't give you the job)
I'll do formula first if you are interested I can do more later.
I am going to assume you are a complete novice for this explanation and work my way up.
1) formula are just maths if you write a '=' in a cell and then write a sum it will out put the answer
so '= 2+2' the cell will say 4
2) Cells can be referenced, as part of the maths for a formula so if we wanted a total of value of cells a1 and b1 together in c1 we would write in it '=a1+b1'
3)other formula can be referenced that do more sophisticated tasks you can check what those are by clicking the 'fx' button next to the input bar at the top of the screen.
4) Formulas all follow a syntax I shall now explain as I think it will help with the next part.
= tells excel a formula is coming, also compares in if statement
+ - / * these are basic maths symbols if you don't know what those mean I can't help you
" is used as brackets to denote actual text so ="cock" would display cock
( ) is used for nesting, this either means in the normal maths sense of priorities this calculation, or declaring what part a formula relates to.
: denotes a reference range so a1:b2 would mean cells a1,b1, a2, b2
& is used to connect bits of text so ="co"&"ck" would display cock
, is used as a divider for different sections of a formula in English versions of excel you'll see what that means in the next section.
< > less than, greater than used for comparison for if statements
$ This is used to make something an absolute reference i.e. $a$1 this is useful if you want to expand the range of a calculation later
{ } used for complex formulas that break all the time that do undocumented behaviour, you will probably never use them.
5) The If statement. If you take no other information away from this, I want you to understand this is the most important formula for all of excel, probably all of programming. I would say understanding this will get you your next job, but most people are fuckcunts who don't applicate it and its power, but every part of artificial intelligence is built off of it.
It is basically breaks down thus
=if(statements of comparision,output if statement is true, output ifstatement is false)
so =if(1=2,"maths is wrong","of course it doesn't you knobhead") would output "of course it doesn't you knobhead"
As I said earlier you can change that = for a < or >
And you could change those outputs to be a cell reference, or even
6 Nesting
You can put formula in other formula
So we wanted either the total range or at least 1 (maybe that is the absolute minimum for a sum we could have
=if(sum(a1:a3)<1),1,sum(a1:3)
Therefore the answer outputted could never be less than 1
There is a lot more and I'll happily explain it later if you are interested but I've probably explained more there than most people know.
>>27499 >{ } used for complex formulas that break all the time that do undocumented behaviour, you will probably never use them.
Bonus exotic feature points: You can't actually type them but have to press a certain key combination and they get added, and you have to remember to remove them and repeat the key combo every time you edit those formulae.
>>27499 Or just learn basic programming and use VBA. At my job a couple of years ago I inherited some absolutely horrendous spreadsheets using multi-layer VLOOKUPs and whatnot.
>>27499 > The If statement. If you take no other information away from this, I want you to understand this is the most important formula for all of excel, probably all of programming. I would say understanding this will get you your next job, but most people are fuckcunts who don't applicate it and its power, but every part of artificial intelligence is built off of it.
Someone please tell me this whole post is a 4chan /g/ copypasta. Please.
formula is basic programing, once someone has got the hang of nested if statements they are well on the way to becoming one.
I assume the kind of person who asks how to get better at excel wouldn't find 'just learn programming' a useful answer.
>VLOOKUPs
Man with a hammer as they say, that was going to go under my third section about executive fads (a nested INDEX and MATCH is simply better in nearly every application but they wouldn't know what the fuck that is), the other one main one is of course Pivot tables.
Using ISBLANK() inside IF() makes you look like a fucking wizard when you update a couple of cells and the whole row fills out. Add conditional formatting to that and the whole thing looks like a just add water data favela. People will either start coming to you for everything, or noticably cower.
>>27500 Back in the dark times before SUMIFS I figured out a cool way to use this feature. Rather than have a column multiplying units and unit costs and then summing that - I could write {=SUM(A1:A20*B1:B20)} and then that would multiply A1 by B1, A2 by B2 etc and then add the whole lot up in just one cell. It blew my colleagues minds to the point that they asked me to remove the formula because it was "wrong".
>>27499 The key combo >>27500 referenced is control+shift+enter.
Other lessons - be aware that if you format a cell as text then you can't enter a formula in it, or rather you can but it will not work like a formula. You'll have to change the format to something else but to get that formula working all you need to do is select the cell and hit F2 and then enter.
Also be aware that Excel considers numbers and numbers stored as text as different things - you can only do maths on the former and lookups can fail because of it. Sometimes this is useful such as when storing phone numbers, in which case you can force Excel to treat it as text by beginning the cell with a single quote mark ' .
Final thing is if you are working with complicated formulas with lots of conditions then you should learn how to used named ranges. As others have said, Excel is basically programming and one of the best practices of coding is having self documenting code. It could turn this =IF(D12,A1*B1,C1) in to this =IF(isGoods,UnitCost*Units,ServiceCost)
>>27512 >People will either start coming to you for everything, or noticably cower.
I'm the office whizz kid on Excel for being able to use SUM, being able to use fill handle, being able to alter the width of several columns at the same time and knowing about how to use $.
My department is predominantly people in their late twenties and early thirties.
>>27512 Showing the middle aged people at my old work that they can just use Ctrl+D to copy the cell above, I swear they were about to dip me in the paint wash tank to check if I was a witch.
I'd just like to say that as someone who spent two years of his miserable life staring at Excel through the lens of IDA Pro it's quite amusing to see a thread full of people who know how to use things like pivot tables to do the things they're actually intended to do rather than to cause an invalid memory access. Carry on.
I've done that before for that number, it was really more VBA work than anything, with pure excel people usually know enough to think they are using excel well, but don't know enough to know that their current models are crap, and when people do know enough to know their models are crap they usually fix it themselves, so it is harder market to work in than you would think.
