Excel percentages: Calculating percentages

Excel percentages: Calculating percentages

Excel percentagesYOUR OBJECTIVE

For your business, you want to determine how much each of several product categories contributed to total sales. To best understand the data, you wish to present the contributions in terms of percentage of total sales.

In this learning module “Excel percentages” you will discover how to use Excel to calculate percentages and some of the things you should consider when doing so.

Excel percentages: HOW DOES IT WORK?

An Excel spreadsheet that contains a list of sales and a grand total has already been created. The numeric sales data is to be enhanced by adding a breakdown of sales by excel percentages.

To calculate excel percentages, you have to calculate the fraction that each product category contributes to total sales. The sum of such fractions would equal 1, i.e., 100%. When sales from one category is divided by total sales, the result will be a value between 0 and 1. If this value is then multiplied by 100, the result can be presented in percent. In the current example (as in the next illustration), calculating the “Electrical” category’s contribution to total sales, results in a decimal value of 0.195. After multiplying by 100, the result displayed as a percent is 19.5%.

To calculate the contribution for the “Electrical” product category in percent, click cell C4 and enter an equal sign (=). As soon as you click cell B4, Excel switches to display mode.

Then type a division sign (/), the forward slash character, and click on the total sales cell (B10). Since every further calcualtion must refer to this one cell, you have to create an absolute reference. If you now press the [F4] function key, the form of the reference changes to $B$10, an absolute reference.

Now press [Enter]. The portion of sales for “Electrical” will be displayed as a decimal fraction.

Instead of displaying the value in decimal, however, you want to display it as excel percentages. To do so, click the button (Home tab, Numbers group).

In that same group, you will also find the  button, which enables you to add more decimal places to your result. If you add too many decimal places, you can always decrease them by clicking the  button.

Now you can use Auto Fill to copy the formula from C4 to the cells below it.