Exercise Excel 3-2:
Move, Insert, Format, Style
|What you will do:
Format cells directly
Format with cell styles
Create a chart
Print whole workbook
soccer budget2.xls (created in
You will practice moving, inserting, and formatting by making changes to
soccer budget.xls, which you previously
created in Exercise 2-2. You will include new columns for the calculations that were used to get certain budget amounts.
- From your Class disk open soccer budget.xls in the
folder to Sheet1.
Save to your
Class disk in the excel project3 folder with the name
- Move: Move the Income cells, including the Totals, to be above the Expenses cells. [Hint: Cut and Insert Cells.]
There should be a blank row between the Totals and Expenses rows. Move
the column labels from below Expenses to above Income.
- Insert: Between the columns Category and Budget, insert 2 columns.
Label the columns Budget Quantity and Budget Cost each and wrap the
text. These will be used to calculate the Budget column.
Below the Trophies row insert a blank row for a second type of trophy.
Data: Use the numbers in the illustration on the right to fill in the data in
the new columns.
- Repair Budget formulas: In the Budget column, replace the values,
wherever values exist in both Budget Quantity and Budget Cost each,
with a formula. [Multiply the Budget Quantity by the Budget Cost
each.] For Trophies, add together the cost of the two types of trophies
for the Budget column.
- Format: Cells A1, A2,and A3 use the font Impact and are merged and
centered across the table. Font size for A1 = 22, A2 and A3 = 18. Make
all of the row labels in Column A Bold.
- Cell Style: Create
a cell style for the column labels named Label - white on red: Fill =
Dark Red, Font Color = White, Font = Arial, Font
Size = 10, Centered, Bold, Wrap Text. Apply this style to the column
labels in row 5. Widen the columns as necessary.
- Cell Style: Create another cell style for the Totals rows named Totals.
(Select cell D10 to base the style on and the current number format
will be retained.) Use Font = Arial, Font Size = 10, Bold, Font Color =
Blue, Fill Color = Gray 25%. Apply the style to the table cells in the
Totals rows and also to the cells containing the words Income and
- Revise Over/Under Formulas: The last column is supposed to show how the
actual income and expenses compare to the budgeted amounts. Being "over
budget" for expenses is a bad thing. But being "over budget" for income
is good since you earned more money than expected. This is confusing!
You need a different approach.
Change the column label to read Difference = Actual - Budget.
Change all the formulas in column F to =Actual - Budget (Yes, you can
really use column labels in your formulas!) If you lose styles, reapply the style.
Error?: If you see #NAME? in the cell instead of a
number, your Excel is not set to allow using labels in formulas. Change
this by choosing Tools | Options and, on the Calculations tab, check
the box for "Accept labels in formulas".
- Grand Totals: Leave a blank row below the expense
Totals row and create a Grand Totals row below it. Type Grand Totals:
in column A of the new row. In cells columns E and D write formulas that subtract the
Expenses value from the Income value in that column. In column F use
the same formula as in the rest of Column F, =Actual - Budget. Format
the Grand Totals row with the Totals style and border it with a dark wide border.
- Rename Sheet: Change the name of Sheet1 to Soccer Budget and of Sheet2
to Budget Charts.
- Charts: On the sheet Budget Charts, there is an extra square
in the Legend because you added a row for the second type of trophy.
Change the Source Data, reselecting the labels cells and the values
cells and omitting the blanks that are causing the problem. [Hint: Hold
CTRL down while you select the cells.] Copy the existing pie chart and
paste it below on the same sheet. Change the Chart Type of the copy to
Column. Do not show
the legend. Format the chart area with the Fill Effect texture Blue
tissue paper. [Format Chart Area | Pattern | Fill Effects | Texture. If
the texture named is not available, choose another one.] If necessary,
change the font size for the axis labels and resize the chart area to
make the chart easy to read.
- Prepare to print: Edit the header for both sheets to show Exercise
Excel 3-2. Spell Check. Page Break Preview. Print Preview. (Each sheet
should fit on one page) Fix any problems.
Print the Entire workbook.
Print formulas: Show formulas and print the Soccer Budget sheet
Landscape orientation on a single page.
- When all printing is complete, close the workbook without saving