How to Create a Line Chart for Time Series Data

Raw numbers in a column tell you what happened. A line chart tells you the story of how it happened over time.

A table showing monthly revenue figures for twelve months requires effort to interpret. You read January, then February, then March, mentally comparing each one to the last, trying to build a picture of the trend in your head. A line chart of the same data shows the entire story in one glance. The slope going up means growth. The dip in July stands out immediately. The sharp climb in November tells you something changed. Patterns that take minutes to find in a table take seconds to see in a line chart.

Line charts are the standard visualization for time series data because they are built around the idea of continuity. Each point connects to the next, showing not just where values were at each moment but how they moved between moments. The line itself carries meaning that a bar chart or table cannot show.

This guide walks through creating line charts for time series data step by step using both Python with matplotlib and Excel, with real examples you can adapt for any time-based dataset.

What Is Time Series Data?

Time series data is any dataset where measurements are recorded at regular intervals over time. Stock prices recorded every minute. Daily website visitors tracked for a year. Monthly sales figures for three years. Hourly temperature readings for a week. What makes it time series data is not just that timestamps are present but that the order of the measurements matters and the time between them is consistent or meaningful.

Think of it like a patient’s health chart in a hospital. The chart records temperature, blood pressure, and heart rate at regular intervals. The individual readings matter but the pattern over time, whether things are improving or deteriorating, is what actually informs decisions. A line chart of those readings communicates that pattern instantly in a way that a table of numbers never could.

The line chart works so well for time series data because the horizontal axis represents time flowing forward from left to right, which matches how humans naturally think about chronological information. The vertical position of the line at any point shows the value at that moment and the slope of the line between two points shows whether things improved or declined in between.

Setting Up Your Environment

For Python, install the required libraries:

pip install matplotlib pandas numpy

Matplotlib is the core charting library. Pandas handles the data and makes working with dates significantly easier. Numpy is used for any numerical operations needed during chart preparation.

python

import pandas as pd
import matplotlib.pyplot as plt
import matplotlib.dates as mdates
import numpy as np

For Excel, no installation is needed. The examples in the Excel section use features available in Excel 2016 and all versions after it.

Step by Step: Creating a Line Chart for Time Series Data in Python

Step 1: Create or Load Your Time Series Dataset

Start with a dataset that has a date column and at least one numeric column to plot. This example creates twelve months of daily sales data:

python

import pandas as pd
import numpy as np

np.random.seed(42)

dates = pd.date_range(start='2024-01-01', end='2024-12-31', freq='D')

base_trend = np.linspace(8000, 14000, len(dates))
seasonality = 2000 * np.sin(np.linspace(0, 4 * np.pi, len(dates)))
noise = np.random.normal(0, 800, len(dates))

revenue = base_trend + seasonality + noise

df = pd.DataFrame({
    'date': dates,
    'revenue': revenue.round(2)
})

print(df.head())
print(f"Date range: {df['date'].min()} to {df['date'].max()}")
print(f"Revenue range: {df['revenue'].min():.2f} to {df['revenue'].max():.2f}")

This dataset has a realistic structure, an upward trend combined with seasonal patterns and daily noise, which makes it a good test case for visualizing time series data clearly.

Step 2: Plot a Basic Line Chart

python

import matplotlib.pyplot as plt

fig, ax = plt.subplots(figsize=(12, 5))

ax.plot(df['date'], df['revenue'], color='#2E86AB', linewidth=1.2)

ax.set_title('Daily Revenue 2024', fontsize=16, fontweight='bold', pad=15)
ax.set_xlabel('Date', fontsize=12)
ax.set_ylabel('Revenue ($)', fontsize=12)

plt.tight_layout()
plt.show()

figsize=(12, 5) creates a wide chart that suits time series data well. Time series charts should almost always be wider than they are tall because the horizontal axis carries the most important information, the progression of time. A square or tall chart compresses the time axis and makes trends harder to read.

Step 3: Format the Date Axis

Daily data plotted over a full year produces a crowded horizontal axis if you do not control how dates are displayed. Matplotlib’s mdates module handles date formatting cleanly:

python

import matplotlib.dates as mdates

fig, ax = plt.subplots(figsize=(12, 5))

ax.plot(df['date'], df['revenue'], color='#2E86AB', linewidth=1.2)

ax.xaxis.set_major_locator(mdates.MonthLocator())
ax.xaxis.set_major_formatter(mdates.DateFormatter('%b %Y'))

plt.xticks(rotation=45, ha='right')

ax.set_title('Daily Revenue 2024', fontsize=16, fontweight='bold', pad=15)
ax.set_xlabel('')
ax.set_ylabel('Revenue ($)', fontsize=12)

ax.yaxis.set_major_formatter(plt.FuncFormatter(lambda x, p: f'${x:,.0f}'))

plt.tight_layout()
plt.show()

MonthLocator() places one tick mark at the start of each month regardless of how many days of data you have. DateFormatter(‘%b %Y’) formats each tick as Jan 2024, Feb 2024 and so on. The rotation=45 prevents the labels from overlapping each other. Formatting the y-axis with dollar signs and commas makes the revenue values immediately readable without requiring viewers to count zeros.

Step 4: Add a Rolling Average Line to Smooth the Trend

Daily data contains a lot of noise that can obscure the underlying trend. Adding a rolling average line on top of the raw data shows both the detail and the trend simultaneously:

python

df['rolling_avg_30d'] = df['revenue'].rolling(window=30).mean()

fig, ax = plt.subplots(figsize=(12, 5))

ax.plot(df['date'], df['revenue'],
        color='#2E86AB', linewidth=0.8,
        alpha=0.4, label='Daily Revenue')

ax.plot(df['date'], df['rolling_avg_30d'],
        color='#E84855', linewidth=2.5,
        label='30-Day Rolling Average')

ax.xaxis.set_major_locator(mdates.MonthLocator())
ax.xaxis.set_major_formatter(mdates.DateFormatter('%b %Y'))
plt.xticks(rotation=45, ha='right')

ax.set_title('Daily Revenue with 30-Day Rolling Average 2024',
             fontsize=16, fontweight='bold', pad=15)
ax.set_ylabel('Revenue ($)', fontsize=12)
ax.yaxis.set_major_formatter(plt.FuncFormatter(lambda x, p: f'${x:,.0f}'))

ax.legend(fontsize=11)
ax.grid(axis='y', alpha=0.3, linestyle='--')

plt.tight_layout()
plt.show()

The raw daily line uses alpha=0.4 to make it semi-transparent so it stays visible without dominating the chart. The rolling average line uses a thicker linewidth and a contrasting color so it stands out clearly as the trend indicator. The grid is added only on the y-axis because horizontal reference lines help viewers read values without adding the visual clutter that diagonal or full grid lines create.

Step 5: Plot Multiple Time Series on the Same Chart

Comparing multiple metrics over the same time period is one of the most common time series chart use cases. Each series gets its own line with a distinct color:

python

df['units_sold'] = (df['revenue'] / np.random.uniform(45, 55, len(df))).round(0)

monthly = df.resample('ME', on='date').agg({
    'revenue': 'sum',
    'units_sold': 'sum'
}).reset_index()

fig, ax1 = plt.subplots(figsize=(12, 5))

color_revenue = '#2E86AB'
color_units = '#E84855'

ax1.plot(monthly['date'], monthly['revenue'],
         color=color_revenue, linewidth=2.2,
         marker='o', markersize=5, label='Monthly Revenue')

ax1.set_ylabel('Revenue ($)', color=color_revenue, fontsize=12)
ax1.tick_params(axis='y', labelcolor=color_revenue)
ax1.yaxis.set_major_formatter(plt.FuncFormatter(lambda x, p: f'${x:,.0f}'))

ax2 = ax1.twinx()
ax2.plot(monthly['date'], monthly['units_sold'],
         color=color_units, linewidth=2.2,
         marker='s', markersize=5, label='Units Sold')
ax2.set_ylabel('Units Sold', color=color_units, fontsize=12)
ax2.tick_params(axis='y', labelcolor=color_units)

ax1.xaxis.set_major_locator(mdates.MonthLocator())
ax1.xaxis.set_major_formatter(mdates.DateFormatter('%b'))
plt.xticks(rotation=45, ha='right')

