Microsoft® Office contains a variety of tools that help people accomplish many personal and professional objectives. Microsoft Excel is perhaps the most versatile and widely used of all the Office applications. No matter which career path you choose, you will likely need to use Excel to accomplish your professional objectives, some of which may occur daily. This chapter provides an overview of the Excel application along with an orientation for accessing the commands and features of an Excel workbook.

MAKING DECISIONS WITH EXCEL

Taking a very simple view, Excel is a tool that allows you to enter quantitative data into an electronic spreadsheet to apply one or many mathematical computations. These computations ultimately convert that quantitative data into information. The information produced in Excel can be used to make decisions in both professional and personal contexts. For example, employees can use Excel to determine how much inventory to buy for a clothing retailer, how much medication to administer to a patient, or how much money to spend to stay within a budget. With respect to personal decisions, you can use Excel to determine how much money you can spend on a house, how much you can spend on car lease payments, or how much you need to save to reach your retirement goals. We will demonstrate how you can use Excel to make these decisions and many more throughout this text.

Figure 1.1 shows a completed Excel worksheet that will be constructed in this chapter. The information shown in this worksheet is top-line sales data for a hypothetical merchandise retail company. The worksheet data can help this retailer determine the number of salespeople needed for each month, how much inventory is needed to satisfy sales, and what types of products should be purchased.

Figure 1.1 Example of an Excel Worksheet

STARTING EXCEL

1. Locate Excel on your computer.
2. Click Microsoft Excel to launch the Excel application and present you with workbook options.
3. Click the first option; “Blank Workbook”.

THE EXCEL WORKBOOK

Once Excel is started, a blank workbook will open on your screen. A workbook is an Excel file that contains one or more worksheets (sometimes referred to as spreadsheets). Excel will assign a file name to the workbook, such as Book1, Book2, Book3, and so on, depending on how many new workbooks are opened. Figure 1.2 shows a blank workbook after starting Excel. Take some time to familiarize yourself with this screen. Your screen may be slightly different based on the version you’re using.

Figure 1.2 Blank Workbook

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

Figure 1.3 Restored Worksheet

NAVIGATING WORKSHEETS

Data are entered and managed in an Excel worksheet. The worksheet contains several rectangles called cells for entering numeric and nonnumeric data. Each cell in an Excel worksheet contains an address, which is defined by a column letter followed by a row number. For example, the cell that is currently activated in Figure 1.3 is A1. This would be referred to as cell location A1 or cell reference A1. The following steps explain how you can navigate in an Excel worksheet:

1. Place your mouse pointer over cell D5 and left click.
2. Check to make sure column letter D and row number 5 are highlighted, as shown in Figure 1.4.

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

Figure 1.4 Activating a Cell Location

1. Move the mouse pointer to cell A1.
2. Click and hold the left mouse button and drag the mouse pointer back to cell D5.
3. Release the left mouse button. You should see several cells highlighted, as shown in Figure 1.5.

This is referred to as a cell range and is documented as follows: A1:D5. Any two cell locations separated by a colon are known as a cell range. The first cell is the top left corner of the range, and the second cell is the lower right corner of the range.

Figure 1.5 Highlighting a Range of Cells

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

THE EXCEL RIBBON

Excel’s features and commands are found in the Ribbon, which is the upper area of the Excel screen that contains several tabs running across the top. Each tab provides access to a different set of Excel commands. Figure 1.6 shows the commands available in the Home tab of the Ribbon. Table 1.1 “Command Overview for Each Tab of the Ribbon” provides an overview of the commands that are found in each tab of the Ribbon.

Figure 1.6 Home Tab of Ribbon

Table 1.1 Command Overview for Each Tab of the Ribbon

The Ribbon shown in Figure 1.6 is full, or maximized. The benefit of having a full Ribbon is that the commands are always visible while you are developing a worksheet. However, depending on the screen dimensions of your computer, you may find that the Ribbon takes up too much vertical space on your worksheet. If this is the case, you can minimize the Ribbon by clicking the button shown in Figure 1.6. When minimized, the Ribbon will show only the tabs and not the command buttons. When you click on a tab, the command buttons will appear until you select a command or click anywhere on your worksheet.

QUICK ACCESS TOOLBAR AND RIGHT-CLICK MENU

The Quick Access Toolbar is found at the upper left side of the Excel screen above the Ribbon, as shown in Figure 1.7. This area provides access to the most frequently used commands, such as Save and Undo. You also can customize the Quick Access Toolbar by adding commands that you use on a regular basis. By placing these commands in the Quick Access Toolbar, you do not have to navigate through the Ribbon to find them. To customize the Quick Access Toolbar, click the down arrow as shown in Figure 1.7. This will open a menu of commands that you can add to the Quick Access Toolbar. If you do not see the command you are looking for on the list, select the More Commands option.

Figure 1.7 Customizing the Quick Access Toolbar

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

Figure 1.8 Right-Click Menu

THE FILE TAB

The File tab is also known as the Backstage view of the workbook. It contains a variety of features and commands related to the workbook that is currently open, new workbooks, or workbooks stored in other locations on your computer or network. Figure 1.9 shows the options available in the File tab or Backstage view. To leave the Backstage view and return to the worksheet, click the arrow in the upper left-hand corner as shown below.

Figure 1.9 File Tab or Backstage View of a Workbook

Included in the File tab are the default settings for the Excel application that can be accessed and modified by clicking the Options button. Figure 1.10 shows the Excel Options window, which gives you access to settings such as the default font style, font size, and the number of worksheets that appear in new workbooks.

Figure 1.10 Excel Options Window

SAVING WORKBOOKS (SAVE AS)

Once you create a new workbook, you will need to change the file name and choose a location on your computer or network to save that file. It is important to remember where you save this workbook on your computer or network as you will be using this file in the Section 1.2 “Entering, Editing, and Managing Data” to construct the workbook shown in Figure 1.1. The process of saving can be different with different versions of Excel.  Please be sure you follow the steps for the version of Excel you are using. The following steps explain how to save a new workbook and assign it a file name.

SAVING WORKBOOKS IN EXCEL 2013

1. If you have not done so already, open a blank workbook in Excel.
2. When saving your workbook for the first time, click the File tab.
3. Click the Save As button in the upper left side of the Backstage view window. This will open the Save As dialog box, as shown in Figure 1.11.
4. Click in the File Name box at the bottom of the Save As dialog box and use the BACKSPACE key to remove the current default name of the workbook.
5. Type the file name:  CH1 GMW Sales Data.
6. Click the Desktop button on the left side of the Save As dialog box if you wish to save this file on your desktop. If you want to save this workbook in a different location, such as a USB drive, select your preferred location.
7. Click the Save button on the lower right side of the Save As dialog box.
8. As you continue to work on your workbook, you will want to Save frequently by click either the Save button on the Home ribbon; or by selecting the Save option from the File menu.

Figure 1.11 Save As Dialog Box in Excel 2013

SAVING WORKBOOKS IN EXCEL 2016

1. If you have not done so already, open a blank workbook in Excel.
2. Click the File tab and then the Save As button in the left side of the Backstage view window. This will open the Save As dialog box.
3. Determine a location for saving on your computer by clicking Browse on the left side to open the Save As dialog box.
4. Click in the File Name box near the bottom of the Save As dialog box. Type the new file name: CH1 GMW Sales Data
5. Review the settings in the screen for correctness and click the Save button.

Figure 1.12 Save As Dialog in 2016

THE STATUS BAR

The Status Bar is located below the worksheet tabs on the Excel screen (see Figure 1.13). It displays a variety of information, such as the status of certain keys on your keyboard (e.g., CAPS LOCK), the available views for a workbook, the magnification of the screen, and mathematical functions that can be performed when data are highlighted on a worksheet. You can customize the Status Bar as follows:

1. Place the mouse pointer over any area of the Status Bar and right click to display the “Customize Status Bar” list of options (see Figure 1.13).
2. Select the Caps Lock option from the menu (see Figure 1.13).
3. Press the CAPS LOCK key on your keyboard. You will see the Caps Lock indicator on the lower right side of the Status Bar.
4. Press the CAPS LOCK on your keyboard again. The indicator on the Status Bar goes away.

Figure 1.13 Customizing the Status Bar

EXCEL HELP

The Help feature provides extensive information about the Excel application. Although some of this information may be stored on your computer, the Help window will automatically connect to the Internet, if you have a live connection, to provide you with resources that can answer most of your questions. You can open the Excel Help window by clicking the question mark in the upper right area of the screen or ribbon. With newer versions of Excel, use the query box to enter your question and select from helpful option links or select the question mark from the dropdown list to launch Excel Help windows.

Figure 1.14 Excel Help Window

In this section, we will begin the development of the workbook shown in Figure 1.1. The skills covered in this section are typically used in the early stages of developing one or more worksheets in a workbook.

ENTERING DATA

You will begin building the workbook shown in Figure 1.1 by manually entering data into the worksheet. The following steps explain how the column headings in Row 2 are typed into the worksheet:

1. Click cell location A2 on the worksheet.
2. Type the word Month.
3. Press the RIGHT ARROW key. This will enter the word into cell A2 and activate the next cell to the right.
4. Type Unit Sales and press the RIGHT ARROW key.
5. Repeat step 4 for the words Average Price and then again for Sales Dollars.

Figure 1.15 shows how your worksheet should appear after you have typed the column headings into Row 2. Notice that the word Price in cell location C2 is not visible. This is because the column is too narrow to fit the entry you typed. We will examine formatting techniques to correct this problem in the next section.

Figure 1.15 Entering Column Headings into a Worksheet

1. Click cell location B3.
2. Type the number 2670 and press the ENTER key. After you press the ENTER key, cell B4 will be activated. Using the ENTER key is an efficient way to enter data vertically down a column.
3. Enter the following numbers in cells B4 through B14: 2160, 515, 590, 1030, 2875, 2700, 900, 775, 1180, 1800, and 3560.
4. Click cell location C3.
5. Type the number 9.99 and press the ENTER key.
6. Enter the following numbers in cells C4 through C14: 12.49, 14.99, 17.49, 14.99, 12.49, 9.99, 19.99, 19.99, 19.99, 17.49, and 14.99.
7. Activate cell location D3.
8. Type the number 26685 and press the ENTER key.
9. Enter the following numbers in cells D4 through D14: 26937, 7701, 10269, 15405, 35916, 26937, 17958, 15708, 23562, 31416, and 53370.
10. When finished, check that the data you entered matches Figure 1.16.

Integrity Check

Figure 1.16 shows how your worksheet should appear after entering the data. Check your numbers carefully to make sure they are accurately entered into the worksheet.

Figure 1.16  Completed Data Entry for Columns B, C, and D

EDITING DATA

Data that has been entered in a cell can be changed by double clicking the cell location or using the Formula Bar. You may have noticed that as you were typing data into a cell location, the data you typed appeared in the Formula Bar. The Formula Bar can be used for entering data into cells as well as for editing data that already exists in a cell. The following steps provide an example of entering and then editing data that has been entered into a cell location:

1. Click cell A15 in the Sheet1 worksheet.
2. Type the abbreviation Tot and press the ENTER key.
3. Click cell A15.
4. Move the mouse pointer up to the Formula Bar. You will see the pointer turn into a cursor. Move the cursor to the end of the abbreviation Tot and left click.
5. Type the letters al to complete the word Total.
6. Click the checkmark to the left of the Formula Bar (see Figure 1.17). This will enter the change into the cell.
   

   Figure 1.17 Using the Formula Bar to Edit and Enter Data

    

7. Double click cell A15.
8. Add a space after the word Total and type the word Sales.
9. Press the ENTER key.

AUTO FILL

The Auto Fill feature is a valuable tool when manually entering data into a worksheet. This feature has many uses, but it is most beneficial when you are entering data in a defined sequence, such as the numbers 2, 4, 6, 8, and so on, or nonnumeric data such as the days of the week or months of the year. The following steps demonstrate how Auto Fill can be used to enter the months of the year in Column A:

1. Click cell A3 in the Sheet1 worksheet.
2. Type the word January and press the ENTER key.
3. Activate cell A3 again.
4. Move the mouse pointer to the lower right corner of cell A3. You will see a small square in this corner of the cell; this is called the Fill Handle (See Figure 1.18) When the mouse pointer gets close to the Fill Handle, the white block plus sign will turn into a black plus sign.

Figure 1.18 Fill Handle

Left click and drag the Fill Handle to cell A14. Notice that the Auto Fill tip box indicates what month will be placed into each cell (see Figure 1.19). Release the left mouse button when the tip box reads “December.”

Figure 1.19  Using Auto Fill to Enter the Months of the Year

Once you release the left mouse button, all twelve months of the year should appear in the cell range A3:A14, as shown in Figure 1.20. You will also see the Auto Fill Options button. By clicking this button, you have several options for inserting data into a group of cells.

Figure 1.20 Auto Fill Options Button

1. Click the Auto Fill Options button.
2. Click the Copy Cells option. This will change the months in the range A4:A14 to January.
3. Click the Auto Fill Options button again.
4. Click the Fill Months option to return the months of the year to the cell range A4:A14. The Fill Series option will provide the same result.

DELETING DATA AND THE UNDO COMMAND

There are several methods for removing data from a worksheet, a few of which are demonstrated here. With each method, you use the Undo command. This is a helpful command in the event you mistakenly remove data from your worksheet. The following steps demonstrate how you can delete data from a cell or range of cells:

1. Click cell C2 by placing the mouse pointer over the cell and clicking the left mouse button.
2. Press the DELETE key on your keyboard. This removes the contents of the cell.
3. Highlight the range C3:C14 by placing the mouse pointer over cell C3. Then left click and drag the mouse pointer down to cell C14.
4. Place the mouse pointer over the Fill Handle. You will see the white block plus sign change to a black plus sign.
5. Click and drag the mouse pointer up to cell C3 (see Figure 1.21). Release the mouse button. The contents in the range C3:C14 will be removed.

Figure 1.21  Using Auto Fill to Delete Contents of Cell

1. Click the Undo button in the Quick Access Toolbar (see Figure 1.2). This should replace the data in the range C3:C14.
2. Click the Undo button again. This should replace the data in cell C2.

Figure 1.22 Clear Command Drop-Down Menu

ADJUSTING COLUMNS AND ROWS

There are a few entries in the worksheet that appear cut off. For example, the last letter of the word September cannot be seen in cell A11. This is because the column is too narrow for this word. The columns and rows on an Excel worksheet can be adjusted to accommodate the data that is being entered into a cell. The following steps explain how to adjust the column widths and row heights in a worksheet:

1. Bring the mouse pointer between Column A and Column B in the Sheet1 worksheet, as shown in Figure 1.23. You will see the white block plus sign turn into double arrows.
2. Click and drag the column to the right so the entire word September in cell A11 can be seen. As you drag the column, you will see the column width tip box. This box displays the number of characters that will fit into the column using the Calibri 11-point font which is the default setting for font/size.
3. Release the left mouse button.

Figure 1.23 Adjusting Column Widths

You may find that using the click-and-drag method is inefficient if you need to set a specific character width for one or more columns. Steps 1 through 6 illustrate a second method for adjusting column widths when using a specific number of characters:

1. Click any cell location in Column A by moving the mouse pointer over a cell location and clicking the left mouse button. You can highlight cell locations in multiple columns if you are setting the same character width for more than one column.
2. In the Home tab of the Ribbon, left click the Format button in the Cells group.
3. Click the Column Width option from the drop-down menu. This will open the Column Width dialog box.
4. Type the number 13 and click the OK button on the Column Width dialog box. This will set Column A to this character width (see Figure 1.24).
5. Once again bring the mouse pointer between Column A and Column B so that the double arrow pointer displays and then double-click to activate AutoFit. This features adjusts the column width based on the longest entry in the column.
6. Use the Column Width dialog box (step 6 above) to reset the width to 13.

Figure 1.24 Column Width Dialog Box

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

1. Click cell A15 by placing the mouse pointer over the cell and clicking the left mouse button.
2. In the Home tab of the Ribbon, left click the Format button in the Cells group.
3. Click the Row Height option from the drop-down menu. This will open the Row Height dialog box.
4. Type the number 24 and click the OK button on the Row Height dialog box. This will set Row 15 to a height of 24 points. A point is equivalent to approximately 1/72 of an inch. This adjustment in row height was made to create space between the totals for this worksheet and the rest of the data.

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

Figure 1.25 GMW Sales Data with Column A and Row 15 Adjusted

In addition to adjusting the columns and rows on a worksheet, you can also hide columns and rows. This is a useful technique for enhancing the visual appearance of a worksheet that contains data that is not necessary to display. These features will be demonstrated using the GMW Sales Data workbook. However, there is no need to have hidden columns or rows for this worksheet. The use of these skills here will be for demonstration purposes only.

1. Click cell C1 in the Sheet1 worksheet by placing the mouse pointer over the cell location and clicking the left mouse button.
2. Click the Format button in the Home tab of the Ribbon.
3. Place the mouse pointer over the Hide & Unhide option in the drop-down menu. This will open a submenu of options.
4. Click the Hide Columns option in the submenu of options (see Figure 1.26). This will hide Column C.

Figure 1.26 Hide & Unhide Submenu

Figure 1.27 shows the workbook with Column C hidden in the Sheet1 worksheet. You can tell a column is hidden by the missing letter C.

Figure 1.27 Hidden Column

To unhide a column, follow these steps:

1. Highlight the range B1:D1 by activating cell B1 and clicking and dragging over to cell D1.
2. Click the Format button in the Home tab of the Ribbon.
3. Place the mouse pointer over the Hide & Unhide option in the drop-down menu.
4. Click the Unhide Columns option in the submenu of options. Column C will now be visible on the worksheet.

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

1. Click cell A3 in the Sheet1 worksheet by placing the mouse pointer over the cell location and clicking the left mouse button.
2. Click the Format button in the Home tab of the Ribbon.
3. Place the mouse pointer over the Hide & Unhide option in the drop-down menu. This will open a submenu of options.
4. Click the Hide Rows option in the submenu of options. This will hide Row 3.

To unhide a row, follow these steps:

1. Highlight the range A2:A4 by activating cell A2 and clicking and dragging over to cell A4.
2. Click the Format button in the Home tab of the Ribbon.
3. Place the mouse pointer over the Hide & Unhide option in the drop-down menu.
4. Click the Unhide Rows option in the submenu of options. Row 3 will now be visible on the worksheet.

INSERTING COLUMNS AND ROWS

Using Excel workbooks that have been created by others is a very efficient way to work because it eliminates the need to create data worksheets from scratch. However, you may find that to accomplish your goals, you need to add additional columns or rows of data. In this case, you can insert blank columns or rows into a worksheet. The following steps demonstrate how to do this:

1. Click cell C1 in the Sheet1 worksheet by placing the mouse pointer over the cell location and clicking the left mouse button.
2. Click the down arrow on the Insert button in the Home tab of the Ribbon (see Figure 1.28).
   

   Figure 1.28 Insert Button (Down Arrow)

    

3. Click the Insert Sheet Columns option from the drop-down menu (see Figure 1.29). A blank column will be inserted to the left of Column C. The contents that were previously in Column C now appear in Column D. Note that columns are always inserted to the left of the activated cell.
   

   Figure 1.29 Insert Drop-Down Menu

    

   Keyboard Shortcuts

   Inserting Columns

   • Press the ALT key and then the letters H, I, and C one at a time. A column will be inserted to the left of the activated cell.
4. Click cell A3 in the Sheet1 worksheet by placing the mouse pointer over the cell location and clicking the left mouse button.
5. Click the down arrow on the Insert button in the Home tab of the Ribbon (see Figure 1.28).
6. Click the Insert Sheet Rows option from the drop-down menu (see Figure 1.29). A blank row will be inserted above Row 3. The contents that were previously in Row 3 now appear in Row 4. Note that rows are always inserted above the activated cell.

MOVING DATA

Once data are entered into a worksheet, you have the ability to move it to different locations. The following steps demonstrate how to move data to different locations on a worksheet:

1. Highlight the range D2:D15 by activating cell D2 and clicking and dragging down to cell D15.
2. Bring the mouse pointer to the left edge of cell D2. You will see the white block plus sign change to cross arrows (see Figure 1.30). This indicates that you can left click and drag the data to a new location.
   

   Figure 1.30 Moving Data

    

3. Left Click and drag the mouse pointer to cell C2.
4. Release the left mouse button. The data now appears in Column C.
5. Click the Undo button in the Quick Access Toolbar. This moves the data back to Column D.

DELETING COLUMNS AND ROWS

You may need to delete entire columns or rows of data from a worksheet. This need may arise if you need to remove either blank columns or rows from a worksheet or columns and rows that contain data. The methods for removing cell contents were covered earlier and can be used to delete unwanted data. However, if you do not want a blank row or column in your workbook, you can delete it using the following steps:

1. Click cell A3 by placing the mouse pointer over the cell location and clicking the left mouse button.
2. Click the down arrow on the Delete button in the Cells group in the Home tab of the Ribbon.
3. Click the Delete Sheet Rows option from the drop-down menu (see Figure 1.31). This removes Row 3 and shifts all the data (below Row 2) in the worksheet up one row.

   Keyboard Shortcuts

   Deleting Rows

   • Press the ALT key and then the letters H, D, and R one at a time. The row with the activated cell will be deleted.
   

   Figure 1.31 Delete Drop-Down Menu

    

4. Click cell C1 by placing the mouse pointer over the cell location and clicking the left mouse button.
5. Click the down arrow on the Delete button in the Cells group in the Home tab of the Ribbon.
6. Click the Delete Sheet Columns option from the drop-down menu (see Figure 1.31). This removes Column C and shifts all the data in the worksheet (to the right of Column B) over one column to the left.
7. Save the changes to your workbook by clicking either the Save button on the Home ribbon; or by selecting the Save option from the File menu.

This section addresses formatting commands that can be used to enhance the visual appearance of a worksheet. It also provides an introduction to mathematical calculations. The skills introduced in this section will give you powerful tools for analyzing the data that we have been working with in this workbook and will highlight how Excel is used to make key decisions in virtually any career. Additionally, Excel Spreadsheet Guidelines for format and appearance will be introduced as a format for the course and spreadsheets submitted.

FORMATTING DATA AND CELLS

Enhancing the visual appearance of a worksheet is a critical step in creating a valuable tool for you or your coworkers when making key decisions. There are accepted professional formatting standards when spreadsheets contain only currency data. For this course, we will use the following Excel Guidelines for Formatting. The first figure displays how to use Accounting number format when ALL figures are currency. Only the first row of data and the totals should be formatted with the Accounting format.  The other data should be formatted with Comma style.  There also needs to be a Top Border above the numbers in the total row.  If any of the numbers have cents, you need to format all of the data with two decimal places.

Figure 1.31a

Often, your Excel spreadsheet will contain values that are both currency and non-currency in nature. When that is the case, you’ll want to use the guidelines in the following figure:

Figure 1.31b

The following steps demonstrate several fundamental formatting skills that will be applied to the workbook that we are developing for this chapter. Several of these formatting skills are identical to ones that you may have already used in other Microsoft applications such as Microsoft® Word® or Microsoft® PowerPoint®.

1. 1. Highlight the range A2:D2 in the Sheet1 worksheet by placing the mouse pointer over cell A2 and left clicking and dragging to cell D2. Click the Bold button in the Font group of commands in the Home tab of the ribbon.
   2. Click the Border button in the Font group of commands in the Home tab of the Ribbon (see Figure 1.32). Select the Bottom Border option from the list to achieve the goal of a border on the bottom of row 2 below the column headings.
      

      Figure 1.32 Font Group of Commands

       

      Keyboard Shortcuts

      Bold Format

      • Hold the CTRL key while pressing the letter B on your keyboard.
   3. Highlight the range A15:D15 by placing the mouse pointer over cell A15 and left clicking and dragging to cell D15.
   4. Click the Bold button in the Font group of commands in the Home tab of the Ribbon.
   5. Click the Border button in the Font group of commands in the Home tab of the Ribbon (see Figure 1.32). Select the Top Border option from the list to achieve the goal of a border on the top of row 15 where totals will eventually display.

      Keyboard Shortcuts

      Italics Format

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

       

      Keyboard Shortcuts

      Underline Format

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

       

      Why?

      Format Column Headings and Totals

      Applying formatting enhancements to the column headings and column totals in a worksheet is a very important technique, especially if you are sharing a workbook with other people. These formatting techniques allow users of the worksheet to clearly see the column headings that define the data. In addition, the column totals usually contain the most important data on a worksheet with respect to making decisions, and formatting techniques allow users to quickly see this information.

1. Highlight the range B3:B14 by placing the mouse pointer over cell B3 and left clicking and dragging down to cell B14.
2. Click the Comma Style button in the Number group of commands in the Home tab of the Ribbon. This feature adds a comma as well as two decimal places. (see Figure 1.33).
   

   Figure 1.33 Number Group of Commands

3. Since the figures in this range do not include cents, click the Decrease Decimal button in the Number group of commands in the Home tab of the Ribbon two times (see Figure 1.33).
4. The numbers will also be reduced to zero decimal places.
5. Highlight the range C3:C14 by placing the mouse pointer over cell C3 and left clicking and dragging down to cell C14.
6. Click the Accounting Number Format button in the Number group of commands in the Home tab of the Ribbon (see Figure 1.33). This will add the US currency symbol and two decimal places to the values. This format is common when working with pricing data. As discussed above in the Formatting Data and Cells section, you will want to use Accounting format on all values in this range since the worksheet contains non-currency as well as currency data.
7. Highlight the range D3:D14 by placing the mouse pointer over cell D3 and left clicking and dragging down to cell D14.
8. Again, select the Accounting Number Format; this will add the US currency symbol to the values as well as two decimal places.
9. Click the Decrease Decimal button in the Number group of commands in the Home tab of the Ribbon.
10. This will add the US currency symbol to the values and reduce the decimal places to zero since there are no cents in these figures.
11. Highlight the range A1:D1 by placing the mouse pointer over cell A1 and left clicking and dragging over to cell D1.
12. Click the down arrow next to the Fill Color button in the Font group of commands in the Home tab of the Ribbon (see Figure 1.34). This will prepare the range for a worksheet title.
    

    Figure 1.34 Fill Color Palette

     

13. Click the Blue, Accent 1, Darker 25% color from the palette (see Figure 1.34). Notice that as you move the mouse pointer over the color palette, you will see a preview of how the color will appear in the highlighted cells. Experiment with this feature.
14. Click on A1 and enter the worksheet title: General Merchandise World and click on the check mark in the formula bar to enter this information.
15. Since the black font is difficult to read on the blue background, you’ll change the font color to be more visible. Click the down arrow next to the Font Color button in the Font group of commands in the Home tab of the Ribbon; select White as the font color for this range (see Figure 1.32).
16. Highlight the range A1:D15 by placing the mouse pointer over cell A1 and left clicking and dragging down to cell D15.
17. Click the drop-down arrow on the right side of the Font button in the Home tab of the Ribbon; select Arial as the font for this range. (see Figure 1.32).
18. Notice that as you move the mouse pointer over the font style options, you can see the font change in the highlighted cells.
19. Expand the column width of Column D to 14 characters.

Figure 1.35 shows how the Sheet1 worksheet should appear after the formatting techniques are applied.

Figure 1.35 Formatting Techniques Applied

DATA ALIGNMENT (WRAP TEXT, MERGE CELLS, AND CENTER)

The skills presented in this segment show how data are aligned within cell locations. For example, text and numbers can be centered in a cell location, left justified, right justified, and so on. In some cases you may want to stack multiword text entries vertically in a cell instead of expanding the width of a column. This is referred to as wrapping text. These skills are demonstrated in the following steps:

1. Highlight the range B2:D2 by placing the mouse pointer over cell B2 and left clicking and dragging over to cell D2.
2. Click the Center button in the Alignment group of commands in the Home tab of the Ribbon (see Figure 1.36). This will center the column headings in each cell location.
   

   Figure 1.36 Alignment Group in Home Tab

    

3. Click the Wrap Text button in the Alignment group (see Figure 1.36). The height of Row 2 automatically expands, and the words that were cut off because the columns were too narrow are now stacked vertically.

   Keyboard Shortcuts

   Wrap Text

   • Press the ALT key and then the letters H and W one at a time.

   Why?

   Wrap Text

   The benefit of using the Wrap Text command is that it significantly reduces the need to expand the column width to accommodate multiword column headings. The problem with increasing the column width is that you may reduce the amount of data that can fit on a piece of paper or one screen. This makes it cumbersome to analyze the data in the worksheet and could increase the time it takes to make a decision.

4. Highlight the range A1:D1 by placing the mouse pointer over cell A1 and left clicking and dragging over to cell D1.
5. Click the down arrow on the right side of the Merge & Center button in the Alignment group of commands in the Home tab of the Ribbon.
6. Left click the Merge & Center option (see Figure 1.37). This will create one large cell location running across the top of the data set.

Figure 1.37 Merge Cell Drop-Down Menu

Figure 1.38 shows the Sheet1 worksheet with the data alignment commands applied. The reason for merging the cells in the range A1:D1 will become apparent in the next segment.

Figure 1.38 Sheet1 with Data Alignment Features Added

ENTERING MULTIPLE LINES OF TEXT

In the Sheet1 worksheet, the cells in the range A1:D1 were merged for the purposes of adding a title to the worksheet. This worksheet will contain both a title and a subtitle. The following steps explain how you can enter text into a cell and determine where you want the second line of text to begin:

1. Activate cell A1 in the Sheet1 worksheet by placing the mouse pointer over cell A1 and clicking the left mouse button. Since the cells were merged, clicking cell A1 will automatically activate the range A1:D1. Position your mouse to the end of the title, directly after the “d” in the word “World” and double-click to get a cursor (flashing I-beam).
2. Hold down the ALT key and press the ENTER key. This will start a new line of text in this cell location.
3. Type the text Retail Sales (in millions) and press the ENTER key.
4. Select cell A1. Then click the Italics and Bold buttons in the Font group of commands in the Home tab of the Ribbon.
5. Increase the height of Row 1 to 30 points. Once the row height is increased, all the text typed into the cell will be visible (see Figure 1.39).

Figure 1.39 Title & Subtitle Added to the Worksheet

BORDERS (ADDING LINES TO A WORKSHEET)

In Excel, adding custom lines to a worksheet is known as adding borders. Borders are different from the grid lines that appear on a worksheet and that define the perimeter of the cell locations. The Borders command lets you add a variety of line styles to a worksheet that can make reading the worksheet much easier. The following steps illustrate methods for adding preset borders and custom borders to a worksheet:

1. Click the down arrow to the right of the Borders button in the Font group of commands in the Home page of the Ribbon to view border options. (see Figure 1.40).Figure 1.40 Borders Drop-Down Menu
2. Highlight the range A1:D15. Left click the All Borders option from the Borders drop-down menu (see Figure 1.40). This will add vertical and horizontal lines to the range A1:D15.
3. Highlight the range A2:D2 by placing the mouse pointer over cell A2 and left clicking and dragging over to cell D2.
4. Click the down arrow to the right of the Borders button.
5. Left click the Thick Bottom Border option from the Borders drop-down menu.
6. Highlight the range A14:D14 and apply a Thick Bottom Border from the drop-down menu. The thick border will help maintain the Excel Formatting Guidelines.
7. Highlight the range A1:D15.
8. Click the down arrow to the right of the Borders button.
9. Click More Borders… at the bottom of the List.
10. This will open the Format Cells dialog box (see Figure 1.41). You can access all formatting commands in Excel through this dialog box.
11. In the Style section of the Borders tab, left click the thickest line style (see Figure 1.41).
12. Left click the Outline button in the Presets section (see Figure 1.41).
13. Click the OK button at the bottom of the dialog box (see Figure 1.41).

Figure 1.41 Borders Tab of the Format Cells Dialog Box

Figure 1.42 Borders Added to the Sheet1 Worksheet

AUTOSUM

You will see at the bottom of Figure 1.42 that Row 15 is intended to show the totals for the data in this worksheet. Applying mathematical computations to a range of cells is accomplished through functions in Excel. Chapter 2 “Mathematical Computations” will review mathematical formulas and functions in detail. However, the following steps will demonstrate how you can quickly sum the values in a column of data using the AutoSum command:

1. Activate cell B15 in the Sheet1 worksheet.
2. Click the Formulas tab of the Ribbon.
3. Click the down arrow below the AutoSum button in the Function Library group of commands (see Figure 1.43). Note that the AutoSum button can also be found in the Editing group of commands in the Home tab of the Ribbon.
   

   Figure 1.43 AutoSum Drop-Down List

    

4. Click the Sum option from the AutoSum drop-down menu. The first click will display a flashing marquee around the range. Click the check mark next to the Formula bar to complete the function.
5. Excel will provide a total for the values in the Unit Sales column.
6. Activate cell D15. It would not make sense to total the averages in column C so C15 will be left blank.
7. Repeat steps 3 through 5 to sum the values in the Sales Dollars column (see Figure 1.44).

Figure 1.44 Totals Added to the Sheet1 Worksheet

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. 1. With the left mouse button, double click the Sheet1 worksheet tab at the bottom of the workbook (see Figure 1.45). Type the name Sales by Month.
   2. Press the ENTER key on your keyboard.
   3. With the left mouse button, double click the Sheet2 worksheet tab at the bottom of the workbook.
   4. Type the name Unit Sales Rank to prepare the worksheet for future use.
   5. Press the ENTER key on your keyboard.
      

      Figure 1.45 Renaming a Worksheet Tab

       

   6. Click the Sheet3 worksheet tab.
   7. Click the Home tab of the Ribbon.
   8. Click the down arrow on the Delete button in the Cells group of commands.
   9. Click the Delete Sheet option from the drop-down list. This removes the unneeded worksheet.
   10. Click the Delete button on the Delete warning box (if a warning box appears).
   11. Complete the steps above to delete the newly named Unit Sales Rank worksheet since it’s decided that worksheet is also unnecessary so that you are left with just one worksheet.
   12. Save the changes to your workbook by clicking either the Save button on the Home ribbon; or by selecting the Save option from the File menu.

Figure 1.46 shows the final appearance of the GMW Sales workbook.

Figure 1.46 Final Appearance of the GMW Sales Workbook

Once you have completed a workbook, it is good practice to select the appropriate settings for printing. These settings are in the Page Layout tab of the Ribbon and discussed in this section of the chapter.

PAGE SETUP

Before you can properly print the worksheets in a workbook, you must establish appropriate settings. The following steps explain several of the commands in the Page Layout tab of the Ribbon used to prepare a worksheet for printing:

1. Open the CH1 GMW Sales workbook, if it is not already open.
2. Click the Page Layout tab of the Ribbon.
3. Click the Margins button in the Page Setup group of commands. This will open a drop-down list of options for setting the margins of your printed document.
4. Click the Wide option from the Margins drop-down list. (see Figure 1.47)
5. Click the Orientation button in the Page Setup and select Landscape.
6. Click on the arrow to the bottom right of the Page Setup category to launch the Page Setup options dialog box.
7. Click the Margins tab and locate “Center on Page”. Click the boxes to Horizontally and Vertically center the data on the worksheet. Click OK.

Figure 1.47 Page Layout Commands for Printing

Table 1.2 Printing Resources: Purpose and Use for Page Setup Commands

HEADERS AND FOOTERS

When printing worksheets from Excel, it is common to add headers and footers to the printed document. Information in the header or footer could include the date, page number, file name, company name, and so on. The following steps explain how to add headers and footers to the GMW Sales Data workbook:

1. Click the Insert Ribbon and click on Header & Footer at the right end of the ribbon (located in the Text group). You will see the Design tab added to the Ribbon; this is used for creating the headers and footers for the printed worksheet. Also, this will convert the view of the worksheet from Normal to Page Layout (see Figure 1.48). This Page Layout view makes adding Headers & Footers easy and provides key features to incorporate.
   

   Figure 1.48 Design Tab for Creating Headers and Footers

    

2. Type your name in the center section of the Header.
3. Place the mouse pointer over the left section of the Header and left click (see Figure 1.48).
4. Click the Current Date button in the Header & Footer Elements group of commands in the Design tab of the Ribbon. This view will display as &[Date] until printed or until you return to normal view.
5. Click the Go to Footer button in the Navigation group of commands in the Design tab of the Ribbon.
6. Place the mouse pointer over the far right section of the footer and left click.
7. Click the Page Number button (you may need to click on the Design tab again) in the Header & Footer Elements group of commands in the Design tab of the Ribbon. This view will display as &[Page] until printed or until you return to normal view.
8. Click any cell location outside the header or footer area. The Design tab for creating headers and footers will disappear.
9. Click the Normal view button in the lower right side of the Status Bar (see Figure 1.49).

Figure 1.49 Worksheet in Page Layout View

PRINTING WORKSHEETS AND WORKBOOKS

Once you have established the print settings for the worksheets in a workbook and have added headers and footers, you are ready to print your worksheets. The following steps explain how to print the worksheets in the GMW Sales workbook:

1. Click the File tab on the Ribbon.
2. Click the Print option on the left side of the Backstage view (see Figure 1.50). On the right side of the Backstage view, you will be able to see a preview of your printed worksheet.
   

   Figure 1.50 Backstage View Print option

3. Click the Print Active Sheets button in the Print section of the Backstage view (see Figure 1.50).
4. If your instructor has asked you to print your work, click the Print button.
5. Click the Home tab of the Ribbon.
6. Save and close the CH1 GMW Sales workbook.
7. Compare your work with the self-check answer key (found in the Course Files link) and then submit the CH1 GMW Sales workbook as directed by your instructor.

To assess your understanding of the material covered in the chapter, complete the following assignment.

BASIC MONTHLY BUDGET FOR MEDICAL OFFICE

Download Data File:  PR1 Data

Creating and maintaining budgets are common practices in many careers. Budgets play a critical role in helping a business or household control expenditures. In this exercise you will create a budget for a hypothetical medical office while reviewing the skills covered in this chapter.

1. Open the file name PR1 Data, then Save As PR1 Medical Office Budget.
2. Activate all the cell locations in the Sheet1 worksheet by left clicking the Select All button in the upper left corner of the worksheet.
3. In the Home tab of the Ribbon, set the font style to Arial and the font size to 12 points.  Then click any cell to Deselect.
4. Increase the width of Column A so all the entries in the range A3:A8 are visible. Place the mouse pointer between the letter A and letter B of Column A and Column B. When the mouse pointer changes to a double arrow, left click and drag it to the right until the character width is approximately 18.00.
5. Enter Quarter 1 in cell B2.
6. Use AutoFill to complete the headings in the range C2:E2. Activate cell B2 and place the mouse pointer over the Fill Handle. When the mouse pointer changes to a black plus sign, left click and drag it to cell E2.
7. Highlight the range B2:E2 and click the Format button in the Home tab of the Ribbon. Click the Column Width option, type 11.57 in the Column Width dialog box, and then click the OK button in the Column Width dialog box.
8. Enter the words Medical Office Budget in cell A1.
9. Insert a blank column between Columns A and B by clicking on any cell in Column B. Then, click the drop-down arrow of the Insert button in the Home tab of the Ribbon. Click the Insert Sheet Columns option.
10. Enter the words Budget Cost in cell B2.
11. Adjust the width of Column B to approximately 13.29 characters.
12. Highlight the range A1:F1 and click the Merge & Center button in the Home tab of the Ribbon to merge the cells in that range.
13. Make the following format adjustments to the range A1:F1: bold; italics; change the font size to 14 points; change the cell fill color to Aqua, Accent 5, Darker 50%; and change the font color to white.
14. Increase the height of Row 1 to approximately 24.75 points.
15. Make the following format adjustment to the range A2:F2: bold; and change the cell fill color to Tan, Background 2, Darker 10%. Center the column titles so that they are horizontally centered in each cell.
16. Set the alignment in cell B2 to Wrap Text. Select B2 and choose the Wrap Text button in the Home tab of the Ribbon.
17. Copy cell C3 and paste the contents into the range D3:F3.
18. Copy the contents in the range C6:C8 by highlighting the range and clicking the Copy button in the Home tab of the Ribbon. Then, highlight the range D6:F8 and click the Paste button in the Home tab of the Ribbon.
19. Calculate the total budget for all four quarters for the salaries. Activate cell B3 and click the down arrow on the AutoSum button in the Formulas tab of the Ribbon. Click the Sum option from the drop-down list. Then, highlight the range C3:F3 and press the ENTER key on your keyboard.
20. Copy the formula in cell B3 and paste them into the range B4:B8.
21. Format the range B3:F8 with Accounting format and zero decimal places.
22. Highlight the range A1:F8 and click the down arrow next to the Borders button in the Home tab of the Ribbon. Select the All Borders option from the drop-down list.
23. Double click the Sheet1 worksheet tab to change the name of Sheet1 to the word Budget, and press the ENTER key.  Delete any unnecessary worksheets.
24. Change the orientation of the Budget worksheet so it prints landscape instead of portrait.
25. Use Fit to 1 page so the Budget worksheet prints on one piece of paper, if it does not already.
26. Add a header to the Budget worksheet that shows the date in the upper left corner and your name in the center.
27. Add a footer to the Budget worksheet that shows the page number in the lower right corner.
28. Save PR1 Medical Office Budget workbook.
29. Compare your work with the self-check answer key (found in the Course Files link) and then submit the PR1 Medical Office Budget workbook as directed by your instructor.

SALES AND INVENTORY ITEMS

Download Data File: SC1 Data

A key activity for marketing professionals is to analyze projected sales and inventory information. This is especially important for retail environments. This exercise utilizes the skills covered in this chapter to analyze sales and inventory data.

1. Open the file named SC1 Data and then Save As SC1 Sales and Inventory
2. In the Sheet1 worksheet, enter the word Totals in cell C14.
3. Format all the cells in Sheet1 to Century font style and a 12-point font size.
4. Set the column width for Columns A through G to 13.5.
5. Edit the entry in cell B2 to read “Item Number.”
6. Use AutoFill to fill the Item Numbers from B3 into the range B4:B13.  The item numbers should increase by one as they are filled through the range.
7. Copy the contents of cell A3 and paste them into the range A4:A8.
8. Delete Column F.
9. Format the range A1:F2 so the text is Bold.
10. Set the alignment in the range A2:F2 to Wrap Text.
11. Prepare A1:F1 for the title text by changing the fill color of the cells in the range A1:F1 to Red, Accent 2, Darker 25%.
12. Make the following font changes to the range A1:F1: set the font color to white, add italics, and set the font size to 14.
13. Merge and center the cells in the range A1:F1.
14. Enter the title for this worksheet in the range A1:F1. The title should appear on two lines. The first line should read Status Report. The second line should read Sales and Inventory by Item.
15. Increase the height of Row 1 so the entire title is visible.
16. Format the values in the range C3:C13 with dollar signs and two decimal places.
17. Format the values in the range E3:F13 with comma style, zero decimal places.
18. In cell E14, use AutoSum to calculate the sum of the values in the range E3:E13.
19. In cell F14, use AutoSum to calculate the sum of the values in the range F3:F13.
20. Apply All Borders to the range A1:F14.
21. Add a thick bottom border to row 2; add a thick bottom border to row 13.
22. Add a thick line border around the perimeter of the range A1:F14.
23. Insert a new blank worksheet in the workbook (this will be Sheet4).
24. Delete Sheet3.
25. Move Sheet4 ahead of Sheet2 so the order of the worksheets is Sheet1, Sheet4, and Sheet2.
26. Rename the Sheet1 worksheet tab to “Status Report.”
27. Change the orientation of the Status Report worksheet so it prints landscape instead of portrait.
28. Add a header to the Status Report worksheet that shows the date (needs to update) in the upper left corner and your name in the center.
29. Add a footer to the Status Report worksheet that shows the page number in the lower right corner with the word “Page” before the number.
30. Center the worksheet both horizontally and vertically on the sheet.
31. Save the SC1 Sales and Inventory workbook.
32. Submit the SC1 Sales and Inventory workbook as directed  by your instructor.

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

THE FUNDAMENTALS OF LOANS AND LEASES

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

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

Table 2.5 Key Terms for Loans and Leases

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

Figure 2.29 Example of an Amortization Table

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

THE PMT (PAYMENT) FUNCTION FOR LOANS

Data file: Continue with CH2 Personal Budget.

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

Table 2.6 Arguments for the PMT Function

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

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

1. Click the Mortgage Payments worksheet tab.
2. Click cell B5.
3. Click the Formulas tab on the Ribbon.
4. Click the Insert Function button (see Figure 2.30). This opens the Insert Function dialog box, which can be used for searching all functions in Excel.
5. In the “Search for a function:” input box at the top of the Insert Function dialog box, type mortgage payments (see Figure 2.31). Note that the current description in the “Search for a function:” input box will already be highlighted. You can begin typing and the description will be replaced with your entry.
6. Click the Go button in the upper right side of the Insert Function dialog box. This adds all the Excel functions that match your description in the “Select a function:” box in the lower half of the Insert Function dialog box (see Figure 2.31).
7. Click the PMT option in the “Select a function:” box in the lower half of the Insert Function dialog box.
8. Click the OK button at the lower right side of the Insert Function dialog box. This will open the Function Arguments dialog box.
   

   Figure 2.30 Mortgage Payments Worksheet

   

   Figure 2.31 Insert Function Dialog Box

    

9. Click the Collapse Dialog button next to the Rate argument in the Function Arguments dialog box. This will be the first argument defined for the function (see Figure 2.32)
10. Click cell B3 on the worksheet. This is the rate being charged on the loan.
11. Type a forward slash / for division.
12. Type the number 12. Since our goal is to calculate the monthly payments for the loan, we need to divide the rate, which is stated in annual terms, by 12. This converts the annual rate to a monthly rate.
13. Press the ENTER key on your keyboard. This returns the Function Arguments dialog box to its expanded form. You will also see that the Rate argument is now defined.
14. Click the Collapse Dialog button next to the Nper argument in the Function Arguments dialog box. This is the second argument we define in the function.
15. Click cell B4 on the worksheet. This is the term or the amount of time we have to repay the loan.
16. Type an asterisk * for multiplication.
17. Type the number 12. Since our goal is to calculate the monthly payments for the loan, we need to multiply the terms of the loan by 12. This converts the terms of the loan from years to months.
18. Press the ENTER key on your keyboard. This returns the Function Arguments dialog box to its expanded form. You will also see that the Nper argument is now defined.
19. Click the Collapse Dialog button next to the Pv argument in the Function Arguments dialog box. This is the third argument we will define in the function.
20. Type a minus sign −. When defining the Pv argument of the PMT function, any cell location or value must be preceded with a minus sign.
21. Click cell B2 on the worksheet. This is the principal of the loan.
22. Press the ENTER key on your keyboard. You will now see the Rate, Nper, and Pv arguments defined for the function.
23. Click the OK button at the bottom of the Function Arguments dialog box. The function will now be placed into the worksheet. Since we are not paying any lump sums of money at the end of the loan, there is no need to define the Fv argument. Also, we will assume that the monthly mortgage payments will be made at the end of each month. Therefore, there is no need to define the Type argument.

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

Figure 2.32 Function Arguments Dialog Box for the PMT Function

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

Figure 2.33 Mortgage Payments Worksheet with the PMT Function

THE PMT (PAYMENT) FUNCTION FOR LEASES

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

1. Click cell B6 in the Car Lease Payments worksheet.
2. Click the Formulas tab on the Ribbon.
3. Click the Financial button in the Function Library group. This opens the Financial Function drop down list.
4. Scroll down and click on the PMT Function in the drop down list. This will open the PMT Function Arguments dialog box. See Figure 2.34.
5. Click the Collapse Dialog button next to the Rate argument in the PMT Function Arguments dialog box. This will be the first argument defined for the monthly car lease payment.
6. Click cell B4. This is the interest rate being charged for the lease.
7. Type the forward slash / for division.
8. Type the number 12. Since our goal is to calculate the monthly lease payments, we divide the interest rate by 12 to convert the annual rate to a monthly rate.
9. Press the ENTER key on your keyboard. This returns the Function Arguments dialog box to its expanded form. You will also see that the Rate argument is now defined.
10. Click the Collapse Dialog button next to the Nper argument in the Function Arguments dialog box. This is the second argument we define in the function.
11. Click cell B5. This is the term or the length of time for the lease contract. Since the term is already expressed in months, we can just reference cell B5 and move to the next argument. If the term was defined in years, instead of months, we would need to multiply the terms (years) of the loan by 12 to convert the years to months.
12. Press the ENTER key on your keyboard. This returns the Function Arguments dialog box to its expanded form. You will also see that the Nper argument is now defined.
13. Click the Collapse Dialog button next to the Pv argument in the Function Arguments dialog box. This is the third argument we will define in the function.
14. Type a minus sign −. Remember that cell locations or values used to define the Pv argument must be preceded with a minus sign.
15. Click cell B2 on the worksheet, which is the price of the car.
16. Press the ENTER key on your keyboard. You will now see the Rate, Nper, and Pv arguments defined for the function.
17. Click the Collapse Dialog button next to the Fv argument in the Function Arguments dialog box. This is the fourth argument we will define in the function.
18. Click cell B3 on the worksheet. This is the residual value of the car. Note that cell location and values used to define the [Fv] argument are NOT preceded by a minus sign.
19. Press the ENTER key on your keyboard. You will now see the Rate, Nper, Pv and Fv arguments defined for the function.
20. Click the Collapse Dialog button next to the Type argument in the Function Arguments dialog box. This is the fifth and last argument we will define in the function.
21. Type the number 1. We will assume that the lease payments will be due at the beginning of each month. For payments made at the beginning of the period, a 1 will be entered in the Type argument box. For payments made at the end of the period, a 0 will be entered in the Type argument box.
22. Press the ENTER key. You will now see the Rate, Nper, Pv, Fv and Type arguments defined for the function. See Figure 2.34.
23. Click the OK button at the bottom of the Function Arguments dialog box. The function will now be placed into the worksheet.

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

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

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

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

LINKING WORKSHEETS (CREATING A SUMMARY WORKSHEET)

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

1. Click cell C3 in the Budget Summary worksheet.
2. Type an equal sign =.
3. Click the Budget Detail worksheet tab.
4. Click cell D12 on the Budget Detail worksheet.
5. Press the ENTER key on your keyboard. The output of the SUM function in cell D12 on the Budget Detail worksheet will be displayed in cell C3 on the Budget Summary worksheet.

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

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

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

1. Click cell C4 in the Budget Summary worksheet.
2. Type an equal sign =.
3. Click the Mortgage Payments worksheet tab.
4. Click cell B5 in the Mortgage Payments worksheet.
5. Type an asterisk * for multiplication.
6. Type the number 12. This multiplies the monthly payments by 12 to calculate the total payments required for the year. The formula in the formula bar should read: =’Mortgage Payments’!B5*12
7. Press the ENTER key on your keyboard. The value of multiplying the monthly mortgage payments by 12 is now displayed on the Budget Summary worksheet.
8. Click cell C5 on the Budget Summary worksheet.
9. Type an equal sign =.
10. Click the Car Lease Payments worksheet tab.
11. Click cell B6 in the Car Lease Payments worksheet.
12. Type an asterisk * for multiplication.
13. Type the number 12. This multiplies the monthly lease payments by 12 to calculate the total payments required for the year.
14. Press the ENTER key on your keyboard. The value of multiplying the monthly lease payments by 12 is now displayed on the Budget Summary worksheet.

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

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

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

1. Click cell D6 in the Budget Summary worksheet.
2. Type an equal sign =.
3. Type the function name SUM followed by an open parenthesis (.
4. Highlight the range C3:C5.
5. Type a closing parenthesis ) and press the ENTER key on your keyboard or simply press the ENTER key to close the function. The total for all annual expenses now appears on the worksheet.
6. Click cell D7 on the Budget Summary worksheet. You will enter a formula to calculate Net Change in Cash in this cell.
7. Type an equal sign =.
8. Click cell D2.
9. Type a minus sign − and then click cell D6.
10. Press the ENTER key on your keyboard. This formula produces an output of $1,942, indicating our income is greater than our total expenses.

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

Figure 2.38 Formulas Added to Show Income Is Greater Than Expenses

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

1. Click cell E6 in the Budget Summary worksheet.
2. Type an equal sign =.
3. Click cell D6.
4. Type a forward slash / for division and then click D2.
5. Press the F4 key on your keyboard. This adds an absolute reference to cell D2.
6. Press the ENTER key. The result of the formula shows that total expenses consume 94.1% of our net income.
7. Click cell E6.
8. Place the mouse pointer over the Auto Fill Handle.
9. When the mouse pointer turns to a black plus sign, left click and drag down to cell E7. This copies and pastes the formula into cell E7.
10. Save the CH2 Personal Budget file.
11. Compare your work with the self-check answer key (found in the Course Files link) and then submit the CH2 Personal Budget workbook as directed by your instructor.
12. Close the CH2 Personal Budget file before moving on to 2.4 – Preparing to Print.

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

Figure 2.39 Calculating the Savings Rate

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

Figure 2.40 Finished Prepare to Print Worksheet in Print Preview

FORMATTING WORKSHEET DATA

Download Data File: CH2 PTP Data

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

1. Open the Data file named CH2 PTP Data and use the File/Save As command to save it with the new name CH2 Sales Data.
2. To change the font of the entire worksheet, click the Select All button in the top left corner of the worksheet grid (see Figure 2.41).
   

   Figure 2.41 Select All button

3. Change the font to Calibri, Size 12.
4. Using the skills learned in Chapter 1, make the following formatting changes:
   1. A1:H1 – Merge and Center; format text as bold and apply a font color and size of your choice
   2. A2:H2 – Merge and Center; format text as bold and italic, apply a font color of your choice
   3. A5:H5 – Apply a dark fill color; format text as white and bold
   4. C5:H5 – Center align
   5. A15:H15 – Apply Top Border to the cells; format text as bold
   6. C6:H6 and C15:H15 – Apply Accounting Number format with 0 decimal places
   7. C7:H14 – Apply Comma style with 0 decimal places
   8. Highlight A6:A14 (salespeople’s names) and click the Increase Indent button in the Alignment group on the Home ribbon (see Figure 2.42). This will indent the text from the cell border.

Figure 2.42 Increase Indent button

USING PAGE SETUP OPTIONS

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

1. With the CH2 Sales Data file still open, go to Backstage View by clicking the File tab on the ribbon. Select Print from the menu. Notice that the worksheet is currently printing on two pages, with the page breaking between the April and May columns. To fix this problem, you will first change the left and right margins while still in Print Preview
2. Click the Margins drop-down arrow in the Settings section (see Figure 2.43)
3. Select Custom Margins… at the bottom of the list.
4. Type in 0.5 for the Left Margin and 0.5 for the Right Margin.
5. Click OK. Changing the margins brought the May column onto the same page, but the June column is still on a separate page. Next you will use Page Scaling to fix this while still in Print Preview.
6. Click the Scaling drop-down arrow in the Settings section (Figure 2.43).
7. Select Fit All Columns on One Page.
8. Exit Backstage View.

Figure 2.43 Settings section of Print Preview

CREATING A FOOTER USING PAGE SETUP

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

1. Click the Page Layout tab on the ribbon.
2. Click the dialog box launcher in the Page Setup group. A window similar to Figure 2.44should appear.
   

   Figure 2.44 Page Setup Dialog Box

3. Click the Header/Footer tab in the Page Setup dialog box.
4. Click the Custom Footer button. The Footer dialog box should appear (see Figure 2.45).
   

   Figure 2.45 Footer Dialog Box

5. Click in the Left section: box and type Printed on.
6. Making sure to leave a space after the word on, click the Insert Date button.
7. Click in the Right section: box and type Filename:.
8. Making sure to leave a space after the colon, click the Insert File Name button.
9. The Footer dialog box should look like Figure 2.46.
10. Click the OK button. Click OK again to close the Page Setup dialog box.
11. Go to Print Preview to see that the current date and file name are displayed in the footer.
12. Exit Backstage View. Save the CH2 Sales Data file.
13. Compare your work with the self-check answer key (found in the Course Files link) and then submit the CH2 Sales Dataworkbook as directed by your instructor.

Figure 2.46 Completed Custom Footer Dialog Box

This section reviews the most commonly used Excel chart types. To demonstrate the variety of chart types available in Excel, it is necessary to use a variety of data sets. This is necessary not only to demonstrate the construction of charts but also to explain how to choose the right type of chart given your data and the idea you intend to communicate.

CHOOSING A CHART TYPE

Before we begin, let’s review a few key points you need to consider before creating any chart in Excel.

1. The first is identifying your idea or message. It is important to keep in mind that the primary purpose of a chart is to present quantitative information to an audience. Therefore, you must first decide what message or idea you wish to present. This is critical in helping you select specific data from a worksheet that will be used in a chart. Throughout this chapter, we will reinforce the intended message first before creating each chart.
2. The second key point is selecting the right chart type. The chart type you select will depend on the data you have and the message you intend to communicate.
3. The third key point is identifying the values that should appear on the X and Y axes. One of the ways to identify which values belong on the X and Y axes is to sketch the chart on paper first. If you can visualize what your chart is supposed to look like, you will have an easier time selecting information correctly and using Excel to construct an effective chart that accurately communicates your message. Table 4.1 “Key Steps Before Constructing an Excel Chart” provides a brief summary of these points.

Table 4.1 Key Steps before Constructing an Excel Chart

TIME SERIES TREND: LINE CHART 1

The first chart we will demonstrate is a line chart. Figure 4.1 shows part of the data that will be used to create two line charts. This chart will show the trend of the NASDAQ stock index.

Read more: http://www.investopedia.com/terms/n/nasdaq.asp

This chart will be used to communicate a simple message: to show how the index has performed over a two-year period. We can use this chart in a presentation to show whether stock prices have been increasing, decreasing, or remaining constant over the designated period of time.

Figure 4.1 Stock Trends

Before we create the line chart, it is important to identify why it is an appropriate chart type given the message we wish to communicate and the data we have. When presenting the trend for any data over a designated period of time, the most commonly used chart types are the line chart and the column chart. With the column chart, you are limited to a certain number of bars or data points. As you increase the number of bars on a column chart, it becomes increasingly difficult to read. As you scroll through the data on the worksheet shown in Figure 4.1 you will see that there are 24 points of data used to construct the chart. This is generally too many data points to put on a column chart, which is why we are using a line chart. Our line chart will show the volume of sales for the NASDAQ on the Y axis and the Month number on the X axis. The following steps explain how to construct this chart:

Download Data file: CH4 Data

1. Open data file CH4 Data and save a file to your computer as CH4 Charting.
2. Navigate to the Stock Trend worksheet.
3. Highlight the range B4:C28 on the Stock Trend worksheet. (Note – you have selected a label in the first row and more labels in column B. Watch where they show up in your completed chart.)
4. Click the Insert tab of the ribbon.
5. Click the Line button in the Charts group of commands. Click the first option from the list, which is a basic 2D Line Chart (see Figure 4.2).

Figure 4.2 Selecting the Basic Line Chart

This adds, or embeds, the line chart to the worksheet, as shown in Figure 4.3

Figure 4.3 shows the embedded line chart in the Stock Trend worksheet. Do you see where your labels showed up on the chart?

Notice that additional tabs, or contextual tabs, are added to the ribbon. We will demonstrate the commands in these tabs throughout this chapter. These tabs appear only when the chart is activated.

Figure 4.3 Embedded Line Chart in the Stock Trend Worksheet

As shown in Figure 4.3, the embedded chart is not placed in an ideal location on the worksheet since it is covering several cell locations that contain data. The following steps demonstrate common adjustments that are made when working with embedded charts:

1. Moving a chart: Click and drag the upper left corner of the chart to the corner of cell B30.
   Note: Keep an eye on your pointer. It will change into when you are in the right place to move your chart.

    

2. Resizing a chart: Place the mouse pointer over the right upper corner sizing handle, hold down the ALT key on your keyboard, and click and drag the chart so it “snaps” to the right side of Column I.
   Note: keep an eye on your pointer. It will change into when you are in the right place to resize your chart
3. Repeat step 2 to resize the chart so the top “snaps” to the top of Row 30, the bottom “snaps” to the bottom of Row 45, and the left side “snaps” to the left side of Column B. Make sure the right side of the chart snaps to the line between column I and J.
4. Adjusting the chart title: Click the chart title once. Then click in front of the first letter. You should see a blinking cursor in front of the letter. This allows you to modify the title of the chart.
5. Type the following in front of the first letter in the chart title: May 2014-2016 Trend for NASDAQ Sales.
6. Click anywhere outside of the chart to deactivate it.
7. Save your work.

Figure 4.4 shows the line chart after it is moved and resized. You can also see that the title of the chart has been edited to read May 2014-2016 Trend for NASDAQ Sales Volume. Notice that the sizing handles do not appear around the perimeter of the chart. This is because the chart has been deactivated. To activate the chart, click anywhere inside the chart perimeter.

Figure 4.4 Line Chart Moved and Resized

ADJUSTING THE Y AXIS SCALE

After creating an Excel chart, you may find it necessary to adjust the scale of the Y axis. Excel automatically sets the maximum value for the Y axis based on the data used to create the chart. The minimum value is usually set to zero. That is usually a good thing. However, depending on the data you are using to create the chart, setting the minimum value to zero can substantially minimize the graphical presentation of a trend. For example, the trend shown in Figure 4.4 appears to be increasing slightly in recent months. The presentation of this trend can be improved if the minimum value started at 500,000. The following steps explain how to make this adjustment to the Y axis:

1. Click anywhere on the Y (value or vertical) axis on the May 2014-2016 Trend for NASDAQ Sales Volume line chart (Stock Trend worksheet).
2. Right Click and select Format Axis. The Format Axis Pane should appear, as shown in Figure 4.5.

Figure 4.5 Format Axis Pane

1. In the Format Axis Pane, click the input box for the “Minimum” axis option and delete the zero. Then type the number 500000 and hit Enter. As soon as you make this change, the Y axis on the chart adjusts.
2. Click the X in the upper right corner of the Format Axis pane to close it.
3. Save your work.

Figure 4.6 shows the change in the presentation of the trend line. Notice that with the Y axis starting at 500,000, the trend for the NASDAQ is more pronounced. This adjustment makes it easier for the audience to see the magnitude of the trend.

Figure 4.6 Adjusted Y Axis for the S&P 500 Chart

TREND COMPARISONS: LINE CHART 2

We will now create a second line chart using the data in the Stock Trend worksheet. The purpose of this chart is to compare two trends: the change in volume for the NASDAQ and the change in the Closing price.

Before creating the chart to compare the NASDAQ volume and sales price, it is important to review the data in the range B4:D28 on the Stock Trend worksheet. We cannot use the volume of sales and the closing price because the values are not comparable. That is, the closing price is in a range of $45.00 to $115.00, but the data for the volume of Sales is in a range of 684,000 to 3,711,000. If we used these values – without making changes to the chart — we would not be able to see the closing price at all.

The construction of this second line chart will be similar to the first line chart. The X axis will be the months in the range B4:D28.

1. Highlight the range B4:D28 on the Stock Trend worksheet.
2. Click the Insert tab of the ribbon.
3. Click the Line button in the Charts group of commands.
4. Click the first option from the list, which is a basic line chart.

Figure 4.6.5 shows the appearance of the line chart comparing both the volume and the closing price before it is moved and resized. Notice that the line for the closing price (Close) appears as a straight line at the bottom of the chart.  Also, the chart is covering the data again, and the title needs to be changed.

Figure 4.6 Trend Comparison Line Chart

1. Move the chart so the upper left corner is in the middle of cell M3.
   Note: The line representing the closing values is flat along the bottom of the chart. This is hard to see and not very useful as is. Fear not. We will fix that.
2. Resize the chart, using the resizing handles and the ALT key, so the left side is locked to the left side of Column M, the right side is locked to the right side of Column U, the top is locked to the top of Row 3, and the bottom is locked to the bottom of Row 17.
3. Click in the text box that says “Chart Title.” Delete the text and replace it with the following: 24 Month Trend Comparison.

Good. But, we still cannot really see the Closing Price data. It is the flat red line at the very bottom of the chart.

1. Right click the red line across the bottom of the chart that represents the Closing Price.
2. On the menu, select Format Data Series. This will open the Format Data Series pane.
3. In the Series Options, select Secondary Axis.

Figure 4.7 Adding a Secondary Axis

Better! But, it would be nice to be able to see that the values on the right represent prices.

1. Right click the Secondary Vertical Axis. (The vertical axis on the right that goes from 0 to 140.)
2. From the menu, select Format Axis.
3. In Axis Options, select Number. (You may have to scroll down to see it.)
4. Use the Symbol list box to add the $.
5. Press the Close button to close the Format Axis pane.
6. Save your work.

Figure 4.8 Modifying the Secondary Axis

Figure 4.9 Final Comparison Line Chart

“INSTANT” CHART – F11

On the Stock Trend worksheet:

1. Select A4:A28.
2. Hold down the Ctrl key and select D4:D28. Figure 4.10 shows what that will look like.
   

   Figure 4.10 Range Selection

3. Press F11. (The F11 function key is on the top row of the keyboard.)  If the factory default settings haven’t been changed, Excel will create a column chart and place it on a separate chart sheet. (See Figure 4.11).
4. Change the name of the chart sheet by double-clicking the worksheet name Chart1. Type Closing Prices as the new name and hit Enter.
5. Save your work.

Figure 4.11 Instant Chart

FREQUENCY DISTRIBUTION: COLUMN CHART 1

A column chart is commonly used to show trends over time, as long as the data are limited to approximately twenty points or less. A common use for column charts is frequency distributions. A frequency distribution shows the number of occurrences by established categories. For example, a common frequency distribution used in most academic institutions is a grade distribution. A grade distribution shows the number of students that achieve each level of a typical grading scale (A, A−, B+, B, etc.). The Grade Distribution worksheet contains final grades for some hypothetical Excel classes. To show the grade frequency distribution for all the Excel classes in that year, the numbers of students appear on the Y axis and the grade categories appear on the X axis. The number of students for this chart is in Column C. The labels for grades are in Column A. The following steps explain how to create this chart:

1. Select the Grade Distribution worksheet.
2. Change the years in Row3 to the current academic term and year.
3. Highlight the range A3:A8 on the Grade Distribution worksheet. Column A shows the grade categories.
4. Hold down the Crtl key.
5. Without letting go of the Ctrl key, select C3:C8
6. Click the Column button in the Charts group section on the Insert tab of the ribbon. Select the first option in the 2-D Column section, which is the Clustered Column format.
7. Click and drag the chart so the upper left corner is in the middle of cell H2.
8. Resize the chart so the left side is locked to the left side of Column H, the right side is locked to the right side of Column O, the top is locked to the top of Row 2, and the bottom is locked to the bottom of Row 16.
9. If Excel displays a legend, delete it by clicking the legend one time and pressing the DELETEkey on the keyboard. Since the chart presents only one data series, the legend is not necessary.
10. Add the text Final Grades for to the chart title. The chart title should now be Final Grades for All Excel Classes 2016/2017 (or whichever academic year you are using).
11. Click any cell location on the Grade Distribution worksheet to deactivate the chart.
12. Save your work.

Figure 4.12 shows the completed grade frequency distribution chart. By looking at the chart, you can immediately see that the greatest number of students earned a final grade in the B+ to B− range.

Figure 4.12 Grade Frequency Distribution Chart

CREATING A CHART SHEET

1. Click anywhere on the Final Grades for All Excel Classes chart on the Grade Distribution worksheet.
2. Right click on the chart. Select Move Chart . . . This opens the Move Chart Dialog box.
3. Click the New sheet option on the Move Chart dialog box. (The top option.)
4. The entry in the input box for assigning a name to the chart sheet tab should automatically be highlighted once you click the New sheet option. Type All Excel Classes. This replaces the generic name in the input box (see Figure 4.13).
5. Click the OK button at the bottom of the Move Chart dialog box. This adds a new chart sheet to the workbook with the name All Excel Classes.
6. Save your work.

Figure 4.13 Moving a Chart to a Chart Sheet

Figure 4.14 shows the Final Grades for the all the Excel Classes column chart is in a separate chart sheet. Notice the new worksheet tab added to the workbook matches the New sheet name entered into the Move Chart dialog box. Since the chart is moved to a separate chart sheet, it no longer is displayed in the Grade Distribution worksheet.

Figure 4.14 Chart Sheet Added to the Workbook

FREQUENCY COMPARISON: COLUMN CHART 2

We will create a second column chart to show a comparison between two frequency distributions. Column B on the Grade Distribution worksheet contains data showing the number of students who received grades within each category for the Spring Quarter. We will use a column chart to compare the grade distribution for Spring (Column B) with the overall grade distribution for the whole year (Column C).

However, since the number of students in the term is significantly different from the total number of students in the year, we must calculate percentages in order to make an effective comparison. The following steps explain how to calculate the percentages:

1. Highlight the range B9:C9 on the Grade Distribution worksheet.
2. Click the AutoSum button in the Editing group of commands on the Home tab of the ribbon. This automatically adds SUM functions that sum the values in the range B4:B8 and C4:C8.
3. Activate cell E4 on the Grade Distribution worksheet.
4. Enter a formula that divides the value in cell B4 by the total in cell B9. Add an absolute reference to cell B9 in the formula =B4/$B$9.
5. Copy the formula in cell E4 and paste it into the range E5:E8 using the Paste command.
   Or, use the Fill Handle to copy the calculation in E4 all the way down to E8.
6. Activate cell F4 on the Grade Distribution worksheet.
7. Enter a formula that divides the value in cell C4 by the total in cell C9. Add an absolute reference to cell C9 in the formula =C4/$C$9.
8. Copy the formula in cell F4 and paste it into the range F5:F8 using the Paste command.
   Or, use the Fill Handle to copy the calculation in E4 all the way down to E8.

Figure 4.15 Completed Grade Distribution Percentages

Figure 4.15 shows the completed percentages added to the Grade Distribution worksheet.

The column chart we are going to create uses the grade categories in the range A4:A8 on the X axis and the percentages in the range E4:F8 on the Y axis. This chart uses data that is not in a contiguous range, so we need to use the Ctrl key to select the ranges of cells.

1. Select A3:A8, hold down the Ctrl key and select E3:F8.
2. Click the Insert tab of the ribbon.
3. Click the Column button in the Charts group of commands. Select the first option from the drop-down list of chart formats, which is the Clustered Column.
4. Click and drag the chart so the upper left corner is in the middle of cell H2.
5. Resize the chart so the left side is locked to the left side of Column H, the right side is locked to the right side of Column N, the top is locked to the top of Row 2, and the bottom is locked to the bottom of Row 16.
6. Change the chart title to Grade Distribution Comparison. If you do not have a chart title, you can add one. On the Design tab, select Add Chart Element. Find the Chart Title. Select the Above Chart option from the drop-down list.
7. Save your work.

Figure 4.16 Completed Data Series for the Class Grade Distribution

Figure 4.17 shows the final appearance of the column chart. The column chart is an appropriate type for this data because there are fewer than twenty data points and we can easily see the comparison for each category. An audience can quickly see that the class issued fewer As compared to the college. However, the class had more Bs and Cs compared with the college population.

Figure 4.17 Completed Grade Distribution Column Chart

Integrity Check

Too Many Bars on a Column Chart?
Although there is no specific limit for the number of bars you should use on a column chart, a general rule of thumb is twenty bars or less. Figure 4.18 contains a total of thirty-two bars. This is considered a poor use of a column chart because it is difficult to identify meaningful trends or comparisons. The data used to create this chart might be better used in two or three different column charts, each with a distinct idea or message.

 

Figure 4.18 Poor Use of a Column Chart

PERCENT OF TOTAL: PIE CHART

The next chart we will demonstrate is a pie chart. A pie chart is used to show a percent of total for a data set at a specific point in time. The data we will use to demonstrate a pie chart is related to enrollment data for Portland Area Community Colleges for Fall of 2014. You will find that data on the Enrollment Statistics sheet.

1. Highlight the range A2:B6 on the Enrollment Statistics worksheet.
2. Click the Insert tab of the ribbon.
3. Click the Pie button in the Charts group of commands.
4. Select the first “2-D Pie” option from the drop-down list of options.
5. To make the “slices” stand out better, “explode” the pie chart.
   • Click and hold the mouse button down in any of the slices of the pie.
   • Note that you have selection handles on all of the pie slices.
   • Without letting go of your mouse button; drag one of the slices away from the center.
   • All of the slices “explode” out from the center.

Note: if you let go of the mouse button before dragging, you may only get one slice to move when you drag it out from the center. This can be another option for displaying your data.  Use the Undo button to undo this if you want to try again.

1. Click off the slices and into the white canvas to deselect the pie and select the entire chart.
2. Click and drag the pie chart so the upper left corner is in the middle of cell E2.
3. Resize the pie chart so the left side is locked to the left side of Column E, the right side is locked to the right side of Column L, the top is locked to the top of Row 2, and the bottom is locked to the bottom of Row 10 (see Figure 4.19).

Figure 4.19 Pie Chart Moved and Resized

1. Click the chart legend once and press the DELETE key on your keyboard. A pie chart typically shows labels next to each slice. Therefore, the legend is not needed.
2. Right click any of the slices in the pie chart, and select Add Data Labels from the list. This will add the values for each of the slices in the pie.
3. Now, you can right click one of the numbers and select Format Data Labels from the list. This will open the Format Data Labels pane on the right.
4. Check the boxes for Category Name and Percentage in the Label Options section in theFormat Data Labels pane.  This will add the Race/ethnicity labels as well as the percentage data to the pie chart.
5. Uncheck the box next to the Value box. This will remove the numbers from the pie chart (see Figure 4.20).
6. Click the Close button at the top of the Format Data Labels pane.
7. Select the data labels again (if needed). Click the Home tab of the ribbon and then click the Bold button. This will bold the data labels on the pie chart.
8. Save your work.

Figure 4.20 Final Settings in the Format Data Labels Pane

Although there are no specific limits for the number of categories you can use on a pie chart, a good rule of thumb is ten or less. As the number of categories exceeds ten, it becomes more difficult to identify key categories that make up the majority of the total.

Figure 4.21 Final Enrollment Statistics Pie Chart

Skill Refresher

Inserting a Pie Chart

1. Highlight a range of cells that contain the data you will use to create the chart.
2. Click the Insert tab of the ribbon.
3. Click the Pie button in the Charts group.
4. Select a format option from the Pie Chart drop-down menu.

PERCENT OF TOTAL: STACKED COLUMN CHART

The last chart type we will demonstrate is the stacked column chart. We use a stacked column chart to show a percent of a total . For example, the data on the Enrollment Statistics worksheet shows student enrollment by race for several colleges. We would like to see all of the data on all of the colleges.

1. Highlight the range A2:D6 on the Enrollment Statistics worksheet.
2. Click the Insert tab of the ribbon.
3. Click the Column button in the Charts group of commands. Select the 100% Stacked Column format option from 2-D Column section in the drop-down list (see Figure 4.22).

Figure 4.22 Selecting the 100% Stacked Column Chart

Figure 4.23 shows the column chart that is created after selecting the 100% Stacked Column format option. As mentioned, the goal of this chart is to show the enrollment of students by race. However, notice that Excel places the racial categories on the X axis. It would be more useful if the different colleges were there instead.

Figure 4.23 Initial Construction of the 100% Stacked Column Chart

The reason that Excel organized the data this way is that there are more Race/ethnicity categories (data in column A) than there are colleges (data in row 2). Not a bad guess. But, not what we wanted in this case.

The remaining steps explain how to correct this problem and complete the chart:

1. Click the Switch Row/Column button in the Design tab on the Chart Tools section of the ribbon. This reverses the legend and current X axis categories.
   

   Figure 4.24 Switch Row/Column.

2. Click and drag the chart so the upper left corner is in the middle of cell E12.
3. Resize the chart so the left side is locked to the left side of Column E, the right side is locked to the right side of Column N, the top is locked to the top of Row 12, and the bottom is locked to the bottom of Row 30.
4. Click the legend one time and press the DELETE key on your keyboard.
5. Add a Data Table. This is another way of displaying a legend for a column chart along with the numerical values that make up each component.
   • In earlier versions of Excel, find the Labels group of commands and select the Show Data Table with Legend Keys option from the drop-down menu.
   • In Excel 2016, find the Add Chart Element tool on the Design tab, select Data Table With Legend Keys
6. Change the Chart Title to Enrollment by Race.
   • If there is no chart title, you will need to add one using the Add Chart Elementtool on the Design tab.
7. Save your work.

Figure 4.25 shows the final stacked column chart. Notice the similarities and differences in the enrollment at the local community colleges.

Figure 4.25 Final 100% Stacked Column Chart

Skill Refresher:

 Inserting a Stacked Column Chart

1. Highlight a range of cells that contain data that will be used to create the chart.
2. Click the Insert tab of the ribbon.
3. Click the Column button in the Charts group.
4. Select the Stacked Column format option from the Column Chart drop-down menu to show the values of each category on the Y axis. Select the 100% Stacked Column option to show the percent of total for each category on the Y axis.

 

Key Takeaways

• Identifying the message you wish to convey to an audience is a critical first step in creating an Excel chart.
• Both a column chart and a line chart can be used to present a trend over a period of time. However, a line chart is preferred over a column chart when presenting data over long periods of time.
• The number of bars on a column chart should be limited to approximately twenty bars or less.
• When creating a chart to compare trends, the values for each data series must be within a reasonable range. If there is a wide variance between the values in the two data series (two times or more), the percent change should be calculated with respect to the first data point for each series.
• When working with frequency distributions, the use of a column chart or a bar chart is a matter of preference. However, a column chart is preferred when working with a trend over a period of time.
• A pie chart is used to present the percent of total for a data set.
• A stacked column chart is used to show how a percent total changes over time.

You can use a variety of formatting techniques to enhance the appearance of a chart once you have created it. Formatting commands are applied to a chart for the same reason they are applied to a worksheet: they make the chart easier to read. However, formatting techniques also help you qualify and explain the data in a chart. For example, you can add footnotes explaining the data source as well as notes that clarify the type of numbers being presented (i.e., if the numbers in a chart are truncated, you can state whether they are in thousands, millions, etc.). These notes are also helpful in answering questions if you are using charts in a live presentation. We will demonstrate these formatting techniques using the column chart and stacked column chart from the previous section.

X AND Y AXIS FORMATS

There are numerous formatting commands we can apply to the X and Y axes of a chart. Although adjusting the font size, style, and color are common, many more options are available through the Format Axis pane. The following steps demonstrate a few of these formatting techniques on the Grade Distribution Comparison chart:

1. Switch to the Grade Distribution worksheet and click anywhere along the X axis (horizontal axis) of the Grade Distribution Comparison chart.
2. Right click and select Font.
3. Change the font to Arial, the Font Style to Bold, and the Size to 11 (see Figure 4.26).
   

   Figure 4.26 Font Dialog Box

4. Click anywhere along the Y axis to activate it and repeat steps 2 and 3.
5. Click on the chart title and repeat steps 2 and 3, but set the Size to 14.
6. The final appearance of the axes is shown in Figure 4.27 Formatted X & Y Axes.

Figure 4.27 Formatted X and Y Axes

Next we want to make some changes to the percentage numbers on the Y (vertical) axis.

1. Right click the vertical (value) axis. Select Format Axis. This opens the Format Axis pane.
2. Click Number from the list of options. The commands in this section of the Format Axispane are used to format numbers that appear on the selected axis of the chart.
3. Click in the Decimal places input box and change the value to 1.
4. Select Axis Options. Change the Minimum Bound to .05 to make the differences in the columns more dramatic. The Format Axis pane should match Figure 4.28.
5. Click the Close button at the top of the Format Axis pane.
6. Save your work.

Figure 4.28 Format Axis Pane changes

CHART LEGEND AND TITLE FORMATS

The next items we will format on the Grade Distribution Comparison chart are the chart legend and title. Similar to the how we formatted the X and Y axes, we can format these items by activating them and using the formatting commands in the Home tab or the Format pane. The following steps explain how to add these formats:

1. Right click the legend on the Grade Distribution Comparison chart and select Format Legend.
2. Select Right in the Legend Position options. Close the Format Legend pane.
3. Move the legend by placing your cursor – shaped like a little plus sign with four arrows – on the edge of the selection box.  Click and drag the legend so the top of the legend aligns with the 35% line next to the plot area (see Figure 4.29).

Figure 4.29 Moving the Legend

1. While the legend is still selected, change the font style in the Home tab of the ribbon to Arial.
2. Change the font size to 12 points.
3. Click the bold and italics commands in the Home tab of the ribbon.
4. Click and drag the left sizing handle so the legend is against the plot area (see Figure 4.30).

Figure 4.30 Legend Formatted and Resized

1. Click the chart title to activate it.
2. Right click on the chart title and select Format Chart Title to open the Format Chart Titlepane.
3. Under Title Options , in the Effects group (the option in the middle) give your title one of the Preset shadows. Change the color, if you like.
4. Close the Format Chart Title pane.
5. Save your work.

Figure 4.31 Format Chart Title Pane

X AND Y AXIS TITLES

Titles for the X and Y axes are necessary for defining the numbers and categories presented on a chart. For example, by looking at the Grade Distribution Comparison chart, it is not clear what the percentages along the Y axis represent. The following steps explain how to add titles to the X and Y axes to define these numbers and categories:

1. Click anywhere on the Grade Distribution Comparison chart in the Grade Distribution worksheet to activate it.
2. On the Design tab on the ribbon select the Add Chart Element button, then Axis Titles, then Primary Vertical. (See Figure 4.32)

Figure 4.32 Selecting a Title for the Y Axis

3. Using the Home ribbon, change the font of the axis title to Arial, Bold, size 11.
4. Click in the beginning of the Y axis title and delete the generic title. Type Percent of Enrolled Excel Students.(see Figure 4.33).

Figure 4.33 Adding and Formatting the Y Axis Title

Next we will add the title for the X axis.

1. On the Design tab select the Add Chart Element button, then Axis Titles, then Primary Horizontal.
2. Using the Home ribbon, change the font of the axis title to Arial, Bold, size 11.
3. Click in the beginning of the X axis title and delete the generic title. Type Final Course Grade. Figure 4.34 shows the added titles for the X and Y axes. The titles provide definitions for the grade categories along the X axis as well as the percentages on the Y axis.
4. Save your work.

Figure 4.34 X and Y Axis Titles Added

DATA SERIES LABELS AND FORMATS

Adding labels to the data series of a chart is a key formatting feature. A data series is the item that is being displayed graphically on a chart. For example, the blue bars on the Grade Distribution Comparison chart represent one data series. We can add labels at the end of each bar to show the exact percentage the bar represents. In addition, we can add other formatting enhancements to the data series, such as changing the color of the bars or adding an effect. The following steps explain how to add these labels and formats to the chart:

1. Click on any of the the red bars representing the All Excel Classes data series on the Grade Distribution Comparison chart in the Grade Distribution worksheet. Clicking one bar automatically activates all bars in the data series. If you click a bar a second time, only that bar is activated.
2. Right click and select Format Data Series to open up the Format Data Series pane.
3. Click the Fill and Line (paint bucket) button to bring up the Fill and Border group of commands.
4. Click the word Fill (if needed) to expand the list of Fill options.
5. Select Pattern Fill. Then select 30% (fifth column, top row). Changing your fill pattern to a pattern makes it easier to distinguish between the data series when you print or view your chart in black and white. While you are there, make changes to the fill by experimenting with different foreground and background colors.
6. Close the Format Data Series pane.

Figure 4.35 Changing the Fill of a Data Series

Now we are going to add the Data Labels at the end of the columns.

1. Be sure that your entire chart is selected, not just one of the data series. Click the Design tab in the Chart Tools section of the ribbon.
2. On the Design tab select the Add Chart Element button, then Data Labels, then Outside End (see Figure 4.36.)
3. Click on one of the Data Labels. Note that all of  the data labels for that data series are selected.
4. Using the Home ribbon, change the font to Arial, Bold, size 9.
5. Click on one of the data labels for the other data series. Format those data labels as Arial, Bold, size 9 as well.
6. Save your work.

Figure 4.36 Adding Labels to a Data Series

Figure 4.37 shows the Grade Distribution Comparison chart with the completed formatting adjustments and labels added to the data series. Note that we can move each individual data label. This might be necessary if two data labels overlap or if a data label falls in the middle of a grid line. To move an individual data label, click it twice, then click and drag.

Figure 4.37 Completed Formatting Adjustments for the Data Series

ADDING SERIES LINES AND ANNOTATIONS TO A CHART

The last formatting features we will demonstrate are adding series lines and annotations to a chart. To demonstrate these skills, we will use the Change in Enrollment Statistics Spend Source stacked column chart. Series lines are commonly used in stacked column charts to show the change from one stack to the next. Annotations are useful for clarifying the data presented in a chart or for identifying data sources. In addition to demonstrating these skills, we will review several of the formatting skills that were covered in this section. The following steps include the skills review as well as the new formatting features:

1. Locate the Enrollment by Race stacked column chart on the Enrollment Statisticsworksheet. Activate the chart by clicking anywhere inside the chart perimeter.
2. Move the chart to a separate chart sheet by clicking the Move Chart button in the Design tab of the ribbon. Type the following in the New sheet input box: Enrollment by Race Chart. Click the OK button.
3. Click anywhere on the data table (on the x axis) to activate it. Using the Home ribbon, change the font to Arial, Bold, size 12.
4. Activate the Y axis and apply the same formatting adjustments as stated in step 3.
5. Add a Y axis title using Add Chart Elements – Axis Titles – then More Axis Title Options.
6. In the Format Axis Title pane change the fill color and border to colors of your choice.
7. Then, using the Home tab of the ribbon, change the font to Arial, Bold, size 14.
8. Change the text of the Y axis title to Percent Enrollment by Race.
9. Check the horizontal axis to see if this process created an extra axis title there.  If it did, delete it.
10. Activate the title of the chart by clicking it once. The Format Chart Title pane should be open. If not, right click the Chart title and select Format Chart Title from the menu. Change the fill and border to match your vertical Axis label.
11. Then, using the Home tab of the ribbon, change the font of the chart title to Arial, Bold, size 20.
12. Close the Formatting pane.
13. Click the Add Chart Elements tool (Design tab), then Lines, then Series Lines.
    This adds lines to the chart, connecting each data series between the three stacks (see Figure 4.38).

Figure 4.38 Selecting the Series Lines Option

1. Right click on any of the series lines added to the chart. Clicking one line will activate all lines on the chart. (If the Format Pane is open, you will not need to right click.  Just left click on any of the series lines to change the format pane to Format Series Lines)
2. Select Format Series Lines. This will open the Format Series Lines pane.
3. Change the width to 2.25.
4. Close the Format Series Lines pane.

Figure 4.39 shows the appearance of the chart with the series lines connecting the two stacks. This formatting enhancement is common for stacked column charts. The lines help focus the audience’s attention to changes in the percent of total trend.

Figure 4.39 Series Lines Added to the Stacked Column Chart

Our chart demonstrates the percentage differences in enrollment between the community colleges. But, it would be handy to know the total Enrollment at each of the colleges. To display that, we will add text boxes above each column. To start with, we need to make room for the text boxes.

1. Select the Plot Area. Place your cursor on the top center handle of the Plot Area and drag down about ½ inch.

Figure 4.40 Resizing the Plot Area

ADD TEXT BOXES TO INCLUDE ADDITIONAL INFORMATION IN THE CHART.

1. Click the Text Box button in the Text group on the Insert tab of the ribbon (see Figure 4.41).
2. Place the mouse pointer on the left edge of the chart area approximately one-quarter inch from the top. Click and drag a rectangle approximately one and a half inches wide and one-quarter inch high (see Figure 4.41). Don’t worry if it’s not exact – you can move and resize text boxes at any time.
3. Type Total Enrollment. This tells the audience the size of each school.
4. Select all of the text in the text box.  (You can either highlight the text or click on the border of the text box once to select all of the text). Using the Home tab of the ribbon, change the font to Arial, size 14.
5. Repeat the process to add and format text boxes above each column.  You can try to copy and paste the text boxes if you would like to save time.
6. In each text box, type the Total Enrollment for each school:
   • Mt Hood – 9,276
   • Portland – 30,929
   • Clackamas – 7,302
7. Save your work.

Figure 4.41 Completed Stacked Column Chart

Charts that are created in Excel are commonly used in Microsoft Word documents or for presentations that use Microsoft PowerPoint slides. Excel provides options for pasting an image of a chart into either a Word document or a PowerPoint slide. You can also establish a link to your Excel charts so that if you change the data in your Excel file, it is automatically reflected in your Word or PowerPoint files. We will demonstrate both methods in this section.

PASTING A CHART IMAGE INTO WORD

For this exercise you will need two files:

• The Excel spreadsheet you have been working with in this chapter — CH4 Charting.
• A Word document data file — CH4 Diversity

Excel charts can be valuable tools for explaining quantitative data in a written report. Reports that address business plans, public policies, budgets, and so on all involve quantitative data. For this example, we will assume that the Change in Enrollment Statistics Spend Source stacked column chart  is being used in a student’s written report (see Figure 4.44). The following steps demonstrate how to paste an image, or picture, of this chart into a Word document:

1. Open CH4 Diversity. Save it as CH4 Diversity in Enrollment in Community Colleges
2. Click below the figure heading in the Word document that reads: Figure 1: Enrollment by Race. The image of the stacked column chart will be placed below this heading.
3. If needed, open the Excel file you have been working with (CH4 Charting). Activate theEnrollment by Race chart in the Enrollment by Race Chart sheet.
4. Click the down arrow on the Copy button in the Home tab of the ribbon.  Select Copy as Picture
5. Select OK — Accepting the Copy Pictures defaults:
   • As shown on Screen
   • Picture
6. Go back to the CH4 Diversity in Enrollment in Community Colleges Word document by clicking the file in the taskbar.
7. Confirm that the insertion point is below the Figure 1: Enrollment by Race heading (see Figure 4.42) and click the Paste button in the Home tab of the ribbon ( or press Crtl-V).

Figure 4.42 Paste Picture in Word

Oh no!! The picture is so big that it falls on to the next page. We will need to change its size.

