Mastering Microsoft Excel 2016 Made Easy Training Tutorial

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

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 where the data is stored and manipulated in the columns and rows. In Excel, you are given one worksheet by default within your new, blank workbook. The default name of the worksheet is “Sheet1” and is displayed on the worksheet tab in the lower left corner of the window. 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 will be displayed 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. They are, from left to right, “Ribbon Display Options,” “Minimize,” “Maximize/Restore Down,” and “Close.” These buttons affect the display of the application window. Learn this and more during this lecture.

The primary tool that is available for you to use in Excel is the Ribbon. This object allows you to perform all of the commands available in the program. The Ribbon is divided into tabs. Within these tabs are different button groups of commands. The commands in each button group can be accessed either through the use of buttons, boxes, or menus that are available within the group. Learn this and more during this lecture.

You can click the “File” tab in the Ribbon to open a view of the file called the “Backstage View.” In this view, you can perform all of your file management. This includes performing functions such as saving your file, opening an existing file, or creating a new file. Learn this and more during this lecture.

The scroll bars run both vertically and horizontally in the bottom right corner of your 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 is located above the Ribbon, by default. However, you can also place it below the Ribbon, if desired, by clicking the “Customize Quick Access Toolbar” button at the right end of the toolbar and then selecting the “Show Below the Ribbon” command. You can reset it to its default location by clicking the same “Customize Quick Access Toolbar” button and then choosing the “Show Above the Ribbon” command. Learn this and more during this lecture.

Because of the increased use of tablets, Excel contains a mode that allows for easier access to the buttons and commands within the Ribbon and Quick Access toolbar. This mode is called touch mode. When you enter touch mode within Excel, the Ribbon and Quick Access toolbar are enlarged and extra space is added around the buttons and commands within the Ribbon and Quick Access toolbar so you can more easily access them on your touch-based tablet. Learn this and more during this lecture.

The Formula Bar is located underneath the Ribbon. At its left end is the “Name Box” which displays the address of the currently selected cell in your workbook. To the right of that is the “Insert Formula” button which looks like the function (fx) sign. Learn this and more during this lecture.

The workbook window is the window in which you will perform almost all of your work. You enter data into the worksheets within the workbook window. The names of the worksheets within a workbook are shown 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 is shown 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 located 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 will open in “Normal” view, which is the view most commonly used for standard workbook creation. Unless otherwise specified by the lesson, you can assume that the workbook view referenced in the lectures of this tutorial will refer to the “Normal” view. Learn this and more during this lecture.

In the lower right corner of the application window, in the Status Bar, you can see the Zoom slider. You use this slider to change the magnification level of the workbook. This does not modify the workbook in any way. It only changes your perception of how close or far away the workbook appears onscreen. Learn this and more during this lecture.

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

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

When you first open the Excel application, you will be presented with the startup screen that allows you to 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 and then selecting the “New” command at the left side of the backstage view. Learn this and more during this lecture.

When you save a workbook for the first time, you must use the “Save As” command so you can 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 shown at the left side of the backstage view. Learn this and more during this lecture.

To open a workbook, you need to know where the workbook is located. When you initially open Excel, you can see a listing of recently opened workbooks shown in the panel at the left side of the startup screen under the “Recent” section. To open one of these workbooks, click its name in the panel to reopen it. Learn this and more during this lecture.

Excel allows you to try to recover unsaved workbook files. To see if Excel has automatically saved a copy of an unsaved workbook you were working on, select the “File” tab within the Ribbon and 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 provides you with tools to assist you in managing your workspace when you have multiple workbooks open. In Excel, you can have many workbooks open at a time to perform functions like copying and pasting text between them, for example. Learn this and more during this lecture.

When you have multiple workbooks open, you can click the “Arrange All” button in the “Window” button group on the “View” tab in the Ribbon to arrange all open workbook windows onscreen. In the “Arrange Windows” dialog box that appears, select an option button to choose the organizational layout for the open workbooks. The options are “Tiled,” “Horizontal,” “Vertical,” or “Cascade.” Select the desired option and click the “OK” button to apply the selected arrangement to the open workbooks. Learn this and more during this lecture.

You can use the “Freeze Panes” button 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 section of the worksheet to view two different sections of a worksheet at the same time. 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 you have clicked the “View Side by Side” button, you can use the “Synchronous Scrolling” button. You can click the “Synchronous Scrolling” button in the “Window” button group on the “View” tab in the Ribbon to toggle the ability to simultaneously scroll multiple workbooks that are being compared side by side on or off, as desired. 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. Learn this and more during this lecture.

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.

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 will be treated as a text entry- not a numeric entry. Text entries will default to using a left alignment in the cells into which they are entered. As you enter information into a cell, remember that you must leave the cell to finish 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 can help speed up repetitive text entry within columns. If you have a column of unbroken text entries, meaning no empty cells, Excel will offer to “fill-in” the next entry that 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 little feature called “Pick From Drop-down List” that can 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! Learn this and more during this lecture.

Starting in Excel 2013, you can use the “Flash Fill” feature to 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 are dependent upon making a reference to a group of cells. In Excel, a group of cells is called a range. Ranges are used in many ways in Excel. Learn this and more during this lecture.

Once you have a range selected, you can easily enter new information into the selected range of cells. Notice that there is one cell within the selected range that is white. That cell is the active cell within the selected range or ranges. Learn this and more during this lecture.

You use formulas to perform mathematical functions on cells. Learn this and more during this lecture.

There are two basic ways of writing formulas available: “ranged syntax” or “simple syntax.” Learn this and more during this lecture.

Much of the time, people use Excel to sum columns and rows of data. In fact it occurs so frequently that Excel has included 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” group on the “Formulas” tab in the Ribbon. You could click the “fx” (function) button in the Formula Bar. You could also select the “Insert Function…” command from the bottom of the drop-down list of functions that appears when you click on any of the function category buttons shown in the “Function Library” button group on the “Formulas” tab in the Ribbon. Whenever you do any one of these actions, you will launch the “Insert Function” dialog box. Learn this and more during this lecture.

You can edit the cell range reference of a formula after it has been created. To do this, just double-click on the formula cell. It will display itself as the formula instead of the answer to the formula, and it will place a blue border around the currently selected range in the formula. Learn this and more during this lecture.

AutoCalculate is a tool that displays the results of simple functions in the Status Bar without having to type a formula. The functions displayed by default are “Average,” “Count,” and “Sum.” Learn this and more during this lecture.

Starting in Excel 2013, some older formula functions have been replaced with new versions to increase their accuracy and to better reflect their purpose. These new, more accurate statistical 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 data from cells and then paste the data 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 cells that contain formulas. Learn this and more during this lecture.

To copy or cut data, first select the cells that you want to copy or cut and then press either the “Copy” or “Cut” buttons in the “Clipboard” group on the “Home” tab in the Ribbon. The cells that you have selected will appear with a blinking marquee around them to indicate that they have been cut or copied. The cut or copied data is then placed onto the clipboard, which is memory allocated to cut or copied data. Learn this and more during this lecture.

You can 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 will simply copy the formula across the range of cells that you select. Learn this and more during this lecture.

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

The “Redo” command, located next to the “Undo” button in the Quick Access toolbar, is the inverse of the “Undo” command. It will redo an action that was undone. This is valuable if you accidentally click the “Undo” button a few too many times when trying to correct a mistake that was made. Learn this and more during this lecture.

Many new Excel users make the mistake of thinking that because they have selected all of the cells that they can see within a column or across a row, 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. To adjust the width of columns, select the columns you want to adjust and then place your mouse cursor between the column headings- to the right of the selected column heading or column headings. Learn this and more during this lecture.

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

When you insert columns and rows in a worksheet, there are two rules that you should remember. Learn this and more during this lecture.

Note that in the “Home” tab of the Ribbon, you have the “Font” group, the “Alignment” group, and the “Number” group. These groups contain buttons that allow you to format the appearance of selected cells. To do this, just start by selecting the cells you want to format. Then click the desired button. Learn this and more during this lecture.

You can use the “Format Cells” dialog box in Excel to control all aspects of cell formatting for the currently selected cells in your worksheet. The “Format Cells” dialog box has six tabs that allow you to change cell properties. Learn this and more during this lecture.

You can clear all of the formatting from selected cells by first selecting the cells from which you want to remove all of the formatting. Learn this and more during this lecture.

To move from one worksheet to another, simply click the worksheet name tab of the worksheet that you want to view from the set of worksheet name tabs located in the lower left corner of the workbook. Learn this and more during this lecture.

You can rename the worksheets in your workbook so that they reflect their content. By default, the worksheets in a workbook are labeled “Sheet1,” “Sheet2,” “Sheet3” and so on. As you add new worksheets, the same naming convention is applied to the new worksheets. You can rename worksheets to practically anything that you want. You are allowed to use spaces in worksheet names. 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” group on the “Home” tab in the Ribbon. Roll your mouse pointer down to the “Tab Color” command. In the side menu that appears, click on the color that you want to apply to the worksheet tab. Learn this and more during this lecture.

One way to copy and paste entire worksheets in a workbook is by using the worksheet name tabs. To copy a selected worksheet or a group of worksheets first select the worksheets that you want to copy. Then hold down the “Ctrl” key on your keyboard. Learn this and more during this lecture.

In Excel, when you get past the end of a printable page, as defined by your current page setup, Excel will insert an automatic page break. Sometimes these automatic page breaks occur in places where you would rather not have them occur. They may leave data on a second page, orphaned from the rest of the worksheet. Learn this and more during this lecture.

You can click the “Page Layout” button in the “Workbook Views” group on the “View” tab in 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 information from your worksheet. Learn this and more during this lecture.

To adjust the page settings of the 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” group on the “Page Layout” tab in 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” group on the “Page Layout” tab in the Ribbon. Then click the “Margins” tab within the dialog box. Learn this and more during this lecture.

To create the headers and footers within the 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” group on the “Page Layout” tab in 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” 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.