lines1, labels1 = ax1.get_legend_handles_labels()
lines2, labels2 = ax2.get_legend_handles_labels()
ax1.legend(lines1 + lines2, labels1 + labels2, loc='upper left', fontsize=11)

ax1.set_title('Monthly Revenue and Units Sold 2024',
              fontsize=16, fontweight='bold', pad=15)
ax1.grid(axis='y', alpha=0.3, linestyle='--')

plt.tight_layout()
plt.show()

twinx() creates a second y-axis on the right side of the chart sharing the same x-axis. This is the right approach when two metrics have completely different scales, like revenue in thousands of dollars and units sold in the hundreds. Putting them on the same y-axis would compress one metric into a flat line at the bottom of the chart.

Step 6: Highlight a Specific Time Period

Calling out a specific event or period directly on the chart is more effective than adding a footnote or explaining it verbally:

python

fig, ax = plt.subplots(figsize=(12, 5))

ax.plot(df['date'], df['revenue'], color='#2E86AB', linewidth=1.2, alpha=0.5)
ax.plot(df['date'], df['rolling_avg_30d'], color='#2E86AB', linewidth=2.5)

ax.axvspan(pd.Timestamp('2024-11-25'), pd.Timestamp('2024-12-31'),
           alpha=0.15, color='#F4A261', label='Holiday Season')

ax.axvline(pd.Timestamp('2024-11-25'), color='#E84855',
           linestyle='--', linewidth=1.5, alha=0.8)

ax.annotate('Black Friday',
            xy=(pd.Timestamp('2024-11-25'), df['rolling_avg_30d'].max() * 0.95),
            xytext=(pd.Timestamp('2024-10-01'), df['rolling_avg_30d'].max() * 0.98),
            arrowprops=dict(arrowstyle='->', color='#E84855'),
            fontsize=10, color='#E84855')

ax.xaxis.set_major_locator(mdates.MonthLocator())
ax.xaxis.set_major_formatter(mdates.DateFormatter('%b %Y'))
plt.xticks(rotation=45, ha='right')

ax.set_title('Daily Revenue 2024 with Holiday Season Highlighted',
             fontsize=16, fontweight='bold', pad=15)
ax.set_ylabel('Revenue ($)', fontsize=12)
ax.yaxis.set_major_formatter(plt.FuncFormatter(lambda x, p: f'${x:,.0f}'))
ax.legend(fontsize=11)
ax.grid(axis='y', alpha=0.3, linestyle='--')

plt.tight_layout()
plt.show()

axvspan() shades a vertical band between two dates. axvline() draws a single vertical line at a specific date. annotate() adds an arrow and label pointing to a specific point on the chart. These three tools together let you call out any event, campaign, product launch, or seasonal period directly on the chart where it happened.

Creating a Time Series Line Chart in Excel

Step 1: Organize Your Data

Put dates in column A and values in column B with headers in row 1. Make sure the date column is formatted as dates, not text. Select the date column, right click, Format Cells, and choose the Date category. If Excel is treating your dates as text, the chart will not recognize them as a time axis.

Step 2: Select Your Data and Insert the Chart

Select both columns including the headers. Go to the Insert tab, click the Line Chart button in the Charts group, and select Line with Markers for datasets with fewer than 50 data points or Line without Markers for larger datasets where individual point markers would create visual clutter.

Step 3: Format the Date Axis

Right click the horizontal axis and select Format Axis. Under Axis Type select Date Axis. This tells Excel to treat the axis as a continuous time scale rather than a category axis, which means gaps in your data appear as actual gaps rather than being ignored. Set the Major Unit to whatever interval makes sense for your data, 1 Month for a year of daily data, 1 Quarter for several years of monthly data.

Step 4: Clean Up the Chart

Click the chart title and type a descriptive name. Right click the gridlines and delete the vertical ones, keeping only horizontal gridlines. Right click the plot area and select Format Plot Area to remove the default grey background and use a white background instead. Right click each data series to change the line color to something more meaningful than the default Excel blue.

Chart Design Decisions for Time Series Data

