Beginning Excel 2019 PDF

Beginning Excel 2019 Beginning Excel 2019 NOREEN BROWN; BARBARA LAVE; HALLIE PUNCOCHAR; JULIE ROMEY; MARY SCHATZ; ART SCHNEIDER; AND DIANE SHINGLEDECKER Open Oregon Educational Resources Beginning Excel 2019 by Noreen Brown; Barbara Lave; Hallie Puncochar; Julie Romey; Mary Schatz; Art Schneider;...

Beginning Excel 2019 Beginning Excel 2019 NOREEN BROWN; BARBARA LAVE; HALLIE PUNCOCHAR; JULIE ROMEY; MARY SCHATZ; ART SCHNEIDER; AND DIANE SHINGLEDECKER Open Oregon Educational Resources Beginning Excel 2019 by Noreen Brown; Barbara Lave; Hallie Puncochar; Julie Romey; Mary Schatz; Art Schneider; and Diane Shingledecker is licensed under a Creative Commons Attribution 4.0 International License, except where otherwise noted. This work is licensed under the Creative Commons Attribution 4.0 International License. To view a copy of this license, visit http://creativecommons.org/licenses/by/4.0/.  Contents Introduction vii CHAPTER 1 – FUNDAMENTAL SKILLS 1.1 Overview of Microsoft Excel 2 1.2 Entering, Editing, and Managing Data 22 1.3 Formatting and Data Analysis 49 1.4 Printing 74 1.5 Chapter Practice 80 1.6 Scored Assessment 83 CHAPTER 2 – MATHEMATICAL COMPUTATIONS 2.1 Formulas 86 2.2 Introductory Statistical Functions 102 2.3 Functions for Personal Finance 126 2.4 Preparing to Print 144 2.5 Chapter Practice 151 2.6 Chapter Scored 157 CHAPTER 3 – FORMULAS, FUNCTIONS, LOGICAL AND LOOKUP FUNCTIONS 3.1 More on Formulas and Functions 162 3.2 Logical and Lookup Functions 169 3.3 Conditional Formatting 181 3.4 Preparing to Print 190 3.5 Chapter Practice 202 3.6 Chapter Scored 205 CHAPTER 4 – PRESENTING DATA WITH CHARTS 4.1 Choosing a Chart Type 208 4.2 Formatting Charts 239 4.3 Using Charts with Microsoft® Word® and Microsoft® PowerPoint® 245 4.4 Preparing to Print 253 4.5 Chapter Practice 259 4.6 Scored Assessment 266 CHAPTER 5 – TABLES 5.1 Table Basics 271 5.2 Intermediate Table Skills 294 5.3 Preparing to Print 331 5.4 Chapter Practice 337 5.5 Scored Assessment 344 CHAPTER 6 – MULTIPLE SHEET FILES 6.1 Multiple Sheet Basics 348 6.2 Formulas with 3-D References 358 6.3 Templates 363 6.4 Preparing to Print 367 6.5 Chapter Practice 371 6.6 Scored Assessment 375 Appendix 378 Introduction This core Microsoft® Excel® text provides students with the skills needed to execute many per- sonal and professional activities. It also prepares them to go on to more advanced skills using the Excel software. The text takes the approach of making decisions using Excel. Personal decisions introduced include important purchases, such as homes and automobiles, savings for retirement, and personal budgets. Professional decisions include budgets for managing expenses, merchan- dise items to mark down or discontinue, and inventory management. Students are given clear, easy-to-follow instructions for each skill presented and are also provided with opportunities to learn additional skills related to the personal or professional objectives presented. For example, students learn the key terms with respect to home mortgages and understand the impact interest rates have on monthly mortgage payments. This text also places an emphasis on “what-if” sce- narios so students gain an appreciation for the computational power of the Excel application. In addition, students learn how Excel is used with Microsoft® Word® and Microsoft® PowerPoint® to accomplish a variety of personal and professional objectives. Screenshots that appeared in How to Use Microsoft Excel: The Careers in Practice Series, adapted by The Saylor Foundation, were used with permission from Microsoft Corporation, which owns their copyright. How to Use Microsoft® Excel®: The Careers in Practice Series is an independent publication and is not affiliated with, nor has it been authorized, sponsored, or otherwise approved by Microsoft Corporation. Our adapted work uses all Microsoft Excel screenshots under fair use. If you plan to redistribute our book, please consider whether your use is also fair use. ATTRIBUTION Adapted from How to Use Microsoft Excel: The Careers in Practice Series, adapted by The Saylor Foundation without attribution as requested by the work’s original creator or licensee, and licensed under CC BY-NC-SA 3.0. vii  CHAPTER 1 – FUNDAMENTAL SKILLS Microsoft® Excel® is a tool that can be used in virtually all careers and is valuable in both pro- fessional and personal settings. Whether you need to keep track of medications in inventory for a hospital or create a financial plan for your retirement, Excel enables you to do these activities effi- ciently and accurately. This chapter introduces the fundamental skills necessary to get you started in using Excel. You will find that just a few skills can make you very productive in a short period of time. ATTRIBUTION Adapted from How to Use Microsoft Excel: The Careers in Practice Series, adapted by The Saylor Foundation without attribution as requested by the work’s original creator or licensee, and licensed under CC BY-NC-SA 3.0. 1 1.1 Overview of Microsoft Excel Learning Objectives 1. Examine the value of using Excel to make decisions. 2. Learn how to start Excel. 3. Become familiar with the Excel workbook. 4. Understand how to navigate worksheets. 5. Examine the Excel Ribbon. 6. Examine the right-click menu options. 7. Learn how to save workbooks. 8. Examine the Status Bar. 9. Become familiar with the features in the Excel Help window. Microsoft® Office contains a variety of tools that help people accomplish many personal and professional objectives. Microsoft Excel is perhaps the most versatile and widely used of all the Office applications. No matter which career path you choose, you will likely need to use Excel to accomplish your professional objectives, some of which may occur daily. This chapter provides an overview of the Excel application along with an orientation for accessing the commands and fea- tures of an Excel workbook. MAKING DECISIONS WITH EXCEL Taking a very simple view, Excel is a tool that allows you to enter quantitative data into an elec- tronic spreadsheet to apply one or many mathematical computations. These computations ulti- mately convert that quantitative data into information. The information produced in Excel can be used to make decisions in both professional and personal contexts. For example, employees can use Excel to determine how much inventory to buy for a clothing retailer, how much medication to administer to a patient, or how much money to spend to stay within a budget. With respect to personal decisions, you can use Excel to determine how much money you can spend on a house, how much you can spend on car lease payments, or how much you need to save to reach your 2 3 DIANE SHINGLEDECKER retirement goals. We will demonstrate how you can use Excel to make these decisions and many more throughout this text. Figure 1.1 shows a completed Excel worksheet that will be constructed in this chapter. The infor- mation shown in this worksheet contains sales data for a hypothetical merchandise retail com- pany. The worksheet data can help a retailer analyze the business and determine the number of salespeople needed for each month for example. Figure 1.1 Example of an Excel Worksheet STARTING EXCEL 1. Locate Excel on your computer. 2. Click Microsoft Excel to launch the Excel application where you are presented with work- book options to help get you started. 3. Click the first option; “Blank Workbook”. BEGINNING EXCEL 2019 4 EXCEL FOR WINDOWS VS EXCEL FOR MAC The Excel for Windows and Excel for Mac software versions are very similar. Most of the features, tools and commands are available in both versions. There are, however, some differences with the Excel interface. There are also a few features that are not available in the Excel for Mac version. The screenshots and step-by-step instructions in this textbook are specific to Excel for Windows. We have attempted to provide alternate screenshots and instructions for the Mac version when the differences are significant. When you see this icon , it means we are providing information specific to Mac users. The Excel Workbook A workbook is an Excel file that contains one or more worksheets (referred to as spreadsheets). Excel will assign a file name to the workbook, such as Book1, Book2, Book3, and so on, depending on how many new workbooks are opened. Figure 1.2 shows a blank workbook after starting Excel. Take some time to familiarize yourself with this screen. Your screen may be slightly different based on the version you’re using. 5 DIANE SHINGLEDECKER Figure 1.2 Blank Workbook BEGINNING EXCEL 2019 6 Your workbook should already be maximized (or shown at full size) once Excel is started, as Figure 1.2a Blank Workbook (right-side) shown in Figure 1.2. However, if your screen looks like Figure 1.3 after starting Excel, you should click the Maximize button, as shown in the figure. 7 DIANE SHINGLEDECKER Figure 1.3 Restored Worksheet NAVIGATING WORKSHEETS Data are entered and managed in an Excel worksheet. The worksheet contains several rectangles called cells for entering numeric and non-numeric data. Each cell in an Excel worksheet contains an address, which is defined by a column letter followed by a row number. For example, the cell that is currently activated in Figure 1.3 is A1. This would be referred to as cell location A1 or cell reference A1. The following steps explain how you can navigate in an Excel worksheet: 1. Place your mouse pointer over cell D5 and click. 2. Check to make sure column letter D and row number 5 are highlighted, as shown in Fig­ ure 1.4. BEGINNING EXCEL 2019 8 Figure 1.4 Activating a Cell Location 1. Move the mouse pointer to cell A1. 2. Click and hold the left mouse button and drag the mouse pointer back to cell D5. 3. Release the left mouse button. You should see several cells highlighted, as shown in Fig­ ure 1.5. This is referred to as a cell range and is documented as follows: A1:D5. Any two cell locations sepa- rated by a colon are known as a cell range. The first cell is the top left corner of the range, and the second cell is the lower right corner of the range. Figure 1.5 Highlighting a Range of Cells 9 DIANE SHINGLEDECKER 1. At the bottom of the screen, you’ll see a sheet tab indicated by “Sheet1″. Clicking on the + adds additional worksheets. This is how you open or add a worksheets within a work- book. To see how this works, click on the + to add another worksheet so that you now have two sheets 2. Click the Sheet1 worksheet tab at the bottom of the worksheet to return to the work- sheet shown in Figure 1.5. Keyboard Shortcuts Basic Worksheet Navigation Use the arrow keys on your keyboard to activate cells on the worksheet. Hold the SHIFT key and press the arrow keys on your keyboard to highlight a range of cells in a worksheet. Hold the CTRL key while pressing the PAGE DOWN or PAGE UP keys to open other worksheets in a workbook. Mac Users: Hold down the Fn and Command keys and press the left or right arrow keys THE EXCEL RIBBON Excel’s features and commands are found in the Ribbon, which is the upper area of the Excel screen that contains several tabs running across the top. Each tab provides access to a different set of Excel commands. Figure 1.6 shows the commands available in the Home tab of the Rib- bon. Table 1.1 “Command Overview for Each Tab of the Ribbon” provides an overview of the commands that are found in each tab of the Ribbon. Figure 1.6 Home Tab of Ribbon BEGINNING EXCEL 2019 10 The Excel for Mac ribbon, as shown in Figure 1.6a below, has two primary differences: ◦ The older dropdown menu structure is still available with Excel for Mac. ◦ The specific commands and tools within each tab are slightly different between the two Excel Ribbons. Some of the commands found within the Excel for Win- dows Ribbon tabs are located within the dropdown menu structure in the Excel for Mac version. So, if you can’t find the tool on the Excel for Mac Ribbon, then try to find the tool by looking through the dropdown menu instead. Figure 1.6a Home tab of Excel for Mac Ribbon with dropdown menu structure Group Title Names on the Ribbon If you look closely at the Excel Ribbon (See Figure 1.6 above), you will see that the Ribbon is sep- arated in groups of tool buttons, and each group has a title name. On Home tab, the group title names are “Clipboard”, “Font”, “Alignment”, “Number”, “Styles”. “Cells”, “Editing”, etc. The tool but- tons within each group are all related to the group title. Mac Users Only: The default “View” for the Excel for Mac ribbon does not display these “group title names”. Notice in Figure 1.6a above, there are no group title names. It is a good idea to change this “view” so you can see the group title names. Here are the steps: 1. Click the “Excel” menu option at top left above the Ribbon 2. Choose “Preferences” 3. Click the “View” button 4. Scroll down and check the box for “Group Titles” 5. Close the “View” dialog box. The group title names should now display as shown in Fig- ure 6.1 (not Figure 6.1a) above Table 1.1 Command Overview for Each Tab of the Ribbon 11 DIANE SHINGLEDECKER Tab Name Description of Commands Also known as the Backstage view of the Excel workbook. Contains all commands for opening, closing, saving, and File creating new Excel workbooks. Includes print commands, document properties, e-mailing options, and help features. The default settings and options are also found in this tab. Contains the most frequently used Excel commands. Formatting commands are found in this tab along with Home commands for cutting, copying, pasting, and for inserting and deleting rows and columns. Insert Used to insert objects such as charts, pictures, shapes, PivotTables, Internet links, symbols, or text boxes. Page Contains commands used to prepare a worksheet for printing. Also includes commands used to show and print the Layout gridlines on a worksheet. Includes commands for adding mathematical functions to a worksheet. Also contains tools for auditing mathematical Formulas formulas. Used when working with external data sources such as Microsoft® Access®, text files, or the Internet. Also contains Data sorting commands and access to scenario tools. Includes Spelling and Track Changes features. Also contains protection features to password protect worksheets or Review workbooks. View Used to adjust the visual appearance of a workbook. Common commands include the Zoom and Page Layout view. This tab provides access to help and support features such as contacting Microsoft support, sending feedback, Help suggesting a new feature, and community discussion groups. This tab is not available with Excel for Mac. Provides drawing options for using a digital pen, mouse or finger depending on the type of device (laptop with touch Draw screen, tablet, computer, etc). This tab is not visible by default. See below on how to customize the Ribbon to add or remove tabs. Provides access to some advanced features such as macros, form controls, and XML commands. This tab is not visible Developer by default. See below on how to customize the Ribbon to add or remove tabs. The Ribbon shown in Figure 1.6 and Figure 1.6a (above) is full, or maximized. The benefit of hav- ing a full Ribbon is that the commands are always visible while you are developing a worksheet. However, depending on the screen dimensions of your computer, you may find that the Ribbon takes up too much vertical space on your worksheet. If this is the case, you can minimize the Rib- bon by clicking the button shown in Figure 1.6. When minimized, the Ribbon will show only the tabs and not the command buttons. When you click on a tab, the command buttons will appear until you select a command or click anywhere on your worksheet. To hide the Ribbon with Excel for Mac you can use the keyboard shortcut: Hold down the “Command and Option” keys and tap the “R” key The same keyboard shortcut will unhide the Ribbon as well. HOW TO CUSTOMIZE THE EXCEL RIBBON Here are the steps to add additional tabs to the Excel Ribbon 1. Click the File tab and choose Options BEGINNING EXCEL 2019 12 2. Click on “Customize Ribbon” at the left side of the Options screen 3. Click the checkbox next to the Tab name that you want to add (See Figure 1.7 below) Figure 1.7 Customize the Ribbon Dialog Box Keyboard Shortcuts Minimizing or Maximizing the Ribbon Hold down the CTRL key and press the F1 key. Hold down the CTRL key and press the F1 key again to maximize the Ribbon. Mac Users: Hold down the Command and Option keys and press R QUICK ACCESS TOOLBAR AND RIGHT-CLICK MENU The Quick Access Toolbar is found at the upper left side of the Excel screen above the Ribbon, as 13 DIANE SHINGLEDECKER shown in Figure 1.7. This area provides access to the most frequently used commands, such as Save and Undo. You also can customize the Quick Access Toolbar by adding commands that you use on a regular basis. By placing these commands in the Quick Access Toolbar, you do not have to navigate through the Ribbon to find them. To customize the Quick Access Toolbar, click the down arrow as shown in Figure 1.8. This will open a menu of commands that you can add to the Quick Access Toolbar. If you do not see the command you are looking for on the list, select the More Commands option. Figure 1.8 Customizing the Quick Access Toolbar In addition to the Ribbon and Quick Access Toolbar, you can also access many commands by right clicking anywhere on the worksheet. Figure 1.9 shows an example of the commands available in the right-click menu. There is no “Right-click” option for Excel for Mac. To access the same commands with Excel for Mac, hold down the Control key and click the mouse button. BEGINNING EXCEL 2019 14 Figure 1.9 Right-Click Menu THE FILE TAB The File tab is also known as the Backstage view of the workbook. It contains a variety of features and commands related to the workbook that is currently open, new workbooks, or workbooks stored in other locations on your computer or network. Figure 1.10 shows the options available in the File tab or Backstage view. To leave the Backstage view and return to the worksheet, click the arrow in the upper left-hand corner as shown below. 15 DIANE SHINGLEDECKER Figure 1.10 File Tab or Backstage View of a Workbook Included in the File tab are the default settings for the Excel application that can be accessed and modified by clicking the Options button. Figure 1.11 shows the Excel Options window, which gives you access to settings such as the default font style, font size, and the number of worksheets that appear in new workbooks. BEGINNING EXCEL 2019 16 Figure 1.11 Excel Options Window To access these same options in Excel for Mac, you must click the “Excel” menu option and choose “Preferences” (see Figure 1.12 below) 17 DIANE SHINGLEDECKER Figure 1.12 The Excel for Mac “Excel” menu option SAVING WORKBOOKS (SAVE AS) Once you create a new workbook, you will need to change the file name and choose a location on your computer or network to save that file. It is important to remember where you save this workbook on your computer or network as you will be using this file in the Section 1.2 “Enter­ ing, Editing, and Managing Data” to construct the workbook shown in Figure 1.1. The process of saving can be different with different versions of Excel. Please be sure you follow the steps for the version of Excel you are using. The following steps explain how to save a new workbook and assign it a file name. SAVING WORKBOOKS IN EXCEL 365 1. If you have not done so already, open a blank workbook in Excel. 2. Click the File tab and then the Save As button in the left side of the Backstage view win- dow. This will open the Save As dialog box. 3. Determine a location for saving on your computer by clicking Browse on the left side to open the Save As dialog box. 4. Click in the File Name box near the bottom of the Save As dialog box. Type the new file name: CH1 Merchandise City Sales Data 5. Review the settings in the screen for correctness and click the Save button. BEGINNING EXCEL 2019 18 Figure 1.13 Save As Dialog entries for Excel 365 Keyboard Shortcuts Save As Press the F12 key and use the tab and arrow keys to navigate around the Save As dialog box. Use the ENTER key to make a selection. Or press the ALT key on your keyboard. You will see letters and numbers, called Key Tips, appear on the Ribbon. Press the F key on your keyboard for the File tab and then the A key. This will open the Save As dialog box. The Mac shortcut is: Hold down the Command and Shift keys and press S 19 DIANE SHINGLEDECKER Skill Refresher Saving Workbooks (Save As) 1. Click the File tab on the Ribbon. 2. Click the Save As option. 3. Click on Browse to select a location on your PC to save. 4. Click in the File name box and type a new file name if needed. 5. Click the down arrow next to the “Save as type” box and select the appropriate file type if needed. Excel will default to the file type of.xlsx 6. Click the Save button. THE STATUS BAR The Status Bar is located below the worksheet tabs on the Excel screen (see Figure 1.13). It dis- plays a variety of information, such as the status of certain keys on your keyboard (e.g., CAPS LOCK), the available views for a workbook, the magnification of the screen, and mathematical functions that can be performed when data are highlighted on a worksheet. You can customize the Status Bar as follows: 1. Place the mouse pointer over any area of the Status Bar and right click to display the “Customize Status Bar” list of options (see Figure 1.14). Mac Users: use “Control-click” on the Status Bar to display the “Customize Status Bar” options. 2. Select the Caps Lock option from the menu (see Figure 1.14). 3. Press the CAPS LOCK key on your keyboard. You will see the Caps Lock indicator on the lower right side of the Status Bar. 4. Press the CAPS LOCK on your keyboard again. The indicator on the Status Bar goes away. BEGINNING EXCEL 2019 20 Figure 1.14 Customizing the Status Bar EXCEL HELP The Help feature provides extensive information about the Excel application. Although some of this information may be stored on your computer, the Help window will automatically connect to the Internet, if you have a live connection, to provide you with resources that can answer most of your questions. You can open the Excel Help window by clicking the question mark in the upper right area of the screen or ribbon. With newer versions of Excel, use the query box to enter your question and select from helpful option links or select the question mark from the dropdown list to launch Excel Help windows. 21 DIANE SHINGLEDECKER Figure 1.15 Excel Help Window Keyboard Shortcuts Excel Help Press the F1 key on your keyboard. Mac Users: Press F1 or hold down the Command key and press / Key Takeaways Excel is a powerful tool for processing data for the purposes of making decisions. You can find Excel commands throughout the tabs in the Ribbon. You can customize the Quick Access Toolbar by adding commands you frequently use. You can add or remove the information that is displayed on the Status Bar. The Help window provides you with extensive information about Excel. ATTRIBUTION Adapted by Barbara Lave from How to Use Microsoft Excel: The Careers in Practice Series, adapted by The Saylor Foundation without attribution as requested by the work’s original creator or licensee, and licensed under CC BY-NC-SA 3.0. 1.2 Entering, Editing, and Managing Data Learning Objectives 1. Understand how to enter data into a worksheet. 2. Examine how to edit data in a worksheet. 3. Examine how Auto Fill is used when entering data. 4. Understand how to delete data from a worksheet and use the Undo command. 5. Examine how to adjust column widths and row heights in a worksheet. 6. Understand how to hide columns and rows in a worksheet. 7. Examine how to insert columns and rows into a worksheet. 8. Understand how to delete columns and rows from a worksheet. 9. Learn how to move data to different locations in a worksheet. In this section, we will begin the development of the workbook shown in Figure 1.1. The skills cov- ered in this section are typically used in the early stages of developing one or more worksheets in a workbook. ENTERING DATA You will begin building the workbook shown in Figure 1.1 by manually entering data into the work- sheet. The following steps explain how the column headings in Row 2 are typed into the work- sheet: 1. Click cell location A2 on the worksheet. 2. Type the word Month. 3. Press the RIGHT ARROW key. This will enter the word into cell A2 and activate the next cell to the right. 4. Type Unit Sales and press the RIGHT ARROW key. 5. Repeat step 4 for the words Average Price and then again for Sales Dollars. 22 23 DIANE SHINGLEDECKER Figure 1.15 shows how your worksheet should appear after you have typed the column headings into Row 2. Notice that the word Price in cell location C2 is not visible. This is because the column is too narrow to fit the entry you typed. We will examine formatting techniques to correct this problem in the next section. Figure 1.15 Entering Column Headings into a Worksheet Integrity Check Column Headings It is critical to include column headings that accurately describe the data in each column of a worksheet. In professional environments, you will likely be sharing Excel workbooks with coworkers. Good column head- ings reduce the chance of someone misinterpreting the data contained in a worksheet, which could lead to costly errors depending on your career. BEGINNING EXCEL 2019 24 1. Click cell B3. 2. Type the number 2670 and press the ENTER key. After you press the ENTER key, cell B4 will be activated. Using the ENTER key is an efficient way to enter data vertically down a column. 3. Enter the following numbers in cells B4 through B14: 2160, 515, 590, 1030, 2875, 2700, 900, 775, 1180, 1800, and 4560. 4. Click cell C3. 5. Type the number 9.99 and press the ENTER key. 6. Enter the following numbers in cells C4 through C14: 12.49, 14.99, 17.49, 14.99, 12.49, 9.99, 19.99, 19.99, 19.99, 17.49, and 14.99. 7. Click cell D3. 8. Type the number 26685 and press the ENTER key. 9. Enter the following numbers in cells D4 through D14: 26937, 7701, 10269, 15405, 35916, 26937, 17958, 15708, 23562, 31416, and 75125. 10. When finished, check that the data you entered matches Figure 1.16. Why? Avoid Formatting Symbols When Entering Numbers When typing numbers into an Excel worksheet, it is best to avoid adding any formatting symbols such as dol- lar signs and commas. Although Excel allows you to add these symbols while typing numbers, it slows down the process of entering data. It is more efficient to use Excel’s formatting features to add these symbols to numbers after you type them into a worksheet. Integrity Check Data Entry 25 DIANE SHINGLEDECKER It is very important to proofread your worksheet carefully, especially when you have entered numbers. Trans- posing numbers when entering data manually into a worksheet is a common error. For example, the number 563 could be transposed to 536. Such errors can seriously compromise the integrity of your workbook. Integrity Check Figure 1.16 shows how your worksheet should appear after entering the data. Check your numbers carefully to make sure they are accurately entered into the worksheet. BEGINNING EXCEL 2019 26 Figure 1.16 Completed Data Entry for Columns B, C, and D EDITING DATA Data that has been entered in a cell can be changed by double clicking the cell location or using the Formula Bar. You may have noticed that as you were typing data into a cell location, the data you typed appeared in the Formula Bar. The Formula Bar can be used for entering data into cells as well as for editing data that already exists in a cell. The following steps provide an example of entering and then editing data that has been entered into a cell location: 1. Click cell A15 in the Sheet1 worksheet. 2. Type the abbreviation Tot and press the ENTER key. 3. Click cell A15. 4. Move the mouse pointer up to the Formula Bar. You will see the pointer turn into a cur- sor. Move the cursor to the end of the abbreviation Tot and left click. 27 DIANE SHINGLEDECKER 5. Type the letters al to complete the word Total. 6. Click the check mark to the left of the Formula Bar (see Figure 1.17). This will enter the change into the cell. Figure 1.17 Using the Formula Bar to Edit and Enter Data 1. Double click cell A15. 2. Add a space after the word Total and type the word Sales. 3. Press the ENTER key. BEGINNING EXCEL 2019 28 Keyboard Shortcuts Editing Data in a Cell Activate the cell that is to be edited and press the F2 key on your keyboard. ◦ Same for Mac Users AUTO FILL The Auto Fill feature is a valuable tool when manually entering data into a worksheet. This feature has many uses, but it is most beneficial when you are entering data in a defined sequence, such as the numbers 2, 4, 6, 8, and so on, or nonnumeric data such as the days of the week or months of the year. The following steps demonstrate how Auto Fill can be used to enter the months of the year in Column A: 1. Click cell A3 in the Sheet1 worksheet. 2. Type the word January and press the ENTER key. 3. Click cell A3 again. 4. Move the mouse pointer to the lower right corner of cell A3. You will see a small square in this corner of the cell; this is called the Fill Handle (See Figure 1.18) When the mouse pointer gets close to the Fill Handle, the white block plus sign will turn into a black plus (+) sign. 29 DIANE SHINGLEDECKER Figure 1.18 Fill Handle Left click and drag the Fill Handle to cell A14. Notice that the Auto Fill tip box indicates what month will be placed into each cell (see Figure 1.19). Release the mouse button when the tip box reads “December.” BEGINNING EXCEL 2019 30 Figure 1.19 Using Auto Fill to Enter the Months of the Year Once you release the left mouse button, all twelve months of the year should appear in the cell range A3:A14, as shown in Figure 1.20. You will also see the Auto Fill Options button. By clicking this button, you have several options for inserting data into a group of cells. 31 DIANE SHINGLEDECKER Figure 1.20 Auto Fill Options Button 1. Click the Auto Fill Options button. 2. Click the Copy Cells option. This will change the months in the range A4:A14 to January. 3. Click the Auto Fill Options button again. 4. Click the Fill Months option to return the months of the year to the cell range A4:A14. The Fill Series option will provide the same result. DELETING DATA AND THE UNDO COMMAND There are several methods for removing data from a worksheet, a few of which are demonstrated BEGINNING EXCEL 2019 32 here. With each method, you use the Undo command. This is a helpful command in the event you mistakenly remove data from your worksheet. The following steps demonstrate how you can delete data from a cell or range of cells: 1. Click cell C2. 2. Press the DELETE key on your keyboard. This removes the contents of the cell. Mac Users: Hold down the Fn key and press the Delete key 3. Highlight the range C3:C14. Then left click and drag the mouse pointer down to cell C14. 4. Place the mouse pointer over the Fill Handle. You will see the white block plus sign change to a black plus sign (+). 5. Click and drag the mouse pointer up to cell C3 (see Figure 1.21). Release the mouse but- ton. The contents in the range C3:C14 will be removed. Figure 1.21 Using Auto Fill to Delete Contents of Cell 1. Click the Undo button in the Quick Access Toolbar (see Figure 1.2). This should replace 33 DIANE SHINGLEDECKER the data in the range C3:C14. 2. Click the Undo button again. This should replace the data in cell C2. Keyboard Shortcuts Undo Command Hold down the CTRL key while pressing the letter Z on your keyboard. ◦ Same for Mac Users. Highlight the range C2:C14 by placing the mouse pointer over cell C2. Then left click and drag the mouse pointer down to cell C14. Click the Clear button in the Home tab of the Ribbon, which is next to the Cells group of commands (see Figure 1.22). This opens a drop-down menu that contains several options for removing or clearing data from a cell. Notice that you also have options for clearing just the formats in a cell or the hyperlinks in a cell. Click the Clear All option. This removes the data in the cell range. Click the Undo button. This replaces the data in the range C2:C14. BEGINNING EXCEL 2019 34 Figure 1.22 Clear Command Drop-Down Menu ADJUSTING COLUMNS AND ROWS There are a few entries in the worksheet that appear cut off. For example, the last letter of the word September cannot be seen in cell A11. This is because the column is too narrow for this word. The columns and rows on an Excel worksheet can be adjusted to accommodate the data that is being entered into a cell using three different methods. The following steps explain how to adjust the column widths and row heights in a worksheet: 1. Bring the mouse pointer between Column A and Column B in the Sheet1 worksheet, as shown in Figure 1.23. You will see the white block plus sign turn into double arrows. 2. Click and drag the column to the right so the entire word September in cell A11 can be seen. As you drag the column, you will see the column width tip box. This box displays the number of characters that will fit into the column using the Calibri 11-point font which is the default setting for font/size. 3. Release the left mouse button. 35 DIANE SHINGLEDECKER Figure 1.23 Adjusting Column Widths You may find that using the click-and-drag method is inefficient if you need to set a specific char- acter width for one or more columns. Steps 1 through 6 illustrate a second method for adjusting column widths when using a specific number of characters: 1. Click any cell location in Column A by moving the mouse pointer over a cell location and clicking the left mouse button. You can highlight cell locations in multiple columns if you are setting the same character width for more than one column. 2. In the Home tab of the Ribbon, left click the Format button in the Cells group. 3. Click the Column Width option from the drop-down menu. This will open the Column Width dialog box. 4. Type the number 13 and click the OK button on the Column Width dialog box. This will set Column A to this character width (see Figure 1.24). 5. Once again bring the mouse pointer between Column A and Column B so that the dou- ble arrow pointer displays and then double-click to activate AutoFit. This features adjusts BEGINNING EXCEL 2019 36 the column width based on the longest entry in the column. 6. Use the Column Width dialog box (step 6 above) to reset the width to 13. Figure 1.24 Column Width Dialog Box Keyboard Shortcuts Column Width Press the ALT key on your keyboard, then press the letters H, O, and W one at a time. This keyboard shortcut is not available for Excel for Mac Steps 1 through 4 demonstrate how to adjust row height, which is similar to adjusting column width: 1. Click cell A15. 2. In the Home tab of the Ribbon, left click the Format button in the Cells group. 3. Click the Row Height option from the drop-down menu. This will open the Row Height dialog box. 37 DIANE SHINGLEDECKER 4. Type the number 24 and click the OK button on the Row Height dialog box. This will set Row 15 to a height of 24 points. A point is equivalent to approximately 1/72 of an inch. This adjustment in row height was made to create space between the totals for this worksheet and the rest of the data. Keyboard Shortcuts Row Height Press the ALT key on your keyboard, then press the letters H, O, and H one at a time. This keyboard shortcut is not available for Excel for Mac Figure 1.25 shows the appearance of the worksheet after Column A and Row 15 are adjusted. BEGINNING EXCEL 2019 38 Figure 1.25 Sales Data with Column A and Row 15 Adjusted Skill Refresher Adjusting Columns and Rows 1. Activate at least one cell in the row or column you are adjusting. 2. Click the Home tab of the Ribbon. 3. Click the Format button in the Cells group. 4. Click either Row Height or Column Width from the drop-down menu. 39 DIANE SHINGLEDECKER 5. Enter the Row Height in points or Column Width in characters in the dialog box. 6. Click the OK button. HIDING COLUMNS AND ROWS In addition to adjusting the columns and rows on a worksheet, you can also hide columns and rows. This is a useful technique for enhancing the visual appearance of a worksheet that contains data that is not necessary to display. These features will be demonstrated using the GMW Sales Data workbook. However, there is no need to have hidden columns or rows for this worksheet. The use of these skills here will be for demonstration purposes only. 1. Click cell C1. 2. Click the Format button in the Home tab of the Ribbon. 3. Place the mouse pointer over the Hide & Unhide option in the drop-down menu. This will open a submenu of options. 4. Click the Hide Columns option in the submenu of options (see Figure 1.26). This will hide Column C. Figure 1.26 Hide & Unhide Submenu BEGINNING EXCEL 2019 40 Keyboard Shortcuts Hiding Columns Hold down the CTRL key while pressing the number 0 on your keyboard. ◦ Same for Mac Users Figure 1.27 shows the workbook with Column C hidden in the Sheet1 worksheet. You can tell a column is hidden by the missing letter C. Figure 1.27 Hidden Column To unhide a column, follow these steps: 1. Select the range B1:D1. 2. Click the Format button in the Home tab of the Ribbon. 3. Place the mouse pointer over the Hide & Unhide option in the drop-down menu. 41 DIANE SHINGLEDECKER 4. Click the Unhide Columns option in the submenu of options. Column C will now be visi- ble on the worksheet. Keyboard Shortcuts Unhiding Columns Highlight cells on either side of the hidden column(s), then hold down the CTRL key and the SHIFT key while pressing the close parenthesis key ()) on your keyboard. Mac Users: Hold down Control and Shift keys and press the number 0 The following steps demonstrate how to hide rows, which is similar to hiding columns: 1. Click cell A3. 2. Click the Format button in the Home tab of the Ribbon. 3. Place the mouse pointer over the Hide & Unhide option in the drop-down menu. This will open a submenu of options. 4. Click the Hide Rows option in the submenu of options. This will hide Row 3. Keyboard Shortcuts Hiding Rows Hold down the CTRL key while pressing the number 9 key on your keyboard. ◦ Same for Mac Users To unhide a row, follow these steps: 1. Select the range A2:A4. 2. Click the Format button in the Home tab of the Ribbon. 3. Place the mouse pointer over the Hide & Unhide option in the drop-down menu. 4. Click the Unhide Rows option in the submenu of options. Row 3 will now be visible on the worksheet. Keyboard Shortcuts Unhiding Rows BEGINNING EXCEL 2019 42 Highlight cells above and below the hidden row(s), then hold down the CTRL key and the SHIFT key while pressing the open parenthesis key (() on your keyboard. Mac Users: Hold down Control and Shift keys and press the number 9 Integrity Check Hidden Rows and Columns In most careers, it is common for professionals to use Excel workbooks that have been designed by a coworker. Before you use a workbook developed by someone else, always check for hidden rows and columns. You can quickly see whether a row or column is hidden if a row number or column letter is missing. Skill Refresher Hiding Columns and Rows 1. Activate at least one cell in the row(s) or column(s) you are hiding. 2. Click the Home tab of the Ribbon. 3. Click the Format button in the Cells group. 4. Place the mouse pointer over the Hide & Unhide option. 5. Click either the Hide Rows or Hide Columns option. Skill Refresher Unhiding Columns and Rows 1. Highlight the cells above and below the hidden row(s) or to the left and right of the hidden column(s). 2. Click the Home tab of the Ribbon. 43 DIANE SHINGLEDECKER 3. Click the Format button in the Cells group. 4. Place the mouse pointer over the Hide & Unhide option. 5. Click either the Unhide Rows or Unhide Columns option. INSERTING COLUMNS AND ROWS Using Excel workbooks that have been created by others is a very efficient way to work because it eliminates the need to create data worksheets from scratch. However, you may find that to accom- plish your goals, you need to add additional columns or rows of data. In this case, you can insert blank columns or rows into a worksheet. The following steps demonstrate how to do this: 1. Click cell C1. 2. Click the down arrow on the Insert button in the Home tab of the Ribbon (see Figure 1.28). Figure 1.28 Insert Button (Down Arrow) 3. Click the Insert Sheet Columns option from the drop-down menu (see Figure 1.29). A blank column will be inserted to the left of Column C. The contents that were previously in Column C now appear in Column D. Note that columns are always inserted to the left BEGINNING EXCEL 2019 44 of the activated cell. Figure 1.29 Insert Drop-Down Menu Keyboard Shortcuts Inserting Columns ◦ Press the ALT key and then the letters H, I, and C one at a time. A column will be inserted to the left of the activated cell. ◦ Mac Users: First hold down the Control key and press the spacebar to select the column; then hold down the Shift and Controls keys and press the + symbol 4. Click cell A3. 5. Click the down arrow on the Insert button in the Home tab of the Ribbon (see Figure 1.28). 6. Click the Insert Sheet Rows option from the drop-down menu (see Figure 1.29). A blank row will be inserted above Row 3. The contents that were previously in Row 3 now appear in Row 4. Note that rows are always inserted above the activated cell. Keyboard Shortcuts Inserting Rows Press the ALT key and then the letters H, I, and R one at a time. A row will be inserted above the activated cell. Mac Users: First hold down the Shift key and press the spacebar to select the row; then 45 DIANE SHINGLEDECKER hold down the Shift and Controls keys and press the + symbol Skill Refresher Inserting Columns and Rows 1. Activate the cell to the right of the desired blank column or below the desired blank row. 2. Click the Home tab of the Ribbon. 3. Click the down arrow on the Insert button in the Cells group. 4. Click either the Insert Sheet Columns or Insert Sheet Rows option. MOVING DATA Once data are entered into a worksheet, you have the ability to move it to different locations. The following steps demonstrate how to move data to different locations on a worksheet: 1. Select the range D2:D15. 2. Bring the mouse pointer to the left edge of cell D2. You will see the white block plus sign change to cross arrows (see Figure 1.30). This indicates that you can left click and drag the data to a new location. Figure 1.30 Moving Data BEGINNING EXCEL 2019 46 Mac Users: when the mouse hovers over the left edge of cell D2, the pointer will turn into a small hand that looks like this: 3. Left Click and drag the mouse pointer to cell C2. 4. Release the left mouse button. The data now appears in Column C. 5. Click the Undo button in the Quick Access Toolbar. This moves the data back to Column D. Integrity Check Moving Data Before moving data on a worksheet, make sure you identify all the components that belong with the series you are moving. For example, if you are moving a column of data, make sure the column heading is included. Also, make sure all values are highlighted in the column before moving it. DELETING COLUMNS AND ROWS You may need to delete entire columns or rows of data from a worksheet. This need may arise if you need to remove either blank columns or rows from a worksheet or columns and rows that contain data. The methods for removing cell contents were covered earlier and can be used to delete unwanted data. However, if you do not want a blank row or column in your workbook, you can delete it using the following steps: 1. Click cell A3. 2. Click the down arrow on the Delete button in the Cells group in the Home tab of the Rib- bon. 3. Click the Delete Sheet Rows option from the drop-down menu (see Figure 1.31). This removes Row 3 and shifts all the data (below Row 2) in the worksheet up one row. Keyboard Shortcuts Deleting Rows 47 DIANE SHINGLEDECKER ◦ Press the ALT key and then the letters H, D, and R one at a time. The row with the activated cell will be deleted. ◦ Mac Users: First hold down the Shift key and press the spacebar to select the row; then hold down Control key and press the – symbol Figure 1.31 Delete Drop-Down Menu 4. Click cell C1. 5. Click the down arrow on the Delete button in the Cells group in the Home tab of the Rib- bon. 6. Click the Delete Sheet Columns option from the drop-down menu (see Figure 1.31). This removes Column C and shifts all the data in the worksheet (to the right of Column B) over one column to the left. 7. Save the changes to your workbook by clicking either the Save button on the Home rib- bon; or by selecting the Save option from the File menu. Keyboard Shortcuts Deleting Columns BEGINNING EXCEL 2019 48 Press the ALT key and then the letters H, D, and C one at a time. The column with the activated cell will be deleted. Mac Users: First hold down the Control key and press the spacebar to select the column; then hold down Control key and press the – symbol Skill Refresher Deleting Columns and Rows 1. Activate any cell in the row or column that is to be deleted. 2. Click the Home tab of the Ribbon. 3. Click the down arrow on the Delete button in the Cells group. 4. Click either the Delete Sheet Columns or the Delete Sheet Rows option. Key Takeaways Column headings should be used in a worksheet and should accurately describe the data con- tained in each column. Using symbols such as dollar signs when entering numbers into a worksheet can slow down the data entry process. Worksheets must be carefully proofread when data has been manually entered. The Undo command is a valuable tool for recovering data that was deleted from a worksheet. When using a worksheet that was developed by someone else, look carefully for hidden columns or rows. ATTRIBUTION Adapted by Barbara Lave from How to Use Microsoft Excel: The Careers in Practice Series, adapted by The Saylor Foundation without attribution as requested by the work’s original creator or licensee, and licensed under CC BY-NC-SA 3.0. 1.3 Formatting and Data Analysis Learning Objectives 1. Use formatting techniques as introduced in the Excel Spreadsheet Guidelines to enhance the appearance of a worksheet. 2. Understand how to align data in cell locations. 3. Examine how to enter multiple lines of text in a cell location. 4. Understand how to add borders to a worksheet. 5. Examine how to use the AutoSum feature to calculate totals. 6. Use the Cut, Copy, and Paste commands to manipulate the data on a worksheet. 7. Understand how to move, rename, insert, and delete worksheet tabs. This section addresses formatting commands that can be used to enhance the visual appearance of a worksheet. It also provides an introduction to mathematical calculations. The skills introduced in this section will give you powerful tools for analyzing the data that we have been working with in this workbook and will highlight how Excel is used to make key decisions in virtually any career. Additionally, Excel Spreadsheet Guidelines for format and appearance will be introduced as a for- mat for the course and spreadsheets submitted. FORMATTING DATA AND CELLS Enhancing the visual appearance of a worksheet is a critical step in creating a valuable tool for you or your coworkers when making key decisions. There are accepted professional formatting stan- dards when spreadsheets contain only currency data. For this course, we will use the following Excel Guidelines for Formatting. The first figure displays how to use Accounting number format when ALL figures are currency. Only the first row of data and the totals should be formatted with the Accounting format. The other data should be formatted with Comma style. There also needs to be a Top Border above the numbers in the total row. If any of the numbers have cents, you need to format all of the data with two decimal places. 49 BEGINNING EXCEL 2019 50 Figure 1.31a Often, your Excel spreadsheet will contain values that are both currency and non-currency in nature. When that is the case, you’ll want to use the guidelines in the following figure: 51 DIANE SHINGLEDECKER Figure 1.31b The following steps demonstrate several fundamental formatting skills that will be applied to the workbook that we are developing for this chapter. Several of these formatting skills are identical to ones that you may have already used in other Microsoft applications such as Microsoft® Word® or Microsoft® PowerPoint®. 1. Select the range A2:D2. Click the Bold button in the Font group of commands in the Home tab of the ribbon. 2. Click the Border button in the Font group of commands in the Home tab of the Ribbon (see Figure 1.32). Select the Bottom Border option from the list to achieve the goal of a border on the bottom of row 2 below the column head- ings. BEGINNING EXCEL 2019 52 Figure 1.32 Font Group of Commands Keyboard Shortcuts Bold Format ▪ Hold down the CTRL key while pressing the letter B on your keyboard. ▪ Mac Users: Hold the Control key and press the letter B or hold down the Command key and press the letter B 3. Select the range A15:D15. 4. Click the Bold button in the Font group of commands in the Home tab of the Ribbon. 5. Click the Border button in the Font group of commands in the Home tab of the Ribbon (see Figure 1.32). Select the Top Border option from the list to achieve the goal of a border on the top of row 15 where totals will eventually display. Keyboard Shortcuts Italics Format 53 DIANE SHINGLEDECKER ▪ Hold the CTRL key while pressing the letter I on your keyboard. ▪ Mac Users: Hold the Control key and press the letter I or hold down the Command key and press the letter I Keyboard Shortcuts Underline Format ▪ Hold the CTRL key while pressing the letter U on your keyboard. ▪ Mac Users: Hold the Control key and press the letter U or hold down the Command key and press the letter U Why? Format Column Headings and Totals Applying formatting enhancements to the column headings and column totals in a work- sheet is a very important technique, especially if you are sharing a workbook with other people. These formatting techniques allow users of the worksheet to clearly see the col- umn headings that define the data. In addition, the column totals usually contain the most important data on a worksheet with respect to making decisions, and formatting tech- niques allow users to quickly see this information. 1. Select the range B3:B14. 2. Click the Comma Style button in the Number group of commands in the Home tab of the Ribbon. This feature adds a comma as well as two decimal places. (see Figure 1.33). BEGINNING EXCEL 2019 54 Figure 1.33 Number Group of Commands 3. Since the figures in this range do not include cents, click the Decrease Decimal button in the Number group of commands in the Home tab of the Ribbon two times (see Figure 1.33). 4. The numbers will also be reduced to zero decimal places. 5. Select the range C3:C14. 6. Click the Accounting Number Format button in the Number group of commands in the Home tab of the Ribbon (see Figure 1.33). This will add the US currency symbol and two decimal places to the values. This format is common when working with pricing data. As discussed above in the Formatting Data and Cells section, you will want to use Account- ing format on all values in this range since the worksheet contains non-currency as well as currency data. 7. Select the range D3:D14. 8. Again, select the Accounting Number Format; this will add the US currency symbol to the values as well as two decimal places. 9. Click the Decrease Decimal button in the Number group of commands in the Home tab of the Ribbon. 10. This will add the US currency symbol to the values and reduce the decimal places to zero since there are no cents in these figures. 11. Select the range A1:D1. 12. Click the down arrow next to the Fill Color button in the Font group of commands in the Home tab of the Ribbon (see Figure 1.34). This will add background fill color the range for a worksheet title when entered. 55 DIANE SHINGLEDECKER Figure 1.34 Fill Color Palette 13. Click the Blue, Accent 1, Darker 25% color from the palette (see Figure 1.34). Notice that as you move the mouse pointer over the color palette, you will see a preview of how the color will appear in the highlighted cells. Experiment with this feature. 14. Click on A1 and enter the worksheet title: Merchandise City, USA and click on the check mark in the formula bar to enter this information. 15. Since the black font is difficult to read on the blue background, you’ll change the font color to be more visible. Click the down arrow next to the Font Color button in the Font group of commands in the Home tab of the Ribbon; select White as the font color for this range (see Figure 1.32). 16. Select the range A1:D1 and format for Italics by clicking on “I” in the Font group. 17. Click the drop-down arrow on the right side of the Font button in the Home tab of the Ribbon; select Arial as the font for this range and format for Bold click on “B”” in the Font group. (see Figure 1.32). 18. Notice that as you move the mouse pointer over the font style options, you can see the font change in the highlighted cells. 19. Expand the column width of Column D to 14 characters. BEGINNING EXCEL 2019 56 Why? Pound Signs (####) Appear in Columns When a column is too narrow for a long number, Excel will automatically convert the number to a series of pound signs (####). In the case of words or text data, Excel will only show the characters that fit in the col- umn. However, this is not the case with numeric data because it can give the appearance of a number that is much smaller than what is actually in the cell. To remove the pound signs, increase the width of the column. Figure 1.35 shows how the Sheet1 worksheet should appear after the formatting techniques are applied. 57 DIANE SHINGLEDECKER Figure 1.35 Formatting Techniques Applied DATA ALIGNMENT (WRAP TEXT, MERGE CELLS, AND CENTER) The skills presented in this segment show how data are aligned within cell locations. For example, text and numbers can be centered in a cell location, left justified, right justified, and so on. In some cases you may want to stack multiword text entries vertically in a cell instead of expanding the width of a column. This is referred to as wrapping text. These skills are demonstrated in the fol- lowing steps: 1. Select the range A2:D2. 2. Click the Center button in the Alignment group of commands in the Home tab of the Rib- bon (see Figure 1.36). This will center the column headings in each cell location. BEGINNING EXCEL 2019 58 Figure 1.36 Alignment Group in Home Tab 3. Click the Wrap Text button in the Alignment group (see Figure 1.36). The height of Row 2 automatically expands, and the words that were cut off because the columns were too narrow are now stacked vertically. Keyboard Shortcuts Wrap Text ◦ Press the ALT key and then the letters H and W one at a time. ◦ There is no equivalent shortcut for Excel for Mac Why? Wrap Text The benefit of using the Wrap Text command is that it significantly reduces the need to expand the column width to accommodate multiword column headings. The problem with increasing the col- 59 DIANE SHINGLEDECKER umn width is that you may reduce the amount of data that can fit on a piece of paper or one screen. This makes it cumbersome to analyze the data in the worksheet and could increase the time it takes to make a decision. 4. Select the range A1:D1. 5. Click the down arrow on the right side of the Merge & Center button in the Alignment group of commands in the Home tab of the Ribbon. 6. Click the Merge & Center option (see Figure 1.37). This will create one large cell location running across the top of the data set and center the text in that cell. Keyboard Shortcuts Merge Commands Merge & Center: Press the ALT key and then the letters H, M, and C one at a time. Merge Cells: Press the ALT key and then the letters H, M, and M one at a time. Unmerge Cells: Press the ALT key and then the letters H, M, and U one at a time. ◦ There are no equivalent shortcuts for Excel for Mac BEGINNING EXCEL 2019 60 Figure 1.37 Merge Cell Drop-Down Menu Why? Merge & Center One of the most common reasons the Merge & Center command is used is to center the title of a worksheet directly above the columns of data. Once the cells above the column headings are merged, a title can be cen- tered above the columns of data. It is very difficult to center the title over the columns of data if the cells are not merged. Figure 1.38 shows the Sheet1 worksheet with the data alignment commands applied. The reason for merging the cells in the range A1:D1 will become apparent in the next segment. 61 DIANE SHINGLEDECKER Figure 1.38 Data Alignment Features Added Skill Refresher Wrap Text 1. Activate the cell or range of cells that contain text data. 2. Click the Home tab of the Ribbon. 3. Click the Wrap Text button. Skill Refresher Merge Cells BEGINNING EXCEL 2019 62 1. Highlight a range of cells that will be merged. 2. Click the Home tab of the Ribbon. 3. Click the down arrow next to the Merge & Center button. 4. Select an option from the Merge & Center list. ENTERING MULTIPLE LINES OF TEXT In the Sheet1 worksheet, the cells in the range A1:D1 were merged for the purposes of adding a title to the worksheet. This worksheet will contain both a title and a subtitle. The following steps explain how you can enter text into a cell and determine where you want the second line of text to begin: 1. Click cell A1. Since the cells were merged, clicking cell A1 will automatically activate the range A1:D1. Position your mouse to the end of the title, directly after the “A” in the word “USA” and double-click to get a cursor (flashing I-beam). 2. Hold down the ALT key and press the ENTER key. This will start a new line of text in this cell location. 3. Type the text Retail Sales and press the ENTER key. 4. Select cell A1. Then click the Bold buttons in the Font group of commands in the Home tab of the Ribbon so that the titles are now in Bold and Italics. 5. Increase the height of Row 1 to 30 points. Once the row height is increased, all the text typed into the cell will be visible (see Figure 1.39). 63 DIANE SHINGLEDECKER Figure 1.39 Title & Subtitle Added to the Worksheet Skill Refresher Entering Multiple Lines of Text 1. Activate a cell location. 2. Type the first line of text. 3. Hold down the ALT key and press the ENTER key. 4. Type the second line of text and press the ENTER key. BEGINNING EXCEL 2019 64 BORDERS (ADDING LINES TO A WORKSHEET) In Excel, adding custom lines to a worksheet is known as adding borders. Borders are different from the grid lines that appear on a worksheet and that define the perimeter of the cell locations. The Borders command lets you add a variety of line styles to a worksheet that can make reading the worksheet much easier. The following steps illustrate methods for adding preset borders and custom borders to a worksheet: 1. Click the down arrow to the right of the Borders button in the Font group of commands in the Home page of the Ribbon to view border options. (see Figure 1.40). Figure 1.40 Borders Dropdown Menu 2. Select the range A1:D15. Left click the All Borders option from the Borders drop-down menu (see Figure 1.40). This will add vertical and horizontal lines to the range A1:D15. 3. Select the range A2:D2. 4. Click the down arrow to the right of the Borders button. 5. Left click the Thick Bottom Border option from the Borders drop-down menu. 6. Select the range A14:D14 and apply a Thick Bottom Border from the drop-down menu. The thick border will help maintain the Excel Formatting Guidelines. 65 DIANE SHINGLEDECKER 7. Select the range A1:D15. 8. Click the down arrow to the right of the Borders button. 9. Click More Borders… at the bottom of the List. 10. This will open the Format Cells dialog box (see Figure 1.41). You can access all format- ting commands in Excel through this dialog box. 11. In the Style section of the Borders tab, click the thickest line style (see Figure 1.41). 12. Click the Outline button in the Presets section (see Figure 1.41). 13. Click the OK button at the bottom of the dialog box (see Figure 1.41). Figure 1.41 Borders Tab of the Format Cells Dialog Box BEGINNING EXCEL 2019 66 Figure 1.42 Borders Added to the Worksheet Skill Refresher Preset Borders 1. Highlight a range of cells that require borders. 67 DIANE SHINGLEDECKER 2. Click the Home tab of the Ribbon. 3. Click the down arrow next to the Borders button. 4. Select an option from the preset borders list. Custom Borders 1. Highlight a range of cells that require borders. 2. Click the Home tab of the Ribbon. 3. Click the down arrow next to the Borders button. 4. Select the More Borders option at the bottom of the options list. 5. Select a line style and line color. 6. Select a placement option. 7. Click the OK button on the dialog box. AUTOSUM You will see at the bottom of Figure 1.42 that Row 15 is intended to show the totals for the data in this worksheet. Applying mathematical computations to a range of cells is accomplished through functions in Excel. Chapter 2 will review mathematical formulas and functions in detail. However, the following steps will demonstrate how you can quickly sum the values in a column of data using the AutoSum command: 1. Click cell B15 in the Sheet1 worksheet. 2. Click the Formulas tab of the Ribbon. 3. Click the down arrow below the AutoSum button in the Function Library group of com- mands (see Figure 1.43). Note that the AutoSum button can also be found in the Editing group of commands in the Home tab of the Ribbon. BEGINNING EXCEL 2019 68 Figure 1.43 AutoSum List 4. Click the Sum option from the AutoSum drop down menu. The first click will display a flashing marquee around the range. Click the check mark next to the Formula bar to complete the function. 5. Excel will provide a total for the values in the Unit Sales column. 6. Click cell D15. It would not make sense to total the averages in column C so C15 will be left blank. 7. Repeat steps 3 through 5 to sum the values in the Sales Dollars column (see Figure 1.44). 8. Click cell C15 to explore other AutoSum selections. Select the COUNT function from the list; Excel will return “12” for the number of months (rows). Excel will also display indica- tors of a green arrow in the corner of C15 and an exclamation point in yellow. These indicate that the function in this cell varies from the other functions in row 15. They can be ignored and do not print. 9. Click cell C15 again; this time selecting the MAX option from the list. Excel will display $19.99. This reflects the Maximum Average Price in column C. 10. Click cell C15 and delete the contents in this cell. 69 DIANE SHINGLEDECKER Figure 1.44 Totals Added to the Sheet1 Worksheet Skill Refresher AutoSum 1. Highlight a cell location below or to the right of a range of cells that contain numeric values. 2. Click the Formulas tab of the Ribbon. 3. Click the down arrow below the AutoSum button. 4. Select a mathematical function from the list. MOVING, RENAMING, INSERTING, AND DELETING WORKSHEETS The default names for the worksheet tabs at the bottom of workbook are Sheet1, Sheet2, and so on. However, you can change the worksheet tab names to identify the data you are using in a BEGINNING EXCEL 2019 70 workbook. Additionally, you can change the order in which the worksheet tabs appear in the work- book. The following steps explain how to rename and move the worksheets in a workbook: 1. Double click the Sheet1 worksheet tab at the bottom of the workbook (see Fig­ ure 1.45). Type the name Sales by Month. 2. Press the ENTER key on your keyboard. 3. Click the + to the right of the newly named worksheet. 4. Type the name Unit Sales Rank to prepare the worksheet for future use. 5. Press the ENTER key on your keyboard. Figure 1.45 Renaming a Worksheet Tab 1. Click the + to add another worksheet tab. 2. Click the Home tab of the Ribbon. 3. Click the down arrow on the Delete button in the Cells group of commands. 4. Click the Delete Sheet option from the drop-down list. This removes the unneeded worksheet. 5. Click the Delete button on the Delete warning box (if a warning box appears). 6. Complete the steps above to delete the newly named Unit Sales Rank work- sheet since it’s decided that worksheet is also unnecessary so that you are left with just one worksheet. 7. Excel incorporates Spell Check which is located on the Review Ribbon. Click- ing on the tool will allow Excel to check Spelling of alphabetic entries and allow for corrections. It’s a good habit to always use Spell Check your work before saving/printing. Figure 1.45a Spell Check Tool 8. Save the changes to your workbook by clicking either the Save button on the Home ribbon; or by selecting the Save option from the File menu. 71 DIANE SHINGLEDECKER Integrity Check Deleting Worksheets Be very cautious when deleting worksheets that contain data. Once a worksheet is deleted, you cannot use the Undo command to bring the sheet back. Deleting a worksheet is a permanent command. Keyboard Shortcuts Inserting New Worksheets Press the SHIFT key and then the F11 key on your keyboard. Same for Excel for Mac. Figure 1.46 shows the final appearance of the Merchandise City, USA workbook. BEGINNING EXCEL 2019 72 Figure 1.46 Final Appearance of the Merchandise City, USA Workbook Skill Refresher Renaming Worksheets 1. Double click the worksheet tab. 2. Type the new name. 3. Press the ENTER key. Moving Worksheets 1. Left click the worksheet tab. 2. Drag it to the desired position. 73 DIANE SHINGLEDECKER Deleting Worksheets 1. Open the worksheet to be deleted. 2. Click the Home tab of the Ribbon. 3. Click the down arrow on the Delete button. 4. Select the Delete Sheet option. 5. Click Delete on the warning box. Key Takeaways Formatting skills are critical for creating worksheets that are easy to read and have a profes- sional appearance. A series of pound signs (####) in a cell location indicates that the column is too narrow to dis- play the number entered. Using the Wrap Text command allows you to stack multiword column headings vertically in a cell location, reducing the need to expand column widths. Use the Merge & Center command to center the title of a worksheet directly over the columns that contain data. Adding borders or lines will make your worksheet easier to read and helps to separate the data in each column and row. You cannot use the Undo command to bring back a worksheet that has been deleted. ATTRIBUTION Adapted by Barbara Lave from How to Use Microsoft Excel: The Careers in Practice Series, adapted by The Saylor Foundation without attribution as requested by the work’s original creator or licensee, and licensed under CC BY-NC-SA 3.0. 1.4 Printing Learning Objectives Use the Page Layout tab to prepare a worksheet for printing. Add headers and footers to a printed worksheet. Examine how to print worksheets and workbooks. Once you have completed a workbook, it is good practice to select the appropriate settings for printing. These settings are in the Page Layout tab of the Ribbon and discussed in this section of the chapter. PAGE SETUP Before you can properly print the worksheets in a workbook, you must establish appropriate set- tings. The following steps explain several of the commands in the Page Layout tab of the Ribbon used to prepare a worksheet for printing: 1. Open the CH1 Merchandise City Sales Data workbook, if it is not already open. 2. Click the Page Layout tab of the Ribbon. 3. Click the Margins button in the Page Setup group of commands. This will open a drop- down list of options for setting the margins of your printed document. 4. Click the Wide option from the Margins drop-down list. (see Figure 1.47) 5. Click the Orientation button in the Page Setup and select Landscape. 6. Click on the arrow to the bottom right of the Page Setup category to launch the Page Setup options dialog box. Mac Users: there is no “arrow at the bottom right of the Page Setup category”. Simply click the Page Setup button on the Page Layout tab. 7. Click the Margins tab and locate “Center on Page”. Click the boxes to Horizontally and Vertically center the data on the worksheet. Click OK. 74 75 DIANE SHINGLEDECKER Why? Use Print Settings Because professionals often share Excel workbooks, it is a good practice to select the appropriate print set- tings in the Page Layout tab even if you do not intend to print the worksheets in a workbook. It can be extremely frustrating for recipients of a workbook who wish to print your worksheets to find that the neces- sary print settings have not been selected. This may reflect poorly on your attention to detail, especially if the recipient of the workbook is your boss. Figure 1.47 Page Layout Commands for Printing Table 1.2 Printing Resources: Purpose and Use for Page Setup Commands BEGINNING EXCEL 2019 76 Command Purpose Use 1. Click the Page Layout tab of the Ribbon. 2. Click the Margin button. Sets the top, bottom, right, and left margin space for the 3. Click one of the preset margin options or click Custom Margins printed document Margins. 1. Click the Page Layout tab of the Ribbon. 2. Click the Orientation button. Sets the orientation of the printed document to either Orientation portrait or landscape 3. Click one of the preset orientation options. 1. Click the Page Layout tab of the Ribbon. 2. Click the Size button. 3. Click one of the preset paper size options or click More Size Sets the paper size for the printed document Paper Sizes. 1. Highlight the range of cells on a worksheet that you wish to print. 2. Click the Page Layout tab of the Ribbon. 3. Click the Print Area button. Used for printing only a specific area or range of cells on a Print Area worksheet 4. Click the Set Print Area option from the drop-down list. 1. Activate a cell on the worksheet where the page break should be placed. Breaks are created above and to the left of the activated cell. 2. Click the Page Layout tab of the Ribbon. 3. Click the Breaks button. 4. Click the Insert Page Break option from the drop-down Breaks Allows you to manually set the page breaks on a worksheet list. 1. Click the Page Layout tab of the Ribbon. 2. Click the Background button. Background Adds a picture behind the cell locations in a worksheet 3. Select a picture stored on your computer or network. 1. Click the Page Layout tab of the Ribbon. 2. Click the Print Titles button. 3. Click in the Rows to Repeat at Top input box in the Page Setup dialog box. 4. Click any cell in the row that contains the column headings for your worksheet. Used when printing large data sets that are several pages long. This command will repeat the column headings at the 5. Click the OK button at the bottom of the Page Setup Print Titles top of each printed page. dialog box. HEADERS AND FOOTERS When printing worksheets from Excel, it is common to add headers and footers to the printed document. Information in the header or footer could include the date, page number, file name, company name, and so on. The following steps explain how to add headers and footers to the Merchandise City, USA Retail Sales worksheet. 1. Click the Insert Ribbon and click on Header & Footer at the right end of the ribbon 77 DIANE SHINGLEDECKER (located in the Text group). You will see the Design tab added to the Ribbon; this is used for creating the headers and footers for the printed worksheet. Also, this will convert the view of the worksheet from Normal to Page Layout (see Figure 1.48). This Page Layout view makes adding Headers & Footers easy and provides key features to incorporate. 2. Click on the Current Date icon to add the date to the left section of the worksheet Header. The &[Date] symbols which will toggle to a Date format when you click outside of this area. 1.48 Design Tab for Creating Headers and Footers Figure 1.48 Design Tab for Creating Headers and Footers 3. Type your name in the center section of the Header. 4. Place the mouse pointer over the left section of the Header and left click (see Figure 1.48). 5. Click the Go to Footer button in the Navigation group of commands in the Design tab of the Ribbon. 6. Place the mouse pointer over the far right section of the footer and left click. 7. Click the Page Number button (you may need to click on the Design tab again) in the Header & Footer Elements group of commands in the Design tab of the Ribbon. This view will display as &[Page] until printed or until you return to normal view. 8. Click any cell location outside the header or footer area. The Design tab for creating headers and footers will disappear. 9. Click the Normal view button in the lower right side of the Status Bar (see Figure 1.49). BEGINNING EXCEL 2019 78 Figure 1.49 Worksheet in Page Layout View PRINTING WORKSHEETS AND WORKBOOKS Once you have established the print settings for the worksheets in a workbook and have added headers and footers, you are ready to print your worksheets. The following steps explain how to print the worksheets in the Merchandise City, USA Sales workbook: 1. Click the File tab on the Ribbon. 2. Click the Print option on the left side of the Backstage view (see Figure 1.50). On the right side of the Backstage view, you will be able to see a preview of your printed work- sheet. 79 DIANE SHINGLEDECKER Figure 1.50 Backstage View Print option 3. Click the Print Active Sheets button in the Print section of the Backstage view (see Figure 1.50). 4. If your instructor has asked you to print your work, click the Print button. 5. Click the Home tab of the Ribbon. 6. Save and close the workbook. Key Takeaways The commands in the Page Layout tab of the Ribbon are used to prepare a worksheet for printing. You can add headers and footers to a worksheet to show key information such as page num- bers, the date, the file name, your name, and so on. The Print commands are in the File tab of the Ribbon. ATTRIBUTION Adapted by Barbara Lave from How to Use Microsoft Excel: The Careers in Practice Series, adapted by The Saylor Foundation without attribution as requested by the work’s original creator or licensee, and licensed under CC BY-NC-SA 3.0. 1.5 Chapter Practice To assess your understanding of the material covered in the chapter, complete the following assignment. BASIC MONTHLY BUDGET FOR MEDICAL OFFICE Download Data File: PR1 Data Creating and maintaining budgets are common practices in many careers. Budgets play a critical role in helping a business or household control expenditures. In this exercise you will create a budget for a hypothetical medical office while reviewing the skills covered in this chapter. 1. Open the file name PR1 Data, then Save As PR1 Medical Office Budget. 2. Activate all the cell locations in the Sheet1 worksheet by clicking the Select All button in the upper left corner of the worksheet. 3. In the Home tab of the Ribbon, set the font style to Arial and the font size to 12 points. Then click any cell to Deselect. 4. Increase the width of Column A so all the entries in the range A3:A8 are visible. Place the mouse pointer between the letter A and letter B of Column A and Column B. When the mouse pointer changes to a double arrow, left click and drag it to the right until the char- 80 81 DIANE SHINGLEDECKER acter width is approximately 18.00. 5. Enter Quarter 1 in cell B2. 6. Use AutoFill to complete the headings in the range C2:E2. Activate cell B2 and place the mouse pointer over the Fill Handle. 7. Select the range B2:E2 and click the Format button in the Home tab of the Ribbon. Click the Column Width option, type 11.57 in the Column Width dialog box, and then click the OK button in the Column Width dialog box. 8. Enter the words Medical Office Budget in cell A1. 9. Insert a blank column between Columns A and B by clicking on any cell in Column B. Then, click the drop-down arrow of the Insert button in the Home tab of the Ribbon. Click the Insert Sheet Columns option. 10. Enter the words Budget Cost in cell B2. 11. Adjust the width of Column B to approximately 12.0 characters. 12. Select the range A1:F1 and click the Merge & Center button in the Home tab of the Rib- bon to merge the cells in that range. 13. Make the following format adjustments to the range A1:F1: bold; italics; change the font size to 14 points; change the cell fill color to Aqua, Accent 5, Darker 50%; and change the font color to white. 14. Increase the height of Row 1 to approximately 24.75 points. 15. Make the following format adjustment to the range A2:F2: bold; and fill color to Tan, Background 2, Darker 10%. Center the column titles so that they are horizontally cen- tered in each cell. 16. Select B2 and choose the Wrap Text button in the Home tab of the Ribbon. Increase the height of Row 2 to approximately 30 points. 17. Copy cell C3 and paste the contents into the range D3:F3. 18. Copy the contents in the range C6:C8 by highlighting the range and clicking the Copy button in the Home tab of the Ribbon. Then, highlight the range D6:F8 and click the Paste button in the Home tab of the Ribbon. 19. Calculate the total budget for all four quarters for the salaries. Click cell B3 and click the down arrow on the AutoSum button in the Formulas tab of the Ribbon. Click the Sum option from the drop-down list. Then, highlight the range C3:F3 and press the ENTER key on your keyboard. 20. Copy the formula in cell B3 and paste them into the range B4:B8. 21. Format the range B3:F8 with Accounting format and zero decimal places. If any of the cells display pound symbols (######), simply widen the column to display the values again. 22. Select the range A1:F8 and click the down arrow next to the Borders button in the Home BEGINNING EXCEL 2019 82 tab of the Ribbon. Select the All Borders option from the drop-down list. 23. Double click the Sheet1 worksheet tab to change the name of Sheet1 to the word Bud­ get, and press the ENTER key. Delete any unnecessary worksheets. 24. Change the orientation of the Budget worksheet so it prints landscape instead of por- trait. 25. Use Fit to 1 page so the Budget worksheet prints on one piece of paper, if it does not already. 26. Add a header to the Budget worksheet that shows the date in the upper left corner and your name in the center. 27. Add a footer to the Budget worksheet that shows the page number in the lower right corner. 28. Check the spelling on the worksheet and make any necessary changes. Save PR1 Med­ ical Office Budget workbook. 29. Compare your work to the screenshot below and then submit the PR1 Medical Office Budget workbook as directed by your instructor. ATTRIBUTION Adapted by Barbara Lave from How to Use Microsoft Excel: The Careers in Practice Series, adapted by The Saylor Foundation with- out attribution as requested by the work’s orig- inal creator or licensee, and licensed under CC BY-NC-SA 3.0. PR 1 Medical Office Budget Solution Screenshot 1.6 Scored Assessment SALES AND INVENTORY ITEMS Download Data File: SC1 Data A key activity for marketing professionals is to analyze projected sales and inventory information. This is especially important for retail environments. This exercise utilizes the skills covered in this chapter to analyze sales and inventory data. 1. Open the file named SC1 Data and then Save As SC1 Sales and Inventory 2. In the Sheet1 worksheet, enter the word Totals in cell C14. 3. Format all the cells in Sheet1 to Century font style and a 12-point font size. 4. Set the column width for Columns A through G to 13.5. 5. Edit the entry in cell B2 to read “Item Number.” 6. Use AutoFill to fill the Item Numbers from B3 into the range B4:B13. The item numbers should in