Formating the Balanced Scorcard in Excel
To get started in creating a colorful presentation for your balanced scorecard in Excel, begin by formatting the title, headers, columns, and rows. The following steps will help you create this sample template in Excel 2003, but you can also make adjustments to the font, color or style to reflect your own preferences.
1. Begin by opening a new workbook in Excel
2. Under File, Page Layout, select Landscape
3. Select the Rows and Columns for the scorecard and under Format set the font to Arial, 12 point, bold, and centered
4. Create a Title for the Scoreboard
Type the Title in cell A1 without any alignment (center, justified, etc.)
- Select and highlight cells A1 through I1
- Click on the Merge and Center button in the Format toolbar
- Set the font to Arial 18 point bold
- Fill-in the Title cells with a yellow background by selecting Format, Cells and then Patterns and choosing the lightest yellow choice
5. Label and Format the Columns
- Label the columns: Objective, Measures, Target, Actual, Initiative Target #, Actual #, Target %, Actual %.
- Fill-in the cells with a yellow background by selecting Format, Cells, Patterns, and choosing the lightest yellow choice.
- Change the font color of the text for Target, Target #, and Target % to Green and Actual, Actual #, and Actual % to Red.
6. Label and Format the Rows
Alternate the format of each row (This alternating format will come in automatically even when you add additional rows).
- Highlight all the rows to be included in the Scorecard
- Under Format select Conditional Formatting
- Choose "Formula is" under Condition 1
- Type in =MOD(ROW(),2)=0
- Click on Format and choose under Pattern the light orange/tan color
- Click Okay
Select and label the four perspectives for the scoreboard by typing and changing the color pattern as follows:
- Financial (Green),
- Customer Satisfaction (Blue)
- Internal Processes (Purple)
- Learning and Growth (Pink)