Intermediate to Advanced Excel Skill Set Question

[claybwagner28]

Hello,

I am looking to extend my knowledge of excel specifically in the areas of data analysis. I would say I am an intermediate user. I can do vlookups, pivot tables, etc., but far from advanced.
What videos, courses, or books of Mr. Jelen’s do you recommend? Another route, what formulas should I master? I think I get overwhelmed trying to master 50+ formulas in different categories. Espceially when I need to combine formulas to get the results I need.

I want to take the next step in my career and I think Excel mastery will set me apart.

Thanks in advance for any guidance!

Clay

 

[Muhammad_Usman]

It's great that you're looking to expand your Excel skills for data analysis! There are several resources and approaches you can consider to take your Excel proficiency to the next level:

1. **Excel Books:**
- **"Excel 2019 Bible" by Michael Alexander and Richard Kusleika:** This is an extensive resource that covers a wide range of Excel topics and is suitable for intermediate users looking to become advanced.
- **"Excel 2019 Power Programming with VBA" by Michael Alexander and Richard Kusleika:** If you want to learn about Excel automation and VBA, this book is an excellent choice.

2. **Online Courses:**
- **LinkedIn Learning (formerly Lynda.com):** They offer numerous courses on Excel for various skill levels. You can explore topics like data analysis, advanced formulas, and Excel VBA programming.
- **Coursera and edX:** These platforms offer courses on data analysis with Excel, often in partnership with top universities.

3. **Excel Functions:**
Focus on mastering some key functions and formula combinations used in data analysis, such as:
- **VLOOKUP and HLOOKUP:** Essential for data retrieval.
- **INDEX and MATCH:** Powerful for more advanced data lookup.
- **SUMIF, SUMIFS, COUNTIF, COUNTIFS:** Used for data summarization.
- **Pivot Tables:** Learn to create and customize pivot tables for data analysis.
- **Power Query (Get & Transform Data):** For data import, transformation, and cleaning.
- **IF, IFERROR, AND, OR:** Basic logical and conditional functions.
- **SUMPRODUCT:** Useful for performing calculations on arrays.
- **DATE, TEXT, and TIME functions:** For handling date and time data.
- **ARRAY formulas:** Essential for complex calculations.

4. **Advanced Tools:**
- **Power Pivot and DAX:** If you're working with large datasets, Power Pivot and Data Analysis Expressions (DAX) are incredibly valuable.
- **Solver and Goal Seek:** These are useful for optimization and sensitivity analysis.
- **Scenario Manager:** Great for managing multiple scenarios within a single model.

5. **Excel Challenges:**
Consider taking part in Excel challenges and competitions on platforms like Kaggle. They provide practical experience and help reinforce your skills.

6. **Practice, Practice, Practice:**
The more you use Excel for real-world tasks, the better you'll become. Apply your knowledge to actual data analysis projects.

7. **Advanced Excel Add-Ins:**
Learn how to use add-ins like Power Query and Power BI for more complex data analysis and visualization tasks.

Remember, the key to mastering Excel is consistent practice and applying what you learn to real-world scenarios. Start with one area you want to improve and gradually build your skills from there. It's also helpful to set specific goals for your Excel proficiency and work towards them. Good luck on your Excel mastery journey!

 

[claybwagner28]

It's great that you're looking to expand your Excel skills for data analysis! There are several resources and approaches you can consider to take your Excel proficiency to the next level:

1. **Excel Books:**
- **"Excel 2019 Bible" by Michael Alexander and Richard Kusleika:** This is an extensive resource that covers a wide range of Excel topics and is suitable for intermediate users looking to become advanced.
- **"Excel 2019 Power Programming with VBA" by Michael Alexander and Richard Kusleika:** If you want to learn about Excel automation and VBA, this book is an excellent choice.

2. **Online Courses:**
- **LinkedIn Learning (formerly Lynda.com):** They offer numerous courses on Excel for various skill levels. You can explore topics like data analysis, advanced formulas, and Excel VBA programming.
- **Coursera and edX:** These platforms offer courses on data analysis with Excel, often in partnership with top universities.

