How to Make a Linear Graph in Excel

Creating a linear graph in Excel is one of the quickest ways to see the relationship between two variables and spot trends in your data. Whether you're tracking sales growth over time or figuring out if your ad spend is actually leading to more customers, a simple line can tell a powerful story. This guide will walk you through exactly how to create, customize, and interpret a linear graph in Excel, step by step.

What Exactly is a Linear Graph?

In simple terms, a linear graph uses a straight line to show a consistent relationship between two sets of data. One set of data is your independent variable (the thing you control or that changes steadily, like time), which goes on the horizontal X-axis. The other is your dependent variable (the thing you are measuring), which goes on the vertical Y-axis.

This type of graph is perfect for:

• Visualizing sales performance month over month.
• Tracking website traffic or social media follower growth.
• Showing the relationship between ad spend and leads generated.
• Plotting scientific data to identify a consistent rate of change.

The goal is to see if, as one variable increases, the other variable increases or decreases at a steady rate. That "steady rate" is what gives you the straight line.

Step 1: Get Your Data Ready

Before you can make a graph, you need clean, organized data. Excel won't know what to do with a messy spreadsheet. For a linear graph, all you need are two columns.

Let’s use an example of tracking a company's quarterly revenue. Your spreadsheet should look like this:

• Column A (Independent Variable): The Quarter (e.g., Q1 2023, Q2 2023, Q3 2023, Q4 2023)
• Column B (Dependent Variable): The Revenue for that quarter (e.g., $50,000, $55,000, $62,000, $68,000)

It's crucial to have clear headers for each column. Your simple data set might look like this:

Make sure your revenue column is formatted as currency or a number. If it’s formatted as text, Excel will get confused.

Step 2: Choose and Insert Your Graph

With your data prepped, creating the graph only takes a few clicks. It's important to choose the right chart type, as a "Line" chart and a "Scatter" chart behave differently in Excel.

• 2D-Line Chart: Best for when your X-axis categories are text and evenly spaced, like days, months, or quarters. It treats the X-axis as evenly sequenced categories.
• Scatter (X,Y) Chart: Best for when you have two sets of numerical data and want to see their relationship. It plots each XY data point precisely according to its numerical value on both axes.

For our time-based revenue example, a standard 2D-Line Chart is the best fit.

To create your graph:

1. Click and drag to select the entire data range you want to plot, including the headers. In our example, that would be cells A1 through B6.
2. Go to the Insert tab on the Excel ribbon at the top of the screen.
3. In the Charts section, click on the icon that looks like a small line graph (labeled "Insert Line or Area Chart").
4. Under the "2-D Line" section, select the first option, which is simply called Line.

Excel will instantly generate your chart and place it on your worksheet. You’ll see a graph with the quarters along the bottom (X-axis) and the revenue values on the side (Y-axis), connected by a nice, clean line.

Step 3: Customize Your Graph for Readability

A basic chart is functional, but a well-formatted chart tells a story clearly and professionally. You'll want to add context so anyone who sees it immediately understands the information.

First, click on your new chart to bring up the Chart Design and Format tabs at the top of Excel. You'll also see a "+" icon appear on the right side of the chart.

Add a Chart Title and Axis Labels

This is the most important customization you can make. An untitled chart is meaningless.

1. Click the "+" icon next to your chart.
2. Check the box for Chart Title. A title box will appear above your chart. Double-click it and type something descriptive, like "Quarterly Revenue Growth (2023-2024)".
3. Check the box for Axis Titles. Two new text boxes will appear. Double-click the vertical axis title and label it "Revenue ($)". Change the horizontal axis title to "Quarter".

Add a Linear Trendline

A trendline is a straight line that best represents the overall direction of your data. This is what officially makes your chart a "linear graph" and is incredibly helpful for visualizing the underlying trend.

1. Right-click on the data line inside your chart.
2. From the menu that appears, select Add Trendline...
3. A "Format Trendline" panel will open on the right side of your screen. Ensure that Linear is selected as the trend type. A new, usually dotted, line will appear on your chart, showing the general upward trend of your revenue.

Display the Equation and R-squared Value

For more advanced analysis, you can display the mathematical formula behind your trendline.

While still in the "Format Trendline" panel, scroll down and check the boxes for:

• Display Equation on chart: This will show you the y = mx + b formula for your trendline, which describes the exact relationship between your quarters and revenue.
• Display R-squared value on chart: The R² value (from 0 to 1) tells you how well your data fits the linear trendline. A value close to 1 means your data points are very close to forming a perfect straight line.

Adjust Colors and Style

To make your chart visually appealing or match your brand, click on the chart and go to the Chart Design tab. Here, you can:

• Choose from dozens of pre-set color schemes and styles.
• Click "Change Colors" to select a different palette.
• Double-click on the line or data points themselves to open the format pane and manually change their color, thickness, and style.

Common Pitfalls and How to Avoid Them

Creating a linear graph is usually smooth, but a few common issues can trip people up.

• Choosing the Wrong Graph Type: If you incorrectly plot time-series data using a Scatter chart, Excel might not treat the dates or quarters correctly. For dates and categories, stick with a Line chart. For plotting two sets of changing numbers (like ad spend vs. sales), a Scatter chart is better.
• Data Formatted as Text: If your dates or numbers are formatted as text, Excel might not plot them correctly. Either the graph won't generate, or the axes will look scrambled. Always double-check your column formatting.
• Not Enough Data: Trying to show a linear trend with only two or three data points is not very meaningful. A linear graph is most effective when you have a sequence of data points that can reveal a consistent pattern.
• Forgetting Labels: It's worth repeating – a graph without a title and labeled axes is confusing. Always take the extra 30 seconds to add them.

Pro Tip: Add a Forecast

One of the most powerful features of adding a trendline is the ability to create simple forecasts.

In the "Format Trendline" pane, you’ll see a section for Forecast. You can enter a number into the "Forward" box to extend your trendline into the future. For example, if you input "2", Excel will extend the linear trend two periods (quarters, in our example) into the future, giving you a statistical estimate of what your revenue might be.

This is an easy way to project future performance based on past data, but remember it's a simple mathematical projection and doesn’t account for market changes or other external factors.

Final Thoughts

Mastering the linear graph in Excel is a fundamental skill that transforms raw numbers into a clear, visual story. By following these steps, you can quickly build charts that not only look professional but also provide critical insights into your business trends, helping you make smarter, data-driven decisions.

While Excel is fantastic for one-off analyses, building reports can become repetitive, especially when you need to pull fresh data from different sources every week. We built Graphed because we wanted to automate that entire process. Instead of downloading CSVs and building charts manually, you can connect your data sources (like Google Analytics, Shopify, or your CRM) and ask a simple question like, "Show me my quarterly revenue trend over the last two years." Graphed instantly builds a live, interactive dashboard that updates automatically, saving you hours of busywork.