How to Analyse Sales Performance in Excel (2023)

Post Guide

Heading 2 Example

Heading 3 Example

Heading 2 Example

Heading 3 Example

Reading Time:
minutes remaining

Are you ready to turn your sales performance around?

If so, you're in the right place.

We'll show you how to do sales data analysis using Excel.

Excel is a powerful tool that you can use to analyze data in many different ways. However, it's important to remember that Excel is only as powerful as the data entered. In other words, you need good data to produce good results.

When it comes to sales performance, there are many key metrics that you should track. These include things like sales volume, sales mix, and sales per employee. By tracking these metrics, you'll see where your company is performing well and where there is room for improvement.

(Video) Visualization of Sales Data in Excel (without Charts) | Excel Analytics | Sales Analysis

So, without further ado, let's get started!

RELATED POSTBest Excel Templates for Project Management and Tracking

What is Sales Data Analysis?

Data is used in sales analysis to assess the salesforce's success.

It offers insightful information on the best- and worst-performing goods and services, as well as selling and market opportunities, and incorporates sales forecasts.

Regular sales data analysis helps you understand the goods that your consumers are purchasing and enables you to analyze why they are acting in particular ways.

Your lead drop offs and conversions can both reveal patterns.

You may streamline your sales process by monitoring all of these factors and taking appropriate action.

Definition of Sales Data

Although "sales data" encompasses a wide range of indicators, everything you can measure about the sales process generally qualifies as useful sales data.

You can collect this data with the aid of modern software, such as Cloud CRM solutions, but it's crucial to learn how to interpret it to know what it means for your company and where you can improve.

It can be challenging to decide where to concentrate your efforts with such a broad definition of sales data, — particularly if you also need to spend time selling.

Should you keep an eye on how long deals spend on average in your pipeline or how well your automated email campaign performs? Would studying successful meeting rates help your sales forecast more?

With the correct sales analysis tools, you'll have access to a lot of data, identify trends, lead your team, and produce more accurate sales projections and objectives for the rest of the company.

Understanding which metrics to use and when is the trick.

Here are some key sales metrics you can apply on your sales data analysis journey.

  • Win rate
  • Average deal size
  • Average time to close
  • Number of deals in the pipeline
  • Sales revenue
  • Sale forecast accuracy
  • The ratio of sales made to new vs. existing customers
  • Success percentages by lead generation types
  • Lead response time
  • Follow up time
  • Time Spent selling
  • Sales pipeline coverage
  • Leads generated

With these metrics, you can make positive changes to your sales strategy and will have a better understanding of your data and sales pipeline to support your reports.

Using Excel for Sales Data Analysis

Excel is the most adaptable tool now utilized in business. This application has been around since the 1980s and is now the most crucial data structure and analysis tool used by professionals worldwide.

Every employee in practically every department imaginable, including IT, Finance, HR, and Marketing, relies on Excel as a vital resource.

Aside from its role in data storage and organization, over time, it evolved into a tool for simple data calculations. With several updates, it is now regarded as an entryway into the analytics field.

Although large enterprises no longer use spreadsheets for large data collection, spreadsheets are still used for routine activities.

In its most basic form, each cell in Excel includes data points.

Raw data exports, sales dates, SKUs, and units sold are added (or imported) into a spreadsheet to make viewing and organizing easier.

You will organize unstructured data in an effective Excel spreadsheet in a way that makes it easier to get actionable insights.

You may create fields and functions in Excel that carry out calculations with more complex data.

Segmented data can be studied and seen in greater detail without extra tools, even for larger data sets.

Establish departmental budgets or hypothetical profit margins.

Although it cannot produce a complete data product alone, it can create easy-to-read graphics and accurate computations.

How to Conduct Sales Analytics with Excel

Let's talk about the well-known Microsoft Excel features and capabilities that sales professionals frequently use for data analysis in Excel.

Conditional Formatting

The Conditional Formatting function allows you to highlight the cell with a distinctive color, data bars, color scales or icon sets, based on the cell value or formula rules.

Duplicate data and anomalies can also be highlighted.

What-if Evaluation

What If analysis makes it possible to explore a wide range of options with different scenarios involving values or equations.

The What If options let you start with the goal and work backwards to calculate a single input value or the best result based on defined constraints.

(Video) Sales Data Analysis using Excel | Pivot Tables | FMCG Industry

Pivot Tables

You can use pivot tables to pinpoint important data from a large dataset. It is the best technique for information analysis.

You can insert a pivot table, change the summary calculation, sort the fields, or filter them.

Other options include:

  • Group Pivot Table Items
  • Multi-level Pivot Table
  • Frequency Distribution
  • Pivot Chart, Slicers
  • Update Pivot Table
  • Calculated Field/Item
  • Get-Pivot-Data formulas.
  • Diagrams


Since a chart can present information in various ways and is very simple, it is more useful than a data sheet.

Every aspect of a chart can be customised.

Here are the different charts available:

  • Column Charts and Bar Charts
  • Line Charts and Area Charts
  • Combo Charts
  • Pie Chart
  • Scatter Plots and Bubble Charts
  • Sunburst Charts and Treemaps
  • Histograms
  • Waterfall Charts
  • Surface Charts and Radar Charts
  • Funnel Charts
  • Stock Charts
  • Sparklines

Not all data is created equal in sales analysis. Some pieces of information are more important than others. Imagine you were curious about your average profit per sale.

The average profit margin of each product, weighted by the quantity sold, would need to be determined.

Excel doesn't have a built-in formula for weighted averaging, but the following will get the right result with your data sets in columns A and B:

=SUMPRODUCT(A1:A10, B1:B10)/SUM (B1:B10).

Sort and Filter

The two Excel functions that are most frequently used on tabular data are sorting and filtering.

Filtering allows the user to filter the main dataset by one or more columns to present a reduced but much more focused data subset.

Sorting can be applied to one or more columns in a specified sequence to present the data in a more meaningful way. Common ways of sorting are numerically, alphabetically, by date, by font color or cell color or by a custom order defined by the user.

These additional tool may enhance sorting and filtering functionality:

  • Standard Filter and Advanced Filters
  • Data Forms
  • Outlining (Grouping) Data
  • Subtotal Tool

Trend Spotting

Simple graphics can sometimes make your point far more quickly than intricate statistical analyses.

For example, a simple line chart or sparkline will show the trend of your data effectively. Add a trend line or error bar to complete the picture.


Lookup functions allow data to be retrieved from another table based on a unique id such as product code, contractor # or supplier id.

This helps to keep the main table lean rather than cluttering it with repetitive data, and provides a versatile and effective way to manage each dataset in only one place.

Lookup functions let you connect and compile important data from various sources within the business or from external providers.

Excel and Complex Sales Data Analysis

Can we use Excel to analyze complex sales data?

Predictive analytics is possible in Excel with the aid of plugins.

Excel add-ons will centralize your complicated business formulas and computations from several systems in one spreadsheet, view, or graph for complex data analysis.

You can compare, measure, and evaluate complicated data to make wise business decisions when all your data is centrally located and available in one place.

For example, an eCommerce company sells its products on several online platforms globally.

Each nation has various target markets, marketing platforms, payment systems, stocks, and logistical channels. As a result, organizations must employ various devices and software to do each task.

For an easy profit calculation:

Sales - Expenses = profits or losses

The sales information will come from eCommerce websites and the fees associated with marketing on websites like Facebook Ads and Google AdWords.

Expenses are found in accounting software like Xero or FreshBooks.

Additional costs like stock purchases may originate from inventory management platforms like Olabi.

Each location will often have a different data silo.

Therefore, you must independently import all of this data for each of these locations from various platforms into Excel, evaluate all of this data along with the spending data, and then compute profits.

(Video) Sales Dashboard in Excel | Dynamic Excel Dashboard for Sales

It takes time and money.

There is typically a lag in the analysis, which decreases the quality and usefulness of the research because the data needs to be evaluated in real-time.

As a result, it becomes important to use a data pipeline to aggregate all the data into a data warehouse.


As you can see, Excel is a powerful tool that can help you track and analyze your sales performance.

By taking advantage of its features, you can improve your sales productivity and make better decisions about your territory, customers, and salespeople.

Don't let the accounting team have all the fun; every member of the sales team needs to be aware of the capabilities of this flexible and potent program.

ACTION ITEM: Before we close out this post I want to give you one little action item! Comment below with ONE thing you learned from this post and how you're going to put it into ACTION.

Enjoy the rest of your day.

How to Analyse Sales Performance in Excel (1)

About the author

Anthony Stevens

