Google Sheets Discount Percentage Formula: A Practical Guide
Learn a practical, step-by-step method to compute discount percentages in Google Sheets using a simple formula, array formulas, and robust edge-case handling for budgeting and pricing.
The discount percentage in Google Sheets is calculated as the difference between the original price and the sale price, divided by the original price. Use = (Original - Sale) / Original, then format the cell as a percentage. This method scales to ranges with array formulas or by applying it row-wise. It also handles simple discounts and can be combined with IF to manage missing values.
Understanding the discount percentage formula in Google Sheets
The keyword for this topic is google sheets discount percentage formula and the method is simple: you compute the percentage saved by taking the difference between the original price and the sale price, then dividing by the original price. In practice, place original in A2 and sale in B2 and enter the formula below. Then format as a percentage to show 0.25 as 25%.
=(A2 - B2) / A2Parameters:
A2: Original priceB2: Sale price- The result is a decimal fraction; format as percentage to view as 0-100%.
Note: If you want to apply this to a column, use an ARRAYFORMULA:
=ARRAYFORMULA((A2:A - B2:B) / A2:A)This approach scales across many rows and is ideal for budgeting and pricing analyses.
Calculating discounts across a list of products with array formulas
When you have a list of items in columns, you can compute the discount percentage for each row using an array formula, avoiding manual copy-paste. The simplest approach is:
=ARRAYFORMULA((A2:A - B2:B) / A2:A)To guard against missing data, you can wrap the calculation in IF to skip blanks:
=ARRAYFORMULA(IF(A2:A="","", (A2:A - B2:B) / A2:A))If you prefer a named-friendly calculation, use LET to assign range names:
=LET(orig, A2:A, sale, B2:B, (orig - sale) / orig)Finally, if you want rounded results for reporting, apply ROUND:
=ROUND((A2 - B2) / A2, 2)This section demonstrates practical patterns for scalable discount reporting.
Handling edge cases and data validation in discount calculations
Real-world data rarely looks perfect. You should handle zero originals, blanks, and non-numeric values to avoid #DIV/0! or #VALUE! errors. A robust base formula is:
=IF(A2=0, "", (A2 - B2) / A2)To apply to a whole column safely:
=ARRAYFORMULA(IF(A2:A=0, "", (A2:A - B2:B) / A2:A))If you also want to suppress errors from non-numeric entries, wrap with IFERROR:
=IFERROR((A2 - B2) / A2, "")These guards keep your dashboards clean and reliable.
Practical variants: final price, percentage off, and stacked discounts
You can derive the final price after a percentage discount with a simple expression. If C2 contains discount rate in decimal form (for 25% use 0.25):
=A2 * (1 - C2)Conversely, if you know the final price and want the discount percentage:
=(A2 - D2) / A2If you apply discounts to multiple items, combine with ARRAYFORMULA to populate a discount column automatically. For example:
=ARRAYFORMULA(IF(A2:A=0, , (A2:A - B2:B) / A2:A))By aligning these formulas with your reporting cadence, you can iterate quickly on pricing strategies and promotions.
Steps
Estimated time: 15-25 minutes
- 1
Set up your data
Enter your original prices in column A and sale prices in column B. Leave a header row if you plan to use it in dashboards.
Tip: Use consistent data types; keep numbers numeric, not text. - 2
Enter the base discount formula
In cell C2, type the core formula that computes the discount percentage: = (A2 - B2) / A2.
Tip: Format C2 as percentage to view 0.25 as 25%. - 3
Copy the formula down the column
Drag the fill handle or use Ctrl+D (Cmd+D on Mac) to apply the formula to the rest of the rows.
Tip: Ensure there are no header mismatches when extending ranges. - 4
Use array formulas for whole-column calculations
Replace the per-row formula with an array version to automate for all rows: =ARRAYFORMULA((A2:A - B2:B) / A2:A).
Tip: Wrap in IF to avoid blanks: =ARRAYFORMULA(IF(A2:A="","", (A2:A - B2:B) / A2:A)) - 5
Guard against zero originals and non-numeric data
Use IF checks or IFERROR to prevent errors from zeros or non-numeric values.
Tip: Example: =IF(A2=0, "", (A2 - B2) / A2) - 6
Validate and summarize results
Cross-check a few rows manually and consider rounding for reports with ROUND(..., 2).
Tip: Consistent rounding avoids presentation discrepancies.
Prerequisites
Required
- Required
- Basic arithmetic and percentage knowledgeRequired
- Ability to enter and edit formulas in cellsRequired
Optional
- Familiarity with array formulas (optional but helpful)Optional
- A modern browser for Google SheetsOptional
Keyboard Shortcuts
| Action | Shortcut |
|---|---|
| CopyCopy the selected formula or cell content | Ctrl+C |
| PastePaste into the target cell or formula bar | Ctrl+V |
| Fill downApply the active formula to subsequent rows | Ctrl+D |
| Format as percentageDisplay results as a percent in Sheets | Ctrl+โง+5 |
FAQ
What is the formula for discount percentage in Google Sheets?
The standard formula is (Original - Sale) / Original, which returns a decimal representing the discount. Format the result as a percentage to display a user-friendly value.
Use (Original minus Sale) divided by Original, then format as a percent.
How do I apply the discount formula to a whole column?
Use ARRAYFORMULA to apply the calculation across many rows: =ARRAYFORMULA((A2:A - B2:B) / A2:A). This fills the entire column automatically.
Use ARRAYFORMULA so every row computes automatically.
What if the original price is zero?
Guard with IF to avoid division by zero: =IF(A2=0, "", (A2 - B2) / A2). For ranges, use a similar pattern with ARRAYFORMULA.
Check for zero originals before dividing.
How can I format the result as a percentage in Sheets?
Select the result cells and apply the percentage format, or use a formatting shortcut such as Ctrl+Shift+5 (Cmd+Shift+5 on Mac) to switch to percent view.
Format the output as percent to show 0.25 as 25%.
Can I round the discount percentage to fewer decimals?
Yes. Wrap the formula with ROUND, e.g., =ROUND((A2 - B2) / A2, 2) to round to two decimals.
Round the result to control decimal places.
The Essentials
- Use (Original - Sale) / Original as the core discount formula
- Leverage ARRAYFORMULA for scalable row-wise calculations
- Guard against zeros and non-numeric data to avoid errors
- Format results as percentage and consider rounding for reports