Teach on Udemy

Turn what you know into an opportunity and reach millions around the world.

Learn More

Your cart is empty.

Keep shopping

Microsoft Excel: Excel Beginner Basics, Formulas & Functions

Name: Microsoft Excel: Excel Beginner Basics, Formulas & Functions
Rating: 4.6 (64 reviews)

Microsoft Excel Course: From A-Z, Zero to Hero - Microsoft/Office/Excel 365, Microsoft Excel 2019, Microsoft Excel 2016

Created byRobert Mathisen

Last updated 7/2024

English

What you'll learn

Build a solid understanding on the Basics of Microsoft Excel
Learn the most common Excel functions used in the Office
Take control of your Excel data! Learn fast and efficient management techniques.
Learn how to THINK like Excel, and write powerful and dynamic Excel formulas from scratch
How to standardize and clean data ready for analysis in Excel
Be a Confident & Advanced Microsoft Excel user
Learn new techniques and formulas to tackle tasks faster and better in Excel
Learn practical methods to sort and filter your data in Excel

Course content

14 sections • 195 lectures • 9h 0m total length

Excel's Start Screen1:12
Microsoft Excel Course: From A-Z, Zero to Hero - Microsoft/Office 365/Excel 365, Microsoft Excel 2019, Microsoft Excel 2016
Workbooks and Worksheets in Excel0:43
In Excel, a workbook is a spreadsheet program file that can contain one or more worksheets, also known as spreadsheets. A worksheet is a single spreadsheet that consists of cells organized into rows and columns, where users can enter and calculate data. The term "workbook" is used because it's similar to a book, with each worksheet like a page.
Exploring Excel's User Interface2:26
Microsoft Excel Course: From A-Z, Zero to Hero - Microsoft/Office 365/Excel 365, Microsoft Excel 2019, Microsoft Excel 2016
Navigating around the Worksheet0:54
Microsoft Excel Course: From A-Z, Zero to Hero - Microsoft/Office 365/Excel 365, Microsoft Excel 2019, Microsoft Excel 2016
Navigating using your Keyboard1:14
Here are some keyboard shortcuts for navigating around Excel:
Arrow keys: Move between cells, with the up arrow moving down, down arrow moving left, left arrow moving right, and right arrow moving to the edge of the current data region
Ctrl + arrow key: Enter End mode, move to the next nonblank cell in the same column or row as the active cell, and turn off End mode
Ctrl + Home: Return to the upper left of the worksheet
Ctrl + End: Return to the lower right of the worksheet
Ctrl + Page Up/Page Down: Switch between sheets
Scroll Lock or ScrLk toggle key: When toggled on, the movement keys scroll the window rather than moving the cursor
Shortcuts for moving the Cell Pointer1:13
Microsoft Excel Course: From A-Z, Zero to Hero - Microsoft/Office 365/Excel 365, Microsoft Excel 2019, Microsoft Excel 2016
Scroll & Zoom1:24
Here are some ways to scroll and zoom in Excel:
Scroll
To scroll to the start or end of a range in a column or row, press CTRL and an arrow key. To scroll to the start or end of a range while selecting it, press CTRL+SHIFT and an arrow key. To scroll one row up or down, press SCROLL LOCK and then use the UP or DOWN arrow key. To scroll one column left or right, press SCROLL LOCK and then use the LEFT or RIGHT arrow key. To scroll one window up or down, press PAGE UP or PAGE DOWN.
Zoom
To zoom in or out, you can use the Zoom group on the View tab:
Click Zoom 100%
Click Zoom to Selection to maximize the view of selected cells
Click Zoom and then enter a percentage or choose other settings
Mouse wheel
You can also zoom in or out by holding down the CTRL key and using the scroll wheel on your mouse. Scrolling forward zooms in, and scrolling downward zooms out. However, some say this method can be choppy. To make it smoother, you can try clicking on cell A1 before zooming.
Keyboard
You can also zoom in or out using the Control key and the plus and minus keys on your keyboard. To zoom in, hold down Control and press the plus key. To zoom out, hold down Control and press the minus key.
Backstage View0:35
The Backstage view in Excel is a central hub that provides access to functions that apply to the entire file, such as opening, saving, printing, and managing files. It also allows users to access program options and customization settings.
Introducing Your First Assignment2:27
Microsoft Excel Course: From A-Z, Zero to Hero - Microsoft/Office 365/Excel 365, Microsoft Excel 2019, Microsoft Excel 2016
Getting to know Excel - Assignment 10:15
Microsoft Excel Course: From A-Z, Zero to Hero - Microsoft/Office 365/Excel 365, Microsoft Excel 2019, Microsoft Excel 2016
Getting to know Excel - Assignment 1 (Answer Guide)7:03
Microsoft Excel Course: From A-Z, Zero to Hero - Microsoft/Office 365/Excel 365, Microsoft Excel 2019, Microsoft Excel 2016
The Excel Ribbon2:21
Microsoft Excel ribbon is the row of tabs and icons at the top of the Excel window that allows you to quickly find, understand and use commands for completing a certain task.
Excel Ribbon Tabs - Part 12:53
The Excel ribbon is a set of toolbars at the top of the window that contains tabs for quick access to commands. The standard Excel ribbon includes the following tabs:
File
Provides access to the backstage view, which contains file-related commands and Excel options
Home
Includes frequently used commands like copying and pasting, sorting and filtering, and formatting
Insert
Allows users to add objects to a worksheet, such as images, charts, PivotTables, hyperlinks, equations, and headers and footers
Formulas
Includes mathematical and logical functions, as well as tools for auditing and evaluating formulas
Excel Ribbon Tabs - Part 21:11
Microsoft Excel Course: From A-Z, Zero to Hero - Microsoft/Office 365/Excel 365, Microsoft Excel 2019, Microsoft Excel 2016
Types of Commands on the Ribbon3:12
Microsoft Excel Course: From A-Z, Zero to Hero - Microsoft/Office 365/Excel 365, Microsoft Excel 2019, Microsoft Excel 2016
Shortcut Menus1:54
In Excel, the shortcut menu displays a list of commands relevant to a selected item. It can be opened by pressing Shift+F10 or right-clicking an item. The commands on the shortcut menu will vary depending on the object that is selected.
The Quick Access toolbar0:42
The Quick Access Toolbar (QAT) in Excel is a customizable toolbar that contains frequently used commands. It's located in the upper left corner of the Excel window, either above or below the ribbon.
Adding Ribbon Commands to the Quick Access toolbar2:24
Add a command to the Quick Access Toolbar
On the ribbon, select the appropriate tab or group to display the command that you want to add to the Quick Access Toolbar.
Right-click the command, and then select Add to Quick Access Toolbar on the shortcut menu.
Adding Non-Ribbon Commands to the Quick Access toolbar1:17
Add a command to the Quick Access Toolbar that isn’t on the ribbon
Select Customize Quick Access Toolbar > More Commands.
In the Choose commands from list, select Commands Not in the Ribbon.
Find the command in the list, and then select Add.
The Formula bar1:44
The formula bar in Excel is a toolbar at the top of a worksheet window that allows users to enter, copy, edit, and review formulas. It's labeled with the function symbol (fx) and is available in both desktop and online versions of Excel.
Dialog Boxes1:11
A dialog box is a temporary window an application creates to retrieve user input. An application typically uses dialog boxes to prompt the user for additional information for menu items.
Tabbed Dialog Boxes0:40
Tabbed dialog boxes are common in Excel. They break complex dialogs into multiple pages that replace each other as the user clicks on the different tabs.
Task Panes1:19
Task panes are interface surfaces that typically appear on the right side of the window within Word, PowerPoint, Excel, and Outlook. Task panes give users access to interface controls that run code to modify documents or emails, or display data from a data source. Use task panes when you don't need to embed functionality directly into the document.
Getting to know Excel - Assignment 20:08
Microsoft Excel Course: From A-Z, Zero to Hero - Microsoft/Office 365/Excel 365, Microsoft Excel 2019, Microsoft Excel 2016
Getting to know Excel - Assignment 2 (Answer Guide)2:48
Microsoft Excel Course: From A-Z, Zero to Hero - Microsoft/Office 365/Excel 365, Microsoft Excel 2019, Microsoft Excel 2016