Situation	Recommendation
Daily data over one year	Plot monthly aggregates or add rolling average
Two metrics with different scales	Use dual y-axis with twinx()
Highlighting a specific event	Use axvline() or axvspan() with annotation
Comparing multiple series	Use distinct colors and a clear legend
Noisy data with unclear trend	Add rolling average line over raw data
Monthly data over multiple years	Show annual averages as reference lines
Presenting to non-technical audience	Aggregate to monthly, remove gridlines, simplify

Common Limitations

Line charts assume continuity between points. The line connecting two data points implies that values changed gradually between them. If your data actually has gaps or sudden jumps, the connecting line misrepresents what happened in between. For data with genuine discontinuities, consider breaking the line or using markers only to show the actual data points without implying interpolated values in between.

Too many lines on one chart creates confusion. Three or four lines on a time series chart are usually readable with a good legend and distinct colors. Seven or eight lines in different shades of blue become impossible to interpret. If you need to compare many series, consider a small multiples layout where each series gets its own small chart arranged in a grid rather than all on one crowded chart.

Date formatting requires explicit attention in both Python and Excel. Python’s matplotlib does not automatically format date axes for readability. Excel defaults to category axes that ignore the actual time intervals between dates. Both require explicit formatting steps to display time series data correctly and the effort is always worth it because unformatted date axes are one of the most common reasons time series charts are confusing to read.

Common Mistakes to Avoid

Not parsing dates before plotting in Python. If your date column contains strings rather than proper datetime objects, matplotlib plots them as text categories rather than a continuous time axis. Always convert date strings before plotting: df[‘date’] = pd.to_datetime(df[‘date’]). Check the column dtype with df.dtypes before plotting to confirm dates are recognized correctly.

Plotting daily raw data without smoothing for trend analysis. A full year of daily data produces a jagged line where day-to-day noise makes the underlying trend invisible. If the goal is showing the trend, add a rolling average. If the goal is showing every individual data point, increase the figure width and reduce the line thickness so the detail is visible without overwhelming the chart.

Using too many colors without purpose. Color in a time series chart should carry meaning. Different lines in a multi-series chart get different colors so viewers can tell them apart. A single line does not need multiple colors. Gradient coloring along a single line by time period can look visually interesting but usually adds complexity without adding insight.

Leaving the default axis labels and title. The default matplotlib title is empty and the default axis labels say whatever the column names happen to be. A chart without a descriptive title and clear axis labels requires viewers to ask what they are looking at before they can understand it. Always write a specific descriptive title and label both axes with the metric name and unit.

Time Series Line Chart Cheat Sheet

Task	Python Code
Parse date column	pd.to_datetime(df[‘date’])
Resample to monthly	df.resample(‘ME’, on=’date’).sum()
Basic line chart	ax.plot(df[‘date’], df[‘value’])
Format date axis monthly	ax.xaxis.set_major_locator(mdates.MonthLocator())
Format date labels	ax.xaxis.set_major_formatter(mdates.DateFormatter(‘%b %Y’))
Add rolling average	df[‘value’].rolling(window=30).mean()
Shade a time period	ax.axvspan(start_date, end_date, alpha=0.2)
Add vertical line	ax.axvline(date, linestyle=’–‘)
Add annotation	ax.annotate(‘label’, xy=(date, value))
Dual y-axis	ax2 = ax1.twinx()
Format y-axis as currency	ax.yaxis.set_major_formatter(plt.FuncFormatter(lambda x, p: f’${x:,.0f}’))
Add horizontal grid only	ax.grid(axis=’y’, alpha=0.3)

A line chart for time series data does one thing better than any other visualization type: it shows how something changed over time in a way that any viewer can understand immediately without explanation.

The technical steps are straightforward. Parse your dates correctly. Choose a figure size that is wider than it is tall. Format the date axis so labels are readable without overlapping. Add a rolling average when raw data is noisy. Use a dual y-axis when comparing metrics on different scales. Annotate events that explain unusual movements in the line.

The design decisions matter as much as the technical ones. A line chart with a descriptive title, clean axis labels, minimal gridlines, and a deliberate color choice communicates in seconds. The same chart with default settings, unlabeled axes, and crowded tick marks makes viewers work to understand something that should be immediately obvious.

Start with one metric, one clean line, and a properly formatted date axis. Get that right before adding rolling averages, annotations, or multiple series. Simplicity in a time series chart is almost always the right direction.

FAQs