Master Microsoft Excel - A Comprehensive Excel Master Class

Quizzes help you learn the material 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!

• Any exercise files used in a video lecture are associated with that video lecture and can be downloaded while you're watching that video lecture.

• You can find all of the files used in this course under the "course files" portion of the "getting started" section.

• You can change the speed of the lecture videos.

• Who was the creator of spreadsheets:

  • Bill Gates

  • Bill The Cat

  • Dan Bricklin

  • Elon Musk

• When were spreadsheets created?

  • 1962

  • 1972

  • 1979

  • 1989

• Practice leads to progress.

• A good general description of spreadsheets is that they allow us to work with numbers and data.

• The web version of Microsoft Excel is part of "Microsoft 365"

• When you subscribe to Microsoft 365, you can download a version of Microsoft Excel that can be installed on your computer.

• At the time of recording, as talked about in the video lecture, who offered the best pricing for Microsoft 365:

  • Microsoft

  • Amazon

  • Costco

  • Some guy named "Mikapalooza" on Reddit

• Microsoft Excel is

  • word processing software

  • spreadsheet software

  • database software

  • image editing software

• Microsoft word is to words, as Microsoft Excel is to numbers.

• To have his mouse highlighted, Todd is using:

  • graphic files installed on his machine

  • a piece of software from Microsoft store called "mouse pointer highlight"

  • some "autohotkey" software

  • computer magic

• To make Excel more readable, you can change it to a high-contrast black and white theme.

• 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

• range

  • a selection of 2+ cells

• colon notation

  • B9:F9

• names

  • named cell & named ranges

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. Using the “sales data” worksheet:

• For items F4:F21, what is the

  • count

  • average

  • sum

Open the workbook attached to this lecture. Write a formula which adds up B4:B7. Use relative references in your formula. Use the autofill handle to copy that formula across B8:M8

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.

• 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

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

• sum

• average

• max

• min

• count

• counta

• countif

• roundup

• rounddown

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

• sum

• average

• max

• min

• count

• counta

• countif

• roundup

• rounddown

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

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.

• SUM function

  • Use this function to add the values in cells.

• IF function

  • Use this function to return one value if a condition is true and another value if it's false. Here's a video about using the IF function.

• LOOKUP function

  • Use this function when you need to look in a single row or column and find a value from the same position in a second row or column.

  • 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

  • Use this function to search for an item in a range of cells, and then return the relative position of that item in the range. For example, if the range A1:A3 contains the values 5, 7, and 38, then the formula =MATCH(7,A1:A3,0) returns the number 2, because 7 is the second item in the range.

• CHOOSE function

  • Use this function to select one of up to 254 values based on the index number. For 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.

• DATE function

  • Use this function to return the sequential serial number that represents a particular date. This function is most useful in situations where the year, month, and day are supplied by formulas or cell references. For example, you might have a worksheet that contains dates in a format that Excel does not recognize, such as YYYYMMDD. **Use the DATEDIF function to calculate the number of days, months, or years between two dates.**

• DAYS function

  • Use this function to return 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 or the reference to a value from within a table or range.

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.

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

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.

• 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 do the date hands-on exercises.

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

• When conveying information to someone, what is more important

  • content

  • form

• Obviously content matters, but when studied, what has a greater impact on people:

  • what you say

  • how you say it

• You can install new fonts on your computer by going to Google Fonts.

• If you double-click the format painter, you can paint formatting from one cell to many cells.

• You can copy a portion of a spreadsheet as a picture.

• When you copy something from the web, when you paste it in Excel, you can choose to "match destination formatting."

• There are many options for pasting which you can explore, but primarily Todd uses paste, paste as values, or match destination formatting.

• To format your spreadsheet, adjusting the horizontal and vertical alignment can produce a nice effect.

• Looking at this picture, you can quickly increase or decrease the size of a font by pressing the large or small A.

• Looking at this picture, the arrow in the bottom right corner tells you that there are more options available.

• In graphic design, font creates feeling.

• In this section, using the spirit of adventure and exploration, we discovered how to use a comment and a note.

• In this section, using the spirit of adventure and exploration, we discovered how to insert a hyperlink to a website.

• In the format cells dialog box, there is an option to "shrink to fit" which shrinks the contents of a cell to fit the cell.

• According to Todd, going on vacation to Hawaii sounds great, but everything is so pricey now!

Copy data from wikipedia and paste it into Excel so that it doesn't have any of the formatting from the web.

Download data from gapminder as a "csv" file, then open it in Excel and save it as an Excel "xlsx" workbook.

Using the format cells dialog box, format various data as different numbers.

Use the "format cells" dialog box "alignment" tab to align cell contents.

Using the "format cells" dialog box "font" tab:

• Use "Parisienne Designed by Astigmatic" from Google Fonts for all text.

• Also make sure to "superscript" the 42 in cell V27.

Using the "format cells" dialog box, work with the "fill" & "border" tabs for format your spreadsheet.

Use the “clear” button on the home ribbon to clear the formatting on a spreadsheet.

Format this spreadsheet and make it look pretty.

• hint: unhide a column

• unhide a row

Transpose the data from a column to a row. Important: only select the data, do not select the entire column. If you select the entire column, you will not be able to paste transpose.

Adjust the row and column height to look good. Hide columns B & C.

Create a new spreadsheet and do the following:

• link

  • create a link

• text box

  • insert a text box with this phrase

• note

  • insert a note

• comment

  • insert a comment

• indent

  • indent the text under TODO

    • link

    • text box

    • note

    • comment

This is the end of the beginner section.

• this is a milestone!

• congratulations!

• look at everything you've learned!

  • shortcuts

    • right clicking

      • context sensitive menu

    • ctrl + c

      • copy

    • ctrl + v

      • paste

    • ctrl + shift + v

      • paste without formatting in google chrome

The Second Coming

BY WILLIAM BUTLER YEATS

Turning and turning in the widening gyre

The falcon cannot hear the falconer;

Things fall apart; the centre cannot hold;

Mere anarchy is loosed upon the world,

The blood-dimmed tide is loosed, and everywhere

The ceremony of innocence is drowned;

The best lack all conviction, while the worst

Are full of passionate intensity.

Surely some revelation is at hand;

Surely the Second Coming is at hand.

The Second Coming! Hardly are those words out

When a vast image out of Spiritus Mundi

Troubles my sight: somewhere in sands of the desert

A shape with lion body and the head of a man,

A gaze blank and pitiless as the sun,

Is moving its slow thighs, while all about it

Reel shadows of the indignant desert birds.

The darkness drops again; but now I know

That twenty centuries of stony sleep

Were vexed to nightmare by a rocking cradle,

And what rough beast, its hour come round at last,

Slouches towards Bethlehem to be born?

• paste in URL window to remove formatting

  • The Second Coming BY WILLIAM BUTLER YEATS Turning and turning in the widening gyre The falcon cannot hear the falconer; Things fall apart; the centre cannot hold; Mere anarchy is loosed upon the world, The blood-dimmed tide is loosed, and everywhere The ceremony of innocence is drowned; The best lack all conviction, while the worst Are full of passionate intensity. Surely some revelation is at hand; Surely the Second Coming is at hand. The Second Coming! Hardly are those words out When a vast image out of Spiritus Mundi Troubles my sight: somewhere in sands of the desert A shape with lion body and the head of a man, A gaze blank and pitiless as the sun, Is moving its slow thighs, while all about it Reel shadows of the indignant desert birds. The darkness drops again; but now I know That twenty centuries of stony sleep Were vexed to nightmare by a rocking cradle, And what rough beast, its hour come round at last, Slouches towards Bethlehem to be born?

• ctrl + x

  • cut

• ctrl + z

  • undo

• ctrl + y

  • redo

• alt + tab

  • cycle through open applications

• ctrl + scroll wheel

  • zoom in, zoom out

• ctrl + backtick

  • show / hide formulas

• ctrl + f

  • find

  • REPLACE

• ctrl + t

  • new tab in google chrome

• escape

  • escape from some condition

• F4

  • add $s

  • cycle through ab$olute, mixed, relative references

• click → then → shift + click

  • select first item, last item, and every item inbetween

• ctrl + click

  • select non-contiguous data

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

  • moves the ACTIVE CELL to the end of contiguous entries

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

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

  • selects data from the ACTIVE CELL to other cells

• ctrl + w

  • close a workbook

• ctrl + o

  • open a workbook

• ctrl + n

  • new workbook

• ctrl + s

  • save a workbook

• alt

  • in excel, shows letters and gives you options to go to different areas by pressing a key on your keyboard

• ctrl + alt + del

  • task manager