Complete Excel Mastery: Microsoft Excel Beginner to Advanced
- 13.5 hours on-demand video
- 5 articles
- 30 downloadable resources
- Full lifetime access
- Access on mobile and TV
- Certificate of Completion
Get your team access to 4,000+ top Udemy courses anytime, anywhere.Try Udemy for Business
- Feel confident using Excel in the workplace.
- Be able to create and navigate Excel spreadsheets.
- Be able to create tables, charts, pivot tables and more.
- Use all of the most common formulas and functions.
- Use keyboard shortcuts and productivity hacks to get more done faster.
- While I can't make promises, my goal is to help you get the raise, promotion, or new job that you deserve.
- You should have basic computer skills.
- You should have a PC (you can get by with a Mac, but this course is not specifically designed for Mac users)
Welcome to my BIGGEST, most complete, Microsoft Excel course ever!
Excel is one of the most important skills in today's job market. Having a solid working knowledge of Excel can improve your job performance, help you qualify for raises and promotions, or even help you land that new job that you deserve.
If you need to know Excel, you're in the right place.
This course walks you through Excel from the very basics of opening Excel all the way to advanced Excel skills used by the pros, like pivot tables and macros.
My goal with this course is to give you the skills you need to get that raise, promotion, or new job you deserve.
This course was created using Excel 2016, but it is good for versions 365, 2019, 2013 and 2010, as well. It was created for use with a PC (personal computer). Mac (Macintosh) users can still benefit from this course, but the course doesn't cover Mac-specific keyboard shortcuts and interface.
We'll start at the very beginning, with opening and saving a worksheet in Excel.
I'll show you what all the tools do and walk you through each Excel skill that you need to know.
I'll show you how to enter and format your data and how to create charts, tables and pivot tables so you can organize and analyze your data. I'll teach you amazing keyboard shortcuts to make your work faster and more productive. You'll learn how to record macros so you can do a whole series of actions with the just click of a button.
We'll keep going until you're a pro at Excel. When you finish this course, you'll know more than most people who use Excel at their job every day.
Here's some of what you'll learn.
Creating spreadsheets in Excel
Saving your work where you can find it
Working with multiple workbooks in Excel
Formatting and printing
Importing data from CSV files, Access and the web
Creating and using formulas and functions like: Vlookup, Index/Match, If statements and dozens of others
Creating charts including: Bar charts, Line charts, Column charts, Pie charts and more
Sorting and filtering your data
Creating pivot tables
Creating pivot charts from your pivot tables
Fun New features for Excel
Keyboard shortcuts to make your work go faster
How to record basic macros to automate repetitive tasks
A whole lot more
The course comes with a certificate of completion, a full 30-day money back guarantee, and I'm always available to answer questions or share additional resources if you get stuck.
Thanks for checking out my biggest Excel course ever!
I'll see you in there!
- Anyone who wants or needs to learn Microsoft Excel for their work.
- Anyone who wishes to improve their job or career prospects in an office environment.
- Designed for beginners who are brand new to Excel (also suitable for intermediate Excel users who want to improve).
Welcome to the Complete Excel 2016 Course. This is the biggest 2016 course on the platform! In this lecture I'm going to walk you through the highlights of what you'll learn. By the end of this course, you're going to feel comfortable with Excel and confident in your ability to use it.
There are 3 things you can do to get more out of this course.
- Use the speed control to speed up or slow down lectures so you can best understand the audio
- Beginners, go through the whole course start to finish. Experienced Excel users should skip to the parts they need to learn.
- Download and use the Excel workbook provided. Practice with them as you go through the lectures.
This lecture is a complete tour of the various Excel dashboards and interfaces. It will show you how to use the tabs and ribbons at the top of an Excel workbook, how to navigate the file menu to save, print and customize documents in Excel. And it gives you a tour of the many features and tools available to you in Excel 2016.
Templates are an often overlooked aspect of Excel. Using the right template can save you tons of time creating and formatting your own spreadsheets. It also allows you to take advantage of Excel tools and features that you wouldn't otherwise learn to use.
Even though templates are ready to use, they still require an understanding of the way Excel works. After you complete this course you'll not only be able to create and use many Excel functions and features, you'll also be able to better utilize templates because you'll have the understanding to use them correctly and even to modify them to best suit your needs.
The first step to successfully creating your first spreadsheet is
opening and saving a document. This lecture will show you how to do that
and how to save it to a place where you can find it later.
Learn how to create simple but powerful addition and multiplication
formulas. These will prepare you to do the calculations necessary for
the Kitchen Remodeling Cost worksheet later in the course.
In this lecture I'll show you the difference between a workbook and a
worksheet. I'll also teach you various ways of moving, copying, renaming
and managing your worksheets within, and between different workbooks.
First, in this lecture I'll give you a quick tour of the finished product of the Kitchen Remodel Costs Worksheet. Then I'll show you how to create and format your heading.
Note: You can download the finished worksheet here so you can practice creating and changing the formatting and formulas used in this section.
There is a lot to learn in this section. This lecture gives you an overview of all of the different functions you are going to learn including:
- AVERAGE & AVERAGEA
- COUNT & COUNTA
- SUMIF & SUMIFS
- COUNTIF & COUNTIFS
- LEFT, RIGHT & MID
A function is a piece of pre-defined code that you can use as part of your formula to increase the power of your calculations. This lecture shows you the power that a function has over simple formulas. It also shows you where to find functions and how to use them.
In this lecture I break down a basic formula into 6 main parts and show you how each part behaves as part of a formula.
The anatomy of an Excel formula is all of the numbers, words and symbols that make up a formula. These are all very important to understand because it enables you to create, modify or correct formulas to do the big calculations for you, saving you lots of time and effort.
This lecture will answer these questions:
- What is the equals sign for in an Excel formula?
- What do the parentheses do in an Excel formula?
- How do you write the cell reference as a range of cells?
- What is an operator?
- What order of operations does Excel follow?
Most formulas that you create in Excel reference relative cells by default. However, by creating absolute cell references you can incorporate a fixed cell into your equation while still being able to copy the formula to other cells. This lecture will show you the difference between absolute and relative cell references, and it will show you useful ways to use them in formulas.
You will learn so much faster and remember what you learn longer if you
use the downloads in this course to follow along with the lectures. By
using the downloads you'll have the same Excel file open that I'm using
in the lecture, so you can enter formulas and do calculations right
along with me.
The COUNT and COUNTA functions will tell you how many cells in your
selected range have data in them. COUNT tells you how many cells have a
number in them. And COUNTA tells you how many cells have any entry,
whether it is a number or text. In this lecture I'll show you how these
functions would be useful for determining the active number of sales
reps in a list.
The IF function is a simple but powerful calculating tool. Essentially the IF function says, "If ____ is true, display this result. If _____ is not true, display this other result."
The result could be a number, text or a complex calculation.
This lecture will show you how to use the IF function in Excel using both text and a numerical calculation.
Just like the SUM function, the SUMIF and SUMIFS functions are adding numbers in a range of cells. The difference is that these functions can be set up to only add up the numbers that meet certain criteria in another selected range of cells.
SUMIF works with one other range of cells and SUMIFS works with multiple conditions and multiple ranges of cells.
The VLOOKUP function is one of the most popular functions in Excel. It takes a little bit of getting used to, but once you master this function you can do powerful searches and sort through massive amounts of data in vertical lists. This lesson will show you:
- The rules you need to follow to successfully create a VLOOKUP function.
- How to create a table so you can add rows to your data and your VLOOKUP will still find your new data.
- How to use the VLOOKUP function to pull data from a vertical list.
The LEFT, RIGHT and MID functions in Excel, allow you to sort data quickly and easily from a fixed width string. A fixed width string is a set of numbers in a list that all contain the same number of characters and that all follow the same format.
For example, in the rep ID Code 1JEA from our lesson:
- The 1 is the rep ID number
- The J is the rep's first initial
- The E stands for East region
- The A is the rep's sales group
You'll see in the lecture that all of the rep ID codes follow the same format and have the same number of characters. That allows us to use the LEFT, RIGHT and MID functions to sort that code into a number of other columns that make the data easier to read and to use.
Many students of Excel have told me that Concatenate is their number one favorite function. Concatenate basically just combines text from different cells into one cell. This can save you a ton of time doing monotonous data entry.
For example, if you have a column of first names and a column of last names, you can quickly and easily combine them into a third column using the Concatenate function.
You can take Bob and Smith and combine them to say Bob Smith.
The FIND and SEARCH functions in Excel are very similar to each other. Both are designed to search for any given character or string of characters within a given string and return a number indicating where it begins within that string. The main difference between the two is that SEARCH is not case sensitive and FIND is case sensitive.
For example, if you want to find the word "West", the SEARCH function will find it even if you are searching for "west", while the FIND function will not unless you look for the word "West".
Min and Max will bring back the minimum or maximum number from a selected range of cells. In this lecture I show you how to use them. I also show you how to create an array formula that does calculations on multiple columns of cells. We'll combine the Min and Max functions with an IF function to search first by a sales rep's name and then bring back the Min and Max of that rep's sales.
The AND and OR functions are simple functions that compare two logical arguments. Alone, they are pretty basic, but nested within an IF function they can be quite powerful.
In this lecture I'll show you how to use both the AND and OR functions. Then I'll show you how to next an AND function within an IF function to bring back more relevant and intuitive results in your spreadsheet.
In this lecture I'll teach you how to use 5 different rounding functions:
These are very powerful functions that can be used not only to round numbers, but to catch and fix math errors when working with dollars and cents, and to calculate prices quickly and easily.
Conditional formatting is amazing. It applies simple formatting such as colors or icons to your cells based on the values in your cells. It can apply colors based on which cells are greater than or less than a number. It can even apply different colors based on formulas that you create. And that's not all it does. Once you've added conditional formatting to a range of cells you can then sort the entire range according to your formatting for quick analysis and organization of your data.
In this lecture I'll show you how to use conditional formatting in Excel and how to use conditional formatting within a table to quickly sort and analyze your data.
Charts take boring data and turn it into a visual story. In this lecture I'll show you some of the possibilities of what you can do with charts and then we'll dive right into creating a chart from our Kitchen Remodel Costs worksheet.
Now that you've created a chart, let's do some formatting to make it look awesome!
In this lecture I'll show you how to resize your chart, change the color and width of your columns, add and delete different elements from your chart and how to change the title. At the end of this lecture you'll be able to create a beautiful, professional looking chart from your spreadsheet.
A bar chart in Excel is basically a column chart turned on it's side. The benefit of a bar chart is that it adds emphasis to differences in values because the bars stretch farther across the page.
In this lecture we'll create a bar chart and we'll do some additional formatting to it to make the data really stand out.
Pie charts are great for showing percentages and how each individual part relates to the whole. In this lecture I'll show you how to create a pie chart in Excel from scratch. I'll also show you how you can easily convert an existing chart, such as the bar chart we created in the previous lecture, into a pie chart to save on formatting.
Line charts are best used to represent the change in a value over time. A good example of this is monthly sales numbers for a whole year. In this lecture I'll first show you how to quickly change our existing bar chart into a line chart. Then I'll show you how to use a new spreadsheet containing dates and sales numbers so you can see the best use of a line chart in Excel.
Preparing your data is very important to the success of your pivot tables. In this lecture I'll show you how to clean up your data, put it in the proper format and organize it so that it will work well in a pivot table or pivot chart. Then we'll create a simple pivot table using the recommended pivot tables tool in Excel 2016.
Pivot tables can be intimidating, and the reason is that many people don't understand the 4 main parts of a pivot table and how they work. In this lecture I'll show you exactly where those parts are in the pivot table and what they do.
In the process I'll show you how to test and build a pivot table using all 4 parts. In the next lecture we'll really get to work using pivot tables to create quick calculations.
Now we're ready to put our pivot tables to work to do some fast calculations. In this lecture I'll show you a bunch of different ways that you can use your pivot table to quickly sort and organize your data. I'll also show you some formatting tips and general tips to make using pivot tables quicker and easier.
With Pivot Charts you can take the data from your pivot table and make it visually appealing. The awesome thing about Pivot Charts is that when you make changes to your pivot table it instantly changes your chart as well.
In this lecture I'll show you how to quickly create a Pivot Chart in Excel 2016 and I'll demonstrate how it changes as you change your pivot table.
There are lots of new features in Excel 2016. Among them are 3D Maps, 1-Click Forecasting, Share and Get and Transform. In this lecture I'll show you where to find each one and what it does. Later in the course I'll go into more detail with a few of the more important and interesting new features.
This lecture introduces you to the 6 new chart types for Excel 2016. These include:
- Box and Whisker
You'll see a preview of each of the charts as well as a detailed understanding of what each chart can be used for in a practical application.
Essentially, the "Tell me what you want to do" feature is where you go for help in Excel 2016. Depending on what you type in, it will bring up functions and features of Excel that you can access directly, or it will bring up a list of help topics for further reading.
This lecture will show you the in's and out's of this feature and will help you to use it effectively.
The new 1-click forecasting button allows you to create forecast sheets and charts quickly and easily. Once you click the button, it will also bring up a convenient dialogue box that allows you to change these options in your chart: seasonality, confidence intervals and forecast starting and ending dates.
Learn about some of the best new features of Excel 2016. This lecture shows you how to use the newly integrated 3D maps feature in Excel 2016. This feature is fun and easy to use. I'll show you a 3D maps tour that I created and then take you step by step through how I created it so you can create one for yourself. 3D maps is a graphically exciting feature, but it isn't just for fun. It has power data visualization and computing power build in, too.