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.