Calculating a Percent Distribution in Excel

What is a Percent Distribution?

A percent distribution is a way of showing how a total value is divided among different categories or groups. It tells us what percentage of the total each category or group represents. For example, if we have a total revenue of $100,000 and we want to know how much each region contributed to it, we can calculate the percent distribution of revenue by region.

How to Calculate a Percent Distribution in Excel

To calculate a percent distribution in Excel, we need two things: the values for each category or group, and the total value. The basic formula for calculating a percent distribution is:

= (category value / total value) * 100

This formula divides the category value by the total value and then multiplies the result by 100 to get the percentage. We can apply this formula to each category or group in our data set and format the cells as percentages.

Example: Calculating the Percent Distribution of Revenue by Region

Let’s say we have the following data set that shows the revenue by region for a company:

To calculate the percent distribution of revenue by region, we can use the following steps:

1. In cell C2, enter the formula =B2/B6*100 and press Enter. This will calculate the percent distribution of revenue for the North region.
2. Copy the formula in cell C2 and paste it in cells C3:C5. This will calculate the percent distribution of revenue for the other regions.
3. Select cells C2:C5 and click the percentage (%) icon in the Number group on the Home tab. This will format the cells as percentages with two decimal places.
4. The result should look like this:

From the result, we can see that the East region contributed the most to the total revenue (35%), followed by the North and West regions (25% each), and the South region (15%).

Other Approaches for Calculating a Percent Distribution in Excel

There are other ways to calculate a percent distribution in Excel, such as using the SUM function, the PivotTable feature, or the Analysis ToolPak add-in. Here are some brief descriptions of these methods:

• Using the SUM function: Instead of entering the total value in a separate cell, we can use the SUM function to calculate the total value within the percent distribution formula. For example, in cell C2, we can enter the formula =B2/SUM(B2:B5)*100 and copy it down to cells C3:C5. This will give us the same result as before, but without using a dedicated cell for the total value.
• Using the PivotTable feature: A PivotTable is a powerful tool that can summarize and analyze data in Excel. We can use a PivotTable to calculate the percent distribution of revenue by region by following these steps:
  1. Select the data range A1:B5 and click the Insert tab on the ribbon. Then click the PivotTable button in the Tables group.
  2. In the Create PivotTable dialog box, choose where to place the PivotTable report. For this example, we will place it in a new worksheet. Click OK.
  3. In the PivotTable Fields pane, drag the Region field to the Rows area and the Revenue field to the Values area.
  4. Right-click any cell in the Revenue column and choose Show Values As > % of Grand Total. This will display the percent distribution of revenue by region in the PivotTable report.
  5. The result should look like this:

• Using the Analysis ToolPak add-in: The Analysis ToolPak is an Excel add-in that provides additional data analysis tools, such as histograms, correlation, and descriptive statistics. We can use the Analysis ToolPak to calculate the percent distribution of revenue by region by following these steps:
  1. Click the File tab on the ribbon and choose Options. Then click the Add-Ins category in the Excel Options dialog box.
  2. In the Manage drop-down list, select Excel Add-ins and click Go. This will open the Add-Ins dialog box.
  3. Check the Analysis ToolPak box and click OK. This will enable the Analysis ToolPak add-in in Excel.
  4. Click the Data tab on the ribbon and click the Data Analysis button in the Analysis group. This will open the Data Analysis dialog box.
  5. In the Data Analysis dialog box, select Histogram and click OK. This will open the Histogram dialog box.
  6. In the Histogram dialog box, enter the following settings:
     • Input Range: B2:B5
     • Bin Range: Leave blank
     • Output Range: E2
     • Check the Chart Output box
     • Check the Cumulative Percentage box
     • Click OK
  7. The result should look like this:

The histogram chart will also be displayed in the worksheet, showing the frequency and cumulative percentage of revenue by bin.