Anthony Stevens is the Content Specialist for, a resource for free Excel templates including calendars and charts. When not writing, he spends time building furniture and running with his dog.



Best Excel Templates for Project Management and Tracking

(Video) Advanced Excel: Using Charts and Functions to See Trends

6 Excel Templates to Help Organize Your Daily Activities

Budgeting 101: Your Easy Guide to Creating a Business Budget in Excel


How to display 1st, 2nd and 3rd on dates

Where to find the best project management templates

VLOOKUP de-mystfied

How to display leading zeros on numbers

How to best manage duplicates in Excel

Don't Merge and Centre. Do this instead

How to create dropdown lists

Try these 3 ways to insert a degree symbol

11 little-known sorting secrets (esp. 5, 6 and 9)


How to Analyse Sales Performance in Excel (2)

Master Microsoft Office
in 30 days or less



How do you calculate sales performance in Excel? ›

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

What are the four 4 ways to measure the performance of sales staff? ›

Here are four metrics to track to ensure you measure sales performance accurately.
  • Sales Productivity Metrics. How much time do your reps spend selling? ...
  • Lead Response Time. Time is valuable when you're looking at how long it takes reps to follow up on leads. ...
  • Opportunity Win Rate. ...
  • Average Deal Size.
Jun 1, 2022

What is sales KPI dashboard in Excel? ›

The Sales KPI Dashboard Excel Template is an important tool for the Sales department with measurable metrics inside that help to evaluate achieving the company's goals and maintaining growth. For example, you want to present successful results and prove the effectiveness of your sales team.

What are sales performance metrics? ›

Sales metrics (also known as key performance indicators or KPIs)are data points that measure the performance of both your sales team and your individual salespeople. Sales metrics are used to set future goals, spot areas of weakness, design incentive plans, calculate commissions, calculate bonuses, and more.

What are key performance indicators in sales? ›

Key performance indicators (KPIs) in sales are the metrics used to measure how closely the performance of a sales team tracks to predetermined goals and how this performance impacts the business as a whole. This includes metrics like average leads generated per quarter and deal conversion rate.

What is a sales analysis example? ›

A common example of a sales analytics activity is setting role-specific objectives for your team in the form of KPIs or metrics. For instance, setting a revenue target for your sales director while setting a sales productivity goal for your account management team.

What should a sales analysis include? ›

A sales analysis report is a document that includes all of the most important data of your business's sales process and provides you with a complete overview of your sales trends, volume, and overall sales activities. Some of the metrics included in sales analysis reports are: Sales trends. Lead conversion rate.

How do I create a KPI report in Excel? ›

Create a KPI
  1. In Data View, click the table containing the measure that will serve as the Base measure. ...
  2. Ensure that the Calculation Area appears. ...
  3. In the Calculation Area, right-click the calculated field that will serve as the base measure (value), and then click Create KPI.

Does Excel have a comparison tool? ›

You can use Microsoft Spreadsheet Compare to run a report on the differences and problems it finds. Important: Spreadsheet Compare is only available with Office Professional Plus 2013, Office Professional Plus 2016, Office Professional Plus 2019, or Microsoft 365 Apps for enterprise.

How do you compare two sales? ›

Working Out The Comparison

Subtract the Highest Previous Sales Mth from our Total Sales and then divide the difference by the Highest Previous Sales Mth. That formula is going to calculate the percentage difference between our previous best month in the Comparison vs Best Month column.

How do you calculate sales comparison? ›

How to do a sales comparison approach
  • Identify comparables in the market. ...
  • Determine the common attributes of the comparables and the primary home. ...
  • Make adjustments to comparables' values. ...
  • Combine all factors and adjustments for a final price estimate.
Jul 21, 2021

What are the seven 7 principles of performance measurement? ›

The company's seven principles for performance excellence in manufacturing include leadership; work force focus; customer focus; strategic planning; measurement and analysis; operations and process management; and continuous improvement.

What are the 4 A's in sales? ›

After all, launching a campaign without a plan rarely leads to the desired results. That's why one of the most effective marketing strategies is using the 4 A model: Acceptability, Affordability, Accessibility, and Awareness. The 4 A marketing model was developed by Professor Jagdish Sheth and Dr Rajendra Sisodia.

What are the 4 S's in sales? ›

