Microsoft Excel 2024 - From Beginner to Expert in 6 Hours

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

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!

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

This quiz will help reinforce everything you are learning!

• 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

• 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

• =

  • 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

• relative

• ab$olute

• mixed

• shortcuts

  • undo

    • ctrl + z

  • copy

    • ctrl + c

  • paste

    • ctrl + v

Using relative and absolute references in a gradebook with a curve.

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.

• sum & average

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.

This quiz will reinforce everything you are learning about Microsoft Excel.

• Relative references

  • 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

• Absolute references

  • Write a formula which calculates the tax. Use an absolute reference. Use autofill to copy the formula over as applicable.

• Mixed references

  • Write a formula to fill in each table. Use mixed references.

• chart

  • create a column chart with just LA, NY, and Tokyo.

This provides an introduction to MS Excel functions!

• 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

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

• context sensitive menu

• example

  • copy → paste → transpose

• 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

• xlsx file extension

  • how to see file extensions

• associate "xlsx" files with Excel

  • right click → open with → always open with

This quiz will help reinforce everything you are learning!

• 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

  • edit the entry in cell B3

    • use the double-click method

  • edit the entry in cell B4

    • use the formula bar

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

You can generate random numbers using rand and randbetween.

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

The concat & textjoin documentation.

This quiz will help reinforce everything you are learning!

• Use the following functions in the spreadsheet:

  • sum

  • average

  • max

  • min

  • count

  • counta

  • countif

  • roundup

  • rounddown

An introduction to popular Excel functions.

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.

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

This is the payment PMT function.

These are the payment PMT and IPMT functions.

This is the FV future value function.

This is the FV function used for retirement amortization.

These are the IF and IFERROR functions used for dynamic amortization.

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

This quiz will help reinforce everything you are learning!

• use XLOOKUP to determine the ice cream choice for each person

  • use the "exact match" to choose the ice cream

• use CONDITIONAL FORMATTING to format the fill color anytime strawberry is chosen

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

table & convert to range

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

• 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

=MOD(ROW(),2)

This quiz will help reinforce everything you are learning!

• use conditional formatting to zebra stripe rows

• add a picture

• format the picture

• format the heading

  • merge and center the heading

  • use a nice font for the heading

• use freeze panes

  • view / freeze panes

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

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

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

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:

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.

Sparklines are small charts that occupy a single cell.

This quiz will help reinforce everything you are learning!

• sparklines

  • Add sparklines for the data.

• combo charts

  • Create two combo charts:

    • one

      • unemployment

      • presidential approval

    • two

      • consumer confidence

      • presidential approval

• switching row / column

  • switch the representation of the data on the x / y axis:

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

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

Sample data that is automatically generated.

Sample data that is automatically generated.

How to sort data in Microsoft Excel

How to hold the top header in place in Microsoft Excel - Freeze Panes

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.

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.

This quiz will help reinforce everything you are learning!

• remove duplicates

  • Remove duplicates from your contact list of friends.

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

Let's practice creating pivot tables in Excel with more examples!

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

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.

This quiz will help reinforce everything you are learning!

Create pivot tables

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

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

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.

• 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

This quiz will help reinforce everything you are learning!

• create a macro that says "Great work in the course!" every time you press

  • "ctrl + shift + g"