How to Limit Data Range in Excel Chart (3 Handy Ways)

Get FREE Advanced Excel Exercises with Solutions!

Sometimes you may need to limit the data range in an Excel chart as you don’t want to show the whole data range in your chart. In this article, we will illustrate 3 different methods for doing this task. So, let’s get into the main article.


How to Limit Data Range in Excel Chart: 3 Handy Ways

Here, we have the following dataset containing the records of profits for 12 months of the year 2021. Using this dataset, we will explain the upcoming 3 methods explicitly.

how to limit data range in excel chart

For creating this article, we have used Microsoft Excel 365 version. However, you can use another version according to your necessary.


Method-1: Using Chart Filters Feature

Here, we will use the Chart Filters feature to select the necessary data which we want to show in our graph. According to the following dataset, we will draw the graphs for profits between February and October months.

using Chart Filters feature to limit data range in excel chart

Firstly, we will insert a column chart for all of the data in our dataset.

  • Select the range, and then go to the Insert tab >> Insert Column or Bar Chart dropdown >> 2-D Clustered Column.

Afterward, the following chart will appear with all of the profit values from January to December.

  • Click on the Chart Filters icon.

Then, you will get a list of month names that are selected as they have been plotted. But we can customize the selection according to our needs.

  • As we don’t want to see the profit for January, so we unchecked this option.
  • Now, scroll down to unselect the last two months.

using Chart Filters feature to limit data range in excel chart

In this way, we have reached the end.

  • Deselect November and October.

In this way, we obtained our required graph showing the profits from February to October.

Read More: How to Ignore Blank Cells with Formulas in Excel Chart


Method-2: Applying Advanced Filter Feature to Limit Data Range in Excel Chart

In this section, we will apply the Advanced Filter feature to plot the profits from January to October. To define the criterion for this filter, we have defined our criterion below this dataset, which is defining the range of the serial number up to which we will filter our dataset.

Applying Advanced Filter Feature to Limit Data Range in Excel Chart

  • Go to the Data tab >> Sort & Filter dropdown >> Advanced.

Afterward, the Advanced Filter dialog box will appear.

  • Select Filter the list, in-place as Action.
  • Choose the List range and Criteria range, and then press OK.

Finally, you will get the following filtered dataset.

Filtered dataset to Limit Data Range in Excel Chart

  • Using our filtered dataset, we have drawn the following column graph like the previous method.

We can see that the following chart has plotted our filtered data only.

But if you are facing the issue of showing all of the visible and non-visible cells in your chart, then you can follow this step.

  • Right-click on your chart and choose Select Data.

Later, the Select Data Source dialog box will pop up.

  • Click on Hidden and Empty Cells.

Applying Advanced Filter Feature to Limit Data Range in Excel Chart

Then, the Hidden and Empty Cell Settings wizard will open up.

  • Uncheck the option Show data in hidden rows and columns.
  • Press OK.

Read More: How to Remove One Data Point from Excel Chart


Method-3: Defining a Dynamic Range to Limit Data Range in Excel Chart

In this section, we will create a dynamic range to limit the range in an Excel chart.

Defining a Dynamic Range to Limit Data Range in Excel Chart


Step-01: Applying TODAY and MONTH Functions

Firstly, we will define the limit of the serial numbers up to which we will extract our dynamic range to plot the chart.

Here, we want to plot the data up to the month of today’s date, and so we have used the following formula in cell C18.

=MONTH(TODAY())

Here, the TODAY function will return today’s date, and then the MONTH function will determine the month number of this date.
As today’s date is 11/17/2022 (mm/dd/yyyy format), we are getting output as 11.

functions


Step-02: Creating Dynamic Ranges

Now, we will create two dynamic ranges for months and profits.

  • Go to the Formula tab >> Defined Names group >> Name Manager.

  • After selecting the New Name option, you will get the New Name dialog box.
  • Write down the Name as Month, and put down the following formula in the Refers to box
=OFFSET('Dynamic Range'!$C$5,0,0,'Dynamic Range'!$C$18,1)

Formula Breakdown

  • ‘Dynamic Range’!$C$5 → it is the start cell of the Month
  • ‘Dynamic Range’!$C$18 → which is 11 here, represents the height of the extracted range.
  • 1 → represents the width of this extracted range.
  • Finally, press OK.

Defining a Dynamic Range to Limit Data Range in Excel Chart

Afterward, you will return to the Name Manager dialog box where you can see the created named range Month.

  • Press the New button to add another named range.

new range

  • Now type the Name as Profit, and put down the following formula in the Refers to box
=OFFSET('Dynamic Range'!$D$5,0,0,'Dynamic Range'!$C$18,1)

Formula Breakdown

  • ‘Dynamic Range’!$D$5 → it is the start cell of the Profit
  • ‘Dynamic Range’!$C$18 → which is 11 here, represents the height of the extracted range.
  • 1 → represents the width of this extracted range.
  • Finally, press OK.

Then, you will return to the Name Manager again, where you can see the newly created named ranges.

  • Press Close.

close


Step-03: Inserting Chart

In this step, we will utilize the ranges to limit the range in the Excel chart.

chart

  • Then right-click on the chart and select the Select Data option.

select data

Afterward, you will be taken to the Select Data Source wizard.

  • Choose the Profit series and click on Edit.

Then the Edit Series dialog box will pop up.

  • Change the Series values to =’Dynamic Range’!Profit, where Profit is the dynamic range.
  • Press OK.

Defining a Dynamic Range to Limit Data Range in Excel Chart

  • After returning to the Select Data Source dialog box, under the Horizontal (Category) Axis Labels click on Edit.

edit

Later, the Axis Labels wizard will pop up.

  • Change the Axis label range to =’Dynamic Range’!Month, where Month is the dynamic range.
  • Press OK.

  • Finally, in the Select Data Source dialog box press OK.

Defining a Dynamic Range to Limit Data Range in Excel Chart

As a result, you will get the column charts for profits up to November.

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


How to Change Charts Dynamically Depending on Data Range in Excel

In this section, we will create a Table to automatically change the chart range after deleting or inserting a row in the dataset.

inserting table to Limit Data Range in Excel Chart

  • Select the data range, and go to the Insert tab >> Table.

Afterward, the Create Table wizard will open up.

  • Check the option My table has headers and press OK.

In this way, you will be able to create the following table.

inserting table to Limit Data Range in Excel Chart

Using this table, we created the following column chart.

chart

  • Delete the row for the month of April.

delete

As you can see, our chart has been automatically updated by removing the column for April.

Now, we will test the effect of inserting a new row.

  • Insert a row below the row for the month of March to put down the data for April here again.

insert

  • Put down the profit for April.

As a result, the profit column for April will appear here again.

inserting table to Limit Data Range in Excel Chart

Read More: How to Change Chart Data Range Automatically in Excel


Practice Section

To practice by yourself, we have created a Practice section on the right side of each sheet.

practice section to Limit Data Range in Excel Chart


Download Practice Workbook


Conclusion

In this article, we have discussed various ways to limit the data range in an Excel chart. Hope these methods will help you a lot. If you have any further queries, then leave a comment below.


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Tanjima Hossain
Tanjima Hossain

TANJIMA HOSSAIN is a marine engineer who enjoys working with Excel and VBA programming. For her, programming is a handy, time-saving tool for managing data, files, and online tasks. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. She holds a B.Sc. in Naval Architecture & Marine Engineering from BUET and is now a content developer. In this role, she creates tech-focused content centred around Excel and VBA. Apart from... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo