How to Create Scorecard in Excel (with Detailed Steps)

Get FREE Advanced Excel Exercises with Solutions!

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.


Watch Video – Create 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.

Sales Report Data to Create Scorecard in Excel

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.

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 the 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


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Tanjim Reza
Tanjim Reza

Md. Tanjim Reza Tanim, a BUET graduate in Naval Architecture & Marine Engineering, contributed over one and a half years to the ExcelDemy project. As an Excel & VBA Content Developer, he authored 100+ articles and, as Team Leader, reviewed 150+ articles. Tanim, leading research, ensures top-notch content on MS Excel features, formulas, solutions, tips, and tricks. His expertise spans Microsoft Office Suites, Automating Finance Templates, VBA, Python, and Developing Excel Applications, showcasing a multifaceted commitment to the... Read Full Bio

3 Comments
  1. Thanks so much for all that you do to help to make us all more proficient at MS EXCEL!

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo