How to Create a Monthly Trend Chart in Excel

Visualizing your data by month is the fastest way to understand your business's momentum. Instead of getting lost in rows of numbers, a simple trend chart instantly tells you if you're growing, stalling, or heading in the wrong direction. This guide will walk you through exactly how to create, customize, and supercharge monthly trend charts in Microsoft Excel.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Why Track Monthly Trends in Excel?

Before jumping into the "how," let's quickly cover the "why." Tracking performance on a monthly basis helps smooth out the day-to-day noise. A single bad day of sales might seem alarming in isolation, but when viewed as part of a monthly trend, it might just be a minor blip. Monthly charts are powerful because they help you:

• Identify Patterns: Is your business seasonal? Do sales always peak in Q4 and dip in Q1? A monthly trend chart makes these cycles obvious.
• Measure Momentum: Are your marketing efforts gaining traction? Is website traffic consistently growing month-over-month? Visuals make this much clearer than raw numbers.
• Forecast a Basic Trajectory: By observing the trend over the last six or twelve months, you can make more educated guesses about where you'll be in the next three.
• Communicate Performance: A simple, clean chart is the most effective way to show your team, your boss, or your clients what's happening. It tells a story that a table of numbers can't.

Getting Your Data Ready for Charting

The foundation of any good chart is well-organized data. Excel can do amazing things, but it needs a clean slate to work from. For a monthly trend chart, your setup should be simple and consistent.

The Ideal Data Structure

For best results, structure your data in two columns. One column for the month and another for the metric you want to track. Keep the column headers simple and descriptive.

For example:

• Column A: Month
• Column B: Website Sessions

Or:

• Column A: Month
• Column B: Sales Revenue

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

An Important Note on Formatting Dates

This is where many people get tripped up. For Excel to recognize your months as a chronological sequence, they must be formatted as dates. Typing "January" or "Feb" as plain text can sometimes confuse Excel's charting engine.

The best practice is to enter the first day of each month (e.g., 1/1/2023, 2/1/2023, 3/1/2023). Even though you're looking at monthly totals, this tells Excel the exact order of your data points.

After entering the dates, you can format them to only show the month name:

1. Highlight the cells containing your dates.
2. Right-click and select Format Cells.
3. Under the Number tab, choose Custom.
4. In the "Type" box, enter mmmm to show the full month name (e.g., "January") or mmm to show the abbreviation (e.g., "Jan").
5. Click OK. The underlying data still tells Excel the exact date, but the cell only displays the easy-to-read month name.

Your prepared data should look something like this:

Step-by-Step: Creating Your First Monthly Trend Chart

With your data properly organized, creating the chart takes less than a minute. We'll use a Line Chart, as it's the best type for showing a trend over a continuous period.

1. Select Your Data

Click and drag your cursor to highlight the entire data set you want to visualize, including the column headers. In our example above, you would select cells A1 through B7.

2. Insert the Chart

Navigate to the Insert tab on Excel's ribbon. In the "Charts" section, you'll see a variety of options. Find the icon that looks like a line graph, labeled "Insert Line or Area Chart."

3. Choose the Right Line Chart Type

Click on the line chart icon. A dropdown will appear with several options like "2-D Line" and "3-D Line." For clarity and professional reporting, always stick with a 2-D option. The "Line with Markers" option is an excellent choice, as it puts a dot on each month's data point, making it easier to read.

Once you click it, Excel will immediately generate a chart and place it on your worksheet. And just like that, you have a basic monthly trend chart!

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Customizing Your Chart for Clarity and Impact

The default chart Excel creates is a decent starting point, but with a few tweaks, you can make it much more professional and easier to understand.

Add a Descriptive Title and Axis Labels

A chart without a title is meaningless. The first thing you should do is give your chart a clear, descriptive name. Click on the "Chart Title" text box at the top of the chart and type something specific, like "Monthly Sales Revenue Trend - First Half 2023."

Next, label your axes. That Y-axis number could be anything - revenue, leads, or units sold. To add axis titles:

1. Click on your chart to select it.
2. A plus sign (+) icon will appear in the top right corner. Click this to open the Chart Elements menu.
3. Check the box for Axis Titles.
4. Text boxes for "Axis Title" will appear. Click on each one and label them appropriately (e.g., "Sales Revenue ($)" for the vertical Y-axis and "Month" for the horizontal X-axis).

Add a Trendline to See the Bigger Picture

A trendline is a straight line that cuts through your data to show the general direction of your performance. It helps you see past the minor ups and downs to understand the overall trajectory. Adding one is easy:

1. With the chart selected, click the + icon again.
2. Hover over Trendline and click the small arrow that appears to the right.
3. Choose Linear. Excel will instantly draw a dotted line that represents the general slope of your data.

A trendline pointing up signifies overall growth, while one pointing down indicates an overall decline.

Adjust the Look and Feel

You can fine-tune the aesthetics from the Chart Design tab that appears when you click on the chart.

• Change Colors: Use the "Change Colors" button to select a palette that matches your brand presentation.
• Chart Styles: This gallery offers pre-made designs with different backgrounds and formatting.
• Fine-tuning: For more detailed control, right-click on any element of the chart (like the line itself, the gridlines, or a marker) and select "Format..." to open a panel with options for changing colors, thickness, transparency, and more.

Advanced Techniques: Comparing Multiple Trends

The real power of charting comes alive when you start comparing metrics. What if you want to see if your marketing spend correlates with an increase in sales?

Plotting Two Metrics on One Chart

Let's say your data is organized in three columns: Month, Sales Revenue, and Ad Spend.

1. Highlight all three columns of data, including the headers.
2. Go to Insert > Line Chart and select a 2-D "Line with Markers" chart.
3. Excel will automatically create a chart with two lines, one for Sales Revenue and one for Ad Spend. It will also create a Legend to differentiate them.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Using a Secondary Axis for Different Scales

Sometimes, the metrics you want to compare are on wildly different scales. For example, your revenue might be in the tens of thousands, while your new customer count might be in the hundreds. If you plot them on the same axis, the customer line would look almost flat and be impossible to analyze.

The solution is a secondary axis.

1. Create the two-line chart as described above.
2. Right-click on the line representing the smaller-scale data (e.g., the customer count line).
3. From the menu, select Format Data Series...
4. In the options panel that opens, under "Series Options," select Secondary Axis.

Excel will add a new Y-axis on the right side of the chart scaled to that data series. Now you can clearly see the trend of both metrics, even though they have different value ranges - letting you spot correlations between cause and effect.

Common Mistakes to Avoid

Building the chart is easy, but a few common missteps can make it misleading or hard to read.

• Choosing the Wrong Chart Type: A trend over time is best shown with a line chart. A pie chart, for example, is the worst possible choice as it can't represent your data over a time series.
• Cluttered Design: Don't add every possible visual element. Busy charts with dark backgrounds, unnecessary 3-D effects, and distracting data labels become difficult to interpret. Simplicity is your friend.
• Inconsistent Data: Ensure your data logging is consistent. If sales revenue for three months includes tax and shipping, but the next three don't, your trend chart will be inaccurate and lead to poor conclusions.

Final Thoughts

Excel provides all the tools you need to turn raw monthly numbers into clear, insightful trend charts. By organizing your data correctly and applying a few key formatting techniques, you can create professional reports that instantly show business performance, identify patterns, and communicate your story effectively.

Manually exporting data and building these reports every month in Excel can be time-consuming, though. We created Graphed to solve this by connecting directly to your data sources – like Google Analytics, Shopify, or Salesforce – and automating the process. You can just ask for "a line chart of Shopify revenue by month for the last year" and get a real-time, shareable dashboard in seconds, skipping the data exporting and spreadsheet wrangling entirely.