You'll drown in the sea of green if you look for things to learn aimlessly.
look for things in your job you are spending most time on. always ask if there is a faster way to do it (it's usually yes). so ask it here in reddit. Learn about the solution we gave. you learn something specific you require in your job and that usually sticks
help out in excel forums. it's the fastest way i improve.
To echo /u/small_trunks, how I learned was via the Excelisfun playlists on youtube.
I'd start working through all of his Excel 2016 general series: https://www.youtube.com/watch?v=miUTG38k2mA
Of, if you use Excel 2013 at Work - start on his Excel 2013 general series, or his series on whatever version you use. If your work has Office 365, then I'd just do the Excel 2016 version.
I did this 30 minutes a day (every day of the week), and would say it took about 5 months to get 'really' good. Via learning, I'd think of "Oh I could use this at work", or alternatively while at work "Wait, I remember something about this..." and would have a place to start when googling.
And of course as everyone else has said, google things when you need them. I feel like that's kind of a given, though.
-----
Excel is complex and can't be "mastered" quickly. However, you can learn several simple things quickly that will make most think you're skilled.
Vlookup
Index(match
Sumif
Countif
Pivot Tables
I consider the above very basic, but most people I've met consider themselves experts for knowing these. They're not.
That said, I'm not an expert, I've been working with excel for 25 years and I'm still learning every single day.
Learn the above, then follow that down the rabbit hole and learn the other basic formulas.
Then learn more complex nested formulas and what the syntaxes mean. And why you're writing what you're writing. Don't just copy/paste from the internet because it works and you don't know why.
Then move onto power query (get and transform), power pivot and power BI.
I wouldn't bother with VBA, but that's my opinion.
-----
Try answer 10-20 questions on here every day.
make an example workbook that shows the solution.
If you are not fast enough, read the answers presented and understand how they came to their conclusions/solutions. Or ignore the answers and work it out anyway.
learn from the questions that experienced people ask (why do you want that?, what's the underlying problem you are trying to solve?, Why is your data this format?, What is the source of this data?, etc etc etc )
It's a fantastic learning exercise because the subject matter for virtually every problem presented is different - so you cannot rely on subject matter experience, even if you had it.
-----
I would start by trying to solve real problems. Not just, ‘what formula does this or that’ but rather full projects, start to finish.
So, find yourself a csv data dump, bring it into excel, and get to work!
Here are some common examples of real problems you’ll need to solve in no particular order.
Figure out how to group and filter, the data.
create a summary page for your data and goto town with vlookup, match, sumifs statements.
write some Vba to loop through the data and transform. Maybe write a vba form for data entry.
create pivot tables - to the point that you can do this without thinking. Pivot tables are your best friend in business!
do string manipulation on the cells - maybe combine all the fields into one address field, or split an address field into city, state, zip, etc. <- you’ll do a lot of this in the real world. Split names into first, middle, last, etc. auto create email addresses based on users first last and company names
do a bunch of date calculations and summaries (last 7, last 30, last month, last quarter, last year, etc) also, find some data (or make it) that has a date field which is easy for a human to read, but difficult to parse. Then figure out how to parse it. You will more than likely work with data from an existing business system (erp, mrp, accounting, etc) and chances are the data will need some manipulation - learning how to do that will be important!
Normalize the data so it could be loaded into a database if need be
take multiple related data sets and cleanly combine them, look for duplicates and remove them, put data in order by various fields, etc.
If you need more, let me know!
-----
It depends on what you want to use it for, excel is a tool not a discipline unto its self. That being said I would suggest you begin by understanding the relational model. It's an easy concept to understand and it makes working with mutliple data sets exponentially easier.
https://m.youtube.com/watch?v=QpdhBUYk7Kk&t=23s
-----
I would break down the "stages" of learning excel as -
Basic navigation - between sheets, from one cell to another, links and references. It helps you not waste time looking for things that are already available.
Shortcuts:-
• F5 or CTRL+G = Go to a particular cell.
• CTRL+ Page Up/Down = Navigate between sheets.
• SHIFT + F11 = Adds new sheet to your workbook.
• CTRL + F = To search for something specific in the sheet/book.
• CTRL + H = to replace something common across the sheet/book with something else.
• Click F4 when giving reference to one or more cells as required. It "fixes" the position and doesn't change based on what cell you copy the reference to.
• CTRL + ] = Opens link given in a cell.
2. Once you're done with learning basic navigation, try to learn some basic functions and formulae in excel.
Suggestions:-
• Pasting data in different formats: CTRL + ALT + V.
• SUM (ALT + = is the shortcut), IF statements, SUMIF, COUNT, COUNTA and COUNTIF.
• Filtering and sorting data - CTRL + SHIFT + L or ALT + A + C.
3. After this you can move to on better and tougher parts of excel. Here we have a bit advanced functions and formulae.
• AND OR and a combination of the two.
• VLOOKUPs, HLOOKUPs and XLOOKUPs.
• INDEX, MATCH and INDEX + MATCH.
• PIVOT tables.
• Goal seek, data validation, what-if analysis, formula auditing, etc.
4. Based on your job, you will be learn a bunch of formulae so I'm not covering those here.
5. Advanced stuff includes but is not limited to - Slicers, PIVOT QUERY, POWER QUERY, VBA, Solvers and Add-ins.
This is just the tip of the iceberg. There's so much more to a spreadsheet than just using it for your job. Excel has templates, formulae and functions.
Formatting a spreadsheet is one of my favorite things to do. Make your excel sheets in a way so anyone who is not an illiterate can understand the purpose of having things done that certain way.
Keep stuff tidy. Avoid a few things. And most importantly, understand that excel is only one of the many means to get stuff done. Don't be afraid of learning about other stuff too.
Cheers!
-----
0 Comments