Ever wondered how to predict trends or uncover hidden patterns in your data? That’s exactly what linear regression helps you do — and in Google Sheets, there’s a powerful built-in function called LINEST that makes it surprisingly easy. Whether you’re analyzing business growth, tracking fitness progress, or exploring trends in school grades, LINEST helps you turn numbers into insights.
Don’t worry if you’re not a stats expert — this guide will walk you through LINEST like you’re learning from a friend. Let’s explore how to use this function in real-world situations — and why it’s even more powerful in Google Sheets than in Excel!
What is LINEST in Google Sheets?
The LINEST function (short for “Linear Estimation”) calculates the statistics for a straight line that best fits your data — also known as a linear regression. It returns key values like slope, intercept, R² value (which shows how well the line fits), and even standard errors — all in one shot.
Plain English: You give it some x-values (independent variable) and y-values (dependent variable), and LINEST shows you the trend line — or the “best guess” at how y changes with x.
Real-Life Example: Predicting Sales from Advertising Spend
Imagine you run a small online store and want to see how your advertising budget affects your monthly sales. You’ve recorded your ad spend and sales for the last 6 months. Here’s what your data looks like:
| Month | Ad Spend ($) | Sales ($) |
|---|---|---|
| January | 500 | 3200 |
| February | 700 | 4000 |
| March | 800 | 4300 |
| April | 1000 | 5000 |
| May | 1200 | 5800 |
| June | 1500 | 6100 |
Now you want to find out: If I spend $1600 on ads, what can I expect in sales? LINEST can help you build a formula for that.
How to Use LINEST in Google Sheets (Step-by-Step)
Step 1: Enter the Data
Place your ad spend data in one column (e.g., B2:B7) and your sales data in the next column (C2:C7).
Step 2: Apply the LINEST Formula
In an empty cell (like E2), enter this array formula:
=ARRAYFORMULA(LINEST(C2:C7, B2:B7, TRUE, TRUE))This returns a table with multiple outputs:
| Slope | Intercept | R² | Standard Error (Slope) | Standard Error (Intercept) |
|---|---|---|---|---|
| 2.88 | 1900 | 0.985 | 0.15 | 80 |
This means the formula for sales is:
Sales = 2.88 × Ad Spend + 1900Step 3: Predict Future Values
To predict sales from an ad spend of $1600, use:
=2.88*1600 + 1900This gives you an estimated sale of $6488.
Pro Tips: Unlocking the Power of LINEST
- Use with Named Ranges: Define ranges like
adsandsalesto simplify formulas. - Use TREND to Predict Automatically: Combine LINEST with
TRENDfor dynamic forecasting:
=TREND(C2:C7, B2:B7, 1600)This returns the same $6488 result dynamically — handy when using live inputs!
- Visualize with a Chart: Select your data and insert a scatter plot with a trendline. Enable the “Use Equation” and “R²” options to see the LINEST output graphically.
Advanced Techniques Only in Google Sheets
Google Sheets has some exclusive tricks that Excel doesn’t support easily:
- Auto-expanding LINEST: Use
ARRAYFORMULA(LINEST(...))to fill a matrix without dragging or CTRL+SHIFT+ENTER like in Excel. - Dynamic LINEST with QUERY: Filter data dynamically before regression using:
=ARRAYFORMULA(LINEST(QUERY(C2:C100, "where B > 1000"), QUERY(B2:B100, "where B > 1000"), TRUE, TRUE))This runs a LINEST only for months where ad spend was over $1000 — all in one formula!
Key Benefits of Using LINEST in Google Sheets
- Quick & powerful: Get full regression statistics in one formula.
- Highly flexible: Works with live data, filtered views, and named ranges.
- Advanced forecasting: Combine with
TRENDandQUERYfor pro-level insights. - Excel-free entry: Unlike Excel, Google Sheets doesn’t require special keystrokes to return arrays — LINEST works smoothly with
ARRAYFORMULA.
Quick Reference Cheat Sheet
| Task | Formula |
|---|---|
| Basic LINEST | =LINEST(Y-values, X-values) |
| LINEST with full stats | =LINEST(Y-values, X-values, TRUE, TRUE) |
| Auto-expand LINEST in a block | =ARRAYFORMULA(LINEST(…)) |
| Predict Y from X | =slope * X + intercept |
| Dynamic prediction | =TREND(Y-values, X-values, New X) |
Using LINEST in Google Sheets is like having a mini data scientist built into your spreadsheet. Whether you’re predicting sales, modeling growth, or exploring trends, this function gives you fast, reliable insights without any complex tools. Plus, with features like ARRAYFORMULA and QUERY integration, Google Sheets gives you a modern twist that Excel just can’t match.
So go ahead — play with your data, experiment with new inputs, and use LINEST to find the story hiding in your numbers. It’s easier (and more fun) than you think!