Table of Contents Understanding DashboardsUnderstanding Sales DashboardsUnderstanding KPI’sUnderstanding Key Sales Metrics for your Sales Dashboard Excel1. Sales Growth2. Sales Target3. Customer Acquisition Cost (CAC)4. Average Revenue Per Unit (ARPU)5. Customer Lifetime Value (LTV)6. Customer Churn Rate7. Lead Conversion Ratio8. Sales Cycle Length9. Upsell Rate10. Cross-Sell RateSteps to Set up a Sales Dashboard ExcelStep 1: Load your Data Into ExcelStep 2: Set up your Sales Dashboard Excel FileStep 3: Create a Table with Raw DataStep 4: Figure out Metrics and VisualizationsStep 5: Build a Sales Dashboard ExcelStep 6: Customize the Sales Dashboard ExcelStep 7: Adding AnimationsConclusionFrequently Asked Questions Try Hevo for Free Share Share To LinkedIn Share To Facebook Share To X Copy Link 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. Table of Contents Understanding DashboardsUnderstanding Sales DashboardsUnderstanding KPI’sUnderstanding Key Sales Metrics for your Sales Dashboard Excel1. Sales Growth2. Sales Target3. Customer Acquisition Cost (CAC)4. Average Revenue Per Unit (ARPU)5. Customer Lifetime Value (LTV)6. Customer Churn Rate7. Lead Conversion Ratio8. Sales Cycle Length9. Upsell Rate10. Cross-Sell RateSteps to Set up a Sales Dashboard ExcelStep 1: Load your Data Into ExcelStep 2: Set up your Sales Dashboard Excel FileStep 3: Create a Table with Raw DataStep 4: Figure out Metrics and VisualizationsStep 5: Build a Sales Dashboard ExcelStep 6: Customize the Sales Dashboard ExcelStep 7: Adding AnimationsConclusionFrequently Asked Questions 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. Simplify your Data Analysis with Hevo’s No-code Data Pipelines Struggling to migrate your Sales, Marketing, and Customer data? Hevo makes it a breeze with its user-friendly, no-code platform. Here’s how we simplify the process: Seamlessly pull data from over 150+ other sources with ease. Utilize drag-and-drop and custom Python script features to transform your data. Efficiently migrate data to a data warehouse, ensuring it’s ready for insightful analysis. You can see it for yourselves by looking at our 2000+ happy customers, such as Airmeet, Cure.Fit, and Pelago. Get Started with Hevo for Free 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. Image Source: Self 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. Solve your data replication problems with Hevo’s reliable, no-code, automated pipelines with 150+ connectors.Get your free trial right away! 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 Image Source: Smartsheet 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. Integrate Google Sheets to BigQueryGet a DemoTry itIntegrate HubSpot to DatabricksGet a DemoTry itIntegrate Salesforce to Azure Synapse AnalyticsGet a DemoTry it 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 1. What are the 7 steps to create a dashboard in Excel? a) Define the Purpose and Audienceb) Gather and Organize Datac) Create a Data Modeld) Design the Dashboard Layoute) Build the Dashboardf) Add Interactivityg) Review, Test, and Share 2. Is Excel good for dashboards? Yes, Excel is a good tool for creating dashboards, especially for small to medium-sized datasets. 3. What should be included in an Excel dashboard? Yes, Excel is a good tool for creating dashboards, especially for small to medium-sized datasets. Easha Meher Research Analyst, Hevo Data Easha is a programming enthusiast with 2+ years of experience. She has worked in automation test script creation, regression testing, and integration projects like Thyrocare Integration. She has a bachelor's degree in Computer Science and loves writing technical articles about data engineering. Her goal is to help people solve everyday problems through her work. × Achieve a 360° View with the Power of a Modern Data Stack Download Now Liked the content? Share it with your connections. Share To LinkedIn Share To Facebook Share To X Copy Link Related Articles Building Sales Dashboard: 14 Critical Metrics to Include Dynamic Dashboard in Google Sheets: 3 Easy Steps How to Connect Excel to Google Sheets using 4 Easy Methods: Steps Explained 16 Best Tableau Sales Dashboards Setting Up Power BI Sales Dashboard: 8 Easy Steps