If the Formula Bar isn’t already open, click Formula Bar on the View menu.

Click cell B5 to activate it. On the Standard toolbar, click the AutoSum arrow.

The AutoSum list displays several simple functions. You can quickly create a formula that uses one of these functions by clicking the function and then verifying the data range Excel suggests that you base your calculation on.

Press the Escape key to collapse the AutoSum list. Then click the AutoSum button.

Excel inserts a formula that uses the SUM() function to total the numbers immediately above the active cell. The cell references appear in the formula in blue text, and the corresponding cells are indicated in the worksheet by a shimmering blue outline.

The AutoSum functions default to the closest logical data range. You can change the cells referenced in the formula by dragging to select other cells, or by changing the cell references.

Press Return to accept the default formula.

The formula calculates the 1^st Quarter Total of sales in Region 1. The result, $154,026, appears in cell B5, and cell B6 becomes the active cell.

Click cell B5, and then point to the fill handle in the lower-right corner of the active cell. When the pointer changes to a black plus sign, drag to the right through cell E5. When you release the mouse button, the 1st Quarter Totals for Regions 2, 3, and 4 appear.

Click cell E5, and verify that the formula in the Formula Bar correctly calculates the 1^st Quarter Total for Region 4 by adding cells E2 through E4.

Drag to select cells B5:E5. Then on the Standard toolbar, click the Copy button.

Excel copies the formulas in the selected cells to the Clipboard, and indicates the copied content with a shimmering blue outline.

Click cell B10 (the 2^nd Quarter Total for Region 1) and then, on the Standard toolbar, click the Paste button (or press Command+V).

The 2^nd Quarter Totals for the four regions appear in cells B10:E10.

Repeat step 8 two times, clicking cell B15 the first time, and cell B20 the second time. Then press the Escape key to release the selection.

The four quarterly totals for the four regions are complete.

Next, we’ll calculate the annual sales for each region by adding the quarterly totals.

Click cell B21, and then type = (an equal sign) to indicate the beginning of a formula.

Click cell B5.

A reference to the selected cell appears in the formula, in blue text, and a blue box outlines the cell.

Type + (a plus sign), click cell B10, type +, click cell B15, type +, and then click cell B20.

As you select each cell, a reference to the cell appears in the formula in a color different than the previous references, and a box of the same color outlines the cell.

By identifying each cell reference with a unique color, Excel makes it easier for you to locate cells referenced within a formula.

Press Return to complete the formula.

The Annual Total of sales for Region 1 appears in cell B21.

Fill the formula from cell B21 to cells C21:E21.

The formula results are bold, because bold formatting was applied to row 21 in the original worksheet.

Next, we’ll calculate the percentage of the total annual sales represented by each region, by dividing each region’s annual sales by the total sales for all regions.

Click cell B22, and then click the AutoSum button.

Excel suggests a formula that sums only cell B21.

Drag to select cells B21:E21.

The formula changes to reflect your selection.

In the Formula Bar, click to position the insertion point after the equal sign.

The cell range and surrounding parentheses, which were until this time black in the Formula Bar, become blue to indicate that you’re actively working in the range. The insertion point is blinking in the Formula Bar. A non-blinking insertion point is visible in the formula in cell B22.

With the insertion point blinking after the equal sign, click cell B21, and then type / (a forward slash, used in formulas to indicate division).

In the Formula Bar and in the worksheet, the reference to cell B21 becomes blue and the reference to the cell range B21:E21 becomes green.

Press Return to complete the formula.

The result is expressed in decimal notation.

Click cell B22. Then on the Formatting toolbar, click the Percent Style button.

The number changes to a percentage, indicating that Region 1 sales were 24 percent of the total sales for the year.

Before filling the formula to the adjacent cells, we need to change the cell range reference from a relative reference to an absolute reference, so that the referenced cell range stays the same when we fill the formula.

In the Formula Bar, drag to select the cell range reference B21:E21 (the green text). Then press Command+T to change the relative reference to an absolute reference, $B$21:$E$21.

Press Return to complete the formula. Then fill the formula from cell B22 to cells C22:E22.

The results show that this year’s sales were quite evenly spread between the four regions.

Next, we’ll compare the division of sales this year to the division of sales last year. We’ll subtract last year’s sales percentages from this year’s sales percentages, so that an increase in sales is expressed as a positive number and a decrease as a negative number.

Click cell B23, and then type = (an equal sign). Press the Up Arrow key to enter a reference to cell B22, and then type – (a minus sign).

At the bottom of the workbook window, click the Last Year sheet tab. On the Last Year worksheet, click cell B15, which displays the percentage of sales by Region 1 last year.

The cell in which you’re building the formula isn’t visible, but the formula is shown in the Formula Bar.

Press Return to complete the formula.

Excel returns to the This Year worksheet and displays the formula result, which shows that Region 1 captured a lower percentage of sales this year than last year.

Copy the formula from cell B23 to cells C23:E23.

The results of your calculations show that Region 4 made the biggest improvement this year.