Microsoft Office Tutorials and References

In Depth Information

**Creating Formulas to Subtract Values**

To calculate the monthly and regional totals for 2012:

◗

1.
Select the range
K6:K19
, and then click the
Copy
button in the Clipboard

group on the Home tab. The cell contents are copied to the Clipboard.

◗

2.
Click cell
K21
, and then click the
Paste
button . Excel inserts formulas to

calculate the 2012 monthly sales totals into the range K21:K34.

◗

3.
Select the range
B19:J19
, and then click the
Copy
button

.

◗

4.
Click cell
B34
, and then click the
Paste
button . Excel inserts formulas to

calculate the 2012 regional sales totals into the range B34:J34. See Figure 2-26.

Figure 2-26

Monthly and regional sales totals for 2012

text and formulas

to copy

monthly sales totals

regional sales totals

Creating Formulas to Subtract Values

Tom also wants you to calculate the change in sales from one year to another, broken

down by region and month. You’ll use the copy and paste features to quickly add this

third table to the Monthly Sales worksheet. Then you will add formulas to calculate the

increase in units sold between 2011 and 2012.

To calculate the change in sales from one year to the next:

◗

1.
In cell A36, enter the label
Net Increase
.

◗

2.
Copy the range
B21:K21
, and then paste it into the range
B36:K36
.

◗

3.
Copy the range
B22:B34
, and then paste it into the range
B37:B49
.

◗

4.
In cell C37, enter the formula
=C22–C7
. After you press the Enter key, the

value 64 appears in the cell; this is the increase in January sales for region R01.

◗

5.
Copy cell
C37
, and then paste it into the range
C37:K49
. The formula =C22–C7 is

copied and pasted into the range of cells with the remaining months and regions.

◗

6.
Click cell
A36
to deselect the range. Figure 2-27 shows the completed table.