First of all, let me remind you that if you start with properly prepared
data, so many tasks in Excel become much easier. Merging cells or inserting
blank rows and columns may help with readability of tables, but it screws up
charting, pivot tables, and many other Excel capabilities. It's better to
have multiple data ranges, linked together to assure they all reflect the
same data; one range can be formatted appropriately for charting (but not
shown to the readers of the spreadsheet), and another range can be formatted
for easier human comprehension or because someone's stupid manager likes it
that way.

Merging cells can cause all kinds of problems, in fact, including
interfering with Excel's ability to recognize how to parse data ranges for
such tasks as charting. As you've discovered. If you can do away with
merging of cells, I urge you to do so. Often you can simulate merging of
cells through formatting of borders, or by using the Center Across Selection
horizontal alignment.

If you decline to follow this advice, you can still chart the data. However,
you cannot rely on Excel to parse the range using the Data Source dialog.
Instead, in step 2 of the Chart Wizard or in the Source Data Dialog from the
Chart menu, click on the Series tab, and explicitly select the ranges for
the Name, Values, and X Values or Category Labels. Note that Excel will not
assume one point per merged cell, but rather one point and enough gaps in
the data to account for the rest of the cells that comprise each merged
cell.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______