Learn Microsoft Excel - The Comprehensive MS Excel Course

Here is a brief overview of what we will cover in this section:

• Introduction

• Exercise files

• Getting your certificate

• Course orientation

• Understanding spreadsheets

• Understanding versions

• Personalization

• Accessibility

• Course files

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 to the COURSE FILES area of 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.

Here is how you get your Microsoft Excel certificate of completion.

• Learning pace

  • change video speed

• This is your course

  • do what you need

  • use it in the way that is best for you

  • quizzes and hands-on exercises are optional

• Course goal

  • a great teacher will teach the student not to need the teacher

  • parable: give a person a fish, or teach them to fish

• Be an adventurer

  • spirit of exploration

• Practice

  • practice leads to progress

• Have fun

  • personal anecdotes

• 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)

• installing

• starting excel

• customizing excel

  • changing look and feel

• coming soon features

• keyboard shortcuts

  • ctrl+f

    • find

• closed captions

• ease of access settings in Windows

• microsoft store

  • powertoys

  • mouse pointer highlight

    • safety: app certification

    • compare to "autohotkey" approach

you can download the course outline as a PDF from here

File extensions are good to know about!

all files for the course can be found here

Quizzes help you learn Microsoft Excel more quickly. By taking this quiz, you are engaging your mind. By working to recall the material, the material is more deeply integrated. Take this quiz to more quickly learn Microsoft Excel!

• Is your computer a 32-bit or 64-bit machine?

• keyboard shortcuts

  • ctrl + t

    • new tab in chrome

  • ctrl + c

    • copy

  • ctrl + v

    • paste

• Introduction

• Workbooks and worksheets

• Useful keyboard shortcuts

• Working with Excel files

• Autosave and autorecover

• Finding your way around Excel

• Mouse pointer awareness

• Writing formulas

• Relative, absolute, & mixed references

• Ranges & names

A workbook has worksheets. When you open a Microsoft Excel file you are opening a workbook. Inside that workbook you will find worksheets. You can add and delete worksheets as needed. Here is what we will learn:

• Starting Excel

  • workbook

  • worksheet

    • columns

    • rows

    • cells

    • active cell

• keyboard shortcuts

  • ctrl + scroll wheel

    • zoom

These are some useful keyboard shortcuts:

• keyboard shortcuts

  • alt + tab

    • cycle through open apps

  • ctrl + t

    • new tab in chrome

  • ctrl + c

    • copy

  • ctrl + v

    • paste

  • ctrl + f

    • find

• Saving a file

  • before you close an Excel file, if it has yet to be saved, MS Excel will ask you to save it

  • the filename area reflects whether or not a file has been saved

• Closing Excel

• Opening a file

  • xlsx file extension

    • how to see file extensions

  • associate "xlsx" files with Excel

• keyboard shortcuts

• ctrl + s

  • save

• ctrl + w

  • closing

• ctrl + n

  • new

• ctrl + o

  • open

• microsoft

  • cloud

    • aka, servers

    • aka, computers

  • consumer retention & switching costs

• autosave

  • microsoft: what is AutoSave?

• autorecover

  • Help protect your files in case of a crash

  • crashing an excel file

• keyboard shortcuts

  • ctrl + alt + del

    • task manager

• TOUR

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

• 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

• RIBBON MENUS

  • showing / hiding

• VIEW / SHOW

  • gridlines

  • headings

  • formula bar

• VIEW / WORKBOOK VIEWS

  • 
    

• PAGE LAYOUT / PAGE SETUP

  • print

    • gridlines

    • headings

• STATUS BAR

  • average

  • count

  • sum

    
    

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.

Learn the basics of writing formulas in Excel including the point-and-click method for writing formulas.

• writing formulas

  • =

  • ‘=

• point-and-click method

• order of operations

• relative references

• formula ribbon

  • show formulas

file: 20-Writing-Formulas

Learn how to use relative, absolute, and mixed references when writing formulas in Excel.

• relative

• ab$olute

• mixed

This quiz will help you master what we have been learning about Microsoft Excel.

Create a new excel spreadsheet. Do the following:

• create a new worksheet

  • name it “Happy items”

  • give the worksheet tab a color

  • move the worksheet tab to the front of the tabs

• starting in cell B2

  • list five items that make you happy

    • one item in each cell: B2, B3, B4, B5, B6

• Delete the other worksheets

  • right-click the worksheets

• edit the entry in cell B3

  • use the double-click method

• edit the entry in cell B4

  • use the formula bar

Create a new workbook. Do the following:

• make sure you are using normal view

• remove the gridlines

• collapse the ribbon menu

pin the ribbon menu back into place

Open the workbook attached to this lecture. Write a formula which calculates the tax. Use an absolute reference. Use autofill to copy the formula.

Open the workbook attached to this lecture. Write a formula which completes the table. Use mixed references. Use autofill to copy the formula.

• Calculate the grade for each student. Assume each graded item carries equal weight. To calculate the grade for each student, just calculate the average score of all scores for that student.

• Calculate the class average for each graded item.

file: 30-gradebook-equal-weight

• Calculate the grade for each student. Each graded item does not carry equal weight. To calculate the grade for each student, you will need to do a weight average calculation.

• Calculate the class average for each graded item.

file: 31-gradebook-weighted

• Calculate the grade for each student. This gradebook is using the points method. To calculate the grade for each student, just add up a student’s points then divide that by total points possible.

• Calculate the class average for each graded item.

file: 32-gradebook-points-method

Calculate the costs for a trip to Disneyland for yourself and three others. Have your trip last 5 days.

• ticket price

• airfare

• hotel

• food & incidentals

Create a spreadsheet that has the recipe for oatmeal peanut butter chocolate chip cookies.

• include the quantities

include the ability for the quantities to multiply by the number of batches desired.

Functions are formulas that are already written for us. Functions allow us to quickly calculate computations. The sum & average functions are covered in this video.

These functions allow you to find the max value in a series of numbers, and find the min value in a series of numbers.

The count function allows you to count how many items are in a series.

• count

• countif

The rounding functions allow you to round numbers. These functions are covered:

• round

• roundup

• rounddown

Documentation is also covered in this video. Also included is how to copy examples out of documentation and get them into Excel.

You can generate random numbers using rand and randbetween.

The concat & textjoin functions allow you to join text together.

The days function allows you to count the number of days between two dates.

• days

• now

The if function allows you to make a decision based upon the value in a cell.

This quiz will help you master what we are learning with Microsoft Excel

• Concatenate is a deprecated function.

• Textjoin allows you to specify a delimiter.

• On the data ribbon, "text to columns" allows you to break text apart either by a delimiter or by a fixed width.

• Examples of how to use a function can be found in the documentation for Excel functions.

• What is the keyboard shortcut for moving the ACTIVE CELL to the end of contiguous entries?

  • ctrl + shift + e

  • ctrl + arrow (down, up, left, or right)

  • ctrl + shift + y

  • ctrl + shift + end

• What is the keyboard shortcut for selecting data from the ACTIVE CELL to other cells?

  • ctrl + shift + e

  • ctrl + shift + arrow (down, up, left, or right)

  • ctrl + shift + y

  • ctrl + shift + end

• What keyboard technique do we use to select non-contiguous data like in the picture?

  • select the first block of data, then hold down CTRL and select other data

  • hold down the FN key and select data

  • hold down FN + TAB and select data

  • use the scroll wheel and meow like a cat

• Using only the keyboard, from the active cell, how do you select the active cell and the two cells to the right?

  • shift + right-arrow key

  • ctrl + right-arrow key

  • fn + shift + right-arrow key

  • fn + shift + ctrl + windows key + f12 + f1 + right-arrow key

Use the following functions in the spreadsheet "44-basic":

• sum

• average

• max

• min

• count

• counta

• countif

• roundup

• rounddown

Looking up a letter grade based upon a percentage score using xlookup and vlookup.

In this hands-on exercise, we will randomly generate names:

1. DOUBLE CHALLENGE - random name generator

   • google:

     • most popular first names

       • https://www.ssa.gov/oact/babynames/

     • most popular last names

       • https://www.thoughtco.com/most-common-us-surnames-1422656

   • COPY / PASTE **or/and** PASTE SPECIAL / VALUES **or/and** PASTE / TEXT

     • hint: new sheet for each "paste special"

     • hint: DATA / SORT A→Z to get rid of empty spaces

   • arrange names

     • 1 column first names

     • 1 column last names

   • use these functions

     • COUNTA

     • RANDBETWEEN

     • INDEX

2. REGULAR CHALLENGE

   • into new spreadsheet

     • select the names

     • COPY / PASTE / VALUES

   • split the names

     • DATA / TEXT TO COLUMNS

   • combine the names

     • TEXTJOIN

Using the if function, display whether or not an individual is OVER or UNDER budget

Copy documentation examples for the round function into Excel.

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])

Vlookup allows us to look a value up in a table of values, then return an associated value. For instance, in a gradebook a student might have a score of 84%. I could use vlookup to then insert “B” next to the student’s grade.

The XMATCH function searches for a specified item in an array or range of cells, and then returns the item's relative position.

Use this function to select one of up to 254 values based on the index number.

• date

  • takes three separate values and combines them to form a date.

    • returns the sequential serial number that represents a particular date.

  • useful in situations where year, month, and day are supplied

    • For example, you might have a worksheet that contains dates in a format that Excel does not recognize, such as YYYYMMDD.

• year, month, day

  • extract the corresponding value from a date

• left, mid, right

  • extract a specified number of characters from a text string

• edate

  • adds/subtracts months from a date

• datedif

  • calculates the number of days, months, or years between two dates.

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

• FIND is for languages that use the single-byte character set (SBCS),

FINDB is for languages that use the double-byte character set (DBCS).

• returns a value from a table / range / array.

• returns a reference from a table / range / array.

This quiz will reinforce what we are learning about Microsoft Excel.

• To use Excel effectively, you must look up functions in documentation and read about them.

• The function DATEDIF will accurately calculate the difference between dates.

• You should never use the DATEDIF function.

• You should strongly avoid using the LOOKUP function.

• When doing a "lookup," you should avoid using the LOOKUP function and instead use the XLOOKUP function.

• If you're not familiar with the function you are going to use, you should read the documentation for that function.

• If it has been awhile since you have skimmed the documentation on a function you regularly use, it would be a good idea to look at it.

• To get to this dialog box:

• RIGHT CLICK and choose FORMAT

• press CONTROL + F10

• do the hokey-pokey and turn yourself around

• You can RIGHT CLICK a cell and then choose FORMAT to format the cell as a specific type of data such as general, text, currency, or number.

• Functions are like building blocks, or legos. We can find creative ways to combine functions together into one formula.

• The INDEX function has two different argument lists. All you really need to remember, however, is that the first three arguments for both are INDEX(array_reference, row_num, [column_num])

• You can add stripes, also known as "row striping," to a spreadsheet by using conditional formatting, choose new rule, and then using the formula =ISEVEN(ROW())

• You can add a drop-down menu to a spreadsheet by using DATA VALIDATION on the data ribbon.

• The YEAR, MONTH, and DAY functions can extract the year, month, and day from a DATE.

• The LEFT, MID, and RIGHT functions can extract a portion of text from a text value.

• the EDATE function is used to add, or subtract, months from a DATE.

• You should never use the DATEDIF function.

• The INDEX function is great for randomly generating data from a list.

• The RAND and RANDBETWEEN functions are also great at helping generate random data from a list.

• If you want to count any non-blank cell, regardless of whether or not text or numbers are stored in the cell, which is the best function for this job:

  • COUNT

  • COUNTA

Use the workbook associated with this exercise to find the secret message.

Use the workbook associated with this exercise to do the date hands-on exercises.

Use the workbook associated with this exercise to do the time hands-on exercises.

Use the workbook associated with this exercise to do the hands-on exercises.

Play with conditional formatting to see what effects you can realize.

• also covered:

  • =ADDRESS(ROW(),COLUMN(),4)

  • =ADDRESS()

  • ROW()

  • COLUMN()

Only allow people to enter text for their name, and a whole number from 0 to 130 for their age.

Use the workbook associated with this exercise to do the hands-on exercises.

• also covered

  • opening a CSV file

Use the workbook associated with this exercise to do the date hands-on exercises.

• also covered in this video:

  • SORT

    • sorting data

Use the workbook associated with this exercise to find peace.

Play with gapminder - very cool data and data visualization!

• https://www.gapminder.org/tools/

And check out the data:

• https://www.gapminder.org/data/

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

• game plan

  • HOME ribbon

    • except "styles" and "format as table" - cover that later

HOME / FONT

• HOME / EDITING / CLEAR

  • clear all

  • clear formats

  • clear contents

  • clear comments and notes

  • clear hyperlinks

• how to insert

  • comment

  • note

  • hyperlink

HOME / CLIPBOARD

• HOME / CLIPBOARD

  • cut

  • copy

    • copy as picture

  • paste

    • keep source formatting

    • match destination formatting

    • paste special

      • many options

        • transpose (already saw this)

        • paste as values (already saw this)

• HOME / ALIGNMENT

  • horizontal alignment

  • vertical alignment

  • merge and center

  • wrap text

HOME / CELLS

More options are found in the format cells dialog box.

Formatting numbers

• general

• number

• currency

• accounting

• date

• time

• percentage

• fraction

• scientific

• text

• special

• custom

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

This quiz will reinforce what we have been learning about Microsoft Excel. Taking the quiz will make you stronger with Excel! Take this quiz to more quickly learn!