How to Create Scorecard in Excel (with Detailed Steps)

A scorecard is a powerful tool by which you can determine and visualize your company’s financial, customer, internal business, learning, and growth. It is a convenient tool for many companies. Now, if you are looking forward to creating a scorecard for your own company, you have come to the right place. In this article, I will show you all the steps to create a scorecard in Excel.

How to Create Scorecard in Excel: with Easy Steps

Say, you have a weekly sales report for 8 employees. Now, you want to create a scorecard from their sales report. Follow the steps below carefully to achieve your desired result.

In this article, we have used the Microsoft 365 version of Microsoft Excel. But, you can use any other version available to you. If you face any problems regarding versions, please feel free to comment below.

📌 Step 1: Visualize Weekly Sales Scorecard with Conditional Formatting

First, you need to create the weekly sales scorecard from the sales report.

• To do this, first and foremost, select C6:F13 cells >> go to the Home tab >> Conditional Formatting tool >> Icon Sets option >> choose the first option from the Directional group.

• Now, upon this selection, go to the Home tab >> Conditional Formatting tool >> Manage Rules option.

• As a result, the Conditional Formatting Rules Manager window will appear.
• Subsequently, select the Icon Set rule >> click on the Edit Rule… button.

• At this time, the Edit Formatting Rule window will appear.
• Following, choose both types as Number in the Type option from the Icon group.
• For the green icon, write 4001 in the Value box.
• Subsequently, write 3501 in the Value box for the yellow icon.
• Finally, click on the OK button.

• At this time, the Conditional Formatting Rules Manager window will appear again. Afterward, click on the OK button.

As a result, you will see that your weekly sales scorecard has been created successfully. And, it would look like this.

📌 Step 2: Create a Total Sales Scorecard Column Using Data Bars

Now, you might want to make a total sales scorecard with bars.

• In order to do this, select the G6:G13 cells >> go to the Home tab >> Conditional Formatting tool >> Data Bars option >> the first option from the Solid Fill option.

Consequently, the data bars will be set in the total sales column. And, the outcome would look like this.

📌 Step 3: Create Dashboard to Record Sales Performance Trend with Excel Sparklines Option

Now, you might need to portray the dashboard to record sales performance trends.

• To do this, at the very beginning, create a new column named Dashboard.

• Now, click on the H6 cell >> go to Insert tab >> Sparklines group >> Line tool.

• As a result, the Create Sparklines window will appear.
• At the Data Range: box, write C6:F6. Subsequently, click on the OK button.

• As a result, the trendline for John will appear in the H6 cell.

• Afterward, click on the H6 cell >> go to the Sparkline tab >> tick the options High Point and Low Point.

• Now, the high and low points are visible in the trendline.
• Following, place your cursor in the bottom right position of the H6 cell.
• Subsequently, a black fill handle will appear. Now, drag it down to copy the trend.

Finally, all employees’ trendlines are visible and the whole scorecard is ready. The final outcome should look like this.

Download Practice Workbook

You can download our practice workbook from here for free!

Conclusion

In a nutshell, in this article, I have shown you all the steps to create a scorecard in Excel. Read the full article carefully and practice accordingly. I hope you find this article helpful and informative. You are very welcome to comment here if you have any further questions or recommendations.

<< Go Back to Scoring | Formula List | Learn Excel