Microsoft Office Tutorials and References

In Depth Information

**Working with Worksheets**

Problem Solving: Writing Effective Formulas

You can use formulas to quickly perform calculations and solve problems. First, identify the

problem you need to solve. Then, gather the data needed to solve the problem. Finally,

create accurate and effective formulas that use the data to answer or resolve the problem.

Follow these guidelines:

•
Keep formulas simple.
Use functions in place of long, complex formulas whenever

possible. For example, use the SUM function instead of entering a formula that adds individual

cells, which makes it easier to confirm that the formula is making an accurate calculation

as it provides answers needed to evaluate the problem.

•
Do not hide data values within formulas.
The worksheet displays formula results, not the

actual formula. For example, to calculate a 5% interest rate on a currency value in cell A5,

you could enter the formula =0.05*A5. However, this doesn’t show how the value is

calculated. A better approach places the value 0.05 in a cell accompanied by a descriptive label

and uses the cell reference in the formula. If you place 0.05 in cell A6, the formula =A6*A5

would calculate the interest value. Other people can then easily see the interest rate as

well as the resulting interest, ensuring that the formula is solving the right problem.

•
Break up formulas to show intermediate results.
When a worksheet contains complex

computations, other people can more easily comprehend how the formula results are

calculated when different parts of the formula are distinguished. For example, the formula

=SUM(A1:A10)/SUM(B1:B10) calculates the ratio of two sums, but hides the two sum

values. Instead, enter each SUM function in a separate cell, such as cells A11 and B11, and

use the formula =A11/B11 to calculate the ratio. Other people can see both sums and the

value of their ratio in the worksheet and better understand the final result, which makes

it more likely that the best problem resolution will be selected.

Finding a solution to a problem requires accurate data and analysis. With workbooks,

this means using formulas that are easy to understand, clearly show the data being used

in the calculations, and demonstrate how the results are calculated. Only then can you be

confident that you are choosing the best problem resolution.

Working with Worksheets

You’ve seen that new workbooks are created with three initial worksheets labeled Sheet1,

Sheet2, and Sheet3. If needed, you can add new worksheets to a workbook and remove

unused sheets. You can also give worksheets more descriptive and meaningful names.

Inserting or Deleting a Worksheet

When you insert a new worksheet in a workbook, the new sheet is named based on the

number and names of the other sheets in the workbook. For example, if a workbook

has four worksheets named Sheet1 through Sheet4, the next inserted worksheet is named

Sheet5. You’ll add a new, blank worksheet to the RipCity Digital Orders workbook.

To insert a new worksheet

to the left of the active

sheet, right-click a sheet

tab, click Insert on the

shortcut menu, select a

sheet type, and then click

the OK button.

To insert a new worksheet:

◗

1.
Locate the three sheet tabs in the lower-left corner of the worksheet window.

◗

2.
To the right of the Sheet3 sheet tab, click the
Insert Worksheet
tab . A new

worksheet named Sheet4 appears at the end of the workbook and is active.

The workbook now includes two empty worksheets: Sheet3 and Sheet4. Because you

don’t plan to use these sheets, it’s a good idea to remove them.