ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel
  • »
  • Technology»
  • Computers & Software»
  • Computer Software»
  • Office Software Suites»
  • Microsoft Office

How to Write Percentage Formulas in Excel

Updated on February 23, 2014
Basic Excel Percentage Formula
Basic Excel Percentage Formula | Source

Real Life Example: Manufactures Use Percentage Formulas to Track Key Indicators

I work as an accountant for a business that manufactures goods. We use Microsoft Excel to do our monthly reporting and analysis. EBITDA, Earnings Before Interest Taxes Depreciation Amortization, is our most important measure of overall business performance. Raw materials cost, as a percent of sales is probably our second most important indicator of how the business is performing. It tells us whether we are able to hold on to our profit margins as raw material prices fluctuates. We track other indicators such as material margin as a percent of sales, overhead as a percent of sales, gross profit as a percent of sales, and working capital as a percent of sales to name a few. Knowing how to write percentage formulas in Excel is critical in the business world. Percentage formulas are important because they provide a benchmark to allow you to see how sales price and cost changes affect the business. Looking at net sales and raw material costs for example, cloud the picture of what is going on. A Percentage formula allows you to compare several periods and take the noise out.

Businesses are not the only area to use percentage formulas. Any kind of statistical analysis will use percentage formulas. Political polling uses them to break down the poll information into a more useful form. Sports use percentages for all kinds of statistics such as completion percent (NFL), free throw percent (NBA, NCAA), etc.

Get Additional Microsoft Excel Help here

Step-by-Step Instructions to Writing Percentage Formulas in Excel

Writing a percentage formula in Excel is easy. Click on the cell where you want to put the formula. Enter “=” and either enter a number or a cell reference that represents the number that you want to divide. Enter a “/” and either the cell reference or the number that represents the divided by number.

=75/100 = .75

Now format the cell as a percentage or leave it as a decimal depending on how you want it to look. To format it as a percentage go to Excel’s Home tab and click either the “%” button on the toolbar or bring up the “Format Cells” dialogue box by right-clicking on the cell, selecting “Format Cells”, and clicking on “Percentage.” Choose however many decimals that you wish.

Real Life Business Use Examples

Now that we have seen how to write a simple percentage formula, let us apply it to the real world. I use the percentage formulas that are listed below every month in our monthly financial reports. I spend the most time looking at raw material percent to tie what the accounting systems says raw material percent should be versus what the P&L actually says.

P&L View
P&L View | Source
  • Raw Material Percent – For a manufacturing business this is a key indicator that tells you if your raw material prices are rising or falling compared to your selling price. If it begins to increase, businesses will try to push through price increases to offset their costs. It is calculated as follows:

=[raw material costs] / [net sales]

  • Material Margin Percent – This is the other side of the calculation for raw material percent. This looks at the amount of profit that a business makes based on sales minus raw material costs. If both of these calculations are used, the total percentage will add up to 100%. Material margin percent is calculated as follows:

=([sales] – [raw material costs]) / [sales]

  • Gross Profit Percent – This is an important calculation that helps a business know what their manufacturing costs are as a percentage of sales. Obviously, the higher the percentage the more profitable the business is. Gross profit is net sales minus manufacturing costs. Gross profit percent is calculated as follows:

=([sales] – [total cost of sales])/[sales]

  • SG&A Percent – This is a measure of all of the selling, marketing, research & development, and administrative costs of a business as a percentage of sales. It is calculated as follows:

=([sales] – [SG&A costs])/[sales]

Without percentages to compare to, reviewing financial information would be much more difficult because sales volume would skew the comparisons. Percentages easily allow financial information to be analyzed across periods.

Comments

    0 of 8192 characters used
    Post Comment

    • profile image

      Qamar Khan 4 years ago

      Thanks

    • BusinessTime profile image

      Sarah Kolb-Williams 5 years ago from Twin Cities

      Great information -- spreadsheets are my weak link, and this is really going to help out!

    • Simone Smith profile image

      Simone Haruko Smith 5 years ago from San Francisco

      This is one of the most useful Hubs I've come across in a while! I love the examples you've used, and am always happy to come across an Excel guide. I'm a huge fan of spreadsheets in general.

    • CassyLu1981 profile image

      CassyLu1981 5 years ago from Spring Lake, NC

      I LOVE Excel! I use it for my bills, all my lists, spreadsheets, projects for the kids, LOL I use it for everything! I've only done the percentage formulas a few times. I might have to go back and make sure I remember how! Thanks for the info, I know my husband will use it too!!! Voted up and shared :)

    • Seeker7 profile image

      Helen Murphy Howell 5 years ago from Fife, Scotland

      This is an excellent hub about Excel. It can seem rather daunting to use Excel at first, but reading excellent articles like this helps so much! Thanks for sharing.

    • ercramer36 profile image
      Author

      Eric Cramer 5 years ago from Chicagoland

      Thanks! I agree with you that there is always something new to learn with Excel.

    • internpete profile image

      Peter V 5 years ago from At the Beach in Florida

      Great info about Excel! It seems like the more I learn about Excel, the more there is to learn!