Use Absolute Reference to Create an Excel Formula to Determine Percentages

Excel is a great tool to help you determine the percentages of a certain category type. Let’s say I have the following data in Excel for customer types in the month of January.

• Walk -In : 3
• Cold Call : 2
• Email Campaign : 21
• Website  :19
• Other  : 8

My total formula which is in cell B11 =SUM(B4:B9).

I click on the first cell I want to create the formula in that is the top row of my first category. In this case I am in cell C4.

The formula is =B4/\$B\$11. The \$ dollar signs in the B11 cell are important because they represent an absolute cell reference. They help the formula copy to other cells below without loosing the cell reference. In other words whenever you need to use a cell in the formula that should not change use an absolute reference. The keyboard shortcut is F4 to create an absolute reference.

After you enter the formula add the percentage style format.

Copy the formula down using the autofill. The cool thing is Excel will copy the C4 (Walk-In) cell down to C5 (Cold Call) and so forth. But the B11 total will remain constant because B11 is written in the formula as \$B\$11.