Microsoft Office Tutorials and References
In Depth Information
Formatting Calculated Values
Using Color to Enhance a Workbook
When used wisely, color can enhance any workbook. However, when used improperly,
color can distract the user, making the workbook more difficult to read. As you format a
workbook, keep in mind the following tips:
• Use colors from the same theme within a workbook to maintain a consistent look and
feel across the worksheets. If the built-in themes do not fit your needs, you can create
a custom theme.
• Use colors to differentiate types of cell content and to direct users where to enter
data. For example, format a worksheet so that formula results appear in cells without a
fill color and users enter data in cells with a light gray fill color.
• Avoid garish color combinations that can annoy the reader and be difficult to read.
• Print the workbook on both color and black-and-white printers to ensure that the
printed copy is readable in both versions.
• Understand your printer’s limitations and features. Colors that look good on your
monitor might not look as good when printed.
• Be sensitive to your audience. About 8 percent of all men and 0.5 percent of all
women have some type of color blindness and might not be able to see the text when
certain color combinations are used. Red-green color blindness is the most common,
so avoid using red text on a green background or green text on a red background.
Formatting Calculated Values
When you format numeric values, the goal is to make the workbook easier for the
reader to interpret (which is same goal of any formatting you apply to a workbook). For
example, adding a comma as a thousands separator, controlling the number of decimal
places, and using percentage and currency symbols can make a large table of numbers
easier to read and understand.
For Tom’s report, you’ll format the values in the worksheets that contain the sales
ﬁ gures for the past two years.
Creating Formulas to Add, Subtract, and Divide Values
The Yearly Sales worksheet contains the annual sales ﬁ gures from 2011 and 2012 for the
X310 heart rate monitor. The upper section of the worksheet displays the number of units
sold in each sales region per year, and the lower section displays the revenue generated
by sales region per year in dollars. You will add formulas to the worksheet to calculate
the total sales for each year as well as the net and percent change in sales from one year
To calculate the total yearly sales:
1. Click the Yearly Sales sheet tab. The Yearly Sales worksheet is now the active
sheet in the workbook.
2. In cells B15 and B26, enter the label Total .
3. Select the range C15:D15 . You’ll insert the SUM function in both cells at once.
4. Click the Home tab on the Ribbon.
5. In the Editing group, click the Sum button . The formulas inserted in these
cells, =SUM(C7:C14) in cell C15 and =SUM(D7:D14) in cell D15, calculate the total
units sold for 2011 and 2012, which are 30136 and 31174, respectively.