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
  • Google AdWords Pay Per Click  : 15
  • Email Campaign : 21
  • Website  :19
  • Other  : 8

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

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.

Lead Source Percentage in Excel formula

After you enter the formula add the percentage style format.

percentage format excel

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.

autofill excel formula


About Steve Chase

I'm a proud husband and dad to 4 boys! Microsoft Certified Trainer and Boy Scout Leader are some of things I call myself. The Cincinnati Reds are my favorite team! When not outdoors, I enjoy working with documents in Microsoft Office, QuickBooks, Visio and Photoshop. My wife, Erin, shares her awesome recipes on her blog at
This entry was posted in Excel and tagged . Bookmark the permalink.

2 Responses to Use Absolute Reference to Create an Excel Formula to Determine Percentages

  1. Johna472 says:

    Good web site! I really love how it is easy on my eyes and the data are well written. I am wondering how I might be notified whenever a new post has been made. I’ve subscribed to your RSS which must do the trick! Have a great day! agdaakfecfcc

  2. Johnd238 says:

    Woah! I’m really loving the templatetheme of this website. It’s simple, yet effective. A lot of times it’s hard to get that perfect balance between usability and visual appeal. I must say you have done a great job with this. In addition, the blog loads super quick for me on Firefox. Excellent Blog! cegdebedeagd

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s