In other words, the “4 S's” of brand measurement: sentiment, search, social, and sales. Put together and used properly, the 4 S's can unlock valuable, actionable insights for marketers.

What are the 5 key performance indicators? ›

What Are the 5 Key Performance Indicators?
  • Revenue growth.
  • Revenue per client.
  • Profit margin.
  • Client retention rate.
  • Customer satisfaction.

What are the 4 main KPIs? ›

Anyway, the four KPIs that always come out of these workshops are:
  • Customer Satisfaction,
  • Internal Process Quality,
  • Employee Satisfaction, and.
  • Financial Performance Index.
Sep 25, 2013

What are the three types of sales performance evaluation? ›

An effective performance evaluation monitoring system (PEMS) has three stages: performance planning, performance appraisal, and performance review.

How do you track sales progress? ›

8 Ways To Best Track Your Sales Employees Progress
  1. Monthly Sales Growth. ...
  2. Checking Reps' Call and Email Volume. ...
  3. Opportunities Created by Sales Reps. ...
  4. Average Conversion Time. ...
  5. Average Follow-Up Attempts. ...
  6. Reflecting Back on Daily, Monthly and Yearly Goals. ...
  7. Using A Lead Response Management Tool.

What are the 3 types of KPIs? ›

Types of KPIs

Quantitative indicators that can be presented with a number. Qualitative indicators that can't be presented as a number. Leading indicators that can predict the outcome of a process.

What is the difference between sales KPIs and metrics? ›

KPIs measure performance based on key business goals while metrics measure performance or progress for specific business activities. KPIs are strategic while metrics are often operational or tactical.

What are 6 KPIs? ›

Here are six such key performance indicators that will ensure success in managing your project portfolio.
  • Customer satisfaction. Our service at the end of the day is to serve our customers and clients. ...
  • Productivity. ...
  • Cost efficiency. ...
  • Time. ...
  • Return on investment (ROI) ...
  • Alignment with goals of the organization.
Feb 11, 2019

What are the 7 steps to analysis? ›

Here are seven steps organizations should follow to analyze their data:
  1. Define goals. Defining clear goals will help businesses determine the type of data to collect and analyze.
  2. Integrate tools for data analysis. ...
  3. Collect the data. ...
  4. Clean the data. ...
  5. Analyze the data. ...
  6. Draw conclusions. ...
  7. Visualize the data.
Aug 24, 2021

What are the four 4 types of analysis? ›

The four types of data analysis are:
  • Descriptive Analysis.
  • Diagnostic Analysis.
  • Predictive Analysis.
  • Prescriptive Analysis.

What are the 5 steps of analysis? ›

Data analysis follows a detailed step-by-step process.
  1. Step One: Ask The Right Questions. ...
  2. Step Two: Data Collection. ...
  3. Step Three: Data Cleaning. ...
  4. Step Four: Analyzing The Data. ...
  5. Step Five: Interpreting The Results.
Mar 16, 2020

What are the 3 methods of analysis? ›

Descriptive analysis, which identifies what has already happened. Diagnostic analysis, which focuses on understanding why something has happened. Predictive analysis, which identifies future trends based on historical data.

What is the sales analysis formula? ›

A simple way to solve for revenue is by multiplying the number of sales and the sales price or average service price (Revenue = Sales x Average Price of Service or Sales Price).

What are the 5 types of analysis? ›

The different types of data analysis include descriptive, exploratory, inferential, predictive, causal and mechanistic.
  • Descriptive Analysis. ...
  • Exploratory Analysis (EDA) ...
  • Inferential Analysis. ...
  • Predictive Analysis. ...
  • Causal Analysis. ...
  • Mechanistic Analysis.
Nov 15, 2022

How do I create a performance chart in Excel? ›

How to Make a Graph in Excel
  1. Enter your data into Excel.
  2. Choose one of nine graph and chart options to make.
  3. Highlight your data and click 'Insert' your desired graph.
  4. Switch the data on each axis, if necessary.
  5. Adjust your data's layout and colors.
  6. Change the size of your chart's legend and axis labels.
Sep 8, 2022

What is the formula for performance indicators? ›

It is calculated by dividing the total book value of the company (i.e. its equity, excluding the value of the preferred shares) by the number of ordinary shares. The total book value of the company is equal to the company's assets minus its liabilities.

Which Excel chart is used for comparing? ›

Column charts are useful to show how data changes over time or to show comparisons among items.

What is the best compare tool for Excel? ›

xlCompare is fast and best performing Excel file comparison tool. Excel file comparison tool detects all modifications in your Excel files. Formulas and macros are compared on the function level - this gives you best and accurate results when you compare Excel files.

How do I compare data quickly in Excel? ›

When comparing two lists of data, select both columns of data, press F5 key on the keyboard, select the “Go to special” dialog box. Then select “Row difference” from the options. Matching cells of data across the rows in the columns are in white color and unmatched cells appear in grey color.

What are the five steps of the sales comparison approach? ›

The steps required in the sales comparison approach:
  • Definition of the appraisal problem.
  • Data collection and verification.
  • Analysis of market data to develop units of comparison and select attributes for adjustment.
  • Development of reasonable adjustments.
  • Application of the adjustments to the comparable sales.

What is sales comparison analysis? ›

The Sales Comparison Approach uses sales prices as evidence of the value of similar properties. The price at which a particular property sells is the price determined by the interaction of supply and demand at the time of sale. If supply or demand factors shift, prices generally rise or fall.

What type of chart will you use to compare performance of sales of two products? ›

Bar and column charts are used to compare different items.

What is sales growth ratio? ›

Sales growth rate measures your company's ability to generate revenue through sales over a fixed period of time. This rate is not only used by your company to look at internal successes and problems, it's also analyzed by investors to see if you're a company on the rise or a company starting to stagnate.

How do you calculate sales achievement percentage? ›

Determine your expenses and total sales for the period. Divide your expenses by your total sales. Multiply your result by 100.

What is average sales growth rate? ›

Growth rate benchmarks vary by company stage but on average, companies fall between 15% and 45% for year-over-year growth.

What is the formula to calculate performance? ›

Performance is calculated by dividing your Total Count by Run Time and comparing it to your Ideal Run Rate or Performance = (Total Count / Run Time) / Ideal Run Rate.

Which formula is used to calculate the performance rating? ›

The process of calculating an average numeric rating is to get the total of all section ratings. Then, this total is divided by the number of sections in the performance document. So, if there were four sections in the document, the calculator would divide the total number of numeric ratings by four.

What are the three main methods of determining performance? ›

Graphic rating scales, management by objectives and forced ranking are three methods used to measure employee performance.

How do you calculate performance KPI? ›

KPI values

The overall digital performance indicator of efficiency can be calculated by summing the KPIs for all selected indicators and dividing the resulting value by the number of indicators. The example shows how simple and clear everything is.

What are the 5 performance ratings? ›

This is more often than not a 5 point rating scale (5– Outstanding, 4– Exceeds Expectations, 3- Meets Expectations, 2- Needs Improvement, 1- Unacceptable).

What are performance rating scales? ›

Rating scales are used in performance management systems to indicate an employee's level of performance or achievement. These scales are commonly used because they provide quantitative assessments, are relatively easy to administer and assist in differentiating between employees.


1. How to build Interactive Sales Analytics Excel Dashboard | (No Formula / Less Formula) Tips & Tricks
(Excel Basement)
2. Excel Sales Performance Metrics Dashboard | Tutorial #1
(Other Level’s)
3. Introduction to Pivot Tables, Charts, and Dashboards in Excel (Part 1)
(Excel Campus - Jon)
4. How to Determine Top Customers: ERP Excel Reporting, Revenue Analysis Tutorial
(FutureView Systems)
5. Salesman Target Tracking Template | Sales Performance Report in Excel!
6. How to build Interactive Excel Dashboards that Update with ONE CLICK!
Top Articles
Latest Posts
Article information

Author: Twana Towne Ret

Last Updated: 01/27/2023

Views: 5846

Rating: 4.3 / 5 (64 voted)

Reviews: 87% of readers found this page helpful

Author information

Name: Twana Towne Ret

Birthday: 1994-03-19

Address: Apt. 990 97439 Corwin Motorway, Port Eliseoburgh, NM 99144-2618

Phone: +5958753152963

Job: National Specialist

Hobby: Kayaking, Photography, Skydiving, Embroidery, Leather crafting, Orienteering, Cooking

Introduction: My name is Twana Towne Ret, I am a famous, talented, joyous, perfect, powerful, inquisitive, lovely person who loves writing and wants to share my knowledge and understanding with you.