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 want to help you work in your business, so you can work on your business. I want to help you achieve your goals and help you have a successful small business journey. I understand that keeping up with the latest technology and cloud based computer trends can be difficult, when you’re running your day to day business. But, it is crucial that you keep up with the technology and best understand your options, in order to thrive in this new era of online business. I can help you discover the right software and tools for your business, train your team on how to use them, and mentor you to best serve and delight your customers. My certifications include: QuickBooks ProAdvisor, Microsoft Office Specialist Master, and OntraPort Certified Consultant. Please contact me on my new website 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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s