How to Gross Up a Number by a Percentage in Excel: Formula and Step-by-Step Guide

If you need to gross up a number by a percentage in Excel, the formula to use is:
=original_number/(1-percentage).
For example, to gross up $100 by 25%, you would enter:
=100/(1-0.25)
which equals $133.33.
This article provides a full explanation of the gross-up formula and a step-by-step guide to using it in Excel, with examples.

What Does It Mean to Gross Up a Number?

Grossing up a number means increasing it so that after a deduction is taken out, you are left with the original amount. The deduction could be in the form of taxes, discounts, fees, or any other percentage-based reduction.

Common situations where you may need to gross up a number include:

• Calculating a pre-tax salary amount that will result in a desired after-tax take-home pay
• Determining the sticker price of an item before a discount is applied
• Calculating the total revenue required to achieve a profit target after accounting for expenses
• Figuring out how much to invoice a client to receive a certain amount after fees are deducted
• Budgeting for a purchase or project to ensure you have enough funds to cover taxes and other costs

Essentially, grossing up allows you to work backwards from a net amount to determine the corresponding gross amount needed before deductions. It’s a way to ensure you end up with the amount you need after all deductions are taken into account.

Gross Up Formula Explanation

The formula to gross up a number is:

Gross Amount = Net Amount / (1 - Deduction Percentage)

Where:

• Gross Amount is the original or initial amount, before any deductions are applied
• Net Amount is the final amount, after deductions have been taken out
• Deduction Percentage represents the percentage (expressed as a decimal) that is being deducted from the gross amount

To convert a percentage to decimal form, simply divide it by 100. For instance, a 25% deduction would be expressed as 0.25 in the formula.

Why the Formula Works

Here’s the mathematical reasoning behind the gross-up formula:

Let’s represent the Gross Amount as X and the Deduction Percentage as P.
To calculate the Net Amount, you would multiply the Gross Amount (X) by (1 – P).
Algebraically, this is expressed as: Net Amount = X * (1 - P)

However, when grossing up, you are doing the reverse – you have the Net Amount and need to work backwards to find X, the Gross Amount.
To solve for X algebraically, you divide both sides of the equation by (1 – P):

X * (1 - P) = Net Amount
X = Net Amount / (1 - P) 

Therefore, the formula to gross up the Net Amount to determine the original Gross Amount is: Gross Amount = Net Amount / (1 - P)

Understanding the logic behind the formula can help you apply it correctly and adapt it to different situations.

How to Gross Up in Excel

Using Excel’s built-in formulas and functions, it’s easy to gross up a number. Just follow these steps:

1. Enter the net amount in a cell. This is the number you want to gross up, i.e., the desired final amount after deductions.
2. In another cell, enter the deduction percentage in decimal form. For example, if the deduction is 25%, enter 0.25 in this cell. Make sure to format the cell as a percentage if you want it to display as such.
3. In a third cell, enter the gross-up formula. The formula should reference the cells containing the net amount and deduction percentage, like this:
   =net_amount_cell/(1-percentage_cell)
4. The result in the formula cell will be the grossed-up amount, i.e., the original amount needed to arrive at the net amount after applying the deduction percentage.

Let’s walk through a couple of specific examples to illustrate the process.

Example 1: Gross Up Salary for Taxes

Suppose you want to determine what gross salary you would need to pay an employee to ensure they receive $75,000 per year after taxes. Let’s assume the applicable tax rate is 25%.

1. In cell A1, enter the desired net pay: 75000
2. In cell B1, enter the tax rate expressed as a decimal: 0.25
3. In cell C1, enter the gross-up formula referencing cells A1 and B1: =A1/(1-B1)

The result in cell C1 will be 100000, indicating that you would need to pay the employee a gross salary of $100,000 to ensure they take home $75,000 after deducting 25% for taxes.

Example 2: Gross Up a Discounted Price

Imagine a product is on sale for $80, and the sale price represents a 20% discount off the regular price. You want to calculate what the regular (pre-discount) price would be.

1. In cell A1, enter the sale price: 80
2. In cell B1, enter the discount percentage expressed as a decimal: 0.20
3. In cell C1, enter the gross-up formula referencing cells A1 and B1: =A1/(1-B1)

The result in cell C1 is 100, meaning the regular price of the product before applying the 20% discount is $100.

Gross Up by a Specific Percentage

In some cases, you may want to gross up a number by a specific percentage, rather than accounting for a deduction percentage. To do this, simply add the desired gross-up percentage to 1 in the denominator of the formula. The modified formula becomes:

Grossed Amount = Original Number / (1 + Gross-Up Percentage)

For example, let’s say you want to gross up an amount of $1000 by 10%. Here’s how you would set it up in Excel:

1. In cell A1, enter the original amount: 1000
2. In cell B1, enter the gross-up percentage expressed as a decimal: 0.10
3. In cell C1, enter the modified gross-up formula referencing cells A1 and B1: =A1/(1+B1)

The result in cell C1 will be 1111.11, which is 10% higher than the original $1000.

Alternative Way to Gross Up by a Percentage

Another way to think about grossing up by a percentage is that you are dividing the original amount by 1 minus the gross-up percentage. Conceptually, to gross up an amount by 10%, you would divide it by (1-0.10) or 0.9.

Using the same example of grossing up $1000 by 10%:

1. In cell A1, enter the original amount: 1000
2. In cell B1, enter the gross-up percentage expressed as a decimal: 0.10
3. In cell C1, enter the alternative gross-up formula referencing cells A1 and B1: =A1/(1-B1)

This formula will also result in 1111.11, demonstrating that both approaches are equivalent and interchangeable.

Tips for Using the Gross Up Formula in Excel

Here are some tips and best practices to keep in mind when grossing up numbers in Excel:

• Double-check your inputs. Before calculating, ensure the net amount and percentage(s) are entered accurately in their respective cells. Even a small input error can significantly impact the result.
• Use cell references in formulas. Instead of hardcoding values directly into your formula, reference the cells containing those values. This keeps your spreadsheet more organized and allows you to update inputs easily without having to modify the formula itself.
• Format percentages properly. When using a percentage in a gross-up formula, it must be expressed in decimal form (e.g., 25% as 0.25). However, you can still format the cell containing the percentage to display it with a percent sign for readability.
• Label your values clearly. Avoid ambiguity by labeling your gross amounts, net amounts, and percentages descriptively. This helps anyone viewing your spreadsheet understand the purpose and meaning of each value.
• Understand when grossing up is appropriate. Grossing up is a useful technique for working backwards from a net amount to a gross amount, but it may not be applicable in every situation. Always consider the context and purpose of your calculation to determine if grossing up aligns with your objective.

Final Thoughts

To recap, the formula to gross up a number by a percentage in Excel is:

Gross Amount = Net Amount / (1 - Deduction Percentage)

Where the Gross Amount is the original amount before deductions, the Net Amount is the desired amount after deductions, and the Deduction Percentage is the percentage being deducted expressed as a decimal.

Remember to pay close attention to your inputs, use cell references in your formulas, format percentages correctly, label your values clearly, and only gross up when it serves your specific objective. With these tips in mind, you can confidently apply the gross-up formula in Excel to ensure you’re always working with the right amounts.

FAQs

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.