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.

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

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)

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.

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

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.

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.

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

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

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

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.

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).

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

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

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.

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

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).

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

Step 3: Again, in the Insert tab, move to Insert > Filters > 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.

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

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

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.

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

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

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

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.

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

Step 2: Paste the below formula in cell H5.

=H$4*(C5/C4)

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

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

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

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.

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.

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

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.

Read More: How to Analyze Text Data in Excel

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.

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

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

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.

⧭ 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.

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

Read More: How to Analyze Raw Data in Excel

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).

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

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.

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

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

Download Excel Workbook

Analyzing Sales Data.xlsx

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.

<< Go Back to Data Analysis with Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!