Data Types2:23
4 Excel data types
Number data. Data is this category includes any kind of number. ...
Text data. This kind of data includes characters such as alphabetical, numerical and special symbols. ...
Logical data. Data in this type is either TRUE or FALSE, usually as the product of a test or comparison. ...
Error data.
Entering Numbers2:22
In Excel, you can format numbers in cells for things like currency, percentages, decimals, dates, phone numbers, or social security numbers.
Entering Text5:05
To enter text into a cell in Excel, you can:
Click on the cell
Type the text you want to enter
Press Enter or Tab
Using Enter Mode1:08
Ready
Ready is the default mode and indicates that Excel is waiting for user input. It will continue to appear providing you are not editing a cell, defined name, conditional formatting formula or chart range.

Edit
Edit mode will be activated when any of the following events occur:
double-clicking on a non-empty cell
clicking in the formula bar—irrespective of whether the active cell is empty or non-empty
pressing the F2 key in the active cell
You cannot navigate your way around the worksheet using your keyboard’s directional keys whilst in this mode.

Enter
Double-clicking on a blank cell or typing directly into a cell will activate Enter mode. However, if you click in the formula bar and start typing, Edit mode will be invoked instead.
Unlike with Edit mode, you can use your keys to select cell references, however, Excel will automatically jump to Point mode when doing so.
Entering Date values3:11
Excel supports two date systems, the 1900 date system and the 1904 date system. Each date system uses a unique starting date from which all other workbook dates are calculated. All newer versions of Excel calculate dates based on the 1900 date system, while older versions used the 1904 system.

The 1900 date system
In the 1900 date system, dates are calculated by using January 1, 1900, as a starting point. When you enter a date, it is converted into a serial number that represents the number of days elapsed since January 1, 1900. For example, if you enter July 5, 2011, Excel converts the date to the serial number 40729. This is the default date system in Excel for Windows, Excel 2016 for Mac, and Excel for Mac 2011. If you choose to convert the pasted data, Excel adjusts the underlying values, and the pasted dates match the dates that you copied.

The 1904 date system
In the 1904 date system, dates are calculated by using January 1, 1904, as a starting point. When you enter a date, it is converted into a serial number that represents the number of days elapsed since January 1, 1904. For example, if you enter July 5, 2011, Excel converts the date to the serial number 39267. This is the default date system in earlier versions of Excel for Mac. If you choose not to convert the data and keep the 1904 date system, the pasted dates vary from the dates that you copied.
Entering Time values1:00
When you type a date or time in a cell, it appears in a default date and time format. This default format is based on the regional date and time settings that are specified in Control Panel, and changes when you adjust those settings in Control Panel. You can display numbers in several other date and time formats, most of which are not affected by Control Panel settings.

Display numbers as dates or times
You can format dates and times as you type. For example, if you type 2/2 in a cell, Excel automatically interprets this as a date and displays 2-Feb in the cell. If this isn't what you want—for example, if you would rather show February 2, 2009 or 2/2/09 in the cell—you can choose a different date format in the Format Cells dialog box, as explained in the following procedure. Similarly, if you type 9:30 a or 9:30 p in a cell, Excel will interpret this as a time and display 9:30 AM or 9:30 PM. Again, you can customize the way the time appears in the Format Cells dialog box.
Working with 2-Digit Years1:16
00 through 29 is interpreted as the years 2000 through 2029. For example, if you type the date 5/28/19, Excel assumes the date is May 28, 2019.

30 through 99 is interpreted as the years 1930 through 1999. For example, if you type the date 5/28/98, Excel assumes the date is May 28, 1998.
Entering and Editing Data - Assignment 10:07
Microsoft Excel Course: From A-Z, Zero to Hero - Microsoft/Office 365/Excel 365, Microsoft Excel 2019, Microsoft Excel 2016
Entering and Editing Data - Assignment 1 (Answer Guide)9:05
Microsoft Excel Course: From A-Z, Zero to Hero - Microsoft/Office 365/Excel 365, Microsoft Excel 2019, Microsoft Excel 2016
Data Entry3:35
Microsoft Excel Course: From A-Z, Zero to Hero - Microsoft/Office 365/Excel 365, Microsoft Excel 2019, Microsoft Excel 2016
Deleting the Contents of a Cell2:05
On the Home tab, in the Editing group, click the arrow next to the Clear button , and then do one of the following:
To clear all contents, formats, and comments that are contained in the selected cells, click Clear All.
To clear only the formats that are applied to the selected cells, click Clear Formats.
To clear only the contents in the selected cells, leaving any formats and comments in place, click Clear Contents.
To clear any comments or notes that are attached to the selected cells, click Clear Comments and Notes.
To clear any hyperlinks that are attached to the selected cells, select Clear Hyperlinks.
Replacing the Contents of a Cell1:24
To replace text or numbers, press Ctrl+H, or go to Home > Editing > Find & Select > Replace.
In the Find what box, type the text or numbers you want to find, or select the arrow in the Find what box, and then select a recent search item from the list.
In the Replace with box, enter the text or numbers you want to use to replace the search text.
Select Replace All or Replace.
Editing the Contents of a Cell1:29
Click the cell that contains the data that you want to edit, and then click anywhere in the formula bar. This starts Edit mode and positions the cursor in the formula bar at the location that you clicked. Click the cell that contains the data that you want to edit, and then press F2.
Undo and Redo1:47
You can undo, redo, or repeat many actions in Microsoft Word, PowerPoint, and Excel. You can undo changes, even after you have saved, and then save again, as long as you are within the undo limits (By default Office saves the last 100 undoable actions).

Undo an action
To undo an action press Ctrl+Z on your keyboard, or select Undo on the Quick Access Toolbar. You can press Undo (or Ctrl+Z) repeatedly if you want to undo multiple steps.

