How to Show Only Dates with Data in an Excel Chart (3 Steps)

Here, we have taken a dataset of “Date-wise Sales” of “Sales Rep.” We want to create a chart showing the dates, but they are not displaying in our chart. We are going to solve this issue.

dataset


Step 1: Insert an Excel Chart

  • Create a chart with our dataset.
    • Select the entire column range with whom you want to make the chart.
    • Go to the Insert tab >> Insert Column or Bar chart dropdown >> pick Clustered Column.

Insert an Excel Chart to show only dates with data

A bar chart is formed with the data, like the image below. But the dates aren’t showing on the horizontal axis.

Read More: How to Create Graph from List of Dates in Excel


Step 2: Use the Format Axis Option

  • To show the date, click on the chart and select the Format Plot Area option.

Use the Format Axis Option to show only dates with data in excel Chart

  • The Format Axis window appears on the right side of the Excel worksheet. Move to Axis Options there.

Format Axis window to show only dates with data in excel chart

Read More: How to Change X-Axis Values in Excel


Step 3: Select the Date Axis

  • Select the Date axis in the Axis Options.

Select the Date Axis to show only dates with data in Excel chart

Your Excel chart shows the dates on the horizontal axis with data, as shown in the image below.


How to Show Only Dates with Available Data in Excel Chart

Here, we have taken the same dataset, but some of our Sales data is missing.

How to Show Dates Only with Available Data in Excel Chart

When we plot a chart using the same procedure as in Step 1, we find dates on the X-axis while the ordinate values are blank.

We have tried to solve this problem. To do this, click on the chart area and select the Select Data option.

The Select Data Source dialog box appears. From there, uncheck the dates that don’t carry a value from the Horizontal (Category) Axis Labels. Press OK.

Select Data Source window

Our Excel chart shows that the blank cells have been removed (see the image).

Read More: How to Combine Daily and Monthly Data in Excel Chart


How to Apply Dynamic Date Range in Excel Chart

For the following dataset, you want to create a dynamic date range that will apply to the Excel chart.

STEPS:

  • Make the dataset dynamic. And you can do it in two ways.
  • The first method involves creating an Excel table and renaming it (e.g., DATElist). Alternatively, you can utilize the OFFSET function with the Named Manager.
  • Add two options to pick the start and end date ranges with the help of the Data Validation tool.
  • Link the drop-down lists with the dynamic dataset using the SUMIFS function.

Your Excel chart now applies to the dynamic date range. You’ll get the desired chart if you assign a start date and end date.

How to Apply Dynamic Date Range in Excel Chart


Practice Section

We have provided a practice section on each sheet on the right side for your practice. 

Practice Section


Download the Practice Workbook

Download the following workbook to practice.


Related Articles


<< Go Back to Data for Excel ChartsExcel Charts | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Fahim Shahriyar Dipto
Fahim Shahriyar Dipto

Fahim Shahriyar Dipto is a graduate of Mechanical Engineering at BUET. With over 1.5 years of experience at Exceldemy, he authored 70+ articles on ExcelDemy. He has expertise in designing worksheets at You’ve Got This Math. Currently, He is a Team Leader at Brainor. Dipto's passion extends to exploring various aspects of Excel. Beyond tech, he enjoys creating engaging kids' worksheets using Illustrator. A dedicated employee and innovative content developer, He incorporates a commitment to academic excellence and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo