A robust method is to use dynamic named ranges. The idea is to define a named range that expands and shrinks as your data changes. This is most easily done if your data is by itself as opposed to in a sheet with lots of little tables of data scattered around. In your case, you can create a named range with a name like
Series1 and a
Refers To: formula that is something like this:
This assumes that
B1 has a header or something you want to ignore, hence the
-1 after the
COUNTA. There are a few annoyances to this solution:
- You have to create a different named range for each series and axis. You can just reference a whole table. If you try that, Excel will convert the chart references to hard-coded ranges.
- When pointing the chart to a named range, you have to use the full name including the workbook name (or worksheet name if it's a worksheet-level named range). This means you can't just use
=Series1, you have to use
However, it's robust so it works even if your data goes past what you originally thought would be a lot.
In summary, for short-term use or very small datasets, use Mike's answer. For data that very become very large or if you also want dynamic axis labels, use named ranges.