Microsoft Excel 2024 - From Beginner to Expert in 6 Hours
- Description
- Curriculum
- FAQ
- Reviews
This training provides you everything you need to know about Microsoft Excel.
From the fundamentals, to the most advanced features, after taking this online class you will be able to use Microsoft Excel at an expert level.
You can learn Microsoft Excel easily and quickly if it is taught correctly. Developed by a Microsoft Certified Master Instructor, this course provides comprehensive coverage on Microsoft Excel. A university professor with over twenty years of experience teaching individuals of all ability levels “how to use” Microsoft Excel, Todd McLeod has designed, refined, and perfected this course to make it easy for you to master Microsoft Excel.
In only five hours of videos, provided to you in 80 separate video lectures so that no one online video is too long, you will learn all of the following about Microsoft Excel:
-
Learn how to navigate around Excel
-
Learn how to enter and edit data in Excel
-
Learn how to adjust the way data and information are displayed in Excel
-
Learn how to write formulas quickly and easily with the point-and-click method
-
Learn how to use relative, absolute, and mixed references in Excel
-
Learn how to create powerful calculations with Excel functions
-
Learn how to visually represent your data with charts and graphs in Excel
-
Learn how to use Excel data tools like sorting, subtotaling, and filtering
-
Learn how to “freeze columns and rows” with freeze panes in Excel
-
Learn how to remove duplicates from data in Excel
-
Learn how to “transpose data” – switching the columns and rows in Excel
-
Learn how to use Excel to leverage data with Pivot Tables & Pivot Charts
-
Learn how to format worksheets in Excel for impact and appeal
-
Learn how to automate Excel tasks with time-saving macros
-
Learn how to integrate Microsoft Excel with Microsoft Word
-
Learn how to use passwords in Excel to protect your work in multiple scenarios
-
Learn tips and tricks about Excel, as well as Excel secrets and shortcuts
-
Learn how to use printing and sharing in Excel
-
how to harvest data from the web
-
how to create online forms which allow you to gather data from individuals
-
Download all of the Excel project files that are used in the videos
This class is guaranteed to teach you Microsoft Excel.
Described as “fun” and “amazing” and “life changing,” Todd McLeod’s Excel training will forever transform the way you work with numbers. Try this course for yourself and see how quickly and easily you too can learn Microsoft Excel.
-
1WelcomeVideo lesson
Learning pace
quick pace
succinct
to the point
if needed
slow me down
rewatch
Course files
in the next lecture
all of the files used in the course
no video
This is your course
learn what you need to learn
use it in the way that is best for you
quizzes and hands-on exercises are optional
Be an adventurer
spirit of exploration
don't fear computers - you can't break them
fear ignorance
Practice
practice leads to progress
drop by drop …
persistently patiently …
every day I take consistent action …
grit …
-
2Course filesText lesson
Any file, or files, that I use in a video can be found in the "COURSE CONTENT" panel of the video in which those files are used. You can also access ALL of the files used in the course by going here to this lecture - the "COURSE FILES" lecture here in the "GETTING STARTED" section.
If you try to download more than one file at a time, your web browser might ask you if you want to "download multiple files from this website." Click "yes" to download the files.
These are the files used in the course. Come back to this lecture when you need a file. You can download files from here!
-
3Getting your certificateVideo lesson
Here is how you get your Microsoft Excel certificate of completion.
-
4Quiz #1Quiz
This quiz will help reinforce everything you are learning!
-
5IntroductionVideo lesson
Spreadsheets
allow us to work with numbers. Spreadsheets are like customizable calculators. Spreadsheets also allow us to organize and manage data.
MS Word allows us to work with words.
MS Excel allows us to work with numbers & data.
Dan Bricklin - father of spreadsheets (1979)
examples of spreadsheets
MS Excel
web based
computer based
Google sheets
-
6Microsoft ExcelVideo lesson
MS Excel
Microsoft 365
formerly called "Office 365"
formerly called "Microsoft Office …"
history of releases
(source: wikipedia)
With Microsoft 365, versions are released continuously.
WEB BASED
runs on the web; 'software as a service'; use a web browser to access it
COMPUTER BASED
install it on your computer
more functionality than than the web based version
purchasing
costco
amazon
microsoft
student pricing
installing desktop excel
-
7Relative referencesVideo lesson
=
all formulas start with the '=' sign
point and click method
formula ribbon
show formulas
trace precedents
formulas / trace precedents
remove arrows
shortcuts
switch between open applications
alt+tab
show formulas
ctrl + `
zoom in / out of spreadsheet
ctrl + scroll wheel
-
8Absolute referencesVideo lesson
relative
ab$olute
mixed
shortcuts
undo
ctrl + z
copy
ctrl + c
paste
ctrl + v
-
9Calculating a gradeVideo lesson
Using relative and absolute references in a gradebook with a curve.
-
10Creating chartsVideo lesson
When you create a chart, what you select is crucially important. Generally speaking, you will want to select
data, without totals
column headers
row headers
Sometimes that means selecting non-contiguous regions. To do that
ctrl + click-&-drag
If you don't like the way a chart looks when you create it, try selecting different data and creating the chart again.
-
11FunctionsVideo lesson
sum & average
-
12FCDBVideo lesson
How to use the format cells dialog box. This will allow you to change the formatting type on the values displayed in Microsoft Excel spreadsheets.
-
13Quiz #2Quiz
This quiz will reinforce everything you are learning about Microsoft Excel.
-
15IntroductionVideo lesson
This provides an introduction to MS Excel functions!
-
16A tour of ExcelVideo lesson
Workbook, worksheets, cells
Every Excel file is known as a workbook.
Each workbook has worksheets.
Spreadsheets are made up of columns and rows.
The intersection of a column and row is a cell.
The active cell has a BOX around it.
Each cell has a cell address
column row, eg, B2
range
a selection of 2+ cells
colon notation
B9:F9
names
named cell & named ranges
columns & rows
inserting & deleting
ADDING A SHEET
naming it
changing its color
ADDING DATA
to enter data into a cell, click on the cell and start typing
you can edit data in a cell by
double-clicking the cell
or up in the formula bar
cells overflow if there is no data in the adjacent cell
#######
shows that there is data in that cell
widen the column to see it
double-click the divider to perfectly adjust
you can make columns wider
you can make rows taller
Other
RIBBON MENUS
showing / hiding
VIEW / SHOW
gridlines
headings
formula bar
VIEW / WORKBOOK VIEWS
normal
page break preview
page layout
PAGE LAYOUT / PAGE SETUP
print
gridlines
headings
STATUS BAR
average
count
sum
-
17Mouse pointer awarenessVideo lesson
While using Excel, your mouse pointer will change depending upon the context. Paying attention to the way your mouse pointer looks, and knowing what the different looking mouse pointers mean, will help you use Excel more effectively.
Mouse pointer will change depending upon context
Pay attention to the way the mouse pointer looks
-
18The power of right clickingVideo lesson
context sensitive menu
example
copy → paste → transpose
-
19Autosave & autorecoverVideo lesson
autosave
microsoft: what is AutoSave?
microsoft "cloud"
aka, servers
aka, computers
consumer retention & switching costs
autorecover
Help protect your files in case of a crash
Help protect your files in case of a crash
task manager
ctrl + alt + del
-
20MS Excel filesVideo lesson
xlsx file extension
how to see file extensions
associate "xlsx" files with Excel
right click → open with → always open with
-
21Quiz #3Quiz
This quiz will help reinforce everything you are learning!
-
23Max, min, & documentationVideo lesson
These functions allow you to find the max value in a series of numbers, and find the min value in a series of numbers.
-
24Rand & randbetweenVideo lesson
You can generate random numbers using rand and randbetween.
-
25Concat & textjoinVideo lesson
The concat & textjoin functions allow you to join text together.
-
26DocumentationVideo lesson
The concat & textjoin documentation.
-
27Quiz #4Quiz
This quiz will help reinforce everything you are learning!
-
29IntroductionVideo lesson
An introduction to popular Excel functions.
-
30Top 10 functionsVideo lesson
Search help for "Excel functions (by category)" then choose "our 10 most popular functions"
SUM function
add the values in cells.
IF function
return one value if a condition is true and another value if it's false.
LOOKUP function
AVOID using this one!
VLOOKUP function
Use this function when you need to find things in a table or a range by row. For example, look up an employee's last name by her employee number, or find her phone number by looking up her last name (just like a telephone book).
MATCH function
DON'T USE THIS ONE - USE XMATCH
The XMATCH function searches for a specified item in an array or range of cells, and then returns the item's relative position.
CHOOSE function
select one of up to 254 values based on an index number.
example: if value1 through value7 are the days of the week, CHOOSE returns one of the days when a number between 1 and 7 is used as index_num.
interesting examples in documentation of combining functions
DATE function
take three separate values and combine them to form a date.
example, you might have a worksheet that contains dates in a format that Excel does not recognize, such as YYYYMMDD.
DAYS function
Returns the number of days between two dates.
FIND, FINDB functions
FIND and FINDB locate one text string within a second text string. They return the number of the starting position of the first text string from the first character of the second text string.
INDEX function
Use this function to return a value from within a range / table / array.
-
31Xlookup, vlookup, & hlookupVideo lesson
XLOOKUP
not VLOOKUP or HLOOKUP
Use the XLOOKUP function to find things in a table or range by row. For example, look up the price of an automotive part by the part number, or find an employee name based on their employee ID. With XLOOKUP, you can look in one column for a search term and return a result from the same row in another column, regardless of which side the return column is on.
Note: XLOOKUP is not available in Excel 2016 and Excel 2019, however, you may come across a situation of using a workbook in Excel 2016 or Excel 2019 with the XLOOKUP function in it created by someone else using a newer version of Excel.
The XLOOKUP function searches a range or an array, and then returns the item corresponding to the first match it finds. If no match exists, then XLOOKUP can return the closest (approximate) match.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
-
32PMT payment functionVideo lesson
This is the payment PMT function.
-
33PMT IPMT loan amortizationVideo lesson
These are the payment PMT and IPMT functions.
-
34FV future value functionVideo lesson
This is the FV future value function.
-
35FV retirement amortizationVideo lesson
This is the FV function used for retirement amortization.
-
36IF IFERROR dynamic amortizationVideo lesson
These are the IF and IFERROR functions used for dynamic amortization.
-
37Conditional formattingVideo lesson
The if function allows you to make a decision based upon the value in a cell.
-
38Quiz #5Quiz
This quiz will help reinforce everything you are learning!
-
40Fundamentals of formattingVideo lesson
Formatting your worksheets is important.
content and form
It is not only what you say that matters (the content) but also how you say it (the form). When studied, the greatest impact upon others isn’t the content, but the form. The 7 38 55 study from UCLA says that what impacts people in public speaking is:
7% the content
38% how it’s said
55% body language
This is true in public speaking, this is true in art, this is true in job interviews, and this is true in your Excel spreadsheets. Take your content and give it good form (make it look good).
-
41Formatting tables of data quicklyVideo lesson
table & convert to range
-
42Font is feeling - font formattingVideo lesson
In graphic design, font determines feeling. There are two broad categories of fonts: serif and sans-serif. A serif font has feet; a sans-serif font does not. For text on computer screens, sans-serif is the most popular and, perhaps by consensus, best choice. You can find the most popular fonts in the world on Google Fonts. Once the fonts are installed on your computer, you can use them in your spreadsheets. Take-aways:
use a sans-serif font
use Google Fonts to get the most popular fonts
HOME / FONT
Font is feeling
-
43Exploring the home ribbonVideo lesson
home ribbon
format cells dialog box (fcdb)
things we've already seen
paste
keep source formatting
match destination formatting
paste special
many options
transpose
paste as values
format painter
clear formats
clear all
clear formats
clear contents
clear comments and notes
clear hyperlinks
-
44Zebra stripe rowsVideo lesson
=MOD(ROW(),2)
-
45Quiz #6Quiz
This quiz will help reinforce everything you are learning!
-
47Content & formVideo lesson
Visually representing data: The representation of data influences the perception of data. Here we once again come back to content and form: It’s not just what you say, it’s how you say it. Great examples of data representation:
Gapminder - https://www.gapminder.org/tools/
Aaron Koblin - http://www.aaronkoblin.com/
Examples of different charts we can create:
pie
parts of a whole
line
data changing over time
column
comparing quantities
bar
like column but horizontal
stacked column
like a pie chart and a column chart combined
stacked bar
like stacked column but horizontal
two different charts convey different impressions
trendlines
trends over time
sparklines
small charts that occupy a single cell
combo charts
multiple charts in one chart
scatter
scattered dots of data
-
48Creating chartsVideo lesson
When you create a chart, what you select is crucially important.
If you don't like the way a chart looks when you create it, try selecting different data and creating the chart again.
spirit of adventure and exploration
Sometimes that means selecting non-contiguous regions. To do that
ctrl + click-&-drag
You can also switch the representation of the data on the x / y axis:
right click a chart
select data
switch row / column
changes the way data is displayed
-
49Formatting chartsVideo lesson
When formatting charts, embrace a spirit of exploration and experimentation. Pay attention to what you are left-clicking and right-clicking. Look at the options available. Explore and experiment until you get the look you want.
click a chart
paintbrush to the right
change look
plus ( + ) to the right
show/hide different aspects of the chart
click part of a chart
delete it by pressing delete
-
50Chart trendlinesVideo lesson
Use a trendline to show the general trend of some data. You can use trendlines with some charts. To insert a trendline, first click on your chart, then go to:
-
51Combo chartsVideo lesson
Combo charts allow you to combine two charts. Creating a combo chart requires a few steps:
create a chart with one column of data
copy/paste a second column of data onto the chart
select the chart, then change the chart type to a combo chart
You can create a secondary axis so that data of different scales can still be graphed together.
consider including an axis title so that others can easily interpret the data.
-
52SparklinesVideo lesson
Sparklines are small charts that occupy a single cell.
-
53Quiz #7Quiz
This quiz will help reinforce everything you are learning!
-
55Gapminder dataVideo lesson
Download data from gapminder as a "csv" file, then open it in Excel and save it as an Excel "xlsx" workbook.
-
56Scraping web dataVideo lesson
Copy data from wikipedia and paste it into Excel so that it doesn't have any of the formatting from the web.
-
57Outdoor gear coopVideo lesson
Sample data that is automatically generated.
-
58Name generatorVideo lesson
Sample data that is automatically generated.
-
59Sorting dataVideo lesson
How to sort data in Microsoft Excel
-
60Freeze panesVideo lesson
How to hold the top header in place in Microsoft Excel - Freeze Panes
-
61Removing duplicatesVideo lesson
You can remove duplicate data using the “remove duplicates” tool from the data ribbon.
download the spreadsheet used in this video then remove the duplicates.
try changing the data in one row of a duplicate, then “remove duplicates” in such a way that this row with changed data is not removed.
-
62Filtering dataVideo lesson
Filter allows us to filter our data by criteria we specify. When we filter data, we tell Excel to only show certain data based upon certain criteria.
-
63Quiz #8Quiz
This quiz will help reinforce everything you are learning!
-
65IntroductionVideo lesson
A pivot table allows you to pivot your view on your data.
pivot tables to turn data into information.
recommended pivot tables (we saw this earlier in the course)
pivot table analyze ribbon
show field list
drag fields between areas
-
66Creating pivot tablesVideo lesson
Let's practice creating pivot tables in Excel with more examples!
-
67Slicers - filtering pivot tablesVideo lesson
A slicer is a visual interface for filtering data in a table. We can use slicers to visually filter our data. Slicers can also be applied to regular tables.
pivot table analyze / insert slicer (salesperson, manufacturer, region, customer)
pivot table analyze / insert timeline (salesperson, manufacturer, region, customer)
Table data and slicers
insert / slicer (data needs to be a table to work)
create table and convert to range
-
68Pivot chartsVideo lesson
We can build charts based upon pivot tables. When we do this, the chart is connected to the pivot table. The chart is known as a pivot chart. When the pivot table is refreshed, the chart will be refreshed.
-
69Quiz #9Quiz
This quiz will help reinforce everything you are learning!
-
71Printing your worksheetVideo lesson
To print well in Excel, the first and most important thing you need to know is how to look at what is going to be printed. To do this, we can use the following
VIEW ribbon
page break preview
page layout
PAGE LAYOUT ribbon
Page setup dialogue box
Print preview
CTRL + P
print to file
-
72Protecting (locking) workbooks, sheets, and cellsVideo lesson
FILE LEVEL (file / info)
Protect an excel file
password needed to open
Read only
you can make changes, you just can't save
WORKBOOK LEVEL
Protect a workbook structure
prevents viewing hidden worksheets, adding, moving, deleting, or hiding worksheets, and renaming worksheets
WORKSHEET LEVEL
Control how users work within worksheets. Specify exactly what users can do within a sheet
STEP 1: LOCK CELLS / UNLOCKED CELLS
STEP 2: protect sheet with password
notice the checkbox:
"protect worksheet and contents of locked cells"
-
73Object linking & embedding (OLE)Video lesson
Object linking and embedding allows you to either LINK or EMBED content from excel into ms word. When content is linked the content in word updates when the source data in excel updates.
-
74Introduction to macrosVideo lesson
Macros allow you to automate your work
If you have a process that you repeat over and over, you can “record” that process and then assign that process to a shortcut key or an icon.
on the view ribbon
view > macros
when you record a macro
every action must be precise
think about what you're going to do before you hit record
use "ctrl + shift"
saving a workbook with macros
"save as macro enabled workbook"
xlsm
-
75Quiz #10Quiz
This quiz will help reinforce everything you are learning!
External Links May Contain Affiliate Links read more