How to Graph in Excel by Date

Trying to graph data by date in Excel can feel surprisingly tedious. You have a clean column of dates and numbers, but when you create a chart, the x-axis shows up as a jumble of random numbers or treats each date as a separate, unevenly spaced category. This guide will walk you through exactly how to prepare your data and create perfectly formatted line charts and pivot charts in Excel that display your time-based data correctly.

The Foundation: Preparing Your Data for Date-Based Graphing

Before you even click the "Insert Chart" button, know this: 90% of date-related graphing problems in Excel stem from poorly formatted source data. Get this step right, and the rest becomes much easier. Follow these simple rules to set yourself up for success.

Rule #1: Ensure Dates are Truly Dates

This is the most common reason charts fail. Sometimes, when you export data from another system, dates get imported as text that looks like a date. To Excel, "01/15/2024" formatted as text is no different from the word "apple." It has no chronological value.

To check if your dates are real, click on a cell in your date column. If the cell is formatted as "General" in the Home tab's Number format dropdown, and you see a date like "1/15/2024," it's likely text. If you change the format to "Number," a real date will turn into a serial number (like 45306), while a text date will stay the same.

Quick Fix: If your dates are text, you can convert them. Select the entire column of text dates, go to the 'Data' tab, and click 'Text to Columns.' In the wizard, simply click 'Finish' without changing any options. This little trick often forces Excel to re-evaluate the cells and convert the text to proper date values.

Rule #2: Structure Your Data as a Simple Table

Your data needs to be organized in a clean, tabular format. This means:

• A Single Header Row: Your first row should contain clear, simple titles for your columns (e.g., "Date," "Sales," "Website Visits").

• One Column for Dates: Don't split the day, month, and year into separate columns. Keep each full date in a single cell within one dedicated column.

• One Column for Values: The numbers you want to plot (your sales figures, user counts, etc.) should be in their own column.

• No Blank Rows or Columns: Ensure there are no completely empty rows or columns cutting through your dataset, as this can confuse Excel when it tries to auto-select your data range.

Example of a Well-Formatted Data Table:

Your data should look something like this before you begin:

Creating a Basic Time-Series Line Chart

Once your data is cleaned and properly formatted, creating the chart is straightforward. A line chart is typically the best choice for visualizing data trends over time.

Step 1: Select Your Data

Click and drag to select the entire data range, including both your date column and your value column, along with their headers.

Step 2: Insert the Chart

Navigate to the Insert tab on the Excel ribbon. In the Charts section, find the Line or Area Chart icon (it looks like a mini line graph). Click it and select the first option under 2-D Line, usually called just "Line."

Excel will immediately generate a chart. If you've formatted your data correctly, it should recognize the date column and use it to create a proper chronological horizontal (X) axis.

Step 3: Verify the Axis Type

This is a quick check to ensure Excel has done its job. Right-click on the horizontal (date) axis at the bottom of your new chart and select Format Axis. A panel will appear on the right side of your screen. Under "Axis Options," you should see that "Date axis" is automatically selected. If "Text axis" is selected instead, it means Excel didn't recognize your dates, and you need to go back and fix your data formatting using the steps in the first section.

Fine-Tuning Your Date Axis

A basic chart is great, but often you'll want to adjust how the dates are displayed to make the chart more readable.

How to Adjust Date Intervals (Days, Months, Years)

1. Right-click the horizontal (date) axis and choose Format Axis.

2. In the Format Axis pane on the right, look for the Units section under 'Axis Options'.

3. Change the number in the Major units box. For instance, if your data is daily, the base unit will be set to "Days." Changing the Major unit from "1" to "7" will make your axis show a label every 7 days.

4. You can also change the Base unit itself from "Days" to "Months" or "Years." This forces the axis to group your data into larger time buckets, which is perfect for looking at longer-term trends.

How to Change the Date Display Format

You may want to abbreviate the date format on your axis - for example, showing "Jan-24" instead of "1/1/2024."

1. Open the Format Axis pane again.

2. Scroll down to the Number section at the bottom of the pane and expand it.

3. Here, you can choose from a list of predefined date formats or create your own. For a "Month-Year" format like Jan-24, you can enter mmm-yy in the Format Code box and click 'Add.' Your axis labels will update instantly.

Advanced Method: Grouping Dates by Month or Quarter with a PivotChart

What if you have daily sales data but want to see a chart showing total sales by month or quarter? This is a perfect job for a PivotChart, as it can summarize your dated data automatically.

Step 1: Create a PivotChart

Select your entire data set (including headers). Go to the Insert tab and, instead of a line chart, click PivotChart. A dialog box will pop up, just click OK to place the new PivotChart on a new worksheet.

Step 2: Assign Your Fields

A "PivotChart Fields" panel will appear on the right. This is where you build your report.

• Drag your Date field into the Axis (Categories) area at the bottom.

• Drag your numerical field (like "Revenue" or "Sales") into the Values area.

Excel will automatically group dates by years, quarters, and months for you (in newer versions). You'll instantly see your values summarized.

Step 3: Group or Ungroup Dates Manually

If Excel doesn't automatically group your dates, or if you want to change the grouping (e.g., just by Month), you can do it easily. Right-click on any of the date labels in your PivotChart (or in the associated PivotTable). Select Group from the menu.

A dialog box will appear allowing you to select how you want to aggregate your dates. Simply click on 'Months' and 'Years', then hit OK. Your PivotChart will dynamically update to show a clear summary grouped exactly how you specified.

Common Problems and How to Fix Them

Problem: My dates are not in chronological order.

Cause: Your "dates" are formatted as text, and Excel is sorting them alphabetically (which is why 01/15/2024 comes before 02/01/2023).

Solution: Go back to the "Preparing Your Data" section and use the Text to Columns trick to convert the text to real recognizable dates. If that doesn't work, you can use a helper column with the formula =DATEVALUE(A2) (assuming your text date is in A2), then copy and paste this new column as values over the old one.

Problem: There are gaps in my line chart for missing dates.

Cause: If your data just stops reporting for a few days, your line chart might show a blank space, which isn't always helpful for visualizing a trend.

Solution: You can tell Excel how to handle empty cells. Right-click your chart and select Select Data. In the bottom-left of the dialog box that appears, click the Hidden and Empty Cells button. Here, you'll see options to show empty cells as "Gaps," "Zero," or "Connect data points with a line." Choosing the last option will bridge the gap and maintain a continuous trendline.

Final Thoughts

Charting in Excel by date doesn't have to be a battle. By ensuring your data is properly structured and formatted from the start, you can easily create insightful time-series charts, fine-tune the display of your date axis, and even use PivotCharts to instantly group your data into months, quarters, or years. These techniques transform raw date entries into professional, easy-to-read visuals that clearly communicate trends over time.

While Excel is a powerful tool for this kind of work, we know that spending hours manually pulling new data, cleaning it, and rebuilding these graphs every week is a major time drain for busy teams. We created Graphed to eliminate this entire routine. After connecting your tools like Shopify, Google Analytics, or Salesforce with a few clicks, you can simply ask for what you want in plain English: "Show me my revenue by month as a line chart for the last year." We build the dashboard for you instantly, and it stays updated in real-time, giving you back the hours you used to spend wrestling with spreadsheets.