Do you want to learn how to graph y=mx+b in Microsoft Excel? Keep reading!

For academic and scientific reasons, you might need to plot a linear curve using the y=mx+b (y=mx+c in the UK) equation. You can do it in many ways, including certain Excel functions and Excel charting tools.

Today, I will walk you through multiple methods to graph y=mx+b in easy and proven ways. Let’s get started!

Graph y=mx+b in Excel Manually

You can follow this method if you’re learning linear equations and the concept of slope (m) and y-intercept (b) as a middle school mathematics course outline. Here you’ll learn the entire process from using the equation to derive the necessary values to create a chart in Excel that displays the linear equation.

Let’s take an example from kinematics in physics, where the equation of motion follows the linear form

y=mx+b

Imagine an object moving with constant acceleration. The velocity-time equation follows:

v=u+at

where:

• v (y-axis) = final velocity (m/s)
• u = initial velocity (m/s) (analogous to 𝑏)
• a = acceleration (m/s²) (analogous to 𝑚)
• t (x-axis) = time (seconds)

Now, here are the values given to you to create a graph for this velocity-time equation:

• Time (x-axis, t): -5, 0, 5, 10, 15, 20
• Initial velocity (b = u): 10 m/s
• Acceleration (m = a): 2 m/s²

Using the given data above, you get the following velocity-time equation:

v=10+2t

Open an Excel workbook and go to Sheet1.

Create the dataset using the given information in the charting problem mentioned earlier. You can refer to the screenshot shown above.

Make these column headers:

• Time (t) X-Axis
• Velocity (v) Y-Axis

Populate the X-axis values from the given data above in this charting problem.

Now, select the first cell just below the Velocity (v) Y-Axis column and enter the following formula into it:

=10+2*A2

Change A2 to a different cell address according to your own dataset.

Hit Enter to calculate the cell.

Now, select this cell again and drag the fill handle down to generate the values for the rest of the Y-axis points.

You must drag the fill handle down until the cell where parallel data exists in the Time (t) X-Axis column.

Excel will copy the formula from the initial cell and use that to generate the Y-axis values.

So far, you’ve created the initial dataset needed to plot a linear chart using the y=mx+b equation.

Select the dataset you just created and click on the Insert tab in the Excel ribbon menu.

Now, go to the Charts block and click on the Insert Scatter (X,Y) command.

Excel will draw a linear equation chart for you using the selected dataset.

Now, click on the chart to get the chart control tools in the top right corner of the chart object.

There, click on the plus (+) icon which is the Chart Elements tool. The Chart Elements context menu will show up.

Hover the cursor over the Trendline option in the Chart Elements context menu and click on the right side overflow arrow.

A new context menu will open showing a More Options menu. Click on that.

The Format Trendline navigation panel will open on the right side.

Checkmark the checkbox for Display Equation on chart at the bottom of the Trendline Options section in the Format Trendline sidebar.

That’s it! You’ve successfully graphed y=mx+b in Excel using the manual method starting from generating values for the Y axis using given data, then creating a chart using the Insert Scatter (X,Y) command, and adding the linear equation to the chart.

Using Excel VBA

You’ve learned the manual method to graph the y=mx+b linear equation in Excel. Now, I’ll show you how you can automate the process for any example problem where you’ve got the fowlloing values given in the problem statement:

• Known X-axis values
• Slope (m)
• Intercept (b)

You can use an Excel VBA macro to automate this process. This method involves writing VBA scripts. However, if you follow the exact steps mentioned in thsi tutorial, you won’t have tow rite any code or even waste time in trial and error.

Firstly, open this Microsoft Excel tutorial and learn how to set up a VBA macro in Excel:

📒 Read More: How To Use The VBA Code You Find Online

Are you ready to create your own macro? Perfect! Use the script given below to set up a macro that will automate the graphing problem:

Sub GraphLinearEquation()
    Dim xRange As Range
    Dim m As Double
    Dim b As Double
    Dim yRange As Range
    Dim chartResponse As VbMsgBoxResult
    Dim chartTitle As String
    Dim chtObj As ChartObject
    Dim eqn As Shape

    ' Prompt user to select the x-axis values
    On Error Resume Next
    Set xRange = Application.InputBox("Select the x-axis values (range of cells):", Type:=8)
    On Error GoTo 0
    If xRange Is Nothing Then Exit Sub

    ' Prompt user to input the slope (m)
    m = Application.InputBox("Enter the slope (m):", Type:=1)
    If m = 0 And Not IsNumeric(m) Then Exit Sub

    ' Prompt user to input the y-intercept (b)
    b = Application.InputBox("Enter the y-intercept (b):", Type:=1)
    If b = 0 And Not IsNumeric(b) Then Exit Sub

    ' Prompt user to select the destination cells for the y-axis values
    On Error Resume Next
    Set yRange = Application.InputBox("Select the destination cells for the y-axis values:", Type:=8)
    On Error GoTo 0
    If yRange Is Nothing Then Exit Sub

    ' Calculate y values using y = mx + b
    Dim i As Long
    For i = 1 To xRange.Cells.Count
        yRange.Cells(i).Value = m * xRange.Cells(i).Value + b
    Next i

    ' Ask if the user wants to create an XY scatter chart
    chartResponse = MsgBox("Do you want to create an XY scatter chart?", vbYesNo)
    If chartResponse = vbYes Then
        ' Create the chart in the active worksheet
        Set chtObj = ActiveSheet.ChartObjects.Add(Left:=100, Width:=375, Top:=50, Height:=225)
        With chtObj.Chart
            .ChartType = xlXYScatterLines ' Use scatter with straight lines
            .SetSourceData Source:=Union(xRange, yRange)
            
            ' Add chart title and axis labels
            chartTitle = "Linear Equation: y = " & m & "x + " & b
            .HasTitle = True
            .ChartTitle.Text = chartTitle
            .Axes(xlCategory, xlPrimary).HasTitle = True
            .Axes(xlCategory, xlPrimary).AxisTitle.Text = "X Values"
            .Axes(xlValue, xlPrimary).HasTitle = True
            .Axes(xlValue, xlPrimary).AxisTitle.Text = "Y Values"
        End With

        ' Add the linear equation as a text box on the chart
        Set eqn = chtObj.Chart.Shapes.AddTextbox(msoTextOrientationHorizontal, 100, 10, 200, 50)
        eqn.TextFrame.Characters.Text = chartTitle
        eqn.TextFrame.Characters.Font.Size = 12
    End If
End Sub

Are you done creating the macro? Awesome! Let me walk you through the simple process to use the VBA macro.

Press Alt + F8 on the active worksheet where you’ve got the known X-axis dataset, slope, and intercept values.

The Macro dialog box will show up. Select the GraphLinearEquation macro and hit Run.

A series of visual instructions or dialog boxes will now walk you through the process of creating the scatter plot with the linear equation.

Firstly, you’ll see an input box that asks you to select the cell range that contains the known X-axis values. You can directly enter the cell range address in the input box, like $A$2:$A$7 or use the mouse cursor to select the source cell range.

The second input box will ask you to select the value or cell for the slope or m.

Now you should see the third dialog box that directs you to select the cell that contains the value for intercept or b.

The VBA script can now calculate the values for Y-axis using the already supplied information.

Therefore, the macro will show a fourth dialog box asking you the cell range address for the destination of the Y-axis values. You can use this dataset later for reference purposes.

Now, an input box will ask you whether you want to create an XY scatter chart.

If you click Yes, the script will create the scatter chart in the active worksheet.

Conclusions

So, now you know how to graph y=mx+b in Microsoft Excel using the manual method and Excel VBA.

If you liked the Excel tutorial, use the comment box to share an acknowledgement. Also, if you need to share a feedback, you can use the comment box.