MCLIFTO8
Board Regular
- Joined
- Nov 13, 2022
- Messages
- 92
- Office Version
- 365
- Platform
- Windows
I'm teaching friends and relatives Excel. In the process I've needed to formulate a training plan that someone working full-time can implement.
This is what I wrote for personal use to help my friends and family. I'd like some constructive feedback to improve it's content.
Why you should learn how to use Excel
This as a roadmap / introduction to self managed learning for the busy professional more than it is a guide on learning Excel.
I'm assuming you want to make your job easier and if you interact with paperwork in any way, then learning Excel just happens to be the best general advice to do so.
The best way to learn Excel
there are things we know we know. We also know there are known unknowns; that is to say we know there are some things we do not know. But there are also unknown unknowns—the ones we don't know we don't know. - Rumsfeld (US SoD)
Rumsfeld butchered the English language a little but the premise remains the same. The hardest problem anyone will face is being unaware they even have a problem to begin with. When it comes to technical competence and later technical excellence in Excel the biggest challenge to overcome will be first familiarising yourself with all that is possible. Dare I say that's a feat none of us will ever accomplish but it's a journey we can all make rewarding progress on.
Why not just take a course?
The issue with courses is that they build a solid foundation of knowledge but take too much time out of your day to become useful.
The issue with blogs is that while they give highly specific examples of problems that can be relevant to you, they're not relevant unless you either know how to ask the right questions or stumble upon an article that you can adapt to your needs.
People are more than happy to sell you a method because it's a tangible thing you can neatly slot away, a hard skill to use. It can be monetised easily and when you run out they sell you more. A course is just an organised series of blog posts.
It's one thing to have methods, it's another to know when you're missing methods, how to aquire new methods and when to use them. Learn how to solve your own problems.
As far as I know, there isn't a framework on how to ask the right questions, what's possible without taking a course and where to go for help, I hope this quick read can help with that.
Start with a project
To internalise your knowledge and be motivated to start in the first place, I suggest you start with a specific need, a project to automate or make more efficient.
Ask the right questions
Then, ask yourself "How can this be done better"? At both a high "concepts" level and a low "nuts and bolts" level.
For example,
You want to import data from a file that changes content but not its name or location. Every week you might want to import this data and do something with it.
Someone, we're going to say Brian, copies over the data into a local Excel sheet weekly and references it with some formulas. Now how can we make it better?
At a low level you might ask "how do I improve the formatting of my spreadsheet to make it easier to read". That would lead you to merged columns, center over selection, formatting, conditional formatting and more.
At a high level you might ask "How do I import data to excel where the data needs to be cleaned up every time I import it?" That would lead you to Power Query.
Now you have a starting point. Either search for how to use these features yourself, or consult a forum for help. Ideally both, at the same time.
Engage the professionals
Never before have we had such a powerful network of highly motivated and skilled professionals ready to hold our hand and walk us through almost any problem we have.
Before you ask for help, note that a vague and broad question is a pain to deal with so try to be structured and specific with your questions, think about inputs and outputs. If you don't, people will just ignore your question. Sometimes breaking down a complex problem into smaller pieces and posting those can get an answer more easily than "do my job for me". Post a small example of the problem with fake data.
You will want to start posting questions at https://www.mrexcel.com/ or https://www.excelforum.com/
They will have time to answer somewhat vague questions, are usually friendly and conversational.
Stack overflow and super user are the best resources for SPECIFIC, usually advanced Excel questions. They will not appreciate "here's my spreadsheet, modify it to suit my needs please" but they are hands down, the largest collection of competent professionals in the entire world. If you can make your question specific to a particular problem they will be able to help.
Super User
https://stackoverflow.com/
Learn beyond the solution
Don't just USE their solution, understand the nuts and bolts of the solution and where each bit of code, or formula or feature or query comes from. Look up that feature separately and learn a little about it.
Example,
Someone wrote you an INDEX(MATCH()) formula. Go learn what INDEX does, go learn what MATCH does.
Someone got you to highlight something using conditional formatting. Go search up "conditional formatting excel" and learn what else the feature can do.
Become aware of the high level features and how they fit together.
If you want a simple list of things to learn, this is in my opinion a list of features roughly in order of complexity. For someone wanting to build up a foundation I would personally learn a little bit from each topic. This approach will equip you with the fundamentals of Excel's entire feature set so that you can best guide your learning on specific projects. However, each subject can essentially be learned in isolation.
Start reading literature, blogs, guides and documentation
The official Microsoft resource:
Microsoft 365 Training
We have a huge ecosystem of enthusiastic bloggers and articles from people just like you, excel enthusiasts:
There are plenty of excel blogs that put out articles on some new tip or trick, they're a great read with your morning coffee to get started on the day, give you inspiration, keep up with the latest developments (Excel adds new features every year) and can be read in 15 minutes or less. You have time to do this.
Exceljet Bookmark this page, great resource for formulas.
Excel VBA Introduction youtube playlist Great resource for learning VBA
Blog - Excel University
frequentlyusedformulas - excel
Excel Easy
Tutorials, videos, useful tips for Excel, Outlook and Google Sheets - Ablebits.com
Become aware of the high level features and how they fit together.
Once you get to this point you will have used Excel to make your job easier. Now you know that the more knowledge you gain, the more Excel can help you. So I recommend you pick up a course, skip to the section you find interesting and dive in.
If you want a simple list of things to learn, this is in my opinion a list of features roughly in order of complexity. For someone wanting to build up a foundation I would personally learn a little bit from each topic and gain a broad perspective which will help equip you with the right tool for the job. This approach will equip you with the fundamentals of Excel's entire feature set so that you can best guide your learning on specific projects. However, each subject can essentially be learned in isolation.
^ Friends, I'd specifically like feedback on this section right here.
This is what I wrote for personal use to help my friends and family. I'd like some constructive feedback to improve it's content.
Why you should learn how to use Excel
This as a roadmap / introduction to self managed learning for the busy professional more than it is a guide on learning Excel.
I'm assuming you want to make your job easier and if you interact with paperwork in any way, then learning Excel just happens to be the best general advice to do so.
The best way to learn Excel
there are things we know we know. We also know there are known unknowns; that is to say we know there are some things we do not know. But there are also unknown unknowns—the ones we don't know we don't know. - Rumsfeld (US SoD)
Rumsfeld butchered the English language a little but the premise remains the same. The hardest problem anyone will face is being unaware they even have a problem to begin with. When it comes to technical competence and later technical excellence in Excel the biggest challenge to overcome will be first familiarising yourself with all that is possible. Dare I say that's a feat none of us will ever accomplish but it's a journey we can all make rewarding progress on.
Why not just take a course?
The issue with courses is that they build a solid foundation of knowledge but take too much time out of your day to become useful.
The issue with blogs is that while they give highly specific examples of problems that can be relevant to you, they're not relevant unless you either know how to ask the right questions or stumble upon an article that you can adapt to your needs.
People are more than happy to sell you a method because it's a tangible thing you can neatly slot away, a hard skill to use. It can be monetised easily and when you run out they sell you more. A course is just an organised series of blog posts.
It's one thing to have methods, it's another to know when you're missing methods, how to aquire new methods and when to use them. Learn how to solve your own problems.
As far as I know, there isn't a framework on how to ask the right questions, what's possible without taking a course and where to go for help, I hope this quick read can help with that.
Start with a project
To internalise your knowledge and be motivated to start in the first place, I suggest you start with a specific need, a project to automate or make more efficient.
Ask the right questions
Then, ask yourself "How can this be done better"? At both a high "concepts" level and a low "nuts and bolts" level.
For example,
You want to import data from a file that changes content but not its name or location. Every week you might want to import this data and do something with it.
Someone, we're going to say Brian, copies over the data into a local Excel sheet weekly and references it with some formulas. Now how can we make it better?
At a low level you might ask "how do I improve the formatting of my spreadsheet to make it easier to read". That would lead you to merged columns, center over selection, formatting, conditional formatting and more.
At a high level you might ask "How do I import data to excel where the data needs to be cleaned up every time I import it?" That would lead you to Power Query.
Now you have a starting point. Either search for how to use these features yourself, or consult a forum for help. Ideally both, at the same time.
Engage the professionals
Never before have we had such a powerful network of highly motivated and skilled professionals ready to hold our hand and walk us through almost any problem we have.
Before you ask for help, note that a vague and broad question is a pain to deal with so try to be structured and specific with your questions, think about inputs and outputs. If you don't, people will just ignore your question. Sometimes breaking down a complex problem into smaller pieces and posting those can get an answer more easily than "do my job for me". Post a small example of the problem with fake data.
You will want to start posting questions at https://www.mrexcel.com/ or https://www.excelforum.com/
They will have time to answer somewhat vague questions, are usually friendly and conversational.
Stack overflow and super user are the best resources for SPECIFIC, usually advanced Excel questions. They will not appreciate "here's my spreadsheet, modify it to suit my needs please" but they are hands down, the largest collection of competent professionals in the entire world. If you can make your question specific to a particular problem they will be able to help.
Super User
https://stackoverflow.com/
Learn beyond the solution
Don't just USE their solution, understand the nuts and bolts of the solution and where each bit of code, or formula or feature or query comes from. Look up that feature separately and learn a little about it.
Example,
Someone wrote you an INDEX(MATCH()) formula. Go learn what INDEX does, go learn what MATCH does.
Someone got you to highlight something using conditional formatting. Go search up "conditional formatting excel" and learn what else the feature can do.
Become aware of the high level features and how they fit together.
If you want a simple list of things to learn, this is in my opinion a list of features roughly in order of complexity. For someone wanting to build up a foundation I would personally learn a little bit from each topic. This approach will equip you with the fundamentals of Excel's entire feature set so that you can best guide your learning on specific projects. However, each subject can essentially be learned in isolation.
- Understanding the interface, resizing cells, filters, sorting, formatting cells, find/find&replace, print settings, worksheet manipulation.
- Tables: Formatted tables, Conditional formatting, Data Validation
- Formulas: VLOOKUP, SUMIFS, INDEX, and MATCH
- Pivot Tables: field options, layout and design, and Slicers
- Researching best practices: sheet protection, hyperlink manipulation, date functions, text functions, and defined names
- Power Query and Power Pivot: managing the data model, defining relationships and writing measures
- VBA: using the macro recorder, writing macros from scratch, and understanding VBA and Excel's object model
Start reading literature, blogs, guides and documentation
The official Microsoft resource:
Microsoft 365 Training
We have a huge ecosystem of enthusiastic bloggers and articles from people just like you, excel enthusiasts:
There are plenty of excel blogs that put out articles on some new tip or trick, they're a great read with your morning coffee to get started on the day, give you inspiration, keep up with the latest developments (Excel adds new features every year) and can be read in 15 minutes or less. You have time to do this.
Exceljet Bookmark this page, great resource for formulas.
Excel VBA Introduction youtube playlist Great resource for learning VBA
Blog - Excel University
frequentlyusedformulas - excel
Excel Easy
Tutorials, videos, useful tips for Excel, Outlook and Google Sheets - Ablebits.com
Become aware of the high level features and how they fit together.
Once you get to this point you will have used Excel to make your job easier. Now you know that the more knowledge you gain, the more Excel can help you. So I recommend you pick up a course, skip to the section you find interesting and dive in.
If you want a simple list of things to learn, this is in my opinion a list of features roughly in order of complexity. For someone wanting to build up a foundation I would personally learn a little bit from each topic and gain a broad perspective which will help equip you with the right tool for the job. This approach will equip you with the fundamentals of Excel's entire feature set so that you can best guide your learning on specific projects. However, each subject can essentially be learned in isolation.
- The basics: undo/redo/cut/copy/paste, resizing cells, filters, sorting, find/find&replace, print settings, worksheet manipulation (new, hide, unhide, formatting), using formatting themes/format painter/general formatting skills.
- Formulas: VLOOKUP, SUMIFS, INDEX, and MATCH
- Tables: Formatted tables, Conditional formatting, Data Validation
- Pivot Tables: field options, layout and design, and Slicers
- Power Query and Power Pivot: managing the data model, defining relationships and writing measures
- VBA: using the macro recorder, writing macros from scratch, and understanding VBA and Excel's object model
- Researching best practices: sheet protection, hyperlink manipulation, date functions, text functions, and defined names
- Specialising into the unique problems your field of work has. Mine is Business Analytics so I combined the power of Excel and PowerBI with web development.
^ Friends, I'd specifically like feedback on this section right here.
