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 “Example of an Excel Worksheet with Embedded Chart” 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. Notice that the embedded chart makes it very easy to see which months have the highest unit sales.

Starting Excel

The following steps will guide you in starting the Excel application. Note that these steps along with Figure 1.2 “Start Menu” relate to the Windows 7 operating system, which is very similar to the Windows Vista operating system.

1. Click the Start button on the lower left corner of your computer screen.
2. Click the All Programs arrow at the bottom left of the Start menu.
3. Click the Microsoft Office folder on the Start menu. This will open the list of Microsoft Office applications.
4. Click the Microsoft Excel 2010 option. This will start the Excel application.

The Excel Workbook

Once Excel is started, a blank workbook will open on your screen. A workbookAn Excel file that contains one or more worksheets. is an Excel file that contains one or more worksheetsMay also be referred to as a spreadsheet and contains rectangles called cells for entering numeric and nonnumeric data. (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.3 “Blank Workbook” shows a blank workbook after starting Excel.

Your workbook should already be maximized (or shown at full size) once Excel is started, as shown in Figure 1.3 “Blank Workbook”. However, if your screen looks like Figure 1.4 “Restored Worksheet” after starting Excel, you should click the Maximize button, as shown in the figure.

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 cellA specific location on a worksheet where data are entered and stored. 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.4 “Restored Worksheet” is A1. This would be referred to as cell locationA column letter followed by a row number used to identify specific cells on a worksheet. A1 or cell referenceWhen cell locations are used in formulas, Excel will reference the data that is entered into the cell. The cell reference is the cell location address. 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 in orange, as shown in Figure 1.5 “Activating a Cell Location”.

   Figure 1.5 Activating a Cell Location

   

3. Move the mouse pointer to cell A1.
4. Click and hold the left mouse button and drag the mouse pointer back to cell D5.

5. Release the left mouse button. You should see several cells highlighted, as shown in Figure 1.6 “Highlighting a Range of Cells”. This is referred to as a cell rangeAny group of contiguous cell locations; a cell range is noted as two cell locations separated by a colon. 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.6 Highlighting a Range of Cells

   

6. Click the Sheet3 worksheet tab at the bottom of the worksheet. This is how you open a worksheet within a workbook.
7. Click the Sheet1 worksheet tab at the bottom of the worksheet to return to the worksheet shown in Figure 1.6 “Highlighting a Range of Cells”.

The Excel Ribbon

Excel’s features and commands are found in the RibbonThe 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., 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.7 “Ribbon for Excel” 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.

The Ribbon shown in Figure 1.7 “Ribbon for Excel” 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.7 “Ribbon for Excel”. 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.

Quick Access Toolbar and Right-Click Menu

The Quick Access ToolbarLocated at the upper-left side of the Excel screen above the Ribbon, this toolbar provides access to the most frequently used commands, such as Save and Undo. is found at the upper left side of the Excel screen above the Ribbon, as shown in Figure 1.3 “Blank Workbook”. This area provides access to the most frequently used commands, such as Save and Undo. You also can customize the Quick Access Toolbar by adding commands that you use on a regular basis. By placing these commands in the Quick Access Toolbar, you do not have to navigate through the Ribbon to find them. To customize the Quick Access Toolbar, click the down arrow as shown in Figure 1.8 “Customizing the Quick Access Toolbar”. 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.

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

The File Tab

If you have used Office 2007, you may have noticed that the Office button has disappeared in the 2010 version. It has been replaced with the File tab on the far left side of the Ribbon. The File tab is also known as the Backstage viewThis view, which is opened through the File tab on the Ribbon, contains a variety of features and commands related to the workbook that is currently open. of the workbook. It contains a variety of features and commands related to the workbook that is currently open, new workbooks, or workbooks stored in other locations on your computer or network. Figure 1.10 “File Tab or Backstage View of a Workbook” shows the options available in the File tab or Backstage view. To leave the Backstage view and return to the worksheet, click any tab on the Ribbon or click the image of the worksheet on the right side of the window. You must click the Info button (highlighted in green in Figure 1.10 “File Tab or Backstage View of a Workbook”) to see the image of your worksheet on the right side of the window.

Included in the File tab are the default settings for the Excel application that can be accessed and modified by clicking the Options button. Figure 1.11 “Excel Options Window” 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.

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 it. The following steps explain how to save a new workbook and assign it a file name. 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 “Example of an Excel Worksheet with Embedded Chart”.

1. If you have not done so already, start Excel. A blank workbook should appear on your screen. Check to make sure the workbook is maximized (see Figure 1.4 “Restored Worksheet”).
2. Click the File tab.
3. Click the Save As button in the upper left side of the Backstage view window, as shown in Figure 1.10 “File Tab or Backstage View of a Workbook”. This will open the Save As dialog box.
4. Click in the File Name box at the bottom of the Save As dialog box.
5. Use the BACKSPACE key to remove the current file name of the workbook.
6. Type the file name: Excel Objective 1.0.
7. 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 on your computer or network, double click the Computer option, as shown in Figure 1.12 “Save As Dialog Box”, and select your preferred location.
8. Click the Save button on the lower right side of the Save As dialog box.

Excel 97-2003 File Type

If you are working with someone who is using a version of Microsoft Office that is older than Office 2007, you will have to save your workbook under the Excel 97-2003 Workbook format. A person who is running Office 2003 will not be able to open workbooks that are saved under the Office 2010 or Office 2007 file types. You can save a workbook as an Excel 97-2003 file type by clicking the down arrow next to the “Save as type” box in the Save As dialog box (see Figure 1.12 “Save As Dialog Box”).

You can also change the file type of your workbook by using the File tab on the Ribbon. The following steps explain this method:

1. Open the workbook you wish to convert to the Excel 97-2003 file type.
2. Click the File tab on the Ribbon.
3. Click the Save & Send button on the left side of the Backstage view.
4. Click the Change File Type button.
5. Double click the Excel 97-2003 Workbook option on the right side of the Backstage view. This will open up the Save As dialog box and set the file type box to Excel 97-2003 Workbook (see Figure 1.13 “Changing the File Type of a Workbook”).
6. Check to make sure the Save As dialog box is set to the location where you want to save your workbook.
7. Click the Save button at the bottom of the Save As dialog box.

When you convert an existing workbook created in Office 2010 to the Excel 97-2003 file type, you may not notice any changes on the workbook itself. If you are using a feature or format that is not compatible with Office 97-2003, a warning will appear upon saving the file. You may want to remove these features and formats before sending the workbook to a person who is running an older version of Office. When you open a file that is saved in the Excel 97-2003 format, you will see the Compatibility Mode indicator next to the workbook name, as shown in Figure 1.14 “Workbook That Has Been Saved in Excel 97-2003 Format”.

The Status Bar

The Status BarLocated below the worksheet tabs on the Excel screen, 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, or mathematical functions that can be performed when data are highlighted on a worksheet. is located below the worksheet tabs on the Excel screen (see Figure 1.15 “Customizing the Status Bar”). 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 (see Figure 1.15 “Customizing the Status Bar”).
2. Select the Caps Lock option from the menu (see Figure 1.15 “Customizing the Status Bar”).
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 key again. The indicator on the Status Bar goes away.

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 corner of the screen (see Figure 1.3 “Blank Workbook”). Here you can search for specific topics or type a question in the upper-left side of the window, as shown in Figure 1.16 “Excel Help Window”.

Entering Data

We will begin building the workbook shown in Figure 1.1 “Example of an Excel Worksheet with Embedded Chart” by manually entering data into the worksheet. There are other ways in which you can bring data into an Excel worksheet, such as importing data from a website or a Microsoft Access database. However, we will demonstrate these other methods later. The following steps explain how the column headings in Row 2 are typed into the worksheet:

1. Activate 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 Sales Dollars.

   Figure 1.17 “Entering Column Headings into a Worksheet” 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.17 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.

6. Activate cell location B3.
7. 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.

8. Repeat step 7 by entering the following numbers in cells B4 through B14: 2160, 515, 590, 1030, 2875, 2700, 900, 775, 1180, 1800, and 3560.

   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.

9. Activate cell location C3.
10. Type the number 9.99 and press the ENTER key.
11. Repeat step 10 by entering 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.
12. Activate cell location D3.
13. Type the number 26685 and press the ENTER key.
14. Repeat step 13 by entering the following numbers in cells D4 through D14: 26937, 7701, 10269, 15405, 35916, 26937, 17958, 15708, 23562, 31416, and 53370.

Figure 1.18 “Completed Data Entry for Columns B, C, and D” shows how your worksheet should appear after entering the data. Check your numbers carefully to make sure they are accurately entered into the worksheet.

Editing Data

Data that has been entered in a cell can be changed by double clicking the cell location or using the Formula BarThe area just above the column letters on a worksheet. It can be used for entering data into cells as well as for editing data that already exists in cells.. 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. Activate 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.19 “Using the Formula Bar to Edit and Enter Data”). This will enter the change into the cell.

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

Auto Fill

The Auto FillAn Excel feature used to complete data in either a quantitative or qualitative sequence. It can also be used to copy and paste data in a worksheet. 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. Activate 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 HandleA small square in lower right corner of an activated cell. When the mouse pointer gets close to the Fill Handle, the white block plus sign turns into a black plus sign. (see Figure 1.20 “Fill Handle”). When the mouse pointer gets close to the Fill Handle, the white block plus sign will turn into a black plus sign.

   Figure 1.20 Fill Handle

   

5. 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.21 “Using Auto Fill to Enter the Months of the Year”). Release the left mouse button when the tip box reads “December.”

   Figure 1.21 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.22 “Auto Fill Options Button”. 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.22 Auto Fill Options Button

   

6. Left click the Auto Fill Options button.
7. Left click the Copy Cells option. This will change the months in the range A4:A14 to January.
8. Left click the Auto Fill Options button again.
9. Left 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. Activate 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. Left click and drag the mouse pointer up to cell C3 (see Figure 1.23 “Using Auto Fill to Delete Contents of Cell”). Release the mouse button. The contents in the range C3:C14 will be removed.

   Figure 1.23 Using Auto Fill to Delete Contents of Cell

   

6. Click the Undo button in the Quick Access Toolbar (see Figure 1.3 “Blank Workbook”). This should replace the data in the range C3:C14.

7. Click the Undo button again. This should replace the data in cell C2.

   Mouseless Command

   Undo Command

   • Hold down the CTRL key while pressing the letter Z on your keyboard.
8. 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.
9. Click the Clear button in the Home tab of the Ribbon, which is next to the Cells group of commands (see Figure 1.24 “Clear Command Drop-Down Menu”). 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.
10. Click the Clear All option. This removes the data in the cell range.
11. Click the Undo button. This replaces the data in the range C2:C14.

Adjusting Columns and Rows

In Figure 1.22 “Auto Fill Options Button”, there are a few entries 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.25 “Adjusting Column Widths”. You will see the white block plus sign turn into double arrows.
2. Left 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 boxA box that appears when the width of a column is being adjusted using the click-and-drag method. It displays the number of characters that will fit into the column using Calibri 11-point font.. This box displays the number of characters that will fit into the column using the Calibri 11-point font.

3. Release the left mouse button.

   Figure 1.25 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 4 through 7 illustrate a second method for adjusting column widths when using a specific number of characters:

4. Activate 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.
5. In the Home tab of the Ribbon, left click the Format button in the Cells group (see Figure 1.26 “Cells Group in the Home Tab”).
6. Click the Column Width option from the drop-down menu (see Figure 1.27 “Format Drop-Down Menu”). This will open the Column Width dialog box.

7. 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.28 “Column Width Dialog Box”).

   Figure 1.26 Cells Group in the Home Tab

   

   Figure 1.27 Format Drop-Down Menu

   

   Figure 1.28 Column Width Dialog Box

   

   Mouseless Command

   Column Width

   • Press the ALT key on your keyboard, then press the letters H, O, and W one at a time.

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

8. Activate cell A15 by placing the mouse pointer over the cell and clicking the left mouse button.
9. In the Home tab of the Ribbon, left click the Format button in the Cells group (see Figure 1.26 “Cells Group in the Home Tab”).
10. Click the Row Height option from the drop-down menu (see Figure 1.27 “Format Drop-Down Menu”). This will open the Row Height dialog box.
11. 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 pointMetric used when measuring the height of a row; equivalent to approximately 1/72 of an inch. 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.

Figure 1.29 “Excel Objective 1.0 with Column A and Row 15 Adjusted” shows the appearance of the worksheet after Column A and Row 15 are adjusted.

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 Excel Objective 1.0 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. Activate 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 (see Figure 1.27 “Format Drop-Down Menu”). This will open a submenu of options.

4. Click the Hide Columns option in the submenu of options (see Figure 1.30 “Hide & Unhide Submenu”). This will hide Column C.

   Figure 1.30 Hide & Unhide Submenu

   

   Mouseless Command

   Hiding Columns

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

   Figure 1.31 “Hidden Column” 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.31 Hidden Column

   

   To unhide a column, follow these steps:

5. Highlight the range B1:D1 by activating cell B1 and clicking and dragging over to cell D1.
6. Click the Format button in the Home tab of the Ribbon.
7. Place the mouse pointer over the Hide & Unhide option in the drop-down menu (see Figure 1.27 “Format Drop-Down Menu”).

8. Click the Unhide Columns option in the submenu of options (see Figure 1.30 “Hide & Unhide Submenu”). Column C will now be visible on the worksheet.

   Mouseless Command

   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:

9. Activate cell A3 in the Sheet1 worksheet by placing the mouse pointer over the cell location and clicking the left mouse button.
10. Click the Format button in the Home tab of the Ribbon.
11. Place the mouse pointer over the Hide & Unhide option in the drop-down menu (see Figure 1.27 “Format Drop-Down Menu”). This will open a submenu of options.

12. Click the Hide Rows option in the submenu of options (see Figure 1.30 “Hide & Unhide Submenu”). This will hide Row 3.

    Mouseless Command

    Hiding Rows

    • Hold down the CTRL key while pressing the number 9 key on your keyboard.

    To unhide a row, follow these steps:

13. Highlight the range A2:A4 by activating cell A2 and clicking and dragging over to cell A4.
14. Click the Format button in the Home tab of the Ribbon.
15. Place the mouse pointer over the Hide & Unhide option in the drop-down menu (see Figure 1.27 “Format Drop-Down Menu”).
16. Click the Unhide Rows option in the submenu of options (see Figure 1.30 “Hide & Unhide Submenu”). Row 3 will now be visible on the worksheet.

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. Activate 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.32 “Insert Button (Down Arrow)”).

   Figure 1.32 Insert Button (Down Arrow)

   

3. Click the Insert Sheet Columns option from the drop-down menu (see Figure 1.33 “Insert Drop-Down Menu”). 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.

   Mouseless Command

   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.

   Figure 1.33 Insert Drop-Down Menu

   

4. Activate 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.32 “Insert Button (Down Arrow)”).
6. Click the Insert Sheet Rows option from the drop-down menu (see Figure 1.33 “Insert Drop-Down Menu”). 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.

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.34 “Moving Data”). This indicates that you can left click and drag the data to a new location.

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

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. Activate 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.35 “Delete Drop-Down Menu”). This removes Row 3 and shifts all the data (below Row 2) in the worksheet up one row.

   Mouseless Command

   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.35 Delete Drop-Down Menu

   

4. Activate 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.35 “Delete Drop-Down Menu”). This removes Column C and shifts all the data in the worksheet (to the right of Column B) over one column to the left.

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

2. Click the Bold button in the Font group of commands in the Home tab of the Ribbon (see Figure 1.36 “Font Group of Commands”).

   Figure 1.36 Font Group of Commands

   

   Mouseless Command

   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 Italics button in the Font group of commands in the Home tab of the Ribbon (see Figure 1.36 “Font Group of Commands”).

6. Click the Underline button in the Font group of commands in the Home tab of the Ribbon (see Figure 1.36 “Font Group of Commands”). Notice that there is a drop-down arrow next to the Underline button. This is for selecting a double underline format, which is common in careers that deal with accounting or budgeting activities.

   Mouseless Command

   Italics Format

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

   Mouseless Command

   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.

7. Highlight the range B3:B14 by placing the mouse pointer over cell B3 and left clicking and dragging down to cell B14.

8. Click the Comma Style button in the Number group of commands in the Home tab of the Ribbon (see Figure 1.37 “Number Group of Commands”).

   Figure 1.37 Number Group of Commands

   

9. Click the Decrease Decimal button in the Number group of commands in the Home tab of the Ribbon (see Figure 1.37 “Number Group of Commands”).
10. The numbers will also be reduced to zero decimal places.
11. Highlight the range C3:C14 by placing the mouse pointer over cell C3 and left clicking and dragging down to cell C14.
12. Click the Accounting Number Format button in the Number group of commands in the Home tab of the Ribbon (see Figure 1.37 “Number Group of Commands”). This will add the US currency symbol and two decimal places to the values. This format is common when working with pricing data.
13. Highlight the range D3:D14 by placing the mouse pointer over cell D3 and left clicking and dragging down to cell D14.
14. Again, this will add the US currency symbol to the values as well as two decimal places.
15. Click the Decrease Decimal button in the Number group of commands in the Home tab of the Ribbon.
16. This will add the US currency symbol to the values and reduce the decimal places to zero.
17. Highlight the range A1:D1 by placing the mouse pointer over cell A1 and left clicking and dragging over to cell D1.

18. 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.38 “Fill Color Palette”).

    Figure 1.38 Fill Color Palette

    

19. Click the Aqua, Accent 5, Darker 25% color from the palette (see Figure 1.38 “Fill Color Palette”). 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.
20. Click the down arrow next to the Font Color button in the Font group of commands in the Home tab of the Ribbon (see Figure 1.36 “Font Group of Commands”).
21. This change will be visible once text is typed into the highlighted cells.
22. Click the Increase Font Size button in the Font group of commands in the Home tab of the Ribbon (see Figure 1.38 “Fill Color Palette”).
23. Highlight the range A1:D15 by placing the mouse pointer over cell A1 and left clicking and dragging down to cell D15.
24. Click the drop-down arrow on the right side of the Font button in the Home tab of the Ribbon (see Figure 1.36 “Font Group of Commands”).
25. Notice that as you move the mouse pointer over the font style options, you can see the font change in the highlighted cells.
26. Expand the row width of Column D to 10 characters.

Figure 1.39 “Formatting Techniques Applied” shows how the Sheet1 worksheet should appear after the formatting techniques are 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 textStacking multiword text entries vertically in a cell.. 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.40 “Alignment Group in Home Tab”). This will center the column headings in each cell location.

   Figure 1.40 Alignment Group in Home Tab

   

3. Click the Wrap Text button in the Alignment group (see Figure 1.40 “Alignment Group in Home Tab”). The height of Row 2 automatically expands, and the words that were cut off because the columns were too narrow are now stacked vertically (see Figure 1.42 “Sheet1 with Data Alignment Features Added”).

   Mouseless Command

   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.41 “Merge Cell Drop-Down Menu”). This will create one large cell location running across the top of the data set.

Figure 1.42 “Sheet1 with Data Alignment Features Added” 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.

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 title will require that two lines of text be entered into a cell. 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.
2. Type the text General Merchandise World.
3. Hold down the ALT key and press the ENTER key. This will start a new line of text in this cell location.
4. Type the text 2011 Retail Sales (in millions) and press the ENTER key.
5. Select cell A1. Then click the Italics button in the Font group of commands in the Home tab of the Ribbon.
6. 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.43 “Title Added to the Sheet1 Worksheet”).

Borders (Adding Lines to a Worksheet)

In Excel, adding custom lines to a worksheet is known as adding borders. BordersLines that are added to a worksheet to separate the data in columns and rows. 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. Note that when you click on cell A1, cells B1:D1 will also activate since they are merged.

2. Click the down arrow to the right of the Borders button in the Font group of commands in the Home page of the Ribbon (see Figure 1.44 “Borders Drop-Down Menu”).

   Figure 1.44 Borders Drop-Down Menu

   

3. Left click the All Borders option from the Borders drop-down menu (see Figure 1.44 “Borders Drop-Down Menu”). This will add vertical and horizontal lines to the range A1:D15.
4. Highlight the range A2:D2 by placing the mouse pointer over cell A2 and left clicking and dragging over to cell D2.
5. Click the down arrow to the right of the Borders button.
6. Left click the Thick Bottom Border option from the Borders drop-down menu.
7. Highlight the range A1:D15.
8. Click the down arrow to the right of the Borders button.
9. This will open the Format Cells dialog box (see Figure 1.45 “Borders Tab of the Format Cells Dialog Box”). You can access all formatting commands in Excel through this dialog box.
10. In the Style section of the Borders tab, left click the thickest line style (see Figure 1.45 “Borders Tab of the Format Cells Dialog Box”).
11. Left click the Outline button in the Presets section (see Figure 1.45 “Borders Tab of the Format Cells Dialog Box”).
12. Click the OK button at the bottom of the dialog box (see Figure 1.45 “Borders Tab of the Format Cells Dialog Box”).

AutoSum

You will see at the bottom of Figure 1.46 “Borders Added to the Sheet1 Worksheet” 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 functionsMathematical computations that are applied to a range of cells or specific cells on a worksheet. 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.47 “AutoSum Drop-Down List”). Note that the AutoSum button can also be found in the Editing group of commands in the Home tab of the Ribbon.

   Figure 1.47 AutoSum Drop-Down List

   

4. Click the Sum option from the AutoSum drop-down menu.
5. Excel will provide a total for the values in the Unit Sales column.
6. Activate cell D15.
7. Repeat steps 3 through 5 to sum the values in the Sales Dollars column (see Figure 1.48 “Totals Added to the Sheet1 Worksheet”).
8. This will remove the pound signs (####) and show the total.

Inserting a Column Chart

As mentioned at the beginning of this chapter, Excel serves as a critical tool for making decisions in both personal and professional contexts. ChartsTools used to graphically display the data in a worksheet. are a powerful tool in Excel that allow you to graphically display the data in a worksheet. Graphical displays allow the reader to immediately identify key trends and behaviors in the data that is being analyzed. For the workbook that we are using for this chapter, understanding the trends in monthly sales data is critical for making decisions such as how many staff members to assign to the store for each month as well as supplying the store with enough inventory to accommodate expected sales. To assist the reader in analyzing this data, a column chart will be created to graphically display the data. It is important for you to plan which type of chart will best display the data so your readers can quickly see key trends. More details on creating charts and on chart types will be presented in a later chapter. The following steps are an introduction to creating the column chart required for this chapter’s objective:

1. Highlight the range A2:B14.
2. Click the Insert tab of the Ribbon.

3. Click the Column button (see Figure 1.49 “Column Chart Drop-Down Menu”). This will open the column chart drop-down menu of options.

   Figure 1.49 Column Chart Drop-Down Menu

   

4. Select the Clustered Column option from the list of column chart options (see Figure 1.49 “Column Chart Drop-Down Menu”). This will create an embedded chart in the Sheet1 worksheet (see Figure 1.50 “Embedded Column Chart in Sheet1”).

   Figure 1.50 “Embedded Column Chart in Sheet1” shows the column chart that is created once a selection is made from the column chart drop-down menu. Notice that there are three new tabs added to the Ribbon. These tabs contain features for enhancing the appearance and construction of Excel charts. These commands will be covered in more detail in a later chapter. For now, you will see that Excel places the chart over the data in the worksheet. The following steps explain how to move and resize the chart:

   Figure 1.50 Embedded Column Chart in Sheet1

   

5. The block white plus sign will become black cross arrows (see Figure 1.50 “Embedded Column Chart in Sheet1”).

6. Left click and drag the chart so the upper left corner is placed in the middle of cell F1 (see Figure 1.51 “Moving an Embedded Chart”).

   Figure 1.51 Moving an Embedded Chart

   

7. Place the mouse pointer over the top center sizing handleDots that appear around the perimeter of an embedded chart in a worksheet. They can be clicked and dragged to desired dimensions to change the size of a chart. (see Figure 1.50 “Embedded Column Chart in Sheet1”). You will see the mouse pointer change from a white block plus sign to a vertical double arrow. Make sure the mouse pointer is not in the cross arrow mode as shown in Figure 1.50 “Embedded Column Chart in Sheet1” as this will move the chart instead of resizing it.
8. While holding down the ALT key on your keyboard, left click and drag the mouse pointer slightly up. The chart will automatically adjust up to the top of Row 1.
9. Place the mouse pointer over the left center sizing handle.
10. While holding down the ALT key on your keyboard, left click and drag the mouse slightly toward the left. The chart will automatically adjust to the left side of Column F.
11. Place the mouse pointer over the lower center sizing handle.
12. While holding down the ALT key on your keyboard, left click and drag the mouse slightly down. The chart will automatically adjust to the bottom of Row 14.
13. Place the mouse pointer over the right center sizing handle.

14. While holding down the ALT key on your keyboard, left click and drag the mouse slightly to the right. The chart will automatically adjust to the right side of Column M.

    Why?

    There Are No Sizing Handles on a Chart

    If you do not see the dots or sizing handles around the perimeter of a chart, it could be that the chart is not activated. To activate a chart, left click anywhere on the chart.

    Figure 1.52 “Embedded Chart Moved and Resized” shows the column chart moved and resized. Notice that the sizing handles are not visible around the perimeter of the chart. This is because the chart is not activated. Once you click anywhere on the worksheet outside the chart area, the chart is automatically deactivated.

    Figure 1.52 Embedded Chart Moved and Resized

    

    Why?

    Use the ALT Key When Resizing a Chart

    Using the ALT key while resizing an embedded chart locks the perimeter of the chart to the columns and rows of the worksheet. This gives you the ability to adjust the chart to precise sizes as you adjust the width and height of the worksheet rows and columns.

    As shown in Figure 1.50 “Embedded Column Chart in Sheet1”, when a chart is created, three tabs are added to the Ribbon. The following steps explain how to use a few of the formatting and design features in these tabs:

15. Check to make sure the column chart in Sheet1 is activated. To activate the chart, left click anywhere on the chart.
16. Click the Design tab under the Chart Tools set of tabs on the Ribbon.

17. Click the down arrow on the right side of the Chart Styles section (see Figure 1.53 “Chart Styles in the Design Tab”).

    Figure 1.53 Chart Styles in the Design Tab

    

18. Click Style 44 in the Chart Styles section. This style has a black background with red columns (see Figure 1.53 “Chart Styles in the Design Tab”).
19. Click the Format tab under the Chart Tools set of tabs on the Ribbon.
20. Click the down arrow on the right side of the WordArt Styles section (see Figure 1.54 “WordArt Styles in the Format Tab”).

Click the Blue, Accent 1, Inner Shadow option (see Figure 1.54 “WordArt Styles in the Format Tab”). Notice that as you move the mouse pointer over the WordArt Styles options, the format of the chart title as well as the X and Y axis titles changes.

Figure 1.55 “Formatting Features Applied to the Column Chart” shows the embedded column chart with the formatting features applied. This chart is very effective in displaying the Unit Sales trends for this company. You can see very quickly that the tallest bar in the chart is the month of December, followed by the months of June, July, January, and February.

Cut, Copy, and Paste

The Cut, Copy, and Paste commands are perhaps the most widely used commands in Microsoft Office. With regard to Excel, the Copy and Paste commands are often used to make copies of worksheets for developing different scenarios or versions for the data being analyzed. The following steps demonstrate how these commands are used for the objective in this chapter:

1. Click the Select All button in the upper left corner of the Sheet1 worksheet (see Figure 1.56 “Clipboard Group of Commands”).

   Figure 1.56 Clipboard Group of Commands

   

2. Click the Copy button in the Clipboard group of commands in the Home tab of the Ribbon (see Figure 1.56 “Clipboard Group of Commands”).

   Mouseless Command

   Copy

   • Press the CTRL key and then the letter C key on your keyboard.
3. Open the Sheet2 worksheet by left clicking on the Sheet2 worksheet tab at the bottom of the workbook.
4. Activate cell location A1.

5. Click the Paste button in the Clipboard group of commands in the Home tab of the Ribbon. Be sure to click the upper area of the Paste button and not the down arrow at the bottom of the button. A copy of Sheet1 will now appear in Sheet2.

   Mouseless Command

   Paste

   • Press the CTRL key and then the letter V key on your keyboard.
6. Click anywhere on the chart in the Sheet2 worksheet.

7. Click the Cut button in the Clipboard group on the Home tab of the Ribbon. This will remove the chart from the Sheet2 worksheet.

   Mouseless Command

   Cut

   • Press the CTRL key and then the letter X key on your keyboard.
8. Open the Sheet3 worksheet by left clicking on the Sheet3 worksheet tab at the bottom of the workbook.
9. Activate cell location A1.
10. Click the Paste button in the Home tab of the Ribbon. This will paste the chart from the Sheet2 worksheet into the Sheet3 worksheet.

Sorting Data (One Level)

As mentioned earlier in this section, a chart is a tool that enables worksheet readers to analyze data quickly to spot key trends or patterns. Another powerful tool that provides similar benefits is the SortAn Excel command used to rank the data in a worksheet based on designated criteria. command. This feature ranks the rows of data in a worksheet based on designated criteria. The following steps demonstrate how the Sort command is used to rank the data in the Sheet2 worksheet:

1. In the Sheet2 worksheet, highlight the range A2:D14.
2. Click the Data tab of the Ribbon.

3. Click the Sort button in the Sort & Filter group of commands. This will open the Sort dialog box (see Figure 1.57 “Sort & Filter Group of Commands”).

   Figure 1.57 Sort & Filter Group of Commands

   

4. Click the down arrow next to the “Sort by” drop-down box in the Sort dialog box (see Figure 1.58 “Sort Dialog Box”).

   Figure 1.58 Sort Dialog Box

   

5. Click the Unit Sales option from the drop-down list.
6. Click the down arrow next to the Order drop-down box.
7. Click Largest to Smallest from the drop-down list.
8. Click the OK button at the bottom of the Sort dialog box. The data in the range A2:D14 will now be sorted in descending order based on the values in the Unit Sales column.

Figure 1.59 “Data Sorted Based on Unit Sales” shows the data in the Sheet2 worksheet sorted based on the values in the Unit Sales column. Similar to the chart, the Sort command makes it easy to identify the months of the year with the highest unit sales.

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.60 “Renaming a Worksheet Tab”).
2. Type the name Sales by Month.
3. Press the ENTER key on your keyboard.
4. With the left mouse button, double click the Sheet2 worksheet tab at the bottom of the workbook.
5. Type the name Unit Sales Rank.

6. Press the ENTER key on your keyboard.

   Figure 1.60 Renaming a Worksheet Tab

   

7. Left click and drag the Unit Sales Rank worksheet tab to the left of the Sales by Month worksheet tab.
8. Click the Sheet3 worksheet tab.
9. Click the Home tab of the Ribbon.
10. Click the down arrow on the Delete button in the Cells group of commands.
11. Click the Delete Sheet option from the drop-down list (see Figure 1.35 “Delete Drop-Down Menu”).
12. Click the Delete button on the Delete warning box.
13. Click the Insert Worksheet tab at the bottom of the workbook (see Figure 1.60 “Renaming a Worksheet Tab”).

Figure 1.61 “Final Appearance of the Excel Objective 1.0 Workbook” shows the final appearance of the Excel Objective 1.0 workbook after the worksheet tabs have been renamed and moved.

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 Unit Sales Rank worksheet by left clicking on the worksheet tab.
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.
5. Open the Sales by Month worksheet by left clicking on the worksheet tab.
6. Click the Page Layout tab of the Ribbon (see Figure 1.62 “Page Layout Commands for Printing”).
7. Click the Margins button in the Page Setup group of commands.
8. Click the Narrow option from the Margins drop-down list.
9. Click the Orientation button in the Page Setup group of commands.
10. Click the Landscape option.
11. Click the down arrow to the right of the Width button in the Scale to Fit group of commands.
12. Click the 1 Page option from the drop-down list.
13. Click the down arrow to the right of the Height button in the Scale to Fit group of commands.
14. Click the 1 Page option from the drop-down list. This step along with step 12 will automatically reduce the worksheet so that it fits on one piece of paper. It is very common for professionals to create worksheets that fit within the width of the paper being used. However, for long data sets, you may need to set the height to more than one page. Table 1.2 “Printing Resources: Purpose and Use for Page Setup Commands” provides a list of commands found in the Page Layout tab of the Ribbon

Headers and Footers

When printing worksheets from Excel, it is common to add headersSpace at the top of a printed worksheet that contains information such as the date, page number, file name, company name, and so on. and footersSpace at the bottom of a printed worksheet that contains information such as the date, page number, file name, company name, and so on. 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 Excel Objective 1.0 workbook:

1. Open the Unit Sales Rank worksheet by left clicking on the worksheet tab.
2. Click the Insert tab of the Ribbon.

3. Click the Header & Footer button in the Text group of commands. 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.63 “Design Tab for Creating Headers and Footers”).

   Figure 1.63 Design Tab for Creating Headers and Footers

   

4. Type your name in the center section of the Header.
5. Place the mouse pointer over the left section of the Header and left click (see Figure 1.63 “Design Tab for Creating Headers and Footers”).
6. Click the Current Date button in the Header & Footer Elements group of commands in the Design tab of the Ribbon.
7. Click the Go to Footer button in the Navigation group of commands in the Design tab of the Ribbon.
8. Place the mouse pointer over the far right section of the footer and left click.
9. Click the Page Number button in the Header & Footer Elements group of commands in the Design tab of the Ribbon.
10. Click any cell location outside the header or footer area. The Design tab for creating headers and footers will disappear.

11. Click the Normal view button in the lower right side of the Status Bar (see Figure 1.64 “Worksheet in Page Layout View”).

    Figure 1.64 Worksheet in Page Layout View

    

12. Open the Sales by Month worksheet by left clicking the worksheet tab.
13. Repeat steps 2 through 11 to create the same header and footer for this worksheet.

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 Excel Objective 1.0 workbook:

1. Open the Unit Sales Rank worksheet by left clicking on the worksheet tab.
2. Click the File tab on the Ribbon.

3. Click the Print option on the left side of the Backstage view (see Figure 1.65 “Print Preview”). On the right side of the Backstage view, you will be able to see a preview of your printed worksheet.

   Figure 1.65 Print Preview

   

4. Click the Print Active Sheets button in the Print section of the Backstage view (see Figure 1.65 “Print Preview”).
5. Click the Print Entire Workbook option from the drop-down list. This will print all worksheets in a workbook when the Print button is clicked.
6. Click the Next Page arrow at the bottom of the preview window.
7. Click the Print button.
8. Click the Home tab of the Ribbon.
9. Save and close the Excel Objective 1.0 workbook.

Time Series Trend: Line Chart 1

The first chart we will demonstrate is a line chart. Figure 4.1 “52 Week Data for the S&P 500 and Microsoft” shows part of the data that will be used to create two line charts. The first line chart will show the trend of the S&P 500An aggregate price index for five hundred of the largest publicly traded US companies. stock index. This is an aggregate price index of five hundred of the largest publicly traded companies. This chart will be used to communicate a simple message: to show how the index has performed over a fifty-two-week 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.

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 “52 Week Data for the S&P 500 and Microsoft”, you will see that there are fifty-two 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 closing price for the S&P 500 on the Y axisThe vertical axis of a chart. and the week number on the X axisThe horizontal axis of a chart.. The following steps explain how to construct this chart:

1. Highlight the range A3:B55 on the Stock Trend worksheet.
2. Click the Insert tab of the Ribbon.
3. Click the Line button in the Charts group of commands (see Figure 4.2 “Selecting the Basic Line Chart”).
4. Click the first option from the list, which is a basic line chart (see Figure 4.2 “Selecting the Basic Line Chart”). This adds, or embeds, the line chart to the worksheet, as shown in Figure 4.3 “Embedded Line Chart in the Stock Trend Worksheet”.

Figure 4.3 “Embedded Line Chart in the Stock Trend Worksheet” shows the embedded line chart in the Stock Trend worksheet. Notice that three additional tabs, or contextual tabsHidden tabs on the Ribbon that contain commands related to a specific object. Contextual tabs become visible when the related object is added or activated., 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.

As shown in Figure 4.3 “Embedded Line Chart in the Stock Trend Worksheet”, the embedded chartAny chart that is created and placed within a worksheet. 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 center of cell H2.
2. Resizing a chart: Place the mouse pointer over the left middle sizing handle, hold down the ALT key on your keyboard, and click and drag the chart so it “snaps” to the left side of Column H.
3. Repeat step 2 to resize the chart so the top “snaps” to the top of Row 2, the bottom “snaps” to the bottom of Row 17, and the right side “snaps” to the right side of Column P.
4. Adjusting the chart title: Click the chart title once. Then click in front of the letter S. You should see a blinking cursor in front of the letter S. This allows you to modify the title of the chart.
5. Type the following in front of the letter S in the chart title: 52 Week Trend for the.
6. Removing the legend: Click the legend once and press the DELETE key on your keyboard. This removes the legend from the chart. Since the chart contains only one data series, the legend is not necessary. Once you remove the legend, the plot area automatically expands.

Figure 4.4 “Line Chart Moved and Resized” 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 52 Week Trend for the S&P 500. Also 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.

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. However, the minimum value is usually set to zero. 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 “Line Chart Moved and Resized” appears to be increasing slightly. However, the S&P 500 increased by over 20% during this period, which is substantial. The presentation of this trend can be improved if the minimum value started at eight hundred. While it is certainly possible for the S&P 500 to fall below eight hundred, it is most likely remote. The following steps explain how to make this adjustment to the Y axis:

1. Click anywhere on the Y axis on the 52 Week Trend for the S&P 500 line chart (Stock Trend worksheet).
2. Click the Format tab in the Chart Tools section of the Ribbon.

3. Click the Format Selection button in the Current Selection group of commands (see Figure 4.5 “Format Axis Dialog Box”). This opens the Format Axis dialog box.

   Figure 4.5 Format Axis Dialog Box

   

4. Click the Fixed option next to the “Minimum” axis option in the Format Axis dialog box.
5. Click the input box for the “Minimum” axis option and delete the zero. Then type the number 800. As soon as you make this change, the Y axis on the chart adjusts.
6. Click the Close button at the bottom of the Format Axis dialog box.

Figure 4.6 “Adjusted Y Axis for the S&P 500 Chart” shows the change in the presentation of the trendline. Notice that with the Y axis starting at 800, the trend for the S&P 500 is more pronounced and reflects the substantial increase over the 52-week period. This adjustment makes it easier for the audience to see the magnitude of the trend.

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 value for the S&P 500 and the Microsoft common stock. Chapter 3 “Logical and Lookup Functions” presented a personal investment portfolio where the investments were compared to a benchmark. The S&P 500 is a benchmark that is commonly used to judge the performance of individual stocks. The purpose and message of this chart is to show whether Microsoft is performing better or worse than the S&P 500 index. This type of analysis can be used to determine whether a stock should be sold, purchased, or held.

Before creating the chart to compare the S&P 500 and Microsoft, it is important to review the data in the range E4:F55 on the Stock Trend worksheet. We cannot use the price data for Microsoft and the S&P 500 because the values are not comparable. That is, the data for Microsoft is in a range of $22.00 to $28.00, but the data for the S&P 500 is in a range of 1,022 to 1,363. If we used these values to create a chart, we would not be able to see any substantial change in the trend for either the S&P 500 or Microsoft. Therefore, formulas were used to calculate the percent change in value for the S&P 500 and Microsoft for each week. For example, looking at cells E5 and F5 on the Stock Trend worksheet, you see that the S&P 500 increased 2.4% in week 2, whereas Microsoft increased 3.1%. The percent change calculations now provide an appropriate method of comparison. This is a very important step to consider when comparing trends.

The construction of this second line chart will be similar to the first line chart. The X axis will be the 52 weeks in the range A4:A55. However, the Y axis will be the percentages in the range E4:F55. This creates a problem because Columns B, C, and D will not be used in this chart. Therefore, we cannot simply highlight one contiguous range of cells to create the chart. In this chapter we will demonstrate two options for charting data that is not in a contiguous range. The following steps demonstrate the first option:

1. Highlight the range A3:A55 on the Stock Trend worksheet.
2. Hold down the CTRL key on your keyboard and highlight the range E3:F55.
3. Click the Insert tab of the Ribbon.
4. Click the Line button in the Charts group of commands.

5. Click the first option from the list, which is a basic line chart.

   Figure 4.7 “Trend Comparison Line Chart” shows the appearance of the line chart comparing the S&P 500 and Microsoft before it is moved and resized. Notice that Excel does not add a title to the chart.

   Figure 4.7 Trend Comparison Line Chart

   

6. Move the chart so the upper left corner is in the middle of cell H20.
7. 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 P, the top is locked to the top of Row 20, and the bottom is locked to the bottom of Row 35.
8. Click the Layout tab in the Chart Tools section of the Ribbon.
9. Click the Chart Title button in the Labels group of commands. Select the Above Chart option from the drop-down list (see Figure 4.8 “Adding a Title to a Chart”). This adds a generic title above the plot area of the chart.
10. Click in the text box containing the chart title. Delete the generic chart title and replace it with the following: 52 Week Trend Comparison.

Figure 4.9 “Final Trend Comparison Line Chart” shows that Microsoft has not performed as well as the S&P 500 benchmark. From week 31 to week 52, Microsoft is showing a significant decline compared to the S&P 500, which continues to grow. What makes this chart effective is that an audience can quickly see how Microsoft compares with the S&P 500 over the 52-week period.

Frequency Distribution: Column Chart 1

A column chart is commonly used to show trends over time so long as the data are limited to approximately twenty points or less. For example, in Chapter 1 “Fundamental Skills” we showed a sales trend over a twelve-month period. Another common use for column charts is frequency distributions. A frequency distributionThe number of occurrences for an established set of categories. 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 a hypothetical academic class. To show the grade frequency distribution, the numbers of students appear on the Y axis and the grade categories appear on the X axis. The following steps explain how to create this chart:

1. Highlight the range A3:B8 on the Grade Distribution worksheet. Column B shows the number of students that achieved a grade within the grade category shown in Column A.
2. Click the Column button in the Charts group section on the Insert tab of the Ribbon. Select the first format from the drop-down list of options, which is the Clustered Column format.
3. Click and drag the chart so the upper left corner is in the middle of cell H2.
4. 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 P, the top is locked to the top of Row 2, and the bottom is locked to the bottom of Row 16.
5. Click the legend one time and press the DELETE key on your keyboard. Since the chart presents only one data series, the legend is not necessary.
6. Click the title of the chart twice so the cursor is placed in front of the word Class.
7. Type the following in front of the word Class: Final Grades for the.
8. Click any cell location on the Grade Distribution worksheet to deactivate the chart.

Figure 4.10 “Grade Frequency Distribution Chart” 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− or the C+ to C− categories.

Creating a Chart Sheet

The charts we have created up to this point have been added to, or embedded in, an existing worksheet. Charts can also be placed in a dedicated worksheet called a chart sheetAn Excel worksheet that is designated for a chart.. It is called a chart sheet because it can contain only 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 the Class chart on the Grade Distribution worksheet.
2. Click the Move Chart button in the Design tab of the Chart Tools set of commands. This opens the Move Chart dialog box. You can use this dialog box to move the chart to a different worksheet or create a dedicated chart sheet.
3. Click the New sheet option on the Move Chart dialog box.
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 (see Figure 4.11 “Moving a Chart to a Chart Sheet”). Type Class Grades. This replaces the generic name in the input box.
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 Class Grades.

Figure 4.12 “Chart Sheet Added to the Workbook” shows the Final Grades for the Class column chart in a separate chart sheet. Notice the new sheet tab added to the workbook matches the tab 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.

Frequency Comparison: Column Chart 2

We will create a second column chart to show a comparison between two frequency distributions. Column C on the Grade Distribution worksheet contains data showing the number of students who received grades within each category for the entire college. We will use a column chart to compare the grade distribution for the class (Column B) with the overall grade distribution for the college (Column C). However, since the number of students in the class is significantly different from the total number of students in the college, 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 Formulas command.
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 Formulas command.

Figure 4.13 “Completed Grade Distribution Percentages” shows the completed percentages added to the Grade Distribution worksheet. The column chart 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. Similar to the trend comparison line chart, this chart uses data that is not in a contiguous range. The following steps explain a second method for creating charts with data that is not in a contiguous range:

1. Activate cell H2 on the Grade Distribution worksheet. It is important to note that this is a blank cell that is not adjacent to any data on the worksheet.
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. This adds a blank chart to the worksheet.
4. Click and drag the blank chart so the upper left corner is in the middle of cell H2.
5. Resize the blank 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 P, the top is locked to the top of Row 2, and the bottom is locked to the bottom of Row 16.
6. Click the Select Data button in the Design tab of the Chart Tools section of the Ribbon. This opens the Select Data Source dialog box.
7. Click the Add button on the Select Data Source dialog box. This opens the Edit Series dialog box.
8. In the Series name input box on the Edit Series dialog box, type the word Class.
9. Press the TAB key on your keyboard to advance to the Series values input box on the Edit Series dialog box.
10. Highlight the range E4:E8 on the Grade Distribution worksheet. This automatically adds the range to the Series values input box. You also see bars added to the column chart (see Figure 4.14 “Completed Data Series for the Class Grade Distribution”).

11. Click the OK button on the Edit Series dialog box.

    Figure 4.14 Completed Data Series for the Class Grade Distribution

    

12. Click the Add button on the Select Data Source dialog box.
13. In the Series name input box on the Edit Series dialog box, type the word College.
14. Press the TAB key on your keyboard to advance to the Series values input box on the Edit Series dialog box.
15. Highlight the range F4:F8 on the Grade Distribution worksheet. This automatically adds the range to the Series values input box. You also see bars added to the column chart.
16. Click the OK button on the Edit Series dialog box.
17. Click the Edit button on the right side of the Select Data Source dialog box under the Horizontal (Category) Axis Labels section. This is used to define the labels that will appear on the X axis of the chart and opens the Axis Labels dialog box.
18. Highlight the range A4:A8 on the Grade Distribution worksheet. This adds the range to the Axis Labels dialog box, and the labels appear on the X axis on the column chart (see Figure 4.15 “Final Settings for the Select Data Source Dialog Box”).
19. Click the OK button on the Axis Labels dialog box.

20. Click the OK button on the Select Data Source dialog box.

    Figure 4.15 Final Settings for the Select Data Source Dialog Box

    

21. Click the Chart Title button on the Layout tab of the Chart Tools section of the Ribbon. Select the Above Chart option from the drop-down list.
22. Click in the text box containing the chart title. Delete the generic chart title and replace it with the following: Grade Distribution Comparison.

Figure 4.16 “Completed Grade Distribution Column Chart” 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.

Percent of Total: Pie Chart

The next chart we will demonstrate is a pie chart. A pie chartA chart used to show the percent of total for each component of a data set. 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 the overall spending activity in the health-care industry. The Health Care worksheet contains data that shows total spending in the United States for the years 1969 and 2009. In 1969, the total amount spent in the United States for health-related expenses was over $66 billion. The pie chart shows how this $66 billion was funded. The following steps explain how to accomplish this:

1. Highlight the range A2:B7 on the Health Care worksheet.
2. Click the Insert tab of the Ribbon.
3. Click the Pie button in the Charts group of commands.
4. Select the “Exploded pie in 3-D” option from the drop-down list of options.
5. Click and drag the pie chart so the upper left corner is in the middle of cell E2.

6. 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 M, the top is locked to the top of Row 2, and the bottom is locked to the bottom of Row 17 (see Figure 4.18 “Pie Chart Moved and Resized”).

   Figure 4.18 Pie Chart Moved and Resized

   

7. Click the chart legend once and press the DELETE key on your keyboard. A pie chart typically shows labels next to each wedge. Therefore, the legend is not needed.
8. Click the Data Labels button in the Layout tab of the Chart Tools section of the Ribbon.
9. Select More Data Label Options from the drop-down list. This opens the Format Data Labels dialog box.
10. Click the box next to the Value option under the Label Options section in the Format Data Labels dialog box. This removes the check mark (see Figure 4.19 “Final Settings in the Format Data Labels Dialog Box”).
11. Click the Percentage option under the Label Options section in the Format Data Labels dialog box. A green check should appear in the box next to this option (see Figure 4.19 “Final Settings in the Format Data Labels Dialog Box”).
12. Click the Category Name option under the Label Options section in the Format Data Labels dialog box. A green check should appear in the box next to this option (see Figure 4.19 “Final Settings in the Format Data Labels Dialog Box”).
13. Click the Close button at the bottom of the Format Data Labels dialog box.

14. Click the Home tab of the Ribbon and then click the Bold button. This should bold the data labels on the pie chart.

    Figure 4.19 Final Settings in the Format Data Labels Dialog Box

    

15. Click the chart title twice.
16. Click in front of the year 1969 and type Health Care Spending by Source:.

Figure 4.20 “Final Health Care Pie Chart” shows the completed pie chart. You can quickly see that Health Insurance and Out of Pocket made up the majority of health-care spending in 1969. Similar to the column chart, the key to creating an effective pie chart is the number of categories presented on the chart. 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. In this example, it is easy to see that two categories compose 75% of the total.

Percent of Total Trend: Stacked Column Chart

The last chart type we will demonstrate is the stacked column chartA chart used to show how a percent of total changes over time or between two or more entities.. We use a stacked column chart to show how a percent of total changes over time. For example, the data on the Health Care worksheet shows spending by source for 1969 and 2009. A stacked column chart can show whether there is any change in the percent of total for each source between the two years. The Y axis of the chart shows the percentage from 0% to 100%. The X axis shows the two years: 1969 and 2009. The following steps explain how to create this chart:

1. Highlight the range A2:C7 on the Health Care 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 the drop-down list (see Figure 4.21 “Selecting the 100% Stacked Column Format”).

   Figure 4.21 Selecting the 100% Stacked Column Format

   

   Figure 4.22 “Initial Construction of the 100% Stacked Column Chart” 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 percentages on the Y axis and the years 1969 and 2009 on the X axis. However, notice that Excel places the spend sources on the X axis. The remaining steps explain how to correct this problem and complete the chart:

   Figure 4.22 Initial Construction of the 100% Stacked Column Chart

   

4. 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 (see Figure 4.22 “Initial Construction of the 100% Stacked Column Chart”).
5. Click and drag the chart so the upper left corner is in the middle of cell E19.
6. 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 19, and the bottom is locked to the bottom of Row 37.
7. Click the legend one time and press the DELETE key on your keyboard.
8. Click the Layout tab on the Chart Tools section of the Ribbon.
9. Click the Data Table button in the Labels group of commands and select the Show Data Table with Legend Keys option from the drop-down menu. This is another way of displaying a legend for a column chart along with the numerical values that make up each component.
10. Click the Chart Title button in the Layout tab of the Chart Tools section of the Ribbon.
11. Select the Above Chart option for the drop-down menu.
12. Click the chart title two times. Delete the generic chart title name and type Change in Health Care Spend Source.

Figure 4.23 “Final 100% Stacked Column Chart” shows the final stacked column chart. Notice that the Out of Pocket category, or the amount of cash people paid for health-care expenses, decreased significantly from 1969 to 2009. However, the Health Insurance category increased significantly from 1969 to 2009. Overall, the chart shows that the total out-of-pocket and health insurance expense increased significantly from 1969 to 2009. These two categories made up approximately 75% of total health-care spending in 1969. By 2009, these two categories increased to over 80% of total health-care spending.

X and Y Axis Formats

There are numerous formatting commands we can apply to the X and Y axes of the chart. Although adjusting the font size, style, and color are common, many more options are available through the Format Axis dialog box (see Figure 4.5 “Format Axis Dialog Box”). The following steps demonstrate a few of these formatting techniques on the Grade Distribution Comparison chart:

1. Click anywhere along the X axis (horizontal axis) of the Grade Distribution Comparison chart on the Grade Distribution worksheet.
2. Click the Home tab of the Ribbon.
3. Change the font style to Arial. Notice that as the mouse pointer hovers over a font style, you can preview the change on the chart before you make a selection.
4. Change the font size to 11 points and bold the font. The final appearance of the X axis is shown in Figure 4.24 “Formatted X Axis”.
5. Click anywhere along the Y axis to activate it.

6. Repeat steps 3 and 4.

   Figure 4.24 Formatted X Axis

   

7. Click the Format tab in the Chart Tools section of the Ribbon.
8. Click the Format Selection button in the Current Selection group of commands. This opens the Format Axis dialog box.
9. Click Number from the list of options on the left side of the Format Axis dialog box (see Figure 4.25 “Formatting Numbers on the Y Axis”). The commands in this section of the Format Axis dialog box are used to format numbers that appear on the X and Y axes of a chart.
10. Click in the Decimal places input box and change the value to 0 (see Figure 4.25 “Formatting Numbers on the Y Axis”).
11. Click the Close button at the bottom of the Format Axis dialog box. The formatting adjustments are shown in Figure 4.26 “Completed X and Y Axis Formats”.

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 tab of the Ribbon. The following steps explain how to add these formats:

1. Click the legend on the Grade Distribution Comparison chart in the Grade Distribution worksheet.

2. Click and drag the legend so the top of the legend aligns with the 35% line next to the plot area (see Figure 4.27 “Moving the Legend”).

   Figure 4.27 Moving the Legend

   

3. Change the font style in the Home tab of the Ribbon to Arial.
4. Change the font size to 12 points.
5. Click the bold and italics commands in the Home tab of the Ribbon.
6. Click and drag the left sizing handle so the legend is against the plot area (see Figure 4.28 “Legend Formatted and Resized”).

7. Click and drag the lower center sizing handle so the bottom of the legend is aligned with the 25% line of the plot area (see Figure 4.28 “Legend Formatted and Resized”).

   Figure 4.28 Legend Formatted and Resized

   

8. Click the chart title to activate it.
9. Click the Format tab in the Chart Tools section of the Ribbon.
10. Click the More down arrow in the Shape Styles group of commands to open the complete set of preset format styles (see Figure 4.28 “Legend Formatted and Resized”).
11. Click the Subtle Effect – Blue, Accent 1 option, which is in the fourth row, second style from the left. As the mouse hovers over a style, you can preview the appearance on the chart.
12. In the Home tab of the Ribbon, change the font style to Arial and reduce the font size to 14 points (see Figure 4.29 “Chart Legend and Title Formatted”).

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. Click the Layout tab in the Chart Tools section of the Ribbon.
3. Click the Axis Titles button in the Labels group of commands.

4. Place the mouse pointer over the Primary Vertical Axis TitleCommand used to add a title to the Y axis of a chart. option from the drop-down list. This opens a second drop-down list. Select the Rotated Title option from the second drop-down list. This adds a title next to the Y axis (see Figure 4.30 “Selecting a Title for the Y Axis”).

   Figure 4.30 Selecting a Title for the Y Axis

   

5. Click the Format tab in the Chart Tools section of the Ribbon.
6. Click the Colored Outline – Blue, Accent 1 preset style option in the Shape Styles group of commands.
7. Change the font style in the Home tab to Arial. Change the font size to 11 points.
8. Click in the beginning of the Y axis title and delete the generic title. Type Percent of Enrolled Students.

9. Click and drag the Y axis title so it is between 0% and 30% in the plot area (see Figure 4.31 “Adding and Formatting the Y Axis Title”).

   Figure 4.31 Adding and Formatting the Y Axis Title

   

10. Click the Layout tab in the Chart Tools section of the Ribbon.
11. Click the Axis Titles button in the Labels group of commands.
12. Place the mouse pointer over the Primary Horizontal Axis TitleCommand used to add a title to the X axis of a chart. option. Select Title Below Axis from the second drop-down list.
13. Click the Format tab in the Chart Tools section of the Ribbon.
14. Click the Colored Outline – Blue, Accent 1 preset style option in the Shape Styles group of commands.
15. Change the font style in the Home tab to Arial. Change the font size to 11 points.
16. Click in the beginning of the X axis title and delete the generic title. Type Final Course Grade.

Figure 4.32 “X and Y Axis Titles Added” 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.

Data Series Labels and Formats

Adding labels to the data series of a chart is a key formatting feature. A data seriesA quantitative data set that is displayed graphically on a chart. Data sets are typically displayed in the form of columns or lines on a chart. 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 any red bar representing the College 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. Click the Format tab in the Chart Tools section of the Ribbon.
3. Click the down arrow on the Shape Fill button in the Shape Styles group of commands.

4. Click the orange color square from the drop-down color palette (see Figure 4.33 “Changing the Color of a Data Series”). As you move the mouse pointer over other colors on the palette, you can preview the change on the data bars.

   Figure 4.33 Changing the Color of a Data Series

   

5. Click the Layout tab in the Chart Tools section of the Ribbon.
6. Click the Data Labels button in the Labels group of commands. Select More Data Label Options at the bottom of the drop-down list to open the Format Data Labels dialog box.
7. Click the Number option from the list on the left side of the Format Data Labels dialog box.
8. Select Percentage on the right side of the Format Data Labels dialog box (see Figure 4.34 “Adding Labels to a Data Series”).
9. Click in the Decimal Places input box and change the number of decimal places to zero.
10. Click the Close button at the bottom of the Format Data Labels dialog box.
11. Click the Home tab of the Ribbon.
12. Change the font style to Arial, change the font size to 9 points, and select the Bold command.
13. Click any blue bar in the Class data series.
14. Repeat steps 5 through 12.

Figure 4.35 “Completed Formatting Adjustments for the Data Series” 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.

Formatting the Plot and Chart Areas

The last items we will format on the Grade Distribution Comparison chart are the plot and chart areas. We format these areas primarily to enhance the visibility of the data series. The following steps explain how to add these formatting enhancements to the chart:

1. Click anywhere in the chart area of the Grade Distribution Comparison chart in the Grade Distribution worksheet.
2. Click the Format tab in the Chart Tools section of the Ribbon.
3. Click the down arrow on the Shape Fill button in the Shape Styles group of commands.
4. Select the Tan, Background 2, Darker 25% option from the color palette (see Figure 4.36 “Formatting the Chart Area”).
5. Click anywhere in the plot area to activate it. Be sure not to click a grid line or one of the data series.
6. Click the Format tab in the Chart Tools section of the Ribbon.
7. Click the Shape Effects button in the Shape Styles group of commands.
8. Place the mouse pointer over the Bevel option from the drop-down list. Then select the Circle bevel option from the second drop-down list (see Figure 4.37 “Putting a Bevel Effect on the Plot Area”).

Figure 4.38 “Grade Distribution Comparison Chart with Formats Applied” shows the completed Grade Distribution Comparison chart. The darker shade on the chart area along with the bevel effect on the plot area make the data series the main focal point of the chart.

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 Health Care Spend Source stacked column chart. Series linesLines that are typically used in a stacked column chart to connect the data series between two or more stacks. are commonly used in stacked column charts to show the change from one stack to the next. AnnotationsNotes or comments that explain the nature and source of the data presented on a chart. 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 Change in Health Care Spend Source chart on the Health Care 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 sheet tab label in the New sheet input box: Health Spending Chart. Click the OK button.
3. Click anywhere on the X axis to activate it. In the Home tab of the Ribbon, change the font style to Arial, change the font size to 12 points, and select the bold command.
4. Activate the Y axis and apply the same formatting adjustments as stated in step 3.
5. Add a Y axis title using the Rotated Title option. In the Format tab under the Chart Tools section of the Ribbon, select the first preset style option, Colored Outline – Black, Dark 1, in the Shape Styles group of commands. Then, in the Home tab of the Ribbon, change the font style to Arial and the font size to 14 points.
6. Change the wording of the Y axis title to read Percent of Total Annual Spend.
7. Activate the title of the chart by clicking it once. In the Format tab under the Chart Tools section of the Ribbon, select the first preset style option, Colored Outline – Black, Dark 1, in the Shape Styles group of commands. Then, in the Home tab of the Ribbon, change the font style to Arial.
8. Click anywhere in the chart area to activate it.
9. Click the Format tab in the Chart Tools section of the Ribbon and click the down arrow on the Shape Fill button. Select the Olive Green, Accent 3, Lighter 60% option on the color palette.
10. Click anywhere on the plot area to activate it. Be sure not to click on a grid line.
11. Click the Shape Effects button in the Format tab of the Ribbon. Place the mouse pointer over the Bevel option from the drop-down menu. Select the first option from the Bevel format list, which is the “Circle” bevel option.
12. Click and drag down the top center sizing handle of the plot area approximately one inch (see Figure 4.39 “Adjusting the Size of the Plot Area”).

13. Click and drag up the bottom center sizing handle approximately three-quarters of an inch (see Figure 4.39 “Adjusting the Size of the Plot Area”). This step and step 12 are necessary to create space at the top and bottom of the chart to add annotations.

    Figure 4.39 “Adjusting the Size of the Plot Area” shows the Change in Health Care Spend Source chart prior to adding the series lines and annotations. Notice that the Ribbon has been minimized to improve the visibility of the chart. The remaining steps will focus on adding lines and annotations:

    Figure 4.39 Adjusting the Size of the Plot Area

    

14. Click the Layout tab in the Chart Tools section of the Ribbon.
15. Click the Lines button in the Analysis group of commands.

16. Click the Series Lines option from the drop-down list. This adds lines to the chart, connecting each data series between the two stacks (see Figure 4.40 “Selecting the Series Lines Option”).

    Figure 4.40 Selecting the Series Lines Option

    

17. Click any of the series lines added to the chart. Clicking one line will activate all lines on the chart (see Figure 4.41 “Activating the Series Lines”).

    Figure 4.41 Activating the Series Lines

    

18. Click the Shape Outline button in the Format tab of the Ribbon. Place the mouse pointer over the Weight option and select the “2¼ line weight” option.

    Figure 4.42 “Series Lines Added to the Stacked Column Chart” 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. In this case, the audience can quickly see the decline in the Out of Pocket category (blue) and the increase in the Health Insurance category (red).

    Figure 4.42 Series Lines Added to the Stacked Column Chart

    

19. Click anywhere in the chart area of the Change in Health Care Spend Source chart.
20. Click the Text Box button in the Insert tab of the Ribbon (see Figure 4.43 “Lines Added to the Stacked Column Chart”).
21. 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.43 “Lines Added to the Stacked Column Chart”).
22. Click the Home tab of the Ribbon and change the font style to Arial, change the font size to 10 points, and select the bold and italics commands.

23. Type Dollars in Millions. This tells the audience that the numbers have been truncated and represent denominations in millions. This means you would add six zeros to the end of each number on the chart. Therefore, the Out of Pocket value for 1969 is shown as $22,617 but is actually $22,617,000,000, or $22.6 billion.

    Figure 4.43 Lines Added to the Stacked Column Chart

    

24. Repeat steps 19–22 to add a second text box to the chart. Begin drawing this text box below the first box approximately one inch in from the left edge of the chart (see Figure 4.43 “Lines Added to the Stacked Column Chart”). Complete the formatting changes in step 22 and select the Align Text Right command.
25. Type 100% = in the second text box.
26. Repeat steps 19–22 to add a third text box to the chart. Center this text box over the 1969 stack. In addition to the formatting commands in step 22, select the Center align command and the Underline command.
27. Type $66,172 in the third text box.
28. Repeat steps 19–22 to add a fourth text box to the chart. Center this text box over the 2009 stack. In addition to the formatting commands in step 22, select the Center align command and the Underline command.
29. Type $2,486,293 in the fourth text box.
30. Repeat steps 19–22 to add a fifth text box to the chart. Begin drawing this text box at the bottom left edge of the chart, just below the data table. The text box will need to be at least four inches wide.
31. Type Source: US Department of Health and Human Services in the fifth text box.

Figure 4.44 “Completed Stacked Column Chart with Annotations” shows the completed Change in Health Care Spend Source stacked column chart. The lines and annotations provide key information for understanding the data and interpreting the trends presented on the chart.

Supply and Demand: The Scatter Chart

A common use for a scatter chart is the study of supply and demand curves. This is because the data points for both the supply and demand lines require quantitative values on both the X and Y axes. The Y axis contains the price of a certain good or item; the X axis contains the quantity sold for that good or item. Fundamental economic laws state that as prices rise, sellers are willing to increase supply and sell more goods. However, the reverse is true for consumers. As prices rise, consumers purchase fewer goods. The Supply & Demand worksheet contains hypothetical data for the supply and demand of breakfast cereal. There are ten data points to show the change in supply and demand as the price changes in Column A. The values you see in Columns A through C are formula outputs that are driven by the percentage in cell C14. For example, if the percentage in cell C14 is changed to 10, each price listed in Column A will increase, as shown in Figure 4.45 “Hypothetical Supply and Demand Data”.

We will use the scatter chart to study the change in quantity supplied and demanded as the price increases over ten data points, as shown in Figure 4.45 “Hypothetical Supply and Demand Data”. For many of the charts demonstrated in this chapter, we were able to highlight a range of cells and insert the chart type we needed. This was especially the case when the data was in a contiguous range of cells. However, this method rarely works when creating a scatter chart, even if the data are in a contiguous range. As a result, the method we present here starts with a blank chart and demonstrates how each data series is added to the chart individually. The following steps explain how we create this chart:

1. Change the value in cell C14 on the Supply & Demand worksheet to zero.
2. Activate cell E1 on the Supply & Demand worksheet. It is important to note that this cell location is not adjacent to any data on the worksheet.
3. Click the Scatter button from the Charts group of commands on the Insert tab of the Ribbon.

4. Select the Scatter with Smooth Lines and Markers format from the drop-down list of options (see Figure 4.46 “Selecting a Scatter Chart Format”). This adds a blank chart to the worksheet.

   Figure 4.46 Selecting a Scatter Chart Format

   

5. Click and drag the chart so the upper left corner is in the center of cell E2.
6. 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 M, the top is locked to the top of Row 2, and the bottom is locked to the bottom of Row 17.
7. Click the Design tab in the Chart Tools section of the Ribbon. Then click the Select Data button in the Data group of commands. This opens the Select Data Source dialog box.
8. Click the Add button on the left side of the Select Data Source dialog box. This opens the Edit Series dialog box. Notice on this dialog box there are inputs for defining values for both the X and Y axes. Charts that we previously created using this method only had an input for putting values on the Y axis.
9. Type the series name Demand. This should appear in the Series name input box.
10. Press the TAB key on your keyboard to advance to the Series X values input box on the Edit Series dialog box.
11. Highlight the range B3:B12 on the Supply & Demand worksheet. You will see this range appear in the Series X values input box after it is highlighted.
12. Press the TAB key on your keyboard to advance to the Series Y values input box on the Edit Series dialog box.

13. Highlight the range A3:A12 on the Supply & Demand worksheet.

    Figure 4.47 “Defining the Demand Data Series” shows the final settings in the Edit Series dialog box for the Demand data series. You will see that as the X and Y axis values are defined in the dialog box, they appear on the chart. The chart in this figure shows the price along the Y axis and quantity along the X axis.

    Figure 4.47 Defining the Demand Data Series

    

14. Click the OK button at the bottom of the Edit Series dialog box.
15. Click the Add button on the left side of the Select Data Source dialog box.
16. Type the series name Supply. This should appear in the Series name input box.
17. Press the TAB key on your keyboard to advance to the Series X values input box on the Edit Series dialog box.
18. Highlight the range C3:C12 on the Supply & Demand worksheet. This range appears in the Series X values input box after it is highlighted.
19. Press the TAB key on your keyboard to advance to the Series Y values input box on the Edit Series dialog box.
20. Highlight the range A3:A12 on the Supply & Demand worksheet.
21. Click the OK button at the bottom of the Edit Series dialog box.
22. Click the OK button at the bottom of the Select Data Source dialog box.

Figure 4.48 “Scatter Chart Showing One Price” shows the appearance of the scatter chart before any formatting enhancements are applied. Notice only two plot points are located on the chart. This is because the price change value in cell C14 is still zero. Therefore, the data are not reflecting any change in price, quantity demanded, or quantity supplied. The chart shows that at the current price of $1.50, suppliers are willing to provide fewer units compared with the number of units consumers are willing to buy.

The following steps explain the formatting enhancements we will apply to the scatter chart shown in Figure 4.48 “Scatter Chart Showing One Price”:

1. Add a title to the chart by clicking the Chart Title button in the Layout tab of the Chart Tools section of the Ribbon. Use the Above Chart option from the drop-down list.
2. Select Subtle Effect – Orange, Accent 6 from the preset style list in the Shape Styles group of commands on the Format tab of the Ribbon.
3. Change the font style of the chart title to Arial and the font size to 14 points.
4. Change the wording of the chart title as follows: Supply and Demand for Breakfast Cereal.
5. Add a title to the Y axis. Use the Rotated Title option from the Primary Vertical Axis Title drop-down list after clicking the Axis Titles button in the Layout tab of the Ribbon.
6. Repeat steps 2 and 3 to format the Y axis title. However, change the font size to 12 points.
7. Change the wording of the Y axis title as follows: Price per Unit.
8. Add a title to the X axis.
9. Repeat steps 2 and 3 to format the X axis title. However, change the font size to 12 points.
10. Change the wording of the X axis title as follows: Quantity in Units.
11. Make the following format changes to the X and Y axis values: font style Arial, font size 11 points, and bold.

12. Change the color of the chart area to Aqua, Accent 5, Lighter 40% (see Figure 4.49 “Formatting Enhancements Added to the Scatter Chart”).

    Figure 4.49 Formatting Enhancements Added to the Scatter Chart

    

13. Apply a bevel effect to the plot area. Use the Circle format option from the Bevel drop-down list of options.
14. Change the font style of the legend to Arial and bold the font.
15. Change the value in cell C14 to 2. Then change it to 4 and then to 8. Change the value one more time to 14. As you change the values in cell C14, you will see the lines change on the chart.

Figure 4.50 “Scatter Chart with Price Change at 2%” shows the completed scatter chart when the Price Change is set to 2%, and Figure 4.51 “Scatter Chart with Price Change at 14%” shows the same chart when the Price Change is set to 14%. The point at which the demand and supply lines intersect on Figure 4.51 “Scatter Chart with Price Change at 14%” is known as the market equilibrium point. The market equilibriumA state in which the quantity demanded equals the quantity supplied at a specific price. is where the quantity demanded equals the quantity supplied at a specific price. The price where quantity demanded equals quantity supplied is referred to as the equilibrium priceThe price where quantity demanded equals quantity supplied..

Changing the Scale of the X and Y Axes

For all the charts demonstrated in this chapter, Excel has automatically established the scale for the Y axis. For scatter charts, Excel has also established the scale for the X axis. The axis scaleThe minimum and maximum value that appears on the X or Y axis of a chart. is the minimum and maximum value that appears on an axis. For example, in Figure 4.51 “Scatter Chart with Price Change at 14%”, the Y axis scale is set to a minimum value of zero and a maximum value of 6.00. Although this is a very convenient feature of Excel, you may want to change the scale in some instances. If you change the value in cell C14 on the Supply & Demand worksheet, the lines jump or shift on the plot area of the chart. This is because Excel keeps rearranging the scale of both the X and Y axes. When studying the shape of lines, it is best to set the scale so it does not change. The following steps explain how to accomplish this:

1. Change the value in cell C14 on the Supply & Demand worksheet to zero.
2. Click anywhere on the Y axis of the chart.
3. Click the Format Selection button in the Layout tab of the Chart Tools section of the Ribbon. This opens the Format Axis dialog box.
4. Click the Fixed option next to the Minimum setting under the Axis Options in the Format Axis dialog box. This ensures that the minimum value for the Y axis will always be zero.
5. Click the Fixed option next to the Maximum setting under the Axis Options in the Format Axis dialog box.
6. Click in the input box next to the Maximum setting. Remove the 1.6 and enter the number 5.0. We will not be studying the behavior of supply and demand beyond a $5.00 price point, so there is no need to extend the Y axis beyond this point.
7. Click the Fixed option next to the Major Unit setting under the Axis Options in the Format Axis dialog box.
8. Click in the input box next to the Major Unit setting and change the value from 0.2 to 0.5 (see Figure 4.52 “Setting the Y Axis Scale”). This allows us to measure the plot points in $0.50 intervals along the Y axis. When the axis extends to $5.00, $0.20 intervals may place too many values along the Y axis, making it difficult to read.

9. Click the Close button at the bottom of the Format Axis dialog box.

   Figure 4.52 Setting the Y Axis Scale

   

10. Click anywhere along the X axis of the chart.
11. Click the Format Selection button in the Layout tab of the Chart Tools section of the Ribbon. This opens the Format Axis dialog box for the X axis.
12. Click the Fixed option next to the Minimum setting under the Axis Options in the Format Axis dialog box. This ensures that the minimum value for the X axis will always be zero.
13. Click the Fixed option next to the Maximum setting under the Axis Options in the Format Axis dialog box.
14. Click in the input box next to the Maximum setting. Remove the 500.0 and enter the number 450.0. The number of units supplied or demanded will not exceed 450 based on the price points in our study. There is no need to extend the X axis to 500.
15. Click the Fixed option next to the Major Unit setting under the Axis Options in the Format Axis dialog box.
16. Click in the input box next to the Major Unit setting and change the value from 100.0 to 50.0. This allows us to measure the plot points in 50-unit intervals along the X axis.
17. Click the Close button at the bottom of the Format Axis dialog box.
18. Change the value in cell C14 to 2. Then change it to 4 and then to 8. Change the value one more time to 14. As you change the values in cell C14, the lines change but they no longer jump or shift since the scale of both axes is fixed.

Figure 4.53 “Final Appearance of the Scatter Chart” shows the final appearance of the scatter chart after the scale is set for both the X and Y axes. Notice that market equilibrium is achieved at a price of approximately $2.50.

Adding a Trendline and Equation

A trendline can be applied to a chart to estimate or predict where plot points may occur at various points along the X and Y axes. Excel enables you to add a trendline to a chart and also provides the equation you can use to plot additional points. The following steps explain how to accomplish this:

1. Set the value in cell C14 on the Supply & Demand worksheet to 14.
2. Click anywhere in the chart area of the scatter chart to activate it.
3. Click the Trendline button in the Layout tab of the Ribbon. Select the Linear Trendline option from the drop-down list.

4. Select the Demand option from the Add Trendline dialog box and click the OK button. This adds a new line to the plot area of the chart as well as the legend.

   Figure 4.54 “Adding a Linear Trendline” shows the scatter chart after adding a linear trendline. Notice that the line goes through only two points on the demand line. This indicates that this trendline may not be a good fit for the line that has been created on the chart.

   Figure 4.54 Adding a Linear Trendline

   

   Finding the right shape for a trendline may require trying a few different options. As shown in Figure 4.54 “Adding a Linear Trendline”, the linear trendline is not a good fit for the shape of the demand line. The remaining steps will demonstrate how to remove a trendline and access more trendline options:

5. Click the Trendline button in the Layout tab of the Ribbon. Select the None option from the drop-down list. This removes the trendline from the chart.
6. Click the Trendline button in the Layout tab of the Ribbon again. This time, select More Trendline Options from the drop-down list.
7. Select the Demand option from the Add Trendline dialog box and click OK. This opens the Format Trendline dialog box.
8. Select the Power option from the Format Trendline dialog box.
9. Click the “Display Equation on chart” option at the bottom of the Format Trendline dialog box (see Figure 4.55 “The Format Trendline Dialog Box”).
10. Click the Close button at the bottom of the Format Trendline dialog box.

Figure 4.56 “Scatter Chart with a Power Trendline” shows the scatter chart with the Power trendline added for the demand series. Notice that the line fits perfectly over the demand series in the plot area. In fact, it may be difficult to see the line in the figure. This indicates that the trendline is an excellent fit for the demand line. As a result, we can be confident in using this line to predict other demand values along the X and Y axes. You can also see that the equation for this trendline has been added to the plot area of the chart. We can use the equation to calculate the price for each quantity value substituted for X. For example, if the number 150 is substituted for X in the equation, the result is a price of $2.59. Based on the values used to create the chart, this result appears to be accurate.

Pasting a Chart Image into Word

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 Health Care Spend Source stacked column chart (see Figure 4.44 “Completed Stacked Column Chart with Annotations”) is being used in a written policy report. The following steps demonstrate how to paste an image, or picture, of this chart into a Word document:

1. Click below the figure heading in the Word document that reads: Figure 6: Health Care Spending in the U.S. The image of the stacked column chart will be placed below this heading.
2. Open the Excel Objective 4.16 follow-along file.
3. Activate the Change in Health Care Spend Source chart in the Health Spending Chart worksheet.
4. Click the Copy button in the Home tab of the Ribbon.
5. Go back to the Excel Objective 4.17 Word document by clicking the file in the taskbar.
6. Click the drop-down arrow below the Paste button in the Home tab of the Ribbon. Click the Picture option from the drop-down list, which is the last option on the far right (see Figure 4.57 “Paste Picture Option for Word”).
7. Click anywhere on the picture of the chart to activate it.

8. Click the Format tab under the Picture Tools section of the Ribbon (see Figure 4.58 “Changing the Size of a Picture in Word”).

   Figure 4.57 Paste Picture Option for Word

   

9. Click the down arrow on the Shape Width button in the Size group of commands (see Figure 4.58 “Changing the Size of a Picture in Word”). Continue to click the down arrow until the width of the picture is 5.5. As you reduce the width of the picture, the height is automatically reduced as well.

Figure 4.59 “Final Appearance of Pasting a Chart Image into Word” shows the final appearance of the Change in Health Care Spend 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.

Pasting a Linked Chart Image into PowerPoint

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 for setting prices for a hypothetical breakfast cereal company. We will paste the scatter chart showing the supply and demand for breakfast cereal into this PowerPoint slide. However, instead of pasting an image, as demonstrated in the Word document, we will establish a linkA dynamic connection between two files, worksheets, or cell locations. Any change in the source file will be reflected in the file, worksheet, or cell that contains the 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 the Excel Objective 4.16 follow-along file.
2. Activate the scatter chart in the Supply & Demand worksheet.
3. Click the Copy button in the Home tab of the Ribbon.
4. Go back to the Excel Objective 4.18 PowerPoint file by clicking the file in the taskbar.
5. Click the down arrow below the Paste button in the Home tab of the Ribbon in the PowerPoint file.

6. Select the Keep Source Formatting & Link Data option from the drop-down list (see Figure 4.60 “Creating a Link to an Excel Chart in PowerPoint”). 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 appear on the PowerPoint slide.

   Figure 4.60 Creating a Link to an Excel Chart in PowerPoint

   

7. Click anywhere in the plot area of the scatter chart pasted into the PowerPoint slide. You will see the same Excel Chart Tools tabs added to the Ribbon (see Figure 4.61 “Modifying an Excel Chart Pasted into a PowerPoint Slide”).

   Figure 4.61 Modifying an Excel Chart Pasted into a PowerPoint Slide

   

8. Click the down arrow next to the Shape Fill button in the Format tab of the Ribbon. Select the white color block from the palette (see Figure 4.61 “Modifying an Excel Chart Pasted into a PowerPoint Slide”).
9. Go back to the Excel Objective 4.16 file by clicking it in the taskbar.
10. In the Supply & Demand worksheet, change the value in cell C14 to 6.
11. Go back to the Excel Objective 4.18 PowerPoint file by clicking it in the taskbar.
12. Click the Design tab in the Chart Tools section of the Ribbon. Click the Refresh Data button (see Figure 4.62 “Refreshing a Linked Excel Chart Pasted into a PowerPoint Slide”). The change made in the Excel workbook is now reflected on the PowerPoint slide.

Figure 4.62 “Refreshing a Linked Excel Chart Pasted into a PowerPoint Slide” shows the appearance of the scatter chart in the PowerPoint slide after the color of the plot area was changed back to white. Figure 4.63 “Final Chart Linked to a PowerPoint Slide” shows the appearance of the scatter plot after the change was made in the Supply & Demand worksheet in the Excel file. The change that was made in the Excel file will appear in the PowerPoint file after clicking the Refresh Data button.

Freeze Panes

The Investment Detail worksheet shown in Figure 3.2 “Investment Detail Worksheet” contains the majority of the information used to create the Portfolio Summary worksheet shown in Figure 3.1 “Completed Personal Investment Portfolio Workbook”. When you first open the worksheet, you will notice it is not possible to view all twenty-four columns on your computer screen. As you scroll to the right to view the rest of the columns, you will lose site of the row headings in Columns A and B. The headings in these columns show the investment that pertains to the data in Columns C through X. To solve this problem of viewing the row headings while scrolling through the remaining columns in the worksheet, we will use the Freeze Panes command.

The Freeze PanesAn Excel command that allows you to lock specific columns and rows in place while scrolling through a large worksheet. command allows you to scroll across the Investment Detail worksheet while keeping the row headings in Columns A and B locked in place. The following steps explain how to do this:

1. Click cell C4 on the Investment Detail worksheet. We select this cell because the Freeze Panes option locks the columns to the left of the activated cell as well as the rows above the activated cell.
2. Click the View tab on the Ribbon.
3. Click the Freeze Panes button (see Figure 3.3 “Freeze Panes Command”).
4. Click the Freeze Panes option from the drop-down list of options.

Once you click the Freeze Panes option shown in Figure 3.3 “Freeze Panes Command”, Columns A and B are locked in place as you scroll through the columns in the worksheet. Since this is a large worksheet, you may find it easier to navigate the columns by using the arrow keys on your keyboard. However, since rows 1 and 2 contain merged cells, make sure a cell location is activated below Row 2 before you begin using the arrow keys. Figure 3.4 “Freeze Panes Command Activated on the Investment Detail Worksheet” shows the appearance of the Investment Detail worksheet after the Freeze Panes command has been activated. To deactivate the Freeze Panes command, click the Freeze Panes button again and select the Unfreeze Panes option.

Formula and Functions Review

We will begin developing the personal investment portfolio workbook by adding several formulas and functions. The formulas and functions we will add were illustrated in detail in Chapter 2 “Mathematical Computations”. Therefore, the steps provided in this chapter will be brief. After the formulas and functions are added to the Investment Detail worksheet, we can add the logical and lookup functions. However, before proceeding, let???s review the investment type definitions in Table 3.1 “Investment Types in Column A of the Investment Detail Worksheet”. Table 3.1 “Investment Types in Column A of the Investment Detail Worksheet” provides a definition for each of the investment types listed in Column A of the Investment Detail worksheet. This project assumes that the personal investment portfolio comprises four types of investments. The reason we include a variety of investment types in any portfolio is to manage our total risk, or potential of losing money. When building an investment portfolio, it is important to keep in mind that investments of all types can dramatically increase or decrease in value over a short period of time. Managing risk requires that your money is not concentrated in one type of investment.

We will begin adding formulas and functions to the Investment Detail worksheet in sections. If you scroll across all the columns in the worksheet, you will notice the worksheet includes five distinct sections. Four of the five sections contain columns that need to be completed with formulas and functions before we can add the logical and lookup functions. Table 3.2 “Definitions for Columns A through G of the Investment Detail Worksheet” contains definitions for each of the columns in the Descriptive Information section (Columns A through D) and the Purchase section (Columns E through G). It will be helpful to understand the purpose of these columns as we complete this worksheet.

The Descriptive Information section of the Investment Detail worksheet (Columns A through D) contains only one blank column, which will be completed using a lookup function. Therefore, we will proceed to the Purchase section (Columns E through G) where the Cost of Purchase column is blank. The following steps explain how to enter the formula into this column:

1. Click cell G4 on the Investment Detail worksheet.
2. Type an equal sign (=).
3. Enter a formula that multiplies the Shares Purchased (cell E4) by the Purchase Price per Share (cell F4).
4. Copy the formula in cell G4.
5. Highlight the range G5:G18.
6. Click the down arrow on the Paste button in the Home tab of the Ribbon.
7. Click the Formulas button from the list of options. This is the Paste Formulas command, which pastes only the formula without any associated formats for the copied cell location.
8. Click cell E19 on the Investment Detail worksheet.
9. Press and hold the ALT key on your keyboard, then press the equal sign (=). This is the shortcut for the Auto Sum feature.
10. Press the ENTER key on your keyboard.
11. Click cell G19 on the Investment Detail worksheet.
12. Repeat step 9.
13. Press the ENTER key on your keyboard.

Figure 3.5 “Completed Formula in the Cost of Purchase Column” shows the formula that was entered into cell G4 in the Purchase section of the Investment Detail worksheet. You can also see the results of the formula after it is pasted into the range G5:G18. The Paste Formulas option was used to paste the formula into this range so the borders would not be altered.

Table 3.3 “Definitions for Columns H through K of the Investment Detail Worksheet” shows the definitions for the Current Value section (Columns H through K) of the Investment Detail worksheet.

We will add a basic formula to the Current Purchase Value and Current Investment Value columns. For the Estimated Dividend Payments column, we will use the FV (future value) function to estimate the dividend payments. The following explains how we add the FV function to the Estimated Dividend Payments column:

1. Click cell J4 and type an equal sign (=).
2. Type the function name FV followed by an open parenthesis (().
3. Click cell D4, type a forward slash (/) for division, and then type 12. This divides the rate in the Dividend/Yield column by 12. The length of ownership of an investment is expressed in terms of months in Column Q. Therefore, the rate for the FV function must be expressed in terms of months by dividing the annual rate by 12.
4. Type a comma.
5. Click cell Q4, which contains the number of months owned or the term of the future value calculation.
6. Type a comma followed by a zero (,0). We are not calculating an annuity or periodic investment in this example, so the PMT argument will be defined with a zero. Type a comma to advance the function to the Pv argument.
7. Type a minus sign (???) and click cell G4. This is the cost of the investment purchase previously calculated.
8. Type a closing parenthesis ()).
9. Type a minus sign (???) and click cell G4. By itself, the FV function is calculating the total value of the investment with dividends or interest earned. To show only the amount of dividends or interest earned, we subtract the cost of the investment purchase in G4 from the result of the FV function.
10. Press the ENTER key on your keyboard.
11. Adjust the decimal places for the output of the FV function to zero.
12. Copy the FV function in cell J4 and paste it into the range J5:J18 using the Paste Formulas command.

Figure 3.6 “Completed FV Function in the Estimated Dividend Payments Column” shows the completed FV function in cell J4 of the Estimated Dividend Payments column. It is important to reduce the decimal places to zero after you enter the function into cell J4. Excel does not display the result of the function until the decimal places are removed because of the column width.

The following steps explain how to add the formulas for the Current Purchase Value and Current Investment Value columns:

1. Click cell I4 on the Investment Detail worksheet.
2. Enter a formula that multiplies the Current Price in cell H4 by the Shares Purchased in cell E4.
3. Copy the formula in cell I4 and paste it into the range I5:I18 using the Paste Formulas command.
4. Click cell K4 on the Investment Detail worksheet.
5. Enter a formula that adds the Current Purchase Value in cell I4 to the Estimated Dividend Payments in cell J4.
6. Copy the formula in cell K4 and paste it into the range K5:K18 using the Paste Formulas command.
7. Click cell K19 on the Investment Detail worksheet.
8. Enter a SUM function that adds the values in the range K4:K18.

Figure 3.7 “Completed Current Value Section of the Investment Detail Worksheet” shows the completed columns of the Current Value section in the Investment Detail worksheet. The formula used to calculate the Current Investment Value illustrates why we used the FV function to calculate the estimated dividend or interest payments for an investment. Investments that earn interest or dividends can achieve growth in two ways. The first way is through interest or dividend payments. The second way is through changes in the price paid for the investment. The formula used to calculate the Current Purchase Value is taking the number of shares purchased for each investment and multiplying it by the current market price. Therefore, the Current Investment Value takes into account any changes in the investment price by adding the purchase value at the current market price to any dividends or interest payments earned.

Table 3.4 “Definitions for Columns L through R of the Investment Detail Worksheet” provides definitions for the Percent of Portfolio section of the Investment Detail worksheet (Columns L through R).

The Percent of Portfolio section of the Investment Detail worksheet (Columns L through R) requires two formulas and one function. The following steps explain how we add them to the worksheet:

1. Click cell L4 in the Investment Detail worksheet.
2. Enter a formula that divides the Current Investment Value in cell K4 by the total in cell K19.
3. Place an absolute reference on cell K19 in the formula by placing the cursor in front of the column letter and pressing the F4 key on your keyboard.
4. Copy the formula and paste it into the range L5:L18 using the Paste Formulas command.
5. Click cell N4 in the Investment Detail worksheet.
6. Enter a formula that subtracts the Target Percent of Portfolio (cell M4) from the Current Percent of Portfolio (cell L4): L4???M4.
7. Copy the formula and paste it into the range N5:N18 using the Paste Formulas command.
8. Click cell Q19 in the Investment Detail worksheet.
9. Enter an AVERAGE function that calculates the average of the values in the range Q4:Q18.

Figure 3.8 “Percent of Portfolio Section of the Investment Detail Worksheet” shows the results of adding two formulas and a function to the Percent of Portfolio section of the Investment Detail worksheet. Notice the absolute reference added to the cell reference for K19 in the formula in the Current Percent of Portfolio column.

Table 3.5 “Definitions for Columns S through X of the Investment Detail Worksheet” provides definitions for the columns in the Performance Analysis section of the Investment Detail worksheet.

Most of the columns in the Performance Analysis section of the Investment Detail worksheet will be completed with formulas and functions. The following steps explain how we add them to the worksheet:

1. Click cell S4 on the Investment Detail worksheet.
2. Enter a formula that subtracts the value in the Cost of Purchase column (cell G4) from the value in the Current Investment Value column (cell K4): K4???G4.
3. Copy the formula and paste it into the range S5:S19 using the Paste Formulas command. Note that this formula will be used to calculate the output for the Total row in this column. The results of the formula are showing how much money has been earned or lost for each investment. It is important to note that these gains or losses do not actually happen unless the investment is sold.
4. Click cell T4 on the Investment Detail worksheet.
5. Enter a formula that divides the Unrealized Gain/Loss (cell S4) by the Cost of Purchase (cell G4): S4/G4.
6. Copy the formula in cell T4 and paste it into the range T5:T19 using the Paste Formulas command.
7. Click cell V4 on the Investment Detail worksheet.
8. Enter a formula that divides the Percent Gain/Loss (cell T4) by the result of dividing the Months Owned (cell Q4) by 12: T4/(Q4/12). Dividing the Months Owned value by 12 expresses the amount of time an investment has been owned in terms of years. The benchmark growth rates for most investments are expressed in terms of annual return rates. Therefore, this formula must first express the amount of time an investment has been owned in terms of years. Then the total percentage gain or loss for each investment is divided by the length of ownership in years to calculate the actual annual rate of return.
9. Copy the formula in cell V4 and paste it into the range V5:V19 using the Paste Formulas command.
10. Click cell W4 on the Investment Detail worksheet.
11. Enter a formula that subtracts the Target Annual Growth Rate (cell U4) from the Actual Annual Growth Rate (cell V4): V4???U4.
12. Copy the formula in cell W4 and paste it into the range W5:W18 using the Paste Formulas command.

Figure 3.9 “Performance Analysis Section of the Investment Detail Worksheet” shows the results of the formulas added to the Performance Analysis section of the Investment Detail worksheet. This completes the required formulas and functions necessary to add before moving on to the logical and lookup functions of the chapter.

The Logical Test

A key component for the logical functions that will be demonstrated in this section is the logical testAn expression used to evaluate the contents of a cell location. The logical test typically contains comparison operators such as equal to (=), greater than (>), less than (<), and so on. The results of the logical test can be either true or false. An example of a logical test is B8 >= 25, which is read as ???if the value in cell B8 is greater than or equal to 25.???. A logical test is used in logical functions to evaluate the contents of a cell location. The results of the logical test can be either true or false. For example, the logical test C7 = 25 (read as ???if the value in cell C7 is equal to 25???) can be either true or false depending on the value that is entered into cell C7. A logical test can be constructed with a variety of comparison operators, as shown in Table 3.6 “Comparison Operator Symbols and Definitions”. These comparison operators will be used in the logical test arguments for the logical functions demonstrated in this chapter.

A logical test will be used to evaluate the contents of a cell location in the Investment Detail worksheet. We will first demonstrate how the logical test is used to evaluate the contents of a cell location. Then we will use this logical test in the IF function, which will be demonstrated next. The following steps explain how the logical test is constructed:

1. Click cell R4 on the Investment Detail worksheet.
2. Type an equal sign (=).
3. Click cell Q4.
4. Type the greater than sign (>) followed by an equal sign (=).

5. Type the number 12. This completes the logical test, which is shown in Figure 3.10 “Logical Test Entered into the Investment Detail Worksheet”. The logical test would be stated as: ???If the value in cell Q4 is greater than or equal to 12.???

   Figure 3.10 Logical Test Entered into the Investment Detail Worksheet

   

6. Press the ENTER key on your keyboard. Notice that the output of the logical test is the word TRUE. This is because the value in cell Q4 is 48, which is greater than 12 (see Figure 3.11 “Output of the Logical Test”).
7. Copy the logical test in cell R4 and paste it into the range R5:R18 using the Paste Formulas command.

Figure 3.11 “Output of the Logical Test” shows the results of the logical test after it is pasted into the range R5:R18. Notice that for any values that are less than 12 in the range Q4:Q18, the logical test produces an output of FALSE.

IF Function

The IF function is used to produce a custom output based on the results of a logical test. If the results of the logical test are TRUE, the IF function can display a specific number or text, or perform a calculation. If the results of the logical test are FALSE, the IF function can display a different number or text, or perform a different calculation. The arguments of the IF function are defined in Table 3.7 “Arguments for the IF Function”.

We will use the IF function in the Percent of Portfolio section of the Investment Detail worksheet. We will use the logical test that was previously demonstrated within the IF function to determine if an investment has been held for a short or long period of time. For tax purposes, an investment is considered short-term if it is held less than twelve months. This requires the investor to pay a higher tax percentage for any profit earned on the investment. An investment held twelve months or longer is considered a long-term investment. The following explains how the IF function is used to identify which investments are long term or short term:

1. Highlight the range R4:R18 on the Investment Detail worksheet and press the DELETE key on your keyboard. This will remove the logical test and allow us to replace it with an IF function.
2. Click cell R4 on the Investment Detail worksheet.
3. Click the Formulas tab on the Ribbon.
4. Click the Logical button in the Function Library group of commands.

5. Click the IF function from the list of functions (see Figure 3.12 “Selecting the IF Function from the Function Library”). This opens the Function Arguments dialog box.

   Figure 3.12 Selecting the IF Function from the Function Library

   

6. Click the Collapse Dialog button next to the Logical_test argument (see Figure 3.13 “Logical_Test Argument Defined”).
7. Click cell Q4 and press the ENTER key on your keyboard.
8. Type the greater than sign (>) followed by an equal sign (=).

9. Type the number 12.

   Figure 3.13 “Logical_Test Argument Defined” shows the appearance of the IF Function Arguments dialog box after defining the Logical_test argument. Notice that next to the Logical_test input box, Excel shows that the results of the test are true. This makes sense given that the value in cell Q4 is 48, which is greater than 12.

   Figure 3.13 Logical_Test Argument Defined

   

10. Press the TAB key on your keyboard to advance to the next argument, which is Value_if_true.
11. Type the word Long in quotation marks. If you forget to put words or text in quotation marks using the Function Arguments dialog box, Excel will insert the quotation marks for you.
12. Press the TAB key on your keyboard to advance to the next argument, which is Value_if_false.
13. Type the word Short in quotation marks.
14. Click the OK button on the Function Arguments dialog box to complete the function.
15. Copy the IF function in cell R4 and paste it into the range R5:R18 using the Paste Formulas command.

Figure 3.14 “Completed Function Arguments Dialog Box for the IF Function” shows the completed Function Arguments dialog box for the IF function. Notice that the results of the function are displayed in the dialog box. Since the value in cell Q4 is greater than 12, the word Long will be displayed in cell R4.

Figure 3.15 “IF Function Output” shows the completed Long/Short Indicator column on the Investment Detail worksheet. Notice the word Short is displayed for any investment held less than twelve months.

The OR Function

The OR function is similar to the IF function in that it uses a logical test to evaluate the contents of a cell location. However, the OR function allows you to define several logical tests as opposed to just one. If one of the logical tests is true, the output of the function will be the word TRUE. If all the logical tests are false, the output of the function will be the word FALSE. This differs from the IF function because the output of the function is only the word TRUE or the word FALSE. As a result, the OR function is commonly used within the IF function to enable specific outputs to be defined.

We will use the OR function in the Performance Indicator column on the Investment Detail worksheet. The purpose of this column is to identify any investment where either the Unrealized Gain/Loss is less than zero or the Target vs. Actual Growth Rate is less than ???1%. We will use the function in the logical test of an IF function so we can define a specific output based on the results of the OR function. However, we will first demonstrate how the OR function works by itself, which is outlined in the following steps:

1. Click cell X4 on the Investment Detail worksheet.
2. Type an equal sign (=).
3. Type the function name OR followed by an open parenthesis (().
4. Click cell S4 on the Investment Detail worksheet.
5. Type the less than symbol (<) followed by a zero. This completes the first logical test, which is evaluating if the value in cell S4 is less than zero.
6. Type a comma. This advances the function to a second logical test.
7. Click cell W4 on the Investment Detail worksheet.
8. Type the less than symbol (<) followed by ???1%. Be sure to include the minus sign and percent symbol. This completes the second logical test, which is evaluating if the value in cell W4 is less than ???1%.
9. Type a closing parenthesis ()) and press the ENTER key on your keyboard.
10. Copy the OR function in cell X4 and paste it into the range X5:X18 using the Paste Formulas command.

Figure 3.16 “Completed OR Function by Itself” shows the construction and result of the OR function by itself. Notice that the only output of the function is the word TRUE or the word FALSE. If either the Unrealized Gain/Loss is less than zero or the Target vs. Actual Growth Rate is less than ???1%, the function shows the word TRUE. However, these descriptions will not be helpful for the person using this worksheet. Displaying the words OK or Warning would be far more helpful in identifying investments that need to be evaluated. We can do this if we use the OR function in the logical test argument of the IF function. The following steps explain how to accomplish this:

1. Highlight the range X4:X18 on the Investment Detail worksheet and press the DELETE key on your keyboard. We are going to start over by creating an IF function.
2. Click cell X4 on the Investment Detail worksheet.
3. Type an equal sign (=).
4. Type the function name IF followed by an open parenthesis (().
5. Type the function name OR followed by an open parenthesis ((). The OR function is being placed into the logical_test argument of this IF function.
6. Click cell S4 on the Investment Detail worksheet.
7. Type the less than symbol (<) followed by a zero.
8. Type a comma. This advances the function to a second logical test.
9. Click cell W4 on the Investment Detail worksheet.
10. Type the less than symbol (<) followed by ???1%.
11. Type a closing parenthesis ()).
12. Type an equal sign (=).
13. Type the word TRUE. Do not put the word inside quotation marks.
14. Type a comma. This completes the logical_test argument of the IF function. We can now go on to define the value_if_true and the value_if_false arguments. This will allow us to specify what the output of the function should be instead, using the OR function outputs of either TRUE or FALSE.
15. Type the word Warning. Be sure to enclose the word in quotation marks.
16. Type a comma. This will advance the function to the value_if_false argument.
17. Type the word OK. Be sure to enclose the word in quotation marks.
18. Type a closing parenthesis ())and press the ENTER key on your keyboard.
19. Copy the IF function in cell X4 and paste it into the range X5:X18 using the Paste Formulas command.

Figure 3.17 “OR Function in the Logical Test of the IF Function” shows the OR function within the logical_test argument of the IF function. The logical test of the IF function is now evaluating if the results of the OR function are true.

The AND Function

The AND function is almost identical to the OR function in that it is composed of only logical tests and produces one of two possible outputs: TRUE or FALSE. However, all logical tests defined for the AND function must be true in order to produce a TRUE output. If one logical test is false, the function will produce a FALSE output. We will use the AND function to complete the Buy/Sell Indicator column on the Investment Detail worksheet. This column will show either the word Buy or the words Hold or Sell based on the results of the logical test argument of an IF function. We will use the AND function to define the logical test argument of the IF function. The following steps explain how to accomplish this:

1. Click cell P4 on the Investment Detail worksheet.
2. Type an equal sign (=).
3. Type the function name IF followed by an open parenthesis (().
4. Type the function name AND followed by an open parenthesis ((). The AND function is being placed into the logical_test argument of this IF function.
5. Click cell N4 and then type the less than symbol (<</b>).
6. Type a minus sign (???) followed by the number 1 and a percent symbol: (???1%).
7. Type a comma. This advances the AND function to the second logical test.
8. Click cell S4.
9. Type a greater than symbol (>) followed by an equal sign (=). These symbols are used to evaluate if the value in a cell location is greater than or equal to a target value.
10. Type a zero followed by a closing parenthesis ()).

11. Type an equal sign (=) followed by the word TRUE. Do not enclose the word in quotation marks.

    Figure 3.18 “AND Function Placed in the Logical Test of an IF Function” shows the appearance of the AND function that has been added to the logical test of the IF function. The AND function will produce a true output if the value in cell N4 is <???1% and the value in cell S4 is greater than or equal to 0.

    Figure 3.18 AND Function Placed in the Logical Test of an IF Function

    

12. Type a comma. This advances the IF function to the value_if_true argument.
13. Type the word Buy enclosed in quotation marks as shown in Figure 3.19 “Results of the AND Function in the Logical Test Argument of an IF Function”. If the Current vs. Target value is less than ???1% and the Unrealized Gain/Loss is greater than or equal to zero, the function will show the word Buy. In other words, if the investment is less than the desired percentage for the total portfolio and it is currently not losing money, we will buy more of that investment so it is in line with the target percentage of the portfolio.
14. Type a comma.
15. Type the words Hold or Sell enclosed in quotation marks as shown in Figure 3.19 “Results of the AND Function in the Logical Test Argument of an IF Function”. For all other investments that are not designated with a Buy indicator, the function will show the words Hold or Sell. This indicates that an investment could either be held or sold.
16. Type a closing parenthesis ()) and press the ENTER key on your keyboard.
17. Copy the IF function in cell P4 and paste it into the range P5:P18 using the Paste Formulas command.
18. Increase the width of Column P to 12 points.

Figure 3.19 “Results of the AND Function in the Logical Test Argument of an IF Function” shows the results of the completed AND function within an IF function after it is copied and pasted into the range P5:P18.

Nested IF Functions

When constructing the IF function, the logical test can produce only two potential outcomes when evaluating the data in a cell. In addition, the function can produce only two possible outputs, which are defined in the value_if_true and value_if_false arguments. However, there may be situations when you need to test for several possible outcomes, which may require more than two possible outputs. To accomplish this, you need to create a nested IF functionUsed when more than two tests and two outputs are required when using the IF function. A nested IF function is when the value_if_true or value_if_false arguments of an IF function are defined with another IF function.. A nested IF function is when either the value_if_true or value_if_false arguments are defined with another IF function.

For the Personal Investment workbook, a nested IF function is required to complete the Rebalance Indicator column (Column O) on the Investment Detail worksheet (see Figure 3.19 “Results of the AND Function in the Logical Test Argument of an IF Function”). The purpose of this column is to indicate where the portfolio needs to be rebalanced. Looking at the Current vs. Target column (Column N) shown in Figure 3.19 “Results of the AND Function in the Logical Test Argument of an IF Function”, you can see that several investments have a significant negative number where the investment value has fallen below the target percentage for the portfolio. Other investments have a significant positive number where the investment has exceeded the target percentage for the portfolio. For this portfolio, a number greater than 1% or less than ???1% will be considered significant. Therefore, we will need to assess three possible outcomes when creating a logical test that evaluates the values in Column N. The first test will be if the value is greater than 1%. The second test will be if the value is less than ???1%. The third test will be if both the first test and the second test are false. This is why we need to construct a nested IF function to produce the outputs in the Rebalance Indicator column. The following steps explain how to accomplish this:

1. Click cell O4 on the Investment Detail worksheet.
2. Type an equal sign (=).
3. Type the function name IF followed by an open parenthesis (().
4. Click cell N4.
5. Type the greater than symbol (>) followed by the number 1%. It is important to use the percent symbol (%) after the number 1. If you omit the percent symbol, Excel will test if the value in cell N4 is greater than 100%.
6. Type a comma.
7. Type the word Rebalance inside quotation marks. When using text data to define any of the arguments for the IF function, the text must be placed inside quotation marks.
8. Type a comma.
9. Start another IF function by typing the function name IF followed by an open parenthesis (().
10. Click cell N4.
11. Type the less than symbol (<) followed by ???1%.
12. Type a comma.
13. Type the word Rebalance inside quotation marks.
14. Type a comma.
15. Type the word OK inside quotation marks.
16. Type two closing parentheses ())). Since two IF functions were started, there are two open parentheses in the function. As a result, we need to add two closing parentheses; otherwise, Excel will produce an error message stating that a closing parenthesis is missing.
17. Press the ENTER key on your keyboard.
18. Copy the nested IF function in cell O4 and paste it into the range O5:O18 using the Paste Formulas command.

Figure 3.20 “Completed Nested IF Function” shows how the completed nested IF function should appear in cell O4 of the Investment Detail worksheet. In addition, we see the results of the function after it was pasted into the range O5:O18. Notice that for any investment where the Current vs. Target value is between plus or minus 1%, the word OK appears.

Basic Conditional Formats

A feature related to the skills used to create logical functions is conditional formatting. Conditional formatsAn Excel feature that applies formatting commands to cell locations based on the cell contents. A basic conditional formatting rule will utilize a logical test to evaluate the contents of a cell location. If the results of the logical test are true, Excel will apply the designated formatting commands to the cell location. allow you to apply a variety of formatting treatments based on the contents of a cell location. A logical test similar to the ones used in the IF, AND, and OR functions is used to evaluate the contents of a cell and apply a designated formatting treatment. For example, looking at Figure 3.20 “Completed Nested IF Function”, you will notice that the Unrealized Gain/Loss column is formatted using the accounting number format. Negative numbers are enclosed in parentheses. However, to make these numbers stand out, we can use conditional formatting to change the font color to red. We will do this for the Unrealized Gain/Loss and Percent Gain/Loss columns. The following steps explain how conditional formats are applied to the cell locations in these columns:

1. Highlight the range S4:T18 on the Investment Detail worksheet.
2. Click the Conditional Formatting button in the Styles group of commands on the Home tab of the Ribbon.

3. Click the New Rule command from the list of options (see Figure 3.21 “Conditional Formatting Options List”). This will open the New Formatting Rule dialog box.

   Figure 3.21 Conditional Formatting Options List

   

4. At the top of the New Formatting Rule dialog box, you will find a list of options under the Select a Rule Type heading. Click the second option that states ???Format only cells that contain.???
5. In the lower portions of the New Formatting Rule dialog box, you will see several drop-down boxes under the heading Edit the Rule Description. Make sure the first drop-down box is set to Cell Value.
6. Click the second drop-down box in the Edit the Rule Description section of the New Formatting Rule dialog box and select the ???less than??? option.
7. Click in the input box, which is next to the drop-down box that was set in the previous step, and type a zero. This completes the logical test of the conditional format, which is going to evaluate if the value in any of the cells in the range S4:T18 is less than zero.
8. Click the Format button, which is near the bottom of the New Formatting Rule dialog box. This will open the Format Cells dialog box.
9. Click the drop-down box in the Color section of the Format Cells dialog box and select the red square from the color palette (see Figure 3.22 “Format Cells Dialog Box”).
10. Click the OK button at the bottom of the Format Cells dialog box.
11. Click the OK button at the bottom of the New Formatting Rule dialog box. This completes the Conditional Formatting rule that will be applied to cells in the range S4:T18.

Figure 3.22 “Format Cells Dialog Box” shows the Format Cells dialog box. This opens when the Format button is clicked on the New Formatting Rule dialog box. Notice the tabs running across the top of the dialog box. All formatting features in Excel are grouped by category, which can be accessed by clicking the related tab on the Format Cells dialog box. You will see some of the formatting commands in light grey. This indicates that these commands cannot be used with the Conditional Formatting feature. You can use the Format Cells dialog box to apply any formatting features by clicking the Format Cells dialog button on the Home tab of the Ribbon (see Figure 3.21 “Conditional Formatting Options List”).

Figure 3.23 “New Formatting Rule Dialog Box” shows the final settings for the New Formatting Rule dialog box. It is important to note that the ???Format only cells that contain??? option was selected in the New Formatting Rule dialog box to set a basic logical test that can be used to apply formatting commands automatically based on the values in cell locations.

Figure 3.24 “Conditional Format Applied to the Range S4:T18” shows the results of the conditional formatting rule that was applied to the range S4:T18. Notice the font color is automatically changed to red for negative numbers.

The COUNTIF Function

The COUNTIF function differs from the regular COUNT function in two ways. First, the regular COUNT function counts only the number of cells in a range that contain numeric data. The COUNTIF function counts the number of cells in a range that contain numeric or text data. Second, the COUNTIF function allows you to selectively count the cells in a range based on specific criteria.

The COUNTIF function contains two arguments: range and criteria. The range argument is defined with the range of cells that will be counted. The criteria argument is defined with the criteria that will be used to decide if a cell in the range should be included in the output of the function. The following steps explain how we can use the COUNTIF function to calculate the number of investments by investment type on the Portfolio Summary worksheet:

1. Click cell B4 on the Portfolio Summary worksheet.
2. Click the Formulas tab of 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.
5. Click the scroll down arrow on the second drop-down list to find the COUNTIF function (see Figure 3.25 “Selecting the COUNTIF Function from the Function Library”).

6. Click the COUNTIF function. This will open the Function Arguments dialog box.

   Figure 3.25 Selecting the COUNTIF Function from the Function Library

   

