Excel for Data Analyst Notes
Introduction
Purpose of this Course
What do I use Excel for
Excel for Mac vs PC
My Opinion on VBA
What about Google Sheets?
Wait but what is Excel really?
Basics
Hyperbasics
Layout
File Formats
Excel Data Types
General
Number
Currency
Accounting
Date
Time
Text
Special
Custom
Functions
Aggregation Functions
Lookups
INDEX MATCH... MATCH
IF
String Functions
Formatting
Conditional Formatting
Data Manipulation
Excel for Data Analyst Notes 1
, Tables
Pivoting
Excel Goal Seek
Excel Solver
Macros
Why do I believe Excel should only be learned/used in limited quantities
Introduction
Purpose of this Course
Excel is a Swiss Army Knife, it can do almost everything but easily gets overpowered when working with
large or complicated amounts of data. Its for this versatility that Excel is a key skill to always have handy as
a Data Analyst. This course is designed to help prospective and current Data Analysts learn the level of
Excel that they’ll need in order to be successful in their jobs. It would be impossible to design a short
course that includes everything that every job in Analytics needs to know, the content here is based on my
experience and that of some analysts I know.
If you work in Finance then the level of Excel skills necessary will be beyond the scope of this course.
What do I use Excel for
I generally use Excel for data cleaning and ad-hoc reporting. It’s also an easy tool to share analyses with
stakeholders of all levels of technical competency.
I have also used it as an initial project planning tool.
Personal financial planning tool
Excel for Mac vs PC
Excel is definitively a better product on PCs than on Macs. For the tasks that most data analysts will need
to undergo, Excel for Mac will work just fine. This course can be completed in either system. The below
article outlines some of the key differences between Excel for Mac and Excel for PC.
Excel for Mac vs Excel for Windows - Pros & Cons
Excel for Mac vs Excel for Windows - Pros & Cons (2022) Written by co-founder Kasper
Langmann, Microsoft Office Specialist. Microsoft Excel has been working great for both
Windows and Mac. Over the years, Microsoft has updated Excel for Mac so it
https://spreadsheeto.com/mac-vs-windows/
To summarize:
Pivot Tables exist on the Mac version of Excel but not Pivot Charts.
PowerPivot doesn’t exist in Excel for Mac (I usually would just default to Python for most of PowerPivot’s
functionality anyways)
VBA editing doesn’t work as well on Excel for Mac
Excel for Data Analyst Notes 2
, My Opinion on VBA
VBA is a great tool to automate various operations in Excel and help speed up analyses. If you’re in a job
that explicitly requires it, then learning it might be useful but otherwise I argue that most data analysts won’t
see a major benefit to their careers from learning VBA. You’re better off learning something like Python
which will help you take on more advanced projects in your career. Check out my free Python course here:
Python for Data Analysts and Data Scientists
What YOU need to know to get started using Python to Analyze data. If you have any
questions please feel free to comment below or drop me a message. Download...
https://www.youtube.com/watch?v=sZDgJKI8DAM
What about Google Sheets?
Google Sheets offer a lot of the functionality of Microsoft Excel and most of what we go over in this course
will apply to Google Sheets as well. Being an internet first tool, Google Sheets have a lot of functionality
that allows it to interact with the internet natively and interact with Google Cloud services.
Wait but what is Excel really?
Basically the only reason to buy Office 365 these days.
Basics
Hyperbasics
Layout
If you’re using the standard Excel format then the file you open will be called a “Workbook” with individual
“Worksheets” being what you typically interact with. In the below screenshot:
Red - The Ribbon, this is where all of your core Excel functionality lives
Brown - This is an individual cell, it’s where your individual records of data exist
Orange - The Excel formula bar, this where you input data into individual cells and edit any formulas
that you may want to write
Blue - The sheet, this is where you input and edit your data
Pink - This is a tab that denotes which sheet you’re currently on
Excel for Data Analyst Notes 3
Introduction
Purpose of this Course
What do I use Excel for
Excel for Mac vs PC
My Opinion on VBA
What about Google Sheets?
Wait but what is Excel really?
Basics
Hyperbasics
Layout
File Formats
Excel Data Types
General
Number
Currency
Accounting
Date
Time
Text
Special
Custom
Functions
Aggregation Functions
Lookups
INDEX MATCH... MATCH
IF
String Functions
Formatting
Conditional Formatting
Data Manipulation
Excel for Data Analyst Notes 1
, Tables
Pivoting
Excel Goal Seek
Excel Solver
Macros
Why do I believe Excel should only be learned/used in limited quantities
Introduction
Purpose of this Course
Excel is a Swiss Army Knife, it can do almost everything but easily gets overpowered when working with
large or complicated amounts of data. Its for this versatility that Excel is a key skill to always have handy as
a Data Analyst. This course is designed to help prospective and current Data Analysts learn the level of
Excel that they’ll need in order to be successful in their jobs. It would be impossible to design a short
course that includes everything that every job in Analytics needs to know, the content here is based on my
experience and that of some analysts I know.
If you work in Finance then the level of Excel skills necessary will be beyond the scope of this course.
What do I use Excel for
I generally use Excel for data cleaning and ad-hoc reporting. It’s also an easy tool to share analyses with
stakeholders of all levels of technical competency.
I have also used it as an initial project planning tool.
Personal financial planning tool
Excel for Mac vs PC
Excel is definitively a better product on PCs than on Macs. For the tasks that most data analysts will need
to undergo, Excel for Mac will work just fine. This course can be completed in either system. The below
article outlines some of the key differences between Excel for Mac and Excel for PC.
Excel for Mac vs Excel for Windows - Pros & Cons
Excel for Mac vs Excel for Windows - Pros & Cons (2022) Written by co-founder Kasper
Langmann, Microsoft Office Specialist. Microsoft Excel has been working great for both
Windows and Mac. Over the years, Microsoft has updated Excel for Mac so it
https://spreadsheeto.com/mac-vs-windows/
To summarize:
Pivot Tables exist on the Mac version of Excel but not Pivot Charts.
PowerPivot doesn’t exist in Excel for Mac (I usually would just default to Python for most of PowerPivot’s
functionality anyways)
VBA editing doesn’t work as well on Excel for Mac
Excel for Data Analyst Notes 2
, My Opinion on VBA
VBA is a great tool to automate various operations in Excel and help speed up analyses. If you’re in a job
that explicitly requires it, then learning it might be useful but otherwise I argue that most data analysts won’t
see a major benefit to their careers from learning VBA. You’re better off learning something like Python
which will help you take on more advanced projects in your career. Check out my free Python course here:
Python for Data Analysts and Data Scientists
What YOU need to know to get started using Python to Analyze data. If you have any
questions please feel free to comment below or drop me a message. Download...
https://www.youtube.com/watch?v=sZDgJKI8DAM
What about Google Sheets?
Google Sheets offer a lot of the functionality of Microsoft Excel and most of what we go over in this course
will apply to Google Sheets as well. Being an internet first tool, Google Sheets have a lot of functionality
that allows it to interact with the internet natively and interact with Google Cloud services.
Wait but what is Excel really?
Basically the only reason to buy Office 365 these days.
Basics
Hyperbasics
Layout
If you’re using the standard Excel format then the file you open will be called a “Workbook” with individual
“Worksheets” being what you typically interact with. In the below screenshot:
Red - The Ribbon, this is where all of your core Excel functionality lives
Brown - This is an individual cell, it’s where your individual records of data exist
Orange - The Excel formula bar, this where you input data into individual cells and edit any formulas
that you may want to write
Blue - The sheet, this is where you input and edit your data
Pink - This is a tab that denotes which sheet you’re currently on
Excel for Data Analyst Notes 3
