Microsoft Excel - From Beginner to Certified Professional
- 5 hours on-demand video
- 9 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
- You will learn all you need to know to go from a beginner with the new Excel 2019 and Excel 365 to a certified professional.
- Not only will this course get you up to speed on the most common features and functions of the most poplar spreadsheet program on the market, it will also prepare you to take and pass the 77-727 Microsoft Excel Certification exam
- How to create and manage worksheets and workbooks
- How to manage data cells and ranges
- How to create and customize tables
- How to perform operations with formulas and functions
- How to create charts and objects
- Learn from a Professional Training and Course Developer that has done work for the DEA, FBI, oil and gas companies and many more.
- You need access to Excel. Any of the following versions will work: 2016, 2019, or as part of the Office 365 subscription
- A desire to learn and time to do some hands-on
What Do His Students Have to Say?
"Scott made learning Excel fun!"
"Thank you for your videos you explain in a way which everyone can understand!!!"
"This guy has the right balance between humor and info in my opinion. Thank you!"
About The Course
Microsoft Excel is the spreadsheet program of choice for companies, governments agencies, and organizations around the world. We have specifically designed this course to help you go from beginner to a certified professional.
In this course, you will learn how to use the most recent versions of Microsoft Excel, the 2019 and constantly updated Office 365 version.
This course follows the objectives created by Microsoft and ensures that not only will you develop the knowledge, skills, and abilities (KSAs) to use Excel but you will also be prepared to take and pass the Microsoft Office Specialist 77-727 Excel Core exam.
In this course you will develop the KSAs to:
Create worksheets and workbooks
Navigate through worksheets and workbooks
Format worksheets and workbooks
Customize options and views for worksheets and workbooks
Configure worksheets and workbooks to print or save
Insert data in cells and ranges
Format cells and ranges
Summarize and organize data
Create and manage tables
Manage table styles and options
Filter and sort a table
Summarize data by using functions
Perform conditional operations by using functions
Format and modify text by using functions
Create a chart
Format graphic elements
Insert and format objects
About Your Teacher
Scott Ford, M.Ed. is a professional instructional designer and technical trainer. In his over 138 years in education and training, he has developed courses and IT training for the DEA, FBI, the City of Houston, City of Arlington, Chevron, Global Knowledge, LyondellBasell, D.R. Horton, and many many more.
Scott has a master degree in educational technology, multiple IT certifications, including Microsoft Office Specialist, Microsoft Certified Professional, CompTIA A+ Computer Technician, CompTIA Network+, and more.
Scott has also been a featured speaker for national, state, and local training and educational organizations. He also maintains an educational YouTube channel (mrfordsclass) that has over 60,000 subscribers and 5+ million views.
- This course was designed to turn beginners into Excel heroes.
"The journey of a thousand miles begins with one step." - Lao Tzu
I want to welcome you to the first video in what I hope you will find an educational and engaging course.
I am a course developer by trade and one thing we learn early on is, start each unit, course, lecture with objectives. As the start of each unit you will find a short video. This video will introduce you to the topic and present you with the objectifies for that unit.
I have tried my best to make sure that when you are done with a unit you have new skills that you can apply right then and there.
With that let me introduce you to the first video of the first unit.
Our objectives for Section 1: Welcome To Excel are
Identify and label the Excel interface
Locate different tools within the Excel menu system
Modify the Quick Access Toolbar
Use common navigation shortcuts
Enable the developer tab
What is Excel, or for that matter, what is a spreadsheet?
In this video, we introduce spreadsheets and briefly discuss how they contributed to the Personal Computer revolution. We then look at Excel, where it came from, and more importantly, what does it look like today. We also explore what Excel 2019 is and how it differs from the Excel you find as part of the Office 365 subscription.
Click on this, type in there and don’t touch that doo-hickey.
Part of becoming proficient in any software is learning what those buttons, drop-downs, and slides are called.
In this video, we explore the current Excel interface and build our solid foundation by discovering the different parts that make up the Excel interface.
The Quick Access Toolbar is a huge time saver and can be customized with pretty much every function and feature that Excel has to offer.
Anyone that uses Excel, even casually, would be well off to learn not only where to find this great tool but to configure based on their workflow.
“The global economy is built on two things: the internal combustion engine and Microsoft Excel. Never forget this.” — Kevin Hector, Twitter
Welcome to unit 2. In this unit we focus on some of the core functionality of Microsoft Excel. We discover how to create and save workbooks and worksheets. We find and modify workbook properties. We learn how to adjust the zoom and explore the different view options.
This unit really sets the ground work we need in order to use Excel efficiently.
Unit 02 Objectives:
Create and save workbooks
Find and modify workbook properties
Create and customize worksheets
Move worksheets both within and to a new workbook
Hide and unhide worksheets
Add and remove both rows and columns
Use different ways to view worksheets
In this video you will learn how to create a new workbook both from scratch and from one of the hundred of templates available for free. You will also learn how to create workbooks and new worksheets from text files, an important skill for the certification exam. Finally you will discover to best to save your document and how to open it.
Each file on your computer maintains hidden property information. This isn’t information inside the document, but rather information about the document. If you know how to configure properties, you can make your life a lot easier when having to save and search a large number of files.
When we are done with this video, you will know how to find the workbook properties, how to add tags and comments. You will also learn how to inspect your workbook for personal information that you might not want to be shared. Before we finish, we also learn how to check the document for accessibility, so people with visual challenges can use your workbook and possible compatibility issues with an older version of Excel.
It is important when using Excel to know how to move worksheets both within the same workbook and to another workbook. When we are done with this video, you will learn how to do both.
You will also learn how to hide and unhide worksheets to keep them safe and out of the way. Finally, you will learn how to delete unwanted worksheets, both one that has content and blank ones.
Sure, Excel is a powerful spreadsheet program, but by default it makes some rather visually unappealing worksheets by default. Lucky for us there are limitless formatting options to make our spreadsheets easier on the eyes and friendlier to use.
In this video you will learn how to apply themes, configure headers and footers, as well as adjust the height and width of the cells.
There will be times when you want or need to add new columns and rows to your worksheet. You might also need to hide information that is out of date or you just don’t someone messing with.
At the end of this lesson you will learn how to manual create new rows and columns. You will lean how to use the keyboard shortcut that will save you time when making new rows. You will also be able to use the very use hide and unhide functions built into Excel.
The video lesson in this unit is all about changing the views of your worksheets and workbooks to really streamline your workflow. Changing these views isn’t necessary to use Excel but it can make a world of difference in how enjoyable it can be to use.
When you are done with this lesson your will learn how to arrange multiple workbooks, how to open multiple instances of the same workbook so you can see the different worksheets, adjust the zoom, and set the very helpful freeze panes.
“To be an accountant in the age of spreadsheet program is — well, almost sexy.” — Steven Levy in “A Spreadsheet Way of Knowledge” (1984)
Howdy and welcome to Unit 3, Create Cells and Ranges.
In this unit we develop some core skills when it comes to entering data into our Excel worksheets. We look at the different ways to not only enter data but to also make life so much easier with features like AutoFill, AutoComplete, and Drop-Down List to name just a few.
We also discover how to format our cells so they are easier to use just plan old nicer to look at. Then we end with the find and replace feature that can save you hours of time in large workbooks.
Use different methods to enter data into cells
Cut, copy, paste and move data within Excel
Format cells to make worksheets easier to use
Find and replace data within a spreadsheet
If you have ever used Microsoft Word, there is a good chance you have used the cut, copy, and paste functions. In Excel, they act in much the same ways, but there are some differences that can save you a great deal of time IF you know how to use them. In this video, we look at the special paste options as well as live preview and how to move cells around the worksheet.
In this video, you will learn how to do the traditional Merge & Center as well as the other types of merging like Merge Across and Merge Cells.
Imagine having to look at spreadsheets all day, or maybe you don’t have to imagine. Improperly formatted worksheets can be hard to work with and can even cause eye strain and headaches. It is important to know how to format your worksheets so that they are easier to work with, plus the eyes you save might be your own.
“Nobody sets out to create a mission critical spreadsheet, they ‘just happen’. ” — Felienne Hermans in “Analyzing and visualizing spreadsheets”
Spreadsheets are great at storing information; for some, it has become a type of database. The real power in Excel can be found within the formulas and functions. With just a few clicks, Excel can do calculations that would make a math teacher jealous.
In this unit, we look at how we use functions and formulas, the proper time to switch between the two and some standards that you need to know.
Name a cell and group
Describe the different types of cell references
Write a simple Excel formula
List some common functions and write out examples of each
Use common format functions to configure a cells
By default, Excel will name a cell based on its location. First by column then by row, if you have ever played Bingo or Battleship this should sound familiar (A4, B12, C15). Excel provides us the ability to give names to cells and ranges. Naming cells and ranges based on function or importance can be very handy when working with larger worksheets.
When you are done with this lesson, you will be able to name pretty much anything that can go into a workbook.
The power to do calculations in Excel isn’t based on static numbers. Excels power is found within the ability to use a cell reference to create a dynamic process in order to do that we use cell references. A cell reference means a cell is referring to another cell.
When you have completed this lesson, you will know how to use relative, absolute, and mixed cell references. You will also learn how to pull information from other worksheets and workbooks into your calculations.
Sure, you could use Excel to make lists of information and never unleash its true power, but why? Excel has the ability to do everything you learned in your math classes, except better. To get Excel to do its magic it needs to know what you want it to do, here is were creating formulas is important.
Knowing how to use formulas in Excel is great, but knowing that Excel already provides these formulas in functions is outstanding. Functions, which there are over 400 different types, already have the formulas built in and can make your life so much easier if you know how to use them.
When you are done with this lesson, you will know what functions are, where to find them and how to use them.
“Excel is the best tool non programmers have in order to write code, that’s why it wins.” — antirez on HackerNews
Nothing puts a shiver down a new Excel user’s back like conditional formulas. In this unit we will take our time and explain conditional formulas, the IF, SUMIF, AVERAGEIF, and COUNTIF in a easy to understand way. Before we are done with this unit you will be able to use, with confidence, these powerful tools that scare so many.
Describe and use the IF function to perform a basic logical operation
List the proper structure of a nested IF function
Describe and use the SUMIF function to perform a basic logical operation
Describe and use the AVERAGEIF function to perform a basic logical operation
Describe and use the COUNTIF function to perform a basic logical operation
The foundation, the building block of conditional formulas is this little two-letter word “IF.” Yet armed with only the IF you can make your spreadsheet do amazing logical calculations that will astonish and impress your friends, assuming, of course, your friends are into that sort of thing, no judgment.
In this lesson, you will learn about the basic syntax of the IF statements and what it means to test a condition and what happens if it is true or false.
“Tell me and I forget. Teach me and I remember. Involve me and I learn” – Benjamin Franklin
Nothing helps you learn and retain information like doing it yourself. In this lesson, you will create a worksheet that applies conditional formatting based on shipping costs. This lesson will take you from a blank worksheet all the way to an actual worksheet that you might find on the job.
Before you start this lesson, be sure to have access to a computer running Excel.
Just like our Shipping Lab, in this lesson, you will get hands-on experience using the SUMIF command. You will be creating a worksheet from scratch and use the SUMIF function to add up the amounts stolen by different famous thieves.
Like the Shipping Lab, be sure to have Excel up and running.
“The best tool available today for exploring real-life questions of quantity and change is the spreadsheet.” — Bret Victor, “Kill Math”
Excel makes creating tables very easy. These tables, in turn, make using Excel and looking at data super simple.
In this unit, we learn all about making tables. When you are done, you will be able to create tables in seconds, add and remove column and rows, add and modify the very helpful total row, filter, and sort data and remove duplicate records with a simple click on a button.
Create a table from a cell range, and convert it back to a cell range
Add and remove columns and rows from a table
Apply and configure table style options
Insert total rows
Sort data by multiple columns
Change sort order
Remove duplicate records
Microsoft: “Tables make it easy to sort, filter, and format data within a sheet,” and they aren’t wrong. Tables are a powerful tool we can use in Excel to manipulate our data. It takes seconds to create if you know how to do it.
When you are done with this lesson, you will learn three different methods for creating tables and how you too, can make one of these powerful creations in seconds.
A primary purpose of using tables is their filtering and sorting capabilities. With just a couple of clicks, you can filter and sort through thousands of cells of data.
After this lesson, you will know how to quickly create filers and sorts for your data. You will also learn how to use the custom sort features that added even more power to the sort function.
When you are dealing with hundreds of rows of data, there is a good chance you will have redundant, duplicated information. One could go through each row manually and spend hours if not days looking for those duplicates, or do a couple of clicks and have Excel do it for you.
Once you have completed this lesson, you will be able to remove duplicate data quickly.
“There's something that happens with the collection of a large amount of data when it's dumped into an Excel spreadsheet or put into a pie chart. You run the risk of completely missing what it's about.” — Aaron Koblin
One can get bogged down in numbers and miss the bigger picture. Visual representations of what we are working with can be a valuable tool, and Excel makes it easy to put that data into charts. Excel also allows us to add different types of images and art to our projects to help break up the monotony and can provide valuable communication tools.
In this unit, you will learn multiple ways to create charts, how to add data series, how to switch the rows and columns, and add formatting. Finally, you will be able to insert different objects, like text boxes, shapes, and images, as well as make sure they are accessible to individuals with visual challenges.
Create a new chart
Add additional data series
Switch between rows and columns in source data
Analyze data by using Quick Analysis
Add and modify chart elements
Apply chart layouts and styles
Move charts to a chart sheet
Insert text boxes, shapes, and images, and add Alt. Text
Modify object properties
Excel gives us a fairly boring default title for our charts. The names don’t help us understand the purpose of those charts. Luckily changing the titles is a pretty simply task.
When you are done with this video, you will be able to change your Excel chart title.
The purpose of a chart is to provide a visual representation of your data. It would only make sense that you should then be able to format your chart to best present the information.
In this lesson, you will learn how to change layouts, add and remove different elements, and modify the styles.
Our final lesson in this unit introduces us to inserting and working with different objects.
When you have completed this lesson, you will be able to insert text boxes, add shapes, locate and use images, and add alt. text to your visual elements to help those with visual challenges.
“If I had a spreadsheet on my computer, it looked like I was busy.” — Nate Silver
In the previous unit, you learned about using charts and objects. In this unit, we look at other ways to view our data. First, you will be introduced to sparklines. Sparklines are miniature charts that can tell you a lot about your data in a glance. Then we look at how to group data, then how to create subtotals. We end with a revisit to conditional formatting.
Apply conditional formatting
This entire unit has been about summarizing and being able to make sense of your data. Subtotals are a great way to consolidate your information. With subtotals, you can create all sorts of formats that give you insights into your data.
When you have completed this lesson, you will be able to sort and add subtotals to your worksheets.
We took a brief look at conditional formatting in another unit. In this lesson, we revisit this powerful and easy to use tool.
When you are done with this lecture, you will know how to use built-in conditional formatting options as well as use the customized conditional formatting options.
“With all the attention given to the personal computer, it's hard to remember that other companion machine in the room - the printer.” — Ellen Ullman
Welcome to our final unit in our study of Microsoft Excel. Printers and printing no matter what program you are using has always been….interesting. Printers have come a LONG way since the old days of using a parallel port, and now with 3D printers becoming a reality. However, even with the advance of printers and printing, one still needs to know how to work with them
In this unit, you will learn about how to get your spreadsheet from a softcopy (on the computer) to a hardcopy (printed out). You will learn how to set up the pages, set print areas, adjust the scaling, repeated rows and columns on the printout, add customized headers and footers. Finally, we will cover how to send all of that to the printer and the options you might encounter.
Modify page setup
Set a print area
Set print scaling
Display repeating row and column titles
Print all or part of a workbook
Microsoft Excel spreadsheets are interesting when compared to Microsoft Word documents. With a Word document, you might want to print out the entire document or just a couple of pages. In Excel, we not only have a single document but within a single workbook, we might have multiple worksheets. We might also want to print out specific parts of a worksheet.
When you are done with this lesson, you will learn how to print out a specific area, a worksheet, or an entire workbook.
Worksheets can get a little long. Microsoft Excel will automatically add page breaks to your documents. You can, however, add breaks manually.
When you are done with this lesson, you will learn how to see where the page breaks have been places, insert page breaks, move page breaks, and finally delete page breaks.
Excel has a cool little feature that allows you to adjust the scaling of your worksheets. With scaling, you can adjust how wide a page will be, how tall and overall size. It is like using zoom for a printout
When you are done with this lecture, you will know how to adjust print scaling, how to scale the width, and how to scale the height.
Imagine that you have a 10-page print out. You have since long forgotten what each column or row means, and you have to go back to page one over and over again. In Excel, you can specify rows and columns to printout on each page over and over again.
When you are done, you will be able to set repeating rows and columns in Excel. This, by the way, is definitely on your MOS exam.
Like Microsoft Word documents, you can set different headers and footers in your Excel print out. If you know how to use the header and footer option, you can put almost anything in them that would normally go into the worksheet itself.
When you are done, you will know how to make the header and footer area visible, as well as how to add and configure different options for print out.
Thank you so very much for taking my course. Be sure to reach out to me and let me know what you liked, what you didn’t like, what course you would like to see next. Finally, if you take the MOS exam and pass it, send me a picture. I love to celebrate my students’ successes.
You can find me on these different social media platforms: