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

We have the following dataset containing the records of profits for 12 months of the year 2021.

Method 1 – Using the Chart Filters Feature

We will draw the graphs for profits between February and October.

Select the range you want to chart.
Go to the Insert tab, select the Insert Column or Bar Chart dropdown.
Choose a 2-D Clustered Column.

The chart will have all of the profit values from January to December.

Click on the Chart Filters icon.

You will get a list of month names that are selected as they have been plotted.

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

Deselect November and October.

We obtained our required graph showing the profits from February to October.

Method 2 – Applying the Advanced Filter Feature to Limit the Data Range in an Excel Chart

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.

Go to the Data tab, choose the Sort & Filter dropdown, and click Advanced.

The Advanced Filter dialog box will appear.

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

You will get the following filtered dataset.

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

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

Right-click on your chart and choose Select Data.

The Select Data Source dialog box will pop up.

Click on Hidden and Empty Cells.

The Hidden and Empty Cell Settings wizard will open up.

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

Method 3 – Defining a Dynamic Range to Limit the Data Range in an Excel Chart

We will create a dynamic range to limit the range in an Excel chart.

Step 1 – Applying TODAY and MONTH Functions

We want to plot the data up to the month of today’s date, 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.

Step 2 – Creating Dynamic Ranges

Go to the Formula tab and the Defined Names group, then select Name Manager.

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.

Press OK.

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.

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.