3. **Excel Functions:**
Focus on mastering some key functions and formula combinations used in data analysis, such as:
- **VLOOKUP and HLOOKUP:** Essential for data retrieval.
- **INDEX and MATCH:** Powerful for more advanced data lookup.
- **SUMIF, SUMIFS, COUNTIF, COUNTIFS:** Used for data summarization.
- **Pivot Tables:** Learn to create and customize pivot tables for data analysis.
- **Power Query (Get & Transform Data):** For data import, transformation, and cleaning.
- **IF, IFERROR, AND, OR:** Basic logical and conditional functions.
- **SUMPRODUCT:** Useful for performing calculations on arrays.
- **DATE, TEXT, and TIME functions:** For handling date and time data.
- **ARRAY formulas:** Essential for complex calculations.

4. **Advanced Tools:**
- **Power Pivot and DAX:** If you're working with large datasets, Power Pivot and Data Analysis Expressions (DAX) are incredibly valuable.
- **Solver and Goal Seek:** These are useful for optimization and sensitivity analysis.
- **Scenario Manager:** Great for managing multiple scenarios within a single model.

5. **Excel Challenges:**
Consider taking part in Excel challenges and competitions on platforms like Kaggle. They provide practical experience and help reinforce your skills.

6. **Practice, Practice, Practice:**
The more you use Excel for real-world tasks, the better you'll become. Apply your knowledge to actual data analysis projects.

7. **Advanced Excel Add-Ins:**
Learn how to use add-ins like Power Query and Power BI for more complex data analysis and visualization tasks.

Remember, the key to mastering Excel is consistent practice and applying what you learn to real-world scenarios. Start with one area you want to improve and gradually build your skills from there. It's also helpful to set specific goals for your Excel proficiency and work towards them. Good luck on your Excel mastery journey!

Wow thank you!

 

[DanteAmor]

Another route, what formulas should I master? I think I get overwhelmed trying to master 50+ formulas in different categories. Espceially when I need to combine formulas to get the results I need.

I want to take the next step in my career and I think Excel mastery will set me apart.

One of the best places to increase your excel level, I think, is to try to answer excel questions on this forum or any excel forum.

In the forum you will find questions from people who have a real case, trying to answer will make you investigate and learn new formulas. And additionally, if someone else responds, there you will have another chance to learn how someone else solved the problem.

Just try it...

And maybe the next level is learning how to create macros with VBA.


Regards
Dante Amor

 

[etaf]

i have been using advanced excel across corporations for years, and when i retired i started answering questions on the forums , i would also say the same

In the forum you will find questions from people who have a real case, trying to answer will make you investigate and learn new formulas. And additionally, if someone else responds, there you will have another chance to learn how someone else solved the problem.

And i have learnt so much from the forums, because of exactly that - investigate real solutions , and see how other more knowledgeable members answer , and alternative are sometimes much easier ways to answer , and using new functions , a lot has changed since the 2010/13 version i was using , before i retired in 2016

I did use a lot of VBA in the 90's but not after that and so i dont look at VBA , but its certainly something to consider and use and power query

A lot of functions have been added to 365 version , which are very useful and not found in other versions
so rather than the 20-19 version, i would get the 365 version - note functions are added with updates to 365

I started out in 80's with the QUE book series and used those for reference - no internet then.
so the
- **"Excel 365 Bible" by Michael Alexander and Richard Kusleika:**
maybe as good - dont know

 

[horseguards]

Excel doesn't exist in a vacuum; it's not an end in itself. You have to put something (useful) in, to get something (useful) out - garbage in, garbage out, etc.

Depending on your current knowledge, may I suggest enhancing your maths skills, eg linear algebra, calculus, statistics. That is where you would have a real advantage, certainly in data analysis. Remember all the hype a few years ago regarding maths Ph.D's? It's still true.

Design simple projects such a budget analysis for the household. You will learn by doing.

Excel is not difficult. I suspect the majority of users are self-taught.