Learning Objectives

• Learn how to create basic formulas.
• Understand relative referencing when copying and pasting formulas.
• Work with complex formulas by controlling the order of mathematical operations.
• Understand formula auditing tools.

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

Open the Data File

Download Data File: CH2 Data

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

Figure 2.1 shows the completed workbook that will be demonstrated in this chapter. Notice that this workbook contains four worksheets. The first worksheet, Budget Summary, serves as an overview of the data that was entered and calculated in the second and third worksheets, Budget Detail and Loan Payments. The second worksheet, Budget Detail, provides a detailed list of all the expenses and the third worksheet, Loan Payments, provides information regarding car payment and mortgage payment amounts.  The last worksheet, Prepare to Print, has data that is unrelated to the budget worksheets but will be used in Section 2.4 – Preparing to Print.

Creating a Basic Formula

When formulas and cell references are used Excel will automatically recalculate when data is changed

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

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

Table 2.1 Spend Category Definitions

Category	Definition
Utilities	Electricity, heat, water, home phone, cable, Internet access
Cell Phone	Cell phone plan and equipment charges
Food	Groceries
Gas	Cost of gas for vehicle
Clothes	Clothes, shoes, and accessories
Insurance	Renter, homeowner, and/or car insurance
Entertainment	Activities like dining out, movie and theater tickets, parties, and so on
Vacation	Vacation expenses
Miscellaneous	Any other spending categories

The amount of money spent each month for each category, as well as the amount of money spent last year, is already entered into the worksheet. We will write formulas that will calculate the annual (yearly) amount spent, the percent of the total spent each category represents, as well as the percent change from last year’s spending to the current year.

The first formula will calculate the Annual Spend values. The formula will be constructed so that it takes the values in the Monthly Spend column and multiplies them by 12 (the number of months in a year). This will show how much money will be spent per year for each of the categories listed in Column A.  Since the first category is Utilities, we will start by creating the formula to multiply the Monthly Spend amount in B3 by 12. This formula will be created in D3 – the Annual Spend cell for the Utilities category.  This formula will be written as: =B3*12

1. Switch to the Budget Detail worksheet if needed.  Click cell D3.Formulas always start with the equal sign. This signifies to Excel that the contents of the cell should be calculated, not just displayed as basic text or numbers.
2. Type an equal sign =
   When the first character entered into a cell is an equal sign, it signals Excel to perform a calculation.
3. Type B3. This adds B3 to the formula, which is now a cell reference. Excel will use whatever value is entered into cell B3 in the calculation.
4. Type the * . This is the symbol for multiplication in Excel. As shown in Table 2.2 the mathematical operators in Excel are slightly different from those found on a typical calculator.
5. Type the number 12. This multiplies the value in cell B3 by 12. In this formula, a number, or constant, is used instead of a cell reference because it will not change. In other words, there will always be 12 months in a year.
6. Press the ENTER key. This enters the formula into the cell.

Table 2.2 Excel Mathematical Operators (move up)

Symbol	Operation
+	Addition
−	Subtraction
/	Division
*	Multiplication
^	Power/Exponent

Why?

Use Cell References

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

Use Universal Constants

There will be times when you are writing formulas that you will need to use universal constants, or numbers that do not change, such as the number of days in a week, weeks or months in a year, and so on.  For example, if you are calculating the monthly cost of an item when you know the yearly cost, you will always divide by 12 since there are 12 months in a year.  In this case, you use the constant of 12 instead of a cell reference because the number of months in a year never changes.

Figure 2.3 shows how the formula appears in cell D3 before you press the ENTER key. Figure 2.4 shows the result of the formula after you press the ENTER key, as well as the formula bar which displays the formula as it was entered in the cell.

The Annual Spend for Utilities is $3,000 because the formula is taking the Monthly Spend in cell B3 and multiplying it by 12. If the value in cell B3 is changed, the formula automatically produces a new result.

 

Relative References (Copying and Pasting Formulas)

Once a formula is typed into a worksheet, it can be copied and pasted to other cell locations. For example, in cell D3 we have calculated the annual spend for the Utilities category, but this calculation needs to be performed for the rest of the cell locations in Column D. Since we used the B3 cell reference in the formula, Excel automatically adjusts that cell reference when the formula is copied and pasted into the rest of the cell locations in the column. This is called relative referencing and is demonstrated as follows:

1. Click cell D3.
2. Place the mouse pointer over the Auto Fill Handle in the bottom right corner of the cell.
3. When the mouse pointer turns from a white block plus sign to a black plus sign, click and drag down to cell D11. This pastes the formula into the range D4:D11.
4. Double click cell D6. Notice that the cell reference in the formula is automatically changed to B6.
5. Press the ENTER key.

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

 

Why?

Use Relative Referencing

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

Creating Complex Formulas (Controlling the Order of Operations)

The next formula to be added to the Personal Budget workbook is the percent change over last year (Column F). This formula determines the difference between this year’s Annual Spend values (Column D) and the values in the Last Year Spend column (Column E) and shows the difference in terms of a percentage. This requires that the order of mathematical operations be controlled to get an accurate result.

Excel uses the standard mathematical order of operations, as defined in Table 2.3.  When writing complex formulas it is important to remember this order of operations. You want to be sure that your formulas will calculate in the order you intend.  To help you remember which operations will be performed first, you can use the acronym PEMDAS.

P – parentheses
E – exponents
MD – multiplication and division
AS – addition and subtraction

Table 2.3 shows the standard order of operations (PEMDAS) for a typical formula. To change the order of operations shown in the table, you can use parentheses to process certain mathematical calculations first.

Table 2.3 Standard Order of Mathematical Operations (PEMDAS)

Symbol	Order
( )	Any calculation inside parentheses will be done first. If there are layers of parentheses used in a formula, Excel computes the innermost parentheses first and the outermost parentheses last.
^	Excel executes any exponential computations next.
* or /	Excel performs any multiplication or division computations next. When there are multiple instances of these computations in a formula, they are executed in order from left to right.
+ or −	Excel performs any addition or subtraction computations last. When there are multiple instances of these computations in a formula, they are executed in order from left to right.

To create the Percent Change formula, we will need to use parentheses to control the order of the calculations. We need the difference of the two values to be found before the division is done, so we will use parentheses around the subtraction portion of the formula to indicate that calculation needs to be done first. This formula is added to the worksheet as follows:

1. Click cell F3 in the Budget Detail worksheet.
2. Type an equal sign =.
3. Type an open parenthesis (.
4. Click cell D3. This will add a cell reference to cell D3 to the formula. When building formulas, you can click cell locations instead of typing them.
5. Type a minus sign −.
6. Click cell E3 to add this cell reference to the formula.
7. Type a closing parenthesis ).
8. Type the slash / symbol for division.
9. Click cell E3. This completes the formula that will calculate the percent change of last year’s actual spent dollars vs. this year’s budgeted spend dollars (see Figure 2.6).
10. Press the ENTER key.
11. Click cell F3 to activate it.
12. Place the mouse pointer over the Auto Fill Handle.
13. When the mouse pointer turns from a white block plus sign to a black plus sign, click and drag down to cell F11. This pastes the formula into the range F4:F11.

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

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

Integrity Check<

Does the Output of Your Formula Make Sense?

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

Skill Refresher

Formulas

1. Type an equal sign =.
2. Click or type a cell location. If using constants, type a number.
3. Type a mathematical operator.
4. Click or type a cell location. If using constants, type a number.
5. Use parentheses where necessary to control the order of operations.
6. Press the ENTER key.

Auditing Formulas

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

1. With the Budget Detail worksheet open, click the Formulas tab of the Ribbon.
2. Click the Show Formulas button in the Formula Auditing group of commands. This displays the formulas in the worksheet instead of showing the mathematical outputs.
3. Click the Show Formulas button again. The worksheet returns to showing the output of the formulas.

You can also toggle Show Formulas on and off using the keyboard.  Hold down the CTRL key while pressing the ` key.

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

Skill Refresher

Show Formulas

1. Click the Formulas tab on the Ribbon.
2. Click the Show Formulas button in the Formula Auditing group of commands.
3. Click the Show Formulas button again to show formula outputs.

Keyboard Shortcuts

Show Formulas

• Hold down the CTRL key while pressing the accent symbol `.  Same for Excel for Mac.

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

1. Click cell D3.
2. Click the Trace Dependents button in the Formula Auditing group of commands in the Formulas tab of the Ribbon. A blue arrow appears, pointing to cell F3 (see Figure 2.9). This indicates that cell D3 is referenced in a formula entered in cell F3.
3. Click the Remove Arrows command in the Formula Auditing group of commands in the Formulas tab of the Ribbon. This removes the Trace Dependents arrow.
4. Click cell F3.
5. Click the Trace Precedents button in the Formula Auditing group of commands in the Formulas tab of the Ribbon. A blue arrow with dots in cells D3 and E3, and pointing to cell F3 appears (see Figure 2.10). This indicates that cells D3 and E3 are references in a formula entered in cell F3.
6. Click the Remove Arrows command in the Formula Auditing group of commands in the Formulas tab of the Ribbon. This removes the Trace Precedents arrow.
7. Save the CH2 Personal Budget file.

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

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

Skill Refresher

Trace Dependents

1. Click a cell location that contains a number or formula.
2. Click the Formulas tab on the Ribbon.
3. Click the Trace Dependents button in the Formula Auditing group of commands.
4. Use the arrow(s) to determine where the cell is referenced in formulas and functions.
5. Click the Remove Arrows button to remove the arrows from the worksheet.

Trace Precedents

1. Click a cell location that contains a formula or function.
2. Click the Formulas tab on the Ribbon.
3. Click the Trace Precedents button in the Formula Auditing group of commands.
4. Use the dot(s) along the line to determine what cells are referenced in the formula or function.
5. Click the Remove Arrows button to remove the line with the dots.

Key Takeaways

• Mathematical computations are conducted through formulas and functions.
• An equal sign = precedes all formulas and functions.
• Formulas and functions must be created with cell references to conduct what-if scenarios where mathematical outputs are recalculated when one or more inputs are changed.
• Mathematical operators on a typical calculator are different from those used in Excel. Table 2.2 “Excel Mathematical Operators” lists Excel mathematical operators.
• When using numerical values in formulas and functions, only use universal constants that do not change, such as days in a week, months in a year, and so on.
• Relative referencing automatically adjusts the cell references in formulas and functions when they are pasted into new locations on a worksheet. This eliminates the need to retype formulas and functions when they are needed in multiple rows or columns on a worksheet.
• Parentheses must be used to control the order of operations when necessary for complex formulas.
• Formula auditing tools such as Trace Dependents, Trace Precedents, and Show Formulas should be used to check the integrity of formulas that have been entered into a worksheet.

Attribution

Adapted by Mary Schatz from How to Use Microsoft Excel: The Careers in Practice Series, adapted by The Saylor Foundation without attribution as requested by the work’s original creator or licensee, and licensed under CC BY-NC-SA 3.0.

Learning Objectives

1. Use the SUM function to calculate totals.
2. Use the COUNT function to count cell locations with numerical values.
3. Use the AVERAGE function to calculate the arithmetic mean.
4. Use the MAX and MIN functions to find the highest and lowest values in a range of cells.
5. Learn how to copy and paste formulas without formats applied to a cell location.
6. Use absolute references to calculate percent of totals.
7. Learn how to set a multiple level sort sequence for data sets that have duplicate values or outputs.

In addition to formulas, another way to conduct mathematical computations in Excel is through functions. Excel functions apply a mathematical process to a group of cells in a worksheet. For example, the SUM function is used to add the values contained in a range of cells. Functions are more efficient than formulas when you are applying a mathematical process to a group of cells. If you use a formula to add the values in a range of cells, you would have to add each cell location to the formula one at a time. This can be very time-consuming if you have to add the values in a few hundred cell locations. However, when you use a function, you can highlight all the cells that contain values you wish to sum in just one step.

The components of a function are as follows:

=FunctionName(Arguments)

Functions are a type of formula, therefore they start with an equal sign.  The next component is the name of the function. A list of commonly used functions is shown in Table 2.4.  After the function name comes the arguments for the function, which are always enclosed in parentheses.  The arguments are the cell locations and/or values that will be used in the function.  The number and type of arguments varies based on the the function being used, although in this section we will only work with a range of cells for the function arguments.   Some examples of different functions with their arguments are:

=SUM(B2:B15) – adds the values in B2 through B15
=SQRT(A5) – finds the square root of the value in A5
=COUNTA(A1:A20) – finds the number of cells from A1 through A20 that contain text or a number

Throughout Section 2.2 we will add a variety of mathematical functions to the Personal Budget workbook. In addition to creating functions, this section also reviews percent of total calculations and the use of absolute references.

Table 2.4 Commonly Used Functions

Function	Output
ABS	The absolute value of a number
AVERAGE	The average or arithmetic mean for a group of numbers
COUNT	The number of cell locations in a range that contain a numeric value
COUNTA	The number of cell locations in a range that contain text or a numeric value
MAX	The highest numeric value in a group of numbers
MEDIAN	The middle number in a group of numbers (half the numbers in the group are higher than the median and half the numbers in the group are lower than the median)
MIN	The lowest numeric value in a group of numbers
MODE	The number that appears most frequently in a group of numbers
PRODUCT	The result of multiplying all the values in a range of cell locations
SQRT	The positive square root of a number
SUM	The total of all numeric values in a group

It is important to note that there are several methods for adding a function to a worksheet, and we will explore each of them throughout this section.

• Typing the function directly into a cell
• Selecting from the function list
• Using the Function Library on the ribbon
• Using the Insert Function button

The SUM Function

The SUM function is used when you need to calculate totals for a range of cells or a group of selected cells on a worksheet. With regard to the Budget Detail worksheet, we will use the SUM function to calculate the totals in row 12, starting with the Monthly Spend total in B12. The following illustrates how a function can be added to a worksheet by typing it into a cell location:

1. Switch to the Budget Detail worksheet if needed.
2. Click cell B12.
3. Type an equal sign =.
4. Type the function name SUM.
5. Type an open parenthesis (.
6. Click cell B3 and drag down to cell B11. This places the range B3:B11 into the function.
7. Type a closing parenthesis ).
8. Press the ENTER key. The function calculates the total for the Monthly Spend column, which is $1,427.

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

As shown in Figure 2.11, the SUM function was added to cell B12. However, this function is also needed to calculate the totals in the Annual Spend and Last Year Spend columns. The function can be copied and pasted into these cell locations because of relative referencing. Relative referencing serves the same purpose for functions as it does for formulas.  To complete the Totals in row 12, we need to copy and paste the SUM function into D12 and E12.  Since we will then have totals in D12 and E12, we can paste the percent change formula into F12.

1. Click cell B12 in the Budget Detail worksheet.
2. Click the Copy button in the Home tab of the Ribbon.
3. Highlight cells D12 and E12.
4. Click the Paste button in the Home tab of the Ribbon. This pastes the SUM function into cells D12 and E12 and calculates the totals for these columns.
5. Click cell F11.
6. Click the Copy button in the Home tab of the Ribbon.
7. Click cell F12, then click the Paste button in the Home tab of the Ribbon.

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

Integrity Check

Cell Ranges in Functions

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

 The COUNT Function

Data file: Continue with CH2 Personal Budget.

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

1. Click cell D13.
2. Type an equal sign =.
3. Type the letter C (to start spelling the name of the function).
4. Click the down arrow on the scroll bar of the function list (see Figure 2.14) and find the word COUNT.
    Mac Users can scroll down with touchpad or mouse to find COUNT
5. Double click the word COUNT from the function list.
    Mac Users should single click the word “COUNT” do not double-click
6. Highlight the range D3:D11.
7. You can type a closing parenthesis ) and then press the ENTER key, or simply press the ENTER key and Excel will close the function for you. The function produces an output of 9 since there are 9 items planned on the worksheet.

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

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

The AVERAGE Function

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

1. Click cell D14 in the Budget Detail worksheet.
2. Click the Formulas tab on the Ribbon.
3. Click the More Functions button in the Function Library group of commands.
4. Place the mouse pointer over the Statistical option from the drop-down list of options.
5. Click the AVERAGE function name from the list of functions that appear in the menu (see Figure 2.16). This opens the Function Arguments dialog box.
6. Click the Collapse Dialog button in the Function Arguments dialog box (see Figure 2.17).
    For Mac Users, the Collapse Dialog button may not collapse. Just continue with Step 7 and press Enter after selecting the range.
7. Highlight the range D3:D11.
8. Click the Expand Dialog button in the Function Arguments dialog box (see Figure 2.18). You can also press the ENTER key to get the same result.
9. Click the OK button on the Function Arguments dialog box. This adds the AVERAGE function to the worksheet.
    Mac Users should click the DONE button

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

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

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

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

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

The MAX and MIN Functions

Data file: Continue with CH2 Personal Budget.

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

1. Click cell D15 in the Budget Detail worksheet.
2. Click the Insert Function button on the Formulas ribbon. (see Figure 2.21)
3. This brings up the Insert Function dialog box.  Type the word MIN in the search box and then click the Go button. (see Figure 2.22)
4. Double-click MIN in the list. This opens the Function Arguments dialog box.
5. Click the Collapse Dialog button in the Function Arguments dialog box.
6. Highlight the range D3:D11.
7. Click the Expand Dialog button in the Function Arguments dialog box.
8. Click the OK button on the Function Arguments dialog box. This adds the MIN function to the worksheet. (see Figure 2.23)
9. Click cell D16.
10. Repeat steps 2-8 (using MAX instead of MIN) to add the MAX function to the worksheet. (see Figure 2.24)

Skill Refresher

Typing a function or selecting from the function list

1. Type an equal sign =.
2. Type the function name followed by an open parenthesis ( or double click the function name from the function list.
3. Highlight the range of cells to use or click individual cell locations followed by commas.
4. Type a closing parenthesis ) and press the ENTER key or press the ENTER key to close the function.

Inserting a function using the ribbon

1. On the Formulas ribbon, select the correct category in the Function Library.   Click the desired function in the list.
2. In the Function Dialog box, click the Collapse Dialog button and highlight the range of cells to use.
3. Click the Expand Dialog button and then click the OK button in the Function Arguments dialog box.

Inserting (and searching for) a function using the Insert Function button

1. On the Formulas ribbon, click the Insert Function button and search for the function to use.  Double-click on the desired function in the list.
2. In the Function Dialog box, click the Collapse Dialog button and highlight the range of cells to use.
3. Click the Expand Dialog button and then click the OK button in the Function Arguments dialog box.

Copy and Paste Formulas (Pasting without Formats)

Data file: Continue with CH2 Personal Budget.

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

1. Highlight the range D14:D16 in the Budget Detail worksheet.
2. Click the Copy button in the Home tab of the Ribbon.
3. Click cell E14.
4. Click the down arrow below the Paste button in the Home tab of the Ribbon.
5. Click the Formulas option from the drop-down list of buttons (see Figure 2.25).

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

Skill Refresher

Paste Formulas without formatting

1. Click a cell location containing a formula or function.
2. Click the Copy button in the Home tab of the Ribbon.
3. Click the cell location or cell range where the formula or function will be pasted.
4. Click the down arrow below the Paste button in the Home tab of the Ribbon.
5. Click the Formulas button under the Paste group of buttons.

Absolute References (Calculating Percent of Totals)

Data file: Continue with CH2 Personal Budget.

To further analyze your budget, you want to see what percentage of your total monthly spending is spent in each category. Since totals were added to row 12 of the Budget Detail worksheet, a percent of total calculation can be added to Column C beginning in cell C3. The percent of total calculation shows the percentage for each value in the Monthly Spend column with respect to the total in cell B12. However, after the formula is created, it will be necessary to turn off Excel’s relative referencing feature before copying and pasting the formula to the rest of the cell locations in the column. Turning off Excel’s relative referencing feature is accomplished through an absolute reference.

First we will create the formula, which needs needs to divide the amount in B3 by the total monthly spend in B12.

1. Click cell C3 in the Budget Detail worksheet.
2. Type an equal sign =.
3. Click cell B3.
4. Type a forward slash /.
5. Click cell B12.
6. Press the ENTER key. You will see that Utilities represent about 17.5% of the Monthly Spend budget (see Figure 2.26).

Figure 2.26 shows the completed formula that is calculating the percentage that Utilities represents to the total Monthly Spend for the budget (see cell C3). Normally, we would copy this formula and paste it into the range C4:C11. However, because of relative referencing, both cell references will increase by one row as the formula is pasted into the cells below C3. This is fine for the first cell reference in the formula (C3) but not for the second cell reference (C12).

Figure 2.27 illustrates what happens if we paste the formula into the range C4:C12 in its current state. Notice that Excel produces the #DIV/0 error code. This means that Excel is trying to divide a number by zero, which is impossible. Looking at the formula in cell C4, you see that the first cell reference was changed from B3 to B4. This is fine because we now want to divide the Monthly Spend for Cell Phone (cell B4) by the total Monthly Spend in cell B12. However, Excel has also changed the B12 cell reference to B13. Because cell location B13 does not contain a number, the formula produces the #DIV/0 error code.

To eliminate the divide-by-zero error shown in Figure 2.27 we must add an absolute reference to cell B12 in the formula. An absolute reference prevents relative referencing from changing a cell reference in a formula. This is also referred to as locking a cell. No matter where you copy a formula with an absolute reference, it will always refer back to the locked cell.  An absolute reference is indicated by a $ sign in front of both the column letter and the row number.  For example, $A$15 is an absolute reference to cell A15.

$A$15 is an example of
an absolute reference

We are going to modify the existing formula in C3 to make the reference to cell B12 an absolute reference. The following explains how this is accomplished:

1. Double click cell C3.
2. Place the mouse pointer in front of B12 and click. The blinking cursor should be in front of the B in the cell reference B12.
3. Press the F4 key. You will see a dollar sign ($) added in front of the column letter B and the row number 12. You can also type the dollar signs in front of the column letter and row number if you prefer. The formula should appear as =B3/$B$12. The F4 key is a cool shortcut for adding the dollar signs.
    Mac Users: If the F4 key does not insert the $ symbols, check the keyboard settings: click black Apple icon at top left of the screen, choose “System Preferences”, click the Keyboard icon, make sure the checkbox is checked for the item that says: “Use F1, F2, etc. as standard function keys”.
4. Press the ENTER key.
5. Click cell C3.
6. Use the AutoFill Handle or Copy and Paste to copy the formula from C3 to the range C4:C11.

Figure 2.28 shows the percent of total formula with an absolute reference added to B12. Notice that in cell C4, the cell reference remains B12 instead of changing to B13. Also, you will see that the percentages are being calculated in the rest of the cells in the column, and the divide-by-zero error is now eliminated.

Skill Refresher

Absolute References

1. Click in front of the column letter of a cell reference in a formula or function that you do not want altered when the formula or function is pasted into a new cell location.
2. Press the F4 key or type a dollar sign $ in front of the column letter and row number of the cell reference.

Sorting Data (Multiple Levels)

Data file: Continue with CH2 Personal Budget.

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

For the purposes of the Budget Detail worksheet, we want to set multiple levels for the sort order.  We are going to sort first by the Percent of Total, and then by the Last Year Spend amount.  Excel will first sort the items by the Percent of Total, and any items with the same Percent of Total will then be sorted by Last Year Spend. This is accomplished through the following steps:

1. Highlight the range A2:F11.
2. Click the Data tab in the Ribbon.
3. Click the Sort button in the Sort & Filter group of commands. This opens the Sort dialog box, as shown in Figure 2.29.
4. Click the down arrow next to the “Sort by” box.
5. Click the Percent of Total option from the drop-down list.
6. Click the down arrow next to the sort Order box.
7. Click the Largest to Smallest option.
8. Click the Add Level button. This allows you to set a second level for any duplicate values in the Percent of Total column.
    the + symbol at bottom left corner is the “Add Level” button for Excel for Mac
9. Click the down arrow next to the “Then by” box.
10. Select the Last Year Spend option. Leave the Sort Order as Smallest to Largest
11. Click the OK button at the bottom of the Sort dialog box.
12. Save the CH2 Personal Budget file.

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

Skill Refresher

Sorting Data (Multiple Levels)

1. Highlight a range of cells to be sorted.
2. Click the Data tab of the Ribbon.
3. Click the Sort button in the Sort & Filter group.
4. Select a column from the “Sort by” drop-down list in the Sort dialog box.
5. Select a sort order from the Order drop-down list in the Sort dialog box.
6. Click the Add Level button in the Sort dialog box.
7. Repeat Steps 4 and 5.
8. Click the OK button on the Sort dialog box.

 

Key Takeaways

• Statistical functions are used when a mathematical process is required for a range of cells, such as summing the values in several cell locations. For these computations, functions are preferable to formulas because adding many cell locations one at a time to a formula can be very time-consuming.
• Statistical functions can be created using cell ranges or selected cell locations separated by commas. Make sure you use a cell range (two cell locations separated by a colon) when applying a statistical function to a contiguous range of cells.
• To prevent Excel from changing the cell references in a formula or function when they are pasted to a new cell location, you must use an absolute reference. You can do this by placing a dollar sign ($) in front of the column letter and row number of a cell reference.
• The #DIV/0 error appears if you create a formula that attempts to divide a constant or the value in a cell reference by zero.
• The Paste Formulas option is used when you need to paste formulas without any formatting treatments into cell locations that have already been formatted.
• You need to set multiple levels, or columns, in the Sort dialog box when sorting data that contains several duplicate values.

Attribution

Adapted by Mary Schatz from How to Use Microsoft Excel: The Careers in Practice Series, adapted by The Saylor Foundation without attribution as requested by the work’s original creator or licensee, and licensed under CC BY-NC-SA 3.0.

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 loan 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 loan repayment. However, before demonstrating this function, it is important to cover a few fundamental concepts on loans.

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.

Table 2.5 Key Terms for Loans

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

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 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, you will place a negative sign between the equal sign and the function name PMT.

We will first use the PMT function in the Personal Budget workbook to calculate the monthly loan payments for a car. These calculations will be made in the Loan 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. When working with more complex functions such as the PMT, it is easiest to use the Function Dialog box.

Remember to use cell references for the arguments of the PMT function whenever possible.  This will allow you the flexibility to change aspects of the loan, such as a lower interest rate or more expensive car, and have the payment automatically recalculate.

Using cell references for the arguments provides greater flexibility in trying different scenarios.

The following steps use the Insert Function command covered in Section 2.2 to add the PMT function:

1. Switch to the Loan Payments worksheet.
2. Click cell B5.
3. Click the Formulas tab on the Ribbon.
4. Click the Insert Function button to bring up the Insert Function dialog box.
5. Type loan payment in the search box and click Go.
    the Excel for Mac search box does is not the same as the “Search for a function: input box”. Mac Users must type: PMT in the search box instead. Then press Enter.
6. Double-click the PMT option in the “Select a function:” box. This will open the Function Arguments dialog box.
7. Drag the Function Arguments dialog box out of the way so that you can see the worksheet cells you want to use in the function. Refer to  Figure 2.31 for the completed Function Arguments dialog box as you complete the next steps.
8. Click in the Rate argument box in the dialog box, then click cell B3 in the worksheet.  This will add B3 (the annual interest rate) to the Rate argument.
9. Type a forward slash / for division.
10. 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.
11. Click the Nper argument box (or use the Tab key) and then click cell B4 in the worksheet. This will add B4 (the number of years to repay the loan) to the Nper argument.
12. Type an asterisk * for multiplication.
13. 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.
14. Click the Pv argument box (or use the Tab key) and then click cell B2 in the worksheet. This will add B2 (the amount of the loan) to the Pv argument.
15. You will now see the Rate, Nper, and Pv arguments defined for the function. (see Figure 2.31)
16. 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 payments will be made at the end of each month. Therefore, there is no need to define the Type argument.
17. Notice that the result of the formula in cell B5 is showing as a negative number (see Figure 2.32). To fix this, double-click on cell B5 and type a negative sign between the equal sign and the letters PMT in the formula (see Figure 2.33).
18. The finished formula in cell B5 should be =-PMT(B3/12,B4*12,B2)

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

The PMT Function when there is a down payment

In addition to calculating the loan payments for a car, the PMT function will be used in the Personal Budget workbook to calculate the mortgage payments for a home. The details for the mortgage payments are also found in the Loan Payments worksheet. Unlike the car loan, there is a down payment with the mortgage.  A down payment on a mortgage is usually a percentage of the price of the home, which is paid up front and reduces the amount of the loan itself.  The down payment amount and amount of the loan will both need to be calculated using formulas.  While we did not use a down payment in the car loan example, it is fairly common to have a down payment when purchasing a car too.

Write the formulas to calculate the Down Payment Amount and new Loan Amount by following these steps:

1. Click cell B11.
2. Write the formula =B9*B10.  This will calculate 20% of the price of the house.
3. Click cell B12. Write the formula =B9-B11. This will subtract the down payment amount from the price of the house (see Figure 2.34 for the Show Formulas View and Figure 2.35 for the formula results).

Now that we have the revised Loan Amount in cell B12, we can write the PMT function following the same process we did for the car loan.

1. Click cell B15.
2. Click the Formulas tab on the Ribbon.
3. Click the Insert Function button to bring up the Insert Function dialog box.
4. Type PMT in the search box and click Go.
5. Double-click the PMT option in the “Select a function:” box. This will open the Function Arguments dialog box.
6. Enter the following arguments (see Figure 2.36)
   • Rate: B13/12  –> divide by 12 to convert the annual rate to a monthly one
   • Nper: B14*12  –> multiply by 12 to convert the number of years into number of months
   • Pv: B12 –> this is the cell with the actual loan amount, not the price of the house
7. Click OK in the Function Arguments dialog box.
8. Modify the formula in cell B15 to display the result as a positive number. Remember to type a negative sign between the equal sign and the letters PMT.
9. Cell B15 should contain the function: =-PMT(B13/12,B14*12,B12) and the result should be $708.60 (see Figure 2.37).

Figure 2.36 shows how the the completed Function Arguments dialog box for the PMT function for the mortgage should appear before pressing the OK button.

Figure 2.37 shows the result of the PMT function for the mortgage. The monthly payments for this mortgage are $708.60. This monthly payment will be displayed in the Budget Summary 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 we will complete the Budget Summary worksheet using values from both the Budget Detail and Loan Payments worksheets.

Outputs from the formulas and functions that were entered into the Budget Detail will be displayed on the Budget Summary worksheet through the use of cell references.

1. Switch to the Budget Summary worksheet and select cell B4. This cell needs to reference the Total Annual Spend (D12) from the Budget Detail worksheet.
2. Type an =
3. Click the Budget Detail worksheet tab.
4. Click cell D12.
5. Press the ENTER key on your keyboard.
6. The formula bar will display the formula =’Budget Detail’!D12 and the cell will display $17,124. (see Figure 2.38)

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

We will use a similar process to enter in the annual car payments and mortgage payments from the Loan Payments worksheet.  The payments on the Loan Payments worksheet are monthly payments though, so we will need to multiply each one by 12 to get the annual amount to display in the Budget Summary worksheet.

1. Click on cell B5. This cell needs to contain a formula that references the monthly car payment cell (B5) on the Loan Payments worksheet and multiplies by 12.
2. Type an =
3. Click the Loan Payments worksheet tab.
4. Click cell B5 on the Loan Payments worksheet.
5. The formula bar will display the formula =’Loan Payments’!B5 
6. Type an asterisk * for multiplication.
7. Type the number 12. The formula in the formula bar should read: =’Loan Payments’!B5*12
8. Press the ENTER key on your keyboard.
9. Click on cell B6. This cell needs to contain a formula that references the monthly mortgage payment cell (B15) on the Loan Payments worksheet and multiplies by 12.
10. Type an =
11. Click the Loan Payments worksheet tab.
12. Click cell B15 on the Loan Payments worksheet.
13. The formula bar will display the formula =’Loan Payments’!B15 
14. Type an asterisk * for multiplication.
15. Type the number 12. The formula in the formula bar should read: =’Loan Payments’!B15*12
16. Press the ENTER key on your keyboard.

Figure 2.39 shows the results of creating formulas that reference cell locations in the Loan Payments worksheet.

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 B3. The following steps explain how this is accomplished:

1. Click cell B7 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 B4:B6.
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 B8 on the Budget Summary worksheet. You will enter a formula to calculate Remaining (Savings) amount in this cell.
7. Type an equal sign =.
8. Click cell B3.
9. Type a minus sign − and then click cell B7.
10. Press the ENTER key on your keyboard. This formula produces a positive number, indicating our income is greater than our total expenses.

Figure 2.40 shows the results of the formulas that were added to the Budget Summary worksheet. 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.

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 C7 in the Budget Summary worksheet.
2. Type an equal sign =.
3. Click cell B7.
4. Type a forward slash / for division and then click B3.
5. Press the F4 key on your keyboard. This adds an absolute reference to cell B3.
6. Press the ENTER key. The result of the formula shows that total expenses consume  89% of our net income.
7. Click cell C7.
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 C8. This copies and pastes the formula into cell C8.
10. Compare your worksheets with Figures 2.41a-c below.  Make any necessary changes before moving on to the next section.
11. Save the CH2 Personal Budget file.

Figure 2.41a shows the completed Budget Summary worksheet

Figure 2.41b shows the completed Budget Detail worksheet

Figure 2.41c shows the completed Loan Payments worksheet

Attribution

Adapted by Mary Schatz from How to Use Microsoft Excel: The Careers in Practice Series, adapted by The Saylor Foundation without attribution as requested by the work’s original creator or licensee, and licensed under CC BY-NC-SA 3.0.

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.

Formatting Worksheet Data

Data File: Continue working with CH2 Personal Budget

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.42 shows how the finished worksheet will appear in Print Preview.

1. 1. Switch to the Prepare to Print worksheet.

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

      

1. Change the font to Calibri, Size 12.
2. 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.44). This will indent the text from the cell border.

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. 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
    Mac Users should click the File menu option and select Print from the menu
2. Click the Margins drop-down arrow in the Settings section (see Figure 2.45)
3. Select Custom Margins… at the bottom of the list.
    Mac Users should select “Manage Custom Margins”
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.46).
    Mac Users: there is no “Scaling drop-down arrow”. Just click the checkbox for “Scale to fit”
7. Select Fit All Columns on One Page.
8. Exit Backstage View.

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.47 should appear.
    Mac Users: there is no “dialog box launcher”. Just click the Page Setup button and continue with Step 3 below.

   

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

   

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.49.
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. Check the spelling on all of the worksheets and make any necessary changes.
13. Save the CH2 Personal Budget file.
14. Compare your work with the completed worksheet shown in Figure 2.42 and then submit the CH2 Personal Budget workbook as directed by your instructor.

Attribution

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

Equipment Loans Worksheet

Complete the Annual Plan Worksheet

Compare both worksheets with the answer keys below.

Learning Objectives

1. Review the use of the =MAX function.
2. Examine the Quick Analysis Tool to create standard calculations, formatting, and charts very quickly.
3. Create Percentage calculation.
   – Use the Smart Lookup tool to acquire additional information about percentage calculations.
   – Review the use of Absolute cell reference in a division formula.

Another use for =MAX

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

Download Data File: CH3 Data

1. Open the data file CH3 Data and save the file to your computer as CH3 Gradebook and Parks.
2. Make B25 your active cell.
3. Start typing =MAX (See Figure 3.2) Note the explanation you see on the offered list of functions. You can either keep typing ( or double click MAX from the list.
   

   

4. Select the range of numbers above row 25. Your calculation will be: =MAX(B5:B24). 
5. Press Enter after selecting the range.
6. Now, use the Fill Handle to copy the calculation from Column B through Column N.
   Note that as you copy the calculation from one column to the next, the cell references change. The calculation in column B reads: =MAX(B5:B24). The one in column N reads: =MAX(N5:N24). These cell references are relative references.

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

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

Quick Analysis Tool

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

Mac Users: the Quick Analysis Tool is not available with Excel for Mac. We have alternate steps for Mac Users below. Skip down below Figure 3.5 to continue.)

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

1. Select the range of cells B5:N25
2. In the lower right corner of your selection, you will see the Quick Analysis tool (see Figure 3.4).
   

   

3. When you click on it, you will see that there are a number of different options. This time we will be using the Totals option. In future exercises, we will use other options.
4. Select Totals, and then the SUM option that highlights the right column (see Figure 3.5). Selecting that SUM option places =SUM() calculations in column O.

 Alternate steps for Mac Users:

1. Select the range B5:O25 then click the AutoSum button on the Ribbon (Home tab or Formulas tab)
2. Select the range O5:O25 and click the Bold button.

Percentage calculation

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

In general, the Smart Lookup tool allows you to get more information and definitions about unfamiliar terms or features. This tool is available in all of the Microsoft Office applications.

1. Select cell P4.
2. Find the Smart Lookup tool on the Review tab (see Figure 3.6) and click it. You can also “Right-click” the specific cell and choose Smart Lookup.
     Mac Users: The Smart Lookup tool is only on the Review tab in the latest versions of Excel for Mac. If you can’t find the Smart Lookup tool on the Review tab, you will find it by clicking on the “Tools” menu bar option.
   Note for all users: there is a keyboard shortcut for using the Smart Lookup tool. You can hold down the Control key and click in the specific cell (in this case, P4)
3. If this is the first time you have used the Smart Lookup tool, you may need to respond to a statement about your privacy. Press the Got it button. We think the Wikipedia article does a pretty good job explaining the calculation, don’t you?
4. Close the Smart Lookup pane after reading through the definitions.

 

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

1. Make sure that P5 is your active cell.
2. Press = then select cell O5. Press /, then cell O25. Your calculation should look like this: =O5/O25. The result of the formula should be 0.95641026.  (So far, so good. DeShea Andrews is doing well in this class – with a percentage grade of almost 96%. Definitely an “A”!)
3. Next use the Fill handle to copy the calculation down through row 24 to calculate the other students’ grades. You should get the error message #DIV/0!. This error message reminds us that you can’t divide a number by 0 (zero). And that is just what is happening. If you look at the calculation in P9, the calculation reads: =O9/O29. The first cell reference is correct – it points to Moesha Gashi’s total points for the class. But the second reference is wrong. It points to an empty cell – O29.

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

1. Make P5 your active cell. In the Formula Bar click on O25 (see Figure 3.7).
2. Press F4 (on the function keys at the top of your keyboard). That will make the O25 reference absolute. It will not change when you copy the calculation (see Figure 3.8).  (If you are working on a laptop and do not have an F4 function key, you can type in a $ before the O and another one before the 25.)
3. The calculation now looks like this: =O5/$O$25.
4. Use the Fill Handle to copy the formula down through P24 again. Now, when you copy the formula, you will get correct values for all of the students.

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

1. Select the range P5:P24.
2. On the Home tab, in the Number Group, select the % (Percent Style) button.

Skill Refresher

Absolute References

1. Click in front of the column letter of a cell reference in a formula or function that you do not want altered when the formula or function is pasted into a new cell location.
2. Press the F4 key or type a dollar sign ($) in front of the column letter and row number of the cell reference.

Keyboard Shortcuts

Smart Lookup Tool

• Hold down the CTRL key and click the specific cell that you are working with. Then choose “Smart Lookup“
•  Mac Users: Same as above

 

Key Takeaways

• Functions can be created using cell ranges or selected cell locations separated by commas. Make sure you use a cell range (two cell locations separated by a colon) when applying a statistical function to a contiguous range of cells.
• To prevent Excel from changing the cell references in a formula or function when they are pasted to a new cell location, you must use an absolute reference. You can do this by placing a dollar sign ($) in front of the column letter and row number of a cell reference or by using the F4 function key.
• The #DIV/0 error appears if you create a formula that attempts to divide a constant or the value in a cell reference by zero.

Attribution

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

Learning Objectives

• Locate and fix formatting consistency errors.
• Apply new formatting techniques.
• Use Print Titles to repeat rows and columns on each page of a multiple page worksheet.
• Control where page breaks occur in a multiple page worksheet.

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

Reviewing Formatting for Consistency

Open the “CH3-Gradebook and Parks” workbook if it isn’t already open.

Click on the “Park Size” sheet tab within your “CH3-Gradebook and Parks” workbook  .

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

Reviewing Formatting for Inconsistencies

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

1. Scroll through the worksheet and locate the following formatting errors:
   • The formatting of the Utah label does not match the other states.
   • The Year Established values for Hawaii are not center aligned like the other years.
   • The cells for the Nevada data should have the same green fill color as the other alternating states.
   • The number of digits after the decimal place for the Size values is inconsistent. Also, these values should be formatted with Comma style to make them easier to read.
2. To fix these errors, complete the following steps:
   • Merge & Center A34:A38. Change the font size to 16 and apply Bold format.
   • Center align C28:C29.
   • Apply the green fill color to A31:E31 (be sure to match the green fill color of the other states).
   • Select E4:E43 and apply Comma Style. Use Increase Decimal and/or Decrease Decimal until one digit appears after the decimal place for all values.
3. While you’re fixing errors, proofread the sheet and correct any typos.
4. Finally, let’s add color to the two sheet tabs. The use of colored tabs assists in navigating between sheet tabs.
   • Right-click the “Park Size” sheet tab ( Mac users hold down Ctrl key and click the sheet tab) 
   • Point to Tab Color and choose a “blue” color.
   • Now right-click the “Grades” sheet tab, point to Tab Color and choose an “orange” color. That’s it!

Fine-tuning Formatting

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

1. Select A4:A43 (the cells with the state labels).
2. Click the Home tab on the ribbon.
3. In the Alignment group, click the Middle Align button (see Figure 3.26). Notice that the names of the states are now centered between the top and bottom borders of the cells.

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

1. Double-click on cell E3 to enter Edit mode
2. Select just the 2 (be careful not to select anything else).
3. On the ribbon (Home tab) click the dialog box launcher arrow in the Font group.
    Mac Users: there is no dialog box launcher for Excel for Mac. Instead, choose Format from the Menu Bar, click Cells:   then continue with Steps 4 and 5  
4. In the Effects section of the Format Cells dialog box, check the box for Superscript (see Figure 3-27). Click OK.
5. Save the CH3 Gradebook and Parks file.

Repeating Column (and Row) Labels

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

1. With the CH3-Gradebook and Parks file still open, and the Parks tab selected, go to Backstage View by clicking the File tab on the ribbon. Select Print from the menu.
    Mac Users: choose File from the Menu Bar, and then choose Print
2. Click through each of the pages. The worksheet is currently printing on four pages ( Mac users may only see three pages but that is ok), with the City and Sizes columns printing on separate pages from the rest of the data.
3. Change the Orientation from Portrait to Landscape. This fits all of the columns on one page. All of the columns are now on the same page, but the second and third pages have no column labels to identify what information is in each column. You are going to use Print Titles to repeat the first three rows of the worksheet on each of the printed pages. To set Print Titles you need to exit Print Preview.
4. Exit Backstage View then click the Page Layout tab on the ribbon.
5. Click the Print Titles button in the Page Setup group on the ribbon. The dialog box shown in Figure 3.28 should appear.
6. Click the Sheet tab if necessary.

   
7. Click in the Rows to repeat at top: box. Be sure your insertion point is blinking in that box before moving on to the next step.
8. In the worksheet, select Rows 1 through 3. The text $1:$3 should now appear in the Rows to repeat at top: box.
9. Click OK.

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

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

Skill Refresher

Creating Print Titles

1. Open the Page Setup dialog box and click the Sheet tab.
2. Click in the Rows to repeat at top: box or the Columns to repeat at left: box.
3. Click in the worksheet and select the row(s) or column(s) that you want to repeat on each page.

Inserting Page Breaks

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

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

 Mac Users: in the next paragraph below, the location of the automatic page breaks may be in different locations. That’s ok.

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

1. Select cell A15. When inserting a page break, you select the cell below where you want the page break to appear.
2. Click the Page Layout tab on the ribbon.
3. Click the Breaks button in the Page Setup group (see Figure 3.30).
4. Select Insert Page Break from the menu. There is now a solid blue line after row 14, which indicates a manual page break that was inserted.
5. Go to Print Preview. Notice that the California data now starts on the second page.

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

1. Exit Backstage View. Switch back to Page Break Preview if needed.
2. Locate the next dotted blue line (automatic page break).
3. Put your pointer over the dotted blue line and it will switch to a vertical double-headed arrow. Click on the dotted blue line and drag it above Montana.
4. Release the mouse button when the line is above row 30 (above Montana). The line will now be a solid blue line, indicating a manual page break.
5. Go to Print Preview. The Montana data now appears at the top of the third page.

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

1. Click the Page Setup link at the bottom of the Settings section of Backstage View to open the Page Setup dialog box.
    Mac Users: there is no “Page Setup link” in Print Preview for Excel for Mac. Click the Margins list arrow instead, and choose “Manage Custom Margins” then continue with the steps below. 
2. Click on the Margins tab.
3. In the Center on page section, check the box for Vertically then click OK.
4. Review each page in Print Preview to see the changes. Exit Backstage View.

Creating a Header and Footer using Page Layout View

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

1. Click the View tab on the ribbon and click the Page Layout button in the Workbook Views group.
2. The white space at the top of the worksheet should say Add header. Place the mouse pointer over the left section of the Header and click to activate that section.
    Mac Users should make sure the mouse pointer turns into a small page icon  then click in the left section of the Header
3. Click the Header & Footer Tools Design tab on the ribbon.
4. Click the Current Date button in the Header & Footer Elements group (see Figure 3.31). Inserting the date this way will insert a field that will update every time the workbook is opened.
5. Click in the right section of the Header. Click the Filename button in the Header & Footer Elements group (see Figure 3.31). Inserting the filename this way will insert a field that will update if the filename is changed.
6. Click the Go to Footer button in the Navigation group of commands.
7. In the center section of the footer, type the word Page with a space after it.
8. Click the Page Number button in the Header & Footer Elements group (see Figure 3.31), then type a space after the &[Page] code that appears.
9. Type the word of with a space after it, then click the Number of Pages button in the Header & Footer Elements group (see Figure 3.31). The footer should match Figure 3.32.
10. Click anywhere on the worksheet to close the Footer editing.
11. Review the worksheet again in Print Preview. Pay careful attention to the page numbers in the footer to ensure they will print correctly, then exit Backstage View.
12. View the correct print preview screenshot below in Figure 3.33
13. Check the spelling on all of the worksheets and make any necessary changes. Save and submit the CH3-Gradebook and Parks workbook.

Skill Refresher

Inserting Page Numbers

1. In Page Layout View, click in the section of the header or footer where you want the page number to appear.
2. Type the word Page, followed by a space, and then click the Page Number button in the Header & Footer Elements group on the Header & Footer Tools Design ribbon. This will create Page 1.
3. If desired, type a space after the &[Page] code then type the word of followed by a space. Then click the Number of Pages button. This will create Page 1 of 4.

 

Key Takeaways

• Always check the formatting of your worksheets for consistency.
• If a worksheet is printing on multiple pages, use Print Titles to repeat rows at the top and/or columns at the left of every page to make it easier to interpret the data.
• Insert manual page breaks as needed in Page Break Preview to control where a new page begins.
• Multiple page worksheets should include the page number in either the header or footer. Be sure to insert the Page Number element so that the correct page number will display on each page of the worksheet.

Attribution

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

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.

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 shown below in Figure 4.1,  as the number of bars increases on a column chart, it becomes increasingly difficult to read.  In our first example, 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.

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). Notice Excel adds, or embeds, the line chart into the worksheet.

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.

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 bottom lower corner sizing handle, drag and drop to approximately the end of Column I, and Row 45.

Note: keep an eye on your pointer. It will change into when you are in the right place to resize your chart

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

 4.  Type the following in front of the first letter in the chart title: May 2014-2016 Trend for NASDAQ Sales.

 5.  Click anywhere outside of the chart to deactivate it.

 6.  Save your work.

Figure 4.4 shows the line chart after it is moved and resized. Notice that the sizing handles do not appear around the perimeter of the chart. This is because the chart has been deactivated. To activate the chart, click anywhere inside the chart perimeter.

Adjusting the Y-Axis Scale

After creating an Excel chart, you may find it necessary to adjust the scale of the Y-axis. Excel automatically sets the maximum value for the Y-axis based on the data used to create the chart. 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.

  Mac Users: Hold down the Control key and click the Y axis. Then choose Format Axis.

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

 4.  Click the X in the upper right corner of the Format Axis pane to close it.

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

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.

1.  Move the chart so the upper left corner is in the middle of cell M1.

2.  Resize the chart, using the resizing handle so the graph is approximately in the area of M1:U13.

3.  Click in the text box that says “Chart Title.” Delete the text and replace it with the following: 24 Month Trend Comparison.

4.  Adjust the Closing Price axis, by double-clicking the red line across the bottom of the chart that represents the Closing Price.

5.  The Format Data Series dialogue box opens. In the Series Options, select Secondary Axis.

Excel adds the secondary axis.  Format the values on the secondary axis to represent prices.

 1.  Double click the Secondary Vertical Axis. (The vertical axis on the right that goes from 0 to 140.)

 2.  In axis options, scroll down to the Number section.

Mac Users: If needed, click the Number “expand arrow”

 3. Use the Symbol list box to add the $.

 4.  Press the Close button to close the Format Axis pane.

 5. Save your work.

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. In this situation, notice we do not select the Total row.  The totals are a representation of all data and would skew the graph. Essentially you would be graphing the information twice.  If you want to display the totals in a chart, the best approach is to create a separate chart that only displays the total values.

The following steps to create the column chart:

 1.  Select the Grade Distribution worksheet.

 2.  In Row3, replace the red text at states [Insert Current Year] and replace it with the actual current academic term and year.

 3.  Select two non-adjacent columns by selecting A3:A8.

 4.  Press, and hold down the Crtl key.

Mac Users: Hold down the Command key instead.

 5. Without letting go of the Ctrl key, select C3:C8

 6. From the ribbon click the Insert tab. Choose the Column button.

 7. Select the Clustered Column format. (First option listed.)

 8. Click and drag the chart so the upper left corner is in the middle of cell H2.  Resize the graph to fit in the area of H2: O13.

 9. Click any cell location on the Grade Distribution worksheet to deactivate the chart.

 10. Save your work.

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

Creating a Chart Sheet

1. Click anywhere on the Final Grades for All Excel Classes chart on the Grade Distribution worksheet.
2. From the Chart Tools Design tab.  Select Move Chart . This opens the Move Chart Dialog box.
3. Click the New sheet option on the Move Chart dialog box.
4. The entry in the input box for assigning a name to the chart sheet tab should automatically be highlighted once you click the New sheet option. Type All Excel Classes. This replaces the generic name in the input box (see Figure 4.11).
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.12 shows the Final Grades for 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.

Map Charts

Data visualization brings more depth in how information, in this case geographically, connects. You can use a map chart to compare values and show categories across geographical regions like countries/regions, states, counties or postal codes. Excel will automatically convert data to geographical locations and will display values on a map. As shown below, in Figure 4.14, in the next steps we will compare West Coast Community College enrollments for Fall of 2019 using a map chart.

 

 

1. From the Enrollment Statistics worksheet, select A3:A13. Next, press and hold the CTRL key and select C3:C13.
2. Click the Insert tab on the Ribbon.
3. Click Maps, and choose the Filled Map option.
4. From the Charts Design Tab, choose Move Chart, and select the New Sheet option. In the name box type Map. Click Ok.
5. To make sure the map and data are vibrant and will stand out in a presentation make the following changes:

 

a) Select the Title. Type Enrollment Totals. Change the font to bold, size 18.

b) From the top right corner of the Chart area, choose the Charts Elements plus sign.

c) Select the Data Labels checkbox. Notice the values appear on each State.

 Mac Users: there is no “Charts Element plus sign”.  Follow the alternate steps below.

Click the “Chart Design” tab on the Ribbon
Click the “Add Chart Element” button on the Ribbon
Point to “Data Labels” option and click “Show”

d) Save your work.

FUNNEL CHARTS

Another graph to visualize data is a Funnel chart.  Funnel charts provide a visual snapshot of a process.  From our data, we will create a Funnel Chart to show how many students we have in the admissions process.  You can quickly review the funnel chart to see admissions predicts to have 932 new enrolled students for Winter Term 2020.

Insert a Funnel chart by following the below steps.

 

1. From the Admissions sheet, select A3:B7.
2. From the Insert tab, choose Recommended Charts.
3. Scroll down the list and select the Funnel Chart. Click OK.
4. Move and resize the graph to approximately fit in the range of D1:K15.
5. From the Design Tab, click the 6th style option provided in the styles gallery. (The background is black.) It is okay if your text color is different than the figure below.
6. Change the Chart Title to Admissions Pipeline Winter 2020. 
7. From the Charts Elements, turn off the Legend.
    Mac Users: Click the “Add Chart Element” button and change the “Legend” option to “None“
8. Save your work.

 

 

PERCENT OF TOTAL: DOUGHNUT PIE CHART

The next chart we will demonstrate is a pie chart. A pie chart is used to show a percent of the total for a data set at a specific point in time.  Using the Doughnut Pie Chart, show the percentage of students enrolled at a full-time status. As in the last example, the data is located on the Enrollment Statistics sheet.

 

1. From the Enrollment Statistics worksheet, select A3:A13. Next, press and hold the CTRL key and select D3:D13.
2. Click the Insert tab on the ribbon.
3. Click the Pie button in the Charts group of commands.
4. Select the “Doughnut” option from the drop-down list of options.
5. From the Charts Design Tab, choose Move Chart, and select the New Sheet option. In the name box type Full-Time Students. Click Ok.
6. From the Chart Tools Design Tab, click the Chart Styles gallery and apply Style 7.
7. On the Chart, expand the Charts Elements tools. Select the Legend Choose to display the legend on the Right.
    Mac Users: Click “Add Chart Element” button, point to Legend and choose “Right”
   
8. From the Charts Elements, select the Data Labels checkbox. Then, drop down the Data Labels menu. Choose, More Options.
    Mac Users: Click “Add Chart Element” button, point to Data Labels and choose “More Data Label Options”
   

 

9.  From the Format Data Label Options menu, select Percentages, and Deselect Values to show the percent of total students that are enrolled at a full-time status.

10.  Close the Format Data Labels menu.

 

Notice the font is small compared to the graph size. Adjust the font size of the Title, Legend, and Data Label by following the below steps:

 

1. Select the Title. Change the font to bold, size 18.
2. Select the Legend. Change the font to bold, size 14.
3. Select one of the data labels in a doughnut wedge. Notice now all labels are selected. Change the font bold, size 12.

 

 

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.

Bar chart vs Column chart

We will statistical data to compare a bar and column chart.  Both the Bar and the Column chart display data using rectangular bars where the length of the bar is proportional to the data value. Both charts are used to compare two or more values. However, the difference lies in their orientation. A bar chart is oriented horizontally whereas the column chart is oriented vertically. Although alike, they cannot be always used interchangeably.  The difference in their orientation, meaning typically the more data values the harder it is to read in a column format.  This is where visually a bar chart would be a better choice.   Complete the below steps to insert both a bar and column chart comparing not only the gender and age differences of enrolled students but the type of graphs you are viewing the data in.

1. From the Enrollment Statistics sheet, select A3:A13. Press and hold the CTRL key and select G3:H13.
2. From the Insert tab, choose Recommended Charts.  Scroll down and select the Stacked Bar chart option. Click Ok.
3. Move and resize the graph so it fits approximately in L1:U13.
4. Add a chart title. Type Age Comparison.
5. Notice the age difference.  Currently, per State, the majority of students are under 20 years old. Save your work.

 

Next, insert a column chart comparing gender.

1. From the Enrollment Statistics sheet, select A3:A13. Press and hold the CTRL key and select I3:J13.
2. From the Insert tab, choose Recommended Charts. Choose the first option, Clustered Column chart.
3. Move and resize the graph so it fits approximately in L15:U32.
4. Add a chart title. Type Gender Comparison.
5. Notice the ratio of women and men enrolled are pretty equal per State.  Save your work.

 

 

Stacked Column Chart

The last chart types we will demonstrate is the stacked column chart and a bar chart. You will use a stacked column chart to show differences in budgeted expense accounts for the admissions department and a bar chart for age comparisons of enrolled students at the college.

Follow the below steps to insert a stacked column chart.

1. Click the Expenses sheet. Select the range A4:G9.
2. Click the Insert tab of the ribbon.
3. Click the Column button in the Charts group of commands. Select the 3D– Stacked Column format.
4. Change the Chart Title to Expenses.
5. Move the Chart to a New Sheet. In the name box type Budget.
6. Save your work.

 

Figure 4.21 shows the final stacked column chart.

 

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

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. Follow the below steps to make some changes to the percentage numbers on the Y (vertical) axis.

1. In the Grade Distribution worksheet, click on the Grade Distribution Comparison chart.  Double-click the vertical (value) axis.  This opens the Format Axis pane.
2. Select Axis Options. Change the Minimum Bound to .05 to make the differences in the columns more dramatic.
3. Click the Close button at the top of the Format Axis pane.
4. Save your work.

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. In the upper right corner of the graph, choose the Charts Element plus sign.  Select the Axis Titles, then Primary Horizontal and Primary Vertical. This inserts the place holders that you will type text in.
   Mac Users click the “Add Chart Element” button in the Design tab, point to “Axis Titles” and click on “Primary Horizontal”. Do this one more time and click on “Primary Vertical”.
3. Click at the beginning of the Y-axis title and delete the generic title. Type Percent of Enrolled Excel Students.
4. Click at the beginning of the X-axis title and delete the generic title. Type Final Course Grade.   

Data Series Labels and Formats

Adding labels to the data series of a chart is a key formatting feature. A data series is an 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 red columns representing the All Excel Classes data series, then Right-Click to open the menu.
   Mac Users should hold down the CTRL key and click on any of the red columns.
2. From the menu, select Format Data Series.
3. From the Format Data Series pane, 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 40% (last option in the top row). Change the Foreground to white, and the Background to Red.
6. Close the Format Data Series pane.

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.25.)
3. Click on one of the Data Labels. Note that all of the data labels for that data series are selected.
4. Check the spelling on all of the worksheets and make any necessary changes. Save your work.

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

Attribution

Adapted by Hallie Puncochar and Noreen Brown from How to Use Microsoft Excel: The Careers in Practice Series, adapted by The Saylor Foundation without attribution as requested by the work’s original creator or licensee, and licensed under CC BY-NC-SA 3.0.

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 on this chapter — CH4 Charting.
• A Word document data file — CH4 CC Enrollment

Excel charts can be valuable tools for explaining quantitative data in a written report. Reports that address business plans, public policies, budgets, and so. For this example, we will assume that the total enrollment per state from the Enrollment Statistics Map chart is being used in a student’s written report. (see Figure 4.26). The following steps demonstrate how to paste an image, or picture, of this chart into a Word document:

1. Open CH4 CC Enrollment. Save it as CH4 Enrollment Totals Per State.
2. Move your cursor to the bottom of the document by clicking below the heading that reads: Figure 1: Enrollment by State. The image of the Map chart will be placed below this heading.
3. If needed, open the Excel file you have been working with (CH4 Charting). Activate the Map chart.
4. On the Home tab of the ribbon.  Click the Copy button dropdown arrow  and select Copy as Picture.
5. Select OK — Accepting the Copy Pictures defaults:
   • As shown on Screen
   • Picture
6. Go back to the CH4 Enrollment Totals Per State Word document.
7. Confirm that the insertion point is below Figure 1: Enrollment by State heading, click the Paste button in the Home tab of the ribbon ( or press Crtl-V).
8. Note the picture of the Map chart will need to be resized. Resize the image by slowly dragging one of the corner sizing handles so it’s large enough to fill space below the text on the first page. Make sure the image does not spill over to the next page. This document should be one page only.
9. Save your work.

Pasting a Linked Chart Image into PowerPoint

For this exercise you will need two files:

• The Excel spreadsheet you have been working on in this chapter — CH4 Charting.
• A PowerPoint data file – CH4 PowerPoint CC
• 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 an explanation of quantitative data. This demonstration includes a PowerPoint slide that could be used in a presentation. We will paste the linked Budget chart into the PowerPoint slide. As a result, if we change the chart in the Excel file, the change will be reflected in the PowerPoint file.

1. Open CH4 PowerPoint CC .pptx. Save it as CH4 PowerPoint CC Enrollment.
2. Navigate to Slide 6 – Budget To Increase Enrollment. This is the slide where you will place the linked chart.
3. If needed, open the Excel file you have been working with (CH4 Charting). Activate the Budget chart. Click copy, (not Copy as Picture.)
4. Go back to the CH4 PowerPoint CC Enrollment presentation.
5. Make sure you are still on Slide 6. Click into the empty prompt box on the right.
6. Click the Paste button dropdown arrow in the Home tab of the ribbon in the PowerPoint file, choose the Paste Option Use Destination Theme and Link Data (L).

Mac Users should choose “Use Destination Theme”
This pastes an image of the Excel chart into the PowerPoint slide yet changing the appearance to match the current theme of the PowerPoint slide.

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 Expenses worksheet. The Advertising cost for June was cut. Update the spreadsheet to change.  Change the value in cell G5 to 1000.
3. Select the Budget worksheet. Notice how the chart has changed.
4. Return to the PowerPoint file.  On Slide6, you should see the updated chart.
5. Save your work.  You will submit both the Word and PowerPoint files, along with the Excel file, at the end of the next section.

Attribution

Adapted by Hallie Puncochar, and Noreen Brown from How to Use Microsoft Excel: The Careers in Practice Series, adapted by The Saylor Foundation without attribution as requested by the work’s original creator or licensee, and licensed under CC BY-NC-SA 3.0.

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 All Excel Classes 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 All Excel Classes 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 Stock Trend worksheet has a lot of data and multiple embedded charts.  We need to print the data and the charts, which will require modifications to the page setup.

 1.  Click on the Stock Trend worksheet tab.

 2.  Go to Print Preview by clicking Print in Backstage View.

