How to Analyze Sales Data in Excel (10 Easy Ways)

Excel is a popular tool for storing and analyzing sales data. Also, numerous ways are offered to analyze sales data in Excel. Therefore, analyzing sales data depending on the data types requires a solid understanding of that particular data.

Let’s say we have half-year sales data for multiple salesmen. And we want to analyze these data.

Dataset-How to Analyze Sales Data in Excel

This article demonstrates multiple ways to analyze sales data in Excel.


Download Excel Workbook


10 Easy Ways to Analyze Sales Data in Excel

Different data types require different ways to analyze them. Some data may need further arrangements before analyzing. Go through the below section to learn more about sales data analysis.


Method 1: Conditional Formatting to Depict Sales Trends

Excel’s Conditional Formatting is an effective tool for Rank, Highlight, Insert Icons, or Color Scale sales data.

Step 1: Sum each salesman’s total sales during the period. Use the below formula in an adjacent cell, such as I4.

=SUM(C4:H4)

Conditional Formatting-How to Analyze Sales Data in Excel

Step 2: Go to Home > Select Conditional Formatting (in the Style section). There are multiple highlighting options, such as Highlight Cells Rules, Top/Bottom Rules, Data Bar, Color Scales, and Icon Sets. Choose one of them (here we choose Icon Sets) to depict your data trend.

Icon Sets

🔺In a moment, Excel inserts trend Icons depending on the sales values as shown in the below picture.

Conditional Formatting Outcomes


Method 2: Pivot Table to Analyze Sales Data as Percentage in Excel

Excel Pivot Table organizes data and has the ability to sort them automatically. It offers much flexibility to assign different fields as its row and column. Also, existing entries can be displayed as their rows or columns’ percentages.

Step 1: Highlight the desired range, then go to Insert > PivotTable (in the Tables section) > Select From Table/Range.

Pivot Table-How to Analyze Sales Data in Excel

Step 2: The PivotTable from table or range window appears. As we highlighted the ranges prior to the insertion, Excel automatically loads the Table/Range. Mark the New Worksheet option as Choose where you want the PivotTable to be placed. Then click on OK.

Inserting Pivot Table

🔺 Excel inserts the Pivot Table in a New Worksheet as depicted below.

Pivot Table

Step 3: Tick the required PivotTable fields and place them where you want them to be (as placed in the following image).

Pivot Table Fields

Step 4: Place the cursor in any cells, and right-click on it. Select the Value Field Settings from the Context Menu.

Value Field Settings

Step 5: The Value Field Settings dialog box appears. In the dialog box, click on Show Value As > Choose the % Column Total option under the Show Value As option. Finally, click on OK.

Value Field Settings-How to Analyze Sales Data in Excel

Step 6: Excel displays all the entries as the percentage of the Monthly Total Sales. You can insert a PivotChart to visually represent them in a Chart. Placing the cursor within the Pivot Table entries, go to the PivotTable Analyze tab > Select Pivot Chart (in the Tools section).

Pivot Chart

Step 7: Excel fetches the Insert Chart dialog box. Choose any of the desired Chart types (here we choose Column Chart) afterward click OK.

Chart Selection

🔺 After clicking OK, Excel inserts a Pivot Chart depicting the percentages of each salesman for each month.

Pivot chart


Method 3: Using RANK Function to Rank Sales Data

The RANK function returns the rank of a given value by comparing a list of numeric values. The syntax of the RANK function is

RANK (number, ref, [order])

Step 1: Type the below formula in any blank cell (i.e., K4)

=RANK(C4,$C$4:$H$13,0)

In the formula, C4 is the number, $C$4:$H$13 is the ref, and 0 indicates the descending order.

Rank-How to Analyze Sales Data in Excel

Step 2: First, drag the Fill Handle to the right and then below to apply the formula in all cells.

Rank Outcome

Ranks of the sales number represent the comparative places among the sales numbers of the salesmen.


Method 4: Slicer to Insert Chart to Analyze Sales Data in Excel

Slicer works like a Filter and can depict individual sales data with Chart. To insert Slicer, we have to organize our data into an Excel Table.

Step 1: Select the desired range then go to Insert > Table (in the Tables section).

Slicer-How to Analyze Sales Data in Excel

Step 2: Excel brings up the Create Table dialog box. Tick the My table has headers option then click on OK.

Create Table

Step 3: Again, in the Insert tab, move to Insert > Filters > Slicer.

Inserting Slicer

Step 4: Excel brings the Insert Slicer command window. Choose any of the Column Headers (Salesman or Months) to slice data by them. Then click on OK. Afterward, Excel inserts the Slicer.

Slicer-How to Analyze Sales Data in Excel

Step 5: Now, in the Insert tab, select any of the Insert Scatter Chart types.

Scatter Chart

🔺 For all the sales data, Excel inserts a scatter chart as shown below.

Chart

Step 6: But what if you want an individual data chart to view each salesman’s progress on sales. Therefore, you need to switch the rows with columns. Click on the inserted Chart > Chart Design > Select Switch Row/Column.

Switch row/column

🔺 Excel switches the axes, therefore a new representation occurs.

switched Chart

Step 7: On the Slicer, choose any of the salesmen (i.e., Bob). And the Chart automatically depicts only Bob’s sales.

Individual Chart-How to Analyze Sales Data in Excel

You can clear the selection by clicking on the Filter-Cross icon situated on the right side of the Slicer window.


Similar Readings


Method 5: Index Chart to Display Trends in the Sales Data

Comparing sales values to 100 gives a lucid picture of their growth. All the occurred sales get transformed into a scale of 100 and users almost instantly understand the sales.

For depicting the Index Chart, we take 3 months’ sales data as shown in the image below. You can use as much data as you desire.

Index Chart-How to Analyze Sales Data in Excel

Step 1: To find the Index values of the sales data, make an identical range to the sales dataset. Then insert 100 in the 1st salesman’s monthly sales as shown below.

100 Insertion

Step 2: Paste the below formula in cell H5.

=H$4*(C5/C4)

Formula Insertion-How to Analyze Sales Data in Excel

Step 3: Press ENTER then drag the Fill Handle to the right then below to apply the formula to all cells.

Outcome

Step 4: Select the Index data range then go to Insert > 2-D Line Chart.

2-D Line Chart

🔺 In a moment, Excel inserts the Index Line Chart to depict the sales of different salesmen for different months.

Index Chart-How to Analyze Sales Data in Excel


Method 6: Calculating Weighted Average to Analyze Sales Data in Excel

In case, users get consolidated data after summarizing a huge dataset, the weighted average is an effective tool to analyze them. Suppose we consolidated our data into Monthly Sales Per Salesman and Monthly Total Sales. Now, we want to find the Weighted Average to understand the overall sales.

Weighted Average-How to Analyze Sales Data in Excel

Step 1: Type the below formula in any blank cell (i.e., D11).

=SUMPRODUCT(C4:C9,D4:D9)/SUM(C4:C9)

The SUMPRODUCT function sums the multiplied values of the Total Salesman and the Monthly Sales Per Salesman. And the SUM function returns the total number of the Salesman.

Weighted Average Formula

Step 2: Hit ENTER to execute the formula and display the weighted average.

Weighted Average Outcome


Method 7: Analyze Data Feature (Excel 365) to Execute Multiple Analyses

Excel 365 provides the Analyze Data feature to execute multiple analyses of sales data. Those analyses range from Inserting Pivot Table to multiple Charts. Users can select any one of them to analyze their data.

Steps: Select the desired range, then go to Home > Click on Analyze Data (in the Analysis section). Now, you can choose any of the analysis options offered in the Analyze Data window to analyze your data. Previously demonstrated Excel features are also included in the Analyze Data window.

Analysis Data-How to Analyze Sales Data in Excel

Read More: How to Analyze Data in Excel Using Pivot Tables (9 Suitable Examples)


Method 8: Analyze Sales Data Using Trendline in Excel

Sometimes, users get yearly accumulated sales data to analyze them. In those cases, using Line Charts allow users to display the trends within the occurred sales. Let’s say the following screenshot depicts the yearly sales.

Trend-How to Analyze Sales Data in Excel

Steps: Highlight the entire dataset then go to Insert > 2-D Line (in the Charts section).

Line Chart

🔺 Excel insert a Line Chart depicting the Trend within the sales as shown below.

Trend Line Chart


Method 9: Sorting Sales Data to Find Greater Values

Data sorting is a quick way to get an instant comprehension of the sales data. Though sorting doesn’t return any parameter rather than order, it’s a good hack to find the maximum or the minimum number of values.

Step 1: Go through Home > Select Sort & Filter (in the Editing section) > Click on Custom Sort.

Sort-How to Analyze Sales Data in Excel

Make sure you select the entire range for sorting. Otherwise, there will be a mismatch in values to column or row headers.

Step 2: The Sort dialog box appears. In that dialog box, assign respective sort items to sort by as depicted in the following image. At last, click on OK.

Custom Sort

Step 3: Clicking OK sorts the data as you instructed in the Sort dialog box.

Sorting


Method 10: Applying Data Analysis Tool to Analyze Sales Data

Excel offers the Data Analysis feature in the Data tab. Data Analysis incorporates multiple Analysis Tools to analyze data Descriptive Statistics is one of them.

Step 1: To execute the descriptive statistics, go to Data > Data Analysis (in the Analysis section).

Data Analysis-How to Analyze Sales Data in Excel

Step 2: Excel fetches the Data Analysis window. From the window, select the Descriptive Statistics analysis tool then click OK.

Descriptive Statistics

Step 3: Afterward, Excel brings up the Descriptive Statistics dialog box. In the dialog box, insert or select the respective options depending on your data and demands. At last, click on OK.

Dialog Box

🔺 After that, Excel inserts Monthly Earning Statistics compiling the sales data.

Descriptive Statistics

There are multiple parameters such as Mean, Median, Maximum, or Minimum sales values to represent the sales data.

Read More: [Fixed:] Data Analysis Not Showing in Excel (2 Effective Solutions)


Conclusion

In this article, we use multiple methods to analyze sales data in Excel. However, two or three methods may successfully analyze your data depending on your data type. We hope you find the desired method among the described ones to analyze your sales data. Comment if you have further inquiries or have anything to add.


Related Articles

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo