Beginning Excel

Barbara Lave, Diane Shingledecker, Julie Romey, Noreen Brown, and Mary Schatz

Open Oregon Educational Resources

Beginning Excel by Noreen Brown, Barbara Lave, Julie Romey, Mary Schatz, Diane Shingledecker is licensed under a Creative Commons Attribution 4.0 International License, except where otherwise noted.

Contents

- Introduction

- Data Files

- Part I. Chapter 1 – Fundamental Skills

- 1. 1.1 Overview of Microsoft Excel

- 2. 1.2 Entering, Editing, and Managing Data

- 3. 1.3 Formatting and Data Analysis

- 4. 1.4 Printing

- 5. 1.5 Chapter Practice

- 6. 1.6 Scored Assessment

- Part II. Chapter 2 – Mathematical Computations

- 7. 2.1 Formulas

- 8. 2.2 Statistical Functions

- 9. 2.3 Functions for Personal Finance

- 10. 2.4 Preparing to Print

- 11. 2.5 Chapter Practice

- 12. 2.6 Chapter Scored

- Part III. Chapter 3 – Formulas, Functions, Logical and Lookup Functions

- 13. 3.1 More on Formulas and Functions

- 14. 3.2 Logical and Lookup Functions

- 15. 3.3 Conditional Formatting

- 16. 3.4 Preparing to Print

- 17. 3.5 Chapter Practice

- 18. 3.6 Chapter Scored

- Part IV. Chapter 4 – Presenting Data with Charts

- 19. 4.1 Choosing a Chart Type

- 20. 4.2 Formatting Charts

- 21. 4.3 Using Charts with Microsoft® Word® and Microsoft® PowerPoint®

- 22. 4.4 Preparing to Print

- 23. 4.5 Chapter Practice

- 24. 4.6 Scored Assessment

- Part V. Chapter 5 – Tables

- 25. 5.1 Table Basics

- 26. 5.2 Intermediate Table Skills

- 27. 5.3 Preparing to Print

- 28. 5.4 Chapter Practice

- 29. 5.5 Scored Assessment

- Part VI. Chapter 6 – Multiple Sheet Files

- 30. 6.1 Multiple Sheet Basics

- 31. 6.2 Formulas with 3-D References

- 32. 6.3 Templates

- 33. 6.4 Preparing to Print

- 34. 6.5 Chapter Practice

- 35. 6.6 Scored Assessment

- Appendix

1

This core Microsoft® Excel® text provides students with the skills needed to execute many personal 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, merchandise 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” scenarios 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.

2

Data Files

Data files for each chapter are linked above assignments where students will need to use them. They are also collected on this page.

- 1.5 Chapter Practice: PR1 Data

- 1.6 Scored Assessment: SC1 Data

- 2.1 Formulas: CH2 Data

- 2.4 Preparing to Print: CH2 PTP Data

- 2.5 Chapter Practice: PR2 Data

- 2.6 Chapter Scored: SC2 Data

- 3.1 More on Formulas and Functions: CH3 Data

- 3.4 Preparing to Print: CH3 PTP Data

- 3.5 Chapter Practice: PR3 Data

- 3.6 Chapter Scored: SC3 Data

- 4.1 Choosing a Chart Type: CH4 Data

- 4.3 Using Charts with Microsoft Word and Microsoft Powerpoint: CH4 Diversity (W0rd) and CH4 Diversity (PPT)

- 4.5 Chapter Practice: PR4 Data

- 4.6 Chapter Scored: SC4 Data

- 5.1 Table Basics: CH5 Data

- 5.4 Chapter Practice: PR5 Data

- 5.5 Chapter Scored: SC5 Data

- 6.1 Multiple Sheet Basics: CH6 Data

- 6.5 Chapter Practice: PR6 Data

I

Chapter 1 – Fundamental Skills

Microsoft® Excel® is a tool that can be used in virtually all careers and is valuable in both professional 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 efficiently 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 features 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 electronic spreadsheet to apply one or many mathematical computations. These computations ultimately 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 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 information shown in this worksheet is top-line sales data for a hypothetical merchandise retail company. The worksheet data can help this retailer determine the number of salespeople needed for each month, how much inventory is needed to satisfy sales, and what types of products should be purchased.

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 and present you with workbook options.

3. Click the first option; “Blank Workbook”.

The Excel Workbook

Once Excel is started, a blank workbook will open on your screen. A workbook is an Excel file that contains one or more worksheets (sometimes 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.

Figure 1.2 Blank Workbook

Your workbook should already be maximized (or shown at full size) once Excel is started, as shown in Figure 1.2. If necessary locate the Maximize button as shown in Figure 1.3.

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 nonnumeric 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 left click.

2. Check to make sure column letter D and row number 5 are highlighted, as shown in Figure 1.4.

Note:  Your highlighted column letter and row number may be different than figure shown.

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 Figure 1.5.

This is referred to as a cell range and is documented as follows: A1:D5. Any two cell locations separated 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

1. At the bottom of the screen, you’ll see worksheets. Depending on your version of Excel, you will see either three as displayed above or just one. If you only have one sheet, click the “Insert Worksheet” to add a worksheet. Depending on your version, you instead may have a + sign; a click on the + adds an additional worksheet as well. This is how you open or add a worksheet within a workbook. Add another worksheet so that you now have three sheets displaying here.

2. Click the Sheet1 worksheet tab at the bottom of the worksheet to return to the worksheet 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.

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 Ribbon. 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

Table 1.1 Command Overview for Each Tab of the Ribbon

Tab Name

Description of Commands

File

Also known as the Backstage view of the Excel workbook. Contains all commands for opening, closing, saving, and 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.

Home

Contains the most frequently used Excel commands. Formatting commands are found in this tab along with 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 Layout

Contains commands used to prepare a worksheet for printing. Also includes commands used to show and print the gridlines on a worksheet.

Formulas

Includes commands for adding mathematical functions to a worksheet. Also contains tools for auditing mathematical formulas.

Data

Used when working with external data sources such as Microsoft® Access®, text files, or the Internet. Also contains sorting commands and access to scenario tools.

Review

Includes Spelling and Track Changes features. Also contains protection features to password protect worksheets or workbooks.

View

Used to adjust the visual appearance of a workbook. Common commands include the Zoom and Page Layout view.

The Ribbon shown in Figure 1.6 is full, or maximized. The benefit of having 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 Ribbon 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.

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.

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 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.7. 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.7 Customizing the Quick Access Toolbar

In addition to the Ribbon and Quick Access Toolbar, you can also access commands by right clicking anywhere on the worksheet. Figure 1.8 shows an example of the commands available in the right-click menu.

Figure 1.8 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.9 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.

Figure 1.9 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.10 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.

Figure 1.10 Excel Options Window

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 “Entering, 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 2013

1. If you have not done so already, open a blank workbook in Excel.

2. When saving your workbook for the first time, click the File tab.

3. Click the Save As button in the upper left side of the Backstage view window. This will open the Save As dialog box, as shown in Figure 1.11.

4. Click in the File Name box at the bottom of the Save As dialog box and use the BACKSPACE key to remove the current default name of the workbook.

5. Type the file name:  CH1 GMW Sales Data.

6. Click the Desktop button on the left side of the Save As dialog box if you wish to save this file on your desktop. If you want to save this workbook in a different location, such as a USB drive, select your preferred location.

7. Click the Save button on the lower right side of the Save As dialog box.

8. As you continue to work on your workbook, you will want to Save frequently by click either the Save button on the Home ribbon; or by selecting the Save option from the File menu.

Figure 1.11 Save As Dialog Box in Excel 2013

Saving Workbooks in Excel 2016

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 window. 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 GMW Sales Data

5. Review the settings in the screen for correctness and click the Save button.

Figure 1.12 Save As Dialog in 2016

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.

Skill Refresher

Saving Workbooks (Save As)

1. Click the File tab on the Ribbon.

2. Click the Save As option.

3. Select a location on your PC.

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.

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 displays 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.13).

2. Select the Caps Lock option from the menu (see Figure 1.13).

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.

Figure 1.13 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.

Figure 1.14 Excel Help Window

Keyboard Shortcuts

Excel Help

- Press the F1 key on your keyboard.

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.

2

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 covered 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 worksheet. The following steps explain how the column headings in Row 2 are typed into the worksheet:

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.

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 headings reduce the chance of someone misinterpreting the data contained in a worksheet, which could lead to costly errors depending on your career.

1. Click cell location 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 3560.

4. Click cell location 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. Activate cell location 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 53370.

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 dollar 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

It is very important to proofread your worksheet carefully, especially when you have entered numbers. Transposing 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.

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 cursor. Move the cursor to the end of the abbreviation Tot and left click.

5. Type the letters al to complete the word Total.

6. Click the checkmark 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

7. Double click cell A15.

8. Add a space after the word Total and type the word Sales.

9. Press the ENTER key.

Keyboard Shortcuts

Editing Data in a Cell

- Activate the cell that is to be edited and press the F2 key on your keyboard.

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. Activate 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.

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 left mouse button when the tip box reads “December.”

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.

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 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 by placing the mouse pointer over the cell and clicking the left mouse button.

2. Press the DELETE key on your keyboard. This removes the contents of the cell.

3. Highlight the range C3:C14 by placing the mouse pointer over cell C3. 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 button. 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 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.

- 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.

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. 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.

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 character 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 double arrow pointer displays and then double-click to activate AutoFit. This features adjusts 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.

Steps 1 through 4 demonstrate how to adjust row height, which is similar to adjusting column width:

1. Click cell A15 by placing the mouse pointer over the cell and clicking the left mouse button.

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.

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.

Figure 1.25 shows the appearance of the worksheet after Column A and Row 15 are adjusted.

Figure 1.25 GMW 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.

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 in the Sheet1 worksheet by placing the mouse pointer over the cell location and clicking the left mouse button.

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

Keyboard Shortcuts

Hiding Columns

- Hold down the CTRL key while pressing the number 0 on your keyboard.

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. Highlight the range B1:D1 by activating cell B1 and clicking and dragging over to cell 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.

4. Click the Unhide Columns option in the submenu of options. Column C will now be visible 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.

The following steps demonstrate how to hide rows, which is similar to hiding columns:

1. Click cell A3 in the Sheet1 worksheet by placing the mouse pointer over the cell location and clicking the left mouse button.

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.

To unhide a row, follow these steps:

1. Highlight the range A2:A4 by activating cell A2 and clicking and dragging over to cell 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

- 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.

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.

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 accomplish 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 in the Sheet1 worksheet by placing the mouse pointer over the cell location and clicking the left mouse button.

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 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.

4. Click cell A3 in the Sheet1 worksheet by placing the mouse pointer over the cell location and clicking the left mouse button.

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.

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. Highlight the range D2:D15 by activating cell D2 and clicking and dragging down to cell 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

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 by placing the mouse pointer over the cell location and clicking the left mouse button.

2. Click the down arrow on the Delete button in the Cells group in the Home tab of the Ribbon.

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

- 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.

Figure 1.31 Delete Drop-Down Menu

4. Click cell C1 by placing the mouse pointer over the cell location and clicking the left mouse button.

5. Click the down arrow on the Delete button in the Cells group in the Home tab of the Ribbon.

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 ribbon; or by selecting the Save option from the File menu.

Keyboard Shortcuts

Deleting Columns

- 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.

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 contained 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.

3

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 format 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 standards 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.

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:

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. Highlight the range A2:D2 in the Sheet1 worksheet by placing the mouse pointer over cell A2 and left clicking and dragging to cell 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 headings.

Figure 1.32 Font Group of Commands

Keyboard Shortcuts

Bold Format

- Hold the CTRL key while pressing the letter B on your keyboard.

3. Highlight the range A15:D15 by placing the mouse pointer over cell A15 and left clicking and dragging to cell 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

- Hold the CTRL key while pressing the letter I on your keyboard.

Keyboard Shortcuts

Underline Format

- Hold the CTRL key while pressing the letter U on your keyboard.

Why?

Format Column Headings and Totals

Applying formatting enhancements to the column headings and column totals in a worksheet 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 column 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 techniques allow users to quickly see this information.

1. Highlight the range B3:B14 by placing the mouse pointer over cell B3 and left clicking and dragging down to cell 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).

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. Highlight the range C3:C14 by placing the mouse pointer over cell C3 and left clicking and dragging down to cell 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 Accounting format on all values in this range since the worksheet contains non-currency as well as currency data.

7. Highlight the range D3:D14 by placing the mouse pointer over cell D3 and left clicking and dragging down to cell 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. Highlight the range A1:D1 by placing the mouse pointer over cell A1 and left clicking and dragging over to cell 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 prepare the range for a worksheet title.

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: General Merchandise World 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. Highlight the range A1:D1 by placing the mouse pointer over cell A1 and and dragging across to cell D1.

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. (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.

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 column. 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.

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 following steps:

1. Highlight the range B2:D2 by placing the mouse pointer over cell B2 and left clicking and dragging over to cell D2.

2. Click the Center button in the Alignment group of commands in the Home tab of the Ribbon (see Figure 1.36). This will center the column headings in each cell location.

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.

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 column 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. Highlight the range A1:D1 by placing the mouse pointer over cell A1 and left clicking and dragging over to cell 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. Left click the Merge & Center option (see Figure 1.37). This will create one large cell location running across the top of the data set.

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.

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 centered 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.

Figure 1.38 Sheet1 with 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

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. Activate cell A1 in the Sheet1 worksheet by placing the mouse pointer over cell A1 and clicking the left mouse button. 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 “d” in the word “World” 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 (in millions) and press the ENTER key.

4. Select cell A1. Then click the Italics and Bold buttons in the Font group of commands in the Home tab of the Ribbon.

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).

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.

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 Drop-Down Menu

2. Highlight 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. Highlight the range A2:D2 by placing the mouse pointer over cell A2 and left clicking and dragging over to cell 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. Highlight 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.

7. Highlight 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 formatting commands in Excel through this dialog box.

11. In the Style section of the Borders tab, left click the thickest line style (see Figure 1.41).

12. Left 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

Figure 1.42 Borders Added to the Sheet1 Worksheet

Skill Refresher

Preset 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 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 “Mathematical Computations” 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. Activate 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 commands (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.

Figure 1.43 AutoSum Drop-Down 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. Activate 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).

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 workbook. Additionally, you can change the order in which the worksheet tabs appear in the workbook. The following steps explain how to rename and move the worksheets in a workbook:

1. With the left mouse button, double click the Sheet1 worksheet tab at the bottom of the workbook (see Figure 1.45). Type the name Sales by Month.

2. Press the ENTER key on your keyboard.

3. With the left mouse button, double click the Sheet2 worksheet tab at the bottom of the workbook.

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

6. Click the Sheet3 worksheet tab.

7. Click the Home tab of the Ribbon.

8. Click the down arrow on the Delete button in the Cells group of commands.

9. Click the Delete Sheet option from the drop-down list. This removes the unneeded worksheet.

10. Click the Delete button on the Delete warning box (if a warning box appears).

11. Complete the steps above to delete the newly named Unit Sales Rank worksheet since it’s decided that worksheet is also unnecessary so that you are left with just one worksheet.

12. 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.

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.

Figure 1.46 shows the final appearance of the General Merchandise World Workbook.

Figure 1.46 Final Appearance of the General Merchandise World 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.

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 professional appearance.

