Function Problems

How to Fix Excel LINEST Returning Only the First Value

Kushani Nimanthika Oct 12, 2026 869 views

Question details

The user needs to correctly output the full array of statistical values from the LINEST function instead of just a single result.

How to Fix Excel LINEST Returning Only the First Value

Product: Excel
Device & OS: not provided
Scenario: Calculating linear regression statistics using the LINEST function on a dataset.
Observed behavior: The LINEST formula calculates successfully but only displays the first value (the slope) rather than the complete array of regression statistics.

Before you start

Verify which version of Excel you are using, as older versions (2019 and earlier) require a different keyboard shortcut to display array results compared to Microsoft 365 or Excel 2021.

Solution 1Recommended

Use a Legacy Array Formula (Ctrl+Shift+Enter)

Required for older versions of Excel (Excel 2019 and earlier) that do not support dynamic array spilling.

In traditional Excel versions, formulas that generate multiple results must be entered as a legacy array formula. If you simply press Enter, Excel only extracts the first item of the array, which is why you only see a single number.

Select the output range

Highlight a blank block of cells that matches the expected output size. For simple linear regression with additional statistics enabled, highlight a range that is 2 columns wide and 5 rows high.

Enter the formula

Click into the formula bar at the top of the screen and type your formula, such as =LINEST(Y_range, X_range, TRUE, TRUE).

Confirm as an array formula

Instead of pressing Enter, hold down the Ctrl and Shift keys, then press Enter. This will populate the selected range with the full array of statistics.

Use a Legacy Array Formula (Ctrl+Shift+Enter)

Verify Array Formula: If executed correctly, Excel will automatically place curly brackets {} around your formula in the formula bar.

Solution 2

Utilize Dynamic Arrays in Newer Excel Versions

Best for Microsoft 365 and Excel 2021 users, taking advantage of modern dynamic array features.

Solution 3

Verify Data Formatting and Consistency

Fixes issues where LINEST fails to process data correctly due to hidden text formats or inconsistent dataset sizes.

Powerful Alternative

Calculate Linear Regression Effortlessly in WPS Office

If you are looking for a reliable, lightweight tool that handles complex statistical formulas with ease, WPS Spreadsheet is the perfect solution. It fully supports array formulas like LINEST and provides excellent compatibility with your existing spreadsheet files.

1. Open your data file: Launch WPS Spreadsheet and open the document containing your dataset.
2. Select the output grid: Highlight an empty 2x5 grid of cells where you want the regression statistics to appear.
3. Input the LINEST function: Type the formula =LINEST(Y_range, X_range, TRUE, TRUE) into the formula bar.
4. Execute the array: Press Ctrl+Shift+Enter to instantly compute and display the complete array of statistical results.

Fully compatible with Microsoft Excel's .xlsx format and LINEST syntaxNatively supports advanced array formulas for statistical outputFree, lightweight, and easy to use without a steep learning curveAvailable across multiple devices including Windows, Mac, and mobile

microsoft office alternative - wps office

Frequently Asked Questions

How do I force LINEST to show additional statistical data?

To retrieve the full statistics, you must set the fourth argument (stats) in the formula to TRUE. Your formula should look like =LINEST(Y_range, X_range, TRUE, TRUE). Without this, LINEST will only calculate the slope and y-intercept.

Why does my LINEST formula return a #REF! error?

This usually happens if the known_x's and known_y's ranges have a different number of rows or columns. You must ensure both data ranges perfectly match in size before running the calculation.

Does the LINEST function ignore blank cells?

No, LINEST does not automatically ignore empty cells. You must ensure your data ranges are completely contiguous and contain no blank cells or text values; otherwise, Excel will return a #VALUE! error.

Can I use LINEST for multiple independent variables?

Yes, LINEST supports multiple regression. To do this, simply select multiple adjacent columns for your known_x's range. Ensure your output range is wide enough to accommodate a slope coefficient for each independent variable plus the y-intercept.

Excel & Data Problems