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.


Download Practice Workbook

You can download our practice workbook from here for free!


Steps to Create a Scorecard in Excel

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.

Sales Report Data to Create Scorecard in Excel

In this article, we have used the Office 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.

Use Conditional Formatting to Create Weekly Sales Scorecard in Excel

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

Choose Manage Rules to Set Condition Properly

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

Click on Edit Rule Button to Edit the Rule

  • 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.

Set Rule to Create Scorecard in Excel

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

Finalize the Conditional Formatting

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

Weekly Sales Scorecard in Excel


📌 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 Home tab >> Conditional Formatting tool >> Data Bars option >> the first option from the Solid Fill option.

Set Data Bars to Create Scorecard in Excel

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

Total Sales Scorecard with Bars


📌 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.

Create New Column Named Dashboard

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

Add Trendline to Create Scorecard in Excel

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

Set Data Range to Create Trendline

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

Trendline for John

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

Active High Point and Low Point in Trendlines

  • 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.

Drag Fill Handle Downward to Repeat Trendline

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

Created Scorecard in Excel


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.

And, visit ExcelDemy to learn about many more Excel problem solutions, tips, and tricks. Thank you!

Tanjim Reza

Tanjim Reza

Hello! I am Md. Tanjim Reza Tanim. I have just completed my B.Sc from Naval Architecture & Marine Engineering Department, BUET. Currently, I am working as an Excel & VBA content developer. I always had a great fascination with Microsoft Excel and its cool functions and formulas. Here, I am learning every day about new functions and formulas and working on applying MS Excel to the analysis of our real-life problems. I have great enthusiasm for learning any kind of new things, writing articles, and solving real-life problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo