If you're considering building a career in data, you NEED to know how to use Excel.
You'll find Excel in almost every business in the world, and to this day it remains one of the most versatile and widely used data analysis tools on the planet.
For those of you who want to make sure your Excel game is up to par, we've got a detailed roadmap that will help, coming up in this post.
It's not meant to cover everything Excel can do (the list is almost endless) but instead to give you a rock solid foundational set of skills that you can employ immediately on the job.
When folks come to us looking to develop Excel skills, we'll typically start them off by focusing on three areas:
Formulas & Functions
Pivot Tables
Data Visualization w/ Charts & Graphs
If you can develop yourself in these three areas, it opens up a world of analytical possibilities with Excel.
Let's dive into your Excel roadmap
Step 1. Learn Excel Formulas And Functions
Why start with Formulas & Functions?
There are a number of good reasons to use this as a jumping off point.
First, writing Excel formulas lets you develop your logic skills, and lets you manipulate data quickly to get moving and build momentum. You can experiment with different functions, make tweaks to your formulas, and see the outcome of your changes in real time.
Side note: this is one of the main reasons we always push folks to learn Excel first, before any other tool. It's extremely approachable for new Analysts.
Another reason to start with Excel formulas is the wide variety of problems you can tackle. You can use conditional & logical operators, statistical functions, lookup and reference functions, text functions, date and time functions, and so much more! Excel's abilities here just go on and on.
Instead of overwhelming you with every formula under the sun, here are 20 formulas we recommend you tackle first to build your foundation:
Logical Functions
1) IF
2) AND / OR
3) NOT
4) IFERROR
Statistical Functions
5) COUNTIFS
6) SUMIFS
7) AVERAGEIFS
8) MAXIFS / MINIFS
9) RAND / RANDBETWEEN
Lookup & Reference Functions
10) VLOOKUP / HLOOKUP / XLOOKUP
11) INDEX
12) MATCH
13) INDIRECT
Text Functions
14) LEFT / MID / RIGHT
15) LEN
16) TRIM
Date & Time Functions
17) YEAR / MONTH / DAY
18) TODAY / NOW
19) WEEKDAY
20) EOMONTH
Pro Tip: one of the best ways to learn about these and troubleshoot syntax errors is within Excel itself, by using the Formula Builder (shown below).
You can search functions, and see a description of their purpose and the inputs they require, right there in Excel.
Once you have a good grasp on those 20 basic functions, try exploring Excel's new Dynamic Array functions like FILTER, SORT, SEQUENCE and UNIQUE. These will take your formula game to an entirely new level!
No excuses, just get practicing! Get your hands dirty with formulas & functions, then you'll be ready move on to the next foundational skill.
Don't have any good data sets to practice with? Sure you do! Grab one of hundreds of free sample data sets available from one of these great sources:
The first one (from us) doesn't even require an account. It's perfect for beginners who just want something to practice with and appreciate getting some direction in the form of analysis prompts.
The other two are HUGE, and are ideal if you need a very specific data set. They do require you sign up for an account, but the accounts are free and get you access to the downloadable data sets.
Now you're all out of excuses. Just get practicing!
This is definitely something you can do on your own. All you need is access to Excel.
If you'd prefer someone walking you through the formulas step by step, we got a course on Excel Formulas that covers over 75 of the most used formulas and functions.
Plus, if you're already a member of our Unlimited Access subscriptions, this course is included in your membership.
Once you're feeling good here, it's time to move onto one of my personal favorites, Pivot Tables!
Step 2. Learn Excel PivotTables
Now you're ready to dive into Pivot Tables which are one of the quickest and most effective ways to perform exploratory analysis on a data set.
Pivot tables let you slice and dice data into groups, and summarize key metrics for those groups to quickly find insights that will help you understand the story your data wants to tell.
Here's a checklist you can use to get started:
PivotTable 101
1) Understand how your source data needs to be structured
2) Insert your first PivotTable
3) Start navigating the field list
4) Learn about Analyze and Design options
5) Be able to copy, clear, refresh, and update your Pivots
PivotTable Formatting
6) Understand how to format numbers in your Pivot Tables
7) Play with table layouts and styles
8) Customize your headers and labels for readability
9) Leverage the power of conditional formatting
10) PRO TIP: Use data bars with invisible text
Sorting, Filtering & Grouping
11) Familiarize yourself with the various sorting options
12) Use label filters
13) Use value filters
14) Enable multiple filters simultaneously
15) Group your data
Calculated Values & Calculated Fields
16) Use 'Summarize Values By' and play with your options
17) Play with 'Show Values As' (% of Column/Row, Different From, Running Total, etc)
18) Insert a calculated field
19) Understand pros and cons of calculated fields vs doing calculations in your source data
20) Answer 3 questions from a Data Playground data set using Pivot Tables
Bonus: Learn About Pivot Charts
21) Understand that Pivot Charts are joined at the hip with their underlying Pivot Table
22) Create your first Pivot Chart, a simple column chart
23) Create a pie chart or a donut chart
24) Create a clustered bar chart
25) Learn how to prevent charts from resizing when cell sizes change
Of course we're not going deep into any of these concepts here in the post. This is meant to give you a list to work through, but we aren't explaining any of these concepts in detail here.
For those of you who are tackling this on your own, Google will be your best friend. Search 'Excel' and then the concept you're looking into.
This is the first result for the search query 'Excel Insert Pivot Table'. You'll often see Microsoft's support website rank highly. They've got a lot of content there that's freely available.
For those of you who do have Unlimited Access memberships with Maven, you should definitely check our Chris' Excel Pivot Tables course.
If you need a data set to play with, remember these three options:
Okay, if you've made it this far, you're ready to learn how to become a data storyteller using Charts and Graphs!
Step 3. Learn Data Viz w/ Charts & Graphs
Before we get into specific chart types, let's start by building your foundation in how to THINK about data visualization.
We've got two detailed resources already built to help you tackle this:
Don't move on with this list until you've checked out the guides above. Before you start learning the tools here, you need to understand how an Analyst thinks about data viz. Then you'll be ready to dive deeper and get your hands dirty.
Understand available chart types and use cases
Bar & Column Charts
Histogram & Pareto Charts (Office 365/2016+)
Line Charts & Trendlines
Area Charts
Pies, Donuts & Race Tracks
Scatter Plots
Bubble Charts
Box & Whisker Charts (Office 365/2016+)
Tree Maps & Sunburst Charts (Office 365/2016+)
Waterfall Charts (Office 365/2016+)
Funnel Charts (Office 365/2016+)
Radar Charts
Stock Charts
Heat Maps
Surface & Contour Charts
Geo-Spatial Maps with Power Map
Basic Combo Charts
Sparklines
Learn how to customize your charts
Experiment with chart elements and layouts
Use color and style intentionally
Play with your formatting options
Understnad how and when to use a secondary axis
Again, this is something you can absolutely work through yourself, without needing Maven or anyone else to walk you through it. Play around inside Excel with a sample data set, and let Google be your friend when you get stuck.
For those of you who are on a Maven Analytics Unlimited Access plan, we've got a great Excel Charts & Graphs course that you can turn to for a guided walkthrough of all of this.
And if you're really feeling ambitious and wanting to really take your data viz game to the next level, check out my course: Advanced Excel Dashboard Design
Again, this one (like all of our courses) is included in our membership, so if you're onboard already, there's no reason not to check it out.
It's extremely hands on, and we'll walk through the critical dashboard design concepts using a number of unique real world business problems and scenarios. This one is my personal favorite course.
Wrapping up
Alright, if you made it this far, good for you!
I know this probably sounds like a lot, but you don't need to tackle it in one day. Just focus on making progress and working down the list.
Anyone can learn this stuff. You've just got to set your mind to it.
Hope this will give you a nudge and a solid roadmap to work off of.
Happy learning!
- Enrique
