Microsoft Excel 2016 Step-by-Step Guide

[Pages:84]MICROSOFT EXCEL 2016 STEP-BY-STEP GUIDE

ANDIE PHILO;MIKE ANGSTADT

MONTGOMERY COUNTY-NORRISTOWN PUBLIC LIBRARY

MICROSOFT EXCEL 2016: STEP-BY-STEP GUIDE

Revised: 2/20/2020

Table of Contents

INTRODUCTION ..................................................................4 SORTING DATA ....................................................................33

EXPLORING THE EXCEL 2016 ENVIRONMENT ......................5

Custom Sort ................................................................ 34 Add a Sort Level .......................................................... 35

TITLE BAR............................................................................. 5 USING A FORMULA TO CALCULATE OUR SAVINGS ........................ 36

QUICK ACCESS TOOLBAR ......................................................... 5 PRECEDENCE OF OPERATIONS ................................................. 36

RIBBON................................................................................ 6 CONDITIONAL FORMATTING ................................................... 38

Active Tab..................................................................... 6 COMMENTS.........................................................................40

Contextual Tabs............................................................ 6 MANAGING WORKSHEETS......................................................43

Groups and Buttons...................................................... 6

Copying a worksheet................................................... 43

Buttons with Arrows..................................................... 7

Renaming a worksheet ............................................... 43

Dialogue Box Launcher................................................. 7

Moving worksheets.....................................................44

Ribbon Display Options button..................................... 8

Tab Color.....................................................................45

Dynamic Resizing ......................................................... 9

Inserting worksheets...................................................45

File Tab ....................................................................... 10 REFERENCING DATA ON ANOTHER WORKSHEET .......................... 45

WORKSPACE ....................................................................... 11 DATA ENTRY TIPS ................................................................. 46

Status Bar ................................................................... 12

Auto fill and resize multiple columns to same width .. 46

Current Information ................................................... 12

Copy and Paste between worksheets ......................... 46

Views .......................................................................... 12

Entering a worksheet reference..................................46

Zoom Slider................................................................. 12

AutoSum and fill formula ............................................ 47

Customization ............................................................ 12

Line Break within a cell ............................................... 48

EXCEL CURSORS................................................................... 13

Wrapping text ............................................................. 48

CREATING AN EXCEL DOCUMENT AND SAVING IT.............14

ENTERING A FUNCTION ? AVERAGE.......................................... 48 FREEZE PANES ..................................................................... 49

CREATING AN EXCEL FILE ....................................................... 14 PREPARING A SAVE TO LOCATION ? A USB DEVICE..................... 14

SAVING A WORKBOOK IN DIFFERENT FORMATS...............50

SAVING THE FILE .................................................................. 15 OLDER EXCEL FILE FORMAT (.XLS) ........................................... 50

SAFE REMOVAL OF A USB DEVICE........................................... 17 PDF ..................................................................................51

CREATING A SIMPLE BUDGET SPREADSHEET.....................19 CREATING A CHART...........................................................53

MERGE AND CENTER CELLS.................................................... 19 INSERT A CHART ................................................................... 53

ENTER DATA AND NAVIGATE BETWEEN CELLS ........................... 20 SELECT DATA FOR CHART ........................................................ 53

FORMAT CELLS .................................................................... 20 FORMAT THE CHART.............................................................. 54

RESIZE COLUMN .................................................................. 20 Using the ribbon ......................................................... 21

PRINTING A WORKSHEET ..................................................56

Double-click Method .................................................. 21 VIEWING THE PREVIEW..........................................................56

ENTER MORE DATA AND RESIZE COLUMNS............................... 22 SCALING A PRINTOUT............................................................. 57

APPLY CURRENCY STYLE FORMATTING ..................................... 23 CHANGING ORIENTATION OF A PRINTOUT.................................. 57

ENTER A SIMPLE FORMULA .................................................... 23 CREATING A HEADER AND FOOTER FOR A PRINTOUT .................... 59

USE A FUNCTION ................................................................. 24 PRINTING COMMENTS........................................................... 60

USE THE MOUSE TO EXPRESS A RANGE OF CELLS ....................... 25 AUTOSUM.......................................................................... 25

LINKING WORKBOOKS ......................................................61

ADD DATA TO A FORMATTED COLUMN .................................... 26 REFERENCING DATA FROM AN EXTERNAL WORKBOOK ................. 61

SPELL CHECK....................................................................... 26 MANAGING LINKED WORKBOOKS............................................ 62

ENHANCING THE BUDGET SPREADSHEET..........................27 NUMBER FORMATS (SUPPLEMENTAL) ..............................63

CELL REFERENCING............................................................... 27 FORMATTING AS TEXT ........................................................... 63

ADDING COLUMNS............................................................... 28 FORMATTING AS PERCENTAGE ................................................ 64

COPY A FORMULA FROM ONE CELL TO ANOTHER ....................... 28 FORMATTING DATES ............................................................. 64

WHAT THE ##?? ................................................................. 29 ENTER A NEW FORMULA AND COPY TO OTHER CELLS ................. 29

FILTERING (SUPPLEMENTAL CONTENT) .............................66

FORMULAS VIEW ................................................................. 30 INTRODUCTION .................................................................... 66

ADDING ROWS.................................................................... 31 FILTERING DATA...................................................................66

MOVING ROWS AND COLUMNS.............................................. 31

To Filter Data .............................................................. 66

TRACE ERRORS .................................................................... 32

To Add Another Filter..................................................68

MC-NPL Computer Lab ? 1001 Powell St ? Norristown, PA 19401 (610) 278-5100 x141 ? mcnplcomputerlab@ ? mc-

Page 2 of 83

MICROSOFT EXCEL 2016: STEP-BY-STEP GUIDE

Revised: 2/20/2020

To Clear a Filter .......................................................... 68 DATA VALIDATION (SUPPLEMENTAL CONTENT)................75

ADVANCED FILTERING........................................................... 69 Filtering Using Search................................................. 69

IF FUNCTION (SUPPLEMENTAL CONTENT).........................78

Advanced Text Filters ................................................. 70 IF STATEMENT WORKSHEET ................................................... 79

Advanced Date Filters ................................................ 71 TAX FORM WORKSHEET ........................................................ 82

To Use Advanced Number Filters: .............................. 71 CONDITIONAL FORMATTING ................................................... 83

TEXT TO COLUMNS (SUPPLEMENTAL CONTENT)...............73 MANIPULATE THE TAX FILE ..................................................... 84

Created & Maintained by:

Andrea Philo Mike Angstadt

MONTGOMERY COUNTY-NORRISTOWN PUBLIC LIBRARY NORRISTOWN, PENNSYLVANIA WWW.MC-

Note to Home Students:

This lesson plan will frequently refer to flash drives that we have our students use during class. Instead of saving files to a flash drive, you may save them to your computer's hard drive.

We preload these flash drives with an assortment of files that are used during class. These files can be downloaded from on our Class Resources page. Our class handouts and exercises can also be downloaded there. The website address is:

class-resources

MC-NPL Computer Lab ? 1001 Powell St ? Norristown, PA 19401 (610) 278-5100 x141 ? mcnplcomputerlab@ ? mc-

Page 3 of 83

MICROSOFT EXCEL 2016: STEP-BY-STEP GUIDE

Revised: 2/20/2020

Introduction

Show Slide 1

Notice the picture of a slide rule on the first slide. This is what people used before Excel!

Microsoft Excel is an electronic spreadsheet program that runs on a personal computer. As with a paper spreadsheet, you can use Excel to organize your data into rows and columns and to perform mathematical calculations.

What is Microsoft Office? The term "Microsoft Office" refers Microsoft's entire suite of office productivity applications. Microsoft Excel is one of the many applications that are grouped under of the "Microsoft Office" umbrella.

What is Office 365? Office 365 is a service where you pay a monthly subscription fee (around $10 a month) to use Microsoft Office programs (as opposed to paying $100 or more up front, as was traditionally done). One benefit to using Office 365 is that software updates are free (for example, if a new version of Microsoft Excel comes out, you can upgrade to that new version for free).

In this class, we will be using Excel 2016.

An Excel spreadsheet contains one or more worksheets. Each worksheet contains a grid of cells. Related worksheets are held together in a

Show Slides 2-5

workbook. When you save a spreadsheet made in Excel it saves a workbook

regardless of how many worksheets it contains. An Excel workbook can hold a maximum of 1,048,576

rows and 16,384 columns. A row goes left-to-right, a column goes up-and-down (like the column of a

building).

MC-NPL Computer Lab ? 1001 Powell St ? Norristown, PA 19401 (610) 278-5100 x141 ? mcnplcomputerlab@ ? mc-

Page 4 of 83

MICROSOFT EXCEL 2016: STEP-BY-STEP GUIDE

Revised: 2/20/2020

Exploring the Excel 2016 Environment

Switch to Excel

Open Excel by using the Start menu or by double-clicking the Desktop icon for Excel 2016.

Title Bar

1. Note the Title Bar section which has window controls at the right end, as in other Microsoft Office programs.

2. Note that a blank workbook opens with a default file name of Book1.

Quick Access Toolbar

The Quick Access Toolbar is located all the way to the left on the Title Bar. It contains frequently used commands and can be customized using the drop-down menu.

Save

1. Point to each small icon to view its ScreenTip.

2. Be aware that the Undo and Repeat buttons commands are not located anywhere else in the application except for on the Quick Access Toolbar.

3. Click the Customize Quick Access Toolbar button, check New on the menu. Notice how a new button has appeared.

Undo Customize Redo/Repeat

4. Click the Customize Quick Access Toolbar button again and select Show Below the Ribbon. This repositions the toolbar to be below the ribbon.

5. Note that when the toolbar is below the ribbon, its customize button is very difficult to see, due to its white color.

6. Move the Quick Access Toolbar back above the ribbon by clicking the customize button and selecting Show Above the Ribbon.

MC-NPL Computer Lab ? 1001 Powell St ? Norristown, PA 19401 (610) 278-5100 x141 ? mcnplcomputerlab@ ? mc-

Page 5 of 83

MICROSOFT EXCEL 2016: STEP-BY-STEP GUIDE

Revised: 2/20/2020

Ribbon

The ribbon contains all of the tools that you use to interact with your Microsoft Excel file. It is located at the top of the window. All of the programs in the Microsoft Office suite have one.

Mention Handout 1

The ribbon has a number of tabs, each of which contains buttons, which are organized into groups. Try clicking on other tabs to view their buttons (do not click the File tab yet), and then return to Home tab.

Active Tab

By default, Excel will open with the Home tab active on the Ribbon. Note how the Active tab has a white background, and the Inactive tabs have the opposite.

Contextual Tabs

Contextual tabs are displayed when certain objects, such as an images and charts, are selected. They contain additional options for modifying the object. Contextual tabs stand out because they are darker in color and are located to the right of all the other tabs. As soon as we start being productive in the program, we will see contextual tabs appear.

Show Slide 6

Groups and Buttons

Switch to Excel

On each tab, the buttons (a.k.a. commands or tools) are organized into Groups. The groups have names, but the names are not clickable.

Hover over some active buttons on the Home tab to observe ScreenTips. The ScreenTips display the name of the button, along with a short description of what the button does.

MC-NPL Computer Lab ? 1001 Powell St ? Norristown, PA 19401 (610) 278-5100 x141 ? mcnplcomputerlab@ ? mc-

Page 6 of 83

MICROSOFT EXCEL 2016: STEP-BY-STEP GUIDE

Revised: 2/20/2020

Buttons with Arrows

Note that some buttons have images on them and some have images and an arrow. The arrow indicates that more information is needed to carry out the function of the button. Some arrowed buttons have two parts: the button proper and the list arrow.

A one-part arrowed button, called a menu button, will darken completely when you point to it:

1. In the Styles group, point to the Conditional Formatting button.

2. Note there is no difference in shading between the left and right of the button when you point to each section.

On a two-part arrowed button, called a split button, only one section at a time will darken when you point to it.

1. In the Font group, point to the left part of the Fill Color button. This is the "button proper" section of the button. Note how it is darkened separately from the arrow portion of the button.

2. Point to the right portion, the section with the arrow. This is the "list arrow" section of the button. Note how it is darkened separately from the left portion.

3. The button proper is the section of a two-part button that will carry out the default option or the last used option.

4. The list arrow section will open an options menu.

Dialogue Box Launcher

On some groups there is a Launcher button which will open a dialogue box or side panel with related but less common commands.

Click a launcher button, and then close the dialogue box.

MC-NPL Computer Lab ? 1001 Powell St ? Norristown, PA 19401 (610) 278-5100 x141 ? mcnplcomputerlab@ ? mc-

Page 7 of 83

MICROSOFT EXCEL 2016: STEP-BY-STEP GUIDE

Revised: 2/20/2020

Ribbon Display Options button

This button provides options that will hide the ribbon from view. The main benefit to this is that it allows your spreadsheet to take up more of the screen.

1. Locate the Ribbon Display Options button (to the left of the window control buttons).

2. Click on it. Three options appear.

3. Click Auto-hide Ribbon. This option essentially makes Excel go into "full screen" mode. It hides not only the ribbon, but also the Quick Access Toolbar, title bar, and Window Controls.

4. To get the ribbon to show after Auto-hiding it:

a. Point to the top-center of the screen and click. (Clicking the three dots does the same thing.) The full ribbon can be seen and used. However, as as soon as the body of the spreadsheet is clicked it will hide again.

b. Click in the middle of the document. Notice how the ribbon hides again. 5. To get a partial display of the ribbon to stay in view:

a. Click the "mini" Ribbon Display Options button on the top right.

b. Click Show Tabs. Note this option has brought back our Quick Access Toolbar, title bar, Window Controls, and part of the ribbon; only the Tabs are visible. The buttons are not.

c. Click the Home tab. Notice how the buttons come into view.

MC-NPL Computer Lab ? 1001 Powell St ? Norristown, PA 19401 (610) 278-5100 x141 ? mcnplcomputerlab@ ? mc-

Page 8 of 83

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download