How to Use GROWTH in Excel
• GROWTH calculates exponential trends and predictions using the formula y = b*m^x, making it ideal for compound growth scenarios like sales acceleration, viral growth, and population modeling—not...
Key Insights
• GROWTH calculates exponential trends and predictions using the formula y = b*m^x, making it ideal for compound growth scenarios like sales acceleration, viral growth, and population modeling—not linear trends. • The function accepts arrays for bulk predictions, eliminating the need for multiple formulas when forecasting several future periods simultaneously. • GROWTH fails with zero or negative y-values because exponential regression requires positive numbers; always validate your data before applying the function.
Introduction to the GROWTH Function
Excel’s GROWTH function performs exponential regression to predict future values based on existing data that follows an exponential growth pattern. Unlike TREND, which fits a straight line through your data, GROWTH models scenarios where change accelerates or decelerates over time.
You’ll recognize exponential growth when each period’s increase is proportional to the current value—think compound interest, viral social media growth, or bacterial populations. If your month-over-month growth rate stays relatively constant (say, 15% monthly), GROWTH will outperform linear predictions significantly.
The fundamental difference: TREND uses y = mx + b (straight line), while GROWTH uses y = b*m^x (exponential curve). Choose GROWTH when your data shows multiplicative growth rather than additive growth.
Here’s the basic syntax:
=GROWTH(known_y's, [known_x's], [new_x's], [const])
A simple example with quarterly revenue showing 20% growth:
A1: Quarter | B1: Revenue
A2: 1 | B2: 10000
A3: 2 | B3: 12000
A4: 3 | B4: 14400
A5: 4 | B5: 17280
A6: 5 | B6: =GROWTH(B2:B5, A2:A5, A6)
This returns approximately 20,736—the predicted Quarter 5 revenue based on the exponential trend.
Understanding the Parameters
Let’s dissect each parameter to understand how GROWTH processes your data:
known_y’s (required): Your historical data points—the values you’re trying to model. Must be positive numbers. This is typically your dependent variable like sales, users, or revenue.
known_x’s (optional): The corresponding x-values for your historical data. If omitted, Excel assumes the array {1, 2, 3, 4…}. You’ll provide this explicitly when your x-values aren’t sequential (missing months, specific dates, etc.).
new_x’s (optional): The x-values for which you want predictions. Omitting this returns fitted values for your existing known_x’s—useful for seeing how well the exponential curve fits your historical data.
const (optional): TRUE or omitted calculates b normally. FALSE forces b to equal 1, changing the formula to y = m^x. You’ll rarely use FALSE unless you have specific mathematical reasons.
Here’s how parameter variations affect output:
A1: Month | B1: Users
A2: 1 | B2: 100
A3: 2 | B3: 150
A4: 3 | B4: 225
A5: 4 | B5: 340
// Predict month 5 (explicit x-values)
D2: =GROWTH(B2:B5, A2:A5, 5)
Result: ~510
// Predict month 5 (implicit x-values)
D3: =GROWTH(B2:B5, , 5)
Result: ~510 (same because x-values are sequential)
// Get fitted values for existing data
D4: =GROWTH(B2:B5)
Result: Returns array {100, 152, 231, 351} showing the curve fit
// Force b=1
D5: =GROWTH(B2:B5, A2:A5, 5, FALSE)
Result: Different prediction based on y = m^x
Basic GROWTH Implementation
Let’s walk through a practical sales forecasting scenario. You run an e-commerce store and want to predict the next quarter’s revenue based on six months of accelerating growth.
A1: Month | B1: Revenue
A2: Jan | B2: 45000
A3: Feb | B3: 49500
A4: Mar | B4: 54450
A5: Apr | B5: 59895
A6: May | B6: 65885
A7: Jun | B7: 72474
A8: Jul | B8: =GROWTH($B$2:$B$7, , 7)
A9: Aug | B9: =GROWTH($B$2:$B$7, , 8)
A10: Sep | B10: =GROWTH($B$2:$B$7, , 9)
The predictions for Jul-Sep would be approximately: $79,721, $87,693, $96,462.
To verify this is truly exponential growth, compare with the underlying formula. GROWTH calculates the base (m) and coefficient (b) automatically:
// Calculate the growth rate manually
D2: Rate (m) | E2: =EXP(LINEST(LN(B2:B7), A2:A7))
D3: Base (b) | E3: =EXP(INDEX(LINEST(LN(B2:B7), A2:A7), 2))
// Manual calculation for July (month 7)
D5: Manual July | E5: =E3 * E2^7
The manual calculation should match GROWTH’s output, confirming the exponential model: Revenue ≈ 40,909 * 1.10^month.
Array Formulas and Multiple Predictions
Rather than writing separate formulas for each future month, GROWTH can return multiple predictions simultaneously. This is where array functionality shines.
In Excel 365 or Excel 2021 with dynamic arrays:
A1: Month | B1: Revenue | D1: Future Month | E1: Prediction
A2: 1 | B2: 10000 | D2: 7 | E2: =GROWTH($B$2:$B$7, $A$2:$A$7, D2:D5)
A3: 2 | B3: 11500 | D3: 8 |
A4: 3 | B4: 13225 | D4: 9 |
A5: 4 | B5: 15209 | D5: 10 |
A6: 5 | B6: 17490 |
A7: 6 | B7: 20114 |
The single formula in E2 automatically spills down to E5, generating all four predictions.
For legacy Excel (pre-365), use Ctrl+Shift+Enter:
// Select E2:E5 first, then type formula, then Ctrl+Shift+Enter
{=GROWTH($B$2:$B$7, $A$2:$A$7, D2:D5)}
The curly braces appear automatically, indicating an array formula.
Alternatively, use individual formulas with absolute references:
E2: =GROWTH($B$2:$B$7, $A$2:$A$7, D2)
E3: =GROWTH($B$2:$B$7, $A$2:$A$7, D3)
// Copy down as needed
The array approach is cleaner and reduces formula maintenance, but individual formulas offer more flexibility for adding conditional logic.
Advanced Applications
Website Traffic Projection with Seasonality Adjustment
GROWTH works well for overall trends, but real data often has noise. Combine it with smoothing techniques:
A1: Week | B1: Visitors | C1: Smoothed | D1: Forecast
A2: 1 | B2: 2400 | C2: =B2 | D2:
A3: 2 | B3: 2850 | C3: =AVERAGE(B2:B3) | D3:
A4: 3 | B4: 3100 | C4: =AVERAGE(B2:B4) | D4:
...
A14: 13 | B14: | C14: | D14: =GROWTH($C$2:$C$13, $A$2:$A$13, A14)
Handling Missing Data Points
GROWTH doesn’t handle gaps well. Use IF to create a clean dataset:
// Source data with gaps
A1: Month | B1: Sales
A2: 1 | B2: 5000
A3: 2 | B3: (blank)
A4: 3 | B4: 6500
A5: 4 | B5: 7200
// Clean data for GROWTH
D2: =IF(B2<>"", B2, "")
// Then filter or use array constant
F2: =GROWTH({5000;6500;7200}, {1;3;4}, 5)
Robust Forecasting with Error Handling
Production forecasts need error management:
=IFERROR(
IF(COUNT(B2:B7)<3, "Insufficient data",
IF(MIN(B2:B7)<=0, "Invalid data",
GROWTH(B2:B7, A2:A7, A8)
)
),
"Calculation error"
)
This formula checks for:
- Minimum data points (at least 3)
- Positive values only
- Calculation errors
Financial Modeling: Compound Annual Growth Rate (CAGR)
Use GROWTH to project revenue under different growth scenarios:
A1: Year | B1: Conservative | C1: Moderate | D1: Aggressive
A2: 2024 | B2: 1000000 | C2: 1000000 | D2: 1000000
A3: 2025 | B3: =GROWTH($B$2, 1, 2, FALSE)*1.08 | C3: =GROWTH($C$2, 1, 2, FALSE)*1.15 | D3: =GROWTH($D$2, 1, 2, FALSE)*1.25
Troubleshooting Common Errors
#NUM! Error
Occurs when known_y’s contains zero or negative values. Exponential regression requires positive numbers:
// Problem
A2: -100 | B2: =GROWTH(A2:A5, , 2) // #NUM!
// Solution: Add offset if data can be negative
B2: =GROWTH(A2:A5+1000, , 2) - 1000
#REF! Error
Mismatched array sizes between known_y’s and known_x’s:
// Problem
=GROWTH(B2:B10, A2:A8, 11) // Different array sizes
// Solution: Match ranges
=GROWTH(B2:B10, A2:A10, 11)
#VALUE! Error
Non-numeric data in your arrays:
// Problem
A2: "January" | B2: 1000
A3: 2 | B3: 1200
=GROWTH(B2:B3, A2:A3, 4) // #VALUE! because A2 is text
// Solution: Use numeric x-values
=GROWTH(B2:B3, {1;2}, 4)
Unrealistic Predictions
GROWTH assumes the exponential trend continues indefinitely. Always sanity-check results:
// Add reality checks
=IF(GROWTH(B2:B7, A2:A7, A8) > B7*2,
"Warning: Prediction exceeds 2x current value",
GROWTH(B2:B7, A2:A7, A8))
Exponential models break down when growth rates change, markets saturate, or external factors intervene. Use GROWTH for short-term forecasts (1-3 periods ahead) and validate predictions against business logic. For long-term planning, consider multiple scenarios and combine statistical forecasts with domain expertise.