Spreadsheets 2016 Training Notes PDF

ICDL SPREADSHEETS LESSON 1 - EXPLORING MICROSOFT EXCEL 2016......................................................................... 1 1.1 Starting Excel 2016.............................................................................................................................. 2 1.2 The User Interface................................................................................................................................ 3 1.3 Excel Options....................................................................................................................................... 5 1.4 Creating a Workbook........................................................................................................................... 7 1.5 Opening a Workbook........................................................................................................................... 9 1.6 Saving a New Workbook.................................................................................................................... 10 1.7 Closing a Workbook........................................................................................................................... 12 1.8 Working with Worksheets................................................................................................................... 12 1.9 Using the Ribbon................................................................................................................................ 16 1.10 Hiding the Ribbon............................................................................................................................ 17 1.11 Using Magnification/Zoom Tools..................................................................................................... 19 1.12 Closing and Exiting Excel................................................................................................................ 19 1.13 Review Exercise.............................................................................................................................. 21 LESSON 2 - GETTING HELP............................................................................................................... 22 2.1 Using Microsoft Excel Help and Resources...................................................................................... 23 2.2 Working with Excel Help.................................................................................................................... 23 2.3 Review Exercise................................................................................................................................ 25 LESSON 3 - BASIC WORKBOOK SKILLS......................................................................................... 26 3.1 Using the Keyboard to Select Cells/Navigate a Workbook............................................................... 27 3.2 Using the Mouse to Scroll.................................................................................................................. 29 3.3 Using the Scroll Bar Shortcut Menu................................................................................................... 30 3.4 Using Go To....................................................................................................................................... 32 3.5 Entering Text...................................................................................................................................... 34 3.6 Entering Numbers.............................................................................................................................. 35 3.7 Shortcuts for Data Entry..................................................................................................................... 36 3.8 Editing Data....................................................................................................................................... 38 3.9 Spell Check........................................................................................................................................ 40 3.10 Saving the Workbook with another Name....................................................................................... 42 3.11 Saving a Workbook as Another File Type....................................................................................... 44 3.12 Review Exercise............................................................................................................................... 46 LESSON 4 - SELECTION..................................................................................................................... 48 4.1 Selecting a Cell.................................................................................................................................. 49 4.2 Selecting a Range of Adjacent cells................................................................................................... 50 4.3 Selecting a Range of Non-Adjacent cells........................................................................................... 50 4.4 Selecting the Entire Worksheet.......................................................................................................... 51 4.5 Selecting a Row................................................................................................................................. 51 4.6 Selecting a Range of Adjacent Rows................................................................................................. 52 4.7 Selecting a Range of Non-Adjacent Rows......................................................................................... 52 4.8 Selecting an Entire Column................................................................................................................ 53 4.9 Selecting a Range of Columns........................................................................................................... 53 4.10 Selecting a Range of Non-Adjacent Columns................................................................................. 54 4.11 Review Exercise.............................................................................................................................. 55 LESSON 5 - WORKING WITH COLUMNS AND ROWS..................................................................... 56 5.1 Adjusting Column Width..................................................................................................................... 57 5.2 Adjusting the Row Height................................................................................................................... 58 5.3 Automatically Adjusting Column......................................................................................................... 59 5.4 Inserting Columns and Rows............................................................................................................. 60 5.5 Deleting Columns and Rows.............................................................................................................. 62 5.6 Freezing and Unfreezing Columns and Rows................................................................................... 64 5.7 Review Exercise................................................................................................................................. 68 LESSON 6 - NUMBER FORMATTING................................................................................................. 69 6.1 About Number Formats...................................................................................................................... 70 6.2 Accounting Number Style................................................................................................................... 70 6.3 Percent Style...................................................................................................................................... 72 6.4 Comma Style...................................................................................................................................... 73 6.5 Decimal Places.................................................................................................................................. 74 6.6 Review Exercise................................................................................................................................. 76 LESSON 7 - TEXT FORMATTING....................................................................................................... 77 7.1 Formatting Text.................................................................................................................................. 78 7.2 Changing the Font.............................................................................................................................. 78 7.3 Changing Font Size............................................................................................................................ 80 Page ii KE000095 © 2017 7.4 Bold and Italic..................................................................................................................................... 81 7.5 Underlining Text................................................................................................................................. 83 7.6 Font Colour......................................................................................................................................... 84 7.7 Rotating Text...................................................................................................................................... 85 7.8 Text Wrapping.................................................................................................................................... 86 7.9 Cell Alignment.................................................................................................................................... 88 7.10 Apply Table Styles using AutoFormat.............................................................................................. 89 7.11 Review Exercise.............................................................................................................................. 92 LESSON 8 - CELL FORMATTING....................................................................................................... 93 8.1 Merging Cells..................................................................................................................................... 94 8.2 Vertical Alignment.............................................................................................................................. 95 8.3 unmerge Cells.................................................................................................................................... 96 8.4 Adding Borders................................................................................................................................... 97 8.5 Drawing Borders................................................................................................................................ 99 8.6 Adding Fill Colour to Cells................................................................................................................ 102 8.7 Format Painter................................................................................................................................. 104 8.8 Inserting Cut or Copied Cells........................................................................................................... 105 8.9 Deleting Cells................................................................................................................................... 108 8.10 Review Exercise............................................................................................................................ 110 LESSON 9 - WORKING WITH TABLES............................................................................................ 111 9.1 Adding Table Rows and Columns.................................................................................................... 112 9.2 Review Exercise............................................................................................................................... 113 LESSON 10 - FORMULAS................................................................................................................. 114 10.1 Using Basic Formulas.................................................................................................................... 115 10.2 Entering Formulas.......................................................................................................................... 116 10.3 Basic Functions.............................................................................................................................. 118 10.4 Using the AutoSum Button............................................................................................................. 120 10.5 Using the AutoSum List.................................................................................................................. 121 10.6 Using AutoComplete...................................................................................................................... 122 10.7 Editing Functions............................................................................................................................ 125 10.8 Using Auto Calculate...................................................................................................................... 126 10.9 Modifying Formulas Using Range Borders.................................................................................... 128 © 2017 KE000095 Page iii 10.10 Error Checking............................................................................................................................. 129 10.11 Creating an Absolute Reference.................................................................................................. 131 10.12 Using the IF Function.................................................................................................................. 133 10.13 Review Exercise.......................................................................................................................... 136 LESSON 11 - CUT, COPY, AND PASTE........................................................................................... 137 11.1 Copying and Pasting Data............................................................................................................. 138 11.2 Cutting Data................................................................................................................................... 139 11.3 Copying Formulas.......................................................................................................................... 140 11.4 Paste Options................................................................................................................................ 142 11.5 Paste List....................................................................................................................................... 143 11.6 Filling Cells..................................................................................................................................... 144 11.7 Drag-and-Drop Editing................................................................................................................... 145 11.8 Undo and Redo.............................................................................................................................. 147 11.9 Review Exercise............................................................................................................................ 149 LESSON 12 - DATA MANAGEMENT................................................................................................ 150 12.1 Sorting............................................................................................................................................ 151 12.2 Finding Data................................................................................................................................... 152 12.3 Replacing Data.............................................................................................................................. 153 12.4 Finding and Replacing Cell Formats.............................................................................................. 155 12.5 Review Exercise............................................................................................................................ 158 LESSON 13 - CREATING CHARTS................................................................................................... 159 13.1 Inserting a Column Chart............................................................................................................... 160 13.2 Inserting a Line Chart..................................................................................................................... 161 13.3 Inserting a Bar Chart...................................................................................................................... 163 13.4 Inserting a Pie Chart...................................................................................................................... 164 13.5 Moving and Resizing a Chart......................................................................................................... 165 13.6 Adding Chart Title.......................................................................................................................... 166 13.7 Changing the Chart Background.................................................................................................... 168 13.8 To Format a Chart Legend, Chart Title, Chart Axis....................................................................... 169 13.9 Changing a column, bar, line or pie slice colours.......................................................................... 171 13.10 Changing the Chart Type............................................................................................................. 172 13.11 Adding Data Labels to a Chart..................................................................................................... 174 Page iv KE000095 © 2017 13.12 Changing the Chart Layout.......................................................................................................... 175 13.13 Copying and Moving Charts........................................................................................................ 176 13.14 Deleting a chart........................................................................................................................... 177 13.15 Review Exercise.......................................................................................................................... 178 LESSON 14 - USING PAGE SETUP.................................................................................................. 179 14.1 Worksheet Margins........................................................................................................................ 180 14.2 Worksheet Orientation................................................................................................................... 181 14.3 Worksheet Page Size..................................................................................................................... 181 14.4 Headers and Footers..................................................................................................................... 182 14.5 Header and Footer Fields.............................................................................................................. 184 14.6 Scaling your Worksheet to fit your page(s).................................................................................... 186 14.7 Repeating Row and Column Labels.............................................................................................. 187 14.8 Changing Sheet Options................................................................................................................ 188 14.9 Review Exercise............................................................................................................................ 190 LESSON 15 - PRINTING..................................................................................................................... 191 15.1 Print Preview.................................................................................................................................. 192 15.2 Printing the Current Worksheet..................................................................................................... 193 15.3 Apply Automatic Title Rows to All Printed Pages........................................................................... 194 15.4 Printing a Selected Range............................................................................................................. 195 15.5 Printing a Page Range................................................................................................................... 196 15.6 Printing Multiple Copies................................................................................................................. 196 15.7 Review Exercise............................................................................................................................ 198 ICDL Syllabus..................................................................................................................................... 199 © 2017 KE000095 Page v LESSON 1 - EXPLORING MICROSOFT EXCEL 2016 In this section, you will learn about: Starting Excel The user interface Excel options Creating a workbook Opening a workbook Saving a new workbook Closing a workbook Working with worksheets Using the ribbon Hiding the ribbon Using magnification/zoom tools Exiting Excel Lesson 1 - Exploring Microsoft Excel ICDL Spreadsheets 1.1 STARTING EXCEL 2016 E9 Concepts Microsoft Excel is a spreadsheet application developed by Microsoft for Microsoft Windows and Mac OS X. It allows you to enter numerical values or data into the rows or columns of a worksheet, and use these numerical entries for calculations, graphs, and statistical analysis. Note: A worksheet is a single spreadsheet organised by columns and rows, while the workbook is an Excel file that contains one or more worksheets. UbJ Steps To start Microsoft Excel 2016: 1. Select the Start icon on the taskbar. The click click scroll bar Start menu appears. Excel 2016 2. Point to the programs list under Recently added The scroll menu appears. 3. Select Excel 2016. The Microsoft Excel 2016 window opens. Click I 4. Click Blank workbook and a new workbook Click opens. Page 2 KE000095 © 2017 ICDL Spreadsheets Lesson 1 - Exploring Microsoft Excel A B C 1 2 3 4 5 6 7 Blank workbook © 2017 KE000095 Page 3 Lesson 1 - Exploring Microsoft Excel ICDL Spreadsheets 1.2 THE USER INTERFACE Q Concepts The Microsoft Excel 2016 user interface uses ribbons and tabs just like its predecessor, Microsoft Office 2013. The layout includes the principle functions of the application and can be customised to the user's needs. H Insert Page Layout Formulas Data Review View 0 Tell me what » Cut □m Calibri T 11 - K /C == = " Hjt'Wrap Text IF[! Copy T ^ Format Painter B IU- EE! - - £ - § Merge & Center ~ Clipboard ri Font r s Alignment Home Tab Excel Layout Page 4 KE000095 © 2017 ICDL Spreadsheets Lesson 1 - Exploring Microsoft Excel Active Cell In an Excel 2016 worksheet, an active cell is the cell with the green outline. Data is always entered into the active cell. Column Letter Columns run vertically on a worksheet and each column is identified by a letter in the column header. Formula Bar Located above the worksheet, this area displays the contents of the active cell. It can also be used for entering or editing data and formulas. Name Box Located left to the formula bar, the Name Box displays the cell reference or the name of the active cell. Row Numbers Rows run horizontally in a worksheet and are identified by a number in the row header. Together a column letter and a row number create a cell reference. Each cell in the worksheet can be identified by this combination of letters and numbers such as A1, F456, or AA34. Sheet Tabs By default there is one worksheet in an Excel file. The tab at the bottom of a worksheet tells you the name of the worksheet - such as Sheet1, Sheet2 etc. Quick Access Toolbar This customisable toolbar allows you to add frequently used commands. Click on the down arrow at the end of the toolbar to display the available options. Application Button Clicking on the Application Button displays a drop down menu containing a number of options, such as open, save, and print. The options in the Button menu are very similar to those found under the File menu in previous versions of Excel. © 2017 KE000095 Page 5 Lesson 1 - Exploring Microsoft Excel ICDL Spreadsheets Ribbon The Ribbon is the strip of buttons and icons located above the work area in Excel 2016. The Ribbon replaces the menus and toolbars found in earlier versions of Excel. 1.3 EXCEL OPTIONS O Concepts Excel Options lets you change some of the basic option preferences in Excel 2016, such as the user name to be used for spreadsheets and the default folder from which to open and save spreadsheets. UJ Steps To enter a user name: 1. Click the File tab. H-Z-l The Backstage view is displayed. 1 1 Home XCut ' Eg] Copy T Paste ,, ^ Format Painter Clipboard 2. Select Options. The Excel Options dialog box is displayed. 3. If necessary, Select the General category from the left hand pane. The options for the General category appear in the | General Formulas | Data right-hand pane. Proofing Save Language Advanced Click [ OK 4. Enter a user name in the User name box and click OK. The user name is entered. Page 6 KE000095 © 2017 ICDL Spreadsheets Lesson 1 - Exploring Microsoft Excel UJ Steps To enter a default file location from which to open and save spreadsheets. 1. Click the File tab. The Backstage view is displayed. 2. Select Options. Options The Excel Options dialog box is displayed. 3. Select the Save category from the left-hand pane. The options for the save category appear in the right-hand pane. 4. Change the file path in the Default file location Change the default location at box. the end of the file path from \My Documents to \My Music. The file location will be changed. 5. Click OK. The Excel options dialog box closes and the Click options are applied. OK Save the file as Practice Options.xlsx and notice it in the My Music folder. Practice the Concept: Change the Excel options back to having My Documents as the default location. Having done this delete the Practice Options.xlsx file from My Music. © 2017 KE000095 Page 7 Lesson 1 - Exploring Microsoft Excel ICDL Spreadsheets 1.4 CREATING A WORKBOOK Concepts A Microsoft Office Excel workbook is a file that contains one or more worksheets that you can use to organise various kinds of related information. You can create a new workbook by simply opening a blank one. You can also use templates on which to base the new workbook, such as the default template provided with Microsoft Excel or templates you have created. Microsoft Excel also offers templates available online, found by using the search function. Creating a New Workbook Steps To create a new blank workbook using a local template: 1. Click the File tab. The Backstage View is displayed. Click 2. Click New. The pane on the right displays the available Click templates. Page 8 KE000095 © 2017 ICDL Spreadsheets Lesson 1 - Exploring Microsoft Excel A B C 1 2 3 4 5 6 7 Blank workbook Close the new workbook without saving. To create a workbook using an online template: 1. Click the File tab. The Backstage View is displayed. 2. Click New. The pane on the right displays the New available templates. Click 3. Search in the Search for online templates search bar by using Search for Trip Planner and hit Enter keywords to find the template. Excel searches for templates 4. Select the Trip Planner template. Click Trip Planner A preview of the template opens. 5. Create the template. The Trip Planner template is Click Create created. Close the new blank workbook without saving. © 2017 KE000095 Page 9 Lesson 1 - Exploring Microsoft Excel ICDL Spreadsheets 1.5 OPENING A WORKBOOK Concepts You can open an existing workbook to work on in Excel. The workbook may be on a storage device, cloud service or an online application. Steps Open an existing workbook from a specific drive and folder location. 1. Click the File tab. The Backstage View is displayed. 2. Click Open. Click The Open window is displayed. 3. Click Browse. The Open dialog box is displayed. Click 4. Select the approriate drive that Click Student Folder contains the Student Folder. Open the Student Folder. The Student Folder will appear. 5. Select Annual Sales.xlsx. Click Annual Sales.xlsx The Annual Sales workbook is selected. 6. Click the Open button. Open The Open dialog box closes and Click the Annual Sales workbook opens. Close the Annual Sales.xlsx workbook without saving. Quick Tip: When multiple workbooks are opened, use the Switch Windows option in the View tab to navigate between the opened workbooks. Page 10 KE000095 © 2017 ICDL Spreadsheets Lesson 1 - Exploring Microsoft Excel 1.6 SAVING A NEW WORKBOOK Concepts Whether using the desktop or web version of Excel, you save documents using the File tab, no matter where you wish to save the documents to. You can save documents to both a local drive on your device, or using OneDrive which offers saving capabilities within Microsoft Excel. Save As dialog box Steps To save a new workbook to a local drive: Open Excel 1. Open a new blank worksheet. A blank worksheet is displayed. 2. Click the File tab. The backstage view will open. Click Save 3. Select the Save button. The Save window will open. © 2017 KE000095 Page 11 Lesson 1 - Exploring Microsoft Excel ICDL Spreadsheets 4. Click Browse. The Save As dialog box will open. Click 5. Select a location for you to save the workbook to. If necessary, select Documents from the list of folders. The Documents folder is selected. 6. Type Annual Sales in the File name box. The existing text is overwritten with the file name you specified. 7. Click the Save button. Save The Save As dialog box closes and Click the file is saved to the Documents folder. To save a new workbook to an online drive: 1. Click the File tab. The backstage view will open. Click Save 2. Select the Save button. The Save section will open. 3. Select OneDrive from the Save As Click OneDrive options. Sign into your OneDrive account, if necessary. Page 12 KE000095 © 2017 ICDL Spreadsheets Lesson 1 - Exploring Microsoft Excel 4. If necessary, select an exact Click Browse location on OneDrive to save the workbook to. The folder will open any Excel workbooks will be displayed. 5. Type the desired file name. Type Annual Sales The text appears in the File name box. Click Save 6. Click the Save button. The Save As dialog box closes and the file is saved to the Documents folder. 1.7 CLOSING A WORKBOOK Steps To close a workbook: 1. Click the File tab. The Backstage View is displayed. 2. Click Close. The workbook closes. Click If a message box opens, asking you if you want to save the workbook, click Don't save. 1.8 WORKING WITH WORKSHEETS O Concepts The tab at the bottom of a worksheet displays the name of the worksheet - such as sheet1, sheet2, etc. You can switch between worksheets by selecting the © 2017 KE000095 Page 13 Lesson 1 - Exploring Microsoft Excel ICDL Spreadsheets desired tab. You can add, rename, and move tab positions as shown in the steps below. Excel Sheet Tab Steps To work with worksheets: Open Explore.xlsx. Notice the worksheet tabs at bottom of the Excel window. 1. Click the Expenses sheet tab. Raw Data Expenses Sheetl © The Expenses sheet is displayed. You can quickly insert a new worksheet by clicking on the button. Excel labels these worksheets using a default name, so consider renaming your worksheets to reflect what they contain. To rename it, double-click on the existing sheet name (e.g. Sheet1) and type a new name. O Concepts You can copy and move a worksheet within a spreadsheet by right-clicking the worksheet at the bottom of the workbook window, click Move or Copy, select the location to move the worksheet to, and clicking OK. To copy a worksheet, simply check the Create a copy button before clicking OK, as shown below. Page 14 KE000095 © 2017 ICDL Spreadsheets Lesson 1 - Exploring Microsoft Excel Steps 1. Select the worksheet tab at the The menu displays. bottom of the worksheet window. Right-click the Sales Data tab at the bottom of the worksheet window 2. Select Move or Copy. Click Move or Copy The Move or Copy dialog box Insert... opens. Delete Rename Move or Copy... View Code E7"1 Protect Sheet... Tab Color ► Hide Unhide... Select All Sheets 3. Select the location to copy the worksheet to. Click (move to end) 4. Select the Create a Copy and click checkbox. OK A new worksheet entitled Sales Data (2) appears. Click in the Create a Copy checkbox, © 2017 KE000095 Page 15 Lesson 1 - Exploring Microsoft Excel Steps 1. Select the worksheet tab at the Right-click the Sales Data (2) tab at the bottom bottom of the worksheet window. of the worksheet window The menu displays. 2. Select Move or Copy. Click Move or Copy The Move or Copy dialog box opens. | Move or Copy... | View Code f Protect Sheet... Select All Sheets. 3. Select the location to move the Click Sales Data in the Before sheet: list worksheet to and click OK. Sales Data (2) appears before Sales Data. Quick Tip: You can also move a worksheet within a workbook by clicking a sheet tab, holding the left mouse button, dragging the sheet to the desired location. To rename a worksheet within a spreadsheet Steps 1. Select the worksheet tab at the Right-click the Sales Data (2) tab at the bottom bottom of the worksheet window. of the worksheet window The menu displays. © 2017 KE000095 Page 16 ICDL Spreadsheets Lesson 1 - Exploring Microsoft Excel 2. Select Rename. Click Rename The worksheet name is highlighted. 3. Enter the worksheet name into the Type Copy of Sales Data highlighted worksheet tab. The worksheet is renamed. Quick Tip: You can quickly move to the next sheet or previous sheet in the workbook by pressing Ctrl+PgDn or Ctrl+PgUp respectively. 1.9 USING THE RIBBON Q Concepts The Ribbon is designed to help you quickly find the commands that you need to complete a task. Commands are organised in logical groups, which are collected together under tabs. Each tab relates to a type of activity, such as writing or laying out a page. To reduce clutter, some tabs are shown only when needed. For example, the Picture Tools tab is shown only when a picture is selected. Excel Ribbon Steps Using the ribbon to make the text bold. Select a cell to make bold: © 2017 KE000095 Page 17 Lesson 1 - Exploring Microsoft Excel ICDL Spreadsheets 1. If necessary, click the Home tab. Click The Home ribbon is displayed. 2. Click B to make the text bold. 0 The selected text is displayed in Insert Page Layout Form bold. 1.10 HIDING THE RIBBON EJ Concepts You can't delete or replace the Ribbon with the toolbars and menus as you could in the earlier versions of Microsoft Office, although you can minimise or hide it to allow for more on-screen space. When this option is in use, the ribbon reappears when you click on a tab, then disappears after you select a command or when you click anywhere in the worksheet. Insert Page Layout Formulas Data Review View Q Tell me what Sy-1 T [=j*J Wrap Text *= *= 1^1 Merge & Center * Alignment Full Ribbon B *5 - c*- ; File Home Insert Page Layout Formulas Data Review View 0 Tell me what you want to do Minimised Ribbon Page 18 KE000095 © 2017 ICDL Spreadsheets Steps To hide the ribbon: 1. Double-click on the currently selected tab. a c*- ^ File Home Insert Page Layout Form The ribbon is hidden temporarily. 2. Click on any tab. The ribbon is displayed at the top of a few rows as an overlay. 3. Click any cell in the worksheet. H *>- c* The ribbon is minimised. File Home Insert Page Layout Formulas Data 4. Double-click on any tab. The ribbon is permanently a Insert Page Layout Form displayed once again. *>- Page 19 KE000095 © 2017 Lesson 1 - Exploring Microsoft Excel ICDL Spreadsheets 1.11 USING MAGNIFICATION/ZOOM TOOLS E9 Concepts You can use the magnification / zoom tools to display the workbook at different magnifications, depending on your specific needs. These tools are particularly useful when working within a worksheet that has a vast amount of data and requires specific attention in particular cells. Steps 1. On the View tab in the Zoom group, select Click Zoom the Zoom button. The Zoom dialog box appears. 2. Check the required magnification check-box Click the 75% option or click the Percent box and enter the magnification required. The options will allow you to set your preferred zoom measurement. 3. Apply the changes. Click OK The zoom options will be applied. 1.12 CLOSING AND EXITING EXCEL E9 Concepts When you're ready to close Excel, you have several choices for shutting down the program: Click the Close button (the X) in the upper-right corner of the Excel 2016 program window. Click the blank space to the left of the Quick Access Toolbar in the top left corner of the Excel 2016 program window and select Close. Press Alt+F4. Note that If you have more than one workbook open, you need to close each workbook individually to exit the program. Be sure to save your changes before exiting the workbook you've been working on. If you attempt to leave the workbook without saving, an alert box appears in Excel warning you that your changes will not be saved. To save these changes Page 20 KE000095 © 2017 ICDL Spreadsheets Lesson 1 - Exploring Microsoft Excel before exiting click the Save button. If you don't want to save your changes click Don't Save. Steps To exit Excel: 1. Click the blank space to the left of the Quick Access Toolbar. Click A pop-up menu is displayed. 2. Click Close. If you have only one workbook open, the Excel program closes. Select Don't Save if you are prompted to save any changes. © 2017 KE000095 Page 21 Lesson 1 - Exploring Microsoft Excel ICDL Spreadsheets 1.13 REVIEW EXERCISE □ Explore Microsoft Excel 2016 1. Start Excel. 2. Click the File tab. 3. Open the Excel Options window. 4. Display the View tab. 5. Minimise the Ribbon. 6. Maximise the Ribbon. 7. Exit Excel without saving changes to the workbook. Page 22 KE000095 © 2017 LESSON 2 - GETTING HELP In this section, you will learn about: Using Microsoft Excel help and resources Working with Excel help Lesson 2 - Getting Help ICDL Spreadsheets Looking further for answers Page 24 KE000095 © 2017 ICDL Spreadsheets Lesson 2 - Getting Help 2.1 USING MICROSOFT EXCEL HELP AND RESOURCES Steps You can use Excel's extensive Help facility when you need assistance on any Excel topic or task. You can search Excel Help to provide assistance and training. You can access the Office Support website as well to answer your questions about all Office products if needed. x Help » P Top help topics Convert text to numbers TEXT function VLOOK J P function SUMIF function All functions Drop-down lists and data validation Need more help? You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice, [0* Explore new Excel training Excel Help Window 2.2 WORKING WITH EXCEL HELP © 2017 KE000095 Page 25 Lesson 2 - Getting Help ICDL Spreadsheets Steps To use Excel Help: Page 26 KE000095 © 2017 ICDL Spreadsheets Lesson 2 - Getting Help If necessary, open a new blank workbook. 1. Press the F1 function key. The Help panel opens displaying a F 1 number of topics on the right-hand side of the application window. 2. Type Statistical Functions into the Search box. The text appears in the Search box. 3. Click the Search button. A list of results displays in the Help window. 4. Select the desired search result. Scroll if necessary, and click Statistical The help topic opens in the same Functions (reference). pane. Note you can add a Help icon to the Quick Access Toolbar. See section 7.10 APPLY TABLE STYLES USING AUTOFORMAT for instructions on customising the Quick Access Toolbar. To access Microsoft Office online help. Click the File tab. Then, click the Help icon ? in the top right-hand corner of the window. The default web browser will launch and open the Office Help website. © 2017 KE000095 Page 27 Lesson 2 - Getting Help ICDL Spreadsheets 2.3 REVIEW EXERCISE □ Getting Help 1. Open Excel and select the Help icon. 2. Change the Help settings to display Excel help from your computer. 3. Clear the Search Help text box and search for conditional formatting. 4. Select the desired search result and view the information. 5. Change the Help settings to display Excel help from Office.com. 6. Navigate to the Excel Help Home page using the Home button. 7. Type Bar Chart in the Search box and select the desired result. 8. Close the Internet Explorer window, Excel Help and Excel. Page 28 KE000095 © 2017 LESSON 3 - BASIC WORKBOOK SKILLS In this section, you will learn about: Using the keyboard to select cells Using the keyboard to navigate a workbook Using the mouse to scroll Using the scroll bar shortcut menu Using go to Entering text Entering numbers Shortcuts for data entry Editing data Spell check Saving the workbook with another name Save a workbook as another file type Lesson 3 - Using Basic Workbook Skills ICDL Spreadsheets 3.1 USING THE KEYBOARD TO SELECT CELLS/NAVIGATE A WORKBOOK Concepts You can use the keyboard to select cells or a range of cells in the worksheet. This is done by clicking into the appropriate cell and using the arrow keys on the keyboard to move left, right, up, and down in the worksheet. To select a rectangle area around the active cell, hold down the SHIFT key and press the arrow keys. A4 Invoice No. C 1 Infinity Trading Inc. 2 3 4 Invoice No Products Sales Pep January 5 1001 Laptops May 1,894 6 1002 Keyboards Deborah 2,764 7 1003 Mouse Sarah 1,922 1 1004 LCD Monitors Alvin 3,120 9 1005 Ethernet Cards Levine 2,467 _ 1006 Keyboards CK 3,261 10 1007 Mouse Allan 2,912 11 1008 Ethernet Cards Alex 3,024 12 10091 Graphics Cards Priscilla 2,454 13 1010 Motherboards Linus 3,416 14 1111 Alvin 2,366 Selected Cells 15 Steps To navigate using the keyboard: Open Navigation.xlsx. Page 30 KE000095 © 2017 ICDL Spreadsheets Lesson 3 - Using Basic Workbook Skills 2. Press the RIGHT arrow key to move one cell to the right. The active cell moves one cell to the right. 3. Press the UP arrow key to move one cell up. The active cell moves one cell up. 4. Press the LEFT arrow key to move one t cell to the left. The active cell moves one cell to the left. 5. Press the Page Down key to move one screen down. The active cell moves down one screen. 6. Press Alt + Page Down to move one screen to the right. + The active cell moves one screen to the right. 7. Press Page UP key to move one screen up. The active cell moves up one screen. 8. Press Alt + Page Up to move one screen to the left. + The active cell moves one screen to the left. 9. Press Ctrl + Home to move to the first cell in the worksheet. The active cell moves to the first cell in + the worksheet. © 2017 KE000095 Page 31 Lesson 3 - Using Basic Workbook Skills ICDL Spreadsheets 10. Press Ctrl + End to move to the last cell in the worksheet. Ctrl + End The active cell moves to the last cell in the worksheet. 3.2 USING THE MOUSE TO SCROLL Steps To navigate through the worksheet using the mouse, open Selection.xls: 1. Click cell A1 The Active Cell moves to the first cell in the worksheet. 2. Click the Scroll Arrow at the bottom of the vertical scrollbar 5 times The display scrolls down 5 rows but the Active Cell remains unchanged. 3. Click the Scroll Arrow at the top of the vertical scrollbar 5 times The display scrolls up 5 rows. The Active Cell remains unchanged. 4. Click the Scroll Arrow at the right end of the horizontal scrollbar 5 times The display scrolls 5 columns to the right. The Active Cell remains unchanged. 5. Click the Scroll Arrow at the left end of the horizontal scrollbar 5 times The display scrolls 5 columns to the left. The Active Cell remains unchanged. Page 32 KE000095 © 2017 ICDL Spreadsheets Lesson 3 - Using Basic Workbook Skills 6. Drag the vertical Scroll Box all the way down. The display scrolls within the utilised area of the worksheet (down to row 101). 7. Drag the horizontal Scroll Box all the way to the right. The display scroll within the utilised area of the worksheet (up to column O). 8. Hold down the [Shift] key and then drag the horizontal scroll bar to the right. The display scrolls beyond the utilised area of the worksheet. 9. Press [Ctrl + Home] to move the active cell back the first cell in the worksheet. The Active Cell moves to cell A1. 3.3 USING THE SCROLL BAR SHORTCUT MENU E9 Concepts When you right-click on the vertical or horizontal scroll bars, a menu appears to let you quickly scroll through the worksheet. You can use the menu to scroll to the top, bottom, left edge, right edge, one page up, one page down, one page left or one page right within the worksheet. You can right-click anywhere on the scroll bars and make the sheet scroll to the selected location. © 2017 KE000095 Page 33 Lesson 3 - Using Basic Workbook Skills ICDL Spreadsheets UJ Steps To use the Scroll Bar shortcut menu to scroll through the worksheet: If needed, select cell A1. 1. Right-click on the mid-point of the vertical scroll bar. A shortcut menu is displayed. 2. Click Scroll Here. The shortcut menu disappears and the worksheet scrolls to the position specified. 3. Right-click anywhere on the vertical scroll bar. A shortcut menu is displayed. Scroll Here Top Bottom Page Up Page Down Scroll Up 4. Select Top. Scroll Down The worksheet scrolls to the top of the worksheet. Page 34 KE000095 © 2017 ICDL Spreadsheets Lesson 3 - Using Basic Workbook Skills 5. Right mouse click anywhere on the vertical scroll bar. A shortcut menu is displayed. 6. Select Page Down. Scroll Here The worksheet scrolls one page down. Top Bottom Page Up Page Down Scroll Up Scroll Down Practice the Concept: Right-click in the horizontal scroll bar and select the Left Edge command. Notice that the worksheet scrolls to display column A. Right-click in the vertical scroll bar and select the Top command. Notice that the worksheet scrolls to display row 1. 3.4 USING GO TO O Concepts You can use the Go To command to find and select cells or select cells that contain specific data or types of data such as formulas, blank cells or cells that contain data validation. Go To Window © 2017 KE000095 Page 35 Lesson 3 - Using Basic Workbook Skills ICDL Spreadsheets UJ Steps To use Go To to navigate to a specific cell in the worksheet: If necessary, select cell A1. 1. If necessary, select the Home tab. Home The Home tab is displayed. Click 2. Select Find & Select in the Editing group. The menu appears below. 3. Select Go To... Click Click Go To. The Go To dialog box appears. 4. Type the cell reference E150 in the Reference box. The cell reference is entered in the Reference box. 5. Click OK. The Go To dialog box closes and Click the active cell moves to cell E150. OK Practice the Concept: Open the Go To dialog box by pressing [F5] and go to cell AZ25. Then use [Ctrl+G] to open the Go To dialog box and go to cell A1. © 2017 KE000095 Page 36 ICDL Spreadsheets Lesson 3 - Using Basic Workbook Skills 3.5 ENTERING TEXT E9 Concepts You can enter text into cells by selecting the cell and then typing the text directly in the cell or in the formula bar. Unless formatted differently, the text is aligned to the left. If the length of the entered text is too long to fit in the cell, it will spill over to the adjacent cell if it is empty. It is important to understand that a cell in a worksheet should only contain one element, or type, of data: For example, first name details in one cell and surname details in an adjacent cell. a F Horn* rseft Page Layout Formulas Da ____ Cilibri ' >, >, in 4 U OB Copy - B r Pajtt x er I y.. ' ^^" = T Format Paint Clipboard r. Font r* A9 T X J& c 1 infinity Trading in:. I 2 Monthly Sales Report 3 J Sales Represental lives 5 Alvrfi Jffl 6 PrlsYarn 7 Ale* Quel a Ajron Del a Torre. 9 II Text in Cells Steps To enter text into a worksheet: If necessary, open a new blank workbook. 1. Click cell A1. Cell A1 becomes the Active Cell. © 2017 KE000095 Page 37 Lesson 3 - Using Basic Workbook Skills ICDL Spreadsheets 2. Type the Infinity Trading Inc. The text appears in cell A1 and in the formula bar. 3. Press the Enter key on your keyboard. Enter The Active Cell moves one cell down. Continue by filling in data as shown below: 1 Infinity Trading Inc. 2 Monthly Sales Report 3 4 Sales Representatives 5 Alvin Lim 6 PrisYam 1 Alex duel S Aaron Dela Torre Notice that the text Infinity Trading Inc. spreads across cells A1 to B1. Click cell A1. The Formula Bar shows that all the text is in cell A1. Click cell B1, notice the Formula Bar shows that this cell is empty. Since there is no text in cell B1, the text in cell A1 uses the space in cell B1 to display the text. You can also edit a cell that already has content in it by selecting the appropriate cell and modifying, or deleting the cell contents. 3.6 ENTERING NUMBERS Steps To enter numbers into cell in the worksheet. Page 38 KE000095 © 2017 ICDL Spreadsheets Lesson 3 - Using Basic Workbook Skills 1. Click cell C5. Cell C5 becomes the Active Cell. 2. Type 1870 in the cell. The number is displayed in the cell and in the Formula Bar. 3. Press the Enter key on your keyboard. Enter The Active Cell moves one cell down. Continue by filling in data as shown below: B _ Infinity Trading Inc. D A MonthlySales Report 1 2 Sales Representatives 3 1370 Alvin Lim 4 2360 Pris Yam 5 3390 Alex Quel 6 Aaron Del Torre 1 S 3.7 SHORTCUTS FOR DATA ENTRY Concepts When you have to enter repetitive text in a column, there are a few ways you can avoid having to type the text over and over again. When you type the first few © 2017 KE000095 Page 39 Lesson 3 - Using Basic Workbook Skills ICDL Spreadsheets characters of text that match previously entered text in the same column, Microsoft Excel will complete the entry for you. This feature only works with text or a combination of text and numbers. Numbers and dates will not be automatically completed. Another feature that helps you with data entry is Pick from Drop-down list. This feature allows you to select the data you want to enter from a list of previously entered data. Steps To use data entry shortcuts to repeat an entry in a column: Open Navigation.xlsx from the Student Data Folder. 1. Select cell B15. Select cell B15. The Active Cell moves to cell B15. 2. Begin by typing the letter G in the ' 100S Ethernet Cards Alex 3,024 2 1009 Graphics Cards Priscilla 2,454 cell. 14 13 1010 Motherboards Linus 3,416 1111 Graphics Cards Alvin 2,366 Notice that the text Graphics Cards appears in the cell. 3. Press Enter on your keyboard. Enter The active cell moves to the next row and the completed text is entered in cell B15. Page 40 KE000095 © 2017 ICDL Spreadsheets Lesson 3 - Using Basic Workbook Skills 4. Right mouse click on cell B16. A menu of options is displayed. 5. Select Pick From Drop-down List. A list of previously entered data is displayed. 6. Select Ethernet Cards from the 1010 Motherboards Linus list. 1111 Alvin |E.thernet Cards The selected text is entered in cell Graphics Lards B16. Keyboards Laptops LCD Monitors Motherboards Mouse © 2017 KE000095 Page 41 Lesson 3 - Using Basic Workbook Skills ICDL Spreadsheets 3.8 EDITING DATA O Concepts When you need to edit the data in a cell, you can edit directly in the cell or edit the data in the formula bar. UbJ Steps To edit cell entries in a worksheet. Page 42 KE000095 © 2017 ICDL Spreadsheets Lesson 3 - Using Basic Workbook Skills 1. Select cell D5. Select cell D5. The active cell moves to cell D5. 2. Type 1750 in the cell. The data appears in the cell and in January Sales Rep 1,750 May 2,764 Deborah Select cell C6. the Formula Bar. 3. Select cell C6. Cell C6 becomes the Active Cell. 4. Type Raymond in the selected cell. The data appears in the cell and formula bar. 5. Press Enter on your keyboard. Enter The active cell moves to the next row. 6. Double-click on cell D6. The cell is displayed in editing mode. 7. Position the insertion point to the 8. Press Backspace twice. right of the number 6. Two of the numbers are deleted. The insertion point moves to the selected location. © 2017 KE000095 Page 43 Lesson 3 - Using Basic Workbook Skills ICDL Spreadsheets BC D 9. Type 95 at the insertion point. c. The changes are displayed in the cell and in the formula bar. B c D Products Sales Rep January Laptops May 1,750 Keyboards Raymond 276)4 B c D Products Sales Rep January Laptops May 1,750 Keyboards Raymond 2.95(4 Products Sales Rep January Laptops May 1,750 Keyboards Raymond 2^ Page 44 KE000095 © 2017 10. Press Enter. The active cell moves to the cell below, and the changes are confirmed. 11. Select cell D7. The Active Cell moves to cell D7. 12. Press Delete. The data in cell D7 is deleted. Enter Practice the Concept: Change the number 3120 in cell Select cell D7 D8 to 4320. Delete the entries in cells C14 and Delete I D14 Close the workbook without saving. 3.9 SPELL CHECK O Concepts The spelling tool allows you to automatically find and then correct spelling mistakes in your workbook. B13 Grapics Cards A _ _L D Spelling: English (Ireland) Not in Dictionary: Invoice No. Products e 1001 Laptops 1002 Keyboards _ I tin ore All 1003 Mouse Add to Dictionary 1004 LCD Monitors Grapics 1005 Ethernet Cards 1006 Keyboards 1007 Mouse 1008 Ethernet Cards Suggestions: | Grapics Cards ~ Change Graphic's Infinity Trading Inc. 1009[_( Graphics' Change All 1010 Motherboards V 1111 Nifty Laptop Dictionary language: S'lSlKSi - | Options... I g n o r e © 2017 KE000095 Page 45 O n c ICDL Spreadsheets Lesson 3 - Using Basic Workbook Skills Lesson 3 - Using BasicICDL Workbook Skills Spreadsheets UJ Steps To check the worksheet for spelling errors: From the Student Folder, open SpellCheck.xlsx. If necessary, select cell A1. Review 1. Click the Review tab. The ribbon displays the commands under the Review tab. 2. Click the Spelling button in the Proofing group. The Spelling dialog box opens and the cell containing the first error in the worksheet is selected. 3. Click Change. The mispelled word "Grapics" is replaced with the word Graphics and the next misspelled word is selected. Page 46 KE000095 © 2017 4. The next word identified is Nfinity, click Ignore All to stop Excel from identifying this word as misspelled. The entry is not replaced and the next mispelled word is selected. 5. Continue checking the rest of the worksheet and correct the errors. Click No, if you are prompted to continue checking at the beginning of the sheet. The Spelling Checker window closes. 3.10 SAVING THE WORKBOOK WITH ANOTHER NAME Q Concepts After making changes to the workbook, you might want to keep the original file intact and save the workbook with the latest modification as another file. You can use Save As to save a copy of a workbook with another name, as another file type, or to a new location. © 2017 KE000095 Page 47 ICDL Spreadsheets Lesson 3 - Using Basic Workbook Skills © Info Open Save Print Publish Close Account Feedbac k Options Save As Window Steps To rename an existing workbook: 1. Click the File tab. The Backstage view appears. 2. Click the Save As button. The Save As window is displayed and the current folder is selected. Click 3. Type Sales Report in the File name box. The text is displayed in the File Name box. 4. If necessary, select the location, for example click the navigate up one level button or select the Browse button and navigate to the desired location. The file location is changed. © 2017 KE000095 Page 48 ICDL Spreadsheets Lesson 3 - Using Basic Workbook Skills Close the workbook. 3.11 SAVING A WORKBOOK AS ANOTHER FILE TYPE O Concepts You can save a workbook as another file type, such as template, text file, pdf, csv, a software specific file extension or another version number. Template - it can be useful to save a workbook as a template if it contains a structure that you are likely use again; for example, a quarterly report. Text file - if you want to save the data in the spreadsheet to be used in another application, such as a database, you can save the workbook as a text file. Software specific file - you can save the workbook as another file type, such as Portal Document Format (.pdf). Version number - you can save a version of the workbook that can, for example, be opened by older versions of Excel, such as Excel 1997 - 2003. UJ Steps To save an existing Excel workbook as a different file type: 1. Click the File tab. The Backstage view appears. 2. Click the Save As button. The Save As window is Click displayed and the current folder is selected. © 2017 KE000095 Page 49 Lesson 3 - Using Basic Workbook Skills ICDL Spreadsheets 3. Click the Save as type drop down list in the File name box. A dropdown list of file types appears, such as Excel Template, Excel 1997-2003 Workbook, Text (tab delimited), PDF. 4. Select the desired file type. Select Excel Template (.xltx) The folder location automatically changes to the Custom Office Templates folder. 5. If necessary, change the location from the custom Office Templates folder, for example click the navigate up one level button to return to the Documents folder. The file location is changed. 6. Click the Save button to save the file. The Save As window closes Click and the workbook is saved to R Save the current folder. Close the workbook and delete it from the Documents folder. Page 50 KE000095 © 2017 ICDL Spreadsheets Lesson 3 - Using Basic Workbook Skills 3.12 REVIEW EXERCISE Using Basic Workbook Skills 1. Create a new workbook. 2. Use the keyboard to move the active cell around the worksheet. 3. Use the Go To dialog box to select cell M90. Then, return to cell A1. 4. Enter the text and numbers beginning in cell A1 as shown in the following table: A B 1 Region 2 Northern 20986 3 Southern 35284 4 Central 40436 5 Western 10675 6 Midwest 5. Delete the entry in cell A6. 6. Select cell A1. 7. Save the workbook to the student data folder with the name Region. 8. Close the workbook. 9. Open RegionSales.xlsx. 10. Use the AutoComplete feature to enter the name Jones, P. in cell B9. 11. Use the Pick From Drop-down List feature in cell B10 to enter the name Banes, M. 12. Edit cell C6; change the value from 3952.68 to 3932.68. 13. Enter the number 43567.50 in cell C9. Note: Notice that the ending zero (0) is dropped. 14. Enter the number 33500.7 in cell C10. © 2017 KE000095 Page 51 Lesson 3 - Using Basic Workbook Skills ICDL Spreadsheets 15. Create a new workbook based on the Personal Monthly Budget template. Change the Actual Monthly Income figure in cell E7 to 2000. Change the Extra income figure in cell E8 to 0. View the Actual Balance figure in cell J6. 16. Save the workbook with the name My Budget to the student data folder. 17. Close the workbook. 18. Use the Open dialog box and delete the Regions folder and its contents. Close the Open dialog box. Upon completion of this review exercise, delete the new files that have been created. Page 52 KE000095 © 2017 LESSON 4 - SELECTION In this section, you will learn about: Selecting a cell Selecting a range of adjacent cells Selecting a range of non-adjacent cells Selecting the entire worksheet Selecting a row Selecting a range of adjacent rows Selecting a range of non-adjacent rows Selecting an entire column Selecting a range of columns Lesson 4 - Selection ICDL Spreadsheets Selecting a range of non-adjacent columns Page 54 KE000095 © 2017 ICDL Spreadsheets Lesson 4 - Selection 4.1 SELECTING A CELL E9 Concepts You can quickly select cells, ranges, rows, or columns, or all data on a worksheet — for example, to format the data in the selection, or to insert other cells, rows, or columns. You can also select all or part of the cell contents and turn on Editing mode so that you can modify the data. 0 *3 ' C*' ' File nsert Page Layout Formulas Data Rev &Cut Calibri - 11 - A* A = -_ L-! ^ Copy » Paste - BIU- T v Format Painter El - 'A- Clipboard (5 F ont ri A9 T : x s/ A — 1 [ ------------------------ 1B1c1 D 1 1 Infinity Trading Ir k- r Select All ror 2 Monthly Sales Re t ----------------------------------------------► Button 3 L Selecting All Cells Steps To select a cell: Open Selection.xlsx. © 2017 KE000095 Page 55 Lesson 4 - Selection ICDL Spreadsheets 4.2 SELECTING A RANGE OF ADJACENT CELLS Steps To select a range of adjacent cells: 1. Click on cell A4 and then drag to Infinity Trading Ire. cell D10. Release the mouse button. Invoice No. Products Sales Rep January The range is selected. 1001 Laptops May 1PB94 1002 Keyboards Deborah 2,764 1003 Mouse Sarah 1PS22 1004 LCD Monitors Alvin 3,120 1005 Ethernet Cards Levine 2P467 1006 Keyboards CK 3,261 Click any cell in the worksheet to deselect the range. 4.3 SELECTING A RANGE OF NON-ADJACENT CELLS Steps To select a range of non-adjacent cells: 1. Click on cell A4 and then drag to Invoice Mo. Products cell A10. Release the mouse 1001 Laptops button. 1002 Keyboards 1003 Mouse The range is selected. Infinity Trading Inc. 1004 LCD Monitors 1005 Ethernet Cards 1006 Keyboards 2. Press and Hold down the Ctrl key on the keyboard. The Ctrl key is pressed. Ctrl Page 56 KE000095 © 2017 ICDL Spreadsheets Lesson 4 - Selection 3. Click on cell C4 and then drag to cell C10. Release the mouse Infinity Trading Inc. button and Ctrl key. Sales Reo Invoice No. Products May 1001 Laptops Deborah 1002 Keyboards Sarah 1003 Mouse Alvin 1004 LCD Monitors Click any cell in the worksheet to deselect the range. 4.4 SELECTING THE ENTIRE WORKSHEET Steps To select all cells in a worksheet: Click any cell in the worksheet to deselect the range. 4.5 SELECTING A ROW Steps To select a row: Click any cell in the worksheet to deselect the range. © 2017 KE000095 Page 57 Lesson 4 - Selection ICDL Spreadsheets Click any cell in 4.6 SELECTING A RANGE OF ADJACENT ROWS the worksheet to deselect the range. Steps To select a range of contiguous rows. 1. Click on row heading 4 and drag 3 4 Invoice No. Products Sales Rep January to row heading 10. Release the S 1001 Laptops May lp894 mouse button. 6 1002 Keyboards Deborah 2,764 7

Spreadsheets 2016 Training Notes PDF

Document Details

Tags

Related

Summary

Full Transcript

Upgrade to continue