[vip_students] Excel Tutorial:Lesson 27Updating Formula Data
- From: "Paul Traynor" <paul.traynor@xxxxxxx>
- To: <vip_students@xxxxxxxxxxxxx>
- Date: Sun, 24 Jun 2007 20:36:35 +0100
Updating Formula Data
Note: There is an excel attached file in this email. Save this attachment to
your documents folder as you need it to complete the exercise.
Introduction. Spreadsheet programs, like Excel, are popular because they
allow you to quickly and easily change your data. This ability to change
data enables you to compare outcomes or update your calculations. By using
cell references in a formula rather than specific numbers, you can quickly
change the cell data. Excel automatically recalculates a formula when data
in a referenced cell changes.
When changing or updating data, first review all of the cells where you
expect a change to occur. Be sure to note the current figures. Then, enter
the new data in the appropriate cell or cells. When you are finished
entering the new data, read the cells again to note the new figure.
Exercise Objectives. In the following two exercises, you will compare the
results caused by a change to the figure in cell B5. First, you will review
the current data in cells B5 and B7. Then, you will type new data in cell B5
and review the data again to note the new figures.
Exercise: Reviewing the Current Data
Complete the following six steps to review the first quarter service figure
in cell B5. Because a change to this figure affects the quarterly total in
cell B7, you will check it as well.
Step 1: Press Ctrl-Home to move the selection cursor to cell A1. Then, move
to cell A5 by pressing the Down Arrow key until JFW announces, "Sales A5."
All sales figures appear in row 5.
Step 2: Press the Down Arrow key to move to cell A6. JFW announces,
"Service A6."
All service figures appear in row 6.
Step 3: Press the Down Arrow key to move to cell A7. JFW announces, "Total
A7."
All totals appear in row 7.
Step 4: Move to cell B5 to review the first quarter sales figure. JFW
announces, "3000 B5."
Step 5: Determine the row and column labels for cell B5 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.
Step 6: Move to cell B7 by pressing the Down Arrow key until JFW announces,
"8000 Has Formula B7." Press Ctrl-F2 to read the edit field on the Formula
Bar. JFW announces, "=SUM(B5:B6)."
The current first quarter revenue in cell B7 is 8000. Note that this figure
is derived from a formula that includes data from cell B5.
You should have determined that:
1. Cell B5 contains the current first quarter sales figure. That figure is
3000;
2. Cell B7 contains the current first quarter total figure. That figure is
8000;
3. Any change to cell B5 will cause a change in cell B7. (Note: Formulas in
cells F5 (total sales) and F7 (revenue totals) reference cells B5 and B7.
So, a change to the data in cell B5 will cause a change to the figures in
cells F5 and F7 as well.)
Exercise: Changing the First Quarter Sales Data
Complete the following three steps to move to cell B5 and change the data.
Step 1: Press Insert-C to verify cell B7 is selected. Then, press the Up
Arrow key until B5 is selected. JFW announces, "3000 B5."
Step 2: In cell B5, type 4000. Then, press Enter to accept the change. JFW
announces, "5000 B6."
Excel accepts the information and moves the focus to cell B6.
When you enter new data into cell B5, Excel automatically recalculates the
total in cell B7. (It also recalculates totals in cells F5 and F7 because
these cells contain formulas that use data in cells B5 and B7.)
Step 3: Press the Down Arrow key to move to cell B7. JFW announces, "9000
Has Formula B7." Note that the current first quarter total revenue in cell
B7 is now 9000.
Tip: You can press Alt-4 and Alt-Ctrl-1 to read the row and column
headings.
What You Learned. In this topic, you learned:
You can easily change worksheet data to compare or update your calculations.
By using cell references in a formula rather than specific numbers, you can
change the data quickly. Excel automatically recalculates a formula when
data in a referenced cell changes.
When changing data, review the cells that will be affected by the update.
Note the current figures. Then, make the changes to your data. When you are
finished, review the cells again to note the new figures.
********************************************************************
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:
