How to Make a Time Series Plot in Excel

A time series plot is one of the most fundamental tools in data analysis, showing you how a specific metric changes over a period of time. Whether you're tracking daily sales, weekly website traffic, or monthly customer sign-ups, visualizing that data chronologically is the first step toward uncovering trends and making informed decisions. This guide will walk you through exactly how to prepare your data, create a clear time series plot in Excel, and customize it to tell a compelling story.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What Exactly Is a Time Series Plot?

Before we jump into the steps, let's quickly clarify what we're building. A time series plot is simply a line chart that displays data points at successive, evenly spaced intervals in time. Your horizontal (X-axis) will always represent time (days, weeks, months, years), and your vertical (Y-axis) will represent the quantity you are measuring (revenue, sessions, units sold).

Using a time series plot helps you instantly spot:

• Trends: Is your metric generally increasing, decreasing, or staying flat over time?
• Seasonality: Are there predictable patterns that repeat at certain times of the year, like a sales spike every December?
• Outliers: Were there any unusual spikes or dips that warrant further investigation?

It's the visual foundation for almost any business performance analysis.

Step 1: Get Your Data Ready

The most common reason a chart doesn't work in Excel is poorly structured data. Getting this right from the start will save you a lot of headaches. Your goal is to have clean, simple data organized in two columns.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Format Your Time Column

Excel needs to correctly recognize your time data as actual dates, not just text that looks like a date.

1. Your first column should contain your dates or timestamps (e.g., '1/1/2024', '1/2/2024', etc.). Your second column will contain the corresponding value you want to plot (e.g., '1,500', '1,750').
2. Select your entire date column.
3. Navigate to the Home tab on the Excel ribbon, find the "Number" group, and open the dropdown menu.
4. Choose either Short Date or Long Date. This tells Excel to treat these cells as chronological values.

A simple check to see if it worked: If your dates are now right-aligned in their cells, Excel has likely recognized them correctly.

Arrange Your Data Logically

Your data table should be simple and contiguous, with no empty rows or columns in the middle. Here’s the ideal structure:

• Column A: Your "time" component (e.g., Date)
• Column B: Your "value" component (e.g., Daily Revenue)
• Row 1 should contain your headers.
• Your data should be sorted chronologically, from oldest to newest. (You can do this by selecting your data and going to Data > Sort).

Here’s an example of perfectly formatted data:

Step 2: Create the Basic Time Series Plot

Once your data is clean and organized, creating the chart itself takes only a few clicks.

1. Select Your Data: Click any cell within your data table. Excel is usually smart enough to detect the entire range automatically. To be safe, you can also click and drag to select all the data, including the headers in the first row.
2. Go to the Insert Tab: On the Excel ribbon, click the Insert tab.
3. Choose Your Chart Type: In the "Charts" group, find the icon for "Insert Line or Area Chart." Click it.
4. Select a "Line with Markers" Chart: A dropdown menu will appear with several line chart options. For a time series plot, the best starting point is usually the "Line with Markers" chart. This option gives you a connecting line that shows the trend, plus individual points (markers) for each data point, making it easy to see specific values.

And that's it! Excel will instantly generate a basic time series plot on your worksheet.

Step 3: Customize Your Plot for Clarity and Impact

Your basic plot is ready, but it probably looks a little plain. A great chart communicates information quickly and clearly. Let's add some essential elements and formatting to make your plot professional and easy to understand.

Give Your Chart a Clear Title and Axis Labels

A chart with no labels is just a picture. Context is everything.

1. First, be sure the Chart Design tab is selected.
2. On the Excel ribbon, open the Add Chart Element button.
3. Hover over Axis Titles to add and edit titles for both the primary horizontal (time) and primary vertical (value) axes.
4. Hover over the Chart Titles tab and edit the title to accurately describe what the chart is showing. For example, change "Sales" to something more descriptive like "Daily Shopify Sales - January 2024."

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Adjust the Axes for Better Readability

Sometimes, Excel's default settings for the axes aren't ideal. You might have too much empty space at the bottom of the chart, or the date labels might be too crowded.

To fix this, right-click on the axis you want to change (either the vertical value axis or the horizontal date axis) and select Format Axis. A pane will open on the right with several options:

• For the Value (Y) Axis: Under "Bounds," you can set a minimum value higher than zero if all your data points are well above it. This makes the variations in your data appear more pronounced. Under "Number," you can format the labels as Currency, Percentage, etc.
• For the Date (X) Axis: Under "Units," you can change how often a label appears. For example, instead of showing every single day, you can set the "Major" units to 7 to show a label every week.

Add a Trendline to See the Bigger Picture

A trendline is a powerful feature that draws a line of best fit through your data, instantly revealing the overall direction.

1. Select your chart.
2. Look at the Add Chart Element tab in the top-right and select Trendline from the dropdown menu.
3. You can choose different types. Linear is great for showing a simple, straight-line trend of growth or decline. Moving Average is useful for smoothing out day-to-day volatility to see a more stable pattern. Set the "Period" to 7 to create a 7-day moving average.

Troubleshooting Common Problems

Here are a couple of typical issues you might encounter while working with data and how to solve them:

Problem: My Horizontal Axis Just Shows '1, 2, 3...' Instead of Dates

This is the most frequent issue people will have. It happens because Excel does not see the data in your date column as chronological data — it just sees them as text.

The Fix: Go back to Step 1 and double-check that your date column is correctly formatted as a "Date" type. If it's still not working, a good tip is to insert a new column and use the =DATEVALUE() formula. Example: if your text format of a date is in cell A2, you can use:

=DATEVALUE(A2)

This forces the text into a date serial number and will fix the formatting issues. Once that's done, delete the original column A and recreate your new, polished chart from the newly converted data.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Problem: I Have Missing Data, and It's Creating a Gap in My Line

If your data is missing an entire row for a specific day, you will likely see a blank spot. You might not actually want that.

The Fix: Right-click your chart and choose Select Data. In the dialog box that pops up, click the button labeled Hidden and Empty Cells on the bottom left. Here, you get three options:

• Gaps: This is the default. It leaves a break in the line.
• Zero: This treats any empty cell as a zero value, causing the line to drop a lot on your axis. Make sure that is the case you care about. When in doubt, a 'gap' is always better.
• Connect data points with line: This tells Excel to just ignore the gap altogether and draw a line that connects the points before and after the missing one. It can create inaccurate visualizations, so be careful.

Final Thoughts

And that's how it's done. Creating a time series plot in Excel is a fundamental skill that provides immediate visibility on all of your key business trends. Following these methods, you can get simple, quick insights whenever you need them. With careful and diligent practice, you can move past basic visualizations and communicate the deeper business stories that lie inside the numbers.

Most people at many companies would admit that the time-consuming part is not necessarily the 'chart-making,' it's getting your data prepped in the first place. For that, we created Graphed to automate the tedious parts of digital charting like manually pulling across many different platforms. Simply connect services like Google Analytics, Shopify, or HubSpot, and create your visualizations in plain English by asking something like "build a line chart of Shopify revenue vs Facebook ad spend for the last 90 days." And just like that, you skip over the spreadsheet headaches and focus on the important task: making good business decisions.