How to Show Only Dates with Data in Excel Chart

Get FREE Advanced Excel Exercises with Solutions!

When working with a dataset that contains a date column that you want to include in a chart axis, you may encounter a problem: the date does not appear on the chart axis. It is because we don’t select the Date axis in the Format Axis option. You may find it difficult to show the dates on the axis. But don’t be concerned. In this article, we’re gonna describe to you to show only dates with data in an Excel chart. Let’s get started.


Download Practice Workbook

Download the following practice workbook. It will help you to realize the topic more clearly.


3 Easy Steps to Show Only Dates with Data in an Excel Chart

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

dataset

Not to mention, we have used the Microsoft 365 version. You can use any other version at your convenience.


Step 1: Insert an Excel Chart

  • First of all, we need to create a chart with our dataset.
  • For doing this, select the entire column range with whom you want to make the chart.
  • Consequently, go to the Insert tab >> Insert Column or Bar chart dropdown >> pick Clustered Column.

Insert an Excel Chart to show only dates with data

Subsequently, 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 Excel Chart Using Data Range Based on Cell Value


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

  • Eventually, 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 Format Data Series in Excel (with Easy Steps)


Similar Readings


Step 3: Select the Date Axis

  • At this moment, you have to select the Date axis in the Axis Options.

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

Finally, you have shown the dates on the horizontal axis with data in your Excel chart, like the image below.

Read More: How to Select Data for a Chart in Excel (2 Ways)


How to Show Only Dates with Available Data in Excel Chart

Now, put your attention on our next dataset. 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 pick up the Select Data option.

Eventually, the Select Data Source dialog box appears. From there, uncheck the dates that aren’t carrying a value from the Horizontal (Category) Axis Labels. Then press OK.

Select Data Source window

Finally, you can see the blank cells have been removed in our Excel Chart (see the image).

Read More: How to Add Data Table in an Excel Chart (4 Quick Methods)


How to Apply Dynamic Date Range in Excel Chart

A dynamic date range means you need to select a start date and an ending date, and then it will take random dates dynamically. You can use either of the two easy ways to create an Excel Chart with a dynamic date range.

Assume that you have the following dataset, and you want to create a dynamic date range that will be applicable to the Excel chart.

To do this, follow the below steps (This is the summary of interactive dynamic date range chart technique method).

  • Initially, you need to make the dataset dynamic. And you can do it in two ways.
  • According to the first way, you may create an Excel table and rename the table (e.g. DATElist). Alternatively, you may utilize the OFFSET function with the Named Manager.
  • Later, you have to add two options to get the options for picking the start and end date ranges with the help of the Data Validation tool.
  • Subsequently, you have to link the drop-down lists with the dynamic dataset using the SUMIFS function.

Now, your Excel chart is applicable to the dynamic date range. If you assign a Start date and End date, you’ll get the desired chart.

How to Apply Dynamic Date Range in Excel Chart

Read More: How to Change Chart Data Range Automatically in Excel (2 Easy Ways)


Practice Section

We have provided a practice section on each sheet on the right side for your practice. Please do it by yourself.

Practice Section


Conclusion

That’s all about today’s session. And these are some easy steps to show only dates with data in an Excel chart. Please let us know in the comments section if you have any questions or suggestions. For a better understanding please download the practice sheet. Visit our website, Exceldemy, a one-stop Excel solution provider, to find out diverse kinds of excel methods. Thanks for your patience in reading this article.


Related Articles

Fahim Shahriyar Dipto

Fahim Shahriyar Dipto

Hello! Welcome to my Excel blog! I am a big fan of MS Excel. I am learning new and exciting things in Excel and writing the process here. I think this will be helpful for you to get used to Excel. Keep visiting our website for new and updated Excel methods.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo