__STYLES__

Nov 3, 2022

/

Microsoft Excel

How To Learn Excel: A Guide For Beginners

8 min read

Nov 3, 2022

/

Microsoft Excel

How To Learn Excel: A Guide For Beginners

8 min read

Nov 3, 2022

/

Microsoft Excel

How To Learn Excel: A Guide For Beginners

8 min read

Currently Reading

How To Learn Excel: A Guide For Beginners

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:

  1. Formulas & Functions

  2. Pivot Tables

  3. 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.

Excel Formula Builder


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

  1. Bar & Column Charts

  2. Histogram & Pareto Charts (Office 365/2016+)

  3. Line Charts & Trendlines

  4. Area Charts

  5. Pies, Donuts & Race Tracks

  6. Scatter Plots

  7. Bubble Charts

  8. Box & Whisker Charts (Office 365/2016+)

  9. Tree Maps & Sunburst Charts (Office 365/2016+)

  10. Waterfall Charts (Office 365/2016+)

  11. Funnel Charts (Office 365/2016+)

  12. Radar Charts

  13. Stock Charts

  14. Heat Maps

  15. Surface & Contour Charts

  16. Geo-Spatial Maps with Power Map

  17. Basic Combo Charts

  18. Sparklines

Learn how to customize your charts

  1. Experiment with chart elements and layouts

  2. Use color and style intentionally

  3. Play with your formatting options

  4. 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


Share this article with your friends

Last chance

Final Enrollment for Power BI & Python Immersive Programs

Explore how small-group, cohort learning immersive programs with direct instructor access, weekly live sessions, and collaborative environments can elevate your skills and launch your career.

LATEST FROM YOUTUBE

LATEST FROM INSTAGRAM

Last chance

Last chance

Final Enrollment for Power BI & Python Immersive Programs

Final Enrollment for Power BI & Python Immersive Programs

Explore how small-group, cohort learning immersive programs with direct instructor access, weekly live sessions, and collaborative environments can elevate your skills and launch your career.

Explore how small-group, cohort learning immersive programs with direct instructor access, weekly live sessions, and collaborative environments can elevate your skills and launch your career.

Free portfolio playbook

How to Build A Strong Data Analyst Project Portfolio

Learn the 5 steps that will make your project portfolio stand out so you can land your dream job!

You May Also Like