Excel MBA Day 5

Beginner to Advanced: Mega Excel Skills Resource

What follows is almost two hours worth of Excel skills lessons that will guide you from beginner level to advanced. The goal is to help you develop the skills to carry out analysis, have a foundation to create reports and finally to speed up your work. Scan through the lessons and start where you feel most appropriate, if you are not sure then start from the very beginning

Beginner Lessons

Introduction to basic Excel:

  • What can we enter into Excel? Is it all about numbers? There are TWO more key things we need to enter...
  • What are the main data types
  • Why people who think Excel is only about the 'numbers' are doing themselves a BIG disservice

Introduction to Excel formulas:

  • Functions: Excel's built in Formulas
  • Explaining how functions (formulas) work
  • This will lay the ground work for the days ahead!

The power of conditional formatting:

  •  Excel's built in conditional formatting options and which ones are useful
  • How to automatically create the infamous RAG (Red Amber Green) Status to make your own reports come to life
  • Combining Conditional Formatting with formulas for some powerful summarising skills

Why the SUBTOTAL formula is great for reports:

  • What is the SUBTOTAL function and why it is an amazing weapon in our arsenal for creating reports
  • Taking Autofilters to another level with SUBTOTAL (If you want a refresher lesson on Autofilters, scroll down)
  • Combining SUBTOTAL with non-numeric information to deepen our data analysis

Using Validation to control data:

  • The importance of placing restrictions on cells
  • Creating drop down lists
  • Customising error messages for added professionalism

What is VLOOKUP and why is it so important:

  • Why you should be maintaining your data vertically
  • The benefit of using Excel to look information up from a list
  • Quick but complete breakdown of one of the most important functions: VLOOKUP and how it can rapidly find the right information for you
  • How 'dragging' formulas can make your life much easier during analysis and reports

Intermediate Lessons

The importance of Autofilters:

  • The power and importance of eliminating data we do NOT need
  • How to put conditions on data to narrow down only the information you need within a list
  • How to see immediately see if data has been filtered (not doing this can be a costly mistake)

Rule building with the IF formula:

  • Treating information with rules (logic building) is a very powerful way to extract the most useful information
  • By using one of the most important functions: IF, we can narrow down individual data from massive lists and eliminate confusion
  • Logic building and the IF function is the first major step to automating your work which can dramatically speed up your work and make you more productive

Summarising Part 1 with COUNTIF:

  • Two quick ways to find unique values in a list
  • Using unique values with Countif to summarise for reports
  • Summarising more conveniently than Pivot Tables!

Summarising Part 2 with SUMIF:

  • How we can combine the power of summarising with the power of logic building in one Excel function: SUMIF
  • Making our summarising even more deeper through adding more logic criteria with SUMIFS
  • Using the 'wildcard' to do powerful partial filtering 

Introduction to Text based data

  • Why being able to deal with Text based data is a crucial skill for ALL professionals
  • Understanding why data gets messy and why it's so important to clean it up before working with it
  • Learn the specific combination of 3 Excel functions that will instantly clean up messy data

Advanced Lessons

Beyond VLOOKUPS for finding data:

  • Why the INDEX/MATCH combo is arguably the most powerful way to find the data you need
  • INDEX/MATCH allows us to think in a grid format which greatly implies data analysis
  • The Double Match technique which allows to easily deal with ambiguous data

Combining IF with VLOOKUP for advanced problem solving:

  • Using the VLOOKUP with '&' will allow you to do some advanced logic work
  • How combining the IF formulas with an advanced technique called 'Nesting' allows you to solve complex problems
  • My process for turning a complex problem into a problem solving formula

Why Pivot Tables are overrated:

  • What Pivot tables are, how they are constructed and why you should consider alternatives in many cases
  • Why the alternatives are more reliable and much better for speeding up the production of report and dashboards

Step by step Automation without VBA/Macros:

  • Using SUMIF, LARGE and VLOOKUP to set up an automatically updating calculation that can be used in reports (Set it and forget it automation)
  • How we can go even deep with automation and crunch 1 to 2 days work within a few seconds

​See you tomorrow for Day 6 - Why VBA/Macros are career game changers