ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

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 

      5 years ago

      Thanks

    • BusinessTime profile image

      Sarah Kolb-Williams 

      6 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 

      6 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 

      6 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 

      6 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 imageAUTHOR

      Eric Cramer 

      6 years ago from Chicagoland

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

    • internpete profile image

      Peter V 

      6 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!

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://hubpages.com/privacy-policy#gdpr

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)