How to Create an Interactive Sales Dashboard in Excel: Step-by-Step Explanation

Any organization primarily measures its growth in terms of Sales. An increment in Sales can be attributed to more customers, revenue, and more profits. Earlier the Sales data was tracked and analyzed manually but now analyzing Sales has become more complex due to huge amounts of varying and dynamic data. Organizations are looking for real-time Sales information and mechanisms to decrease their Sales analysis time.

Sales can help an organization identify the best & worst-performing assets and help them make better business decisions that will drive future Sales even higher. A Sales Dashboard Excel enables organizations to visualize their real-time Sales data and boost productivity. In this article, you will learn about the various key Sales metrics you should keep in mind while creating your Sales Dashboard depending on your use-case while setting up a Sales Dashboard Excel.

Understanding Dashboards

A Dashboard is a very useful tool that brings together all the data in the form of charts, graphs, histograms, statistics, and many more visualizations which lead to data-driven decision-making. Dashboards contain actionable data. By looking at this data an organization can make key business decisions on the go.

Many tools help organizations build their Dashboards. For example tools like Power BI, Tableau, Excel help organizations build Dashboards suiting to their needs. Depending on the outcome you need, you can build Dashboards for Sales, Marketing, Engineering, and many more departments to track the performance. In this article, we will focus on the Sales Dashboard Excel.

Understanding Sales Dashboards

A Sales Dashboard is a visual representation of your organization’s data. You need to put a lot of thought into making a Sales Dashboard as it will affect all your future decisions. Sales Dashboards are used by executives, managers, or any employee in an organization and they should be designed in such a way that different employees can make use of them.

Before building a Sales Dashboard ask yourself the following questions:

• Why do you need the Dashboard and what kind of outcome are you looking for?
• Who are the employees that benefit from the Sales Dashboard?
• What key metrics are you going to visualize to get the best out of your Sales Dashboard?
• How are you going to integrate data into the Dashboard?
• How old is the Sales data in your Dashboard?
• What kind of template do you need to use for your Sales Dashboard? 

Understanding KPI’s

KPI stands for Key Performance Indicators. These are very essential in order to create an efficient and useful Sales Dashboard Excel. KPI’s measure your progress toward a certain goal in a certain amount of time. Let’s assume that your goal is to achieve 100 leads in one month but you end up achieving 56 leads so now you may define a lead attainment KPI as achieved leads/ goal leads(56/100). This way you can define your own KPI’s.

Once you visualize your KPI if it is not satisfactory then you can try identifying the factors that lead to a low KPI and you can make the changes accordingly. Further, you will get to know the most commonly used KPI’s used for creating a Sales Dashboard Excel. 

Take a look at the essential data quality metrics and KPIs before setting up your Sales Dashboard Excel.

Understanding Key Sales Metrics for your Sales Dashboard Excel

Key Sales metrics also known as Sales KPI’s are very crucial to create the right kind of Sales Dashboard Excel that meets the needs of your organization. You can also define your own KPI’s to monitor your goals but for now, let’s talk about the 7 Sales KPI’s that are commonly monitored on the Sales Dashboard Excel:

• Sales Growth
• Sales Target
• Customer Acquisition Cost (CAC)
• Average Revenue Per Unit (ARPU)
• Customer Lifetime Value (LTV) 
• Customer Churn Rate
• Lead Conversion Ratio
• Sales Cycle Length
• Upsell Rate
• Cross-Sell Rate

1. Sales Growth

This KPI measures the overall Sales growth. You have to fix a period of time (ex. 2 years, 5 years, etc) and calculate the percentage of change in Sales from the last Sales period to the current sales period. An increase in this indicates that your product is doing well and a decrease indicates that you need to look for issues that are leading to a decrease in sales.

2. Sales Target

This KPI measures the percentage of Sales Revenue against Target Revenue. 

If the percentage is low you have to look further into which division/ asset is generating the least revenue and take measures to increase the revenue from these.

3. Customer Acquisition Cost (CAC)

Customer Acquisition Cost (CAC) is the total revenue spent by a company to convert them into customers. This includes money spent on marketing, personnel, discounts, and anything which has a role in acquiring the customer. This is an important measure because you should be able to recover this cost over a period of time when you convert them into customers. If you are unable to recover this amount then you have to cut back on expenditures in the acquisition.

4. Average Revenue Per Unit (ARPU)

This KPI is your organization’s average revenue. To calculate the Average Revenue Per Unit (ARPU) divide your whole revenue by the number of customers. It is basically the average revenue a customer brings to your organization. If ARPU is less than Customer Acquisition Cost (CAC) you may run into troubles and may face a loss so it is important to keep tracking this metric.

5. Customer Lifetime Value (LTV)

To calculate this KPI first calculate the Average Customer Lifetime (total lifetime of all the customers/ number of customers). Depending on this now calculate how much revenue can be generated during this average lifetime which is the Customer Lifetime Value(CLV). You can use this to set your CAC which is less than CLV to ensure profits.

6. Customer Churn Rate

A business can grow as long as it retains old customers and gains new ones. Customer Churn Rate gives the count of the customers that left your business over a period of time. It is important to maintain a low churn rate because a high churn rate ensures loss of revenue. So it is very important to retain existing customers.

7. Lead Conversion Ratio

Leads are potential customers of your organization. A person becomes a lead when he/she visits your website. So if you can track the leads and promote your product you will observe an increase in sales. Set up a target ratio and try to reach it and keep monitoring this ratio.

8. Sales Cycle Length

It is important to include your Sales Cycle length in your Sales Dashboard because keeping track of metrics from time to time is efficient. Initially, the whole process of converting the leads into customers takes a long time. Companies aim towards decreasing their Sales Cycle length. Also, as the company becomes more acclaimed, the conversion into customers becomes easy which reduces the Sales Cycle length.