Mac Users choose “File/Print…” from the Excel File menu option.

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 (page 7) has a column of seemingly random numbers.

 5.  Exit Backstage View.

6.  The first thing we are going to do is hide the numbers that are appearing on page 7.  We are going to hide the column, instead of deleting the numbers, in case the numbers are being utilized somewhere else in the workbook.

7.  Scroll to the right on the worksheet until you find the numbers in column AH.

8.  Click anywhere in column AH.

9.  On the Home ribbon, click the Format button in the Cells group.

10.  In the Visibility section, select Hide & Unhide then select Hide Columns.

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

Although Excel is primarily used in business and scientific applications, you will find it useful in other areas of study as well. In these exercises, we will use Excel to create charts using historical, and health data.

Charting Historical Data (Comprehensive Review)

Download Data File: PR4 Data

Excel is an excellent tool for helping display historical data. In this exercise, we will be examining ways to display information on minimum mage data and life expectancy.

 

Task1 – National Minimum Wages Changes in The United States 2019-2020

Since the beginning of the previous century, the United States has set a minimum wage, in order to set a “floor” beneath which wages cannot fall. Most states have set their own minimum wages, but none are lower than the national minimum wage. Follow the below steps to insert a Map Chart outlining what the current minimum wage is per state.

 1.  Open the file named PR4 Data and then Save As PR4 Historical Data.

 2.  On the Minimum Wage worksheet, select the range B4:B55. Press and hold the CTRL key and select D4:D55.

Mac Users: hold down the “Command” key not the CTRL key

 3.  Select the Insert tab, then the Map Chart tool in the Charts group.

 4.  Move the Chart as a New Sheet.  Rename the sheet Map.

 5.  Update the Chart Title to US Minimum Wage 2020.

 6.  From the Charts Element menu choose to display the Data Labels.

 7.  From the Charts Element menu, turn off the Legend.

 8.  Prepare the Minimum Wage worksheet for printing by changing the scaling to Fit Sheet on One Page.

 9.  Save your work.

 

Task 2 – Oregon: Projected Life Expectancy at Birth

In the past 40 years, between 1970 and 2010, life expectancy for Oregon men improved by 8.7 years and for women by 5.5 years. Oregon’s life expectancy has remained slightly higher than the U.S. average. The life expectancy will continue to improve for both men and women. However, the gain for men has been outpacing the gain for women. Consequently, the difference between men’s and women’s life expectancies has continued to shrink.

https://www.oregon.gov/das/OEA/Documents/OR_pop_trend2012.pdf

 1.  On the Life Expectancy sheet, select A5:B11.

 2.  From the Insert tab choose Recommend Charts. Select the second option, Clustered Column chart.

 3.  Move the chart to a new sheet. Name the sheet Men.

 

 

4.  Repeat steps above to create a matching chart for Life Expectancy for Oregon Women, by selecting A5:A11.  Press and hold the CTRL key and select C5:C11.

Mac Users hold down the Command key

5.  Use the Recommended Charts and select the Clustered Column chart.

6.  Move the chart to a new sheet. Name the sheet Women.

 

 

7.  Notice on the men’s and women’s vertical axis the min and maximum bounds do not match.  To ensure data is comparable, adjust the min and max bounds of both the Mens and Womens chart to chart to match:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

8.  Return to the Life Expectancy tab, select A5:D11.

9.  Use the Recommended Charts tool to create a simple line chart.

10.  Change the Chart Title to Oregon: Projected Life Expectancy at Birth.

11.  Leave the chart embedded in the worksheet. Move and resize it accordingly.

12.  The line across the bottom of the chart represents the difference between men’s and women’s life expectancy. It is not very helpful as it is.  Right-click on the line to open the pop-up menu. Select Format Data Series. In the Format Data Series pane, under the Series Options tab, select the radio button in front of Secondary Axis.

Mac Users should hold down the CTRL key and click the line at the bottom.
Select Format Data Series. In the Format Data Series pane, under the Series Options tab, select the radio button in front of Secondary Axis.

13.  Close the Format Data Series pane.

14.  Use the Chart Styles tools to change your chart to something a bit more dramatic.

15.  Preview the Life Expectancy worksheet in Print Preview and make any necessary changes. The solutions are shown in below in Figure 4.35.

16.  Check the spelling on all of the worksheets and make any necessary changes. Save the PR4 Historical Data workbook.

17.  Submit the PR4 Historical Data workbook as directed by your instructor.

 

Charting  Sales Data

Download Data File: SC4 Data

An effective way to communicate findings in Excel is by graphing data. Graph the attached sales information, visualizing profit margin trends, and production patterns, and the sales pipeline; enabling decision-makers to grasp concepts of current business practices.

Task 1 Revenue & Profit Margin

Following the directions below create the below charts. Note to match each chart exactly, including the chart styles, and axis bounds.

1. Open the file named SC4 Data and then Save As SC4 Sales.
2. From the Revenue & Profit Margin sheet, create the below Combination Chart.
3. Move the chart to a new sheet. Name the sheet Combo.
4. Save your work.

 

Task 2 Quartly Production Data

Create the below Pie Chart showing how many locations will be producing products in the North, South, East and West Regions.

1. From the Products sheet create the below chart.
2. Move the chart to a new sheet. Name the sheet Doughnut.
3. Save your work.

 

Task 3 Sales Pipeline

Create the below Funnel Chart to provide our sales team a visual snapshot of the company’s sales process, outlining deals that are expected to close within the month.

1. From the Sales sheet, create the below Funnel Chart.
2. Note to leave the chart embedded in the sheet.  Resize, and move the chart accordingly.
3. Check the spelling on all of the worksheets and make any necessary changes. Save your work and submit SC4 Sales as directed by your instructor.

Learning Objectives

1. Understand table properties and structure.
2. Format data as a table.
3. Use Freeze Panes.
4. Work with the Table Tools Design tab.

Organizing, maintaining, analyzing, and reporting human resources data is essentials across industries. In this chapter, we will import data, and demonstrate tabling skills by examining employee relations, payroll, benefits, and training options.

 

 

TABLE PROPERTIES & STRUCTURE       

Turning a range of cells into an Excel table makes related data easier to analyze, visualize, and report. Structuring and planning table layouts are vital for data integrity. Below are guidelines to consider when designing and building a table from scratch:

 

OVERVIEW 

Excel tables behave independently from the rest of the information on the worksheet. Excel treats the table area as a database locking the record entries together. There are several advantages of Excel treating the data independently. For example, using integrated filters and sort functions you can effortlessly drill down data based on questions and in return get results. Excel will also automatically expand the table to accommodate new data entries and allows for automatic formatting, such as recoloring of banded rows or columns.

You will also notice Excel treats formulas and calculations differently in a table, showing structured column names, along with automatically filling a calculated field to the entire table or offering quick and easy table totaling tools.

When graphing and charting table data you will also see Excel automatically adjusts of associated charts and ranges based on what the user is sorting or filtering at the time.

In industry, data is commonly stored in databases or multiple Excel files.   Databases vary drastically, therefore in some cases, it is necessary to import data types into Excel.  In our example, we will work with an Excel file that has imported data from a human resources database. The data downloaded from the database is stored in an Excel workbook, however, it’s in a Comma Separated Values (CSV) format. We will import the Excel file into our CH 5 Data file, turn the data into a table for further analysis.

IMPORT AND FORMAT DATA AS A TABLE

Download Data file:  CH5 Data

CH-5-HR_

Keeping the above table guidelines in mind, import human resource data into Excel, as a table.  Demonstrate tabling skills by examining employee relations, payroll, and benefits. Note you will need to save the CH 5 HR file on your computer as you will import this file into the CH 5 Data file in the below steps.

 1.  Open data file CH 5 Data and save the file as CH5 HR Report.

 2.  In the EmployeeData sheet, click on cell A5.

Mac Users: Excel for Mac does not have the tool for “Getting Data” from an Excel Workbook. You will set up this data using alternate steps. Please skip steps 3-11. The alternate steps can be found below after Step 11.

 3.  From the Data tab, choose Get Data.

 4.  From the Get Data menu, choose From File, then From Workbook.

5. Navigate to the course data files.  Find, and select the CH 5 HR file.

6.  Click Import.

7.  The Navigator dialogue box will open. Select the CH5 CSV File listed in the Display Options pane.

8.  At the bottom of the Navigator dialogue box, select Load to expand the menu and choose Load To…

9.  The Import dialogue box will open. In the “Where do you want to put the data?” section choose Existing worksheet: 

 10.  In the above steps A5 was already selected when we started the import, so Excel will indicate we want the information to import and display starting at cell =$A$5.  If you did not click cell A5, then select the cell now.  Click OK.

11.  The data imports as a table.  Close the Queries & Connections dialogue box.

 These are the alternate steps for Mac Users Only. If you are using Excel for Windows, please continue with the “Table Tools Design Tab” section below these alternate steps.

1. Only complete the following steps if you are using a Mac. If you are using a PC, you have already inserted the table. You should already have the CH 5 HR Report open and you should have clicked into cell A5 in the EmploymentData sheet. If you did not do this, please do it now.
2. Open the CH 5 HR workbook that you downloaded
3. Use the keyboard shortcut of Ctrl key + letter A to select all of the data in the worksheet. That should be cells A1:J102
4. Copy this data
5. Switch back to the CH 5 Report workbook and make sure cell A5 is the active cell
6. Paste the data into the Employment Data sheet at cell A5
7. Make sure Cell A5 is still the active cell and click the Insert tab from the Ribbon
8. Click the Tables button from the Ribbon and then click the Tables icon
9. The Create Table dialog box should appear with the cell range of A5:J106 as shown here in Figure 5.8. Click “OK” to accept this range for your table.
   

   

    

10. Congrats! You just converted the data to an Excel table. Continue following the steps in the section below.

TABLE TOOLS DESIGN TAB

Excel tables require specific tools. The Table Tools Design tab houses these specific tools used for formatting and editing tables. The Table Tools tab is considered a contextual tab; meaning the tabs appear when you are clicked in a table area.  When you click out of a table, the Table Tools disappear.

Explore the table tools now.  Notice the specific checkboxes to turn on table options, for example, you can choose to display banded rows or banded columns, or a total row etc.  We will explore table tools in the following steps.

When importing data as a table, Excel automatically applied table formatting.   Follow the below steps to format and edit the table.

 1.  Click the Table Tools/Design tab on the ribbon.

 Mac Users: you don’t have a Table Tools/Design tab. Just make sure the Table tab is selected.

 2.  From the provided Table Styles, choose the Blue, Table Style Medium 2 option.

 Mac Users: the table you just created may already have the “Blue, Table Style Medium 2 option. 

Another option for inserting a table is using the Insert button. The Insert Table button, located on the Insert tab will turn a range of information into an unformatted table. We will use the insert table option later on in the chapter.

Skill Refresher

Format Data as a Table

1. Click on the top-left cell in your data.
2. Click the Format As Table menu from the Home tab on the Ribbon. Choose a style.
3. Make sure “My table has headers” is checked. Click OK.
4. Click on the top-left cell again.
5. Adjust the widths of the columns so that you can see the complete headings with the filter arrows showing.

 

VIEWING table data

USING PANES 

Data sets can bridge thousands of records with dozens of fields and extend beyond a workbook window. It can be difficult to compare fields and records in widely separated columns and rows. One way of dealing with this problem is by dividing the workbook window into viewing panes by using the Split view option.  Excel can split the workbook window into four sections called panes with each pane offering a separate view into the worksheet. By scrolling through the contents of individual panes, you can compare cells from different sections of the worksheet side-by-side within the workbook window.

To split the workbook window into four panes, select any cell or range in the worksheet, and then on the View tab, in the Window group, click the Split button. Split bars divide the workbook window along the top and left border of the selected cell or range. To split the window into two vertical panes displayed side-by-side, select any cell in the first row of the worksheet and then click the Split button. To split the window into two stacked horizontal panes, select any cell in the first column and then click the Split button. To turn off the Spilt window option, simply click Split again on the View tab.

In our specific example the data set is manageable, however freezing the first column, and the top heading could be useful when scrolling through data.

 

FREEZE PANES 

To keep an area of a worksheet visible while you scroll to another area of the worksheet use Freeze Panes.  Follow the steps below to freeze, based on selection, the first column, and heading row.

 1.  If needed, adjust column widths so all heading names in row 5 are visible.

 2.  Click cell B6 in the table. (By selecting this specific cell, when we apply the freeze pane option,  Excel will freeze the table where the first column ends and the heading row is viewable.)

 3.  Click the View tab.

 4.  Select Freeze Panes, and for the listed options choose Freeze Panes (See Figure 5.10 below). The column and rows will remain visible based on the cell that was selected above.

Mac Users should just click the Freeze Panes button  under the View tab.

 

 

 

Formatting table Data

After reviewing the table, two columns have data that need to be formatted accordingly. In large data sets, it is useful to know data selection short cuts.  In this example, we are going to use keyboard short cuts to select a column of information in the table and apply number formatting.

Format data by following the below steps:

 1.  In the EmployeeData sheet, click cell E6.

 2.  On the keyboard press and hold the CTRL and SHIFT and DOWN keys.

 3.  With the “Years of Service” data selected, click the Home tab. In the Numbers category, format the data as a Number. The number should automatically decrease the decimal to two decimal places.

 Mac Users: click the “list arrow” next to “General,  and then choose “Number” from the list.

 4.  Click in cell J6. (Be sure you have clicked J6 so that you are in the first cell in the Current Salary column). Using the same selection process, select the Current Salary column, and format the data as Currency, zero decimal place.

 5.  Using the non-adjacent selection method, select column headings E, G, and I, and center the data.

 

 

NAMING A TABLE 

Each time a table is created, Excel assigns a default name.  The default naming convention is similar to the way new workbooks are named (Book1, Book2, etc.), however in this case Excel recognizes the area as a table and will assign the name table instead of book: Table1, Table2, Table3, and so on.

Why name a table range? Referring to the table by name rather than by range will make it easier to refer to a table in the future, for example, in a workbook that contains many tables. Seeing tables named Jan or Feb is more informational then seeing Table1 or Table 2. You can custom name each table and in the future connect named tables for reporting purposes.

There are two rules to consider when naming tables. One, Excel does not allow spaces in table names, and two, Excel also requires that table names begin with a letter or underscore.

Follow the next step to assign a custom name to the table.

 1.  Click anywhere in the table and then display the Table Tools Design tab.

 Mac Users: there is no “Table Tools Design” tab in Excel for Mac. Simply click the Table tab and follow steps 2 and 3 below to give the table a new name.

 2.  Click the Table Name text box, in the Properties group.

 3.  Type Employee_DB and then press enter to name the table.

 

ENTERING & DELETING RECORDS 

Tables require constant updating and may need calculations. When your table needs updating you can add/delete data, by adding/deleting rows, or columns. Excel adjusts the table automatically to the new content. The format applied to the banded rows updates to accommodate the new data set size.

When calculations are needed you can create a calculated column or use the built-in Total Row tool.  Excel tables are a fantastic tool for entering formulas efficiently in a calculated column. Excel allows you to enter a single formula in one cell, and then that formula will automatically expand to the rest of the column by itself. There’s no need to use the Fill or Copy commands. This feature can be incredibly time-saving, especially if you have a lot of rows. And the same thing happens when you change a formula; the change will also expand to the rest of the calculated column. The Total Row tool, available on the Table Tools Design tab automatically adds a total row to the bottom of the table. To add a new row, uncheck the Total Row checkbox, add the row, and then recheck the Total Row checkbox. From the total row drop-down, you can select a function, like Average, Count, Count Numbers, Max, Min, Sum, StdDev, Var, and more.

Follow the steps below to update the employee table. You will insert new information just below the table. Data entered in rows or columns adjacent to the table becomes part of the table. Excel will format the new table data automatically.

 1.  Press and hold the Ctrl and End button to move to the last record in the table.

 Mac Users: there is no “End” key on most Mac keyboards. Press and hold the “Command” key and tap the right arrow key. Then press and hold the Command key, again, and tap the down arrow key. That should move to the last record in the table.

 2.  Click tab to start a new record.

 3.  Type the new entries below. Click tab to move to the next column.

As you enter the data, notice that Excel tries to complete your fields based on previous common entries.

REMOVE DUPLICATES 

Duplicate entries may appear in tables. Why? Duplicates sometimes happen when data is entered incorrectly, by more than one person, or from more than one source. The following steps remove duplicate records in the table. In this particular table, Robert Griffin was entered twice by mistake. Delete the duplicate record by following the below steps:

 1.  Click anywhere in the table.

 2.  From the Table Tools Design tab click the Remove Duplicates button.

 Mac Users: Click the Table tab and click the Remove Duplicates button

 3.  The Remove Duplicates dialog box will open.

 4.  If necessary, click the Select All button to deselect all columns.

5.  Click OK to remove duplicate records from the table.

 6.  Excel notifies you that 1 duplicate record was removed.

CREATE NEW COLUMNS

In this next exercise, we will explore how to add two new columns in the table.  Take note, Excel automatically adds the column to the table’s range and copies the format of the existing table heading to the new column heading. The first new column will use the VLOOKUP function to determine what cost of living adjustment (COLA) the employee qualifies for based on the region the employee lives in. The second column added will calculate the projected salary increase based on the COLA. When you use a formula in a table it is considered a calculated column.

A calculated column uses a single formula that adjusts for each row and automatically expands to include additional rows in that column. The formula is immediately extended to those rows. You only need to enter a formula to have it automatically filled down to create a calculated column—there’s no need to use the Fill or Copy commands.

As mentioned in the previous section, Excel assigns a name to the table, and to each column header in the table. When you add formulas to an Excel table, those names can appear automatically as you enter the formula and select the cell references in the table instead of manually entering them.

As a visual reference compare the differences to a formula entered in a cell, compared to in a table:

Excel displaying table and or field names in a formula is called a structured reference. The names in structured references adjust whenever you add or remove data from the table headings. Structured references also appear when you create a formula outside of an Excel table that references table data. The references can make it easier to locate tables in a large workbook. To include structured references in your formula, use point mode method to click the cells you want to reference instead of typing their cell reference in the formula.

Complete the following steps to enter two new columns to determine each employee’s COLA and their projected salaries.

 1.  Click cell K5, and type COLA. Autofit the column width.

 2.  Click cell L5, and type Projected Salary Increase. Autofit the column width.

 3.  Click cell K6.  From the Formulas tab, choose the VLOOKUP function (it is located within the “Lookup and Reference” tool) to look up each employee’s Store location. Matching their store location to the COLA table, located on the COLA sheet, bring over their percentage of increase listed in the second (2) column of the col_index. Note this is an EXACT match, so eliminate all FALSE possibilities in the Range_lookup area:

4.  The Excel table will request you to overwrite all cells in the column with the formula. Click the icon, and choose the Overwrite command as shown below:

 Mac Users: Excel for Mac will automatically fill in the rest of the cells in the column. You do not have to click the icon. Close the Formula Builder pane.

5.  Using point mode method click the table cells to calculate the employees Projected Salary Increase by multiplying the Current Salary by the COLA increase:

=[@[Current Salary]]*[@COLA]

6.  The Excel table will again request you to overwrite all cells in the column with the formula. Click the icon, and choose the Overwrite command.

 Mac Users: You do not have to click the icon. Excel for Mac will auto-fill the rest of the cells in the column.

7.  Format the COLA and Projected Salary Increase columns by selecting K6:K107, and applying the percentage number format, and increase the decimal to one place.   Autofit the column widths.

(Suggestion: Use the short cut selection method; click in K6, press and hold the CTRL and SHIFT and DOWN arrow keys to select the column data.)

 8.  Select L6:L107, and apply the Currency number format.

(Suggestion: Use the short cut selection method; click in L6, press and hold the CTRL and SHIFT and DOWN arrow keys to select the column data.)

 9.  Select L5.  Wrap, and right-align the text, then decrease the column width, and increase the row height to show the contents of the heading row wrapped on two lines.

TOTAL ROW

A useful table tool for data analysis is the Total Row. You can quickly total data in an Excel table by enabling the Total Row option, and then use one of several built-in functions provided in a drop-down list, per column. The Total row, which is added to the end of the table after the last data record can calculate summary statistics, including the average, sum, minimum, and maximum of select fields within the table. The Total row is formatted with values displayed in bold, the double border line option is separating the data records from the Total row.

Apply a Total Row, and follow the below steps to sum three columns of data:

1. Click anywhere in the table and choose the Table Tools Design tab, and click on the Total Row option.

 Mac Users: just click the Table tab and click on the Total Row option

2.  Excel redirects you to the bottom of the table to view the total row, where a SUM defaulted in the Projected Salary Increase column. Click cell J108, select choose the Total Row menu arrow. Choose SUM to total the Current Salary column.

3.  Click cell K108, from the total row menu select Average. The average COLA increase will display.

CENTER ACROSS SELECTION 

Follow the below steps to center the title in cell A1:L2 using the Center Across Selection tool located in the Format Cells dialog box. In prior chapters, we used the ‘Merge & Center’ button to center text across a range. The Merge & Center tool centers the title but removes access to individual cells.  This restriction can present a problem when trying to autofit column widths in a table. The Center Across Selection format centers text across multiple cells but does not merge the selected cell range into one cell making it a better formatting choice when working with tables.

 1.  Select cell A1:L2, and right-click to access the short cut menu.

 Mac Users: hold down CTRL key and click the selected cells to access the short cut menu

 2.  Choose Format Cells.

 3.  In the Format Cells dialogue box, choose the Alignment tab.

 4.  From the Horizontal alignment menu, choose Center Across Selection. Click OK to return to the table.

Attribution

“5.1 Table Basics” by Hallie Puncochar, Portland Community College is licensed under CC BY 4.0

INTERMEDIATE TABLE SKILLS

SORT, FILTER, AND ANALYZE DATA WITH PIVOT TABLES & SUBTOTALS 

SORTING

Sorting is one of the most common tools for data management. By arranging data sequentially the information becomes more meaningful. Arranging records in a specific sequence is called sorting. If you sort by one column this is considered a single sort.  If you need to sort by more than one column, this is considered a custom sort.

The field or fields you select to sort are called sort keys. In Excel, you can sort your table by ascending or descending order. Data in ascending order appears lowest to highest, earliest to most recent, or alphabetically from A to Z.  Data in descending order in arranged by highest to lowest, most recent to earliest, or alphabetically from Z to A.

Excel will sort a range of data that is not in a table.  However, when working with large sets of information it is wise to make the data a table for integrity. Excel locks the row of information creating a record, thus when sorted, the record remains intact, just reorganized.  For example, when you sort the table by last name, all of the records in each row move together. It is always a good idea to save a copy of your worksheet before applying sorts.

There are multiple places you can find and use sorting tools:

• When you first create a table, Excel automatically enables AutoFilter buttons; a tool used to sort, query, and filter the records in a table.  The filter buttons appear to the right of the column headings.  When you click the filter button sorting options appear on the menu options.

• From the Home tab, in the Editing group, click the ‘Sort & Filter’ button, and then click one of the sorting options on the Sort & Filter menu.

• From the Data tab, use the ‘Sort A to Z’ or ‘Sort Z to A’ buttons or for multiple levels select the Sort button to open the Custom Sort dialogue.

• Right-click anywhere in a table and then point to Sort on the shortcut menu to display the Sort sub-menu.

Complete a single level sort by following the steps:

 1.  In the EmployeeID heading, click the filter button.

 2.  Choose to Sort Smallest to Largest.

 Mac Users: Click the A-Z Ascending button

Notice Excel arranges in chronological order all the employee data based on the EmployeeID number, however keeping each record together. You will also notice the filter button now displays an up arrow denoting an ascending sort.

The following steps will sort the records in descending order by Current Salary using the ‘Sort Largest to Smallest’ option form the filter button.

 1.  Click the filter button located in the Current Salary heading.

 2.  Choose Sort Largest to Smallest option from the menu.

 Mac Users: click the “Descending” button

Notice the original sort has been overridden, and the information is now organized based on the largest Current Salary. You will see the small arrow on the EmployeeID filter is gone, and an arrow pointing down for Descending Order is visible on the Current Salary filter button.

 

CUSTOM SORT 

When you need to sort by more than one level, you must use the Custom Sort option. Complete the following steps to organize the data by Store, Last Name, Current Salary, all in Ascending Order (A-Z).

 1.  Select the Data tab, and click the Sort button. Notice the last column sorted by is listed. Change the column heading name by dropping down the Sort by menu and select Store.

 2.  Click Add Level.

 Mac Users: click the + symbol

 3.  Click the down arrow in the Then by section, and choose the column heading names as shown below in Figure 5.29. Note to click Add Level to add the next column heading.  The order you select the headings will determine how the table information is sorted.

 4.  Once you select to Sort by column headings, choose the Order by selecting to sort in ascending order (A-Z) for the Store and Last name fields, and Smallest to Largest, for the Current Salary field.

 5.  Click OK.

Notice the information is now sorted by three levels, per Store, each employee is organized by Last Name, and  Current Salary in ascending order (smallest to largest). Each of the filter buttons indicates the sort with the up arrow.

Seattle

San Diego

Portland

San Francisco

 Mac Users: The steps to create a custom sort list are different for Excel for Mac. Please skip the below steps and follow the alternate steps below Figure 5.34.

Follow the below steps to create a custom list ordering:

1. While clicked in the table, choose the Data tab and click the Sort button.
2. In the Sort by row, click the drop-down menu in the Order Column for the Store heading. Choose Custom List.

3. Click in the List entries: box and type Seattle, and press enter.  Type the remainder of the locations shown in Figure 5.32, pressing enter after each store location typed. Once all locations are entered, click Add. Then choose  Ok.

4. You will see the Order of the Store sort update. Click OK to close the Sort dialogue box.

The custom sort is applied and the table is now sorted by Store, using the custom order, then the Last Name of the employee and then by the Current Salary column.

 Mac Users alternate steps for creating a custom sort list:

1. Click the Excel menu option and choose Preferences 
2. Click on the Custom List button
3. Type the list of cities in the “List entries” box as shown in Figure 5.32 above then click the Add button and close the Custom List dialog box
4. Click anywhere in the table, and then click the Data tab and click the Sort button
5. Click the drop-down menu in the Order Column for the Store heading. Choose Custom List
6. Click on the custom list of cities that you just created and then click the OK button twice
7. The custom sort is applied and the table is now sorted by Store, using the custom order, then the Last Name of the employee and then by the Current Salary column. See Figure 5.34 above.

FILTER DATA 

If your worksheet contains a lot of data, it can be difficult to find information quickly. Applying Filters is an efficient and effective way to only show the information needed.  Typically when filtering you are searching the data for specific information. Generally speaking, you are searching the data based on a question, or in other words, querying the data, and returning only the information that satisfies the question. The process of filtering records based on one or more filter criteria is called a query. Filtering data hides the rows whose values do not match the search criteria.  The information that does not display is not deleted, it is just hidden, and will be redisplayed by removing the filter or applying a new filter.

Like sorting, Filter options are located in the filter button alongside each field name. By clicking the filter button, you can choose which values in that field to display, hiding the rows or records that do not match that value. The filter lets you choose to display only those records that meet specified criteria such as color, number, or text. In this situation, criteria is defined as; a logical rule by which data is tested and chosen.

For example, you can filter the table to display a specific name or item by typing it in a Search box. The name you selected acts as the criterion for filtering the table, which results in Excel displaying only those records that match the criterion. The selected checkboxes indicate which items will appear in the table. By default, all of the items are selected. If you deselect an item from the filter menu, it is removed from the filter criterion. Excel will not display any record that contains the unchecked item. As with the previous sort techniques, you can include more than one column when you filter by clicking a second filter button and making choices. After you filter data, you can copy, find, edit, format, chart, or print the filtered data without rearranging or moving it.

Complete the following steps and filter data according to each query.

How many employees are at a Part-Time (PT) status?

1. Click the filter button on the Job Status column heading.
2. Click Select All, to deselect options.
3. Click the PT box to only display the part-time employees.
4. From the total row, in cell I108, choose the Count function count the number of employees at a PT status.

The answer to the question is there are currently are 11 employees at a PT time status.  The total row will display the part-time total current salaries, and what the projected salary increase for part-time help will be after COLA adjustments.

USING CRITERIA FILTERS 

The filters created are limited to selecting records for fields matching a specific value or set of values. For more general criteria, you can use criteria filters, which are expression involving dates and times, numeric values, and text strings. Excel will identify what criteria filter to display based on the information in the column. For example, you can filter the employee data to show only those employees hired within a specific date range. Notice the criteria filter changes to Date Filters.  If we were looking at the Current Salary column, the filter would be a Numbers Filter.

Using criteria filters, follow the below steps to search for employees who have been with the company for a specific time period.

Identify employees who have been with the company between 2013-2016.

 1.  While clicked in the table, clear any sort or filter applied by clicking the Data tab. In the Sort & Filter group choose the Clear button. 

 2.  Click the Filter button in the Hire Date column. Select Date Filters, and choose the Between criteria.

 Mac Users: uncheck the Select All checkbox before choosing the Between option.

3.  Search for employees with a hire date between 2013, and 2016. In the “is after or equal to” section type 1/01/2013, and typing in the “is before or equal to” section type 12/31/2016. Then click OK.

Mac Users: Excel for Mac sections simply say “After” and “Before”

4.  Sort the filtered table from Oldest to Newest by Date Hired.

5.  In the total row section, count the last name names of the employees by applying the count function in cell B108.

6.  In the total row, select cell I108, and choose None to turn off the count function in the Job Status Column.

Notice the table total row show 47 employees hired between the specified dates. These employees will be evaluated for a COLA adjustment.

Notice the filter button displays a filter symbol and an up arrow indicating the column is filtered and sorted in ascending order.

SLICERS

Another way to filter an Excel table is with slicers. Slicers, generally speaking, are visual filter buttons you can click to filter the table data. Slicers show the current filtered category, which makes it easy to understand what exactly is displayed. For example, a slicer for the Store field would have buttons for the Seattle, San Diego, Portland, and San Francisco locations.

When slicer buttons are selected, the data is filtered to show only those records that match the criteria.  Multiple buttons can be selected at the same time, and a table can have multiple slicers, each linked to a different field. When multiple slicers are used, Excel uses the AND logical operator so filtered records must meet all of the criteria indicated in the slicer. When selecting multiple buttons in a Slicer, use the shift key to select adjacent field names. If the field names are not adjacent, use the non-adjacent selection method,  pressing the CTL button, and selecting the field names needed.

Follow the below steps to filter the table using visual Slicer buttons.

 1.  Click in the table area. From the Data tab, choose Clear to remove the current sort and filter applied to the data.

 2.  To make room for the Slicer buttons at the top of the table, we will add 4 rows between the title and the table area.  Right-click cell A3. Choose Insert. Select Entire Row. Repeat these steps until the table heading starts in row A9.

 Mac users should hold down CTRL key and click cell A3. Then repeat until the table heading starts in row A9.

3.  Click back into the table area. Choose the Insert tab. Click  Slicer. When the Insert Slicers dialogue box opens, click the Store and Job Status field names to display as slicers. Click OK.

4.  Move, and re-size the Slicer boxes to fit in the approximate area of I1:J8 and K1:L8. Make sure the buttons remain visible. Below is a visual example.

5.  From the Store slicer, click the San Diego button. Notice the data filters to only show the data for San Diego. 

6.  From the Job Status slicer click PT. Notice the data filters to only show the data for PT employees in  San Diego.

 7.  Return to the Store slicer and choose Seattle and Portland.  Note the non-adjacent selection method is needed. Select Seattle first, then press and hold the Ctrl button on the keyboard, and then select Portland.
 Mac Users: hold down the Command key not the Ctrl key before you click on Portland.

 8.  Change the Job Status slicer selection to FT.

The table results show there are 61 FT employees in Seattle and Portland.  The Projected Salary Increase after the COLA adjustment for the Northwest region is $150,465.80.

ADVANCED FILTERS 

Filter buttons are limited to combining fields using advanced logic or complex criteria. If the data you want to filter requires complex criteria, you can use the Advanced Filter dialog box. The Advanced Filter works differently from the Filter command in several important ways:

• It displays the Advanced Filter dialog box instead of the AutoFilter menu.
• You type the advanced criteria in a separate criteria range in a worksheet and above the range of cells or table that you want to filter. Excel uses the separate criteria range in the Advanced Filter dialog box as the source for the advanced criteria.

For example, you searched records for employees in the Seattle and San Diego offices AND for employees working at full-time bases, AND have a base salary between the below Salary Ranges:

To run the above complex criteria mentioned above follow the below steps:

1. From the EmployeeData sheet, click in the table, then select the Data tab and clear the current filters by selecting the Clear button.
2. Select the Table Tools Design tab and turn off the Total Row.
    Mac Users: just click the Table tab and turn off the Total Row
3. Select the Advanced Filter sheet. Click cell A10. The criteria mentioned in the above example has already been entered for this advanced filter exercise.  Next, you will use an advanced filter to copy the records that match these criteria.
4. From the Data tab, click the Advanced button. The Advanced Filter dialog box opens.
5. Click the Copy to another location option button to copy matching records from the data range.
6. Click in the List range box to make it active, and then navigate to the EmployeesData sheet, click cell A9, and then press and hold the CTRL and SHIFT and SPACEBAR to select the entire table. In the List range box, you will see Employee_DB[#All] in the list range box.
    Mac Users: The keyboard shortcut of “CTRL, SHIFT, SPACEBAR” does not work in Exel for Mac. You should click in Cell A9, scroll down to the end of the data, hold down the Shift key and click in Cell L112 to select the entire table
7. Click, or press the tab key, to move to the Criteria Range box.
8. From the Advanced Filter sheet, select A6:D8. You will see ‘Advanced Filter’!Criteria populate in the criteria range box.
9. Click, or press the tab key, to move to the Copy to box, and then click cell A10 to specify the location for inserting the copied records. You will see ‘Advanced Filter’!$A$10 in the Copy to criteria range box.

9.  Click OK to copy the records that match the advanced filter criteria. Save your work.

The advanced search results list 7 employees that meet the criteria.  Of these 7 employees, only 1 full-time employee in San Diego has a current salary between $70,000 and $80,000 dollars, and 6 full-time Seattle employees have a current salary between $50,000 and $60,000 dollars.

INSERT TABLE 

 Let’s review another away to turn a range of data into a table.

1. Select the Advanced Filter sheet, and click cell A10.
2. From the Insert tab, choose Table.
3. The Create Table dialogue box will appear.
4. Make sure “My table has headers” is selected so Excel recognizes the column headings.
5. Click OK. Excel turns our advance search data into a table.
6. Sort the table in ascending order (A-Z), by Store, and Employee ID, then Last Name. Hint: Click the Data tab, Click the Sort button, add levels for the three fields.
7. Autofit the column widths and row height to make sure the heading row is visible.
8. Save your work.

Excel turns the information into a table and sorts accordingly:

ANALYZING WORKSHEET DATA

INTRODUCTION TO PIVOT TABLES

Another way to analyze table information is with PivotTables. A PivotTable is a powerful tool that calculates, summarizes, and analyzes table data to compare, patterns, and trends. PivotTables are inserted directly from a table, linking the table data.  Generally speaking, when you pivot on the table data you are reorganizing the table information to reveal different levels of detail that allow you to analyze specific subgroups of information and summarize data quickly and easily without having to change the structure or layout of the original table area.

When you pull table data into a PivotTable there are four main area fields: Rows, Columns, Values, and Filters.  The Rows and Columns fields can interchange quickly to summarize the data in different ways or to run new reports based on the question or criteria being asked. The Value field is data from the table that can be calculated, or that contain values that the PivotTable will summarize. The Values field has multiple settings to choose how you want to calculate the data; SUM, COUNT, AVERAGE, MIN, MAX, and can even show the displayed values as a percentage of the total, column total, grand total, and so on. Lastly, is the Filters area, which restricts the PivotTable to only show the values matching specified criteria.

Four Primary PivotTable Areas:

Figure 5.49 Four Primary PivotTable Areas

In our situation, shown below, we will create a PivotTable to summarize employee data to show Projected Salary Increases, for both Part-Time (PT) and Full -Time (FT) employees for all store locations.

Follow the below steps to explore and build a PivotTable report.

1. Click the EmployeeData sheet. Click anywhere in the table area.
2. From the Insert tab, choose PivotTable.

3.  From the Create PivotTable dialogue box, make sure the PivotTable report will be placed in a New Worksheet, and click OK.

4. Notice a new sheet (Sheet1) is inserted, at the bottom of the workbook, that contains the PivotTable1 area and fields dialogue box.  Rename the default name (Sheet 1) to StorePT.

5.  From the PivotTable pane, drag and drop the Store heading to the Rows section of PivotTable field area.

6. From the PivotTable fields list drag and drop the Projected Salary Increase heading to the Values section.

7.  Drag and Drop the Job Status heading to the Columns field section. Notice the Job Status categories display. In this case, displaying Full-Time (FT) and Part-Time (PT) employees.

FORMATTING PIVOT TABLES

After creating a PivotTable and adding the fields that you want to analyze, you may want to enhance the report to include slicers, or graphs and or format the data to make it easier to read and scan for details. When clicked in the PivotTable area you will see a contextual tab appear on the ribbon, containing PivotTable Tools and two specific tabs; Analyze and Design.  Mac Users: there is not a “PivotTable Tools” tab but you will see two tabs named: PivotTable Analyze and Design. They are only visible when you have clicked inside the PivotTable area.

The Analyze tab contains tools specifically for examining data, for example, the ability to insert Slicers, or PivotCharts. The Design tab contains tools that specifically tie to how the table and data visibly display. For example, when you have a lot of data in your PivotTable, it may help to show banded rows or columns for easy scanning or to highlight important data to make it stand out.

Follow the below steps to add format the PivotTable, and add a PivotChart.

1. Click in the PivotTable. From the PivotTable Tools choose the Design tab.
2. In the PivotTable Styles gallery select the Light Blue, Pivot Style Medium Style  2 format.

3.  To format the PivotTable numbers, select B5: D9. Click the Home tab. Apply the Currency number format and decrease the decimal place to zero decimals.

(The alternative method to number formatting in a PivotTable is to expand the menu on value field; Sum of Projected Salary Increase. Click the Value Field Settings. Choose Number Format and apply the desired number format option.  Mac Users should click the small circle with an “i”  next to “Sum Projected Salary Increase” in the Values section then click the Number button to change the Number Format. )

NOW LET’S CREATE A PIVOTCHART!

4.  Click in the PivotTable. Click the Analyze tab. Choose the PivotChart button on the Ribbon.

5.  From the listed chart types, choose Column.  And select the 3D Clustered Column option. Click OK.

 Mac Users: Only a basic, 2D column chart is available when clicking the Pivot Chart button. In order to select a different chart type, such as the 3D clustered column option, you must do the following:

• • • Click on the 2D chart that was just inserted
    • Click the Design tab on the Ribbon
    • Click the Change Chart Type button
    • Select the 3D Clustered Column option

6.  Move the PivotChart under the PivotTable area. Resize accordingly.  Save your work.

Note the formatting changes in the new chart below. The “Job Status” and “Store” buttons are column and row “filters” for the Pivot Chart.

 Mac Users: Excel for Mac does not insert these formatting changes within a  Pivot Chart. You can add a chart title by clicking the “Add Chart Element” button from the Design tab. It is not possible to add the “chart filter” buttons as shown in Figure 5.59. The filters on the pivot table can be used to also filter the columns and rows in the Pivot Chart.

SUBTOTALS

Another way to summarize data is by using subtotals. Analyzing a large data range usually includes making calculations on the data. You can summarize the data by applying summary functions such as COUNT, SUM, and AVERAGE to the entire organized range of information. Subtotals, in general, are summary functions applied to parts of an organized data range.

For example, you can SUM Current Salaries for employees from each Store location. To subtotal the information the data must first be sorted by the Store field. For subtotals, the field that you sort is referred to as the control field. For example, if you choose the Store location as your control field, all of the Seattle, San Diego, Portland, and San Francisco entries will be grouped together within the data range. The SUM function then can be applied to SUM the Current Salary fields for each Store location. Excel calculates and displays the subtotal each time the Store location changes.

A new row containing a subtotal of that particular location will be inserted, and wherever the field changes a value will display; a subtotal group of records.  Excel updates the subtotal automatically when the control field is changed. In theory, when subtotaling, you are adding a calculation row to the set of data.  Adding rows that total information in the middle of a table would compromise the integrity of data in the table.  The table tools would look at the total as a record, not a calculation. Therefore the Subtotal feature cannot be used in tabling, and can only be applied to a normal range of data. You must convert all tables to a range prior to subtotaling.

Multiple functions can be applied within the same Subtotal.  For example, we will explore how you can SUM Current Salary’s and also provide the AVERAGE Current Salary for each Store location within the same Subtotal. Note Subtotal data can also be filtered.

The best practice when subtotaling is to follow four rules:

Follow the below steps to Subtotal the Employee Data and provide a total Current Salary per Store.

1. Select the Employee Data sheet. If necessary clear any filters applied to the data by clicking the Data tab and choosing the Clear filter option.
2. From the Data tab, choose Sort button. Sort the Store Location, using the preferred Custom List order of Seattle, San Diego, Portland, and San Francisco. If the list we set up previously is not available type the entries in the List entries area. Choose Add, and then OK.

3.  Choose the Table Tools Design tab.  Mac Users: just click the “Table” tab. 

Select “Convert to Range.” Excel will display a message asking if you really want to convert the table back to a normal range.  Choose Yes.

4. Click the Data tab, in the Outline group find and select the Subtotal Command. (Notice the heading row no longer has filters buttons. The data looks like a table but is not a table. The table tools are not active, and the information is a normal range.)

5.  In the Subtotal dialogue box, choose the Store field in the “At each change in.” For the “Use Function,” choose Sum, and only check Current Salary. Click OK.

6. Notice the Current Salary column is totaled, per location. Save your work.

SUBTOTAL OUTLINE VIEW

1. Click level 1. Notice it displays the Grand Total.
2. Click level 2. Notice the totals for all store locations are displayed.

ADDING A SUBTOTAL WITHIN A SUBTOTAL

As mentioned at the beginning of the section, you can use multiple functions within the same subtotal.  We will now explore how you can SUM Current Salary’s and also provide the Average Current Salary for each Store location within the same Subtotal.

1. Click within the Subtotal data, go to the Outline, click Level 3, to display all the subtotal data.
2. From the Data tab, and click Subtotal.
3. In the Subtotal dialogue box, select the Store field for the “At each change in:” option.
4. In the “Use function:” section select to display the Average.
5. Only check the Current Salary field in the “Add subtotal to section:”. (Note Excel will default check something in this area.  Uncheck any other fields.)
6. Uncheck “Replace current subtotals”; we do not want to replace the current subtotal summing the Current Salary.
7. Click OK

8.  Notice each location is now subtotaled showing the Average and Total Current Salary. Excel has also added 4th level to the Outline, accounting for the Averages.  Save your work.

“5.2 Intermediate Table Skills” by Hallie Puncochar, Portland Community College is licensed under CC BY 4.0

Learning Objectives

1. Adjust page settings for printing.
2. Add footer information for user integrity.
3. Preview a worksheet, adjust settings to print in a professional manner.
4. Insert a 3D Model to enhance the visual appearance of a worksheet.

Previewing a Worksheet

Although printing large data sets is uncommon, it is an industry curiosity to set up Excel workbooks to print correctly, and to also add documentation as to when data was revised. Follow the below steps to prepare the worksheets to print.

 

 1.  Click on the AdvancedFilter worksheet. At the bottom of the screen choose the Page Layout option.

 

 

 2.  At the bottom of the page, click into the left section, of the Add Footer panel.

 

 

 3.  From the Header and Footer Design tab, choose to insert the Current Date field.

 

 

 4.  Click in the right panel section, insert the File Name field.

 

 

 

 5.  Click back into the spreadsheet to close the Header and Footer section, and choose the Normal page layout.

 6.  From the File tab, select Print.    Change the Orientation to Landscape. In the Scaling section, choose Fit Sheet on One Page.

 Mac Users: click the “Scale to Fit” option

 7.  Save your work. You don’t have to actually print this sheet. Go back to your worksheet.

Follow the below steps to add a footer to indicate when the last update was made and apply settings to the EmployeeData worksheet to ensure it will print correctly if needed.

 

 1.  Click the EmployeeData worksheet. At the bottom of the screen choose the Page Layout option. You may get a message telling you that Page Layout and Freeze Panes are not compatible. You should click OK to remove the Freeze Panes setting.

 2.  At the bottom of the page, click into the left section, of the Add Footer panel type Revision Date: add a space, then click the  Current Date button from the Ribbon.  Example: Revision Date: 1/01/2020.

 3.   Click in the center panel, add the page number field.

 4.  Click in the right panel section, type Revised by: then type Your Name. Example: Revised by: Jane Doe

 5.  Click back into the spreadsheet to close the Header and Footer section, and choose the Normal page layout.

 6.  From the File tab, select Print.  Change the Margins to Narrow. In the Scaling section, choose Fit All Columns on One Page.

 Mac Users: set the “Scale to Fit” option to 1 page wide by 2 pages tall. 

 7.  Save your work. Again, you do not have to print this sheet. Go back to the worksheet.

 

 

Insert a 3D Model to the worksheet to enhance its appearance. In Excel, you can either insert Pictures, Shapes, Icons, SmartArt,  Screenshots or 3D Models.

 

 

In this example, we will insert (from online) a 3D Model that looks like the Seattle Space Needle.

 1.  Click the Advanced Filter sheet tab, then click the Insert tab on the ribbon.

 2.  Click 3D Models button from the Illustrations group. (If necessary choose From Online Sources or Stock 3D Models.)

 Mac Users: click the 3D Model icon button and then choose “Stock 3D Models…“.      

 3.  In the Search box type Tower, and hit Enter from the keyboard.

 4.  From the results window, choose a model that looks like the Space Needle.  And click Insert. Again,  if the Space Needle is not available in the gallery, click the Back arrow and find an alternate building or tower from the 3D Model “Buildings” category.

 

 

 

5. Notice the model can be manipulated 360 degrees tilted up and down to show a specific feature of the object. Adjust based on your preference.

 

 

 

Attribution

“5.3 Preparing to Print” by Hallie Puncochar, Portland Community College is licensed under CC BY 4.0

Tables for a Retail Company

Download Data File: SC5 Data

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.

 1.  Open the data file SC5 Data and save the file to your computer as SC5 Dynamite Customer Sales.

 2.  Click the Sales Sheet. Format the data set as a table. Choose a style from the styles gallery.

3.  Create the following calculated columns.

a)  In I4, and J4, use a VLOOKUP function that will look up the Product Price and Product Cost based on the Product Sold column which lists an ID number.  [Hint: The Product Table sheet lists the Price and Cost] If any error messages appear, check the Range Lookup.

b)  In K4, use a formula to calculate Profit. Hint: Profit =(Product Price-Product Cost)*Units Sold

4.  Format columns I, J, and K as currency with two decimal places.

5.  Make a copy of the Sales sheet and rename it Online Sales by Date. Place this sheet to the right of the Sales sheet.  Answer the following question by filtering the data accordingly. What was the total Profit for Online Sales?  Sort the results by Date Sold (A/Z).

6.  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. Answer the following question by filtering the data accordingly.  In June, for all products sold, what was the total profit earned?  Sort the results by Country, then Name both in (A/Z).

7.  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. Using a slicer, filter the data accordingly to display the average Product Price, Product Cost, and Profit for DETA100 and DETA200 items.  Sort the filtered sheet by Product Sold. [Hint: In the total row make sure to type the word Average in column A.]

8. Make one final copy of the Sales sheet and rename it Subtotals by Sales Type. Place this new sheet to the right of the Sales by Product sheet. Subtotal the sheet by Sales Type, summing the Profit column.

9.  Add a 2nd subtotal to the Subtotals by Type sheet that subtotals by Sales Type and averages the Profit column.

10.  From the Sales sheet, create the below PivotTable totaling per product type the Product Cost for Online and Retail sales. Insert the PivotTable as a new sheet. Name the sheet ProductPT.

11.  For each worksheet, add a footer with the worksheet name in the center and your name in the right panel.

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

13.  Check the spelling on all of the worksheets and make any necessary changes. Save the SC5 Dynamite Customer Sales workbook, and submit the file as directed by your instructor.

Attribution

“5.5 Scored Assessment” by Hallie Puncochar and Diane Shingledecker, Portland Community College is licensed under CC BY 4.0

Learning Objectives

1. Navigating through a multiple sheet file.
2. Adding, deleting, copying, and moving sheets.
3. Grouping and ungrouping sheets.

Throughout our textbook, we have worked with Excel files that have included multiple sheets. Depending on the version of Excel you are using, a new Excel file may start with one or several sheets. In this chapter, we will be working with a personal budget file that contains income and expenses for an entire year. Our file contains a sheet for each month of the year, as well as, a Summary sheet that will add all twelve monthly sheets of data together. To begin with, we’ll get comfortable with moving through the sheets, organizing them, and making sure that all twelve monthly sheets are consistent.

Figure 6.1 shows the January sheet in the Personal Budget file along with all the sheet tabs along the bottom of the window.

Navigating Through a Multiple Sheet File

Download Data file: CH6 Data

1. Open the data file CH6 Data and save the file to your computer as CH6 Personal Budget. Notice that the file has an Expenses Summary sheet at the far left followed by monthly sheets.

2. Click on the different sheets at the bottom of the screen to move through the sheets. Notice that the Expenses Summary sheet is formatted differently from the monthly sheets. Notice also that all the monthly sheets are identical in layout and format.

3. Take a second look at the months at the end of the year. Not all the data has been entered for September through November… and there is no sheet for December. We will enter the remaining data and add an additional sheet for December.

4. Add the following data in the September, October, and November sheets:

	September	October	November
Power	$135	$135	$135
Water	$30	$30	$30
Groceries	$300	$325	$400
Miscellaneous	$100	$50	$100
Bonus
Freelance	$500		$150
Other		$100

Copying a Sheet

1. To make a December sheet, we are going to copy the November sheet.
2. Point your mouse at the November sheet tab at the bottom of the screen.
3. Hold down your left mouse button and then press and hold down the CTRL key.
    Mac Users: hold down your left button while pointing at the November sheet tab and then press and hold down the Option (alt) key 
4. At this point, you will see a black down-pointing arrow to the left of the November sheet tab and your mouse cursor will become a small piece of paper with a plus sign on it.
5. Drag your mouse to the right (still holding down the left-mouse button and the CTRL key) until the black down-pointing arrow is to the right of the November sheet tab.
6. Let go of the mouse button and then the CTRL key. You should now have a November (2) sheet to the right of the November sheet as shown in Figure 6.2.

Next, we’ll update the November (2) sheet to turn it into our December sheet.

1. Right-click on the November (2) sheet name at the bottom of the screen and choose Rename.
    Mac Users: hold down the Ctrl key and click the November (2) sheet name. Choose Rename
2. Type “December” and press Enter.
   Note for all users: you can also double-click a sheet tab name to rename it.
3. Click on the December sheet.
4. Click on B1 and change “November” to “December”. Be careful not to click on the “Monthly Budget” image.
5. Make the following data changes:
   • Miscellaneous: $300
   • Bonus: $250 (it’s the holidays!)
   • Freelance: delete amount
6. Save your work.
7. Point your mouse at the December sheet tab at the bottom of the screen.
8. Hold down your left mouse button and then press and hold down the CTRL key ( Mac Users hold down Option key)
9. Drag your mouse to the right (still holding down the left-mouse button and the CTRL key) until the black down-pointing arrow is to the right of the December sheet.
10. Let go of the mouse button and then the CTRL key. You should now have a December (2) sheet to the right of the December sheet.
11. Rename the December(2) sheet Practice.

Skill Refresher

Copying a Sheet

1. Point your mouse at the sheet you want to copy at the bottom of the screen.
2. Hold down your left mouse button and then press and hold down the CTRL key ( Mac Users hold down the Option key)
3. Drag your mouse to the right (still holding down the left-mouse button and the CTRL key) until the black down-pointing arrow is to the right of  your existing sheet.
4. Let go of the mouse button and then the CTRL key. You should now have a Sheetname (2) to the right of the original sheet.
5. Rename the Sheetname (2) sheet as desired.

Moving and Deleting Sheets

Sometimes your sheets do not end up in the right order, and you need to move them in order to fix this. Let’s try moving our “Practice” sheet to see how this is done.

1. Point to the Practice sheet and hold down your left mouse button.
2. Notice this time that there is still a black arrow to the left of the Practice sheet, but the piece of paper is blank. It does not have a plus sign (+) because we are moving, instead of copying, the sheet.
3. Left-drag the mouse to the left until the black arrow marker is between the October and November sheets.
4. Release the mouse button.
5. Try moving the Practice sheet back to the right of the December sheet.

Since our Practice sheet is not a sheet we will need in our Budget file, we’ll go ahead and delete it now.

1. Right-click on the Practice sheet tab at the bottom of the screen.
    Mac Users hold down the Ctrl key and click the Practice sheet tab
2. Click Delete. Figure 6.3 shows the warning message box that will appear on your screen.  Your message box might look slightly different depending on the version of Excel you are using. It is important to note that you cannot Undo once you delete a sheet!
   

   

3. Click Delete.

Grouping and Ungrouping Sheets

Take a look at the monthly sheets again. Notice that there is a place in each of these sheets in the range F11:F13 to calculate three pieces of Summary data: Income, Expenses, and Balance; but there aren’t any formulas in these cells. There is also a place for the % of Income Spent (in cell I5), but we will need to add a formula in I6:I7 to calculate this. If we entered these formulas individually in each of the 12 month sheets, it would take a long time! Because this task would be very repetitive, it would also be fairly likely that we would make some mistakes along the way entering the same formulas over and over again. By grouping all the month sheets together, we can enter each of the formulas once and have them appear in all the sheets.

1. Click on the January sheet to make it active.
2. Hold the SHIFT key down and click on the December sheet.

Now all 12 sheets should be selected. You can tell this in two ways: the sheet tabs that have been selected are now bold at the bottom of your screen. Also, notice in the Title bar at the top of the screen the word [Group] or – Group (depending on your version of Excel) added to the end of the title. You can see both of these in Figure 6.4.

IT IS IMPORTANT TO REMEMBER THAT ANY CHANGES WE MAKE TO THE JANUARY SHEET WILL BE MADE TO ALL THE SHEETS!! This is a very good thing when we want to make changes to all the sheets at once, but we need to be sure to ungroup them when we’re done making these changes. Let’s go ahead and add the formulas to all twelve of the sheets at once:

1. Click in F11 in the January grouped sheet.
2. Enter the formula =SUM(F5:F8).
3. In F12, enter the formula =SUM(C5:C13).
4. In F13, subtract Expenses from Income. In the January sheet, your balance should be $690. HINT: if your answer is negative, you subtracted Income from Expenses.
5. Click on I6. (I6 and I7 are formatted and merged together – this is fine.)
6. Enter a formula that divides Expenses (F12) by Income (F11). Your answer will show as a percentage since this cell has already been formatted to do this. HINT: If you percentage is greater than 100%, you have your numbers reversed.

Notice that a data bar was set up in I5 to visually show the income spent. Do you remember how to do this from earlier in our textbook? Your January sheet should now look like Figure 6.5.

7. Now that we are done making changes to all the monthly sheets at once, we need to ungroup them. Right-click on one of the grouped sheets and choose Ungroup Sheets.
 Mac Users hold down Ctrl key click on one of the grouped sheets and choose Ungroup Sheets.

Notice the sheets tabs are no longer bold and the word [Group] is no longer in the title bar.

8. Click on several of the month sheets to see that all the formulas have been added.

9. Click on the December sheet. Your sheet should now look like Figure 6.6.

1. Take a look at the Notes in the September sheet. It says that the rent was raised in September, so we need to cancel our Gym Membership and show $0 for the Gym amount in October, November, and December.
2. Group the October, November, and December sheets. If you do this successfully, these three sheet names should be bold and the word [Group] or – Group will appear in the Title bar.
3. Click on C13 and change the amount to $0. Press Enter.
4. Ungroup the sheets. The balances should be: October $605, November $530, and December $430.

Skill Refresher

To Group Sheets:
Click on the leftmost sheet you want to group; then hold the SHIFT key down and click on the rightmost sheet you want to group.  Same for Excel for Mac.

To Ungroup Sheets:
Right-click on one of the grouped sheets and choose Ungroup Sheets.

 Mac Users hold down Ctrl key click on one of the grouped sheets and choose Ungroup Sheets.

Key Takeaways

• You can easily move, copy, delete, and rename sheets in your Excel file.
• Grouping sheets allows you to change a group of identically formatted sheets at the same time.

Attribution

“6.1 Multiple Sheet Basics” by Mary Schatz and Diane Shingledecker, Portland Community College is licensed under CC BY 4.0

Let’s try adding up all the monthly amounts in our Expenses Summary sheet:

1. Click in C5 in the Expenses Summary sheet.
2. Type =SUM(. (Make sure to type the open parentheses!)
3. Click on the January sheet.
4. Hold the SHIFT key down and click on the December sheet.
5. Click on C5 again and press ENTER. Cell C5 should display the sum amount of $8,400.
6. Click on C5 in the Expenses Summary sheet. In the formula bar, you should see the following formula: =SUM(January:December!C5). This formula tells Excel to SUM cell C5 in the sheets January through December.
7. Let’s try another 3-D SUM together. Click on C6.
8. Type =SUM(. (Make sure to type the open parentheses!)
9. Click on the January sheet.
10. Hold the SHIFT key down and click on the December sheet.
11. Click on C6 again and press ENTER. Cell C6 should now display the sum amount of $1,610.
12. Click on C6 in the Expenses Summary sheet. In the formula bar, you should see the following formula: =SUM(January:December!C6).

If you feel comfortable with these 3-D formulas, you can copy C6 down through C13 to fill in the rest of the formulas. If you’re not quite comfortable yet, keep practicing the above steps to add 3-D formulas to cells C7:C13. When you’re done, your Expenses Summary sheet should match Figure 6.7.

While our 3-D formulas are complete in the Expenses Summary sheet, our summary feels like it is lacking something. Let’s add a visual representation of our summary numbers to the sheet.

1. Highlight cells B5:C13 in the Expenses Summary sheet.
2. Click on Pie Chart in the Insert tab in the ribbon and select the 2-D pie.
3. Move and resize the pie chart so that it fills cells D3:J15.
4. Delete the chart title.
5. Move the legend to the right side of the chart. Resize the legend as needed.
6. Add percentage data labels to the pie slices. Format the data labels to be bold with white font color. Your complete Expenses Summary sheet should look like Figure 6.8 below.
7. Check the spelling on all of the worksheets and make any necessary changes.
8. Save your file. If you’re printing your assignment at this point, print ONLY the Summary sheet in regular and formula view. Close your file.

Attribution

“6.2 Formulas with 3-D References” by Mary Schatz and Diane Shingledecker, Portland Community College is licensed under CC BY 4.0

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 in 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. Depending on your version of Excel or Office 365, template categories may include: Business, Personal, Planners and Trackers, Lists, Budgets, Charts, and Calendars. In this chapter we will explore using existing Microsoft templates.

To access the templates in Excel, do the following:

1. Click the File tab in the ribbon.
2. Click New in Backstage View.  Mac Users: Click the File menu option and choose New from Template
3. The top of your screen should look similar to Figure 6.9. If you have opened templates previously, these may show in this screen as well.

In the Search for online templates box shown above, you can type a description for the template you want to use. Let’s start by searching for a Travel Expense Report.

1. Click in the Search for online templates box.
2. Type Travel Expense and press ENTER.
3. Click on the Travel Expense Report that looks like Figure 6.10 below and click Create.  NOTE:  If this template is not available, select a similar one.

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

Try using this template by doing the following:

1. Change the Name to your name.
2. Change the Department to Technology.
3. Hold down CTRL and click ~  to see where the formulas are in the sheet.  Working in formula view helps you see where the formulas are, so you won’t delete them.
4. In formula view, carefully delete just the data, including the Description of Expense and the dollar amounts. Don’t delete any formulas!
5. Hold down CTRL and click ~ again to return to Normal view.
6. Enter Dates, Descriptions 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.

We will practice using one more Excel template to create a class schedule. If you already know your schedule for next term, you can use it to complete this template. Otherwise, use your current class schedule.

1. Click the File tab in the ribbon.
2. Click New in Backstage View.  Mac Users: Click the File menu option and choose New from Template
3. Click in the Search for online templates box.
4. Type Class Schedule and press Enter.
5. Click on the Student schedule that looks like Figure 6.11 below and click Create.  NOTE:  If this template is not available, select a similar one.
   

   

6. Click on the Class List sheet. Replace the information in the Class, ID, Day, Location, Start Time and End Time columns with your own class schedule. In the Day column, use the dropdown arrow in the lower right corner of the cell to select the day of the week.
7. Click back on the Class Schedule sheet to view your completed schedule. Click in cell H2, then click the dropdown arrow in the lower right corner and select 60 MIN for the Time Interval.
8. Save the completed file as CH6 Class Schedule. Close the file.

Attribution

“6.3 Templates” by Mary Schatz and Diane Shingledecker, Portland Community College is licensed under CC BY 4.0

Attribution

“6.4 Preparing to Print” by Mary Schatz and Julie Romey, Portland Community College is licensed under CC BY 4.0