Mastering Microsoft Excel 2019 & 365 Training Tutorial
0.0 (0 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
8 students enrolled

Mastering Microsoft Excel 2019 & 365 Training Tutorial

A Complete Beginner's Guide to Excel
0.0 (0 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
8 students enrolled
Created by TeachUcomp, Inc.
Last updated 8/2019
English
English [Auto]
Current price: $13.99 Original price: $19.99 Discount: 30% off
5 hours left at this price!
30-Day Money-Back Guarantee
This course includes
  • 8.5 hours on-demand video
  • 1 downloadable resource
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
Training 5 or more people?

Get your team access to 4,000+ top Udemy courses anytime, anywhere.

Try Udemy for Business
What you'll learn
  • Create both basic and advanced spreadsheets
  • Create complex formulas
  • Chart and graph data
  • Create PivotTables and PivotCharts
  • Audit worksheets
  • Manage security features
  • Much more!
Requirements
  • Good command of operating system
  • Basic Microsoft Office skills helpful (Especially Word)
Description

Learn Microsoft Excel with this comprehensive Excel tutorial from TeachUcomp, Inc. Mastering Excel Made Easy features 212 video lessons with over 10 hours of introductory through advanced instruction. Watch, listen and learn as your expert instructor guides you through each lesson step-by-step. During this media-rich learning experience, you will see each function performed just as if your instructor were there with you. Reinforce your learning with the text of our printable classroom instruction manual (453 pages) with additional images and practice exercises. Our Excel tutorial shows how to effectively create and format spreadsheets, charts, pivot tables and much more.

Whether you are completely new to Excel or upgrading from an older version, this Excel tutorial empowers you with the knowledge and skills to become a proficient user. Our Excel tutorial is based on years of classroom training experience and teaching techniques. This lets us create an Excel tutorial that is easy-to-use. You can also customize it to meet your personal learning needs. Just launch the Excel tutorial, click a video lesson or open a manual, and you’re on your way to mastering Excel!

Who this course is for:
  • Students new to Microsoft Excel
  • Office employees
  • Executives
  • Managers
  • Assistants
  • Accounting professionals
Course content
Expand all 213 lectures 08:16:47
+ Course Introduction
1 lecture 01:03

This lecture provides a brief summary of the topics covered throughout the course and offers suggestions for further reading and learning materials.

Preview 01:03
+ Getting Acquainted with Excel
15 lectures 23:03

Microsoft Excel is a spreadsheet program you use to organize and manipulate information. With Excel, you can create worksheets that can instantly perform complex calculations. When the numbers in a worksheet change, Excel recalculates the answers automatically! Learn this and more during this lecture.

Preview 01:30

The picture below shows the user interface in Excel 2019 or 365 after creating a new, blank workbook. A workbook is the default file type in Excel, much like a document is the default file type in Word. A workbook can contain multiple worksheets that store and manipulate data in their columns and rows. By default, Excel gives you one worksheet in a new, blank workbook. Learn this and more during this lecture.

Preview 02:01

The Title Bar is the bar that runs across the top of the application window. The name of the workbook you are working on appears in the center of this bar. At the right end of the Title Bar is a button group. There are four buttons in this button group. Learn this and more during this lecture.

Preview 01:31

The primary tool available for you to use in Excel is the Ribbon. The Ribbon lets you perform all the commands available in the program. The Ribbon is divided into tabs. Within these tabs are different button groups of commands. You can access the commands in each button group by using either the buttons, boxes, or menus available in the button group. Learn this and more during this lecture.

Preview 02:01

Unlike other primary tabs in the Ribbon, clicking the “File” tab in the Ribbon opens a view of the file called the “Backstage View.” This view lets you perform all your file management. This includes functions like saving a file, opening an existing file, and creating a new file. Learn this and more during this lecture.

Preview 01:25

The scroll bars run both vertically and horizontally in the lower-right corner of a workbook. You can click the arrows at the ends of the scroll bars to scroll through the workbook in that direction. Learn this and more during this lecture.

Preview 00:20

The Quick Access toolbar appears above the Ribbon, by default. To place it below the Ribbon, if desired, click the “Customize Quick Access Toolbar” button at the right end of the toolbar. Then select the “Show Below the Ribbon” command from the drop-down menu that appears. Learn this and more during this lecture.

Preview 02:50

Because of the increased use of tablets, Excel contains a mode that gives you easier access to the buttons and commands in the Ribbon and Quick Access toolbar. This mode is called touch mode. Learn this and more during this lecture.

Touch Mode
01:13

The Formula Bar appears underneath the Ribbon. At its left end is the “Name Box,” which displays the address of the currently selected cell in your workbook. Learn this and more during this lecture.

The Formula Bar
00:47

The workbook window is the window in which you will perform almost all your work. You enter data into the worksheets within the workbook window. The names of the worksheets in a workbook appear on tabs in the lower-left corner of the workbook. A workbook stores information much like a database table does. Learn this and more during this lecture.

The Workbook Window
02:45

At the bottom of the application window is the Status Bar. This tool provides you with information about your workbook. One of the most important status indicators in Excel is the “Cell Mode” status indicator that appears at the far left end of the Status Bar. There are three main cell modes you will see: “Ready,” “Edit,” or “Enter.” Learn this and more during this lecture.

The Status Bar
01:56

The workbook view buttons are a group of three buttons that appear towards the lower-right corner of the application in the Status Bar. You can click these buttons to change the working view of your workbook. By default, Excel opens in “Normal” view, which is the view most commonly-used for standard workbook creation. Learn this and more during this lecture.

The Workbook View Buttons
01:08

The Zoom slider appears in the Status Bar towards the lower-right corner of the application window. You use it to change the workbook’s magnification level. This does not modify the workbook at all. It only changes your perception of how close or far away the workbook’s contents appear onscreen. Learn this and more during this lecture.

The Zoom Slider
01:22

When you select text in a cell and hold your mouse pointer over it, a small dimmed-out toolbar appears next to the selection. This is the Mini toolbar. Learn this and more during this lecture.

The Mini Toolbar
00:22

A keyboard shortcut lets you press a combination of keyboard characters to execute a command, instead of clicking a button in the Ribbon or the Quick Access toolbar. While you may never use them, many users find it tiresome to reach for their mouse to execute a command function. Learn this and more during this lecture.

Keyboard Shortcuts
01:52
+ File Management
15 lectures 28:17

When you first open Excel, a startup screen appears. This screen lets you create a new workbook. If you already have opened a workbook, you can view a similar screen by clicking the “File” tab within the Ribbon. Learn this and more during this lecture.

Creating New Workbooks
00:33

When you save a workbook for the first time, you must use the “Save As” command to choose where to save the file and what to name it. To do this, click the “File” tab within the Ribbon. Then click the “Save As” command in the command panel at the left side of the backstage view. Learn this and more during this lecture.

Saving Workbooks
04:58

To close a workbook in Excel, click the “File” tab within the Ribbon. Then click the “Close” command at the left side of the backstage view. Learn this and more during this lecture.

Closing Workbooks
00:27

After initially opening Excel, a list of both “pinned” and recently-opened workbooks appears in the startup screen. A “pinned” file is simply one which you have chosen to pin to the “Pinned” section for easy access, regardless of how long it has been since it was opened. Learn this and more during this lecture.

Opening Workbooks
05:15

Excel lets you try to recover unsaved workbook files. To see if Excel has automatically saved a copy of an unsaved workbook on which you were working, select the “File” tab in the Ribbon. Then click the “Open” command at the left side of the backstage view. In the area to the right of the command panel, select the “Recent” command. Learn this and more during this lecture.

Recovering Unsaved Workbooks
01:06

Excel gives you tools that help you manage your workspace when you have opened multiple workbooks. In Excel, you can have many workbooks open at once to do things like copy and paste text between them, for example. Learn this and more during this lecture.

Opening a Workbook in a New Window
00:43

To arrange all open workbook windows in Excel onscreen when you have multiple workbooks open, click the “Arrange All” button in the “Window” button group on the “View” tab in the Ribbon. In the “Arrange Windows” dialog box that appears, select an option button to choose the layout for the opened workbooks. Learn this and more during this lecture.

Arranging Open Workbook Windows
01:40

You can use the “Freeze Panes” button in Excel to view data in two separate sections of a long worksheet simultaneously. You can freeze one or two sections of a worksheet to prevent scrolling and then scroll the unfrozen worksheet section to view two different sections of a worksheet at the same time. Learn this and more during this lecture.

Freeze Panes
01:27

The “Split” command in Excel is another useful tool for working with longer worksheets. This command lets you split the Excel worksheet into different panes. Each pane has its own horizontal and vertical scroll bars, so you can scroll each pane separately to view information from different sections of the worksheet. Learn this and more during this lecture.

Split Panes
02:12

To hide the current workbook window, click the “Hide” button in the “Window” button group on the “View” tab in the Ribbon. Learn this and more during this lecture.

Hiding and Unhiding Workbook Windows
00:27

To compare multiple open workbooks side by side with each workbook taking an equal portion of the screen, click the “View Side by Side” button in the “Window” button group on the “View” tab in the Ribbon. After clicking the “View Side by Side” button, you can then enable or disable the “Synchronous Scrolling” button by clicking it to toggle it on or off. Learn this and more during this lecture.

Comparing Open Workbooks
01:01

At the far right end of the “Window” button group on the “View” tab in the Ribbon is the “Switch Windows” button. You can click this button to view a drop-down menu that shows the titles of all the opened workbooks in Excel. Learn this and more during this lecture.

Switching Open Workbooks
00:21

In Excel, full screen mode maximizes the amount of worksheet area by removing everything else from the Excel window. To switch to full screen mode in Excel, click the “Ribbon Display Options” button in the button group at the right end of the Title Bar and then select the “Auto-hide Ribbon” command. Learn this and more during this lecture.

Switching to Full Screen View
00:38

Excel saves its workbooks using a default file format that provides a smaller file size and better security than the format used in versions of Excel prior to Excel 2007. If you will be sharing your workbook collaboratively with others who need to use and edit the workbook with an old version of Excel, note that some of the newer features are not supported by old versions of Excel. Learn this and more during this lecture.

Working with Excel File Formats
03:31

Excel automatically enables the “AutoSave” feature when you save a workbook using the newest file formats to an online destination, like OneDrive or SharePoint. AutoSave saves the file to the online location every few seconds as you work. Learn this and more during this lecture.

AutoSave Online Workbooks
03:58
+ Data Entry
9 lectures 19:31

When you open a new workbook, the active cell is always cell “A1.” You can use the mouse to click into any cell to make it the active cell. You can also use the keyboard to move the active cell cursor. Learn this and more during this lecture.

Selecting Cells
01:39

In Excel, any combination of numbers and letters entered into a cell is treated as a text entry, not a numeric entry. Text entries use a left-alignment, by default, in the cells into which they are entered. As you enter information into a cell, remember you must leave the cell to finish data entry. Learn this and more during this lecture.

Entering Text into Cells
01:51

In Excel, for a cell to be considered a “numeric” entry, it can only contain digits and decimals. There can be no text in numeric entries. Ideally, you should enter numbers in a plain and raw format, that is- without any formatting, like dollar signs. Learn this and more during this lecture.

Entering Numbers into Cells
02:25

Excel contains a handy feature called AutoComplete that helps speed up repetitive text entry in columns. If you have a column of unbroken text entries, meaning no empty cells, Excel offers to “fill-in” the next entry you make at the bottom of the column as you type your entries down the column. Learn this and more during this lecture.

AutoComplete
00:49

Excel contains another handy feature called “Pick From Drop-down List” that also helps speed up repetitive text entry in columns. If you have a column of unbroken text entries, meaning no empty cells, Excel can display a drop-down menu of the previous text entries within that column from which you can pick! That way, you won’t have to retype duplicate entries and risk misspelling them. Learn this and more during this lecture.

Pick from Drop-Down List
00:42

The “Flash Fill” feature lets you automatically fill-in values within a column with information entered into an adjacent column. This feature is most useful when dealing with data that has a consistent data entry pattern in the column whose values are referenced by the second, flash-filled column. Learn this and more during this lecture.

Flash Fill
03:28

Many features of Excel depend on referring to a group of cells. In Excel, a group of cells is called a range. You use ranges in many ways in Excel. Learn this and more during this lecture.

Selecting Ranges
05:03

After selecting a range, you can easily enter new information into the selected cell range. There is one cell in a selected range that is white. That cell is the active cell within the selected range or ranges. You can enter information into this cell, as usual. Learn this and more during this lecture.

Ranged Data Entry
00:50

AutoFill is a feature that lets Excel automatically fill-in a repeating pattern you establish. Learn this and more during this lecture.

Using AutoFill
02:44
+ Creating Formulas
9 lectures 16:49

During this lecture we will show an example of the syntax used by a common ranged Excel formula.

Ranged Formula Syntax
01:24

During this lecture we will show an example of the syntax used by a common simple Excel formula.

Simple Formula Syntax
00:51

You use formulas to perform mathematical functions on cells. There are two basic ways of writing formulas: “ranged syntax” or “simple syntax.” A “syntax” is simply a way of expressing or writing something. It is important to note that these two syntaxes are not mutually exclusive! Learn this and more during this lecture.

Writing Formulas
04:39

People often use Excel to sum columns and rows of data. This happens so frequently that Excel includes a feature called AutoSum that automatically performs a selected function, like SUM, on a selected column or row of uninterrupted (no blank) cells. This saves you time in creating basic formulas. Learn this and more during this lecture.

Using AutoSum
01:58

You can insert functions into a selected cell in one of several ways. You could click the “Insert Function” button in the “Function Library” button group on the “Formulas” tab in the Ribbon. Learn this and more during this lecture.

Inserting Functions
03:14

You can edit a formula’s cell range reference after creating it, if needed. To do this, double-click the formula cell. It then displays itself as the formula, instead of the answer to the formula, in the worksheet. It also places a blue shading and border around the first cell range referenced by the formula. Learn this and more during this lecture.

Editing a Range
01:08

If you mistype a formula, Excel may automatically correct your formula syntax. Excel may show a dialog box that offers to correct a mistake for you if you try to exit a cell that contains an incorrect formula. Learn this and more during this lecture.

Formula AutoCorrect
00:29

AutoCalculate is a tool that shows the results of simple functions in the Status Bar without requiring you to type a formula. The functions for which you can automatically show results are “Average,” “Count,” and “Sum.” Learn this and more during this lecture.

AutoCalculate
00:59

In Excel, some older formula functions have been replaced with new versions. This occurs for many reasons, from increasing their accuracy and providing new features to simply renaming them to make them easier to use or to better reflect their purpose. These new, more accurate functions have different names than their older counterparts and may also require different arguments. Learn this and more during this lecture.

Function Compatibility
02:07
+ Copying and Pasting Formulas
5 lectures 08:59

In Excel, you can cut or copy cell data and then paste it into other cells. Cells that contain numeric or text entries can be moved wherever you want. The only trick is when you need to cut or copy formula cells. Learn this and more during this lecture.

Relative References and Absolute References
03:40

To copy or cut data, first select the cells to copy or cut. Then click either the “Copy” or “Cut” button in the “Clipboard” button group on the “Home” tab in the Ribbon, as needed. The selected cells then appear with a blinking marquee around them to show they have been cut or copied. The cut or copied data is then placed onto the clipboard, which is memory allocated for cut or copied data. Learn this and more during this lecture.

Cutting, Copying, and Pasting Data
03:04

You can also use the AutoFill handle to copy formulas across rows and down columns. Because a formula is not the start of a standard series, when you use the AutoFill handle on a selected formula cell, it copies the formula to the range of cells over which you click and drag. Learn this and more during this lecture.

AutoFilling Cells
00:46

The “Undo” command is one of the most important functions available. It lets you reverse the last command you performed. The drop-down arrow next to the “Undo” button in the Quick Access toolbar contains a drop-down menu of the last few actions performed. You may click to reverse any actions in this drop-down menu. Learn this and more during this lecture.

The Undo Button
00:49

The “Redo” command, next to the “Undo” button in the Quick Access toolbar, is the inverse of the “Undo” command. It redoes an undone action. This helps if you accidentally click the “Undo” button too many times when trying to undo a mistake. Learn this and more during this lecture.

The Redo Button
00:40
+ Columns and Rows
4 lectures 08:02

A mistake many new Excel users make is thinking that because they have selected all the cells they can see in a column or across a row, that they have selected the entire column or row. Selecting a few visible cells onscreen is not the same as selecting entire columns or rows. Learn this and more during this lecture.

Selecting Columns and Rows
01:33

You can adjust the width of columns to correct the display of longer cell entries. You can also adjust row height to accommodate larger fonts. Learn this and more during this lecture.

Adjusting Column Width and Row Height
01:53

You can hide columns and rows that contain sensitive data you need for formulas but don’t want to show in a worksheet, like salary information, for example. Hiding a column or row conceals its display, but still uses its data for formula calculations. Learn this and more during this lecture.

Hiding and Unhiding Columns and Rows
01:10

Be careful when inserting columns and rows to ensure the insertion doesn’t create problems for existing worksheet formulas. Formulas most often adjust their cell references to accommodate the insertion, but more complex cell references may not be adjusted. Learn this and more during this lecture.

Inserting and Deleting Columns and Rows
03:26
+ Formatting Worksheets
4 lectures 10:19

You can easily format selected cells using the buttons within the “Font” button group, the “Alignment” button group, and the “Number” button group on the “Home” tab of the Ribbon. To do this, select the cells to format. Then click the desired formatting button within these button groups. Learn this and more during this lecture.

Formatting Cells
02:57

The “Format Cells” dialog box in Excel lets you control all cell formatting options for the currently-selected worksheet cells. The “Format Cells” dialog box has six tabs that let you change cell formatting options. The sixth tab, “Protection,” is related to worksheet security and has no bearing on cell formatting. The other five tabs in this dialog box that control cell formatting are: “Number,” “Alignment,” “Font,” “Border,” and “Fill.” Learn this and more during this lecture.

The Format Cells Dialog Box
06:00

To clear all formatting from selected cells, first select the worksheet cells from which to remove all formatting. Then click the “Clear” button in the “Editing” button group on the “Home” tab of the Ribbon. Learn this and more during this lecture.

Clearing All Formatting from Cells
00:21

To copy only the formatting of a selected cell or cells and then paste only the copied formatting to other workbook cells, first select the cell or cell range that contains the formatting to copy. Then click the “Format Painter” button in the “Clipboard” button group on the “Home” tab of the Ribbon. Then click and drag over the cell or cell range to which to apply the formatting you just copied to apply it. After doing that, the “Format Painter” turns itself off automatically. Learn this and more during this lecture.

Copying All Formatting from Cells to Another Area
01:01
+ Worksheet Tools
6 lectures 07:08

Many people must insert or delete worksheets in a workbook when using Excel. To insert a single new worksheet to the right of the currently-selected worksheet, click the “New Sheet” button at the right end of the spreadsheet name tabs. Learn this and more during this lecture.

Inserting and Deleting Worksheets
01:09

When you select multiple worksheets at the same time, changes you make to one worksheet are applied to all the other selected worksheets. You can select multiple adjacent or non-adjacent worksheets in a workbook. Learn this and more during this lecture.

Selecting Multiple Worksheets
01:03

To move from one worksheet to another, click the worksheet name tab of the worksheet to view from the worksheet name tabs in the lower-left corner of the workbook. Learn this and more during this lecture.

Navigating Worksheets
01:09

You can rename the worksheets in a workbook so they reflect their content. By default, the worksheets in a workbook are named “Sheet1,” “Sheet2,” “Sheet3,” and so on. As you add new worksheets, the same naming convention applies to the new worksheets. You can rename worksheets to almost anything you want. You can use spaces in worksheet names, if desired. Learn this and more during this lecture.

Renaming Worksheets
00:48

You can change the color of the worksheet tabs, if desired. To do this, first select the worksheet tab whose tab color you wish to change. Next, click the “Format” button in the “Cells” button group on the “Home” tab of the Ribbon. Learn this and more during this lecture.

Coloring Worksheet Tabs
00:59

One way to copy and paste (copy) or cut and paste (move) entire workbook worksheets is by using the worksheet name tabs. To copy or cut a selected worksheet or group of worksheets by using the worksheet name tabs, first select the worksheets to copy or move. Then, to copy the selected worksheets, hold down the “Ctrl” key on your keyboard. Then, for either method, click and drag the first worksheet tab you selected (which is the active worksheet tab, whose title appears in color), left or right and then release it where you want to paste the selected worksheets. Learn this and more during this lecture.

Copying or Moving Worksheets
02:00
+ Setting Worksheet Layout
7 lectures 11:20

When you get past the end of a printable page in Excel, as defined by your current page setup, Excel inserts an automatic page break. Sometimes these automatic page breaks occur in places where you would rather not have them occur. Learn this and more during this lecture.

Using Page Break Preview
01:18

You can click the “Page Layout” button in the “Workbook Views” button group on the “View” tab of the Ribbon to see how your workbook will appear when printed. In this mode, you can see the margins and the allowable room for header and footer data, along with the worksheet data. Learn this and more during this lecture.

Using the Page Layout View
00:39

To adjust the settings of the workbook for printing, open the “Page Setup” dialog box by clicking the “Page Setup” dialog box button in the lower-right corner of the “Page Setup” button group on the “Page Layout” tab of the Ribbon. The “Page Setup” dialog box lets you change your worksheet’s printed layout. Learn this and more during this lecture.

Opening the Page Setup Dialog Box
00:34

To adjust the workbook’s page settings before printing it, open the “Page Setup” dialog box by clicking the “Page Setup” dialog box button in the lower-right corner of the “Page Setup” button group on the “Page Layout” tab of the Ribbon. Then click the “Page” tab within the dialog box. Learn this and more during this lecture.

Page Settings
01:37

To set worksheet margins before printing, open the “Page Setup” dialog box by clicking the “Page Setup” dialog box button in the lower-right corner of the “Page Setup” button group on the “Page Layout” tab of the Ribbon. Then click the “Margins” tab within the dialog box. Learn this and more during this lecture.

Setting Margins
00:52

To create headers and footers in a workbook before printing it, open the “Page Setup” dialog box by clicking the “Page Setup” dialog box button in the lower-right corner of the “Page Setup” button group on the “Page Layout” tab of the Ribbon. Then click the “Header/Footer” tab within the dialog box. Learn this and more during this lecture.

Creating Headers and Footers
03:42

To adjust the worksheet settings before printing it, open the “Page Setup” dialog box by clicking the “Page Setup” dialog box button in the lower-right corner of the “Page Setup” button group on the “Page Layout” tab in the Ribbon. Then click the “Sheet” tab within the dialog box. Learn this and more during this lecture.

Sheet Settings
02:38