The majority of people who are really good at Microsoft Excel will tell you that they gained their knowledge simply through experience. When I was a management consulting analyst, I’d frequently be given tasks that I thought were impossible with a spreadsheet; the process of coming up with an Excel solution was what really established the foundation of my Excel knowledge as well as my overall confidence in spreadsheets. The problem with this fact is that it is impossible for me to transfer those experiences to another person. Those that are new to Excel and want to learn really quickly face a steep learning curve.
The purpose of this post is to summarize all of my tips, tricks, and tutorials for Excel from a capability perspective. For example, if you learn INDEX MATCH, you’re really learning Database Theory, which represents the first section of this list. Few people who use Excel ever think about the actual capabilities they are developing, but I personally feel that this is the most effective way to learn Excel.
Books like John Walkenbach’s Excel Bible are comprehensive and really great from that perspective, but the organization of the book reads like a dictionary and it acts more as a reference guide than an actual lesson plan. My advice to anyone who is learning Excel is to think about your learning within a skills based context. Doing so will allow you to see where your gaps are and balance out your learning of the tool.
Below I’ve listed the capabilities that I feel are most important to being good at Excel. Under each capability are a set of tutorials (most of which, as of this writing, have not been created yet). As I build out this blog, I’ll update the list periodically to link to created tutorials and incorporate new content.
- Database Theory
- Visual Design
- Model Building
- Formula Writing
- Excel Analysis
- Error Checking
- Excel Shortcuts
- File Administration
- VBA
- Arrays
Database Theory
- How to Use VLOOKUP
- How to Use HLOOKUP
- How to Use INDEX MATCH
- How to Use VLOOKUP MATCH
- How to Use INDEX MATCH MATCH
- How to Use OFFSET MATCH MATCH
- How to Use VLOOKUP HLOOKUP
- Why INDEX MATCH is Better Than VLOOKUP
- How to Use Pivot Table
- How to Use Pivot Charts
- Primary Key Creation
- Troubleshooting your Lookup Formula
Visual Design
- Three Key Principles of Visual Design in Excel
- How to Make Your Excel Bar Chart Look Better
- How to Make Your Excel Line Chart Look Better
- Excel Visual Design Tricks
- How to Add Selective Highlighting to Your Excel Chart Background
- How to Use Excel Custom Number Formatting
- How to Paste Excel Chart Formatting
Model Building
- How to Build an Excel Model: Key Principles
- How to Build an Excel Model: Tab Structure
- How to Build an Excel Model: Step by Step
- MBA Excel Sample Model
- Business Case Template
- Linked Balance Sheet, Cash Flow, and Income Statement
- Pricing Model Template
- Homework Tracking Template
- Recruiting Template
- Project Management Template
Formula Writing
Excel Analysis
- Distribution Analysis
- Regression Analysis
- Optimization Analysis: How to Use Excel Solver
- Simulation Analysis: How to Run a Simulation in Excel
- Sensitivity Analysis
Error Checking
- How to Remove Duplicate Values in Excel
- How to look for Duplicates
- The Eight Things You Should Always Check Whenever You Receive an Excel File
- Top 3 Reasons VLOOKUP is Not Working
- Top Mistakes Made When Using INDEX MATCH
- Why You Should Be Cautious About Using Excel’s IFERROR Formula
Shortcuts
File Administration
- How to work on a file together
- How to Lock Cells
VBA
- How to Learn VBA for Excel
Arrays
- Most Useful Excel Arrays
my name is punit but i do not like sumit
Everything is very open with a really clear description of the challenges.
It was truly informative. Your website is extremely helpful.
Thanks for sharing!
Thanks a ton for this clear and Professional Tutorial on Index Match!!!
Your initiative is highly appreciated!!!
Regards,
R V Firoz
Thanks a lot .With Index match ,I save four hours.
Iam quite thrilled at the way you have simplified my understanding and application of excel formulae.
Assist if you can; How can i use information on my data base of workers say totalling 100 to create daily reports say on the absenteesm, resignations, sick off, material usage etc.
Regards
Bennet
I learn index matach formulas easily
Thanks
It makes sense that experience is the best way to gain your knowledge. My son has to work with excel at his new job, and he isn’t very familiar with it. Maybe I will find an excel training course he can take to help him out. I’m sure it will take some time to get used to it. Thanks for sharing!
Had a question – which one is the most useful?
I am using your template, Homework Tracker v2.0 .How may I change the color of the Output Heading tab in the Calendar View of Homework Tracker v2.0? I have two sections from the same work group and I would like them to be classed in the same colored tab, even though they are different headings.
Thanks,
Don
Thank you for the tutorials
these tutorials are EXCELlent
interesting ! Thanks for information !
Thanks for your good website and information !