Calculating Percentage of a Column of Numbers in Excel

Objective

To calculate the percentage of a column of numbers in Excel, we will derive the percentage of each individual value in relation to the total sum of that column.

Formula

The basic formula for calculating the percentage of an individual number in a column is as follows:

= ( Cell / Total ) * 100

Where:

• Cell refers to the specific cell containing the number whose percentage you want to calculate.
• Total refers to the sum of all values in the column.

Example Setup

Assuming you have a column of numbers in column A (from A1 to A5):

Steps to Calculate Percentage

1. Calculate the total sum of the numbers in column A. Use the following formula in a separate cell, say B1:

   =SUM(A1:A5)

2. To calculate the percentage of each number in column A, enter the following formula in cell B2 and drag it down to B6:

   = (A2 / $B$1) * 100

Explanation of the Formula:

• A2: This is the cell reference for which you are calculating the percentage.
• $B$1: This is an absolute reference pointing to the total sum. The dollar signs ensure that the reference does not change when the formula is dragged down.
• * 100: Multiplies the result by 100 to convert it into a percentage format.

Resulting Table

After applying the above calculations, your worksheet will look like this:

Formatting as Percentage

To display the values in column B as percentages:

1. Select cells B2 to B6.
2. Navigate to the Home tab.
3. In the Number group, choose the Percentage format.

Summary

By utilizing the formula (Cell / Total) * 100, you can effectively calculate the percentage of each number in a column relative to the total. The process illustrates both the calculation and presentation of values in a clear manner suitable for data analysis tasks. For further learning and development in Excel, consider exploring the courses available on the Enterprise DNA Platform.