1. Click anywhere on the picture of the chart to activate it.
2. Click the Format tab under the Picture Tools section of the ribbon (see Figure 4.43).
3. Click the down arrow on the Shape Width button in the Size group of commands. Continue to click the down arrow until the width of the picture is 5.4.” As you reduce the width of the picture, the height is automatically reduced as well. (The height should be ~ 3.92″)
4. To center the chart on the page, make sure the chart is activated.  Then go to the Home tab, to the Paragraph group, and select Center.
5. Save your work.

Figure 4.43 Changing the Size of a Picture in Word

Figure 4.44 shows the final appearance of the Enrollment by Race Source chart pasted into a Word document. It is best to use either the Shape Width or Shape Height buttons to reduce the size of the chart. Using either button automatically reduces the height and width of the chart in proper proportion. If you choose to use the sizing handles to resize the chart, holding the SHIFT key while clicking and dragging on a corner sizing handle will also keep the chart in proper proportion.

Figure 4.44 Final Appearance of Pasting a Chart Image into Word

PASTING A LINKED CHART IMAGE INTO POWERPOINT

For this exercise you will need two files:

• The Excel spreadsheet you have been working with in this chapter — CH4 Charting.
• A PowerPoint data file – CH4 Diversity.

Microsoft PowerPoint is perhaps the most commonly used tool for delivering live presentations. The charts used in a live presentation are critical for efficiently delivering your ideas to an audience. Similar to written documents, a wide range of presentations may require the explanation of quantitative data. This demonstration includes a PowerPoint slide that could be used in a presentation. We will paste the Enrollment by Race chart into this PowerPoint slide. However, instead of pasting an image, as demonstrated in the Word document, we will establish a link to the Excel file. As a result, if we change the chart in the Excel file, the change will be reflected in the PowerPoint file. The following steps explain how to accomplish this:

1. Open CH4 Diversity.pptx. Save it as CH4 Diversity in Enrollment in Community Colleges.
2. Navigate to Slide 6 – Diversity in Enrollment. This is the slide where you will place the linked chart.
3. If needed, open the Excel file you have been working with (CH4 Charting). Activate theEnrollment by Race chart in the Enrollment by Race Chart sheet.
4. Click the down arrow on the Copy button in the Home tab of the ribbon.  Select Copy (not Copy as Picture.)
5. Go back to the CH4 Diversity in Enrollment in Community Colleges presenation by clicking the file in the taskbar.
6. Make sure you are still on Slide 6 – Diversity in Enrollment. Click on the outside edge of the empty prompt box on the right.
7. Click the down arrow below the Paste button in the Home tab of the ribbon in the PowerPoint file.
8. Hover over each of the Paste Options until you find Keep Source Formatting & Link Data (see Figure 4.45).  Select this option. This pastes an image of the Excel chart into the PowerPoint slide. In addition, a link is created so that any changes made to the chart (in Excel) appear on the PowerPoint slide.

Figure 4.45 Creating a Link to an Excel Chart in PowerPoint

Next we need to make some changes to clean up the chart a bit.  First, we are going to apply a different chart style.

1. Click anywhere in the plot area of the column chart pasted into the PowerPoint slide. You will see the same Excel Chart Tools tabs added to the ribbon (see Figure 4.46).
2. On the Design tab, select Style 8 in the Chart Style group.

Figure 4.46 Modifying and Excel Chart Pasted into a PowerPoint Slide

Paste linking this chart caused trouble with the text boxes we added, so next we are going to delete them.

1. Select each text box by clicking on the outside edge of the text box with the four-headed arrow. Press the delete key on your keyboard. Be sure that the insertion point is NOT blinking inside the text box.  If it is, you will be editing the contents of the text box instead of deleting the actual text box.

The benefit of adding this chart to the presentation as a link is that it will automatically update when you change the data in the linked spreadsheet file.

1. Return to your CH4 Charting Excel file.
2. Select the Enrollment Statistics worksheet (the one with the Enrollment data.) Change the value in cell D3 to 1000.  You have just changed the number of white students at Clackamas Community College to 1000. This isn’t true, but you want to change the data enough to see the effect in the charts.
3. Select the Enrollment by Race Chart worksheet. Notice how the chart has changed.
4. Return to the Diversity in Enrollment in Community Colleges PowerPoint file by clicking the file in the taskbar.
5. On Slide6, you should see the updated chart (see Figure 4.47).
6. If the chart has not changed; be sure that your chart is selected, click the Design tab in the Chart Tools section of the ribbon. Click the Refresh Data button. The change made in the Excel workbook is now reflected on the PowerPoint slide.
7. If that still doesn’t work, you may have created a “normal” link — instead of a Paste Link.  Delete the chart and follow the steps again.  Start from the beginning of this section.
8. Save your work.  You will submit both the Word and PowerPoint files, along with the Excel file, at the end of the next section.

Figure 4.47 shows the appearance of the column chart after the change was made in the Enrollment Statistics worksheet in the Excel file. Note that the Data Chart at the bottom reflects the new number, too. The change that was made in the Excel file will appear in the PowerPoint file after clicking the Refresh Data button.

Figure 4.47 Styled and Updated Chart.

In this section we will take a look at each of the worksheets created in the previous sections. Since these worksheets contain a combination of data and charts, there are specific things to watch for if you will be printing the sheets.

We will start by looking at each worksheet in Print Preview in Backstage View.  We will then make any changes necessary, such as changing the orientation and scaling, or moving charts around on the worksheet.  To make sure we don’t miss any worksheets, we are going to review the worksheets in the order they appear in the tabs.

PREVIEWING CHART SHEETS FOR PRINTING

Data file: Continue with CH4 Charting.

The first worksheet, Closing Prices, is a chart sheet. This means that it does not contain any data; remember that chart sheets just contain charts.  We still need to review it in Print Preview.

1. Click on the Closing Prices worksheet tab.
2. Go to Print Preview by clicking Print in Backstage View.
3. Notice that the chart will print on the entire page, in Landscape orientation.
4. There is nothing to change. Exit Backstage View.

PRINTING WORKSHEETS WITH DATA AND CHARTS

The next worksheet (Stock Trend) has a lot of data and multiple charts.  We need to print the data and the charts, which will require some modifications of the page setup.

1. Click on the Stock Trend worksheet tab.
2. Go to Print Preview by clicking Print in Backstage View.
3. Notice that this worksheet is currently printing on seven pages.
4. As you click through each page you should make the following observations:
   • The data is split between the first and third pages.
   • The line chart starts on the first page, but part of it is also on the second page.
   • The double-line chart starts on the third page, and then finishes on the fifth page.
   • The fourth and sixth pages are blank.
   • The last page has a column of seemingly random numbers.
5. Exit Backstage View.

The first thing we are going to do is remove the numbers that are appearing on page 7. To do this, we will hide the column where they are being stored.  We are going to hide the column, instead of deleting the numbers, in case the numbers are being utilized somewhere else in the workbook.

1. Scroll to the right on the worksheet until you find the numbers in column AH.
2. Click anywhere in column AH.
3. On the Home ribbon, click the Format button in the Cells group.
4. In the Visibility section, select Hide & Unhide then select Hide Columns (see Figure 4.64).
5. The visible column headings should now go from AG to AI.
6. Return to Print Preview in Backstage View to see the changes to the printed worksheet.
7. Notice that there are now five pages.  The data and charts are still splitting across multiple pages, but the numbers in column AH are no longer going to print.
8. Remain in Backstage View for the next steps.

Figure 4.64 Hide Columns in Format Menu

The data is still split between pages 2 and 3, and the charts are splitting oddly as well.  The first step we will try to fix these issues is to change the page orientation and scaling.

1. While still in Backstage View, change the page orientation to Landscape (use the Orientation drop-down menu in the Settings section).
2. This puts all of the data on one sheet, but the charts are still split between multiple pages.
3. Change the page scaling to Fit Sheet on One Page (use the Scaling drop-down menu in the Settings section).
4. This fits everything on one page, but it is too small to be able to read.
5. Change the page scaling back to No Scaling.

The next thing we will try is moving one, or both, of the charts. In order to move the charts, we need to exit out of Backstage View.

1. Exit Backstage View.
2. Switch to the View ribbon and then select Page Break Preview.  Your screen should look similar to Figure 4.65.  (Remember that the dotted blue lines indicate automatic page breaks.)
3. Move the 24 Month Comparison (double-line) chart closer to the top of its page.
4. Move the May 2014-2015 Trend for NASDAQ Sales Volume (line chart) so that it is under the 24 Month Comparison chart.
5. The link to the data source is still at the bottom of page 2 (in A50:A51) so you need to move it as well.  Using your preferred method, move the text from A50:A51 to M31:M32.
6. Now your screen should look similar to Figure 4.66.

Figure 4.65 Page Break Preview before moving the charts in Step 3

Figure 4.66 Page Break Preview after moving the charts and text

We don’t want the data source link text to print on its own page, but there is no room to move it onto the same page as the charts. To fix this, we are going to remove the automatic page break between the charts and the text in M31:M32.

1. Place your pointer on the horizontal blue dashed line (automatic page break) between the line chart and the Data Source link text.
2. When your pointer changes to the double arrow (pointing up and down), drag the page break down into the gray area. This removes the page break.
3. If your vertical automatic page break between columns L and M moves, drag it back between columns L and M. This will make it a solid blue line, which will no longer adjust automatically.
4. Your screen should now look like Figure 4.67.

Figure 4.67 Page Break Preview after removing a page break

Now you need to do one final check of this worksheet in Print Preview.

1. Go to Print Preview and look at both pages. Page 1 should contain just the data and page 2 should have both charts and the Data Source link text.
2. Exit Backstage View and save the file.

PREVIEW REMAINING WORKSHEETS FOR PRINTING

There are four remaining worksheets to be reviewed. Some of them will need minor changes and some will not need any changes.  You will need to preview each one and then make the specified changes.  In the following steps you will preview and modify the next three worksheets.

1. All Excel Classes – this is a chart sheet, so it should not need any changes.
2. Grade Distribution – the chart is split across two pages. Fix this by changing the orientation (Landscape) and scaling (Fit Sheet on One Page).
3. Enrollment by Race Chart – this is a chart sheet, so it should not need any changes.

PRINTING A CHART ONLY

Sometimes you might have a worksheet that has data and a chart, but you only want the chart to print.  That is the case with the Enrollment Statistics worksheet.

1. Switch to the Enrollment Statistics worksheet.
2. Select the chart.
3. Go to Print Preview.  Only the chart is printing.  (If it shows the data printing along with the chart, exit Backstage View and be sure to select just the chart on the worksheet.)
4. If needed, change the orientation to Landscape.  This orientation looks better when printing just a chart.
5. Exit Backstage View.

HIDING A WORKSHEET

You have actually decided that you do not want the Enrollment by Race Chart sheet to be visible at all, but you do not want to delete it. We are going to hide it from anyone looking at the workbook.

1. Right-click on the Enrollment by Race Chart tab.
2. Select Hide from the menu that appears.  The Enrollment by Race Chart sheet should no longer be visible.
3. Now you want to bring the worksheet back.  To unhide it, right-click on any other worksheet tab and select Unhide from the menu. A list of hidden worksheets will be displayed. Select the Enrollment by Race Chart and click OK.
4. Save the CH4 Charting workbook.
5. Compare your work (on all of the worksheets) with the self-check answer key (found in the Course Files link).
6. Submit all three files from this chapter (CH4 Charting.xlsx, CH4 Diversity in Enrollment in Community Colleges.docx, and CH4 Diversity in Enrollment in Community Colleges.pptx) as directed by your instructor.

Learning Objectives

• Filter table data.
• Add a total row to a table.
• Insert subtotals into a table.

FILTERING DATA

When you first create an Excel table, filter arrows appear in all the column headings. We have seen that you can use those arrows to sort your data by a single column. You can also use these same arrows to filter or limit the data you see by narrowing the displayed data within a column. There are many ways to filter data within a column depending on whether the data in the column is text or numeric. Table 5.5 gives you some filter examples:

Table 5.5 Filter Examples

Text Filters
Desired Results	Filter Column	Text Filter	Checkbox Selected
Data for the State of New Jersey (NJ)	State	Equals NJ	NJ
Data for Books that Have Gardening in Their Title	Title	Contains Gardening
Data for Weather on the Weekend	Day	Equals Saturday OR equals Sunday	Saturday and Sunday

 

Numeric Filters
Desired Results	Filter Column	Number Filter	Checkbox Selected
Data for Income Greater Than $1,000	Income	Greater than 1,000
Data for Amount Paid Equal to Zero	Amount Paid	Equals 0.00	0.00
Data for Mortgage and Auto Loans	Loan Type	Equals Mortgage OR equals Auto	Mortgage and Auto

Notice there are sometimes more than one way to filter data (i.e. – with a filter choice or a checked box). There are also single criteria filters, as well as, multi-criteria filters. We will explore all of these next.

To start filtering, let’s look at just the first week of data in the Weekly OR sheet:

1. Click on the Weekly OR sheet and click on a cell in the table.
2. Click the filter arrow to the right of the Week heading.
3. Click the Select All checkbox to deselect all of the checkbox choices.
4. Click on 1 to select Week 1.
5. Click OK.

Your table should look like Figure 5.16. You should see only 7 rows of Week 1 data in your table. Notice in your Status Bar at the bottom of your screen the message “7 of 31 records found”. Also notice that the filter arrow in the Week heading has changed to a funnel which indicates that this column is currently filtered.

Figure 5.16 Filter

 To remove your filter:

1. Click the funnel next to the Week heading.
2. Select “Clear filter from Week”.

Skill Refresher

Filter a Column

1. Click the filter arrow to the right of the heading in the column you want to filter.
2. Click the Select All checkbox to deselect all of the checkbox choices.
3. Click on the checkboxes you want to filter by.
4. Click OK.

Un-Filter a Column

1. Click the funnel to the right of the heading in the column you filtered.
2. Select Clear filter.

 Now let’s try a numeric filter. We want to find days in Portland ME when it’s warmer than 32 degrees in January:

1. Click in the Portland ME sheet, then click on a cell in the table.
2. Click on the filter arrow next to the High heading.
3. Click on Number filters, then select Greater than. The Custom AutoFilter dialog box will appear on your screen.
4. Enter 32 in the space to the right of “is Greater than”. Your Custom AutoFilter dialog box should now match Figure 5.17.
   

   Figure 5.17 AutoFilter Dialog Box

5. Click OK.

You should see that it was only above 32 degrees three days in January in Maine – the first three! Check your table against Figure 5.18.

Figure 5.18 Filter Results

Let’s review sorting and filtering in the following steps:

1. Click on the Weekly OR sheet and clear the Day column filter.
2. Sort the table by Week (smallest to largest).
3. Filter the table to only show Mondays.
4. Compare your table results to Figure 5.19.
   

   Figure 5.19 Filter Results

FILTERING USING THE SLICER

Beginning in Excel 2013, slicers were added to the software as another way to filter your table data. A slicer is really useful because it clearly indicates what data is shown in your table after you filter your data.

Let’s try using the Slicer to filter our Portland OR data table:

1. Click on the Portland OR sheet and click in the table.
2. In the ribbon’s Table Tools Design tab, click Insert Slicer.
3. Click on Day in the Insert Slicers dialog box, and then click OK.
4. Drag the slicer so that the upper left-hand corner lines up with the top corner of cell G5.
5. Notice that when you insert a Slicer, a Slicer Options tab appears on the ribbon. This tab lets you change the style and size of the entire slicer or the individual slicer buttons.
6. Click on the Slicer options tab, then click on the More button next to Slicer Styles. The choices in Figure 5.20 will show on your screen.

Figure 5.20 Slicer Styles

1. Select the first choice under Dark.
2. In the Size group on the Slicer Options ribbon (NOT the Buttons group), change the width to 1”.
3. Click in the table and scroll down to Day 15 and click the 15 button to show only the data for January 15th in the table.
4. Hold down the CTRL key and click on the Slicer buttons for Days 10 through 14. Your table should now show the data from Days 10-15.
5. Sort the Day column in Ascending order to show the days in order as in Figure 5.21.

Figure 5.21 Slicer Results

TOTAL ROWS

By adding a total row to the bottom of your table, you can quickly see summary data for one or more of the columns in your table. Total rows can be added to tables as a whole, or those that are filtered. Total rows can easily be toggled on and off as the need for summary data arises.

1. Click on the Portland ME sheet and clear the filter from the High column.
2. Click on the Total Row check box in the Table Style Options group in the Table Tools Design tab in the ribbon.
3. Scroll to the bottom of your table to the Total Row. Notice the total for the Snow data.
4. Click on D37 (in the Rain column), and then click the down-arrow that appears to the right of the cell.
5. Choose Sum to add a sum to the Total Row in the Rain column.
6. To see the Average rainfall for the month of January, click on the arrow again and choose Average.
7. Repeat this step in E37 to see the Average snowfall.
8. Use the Decrease Decimal button in the Home tab of the ribbon to change the decimal places in D37 and E37 to 2. Compare your Total Row to Figure 5.22.

Figure 5.22 Total Row

1. Now switch to the Weekly OR sheet and see if you can successfully add a Slicer and Total Row to this table:
2. Clear the filter from the Day column.
3. Add a Slicer for the Day column to the sheet.
4. Move the top left corner of the slicer to H5. Resize it as needed and choose a Slicer Style.
5. Select Monday through Friday in the Slicer so that Saturday and Sunday data do NOT show in your table.
6. Add a Total Row that averages the High and Low columns. Your averages should be High: 47.0 and Low: 35.8. Change the label “Total” to “Average” by clicking A37 and typing Average.

Skill Refresher

Add a Total Row

1. Click on the Total Row check box in the Table Style Options group in the Table Tools Design tab in the ribbon.
2. Scroll to the bottom of your table to find the Total Row.
3. Click in one of the columns in the Total Row, and then click the down-arrow that appears to the right of the cell.
4. Choose Sum to add a sum to the Total Row in the column.
5. To see the Average for column, click on the arrow again and choose Average.

Some other choices in the Total Row are Count (for words), Count Numbers, Max, and Min.

 

 

SUBTOTALING

You can automatically calculate subtotals and grand totals in a table for a column. This is a powerful tool that allows you to quickly display multiple levels of summary data within your table. This can provide Management with a report of higher level summary data one minute, and then can be easily switched back to detailed data the next minute. It is important to save often during this process and follow the steps carefully. It is recommended that you make a copy of the data you want to subtotal and place it in a new sheet, so that you can save the summary subtotaled data separately if desired.

In order to subtotal successfully, you always need to do the following in order:

1. Sort by the column you want to subtotal on.
2. Convert the table back to a normal Excel range. You cannot subtotal inside a table.
3. Subtotal in the Data tab in the ribbon.
4. If you want to limit your displayed data further, Filter in the Data tab in the ribbon.

We want to find out what the weather looks like for each day of the week, so we’ll need to save our data to a new sheet, sort by the days of the week, and then convert the table in order to get ready to see the subtotal.

1. Click on the Weekly OR sheet.
2. Point at the Weekly OR sheet tab at the bottom of the screen, hold the CTRL key down, and left-drag the sheet to the right until you are past all the existing sheets.
3. When you see a sheet icon with a + sign, let go of the mouse button and then the CTRL key. A Weekly OR (2) sheet will appear.
4. Right-click on the new Sheet tab, select Rename, type Subtotal OR, and then press ENTER.
5. Save your file before you start Subtotaling!
6. Remove all filters in the table by clicking the Data tab and then choosing Clear.
7. Now we want to Sort the table by the Day column using a Custom Sort in the Sort button in the ribbon to sort in the order Sunday, Monday, Tuesday, etc. (See Figure 5.13 through 5.15 for a review of Custom Sorting.)
8. Before you can subtotal, you must convert your table back to a regular range. To do this, click Convert to Range in the Table Tools Design tab on the ribbon. (See Figure 5.23)
   

   Figure 5.23 Convert to Range

9. When asked if you want to convert the table, click Yes.
10. Because your data is no longer formatted as a table, your slicer will disappear; and you will no longer have access to the Table Tools Design tab in the ribbon.
11. Under the Data tab in the ribbon, click Subtotal.
12. In the Subtotal Window, make the choices shown in the Figure 5.24. It is essential that you select the column you sorted by in the “At each change in” field at the top of the window. Click OK.

Figure 5.24 Subtotal Window

Your data should look like Figure 5.25. Successful subtotaling shows only one subtotal for each group in the column you sorted by. (HINT: If you end up with more than one Subtotal for the same group (i.e – one of the days of the week in our example), you did not sort before subtotaling. Remove your subtotals using the Remove All button in Figure 5.24, sort your table, and then try subtotaling again.)

Figure 5.25 Subtotal Results

Notice the three Outline buttons circled in the upper-left corner of the spreadsheet. These allow you to control the amount of subtotaled data that is displayed. Table 5.5 describes the different Outline buttons.

Table 5.5 Subtotal Outline Buttons

Let’s try the three Outline buttons to see the difference in the data displayed:

1. Click on the 1 Outline button in the upper left-hand corner of the sheet.
2. You should see only the Grand Average row with averages for High, Low, Rain, and Snow.
3. Click on the 2 Outline button.
4. Now you’ll see the average for each day of the week along with the Grand Average.
5. Click on the + Sign button to the left of the Sunday Average row.
6. This expands just the Sunday Day data and displays the individual records for this subset of the data. Clicking on + Sign buttons will expand a portion of the data at a time. Clicking on – Sign buttons hide a portion of the data at a time.
7. Click on the 3 Outline button.
8. All the individual records along with the subtotals, and Grand Average should be displayed.
9. Save your Excel file.

TABLES FOR A RETAIL COMPANY

Download Data File: SC5 Data

Figure 5.31 Completed Exercise

Retail companies with today’s online, as well as, in-store sales have a lot of data to keep track of! Keeping track of sales, costs, and profits on a daily basis is essential to making the most of a business. This exercise illustrates how to use the skills presented in this chapter to generate the data needed on a daily basis by a retail company.  See Figure 5.31 above.

1. Open the data file SC5 Data and save the file to your computer as SC5 Dynamite Customer Sales.
2. Click on the Sales sheet. In I4 , enter a VLOOKUP function that will find the Product Price for the Product in E4 in the table in the Product Table sheet and return it to I4. In your VLOOKUP function, fill in the required parameters using Figure 5.32 below. Copy the VLOOKUP function down column I.
   

   Figure 5.32 VLOOKUP window

3. In J4 , enter a VLOOKUP function that will find the Product Cost for the Product in E4 in the table in the Product Table sheet and return it to J4. This VLOOKUP function will be the same as the VLOOKUP function in I4 – EXCEPT THE COL_INDEX_NUM will be 3 instead of 4. Copy the function down column J.
4. In K4, calculate Profit (Product Price – Product Cost). Copy this formula down column K.
5. Format columns I, J, and K as currency with two decimal places.
6. Click in cell A3. Insert a table with headers for the range A3:K52. BE CAREFUL HERE: Excel will try to insert a table starting with A2. You want to make sure your range starts with A3 here.
7. Make a copy of the Sales sheet and rename it Online Sales by Date. Place this sheet to the right of the Sales sheet. Filter out Retail in Sales Type, so that only Online Sales are displayed. Sort the filtered data by Date Sold (oldest to newest).
8. Make a copy of the Sales sheet and rename it June Sales by Country. Place this new sheet to the right of the Online Sales by Date sheet. Filter this sheet to only show June dates by using the Date Filter Between. Sort this sheet alphabetically (A to Z) by Country and then alphabetically by Name.
9. Make another copy of the Sales sheet and rename it Sales by Product. Place this new sheet to the right of the June Sales by Country sheet. Hide the Region column.
10. Insert a slicer in the Sales by Product sheet for Product Sold. Move the top left corner of the slicer to the top left-hand corner of cell M1. Resize the height of the entire slicer to 2.09”.
11. Select both DETA100 and DETA200 in the slicer. Sort the filtered sheet by Product Sold. Add a Total Row that includes the overall average for the Product Price, Product Cost, and Profit columns. Change the heading in A53 to Average.
12. Make a copy of the Sales sheet and rename it Subtotals by Date. Place this new sheet to the right of the Sales by Product sheet. Subtotal the sheet by Date (Oldest to Newest), summing the Profit column. Click the 2 Outline button to show just the subtotals by date and the grand total.
13. Make one final copy of the Sales sheet and rename it Subtotals by Type. Place this new sheet to the right of the Subtotals by Date sheet. Subtotal the sheet by Sales Type, summing the Profit column.
14. Add a 2nd subtotal to the Subtotals by Type sheet that subtotals by Type and averages the Profit column. (Hint: uncheck Replace Current Subtotals in the Subtotal dialog box.) Notice that 4 Outline buttons appear with the 2nd subtotal. Figure out which Outline button to click to display both subtotals for Online and Retail and two Grand Totals.
15. For each worksheet, add a footer with the worksheet name in the center.
16. Preview each worksheet in Print Preview and make any necessary changes for professional printing. (Hint: Orientation, page scaling, and print titles might need to be used)
17. Double-check that your sheets are in the following order from left to right:  Sales,  Online Sales by Date, June Sales by Country, Sales by Product, Subtotals by Date, Subtotals by Sales Type, and Product Table.
18. Save the SC5 Dynamite Customer Sales workbook.
19. Submit the SC5 Dynamite Customer Sales workbook as directed by your instructor.

A template is a predefined pattern for a spreadsheet that has already been created for you. Hundreds of templates, already created by Microsoft, are available for you to use inside Excel. These templates are very helpful if you have limited time to get a new task done in Excel, and you don’t know where to start. Templates do a lot of the work for you! Templates include all the formulas, formatting, etc. needed in a professional Excel spreadsheet. All that’s left to do is enter the data. Predefined Microsoft templates include everything from billing statements to blood pressure trackers to business cards. Categories include: Business, Personal, Industry, Financial Management, Logs, Calculators, and Lists.

Sometimes you need a very specific template that hasn’t already been created by Microsoft. Taking the time to create your own template will allow you to use this spreadsheet pattern to create files from it over and over again. If you need to create a new version of a spreadsheet on a regular basis, templates will make this work much easier. In this chapter we will explore using existing Microsoft templates, as well as, creating our own templates.

Let’s start by trying out a predefined, Microsoft template:

1. Click the File tab in the ribbon.
2. Click New in the Backstage View.
3. Click in the Search box for Online template.
4. Type Travel and press ENTER.
5. Click on the Travel Expense Report and click Create.  NOTE:  If this template is not available, ask your instructor which template you should choose.

Your screen should look like Figure 6.9 below. Notice the design, layout, and formulas have already been set up for you.

Figure 6.9 Travel Expenses Report Template

Try using the template by doing the following:

1. Change the Name to your name.
2. Change the Department to CAS.
3. Press CTRL+~ to see where the formulas are in the sheet.  Working in the formula view helps you see where the formulas are, so you won’t delete them.
4. In formula view, carefully delete just the data. Don’t delete any formulas!
5. Press CTRL+~ again to return to Normal view.
6. Enter dates and expenses for a trip of your imagining in the first three rows under the column headings.
7. Save the completed file as CH6 Travel Expenses. Close the file.

Now let’s shift to creating our own template. Sometimes you create a blank template first and then create spreadsheets from it. Other times, you have an existing spreadsheet that you realize you need a template for, so that you can recreate the file with new data over and over again. We’ll turn our existing CH6 Personal Budget file into a template now, and in the assignments at the end of the chapter, we’ll start templates from scratch before a filled-in spreadsheet is created.

1. Open your CH6 Personal Budget.xlsx file.
2. Group the month sheets (January through December).
3. Press CTRL+~ to switch to Formula view.
4. We only want to delete data from these sheets – not labels or formulas. The only data is in C5:C13, F5:F8, and in the Notes in H11:J13.
5. Highlight C5:C13 (with all the sheets still grouped) and press DELETE.
6. Highlight F5:F8 (with all the sheets still grouped) and press DELETE.
7. Highlight H11:J13 (with all the sheets still grouped) and press DELETE.
8. Press CTRL+~ to switch back to Normal view.
9. Ungroup the sheets. Look through the sheets to check that only the data has been deleted. Notice the error message #DIV/O appears in I6:I7 since the data for this formula has been deleted. Your January sheet should look like Figure 6.10.

Figure 6.10 January Template Sheet

NOTE: There are only formulas and the pie chart in the Expenses Summary sheet, so nothing needs to be deleted from this sheet to setup your template.

1. Click the File tab in the ribbon and then click Save As.
2. Choose the location where you want to save the file.
3. In the Save as type pull-down list, select Excel Template (*.xltx).
4. At the top of the screen, double-check that the location you want to save the file to has not changed. If it has, use the pull-down list to find the location where you want to save your file. BE CAREFUL HERE! By default, Excel will try to save this to a default template file location on your hard drive.
5. Type in the file name CH6 Personal Budget Template.xltx. Check your screen carefully with Figure 6.11.  Keep in mind that you may be saving your template file to a different place on your computer.  By default, Excel will save the template to your hard drive assuming you always work on the same computer.
   

   Figure 6.11 Save As Template

6. Click Save.

We are now going to use our new budget template to start a Personal Budget file for 2017. We want to use the Ch6 Personal Budget Template to create the new file, but we don’t want to overwrite the template. We want to keep it clean to use to start each new year’s file. To do this, we’ll save the file to our new 2017 file name before we start filling in any data.

1. With the CH6 Personal Budget Template open, click the File tab in the ribbon.
2. Choose Save As and choose the location where you want to save the 2017 version of the file.
3. Change the Save as Type back to Excel Workbook (*.xlsx).
4. Enter the File name CH6 2017 Personal Budget. Compare your screen to Figure 6.12.
   

   Figure 6.12 Save As 2017 Budget File

5. Click Save.
6. Group all the sheets together including the Expenses Summary sheet.
7. Click on H1. Type 2017 and press ENTER.
8. Ungroup the sheets.
9. Click on the January sheet. Enter the following data in Figure 6.13:
   

   Figure 6.13 January 2017 Data

10. Click on the Expenses Summary sheet – the data and the pie chart should show the January data since that is all the data in the twelve month sheets for now.Your sheet should look like Figure 6.14.Figure 6.14 Expense Summary Sheet
11. Save your file.