7. Click the Collapse Dialog button next to the Range argument on the Function Arguments dialog box (see Figure 3.26 “Completed Function Arguments Dialog Box for the COUNTIF Function”).
8. Click the Investment Detail worksheet tab.
9. Highlight the range A4:A18 on the Investment Detail worksheet and press the ENTER key on your keyboard.
10. Click in the Range argument input box and place an absolute reference on the range A4:A18. This is done by typing a dollar sign ($) in front of the column letter and row number for both cells in the range. Alternatively, place the insertion point after the 4 in cell reference A4 and press the F4 key on your keyboard. Then place the insertion point after the 8 in cell reference A18 and press the F4 key. This will add the $ to the appropriate positions automatically.
11. Press the TAB key on your keyboard to advance to the next argument, which is the Criteria argument. Then type the cell location A4. The criteria for the function will be the investment type entered into cell A4 on the Portfolio Summary worksheet.

12. Click the OK button at the bottom of the Function Arguments dialog box. Figure 3.26 “Completed Function Arguments Dialog Box for the COUNTIF Function” shows the completed Function Arguments dialog box for the COUNTIF function. Notice the absolute references that were placed on each cell location in the range that was used to define the Range argument. The Criteria argument is defined with the cell A4, which means the function will only count cell locations in the range A4:A18 where the contents in the cell match the contents in cell A4.

    Figure 3.26 Completed Function Arguments Dialog Box for the COUNTIF Function

    

13. Copy the function in cell B4 and paste it into the range B5:B7 using the Paste Formulas command.
14. Enter a SUM function in cell B8 that sums the values in the range B4:B7.

Figure 3.27 “COUNTIF Function Output in the Portfolio Summary Worksheet” shows the results of the COUNTIF function after it is pasted into the range B5:B7. Because of relative referencing, the cell location used in the criteria argument is changed after the function is pasted into the range B5:B7. For example, in cell B6, the function is counting the cell locations in the range A4:A18 where the contents match the contents of cell A6. This allows you to use the function to count the number of investments per investment type. As shown in the figure, the range B4:B7 now shows the number of investments in this portfolio by investment type.

The AVERAGEIF Function

The AVERAGEIF function performs the identical mathematical calculation as the regular AVERAGE function. However, similar to the COUNTIF function, it allows you to define criteria that will select cells in a range that will be used in the function output. The AVERAGEIF function differs from the COUNTIF function in that it allows you to define two cell ranges instead of one. The first range pertains to the criteria that will be used to select cells for the function output. The second range contains the values that will be used to calculate the arithmetic mean. Table 3.8 “Arguments for the AVERAGEIF and SUMIF Functions” provides definitions for the arguments contained in the AVERAGEIF and SUMIF functions.

The AVERAGEIF function will be used in the Portfolio Summary worksheet to calculate the average length of time that investments for each investment type are held. The following steps explain how to add this function to the worksheet:

1. Click cell C4 on the Portfolio Summary 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 (see Figure 3.25 “Selecting the COUNTIF Function from the Function Library”).
5. Click the AVERAGEIF function, which will be near the top of the list of functions. This opens the Function Arguments dialog box.
6. Click the Collapse Dialog button next to the Range argument on the Function Arguments dialog box (see Figure 3.28 “Defined Arguments for the AVERAGEIF Function”).
7. Click the Investment Detail worksheet tab.
8. Highlight the range A4:A18 on the Investment Detail worksheet and press the ENTER key on your keyboard.
9. Click in the Range argument input box and place an absolute reference on the range A4:A18. This is done by typing a dollar sign ($) in front of the column letter and row number for both cells in the range.
10. Press the TAB key on your keyboard to advance to the Criteria argument and type the cell location A4. The criteria for the function will be the investment type entered into cell A4 on the Portfolio Summary worksheet.
11. Click the Collapse Dialog button next to the Average_range argument on the Function Arguments dialog box (see Figure 3.28 “Defined Arguments for the AVERAGEIF Function”).
12. Click the Investment Detail worksheet tab.
13. Highlight the range Q4:Q18 on the Investment Detail worksheet and press the ENTER key on your keyboard.
14. Click in the Average_range argument input box and place an absolute reference on the range Q4:Q18. This is done by typing a dollar sign ($) in front of the column letter and row number for both cells in the range.

15. Click the OK button at the bottom of the Function Arguments dialog box.

    Figure 3.28 “Defined Arguments for the AVERAGEIF Function” shows the Function Arguments dialog box for the AVERAGEIF function that will be input into cell C4. Notice that absolute references are placed on the cell locations used to define the Range and Average_range arguments. The function will evaluate the cells in the range A4:A18 using the value that exists in cell A4 on the Portfolio Summary worksheet. When a cell in the range A4:A18 meets the criteria, the function will pull the cell location in the same row from the range Q4:Q18 and include it in the average calculation.

    Figure 3.28 Defined Arguments for the AVERAGEIF Function

    

16. Copy the function in cell C4 and paste it into the range C5:C7 using the Paste Formulas option.
17. Type an equal sign (=) in cell C8.
18. Click the Investment Detail worksheet tab. Then click cell Q19 and press the ENTER key on your keyboard.

Figure 3.29 “AVERAGEIF Function Output on the Portfolio Summary Worksheet” shows the output of the AVERAGEIF function in the Average Months Owned column on the Portfolio Summary worksheet. The function calculates the average months owned in Column Q on the Investment Detail worksheet where the investment type is equal to the description entered in the range A4:A7 on the Portfolio Summary worksheet.

The SUMIF Function

The SUMIF function performs the same mathematical calculation as the regular SUM function. However, similar to the AVERAGEIF function, this function allows you to select specific cells from a range that will be used in the output. The arguments for the SUMIF function are identical to the AVERAGEIF function (see Table 3.8 “Arguments for the AVERAGEIF and SUMIF Functions”). We will use the SUMIF function in two columns on the Portfolio Summary worksheet. The first column will show the total investment cost for each investment type. The second column will show the total current value for each investment type. This will allow us to calculate the total annual growth rate for each investment type. The following steps explain how we will use this function to complete the first column:

1. Click cell D4 on the Portfolio Summary worksheet.
2. Click the Formulas tab on the Ribbon.
3. Click the Math & Trig button in the Function Library group of commands (see Figure 3.25 “Selecting the COUNTIF Function from the Function Library”).
4. Select the SUMIF function from the drop-down list. Use the scroll bar to scroll down to find the SUMIF function.
5. Click the Collapse Dialog button next to the Range argument on the Function Arguments dialog box (see Figure 3.30 “Defined Arguments for the First SUMIF Function on the Portfolio Summary Worksheet”).
6. Click the Investment Detail worksheet tab.
7. Highlight the range A4:A18 on the Investment Detail worksheet and press the ENTER key on your keyboard.
8. Click in the Range argument input box and place an absolute reference on the range A4:A18. This is done by typing a dollar sign ($) in front of the column letter and row number for both cells in the range.
9. Press the TAB key on your keyboard to advance to the Criteria argument and type the cell location A4. The criteria for the function will be the investment type entered into cell A4 on the Portfolio Summary worksheet.

10. Click the Collapse Dialog button next to the Sum_range argument on the Function Arguments dialog box (see Figure 3.30 “Defined Arguments for the First SUMIF Function on the Portfolio Summary Worksheet”).

    Figure 3.30 Defined Arguments for the First SUMIF Function on the Portfolio Summary Worksheet

    

11. Click the Investment Detail worksheet tab.
12. Highlight the range G4:G18 on the Investment Detail worksheet and press the ENTER key on your keyboard.
13. Click in the Sum_range argument input box and place an absolute reference on the range G4:G18. This is done by typing a dollar sign ($) in front of the column letter and row number for both cells in the range.
14. Click the OK button at the bottom of the Function Arguments dialog box.
15. Copy the function in cell D4 and paste it into the range D5:D7 using the Paste Formulas option.
16. Enter a regular SUM function into cell D8 on the Portfolio Summary worksheet to calculate the sum of the values in the range D4:D7.

Figure 3.30 “Defined Arguments for the First SUMIF Function on the Portfolio Summary Worksheet” shows how the SUMIF arguments were defined for the Total Purchase Cost column on the Portfolio Summary worksheet. Notice that the row numbers are identical in the range used to define the Range argument and the Sum_range argument.

The following steps explain how to add the SUMIF function to the second column on the Portfolio Summary worksheet:

1. Click cell E4 on the Portfolio Summary worksheet.
2. Click the Formulas tab on the Ribbon.
3. Click the Math & Trig button in the Function Library group of commands (see Figure 3.25 “Selecting the COUNTIF Function from the Function Library”).
4. Select the SUMIF function from the drop-down list. Use the scroll bar to scroll down to find the SUMIF function.
5. Click the Collapse Dialog button next to the Range argument on the Function Arguments dialog box (see Figure 3.30 “Defined Arguments for the First SUMIF Function on the Portfolio Summary Worksheet”).
6. Click the Investment Detail worksheet tab.
7. Highlight the range A4:A18 on the Investment Detail worksheet and press the ENTER key on your keyboard.
8. Click in the Range argument input box and place an absolute reference on the range A4:A18. This is done by typing a dollar sign ($) in front of the column letter and row number for both cells in the range.
9. Press the TAB key on your keyboard to advance to the Criteria argument and type the cell location A4.
10. Click the Collapse Dialog button next to the Sum_range argument on the Function Arguments dialog box (see Figure 3.30 “Defined Arguments for the First SUMIF Function on the Portfolio Summary Worksheet”).
11. Click the Investment Detail worksheet tab.
12. Highlight the range K4:K18 on the Investment Detail worksheet and press the ENTER key on your keyboard.
13. Click in the Sum_range argument input box and place an absolute reference on the range K4:K18. This is done by typing a dollar sign ($) in front of the column letter and row number for both cells in the range.
14. Click the OK button at the bottom of the Function Arguments dialog box.
15. Copy the function in cell E4 and paste it into the range E5:E7 using the Paste Formulas option.
16. Enter a regular SUM function into cell E8 on the Portfolio Summary worksheet to calculate the sum of the values in the range E4:E7.

Figure 3.31 “SUMIF Function Outputs in the Portfolio Summary Worksheet” shows the results of the SUMIF function in the Total Purchase Cost and Current Value columns in the Portfolio Summary worksheet.

A formula can now be added to show the annual growth for each investment category. The following steps explain how to add this formula to the Portfolio Summary worksheet:

1. Click cell F4 on the Portfolio Summary worksheet.
2. Type an equal sign (=) followed by two open parenthesis ((().
3. Click cell E4 and type a minus sign (???).
4. Click cell D4 and type a closing parenthesis ()).
5. Type a slash (/) for division and click cell D4.
6. Type a closing parenthesis ()). This completes the first part of the formula, which is calculating the growth rate between the Total Purchase Cost (cell D4) and the Current Value (cell E4).
7. Type a slash (/) for division followed by an open parenthesis (().
8. Click cell C4, which is the Average Months Owned.
9. Type a slash (/) for division and the number 12. This part of the formula converts the number of months owned to years by dividing it by 12. This result is being divided into the growth rate, which will then show the average growth per year.
10. Type a closing parenthesis ()) and press the ENTER key on your keyboard.
11. Copy the formula in cell F4 and paste it into the range F5:F8 using the Paste Formulas command.

Figure 3.32 “Completed Annual Growth Column in the Portfolio Summary Worksheet” shows the results of the statistical IF functions that were added to the Total Summary section of the Portfolio Summary worksheet. The statistical IF functions used on this worksheet allowed us to group the details in the Investment Detail worksheet by investment type. Once this was accomplished, we added a formula to show the annual growth rate by investment type.

The COUNTIFS Function

Up to this point, the statistical IF functions that were demonstrated provided the ability to define one criteria or logical test used to select cells from a targeted range. The next set of statistical functions that will be demonstrated provides the ability to define multiple sets of criteria for selecting cells from a targeted range. We will begin with the COUNTIFS function.

It is easy to distinguish the difference between a statistical IF function that allows one criteria argument to be defined and one that allows multiple criteria arguments. If the IF at the end of the function name is plural, you can define multiple sets of criteria arguments. Therefore, the COUNTIFS function provides the option of defining multiple sets of criteria for selecting cells from a targeted range that will be used in the function output. The arguments for the COUNTIFS function are established in pairs. For example, the first arguments for the function are Criteria_range1 and Criteria1. The function will use the Criteria1 argument to select cells in the Criteria_range1 argument. A second pair of arguments, Criteria_range2 and Criteria2, can be defined to select a subset of cell locations that were selected in the Criteria_range1 and Criteria1 arguments. This process can be repeated for several pairs of criteria arguments. We will continue to work on the Portfolio Summary worksheet by adding the COUNTIFS function to count the number of poor performing investments by investment type. The following steps explain how to add this function to the worksheet:

1. Click cell B11 on the Portfolio Summary worksheet.
2. Click the Formulas tab of 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.
5. Click the scroll down arrow on the second drop-down list to find the COUNTIFS function (see Figure 3.25 “Selecting the COUNTIF Function from the Function Library”).
6. Click the COUNTIFS function. This will open the Function Arguments dialog box.
7. Click the Collapse Dialog button next to the Criteria_range1 argument on the Function Arguments dialog box (see Figure 3.33 “Defined Arguments for the COUNTIFS Function”).
8. Click the Investment Detail worksheet tab.
9. Highlight the range A4:A18 on the Investment Detail worksheet and press the ENTER key on your keyboard.
10. Click in the Criteria_range1 argument input box and place an absolute reference on the range A4:A18. This is done by typing a dollar sign ($) in front of the column letter and row number for both cells in the range.
11. Press the TAB key on your keyboard to advance to the Criteria1 argument and type the cell location A11. The criteria for the function will be the investment type that is entered into cell A11 on the Portfolio Summary worksheet. You will notice that when you define this argument, the Criteria_range2 argument will appear on the Function Arguments dialog box.
12. Click the Collapse Dialog button next to the Criteria_range2 argument on the Function Arguments dialog box (see Figure 3.33 “Defined Arguments for the COUNTIFS Function”).
13. Click the Investment Detail worksheet tab.
14. Highlight the range W4:W18 on the Investment Detail worksheet and press the ENTER key on your keyboard.
15. Click in the Criteria_range2 argument input box and place an absolute reference on the range W4:W18. This is done by typing a dollar sign ($) in front of the column letter and row number for both cells in the range.
16. Press the TAB key on your keyboard to advance to the Criteria2 argument.
17. Type an open quotation mark followed by the logical test <???1% and then type a closing quotation mark (???<???1%???). The second criterion for this function is a logical test that will identify cell locations where the value is less than ???1%. For the purposes of this exercise, a poor performing investment is one that is below the target growth rate by more than 1%.
18. Click the OK button at the bottom of the Function Arguments dialog box.
19. Copy the function in cell B11 and paste it into the range B12:B14 using the Paste Formulas command.
20. Enter a SUM function in cell B15 on the Portfolio Summary worksheet that sums the values in the range B11:B14.

Figure 3.33 “Defined Arguments for the COUNTIFS Function” shows the completed arguments for the COUNTIFS function in the Function Arguments dialog box. Notice the scroll bar that appears on the right side of the Function Arguments dialog box. This allows you to scroll through and define multiple pairs of criteria arguments for the function.

Figure 3.34 “Outputs for the COUNTIFS Functions” shows the output of the COUNTIFS function on the Portfolio Summary worksheet. The criteria established for the COUNTIFS function shows that there are a total of seven investments that are underperforming in the portfolio.

The AVERAGEIFS Function

The AVERAGEIFS function is similar to the COUNTIFS function in that multiple sets of criteria can be defined instead of one. However, the arguments for the AVERAGEIFS function are slightly different from those for the COUNTIFS function. Table 3.9 “Arguments for the AVERAGEIFS and SUMIFS Functions” provides definitions for the arguments of the AVERAGEIFS and the SUMIFS functions.