- A series of pound signs (####) in a cell location indicates that the column is too narrow to display 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.

4

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 settings. 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 GMW Sales 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.

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.

Why?

Use Print Settings

Because professionals often share Excel workbooks, it is a good practice to select the appropriate print settings 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 necessary 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

Command

Purpose

Use

Margins

Sets the top, bottom, right, and left margin space for the printed document

1. Click the Page Layout tab of the Ribbon.

2. Click the Margin button.

3. Click one of the preset margin options or click Custom Margins.

Orientation

Sets the orientation of the printed document to either portrait or landscape

1. Click the Page Layout tab of the Ribbon.

2. Click the Orientation button.

3. Click one of the preset orientation options.

Size

Sets the paper size for the printed document

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 Paper Sizes.

Print Area

Used for printing only a specific area or range of cells on a worksheet

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.

4. Click the Set Print Area option from the drop-down list.

Breaks

Allows you to manually set the page breaks on a worksheet

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 list.

Background

Adds a picture behind the cell locations in a worksheet

1. Click the Page Layout tab of the Ribbon.

2. Click the Background button.

3. Select a picture stored on your computer or network.

Print Titles

Used when printing large data sets that are several pages long. This command will repeat the column headings at the top of each printed page.

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.

5. Click the OK button at the bottom of the Page Setup 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 GMW Sales Data workbook:

1. Click the Insert Ribbon and click on Header & Footer at the right end of the ribbon (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.

Figure 1.48 Design Tab for Creating Headers and Footers

2. Type your name in the center section of the Header.

3. Place the mouse pointer over the left section of the Header and left click (see Figure 1.48).

4. Click the Current Date button in the Header & Footer Elements group of commands in the Design tab of the Ribbon. This view will display as &[Date] until printed or until you return to normal view.

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).

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 GMW 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 worksheet.

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 CH1 GMW Sales workbook.

7. Compare your work with the self-check answer key (found in the Course Files link) and then submit the CH1 GMW Sales workbook as directed by your instructor.

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 numbers, 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.

5

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 left 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 character 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. When the mouse pointer changes to a black plus sign, left click and drag it to cell E2.

7. Highlight 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 13.29 characters.

12. Highlight the range A1:F1 and click the Merge & Center button in the Home tab of the Ribbon 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 change the cell fill color to Tan, Background 2, Darker 10%. Center the column titles so that they are horizontally centered in each cell.

16. Set the alignment in cell B2 to Wrap Text. Select B2 and choose the Wrap Text button in the Home tab of the Ribbon.

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. Activate 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.

22. Highlight the range A1:F8 and click the down arrow next to the Borders button in the Home 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 Budget, and press the ENTER key.  Delete any unnecessary worksheets.

24. Change the orientation of the Budget worksheet so it prints landscape instead of portrait.

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. Save PR1 Medical Office Budget workbook.

29. Compare your work with the image here and then submit the PR1 Medical Office Budget workbook as directed by your instructor.

Figure 1.52 PR1 Medical Office Budget Final

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.

6

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 increase by one as they are filled through the range.

7. Copy the contents of cell A3 and paste them into the range A4:A8.

8. Delete Column F.

9. Format the range A1:F2 so the text is Bold.

10. Set the alignment in the range A2:F2 to Wrap Text.

11. Prepare A1:F1 for the title text by changing the fill color of the cells in the range A1:F1 to Red, Accent 2, Darker 25%.

12. Make the following font changes to the range A1:F1: set the font color to white, add italics, and set the font size to 14.

13. Merge and center the cells in the range A1:F1.

14. Enter the title for this worksheet in the range A1:F1. The title should appear on two lines. The first line should read Status Report. The second line should read Sales and Inventory by Item.

15. Increase the height of Row 1 so the entire title is visible.

16. Format the values in the range C3:C13 with dollar signs and two decimal places.

17. Format the values in the range E3:F13 with comma style, zero decimal places.

18. In cell E14, use AutoSum to calculate the sum of the values in the range E3:E13.

19. In cell F14, use AutoSum to calculate the sum of the values in the range F3:F13.

20. Apply All Borders to the range A1:F14.

21. Add a thick bottom border to row 2; add a thick bottom border to row 13.

22. Add a thick line border around the perimeter of the range A1:F14.

23. Insert a new blank worksheet in the workbook (this will be Sheet4).

24. Delete Sheet3.

25. Move Sheet4 ahead of Sheet2 so the order of the worksheets is Sheet1, Sheet4, and Sheet2.

26. Rename the Sheet1 worksheet tab to “Status Report.”

27. Change the orientation of the Status Report worksheet so it prints landscape instead of portrait.

28. Add a header to the Status Report worksheet that shows the date (needs to update) in the upper left corner and your name in the center.

29. Add a footer to the Status Report worksheet that shows the page number in the lower right corner with the word “Page” before the number.

30. Center the worksheet both horizontally and vertically on the sheet.

31. Save the SC1 Sales and Inventory workbook.

32. Submit the SC1 Sales and Inventory 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 without attribution as requested by the work’s original creator or licensee, and licensed under CC BY-NC-SA 3.0.

II

Chapter 2 – Mathematical Computations

Perhaps the most valuable feature of Excel is its ability to produce mathematical outputs using the data in a workbook. This chapter reviews several mathematical outputs that you can produce in Excel through the construction of formulas and functions. The chapter begins with the construction of formulas for basic and complex mathematical computations. The second section reviews statistical functions, such as SUM, AVERAGE, MIN, and MAX, which can be applied to a range of cells. The last section of the chapter addresses functions used to calculate mortgage and lease payments as well as the valuation of investments. This chapter also shows how you can use data from multiple worksheets to construct formulas and functions. These skills will be demonstrated in the context of a personal cash budget, which is a vital tool for managing your money for long-term financial security. The personal budget objective will also provide you with several opportunities to demonstrate Excel’s what-if scenario capabilities, which highlight how formulas and functions automatically produce new outputs when one or more inputs are changed.

Attribution

Adapted by Mary Schatz 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.

7

2.1 Formulas

Learning Objectives

- Learn how to create basic formulas.

- Understand relative referencing when copying and pasting formulas.

- Work with complex formulas by controlling the order of mathematical operations.

- Understand formula auditing tools.

This section reviews the fundamental skills for entering formulas into an Excel worksheet. The objective used for this chapter is the construction of a personal cash budget. Most financial advisors recommend that all households construct and maintain a personal budget to achieve and maintain strong financial health. Organizing and maintaining a personal budget is a skill you can practice at any point in your life. Whether you are managing your expenses during college or maintaining the finances of a family of four, a personal budget can be a vital tool when making financial decisions. Excel can make managing your money a fun and rewarding exercise.

Figure 2.1 shows the completed workbook that will be demonstrated in this chapter. Notice that this workbook contains four worksheets. The first worksheet, Budget Summary, contains formulas that utilize or reference the data in the other three worksheets. As a result, the Budget Summary worksheet serves as an overview of the data that was entered and calculated in the other three worksheets of the workbook.

Figure 2.1 Completed Personal Budget Workbook

Creating a Basic Formula

Download Data File: CH2 Data

Formulas are used to calculate a variety of mathematical outputs in Excel and can be used to create virtually any custom calculation required for your objective. Furthermore, when constructing a formula in Excel, you use cell locations that, when added to a formula, become cell references. This means that Excel uses, or references, the number entered into the cell location when calculating a mathematical output. As a result, when the numbers in the cell references are changed, Excel automatically produces a new output. This is what gives Excel the ability to create a variety of what-if scenarios, which will be explained later in the chapter.

To demonstrate the construction of a basic formula, we will begin working on the Budget Detail worksheet in the Personal Budget workbook, which is shown in Figure 2.2. To complete this worksheet, we will add several formulas and functions. Table 2.1 provides definitions for each of the spend categories listed in the range A3:A11. When you develop a personal budget, these categories are defined on the basis of how you spend your money. It is likely that every person could have different categories or define the same categories differently. Therefore, it is important to review the definitions in Table 2.1 to understand how we are defining these categories before proceeding.

Figure 2.2 Budget Detail Worksheet

Table 2.1 Spend Category Definitions

Category

Definition

Household Utilities

Money spent on electricity, heat, and water and on cable, phone, and Internet access

Food

Money spent on groceries, toiletries, and related items

Gasoline

Money spent on fuel for automobiles

Clothes

Money spent on clothes, shoes, and accessories

Insurance

Money spent on homeowner’s or automobile insurance

Taxes

Money spent on school and property taxes (this example of the personal budget assumes that we own property).

Entertainment

Money spent on entertainment, including dining out, movie and theater tickets, parties, and so on

Vacation

Money spent on vacations

Miscellaneous

Includes any other spending categories, such as textbooks, software, journals, school or work supplies, and so on

The first formula that we will add to the Budget Detail worksheet will calculate the Monthly Spend values. The formula will be constructed so that it takes the values in the Annual Spend column and divides them by 12. This will show how much money will be spent per month for each of the categories listed in Column A. The following explains how this formula is created:

1. Open the Data file named CH2 Data and use the File/Save As command to save it with the new name CH2 Personal Budget.

2. Click the Budget Detail worksheet tab to open the worksheet.

3. Click cell C3.

4. Type an equal sign =. When the first character entered into a cell location is an equal sign, it signals Excel to perform a calculation or produce a logical output.

5. Type D3. This adds D3 to the formula, which is now a cell reference. Excel will use whatever value is entered into cell D3 to produce an output.

6. Type the slash symbol /. This is the symbol for division in Excel. As shown in Table 2.2 the mathematical operators in Excel are slightly different from those found on a typical calculator.

7. Type the number 12. This divides the value in cell D3 by 12. In this formula, a number, or constant, is used instead of a cell reference because it will not change. In other words, there will always be 12 months in a year.

8. Press the ENTER key.

Table 2.2 Excel Mathematical Operators

Symbol

Operation

+

Addition

−

Subtraction

/

Division

*

Multiplication

^

Power/Exponent

Why?

Use Cell References

Cell references enable Excel to dynamically produce new outputs when one or more inputs in the referenced cells are changed. Cell references also allow you to trace how outputs are being calculated in a formula. As a result, you should never use a calculator to determine a mathematical output and type it into the cell location of a worksheet. Doing so eliminates Excel’s cell-referencing benefits as well as your ability to trace a formula to determine how outputs are being produced.

Figure 2.3 shows how the formula appears in cell C3 before you press the ENTER key. Figure 2.4 shows the output of the formula after you press the ENTER key. The Monthly Spend for Household Utilities is $250 because the formula is taking the Annual Spend in cell D3 and dividing it by 12. If the value in cell D3 is changed, the formula automatically produces a new output. We are calculating the spend per month for each category because people often get paid and are billed for these items on a monthly basis. This formula allows you to compare your monthly income to your monthly bills to determine whether you have enough income to pay these expenses.

Figure 2.3 Adding a Formula to a Worksheet

Figure 2.4 Formula Output for Monthly Spend

Why?

Use Universal Constants

If you are using constants, or numerical values, in an Excel formula, they should be universal constants that do not change, such as the number of days in a week, weeks in a year, and so on. Do not type the values that exist in cell locations into an Excel formula. This will eliminate Excel’s cell-referencing benefits, which means if the value in the cell location you are using in a formula is changed, Excel will not be able to produce a new output.

Relative References (Copying and Pasting Formulas)

Once a formula is typed into a worksheet, it can be copied and pasted to other cell locations. For example, Figure 2.4 shows the output of the formula that was entered into cell C3. However, this calculation needs to be performed for the rest of the cell locations in Column C. Since we used the D3 cell reference in the formula, Excel automatically adjusts that cell reference when the formula is copied and pasted into the rest of the cell locations in the column. This is called relative referencing and is demonstrated as follows:

1. Click cell C3.

2. Place the mouse pointer over the Auto Fill Handle.

3. When the mouse pointer turns from a white block plus sign to a black plus sign, click and drag down to cell C11. This pastes the formula into the range C4:C11.

4. Double click cell C6. Notice that the cell reference in the formula is automatically changed to D6.

5. Press the ENTER key.

Figure 2.5 shows the outputs added to the rest of the cell locations in the Monthly Spend column. For each row, the formula takes the value in the Annual Spend column and divides it by 12. You will also see that cell D6 has been double clicked to show the formula. Notice that Excel automatically changed the original cell reference of D3 to D6. This is the result of relative referencing, which means Excel automatically adjusts a cell reference relative to its original location when it is pasted into new cell locations. In this example, the formula was pasted into eight cell locations below the original cell location. As a result, Excel increased the row number of the original cell reference by a value of one for each row it was pasted into.

Figure 2.5 Relative Reference Example

Why?

Use Relative Referencing

Relative referencing is a convenient feature in Excel. When you use cell references in a formula, Excel automatically adjusts the cell references when the formula is pasted into new cell locations. If this feature were not available, you would have to manually retype the formula when you want the same calculation applied to other cell locations in a column or row.

Creating Complex Formulas (Controlling the Order of Operations)

The next formula to be added to the Personal Budget workbook is the percent change over last year. This formula determines the difference between the values in the LY (Last Year) Spend column and shows the difference in terms of a percentage. This requires that the order of mathematical operations be controlled to get an accurate result. Table 2.3 shows the standard order of operations for a typical formula. To change the order of operations shown in the table, we use parentheses to process certain mathematical calculations first. This formula is added to the worksheet as follows:

1. Click cell F3 in the Budget Detail worksheet.

2. Type an equal sign =.

3. Type an open parenthesis (.

4. Click cell D3. This will add a cell reference to cell D3 to the formula. When building formulas, you can click cell locations instead of typing them.

5. Type a minus sign −.

6. Click cell E3 to add this cell reference to the formula.

7. Type a closing parenthesis ).

8. Type the slash / symbol for division.

9. Click cell E3. This completes the formula that will calculate the percent change of last year’s actual spent dollars vs. this year’s budgeted spend dollars (see Figure 2.6).

10. Press the ENTER key.

11. Click cell F3 to activate it.

12. Place the mouse pointer over the Auto Fill Handle.

13. When the mouse pointer turns from a white block plus sign to a black plus sign, click and drag down to cell F11. This pastes the formula into the range F4:F11.

Table 2.3 Standard Order of Mathematical Operations

Symbol

Order

( )

Override Standard Order: Any mathematical computations placed in parentheses are performed first and override the standard order of operations. If there are layers of parentheses used in a formula, Excel computes the innermost parentheses first and the outermost parentheses last.

^

First: Excel executes any exponential computations first.

* or /

Second: Excel performs any multiplication or division computations second. When there are multiple instances of these computations in a formula, they are executed in order from left to right.

+ or −

Third: Excel performs any addition or subtraction computations third. When there are multiple instances of these computations in a formula, they are executed in order from left to right.

Figure 2.6 shows the formula that was added to the Budget Detail worksheet to calculate the percent change in spending. The parentheses were added to this formula to control the order of operations. Any mathematical computations placed in parentheses are executed first before the standard order of mathematical operations (see Table 2.3). In this case, if parentheses were not used, Excel would produce an erroneous result for this worksheet.

Figure 2.6 Adding the Percent Change Formula

Figure 2.7 shows the result of the percent change formula if the parentheses are removed. The formula produces a result of a 299900% increase. Since there is no change between the LY spend and the budget Annual Spend, the result should be 0%. However, without the parentheses, Excel is following the standard order of operations. This means the value in cell E3 will be divided by E3 first (3,000/3,000), which is 1. Then, the value of 1 will be subtracted from the value in cell D3 (3,000−1), which is 2,999. Since cell F3 is formatted as a percentage, Excel expresses the output as an increase of 299900%.

Figure 2.7 Removing the Parentheses from the Percent Change Formula

Integrity Check

Does the Output of Your Formula Make Sense?

It is important to note that the accuracy of the output produced by a formula depends on how it is constructed. Therefore, always check the result of your formula to see whether it makes sense with data in your worksheet. As shown in Figure 2.7, a poorly constructed formula can give you an inaccurate result. In other words, you can see that there is no change between the Annual Spend and LY Spend for Household Utilities. Therefore, the result of the formula should be 0%. However, since the parentheses were removed in this case, the formula is clearly producing an erroneous result.

Skill Refresher

Formulas

1. Type an equal sign =.

2. Click or type a cell location. If using constants, type a number.

3. Type a mathematical operator.

4. Click or type a cell location. If using constants, type a number.

5. Use parentheses where necessary to control the order of operations.

6. Press the ENTER key.

Auditing Formulas

Excel provides a few tools that you can use to review the formulas entered into a worksheet. For example, instead of showing the outputs for the formulas used in a worksheet, you can have Excel show the formula as it was entered in the cell locations. This is demonstrated as follows:

1. With the Budget Detail worksheet open, click the Formulas tab of the Ribbon.

2. Click the Show Formulas button in the Formula Auditing group of commands. This displays the formulas in the worksheet instead of showing the mathematical outputs.

3. Click the Show Formulas button again. The worksheet returns to showing the output of the formulas.

Figure 2.8 shows the Budget Detail worksheet after activating the Show Formulas command in the Formulas tab of the Ribbon. As shown in the figure, this command allows you to view and check all the formulas in a worksheet without having to click each cell individually. After activating this command, the column widths in your worksheet increase significantly. The column widths were adjusted for the worksheet shown in Figure 2.8 so all columns can be seen. The column widths return to their previous width when the Show Formulas command is deactivated.

Figure 2.8 Show Formulas Command

Skill Refresher

Show Formulas

1. Click the Formulas tab on the Ribbon.

2. Click the Show Formulas button in the Formula Auditing group of commands.

3. Click the Show Formulas button again to show formula outputs.

Keyboard Shortcuts

Show Formulas

- Hold down the CTRL key while pressing the accent symbol `.

Two other tools in the Formula Auditing group of commands are the Trace Precedents and Trace Dependents commands. These commands are used to trace the cell references used in a formula. A precedent cell is a cell whose value is used in other cells. The Trace Precedents command shows an arrow to indicate the cells or ranges (precedents) which affect the active cell’s value. A dependent cell is a cell whose value depends on the values of other cells in the workbook. The Trace Dependents command shows where any given cell is referenced in a formula. The following is a demonstration of these commands:

1. Click cell D3 in the Budget Detail worksheet.

2. Click the Trace Dependents button in the Formula Auditing group of commands in the Formulas tab of the Ribbon. A double blue arrow appears, pointing to cell locations C3 and F3 (see Figure 2.9). This indicates that cell D3 is referenced in formulas that are entered in cells C3 and F3.

3. Click the Remove Arrows command in the Formula Auditing group of commands in the Formulas tab of the Ribbon. This removes the Trace Dependents arrow.

4. Click cell F3 in the Budget Detail worksheet.

5. Click the Trace Precedents button in the Formula Auditing group of commands in the Formulas tab of the Ribbon. A blue arrow running through cells D3 and E3 and pointing to cell F3 appears (see Figure 2.10). This indicates that cells D3 and E3 are references in a formula entered in cell F3.

6. Click the Remove Arrows command in the Formula Auditing group of commands in the Formulas tab of the Ribbon. This removes the Trace Precedents arrow.

7. Save the Ch2 Personal Budget file.

Figure 2.9 shows the Trace Dependents arrow on the Budget Detail worksheet. The blue dot represents the activated cell. The arrows indicate where the cell is referenced in formulas.

Figure 2.9 Trace Dependents Example

Figure 2.10 shows the Trace Precedents arrow on the Budget Detail worksheet. The blue dots on this arrow indicate the cells that are referenced in the formula contained in the activated cell. The arrow is pointing to the activated cell location that contains the formula.

Figure 2.10 Trace Precedents Example

Skill Refresher

Trace Dependents

1. Click a cell location that contains a number or formula.

2. Click the Formulas tab on the Ribbon.

3. Click the Trace Dependents button in the Formula Auditing group of commands.

4. Use the arrow(s) to determine where the cell is referenced in formulas and functions.

5. Click the Remove Arrows button to remove the arrows from the worksheet.

Skill Refresher

Trace Precedents

1. Click a cell location that contains a formula or function.

2. Click the Formulas tab on the Ribbon.

3. Click the Trace Precedents button in the Formula Auditing group of commands.

4. Use the dot(s) along the line to determine what cells are referenced in the formula or function.

5. Click the Remove Arrows button to remove the line with the dots.

Key Takeaways

- Mathematical computations are conducted through formulas and functions.

- An equal sign = precedes all formulas and functions.

- Formulas and functions must be created with cell references to conduct what-if scenarios where mathematical outputs are recalculated when one or more inputs are changed.

- Mathematical operators on a typical calculator are different from those used in Excel. Table 2.2 “Excel Mathematical Operators” lists Excel mathematical operators.

- When using numerical values in formulas and functions, only use universal constants that do not change, such as days in a week, months in a year, and so on.

- Relative referencing automatically adjusts the cell references in formulas and functions when they are pasted into new locations on a worksheet. This eliminates the need to retype formulas and functions when they are needed in multiple rows or columns on a worksheet.

- Parentheses must be used to control the order of operations when necessary for complex formulas.

- Formula auditing tools such as Trace Dependents, Trace Precedents, and Show Formulas should be used to check the integrity of formulas that have been entered into a worksheet.

Attribution

Adapted by Mary Schatz 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.

8

2.2 Statistical Functions

Learning Objectives

1. Use the SUM function to calculate totals.

2. Use absolute references to calculate percent of totals.

3. Use the COUNT function to count cell locations with numerical values.

4. Use the AVERAGE function to calculate the arithmetic mean.

5. Use the MAX and MIN functions to find the highest and lowest values in a range of cells.

6. Learn how to copy and paste formulas without formats applied to a cell location.

7. Learn how to set a multiple level sort sequence for data sets that have duplicate values or outputs.

In addition to formulas, another way to conduct mathematical computations in Excel is through functions. Statistical functions apply a mathematical process to a group of cells in a worksheet. For example, the SUM function is used to add the values contained in a range of cells. A list of commonly used statistical functions is shown in Table 2.4. Functions are more efficient than formulas when you are applying a mathematical process to a group of cells. If you use a formula to add the values in a range of cells, you would have to add each cell location to the formula one at a time. This can be very time-consuming if you have to add the values in a few hundred cell locations. However, when you use a function, you can highlight all the cells that contain values you wish to sum in just one step. This section demonstrates a variety of statistical functions that we will add to the Personal Budget workbook. In addition to demonstrating functions, this section also reviews percent of total calculations and the use of absolute references.

Table 2.4 Commonly Used Statistical Functions

Function

Output

ABS

The absolute value of a number

AVERAGE

The average or arithmetic mean for a group of numbers

COUNT

The number of cell locations in a range that contain a numeric character

COUNTA

The number of cell locations in a range that contain a text or numeric character

MAX

The highest numeric value in a group of numbers

MEDIAN

The middle number in a group of numbers (half the numbers in the group are higher than the median and half the numbers in the group are lower than the median)

MIN

The lowest numeric value in a group of numbers

MODE

The number that appears most frequently in a group of numbers

PRODUCT

The result of multiplying all the values in a range of cell locations

SQRT

The positive square root of a number

STDEV.S

The standard deviation for a group of numbers based on a sample

SUM

The total of all numeric values in a group

The SUM Function

The SUM function is used when you need to calculate totals for a range of cells or a group of selected cells on a worksheet. With regard to the Budget Detail worksheet, we will use the SUM function to calculate the totals in row 12. It is important to note that there are several methods for adding a function to a worksheet, which will be demonstrated throughout the remainder of this chapter. The following illustrates how a function can be added to a worksheet by typing it into a cell location:

1. Click the Budget Detail worksheet tab to open the worksheet.

2. Click cell C12.

3. Type an equal sign =.

4. Type the function name SUM.

5. Type an open parenthesis (.

6. Click cell C3 and drag down to cell C11. This places the range C3:C11 into the function.

7. Type a closing parenthesis ).

8. Press the ENTER key. The function calculates the total for the Monthly Spend column, which is $1,496.

Figure 2.11 shows the appearance of the SUM function added to the Budget Detail worksheet before pressing the ENTER key.

Figure 2.11 Adding the SUM Function to the Budget Detail Worksheet

As shown in Figure 2.11, the SUM function was added to cell C12. However, this function is also needed to calculate the totals in the Annual Spend and LY Spend columns. The function can be copied and pasted into these cell locations because of relative referencing. Relative referencing serves the same purpose for functions as it does for formulas. The following demonstrates how the total row is completed:

1. Click cell C12 in the Budget Detail worksheet.

2. Click the Copy button in the Home tab of the Ribbon.

3. Highlight cells D12 and E12.

4. Click the Paste button in the Home tab of the Ribbon. This pastes the SUM function into cells D12 and E12 and calculates the totals for these columns.

5. Click cell F11.

6. Click the Copy button in the Home tab of the Ribbon.

7. Click cell F12, then click the Paste button in the Home tab of the Ribbon. Since we now have totals in row 12, we can paste the percent change formula into this row.

Figure 2.12 shows the output of the SUM function that was added to cells C12, D12, and E12. In addition, the percent change formula was copied and pasted into cell F12. Notice that this version of the budget is planning a 1.7% decrease in spending compared to last year.

Figure 2.12 Results of the SUM Function in the Budget Detail Worksheet

Integrity Check

Cell Ranges in Statistical Functions

When you intend to use a statistical function on a range of cells in a worksheet, make sure there are two cell locations separated by a colon and not a comma. If you enter two cell locations separated by a comma, the function will produce an output but it will be applied to only two cell locations instead of a range of cells. For example, the SUM function shown in Figure 2.13 will add only the values in cells C3 and C11, not the range C3:C11.

Figure 2.13 SUM Function Adding Two Cell Locations

Absolute References (Calculating Percent of Totals)

Data file: Continue with CH2 Personal Budget.

Since totals were added to row 12 of the Budget Detail worksheet, a percent of total calculation can be added to Column B beginning in cell B3. The percent of total calculation shows the percentage for each value in the Annual Spend column with respect to the total in cell D12. However, after the formula is created, it will be necessary to turn off Excel’s relative referencing feature before copying and pasting the formula to the rest of the cell locations in the column. Turning off Excel’s relative referencing feature is accomplished through an absolute reference. The following steps explain how this is done:

1. Click cell B3 in the Budget Detail worksheet.

2. Type an equal sign =.

3. Click cell D3.

4. Type a forward slash /.

5. Click cell D12.

6. Press the ENTER key. You will see that Household Utilities represents 16.7% of the Annual Spend budget (see Figure 2.14).

Figure 2.14 Adding a Formula to Calculate the Percent of Total

Figure 2.14 shows the completed formula that is calculating the percentage that Household Utilities Annual Spend represents to the total Annual Spend for the budget (see cell B3). Normally, we would copy this formula and paste it into the range B4:B11. However, because of relative referencing, both cell references will increase by one row as the formula is pasted into the cells below B3. This is fine for the first cell reference in the formula (D3) but not for the second cell reference (D12). Figure 2.15 illustrates what happens if we paste the formula into the range B4:B12 in its current state. Notice that Excel produces the #DIV/0 error code. This means that Excel is trying to divide a number by zero, which is impossible. Looking at the formula in cell B4, you see that the first cell reference was changed from D3 to D4. This is fine because we now want to divide the Annual Spend for Insurance by the total Annual Spend in cell D12. However, Excel has also changed the D12 cell reference to D13. Because cell location D13 is blank, the formula produces the #DIV/0 error code.

Figure 2.15 #DIV/0 Error from Relative Referencing

To eliminate the divide-by-zero error shown in Figure 2.15 we must add an absolute reference to cell D12 in the formula. An absolute reference prevents relative referencing from changing a cell reference in a formula. This is also referred to as locking a cell. The following explains how this is accomplished:

1. Double click cell B3.

2. Place the mouse pointer in front of D12 and click. The blinking cursor should be in front of the D in the cell reference D12.

3. Press the F4 key. You will see a dollar sign ($) added in front of the column letter D and the row number 12. You can also type the dollar signs in front of the column letter and row number.

4. Press the ENTER key.

5. Click cell B3.

6. Click the Copy button in the Home tab of the Ribbon.

7. Highlight the range B4:B11.

8. Click the Paste button in the Home tab of the Ribbon.

Figure 2.16 shows the percent of total formula with an absolute reference added to D12. Notice that in cell B4, the cell reference remains D12 instead of changing to D13 as shown in Figure 2.15. Also, you will see that the percentages are being calculated in the rest of the cells in the column, and the divide-by-zero error is now eliminated.

Figure 2.16 Adding an Absolute Reference to a Cell Reference in a Formula

Skill Refresher

Absolute References

1. Click in front of the column letter of a cell reference in a formula or function that you do not want altered when the formula or function is pasted into a new cell location.

2. Press the F4 key or type a dollar sign $ in front of the column letter and row number of the cell reference.

The COUNT Function

Data file: Continue with CH2 Personal Budget.

The next function that we will add to the Budget Detail worksheet is the COUNT function. The COUNT function is used to determine how many cells in a range contain a numeric entry. The COUNT function will not work for counting text or other non-numeric entries. For the Budget Detail worksheet, we will use the COUNT function to count the number of items that are planned in the Annual Spend column (Column D). The following explains how the COUNT function is added to the worksheet by using the function list:

1. Click cell D13 in the Budget Detail worksheet.

2. Type an equal sign =.

3. Type the letter C.

4. Click the down arrow on the scroll bar of the function list (see Figure 2.17) and find the word COUNT.

5. Double click the word COUNT from the function list.

6. Highlight the range D3:D11.

7. You can type a closing parenthesis ) and then press the ENTER key, or simply press the ENTER key and Excel will close the function for you. The function produces an output of 9 since there are 9 items planned on the worksheet.

Figure 2.17 shows the function list box that appears after completing steps 2 and 3 for the COUNT function. The function list provides an alternative method for adding a function to a worksheet.

Figure 2.17 Using the Function List to Add the COUNT Function

Figure 2.18 shows the output of the COUNT function after pressing the ENTER key. The function counts the number of cells in the range D3:D11 that contain a numeric value. The result of 9 indicates that there are 9 categories planned for this budget.

Figure 2.18 Completed COUNT Function in the Budget Detail Worksheet

The AVERAGE Function

The next function we will add to the Budget Detail worksheet is the AVERAGE function. This function is used to calculate the arithmetic mean for a group of numbers. For the Budget Detail worksheet, we will use the function to calculate the average of the values in the Annual Spend column. We will add this to the worksheet by using the Function Library. The following steps explain how this is accomplished:

1. Click cell D14 in the Budget Detail worksheet.

2. Click the Formulas tab on the Ribbon.

3. Click the More Functions button in the Function Library group of commands.

4. Place the mouse pointer over the Statistical option from the drop-down list of options.

5. Click the AVERAGE function name from the list of functions that appear in the menu (see Figure 2.19). This opens the Function Arguments dialog box.

6. Click the Collapse Dialog button in the Function Arguments dialog box (see Figure 2.20).

7. Highlight the range D3:D11.

8. Click the Expand Dialog button in the Function Arguments dialog box (see Figure 2.21). You can also press the ENTER key to get the same result.

9. Click the OK button on the Function Arguments dialog box. This adds the AVERAGE function to the worksheet.

Figure 2.19 illustrates how a function is selected from the Function Library in the Formulas tab of the Ribbon.

Figure 2.19 Selecting the AVERAGE Function from the Function Library

Figure 2.20 shows the Function Arguments dialog box. This appears after a function is selected from the Function Library. The Collapse Dialog button is used to hide the dialog box so a range of cells can be highlighted on the worksheet and then added to the function.

Figure 2.20 Function Arguments Dialog Box

Figure 2.21 shows how a range of cells can be selected from the Function Arguments dialog box once it has been collapsed.

Figure 2.21 Selecting a Range from the Function Arguments Dialog Box

Figure 2.22 shows the Function Arguments dialog box after the cell range is defined for the AVERAGE function. The dialog box shows the result of the function before it is added to the cell location. This allows you to assess the function output to determine whether it makes sense before adding it to the worksheet.

Figure 2.22 Function Arguments Dialog Box after a Cell Range Is Defined for a Function

Figure 2.23 shows the completed AVERAGE function in the Budget Detail worksheet. The output of the function shows that on average we expect to spend $1,994 for each of the categories listed in Column A of the budget. This average spend calculation per category can be used as an indicator to determine which categories are costing more or less than the average budgeted spend dollars.

Figure 2.23 Completed AVERAGE Function

The MAX and MIN Functions

Data file: Continue with CH2 Personal Budget.

The final two statistical functions that we will add to the Budget Detail worksheet are the MAX and MIN functions. These functions identify the highest and lowest values in a range of cells. The following steps explain how to add these functions to the Budget Detail worksheet:

1. Click cell D15 in the Budget Detail worksheet.

2. Type an equal sign =.

3. Type the word MIN.

4. Type an open parenthesis (.

5. Highlight the range D3:D11.

6. Type a closing parenthesis ) and press the ENTER key, or simply press the ENTER key and Excel will close the function for you. The MIN function produces an output of $1,200, which is the lowest value in the Annual Spend column (see Figure 2.24).

7. Click cell D16.

8. Type an equal sign =.

9. Type the word MAX.

10. Type an open parenthesis (.

11. Highlight the range D3:D11.

12. Type a closing parenthesis ) and press the ENTER key, or simply press the ENTER key and Excel will close the function for you. The MAX function produces an output of $3,500. This is the highest value in the Annual Spend column (see Figure 2.25).

Figure 2.24 MIN Function Added to the Budget Detail Worksheet

Figure 2.25 MAX Function Added to the Budget Detail Worksheet

Skill Refresher

Statistical Functions

1. Type an equal sign =.

2. Type the function name followed by an open parenthesis ( or double click the function name from the function list.

3. Highlight a range on a worksheet or click individual cell locations followed by commas.

4. Type a closing parenthesis ) and press the ENTER key or press the ENTER key to close the function.

Copy and Paste Formulas (Pasting without Formats)

Data file: Continue with CH2 Personal Budget.

As shown in Figure 2.25, the COUNT, AVERAGE, MIN, and MAX functions are summarizing the data in the Annual Spend column. You will also notice that there is space to copy and paste these functions under the LY Spend column. This allows us to compare what we spent last year and what we are planning to spend this year. Normally, we would simply copy and paste these functions into the range E13:E16. However, you may have noticed the double-line style border that was used around the perimeter of the range B13:E16. If we used the regular Paste command, the double line on the right side of the range E13:E16 would be replaced with a single line. Therefore, we are going to use one of the Paste Special commands to paste only the functions without any of the formatting treatments. This is accomplished through the following steps:

1. Highlight the range D13:D16 in the Budget Detail worksheet.

2. Click the Copy button in the Home tab of the Ribbon.

3. Click cell E13.

4. Click the down arrow below the Paste button in the Home tab of the Ribbon.

5. Click the Formulas option from the drop-down list of buttons (see Figure 2.26).

Figure 2.26 shows the list of buttons that appear when you click the down arrow below the Paste button in the Home tab of the Ribbon. One thing to note about these options is that you can preview them before you make a selection by dragging the mouse pointer over the options. As shown in the figure, when the mouse pointer is placed over the Formulas button, you can see how the functions will appear before making a selection. Notice that the double-line border does not change when this option is previewed. That is why this selection is made instead of the regular Paste option.

Figure 2.26 Paste Formulas Option

Skill Refresher

Paste Formulas

1. Click a cell location containing a formula or function.

2. Click the Copy button in the Home tab of the Ribbon.

3. Click the cell location or cell range where the formula or function will be pasted.

4. Click the down arrow below the Paste button in the Home tab of the Ribbon.

5. Click the Formulas button under the Paste group of buttons.

Sorting Data (Multiple Levels)

Data file: Continue with CH2 Personal Budget.

The Budget Detail worksheet shown in Figure 2.26 is now producing several mathematical outputs through formulas and functions. The outputs allow you to analyze the details and identify trends as to how money is being budgeted and spent. Before we draw some conclusions from this worksheet, we will sort the data based on the Percent of Total column. Sorting is a powerful tool that enables you to analyze key trends in any data set. Sorting will be covered thoroughly in a later chapter, but will be briefly introduced here. For the purposes of the Budget Detail worksheet, we want to set multiple levels for the sort order. This is accomplished through the following steps:

1. Highlight the range A2:F11 in the Budget Detail worksheet.

2. Click the Data tab in the Ribbon.

3. Click the Sort button in the Sort & Filter group of commands. This opens the Sort dialog box, as shown in Figure 2.27.

4. Click the down arrow next to the “Sort by” box.

5. Click the Percent of Total option from the drop-down list.

6. Click the down arrow next to the sort Order box.

7. Click the Largest to Smallest option.

8. Click the Add Level button. This allows you to set a second level for any duplicate values in the Percent of Total column.

9. Click the down arrow next to the “Then by” box.

10. Select the LY Spend option. Leave the Sort Order as Smallest to Largest

11. Click the OK button at the bottom of the Sort dialog box.

12. Save the Ch2 Personal Budget file.

Figure 2.27 Sort Dialog Box

Figure 2.28 shows the Budget Detail worksheet after it has been sorted. Notice that there are three identical values in the Percent of Total column. This is why a second sort level had to be created for this worksheet. The second sort level arranges the values of 8.4% based on the values in the LY Spend column in ascending order. Excel gives you the option to set as many sort levels as necessary for the data contained in a worksheet.

Figure 2.28 Budget Detail Worksheet after Sorting

Skill Refresher

Sorting Data (Multiple Levels)

1. Highlight a range of cells to be sorted.

2. Click the Data tab of the Ribbon.

3. Click the Sort button in the Sort & Filter group.

4. Select a column from the “Sort by” drop-down list in the Sort dialog box.

5. Select a sort order from the Order drop-down list in the Sort dialog box.

6. Click the Add Level button in the Sort dialog box.

7. Repeat Steps 4 and 5.

8. Click the OK button on the Sort dialog box.

Now that the Budget Detail worksheet is sorted, a few key trends can be easily identified. The worksheet clearly shows that the top three categories as a percentage of total budgeted spending for the year are Taxes, Household Utilities, and Food. All three categories are necessities (or realities) of life and typically require a significant amount of income for most households. Looking at the Percent Change column, we can see how our planned spending is expected to change from last year. This is perhaps the most important column on the worksheet because it allows you to assess whether your plan is realistic. You will see that there are no changes planned for Taxes and Household Utilities. While Taxes can change from year to year, it is not too difficult to predict what they will be. In this case, we are assuming that there are no changes to the tax costs for our budget. We are also planning no change in Household Utilities. These costs can fluctuate from year to year as well. However, you can take measures to reduce costs, such as using less electricity, turning off heat when no one is in the house, keeping track of your wireless minutes so you do not go over the maximum allowed in your plan, and so on. As a result, there is no change in planned spending for Household Utilities because we will assume that any rate increases will be offset with a decrease in usage. The third item that is planned not to change is Insurance. Insurance policies for cars and homes can change, but as is true for taxes, the changes are predictable. Therefore, we are assuming no changes in our insurance policy.

The first big change that is noticeable in the worksheet is the Food and Entertainment categories in rows 5 and 6 (see definitions in Table 2.1). The Percent Change column indicates that there is an 11.1% decrease in Entertainment spending and an 11.1% increase in Food spending. This is logical because if you plan to eat in restaurants less frequently, you will be eating at home more frequently. Although this makes sense in theory, it may be hard to do in practice. Dinners and parties with friends may be tough to turn down. However, the entire process of maintaining a budget is based on discipline, and it certainly takes a significant amount of discipline to plan targets for yourself and stick to them.

A few other points to note are the changes in the Gasoline and Vacation categories. If you commute to school or work, the price of gas can have a significant impact on your budget. It is important to be realistic if gas prices are increasing, and you should reflect these increases in your budget. To compensate for the increased spending for gas, the spending plan for vacations has been reduced by 25%. Budgeting often requires a certain degree of creativity. Although the Vacation budget has been reduced, there is still money you can set aside to make plans for spring break or winter break.

Finally, the budget shows a decrease in Miscellaneous spending of 19.8%. This was defined as a group containing several expenses, such as textbooks, school supplies, software updates, and so on (see Table 2.1). You may be able to reduce your spending in this category if you can use items such as online textbooks. This reduction in spending can free up funds for Clothes, a spend category that has increased by 20%. We will continue to develop the Personal Budget workbook further in Section 2.3 “Functions for Personal Finance”.

Key Takeaways

- Statistical functions are used when a mathematical process is required for a range of cells, such as summing the values in several cell locations. For these computations, functions are preferable to formulas because adding many cell locations one at a time to a formula can be very time-consuming.

- Statistical functions can be created using cell ranges or selected cell locations separated by commas. Make sure you use a cell range (two cell locations separated by a colon) when applying a statistical function to a contiguous range of cells.

- To prevent Excel from changing the cell references in a formula or function when they are pasted to a new cell location, you must use an absolute reference. You can do this by placing a dollar sign ($) in front of the column letter and row number of a cell reference.

- The #DIV/0 error appears if you create a formula that attempts to divide a constant or the value in a cell reference by zero.

- The Paste Formulas option is used when you need to paste formulas without any formatting treatments into cell locations that have already been formatted.

- You need to set multiple levels, or columns, in the Sort dialog box when sorting data that contains several duplicate values.

Attribution

Adapted by Mary Schatz 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.

9

2.3 Functions for Personal Finance

Learning Objectives

1. Understand the fundamentals of loans and leases.

2. Use the PMT function to calculate monthly mortgage payments on a house.

3. Use the PMT function to calculate monthly lease payments for an automobile.

4. Learn how to summarize data in a workbook by using worksheet links to create a summary worksheet.

In this section, we continue to develop the Personal Budget workbook. Notable items that are missing from the Budget Detail worksheet are the payments you might make for a car or a home. This section demonstrates Excel functions used to calculate lease payments for a car and to calculate mortgage payments for a house.

The Fundamentals of Loans and Leases

One of the functions we will add to the Personal Budget workbook is the PMT function. This function calculates the payments required for a loan or a lease. However, before demonstrating this function, it is important to cover a few fundamental concepts on loans and leases.

A loan is a contractual agreement in which money is borrowed from a lender and paid back over a specific period of time. The amount of money that is borrowed from the lender is called the principal of the loan. The borrower is usually required to pay the principal of the loan plus interest. When you borrow money to buy a house, the loan is referred to as a mortgage. This is because the house being purchased also serves as collateral to ensure payment. In other words, the bank can take possession of your house if you fail to make loan payments. As shown in Table 2.5, there are several key terms related to loans and leases.

Table 2.5 Key Terms for Loans and Leases

Term

Definition

Collateral

Any item of value that is used to secure a loan to ensure payments to the lender

Down Payment

The amount of cash paid toward the purchase of a house. If you are paying 20% down, you are paying 20% of the cost of the house in cash and are borrowing the rest from a lender.

Interest Rate

The interest that is charged to the borrower as a cost for borrowing money

Mortgage

A loan where property is put up for collateral

Principal

The amount of money that has been borrowed

Residual Value

The estimated selling price of a vehicle at a future point in time

Terms

The amount of time you have to repay a loan

Figure 2.29 shows an example of an amortization table for a loan. A lender is required by law to provide borrowers with an amortization table when a loan contract is offered. The table in the figure shows how the payments of a loan would work if you borrowed $100,000 from a lender and agreed to pay it back over 10 years at an interest rate of 5%. You will notice that each time you make a payment, you are paying the bank an interest fee plus some of the loan principal. Each year the amount of interest paid to the bank decreases and the amount of money used to pay off the principal increases. This is because the bank is charging you interest on the amount of principal that has not been paid. As you pay off the principal, the interest rate is applied to a lower number, which reduces your interest charges. Finally, the figure shows that the sum of the values in the Interest Payment column is $29,505. This is how much it costs you to borrow this money over 10 years. Indeed, borrowing money is not free. It is important to note that to simplify this example, the payments were calculated on an annual basis. However, most loan payments are made on a monthly basis.

Figure 2.29 Example of an Amortization Table

A lease is a contract in which you, the lessee, use an asset such as a car or a piece of equipment and you agree to make regular payments to the owner or the lessor. When you lease a car, the manufacturer or a leasing company retains ownership of the vehicle and you agree to make regular payments for a specific period of time. The amount of money you pay depends on the price of the car, the terms of the lease contract, and the car’s expected residual value at the end of the lease. The calculation of lease payments is similar to the calculation of loan payments. However, when you lease a car, you pay only the value of the car that is used. For example, suppose you are leasing a car that is priced at $25,000. The lease contract is for 4 years at an interest rate of 5%. The residual value of the car is $10,000. This means the car will lose $15,000 of its value over 4 years. Another way to state this is that the car will depreciate $15,000. A lease will be structured so that you pay this $15,000 in depreciation. However, the interest charges will be based on the purchase price of $25,000. We will look at a demonstration of leasing a car as well as buying a home in the next section.

The PMT (Payment) Function for Loans

Data file: Continue with CH2 Personal Budget.

If you own a home, your mortgage payments are a major component of your household budget. If you are planning to buy a home, having a clear understanding of your monthly payments is critical for maintaining strong financial health. In Excel, mortgage payments are conveniently calculated through the PMT (payment) function. This function is more complex than the statistical functions covered in Section 2.2 “Statistical Functions”. With statistical functions, you are required to add only a range of cells or selected cells within the parentheses of the function, also known as the argument. With the PMT function, you must accurately define a series of arguments in order for the function to produce a reliable output. Table 2.6 lists the arguments for the PMT function. It is helpful to review the key loan and lease terms in Table 2.5 before reviewing the PMT function arguments.

Table 2.6 Arguments for the PMT Function

Argument

Definition

Rate

This is the interest rate the lender is charging the borrower. The interest rate is usually quoted in annual terms, so you have to divide this rate by 12 if you are calculating monthly payments.

Nper

The argument letters stand for number of periods. This is the term of the loan, which is the amount of time you have to repay the bank. This is usually quoted in years, so you have to multiply the years by 12 if you are calculating monthly payments.

Pv

The argument letters stand for present value. This is the principal of the loan or the amount of money that is borrowed. When defining this argument, a minus sign must precede the cell location or value. For leases, this argument is used for the price of the item being leased.

[Fv]

The argument letters stand for future value. The brackets around the argument indicate that it is not always necessary to define it. It is used if there is a lump-sum payment that will be made at the end of the loan terms. This is also used for the residual value of a lease. If it is not defined, Excel will assume that it is zero.

[Type]

This argument can be defined with either a 1 or a 0. The number 1 is used if payments are made at the beginning of each period. A 0 is used if payments are made at the end of each period. The argument is in brackets because it does not have to be defined if payments are made at the end of each period. Excel assumes that this argument is 0 if it is not defined.

By default, the result of the PMT function in Excel is shown as a negative number. This is because it represents an outgoing payment. When making a mortgage or car payment, you are paying money out of your pocket or bank account. Depending on the type of work that you do, your employer may want you to leave your payments negative or they may ask you to format them as positive numbers. In the following assignments, the payments calculated using the PMT function will be made positive to make them easier to work with. To do this, when defining the PV argument (amount of money borrowed) in the PMT dialog box, a minus sign must precede the cell location or value (see the PV argument in Figure 2.32).

We will use the PMT function in the Personal Budget workbook to calculate the monthly mortgage payments for a house. These calculations will be made in the Mortgage Payments worksheet and then displayed in the Budget Summary worksheet through a cell reference link. So far we have demonstrated several methods for adding functions to a worksheet. The following steps explain a new method using the Insert Function command for adding the PMT function:

1. Click the Mortgage Payments worksheet tab.

2. Click cell B5.

3. Click the Formulas tab on the Ribbon.

4. Click the Insert Function button (see Figure 2.30). This opens the Insert Function dialog box, which can be used for searching all functions in Excel.

5. In the “Search for a function:” input box at the top of the Insert Function dialog box, type mortgage payments (see Figure 2.31). Note that the current description in the “Search for a function:” input box will already be highlighted. You can begin typing and the description will be replaced with your entry.

6. Click the Go button in the upper right side of the Insert Function dialog box. This adds all the Excel functions that match your description in the “Select a function:” box in the lower half of the Insert Function dialog box (see Figure 2.31).

7. Click the PMT option in the “Select a function:” box in the lower half of the Insert Function dialog box.

8. Click the OK button at the lower right side of the Insert Function dialog box. This will open the Function Arguments dialog box.

Figure 2.30 Mortgage Payments Worksheet

Figure 2.31 Insert Function Dialog Box

9. Click the Collapse Dialog button next to the Rate argument in the Function Arguments dialog box. This will be the first argument defined for the function (see Figure 2.32)

10. Click cell B3 on the worksheet. This is the rate being charged on the loan.

11. Type a forward slash / for division.

12. Type the number 12. Since our goal is to calculate the monthly payments for the loan, we need to divide the rate, which is stated in annual terms, by 12. This converts the annual rate to a monthly rate.

13. Press the ENTER key on your keyboard. This returns the Function Arguments dialog box to its expanded form. You will also see that the Rate argument is now defined.

14. Click the Collapse Dialog button next to the Nper argument in the Function Arguments dialog box. This is the second argument we define in the function.

15. Click cell B4 on the worksheet. This is the term or the amount of time we have to repay the loan.

16. Type an asterisk * for multiplication.

17. Type the number 12. Since our goal is to calculate the monthly payments for the loan, we need to multiply the terms of the loan by 12. This converts the terms of the loan from years to months.

18. Press the ENTER key on your keyboard. This returns the Function Arguments dialog box to its expanded form. You will also see that the Nper argument is now defined.

19. Click the Collapse Dialog button next to the Pv argument in the Function Arguments dialog box. This is the third argument we will define in the function.

20. Type a minus sign −. When defining the Pv argument of the PMT function, any cell location or value must be preceded with a minus sign.

21. Click cell B2 on the worksheet. This is the principal of the loan.

22. Press the ENTER key on your keyboard. You will now see the Rate, Nper, and Pv arguments defined for the function.

23. Click the OK button at the bottom of the Function Arguments dialog box. The function will now be placed into the worksheet. Since we are not paying any lump sums of money at the end of the loan, there is no need to define the Fv argument. Also, we will assume that the monthly mortgage payments will be made at the end of each month. Therefore, there is no need to define the Type argument.

Keyboard Shortcuts

Insert Function

- Hold the SHIFT key while pressing the F3 key.

Function Arguments Dialog Box

- After the equal sign = and function name are typed into cell a location, hold down the CTRL key and press the letter A on your keyboard.

Figure 2.32 shows the completed Function Arguments dialog box for the PMT function. Notice that the dialog box shows the values for the Rate and Nper arguments. The Rate is divided by 12 to convert the annual interest rate to a monthly interest rate. The Nper argument is multiplied by 12 to convert the terms of the loan from years to months. Finally, the dialog box provides you with a definition for each argument. The definition appears when you click in the input box for the argument.

Figure 2.32 Function Arguments Dialog Box for the PMT Function

Integrity Check

Comparable Arguments for PMT Function

When using functions such as PMT, make sure the arguments are defined in comparable terms. For example, if you are calculating the monthly payments of a loan, make sure both the Rate and Nper argument are expressed in terms of months. The function will produce an erroneous result if one argument is expressed in years while the other is expressed in months.

Figure 2.33 shows the final appearance of the Mortgage Payments worksheet after the PMT function is added. The result of the function in cell B5 will be displayed in the Budget Summary worksheet.

Figure 2.33 Mortgage Payments Worksheet with the PMT Function

The PMT (Payment) Function for Leases

In addition to calculating the mortgage payments for a home, the PMT function will be used in the Personal Budget workbook to calculate the lease payments for a car. The details for the lease payments are found in the Car Lease Payments worksheet. Similar to the statistical functions, we can either type the PMT function directly into a cell or use the Insert Function button. However, you must know the definitions for each argument of the function and understand how these arguments need to be defined based on your objective. The terms for loans and leases are in Table 2.5, and the definitions for the arguments of the PMT function are in Table 2.6. The following steps explain how the PMT function is added to the Personal Budget workbook to calculate the lease payments for a car:

1. Click cell B6 in the Car Lease Payments worksheet.

2. Click the Formulas tab on the Ribbon.

3. Click the Financial button in the Function Library group. This opens the Financial Function drop down list.

4. Scroll down and click on the PMT Function in the drop down list. This will open the PMT Function Arguments dialog box. See Figure 2.34.

5. Click the Collapse Dialog button next to the Rate argument in the PMT Function Arguments dialog box. This will be the first argument defined for the monthly car lease payment.

6. Click cell B4. This is the interest rate being charged for the lease.

7. Type the forward slash / for division.

8. Type the number 12. Since our goal is to calculate the monthly lease payments, we divide the interest rate by 12 to convert the annual rate to a monthly rate.

9. Press the ENTER key on your keyboard. This returns the Function Arguments dialog box to its expanded form. You will also see that the Rate argument is now defined.

10. Click the Collapse Dialog button next to the Nper argument in the Function Arguments dialog box. This is the second argument we define in the function.

11. Click cell B5. This is the term or the length of time for the lease contract. Since the term is already expressed in months, we can just reference cell B5 and move to the next argument. If the term was defined in years, instead of months, we would need to multiply the terms (years) of the loan by 12 to convert the years to months.

12. Press the ENTER key on your keyboard. This returns the Function Arguments dialog box to its expanded form. You will also see that the Nper argument is now defined.

13. Click the Collapse Dialog button next to the Pv argument in the Function Arguments dialog box. This is the third argument we will define in the function.

14. Type a minus sign −. Remember that cell locations or values used to define the Pv argument must be preceded with a minus sign.

15. Click cell B2 on the worksheet, which is the price of the car.

16. Press the ENTER key on your keyboard. You will now see the Rate, Nper, and Pv arguments defined for the function.

17. Click the Collapse Dialog button next to the Fv argument in the Function Arguments dialog box. This is the fourth argument we will define in the function.

18. Click cell B3 on the worksheet. This is the residual value of the car. Note that cell location and values used to define the [Fv] argument are NOT preceded by a minus sign.

19. Press the ENTER key on your keyboard. You will now see the Rate, Nper, Pv and Fv arguments defined for the function.

20. Click the Collapse Dialog button next to the Type argument in the Function Arguments dialog box. This is the fifth and last argument we will define in the function.

21. Type the number 1. We will assume that the lease payments will be due at the beginning of each month. For payments made at the beginning of the period, a 1 will be entered in the Type argument box. For payments made at the end of the period, a 0 will be entered in the Type argument box.

22. Press the ENTER key. You will now see the Rate, Nper, Pv, Fv and Type arguments defined for the function. See Figure 2.34.

23. Click the OK button at the bottom of the Function Arguments dialog box. The function will now be placed into the worksheet.

Figure 2.34 shows how the the completed Function Arguments dialog box for the PMT function car lease should appear before pressing the OK button.

Figure 2-34 Function Arguments Dialog Box for the PMT Lease Function

Figure 2.35 shows the result of the PMT function for the car lease. The monthly payments for this lease are $206.56. This monthly payment will be displayed in the Budget Summary worksheet.

Figure 2.35 Results of the PMT Function in the Car Lease Payments Worksheet

Skill Refresher

PMT Function

1. Type an equal sign =.

2. Type the letters PMT followed by an open parenthesis, or double click the function name from the function list.

3. Define the Rate argument with a cell location that contains the rate being charged by the lender for the loan or lease. If the interest rate given is an annual rate, divide it by 12 to convert it to a monthly rate.

4. Define the Nper argument with a cell location that contains the amount of time to repay the loan or lease. If the amount of time is in years, multiply it by 12 to convert it to number of months.

5. Define the Pv argument with a cell location that contains the principal of the loan or the price of the item being leased. Cell locations or values used for this argument must be preceded by a minus sign.

6. Define the [Fv] argument with a cell location that contains the residual value of the item being leased or the lump sum payment for a loan.

7. Define the [Type] argument with a 1 if payments are made at the beginning of each period or 0 if payments are made at the end of each period.

8. Type a closing parenthesis ).

9. Press the ENTER key.

Linking Worksheets (Creating a Summary Worksheet)

So far we have used cell references in formulas and functions, which allow Excel to produce new outputs when the values in the cell references are changed. Cell references can also be used to display values or the outputs of formulas and functions in cell locations on other worksheets. This is how data will be displayed on the Budget Summary worksheet in the Personal Budget workbook. Outputs from the formulas and functions that were entered into the Budget Detail, Mortgage Payments, and Car Lease Payments worksheets will be displayed on the Budget Summary worksheet through the use of cell references. The following steps explain how this is accomplished:

1. Click cell C3 in the Budget Summary worksheet.

2. Type an equal sign =.

3. Click the Budget Detail worksheet tab.

4. Click cell D12 on the Budget Detail worksheet.

5. Press the ENTER key on your keyboard. The output of the SUM function in cell D12 on the Budget Detail worksheet will be displayed in cell C3 on the Budget Summary worksheet.

Figure 2.36 shows how the cell reference appears in the Budget Summary worksheet. Notice that the cell reference D12 is preceded by the Budget Detail worksheet name enclosed in apostrophes followed by an exclamation point (‘Budget Detail’!) This indicates that the value displayed in the cell is referencing a cell location in the Budget Detail worksheet.

Figure 2.36 Cell Reference Showing the Total Expenses in the Budget Summary Worksheet

As shown in Figure 2.36, the Budget Summary worksheet is designed to show the expense budget for the mortgage payments and the auto lease payments. However, you will recall that we used the PMT function to calculate the monthly payments. In the Budget Summary worksheet, we need to show the total annual payments. As a result, we will create a formula that references cell locations in the Mortgage Payments and Car Lease Payments worksheets. The following steps explain how this is accomplished:

1. Click cell C4 in the Budget Summary worksheet.

2. Type an equal sign =.

3. Click the Mortgage Payments worksheet tab.

4. Click cell B5 in the Mortgage Payments worksheet.

5. Type an asterisk * for multiplication.

6. Type the number 12. This multiplies the monthly payments by 12 to calculate the total payments required for the year. The formula in the formula bar should read: =’Mortgage Payments’!B5*12

7. Press the ENTER key on your keyboard. The value of multiplying the monthly mortgage payments by 12 is now displayed on the Budget Summary worksheet.

8. Click cell C5 on the Budget Summary worksheet.

9. Type an equal sign =.

10. Click the Car Lease Payments worksheet tab.

11. Click cell B6 in the Car Lease Payments worksheet.

12. Type an asterisk * for multiplication.

13. Type the number 12. This multiplies the monthly lease payments by 12 to calculate the total payments required for the year.

14. Press the ENTER key on your keyboard. The value of multiplying the monthly lease payments by 12 is now displayed on the Budget Summary worksheet.

Figure 2.37 shows the results of creating formulas that reference cell locations in the Mortgage Payments and Car Lease Payments worksheets.

Figure 2.37 Formulas Referencing Cells in Mortgage Payments and Car Lease Payments Worksheets

We can now add other formulas and functions to the Budget Summary worksheet that can calculate the difference between the total spend dollars vs. the total net income in cell D2. The following steps explain how this is accomplished:

1. Click cell D6 in the Budget Summary worksheet.

2. Type an equal sign =.

3. Type the function name SUM followed by an open parenthesis (.

4. Highlight the range C3:C5.

5. Type a closing parenthesis ) and press the ENTER key on your keyboard or simply press the ENTER key to close the function. The total for all annual expenses now appears on the worksheet.

6. Click cell D7 on the Budget Summary worksheet. You will enter a formula to calculate Net Change in Cash in this cell.

7. Type an equal sign =.

8. Click cell D2.

9. Type a minus sign − and then click cell D6.

10. Press the ENTER key on your keyboard. This formula produces an output of $1,942, indicating our income is greater than our total expenses.

Figure 2.38 shows the results of the formulas that were added to the Budget Summary worksheet. The output for the formula in cell D7 shows that the net income exceeds total planned expenses by $1,942. Overall, having your income exceed your total expenses is a good thing because it allows you to save money for future spending needs or unexpected events.

Figure 2.38 Formulas Added to Show Income Is Greater Than Expenses

We can now add a few formulas that calculate both the spending rate and the savings rate as a percentage of net income. These formulas require the use of absolute references, which we covered earlier in this chapter. The following steps explain how to add these formulas:

1. Click cell E6 in the Budget Summary worksheet.

2. Type an equal sign =.

3. Click cell D6.

4. Type a forward slash / for division and then click D2.

5. Press the F4 key on your keyboard. This adds an absolute reference to cell D2.

6. Press the ENTER key. The result of the formula shows that total expenses consume 94.1% of our net income.

7. Click cell E6.

8. Place the mouse pointer over the Auto Fill Handle.

9. When the mouse pointer turns to a black plus sign, left click and drag down to cell E7. This copies and pastes the formula into cell E7.

10. Save the CH2 Personal Budget file.

11. Compare your work with the self-check answer key (found in the Course Files link) and then submit the CH2 Personal Budget workbook as directed by your instructor.

12. Close the CH2 Personal Budget file before moving on to 2.4 – Preparing to Print.

Figure 2.39 shows the output of the formulas calculating the spending rate and savings rate as a percentage of net income. The absolute reference shown for cell D2 prevents the cell from changing when the formula is copied from cell E6 and pasted into cell E7. The results of the formula show that our current budget allows for a savings rate of 5.9%. This is a fairly good savings rate.

Figure 2.39 Calculating the Savings Rate

Key Takeaways

- The PMT function can be used to calculate the monthly mortgage payments for a house or the monthly lease payments for a car.

- When using the PMT function, each argument must be separated by a comma.

- To calculate the monthly payment for a loan using the PMT function, the Rate and Nper arguments must be defined in terms of months. The Rate should be divided by 12 to convert it from an annual rate to a monthly rate. The Nper should be multiplied by 12 to convert the term of the loan from years to months.

- The PMT function produces a negative output if the Pv argument is not preceded by a minus sign. For the purposes of this textbook, a minus sign will be entered before the PV argument in the PMT dialog box.

Attribution

Adapted by Mary Schatz 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.

10

2.4 Preparing to Print

Learning Objectives

1. Review and learn new cell formatting techniques.

2. Understand how to modify page scaling and margins.

3. Create custom headers and footers to automatically update information.

In this section, we will review some of the formatting techniques covered in Chapter 1, as well as learn some new techniques. We will also preview a two-page worksheet and set page setup options to present the data in a professional manner. A new data file will be used in this section.

Figure 2.40 Finished Prepare to Print Worksheet in Print Preview

Formatting Worksheet Data

Download Data File: CH2 PTP Data

You have been given sales data that needs to be formatted in a professional manner. This worksheet will be printed and presented to investors, so it needs to be prepared for printing as well. Figure 2.40 shows how the finished worksheet will appear in Print Preview.

1. Open the Data file named CH2 PTP Data and use the File/Save As command to save it with the new name CH2 Sales Data.

2. To change the font of the entire worksheet, click the Select All button in the top left corner of the worksheet grid (see Figure 2.41).

Figure 2.41 Select All button

3. Change the font to Calibri, Size 12.

4. Using the skills learned in Chapter 1, make the following formatting changes:

1. A1:H1 – Merge and Center; format text as bold and apply a font color and size of your choice

2. A2:H2 – Merge and Center; format text as bold and italic, apply a font color of your choice

3. A5:H5 – Apply a dark fill color; format text as white and bold

4. C5:H5 – Center align

5. A15:H15 – Apply Top Border to the cells; format text as bold

6. C6:H6 and C15:H15 – Apply Accounting Number format with 0 decimal places

7. C7:H14 – Apply Comma style with 0 decimal places

8. Highlight A6:A14 (salespeople’s names) and click the Increase Indent button in the Alignment group on the Home ribbon (see Figure 2.42). This will indent the text from the cell border.

Figure 2.42 Increase Indent button

Using Page Setup Options

Once the worksheet is professionally formatted, you need to look in Print Preview to see how the pages will print.

1. With the CH2 Sales Data file still open, go to Backstage View by clicking the File tab on the ribbon. Select Print from the menu. Notice that the worksheet is currently printing on two pages, with the page breaking between the April and May columns. To fix this problem, you will first change the left and right margins while still in Print Preview

2. Click the Margins drop-down arrow in the Settings section (see Figure 2.43)

3. Select Custom Margins… at the bottom of the list.

4. Type in 0.5 for the Left Margin and 0.5 for the Right Margin.

5. Click OK. Changing the margins brought the May column onto the same page, but the June column is still on a separate page. Next you will use Page Scaling to fix this while still in Print Preview.

6. Click the Scaling drop-down arrow in the Settings section (Figure 2.43).

7. Select Fit All Columns on One Page.

8. Exit Backstage View.

Figure 2.43 Settings section of Print Preview

Creating a Footer using Page Setup

Now that the entire worksheet is printing on one page, you need to add a footer with information about the date the file was printed along with the filename. In Chapter 1 you learned how to create headers and footers using the Insert ribbon. You can also create headers and footers using the Custom Header/Footer dialog box.

1. Click the Page Layout tab on the ribbon.

2. Click the dialog box launcher in the Page Setup group. A window similar to Figure 2.44 should appear.

Figure 2.44 Page Setup Dialog Box

3. Click the Header/Footer tab in the Page Setup dialog box.

4. Click the Custom Footer button. The Footer dialog box should appear (see Figure 2.45).

Figure 2.45 Footer Dialog Box

5. Click in the Left section: box and type Printed on.

6. Making sure to leave a space after the word on, click the Insert Date button.

7. Click in the Right section: box and type Filename:.

8. Making sure to leave a space after the colon, click the Insert File Name button.

9. The Footer dialog box should look like Figure 2.46.

10. Click the OK button. Click OK again to close the Page Setup dialog box.

11. Go to Print Preview to see that the current date and file name are displayed in the footer.

12. Exit Backstage View. Save the CH2 Sales Data file.

13. Compare your work with the self-check answer key (found in the Course Files link) and then submit the CH2 Sales Data workbook as directed by your instructor.

Figure 2.46 Completed Custom Footer Dialog Box

Key Takeaways

- It is important to always check your workbooks in Print Preview to ensure that the data is printed in a professional and easy to read manner.

- Adjust margins and page scaling as needed to keep columns of data together on one page if possible.

- Use headers and footers to display information in the top and bottom margins of the printed worksheet. Use the Insert buttons to insert changing information, such as dates and file names, instead of typing them in directly.

Attribution

“2.4 Preparing to Print” by Julie Romey, Portland Community College is licensed under CC BY 4.0

11

2.5 Chapter Practice

Financial Plan for a Lawn Care Business

Download Data File: PR2 Data

Running your own lawn care business can be an excellent way to make money over the summer while on break from college. It can also be a way to supplement your existing income for the purpose of saving money for retirement or for a college fund. However, managing the costs of the business will be critical in order for it to be a profitable venture. In this exercise you will create a simple financial plan for a lawn care business by using the skills covered in this chapter.

1. Open the file named PR2 Data and then Save As PR2 Lawn Care.

2. Click cell C5 in the Annual Plan worksheet.

3. Write a formula that calculates the average price per lawn cut.  Do not use the AVERAGE function. The formula should be the Price per Acre multiplied by the Average Acreage per Customer.

4. Click cell C8.

5. Enter a formula that calculates the total number of lawns that will be cut during the year (Number of Customers * Frequency of Lawn Cuts per Customer).

6. Click cell D9.

7. Enter a formula that calculates the total sales for the plan (Average Price per Cut * Total Lawn Cuts).

8. Click cell F3 in the Leases worksheet. The PMT function will be used to calculate the monthly lease payment for the first item. For many businesses, leasing (or renting) equipment is a more favorable option than purchasing equipment because it requires far less cash. This enables you to begin a business such as a lawn care business without having to put up a lot of money to buy equipment. The PMT function can be entered using the Insert Function button as seen in Chapter 2, or we can type the PMT function directly into a cell. For this assignment, we will type the function into cell F3 using the following instructions.

9. Type =PMT(. Define the arguments of the function as follows:

10. Rate: Click cell B3, type a forward slash / for division, type the number 12, and type a comma ,. Since we are calculating monthly payments, the annual interest rate must be converted to a monthly interest rate by dividing by 12.

11. Nper: Click cell C3, type *12 and then type a comma ,. Similar to the Rate argument, the terms of the lease must be converted to months by multiplying by 12 since we are calculating monthly payments.

12. Pv: Type a minus sign −, click cell D3, and type a comma ,. Remember that this argument must always be preceded by a minus sign.

13. Fv: Click cell E3 (Residual Value) and type a comma ,.

14. Type: Type the number 1, type a closing parenthesis ), and press the ENTER key. We will assume the lease payments will be made at the beginning of each month, which requires that this argument be defined with a value of 1.

15. Copy the PMT function in cell F3 and paste it into the range F4:F6, or use the Autofill handle.

16. Click cell F10 in the Leases worksheet. Enter an Autosum function to calculate the total for the monthly lease payments. Make sure that blank rows (7 through 9) were included in the range for the SUM function. If other items are added to the worksheet, they will be included in the output of the SUM function.

17. Highlight the range A2:F6 on the Leases worksheet. The data in this range will be sorted, first by Interest Rate and then by Price. Click the Sort button in the Data tab of the Ribbon. In the Sort dialog box, select the Interest Rate option in the “Sort by” drop-down box. Select Largest to Smallest for the sort order. Then, click the Add Level button on the Sort dialog box. Select the Price option in the “Then by” drop-down box. Select Largest to Smallest for the sort order. Click the OK button in the Sort dialog box.

18. Click cell B11 on the Annual Plan worksheet. The monthly lease payments that are calculated in the Lease worksheet will be displayed in this cell.

19. Type an equal sign =. Click the Leases worksheet, click cell F10, and press the ENTER key.

20. Click cell C12 on the Annual Plan worksheet. Create a formula that calculates the annual lease payments. This should be the Monthly Lease Payments * 12.

21. Click cell C14 on the Annual Plan worksheet. Create a formula that calculates the Total Lawn & Equipment Expenses (Lawn & Equipment Expenses per Cut * Total Lawn Cuts).

22. Click cell D16 on the Annual Plan worksheet. Enter a SUM function that adds the Expenses for the business in column C. Make sure to add the Expenses only (not the Sales Plan information).

23. Click cell D17 on the Annual Plan worksheet. Enter a formula that calculates the annual profit (Operating Income) for the business. This should be the Total Sales – Total Expenses.

24. Format all cells that contain money amounts in the Annual Plan worksheet for Accounting Number Format ($) with no decimals.

25. Click cell B10 on the Investments worksheet. Enter a COUNT function that counts the number of investments that currently have a balance in column B. Make sure that the additional blank rows in rows 6 through 8 are included in the range for this function. The function output will automatically change if any new investments are added to the worksheet. It is important to note, however, that the Total in cell B9 should not be included in the Count range.

26. Click cell D3 on the Investments worksheet.

27. Type an equal sign =. Click the Annual Plan worksheet. Click cell D17 and type a forward slash / for division. Click the Investments worksheet. Click cell B10 and press the ENTER key. This formula divides the profit calculated on the Annual Plan worksheet by the number of investments in the Investments worksheet. We will assume that the profits from this business will be invested evenly among the funds listed in Column A of the Investments worksheet.

28. Before copying and pasting the formula created in cell D3, absolute references must be added to the cell locations in the formula. Edit the formula in cell D3 on the Investments worksheet so that cells D17 and B10 are absolute. The formula in cell D3 should be: =’Annual Plan’!$D$17/Investments!$B$10. When you copy the formula in cell D3 down, the cell references will not change, because they are absolute. The formula will continue to divide the Operating Income in cell D17 of the Annual Plan by the Number of Investments in cell B10 of the Investments sheet.

29. Copy cell D3 and paste it into cells D4 and D5 or use the Auto Fill handle to copy down.

30. Click cell B9 on the Investments worksheet. Enter a SUM function that adds the current balance for all investments in column B. Make sure that blank rows (rows 6 through 8) are added to the range for the function so additional investments will automatically be included in the Autosum function output.

31. Copy the SUM function in cell B9 and paste it into cell D9.

32. Format the Investments and Leases sheets appropriately for Accounting Format. This should include $ signs on the top row and total row for money amounts, and comma style in the middle rows. In the Investments sheet, apply Comma Style with 0 decimals to the ranges B4:B5 and D4:D5. In the Leases worksheet, apply Accounting Number Format ($) with two decimals to the range D3:F3 and F10. Apply comma format with two decimals to the range D4:F9. Double check that your formatting matches Figures 2.47a through 2.47c.

33. Save the PR2 Lawn Care workbook.

34. Compare your work with the self-check answer key (found in the Course Files link) and then submit the PR2 Lawn Care workbook as directed by your instructor.

Figure 2.47a Completed Lawn Care Annual Plan Worksheet

Figure 2.47b Completed Lawn Care Investments Worksheet

Figure 2.47c Completed Lawn Care Leases Worksheet

Attribution

Adapted by Mary Schatz 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.

12

2.6 Chapter Scored

Hotel Occupancy and Expenses

Download Data File: SC2 Data

The hotel management industry presents a wide variety of career opportunities. These range from running a bed and breakfast to a management position at a large hotel. No matter what hotel management career you choose to pursue, understanding hotel occupancy and costs are critical to running a successful operation. This exercise examines the occupancy rate and expenses of a small hotel.

1. Open the file named SC2 Data and then Save As SC2 Hotel.

2. Enter a formula in cell C5 on the Occupancyworksheet to calculate the January capacity for the hotel. The capacity shows how many people the hotel can hold during the month. It is calculated by first multiplying the occupants per room by the number of rooms in the hotel. This result is then multiplied by the number of days in the month (cell B5 for January). Create this formula using absolute references so that the appropriate cells do not change when the formula is pasted throughout column C. Hint: two of the cells in the formula need to be absolute references.

3. Copy the formula in cell C5 and paste it into the range C6:C16. Use a paste method that does not remove the border at the bottom of cell C16.

4. Enter a formula in cell E5 on the Occupancyworksheet to calculate the Percent Occupied of the hotel (this statistic shows what percentage of the hotel is full or occupied). Your formula should divide the Actual Occupancy by the Hotel Capacity. Then copy and paste the formula into the range E6:E16. Use a paste method that does not remove the border at the bottom of cell E16. Format the results in E5:E16 as percentages with two decimal places.

5. Enter a function in cell C17 on the Occupancyworksheet that sums the values in the range C5:C16. Copy the function and paste it into cell D17.

6. Copy the formula in cell E16 and paste it into cell E17. Make sure cell E17 is formatted as a percentage with two decimals and bold.

7. On the Statistics worksheet, enter a function into cell B3 that shows the highest value (Max) in the range D5:D16 in the Actual Occupancy column on the Occupancy worksheet.

8. On the Statistics worksheet, enter a function into cell B4 that shows the lowest value (Min) in the range D5:D16 in the Actual Occupancy column on the Occupancy worksheet.

9. On the Statistics worksheet, enter a function into cell B5 that shows the average value in the range D5:D16 in the Actual Occupancy column on the Occupancy worksheet.

10. Use the Auto Fill handle to copy the formulas in the range B3:B5 to the range C3:C5.

11. Format the range B3:B5 for comma format with zero decimal places. Format cells C3:C5 as percentages with two decimal places.

12. The hotel is considering buying or leasing a car to shuttle customers to and from the airport. You hope to keep the monthly payment under $400 and will determine whether leasing or buying will meet that goal. On the Lease or Buy worksheet, type the terms in Figure 2.48 below for the purchase vs. lease of the car. Make sure that dollar amounts and percentages are formatted to match Figure 2.48:

Figure 2.48 Terms for the purchase vs. lease of the car

13. In cell B8 create a PMT function to calculate the Monthly Payment if you purchase the car. Make sure the arguments in the PMT function are converted into months and that the Monthly Payment is a positive number.

14. In cell C8 create a PMT function to calculate the Monthly Payment if you lease the car. The car will have a residual value of $15,000 when the lease is over. Assume that payments are made at the end of the month.

15. Format the Monthly Payments in B8:C8 for Accounting Number Format with two decimals.

16. Select the range A4:A8 and click the Increase Indent button once to indent the labels in column A.

17. From the Page Layout tab, access the Page Setup dialog box launcher and center the Lease or Buy worksheet horizontally on the page.

18. Insert a footer on the Lease or Buy worksheet. Insert the date (use the Insert Date button) in the left section of the footer. Insert the File Name (use the Insert File Name button) in the right section of the footer.

19. Save the SC2 Hotel workbook.

20. Submit the SC2 Hotel workbook as directed by your instructor.

Attribution

Adapted by Mary Schatz 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.

III

Chapter 3 – Formulas, Functions, Logical and Lookup Functions

Excel workbooks are designed to allow you to create useful and complex calculations. In addition to doing arithmetic, you can use Excel to look up data, and to display results based on logical conditions. We will also look at ways to highlight specific results. These skills will be demonstrated in the context of a typical gradebook spreadsheet that contains the results for an imaginary Excel class.

In this chapter, we will:

- Use the Quick Analysis Tool to find the Total Points for all students and Points Possible.

- Write a division formula to find the Percentage for each student, using an absolute reference to the Total Points Possible.

- Write an IF Function to determine Pass/Fail – passing is 70% or higher.

- Write a VLOOKUP to determine the Letter Grade using the Letter Grades scale.

- Use the TODAY function to insert the current date.

- Review common Error Messages using Smart Lookup to get definitions of some of the terms in your spreadsheet.

- Apply Data Bars to the Total Points values.

- Apply Conditional Formatting to the Percentage, Pass/Fail, and Letter Grade columns.

- Printing Review – Change to Landscape, Scale to Fit Columns on One Page and Set Print Area.

Figure 3-1 shows the completed workbook that will be demonstrated in this chapter. Notice the techniques used in columns O and R that highlight the results of your calculations. Notice, also that there are more numbers on this version of the file than you will see in your original data file. These are all completed using Excel calculations.

Figure 3.1 Completed Gradebook Worksheet

Attribution

Chapter 3 – Formulas, Functions, Logical and Lookup Functions by Noreen Brown and Mary Schatz, Portland Community College, is licensed under CC BY 4.0

13

3.1 More on Formulas and Functions

Learning Objectives

1. Review the use of the =MAX function.

2. Examine the Quick Analysis Tool to create standard calculations, formatting, and charts very quickly.

3. Create Percentage calculation.
– Use the Smart Lookup tool to acquire additional information about percentage calculations.
– Review the use of Absolute cell reference in a division formula.

Another use for =MAX

Before we move on to the more interesting calculations we will be discussing in this chapter, we need to determine how many points it is possible for each student to earn for each of the assignments. This information will go into Row 25. The =MAX function is our tool of choice.

Download Data File: CH3 Data

1. Open the data file CH3 Data and save the file to your computer as CH3 Gradebook.

2. Make B25 your active cell.

3. Start typing =MAX (See Figure 3.2) Note the explanation you see on the offered list of functions. You can either keep typing ( or double click MAX from the list.

Figure 3.2  Entering a function

4. Select the range of numbers above row 25. Your calculation will be: =MAX(B5:B24)

5. Now, use the Fill Handle to copy the calculation from Column B through Column N.
Note that as you copy the calculation from one column to the next, the cell references change. The calculation in column B reads: =MAX(B5:B24). The one in column N reads: =MAX(N5:N24). These cell references are relative references.

By default, the calculations that Excel copies change their cell references relative to the row or column you copy them to. That makes sense. You wouldn’t want column N to display an answer that uses the values in column L.

Want to see all the calculations you have just created? Press Ctrl ~ (See Figure 3.3.) Ctrl ~ displays your calculations (formulas). Pressing Ctrl ~ a second time will display your calculations in the default view – as values.

Figure 3.3 Relative References – Displayed as calculations.

Quick Analysis Tool

The Quick Analysis Tool allows you to create standard calculations, formatting, and charts very quickly. In this exercise we will use it to insert the Total Points for each student in Column O.

Be sure to press Ctrl ~ to return your spreadsheet to the normal view (the formula results should display, not the formulas themselves).

1. Select the range of cells B5:N25

2. In the lower right corner of your selection, you will see the Quick Analysis tool (see Figure 3.4).

Figure 3.4 Quick Analysis Tool

3. When you click on it, you will see that there are a number of different options. This time we will be using the Totals option. In future exercises, we will use other options.

4. Select Totals, and then the SUM option that highlights the right column (see Figure 3.5). Selecting that SUM option places =SUM() calculations in column O.

Figure 3.5 Quick Analysis Tool – Totals, Sum Column

Percentage calculation

Column P requires a Percentage calculation. Before we launch in to creating a calculation for this, it might be handy to know precisely what it is we are looking for. If you are connected to the internet, and are using Excel 2016, you can use the Smart Lookup tool to get some more information.

1. Select cell P4.

2. Find the Smart Lookup tool on the Review tab (see Figure 3.6).

3. Press the Smart Lookup tool to find more about Percentage calculations.
If this is the first time you have used the Smart Lookup tool, you may need to respond to a statement about your privacy. Press the Got it button.

I think the first Wikipedia article does a pretty good job explaining the calculation, don’t you?

Figure 3.6 Smart Lookup tool

Now that we know what is needed for the Percentage calculation, we can have Excel do the calculation for us. We need to divide the Total Points for each student by the Total Points of all the Points Possible. Notice that there is a different number on each row – for each student. But, there is only one Total Points Possible – the value that is in cell O25.

1. Make sure that P5 is your active cell.

2. Press = then select cell O5. Press /, then cell O25. Your calculation should look like this: =O5/O25. The result of the formula should be 0.95641026.  (So far, so good. DeShea Andrews is doing well in this class – with a percentage grade of almost 96%. Definitely an “A”!)

3. Next use the Fill handle to copy the calculation down through row 24 to calculate the other students’ grades. You should get the error message #DIV/0!. This error message reminds us that you can’t divide a number by 0 (zero). And that is just what is happening. If you look at the calculation in P9, the calculation reads: =O9/O29. The first cell reference is correct – it points to Moesha Gashi’s total points for the class. But the second reference is wrong. It points to an empty cell – O29.

Before copying the calculation, we have to make the second reference (O25) an absolute cell reference. That way, when we copy the formula down, the cell reference for O25 will be locked and will not change.

1. Make P5 your active cell. In the Formula Bar click on O25 (see Figure 3.7).

2. Press F4 (on the function keys at the top of your keyboard). That will make the O25 reference absolute. It will not change when you copy the calculation (see Figure 3.8).  (If you are working on a laptop and do not have an F4 function key, you can type in a $ before the O and another one before the 25.)

3. The calculation now looks like this: =O5/$O$25.

4. Use the Fill Handle to copy the formula down through P24 again. Now, when you copy the formula, you will get correct values for all of the students.

Figure 3.7 Editing a formula

Figure 3.8 Absolute Cell reference – press F4

Those long decimals are a bit nonstandard. Let’s change them to % by applying cell formatting.

1. Select the range P5:P24.

2. On the Home tab, in the Number Group, select the % (Percent Style) button.

Skill Refresher

Absolute References

1. Click in front of the column letter of a cell reference in a formula or function that you do not want altered when the formula or function is pasted into a new cell location.

2. Press the F4 key or type a dollar sign ($) in front of the column letter and row number of the cell reference.

Key Takeaways

- Functions can be created using cell ranges or selected cell locations separated by commas. Make sure you use a cell range (two cell locations separated by a colon) when applying a statistical function to a contiguous range of cells.

- To prevent Excel from changing the cell references in a formula or function when they are pasted to a new cell location, you must use an absolute reference. You can do this by placing a dollar sign ($) in front of the column letter and row number of a cell reference or by using the F4 function key.

- The #DIV/0 error appears if you create a formula that attempts to divide a constant or the value in a cell reference by zero.

Attribution

3.1 More on Formulas and Functions by Noreen Brown and Mary Schatz, Portland Community College, is licensed under CC BY 4.0

14

3.2 Logical and Lookup Functions

Learning Objectives

- Use an IF Function to make logical comparisons between a value and what you expect.

- Create a VLOOKUP calculation to look up information in a table.

- Understand error messages.

- Understand how to enter and format Date/Time Functions.

In addition to doing arithmetic, Excel can do other kinds of functions based on the data in your spreadsheet. In this section we will use an =IF function to determine whether a student is passing or failing the class. Then, we will use a =VLOOKUP function to determine what grade each student has earned.

IF Function

The IF function is one of the most popular functions in Excel. It allows you to make logical comparisons between a value and what you expect. In its simplest form, the IF function says something like:
If the value in a cell is what you expect (true) – do this. If not – do that.

The IF function has three arguments:

- Logical test – Here, we can test to see if the value in a selected cell is what we expect. You could use something like “B7=14” or “B7>12” or “B7<6”

- Value_if_true – If the requirements in the logical test are met – if B7 is equal to 14 – then it is said to be true. For this argument you can type text – “True”, or “On budget!” Or you could insert a calculation, like B7*2 (If B7 does equal 14, multiply it by 2). Or, if you want Excel to put nothing at all in the cell, type “” (two quotes).

- Value_if_false – If the requirements in the logical test are not met – if B7 does not equal 14 – then it is said to be false. You can enter the same instructions here as you did above. Let’s say that you type the double quotes here. Then, if B7 does not equal 14, nothing will be displayed in this cell.

In column Q we would like Excel to tell us whether a student is passing – or failing the class. If the student scores 70% or better, he/she will pass the class. But, if he/she scores less than 70%, he/she is failing.

1. Make sure that Q5 is your active cell.

2. On the Formulas tab, in the Function Library, find the IF function on the Logical pulldown menu (see Figure 3.9).

Figure 3.9 IF Function

Now you will see the IF Function dialog box, with a place to enter each of the three arguments.

1. Click in the box for Logical Test. We need to test whether a student’s score is less than .7. So, in this box, type P5<.7

2. Click in the box for Value_if_true. If the student’s score is less than .7, then they are failing the class. In this box, type Fail.

3. Click in the box for Value_if_false. If the student’s score is NOT less than .7, then they are passing the class. In this box, type Pass.

4. Make sure that your dialog box matches Figure 3.10.

Figure 3.10 IF Function Dialog Box

While we are here, let’s take a look at the dialog box. Notice that as you click in each box, Excel gives you a brief explanation of the contents (in the middle below the boxes.) In the lower left hand corner, you can see the results of the calculation. In this case DeShae is passing the class. Below that is a link to Help on this function. Selecting this link will take you to the Excel help for this function – with detailed information on how it works.

5. Once you have typed in the required arguments and reviewed to make sure they are correct, press OK.  (The text Pass should be displayed in Q5 because DeShae is passing the class.)

6. Use the Fill handle to copy the IF function down through row 24.

7. Click on Q5. When you look in the formula bar, you will see the IF calculation: =IF(P5<0.7,”Fail”,”Pass”) (see Figure 3.11).

Figure 3.11 IF Function Results

VLOOKUP Function

You need to use a VLOOKUP function to look up information in a table. Sometimes that table is on a different sheet in your workbook.  Sometimes it is in another file entirely.  In this case, we need to know what grade each student is getting based on their percentage score. You will find the table that defines the scores and the grades in A28:B32.

There are four pieces of information that you will need in order to build the VLOOKUP syntax. These are the four arguments of a VLOOKUP function:

- The value you want to look up, also called the Lookup_value. In our example, the lookup value will be the student’s percentage score in column P.

- The Table_array is the range (table) where the lookup values and the values you want returned by the function are located. In our example, this is the table of percentages and corresponding letter grades in the range A28:B32. The lookup value should always be in the first column in the table array for VLOOKUP to work correctly. For example, in our table_array the lookup value is in cell A28, so the range should start with A.

- The Col_index_num is the column number in the range that contains the value to return. In our example, when you specify A28:B32 as the Table_array range, you should count A as the first column (1), B as the second column (2), and so on. You will enter the appropriate column number in this box as 1, 2, or 3 and so on.

- In the Range_lookup, you can optionally specify TRUE if you want an approximate match or FALSE if you want an exact match of the return value. If you leave this blank, the default value will always be TRUE, or approximate match.

Let’s create the VLOOKUP to display the correct Letter Grade in column R.

1. Make sure that R5 is your active cell.

2. On the Formulas tab, in the Function Library, find the VLOOKUP function on the Lookup & Reference pull-down menu (see Figure 3.12).

Figure 3.12 VLOOKUP Function

3. Fill in the dialog box so that it looks like the image in Figure 3.13.

- Lookup_value – In this case, we will use the Percentage score. So, P5 for the first look up value.

- Table_array – This is the range that contains the value you want returned by the function. In this case, that range is A28:B32. Note that this range does NOT include the label in row 27; just the actual data.  The cell references for the Table_array need to be absolute – $A$28:$B$32. When we copy this function to the other cells, we do not want these cell references to change. It should always be $A$28:$B$32.

- Col_index_number – This is the column in the table array range that includes the information that we are looking up. In our case, the actual grades are in the 2nd column of the range. So, the column index will be 2.

- Range_lookup – In some cases, you will need something in the Range_lookup box. Since we are looking for an approximate match for the percentages, we want the default value of TRUE, so we do not need to enter anything for this argument.

4. While you are in the dialog box, be sure to look at all the helpful definitions that Excel offers.

5. When you have filled in the dialog box, press OK.

6. The calculation you will see in the formula bar is: =VLOOKUP(P5,$A$28:$B$32,2)

7. Use the fill handle to copy the function down through row 24. The results displayed should match Figure 3.14.

Figure 3.13 VLOOKUP completed dialog box

Figure 3.14 VLOOKUP Complete

Note: What if it didn’t work? What if you get a result different from the one predicted? In this case, either you have made a previous error, resulting in different % scores than this exercise anticipated, or you made a mistake entering your VLOOKUP function.

To make repairs in the function, make sure that R5 is your active cell. On the Formula bar, press the Insert Function button (see Figure 3.15). That will reopen the dialog box so you can make your repairs. Did you forget to make the cell references for the Table_array absolute? Did you use the wrong cell for the Lookup_value? Press OK when you are done and recopy the corrected function.

Figure 3.15 Insert Function

Error Messages

Sometimes Excel notices that you have made errors in your calculations before you do. In those cases Excel alerts you with some slightly mysterious error messages. A list of common error messages can be found in Table 3.1 below.

Table 3.1 – Common Error Messages

Message

What Went Wrong

#DIV/0!

You tried to divide a number by a zero (0) or an empty
cell.

#NAME

You used a cell range name in the formula, but the name isn’t
defined. Sometimes this error occurs because you type the name
incorrectly.

#N/A

The formula refers to an empty cell, so no data is available
for computing the formula. Sometimes people enter N/A in a cell as
a placeholder to signal the fact that data isn’t entered yet.
Revise the formula or enter a number or formula in the empty
cells.

#NULL

The formula refers to a cell range that Excel can’t understand.
Make sure that the range is entered correctly.

#NUM

An argument you use in your formula is invalid.

#REF

The cell or range of cells that the formula refers to aren’t
there.

#VALUE

The formula includes a function that was used incorrectly,
takes an invalid argument, or is misspelled. Make sure that the
function uses the right argument and is spelled correctly.

This table was copied from the internet. Look here for additional information.
http://www.dummies.com/software/microsoft-office/excel/how-to-detect-and-correct-formula-errors-in-excel-2016/

Date Functions

Very often dates and times are an important part of Excel data. Numbers that are correct today may not be accurate tomorrow. So, it is frequently useful to include dates and times on your spreadsheets.

These dates and times fall into two categories – ones that:

- Remain the same. For instance, if a spreadsheet includes data for May 15th, you don’t want the date to change each time you re-open the spreadsheet.

- Change to reflect the current date/time. When it is important to have the current date or time on a spreadsheet, you want Excel to update the information regularly.

Take a look at the list of Date and Time functions offered in the Function Library on the Formulas tab (see Figure 3.16).

Figure 3.16 Date & Time Functions

For our gradebook, we want the date and time to be displayed in A2, and it needs to update whenever the workbook file is opened.

1. Make A2 your active cell. Notice that A2 extends all the way from column A to Column R. Previously, someone has used the Merge & Center tool on this cell to make it match the title above.

2. On the Formulas tab, in the Function Library, select NOW from the Date & Time drop-down list and then click OK.

3. The result you will see in the formula bar is: =NOW(). The result you will see in A2 depends on the current date and time.The NOW function is a very handy function. It takes no arguments and is Volatile! That is not as alarming as it may seem. This just means that you don’t need to give it any more information to do its job and that your results will change frequently.You can update the date and time whenever you want – you don’t have to wait until you open the workbook again.

4. Make sure that A1 is your active cell and press the F9 function key (along the top of your keyboard.) The time will update.

Excel will update this field independently whenever you save and re-open the file, or print it. It may happen more frequently than that – depending on how you have set this up in your installation of Excel.

Another variation of the current date is the TODAY function. Let’s try that one next.

1. Make sure A2 is your active cell. Press Delete to remove the NOW function.

2. From the Date & Time drop-down list in the Function Library on the Formulas tab (see Figure 3.16), select TODAY and then click OK.

3. The result you will see in the formula bar is: =TODAY(). The result you will see in A2 depends on the current date.Since we haven’t asked for the time, the time you are seeing is likely 12:00. That is not very helpful so we need to change the format of the date.

4. On the Home tab, in the Number group, press the Number Format Launch button (see Figure3.17).

5. In the Format Cells dialog box, click the Number tab. Choose the Date category and select Wednesday, March 14, 2012 (this format is called Long Date).

6. The current day and date will display in A2.

Figure 3.17 Number Format Launcher

Keyboard Shortcuts

Sometimes you want the date or the time to show up in your spreadsheet, but you don’t want it to change. You can simply type in the date or time. Or, you can use shortcut keys.

- CTRL; (semi colon) will bring you the current date

- CTRL: (colon or CTRL SHIFT ; ) will bring you the current time.

Key Takeaways

- Functions don’t always have to be about arithmetic. Excel provides functions that will help you perform logical evaluations, look things up, and work with dates and times.

- Excel displays error messages when your formulas and functions are not constructed properly.

Attribution

3.2 Logical and Lookup Functions by Noreen Brown and Mary Schatz, Portland Community College, is licensed under CC BY 4.0

15

3.3 Conditional Formatting

Learning Objectives

- Use Conditional Formatting techniques to provide flexible highlighting, applying specified formatting only when certain conditions are met.  Techniques include:

- Data bars — to make it easy to visualize values in a range of cells.

- Cells Rules — to highlight values that match the requirements you specify.

You now have all the calculations you need in your CAS 170 Grades spreadsheet. There is a lot of data here. To make it easier to pick out the most important pieces of data, Excel provides Conditional Formatting. The best thing about Conditional Formatting is that it is flexible, applying specified formatting only when certain conditions are met.

1. Select the values in the Total Points column (O5:O24).

2. At the bottom of your selection, click on the Quick Analysis Tool. On the Formatting tab, select Data Bars (see Figure 3.18).

Excel places blue bars on top of your values; long blue bars for larger numbers, shorter ones for smaller numbers. This makes it easier to see how well each student did in the class – without having to look at the specific numbers.

Figure 3.18 Data Bars on the Quick Analysis tool

Another way to apply Data Bars is to:

- Select the range that needs data bars

- On the Home tab, in the Styles group, select Data Bars from the Conditional Formatting tool.

- From there you can select data bars of different colors and opacities (see Figure 3.19).

Figure 3.19 Data Bars on the Conditional Formatting tool

It is even more important to highlight the students who are failing in the class. To practice further with Conditional Formatting we will do that in two places, in the Percentages column and on the Letter Grade column.  To start with, we want any F letter grades to be formatted with a light red fill color and dark red text.

1. Select the Letter Grades (R5:R24).

2. On the Home tab, in the Styles group, select Highlight Cell Rules from the Conditional Formatting tool (see Figure 3.20).

3. Select Equal To

4. Fill out the Equal to dialog box so that cells that are equal to: F have Light Red Fill with Dark Red Text (see Figure 3.21).

Figure 3.20 Conditional Formatting Equal To

Figure 3.21 Conditional Formatting Equal To Dialog Box

Let’s try that one more time – to highlight those students who are passing the class.  This time we will use the Pass/Fail text in the Pass/Fail column.  If the text for a student is Pass we want the cell to be formatted with a yellow fill with dark yellow text.

1. Select the Pass/Fail grades (Q5:Q24).

2. On the Home tab, in the Styles group, select Highlight Cell Rules from the Conditional Formatting tool (see Figure 3.20).

3. Select Equal To

4. Fill out the Equal to dialog box so that cells that are equal to: Pass have Yellow Fill with Dark Yellow Text. (To find the Yellow Fill with Dark Yellow text option, click the the down arrow at the end of the last (with) box).

You do not have to use the default styles to make your data stand out. You can set any formatting you want. When you do, it is probably a good idea to include other styling in addition to color. Your spreadsheet might be printed in black and white. You would hate to lose your Conditional formatting.  Now we are going to use conditional formatting to display any Percentages that are less than 60% with red text formatted in bold and italic.

1. Select the Percentage grades (P5:P24).

2. On the Home tab, in the Styles group, select Highlight Cell Rules from the Conditional Formatting tool (see Figure 3.20).

3. Select Less Than

4. Fill out the Less Than dialog box so that cells that are less than .6 will be have conditional formatting. But, instead of using the default red text on a light red fill, press the down arrow at the end of that box and select Custom Format.

5. On the Font tab of the Format Cells dialog box, in the Font style box, select Bold Italic. In the Color box, select Red (see Figure 3.22).

6. Press OK.  Then press OK again.

Figure 3.22 Conditional Formatting Custom Format Cells Dialog box

Conditional Formatting is valuable in that it reflects the current data. It changes to reflect changes in the data. To test this, delete DeShea’s final exam score. (Select N5. Press Delete on your keyboard.) Suddenly, DeShae is failing the course and the Conditional Formatting reflects that. This is a little unfair to DeShae – who has worked so hard this quarter. Let’s give him back his grade. Press CTRL Z (Undo). His test score reappears and the Conditional formatting reflects that as well.

Making Changes

What if you have made a mistake with your Conditional Formatting? Or, you want to delete it altogether? You can use the Conditional Formatting Manage Rules tool. In our example, we want to remove the conditional formatting rule that formats the Pass text with yellow.  We are also going to modify the minimum passing percentage for the conditional formatting rule that is applied to the percentages.

1. On the Home Tab, in the Styles Group, select Manage Rules at the very bottom of the Conditional Formatting drop-down list.

2. Show formatting rules for: This Worksheet (see Figure 3.23).

3. We don’t really need to highlight the students who are passing the class, so select that rule in the Rules Manager and press the Delete Rule button.

Figure 3.23 Conditional Formatting Manage Rules

In a previous exercise (the IF function), we decided that students were failing if they got a percentage score of less than 70%, so the Conditional Formatting rule in the Percentage column needs repair.

4. Select the rule that reads Cell Value <0.6.

5. Select the Edit Rule button, and change the .6 to .7 (see Figure 3.24).

6. Click OK (or Apply) twice. Double check that your completed workbook matches Figure 3.25.

Figure 3.24 Conditional Formatting Edit Formatting Rule Dialog box

Figure 3.25 Completed Ch3 Gradebook

Setting the Print Area

Before you consider this workbook finished, you need to prepare it for printing.  The first thing you will do is set the Print Area so that the table of Letter Grades in A27:B32 does not print.

1. Select A1:R25. This is the only part of the worksheet that you want to have print.

2. On the Page Layout ribbon, click the Print Area button.  Choose Set Print Area from the menu.

Next you will preview the worksheet in Print Preview to check that the print area setting worked, as well as make sure it is printing on one page.

1. View the workbook in Print Preview.

2. Set the page orientation to Landscape.

3. Change the page scaling if needed so that the entire worksheet prints on one page.

4. Save and close the CH3 Gradebook workbook.

5. Compare your work with the self-check answer key (found in the Course Files link) and then submit the CH3 Gradebook workbook as directed by your instructor.

Attribution

3.3 Conditional Formatting by Noreen Brown and Mary Schatz, Portland Community College, is licensed under CC BY 4.0

16

3.4 Preparing to Print

Learning Objectives

- Locate and fix formatting consistency errors.

- Apply new formatting techniques.

- Use Print Titles to repeat rows and columns on each page of a multiple page worksheet.

- Control where page breaks occur in a multiple page worksheet.

In this section, we will review a worksheet for formatting consistency, as well as learn two new formatting techniques. This worksheet currently prints on four pages, so we will learn new page setup options to control how these pages print. A new data file will be used for this section.

Reviewing Formatting for Consistency

Download Data File: CH3 PTP Data

You have been given a workbook with data about the national parks in the western United States. Your coworker formatted the workbook and has asked you to review it for consistency. You also need to prepare it for printing. Figure 3.26 shows how the second page of the finished worksheet will appear in Print Preview.

Figure 3.26 Completed National Parks worksheet

Reviewing Formatting for Inconsistencies

The first thing you are going to do is review the worksheet for formatting inconsistencies.

1. Open the data file named CH3 PTP Data and use the File/Save As command to save it with the new name CH3 National Parks.

2. Scroll through the worksheet and locate the following formatting errors:

- The formatting of the Utah label does not match the other states.

- The Year Established values for Hawaii are not center aligned like the other years.

- The cells for the Nevada data should have the same green fill color as the other alternating states.

- The number of digits after the decimal place for the Size values is inconsistent. Also, these values should be formatted with Comma style to make them easier to read.

3. To fix these errors, complete the following steps:

- Merge & Center A34:A38. Change the font size to 16 and apply Bold format.

- Center align C28:C29.

- Apply the green fill color to A31:E31 (be sure to match the green fill color of the other states).

- Select E4:E43 and apply Comma Style. Use Increase Decimal and/or Decrease Decimal until one digit appears after the decimal place for all values.

4. While you’re fixing errors, proofread the sheet and correct any typos.

Fine-tuning Formatting

Now that you have fixed the inconsistencies in the formatting, you decide to apply some formatting techniques to make the worksheet look even better. You are going to start by vertically aligning the names of the states within the cells.

1. Select A4:A43 (the cells with the state labels).

2. Click the Home tab on the ribbon.

3. In the Alignment group, click the Middle Align button (see Figure 3.26). Notice that the names of the states are now centered between the top and bottom borders of the cells.

Figure 3.26 Alignment Group

The next new formatting skill is to change the label in E3 from Size (km2) to Size (km^2) with the 2 after km formatted with superscript.

1. Double-click on cell E3 to enter Edit mode

2. Select just the 2 (be careful not to select anything else).

3. On the ribbon (Home tab) click the dialog box launcher arrow in the Font group.

4. In the Effects section of the Format Cells dialog box, check the box for Superscript (see Figure 3-27). Click OK.

5. Save the CH3 National Parks file.

Figure 3.27 Font Tab in Format Cells Dialog Box

Repeating Column (and Row) Labels

Now that you have fixed the cell and text formatting, you are ready to review the worksheet in Print Preview. You will notice that the worksheet is printing on multiple pages, and you cannot tell what each column of data represents on some of the pages.

1. With the CH3 National Parks file still open, go to Backstage View by clicking the File tab on the ribbon. Select Print from the menu.

2. Click through each of the pages. The worksheet is currently printing on four pages, with the City and Sizes columns printing on separate pages from the rest of the data.

3. Change the Orientation from Portrait to Landscape. This fits all of the columns on one page.All of the columns are now on the same page, but the second and third pages have no column labels to identify what information is in each column. You are going to use Print Titles to repeat the first three rows of the worksheet on each of the printed pages. To set Print Titles you need to exit Print Preview.

4. Exit Backstage View then click the Page Layout tab on the ribbon.

5. Click the Print Titles button in the Page Setup group on the ribbon. The dialog box shown in Figure 3.28 should appear.

Figure 3.28 Print Titles

6. Click in the Rows to repeat at top: box. Be sure your insertion point is blinking in that box before moving on to the next step.

7. In the worksheet, select Rows 1 through 3. The text $1:$3 should now appear in the Rows to repeat at top: box.

8. Click OK.

You will not see a change to the worksheet in Normal view, so you will need to return to Print Preview. While looking in Print Preview, you will notice that the pages are breaking in inconvenient places.

1. Go to Print Preview and look at each of the pages. Notice that the first three rows are now repeated at the top of each page.

2. Exit Backstage View.

Skill Refresher

Creating Print Titles

1. Open the Page Setup dialog box and click the Sheet tab.

2. Click in the Rows to repeat at top: box or the Columns to repeat at left: box.

3. Click in the worksheet and select the row(s) or column(s) that you want to repeat on each page.

Inserting Page Breaks

Notice that the data for California is split between the first and second pages. You want all of the data for each state to be together on the same page, so you need to control the page breaks. You are going to start by inserting a page break before the California data to force it to start on the second page, then you will move the page break for the third page if needed. To make these changes you are going to work in Page Break Preview.

1. Click the View tab on the ribbon then click Page Break Preview in the Workbook Views Group. Your screen should look similar to Figure 3.29.

Figure 3.29 Page Break Preview

In Page Break Preview, automatic page breaks are displayed as dotted blue lines. Notice the dotted blue lines after rows 19 and 35. These lines indicate where Excel will start a new page. For this worksheet, you want the first page to break before the California data, so you are going to insert a manual page break.

1. Select cell A15. When inserting a page break, you select the cell below where you want the page break to appear.

2. Click the Page Layout tab on the ribbon.

3. Click the Breaks button in the Page Setup group (see Figure 3.30).

4. Select Insert Page Break from the menu. There is now a solid blue line after row 14, which indicates a manual page break that was inserted.

5. Go to Print Preview. Notice that the California data now starts on the second page.

Figure 3.30 Breaks Button on Page Layout tab

While looking at each page in Print Preview you decide that the third page should start with Montana. To make this change you are going to move the automatic page break that appears after Nevada.

1. Exit Backstage View. Switch back to Page Break Preview if needed.

2. Locate the next dotted blue line (automatic page break).

3. Put your pointer over the dotted blue line and it will switch to a vertical double-headed arrow. Click on the dotted blue line and drag it above Montana.

4. Release the mouse button when the line is above row 30 (above Montana). The line will now be a solid blue line, indicating a manual page break.

5. Go to Print Preview. The Montana data now appears at the top of the third page.

While evaluating the pages in Print Preview you decide that there is too much white space at the bottom of the pages. To fix this, you are going to center the contents vertically on the pages.

1. Click the Page Setup link at the bottom of the Settings section of Backstage View to open the Page Setup dialog box.

2. Click on the Margins tab.

3. In the Center on page section, check the box for Vertically then click OK.

4. Review each page in Print Preview to see the changes. Exit Backstage View.

Creating a Header and Footer using Page Layout View

Now that the worksheet is printing on three pages, with page breaks in appropriate places, you are ready to add a header with the current date and filename. You will also add a footer with the page number and the total number of pages that will appear as Page 1 of 3. You are going to edit the header and footer in Page Layout View.

1. Click the View tab on the ribbon and click the Page Layout button in the Workbook Views group.

2. The white space at the top of the worksheet should say Add header. Place the mouse pointer over the left section of the Header and click to activate that section.

3. Click the Header & Footer Tools Design tab on the ribbon.

4. Click the Current Date button in the Header & Footer Elements group (see Figure 3.31). Inserting the date this way will insert a field that will update every time the workbook is opened.

5. Click in the right section of the Header. Click the Filename button in the Header & Footer Elements group (see Figure 3.31). Inserting the filename this way will insert a field that will update if the filename is changed.

6. Click the Go to Footer button in the Navigation group of commands.

7. In the center section of the footer, type the word Page with a space after it.

8. Click the Page Number button in the Header & Footer Elements group (see Figure 3.31), then type a space after the &[Page] code that appears.

9. Type the word of with a space after it, then click the Number of Pages button in the Header & Footer Elements group (see Figure 3.31). The footer should match Figure 3.32.

10. Click anywhere on the worksheet to close the Footer editing.

11. Review the worksheet again in Print Preview. Pay careful attention to the page numbers in the footer to ensure they will print correctly, then exit Backstage View.

12. Save the CH3 National Parks workbook.

13. Compare your work with the self-check answer key (found in the Course Files link) and then submit the CH3 National Parks workbook as directed by your instructor.

Figure 3.31 Header & Footer Elements buttons

Figure 3.32 Completed Footer

Skill Refresher

Inserting Page Numbers

1. In Page Layout View, click in the section of the header or footer where you want the page number to appear.

2. Type the word Page, followed by a space, and then click the Page Number button in the Header & Footer Elements group on the Header & Footer Tools Design ribbon. This will create Page 1.

3. If desired, type a space after the &[Page] code then type the word of followed by a space. Then click the Number of Pages button. This will create Page 1 of 4.

Key Takeaways

- Always check the formatting of your worksheets for consistency.

- If a worksheet is printing on multiple pages, use Print Titles to repeat rows at the top and/or columns at the left of every page to make it easier to interpret the data.

- Insert manual page breaks as needed in Page Break Preview to control where a new page begins.

- Multiple page worksheets should include the page number in either the header or footer. Be sure to insert the Page Number element so that the correct page number will display on each page of the worksheet.

Attribution

“3.4 Preparing to Print” by Julie Romey, Portland Community College is licensed under CC BY 4.0

17

3.5 Chapter Practice

Household Budget

Download Data File: PR3 Data

Elijah and Kelly Williams are a recently married couple living in Portland, Oregon. Elijah works part time and attends the local community college. Kelly works as a marketing manager at a clothing company in North Portland. They are trying to decide if they can afford to move to a better apartment, one that is closer to work and school. They want to use Excel to examine their household budget. They have started their budget spreadsheet, but they need your help with it.

1. Open the file named PR3 Data and then save it as PR3 Williams.

2. Insert two new rows at the top of the worksheet.

3. Enter the following text:

A2            Category
B2            Item
C2            January
O2            Yearly Total (adjust column width as needed to fit this text)

1. Using the text in cell C2, use Autofill to fill in the months February through December in cells D2:N2. Adjust column widths as needed to fit the names of the months in these columns.

2. Bold and center align all of the headings in Row 2.

3. Type “Williams Family Budget” in A1. Merge & Center A1:O1. Make this text 22 point bold.

4. Next you need to complete the monthly values for some of the income and expense items. In the rows for Income #1, Income #2, Mortgage/Rent, Homeowners/Rent Insurance, Car Insurance, Car Payment, and Gym Fees/Memberships, copy the values for January to the cells for February through December.

5. Use the Totals tab in the Quick Analysis tool to add the SUM to Column O. Delete the formulas from O7, O17, O24, O32, and O38.

6. In C6: N6, use the SUM function to calculate the Total Income for each month.

7. Similar to step 6, use the SUM function to calculate the Total Home Expenses, Total Daily Living Expenses, Total Transportation Expenses, Total Entertainment Expenses, and Total Personal Expenses for each month.

8. Use the SUM function to calculate the Yearly Total Personal Expenses in cell O45.

9. Format the numerical data in Row 3 as Currency with no decimal places. Format all the total rows as Currency with no decimal places and with a top border.

10. Apply the Comma format with no decimal places in all the other rows.

11. In A47, type “Total Expenses”.

12. In C47, enter a formula that adds together all of the expense categorytotals for January. Copy the formula in C47 to D47:O47.

13. In A49, type “NET INCOME”. Bold and indent this text.

14. In C49, enter a formula that calculates the difference between Total Income and Total Expenses (=Total Income-Total Expenses) for January. Copy this formula to D49:O49.

15. Format the data in Rows 47 and 49 as Currency with no decimal places. Bold O47 and O49. Add a Top and Double Bottom Border to the data in Row 49.

16. Select C49:N49. Use the Quick Analysis tool to add data bars to this data.

17. In B50, type “New Apartment?”. Enter an IF statement in C50 that displays the word “No” if the amount in C49 is less than or equal to zero and “Maybe” if the amount is greater than zero. Copy C50 to D50:N50.

18. Check to see if your IF statement worked correctly in row 50. If the cells say “No” when the data bar in the cell above it is red and “Maybe” when the data bar in the cell above it is blue, your IF statement is correct.

19. Review the worksheet in Print Preview. Make any changes needed to make the worksheet print on one page with landscape orientation.

20. Save the PR3 Williams workbook.

21. Compare your work with the self-check answer key and then submit the PR3 Williams workbook as directed by your instructor.

Attribution

“3.5 Chapter Practice” by Diane Shingledecker, Portland Community College is licensed under CC BY 4.0, It is adapted from Personal Budget Project by Matt Goff, CC BY-SA 4.0.

18

3.6 Chapter Scored

AstroCoffee Company

Download Data File: SC3 Data

AstroCoffee: Cynthia McHenry owns a coffee supply company named AstroCoffee. She needs some help writing the formulas for the order form she uses to invoice customers. You will need to write the formulas for all of the calculations on the form. Some of the more complex parts are determining if the customer will get a discount (based on the customer status) as well as the shipping charge (orders over $200 get free shipping). You will use IF functions for both of those calculations.

1. Open the SC3 Data workbook and save the workbook as SC3 AstroCoffee.

2. Enter the following order information:
Order #: 45676
Order Date: use a function that displays the current date

3. Enter the following Billing Information:
Edwina Copeland
4270 Heron Way Portland, OR 97225
503-779-1873
edwina.copeland@hmail.com

4. For the Shipping Information, create formulas using cell references to display the corresponding information from the Billing Information section. For example, the Customer cell will display the name of the customer in cell C11.

5. In the range B19:E22, enter the following item orders:

Item #

Description

Qty

Unit Price

K56

Dark Mocha K-Cups (12 pack)

1

10.99

G03

Decaf Dark Roast – Ground (1 lb.)

3

12.99

B07

Dark Roast – Whole Bean (1 lb.)

2

13.99

K52

Chai Latte K-Cups (12 pack)

3

12.99

6. In cell F19, enter an IF function that tests whether the order quantity in cell D19 is greater than 0 (zero). lf it is, return the value of the Qty (in D19) multiplied by the Unit Price (in E19); otherwise, return no text by entering “”.

7. Copy/fill this formula into the other cells in the range F19:F25. Hint: be sure to copy the formula to all of the Item Total cells, even if it is a blank row. You want the worksheet to be prepared for orders with more items in the future.

8. In cell F26, calculate the sum of all of the Item Total cells.

9. In cell F27, use an IF function to calculate the discount amount for this order based on the customer’s status (which is found in F16). If the customer’s status is Preferred, the discount amount will be the Order Subtotal times the discount percentage found in cell B29; otherwise the discount amount will be 0 (zero). Hint: You will need to use a formula for the Value if True argument.

10. Calculate the Discounted Total for this order in cell F28. Hint: Use a simple subtraction formula.

11. In cell F29, use an IF function to display the correct Shipping Charge, based on the amount of the Discounted Total. If the Discounted Total is greater than or equal to the Free Shipping Minimum found in cell B28, the Shipping Charge is 0 (zero); otherwise, the Shipping Charge is 5% of the Discounted Total. Hint: You will need to use a formula for the Value if False to calculate what 5% of the Discounted Total will be.

12. Calculate the Invoice Total in cell F31. Hint: This will be the total of the Discounted Total and the Shipping Charge.

13. Review the worksheet in Print Preview. Make any changes needed to make the worksheet print on one page.

14. Save the SC3 AstroCoffee workbook.

15. Submit the SC3 Astro Coffee workbook as directed by your instructor.

Attribution

“3.6 Chapter Scored” by Noreen Brown and Mary Schatz, Portland Community College is licensed under CC BY 4.0

IV

Chapter 4 – Presenting Data with Charts

One of the most important things to consider when using charts in Excel is that they are intended to be used for communicating an idea to an audience. Your audience can be reading your charts in a written document or listening to you in a live presentation. In fact, Excel charts are often imported or pasted into Word documents or PowerPoint slides, which serve this very purpose of communicating ideas to an audience. Although there are no rules set in stone for using specific charts for certain data types, some chart types are designed to communicate certain messages better than others. This chapter explores numerous charts that can be used for a variety of purposes. In addition, we will examine formatting charts and using those charts in Word and PowerPoint documents.

Attribution

Adapted by Noreen Brown 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.

19

4.1 Choosing a Chart Type

LEARNING OBJECTIVES

1. Construct a line chart to show a time series trend.

2. Learn how to adjust the Y axis scale.

3. Construct a line chart to present a comparison of two trends.

4. Learn how to use a column chart to show a frequency distribution.

5. Create a separate chart sheet for a chart embedded in a worksheet.

6. Construct a column chart that compares two frequency distributions.

7. Learn how to use a pie chart to show the percent of total for a data set.

8. Construct a stacked column chart to show how a percent of total changes over time.

This section reviews the most commonly used Excel chart types. To demonstrate the variety of chart types available in Excel, it is necessary to use a variety of data sets. This is necessary not only to demonstrate the construction of charts but also to explain how to choose the right type of chart given your data and the idea you intend to communicate.

Choosing a Chart Type

Before we begin, let’s review a few key points you need to consider before creating any chart in Excel.

1. The first is identifying your idea or message. It is important to keep in mind that the primary purpose of a chart is to present quantitative information to an audience. Therefore, you must first decide what message or idea you wish to present. This is critical in helping you select specific data from a worksheet that will be used in a chart. Throughout this chapter, we will reinforce the intended message first before creating each chart.

2. The second key point is selecting the right chart type. The chart type you select will depend on the data you have and the message you intend to communicate.

3. The third key point is identifying the values that should appear on the X and Y axes. One of the ways to identify which values belong on the X and Y axes is to sketch the chart on paper first. If you can visualize what your chart is supposed to look like, you will have an easier time selecting information correctly and using Excel to construct an effective chart that accurately communicates your message. Table 4.1 “Key Steps Before Constructing an Excel Chart” provides a brief summary of these points.

Integrity Check

Carefully Select Data When Creating a Chart

Just because you have data in a worksheet does not mean it must all be placed onto a chart. When creating a chart, it is common for only specific data points to be used. To determine what data should be used when creating a chart, you must first identify the message or idea that you want to communicate to an audience.

Table 4.1 Key Steps before Constructing an Excel Chart

Step

Description

Define your message.

Identify the main idea you are trying to communicate to an audience. If there is no main point or important message that can be revealed by a chart, you might want to question the necessity of creating a chart.

Identify the data you need.

Once you have a clear message, identify the data on a worksheet that you will need to construct a chart. In some cases, you may need to create formulas or consolidate items into broader categories.

Select a chart type.

The type of chart you select will depend on the message you are communicating and the data you are using.

Identify the values for the X and Y axes.

After you have selected a chart type, you may find that drawing a sketch is helpful in identifying which values should be on the X and Y axes. In Excel, the axes are:
The “category” axis. Usually the horizontal axis – where the labels are found

The “value” axis. Usually the vertical axis – where the numbers are found.

Time Series Trend: Line Chart 1

The first chart we will demonstrate is a line chart. Figure 4.1 shows part of the data that will be used to create two line charts. This chart will show the trend of the NASDAQ stock index.

Read more: http://www.investopedia.com/terms/n/nasdaq.asp

This chart will be used to communicate a simple message: to show how the index has performed over a two-year period. We can use this chart in a presentation to show whether stock prices have been increasing, decreasing, or remaining constant over the designated period of time.

Figure 4.1 Stock Trends

Before we create the line chart, it is important to identify why it is an appropriate chart type given the message we wish to communicate and the data we have. When presenting the trend for any data over a designated period of time, the most commonly used chart types are the line chart and the column chart. With the column chart, you are limited to a certain number of bars or data points. As you increase the number of bars on a column chart, it becomes increasingly difficult to read. As you scroll through the data on the worksheet shown in Figure 4.1 you will see that there are 24 points of data used to construct the chart. This is generally too many data points to put on a column chart, which is why we are using a line chart. Our line chart will show the volume of sales for the NASDAQ on the Y axis and the Month number on the X axis. The following steps explain how to construct this chart:

Download Data file: CH4 Data

1. Open data file CH4 Data and save a file to your computer as CH4 Charting.

2. Navigate to the Stock Trend worksheet.

3. Highlight the range B4:C28 on the Stock Trend worksheet. (Note – you have selected a label in the first row and more labels in column B. Watch where they show up in your completed chart.)

4. Click the Insert tab of the ribbon.

5. Click the Line button in the Charts group of commands. Click the first option from the list, which is a basic 2D Line Chart (see Figure 4.2).

Figure 4.2 Selecting the Basic Line Chart

This adds, or embeds, the line chart to the worksheet, as shown in Figure 4.3

Why?

Line Chart vs. Column Chart

We can use both a line chart and a column chart to illustrate a trend over time. However, a line chart is far more effective when there are many periods of time being measured. For example, if we are measuring fifty-two weeks, a column chart would require fifty-two bars. A general rule of thumb is to use a column chart when twenty bars or less are required. A column chart becomes difficult to read as the number of bars exceeds twenty.

Figure 4.3 shows the embedded line chart in the Stock Trend worksheet. Do you see where your labels showed up on the chart?

Notice that additional tabs, or contextual tabs, are added to the ribbon. We will demonstrate the commands in these tabs throughout this chapter. These tabs appear only when the chart is activated.

Note: Excel 2010 uses three contextual tabs for charts. Later versions use only two. Each has all the same tools. They are just organized a little differently.

Figure 4.3 Embedded Line Chart in the Stock Trend Worksheet

As shown in Figure 4.3, the embedded chart is not placed in an ideal location on the worksheet since it is covering several cell locations that contain data. The following steps demonstrate common adjustments that are made when working with embedded charts:

1. Moving a chart: Click and drag the upper left corner of the chart to the corner of cell B30.

Note: Keep an eye on your pointer. It will change into when you are in the right place to move your chart.

2. Resizing a chart: Place the mouse pointer over the right upper corner sizing handle, hold down the ALT key on your keyboard, and click and drag the chart so it “snaps” to the right side of Column I.

Note: keep an eye on your pointer. It will change into when you are in the right place to resize your chart

3. Repeat step 2 to resize the chart so the top “snaps” to the top of Row 30, the bottom “snaps” to the bottom of Row 45, and the left side “snaps” to the left side of Column B. Make sure the right side of the chart snaps to the line between column I and J.

4. Adjusting the chart title: Click the chart title once. Then click in front of the first letter. You should see a blinking cursor in front of the letter. This allows you to modify the title of the chart.

5. Type the following in front of the first letter in the chart title: May 2014-2016 Trend for NASDAQ Sales.

6. Click anywhere outside of the chart to deactivate it.

7. Save your work.

Figure 4.4 shows the line chart after it is moved and resized. You can also see that the title of the chart has been edited to read May 2014-2016 Trend for NASDAQ Sales Volume. Notice that the sizing handles do not appear around the perimeter of the chart. This is because the chart has been deactivated. To activate the chart, click anywhere inside the chart perimeter.

Figure 4.4 Line Chart Moved and Resized

Integrity Check

When using line charts in Excel, keep in mind that anything placed on the X axis is considered a descriptive label, not a numeric value. This is an example of a category axis. This is important because there will never be a change in the spacing of any items placed on the X axis of a line chart. If you need to create a chart using numeric data on the category axis, you will have to modify the chart. We will do that later in the chapter.

Skill Refresher

Inserting a Line Chart

1. Highlight a range of cells that contain data that will be used to create the chart. Be sure to include labels in your selection.

2. Click the Insert tab of the ribbon.

3. Click the Line button in the Charts group.

4. Select a format option from the Line Chart drop-down menu.

Adjusting the Y Axis Scale

After creating an Excel chart, you may find it necessary to adjust the scale of the Y axis. Excel automatically sets the maximum value for the Y axis based on the data used to create the chart. The minimum value is usually set to zero. That is usually a good thing. However, depending on the data you are using to create the chart, setting the minimum value to zero can substantially minimize the graphical presentation of a trend. For example, the trend shown in Figure 4.4 appears to be increasing slightly in recent months. The presentation of this trend can be improved if the minimum value started at 500,000. The following steps explain how to make this adjustment to the Y axis:

1. Click anywhere on the Y (value or vertical) axis on the May 2014-2016 Trend for NASDAQ Sales Volume line chart (Stock Trend worksheet).

2. Right Click and select Format Axis. The Format Axis Pane should appear, as shown in Figure 4.5.

Note: If you do not see “Format Axis . . . on your menu, you have not right clicked in the correct spot. Press “Escape” to turn the menu off and try again

Figure 4.5 Format Axis Pane

1. In the Format Axis Pane, click the input box for the “Minimum” axis option and delete the zero. Then type the number 500000 and hit Enter. As soon as you make this change, the Y axis on the chart adjusts.

2. Click the X in the upper right corner of the Format Axis pane to close it.

3. Save your work.

Figure 4.6 shows the change in the presentation of the trend line. Notice that with the Y axis starting at 500,000, the trend for the NASDAQ is more pronounced. This adjustment makes it easier for the audience to see the magnitude of the trend.

Figure 4.6 Adjusted Y Axis for the S&P 500 Chart

Skill Refresher

Adjusting the Y Axis Scale

1. Click anywhere along the Y axis to activate it.

2. Right Click.
(Note, you can also select the Format tab in the Chart Tools section of the ribbon.)

3. Select Format Axis . . .

4. In the Format Axis pane, make your changes to the Axis Options.

5. Click in the input box next to the desired axis option and then type the new scale value.

6. Click the Close button at the top right of the Format Axis pane to close it.

Trend Comparisons: Line Chart 2

We will now create a second line chart using the data in the Stock Trend worksheet. The purpose of this chart is to compare two trends: the change in volume for the NASDAQ and the change in the Closing price.

Before creating the chart to compare the NASDAQ volume and sales price, it is important to review the data in the range B4:D28 on the Stock Trend worksheet. We cannot use the volume of sales and the closing price because the values are not comparable. That is, the closing price is in a range of $45.00 to $115.00, but the data for the volume of Sales is in a range of 684,000 to 3,711,000. If we used these values – without making changes to the chart — we would not be able to see the closing price at all.

The construction of this second line chart will be similar to the first line chart. The X axis will be the months in the range B4:D28.

1. Highlight the range B4:D28 on the Stock Trend worksheet.

2. Click the Insert tab of the ribbon.

3. Click the Line button in the Charts group of commands.

4. Click the first option from the list, which is a basic line chart.

Figure 4.6.5 shows the appearance of the line chart comparing both the volume and the closing price before it is moved and resized. Notice that the line for the closing price (Close) appears as a straight line at the bottom of the chart.  Also, the chart is covering the data again, and the title needs to be changed.

Figure 4.6 Trend Comparison Line Chart

1. Move the chart so the upper left corner is in the middle of cell M3.

Note: The line representing the closing values is flat along the bottom of the chart. This is hard to see and not very useful as is. Fear not. We will fix that.

2. Resize the chart, using the resizing handles and the ALT key, so the left side is locked to the left side of Column M, the right side is locked to the right side of Column U, the top is locked to the top of Row 3, and the bottom is locked to the bottom of Row 17.

3. Click in the text box that says “Chart Title.” Delete the text and replace it with the following: 24 Month Trend Comparison.

Good. But, we still cannot really see the Closing Price data. It is the flat red line at the very bottom of the chart.

1. Right click the red line across the bottom of the chart that represents the Closing Price.

2. On the menu, select Format Data Series. This will open the Format Data Series pane.

3. In the Series Options, select Secondary Axis.

Figure 4.7 Adding a Secondary Axis

Better! But, it would be nice to be able to see that the values on the right represent prices.

1. Right click the Secondary Vertical Axis. (The vertical axis on the right that goes from 0 to 140.)

2. From the menu, select Format Axis.

3. In Axis Options, select Number. (You may have to scroll down to see it.)

4. Use the Symbol list box to add the $.

5. Press the Close button to close the Format Axis pane.

6. Save your work.

Figure 4.8 Modifying the Secondary Axis

Figure 4.9 Final Comparison Line Chart

“Instant” Chart – F11

On the Stock Trend worksheet:

1. Select A4:A28.

2. Hold down the Ctrl key and select D4:D28. Figure 4.10 shows what that will look like.

Figure 4.10 Range Selection

3. Press F11. (The F11 function key is on the top row of the keyboard.)  If the factory default settings haven’t been changed, Excel will create a column chart and place it on a separate chart sheet. (See Figure 4.11).

4. Change the name of the chart sheet by double-clicking the worksheet name Chart1. Type Closing Prices as the new name and hit Enter.

5. Save your work.

Figure 4.11 Instant Chart

Frequency Distribution: Column Chart 1

A column chart is commonly used to show trends over time, as long as the data are limited to approximately twenty points or less. A common use for column charts is frequency distributions. A frequency distribution shows the number of occurrences by established categories. For example, a common frequency distribution used in most academic institutions is a grade distribution. A grade distribution shows the number of students that achieve each level of a typical grading scale (A, A−, B+, B, etc.). The Grade Distribution worksheet contains final grades for some hypothetical Excel classes. To show the grade frequency distribution for all the Excel classes in that year, the numbers of students appear on the Y axis and the grade categories appear on the X axis. The number of students for this chart is in Column C. The labels for grades are in Column A. The following steps explain how to create this chart:

1. Select the Grade Distribution worksheet.

2. Change the years in Row3 to the current academic term and year.

3. Highlight the range A3:A8 on the Grade Distribution worksheet. Column A shows the grade categories.

4. Hold down the Crtl key.

5. Without letting go of the Ctrl key, select C3:C8

6. Click the Column button in the Charts group section on the Insert tab of the ribbon. Select the first option in the 2-D Column section, which is the Clustered Column format.

7. Click and drag the chart so the upper left corner is in the middle of cell H2.

8. Resize the chart so the left side is locked to the left side of Column H, the right side is locked to the right side of Column O, the top is locked to the top of Row 2, and the bottom is locked to the bottom of Row 16.

9. If Excel displays a legend, delete it by clicking the legend one time and pressing the DELETE key on the keyboard. Since the chart presents only one data series, the legend is not necessary.

10. Add the text Final Grades for to the chart title. The chart title should now be Final Grades for All Excel Classes 2016/2017 (or whichever academic year you are using).

11. Click any cell location on the Grade Distribution worksheet to deactivate the chart.

12. Save your work.

Figure 4.12 shows the completed grade frequency distribution chart. By looking at the chart, you can immediately see that the greatest number of students earned a final grade in the B+ to B− range.

Figure 4.12 Grade Frequency Distribution Chart

Why?

Column Chart vs. Bar Chart

When using charts to show frequency distributions, the difference between a column chart and a bar chart is really a matter of preference. Both are very effective in showing frequency distributions. However, if you are showing a trend over a period of time, a column chart is preferred over a bar chart. This is because a period of time is typically shown horizontally, with the oldest date on the far left and the newest date on the far right. Therefore, the descriptive categories for the chart would have to fall on the horizontal – or category axis, which is the configuration of a column chart. On a bar chart, the descriptive categories are displayed on the vertical axis.

Creating a Chart Sheet

The charts we have created up to this point have been added to, or embedded in, an existing worksheet (with the exception of the Instant Chart we created using F11). Charts can also be placed in a dedicated worksheet called a chart sheet. It is called a chart sheet because it can only contain an Excel chart. Chart sheets are useful if you need to create several charts using the data in a single worksheet. If you embed several charts in one worksheet, it can be cumbersome to navigate and browse through the charts. It is easier to browse through charts when they are moved to a chart sheet because a separate sheet tab is added to the workbook for each chart. The following steps explain how to move the grade frequency distribution chart to a dedicated chart sheet:

1. Click anywhere on the Final Grades for All Excel Classes chart on the Grade Distribution worksheet.

2. Right click on the chart. Select Move Chart . . . This opens the Move Chart Dialog box.

3. Click the New sheet option on the Move Chart dialog box. (The top option.)

4. The entry in the input box for assigning a name to the chart sheet tab should automatically be highlighted once you click the New sheet option. Type All Excel Classes. This replaces the generic name in the input box (see Figure 4.13).

5. Click the OK button at the bottom of the Move Chart dialog box. This adds a new chart sheet to the workbook with the name All Excel Classes.

6. Save your work.

Figure 4.13 Moving a Chart to a Chart Sheet

Figure 4.14 shows the Final Grades for the all the Excel Classes column chart is in a separate chart sheet. Notice the new worksheet tab added to the workbook matches the New sheet name entered into the Move Chart dialog box. Since the chart is moved to a separate chart sheet, it no longer is displayed in the Grade Distribution worksheet.

Figure 4.14 Chart Sheet Added to the Workbook

Frequency Comparison: Column Chart 2

We will create a second column chart to show a comparison between two frequency distributions. Column B on the Grade Distribution worksheet contains data showing the number of students who received grades within each category for the Spring Quarter. We will use a column chart to compare the grade distribution for Spring (Column B) with the overall grade distribution for the whole year (Column C).

However, since the number of students in the term is significantly different from the total number of students in the year, we must calculate percentages in order to make an effective comparison. The following steps explain how to calculate the percentages:

1. Highlight the range B9:C9 on the Grade Distribution worksheet.

2. Click the AutoSum button in the Editing group of commands on the Home tab of the ribbon. This automatically adds SUM functions that sum the values in the range B4:B8 and C4:C8.

3. Activate cell E4 on the Grade Distribution worksheet.

4. Enter a formula that divides the value in cell B4 by the total in cell B9. Add an absolute reference to cell B9 in the formula =B4/$B$9.

5. Copy the formula in cell E4 and paste it into the range E5:E8 using the Paste command.
Or, use the Fill Handle to copy the calculation in E4 all the way down to E8.

6. Activate cell F4 on the Grade Distribution worksheet.

7. Enter a formula that divides the value in cell C4 by the total in cell C9. Add an absolute reference to cell C9 in the formula =C4/$C$9.

8. Copy the formula in cell F4 and paste it into the range F5:F8 using the Paste command.
Or, use the Fill Handle to copy the calculation in F4 all the way down to F8.

Figure 4.15 Completed Grade Distribution Percentages

Figure 4.15 shows the completed percentages added to the Grade Distribution worksheet.

The column chart we are going to create uses the grade categories in the range A4:A8 on the X axis and the percentages in the range E4:F8 on the Y axis. This chart uses data that is not in a contiguous range, so we need to use the Ctrl key to select the ranges of cells.

1. Select A3:A8, hold down the Ctrl key and select E3:F8.

2. Click the Insert tab of the ribbon.

3. Click the Column button in the Charts group of commands. Select the first option from the drop-down list of chart formats, which is the Clustered Column.

4. Click and drag the chart so the upper left corner is in the middle of cell H2.

5. Resize the chart so the left side is locked to the left side of Column H, the right side is locked to the right side of Column N, the top is locked to the top of Row 2, and the bottom is locked to the bottom of Row 16.

6. Change the chart title to Grade Distribution Comparison. If you do not have a chart title, you can add one. On the Design tab, select Add Chart Element. Find the Chart Title. Select the Above Chart option from the drop-down list.

7. Save your work.

Figure 4.16 Completed Data Series for the Class Grade Distribution

Figure 4.17 shows the final appearance of the column chart. The column chart is an appropriate type for this data because there are fewer than twenty data points and we can easily see the comparison for each category. An audience can quickly see that the class issued fewer As compared to the college. However, the class had more Bs and Cs compared with the college population.

Figure 4.17 Completed Grade Distribution Column Chart

Integrity Check

Too Many Bars on a Column Chart?
Although there is no specific limit for the number of bars you should use on a column chart, a general rule of thumb is twenty bars or less. Figure 4.18 contains a total of thirty-two bars. This is considered a poor use of a column chart because it is difficult to identify meaningful trends or comparisons. The data used to create this chart might be better used in two or three different column charts, each with a distinct idea or message.

Figure 4.18 Poor Use of a Column Chart

Percent of Total: Pie Chart

The next chart we will demonstrate is a pie chart. A pie chart is used to show a percent of total for a data set at a specific point in time. The data we will use to demonstrate a pie chart is related to enrollment data for Portland Area Community Colleges for Fall of 2014. You will find that data on the Enrollment Statistics sheet.

1. Highlight the range A2:B6 on the Enrollment Statistics worksheet.

2. Click the Insert tab of the ribbon.

3. Click the Pie button in the Charts group of commands.

4. Select the first “2-D Pie” option from the drop-down list of options.

5. To make the “slices” stand out better, “explode” the pie chart.

- Click and hold the mouse button down in any of the slices of the pie.

- Note that you have selection handles on all of the pie slices.

- Without letting go of your mouse button; drag one of the slices away from the center.

- All of the slices “explode” out from the center.

Note: if you let go of the mouse button before dragging, you may only get one slice to move when you drag it out from the center. This can be another option for displaying your data.  Use the Undo button to undo this if you want to try again.

1. Click off the slices and into the white canvas to deselect the pie and select the entire chart.

2. Click and drag the pie chart so the upper left corner is in the middle of cell E2.

3. Resize the pie chart so the left side is locked to the left side of Column E, the right side is locked to the right side of Column L, the top is locked to the top of Row 2, and the bottom is locked to the bottom of Row 10 (see Figure 4.19).

Figure 4.19 Pie Chart Moved and Resized

1. Click the chart legend once and press the DELETE key on your keyboard. A pie chart typically shows labels next to each slice. Therefore, the legend is not needed.

2. Right click any of the slices in the pie chart, and select Add Data Labels from the list. This will add the values for each of the slices in the pie.

3. Now, you can right click one of the numbers and select Format Data Labels from the list. This will open the Format Data Labels pane on the right.

4. Check the boxes for Category Name and Percentage in the Label Options section in the Format Data Labels pane.  This will add the Race/ethnicity labels as well as the percentage data to the pie chart.

5. Uncheck the box next to the Value box. This will remove the numbers from the pie chart (see Figure 4.20).

6. Click the Close button at the top of the Format Data Labels pane.

7. Select the data labels again (if needed). Click the Home tab of the ribbon and then click the Bold button. This will bold the data labels on the pie chart.

8. Save your work.

Figure 4.20 Final Settings in the Format Data Labels Pane

Although there are no specific limits for the number of categories you can use on a pie chart, a good rule of thumb is ten or less. As the number of categories exceeds ten, it becomes more difficult to identify key categories that make up the majority of the total.

Figure 4.21 Final Enrollment Statistics Pie Chart

Skill Refresher

Inserting a Pie Chart

1. Highlight a range of cells that contain the data you will use to create the chart.

2. Click the Insert tab of the ribbon.

3. Click the Pie button in the Charts group.

4. Select a format option from the Pie Chart drop-down menu.

Percent of Total: Stacked Column Chart

The last chart type we will demonstrate is the stacked column chart. We use a stacked column chart to show a percent of a total . For example, the data on the Enrollment Statistics worksheet shows student enrollment by race for several colleges. We would like to see all of the data on all of the colleges.

1. Highlight the range A2:D6 on the Enrollment Statistics worksheet.

2. Click the Insert tab of the ribbon.

3. Click the Column button in the Charts group of commands. Select the 100% Stacked Column format option from 2-D Column section in the drop-down list (see Figure 4.22).

Figure 4.22 Selecting the 100% Stacked Column Chart

Figure 4.23 shows the column chart that is created after selecting the 100% Stacked Column format option. As mentioned, the goal of this chart is to show the enrollment of students by race. However, notice that Excel places the racial categories on the X axis. It would be more useful if the different colleges were there instead.

Figure 4.23 Initial Construction of the 100% Stacked Column Chart

The reason that Excel organized the data this way is that there are more Race/ethnicity categories (data in column A) than there are colleges (data in row 2). Not a bad guess. But, not what we wanted in this case.

The remaining steps explain how to correct this problem and complete the chart:

1. Click the Switch Row/Column button in the Design tab on the Chart Tools section of the ribbon. This reverses the legend and current X axis categories.

Figure 4.24 Switch Row/Column.

2. Click and drag the chart so the upper left corner is in the middle of cell E12.

3. Resize the chart so the left side is locked to the left side of Column E, the right side is locked to the right side of Column N, the top is locked to the top of Row 12, and the bottom is locked to the bottom of Row 30.

4. Click the legend one time and press the DELETE key on your keyboard.

5. Add a Data Table. This is another way of displaying a legend for a column chart along with the numerical values that make up each component.

- In earlier versions of Excel, find the Labels group of commands and select the Show Data Table with Legend Keys option from the drop-down menu.

- In Excel 2016, find the Add Chart Element tool on the Design tab, select Data Table With Legend Keys

6. Change the Chart Title to Enrollment by Race.

- If there is no chart title, you will need to add one using the Add Chart Element tool on the Design tab.

7. Save your work.

Figure 4.25 shows the final stacked column chart. Notice the similarities and differences in the enrollment at the local community colleges.

Figure 4.25 Final 100% Stacked Column Chart

Skill Refresher:

Inserting a Stacked Column Chart

1. Highlight a range of cells that contain data that will be used to create the chart.

2. Click the Insert tab of the ribbon.

3. Click the Column button in the Charts group.

4. Select the Stacked Column format option from the Column Chart drop-down menu to show the values of each category on the Y axis. Select the 100% Stacked Column option to show the percent of total for each category on the Y axis.

Key Takeaways

- Identifying the message you wish to convey to an audience is a critical first step in creating an Excel chart.

- Both a column chart and a line chart can be used to present a trend over a period of time. However, a line chart is preferred over a column chart when presenting data over long periods of time.

- The number of bars on a column chart should be limited to approximately twenty bars or less.

- When creating a chart to compare trends, the values for each data series must be within a reasonable range. If there is a wide variance between the values in the two data series (two times or more), the percent change should be calculated with respect to the first data point for each series.

- When working with frequency distributions, the use of a column chart or a bar chart is a matter of preference. However, a column chart is preferred when working with a trend over a period of time.

- A pie chart is used to present the percent of total for a data set.

- A stacked column chart is used to show how a percent total changes over time.

Attribution

Adapted by Noreen Brown 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.

20

4.2 Formatting Charts

Learning Objectives

1. Apply formatting commands to the X and Y axes.

2. Enhance the visual appearance of the chart title and chart legend by using various formatting techniques.

3. Assign titles to the X and Y axes that clarify labels and numeric values for the reader.

4. Apply labels and formatting techniques to the data series in the plot area of a chart.

5. Apply formatting commands to the chart area and the plot area of a chart.

6. Employ series lines and annotations to enhance trends and provide additional information on a chart.

You can use a variety of formatting techniques to enhance the appearance of a chart once you have created it. Formatting commands are applied to a chart for the same reason they are applied to a worksheet: they make the chart easier to read. However, formatting techniques also help you qualify and explain the data in a chart. For example, you can add footnotes explaining the data source as well as notes that clarify the type of numbers being presented (i.e., if the numbers in a chart are truncated, you can state whether they are in thousands, millions, etc.). These notes are also helpful in answering questions if you are using charts in a live presentation. We will demonstrate these formatting techniques using the column chart and stacked column chart from the previous section.

X and Y Axis Formats

There are numerous formatting commands we can apply to the X and Y axes of a chart. Although adjusting the font size, style, and color are common, many more options are available through the Format Axis pane. The following steps demonstrate a few of these formatting techniques on the Grade Distribution Comparison chart:

1. Switch to the Grade Distribution worksheet and click anywhere along the X axis (horizontal axis) of the Grade Distribution Comparison chart.

2. Right click and select Font.

3. Change the font to Arial, the Font Style to Bold, and the Size to 11 (see Figure 4.26).

Figure 4.26 Font Dialog Box

4. Click anywhere along the Y axis to activate it and repeat steps 2 and 3.

5. Click on the chart title and repeat steps 2 and 3, but set the Size to 14.

6. The final appearance of the axes is shown in Figure 4.27 Formatted X & Y Axes.

Figure 4.27 Formatted X and Y Axes

Next we want to make some changes to the percentage numbers on the Y (vertical) axis.

1. Right click the vertical (value) axis. Select Format Axis. This opens the Format Axis pane.

2. Click Number from the list of options. The commands in this section of the Format Axis pane are used to format numbers that appear on the selected axis of the chart.

3. Click in the Decimal places input box and change the value to 1.

4. Select Axis Options. Change the Minimum Bound to .05 to make the differences in the columns more dramatic. The Format Axis pane should match Figure 4.28.

5. Click the Close button at the top of the Format Axis pane.

6. Save your work.

Figure 4.28 Format Axis Pane changes

Note: Experiment!  You can also change font styling using shortcut keys and the buttons on the Home tab.

Skill Refresher

Formatting the X and Y Axes

1. Click anywhere along the X or Y axis to activate it.

2. Click either the Home tab or Design tab of the ribbon.

3. Select any of the available formatting commands in these tabs.

Skill Refresher

X and Y Axis Number Formats

1. Click anywhere along the X or Y axis to activate it.

2. Click the Layout tab in the Chart Tools section of the ribbon.

3. Click the Format Selection button in the Current Selection group of commands.

4. Click Number from the list of options on the left side of the Format Axis dialog box.

5. Select a number format and set decimal places on the right side of the Format Axis dialog box.

6. Click the Close button in the Format Axis pane.

Chart Legend and Title Formats

The next items we will format on the Grade Distribution Comparison chart are the chart legend and title. Similar to the how we formatted the X and Y axes, we can format these items by activating them and using the formatting commands in the Home tab or the Format pane. The following steps explain how to add these formats:

1. Right click the legend on the Grade Distribution Comparison chart and select Format Legend.

2. Select Right in the Legend Position options. Close the Format Legend pane.

3. Move the legend by placing your cursor – shaped like a little plus sign with four arrows – on the edge of the selection box.  Click and drag the legend so the top of the legend aligns with the 35% line next to the plot area (see Figure 4.29).

Figure 4.29 Moving the Legend

1. While the legend is still selected, change the font style in the Home tab of the ribbon to Arial.

2. Change the font size to 12 points.

3. Click the bold and italics commands in the Home tab of the ribbon.

4. Click and drag the left sizing handle so the legend is against the plot area (see Figure 4.30).

Figure 4.30 Legend Formatted and Resized

1. Click the chart title to activate it.

2. Right click on the chart title and select Format Chart Title to open the Format Chart Title pane.

3. Under Title Options , in the Effects group (the option in the middle) give your title one of the Preset shadows. Change the color, if you like.

4. Close the Format Chart Title pane.

5. Save your work.

Figure 4.31 Format Chart Title Pane

Skill Refresher

Formatting the Chart Legend

1. Click the Legend to activate it.

2. Click either the Home tab or right click to activate the appropriate formatting pane.

3. Select any of the available formatting commands.

4. Click and drag the legend to move it.

5. Click and drag any of the sizing handles to adjust the size of the legend.

Skill Refresher

Formatting the Chart Title

1. Click anywhere on the chart title.

2. Click either the Home tab or right click to activate the appropriate formatting pane.

3. Select any of the available formatting commands.

X and Y Axis Titles

Titles for the X and Y axes are necessary for defining the numbers and categories presented on a chart. For example, by looking at the Grade Distribution Comparison chart, it is not clear what the percentages along the Y axis represent. The following steps explain how to add titles to the X and Y axes to define these numbers and categories:

1. Click anywhere on the Grade Distribution Comparison chart in the Grade Distribution worksheet to activate it.

2. On the Design tab on the ribbon select the Add Chart Element button, then Axis Titles, then Primary Vertical. (See Figure 4.32)

Figure 4.32 Selecting a Title for the Y Axis

3. Using the Home ribbon, change the font of the axis title to Arial, Bold, size 11.

4. Click in the beginning of the Y axis title and delete the generic title. Type Percent of Enrolled Excel Students.(see Figure 4.33).

Figure 4.33 Adding and Formatting the Y Axis Title

Next we will add the title for the X axis.

1. On the Design tab select the Add Chart Element button, then Axis Titles, then Primary Horizontal.

2. Using the Home ribbon, change the font of the axis title to Arial, Bold, size 11.

3. Click in the beginning of the X axis title and delete the generic title. Type Final Course Grade. Figure 4.34 shows the added titles for the X and Y axes. The titles provide definitions for the grade categories along the X axis as well as the percentages on the Y axis.

4. Save your work.

Figure 4.34 X and Y Axis Titles Added

Skill Refresher

X and Y Axis Titles

1. On the Design tab select the Add Chart Element button.

2. Click anywhere on the chart to activate it.

3. Select one of the options from the second drop-down list.

4. Click in the axis title to remove the generic title and type a new title.

Data Series Labels and Formats

Adding labels to the data series of a chart is a key formatting feature. A data series is the item that is being displayed graphically on a chart. For example, the blue bars on the Grade Distribution Comparison chart represent one data series. We can add labels at the end of each bar to show the exact percentage the bar represents. In addition, we can add other formatting enhancements to the data series, such as changing the color of the bars or adding an effect. The following steps explain how to add these labels and formats to the chart:

1. Click on any of the the red bars representing the All Excel Classes data series on the Grade Distribution Comparison chart in the Grade Distribution worksheet. Clicking one bar automatically activates all bars in the data series. If you click a bar a second time, only that bar is activated.

2. Right click and select Format Data Series to open up the Format Data Series pane.

3. Click the Fill and Line (paint bucket) button to bring up the Fill and Border group of commands.

4. Click the word Fill (if needed) to expand the list of Fill options.

5. Select Pattern Fill. Then select 30% (fifth column, top row). Changing your fill pattern to a pattern makes it easier to distinguish between the data series when you print or view your chart in black and white. While you are there, make changes to the fill by experimenting with different foreground and background colors.

6. Close the Format Data Series pane.

Figure 4.35 Changing the Fill of a Data Series

Now we are going to add the Data Labels at the end of the columns.

1. Be sure that your entire chart is selected, not just one of the data series. Click the Design tab in the Chart Tools section of the ribbon.

2. On the Design tab select the Add Chart Element button, then Data Labels, then Outside End (see Figure 4.36.)

3. Click on one of the Data Labels. Note that all of  the data labels for that data series are selected.

4. Using the Home ribbon, change the font to Arial, Bold, size 9.

5. Click on one of the data labels for the other data series. Format those data labels as Arial, Bold, size 9 as well.

6. Save your work.

Figure 4.36 Adding Labels to a Data Series

Figure 4.37 shows the Grade Distribution Comparison chart with the completed formatting adjustments and labels added to the data series. Note that we can move each individual data label. This might be necessary if two data labels overlap or if a data label falls in the middle of a grid line. To move an individual data label, click it twice, then click and drag.

Figure 4.37 Completed Formatting Adjustments for the Data Series

Skill Refresher:

Ad d ing D a t a Labels

1. Click anywhere on the chart to activate it.

2. Click the Design tab in the Chart Tools section of the ribbon.

3. Click the Add Chart Element in the Chart Layout group.

4. Then, select Data Labels

5. Select one of the preset positions from the drop-down list.

Skill Refresher

F o r m a tt ing a D a t a Se r ies

1. Click any bar or line for a data series.

2. Right click to activate the Format Data Series pane.

3. Use the formatting tools in the pane to make changes to the data series.

Adding Series Lines and Annotations to a Chart

The last formatting features we will demonstrate are adding series lines and annotations to a chart. To demonstrate these skills, we will use the Change in Enrollment Statistics Spend Source stacked column chart. Series lines are commonly used in stacked column charts to show the change from one stack to the next. Annotations are useful for clarifying the data presented in a chart or for identifying data sources. In addition to demonstrating these skills, we will review several of the formatting skills that were covered in this section. The following steps include the skills review as well as the new formatting features:

1. Locate the Enrollment by Race stacked column chart on the Enrollment Statistics worksheet. Activate the chart by clicking anywhere inside the chart perimeter.

2. Move the chart to a separate chart sheet by clicking the Move Chart button in the Design tab of the ribbon. Type the following in the New sheet input box: Enrollment by Race Chart. Click the OK button.

3. Click anywhere on the data table (on the x axis) to activate it. Using the Home ribbon, change the font to Arial, Bold, size 12.

4. Activate the Y axis and apply the same formatting adjustments as stated in step 3.

5. Add a Y axis title using Add Chart Elements – Axis Titles – then More Axis Title Options.

6. In the Format Axis Title pane change the fill color and border to colors of your choice.

7. Then, using the Home tab of the ribbon, change the font to Arial, Bold, size 14.

8. Change the text of the Y axis title to Percent Enrollment by Race.

9. Check the horizontal axis to see if this process created an extra axis title there.  If it did, delete it.

10. Activate the title of the chart by clicking it once. The Format Chart Title pane should be open. If not, right click the Chart title and select Format Chart Title from the menu. Change the fill and border to match your vertical Axis label.

11. Then, using the Home tab of the ribbon, change the font of the chart title to Arial, Bold, size 20.

12. Close the Formatting pane.

13. Click the Add Chart Elements tool (Design tab), then Lines, then Series Lines.
This adds lines to the chart, connecting each data series between the three stacks (see Figure 4.38).

Figure 4.38 Selecting the Series Lines Option

1. Right click on any of the series lines added to the chart. Clicking one line will activate all lines on the chart. (If the Format Pane is open, you will not need to right click.  Just left click on any of the series lines to change the format pane to Format Series Lines)

2. Select Format Series Lines. This will open the Format Series Lines pane.

3. Change the width to 2.25.

4. Close the Format Series Lines pane.

Figure 4.39 shows the appearance of the chart with the series lines connecting the two stacks. This formatting enhancement is common for stacked column charts. The lines help focus the audience’s attention to changes in the percent of total trend.

Figure 4.39 Series Lines Added to the Stacked Column Chart

Our chart demonstrates the percentage differences in enrollment between the community colleges. But, it would be handy to know the total Enrollment at each of the colleges. To display that, we will add text boxes above each column. To start with, we need to make room for the text boxes.

1. Select the Plot Area. Place your cursor on the top center handle of the Plot Area and drag down about ½ inch.

Figure 4.40 Resizing the Plot Area

Add text boxes to include additional information in the chart.

1. Click the Text Box button in the Text group on the Insert tab of the ribbon (see Figure 4.41).

2. Place the mouse pointer on the left edge of the chart area approximately one-quarter inch from the top. Click and drag a rectangle approximately one and a half inches wide and one-quarter inch high (see Figure 4.41). Don’t worry if it’s not exact – you can move and resize text boxes at any time.

3. Type Total Enrollment. This tells the audience the size of each school.

4. Select all of the text in the text box.  (You can either highlight the text or click on the border of the text box once to select all of the text). Using the Home tab of the ribbon, change the font to Arial, size 14.

5. Repeat the process to add and format text boxes above each column.  You can try to copy and paste the text boxes if you would like to save time.

6. In each text box, type the Total Enrollment for each school:

- Mt Hood – 9,276

- Portland – 30,929

- Clackamas – 7,302

7. Save your work.

Figure 4.41 Completed Stacked Column Chart

Integrity Check

Annotations and Axis Titles

Although adding annotations and axis titles can be a tedious process, doing so maintains a high level of integrity for your charts. People can misinterpret the message being conveyed by the chart if they make inaccurate assumptions about the values displayed. Axis titles and annotations help prevent readers from making false assumptions and ensure that readers see the most accurate representation of the message being conveyed by the chart.

Skill Refresher

Ad d ing Series Lines

1. Click anywhere on the chart area.

2. Click the Layout tab of the ribbon.

3. Click the Lines button in the Analysis group of commands.

4. Click the Series Lines option from the drop-down list.

Skill Refresher:

Ad d ing Anno t ations

1. Click anywhere on the chart area.

2. Click the Insert tab of the ribbon.

3. Click the Text Box button in the Text group of commands.

4. Click and drag the size of the text box needed on the chart.

5. Apply any desired format changes from the Home tab of the ribbon.

6. Type the desired text.

Key Takeaways

- Applying appropriate formatting techniques is critical for making a chart easier to read.

- Many formatting commands in the Home tab of the ribbon can be applied to a chart.

- To change the number format for a data label, you must use the Number section in the Format Data Labels dialog box. You cannot use the Number format commands in the Home tab of the ribbon.

- To change the number format for the values on the Y axis, and the X axis in the case of a scatter chart, you must use the Number section of the Format Axis dialog box. You cannot use the Number format commands in the Home tab of the ribbon.

- Axis titles and annotations help prevent false assumptions from being made and ensure that the reader sees the most accurate representation of the information presented on a chart.

Attribution

Adapted by Noreen Brown 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.

21

4.3 Using Charts with Microsoft® Word® and Microsoft® PowerPoint®

Learning Objectives

1. Learn how to paste an image of an Excel chart into a Word document.

2. Learn how to paste a link to an Excel chart into a PowerPoint slide.

Charts that are created in Excel are commonly used in Microsoft Word documents or for presentations that use Microsoft PowerPoint slides. Excel provides options for pasting an image of a chart into either a Word document or a PowerPoint slide. You can also establish a link to your Excel charts so that if you change the data in your Excel file, it is automatically reflected in your Word or PowerPoint files. We will demonstrate both methods in this section.

Pasting a Chart Image into Word

For this exercise you will need two files:

- The Excel spreadsheet you have been working with in this chapter — CH4 Charting.

- A Word document data file — CH4 Diversity

Excel charts can be valuable tools for explaining quantitative data in a written report. Reports that address business plans, public policies, budgets, and so on all involve quantitative data. For this example, we will assume that the Change in Enrollment Statistics Spend Source stacked column chart  is being used in a student’s written report (see Figure 4.44). The following steps demonstrate how to paste an image, or picture, of this chart into a Word document:

1. Open CH4 Diversity. Save it as CH4 Diversity in Enrollment in Community Colleges

2. Click below the figure heading in the Word document that reads: Figure 1: Enrollment by Race. The image of the stacked column chart will be placed below this heading.

3. If needed, open the Excel file you have been working with (CH4 Charting). Activate the Enrollment by Race chart in the Enrollment by Race Chart sheet.

4. Click the down arrow on the Copy button in the Home tab of the ribbon.  Select Copy as Picture

5. Select OK — Accepting the Copy Pictures defaults:

- As shown on Screen

- Picture

6. Go back to the CH4 Diversity in Enrollment in Community Colleges Word document by clicking the file in the taskbar.

7. Confirm that the insertion point is below the Figure 1: Enrollment by Race heading (see Figure 4.42) and click the Paste button in the Home tab of the ribbon ( or press Crtl-V).

Figure 4.42 Paste Picture in Word

Oh no!! The picture is so big that it falls on to the next page. We will need to change its size.

1. Click anywhere on the picture of the chart to activate it.

2. Click the Format tab under the Picture Tools section of the ribbon (see Figure 4.43).

3. Click the down arrow on the Shape Width button in the Size group of commands. Continue to click the down arrow until the width of the picture is 5.4.” As you reduce the width of the picture, the height is automatically reduced as well. (The height should be ~ 3.92″)

4. To center the chart on the page, make sure the chart is activated.  Then go to the Home tab, to the Paragraph group, and select Center.

5. Save your work.

Figure 4.43 Changing the Size of a Picture in Word

Figure 4.44 shows the final appearance of the Enrollment by Race Source chart pasted into a Word document. It is best to use either the Shape Width or Shape Height buttons to reduce the size of the chart. Using either button automatically reduces the height and width of the chart in proper proportion. If you choose to use the sizing handles to resize the chart, holding the SHIFT key while clicking and dragging on a corner sizing handle will also keep the chart in proper proportion.

Figure 4.44 Final Appearance of Pasting a Chart Image into Word

Skill Refresher

P a sting a C h art I m a g e i n t o W o rd

1. Activate an Excel chart and click the Copy button in the Home tab of the ribbon.

2. Click on the location in the Word document where the Excel chart will be pasted.

3. Click the down arrow of the Paste button in the Home tab of the ribbon.

4. Click the Picture option from the drop-down list.

5. Click the Format tab in the Picture Tools section of the ribbon.

6. Resize the picture by clicking the up or down arrow on the Shape Width or Shape Height buttons.

Pasting a Linked Chart Image into PowerPoint

For this exercise you will need two files:

- The Excel spreadsheet you have been working with in this chapter — CH4 Charting.

- A PowerPoint data file – CH4 Diversity.

Microsoft PowerPoint is perhaps the most commonly used tool for delivering live presentations. The charts used in a live presentation are critical for efficiently delivering your ideas to an audience. Similar to written documents, a wide range of presentations may require the explanation of quantitative data. This demonstration includes a PowerPoint slide that could be used in a presentation. We will paste the Enrollment by Race chart into this PowerPoint slide. However, instead of pasting an image, as demonstrated in the Word document, we will establish a link to the Excel file. As a result, if we change the chart in the Excel file, the change will be reflected in the PowerPoint file. The following steps explain how to accomplish this:

1. Open CH4 Diversity.pptx. Save it as CH4 Diversity in Enrollment in Community Colleges.

2. Navigate to Slide 6 – Diversity in Enrollment. This is the slide where you will place the linked chart.

3. If needed, open the Excel file you have been working with (CH4 Charting). Activate the Enrollment by Race chart in the Enrollment by Race Chart sheet.

4. Click the down arrow on the Copy button in the Home tab of the ribbon.  Select Copy (not Copy as Picture.)

5. Go back to the CH4 Diversity in Enrollment in Community Colleges presenation by clicking the file in the taskbar.

6. Make sure you are still on Slide 6 – Diversity in Enrollment. Click on the outside edge of the empty prompt box on the right.

7. Click the down arrow below the Paste button in the Home tab of the ribbon in the PowerPoint file.

8. Hover over each of the Paste Options until you find Keep Source Formatting & Link Data (see Figure 4.45).  Select this option. This pastes an image of the Excel chart into the PowerPoint slide. In addition, a link is created so that any changes made to the chart (in Excel) appear on the PowerPoint slide.

Figure 4.45 Creating a Link to an Excel Chart in PowerPoint

Next we need to make some changes to clean up the chart a bit.  First, we are going to apply a different chart style.

1. Click anywhere in the plot area of the column chart pasted into the PowerPoint slide. You will see the same Excel Chart Tools tabs added to the ribbon (see Figure 4.46).

2. On the Design tab, select Style 8 in the Chart Style group.

Figure 4.46 Modifying and Excel Chart Pasted into a PowerPoint Slide

Paste linking this chart caused trouble with the text boxes we added, so next we are going to delete them.

1. Select each text box by clicking on the outside edge of the text box with the four-headed arrow. Press the delete key on your keyboard. Be sure that the insertion point is NOT blinking inside the text box.  If it is, you will be editing the contents of the text box instead of deleting the actual text box.

The benefit of adding this chart to the presentation as a link is that it will automatically update when you change the data in the linked spreadsheet file.

1. Return to your CH4 Charting Excel file.

2. Select the Enrollment Statistics worksheet (the one with the Enrollment data.) Change the value in cell D3 to 1000.  You have just changed the number of white students at Clackamas Community College to 1000. This isn’t true, but you want to change the data enough to see the effect in the charts.

3. Select the Enrollment by Race Chart worksheet. Notice how the chart has changed.

4. Return to the Diversity in Enrollment in Community Colleges PowerPoint file by clicking the file in the taskbar.

5. On Slide6, you should see the updated chart (see Figure 4.47).

6. If the chart has not changed; be sure that your chart is selected, click the Design tab in the Chart Tools section of the ribbon. Click the Refresh Data button. The change made in the Excel workbook is now reflected on the PowerPoint slide.

7. If that still doesn’t work, you may have created a “normal” link — instead of a Paste Link.  Delete the chart and follow the steps again.  Start from the beginning of this section.

8. Save your work.  You will submit both the Word and PowerPoint files, along with the Excel file, at the end of the next section.

Figure 4.47 shows the appearance of the column chart after the change was made in the Enrollment Statistics worksheet in the Excel file. Note that the Data Chart at the bottom reflects the new number, too. The change that was made in the Excel file will appear in the PowerPoint file after clicking the Refresh Data button.

Figure 4.47 Styled and Updated Chart.

Integrity Check

Refreshing Linked Charts in PowerPoint and Word

When creating a link to a chart in Word or PowerPoint, you must refresh the data if you make any changes in the Excel workbook. This is especially true if you make changes in the Excel file prior to opening the Word or PowerPoint file that contains a link to a chart. To refresh the chart, make sure it is activated, then click the Refresh Data button in the Design tab of the ribbon. Forgetting this step can result in old or erroneous data being displayed on the chart.

Integrity Check

Severed Link?

When creating a link to an Excel chart in Word or PowerPoint, you must keep the Excel workbook in its original location on your computer or network. If you move or delete the Excel workbook, you will get an error message when you try to update the link in your Word or PowerPoint file. You will also get an error if the Excel workbook is saved on a network drive that your computer cannot access. These errors occur because the link to the Excel workbook has been severed. Therefore, if you know in advance that you will be using a USB drive to pull up your documents or presentation, move the Excel workbook to your USB drive before you establish the link in your Word or PowerPoint file.

Skill Refresher:

P a sting a Linked C hart Image in t o P o w er P oint

1. Activate an Excel chart and click the Copy button in the Home tab of the ribbon.

2. Click in the PowerPoint slide where the Excel chart will be pasted.

3. Click the down arrow of the Paste button in the Home tab of the ribbon.

4. Click the Keep Source Formatting & Link Data option from the drop-down list.

5. Click the Refresh Data button in the Design tab of the ribbon to ensure any changes in the Excel file are reflected in the chart.

Key Takeaways

- When pasting an image of an Excel chart into a Word document or PowerPoint file, use the Picture option from the Paste drop-down list of options – if you want the image to act as an image.  You will not be able to make any changes to the content of the picture.

- When creating a link to a chart in Word or PowerPoint, you may need to refresh the data if you make any changes in the originating spreadsheet.  You should  not use the Picture option.

Attribution

Adapted by Noreen Brown 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.

22

4.4 Preparing to Print

Learning Objectives

1. Review each worksheet in a workbook in Print Preview.

2. Modify worksheets as needed to professionally print data and charts.

In this section we will take a look at each of the worksheets created in the previous sections. Since these worksheets contain a combination of data and charts, there are specific things to watch for if you will be printing the sheets.

We will start by looking at each worksheet in Print Preview in Backstage View.  We will then make any changes necessary, such as changing the orientation and scaling, or moving charts around on the worksheet.  To make sure we don’t miss any worksheets, we are going to review the worksheets in the order they appear in the tabs.

Previewing Chart Sheets for Printing

Data file: Continue with CH4 Charting.

The first worksheet, Closing Prices, is a chart sheet. This means that it does not contain any data; remember that chart sheets just contain charts.  We still need to review it in Print Preview.

1. Click on the Closing Prices worksheet tab.

2. Go to Print Preview by clicking Print in Backstage View.

3. Notice that the chart will print on the entire page, in Landscape orientation.

4. There is nothing to change. Exit Backstage View.

Printing Worksheets with Data and Charts

The next worksheet (Stock Trend) has a lot of data and multiple charts.  We need to print the data and the charts, which will require some modifications of the page setup.

1. Click on the Stock Trend worksheet tab.

2. Go to Print Preview by clicking Print in Backstage View.

3. Notice that this worksheet is currently printing on seven pages.

4. As you click through each page you should make the following observations:

- The data is split between the first and third pages.

- The line chart starts on the first page, but part of it is also on the second page.

- The double-line chart starts on the third page, and then finishes on the fifth page.

- The fourth and sixth pages are blank.

- The last page has a column of seemingly random numbers.

5. Exit Backstage View.

The first thing we are going to do is remove the numbers that are appearing on page 7. To do this, we will hide the column where they are being stored.  We are going to hide the column, instead of deleting the numbers, in case the numbers are being utilized somewhere else in the workbook.

1. Scroll to the right on the worksheet until you find the numbers in column AH.

2. Click anywhere in column AH.

3. On the Home ribbon, click the Format button in the Cells group.

4. In the Visibility section, select Hide & Unhide then select Hide Columns (see Figure 4.64).

5. The visible column headings should now go from AG to AI.

6. Return to Print Preview in Backstage View to see the changes to the printed worksheet.

7. Notice that there are now five pages.  The data and charts are still splitting across multiple pages, but the numbers in column AH are no longer going to print.

8. Remain in Backstage View for the next steps.

Figure 4.64 Hide Columns in Format Menu

The data is still split between pages 2 and 3, and the charts are splitting oddly as well.  The first step we will try to fix these issues is to change the page orientation and scaling.

1. While still in Backstage View, change the page orientation to Landscape (use the Orientation drop-down menu in the Settings section).

2. This puts all of the data on one sheet, but the charts are still split between multiple pages.

3. Change the page scaling to Fit Sheet on One Page (use the Scaling drop-down menu in the Settings section).

4. This fits everything on one page, but it is too small to be able to read.

5. Change the page scaling back to No Scaling.

The next thing we will try is moving one, or both, of the charts. In order to move the charts, we need to exit out of Backstage View.

1. Exit Backstage View.

2. Switch to the View ribbon and then select Page Break Preview.  Your screen should look similar to Figure 4.65.  (Remember that the dotted blue lines indicate automatic page breaks.)

3. Move the 24 Month Comparison (double-line) chart closer to the top of its page.

4. Move the May 2014-2015 Trend for NASDAQ Sales Volume (line chart) so that it is under the 24 Month Comparison chart.

5. The link to the data source is still at the bottom of page 2 (in A50:A51) so you need to move it as well.  Using your preferred method, move the text from A50:A51 to M31:M32.

6. Now your screen should look similar to Figure 4.66.

Figure 4.65 Page Break Preview before moving the charts in Step 3

Figure 4.66 Page Break Preview after moving the charts and text

We don’t want the data source link text to print on its own page, but there is no room to move it onto the same page as the charts. To fix this, we are going to remove the automatic page break between the charts and the text in M31:M32.

1. Place your pointer on the horizontal blue dashed line (automatic page break) between the line chart and the Data Source link text.

2. When your pointer changes to the double arrow (pointing up and down), drag the page break down into the gray area. This removes the page break.

3. If your vertical automatic page break between columns L and M moves, drag it back between columns L and M. This will make it a solid blue line, which will no longer adjust automatically.

4. Your screen should now look like Figure 4.67.

Figure 4.67 Page Break Preview after removing a page break

Now you need to do one final check of this worksheet in Print Preview.

1. Go to Print Preview and look at both pages. Page 1 should contain just the data and page 2 should have both charts and the Data Source link text.

2. Exit Backstage View and save the file.

Preview Remaining Worksheets for Printing

There are four remaining worksheets to be reviewed. Some of them will need minor changes and some will not need any changes.  You will need to preview each one and then make the specified changes.  In the following steps you will preview and modify the next three worksheets.

1. All Excel Classes – this is a chart sheet, so it should not need any changes.

2. Grade Distribution – the chart is split across two pages. Fix this by changing the orientation (Landscape) and scaling (Fit Sheet on One Page).

3. Enrollment by Race Chart – this is a chart sheet, so it should not need any changes.

Printing a Chart Only

Sometimes you might have a worksheet that has data and a chart, but you only want the chart to print.  That is the case with the Enrollment Statistics worksheet.

1. Switch to the Enrollment Statistics worksheet.

2. Select the chart.

3. Go to Print Preview.  Only the chart is printing.  (If it shows the data printing along with the chart, exit Backstage View and be sure to select just the chart on the worksheet.)

4. If needed, change the orientation to Landscape.  This orientation looks better when printing just a chart.

5. Exit Backstage View.

Hiding a Worksheet

You have actually decided that you do not want the Enrollment by Race Chart sheet to be visible at all, but you do not want to delete it. We are going to hide it from anyone looking at the workbook.

1. Right-click on the Enrollment by Race Chart tab.

2. Select Hide from the menu that appears.  The Enrollment by Race Chart sheet should no longer be visible.

3. Now you want to bring the worksheet back.  To unhide it, right-click on any other worksheet tab and select Unhide from the menu. A list of hidden worksheets will be displayed. Select the Enrollment by Race Chart and click OK.

4. Save the CH4 Charting workbook.

5. Compare your work (on all of the worksheets) with the self-check answer key (found in the Course Files link).

6. Submit all three files from this chapter (CH4 Charting.xlsx, CH4 Diversity in Enrollment in Community Colleges.docx, and CH4 Diversity in Enrollment in Community Colleges.pptx) as directed by your instructor.

Attribution

“4.4 Preparing to Print” by Julie Romey, Portland Community College is licensed under CC BY 4.0

23

4.5 Chapter Practice

To assess your understanding of the material covered in the chapter, please complete the following assignments.

Although Excel is primarily used in business and scientific applications, you will find it useful in other areas of study as well. In these exercises we will use Excel to create charts using historical, health, and social justice data.

Charting Historical Data (Comprehensive Review)

Download Data File: PR4 Data

Excel is an excellent tool for helping to display historical data. In this exercise we will be examining ways to display information on minimum wage data and life expectancy.

Task1 – National Minimum Wage in the US – 1960-2014

Since the beginning of the previous century, the United States has set a minimum wage, in order to set a “floor” beneath which wages cannot fall. Most states have set their own minimum wages, but none are lower than the national minimum wage. To learn more about the national minimum wage, look here: https://en.wikipedia.org/wiki/Minimum_wage_in_the_United_States

1. Open the file named PR4 Data and then Save As PR4 Historical Data.

2. On the Minimum Wage worksheet, select the range B4:C60

3. Select the Insert tab, then the Recommended Chart tool in the Charts group.
Recommended Charts allows users to first see how selected data would be represented on a variety of chart types before committing to a particular type of chart. Being able to see your data as it would look in a variety of charts helps you select the kind of chart that best matches your date.
It does a particularly good job when you want to use dates or years as labels. Sometimes, Excel gets confused and thinks that dates are part of the data – instead of labels.

4. Select the first Line chart. Press OK.

5. Your line chart is embedded in the Minimum Wage worksheet.

6. Make sure that the upper left corner of your chart is in the upper left corner of E4. The lower right corner is in the lower right corner of M20.

7. Adjusting the chart title: Click the chart title once. Then click in front of the first letter. You should see a blinking cursor in front of the letter. This allows you to modify the title of the chart.

8. Type the following in front of the first letter in the chart title: US National

9. While your chart is still selected, select the Design tab. You will find the Chart Styles group in the middle of the ribbon. If necessary, press the More button to see the available styles.

10. Float your cursor over the available styles so you can see how they will affect your chart. Select Style 4.

11. The years across the X (category) axis are a little hard to read.
Select the labels. When you have them selected, you will see a box surrounding the list of years.
On the Home tab, in the Alignment Group, select the Orientation tool. Select Angle Counter Clockwise.

12. Prepare the Minimum Wage worksheet for printing by changing the scaling to Fit Sheet on One Page.

Task2 – Oregon: Projected Life Expectancy at Birth

In the past 40 years, between 1970 and 2010, life expectancy for Oregon men improved by 8.7 years and for women by 5.5 years. Oregon’s life expectancy has remained slightly higher than the U.S. average. The life expectancy will continue to improve for both men and women. However, the gain for men has been outpacing the gain for women. Consequently, the difference between men’s and women’s life expectancies has continued to shrink.

https://www.oregon.gov/das/OEA/Documents/OR_pop_trend2019.pdf

1. On the Life Expectancy sheet, select A5:B11

2. Press F11

3. This creates a column chart, and puts it on a separate sheet.

4. Double click the chart sheet tab, change the name to Men.

5. Take a good look at this chart. It is not what we were expecting. Excel has made a mistake and charted the Birth Year information as though it was data, instead of using it to label the bottom (category) axis. That needs to be fixed. We need to tell Excel that the data in Column A are labels, not values.

6. With the chart selected, go to the Design tab, and select the Select Data tool.
This opens the Select Data Source dialog box. The box at the top tells us the range we selected, which looks fine.

The Legend Entries need to be corrected to fix the issue with the data series (columns).  Also, the Horizontal Axis Labels are just a series of default numbers. They need to be the range that contains the years.

7. In the Legend Entries, click in the small box in front of Birth Year to remove the check mark.  This will remove the Birth Year as a data series on the chart.

8. In the Horizontal (Category) Axis Labels box, press the Edit button. This will open the Axis Labels dialog box. Press the Select range button.

9. Navigate to the Life Expectancy tab and select A6:A11. After “=’Life Expectancy’!$A$6:$A$11” is displayed in the box, press the Select Range button. Press OK.
Press OK

10. Change the chart title so that it reads: Life Expectancy for Oregon Men.

11. Remove the Legend from the bottom of the chart by right-clicking on it and selecting Delete.

12. Return to the Life Expectancy tab, select A5:All , C5:C11 (Select the first range, hold down the CTRL key, select the next to select both noncontiguous ranges at the same time.

13. Repeat steps 2-11 above to create a matching chart for Life Expectancy for Oregon Women.

14. Return to the Life Expectancy tab, select A5:D11

15. Use the Recommended Charts tool to create a simple line chart.

16. Change the Chart Title to Oregon: Projected Life Expectancy at Birth.

17. The green line across the bottom of the chart represents the difference between men’s and women’s life expectancy. It is not very helpful as it is.
Right click on the green line to open the pop up menu. Select Format Data Series. In the Format Data Series pane, under the Series Options tab, select the radio button in front of Secondary Axis.

18. Close the Format Data Series pane.

19. Add a text box that explains the Difference calculation. While the chart is still selected:

1. On the Insert tab, on the right side of the ribbon, find Text. Select the Text Box tool.

2. When you select it, your cursor will turn into cross hair (thin black plus sign)

3. Click once in the lower left corner of your chart. This creates a text box.

4. Type the following into the text box: Difference = Female life expectancy minus male.
Move or resize the text box if you wish.

20. Move or resize your chart so that it no longer is on top of the spreadsheet data, and so it covers the text that starts in A19.

21. Use the Chart Styles tools to change your chart to something a bit more dramatic.

22. Preview the Life Expectancy worksheet in Print Preview and make any necessary changes.

23. Save the PR4 Historical Data workbook.

24. Compare your work with the self-check answer key (found in the Course Files link) and then submit the PR4 Historical Data  workbook as directed by your instructor.

Attribution

“4.5 Chapter Practice” by Noreen Brown, Portland Community College is licensed under CC BY 4.0

24

4.6 Scored Assessment

Charting Social Justice Data (Comprehensive Review)

Download Data File: SC4 Data

Task 1 International Incarceration rates

1. Open the file named SC4 Data and then Save As SC4 Social Justice.

2. On the International tab, use a function in cell D20 to find the average of the Individuals incarcerated data.

3. Create a bar chart that looks like this:

Figure 4.68

1. Move and or resize it so that it does not cover any of the data.

2. In cell A25 write a brief note explaining why a bar (or column) chart is a better choice for this data than a pie chart.

3. Set the Print Area so that everything prints, except for the explanation you added in cell A25.

4. Preview the International worksheet in Print Preview and make any changes needed to print professionally on one page.

Task 2 Disenfranchisement Rate

Felony disenfranchisement is the exclusion from voting of people otherwise eligible to vote (known as disfranchisement) due to conviction of a criminal offense, usually restricted to the more serious class of crimes: felonies. Jurisdictions vary as to whether they make such disfranchisement permanent, or restore suffrage after a person has served a sentence, or completed parole or probation. Affected individuals suffer “collateral consequences” including loss of access to jobs, housing, and other facilities.

Opponents have argued that such disfranchisement restricts and conflicts with principles of universal suffrage. It can affect civic and communal participation in general.

https://en.wikipedia.org/wiki/Felony_disenfranchisement

On the Disenfranchisement rate sheet, use the Recommended Chart tool to create the following three charts.  Put each chart on its own individual sheet.  (Note:  Not an object in a spreadsheet.)

Chart1 — Felony Disenfranchisement Rate Washington

Figure 4.69

Notes

This is called a Clustered column chart in the Recommended charts tool, but it is really a combo chart – with some data displayed as columns and some data displayed as a line.

You will need to edit the Chart Title.

You may need to adjust the chart size to make it look like the illustration.

Put the chart on its own individual sheet — don’t leave it as an object on the spreadsheet.

Chart2 — Felony Disenfranchisement Rate Oregon

Figure 4.70

Notes:
Take a look at both the Washington and Oregon charts.  They are the same kind of chart, but your charts may look different because the data is a bit different.

To make them more similar – make sure that the vertical axes are the same in both charts.  Format the left vertical (value) axis, so that the Maximum is 20000. Format the right vertical axis, so that the Maximum is .20.

Chart3 – Comparison – Oregon and Washington

Notes:

Be sure that you have years as labels on the horizontal axis (not 1, 2, 3 . . . )

This chart is on its own separate sheet.

1. When you have completed all three charts, save your work.  Preview the worksheet(s) in Print Preview and make any necessary changes for professional printing.

2. Submit the SC4 Social Justice workbook as directed by your instructor.

Attribution

“4.6 Scored Assessment” by Noreen Brown, Portland Community College is licensed under CC BY 4.0

V

Chapter 5 – Tables

Excel workbooks are designed to store lots of information. Organizing this information so that they display meaningful data can be challenging! Excel has many features that can help you organize your data and find what you need efficiently. Setting up your data as a table from the onset will allow you to sort, filter, total, and subtotal your data. In Excel, a table is a collection of data about a particular subject stored in adjacent rows and columns. Tables can improve the look and feel of your workbook. This chapter explores how to best set up Excel tables, how to edit them, and then how to work with them effectively. These skills will be demonstrated in the context of a multi-sheet file that shows national average weather for two very different cities in the United States. Weather data is often voluminous and difficult to summarize since we collect weather data every hour of every day. Providing meaningful summaries of such data is a useful skill to have.The skills learned with this data can easily be transferred to data specific to your professional and personal needs.

Attribution

Chapter 5 – Tables by Diane Shingledecker, Portland Community College is licensed under CC BY 4.0

25

5.1 Table Basics

Learning Objectives

1. Understand table structure.

2. Plan, create, and edit a table.

3. Freeze rows and columns.

4. Sort data in a table.

This section reviews the fundamental skills for setting up and maintaining an Excel table. The objective used for this chapter is the construction of a multi-sheet file to keep track of two cities’ national weather data for the month of January. Organizing, maintaining, and reporting data are essentials skills for employees in most industries.

Figure 5.1 shows the completed workbook that will be demonstrated in this chapter. Notice that this workbook contains three worksheets. The first worksheet lists average weather for January in Portland, Maine. The second sheet lists average weather data for January in a very different climate – Portland, Oregon. The third sheet adds a weekly column to the Portland, Oregon data so that it can be subtotaled by week.

Figure 5.1 Completed National Weather Workbook

Creating a Table

Download Data file: CH5 Data

When data is presented in long lists or columns, it helps if the table is set up well. Here are some rules of data-entry etiquette to follow when creating a table from scratch:

1. Whenever you can, organize your information using adjacent (neighboring) columns and rows.

2. Start the table in the upper-left corner of the worksheet and work your way down the sheet.

3. Don’t skip columns and rows just to “space out” the information. (To place white space between information in adjacent columns and rows, you can widen columns, heighten rows, and change the alignment.)

4. Reserve a single column at the left edge of the table for the table’s row headings or identifying information.

5. Reserve a single row at the top of the table for the table’s column headings.

6. If your table requires a title, put the title in the row(s) above the column headings.

Following these rules will help insure that the sorts, filters, totals, and subtotals you apply to your table with give you the desired results.

With these rules in mind, we will begin working on the Portland ME worksheet in the National Weather workbook. Notice that the data is in adjacent columns and rows. The upper-left corner of the table is in A5 and the titles are above the column headings in Row 5. Since the set-up of our data looks good, we are ready to turn our data range into an Excel table:

1. Open data file CH5 Data and save a file to your computer as CH5 National Weather.

2. Click on A5 in the Portland ME sheet.

3. Click the Table button in the Insert tab of the Ribbon.
Figure 5.2 will appear on your screen.

Figure 5.2 Create Table

1. Make sure “My table has headers” is checked. Click OK.

2. Click in A5 again.

3. Adjust your columns widths so that you can see the complete headings in row 5 with the filter arrows showing. The filter arrows are the down-arrow buttons that will appear in row 5 when you create your table. We will learn how to use these to sort and filter later in this chapter.

After this, your spreadsheet will look like Figure 5.3.

Figure 5.3 Weather Table

Notice that a new ribbon tab, Table Tools Design, appears when you click inside your table. This ribbon tab allows you to edit, style, and add functionality to your table.

Let’s try these steps again in the following steps:

1. Click on the Portland OR sheet and click in cell A5.

2. Click the Table button in the Insert tab of the Ribbon.

3. Make sure “My table has headers” is checked. Click OK.

4. Click in A5 again.

5. Adjust your columns widths so that you can see the complete headings in row 5 with the filter arrows showing.

Skill Refresher

Create a Table

1. Click on the top left cell in your data.

2. Click the Table button in the Insert tab of the Ribbon.

3. Make sure “My table has headers” is checked. Click OK.

4. Click on the top left cell again.

5. Adjust your columns widths so that you can see the complete headings with the filter arrows showing.

Formatting Tables

There are many ways to format an Excel table. There are preset colored Table Styles with Light, Medium, and Dark colors. There are also a variety of Table Style Options listed in Table 5.1.

Table 5.1 Table Style Options

Table Style

Description

Header Row

Top row of the table that includes column headings

Total Row

Row added to the bottom that applies column summary calculations

First Column

Formatting added to the left-most column in the table

Last Column

Formatting added to the right-most column in the table

Banded Rows

Alternating rows of color added to make it easier to see rows of data

Banded Columns

Alternating columns of color added to make it easier to see columns of
data

Filter Button

Button that appear at the top of each column that lists options for sorting and filtering

We’ll add some formatting to both of our Portland weather tables in the following steps:

1. Click on the Portland ME sheet in your file.

2. In the Table Tools Design tab, in the Table Styles group, click the More button.

A gallery of table styles will appear as in Figure 5.4.

Figure 5.4 Table Styles

3. In the Table Styles gallery, in the Medium Section, click Table Style Medium 7.

4. In the Table Style Options group in the Ribbon, click Banded Rows.

The alternating colored rows will disappear. The data in the table is now more difficult to read.

5. Try out some of the other options in the Table Style Options group. Once you’re finished, check just Header Row, Banded Rows, and Filter Button as in Figure 5.5 below.

Figure 5.5 Ribbon Table Style Options

Adding Data to Tables

Over time, you will need to add new data to an Excel table. You will add the data to the table in a blank row. The easiest way to do this is to enter the data in the first blank row below the last row in the table. You can then rearrange the data in the table by sorting it. If you need to add data in a specific place in the middle of a table, you can insert a blank row in the middle and add your data there.

We need to add the last three days of the months to both our Portland, Maine and Portland, Oregon tables. The following steps will walk you through doing this.

1. Click on the Portland ME worksheet.

2. Click on A34 (the left-most cell below the last row in the table).

3. Enter the following data:

Table 5.2 Portland, Maine data

Day

High (°F)

Low (°F)

Rain
(inches)

Snow
(inches)

29

31.4

13.3

0.12

0.59

30

31.6

3.4

0.08

0.47

31

31.7

13.5

0.12

0.63

Notice that the banded row formatting continues as additional rows are added to the tables.

1. Click on the Portland OR worksheet.

2. Click on A34 (the left-most cell below the last row in the table).

3. Enter the following data:

Table 5.3 Portland, Oregon data

Day

High (°F)

Low (°F)

Rain
(inches)

Snow
(inches)

29

48.8

36.2

0.16

0

30

49.0

36.2

0.11

0.32

31

49.1

36.1

0.16

0

Finding and Editing Data

It is inevitable that you will find data errors in your table and need to correct them. While you can visually scan through a table to find your errors, this can be a tedious and tiresome process. Excel can help with this through the Find command. When you use Find, the best practice is to start at the top of the table to ensure that all your data is included in the search.

We know that a temperature of 3.4 degrees (brrr!) was entered erroneously in the Portland Maine sheet. It should have been 13.4. To fix this error, complete the following steps.

1. Click on the Portland ME sheet.

2. Press the CTRL+HOME keys together to go to the top of the sheet (A1).

3. In the Home tab of the ribbon, click on Find & Select in the Editing Group and then click Find.

4. In the Find box, type 3.4, and then click Find Next.

Figure 5.6 Find and Replace

5. Click the Close button.

6. Replace 3.4 in the Low column for Day 10 with 13.4.

7. Now switch to the Portland Oregon sheet and find the Snow error of .32. Change it to 0.12. You should find the error in Day 3.

Skill Refresher

Finding and Replacing Data

1. In the Home tab of the ribbon, click on Find & Select in the Editing Group and then click Find.

2. In the Find box, type what you want to find, and then click Find Next.

3. Continuing click Find Next until you find.what you are looking for.

4. Click Close and edit your data.

Freeze Rows and Columns

When you freeze panes, Microsoft Excel keeps specific rows or columns visible in your table when you scroll through it on your screen. For example, if the first row in your spreadsheet contains labels, you might freeze that row to make sure that the column labels remain visible as you scroll down in your spreadsheet. When we scroll through our weather data, it would be nice to keep our column headings visible on the screen.

To freeze your headings:

1. Click in A6, the left-most cell below the headings row.

2. Click the View tab in the ribbon.

3. Select Freeze Panes and then Freeze Panes again.

4. Scroll up and down the sheet and notice that the headings are always displayed at the top of the table.

Figure 5.7 Freeze Pane

To unfreeze your headings:

1. Click on the View tab in the ribbon.

2. Select Unfreeze Panes.

Simple Sort

Content in a table can be sorted alphabetically, numerically, and in many other ways. Sorting helps organize data by one or more columns in your table. Table 5.4 describes the different sort orders available for each column of data.

Table 5.4 Sort Options

Sort Order

Text

Numbers

Dates

Ascending

Alphabetical (A-Z)

Smallest to Largest

Lowest to Highest

Chronological (oldest to newest)

Descending

Reverse Alphabetical (Z-A)

Largest to Smallest

Highest to Lowest

Reverse Chronological (newest to
oldest)

Let’s say we want to know what the snowiest day was in January in Portland, Maine; so we want to sort the Snow column in Descending order so that the snowiest day ends up at the top of the table.

1. Click on the filter Click arrow to the right of the header Snow (inches) in the Portland ME worksheet.

2. Click on the choice Click ZA↓ Sort Largest to Smallest. See Figure 5.8 below.

Figure 5.8 Sort by One Column

If you did this correctly, you’ll see that the snowiest day at the top of the list in January 3rd (in row 6) with 0.73 inches of snow! Notice the filter arrow changes in the snow column to a downward pointing arrow to indicate you sorted that column in descending order (largest to smallest).

Figure 5.9 Snowiest Days in Maine

3. Now switch to the Portland Oregon sheet and repeat these sort steps to find the snowiest day in Oregon. Check your answers with Figure 5.10.

Figure 5.10 Snowiest Days in Oregon

Skill Refresher

Sort a Column

1. Click on the filter Click arrow to the right of the header in the column you want to sort.

2. Click on the choice AZ! or ZA↓ to sort your data by that column.

Multi-level Sort

Sometimes you will need to sort your table by more than one column at a time in order to efficiently analyze your data. For example, if you were looking at several different types of loans from several bank offices, you would need to sort by the type of loan and then by bank office name to clearly see the different groups of loans. If you had a list of grades for students over their time in high school, you’d want to sort first by student name, but then also by grade level (freshman, sophomore, junior, and senior) so that each student’s grades would appear in chronological order.

For our weather data, let’s look at the snow days in Oregon and see how cold they were!

1. Click on the Portland OR sheet, then click on a cell in the table.

2. Click on the Data tab in the ribbon and then click the Sort button.

3. Click the down-arrow for Column and select Snow (inches).

4. Click the down-arrow for Order and select Largest to Smallest.

5. To add 2nd level sort, click on the Add Level button in the top left corner of the dialog box.

6. In the new Then by row, click the down-arrow for Column and select Low (°F).

7. In the same row, click the down-arrow for Order and select Smallest to Largest. Your dialog box should look like Figure 5.11.

Figure 5.11 Multi-Level Sort

8. Click OK. Your table sort results should look like Figure 5.12. Notice for the two days with 0.08 inches of snow, the low temp of 35.5 on Day 9 is displayed before the low temp of 36.2 on Day 25. The lowest of the two was listed first.  Also notice that the filter arrows changed on the sorted columns to show you how they are sorted.

Figure 5.12 Multi-Level Sort Results

Custom Sorts

In most cases, we want our data sorted in “typical” sort order: numbers sorted highest to lowest, words sorted alphabetically, etc. Some data in our everyday lives; however, does not make sense when sorted this way. For example, if you sorted the days of the week alphabetically, you’d get: Friday, Monday, Saturday, Sunday, Thursday, Tuesday, and Wednesday. This order would be of no use to anyone! Similarly, the months of the year would not make sense alphabetically. Can you think of a number that would not make sense in either highest to lowest or lowest to highest order? (This is a good brain teaser!)

In our weather data, we’ve added a column for the week in the Weekly OR sheet and changed the days to Sunday through Saturday. This sheet lets us further analyze Portland, Oregon’s data to see if there are weekly trends in the weather. Let’s see if we can sort the Weekly OR sheet by Week and then by Day.

1. Click on the Weekly OR worksheet.

2. Click on A5 and insert a table.

3. Click on Sort in the Data tab in the ribbon.

4. Click the down-arrow for Column and select Week.

5. Click the down-arrow for Order and select Smallest to Largest.

6. To add 2nd level sort, click on the Add Level button in the top right corner of the dialog box.

7. In the new Then by row, click the down-arrow for Column and select Day.

8. Click the down-arrow for Order and select Custom List. The dialog box in Figure 5.13 will appear on your screen.

Figure 5.13 Custom Lists

9. Click on Sunday, Monday, Tuesday, etc. in the Custom lists on the left-side of the dialog box. NOTE: Make sure you select the days of the week spelled out, not the abbreviations for the days of the week.

10. Click OK. Your Sort dialog box should look like Figure 5.14.

Figure 5.14 Sort Dialog Box

11. Click OK again. Your sorted table should now look like Figure 5.15. Notice the data is in Week order and, within each week, in Day order.

12. Save your work.

Figure 5.15 Custom Sort

Key Takeaways

- Tables are made up of adjacent rows and columns of data with a single row of column headings at the top.

- You can create a table by clicking in the top left-most cell in your data and selecting Table in the Insert tab of the ribbon.

- There are a gallery of styles, as well as, style options to choose from to format a table.

- When you need to add data, it is best to add it one row below the bottom of the table. You can then sort to reorganize your data.

- Freezing heading keeps your column headings displayed while you scroll through your table data.

- You can use the filter arrows in the table headings to sort by a single column. Use Sort in the Data tab in the ribbon to sort by two or more columns at a time.

- Custom Sorts can be used when data needs to be sorted in a special way (i.e. – Days of the Week).

Attribution

“5.1 Table Basics” by Diane Shingledecker, Portland Community College is licensed under CC BY 4.0

26

5.2 Intermediate Table Skills

Learning Objectives

- Filter table data.

- Add a total row to a table.

- Insert subtotals into a table.

FILTERING DATA

When you first create an Excel table, filter arrows appear in all the column headings. We have seen that you can use those arrows to sort your data by a single column. You can also use these same arrows to filter or limit the data you see by narrowing the displayed data within a column. There are many ways to filter data within a column depending on whether the data in the column is text or numeric. Table 5.5 gives you some filter examples:

Table 5.5 Filter Examples

Text Filters

Desired Results

Filter Column

Text Filter

Checkbox Selected

Data for the State of New Jersey (NJ)

State

Equals NJ

NJ

Data for Books that Have Gardening in Their Title

Title

Contains Gardening

Data for Weather on the Weekend

Day

Equals Saturday OR equals Sunday

Saturday and Sunday

Numeric Filters

Desired Results

Filter Column

Number Filter

Checkbox Selected

Data for Income Greater Than $1,000

Income

Greater than 1,000

Data for Amount Paid Equal to Zero

Amount Paid

Equals 0.00

0.00

Data for Mortgage and Auto Loans

Loan Type

Equals Mortgage OR equals Auto

Mortgage and Auto

Notice there are sometimes more than one way to filter data (i.e. – with a filter choice or a checked box). There are also single criteria filters, as well as, multi-criteria filters. We will explore all of these next.

To start filtering, let’s look at just the first week of data in the Weekly OR sheet:

1. Click on the Weekly OR sheet and click on a cell in the table.

2. Click the filter arrow to the right of the Week heading.

3. Click the Select All checkbox to deselect all of the checkbox choices.

4. Click on 1 to select Week 1.

5. Click OK.

Your table should look like Figure 5.16. You should see only 7 rows of Week 1 data in your table. Notice in your Status Bar at the bottom of your screen the message “7 of 31 records found”. Also notice that the filter arrow in the Week heading has changed to a funnel which indicates that this column is currently filtered.

Figure 5.16 Filter

To remove your filter:

1. Click the funnel next to the Week heading.

2. Select “Clear filter from Week”.

Skill Refresher

Filter a Column

1. Click the filter arrow to the right of the heading in the column you want to filter.

2. Click the Select All checkbox to deselect all of the checkbox choices.

3. Click on the checkboxes you want to filter by.

4. Click OK.

Un-Filter a Column

1. Click the funnel to the right of the heading in the column you filtered.

2. Select Clear filter.

Now let’s try a numeric filter. We want to find days in Portland ME when it’s warmer than 32 degrees in January:

1. Click in the Portland ME sheet, then click on a cell in the table.

2. Click on the filter arrow next to the High heading.

3. Click on Number filters, then select Greater than. The Custom AutoFilter dialog box will appear on your screen.

4. Enter 32 in the space to the right of “is Greater than”. Your Custom AutoFilter dialog box should now match Figure 5.17.

Figure 5.17 AutoFilter Dialog Box

5. Click OK.

You should see that it was only above 32 degrees three days in January in Maine – the first three! Check your table against Figure 5.18.

Figure 5.18 Filter Results

Let’s review sorting and filtering in the following steps:

1. Click on the Weekly OR sheet and clear the Day column filter.

2. Sort the table by Week (smallest to largest).

3. Filter the table to only show Mondays.

4. Compare your table results to Figure 5.19.

Figure 5.19 Filter Results

Filtering using the Slicer

Beginning in Excel 2013, slicers were added to the software as another way to filter your table data. A slicer is really useful because it clearly indicates what data is shown in your table after you filter your data.

Let’s try using the Slicer to filter our Portland OR data table:

1. Click on the Portland OR sheet and click in the table.

2. In the ribbon’s Table Tools Design tab, click Insert Slicer.

3. Click on Day in the Insert Slicers dialog box, and then click OK.

4. Drag the slicer so that the upper left-hand corner lines up with the top corner of cell G5.

5. Notice that when you insert a Slicer, a Slicer Options tab appears on the ribbon. This tab lets you change the style and size of the entire slicer or the individual slicer buttons.

6. Click on the Slicer options tab, then click on the More button next to Slicer Styles. The choices in Figure 5.20 will show on your screen.

Figure 5.20 Slicer Styles

1. Select the first choice under Dark.

2. In the Size group on the Slicer Options ribbon (NOT the Buttons group), change the width to 1”.

3. Click in the table and scroll down to Day 15 and click the 15 button to show only the data for January 15th in the table.

4. Hold down the CTRL key and click on the Slicer buttons for Days 10 through 14. Your table should now show the data from Days 10-15.

5. Sort the Day column in Ascending order to show the days in order as in Figure 5.21.

Figure 5.21 Slicer Results

Total Rows

By adding a total row to the bottom of your table, you can quickly see summary data for one or more of the columns in your table. Total rows can be added to tables as a whole, or those that are filtered. Total rows can easily be toggled on and off as the need for summary data arises.

1. Click on the Portland ME sheet and clear the filter from the High column.

2. Click on the Total Row check box in the Table Style Options group in the Table Tools Design tab in the ribbon.

3. Scroll to the bottom of your table to the Total Row. Notice the total for the Snow data.

4. Click on D37 (in the Rain column), and then click the down-arrow that appears to the right of the cell.

5. Choose Sum to add a sum to the Total Row in the Rain column.

6. To see the Average rainfall for the month of January, click on the arrow again and choose Average.

7. Repeat this step in E37 to see the Average snowfall.

8. Use the Decrease Decimal button in the Home tab of the ribbon to change the decimal places in D37 and E37 to 2. Compare your Total Row to Figure 5.22.

Figure 5.22 Total Row

1. Now switch to the Weekly OR sheet and see if you can successfully add a Slicer and Total Row to this table:

2. Clear the filter from the Day column.

3. Add a Slicer for the Day column to the sheet.

4. Move the top left corner of the slicer to H5. Resize it as needed and choose a Slicer Style.

5. Select Monday through Friday in the Slicer so that Saturday and Sunday data do NOT show in your table.

6. Add a Total Row that averages the High and Low columns. Your averages should be High: 47.0 and Low: 35.8. Change the label “Total” to “Average” by clicking A37 and typing Average.

Skill Refresher

Add a Total Row

1. Click on the Total Row check box in the Table Style Options group in the Table Tools Design tab in the ribbon.

2. Scroll to the bottom of your table to find the Total Row.

3. Click in one of the columns in the Total Row, and then click the down-arrow that appears to the right of the cell.

4. Choose Sum to add a sum to the Total Row in the column.

5. To see the Average for column, click on the arrow again and choose Average.

Some other choices in the Total Row are Count (for words), Count Numbers, Max, and Min.

Skill Refresher

Add a Slicer

1. Click on Insert Slicer in the Table Tools Design tab in the ribbon.

2. Check the box for the column to which you want to add a Slicer.

3. Click OK.

Subtotaling

You can automatically calculate subtotals and grand totals in a table for a column. This is a powerful tool that allows you to quickly display multiple levels of summary data within your table. This can provide Management with a report of higher level summary data one minute, and then can be easily switched back to detailed data the next minute. It is important to save often during this process and follow the steps carefully. It is recommended that you make a copy of the data you want to subtotal and place it in a new sheet, so that you can save the summary subtotaled data separately if desired.

In order to subtotal successfully, you always need to do the following in order:

1. Sort by the column you want to subtotal on.

2. Convert the table back to a normal Excel range. You cannot subtotal inside a table.

3. Subtotal in the Data tab in the ribbon.

4. If you want to limit your displayed data further, Filter in the Data tab in the ribbon.

We want to find out what the weather looks like for each day of the week, so we’ll need to save our data to a new sheet, sort by the days of the week, and then convert the table in order to get ready to see the subtotal.

1. Click on the Weekly OR sheet.

2. Point at the Weekly OR sheet tab at the bottom of the screen, hold the CTRL key down, and left-drag the sheet to the right until you are past all the existing sheets.

3. When you see a sheet icon with a + sign, let go of the mouse button and then the CTRL key. A Weekly OR (2) sheet will appear.

4. Right-click on the new Sheet tab, select Rename, type Subtotal OR, and then press ENTER.

5. Save your file before you start Subtotaling!

6. Remove all filters in the table by clicking the Data tab and then choosing Clear.

7. Now we want to Sort the table by the Day column using a Custom Sort in the Sort button in the ribbon to sort in the order Sunday, Monday, Tuesday, etc. (See Figure 5.13 through 5.15 for a review of Custom Sorting.)

8. Before you can subtotal, you must convert your table back to a regular range. To do this, click Convert to Range in the Table Tools Design tab on the ribbon. (See Figure 5.23)

Figure 5.23 Convert to Range

9. When asked if you want to convert the table, click Yes.

10. Because your data is no longer formatted as a table, your slicer will disappear; and you will no longer have access to the Table Tools Design tab in the ribbon.

11. Under the Data tab in the ribbon, click Subtotal.

12. In the Subtotal Window, make the choices shown in the Figure 5.24. It is essential that you select the column you sorted by in the “At each change in” field at the top of the window. Click OK.

Figure 5.24 Subtotal Window

Your data should look like Figure 5.25. Successful subtotaling shows only one subtotal for each group in the column you sorted by. (HINT: If you end up with more than one Subtotal for the same group (i.e – one of the days of the week in our example), you did not sort before subtotaling. Remove your subtotals using the Remove All button in Figure 5.24, sort your table, and then try subtotaling again.)

Figure 5.25 Subtotal Results

Notice the three Outline buttons circled in the upper-left corner of the spreadsheet. These allow you to control the amount of subtotaled data that is displayed. Table 5.5 describes the different Outline buttons.

Table 5.5 Subtotal Outline Buttons

Button

Content Displayed

Level 1

Only grand total

Level 2

Subtotals and grand total

Level 3

Individual records, subtotals, and grand total

Let’s try the three Outline buttons to see the difference in the data displayed:

1. Click on the 1 Outline button in the upper left-hand corner of the sheet.

2. You should see only the Grand Average row with averages for High, Low, Rain, and Snow.

3. Click on the 2 Outline button.

4. Now you’ll see the average for each day of the week along with the Grand Average.

5. Click on the + Sign button to the left of the Sunday Average row.

6. This expands just the Sunday Day data and displays the individual records for this subset of the data. Clicking on + Sign buttons will expand a portion of the data at a time. Clicking on – Sign buttons hide a portion of the data at a time.

7. Click on the 3 Outline button.

8. All the individual records along with the subtotals, and Grand Average should be displayed.

9. Save your Excel file.

Key Takeaways

- Filtering is an easy way to see a subset of your data. Filtering arrows appear to the right of each column heading when you insert a table with a header row.

- You can filter by text or numerically.

- A slicer is another way to filter in Excel that provides a set of filtering buttons on your sheet.

- Adding a total row to a table is a quick, efficient way to see summary statistics for one or more columns in a table.

- Subtotaling provides a way to quickly add totals to groups within a column along with providing a grand total at the bottom of the table.

- Subtotal Outline buttons allow users to see add of the subtotaled data, just the totals and grand total, or simply the grand total.

- Plus and minus buttons within subtotaling allow a user to expand and hide portions of the subtotaled data.

Attribution

“5.2 Intermediate Table Skills” by Diane Shingledecker, Portland Community College is licensed under CC BY 4.0

27

5.3 Preparing to Print

Learning Objectives

1. Review options for professional page setup for printing.

2. Understand how to insert a picture to enhance the visual appearance of a worksheet.

3. In this section, we will preview the worksheets containing tables to ensure they will print in a professional manner. We will make any formatting and page setup changes that are needed, as well as add a picture to one of the worksheets to enhance its appearance.

Previewing a Worksheet

Data file: Continue with CH5 National Weather

Now that the weather data has been sorted, filtered, and subtotaled as needed, it is time to print the worksheets. You are going to start with the Portland ME worksheet.

1. Click on the Portland ME worksheet. If needed, use Ctrl-Home to move to cell A1.

Notice that cells A1, A2, and A3 are not merged and centered over the entire table of data. To fix this, you need to unmerge each of the merged cells, and then merge them again, making sure to include E1, E2, and E3 in the selection.

1. Select cell A1 and click the Merge & Center button. This should split A1 into four cells (A1:D1).

2. Select the range A1:E1 and click the Merge & Center button. Cell A1 should now be merged across A1:E1.

3. Repeat steps 1 and 2 for A2 and A3.

Next you need to preview the worksheet in Print Preview and determine what page setup options need to be set.

1. Go to Backstage view and select Print from the menu.

Notice that the table is to the far left of the page, with quite a bit of white space on the left. You decide that it would look better centered on the page.

1. In the Settings section, click the link for Page Setup. This opens the Page Setup dialog box.  See Figure 5.26.

Figure 5.26 Page Setup

2. Click on the Margins tab.

3. In the Center on page section, check the box for Horizontally.

4. Click OK. The table should now be centered horizontally on the page.

5. Next you need to add a footer with the workbook filename as well as the worksheet name.

6. Open the Page Setup dialog box again (see Step 1 above).

7. Click the Header/Footer tab then click the Custom Footer button.

8. In the Left section: box type File:.

9. Making sure to leave a space after the colon, click the Insert File Name button.

10. In the Right section: box type Worksheet:.

11. Making sure to leave a space after the colon, click the Insert Sheet Name button.

12. The Footer dialog box should look like Figure 5.27. Click the OK button twice to return to Print Preview. Confirm that the footer appears correctly, then exit Backstage View.

Figure 5.27 Custom Footer

Inserting an Image to Enhance a Worksheet

Next you are going to add a small weather related graphic to the worksheet to enhance its appearance. In Excel you can either insert an image file that you have saved or you can search for one online within Excel. In this example, there is a graphic saved in the data files for this chapter that you will use.

1. Click the Insert tab on the ribbon.

2. Click the Pictures button from the Illustrations group. (This allows you to insert an image you have saved. If you wanted to search for an image online, you would click the Online Pictures button.) (See Figure 5.28.)

Figure 5.28 Insert Pictures

3. Navigate to the location where your data files for Chapter 5 are located and double-click on the Weather image file.

The image now appears on your worksheet, but not in the location you want. It is also slightly larger than you would like.  (See Figure 5.29.) You are going to move the image to cell E1, then resize it so it does not cover up part of the table.

Figure 5.29 Inserted Image

1. Place your pointer in the image so that the  appears. Drag the image so that the top left corner is in cell E1.

2. Using the resizing handle in the bottom right corner of the image, resize the image so that it does not cover any of the table. Hint: drag diagonally to the left and up.

3. Check Print Preview again to make sure the worksheet with the image added looks good.

4. Exit Backstage View and save the Excel file.

Previewing the Remaining Worksheets

Before considering this workbook complete finished, you need to confirm that the remaining worksheets are all printing appropriately.

1. Click the Portland OR worksheet and go to Print Preview. No changes need to be made to this worksheet. Exit Backstage View.

2. Click the Weekly OR worksheet and go to Print Preview. Notice that the Slicer is printing on a second page. To fix this, set the Page Scaling to Fit All Columns on One Page.

Notice that the last Slicer button (Saturday) is being cut off. This is because the Slicer height needs to be adjusted.

1. Exit Backstage View.

2. Resize the Slicer so that all of the buttons display.

3. Return to Print Preview and confirm the worksheet, including the slicer, is printing appropriately. Exit Backstage View.

4. Click the Subtotal OR worksheet and go to Print Preview.

5. Using the Page Setup dialog box, center this worksheet horizontally on the page.

6. Exit Backstage View.

7. Save the CH5 National Weather workbook.

8. Compare your work with the self-check answer key (found in the Course Files link) and then submit the CH5 National Weather  workbook as directed by your instructor.

Key Takeaways

- When working with Excel workbooks, the final step should always be to review the worksheets in Print Preview to make sure they are printing appropriately.

- You can add images you have saved, or images you find online, to a worksheet to enhance its appearance. Be sure to resize and move them appropriately so they do not detract from the data.

Attribution

“5.3 Preparing to Print” by Julie Romey, Portland Community College is licensed under CC BY 4.0

28

5.4 Chapter Practice

Tables for a Tourism Company

Download Data File: PR5 Data

Figure 5.30 Completed Exercise

Travel and tour companies need to keep track of client data, as well as, travel/tour options and tour guides. Keeping up-to-date, accurate records is essential to their bottom line. To run a tour company, employees must be able to manipulate their data quickly and easily. This exercise illustrates how to use the skills presented in this chapter to generate the data needed on a daily basis by a tourism company. See Figure 5.30 above.

1. Open the data file PR5 Data and save the file to your computer as PR5 Canyon Trails.

2. In Column J, calculate Total Cost (number of Guests *Per Person Cost). Copy the formula down the column.

3. Format Columns I and J with Currency and no decimal places.

4. Center all headings in Row 3.

5. Click in cell A3. Insert a table with headers for the range A3:J53.

6. Adjust column widths within the table so that all the headings are completely visible.

7. Rename Sheet 1 Current Tours. Sort this sheet alphabetically (A to Z) by Last Name.

8. Make a copy of the Current Tours sheet and rename it Tours by Canyon.  Place the Tours by Canyon sheet to the right of the Current Tours sheet. Sort this sheet by Tour Canyon (A to Z), then Home Country (A to Z), and then Last Name (A to Z).

9. Make another copy of the Current Tours sheet and rename it US Guests. Place the US Guests sheet to the right of the Tours by Canyon sheet.  Filter this sheet so that only guests with a Home Country of the United States show. Sort the filtered data alphabetically (A to Z) by Tour State. Add a Total Row that sums the Guests and Total Cost columns.

10. Make another copy of the Current Tours sheet and rename it European Guests.  Place the European Guests sheet to the right of the US Guests sheet. Hide the Average Age column.

11. Insert a slicer in the European Guests sheet for Home Country. Move the top left corner of the slicer to the top left-hand corner of cell K3. Change the width of the entire slicer to 1.65”.

12. Select both Germany and the United Kingdom on the slicer. Sort the filtered sheet by Home Country (A to Z) and then Last Name (A to Z).

13. Make one more copy of the Current Tours sheet and rename it Tours by State. Place the Tours by State sheet to the right of the European Guests sheet. Subtotal the sheet by State, summing the Total Cost column.

14. Change the name of the Tours by State sheet to 5-7 Day Tours by State. Filter out 3 day tours in the table.

15. On each worksheet, make the following print setup changes:

1. Add a footer with the worksheet name in the center.

2. Change to Landscape Orientation

3. Set the scaling to Fit All Columns on One Page

16. For any worksheets that print on more than one page, add Print Titles to repeat the first three rows at the top of each page.

17. Save the PR5 Canyon Trails workbook.

18. Make sure your sheets are in the following order from left to right: Current Tours, Tours by Canyon, US Guests, European Guests, and 5-7 Day Tours by State.

19. Compare your work with the self-check answer key (found in the Course Files link) and then submit the PR5 Canyon Trails workbook as directed by your instructor.

Attribution

“5.4 Chapter Practice” by Diane Shingledecker, Portland Community College is licensed under CC BY 4.0

“Canyon Trails Data File” by Matt Goff is licensed under CC BY 3.0

29

5.5 Scored Assessment

Tables for a Retail Company

Download Data File: SC5 Data

Figure 5.31 Completed Exercise

Retail companies with today’s online, as well as, in-store sales have a lot of data to keep track of! Keeping track of sales, costs, and profits on a daily basis is essential to making the most of a business. This exercise illustrates how to use the skills presented in this chapter to generate the data needed on a daily basis by a retail company.  See Figure 5.31 above.

1. Open the data file SC5 Data and save the file to your computer as SC5 Dynamite Customer Sales.

2. Click on the Sales sheet. In I4 , enter a VLOOKUP function that will find the Product Price for the Product in E4 in the table in the Product Table sheet and return it to I4. In your VLOOKUP function, fill in the required parameters using Figure 5.32 below. Copy the VLOOKUP function down column I.

Figure 5.32 VLOOKUP window

3. In J4 , enter a VLOOKUP function that will find the Product Cost for the Product in E4 in the table in the Product Table sheet and return it to J4. This VLOOKUP function will be the same as the VLOOKUP function in I4 – EXCEPT THE COL_INDEX_NUM will be 3 instead of 4. Copy the function down column J.

4. In K4, calculate Profit (Product Price – Product Cost). Copy this formula down column K.

5. Format columns I, J, and K as currency with two decimal places.

6. Click in cell A3. Insert a table with headers for the range A3:K52. BE CAREFUL HERE: Excel will try to insert a table starting with A2. You want to make sure your range starts with A3 here.

7. Make a copy of the Sales sheet and rename it Online Sales by Date. Place this sheet to the right of the Sales sheet. Filter out Retail in Sales Type, so that only Online Sales are displayed. Sort the filtered data by Date Sold (oldest to newest).

8. Make a copy of the Sales sheet and rename it June Sales by Country. Place this new sheet to the right of the Online Sales by Date sheet. Filter this sheet to only show June dates by using the Date Filter Between. Sort this sheet alphabetically (A to Z) by Country and then alphabetically by Name.

9. Make another copy of the Sales sheet and rename it Sales by Product. Place this new sheet to the right of the June Sales by Country sheet. Hide the Region column.

10. Insert a slicer in the Sales by Product sheet for Product Sold. Move the top left corner of the slicer to the top left-hand corner of cell M1. Resize the height of the entire slicer to 2.09”.

11. Select both DETA100 and DETA200 in the slicer. Sort the filtered sheet by Product Sold. Add a Total Row that includes the overall average for the Product Price, Product Cost, and Profit columns. Change the heading in A53 to Average.

12. Make a copy of the Sales sheet and rename it Subtotals by Date. Place this new sheet to the right of the Sales by Product sheet. Subtotal the sheet by Date (Oldest to Newest), summing the Profit column. Click the 2 Outline button to show just the subtotals by date and the grand total.

13. Make one final copy of the Sales sheet and rename it Subtotals by Type. Place this new sheet to the right of the Subtotals by Date sheet. Subtotal the sheet by Sales Type, summing the Profit column.

14. Add a 2nd subtotal to the Subtotals by Type sheet that subtotals by Type and averages the Profit column. (Hint: uncheck Replace Current Subtotals in the Subtotal dialog box.) Notice that 4 Outline buttons appear with the 2nd subtotal. Figure out which Outline button to click to display both subtotals for Online and Retail and two Grand Totals.

15. For each worksheet, add a footer with the worksheet name in the center.

16. Preview each worksheet in Print Preview and make any necessary changes for professional printing. (Hint: Orientation, page scaling, and print titles might need to be used)

17. Double-check that your sheets are in the following order from left to right:  Sales,  Online Sales by Date, June Sales by Country, Sales by Product, Subtotals by Date, Subtotals by Sales Type, and Product Table.

18. Save the SC5 Dynamite Customer Sales workbook.

19. Submit the SC5 Dynamite Customer Sales workbook as directed by your instructor.

Attribution

“5.5 Scored Assessment” by Diane Shingledecker, Portland Community College is licensed under CC BY 4.0

VI

Chapter 6 – Multiple Sheet Files

Excel workbooks often contain a large amount of data, and worksheets can quickly become overwhelming.When one worksheet becomes cumbersome, data can be broken out into smaller subsets and placed in separate worksheets within the same Excel file. Separating out spreadsheet data into smaller pieces can lead to better data organization within a file and increase its ease of use.When a retail company needs to track overall sales, as well as, individual store sales, it makes sense to place each store’s sales data in a separate sheet within a file. Adding a summary sheet that sums across all the sheets will allow for total company sales data in the same file! This chapter will show how to set up a workbook to make multi-sheet formulas quick and easy.

Other examples of when multiple sheets make the most sense are when you are comparing regional data, data for a salesforce and wish to evaluate individual salesperson performance along with overall sales, and data over a period of time where sheets can be broken out by year or by month. When comparing data across several sheets, it is essential that all the sheets are laid out in the same way. To facilitate this, a template can be used. A template is the basic pattern for each new sheet that can be used repeatedly to make sure each new sheet has the same setup, formatting, formulas, etc. as the existing sheets in a file. In this chapter, we will use both pre-made, Microsoft Excel templates, as well as, ones we will create from scratch to meet the specific needs of our work.

Attribution

Chapter 6 – Multiple Sheet Files by Diane Shingledecker, Portland Community College is licensed under CC BY 4.0

30

6.1 Multiple Sheet Basics

Learning Objectives

1. Navigating through a multiple sheet file.

2. Adding, deleting, copying, and moving sheets.

3. Grouping and ungrouping sheets.

Throughout our textbook, we have worked with Excel files that have included multiple sheets. Depending on the version of Excel you are using, a new Excel file starts with several sheets. In this chapter, we will be working with a personal budget file that contains income and expenses for an entire year. Our file contains a sheet for each month of the year, as well as, a Summary sheet that will add all twelve monthly sheets of data together. To begin with, we’ll get comfortable with moving through the sheets, organizing them, and making sure that all twelve monthly sheets are consistent.
Figure 6.1 shows the January sheet in the Personal Budget file along with all the sheet tabs along the bottom of the window.

Figure 6.1 Personal Budget File

Navigating Through a Multiple Sheet File

Download Data file: CH6 Data

1. Open the data file CH6 Data and save the file to your computer as CH6 Personal Budget. Notice that the file has a Expenses Summary sheet at the far left followed by monthly sheets.

2. Click on the different sheets at the bottom of the screen to move through the sheets. Notice that the Expenses Summary sheet is formatted differently from the monthly sheets. Notice also that all the monthly sheets are identical in layout and format.

3. Take a second look at the months at the end of the year. Not all the data has been entered for September through October… and there is no sheet for December. We will enter the remaining data and add an additional sheet for December.

4. Add the following data in the September, October, and November sheets:

September

October

November

Power

$135

$135

$135

Water

$30

$30

$30

Groceries

$300

$325

$400

Miscellaneous

$100

$50

$100

Bonus

Freelance

$500

$150

Other

$100

Copying a Sheet

1. To make a December sheet, we are going to copy the November sheet.

2. Point your mouse at the November sheet tab at the bottom of the screen.

3. Hold down your left mouse button and then press and hold down the CTRL key.

4. At this point, you will see a black down-pointing arrow to the left of the November sheet tab and your mouse cursor will become a small piece of paper with a plus sign on it.

5. Drag your mouse to the right (still holding down the left-mouse button and the CTRL key) until the black down-pointing arrow is to the right of the November sheet tab.

6. Let go of the mouse button and then the CTRL key. You should now have a November (2) sheet to the right of the November sheet as shown in Figure 6.2.

Figure 6.2 Additional November Sheet

Next, we’ll update the November (2) sheet to turn it into our December sheet.

1. Right-click on the November (2) sheet name at the bottom of the screen and choose Rename.

2. Type “December” and press Enter.

3. Click on the December sheet.

4. Click on B1 and change “November” to “December”.

5. Make the following data changes:

- Miscellaneous: $300

- Bonus: $250 (it’s the holidays!)

- Freelance: delete amount

6. Save your work.

7. Point your mouse at the December sheet tab at the bottom of the screen.

8. Hold down your left mouse button and then press and hold down the CTRL key.

9. Drag your mouse to the right (still holding down the left-mouse button and the CTRL key) until the black down-pointing arrow is to the right of the December sheet.

10. Let go of the mouse button and then the CTRL key. You should now have a December (2) sheet to the right of the December sheet.

11. Rename the December(2) sheet Practice.

Skill Refresher

Copying a Sheet

1. Point your mouse at the sheet you want to copy at the bottom of the screen.

2. Hold down your left mouse button and then press and hold down the CTRL key.

3. Drag your mouse to the right (still holding down the left-mouse button and the CTRL key) until the black down-pointing arrow is to the right of  your existing sheet.

4. Let go of the mouse button and then the CTRL key. You should now have a Sheetname (2) to the right of the original sheet.

5. Rename the Sheetname (2) sheet as desired.

Moving and Deleting Sheets

Sometimes your sheets do not end up in the right order, and you need to move them in order to fix this. Let’s try moving our “Practice” sheet to see how this is done.

1. Point to the Practice sheet and hold down your left mouse button.

2. Notice this time that there is still a black arrow to the left of the Practice sheet, but the piece of paper is blank. It does not have a plus sign (+) because we are moving, instead of copying, the sheet.

3. Left-drag the mouse to the right until the black arrow marker is between the October and November sheets.

4. Release the mouse button.

5. Try moving the Practice sheet back to the right of the December sheet.

Since our Practice sheet is not a sheet we will need in our Budget file, we’ll go ahead and delete it now.

1. Right-click on the Practice sheet tab at the bottom of the screen.

2. Click Delete. Figure 6.3 shows the warning message box that will appear on your screen.  Your message box might look slightly different depending on the version of Excel you are using. It is important to note that you cannot Undo once you delete a sheet!

Figure 6.3 Warning Message Box

3. Click Delete.

Grouping and Ungrouping Sheets

Take a look at our monthly sheets again. Notice that there is a place in each of these sheets to calculate three pieces of Summary data: Income, Expenses, and Balance; but there aren’t any formulas in these cells. There is also a place for the % of Income Spent, but we need a formula in I6:I7 to calculate this. If we entered these formulas individually in each of the 12 month sheets, it would take a long time! Because this task would be very repetitive, it would also be fairly likely that we would make some mistakes along the way entering the same formulas over and over again. By grouping all the month sheets together, we can enter each of the formulas once and have them appear in all the sheets.

1. Click on the January sheet to make it active.

2. Hold the SHIFT key down and click on the December sheet.

Now all 12 sheets should be selected. You can tell this in two ways: the sheet tabs that have been selected are now bold at the bottom of your screen. Notice in the Title bar at the top of the screen the word [Group] added to the end of the title. You can see both of these in Figure 6.4.

Figure 6.4 Grouped Sheets

IT IS IMPORTANT TO REMEMBER THAT ANY CHANGES WE MAKE TO THE JANUARY SHEET WILL BE MADE TO ALL THE SHEETS!! This is a very good thing when we want to make changes to all the sheets at once, but we need to be sure to ungroup them when we’re done making these changes.Let’s go ahead and add the formulas to all twelve of the sheets at once:

1. Click in F11 in the January grouped sheet.

2. Enter the formula =SUM(F5:F8).

3. In F12, enter the formula =SUM(C5:C13).

4. In F13, subtract Expenses from Income. In the January sheet, your balance should be $690. HINT: if your answer is negative, you subtracted Income from Expenses.

5. Click on I6. (I6 and I7 are formatted and merged together – this is fine.)

6. Enter a formula that divides Expenses (F12) by Income (F11). Your answer will show as a percentage since this cell has already been formatted to do this. HINT: If you percentage is greater than 100%, you have your numbers reversed.

Notice that a data bar was set up in I5 to visually show the income spent. Do you remember how to do this from earlier in our textbook? Your January sheet should now look like Figure 6.5.

Figure 6.5 January Sheet with Formulas

7. Now that we are done making changes to all the monthly sheets at once, we need to ungroup them. Right-click on one of the grouped sheets and choose Ungroup Sheets.

Notice the sheets tabs are no longer bold and the word [Group] is no longer in the title bar.

8. Click on several of the month sheets to see that all the formulas have been added.

9. Click on the December sheet. Your sheet should now look like Figure 6.6.

Figure 6.6  December Sheet with Formulas

1. Take a look at the Notes in the September sheet. It says that the rent was raised in September, so we need to cancel our Gym Membership and show $0 for the Gym amount in October, November, and December.

2. Group the October, November, and December sheets. If you do this successfully, these three sheet names should be bold and the word [Group] will appear in the Title bar.

3. Click on C13 and change the amount to $0. Press Enter.

4. Ungroup the sheets.The balances should be: October $605, November $530, and December $430.

Skill Refresher

To Group Sheets:
Click on the leftmost sheet you want to group; then hold the SHIFT key down and click on the rightmost sheet you want to group.

To Ungroup Sheets:
Right-click on one of the grouped sheets and choose Ungroup Sheets.

Key Takeaways

- You can easily move, copy, delete, and rename sheets in your Excel file.

- Grouping sheets allows you to change a group of identically formatted sheets at the same time.

Attribution

“6.1 Multiple Sheet Basics” by Diane Shingledecker, Portland Community College is licensed under CC BY 4.0

31

6.2 Formulas with 3-D References

Learning Objectives

- Entering formulas that reference another sheet.

- Using the SUM function to add up multiple sheets.

The Summary sheet in many multiple sheet workbooks is utilized to present totaled information from the other sheets in the file. This is done to give a quick synopsis of all the other sheets in one convenient location. For this reason, the Summary sheet is usually the first sheet in multiple sheet files. Summary sheets “pull” data from the other sheets using three-dimensional (3-D) cell references. In order to distinguish between A3 in the Summary sheet, A3 in the January sheet, A3 in the February sheet, etc.; a 3-D cell reference includes the sheet name along with the cell reference. The syntax to reference a cell in a different sheet is =SheetName!CellRange. So, the cell reference for A15 in the March sheet would be =March!A15.

Let’s start working on our summary sheet by trying out some 3-D formulas:

1. Click on the Expenses Summary sheet tab at the bottom of the screen.

2. Click on C5 and enter the formula =January!C5.  Press Enter.
This will get the amount $700 from cell C5 in the January sheet.

3. Delete the formula in C5 in the Expenses Summary sheet.

4. This time, click on C5 and type =. Then click on the January sheet, and then click on C5.

5. Press Enter. This will put the same formula, =January!C5, in cell C5 in the Expenses Summary sheet and will return the value $700.

6. In cell C6 in the Expenses Summary sheet, try entering a formula for the Power amount in the April sheet. You should get $135 as the Power amount.

7. Delete the formulas in cells C5 and C6 in the Expenses Summary sheet.

For the Annual Amounts in C5:C13 in the Expenses Summary sheet, we don’t need the amount from a single month’s sheets; instead, we need the sum of all the entries in all the monthly sheets. So, we need to sum three-dimensionally through all twelve month sheets. Here’s a helpful hint on the steps you need to follow to add through multiple sheets:

Skill Refresher

To SUM across sheets:

1. Click on the cell where you want the 3-D SUM to appear.

2. Type =SUM(

3. Click on the leftmost sheet in the group of sheets you want to sum.

4. Hold the SHIFT key down and click on the rightmost sheet in the group of sheets you want to sum.

5. Click on the cell in the sheet you’re in that you want to sum.

6. Press ENTER.

Let’s try adding up all the monthly amounts in our Expenses Summary sheet:

1. Click in C5 in the Expenses Summary sheet.

2. Type =SUM(. (Make sure to type the open parentheses!)

3. Click on the January sheet.

4. Hold the SHIFT key down and click on the December sheet.

5. Click on C5 again and press ENTER. Cell C5 should display the sum amount of $8,400.

6. Click on C5 in the Expenses Summary sheet. In the formula bar, you should see the following formula: =SUM(January:December!C5). This means SUM C5 in the sheets January through December.

7. Let’s try another 3-D SUM together. Click on C6.

8. Type =SUM(. (Make sure to type the open parentheses!)

9. Click on the January sheet.

10. Hold the SHIFT key down and click on the December sheet.

11. Click on C6 again and press ENTER. Cell C6 should now display the sum amount of $1,610.

12. Click on C6 in the Expenses Summary sheet. In the formula bar, you should see the following formula: =SUM(January:December!C6).

If you feel comfortable with these 3-D formulas, you can copy C6 down through C13 to fill in the rest of the formulas. If you’re not quite comfortable yet, keep practicing the above steps to add 3-D formulas to cells C7:C13. When you’re done, your Expenses Summary sheet should match Figure 6.7.

Figure 6.7  Complete Expenses Summary Formulas

While our 3-D formulas are complete in the Expenses Summary sheet, our summary feels like it is lacking something. Let’s add a visual representation of our summary numbers to the sheet.

1. Highlight cells B5:C13 in the Expenses Summary sheet.

2. Click on Pie Chart in the Insert tab in the ribbon and select the 2-D pie.

3. Move and resize the pie chart so that it fills cells D3:J15.

4. Delete the chart title.

5. Move the legend to the right side of the chart. Resize the legend as needed.

6. Add percentage data labels to the pie slices. Format the data labels to be bold with white font color. Your complete Expenses Summary sheet should look like Figure 6.8 below.

7. Save your file. If you’re printing your assignment at this point, print ONLY the Summary sheet in regular and formula view. Close your file.

Figure 6.8 Completed Expenses Summary Sheet

Skill Refresher

3-D References in Formulas

To reference a cell in another sheet, use the formula syntax =SheetName!CellAddress.

To enter a 3-D reference:

1. Click on the cell where you want the formula to appear and type =.

2. Click on the sheet with the cell you want.

3. Click on the cell in the sheet you want and press ENTER.

Key Takeaways

- 3-D references in formulas allow you to use data from one or more sheets on another sheet.

Attribution

“6.2 Formulas with 3-D References” by Diane Shingledecker, Portland Community College is licensed under CC BY 4.0

32

6.3 Templates

Learning Objectives

1. Use an existing Microsoft Excel template to create a new spreadsheet.

2. Create a custom template to create new spreadsheets.

A template is a predefined pattern for a spreadsheet that has already been created for you. Hundreds of templates, already created by Microsoft, are available for you to use inside Excel. These templates are very helpful if you have limited time to get a new task done in Excel, and you don’t know where to start. Templates do a lot of the work for you! Templates include all the formulas, formatting, etc. needed in a professional Excel spreadsheet. All that’s left to do is enter the data. Predefined Microsoft templates include everything from billing statements to blood pressure trackers to business cards. Categories include: Business, Personal, Industry, Financial Management, Logs, Calculators, and Lists.

Sometimes you need a very specific template that hasn’t already been created by Microsoft. Taking the time to create your own template will allow you to use this spreadsheet pattern to create files from it over and over again. If you need to create a new version of a spreadsheet on a regular basis, templates will make this work much easier. In this chapter we will explore using existing Microsoft templates, as well as, creating our own templates.

Let’s start by trying out a predefined, Microsoft template:

1. Click the File tab in the ribbon.

2. Click New in the Backstage View.

3. Click in the Search box for Online template.

4. Type Travel and press ENTER.

5. Click on the Travel Expense Report and click Create.  NOTE:  If this template is not available, ask your instructor which template you should choose.

Your screen should look like Figure 6.9 below. Notice the design, layout, and formulas have already been set up for you.

Figure 6.9 Travel Expenses Report Template

Try using the template by doing the following:

1. Change the Name to your name.

2. Change the Department to CAS.

3. Press CTRL+~ to see where the formulas are in the sheet.  Working in the formula view helps you see where the formulas are, so you won’t delete them.

4. In formula view, carefully delete just the data. Don’t delete any formulas!

5. Press CTRL+~ again to return to Normal view.

6. Enter dates and expenses for a trip of your imagining in the first three rows under the column headings.

7. Save the completed file as CH6 Travel Expenses. Close the file.

Skills Refresher

To use a Microsoft predefined template:

1. Click on the File tab in the ribbon.

2. Click on New

3. Type the desired template description in the Search box, and press ENTER.

Now let’s shift to creating our own template. Sometimes you create a blank template first and then create spreadsheets from it. Other times, you have an existing spreadsheet that you realize you need a template for, so that you can recreate the file with new data over and over again. We’ll turn our existing CH6 Personal Budget file into a template now, and in the assignments at the end of the chapter, we’ll start templates from scratch before a filled-in spreadsheet is created.

1. Open your CH6 Personal Budget.xlsx file.

2. Group the month sheets (January through December).

3. Press CTRL+~ to switch to Formula view.

4. We only want to delete data from these sheets – not labels or formulas. The only data is in C5:C13, F5:F8, and in the Notes in H11:J13.

5. Highlight C5:C13 (with all the sheets still grouped) and press DELETE.

6. Highlight F5:F8 (with all the sheets still grouped) and press DELETE.

7. Highlight H11:J13 (with all the sheets still grouped) and press DELETE.

8. Press CTRL+~ to switch back to Normal view.

9. Ungroup the sheets. Look through the sheets to check that only the data has been deleted. Notice the error message #DIV/O appears in I6:I7 since the data for this formula has been deleted. Your January sheet should look like Figure 6.10.

Figure 6.10 January Template Sheet

NOTE: There are only formulas and the pie chart in the Expenses Summary sheet, so nothing needs to be deleted from this sheet to setup your template.

1. Click the File tab in the ribbon and then click Save As.

2. Choose the location where you want to save the file.

3. In the Save as type pull-down list, select Excel Template (*.xltx).

4. At the top of the screen, double-check that the location you want to save the file to has not changed. If it has, use the pull-down list to find the location where you want to save your file. BE CAREFUL HERE! By default, Excel will try to save this to a default template file location on your hard drive.

5. Type in the file name CH6 Personal Budget Template.xltx. Check your screen carefully with Figure 6.11.  Keep in mind that you may be saving your template file to a different place on your computer.  By default, Excel will save the template to your hard drive assuming you always work on the same computer.

Figure 6.11 Save As Template

6. Click Save.

Skills Refresher

To save a file you created as a template:

1. Click the File tab in the ribbon and then click Save As.

2. Choose the location where you want to save the file.

3. In the Save as type pull-down list, select Excel Template (*.xltx).

4. At the top of the screen, double-check that the location you want to save the file to has not changed. If it has, use the pull-down list to find the location where you want to save your file. BE CAREFUL HERE! By default, Excel will try to save this to a default template file location on your hard drive.

5. Type in the file name

6. Click Save.

We are now going to use our new budget template to start a Personal Budget file for 2017. We want to use the Ch6 Personal Budget Template to create the new file, but we don’t want to overwrite the template. We want to keep it clean to use to start each new year’s file. To do this, we’ll save the file to our new 2017 file name before we start filling in any data.

1. With the CH6 Personal Budget Template open, click the File tab in the ribbon.

2. Choose Save As and choose the location where you want to save the 2017 version of the file.

3. Change the Save as Type back to Excel Workbook (*.xlsx).

4. Enter the File name CH6 2017 Personal Budget. Compare your screen to Figure 6.12.

Figure 6.12 Save As 2017 Budget File

5. Click Save.

6. Group all the sheets together including the Expenses Summary sheet.

7. Click on H1. Type 2017 and press ENTER.

8. Ungroup the sheets.

9. Click on the January sheet. Enter the following data in Figure 6.13:

Figure 6.13 January 2017 Data

10. Click on the Expenses Summary sheet – the data and the pie chart should show the January data since that is all the data in the twelve month sheets for now.Your sheet should look like Figure 6.14.Figure 6.14 Expense Summary Sheet

11. Save your file.

Key Takeaways

- There are many pre-designed templates in Excel developed in Excel that you can use.  This will save you the time and effort of designing and creating these files from scratch.

- You can create your own template files in Excel that you can use over and over again.

Attribution

“6.3 Templates” by Diane Shingledecker, Portland Community College is licensed under CC BY 4.0

33

6.4 Preparing to Print

Learning Objectives

1. Printing all of the worksheets in a workbook at one time.

2. Preparing multiple worksheets for printing using grouping.

Just like consistency in formatting is important when working with workbooks containing multiple worksheets with the same type of data, so is consistency in page setup. Now that the Personal Budget 2017 workbook is complete, you are going to prepare it for printing by changing the page orientation and adding a header. You will also print all 13 worksheets at one time.

Applying Page Setup Options to Grouped Worksheets

Data file: Continue with CH6 2017 Personal Budget.

As always, you need to review your workbook in Print Preview before considering it complete. When you do that with this workbook, you notice that the worksheets are each printing on two pages. You decide to switch all the worksheets to Landscape orientation to see if that helps. You will also add a footer with the worksheet name to each of the worksheets.

1. Go to Print Preview. To view all of the worksheets at one time, select Print Entire Workbook in the first box in the Settings section. You should now have 26 pages to scroll through in Print Preview. If you were to click the Print button, all of the worksheets would print, not just the active sheet.

2. Exit Backstage View. You want to change the page orientation of all the sheets, but you cannot change all of them at one time in Print Preview.

3. Group all of the worksheets together, including the Expenses Summary sheet through the December sheet.

4. Click on the Page Layout tab on the ribbon, then select Landscape using the Orientation button in the Page Setup group.

5. Click the Page Setup dialog box launcher arrow in the Page Setup group then click the Header/Footer tab.

6. Click the Custom Footer button. In the center section, insert the worksheet name using the Insert Sheet Name button. The Footer dialog box should look like Figure 6.15.

7. Click OK to close the Footer dialog box. Click OK again to close the Page Setup dialog box.

Figure 6. 15 Insert Worksheet Name

8. Return to Print Preview to confirm that each worksheet is printing on one page, in landscape orientation, with the correct worksheet name appearing in the footer.

In Print Preview you notice that the Expenses Summary sheet is not set to print correctly. Part of the chart is appearing on a second page. You can easily fix this by changing the Scaling, but you only want to change the scaling of the Expenses Summary sheet, not the entire workbook. If you make the change in Print Preview while the worksheets are grouped it will change all of the worksheets.

1. Exit Backstage View.

2. Ungroup the worksheets by right-clicking on any of the worksheet tabs and selecting Ungroup Sheets.

3. If needed, click on the Expenses Summary worksheet tab to make it the active worksheet.

Figure 6.16 Scale to Fit

4. Click on the Page Layout tab on the ribbon and locate the Scale to Fit group of commands. (See Figure 6.16 above.)

5. Click the drop-down arrow for Width: and select 1 page. This has the same result as selecting Fit All Columns on One Page in the Scaling setting in Print Preview.

6. Return to Print Preview to confirm that the Expenses Summary worksheet is now printing on one page only.

7. Exit Backstage View.

8. Save the CH6 2017 Personal Budget workbook.

9. Compare your work with the self-check answer keys (found in the Course Files link) and then submit the following files as directed by your instructor.

- CH6 Personal Budget

- CH6 Personal Budget Template

- CH6 Travel Expenses

- CH6 2017 Personal Budget

Key Takeaways

- To print all of the worksheets in a workbook at one time select Print Entire Workbook in the Print Settings.

- You can apply page setup options, such as scaling, orientation, and headers/footers, to multiple worksheets at one time by grouping the worksheets.

Attribution

“6.4 Preparing to Print” by Julie Romey, Portland Community College is licensed under CC BY 4.0

34

6.5 Chapter Practice

A Multiple Sheet Template for a Sports Team

Data file: PR6 Data

You have just gotten a job with the Pacific Northwest Soccer Club, and you quickly realize that there isn’t a consistent way for all the coaches to keep track of their team statistics. To help with this, you decide to make a template for Season Stats for each team. Since you are also the coach of the High Flyers this season, you will need to use the template to enter your team’s statistics into a team spreadsheet.

1. Open the data file PR6 Data and save the file to your computer as PR6 Pacific NW Sports Team.

2. Copy the range B11:G22 in the Season Stats sheet to the same range in the Player Stats sheet.

3. Group the sheets and add the following formulas to both sheets:

4. In C22 and D22, you’ll need to count the Xs in rows 12 through 21. To do this, use a COUNTA formula.

5. In E22 and F22, sum rows 12 through 21.

6. In G12, calculate Goal Percentage by dividing the number of Shots by the number of Goals. This will display an error message because there are zeros in column F. We don’t want to display error messages in the file, so an IF statement that tests the value of column F will solve this problem.

7. Change the formula in G12 with the following three pieces:

- Test – is F12 greater than zero

- If the Test is True – divide the number of Goals by the number of Shot

- If the Test is False – enter a zero

8. Copy G12 down the column through G22. Format these cells as percentages.

9. For an extra challenge, put the “banded row” format back in G12:G22.

10. Ungroup the sheets.

11. Save the file as a template called PR6 Pacific NW Team Template.xltx. Make sure to save your template to your USB and not the default folder for templates on your hard drive!

12. Make a new file using the PR6 Pacific NW Team Template and save it as PR6 High Flyers.xlsx.

13. In the Season Stats sheet, enter the following data:

- D3 – High Flyers

- D4 – Fall and the current year (i.e. – Fall 2016)

- D5 – Pacific Northwest Soccer

14. Enter your name, phone number, and email address in row 8.

15. Make four copies of the Player Stats worksheet. Rename the player worksheets Player 1, Player 2, Player 3, Player 4, and Player 5.

16. Group the Player sheets. Enter the following formulas:

17. A formula in D4 that points to cell D3 in the Season Stats sheet. Note: Your formula will be =’Season Stats’!D3:G3 instead of =’Season Stats’!D3 because D3:G3 are merged together.

18. A formula in D5 that points to cell D4 in the Season Stats sheet.

19. A formula in D6 that points to cell D5 in the Season Stats sheet.

20. Ungroup the sheets.

21. Click on the Player 1 sheet. Enter the Player Name: Juan Ramirez. Enter the following data from Table 1:
Table 1: Player 1 Sheet

Played

Started

Shots

Goals

Game 1

x

x

2

1

Game 2

x

x

3

1

Game 3

Game 4

x

Game 5

x

x

2

0

Game 6

x

Game 7

Game 8

x

x

1

1

Game 9

x

x

4

2

Game 10

x

x

3

3

22. Click on the Player 2 sheet. Enter the Player Name: Zach Johnson. Enter the following data from Table 2:
Table 2: Player 2 Sheet

Played

Started

Shots

Goals

Game 1

x

x

1

1

Game 2

x

x

2

1

Game 3

x

x

1

1

Game 4

x

x

1

1

Game 5

x

x

2

0

Game 6

x

x

5

2

Game 7

x

x

4

2

Game 8

x

x

1

1

Game 9

x

x

4

1

Game 10

x

x

3

2

23. Click on the Player 3 sheet. Enter the Player Name: Vito Lawrenz. Enter the following data from Table 3:
Table 3: Player 3 Sheet

Played

Started

Shots

Goals

Game 1

x

x

0

0

Game 2

x

x

1

1

Game 3

x

x

2

0

Game 4

x

1

1

Game 5

x

x

2

0

Game 6

x

x

3

1

Game 7

x

x

2

1

Game 8

x

x

1

1

Game 9

x

x

1

1

Game 10

x

x

1

1

24. Make up information for the names and data in the Player 4 and Player 5 sheets.

25. Go to the Season Stats sheet and click on cell C12.  Enter a 3-D formula to COUNTA (count text) in C12 through sheets Player 1 through Player 5.  Copy the formula in C12 through D22 (be sure to preserve the original formatting).

26. Change the formulas in C22 and D22 from COUNTA to SUM.

27. Click on E12.  Enter a 3-D formula to SUM E12 in sheets Player 1 through Player 5. Copy the formulas through F22 (again, be sure to preserve the original formatting).

28. Preview the worksheets in Print Preview.  Notice that only part of the data is printing for each worksheet. This is because a Print Area was incorrectly set when the file was first created. You need to clear this Print Area for each worksheet individually (modifying print areas cannot be done on grouped sheets).  Exit Backstage View and for each worksheet, click the Print Area button on the Page Layout tab and select Clear Print Area.

29. Save the PR6 High Flyers workbook.

30. Compare your work with the self-check answer key (found in the Course Files link) and then submit the PR6 High Flyers workbook and PR6 Pacific NW Team Template template file as directed by your instructor.

Attribution

” 6.5 Chapter Practice” by Diane Shingledecker, Portland Community College is licensed under CC BY 4.0

35

6.6 Scored Assessment

A Multiple Sheet Template for National Parks Data

Data file: none

You are working in the National Headquarters of the National Parks Department. One of your jobs this week is to develop a template for individual park use data, along with a Summary of the parks visitation data. To do this you will need to create a template with summary and individual park sheets, and then use the template to enter park data for five national parks.

1. Open a blank spreadsheet.

2. Design a professional quality sheet to display individual park data.  Include areas to enter the name of a park and the park statistics college in Table 4 below.  Name the sheet Park Data. (Do not copy the physical layout of Table 4.) Figure 6.17 below is an example of how you could set this up:

Figure 6.17 Sample setup

3. Make a copy of the Park Data sheet and rename it Summary Park Data. Change any text needed for the Summary sheet. Make sure both sheets are laid out exactly the same.

4. Save the file as a template called SC6 National Parks Template.xltx. Make sure this gets saved to your USB and not the default folder on your hard drive! Close the template once it has been saved.

5. Make a new file using the template and save it as SC6 Five National Parks.xlsx.

6. Make four copies of the Park Data sheet. Rename the five Park Data sheets after the five parks listed in Table 4.

Table 4: 2015 National Park Data

Park

Recreation Visitors

Non-Recreation Visitors

Recreation Hours

Non-Recreation Hours

Concessioner Lodging

Concessioner Camping

Tent Campers

RV Campers

Backcountry Campers

Miscellaneous Overnight Stays

Acadia NP

2,811,184

47,100

14,452,151

47,100

0

0

135,000

32,094

1,233

8,343

Blue Ridge PKWY

15,054,603

1,942,260

93,977,122

971,136

53,688

0

61,481

33,499

2,101

1,294

Crater Lake NP

614,712

49,600

4,033,484

24,800

34,629

55,596

7,548

0

3,253

0

Yellowstone NP

4,097,710

1,156,118

82,016,845

711,795

552,940

584,979

104,149

69,830

44,898

11,715

Yosemite NP

4,150,217

155,081

78,505,877

3,993,223

938,418

0

588,701

284,372

211,966

39,214

1. Enter the park data for each of the parks in each of the five sheets.

2. In the Summary Park Data sheet, create formulas for all the fields that add up the five park sheets.

3. Preview the entire workbook in Print Preview to ensure that it is printing professionally.  Make any changes needed.

4. Save the SC6 Five National Parks workbook.

5. Submit both the SC6 Five National Parks and SC6 National Parks Template files as directed by your instructor.

National Park 2015 Data from: https://irma.nps.gov/Stats/SSRSReports

Attribution

“6.6 Scored Assessment” by Diane Shingledecker, Portland Community College is licensed under CC BY 4.0

1

This is where you can add appendices or other back matter.