Transcription
Excel 2016Level 1The Computer Workshop, hop.com
Lesson Notes
Microsoft Excel 2016Level 1Course Number: 0200-100-16-WCourse Release Number: 2.0Software Release Number: 201610/01/2018Developed by:Brian IresonSuzanne HixonThelma TippieEdited by:Marissa KainCatherine DestadioPublished by:RoundTown Publishing5131 Post Road, Suite 102Dublin, Ohio 43017forThe Computer Workshop, Inc.5131 Post Road, Suite 102Dublin, Ohio 43017(614) 798-9505Copyright 2018 by RoundTown Publishing. No reproduction or transmittal of any part of this publication,in any form or by any means, mechanical or electronic, including photocopying, recording, storage in aninformation retrieval system, or otherwise, is permitted without the prior consent of RoundTown Publishing.Disclaimer:Round Town Publishing produced this manual with great care to make it of good quality and accurate, andtherefore, provides no warranties for this publication whatsoever, including, but not limited to, the impliedwarranties of merchantability or fitness for specific uses. Changes may be made to this document withoutnotice.Trademark Notices:The Computer Workshop, Inc. and The Computer Workshop logo are registered trademarks of TheComputer Workshop, Inc. [Microsoft], [Windows], [PowerPoint], [Excel], [Word], and [Access] are registeredtrademarks of Microsoft Corporation. [Photoshop] and [InDesign] are a registered trademark of Adobe. Allother product names and services identified throughout this book are trademarks or registered trademarksof their respective companies. All NASA information was obtained from public resources. Using any ofthese trade names is for editorial purposes only and in no way is intended to convey endorsement or otheraffiliation with this manual.
PrefaceContentsContents.ivUsing this Manual. viiiConventions. ixLesson 1: Getting StartedAbout Excel. 3Starting Excel. 4Using the Shortcut on the Desktop . 4Using the Taskbar. 4Using the Start Menu. 4Using the Search. 4Excel Elements. 6Interface Elements. 6Worksheet Elements. 8The Ribbon. 9The Ribbon Tabs. 9Using the Ribbon. 11Mini Toolbar and Shortcut Menu. 12Using a Mini Toolbar and Shortcut Menu. 12Quick Access Toolbar (QAT). 14Customizing the Ribbon. 17Creating a Custom Tab. 17Opening and Viewing Workbooks. 20Opening an Existing Workbook. 20Opening a Recent Workbook. 21Opening Multiple Workbooks. 21Opening a Workbook from Earlier Excel Versions . 22Viewing Opened Workbooks. 22Protected View . 22Cursor Movement. 25Using the Keyboard. 25Using the Mouse. 25Selecting Cells. 26Selecting a Single Cell. 26Selecting a Cell Range. 26Selecting a Row or Column. 27Selecting a Range of Rows or Columns. 27Selecting a Range of Adjacent Data. 28Selecting Noncontiguous Ranges. 28Selecting an Entire Worksheet. 28Saving A Workbook. 31File Names and Extensions. 31File Formats. 31Saving a New Workbook/Renaming an ExistingPage ivMicrosoft Excel 2016: Level 1, Rel. 2.0, 10/01/2018
PrefaceContents,continuedWorkbook. 32Saving an Existing Workbook. 32Workbook Conversion and Compatibility. 34Compatibility Checker. 34Converting a Workbook from an Earlier Version of Excel.34Closing a Workbook. 36Lesson 2: Creating a WorkbookCreating a Workbook. 41Document Properties. 42The Four Types of Document Properties. 42Accessing the Document Properties. 43Custom Properties. 47Viewing and Creating Custom Properties. 47Searching for Files Using Properties. 51Entering Information. 53Auto Fill. 54Custom Lists. 57Flash Fill. 62Editing Cells. 64Entering Numbers. 66Lesson 3: Introduction to FormulasCreating a Formula. 71Creating a Function Formula. 74AutoSum. 78Formula Errors. 81Cell Addressing. 82Inspecting Documents. 85Inspecting the Document. 85Lesson 4: Formatting a WorksheetInserting Rows and Columns. 91Inserting Cells. 92Deleting Rows, Columns, & Cells. 95Undo and Redo. 96Changing Column Width. 98Changing Row Height. 99Font Formatting. 102Aligning Text. 105Cell Formatting. 110Formatting Numbers . 114Moving Cells. 122Microsoft Excel 2016: Level 1, Rel. 2.0, 10/01/2018Page v
PrefaceContents,continuedCopying Cells. 123Clipboard. 125Using the Clipboard. 125Clearing the Clipboard. 126Format Painter. 132Cell Styles. 134Clear Command. 136Themes. 138Parts of a Theme. 138Applying a Theme. 138Customizing Theme Elements. 139Saving A Custom Theme. 140Hiding Columns and Rows. 144Lesson 5: Viewing, Page Layout and PrintingExcel Views. 149Normal View. 149Page Layout View . 149Page Break Preview. 149Changing Views. 150Show/Hide Options. 150Print Preview. 153Headers and Footers. 155Adding a Header. 155Adding a Footer. 156Page Setup. 159Margins. 159Orientation . 159Size. 159Print Area. 159Breaks . 159Background. 160Print Titles. 160Sheet Options. 165Printing/Viewing Gridlines and Headings. 165Changing the Page Order. 165Scale to Fit. 166Scaling to Fit. 166Printing. 168Page viMicrosoft Excel 2016: Level 1, Rel. 2.0, 10/01/2018
PrefaceContents,continuedLesson 6: Proofing and EditingChecking Spelling. 171Using Spell Check. 171AutoCorrect and AutoComplete. 173Adding Entries to AutoCorrect . 173Find. 176Using Find Options. 177Replace. 179Smart Lookup. 181Using the Smart Lookup. 181Microsoft Excel 2016: Level 1, Rel. 2.0, 10/01/2018Page vii
PrefaceUsing thisManualWelcome to the Advanced Excel 2013 course. This manual andthe data files are designed to be used for learning, review andreference after the class. The data files can be downloaded anytime from The Computer Workshop website:http:\\www.tcworkshop.comThere is no login or password required to access these files. Youwill also find handouts and supplementary materials on thewebsite in the Download section.To Download Data FilesOnce on The Computer Workshops website, look at the bottomof any page to find the link Download. Clicking this link opensthe Download page where you can choose either Data Files orHandouts.1. Data Files opens a list of general application types.2. Click once on the Microsoft Office Courses link.3. Click once on the software related to the course.4. Click once on the version related to the course.5. If there are multiple folders, click on the TCW folder.6. Click on the course name to download the data files.You can choose to open or save the zipped folders content toyour computer.The handouts are in PDF format and also available to youwithout login or password. Simply open the PDF and eitherprint or save to your computer.Page viiiMicrosoft Excel 2016: Level 1, Rel. 2.0, 10/01/2018
PrefaceConventionsConventions Used in this ManualThe hands-on exercises (Actions) are written in a two-columnformat. The left column (“Instructions”) gives numberedinstructions, such as what to type, keys to press, commandsto choose from menus, etc. The right column (“Results/Comments”), contains comments describing results of, reasonsfor, quick keys, etc. for the instructions listed on the left. Key names and Functions are bold and enclosed insquare brackets:[Enter], [Tab], [F5], [F10] Keys you press simultaneously are separated by a plus( ) sign, typed in bold and enclosed in square brackets.You do not press the plus.[Shift F5] Keys you press in sequence are separated by a space,bold and enclosed in square brackets.[Home] [Down Arrow] Ribbon tab names are in bold and italic: Example:Home Group names are in bold: Example: Font Dialog box names are in italic: Example: Save As Button names are bold and enclosed in square brackets:Example: [Sort] Information you are to type will be in bold. Example:This is the first day of the rest of your life. Information that you need to supply will be indicatedwith pointed brackets. Example: Type: your name .Page ixMicrosoft Excel 2016: Level 1, Rel. 2.0, 10/01/2018
Tips and Notes
Excel 2016: Level 1Rel. 2.0, 10/01/2018Lesson 1: GettingStartedLesson OverviewYou will cover the following concepts in thischapter: About Excel Starting Excel Excel Elements The Ribbon Mini Toolbar and Shortcut Menu Quick Access Toolbar (QAT) Customizing the Ribbon Opening and Viewing Workbooks Cursor Movement Selecting Cells Saving A Workbook Workbook Conversion andCompatibility Closing a Workbook
Lesson Notes
Lesson 1: Getting StartedAbout ExcelMicrosoft Excel 2016 is one of the most powerful and widely usedspreadsheet applications available today. Excel’s functionalityand popularity have made it an essential component oncomputers in countless organizations, businesses, and otherinstitutions throughout the world.In general, Excel is a very powerful and flexible tool fororganizing and analyzing data. Although Excel is often usedfor managing financial information, it is just as well suited toscientific data, sports statistics, or practically any other kind ofinformation you need to work with.Excel provides a wealth of financial, mathematical, and statisticalfunctions that you can apply to your data and also offersnumerous formatting and presentation options that will help youcreate clear, professional reports. You can use Excel as a database,a graphing and charting tool, a means of evaluating complexformulas, and as a way of sharing data and collaboratingwith others. Any data changed in an Excel spreadsheet will beupdated and any related totals, functions, and formulas will berecalculated accordingly.Excel is comprehensive enough to meet the needs of beginnersand experienced users alike. With Excel you can perform a widerange of tasks, from building basic spreadsheets to performingadvanced data analysis. Excel can help you process, interpret,and extract meaningful conclusions from your information.If you are new to Excel, the extensive array of features andcapabilities that it provides may seem daunting at first, but don’tworry. The keys to becoming proficient with Excel are patience,practice, and a solid foundation built on the basics.Excel 2016: Level 1, Rel. 2.0, 10/01/2018Page 3
Lesson 1: Getting StartedStarting ExcelThere are a number of ways to start the Excel program:Using the Shortcut on the Desktop Look for the Excel 2016 icon on your desktop. Double click it to launch Excel.Using the Taskbar Look for the Excel icon on the Taskbar. Click it once to launch Excel.Using the Start MenuIn Windows 10you will click theAll Apps option in theStart Menu. Click on the [Start] button, located at the lower leftcorner of the desktop. The Start menu will appear. Click on the menu item called All Programs to displaya second menu. Scroll down through the list to find the MicrosoftOffice 2016 folder menu item. Click the folder toexpand it’s contents. When you see Excel, click it to launch the application.Using the Search Click the [Start] button or tap the [Start] key.By default,Excel 2016 alwaysopens with theStartup Screen. To turnit off, click the File Tab,then select [Options]and deselect the checkbox next to Show theStartup screen when thisapplication starts.Page 4 Type Excel, when the program is highlighted tap the[Enter] key. Excel will openAfter the Excel program is opened, the Start Screen is shown. Thisis a “Backstage View” where you can open recent files, search forfiles, or create new workbooks from a variety of templates.Excel 2016: Level 1, Rel. 2.0, 10/01/2018
Action 1.1 - Starting ExcelInstructions:Results/ Comments:1. Click the [Start] button, select AllPrograms then select Microsoft Office2016 then click on Excel.To open Excel. If you cannot locate themenu items, please ask your instructor forhelp.2. Excel opens to the Start Screen, as shownbelow.This is the new way that Excel opens in2016.3. Click the Blank Workbook button.A new blank workbook is displayed in thenormal Excel view.Excel 2016: Level 1, Rel. 2.0, 10/01/2018Lesson 1: Getting Started, Page 5
Lesson 1: Getting StartedExcel ElementsOnce a file is open the interface looks very similar to and hasmost of the features of previous versions of Excel, such as: aRibbon navigation system, a Quick Access Toolbar, a Name Box,a Formula Bar, etc.Interface Elements Quick Access Toolbar (QAT) – The toolbar is locatedat the very top of the Excel window and, by default,contains three buttons, [Save], [Undo] and [Redo].However this can be personalized by the user. User Account Information - The User AccountInformation is a new feature in Excel 2016 and islocated at the very top, upper right corner of thewindow. This is where you can sign in to your account,switch accounts, or change account settings. Ribbon – Located above the spreadsheet, the Ribbonis a navigation panel with tabs of grouped commandbuttons which can be used to control, format and editthe data in the worksheet. Formula Bar – The Formula Bar is located below theRibbon and displays the true contents of the selectedcell. It can also be used to enter or edit the contents ofcells. Name Box – Located on the left side of the FormulaBar, the Name Box shows the address of the active cell.This box also allows you to type in cell locations to goto specific cells. Scroll Bars – The Scroll Bars along the right side andbottom of the window let you see what is beyond thescreen view. You can scroll up, down, left or right by:clicking on the corresponding arrow, clicking in thegrey area of the scroll bar, or dragging the scroll box. Sheet Tabs – Located just below the worksheet andto the left of the bottom scroll bar, the Sheet Tabs candisplay the different worksheets in the workbook.These can be renamed to describe the data on eachsheet.Page 6Excel 2016: Level 1, Rel. 2.0, 10/01/2018
Lesson 1: Getting StartedExcel Elements,continued Status Bar – Located at the very bottom of the Excelwindow, the Status Bar displays information about therange of selected cells. It also displays the descriptionof the current command or activity in progress. Zoom Slider – The Zoom Slider is located at the verybottom right of the Excel window. This allows you tozoom in and out as well as change the screen view.Quick Access ToolbarFormula BarUser AccountInformationRibbonName BoxScroll BarsSheet TabsStatus BarZoom SliderExcel 2016: Level 1, Rel. 2.0, 10/01/2018Page 7
Lesson 1: Getting StartedExcel Elements,Worksheet Elementscontinued Worksheet – The largest part of the screen is aspreadsheet which consists of a grid-like pattern ofcells in columns and rows. Columns - Columns are labeled with letters along thetop of the grid. There are up to 16,384 columns areavailable in a single spreadsheet. Rows - Rows are labeled with numbers down the leftside of the grid. There are up to 1,048,576 rows areavailable in a single spreadsheet. Cell – A Cell is the basic unit on a worksheet createdby the intersection of a row and column where values,text and formulas are entered and stored. Active Cell – The Active Cell is highlighted with boldlines. If you begin typing, the information will appearin the Formula Bar and will be entered into the cellwhen you press [Enter] or any directional key. If thereis more than one cell selected, the Active Cell is the onethat is white. Cell Address – The Cell Address is the location ofthe cell in the worksheet. The location is determinedby the cell’s column letter and row number. Thecell address for the first cell in a worksheet is A1, forcolumn A, row 1.Column LabelsCellAddress(displayed inName Box)Active CellRowLabelsPage 8Excel 2016: Level 1, Rel. 2.0, 10/01/2018
Lesson 1: Getting StartedThe RibbonThe Ribbon TabsBy default, the Ribbon contains nine tabs. This can be modifiedby the user. Each tab is divided up into groups of relatedcommand buttons. Below is a description of the default tabs:If you happento have AdobeAcrobat Professionalinstalled on yourcomputer, you will alsohave an Acrobat Tab. File Tab — takes you to the “Backstage View” of Excel.Listed features along the left side include basic features(New, Open, Save, Save As, Print, and Close) as well assome new features such as Account and Feedback. Thelarger area on the right displays associated commandoptions for the selected feature. To exit the “BackstageView”, click the Back arrow button in the upper leftcorner of the window or tap the [Esc] key. Home Tab — contains the most frequently usedbuttons to format and edit the worksheet. Insert Tab — contains buttons used for inserting avariety of different objects into your spreadsheet suchas graphs, pictures, and tables.Excel 2016: Level 1, Rel. 2.0, 10/01/2018Page 9
Lesson 1: Getting StartedThe Ribbon,continued Page Layout Tab — contains buttons that allow you tochange the theme of your Excel document, arrange thelayout of objects in your document and prepare yourdocument for printing. Formulas Tab — contains the tools needed to createformulas and perform calculations on your data. Data Tab — contains buttons to allow you to importdata from external sources, manage your dataconnections, and merge and organize data. Review Tab — contains the tools you need to proofand review your spreadsheet such as the spell checker,thesaurus and research tool. View Tab — contains the tools that allow you to controlthe way your worksheet is displayed.Page 10Excel 2016: Level 1, Rel. 2.0, 10/01/2018
Lesson 1: Getting StartedThe Ribbon, Power Pivot — contains tools associated withworking in Power PivotTables or relational databaseconnections.continuedUsing the Ribbon Select the cell containing the information you wish towork with. Click the appropriate tab on the Ribbon. The groupsof buttons will change according to the tab selected. If you let your mouse pointer hover on a button, ashaded box will appear with the name and descriptionof the button. Click on the desired button.Tell me what you want to doThis is a new feature in Excel 2016. If you have a cell selectedand click this space, a search field becomes active. Clicking thedrop-down for the field will offer some possible suggestions onthings you may wish to do, choose the one you need and Excelwill make those tools active. You can also type in what you wantto have Excel prompt the commands associated with the desiredaction.Dialog LauncherThe groups ona tab are usedto organize thecommands into alogical segmentation ofcommands.Some Groups of buttons in the Ribbon Tab will have a DialogLauncher located in the lower right corner of the group. Thisopens a dialog box which provides a more extensive and detailedlist of available options.Dialog LauncherExcel 2016: Level 1, Rel. 2.0, 10/01/2018Page 11
Lesson 1: Getting StartedMini Toolbar andShortcut MenuExcel 2016 utilizes Mini Toolbar and Shortcut Menus for quickaccess to common commands for a selected object. These come inhandy when you are in a tab that does not contain the commandyou need. Using the Mini Toolbar and Shortcut Menus allowyou to quickly get to a command without having to change tabs.Using a Mini Toolbar and Shortcut Menu In the worksheet, select a cell or range of cells. Right click the mouse button. A Mini Toolbar with a Shortcut Menu will be displayed.Select an option as desired.Mini ToolbarShortcut MenuPage 12Excel 2016: Level 1, Rel. 2.0, 10/01/2018
Action 1.2 - Using the Ribbon and Mini Toolbar/Shortcut MenuInstructions:Results/ Comments:1. You should still have a blank worksheeton your screen.2. Click on each of the Ribbon tabs.3. Click on the Home Tab.4. In the Font Group, rest your mousepointer over the [Fill Color] buttonwhich looks like a paint can.A description of the button will appear ina shaded text box.5. Rest your mouse pointer over severalother buttons to see their descriptions.6. Click on cell A3.To make it active. A thick black borderwill surround the cell.7. Type your name and press [Ctrl Enter].The content is added and the cell is stillactive.8. In the Font Group, click on the [Bold]button.Notice the change in your text. It shouldbe in bold. [Ctrl B].9. Click in cell A6.To make it active.10. Type 200. Press [Enter].11. Click in A6 again to make it active again.Click the cell or press the [Up Arrow] key.12. In the Number Group, click on the[Accounting Number Format] buttonwhich looks like a dollar sign (“ ”).The number has changed to an accountingformat.13. Click on the Formulas Tab.All of the formatting buttons are now outof view.14. Click on cell A3. Right-click the mouse.The Shortcut Menu and Mini Toolbarappear for quick access to commands.15. On the Mini Toolbar, click on the [Italic]button.Excel 2016: Level 1, Rel. 2.0, 10/01/2018Lesson 1: Getting Started, Page 13
Lesson 1: Getting StartedQuick AccessToolbar (QAT)The Quick Access Toolbar or (QAT) can be used to store buttonsthat you rely on heavily. When buttons are added to the QAT,they can be brought into play with a single click, even when theassociated ribbon is unavailable.Using the Customize Quick Access ToolbarKeyboard shortcuts for QATdefault options:[Ctrl S] is theshortcut for Save.[Ctrl Z] is theshortcut for Undo.[Ctrl Y] is theshortcut for Redo.Adding Buttons Click on the [Customize Quick Access Toolbar] dropdown button on the QAT. Click on the command you want to add. If you don’tsee the command you want, click on More Commands.Removing Buttons Click on the [Customize Quick Access Toolbar] dropdown button on the QAT. Click on the command you want to remove. This willuncheck it and remove it from the QAT.Page 14Excel 2016: Level 1, Rel. 2.0, 10/01/2018
Lesson 1: Getting StartedQuick AccessToolbar (QAT),Using the QAT MenuAdding Buttons Right click on any button on the Ribbon that youwould like to add to the QAT. The QAT Menu willappear.continuedThe Touch /Mouse Modeoption is designedto make the programwork efficiently onmobile devices whenthey are configured astouchscreen devices orconnected to a mouseand keyboard. Click on Add to Quick Access Toolbar.-OR To add a group of buttons to the QAT, right click onthe word that defines the group. (i.e. Font Group) Click on Add to Quick Access Toolbar.Removing Buttons Right-click on the QAT button you want to remove.The QAT Menu will appear. Click on Remove from Quick Access Toolbar.Excel 2016: Level 1, Rel. 2.0, 10/01/2018Page 15
Action 1.3 - Adding and Removing Buttons from the QATInstructions:1. Locate the Quick Access Toolbar or QAT.Results/ Comments:In the upper left-ha
Lesson 1: Getting Started Page 4 Excel 2016: Level 1, Rel. 2.0, 10/01/2018 Starting Excel There are a number of ways to start the Excel program: Using the Shortcut on the Desktop Look for the Excel 2016 icon on your desktop. Double click it to launch Excel. Using the Taskbar Look for the Excel icon on the Taskbar.