Redo an action
To redo something you've undone, press Ctrl+Y or F4. (If F4 doesn't seem to work, you may need to press the F-Lock key or Fn Key, then F4) on your keyboard, or select Redo on the Quick Access toolbar. (The Redo button only appears after you've undone an action.)
Editing in the Cell versus on the Formula Bar0:31
You can edit the contents of a cell directly in the cell. You can also edit the contents of a cell by typing in the formula bar.
When you edit the contents of a cell, Excel is operating in Edit mode. Some Excel features work differently or are unavailable in Edit mode.
When Excel is in Edit mode, the word Edit appears in the lower-left corner of the Excel program window.
Inserting Cells0:54
Insert cells
When you insert blank cells, you can choose whether to shift other cells down or to the right to accommodate the new cells. Cell references automatically adjust to match the location of the shifted cells.
Select the cell, or the range of cells, to the right or above where you want to insert additional cells.
Tip: Select the same number of cells as you want to insert. For example, to insert five blank cells, select five cells.
Hold down CONTROL, click the selected cells, then on the pop-up menu, click Insert.
On the Insert menu, select whether to shift the selected cells down or to the right of the newly inserted cells.
Displaying Text on Separate Lines within a Cell1:10
To start a new line of text at any specific point in a cell:
Double-click the cell in which you want to enter a line break.
Tip: You can also select the cell, and then press F2.
In the cell, select the location where you want to break the line, and press Alt + Enter.
Using AutoFill to Enter a Series of Values3:13
Select one or more cells you want to use as a basis for filling additional cells.
For a series like 1, 2, 3, 4, 5..., type 1 and 2 in the first two cells. For the series 2, 4, 6, 8..., type 2 and 4.
For the series 2, 2, 2, 2..., type 2 in first cell only.
Drag the fill handle.
AutoFill Series with Custom Increments1:37
Microsoft Excel Course: From A-Z, Zero to Hero - Microsoft/Office 365/Excel 365, Microsoft Excel 2019, Microsoft Excel 2016
Entering and Editing Data - Assignment 20:08
Microsoft Excel Course: From A-Z, Zero to Hero - Microsoft/Office 365/Excel 365, Microsoft Excel 2019, Microsoft Excel 2016
Entering and Editing Data - Assignment 2 (Answer Guide)14:37
Microsoft Excel Course: From A-Z, Zero to Hero - Microsoft/Office 365/Excel 365, Microsoft Excel 2019, Microsoft Excel 2016
AutoComplete3:59
Microsoft Office Excel completes text entries that you start to type in a column of data — if the first few letters that you type match an existing entry in that column. If you want to stop automatic completion, you can turn this option off.
Click File > Options.
Click Advanced, and then under Editing options, select or clear the Enable AutoComplete for cell values check box to turn this option on or off.
Entering Fractions1:04
Use the Fraction format to display or type numbers as actual fractions, rather than decimals.
Select the cells that you want to format.
On the Home tab, click the Dialog Box Launcher next to Number.
In the Category list, click Fraction.
In the Type list, click the fraction format type that you want to use.
Automatic Number Formatting1:46
Microsoft Excel Course: From A-Z, Zero to Hero - Microsoft/Office 365/Excel 365, Microsoft Excel 2019, Microsoft Excel 2016
Using the Ribbon to Format Numbers1:21
Microsoft Excel Course: From A-Z, Zero to Hero - Microsoft/Office 365/Excel 365, Microsoft Excel 2019, Microsoft Excel 2016
Formatting Numbers by using the Format Cells Dialog Box5:18
Microsoft Excel Course: From A-Z, Zero to Hero - Microsoft/Office 365/Excel 365, Microsoft Excel 2019, Microsoft Excel 2016
Entering and Editing Data - Assignment 30:08
Microsoft Excel Course: From A-Z, Zero to Hero - Microsoft/Office 365/Excel 365, Microsoft Excel 2019, Microsoft Excel 2016
Entering and Editing Data - Assignment 3 (Answer Guide)2:32
Microsoft Excel Course: From A-Z, Zero to Hero - Microsoft/Office 365/Excel 365, Microsoft Excel 2019, Microsoft Excel 2016

Working with Excel Windows1:21
Microsoft Excel Course: From A-Z, Zero to Hero - Microsoft/Office 365/Excel 365, Microsoft Excel 2019, Microsoft Excel 2016
Moving and Resizing Windows1:43
Microsoft Excel Course: From A-Z, Zero to Hero - Microsoft/Office 365/Excel 365, Microsoft Excel 2019, Microsoft Excel 2016
Switching among Windows0:42
Microsoft Excel Course: From A-Z, Zero to Hero - Microsoft/Office 365/Excel 365, Microsoft Excel 2019, Microsoft Excel 2016
Closing Windows0:36
Microsoft Excel Course: From A-Z, Zero to Hero - Microsoft/Office 365/Excel 365, Microsoft Excel 2019, Microsoft Excel 2016
Activating a Worksheet1:16
Microsoft Excel Course: From A-Z, Zero to Hero - Microsoft/Office 365/Excel 365, Microsoft Excel 2019, Microsoft Excel 2016
Adding Worksheets0:48
Microsoft Excel Course: From A-Z, Zero to Hero - Microsoft/Office 365/Excel 365, Microsoft Excel 2019, Microsoft Excel 2016
Deleting Worksheets0:58
Microsoft Excel Course: From A-Z, Zero to Hero - Microsoft/Office 365/Excel 365, Microsoft Excel 2019, Microsoft Excel 2016
Renaming a Worksheet1:23
Microsoft Excel Course: From A-Z, Zero to Hero - Microsoft/Office 365/Excel 365, Microsoft Excel 2019, Microsoft Excel 2016
Changing a Sheet Tab Color0:49
Microsoft Excel Course: From A-Z, Zero to Hero - Microsoft/Office 365/Excel 365, Microsoft Excel 2019, Microsoft Excel 2016
Rearranging your Worksheets1:15
Microsoft Excel Course: From A-Z, Zero to Hero - Microsoft/Office 365/Excel 365, Microsoft Excel 2019, Microsoft Excel 2016
Zooming In and Zooming Out2:24
Microsoft Excel Course: From A-Z, Zero to Hero - Microsoft/Office 365/Excel 365, Microsoft Excel 2019, Microsoft Excel 2016
Worksheet Operations - Assignment 10:07
Microsoft Excel Course: From A-Z, Zero to Hero - Microsoft/Office 365/Excel 365, Microsoft Excel 2019, Microsoft Excel 2016
Worksheet Operations - Assignment 1 (Answer Guide)3:58
Microsoft Excel Course: From A-Z, Zero to Hero - Microsoft/Office 365/Excel 365, Microsoft Excel 2019, Microsoft Excel 2016
Freezing Window Panes3:29
Microsoft Excel Course: From A-Z, Zero to Hero - Microsoft/Office 365/Excel 365, Microsoft Excel 2019, Microsoft Excel 2016
Selecting Rows and Columns1:42
Microsoft Excel Course: From A-Z, Zero to Hero - Microsoft/Office 365/Excel 365, Microsoft Excel 2019, Microsoft Excel 2016
Adding New Columns and Rows2:48
Microsoft Excel Course: From A-Z, Zero to Hero - Microsoft/Office 365/Excel 365, Microsoft Excel 2019, Microsoft Excel 2016
Deleting Columns and Rows2:11
Microsoft Excel Course: From A-Z, Zero to Hero - Microsoft/Office 365/Excel 365, Microsoft Excel 2019, Microsoft Excel 2016
Hiding Rows and Columns2:20
Microsoft Excel Course: From A-Z, Zero to Hero - Microsoft/Office 365/Excel 365, Microsoft Excel 2019, Microsoft Excel 2016

Finding Data8:11
Use find and replace to locate data across worksheets, specific ranges and formatting, with wildcards ? and *, and options like match case and match entire cell contents.
Finding and Replacing Data2:31
Find and replace data in a worksheet using find and replace dialog, selecting the search scope (whole sheet or specific cells), entering the search and replacement strings, and undoing mistakes.
Copying or Moving Ranges2:15
Master copying and moving ranges in Excel with drag-and-drop or cut, copy, and paste, including contents, formatting, and notes, while avoiding overwriting existing entries.
Paste Special3:42
Explore Excel's paste special options to control what you paste, including formulas, values, number formatting, source formatting, column widths, orientation, linking, pictures, and conditional formatting.
Paste Special Dialog Box2:10
Explore the paste special dialog box in Excel to perform add, subtract, multiply, and divide, choose paste and operation options, use skip blanks, and transpose to pivot a data table.
Working with Ranges - Assignment 10:07
Working with Ranges - Assignment 1 (Answer Guide)6:51
Use find and replace to identify n/a values in columns c and e, replace them with question marks, then copy, move, and adjust ranges with keyboard shortcuts.
Hyperlinks2:46
Create hyperlinks in an Excel worksheet by attaching links to text or objects, directing to destinations such as cells, ranges, external files, web pages, or emails.
Editing Links1:44
Learn to edit links in Excel by right-clicking a link to open the Edit Hyperlink dialog, and modify the destination or screen with options identical to Insert Hyperlink.
Removing a Link0:58
Remove a hyperlink from a cell without deleting its text or graphic by right-clicking the cell and selecting the remove hyperlink command.
Adding Notes to Cells2:27
Add notes to Excel cells to attach simple text boxes describing values or formulas, resize and format them, and manage visibility with the red triangle indicator and show/hide options.
Sorting and Filtering7:18
Learn to sort and filter Excel tables using the header filter button, including by column or color, multi-column and custom sorts, text and number filters, and copying only visible data.
Custom Sort2:34
Use a custom sort in Excel to order sales reps A to Z and sales from smallest to largest, using multiple levels.
Copy & Paste - Filter vs Hide1:34
Explore how copy-paste differs when filtering versus hiding data in Excel. Filter for sales above 400 and compare with hiding rows, which become zero height.
Working with Ranges - Assignment 20:08
4) Working with Ranges - Assignment 2 (Answer Guide)7:55
Explore Excel fundamentals by adding and editing cell notes, formatting note colors, and applying sorting, filtering, and basic data analysis to identify top discounts, highest prices, and earliest dates.

