[vip_students] Excel Tutorial:Lesson 26,Using the AutoSum and AutoCalculate Features
- From: "NCBI Technical Support" <paul.traynor@xxxxxxx>
- To: <vip_students@xxxxxxxxxxxxx>
- Date: Mon, 14 May 2007 08:34:23 +0100
Using the AutoSum and AutoCalculate Features
Introduction. Because finding the sum of a range of cells is a very common
task, Excel includes two features (AutoSum and AutoCalculate) that allow you
to find sums quickly.
About AutoSum. The AutoSum command causes Excel to check the cells above or
to the left of the current cell for a series of cells containing numbers. If
either the cells above or to the left of the current cell contain a series
of numbers, Excel places a dotted selection cursor around those cells. Then,
it automatically enters a formula that sums the numbers in that cell range.
If you use the AutoSum command in a cell that intersects a column of numbers
and a row of numbers, the command will sum the numbers in the column.
If text appears in a cell within the range you want to sum, the AutoSum
command will only sum numbers in the cells up to the cell containing text.
About AutoCalculate. Whenever you select a series of cells that contain
numbers, Excel's AutoCalculate feature sums the numbers and displays the sum
in the status bar. To find the sum for selected cells, read the status bar.
The sum does not appear anywhere in the worksheet.
Exercise Objectives. In the following two exercises, you will use the
AutoSum command to find the total quarterly and yearly revenues. You will
also use the AutoCalculate command to determine a sum quickly.
Exercise: Using the AutoSum Command
Complete the following twenty steps to find the total quarterly and yearly
revenues.
Step 1: Move to cell A4 by pressing Ctrl-G to open the Go To dialog box.
Type A4 in the Reference field, and then press Enter.
Excel moves to cell A4. JFW announces, "Revenue A4."
Step 2: Press the Right Arrow key to move to cell B4. JFW announces,
"Quarter 1 B4." Then, press the Down Arrow key three times to move to cell
B7. Note the first quarter sales and service revenue figures.
Tip: You can press Alt-Ctrl-1 to read the contents of the first cell in the
current row. (You can use this keystroke to read the row label for this data
because the label is in the first cell of the row.)
Step 3: Press Insert-C to verify cell B7 is selected. Then, press Alt-Equal
Sign to activate the AutoSum command. JFW announces, "Type in the range to
sum using colon to separate, or press Enter to accept default range. B5:B6."
When you activate the AutoSum command, Excel looks above or to the left of
the current cell for a series of cells that contain numbers. It then places
a dotted selection cursor around those cells.
In this instance, Excel placed a dotted selection cursor around cells B5 and
B6. Then, it inserted in cell B7 the formula to add the figures in cells B5
and B6.
Step 4: To verify the formula, press Insert-Up Arrow. JFW announces,
"=Sum(B5:B6)," indicating Excel automatically inserted the correct formula
in cell B7.
Step 5: Press Enter to accept the formula in the cell.
Excel sums the data in cells B5 and B6, displays the result in cell B7 and
moves the selection cursor to cell B8.
Step 6: Press the Up Arrow key to return to cell B7 and read the first
quarter revenues. JFW announces, "8000 Has Formula B7." Then, press
Ctrl-F2 to read the formula in cell B7. JFW announces, "=SUM(B5:B6)."
Step 7: Press the Right Arrow key to move to cell C7. JFW announces, "Blank
C7."
Tip: Press Alt-4 to read the column label in the fourth cell of the current
column. Press Alt-Ctrl-1 to read the label in the first cell in the current
row.
Step 8: Press Alt-Equal Sign to activate the AutoSum command. JFW
announces, "Type in the range to sum using colon to separate, or press Enter
to accept default range. C5:C6."
When you activate the AutoSum command, Excel places a dotted selection
cursor around cells C5 and C6. Then, it inserts in cell C7 the formula to
add the figures in cell C5 and C6.
Step 9: Press Insert-Up Arrow to verify the formula. JFW announces,
"=SUM(C5:C6)," indicating Excel automatically inserted the correct formula
in cell C7. Press Enter to accept the formula in cell C7.
Excel displays the result in cell C7 and moves the selection cursor to cell
C8. JFW announces, "Blank C8."
Step 10: Press the Up Arrow key to move to cell C7 and read the second
quarter revenues. JFW announces, "11000 Has Formula C7." Then, press
Ctrl-F2 to read the formula in this cell. JFW announces, "=SUM(C5:C6)."
Step 11: Press the Right Arrow key to move to cell D7. JFW announces,
"Blank D7." Then, press Alt-Equal Sign to activate the AutoSum command.
Excel places a dotted selection cursor around cells D5 and D6. Then, it
inserts the formula to add the figures in cell D5 and D6 in cell D7.
Step 12: Press Insert-Up Arrow to verify the formula. Then, press Enter to
accept the formula.
Excel displays the result in cell D7 and moves the selection cursor to cell
D8.
Step 13: Press the Up Arrow key to move to cell D7 and read the third
quarter revenues. JFW announces, "13000 Has Formula D7." Then, press
Ctrl-F2 to read the formula in this cell. JFW announces, "=SUM(D5:D6)."
Step 14: Press the Right Arrow key to move to cell E7. JFW announces,
"Blank E7." Then, press Alt-Equal Sign to activate the AutoSum command.
Step 15: Press Insert-Up Arrow to verify the formula. Then, press Enter to
accept the information in the cell.
Excel displays the result in cell E7 and moves the selection cursor to cell
E8.
Step 16: Press the Up Arrow key to move to cell E7 and read the total
revenues. JFW announces, "16000 Has Formula E7." Then, press Ctrl-F2 to
read the formula in this cell. JFW announces, "=SUM(E5:E6)."
Step 17: Press the Right Arrow key to move to cell F7. JFW announces,
"Blank F7." Then, press Alt-Equal Sign to activate the AutoSum command.
Tip: Press Alt-4 to read the fourth cell in the current column. Press
Alt-Ctrl-1 to read the first cell in the current row.
Step 18: Press Insert-Up Arrow to verify the formula. JFW announces,
"=SUM(F5:F6)," indicating Excel automatically inserted the correct formula
in cell F7.
Note: Cell F7 intersects a row and column of numbers. When you use the
AutoSum command in a cell that intersects a column of numbers and a row of
numbers, the command will sum the numbers in the column.
Step 19: Press Enter to accept the formula in cell F7.
Excel displays the result in cell F7 and moves the selection cursor to cell
F8.
Step 20: Press the Up Arrow key to move to cell F7 and read the total
revenues. JFW announces, "48000 Has Formula F7." Then, press Ctrl-F2 to
read the formula in this cell. JFW announces, "=SUM(F5:F6)."
Exercise: Using the AutoCalculate Feature
Complete the following eight steps to use the AutoCalculate feature.
Step 1: Move to cell B5 by pressing Ctrl-G to open the Go To dialog box.
Type B5 in the Reference field, and then press Enter. JFW announces, "3000
B5."
Step 2: Press the Numpad 5 key to read cell contents. JFW announces,
"3000."
Step 3: Determine the row and column labels for this cell by pressing Alt-4
to read the fourth cell in the current column. Then, press Alt-Ctrl-1 to
read the first cell in the current row.
You will find the sum of the first and second quarter sales revenues.
Step 4: Press Shift-Right Arrow to include cell C5 in the selection. JFW
announces, "Select 5000 C5."
When you select another cell that contains numerical data, Excel
automatically calculates the sum of the data in the selected cells and
displays the sum in the status bar.
Step 5: Press Shift-Insert-Down Arrow to verify cells B5 and C5 are
selected. JFW announces, "Selected Range B5 Through C5 3000 5000."
Step 6: Press Insert-Page Down to read the status bar. JFW announces,
"Ready Sum=8000."
Reminder. Excel does not insert the sum in your worksheet. It only displays
the sum for the selected cells on the status bar.
You can find the sum for non-adjacent cells as well. Next, you will find the
sum of the second quarter revenues and total fourth quarter revenues. Cell
C7 contains the second quarter revenues and cell E7 contains the fourth
quarter revenues.
Step 7: Press Ctrl-G to open the Go To dialog box. Type C7, E7 in the
Reference field, and then press Enter to select these cells. JFW announces,
"Two Areas C7 E7."
The sum for the figures in cells C7 and E7 appears in the status bar.
Step 8: Press Insert-Page Down to read the sum. JFW announces, "Ready
Sum=27000."
What You Learned. In this topic, you learned:
The AutoSum and AutoCalculate features allow you to find the sum for
selected cells quickly.
The AutoSum command directs Excel to check the cells above or to the left of
the current cell for a series of cells containing numbers. If either the
cells above or to the left of the current cell contain a series of numbers,
Excel places a dotted selection cursor around those cells. Then, it will
automatically enter a formula that sums the numbers in that cell range.
If you use the AutoSum command in a cell that intersects a column of numbers
and a row of numbers, the command will sum the numbers in the column.
If text appears in a cell within the range you want to sum, the AutoSum
command will only sum numbers up to the cell containing text.
Whenever you select a series of cells that contain numbers, Excel's
AutoCalculate feature sums the numbers and displays the sum in the status
bar. AutoCalculate allows you to find the sum without entering that data in
your worksheet.
********************************************************************
NOTICE: The information contained in this email and any attachments
is confidential and may be privileged. If you are not the intended
recipient you should not use, disclose, distribute or copy any of
the content of it or of any attachment; you are requested to notify
the sender immediately of your receipt of the email and then to
delete it and any attachments from your system.
NCBI endeavours to ensure that emails and any attachments generated
by its staff are free from viruses or other contaminants. However,
it cannot accept any responsibility for any such which are
transmitted. We therefore recommend you scan all attachments.
Please note that the statements and views expressed in this email
and any attachments are those of the author and do not necessarily
represent the views of NCBI
********************************************************************
Other related posts:
- » [vip_students] Excel Tutorial:Lesson 26,Using the AutoSum and AutoCalculate Features