9. Upsell Rate

Upsell rate talks about the number of customers that purchased a product or accessory related to their previous purchase. A good Upsell rate implies that your products are satisfactory to your customer and he/she is inclined to purchase related to your company.

10. Cross-Sell Rate

Cross-sell rate is similar to Upsell Rate. The only difference is that you pitch different products from their previous purchase to your customers and this rate tells you how many people are willing to buy other products. A good Cross-sell rate is an indicator that all the products in your company are being loved by your customers. Make sure to include both Upsell and Cross-sell Rates in your Sales Dashboard Excel.

Steps to Set up a Sales Dashboard Excel

After you identify all the KPI’s and understand what you want to do with your Dashboard follow the below-given steps to create a Sales Dashboard Excel:

• Step 1: Load your Data Into Excel
• Step 2: Set up your Sales Dashboard Excel File
• Step 3: Create a Table With Raw Data
• Step 4: Figure out Metrics and Visualizations
• Step 5: Build a Sales Dashboard Excel
• Step 6: Customize the Sales Dashboard Excel
• Step 7: Adding Animation

Step 1: Load your Data Into Excel

Since you are creating a Sales Dashboard in Excel you must load your data into Excel first. There are ETL tools that can help you achieve this. ETL (Extract Transform Load) extracts data from a source and loads them into a destination. There are also some ODBC connectors that help move your data from your apps to Excel.

Step 2: Set up your Sales Dashboard Excel File

Once your data is available in Excel open a new Excel Workbook & create two tabs/ sheets. One is Sales Dashboard and the other is for Raw Data (here you can hide your raw data). Look at the image below to understand.

Step 3: Create a Table with Raw Data

The Raw Data tab is where you load your data using an ETL tool. Make sure that there is only one item in each cell. Also, for numerical columns in the table, you can add all the items present in the column by clicking on the empty cell at the end of the column and type the =SUM formula.

Now drag your mouse from the first item of your cell to the last item. Now type a closed bracket. This will look some like:

Step 4: Figure out Metrics and Visualizations

Once you figure out the metrics you can use Excel tools to create visualizations for those metrics. These visualizations include pivot tables, auto shapes, charts, names ranges, Marcos, etc. All these visualizations are very simple to make and an Excel Dashboard can easily be set up.

Step 5: Build a Sales Dashboard Excel

Go to your Sales Dashboard Excel sheet. In the Dashboard sheet, select Insert from the top left corner of the window. Now you will be able to see a lot of different kinds of charts offered by Excel. Choose the chart of your choice. Now the chart will appear on the screen. Right-click with your mouse on the chart and add the data sources, names and labels. After making the changes click on the Ok button. 

Step 6: Customize the Sales Dashboard Excel

Now you can customize your Sales Dashboard Excel and make it look attractive by adding colours and changing the fonts. The more attractive it is, the more you will want to work with your Dashboard. You will learn about:

• Adding Color to Elements
• Adding Color to Background
• Adding Title to Charts and Dashboard

6.1. Adding Color to Elements

To customize your Sales Dashboard Excel click on the element of the chart that you want to add colour to. Now go to the Home tab and select Font where you can find a paint bucket symbol. Click on it and change the colour. Also in the Font option, you can change the font if it is a text element.

6.2. Adding Color to Background

To fill the background of a chart with colour, use your mouse to right-click on the chart and select Format Chart. In the list that appears choose Fill. Now choose Solid Fill. Go to the Font section and you can see a bucket. Click on the bucket and choose a background colour of your choice.

6.3. Adding Title to Charts and Dashboard

To add your chart title, click on a chart title (this is a random name already created by default by Excel) and click on Font group. Now you can select your font type, size, and colour of your title.

To add a title to your Sales Dashboard Excel, put your cursor in the upper-right cell and right-click on it, and select Insert and click on Entire Row. Do this repeatedly until you have space to add a title. Then, select a couple of cells in the first empty row, and in the Alignment group, click Merge and Center. After all this, you’ll have enough space to add your Dashboard title.

Step 7: Adding Animations

Interactive Dashboards are fun to work with. Excel offers some interesting dynamic elements. Let’s talk about these elements:

• Slicers
• Drop-Down List
• Macros

7.1. Slicers

Slicers can be used to modify tables and pivot tables. Slicers have buttons that facilitate this feature. So they filter data and display what kind of data is being filtered. Read more about creating Slicers in the official documentation.

7.2. Drop-Down List

Drop-down lists are some of the common dynamic elements everybody knows about. It makes any page looks less bulky, neat and categorized. Drop-down lists are used to limit user inputs to acceptable ones.

7.3. Macros

Codes have become popular because anyone doesn’t want to repeat a set of tasks again and again. Similarly if in your Sales Dashboard Excel you want to perform a task repeatedly you can do it by using Marcos. This feature records your set of actions and then performs them without your intervention. Read more about creating Marcos in the official documentation.

Conclusion

Keeping track of all your Sales metrics is essential for boosting Sales and identifying underlying problems. Every metric in your Sales Dashboard Excel will help you understand more about your company’s progress and it also allows you to make quick and accurate business decisions. 

If you are looking for sample templates for Tableau Sales Dashboards you can find here and if you are planning to set up Sales Dashboards in Power BI you can find the guide.

Integrating and analyzing your Sales data from a huge set of diverse sources can be challenging, this is where Hevo comes into the picture. 

Sign up for a 14-day free trial and simplify your data integration process. Check out the pricing details to understand which plan fulfills all your business needs.

Frequently Asked Questions