Fonts3:13
Apply fonts, sizes, attributes to headers and data in worksheets; use font group and format cells font tab to control style, color, and effects with bold, italics, and underline shortcuts.
Text Alignment5:16
Master horizontal and vertical text alignment in Excel, including left, center, right, wrap text, justify, and center across selection, using the Format Cells dialog.
Colors and Shading3:43
Explore Excel's colors and shading tools to customize text and cell backgrounds, keep to theme colors for cohesion, and access over 16 million colors via the home tab's font group.
Borders and Lines5:41
Apply borders to a cell selection using the borders dropdown, adjust border type and line style, and choose color, or use draw border and draw border grid.
Format Painter2:43
Copy cell formatting with the format painter to apply fonts, font sizes, bold, and italics across multiple cells by dragging the paintbrush, or keep it active by double clicking.
Formatting - Assignment 10:07
Formatting - Assignment 1 (Answer Guide)5:48
Explore the data by selecting a cell and pressing ctrl+down to view the row count. Bold the headers, auto-fit columns, and format the annual salary as currency with no decimals.

Creating a New Workbook1:07
Create a new workbook in Excel using a blank workbook or template from the start screen, and adjust the default sheet count in the general tab of Excel options.
Opening an Existing Workbook3:32
Open an existing Excel workbook by navigating to its folder and double-clicking the file, or open from the backstage view using the recent files list.
Saving a Workbook2:41
Save your workbook frequently to guard against power failures and crashes, using the save icon or Ctrl+S; choose Save As for file or location with descriptive names and .xls extension.
Changing the Default File Location1:44
Change the default local file location in Excel using save as backstage and this PC under locations; edit the path in file > options > save for automatic saving.
Password-Protecting a Workbook1:03
Set a password to protect a workbook by encrypting with a password, confirming it, and saving. When reopened, Excel prompts for the password.
Saving and Password Protection - Assignment 10:07
Saving and Password Protection - Assignment 1 (Answer Guide)2:10
Demonstrate password protecting an Excel workbook by encrypting with a password, saving, closing, and reopening to verify access. Then remove the password by deleting it and saving again.

Printing from the Backstage View5:04
Learn to print from the backstage view in Excel, choosing printer, copies, orientation, paper size, margins, and scaling, with options for active sheets, workbook, or selection.
Previewing the Printout0:58
Explore two ways to preview page layout in Excel before printing: the page layout view with margins, headings, and rulers, or the Backstage print preview showing pages as printed.
Changing Your Page View3:44
Explore Excel views for printing. Use normal view with page breaks, page layout view for margins and headers, and page break preview to fit data to one page.
Previewing the Pages2:01
Preview and adjust page layout in Excel before printing, using print preview to view worksheets or workbooks, navigate pages, adjust margins, zoom to actual size, and print.
Creating PDF Files1:11
Export worksheets to pdf or xps to preserve layout in a read-only format. Use file export to name, locate, and configure the pdf document in Excel, noting pdf's wide support.
Printing - Assignment 10:07
Printing - Assignment 1 (Answer Guide)5:42
Learn how to print and export Excel data by selecting the first 25 passengers with headers, adjusting orientation to landscape, and saving the result as a PDF.

Formulas & Editing Formulas2:29
Master creating and editing formulas in Excel by starting with an equal sign, referencing cell values, and editing via the formula bar, mouse, or F2, including basic addition.
Order of Operations1:47
Learn how excel applies the order of operations to formulas, including parentheses, division, and addition, using pemdas and bodmas as guides.
Excel Functions5:08
Learn to use Excel functions, including the sum, average, count, max, and min, with auto fill, function arguments, and handling range shifts and common errors.
Relative & Absolute Cell References1:25
Explore relative versus absolute cell references, lock a cell with absolute references using F4, and correctly drag formulas to calculate percentages of a total in Excel.
Mixed References6:57
Master mixed references in Excel by using absolute and relative references to compute tax payable, drag formulas across and down, toggle with F4, and handle transposed data.
Circular References2:31
Identify how circular references arise in Excel formulas, with examples using B4, D4, and D5. Learn to rethink formulas to avoid these issues and maintain reliable calculations.
Intro to Formulas, Functions, and Cell References - Assignment 10:07
Intro to Formulas, Functions, and Cell References - Assignment (Answer Guide)5:32
Master excel formulas and functions to sum salaries, calculate average, count, max, and min, and explore status bar insights and relative and absolute references with the fill handle.

RANDBETWEEN2:30
Demonstrates using randbetween to generate monthly sales rep data between 5 and 15, autofill, recalculate with F9, copy values only, and adjust column widths for a cleaner table.
ROUND, ROUNDUP, ROUNDDOWN2:36
Learn how to round numbers in Excel with round, round up, and round down. Set decimal places with digits, and round to tens or hundreds using negative digits.
INT, TRUNC, ROUND2:49
Learn how to use Excel's integer, truncate, and round functions to round down to the nearest integer, remove the fractional part, and round to a specified number of digits.
MROUND2:08
Use the mround function to round numbers to a desired multiple (5, 10, 1000, 10000), format the result (currency, etc.), and round to decimals like 0.5, 0.25, or 0.05.
FLOOR, CEILING4:05
Use floor and ceiling to round numbers down or up to the nearest multiple, compare them with the m round function, and note round up/down by digits only.
Math Functions - Assignment 10:07
Math Functions - Assignment 1 (Answer Guide)3:28
Use randbetween to generate numbers 0–100, copy as values, multiply unit cost by units sold for total, then apply round and mround, and format as currency.
ODD, EVEN1:45
Explore the odd and even functions in Excel basics, which round numbers up to the nearest odd or even integer, with zero being even and negatives rounding away from zero.
MOD1:08
Explore how the mod function returns the remainder when numbers are divided by four. See examples with 4, 5, 6, 7, and 8 to understand remainders.
SUM, AutoSum, Alt + Plus Sign, Quick Analysis3:01
Learn multiple ways to add values in Excel, including the sum function, autosum, alt plus sign, and quick analysis for summing rows and columns.
Running Total Formula3:23
Discover how to compute a running total in Excel with sum function, adding each month to the prior total, and drag the formula while locking starting cell with absolute references.
SUMIF1:53
Learn to use the sum if function to sum sales that meet the criteria of greater than or equal to 400, wrap the criteria in quotes, and verify results.
SUMIFS1:29
Learn to use the sumifs function to sum sales for Jack where sales are at least 400, returning 800 in this example.
Math Functions - Assignment 20:08
Math Functions - Assignment 2 (Answer Guide)10:37
Learn to perform summation in Excel using the sum function, autosum, and running totals, then apply sum if and sumifs with filters to compute item totals like pencils and binders.

AND Function1:43
Explore the and function in Excel, which returns true only when all conditions are met, demonstrated with a sample where C3 equals boy and D3 is greater than four.
IF (boy)2:18
Learn how the if function in Excel performs a logical test, returning yes or no when a cell contains 'boy', with an and condition for 'boy' and older than four.
IF (older than 4)3:40
Explore the if function to test if a value is greater than four, returning yes or nothing, and learn why numbers require no quotes in Excel formulas.
IF & AND (boy, and older than 4)1:59
Explore the IF and AND functions in Excel by building a conditional test: if a person is a boy and older than four, return football; otherwise return nothing.
IF (not a boy)0:57
Explore the Excel IF function with not logic to test not a boy and return a doll or nothing, using comparisons like less than, greater than, and equal to.
IF & AND (not a boy, and younger than 4)1:42
Learn to apply the if function and the and function in Excel to check not a boy and younger than four, returning a doll or candy.
Logical Functions - Assignment 10:07
Logical Functions - Assignment 1 (Answer Guide)4:28
Apply the and function inside an if statement to identify central region orders of at least $400, returning high central orders and leaving nonmatching rows blank using relative references.
OR Function1:15
Explore how the or function returns true when any condition is met, such as a value equal to girl or younger than four, shown with Kara, Zoe, and Jack.
IF & OR (girl, and younger than 4)1:16
Build an Excel formula using if and or to test if someone is a girl or younger than four, returning a stuffed animal; modify it to return a toy.
IFS3:51
Learn how the IFS function handles multiple conditions for student scores, using and for 60 to 85, and returning comments like 'challenge this student' or 'seek extra help'.
Nested IF4:38
Master nesting of the if function in Excel to test multiple criteria, producing outcomes such as kid, young boy, young girl, and adult, including adult male and adult female.
Logical Functions - Assignment 20:08
Logical Functions - Assignment 2 (Answer Guide)17:22
Master logical functions in Excel by building or and if statements, including nested if statements with and, isblank, and isformula, and apply them to pricing, sorting, and data validation.
ISEVEN2:16
Explore how the is even function identifies even numbers in Excel, handles blank cells, and returns yes for even values, nothing for odd values.
ISODD1:35
Use the isodd function in excel to test for odd numbers, identify odd values, and return yes for odds while blank cells return false and other cells return nothing.
ISBLANK4:22
Learn how the isblank function identifies blank cells and handles blanks in formulas. Use even/odd checks and the IFS function to return values or nothing.
ISFORMULA2:12
Explore the is formula function in Excel to detect whether a cell contains a formula, returning true, and transform results to the word formula, then convert formulas to values.
ISERROR, IFERROR3:21
Master handling division by zero when calculating average sales per client using ISERROR and IFERROR. The lesson demonstrates replacing errors with zero or nothing and introduces IFNA and IFS.
Logical Functions - Assignment 30:08
Logical Functions - Assignment 3 (Answer Guide)2:45
Demonstrates using an if statement to flag blank cells as missing regions and return nothing otherwise, while highlighting that spaces are not blank and require proper quotation mark handling.

Requirements

Must have access to Microsoft Excel (ideally Excel 365, Excel 2021, or Excel 2019)

Description

Are you ready to master Microsoft Excel and unlock its full potential? Whether you're a beginner looking to grasp the basics or an experienced user aiming to refine your skills, you've come to the right place!

In this comprehensive Microsoft Excel course, we cover everything from the fundamental functions to advanced techniques that will make you a spreadsheet wizard. Excel isn't just a tool. It's your secret weapon for organizing data, and performing complex calculations that drive informed decisions.

Why Microsoft Excel? It's the cornerstone of data management across industries, from finance and marketing to education and beyond. By the end of this course, you'll feel confident navigating Excel's interface, and creating formulas with ease.

Each module is designed for maximum clarity and hands on practice, ensuring you not only understand concepts but can apply them immediately.

Ready to excel in Excel? Join us on this exciting learning journey and take your skills to the next level. Enroll today and empower yourself with the spreadsheet skills that employers value.

Let's dive in and discover the endless possibilities of Microsoft Excel together!

Below are the sections and topics that will be covered in this course.

Getting to know Excel

Understanding Excel Workbooks and Worksheets: Learn how Excel organizes data using workbooks and worksheets, including creating, saving, and managing multiple sheets within a single Excel file.
Exploring the User Interface: Discover the key components of the Excel interface, such as the toolbar, formula bar, and sheet tabs, to streamline your workflow and boost productivity.
Navigating Excel: Master the techniques for moving through large datasets in Excel, efficiently locating specific data or cells within your worksheets.
Shortcuts for moving the Cell Pointer: Accelerate your work by using keyboard shortcuts to quickly move the cell pointer in Excel, improving your navigation and data entry speed.
The Microsoft Excel Ribbon: Get familiar with the Excel Ribbon, where you can find essential tools and functions organized into tabs for easy access to the features you need.
Excel Dialog Boxes: Learn how to interact with Excel’s dialog boxes, which provide advanced settings and options for functions, formatting, and more, enhancing your control over the software.

Entering and Editing Data in Excel

Excel Data Types: Learn about the various data types used in Excel, including text, numbers, dates, and formulas, to optimize how Excel interprets and processes data.
Entering Data: Discover how to input data into Excel cells efficiently and accurately, from manual entry to using built-in shortcuts.
Editing & Deleting Cell Contents: Explore methods for editing existing data in Excel cells, as well as removing cell contents without disrupting your spreadsheet's structure.
Undo & Redo in Excel: Master the Undo and Redo functions in Excel to quickly reverse or reapply changes, ensuring smoother data entry and editing.
Inserting Cells: Learn how to insert new cells into an Excel worksheet, shifting existing data to accommodate additional entries.
AutoFill: Understand how Excel’s AutoFill feature helps replicate data or extend patterns across multiple cells, saving time in repetitive tasks.
AutoComplete: See how Excel’s AutoComplete feature can automatically suggest and complete entries based on previous data, reducing typing time and errors.
Intro to Formatting: Get an introduction to Excel formatting tools to enhance the appearance of your spreadsheets, making them easier to read and interpret.

Worksheet Operations in Excel

Moving and Resizing Microsoft Excel Windows: Learn how to efficiently move and resize Excel windows to manage your workspace and improve multitasking across multiple files.
Activating a Worksheet: Discover the simple ways to activate and switch between Excel worksheets, helping you navigate large workbooks with ease.
Adding, Deleting, and Renaming Worksheets: Master the steps to add new worksheets, delete unnecessary ones, and rename them in Excel to keep your work organized.
Zooming In & Out in Excel: Explore how to zoom in and out of your Excel worksheet to get a better view of your data, whether you’re fine-tuning details or looking at the bigger picture.
Freezing Window Panes: Learn how to freeze rows and columns in Excel, ensuring critical data remains visible while scrolling through large datasets.
Selecting, Adding, Deleting, and Hiding Rows and Columns: Understand how to select, insert, delete, and hide rows and columns in Excel, streamlining your data management and organization.

Working with Ranges in Excel

Finding and Replacing Data: Learn how to quickly locate and replace specific text or values within Excel to save time and improve efficiency when working with large data sets.

Copying, Moving, and Cutting Ranges: Master the essential skills of copying, moving, and cutting Excel data ranges to organize and restructure your spreadsheets with ease.

Paste Special in Excel: Explore advanced options for pasting data in Excel, including formulas, values, formats, and more, using the powerful Paste Special feature.
Adding, Editing, and Removing Hyperlinks: Discover how to insert, modify, and remove hyperlinks in Excel to create interactive, connected spreadsheets that link to external sources or internal sheets.
Excel Cell Notes: Learn how to add, edit, and manage cell notes in Excel, allowing you to insert helpful comments or annotations for better collaboration and data tracking.
Sort and Filter: Understand how to use Excel's Sort and Filter tools to organize and analyze data, making it easier to find relevant information within your worksheets.
Custom Sort: Dive into Excel's Custom Sort feature, enabling you to sort data based on multiple criteria for a more detailed and organized view of your spreadsheet.

Formatting in Excel

Excel Fonts: Learn how to change font types, sizes, and styles to enhance the appearance of your Excel worksheets.
Text Alignment: Discover how to adjust the alignment of text within cells, including vertical and horizontal alignment options in Excel.
Colors and Shading: Understand how to apply background colors and shading to cells to highlight important data in Excel.
Borders and Lines in Excel: Master the use of borders and lines to clearly separate data and create professional-looking tables in Excel.
Format Painter: Learn how to quickly copy and apply formatting from one cell to another using Excel’s Format Painter tool.

Saving and Password Protection in Excel

Creating a New Microsoft Excel Workbook: Learn how to start fresh by creating a new Excel workbook, customizing it to your needs from the ground up.
Opening an Existing Microsoft Excel Workbook: Discover how to quickly and efficiently open existing Excel workbooks to access and update your data.
Saving a Microsoft Excel Workbook: Understand the best practices for saving your Excel workbook to ensure your data is securely stored and accessible.
Changing the Default File Location: Learn how to modify the default save location in Excel to streamline your workflow and keep your files organized.
Password-Protecting a Microsoft Excel Workbook: Explore how to add password protection to your Excel workbooks to safeguard sensitive information from unauthorized access.

Printing in Excel

Previewing the Printout: Learn how to use the print preview feature in Excel to ensure your data is formatted correctly before printing.
Changing your Page View: Discover how to adjust Excel's page view settings to optimize your worksheet for printing, including switching between normal, page layout, and page break views.
Previewing the Pages: Master the skill of navigating through multiple pages in Excel’s print preview mode to confirm all your data appears as expected.
Creating PDF Files: Explore how to easily save and export your Excel worksheets as PDF files for sharing or printing.

Disclaimer: If running Excel 365, Excel 2021, Excel 2019, you'll likely have access to all of the following functions.
Although, if you're using Excel 2016 or earlier versions, some functions may not be available.

Intro to Excel Formulas, Functions, and Cell References

Excel Formulas: Learn how to create and use Excel formulas to perform basic and complex calculations, helping you automate tasks and improve efficiency in your spreadsheets.
Excel Functions: Discover the most commonly used Excel functions, such as SUM, AVERAGE, and IF, which allow you to streamline data analysis and make more informed decisions.
Relative & Absolute Cell References: Understand how Excel's relative and absolute cell references work, ensuring that your formulas behave as expected when copied to different cells in your worksheet.
Mixed References: Explore mixed cell references in Excel, which combine both relative and absolute references to offer more flexibility when creating formulas.
Circular References: Learn about circular references in Excel, how they can occur, and the steps to resolve or manage them effectively within your formulas.

Math Functions

RANDBETWEEN: The Excel RANDBETWEEN function generates a random number between two specified values.
ROUND, ROUNDUP, ROUNDDOWN: Excel functions used to round numbers; ROUND rounds to the nearest digit, ROUNDUP always rounds up, and ROUNDDOWN always rounds down.
INT, TRUNC, ROUND: The Excel INT function returns the integer part of a number, TRUNC cuts off the decimal places without rounding, and ROUND rounds to a specified number of digits.
MROUND: Excel’s MROUND function rounds a number to the nearest specified multiple.
FLOOR, CEILING: Excel FLOOR rounds a number down to the nearest multiple, while CEILING rounds a number up.
ODD, EVEN: These Excel functions round a number up to the nearest odd or even integer, respectively.
MOD: The Excel MOD function returns the remainder after division, useful for working with cycles or grouping data.
SUM, AutoSum, Alt + Plus Sign, Quick Analysis: Excel's SUM function adds a range of numbers, AutoSum quickly applies the SUM function, Alt + Plus Sign is a shortcut for AutoSum, and Quick Analysis provides options to apply sum functions with one click.
Running Total Formula: Excel’s Running Total Formula helps you track cumulative totals over a series of rows or columns.
SUMIF: Excel’s SUMIF function adds up cells that meet a specific condition.
SUMIFS: The Excel SUMIFS function extends SUMIF by allowing multiple criteria for summing data across ranges.

Logical Functions

AND: The Excel AND function checks if all specified conditions are true. It returns TRUE if all arguments evaluate to TRUE and FALSE otherwise.
IF: The Excel IF function allows you to create conditional statements. It returns one value if a condition is met and another if it’s not.
OR: The Excel OR function evaluates multiple conditions and returns TRUE if at least one condition is true, and FALSE if all conditions are false.
IFS: The Excel IFS function tests multiple conditions in a sequence and returns a corresponding value for the first true condition.
Nested IF: Nested IF in Excel is a technique where multiple IF functions are combined to test more than one condition, returning different outcomes based on the results.
ISEVEN: The Excel ISEVEN function checks if a number is even. It returns TRUE for even numbers and FALSE for odd numbers.
ISODD: The Excel ISODD function determines if a number is odd. It returns TRUE for odd numbers and FALSE for even numbers.
ISBLANK: The Excel ISBLANK function checks if a specified cell is empty and returns TRUE if the cell is blank.
ISFORMULA: The Excel ISFORMULA function verifies if a cell contains a formula. It returns TRUE if the cell has a formula, otherwise FALSE.
ISERROR: The Excel ISERROR function checks if a cell contains any error, returning TRUE for errors and FALSE for non-errors.
IFERROR: The Excel IFERROR function handles errors by returning a specified value or message if an error is found in a formula.

Statistical Functions

AVERAGE: The Excel AVERAGE function calculates the mean of a range of numbers, providing a central value.
MEDIAN: The Excel MEDIAN function returns the middle number in a set of data, ensuring accurate analysis of skewed distributions.
MODE: The Excel MODE function identifies the most frequently occurring value in a dataset, highlighting trends or common figures.
COUNT: The Excel COUNT function counts the number of cells containing numeric data, helping track quantities within a range.
COUNTA: The Excel COUNTA function counts non-empty cells, including those with text, numbers, or formulas.
COUNTBLANK: The Excel COUNTBLANK function counts the number of empty cells in a specified range, useful for data analysis and cleanup.
COUNTIF: The Excel COUNTIF function counts cells that meet a single condition, ideal for filtering data based on criteria.
COUNTIFS: The Excel COUNTIFS function counts cells that meet multiple conditions across ranges, allowing for more complex data analysis.
MAX, MIN: The Excel MAX and MIN functions find the largest and smallest values in a dataset, helping to quickly identify extremes.

Text Functions

UPPER, LOWER, PROPER: In Excel, these functions convert text to uppercase, lowercase, or proper case (capitalizing the first letter of each word).
LEN: This Excel function returns the number of characters in a text string.
TRIM: Removes any extra spaces from text in Excel, leaving only single spaces between words.
Concatenation using the Ampersand symbol (&): In Excel, this combines multiple text values into one using the & symbol.
LEFT, RIGHT, MID: These Excel functions extract specific parts of a text string—LEFT retrieves characters from the start, RIGHT from the end, and MID from the middle.
TEXTBEFORE: This Excel function extracts text that appears before a specified delimiter in a string.
TEXTAFTER: In Excel, this extracts text that appears after a specific delimiter in a string.
Text to Columns: This Excel feature splits text into separate columns based on a delimiter like commas or spaces.
TEXTJOIN: Combines text strings with a specified delimiter in Excel, skipping any empty cells.
CONCATENATE: An Excel function that merges multiple text strings into one, an older method similar to using &.
TEXTSPLIT: In Excel, this function splits a text string into an array based on a specified delimiter.
TEXT (including Leading Zeros and Padding Zeros): This Excel function formats numbers as text, useful for preserving leading or padded zeros.
TYPE: In Excel, this function returns the data type of a value, such as text, number, or error.
VALUE: Converts text that appears as a number into an actual numeric value in Excel.
T: Returns the text from a value, or an empty string if the value is not text, in Excel.

Lookup Functions

XLOOKUP: XLOOKUP is a powerful function that allows users to search for a value in a range or array and return a corresponding value from another range, offering more flexibility than VLOOKUP or HLOOKUP in Excel.
UNIQUE: The UNIQUE function in Excel is used to extract unique values from a range or array, helping users eliminate duplicates and simplify data analysis.
TRANSPOSE: TRANSPOSE in Excel allows users to switch the orientation of data, converting rows into columns or columns into rows, making data organization and presentation more flexible.
VSTACK, HSTACK: VSTACK and HSTACK functions in Excel enable users to vertically or horizontally combine ranges or arrays, making it easy to merge data for analysis without needing manual adjustments.

Who this course is for:

Anyone looking for a deeper understanding of Microsoft Excel.

Microsoft Excel: Excel Beginner Basics, Formulas & Functions

What you'll learn

Explore related topics

Course content

Getting to know Excel25 lectures • 43min

Entering and Editing Data in Excel28 lectures • 1hr 14min

Excel Worksheet Operations18 lectures • 30min

Working with Ranges in Excel16 lectures • 53min

Formatting in Excel7 lectures • 27min

Saving and Password Protection7 lectures • 12min

Printing in Excel7 lectures • 19min

Intro to Excel Formulas, Functions, and Cell References8 lectures • 26min

Math Functions in Excel15 lectures • 41min

Logical Functions in Excel21 lectures • 1hr 2min

Requirements

Description

Who this course is for: