Line charts in Excel - Cell-by-Cell Formula Trace

Sample Data

Monthly sales data for five months, with months in column A and sales numbers in column B.

Cell	Value
A1	Month
B1	Sales
A2	Jan
B2	100
A3	Feb
B3	120
A4	Mar
B4	90
A5	Apr
B5	150
A6	May
B6	130

Formula Trace

LINEST(B2:B6, {1,2,3,4,5}, TRUE, TRUE)

Step 1: Identify known y-values: B2:B6 = {100, 120, 90, 150, 130}

Step 2: Identify known x-values: A2:A6 = {"Jan", "Feb", "Mar", "Apr", "May"}

Step 3: Convert months to numbers: Jan=1, Feb=2, Mar=3, Apr=4, May=5

Step 4: Apply LINEST to y-values {100,120,90,150,130} and x-values {1,2,3,4,5}

Cell Reference Map

     A      B
  +-------+-------+
1 | Month | Sales |
  +-------+-------+
2 | Jan   | 100   |
3 | Feb   | 120   |
4 | Mar   | 90    |
5 | Apr   | 150   |
6 | May   | 130   |

References:
- B2:B6 for sales values
- A2:A6 converted to numbers 1-5 for months

The formula uses sales data from B2:B6 and numeric month values converted from A2:A6.

Result

     A      B       C
  +-------+-------+---------+
1 | Month | Sales | Formula |
  +-------+-------+---------+
2 | Jan   | 100   | 104     |
3 | Feb   | 120   | 113     |
4 | Mar   | 90    | 122     |
5 | Apr   | 150   | 131     |
6 | May   | 130   | 140     |

Line chart plots Sales (B2:B6) vs Month (A2:A6 as numbers 1-5).
The formula column shows predicted sales from the line: Sales ≈ 9*MonthNumber + 95.

The line chart would show points for actual sales and a line for predicted sales increasing over months.

Sheet Trace Quiz - 3 Questions

Why can't the LINEST function use the text months directly as x-values?

A Because LINEST only works with numbers for x-values

B Because text values cause the formula to sum instead

C Because months are too long to process

D Because LINEST only accepts dates, not text

Key Result

LINEST fits a straight line to y-values based on numeric x-values, returning slope and intercept.