Mastering Excel for Microsoft 365 Training Tutorial

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.

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.

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.

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.

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.

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.

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.

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.

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

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

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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 lower-right corner of the far-right button group in the Ribbon and then select the “Full screen mode” command. To temporarily show the Ribbon again in full screen mode in Excel, click the ellipsis mark button (…) in the upper-right corner of the screen. To exit full screen mode, click the “Ribbon Display Options” button again and then choose the “Always show Ribbon” or “Show tabs only” choice, as desired.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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

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

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

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.