Excel 2019 Level 1 Training PDF
Document Details
Uploaded by MeaningfulGyrolite3321
2019
Brian Ireson, Marissa Kain, Thelma C. Tippie
Tags
Summary
This is a comprehensive guide for Microsoft Excel 2019, covering Level 1 topics. It includes lesson notes, instructions on using the software, and information on saving and formatting workbooks.
Full Transcript
Excel 2019 Level 1 The Computer Workshop, Inc. 800-639-3535 www.tcworkshop.com [email protected] Lesson Notes Microsoft Excel 2019 Level 1 Rel 1, 4/26/2019...
Excel 2019 Level 1 The Computer Workshop, Inc. 800-639-3535 www.tcworkshop.com [email protected] Lesson Notes Microsoft Excel 2019 Level 1 Rel 1, 4/26/2019 Course Number: 0200-100-19-W Course Release Number: 1 Software Release Number: 2019 April 26, 2019 Developed by: Brian Ireson, Marissa Kain, Thelma C. Tippie Edited by: Suzanne Hixon, Thelma Tippie, and Jeffrey DeRamus Published by: The Computer Workshop, Inc. 5200 Upper Metro Place, Suite 140 Dublin, Ohio 43017 (614) 798-9505 dba RoundTown Publishing 5200 Upper Metro Place, Suite 140 Dublin, Ohio 43017 Copyright © 2019 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 an information 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, and therefore, provides no warranties for this publication whatsoever, including, but not limited to, the implied warranties of merchantability or fitness for specific uses. Changes may be made to this document without notice. Trademark Notices: The Computer Workshop, Inc. and The Computer Workshop logo are registered trademarks of The Computer Workshop, Inc. [Microsoft], [Windows], [PowerPoint], [Excel], [Word], [Word for Windows], and [Works] are registered trademarks of Microsoft Corporation. [InDesign] is a registered trademark of Adobe Systems Incorporated. All other product names and services identified throughout this book are trademarks or registered trademarks of their respective companies. Using any of these trade names is for editorial purposes only and in no way is intended to convey endorsement or other affiliation with this manual. Contents Contents..........................................................................................ii Using this Manual........................................................................vi To Download Data Files........................................................vi Conventions.................................................................................vii Conventions Used in this Manual......................................vii Lesson 1: Getting Started About Excel.................................................................................... 3 Starting Excel................................................................................. 4 Using the Shortcut on the Desktop...................................... 4 Using the Taskbar.................................................................... 4 Using the Start Menu.............................................................. 4 Using the Search...................................................................... 4 Excel Elements............................................................................... 6 Interface Elements................................................................... 6 Worksheet Elements............................................................... 8 The Ribbon..................................................................................... 9 The Ribbon Tabs...................................................................... 9 Mini Toolbar and Shortcut Menu............................................. 12 Using a Mini Toolbar and Shortcut Menu......................... 12 Quick Access Toolbar (QAT)..................................................... 14 Customizing the Ribbon............................................................ 17 Creating a Custom Tab......................................................... 17 Opening and Viewing Workbooks........................................... 20 Opening an Existing Workbook......................................... 20 Opening a Recent Workbook.............................................. 21 Opening Multiple Workbooks............................................ 21 Opening a Workbook from Earlier Excel Versions......... 22 Viewing Opened Workbooks.............................................. 22 Protected View..................................................................... 22 Cursor Movement....................................................................... 25 Using the Keyboard.............................................................. 25 Using the Mouse................................................................... 25 Selecting Cells.............................................................................. 26 Selecting a Single Cell........................................................... 26 Selecting a Cell Range.......................................................... 26 Selecting a Row or Column................................................. 27 Selecting a Range of Rows or Columns............................. 27 Selecting a Range of Adjacent Data.................................... 28 Selecting Noncontiguous Ranges....................................... 28 Selecting an Entire Worksheet............................................ 28 Deselecting Cells................................................................... 28 Page ii Excel 2019 Level 1, Rel 1, 4/26/2019 Saving A Workbook.................................................................... 31 File Names and Extensions.................................................. 31 File Formats............................................................................ 31 Saving a New Workbook/Renaming an Existing Workbook............................................................................... 32 Saving an Existing Workbook............................................. 32 Workbook Conversion and Compatibility.............................. 34 Compatibility Checker......................................................... 34 Converting a Workbook from an Earlier Version of Excel........................................................ 34 Closing a Workbook................................................................... 36 Lesson 2: Creating a Workbook Creating a Workbook................................................................. 41 Document Properties.................................................................. 42 The Four Types of Document Properties.......................... 42 Accessing the Document Properties................................... 43 Custom Properties...................................................................... 47 Viewing and Creating Custom Properties........................ 47 Searching for Files Using Properties........................................ 51 Entering Information.................................................................. 53 Auto Fill........................................................................................ 54 Custom Lists................................................................................ 57 Flash Fill....................................................................................... 62 Editing Cells................................................................................. 64 Entering Numbers....................................................................... 66 Lesson 3: Introduction to Formulas Creating a Formula..................................................................... 71 Creating a Function Formula.................................................... 74 AutoSum....................................................................................... 78 Formula Errors............................................................................ 81 Cell Addressing........................................................................... 82 Inspecting Documents................................................................ 85 Inspecting the Document..................................................... 85 Lesson 4: Formatting a Worksheet Inserting Rows and Columns.................................................... 91 Inserting Cells.............................................................................. 92 Deleting Rows, Columns, & Cells............................................ 95 Undo and Redo........................................................................... 96 Changing Column Width.......................................................... 98 Changing Row Height................................................................ 99 Font Formatting......................................................................... 102 Aligning Text............................................................................. 105 Excel 2019 Level 1, Rel 1, 4/26/2019 Page iii Cell Formatting.......................................................................... 110 Formatting Numbers............................................................... 114 Moving Cells.............................................................................. 122 Copying Cells............................................................................ 123 Clipboard.................................................................................... 125 Using the Clipboard........................................................... 125 Clearing the Clipboard....................................................... 126 Format Painter........................................................................... 132 Cell Styles................................................................................... 134 Clear Command........................................................................ 136 Themes........................................................................................ 138 Parts of a Theme.................................................................. 138 Applying a Theme.............................................................. 138 Customizing Theme Elements.......................................... 139 Saving A Custom Theme................................................... 140 Hiding Columns and Rows..................................................... 144 Lesson 5: Viewing, Page Layout and Printing Excel Views................................................................................ 149 Normal View........................................................................ 149 Page Layout View.............................................................. 149 Page Break Preview............................................................ 149 Changing Views.................................................................. 150 Show/Hide Options............................................................ 150 Print Preview............................................................................. 153 Headers and Footers................................................................. 155 Adding a Header................................................................. 155 Adding a Footer.................................................................. 156 Page Setup.................................................................................. 159 Margins................................................................................. 159 Orientation.......................................................................... 159 Size........................................................................................ 159 Print Area............................................................................. 159 Breaks................................................................................... 159 Background.......................................................................... 160 Print Titles............................................................................ 160 Sheet Options............................................................................. 165 Printing/Viewing Gridlines and Headings..................... 165 Changing the Page Order.................................................. 165 Scale to Fit.................................................................................. 166 Scaling to Fit......................................................................... 166 Printing....................................................................................... 168 Page iv Excel 2019 Level 1, Rel 1, 4/26/2019 Lesson 6: Proofing and Editing Checking Spelling..................................................................... 171 Using Spell Check............................................................... 171 AutoCorrect and AutoComplete............................................. 173 Adding Entries to AutoCorrect........................................ 173 Find............................................................................................. 176 Using Find Options............................................................. 177 Replace........................................................................................ 179 Smart Lookup............................................................................ 181 Using the Smart Lookup.................................................... 181 Excel 2019 Level 1, Rel 1, 4/26/2019 Page v Using this Welcome to the Excel 2019 Level 1 course. This manual and the data files are designed to be used for learning, review and Manual reference after the class. The data files can be downloaded any time from The Computer Workshop website: http:\\www.tcworkshop.com There is no login or password required to access these files. You will also find handouts and supplementary materials on the website in the Download section. To Download Data Files Once on The Computer Workshop website, locate and click the Student Resources link in the top navigation bar. When on the Student Resources page, click the Data Files button. 1. Data Files page displays 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 to your computer. While on the Student Resources page, you can also access handouts by clicking the Handouts button. Handouts are in PDF format and also available to you without login or password. Simply open the PDF and either print or save to your computer. Page vi Excel 2019 Level 1, Rel 1, 4/26/2019 Conventions Conventions Used in this Manual The hands-on exercises (Actions) are written in a two-column format. The left column (“Instructions”) gives numbered instructions, such as what to type, keys to press, commands to choose from menus, etc. The right column (“Results/ Comments”), contains comments describing results of, reasons for, quick keys, etc. for the instructions listed on the left. Key names and Functions are bold and enclosed in square 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 indicated with pointed brackets. Example: Type:. Excel 2019 Level 1, Rel 1, 4/26/2019 Page vii Tips and Notes Excel 2019: Level 1 Rel. 1.0, 4/26/2019 Lesson 1: Getting Started Lesson Overview You will cover the following concepts in this chapter: 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 and Compatibility Closing a Workbook Lesson Notes Lesson 1: Getting Started About Excel Microsoft Excel is one of the most powerful and widely used spreadsheet applications available today. Excel’s functionality and popularity have made it an essential component on computers in countless organizations, businesses, and other institutions throughout the world. In general, Excel is a very powerful and flexible tool for organizing and analyzing data. Although Excel is often used for managing financial information, it is just as well suited to scientific data, sports statistics, or practically any other kind of information you need to work with. Excel provides a wealth of financial, mathematical, and statistical functions that you can apply to your data and also offers numerous formatting and presentation options that will help you create clear, professional reports. You can use Excel as a database, a graphing and charting tool, a means of evaluating complex formulas, and as a way of sharing data and collaborating with others. Any data changed in an Excel spreadsheet will be updated and any related totals, functions, and formulas will be recalculated accordingly. Excel is comprehensive enough to meet the needs of beginners and experienced users alike. With Excel you can perform a wide range of tasks, from building basic spreadsheets to performing advanced 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 and capabilities that it provides may seem daunting at first, but don’t worry. The keys to becoming proficient with Excel are patience, practice, and a solid foundation built on the basics. Excel’s versatilely even extends to begin able to modify what language is used for Help and on-screen display. This change is made easily from within the applications options. The default here will be English but Excel offers most international languages from around the world. Excel 2019: Level 1, Rel. 1.0, 4/26/2019 Page 3 Lesson 1: Getting Started Starting Excel There are a number of ways to start the Excel program: Using the Shortcut on the Desktop Look for the Excel 2019 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 Menu Note In Windows 10 Click on the [Start] button, located at the lower left you will click the corner of the desktop. The Start menu will appear. All Apps option in the Start Menu. Click on the menu item called All Programs to display a second menu. Scroll down through the list to find the Microsoft Office 2019 folder menu item. Click the folder to expand 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. Note By default, Excel Type Excel and tap the [Enter] key. Excel will open 2019 opens with the Start Screen. To turn it off, click the File Tab, then select [Options] and deselect After the Excel program is opened, the Start Screen is shown. This the check box next to is a “Backstage View” where you can open recent files, search for Show the Start screen files, or create new workbooks from a variety of templates. when this application starts. Page 4 Excel 2019: Level 1, Rel. 1.0, 4/26/2019 Action 1.1 - Starting Excel Instructions: Results/ Comments: 1. Click the [Start] button, select All To open Excel. If you cannot locate the Programs then select Microsoft Office menu items, please ask your instructor for 2019 then click on Excel. help. 2. Excel opens to the Start Screen, as shown This is the new way that Excel opens in below. 2019. 3. Click the Blank Workbook button. A new blank workbook is displayed in the normal Excel view. Excel 2019: Level 1, Rel. 1.0, 4/26/2019 Lesson 1: Getting Started, Page 5 Lesson 1: Getting Started Excel Elements Once a file is open the interface looks very similar and has most of the features of previous versions of Excel, such as: a Ribbon navigation system, a Quick Access Toolbar, a Name Box, a Formula Bar, etc. Interface Elements Quick Access Toolbar (QAT) – The toolbar is located at 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 Account Information is a new feature in Excel 2019 and is located at the very top, upper right corner of the window. This is where you can sign in to your account, switch accounts, or change account settings. Ribbon – Located above the spreadsheet, the Ribbon is a navigation panel with tabs of grouped command buttons which can be used to control, format and edit the data in the worksheet. Formula Bar – The Formula Bar is located below the Ribbon and displays the true contents of the selected cell. It can also be used to enter or edit the contents of cells. Name Box – Located on the left side of the Formula Bar, the Name Box shows the address of the active cell. This box also allows you to type in cell locations to go to specific cells. Scroll Bars – The Scroll Bars along the right side and bottom of the window let you see what is beyond the screen view. You can scroll up, down, left or right by: clicking on the corresponding arrow, clicking in the grey area of the scroll bar, or dragging the scroll box. Sheet Tabs – Located just below the worksheet and to the left of the bottom scroll bar, the Sheet Tabs can display the different worksheets in the workbook. These can be renamed to describe the data on each sheet. Page 6 Excel 2019: Level 1, Rel. 1.0, 4/26/2019 Lesson 1: Getting Started Excel Elements, Status Bar – Located at the very bottom of the Excel continued window, the Status Bar displays information about the range of selected cells. It also displays the description of the current command or activity in progress. Zoom Slider – The Zoom Slider is located at the very bottom right of the Excel window. This allows you to zoom in and out as well as change the screen view. Quick Access Toolbar Formula Bar User Account Information Ribbon Name Box Scroll Bars Sheet Tabs Status Bar Zoom Slider Excel 2019: Level 1, Rel. 1.0, 4/26/2019 Page 7 Lesson 1: Getting Started Excel Elements, Worksheet Elements continued Worksheet – The largest part of the screen is a spreadsheet which consists of a grid-like pattern of cells in columns and rows. Columns - Columns are labeled with letters along the top of the grid. There are up to 16,384 columns are available in a single spreadsheet. Rows - Rows are labeled with numbers down the left side of the grid. There are up to 1,048,576 rows are available in a single spreadsheet. Cell – A Cell is the basic unit on a worksheet created by the intersection of a row and column where values, text and formulas are entered and stored. Active Cell – The Active Cell is highlighted with bold lines. If you begin typing, the information will appear in the Formula Bar and will be entered into the cell when you press [Enter] or any directional key. If there is more than one cell selected, the Active Cell is the one that is white. Cell Address – The Cell Address is the location of the cell in the worksheet. The location is determined by the cell’s column letter and row number. The cell address for the first cell in a worksheet is A1, for column A, row 1. Column Labels Cell Address (displayed in Name Box) Active Cell Row Labels Page 8 Excel 2019: Level 1, Rel. 1.0, 4/26/2019 Lesson 1: Getting Started The Ribbon The Ribbon Tabs By default, the Ribbon contains ten tabs. This can be modified by the user. Each tab is divided up into groups of related command buttons. Below is a description of the default tabs: Note If you happen to File Tab — takes you to the “Backstage View” of Excel. have Adobe Acrobat Listed features along the left side include basic features Professional installed on your computer, you will (New, Open, Save, Save As, Print, and Close) as well as also have an Acrobat Tab. some new features such as Account and Feedback. The larger area on the right displays associated command options for the selected feature. To exit the “Backstage View”, click the Back arrow button in the upper left corner of the window or tap the [Esc] key. Home Tab — contains the most frequently used buttons to format and edit the worksheet. Insert Tab — contains buttons used for inserting a variety of different objects into your spreadsheet such as graphs, pictures, and tables. Excel 2019: Level 1, Rel. 1.0, 4/26/2019 Page 9 Lesson 1: Getting Started The Ribbon, Page Layout Tab — contains buttons that allow you to continued change the theme of your Excel document, arrange the layout of objects in your document and prepare your document for printing. Formulas Tab — contains the tools needed to create formulas and perform calculations on your data. Data Tab — contains buttons to allow you to import data from external sources, manage your data connections, and merge and organize data. Review Tab — contains the tools you need to proof and review your spreadsheet such as the spell checker, thesaurus and research tool. View Tab — contains the tools that allow you to control the way your worksheet is displayed. Page 10 Excel 2019: Level 1, Rel. 1.0, 4/26/2019 Lesson 1: Getting Started The Ribbon, Help — offers assistance options from Microsoft. continued Power Pivot — contains tools associated with working in Power PivotTables or relational database connections. Tell me what you want to do If you have a cell selected and click this space, a search field becomes active. Clicking the drop-down for the field will offer some possible suggestions on things you may wish to do, choose the one you need and Excel will make those tools active. You can also type in what you want to have Excel prompt the commands associated with the desired action. Dialog Launcher Note Some Groups of buttons in the Ribbon Tab will have a Dialog The groups on a tab are used to organize Launcher located in the lower right corner of the group. This the commands into a opens a dialog which provides a more extensive and detailed list logical segmentation of of available options. commands. Dialog Launcher Excel 2019: Level 1, Rel. 1.0, 4/26/2019 Page 11 Lesson 1: Getting Started Mini Toolbar and Excel 2019 utilizes Mini Toolbar and Shortcut Menus for quick access to common commands for a selected object. These come in Shortcut Menu handy when you are in a tab that does not contain the command you need. Using the Mini Toolbar and Shortcut Menus allow you 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 Toolbar Shortcut Menu Page 12 Excel 2019: Level 1, Rel. 1.0, 4/26/2019 Action 1.2 - Using the Ribbon and Mini Toolbar/Shortcut Menu Instructions: Results/ Comments: 1. You should still have a blank worksheet on your screen. 2. Click on each of the Ribbon tabs. Observe that there is a new set of buttons available on each tab. 3. Click on the Home Tab. 4. In the Font Group, rest your mouse A description of the button will appear in pointer over the [Fill Color] button a shaded text box. which looks like a paint can. 5. Rest your mouse pointer over several other buttons to see their descriptions. 6. Click on cell A3. To make it active. A thick black border will surround the cell. 7. Type and press [Ctrl + The content is added and the cell is still Enter]. active. 8. In the Font Group, click on the [Bold] Notice the change in your text. It should button. be 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 in the cell or press the [Up Arrow] key. 12. In the Number Group, click on the The number has changed to an accounting [Accounting Number Format] button format. which looks like a dollar sign (“$”). 13. Click on the Formulas Tab. All of the formatting buttons are now out of view. 14. Click on cell A3. Right-click the mouse. The Shortcut Menu and Mini Toolbar appear for quick access to commands. 15. On the Mini Toolbar, click on the [Italic] button. Excel 2019: Level 1, Rel. 1.0, 4/26/2019 Lesson 1: Getting Started, Page 13 Lesson 1: Getting Started Quick Access The Quick Access Toolbar or (QAT) can be used to store buttons that you rely on heavily. When buttons are added to the QAT, Toolbar (QAT) they can be brought into play with a single click, even when the associated ribbon is unavailable. Using the Customize Quick Access Toolbar Note Adding Buttons Keyboard short Click on the [Customize Quick Access Toolbar] drop cuts for QAT default options: down button on the QAT. [Ctrl + S] is the shortcut Click on the command you want to add. If you don’t for Save. [Ctrl + Z] is the shortcut see the command you want, click on More Commands... for Undo. [Ctrl + Y] is the shortcut for Redo. Note You can add new Subscript and Superscript button to the QAT. Removing Buttons Click on the [Customize Quick Access Toolbar] drop down button on the QAT. Click on the command you want to remove. This will uncheck it and remove it from the QAT. Page 14 Excel 2019: Level 1, Rel. 1.0, 4/26/2019 Lesson 1: Getting Started Quick Access Using the QAT Menu Toolbar (QAT), Adding Buttons continued Right click on any button on the Ribbon that you would like to add to the QAT. The QAT Menu will appear. Note The Touch / Mouse Mode option is designed to make the program work efficiently on mobile devices when they are configured as touchscreen devices or connected to a mouse Click on Add to Quick Access Toolbar. and keyboard. -OR - To add a group of buttons to the QAT, right click on the 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 2019: Level 1, Rel. 1.0, 4/26/2019 Page 15 Action 1.3 - Adding and Removing Buttons from the QAT Instructions: Results/ Comments: 1. Locate the Quick Access Toolbar or QAT. In the upper left-hand corner. 2. Identify the three buttons on the QAT: You will use these buttons often. Notice [Save], [Undo] and [Redo]. that the [Redo] button is “grayed” out. This means that it is unavailable. 3. Click on the [Undo] button. This undoes your last action. Notice that the italic is removed from your name. The [Redo] button is now available. 4. Click on the [Redo] button. The italic is restored. 5. Click on the [Customize Quick Access The Customize Quick Access Toolbar Menu is Toolbar] drop-down button on the QAT. displayed. 6. Select New from the menu. The [New] button is added to the QAT 7. Click on the Home Tab. 8. Right click the [Bold] button The QAT Menu is displayed 9. Select Add to Quick Access Toolbar. You can add a button from anywhere on the Ribbon by right-clicking and selecting Add to Quick Access Toolbar. 10. On the QAT, right-click on the [Bold] The button will be removed from the QAT. button and choose Remove from Quick Access Toolbar. Excel 2019: Level 1, Rel. 1.0, 4/26/2019 Lesson 1: Getting Started, Page 16 Lesson 1: Getting Started Customizing the As you work with Excel, you may need to regularly use specific commands from different ribbon tabs. Instead of having to Ribbon constantly switch from one tab to another, you may want to build a custom tab that contains all your commonly used commands in one place. Note Creating a Custom Tab Thre is now also a Draw Tab which Click on the File Tab and choose Options from the list has tools for drawing of categories on the left. markup in the files. This may need to be actived In the Excel Options dialog choose Customize Ribbon from within the options from the list of categories on the left. window. Note Above the right display is a field drop-down arrow, clicking the drop-down gives access to the contextual tabs. Click the [New Tab] button below the right field to create a new tab and group. Select New Tab (Custom) from the right field. Click the [Rename] button below the right field list to rename the tab something more appropriate, then click [OK]. Select the New Group (Custom) tab from the right field. Click the [Rename] button below the field list to rename the group something more appropriate, then click [OK]. Add and name as many groups as desired. Excel 2019: Level 1, Rel. 1.0, 4/26/2019 Page 17 Lesson 1: Getting Started Customizing the Adding Commands to the Custom Tab Ribbon, The left field of the Customize Ribbon shows the list of popular commands by default. continued Click the drop-down from the Choose commands from: field and choose All Commands to display all the commands that are available in Excel. Scroll through the list to find your desired commands. Click and drag the command over to the custom group in your custom tab or select the command and click the [Add] button. Commands can also be renamed by using the [Rename] button. Once all the commands are in place, click the [OK] button. Exporting Customizations While still in the Customize Ribbon view of the Excel Options dialog. Click the [Import/Export] button and select Export all customizations Save the file to your computer. This file with your customizations can be copied to flash drives and imported to other systems so the program interface will be consistent across different computers. Resetting Customizations If you want to reset the default settings, navigate back to the Customize Ribbon view of the Excel Options dialog. Click the [Reset] button and choose Reset all customizations. Click [Yes] to verify the request and Excel is reset. Click the [OK] button to exit the dialog. Page 18 Excel 2019: Level 1, Rel. 1.0, 4/26/2019 Action 1.4: Customizing the Ribbon Instructions: Results/ Comments: 1. Click the File Tab and choose Options The Excel Options dialog opens. from the list of categories on the left. 2. Click Customize Ribbon from the list on The Customize Ribbon view is displayed. the left. 3. Click the [New Tab] button. A new tab and group are added to the list of available tabs in the right field. 4. Select the New Tab (Custom). The new tab is active. 5. Click the [Rename] button. The Rename dialogue is displayed. 6. Type and click [OK]. The name is applied. 7. Select the New Group (Custom). The new group is active. 8. Click the [Rename] button. The Rename dialogue is displayed. 9. Type My Group and click [OK]. The name is applied. 10. Choose All Commands from the Choose Every command in Excel is displayed in commands from: field drop-down. the list. 11. Click and drag some commands into the The commands are added in the group of group of your custom tab. your tab. 12. Click [OK]. Your tab is added to the ribbon. 13. Click the File Tab and choose Options The Excel Options dialog opens. from the list of categories on the left. 14. Click Customize Ribbon from the list on The Customize Ribbon view is displayed. the left. 15. Click the [Reset] button and choose Reset The custom tab and any QAT all customizations. customizations are removed. 16. Click [Yes] and click [OK]. Excel 2019: Level 1, Rel. 1.0, 4/26/2019 Lesson 1: Getting Started, Page 19 Lesson 1: Getting Started Opening The Open command is used to retrieve a copy of an existing workbook. and Viewing Opening an Existing Workbook Workbooks Click on the File Tab. Click the Open category on the left side. In the middle section of the screen, you will see locations from which you can open files, including Recent Workbooks, OneDrive, This PC, Add a Place and Browse. Note The keyboard shortcut for Open is [Ctrl + O]. To search for files: Note You may have to maneuver Click on This PC, to access a simplified search for a through folders to locate folder on the local drive. the document you are looking for. -OR - Click the [Browse] button, to open a normal explorer window. If you preferring to go straight into an explorer window when opening or saving files instead of the “Backstage View”: 1. Go to the program Options. 2. Choosing the Save category on the left. 3. Check the Don’t show the Backstage view when opening or saving files checkbox. Page 20 Excel 2019: Level 1, Rel. 1.0, 4/26/2019 Lesson 1: Getting Started Opening Opening a Recent Workbook and Viewing Click on the File Tab and select Open from the left. Recent Workbooks are displayed on the right side of the Workbooks, menu. continued Double click on the desired file to reopen it. Opening Multiple Workbooks Repeat the methods to open a single workbook until all the files needed are opened. -OR - If all the workbooks are in the same folder and in sequential order, select the first workbook, hold the [Shift] key, then click the last file you want. Click the [Open] button. If all the workbooks are in the same folder and not in a sequential order, select the first one and hold the [Ctrl] key, then click the other workbooks. Click the [Open] button. Excel 2019: Level 1, Rel. 1.0, 4/26/2019 Page 21 Lesson 1: Getting Started Opening Opening a Workbook from Earlier Excel Versions and Viewing A workbook created in an earlier version of Excel, will open in compatibility mode by default to maintain data integrity Workbooks, and prevent any loss of data. This can be seen by the Title Bar continued display: filename.xls [Compatibility Mode] – Excel. Note Some files created You can keep the file in its present version for easier file sharing, in Excel 2007 and however none of the new 2019 features will be available. In 2010 versions have had order to use these features, you will need to convert or save the issues opening and appear to be locked and file as the newer.xlsx version. This will be discussed in Lesson 2. uneditible, to fix the issue simply save the file using Save As coomand. Viewing Opened Workbooks Click the [Switch Windows] drop down button in the Window Group on the View Tab. Then select the file you wish to see. -OR - Note Click the workbook name on the Windows Task Bar [Alt + Tab] will switch between open workbooks and programs Protected View If you open a file originating from potentially unsafe locations (i.e. the Internet), a Message Bar may appear at the top indicating the file was opened in Protected View. This is a default setting designed to protect your computer from files which may contain viruses, worms, or other kinds of malware. When you open a file, you may disable this view for the current file only; or disable it permanently which prevents the message again. Disabling Protected View for the Current File Only: Click on the [Enable Editing] button in the Message Bar. Page 22 Excel 2019: Level 1, Rel. 1.0, 4/26/2019 Lesson 1: Getting Started Opening Disabling Protected View for the Program: and Viewing Select the File Tab, then Options. In the Excel Options dialog, click Trust Center from the index on the left. Workbooks, Click on the [Trust Center Settings] button. In the continued Trust Center dialog, select Protected View from the index on the left. Deselect the check boxes for any types of files you do not want to open in Protected View. Click the [OK] button to exit the Trust Center dialog. Click [OK] to exit the Excel Options dialog. Excel 2019: Level 1, Rel. 1.0, 4/26/2019 Page 23 Action 1.5- Opening and Viewing Workbooks Instructions: Results/ Comments: 1. Click the File Tab and then click on Open. 2. In the “Backstage View”, click on This PC, then the [Browse] button. 3. Navigate to and open the Data files If you have trouble locating the file, your folder. instructor will show you where it is. 4. Select the file BUDGET. The file Budget opens. 5. Click [Open] or press [Enter]. We added this button to the QAT earlier for easy access to this command. 6. From the QAT, click on the [Open] button. 7. Click on This PC and click the Level 1 You can have more than one workbook student files folder to open it. Double- open at a time. click on the file name EMPLOYEE. 8. Notice the Excel icon on the Windows There are three open workbooks: Task Bar. BUDGET, EMPLOYEE and Book1. 9. From the Windows Task Bar, click on The file BUDGET comes into view. Try BUDGET. using the [Ctrl + Tab] shortcut to change views of open workbooks. 10. Click on the View Tab. In the Window A menu of open workbooks appears. Group, click on [Switch Windows]. This is an alternative way to view open workbooks. If you want, you can add this button to the QAT for quick access. 11. Click on EMPLOYEE. The Employee file is active in Excel. 12. Hold the [Alt] key down and tap the A list of all open applications is displayed, [Tab] key, the Budget file is active let go each time the [Tab] key is tapped the of the [Alt] key. active window changes to the next in the list. When the one you want is active, let go of the [Alt] key to make your selection active. 13. Click the File Tab and then click on [Ctrl + W]. Close. Excel 2019: Level 1, Rel. 1.0, 4/26/2019 Lesson 1: Getting Started, Page 24 Lesson 1: Getting Started Cursor There are three methods of moving around on the worksheet: using keyboard commands, arrow keys or the mouse. Movement Using the Keyboard Press To Go to [Tab] or [] Right one cell [Shift + Tab] or [] Left one cell [] Up one cell [] Down one cell [Page Down] Down one screen view [Page Up] Up one screen view [End + ] End of row [Home] Beginning of row [Ctrl + Home] Cell A1 [Ctrl + End] Goes to the last cell that data was entered on the worksheet. [Ctrl + Arrow Key] Moves in the direction of the arrow to the last cell in a range of cells that contains data [Ctrl + Page Up] Moves to left worksheet tab [Ctrl + Page Down] Moves to right worksheet tab Using the Mouse The cross shaped cursor/pointer on the screen tracks the movement of the mouse and allows you to select an entry by clicking the mouse on the designated cell. If you drag the cursor over several cells, you have selected a range of cells. Excel 2019: Level 1, Rel. 1.0, 4/26/2019 Page 25 Lesson 1: Getting Started Selecting Cells Before you can enter, edit, or format data, you must select a cell or cells. Selecting a Single Cell Using the mouse, place the cursor on the cell. Click once. The heavy border around the cell indicates that the cell is now active. The cell address (column letter and row number) appears in the Name Box. Selecting a Cell Range A cell range is a series or block of 2 or more adjacent cells and can be useful to make changes to multiple cells at one time. A range is designated by two cell addresses – the first and last cell in the range – separated by the range operator (:). For example, A1:D5 describes the rectangular area consisting of all cells between columns A and D and between rows 1 and 5. When selecting a range that is larger than the screen, the window will scroll when the pointer touches the edge of the screen. When typing in a range, the cursor is confined within that range as long as you use the [Tab] or [Enter] keys. Note In a selected range, Use the mouse to click on the first cell. only the active cell’s address is displayed in Hold down the mouse button and drag the mouse the Name Box. pointer to the last cell in the range. The range is shown as shaded while the first cell remains white indicating it is still active. Page 26 Excel 2019: Level 1, Rel. 1.0, 4/26/2019 Lesson 1: Getting Started Selecting Cells, - OR - continued Click on the first cell desired. Hold down the [Shift] key. Click on the last cell of the selection. If you already have part of the range selected, just hold the [Shift] and click the last cell of the selection. Selecting a Row or Column Place the pointer on a row number or column letter. The pointer will change to a black arrow. Click once. The entire row or column is shaded and the active cell (usually the first cell) remains white. Selecting a Range of Rows or Columns Select the row or column using the pointer. Hold down the mouse button and drag to select the number of rows or columns in your range. - OR - Select a row or column. Hold down the [Shift] key and select the last row or column in your range. Excel 2019: Level 1, Rel. 1.0, 4/26/2019 Page 27 Lesson 1: Getting Started Selecting Cells, Selecting a Range of Adjacent Data continued This selection method is a quick way to select a large range of adjacent cells. It will automatically select all the cells from the active cell(s) until the next blank cell. There must be information in the adjacent cells for this to work. Select the first cell or cells of your desired range. Hold down the [Shift] key. Double-click the border of the selected range in the direction you want to go. The selection will be extended until the next blank cell. (Example: Click the right side of the border to highlight all adjacent cells to the right.) Selecting Noncontiguous Ranges Click and/or drag to make the first selection. Hold down the [Ctrl] key and click the additional selections. Selecting an Entire Worksheet Click once on the [Select all] button located above the row numbers and to the left of the column headings. Deselecting Cells When a selection includes cells that do not need to be selected use the [Ctrl] key to deselect cells or ranges. Page 28 Excel 2019: Level 1, Rel. 1.0, 4/26/2019 Action 1.6- Selecting Cells and Using the Home Tab Instructions: Results/ Comments: 1. The file EMPLOYEE should be open on If not, open it. your screen. 2. In cell A1 type: If you cannot see cell A1 press [Ctrl + Employee Records, Widgets Home]. Notice that the typed text appears International. in the Formula Bar. 3. Press [Enter]. The information is displayed in the cell. Notice that the information overrides the cell boundary. 4. Press the [] button on the keyboard. To re-activate cell A1. 5. Click the [Font Size] drop-down button Notice as your mouse passed over the in the Font Group on the Home Tab and choices in the gallery, the text in the cell choose 14. adjusts to match. Also, notice that the row height automatically changed to accommodate the larger font size. 6. Select cell range G8:H9. Select by dragging across the range. 7. Type the following: Notice that pressing the [Tab] key moves OH, Press [Tab] from cell to cell within the selection. 76123, Press [Tab] You will not be able to move out of the KY, Press [Tab] selection until you press an arrow key. 51322, Press [Tab] Press [] 8. Click on the row number 5 and dragging This selects rows 5 and 6 to row number 6. 9. Click on the [Bold] button in the Font All content in both rows is now bold. Group in the Home Tab. [Ctrl + B]. 10. Click on the [Center] button in the All content in both rows is now center Alignment Group on the Home Tab. aligned. Excel 2019: Level 1, Rel. 1.0, 4/26/2019 Lesson 1: Getting Started, Page 29 Action 1.6- Selecting Cells and Using the Home Tab, continued Instructions: Results/ Comments: 11. Click the Column N heading letter. The Column N is selected. 12. Click on the [Center] button in the All of the content in the column is now Alignment Group on the Home Tab. aligned to the center. 13. Select cell N6. Press [Shift + ] once, This selects the cell range N6:A5. then press [Ctrl + Shift + []. 14. Click on the [Border] button drop-down The entire selected range of cells now has a in the Font Group on the Home Tab, and boarder. choose Outside Borders. 15. Click D8, then hold the [Ctrl] key and This selects a noncontiguous range. click cells C12, D14, and F16. 16. Click on the [Bold] button in the Font To remove the bold from the text. You Group on the Home Tab. could also use the keyboard shortcut [Ctrl + B]. 17. Click the [Select All] button located This selects the entire worksheet. where the column and row headers intersect. 18. Click the [Font Size] drop-down button Notice that all of the text is converted to in the Font Group on the Home Tab, and the same font size. Any time you select choose 14. the entire worksheet, make sure the formatting needs to be applied to the entire worksheet. 19. Click in cell A1. To deselect the cells. 20. Select the entire spreadsheet. Click the corner where the row and column headers meet. 21. Hold the [Ctrl] key and click on the The row is deselected. Using the [Ctrl] header of row 7. key allows you to add or subtract from the selection. Excel 2019: Level 1, Rel. 1.0, 4/26/2019 Lesson 1: Getting Started, Page 30 Lesson 1: Getting Started Saving a Special consideration should be given to how you save the file depending on how you are going to use it and with whom you Workbook are going to share it. File Names and Extensions Note An Excel file name has two parts, the name you provide (such as Filenames can include up to 255 “Budget”) and the extension that tells you what kind of file it is. characters. For example Budget.xlsx indicates the file is named “Budget” and “.xlsx” indicates it is an Excel 2007, 2010, 2013, 2016 or 2019 file type. File Formats All Microsoft Office 2007, 2010, 2013 and 2016 software (which includes Excel) use a file format called XML. Earlier versions of Microsoft Office XP and 2003 use a binary file format. There are some distinct advantages in the XML file format. Saved files are automatically compressed, reducing the size of the file and saving storage space. It also makes it easier to send files via e-mail or over a network. In addition, there is improved file recovery for damaged files, detection of files containing macros, and improved controls over personal information contained in the creation of the document - such as any hidden data and document profiles that you would not like to share with others. Excel 2019 will, by default, save files in the XML format using one of the extensions listed below. If you are sharing a file with someone who has Excel 2007, 2010 or 2013, you can save it using this default setting. xlsx Excel 2007, 2010, 2013, 2016 or 2019 workbook file (Earlier versions of Excel use the xls extension) xlsm Excel macro-enabled workbook file xltx Excel template file xltm Excel macro-enabled template file xlsb Excel binary workbook file xlam Excel add-in file Excel 2019: Level 1, Rel. 1.0, 4/26/2019 Page 31 Lesson 1: Getting Started Saving a Saving a New Workbook/Renaming an Existing Workbook Workbook, continued Click the File Tab. Select Save As on the left to view the list of available location options. Note Press [F12] to quickly display the Save As dialog. To save to your computer, select This PC. Choose from the Current Folder list, the Recent Folders list or click the [Browse] button to find a different folder. The Save As dialog opens. Notice that the Address Bar shows the folder name and the folder path where the file will be saved. (Excel will automatically place the document in the last active folder) To save in a different folder than the one shown, click on the down arrow on the Address Bar or use the folder options listed on the left. In the File name: field, type the name of the file. Click [Save] or press [Enter]. The file name will be displayed in the title bar of the spreadsheet. Saving an Existing Workbook Note Click on the File Tab. Select Save. If you select Save on an unnamed document, the Save As - OR - dialog will be displayed. Click the [Save] button on the Quick Access Toolbar. - OR - Use the keyboard shortcut [Ctrl + S]. Page 32 Excel 2019: Level 1, Rel. 1.0, 4/26/2019 Action 1.7- Saving a Workbook Instructions: Results/ Comments: 1. Click on the File Tab, choose Save As, 2. Select This PC and click the folder The Save As dialog is displayed. location listed under Current Folder. You could also use the keyboard shortcut [F12]. 3. In the Save As dialog, check the Address To ensure that file will be saved in the Bar. correct folder. 4. In the File name: field, type: To give the file a new name. My_List 5. In the Save As type: field, choose Excel To ensure that the file type is correct. Workbook if it is not already displayed. 6. Click [Save] or press [Enter]. The dialog will disappear. Notice that the file name was replaced with the new file name and displayed in the Title Bar of the document. Excel 2019: Level 1, Rel. 1.0, 4/26/2019 Lesson 1: Getting Started, Page 33 Lesson 1: Getting Started Workbook If you will be sharing the file with a user that has Excel 97-2003 versions, you may want to save it to be compatible. These users Conversion and can still open, edit and save newer version files using a download Compatibility that is available from Microsoft. Compatibility Checker Prior to saving your file, it is a good idea to run the Compatibility Note Checker to look for any potential compatibility problems and You can find a list issue a report so that you can resolve the problems. of compatibility issues and Click on the File Tab to display the backstage. their resolutions by typing the keyword In the menu, select Info and then click on [Check “compatibility” into the for Issues] in Prepare for Sharing. Choose [Check Help dialog. Compatibility]. Excel will search the workbook. A dialog will appear with a report on any issues. Click [OK] to close the dialog. In order to use Excels’ newer features, any file created from an older version will need to be saved or converted to the newer.xlsx version. Saving a Workbook from an Earlier Version of Excel To keep a copy of the file in its original format, use the Save As command as mentioned before and make sure the Save As Type: field shows the.xlsx extension. This will save the file using the default file format. (You do not need to do this in Windows 7 or later, since it automatically saves to the older file format.) Converting a Workbook from an Earlier Version of Excel Click on the File Tab and select Info Click the [Convert] button in Compatibility Mode. Click [OK] to complete the process. Page 34 Excel 2019: Level 1, Rel. 1.0, 4/26/2019 Action 1.8- Workbook Conversion and Compatibility Instructions: Results/ Comments: 1. Open the file Student Grades.xls. This is a file created in Excel 2003. 2. Observe the title bar, note that you are in This comes on automatically whenever compatibility mode. you open a file created in an earlier version. 3. Click on the File Tab to display the “Backstage View”. 4. Select Info and then click on [Convert]. The [Convert] command is only available Click [OK]. when you have a file that was created in an earlier version. 5. Click [Yes] for the message displayed This will make all of Excel’s newer features asking if you want to close and reopen available. Notice that you are no longer in the file compatibility mode. We are going to see how the check compatibility feature works. 6. Click on the File Tab, then select Info 7. Click on [Check for Issues] and choose Use this feature when you are sharing [Check Compatibility]. a file with users of earlier versions. A dialog will appear letting you know of any compatibility problems. 8. Click [OK]. To close the dialog. 9. Click on the [Save] button on the QAT. To save the file. Excel 2019: Level 1, Rel. 1.0, 4/26/2019 Lesson 1: Getting Started, Page 35 Lesson 1: Getting Started Closing a Worksheets remain in the RAM (Random Access Memory) of your computer and therefore on display until you close them. Workbook Closing a file does not exit Excel, it only removes the file from RAM. Your file will no longer be displayed on the screen. Closing a Workbook Note Click on the File Tab. The “Backstage View” will be Keyboard Shortcut to close a displayed. workbook is Choose Close. The active workbook will disappear and [Ctrl + W]. the next workbook that is open will be displayed. If you attempt to close a workbook that has not been saved, a message will appear asking if you want to save the changes before closing the file. Note Keyboard Click Save, if you need to save. Click Don’t Save, if Shortcuts in you do not. Click Cancel, to return to your workbook. Message windows: If a window appears with buttons containing underlined letters, you can tap the underlined letter on the keyboard to trigger the associated button. Page 36 Excel 2019: Level 1, Rel. 1.0, 4/26/2019 Action 1.9- Closing a Workbook Instructions: Results/ Comments: 1. Click the File Tab. 2. Click Close. The file Student Grades closes. The file My_List should still be on your screen. 3. Press [Ctrl + W]. The file My_List closes. The file Budget should still be on your screen. 4. Close the file Budget. Using any method you want. 5. Close Book1. A warning will appear asking you if you want to save changes to Book1. 6. In the warning box, click [Don’t Save]. Most of the time you will want to click [Save]. When you click [Don’t Save] you will lose any changes you have made. 7. Notice the screen. Excel is still open, but there are no workbooks currently open. Excel 2019: Level 1, Rel. 1.0, 4/26/2019 Lesson 1: Getting Started, Page 37 Tips and Notes Excel 2019: Level 1 Rel. 1.0, 4/26/2019 Lesson 2: Creating a Workbook Lesson Overview You will cover the following concepts in this chapter: Contents Creating a Workbook Document Properties Custom Properties Searching for Files Using Properties Entering Information Auto Fill Custom Lists Flash Fill Editing Cells Entering Numbers Lesson Notes Lesson 2: Creating a Workbook Creating a If you have turned off the Show the Start screen when this application starts found under Options on the File Tab, Excel Workbook will automatically create a new Blank Workbook when the program is first opened. Otherwise you will need to click Blank Workbook in the Start screen or simply press the [Esc] key. Once an Excel file is already open, you can create additional new workbooks. Note To open a Blank Workbook, you can also use the [New] button on the QAT or press [Ctrl + N] on the keyboard. Click on the File Tab and select New. Click Blank Workbook to start a from scratch -OR- Click to select a template from the list shown that matches the type of data you need to enter: Featured Templates are pre-existing formats created by Microsoft. (You can also use the Search box at the top to find more available templates on Microsoft Online.) Personal Templates are templates you have created and saved to your local drives (this is discussed further in Level 2). Excel 2019: Level 1, Rel. 1.0, 4/26/2019 Page 41 Lesson 2: Creating a Workbook Document All Excel documents have properties, also known as metadata. Theses properties are used to help describe or identify a given Properties document. The properties of documents include: title, author name, subject, and file size, date information, and category to name a few, they are used to identify a document’s topic or contents. Including information in the document properties allows you to easily search for documents based on those properties. Properties can be edited and/or updated form the Info category of the File Tab, backstage view. The Four Types of Document Properties Standard Properties: These include- author, title, and subject. Automatically Updated Properties: These include both file system properties (file size or the dates when a file was created or last modified) and statistics that are maintained by Office programs. You cannot specify or change the automatically updated properties. Custom Properties: These are created and defined by users, they are additional custom properties for documents. Users are able to assign a text, time, or numeric value to custom properties, and users may assign them yes or no values. There is a list to choose from but it is also possible to name and define custom properties. Document Library Properties: These are associated with documents in a document libraries on a website or in public folders. These properties are defined in relation to the document library. When adding documents to the library, users will be prompted to include the values for any required properties or Page 42 Excel 2019: Level 1, Rel. 1.0, 4/26/2019 Lesson 2: Creating a Workbook Document to update any incorrect properties. Examples of these could include Submitted By, Date, Category, and Description. Properties, continued Accessing the Document Properties Click the File Tab. Choose the Info category on the left of the Backstage view. The basic list of Properties is displayed on the right side of the Backstage view. Click the [Show All Properties] link to expand the list. - OR - Excel 2019: Level
