You have kept a month’s worth of data in a table and created a chart based on that data. There is data that is in this table but you do not want to include in your chart.

Most know that you could hide the rows and they would be excluded from the chart. However, you have a problem: you need the hidden rows to be displayed in the table, so you want them displayed in the data table, but not in the chart.

There are a couple of ways you could accomplish this.

First one is to simply make a copy of your data, or perhaps the entire worksheet, and then delete the rows that contain weekend data. You would still have your master data for whatever purposes you need, but you could base your chart on the modified copy of that data.

There is a drawback to this and that is that it creates two sets of data that may need to be updated or kept in sync in some way.

It may be better to base your chart on a non-contiguous data range.

So assuming that your data was in A1:B15, and that there were unwanted/unneeded data in rows 7, 8, 14, and 15. You could, within the chart, set the data range for the source data to this:

=Sheet1!$A$1:$B$6,Sheet1!$A$9:$B$13

You could also create a named range that refers to the non-contiguous ranges you want included in the chart. You could then use the named range in your chart, as a reference to the source data.

Finally, if you don’t mind adding another column to your data, you could use the new column for your chart source.

Assume, for a moment, that your readings are in column A and the dates of those readings are in column B. In each cell of column C you could place the following formula:

=IF(WEEKDAY(B1,2)>5,NA(),A1)

You then end up with a series of readings for all weekdays; the weekends show #N/A for the reading. You can base your chart on this data and Excel will ignore the #N/A values. You can even hide column C so it does not distract from your source data.