Using named data range for box and whisker plot?

Question

Using named data range for box and whisker plot?

Andy Entrekin 20

I've got a Box and Whisker plot of a single data series, but the series expands over time. So far, I've been unable to find a clean way to keep the plot updated. I thought I could use Name Manager to define a named range using the OFFSET function, so I set that up. The named range works exactly as I want. But I can't use it in the Select Data dialog for the chart. More accurately, I can enter the name but Excel converts it to the current evaluated range when I apply it. I enter "=Data!PayDelay" in the form, but when I click OK I can see it get converted to "=Data!$E$3:$E$199" (the current range). If I add a value in E200, it is not reflected in the plot unless I repeat this exercise (in which case it's just as easy to update the cell reference and not bother with the named range). This is Excel on my Windows machine, not the web version, in case that matters. What am I missing?

0 comments

Answer accepted by question author

0 additional answers

Your answer

Answer 1

Hendrix-C 20,015 Microsoft External Staff Moderator

Hi @Andy Entrekin,

Based on your sharing, if the named range are correctly defined with OFFSET and then added to the data ranges in Select Data, normally Excel should keep the define named without converting it to current range. I have tried to reproduce the problem you are experiencing, but I couldn't gather more details or insights into the problem causing it.

However, I suggest you can consider trying using Excel Table for your case. It has the same dynamic approach with OFFSET define names that when you add a new value directly below the table, Excel expands the table automatically and the chart referencing to the table also updates automatically. You just need to select the data range (e.g A1:E200) then go to Insert > Table (or use shortcut Ctrl + T) > check the Headers box if your data has headers > OK. Then normally create the Box & Whisker chart with that data table.

You can give it a try and see if it meets your needs. If not, don't hesitate to reach out again and we can work together to find the best approach for your concern.

If you have any questions or need any further clarification, please leave a comment here and I will be happy to continue to help you.

Thank you for your patience and understanding. I'm looking forward to your reply.

If the answer is helpful, please click "Accept Answer" and kindly upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Andy Entrekin 20 Reputation points

2026-06-11T20:53:13.45+00:00

This works - mostly. The table does not adjust as I add values. I have to manually drag the table definition to update it, but then the plot does update. So this is a step in the right direction, but not an actual solution yet. In case it matters, the column I'm plotting is calculated values, not directly entered ones.
Hendrix-C 20,015 Reputation points Microsoft External Staff Moderator

2026-06-11T21:19:56.5766667+00:00

Hi @Andy Entrekin,

Thank you for your updates.

If that's the case, for the best assistance for your case, please share some screenshots or sample data of your worksheet so I can clearly understand the situation to provide the best possible solution for your concern.

Thank you for your cooperation and understanding. I hope to hear from you soon.
Andy Entrekin 20 Reputation points

2026-06-11T21:38:37.3266667+00:00

I tried again and it works! Thanks for the suggestions.
Hendrix-C 20,015 Reputation points Microsoft External Staff Moderator

2026-06-11T22:09:30.1333333+00:00

Hi @Andy Entrekin,

I'm very glad to know that my sharing would help resolve your concern.

For any concerns or questions in the future, please don’t hesitate to reach out. Not only will I be happy to assist you, but all moderators in the Q&A forum will also do their best to provide support and guidance.

Wishing you a wonderful day ahead.