Chart with merged rows

  • Thread starter Thread starter iyerns
  • Start date Start date
I

iyerns

Another question for EXCEL gurus !

I have a chart with 56 rows (corresponding to 56 weeks in a year),
each of which is composed of merging 7 rows (corresponding to 7
days/week). How can I get a graph with the values in these merged
columns ? Selecting these and clicking the chart icon does not produce
anything !

X-axis C, Y-axis - D
--------------------------------------------------------------------------------
A B C D
---------------------------------------------------------------------------------
13. Aug. Sunday 33 23:55:00
14. Aug. Monday
15. Aug. Tuesday
16. Aug. Wednesday
17. Aug. Thursday
18. Aug. Friday
19. Aug. Saturday
---------------------------------------------------------------------------------
20. Aug. Sunday 34 30:55:00
21. Aug. Monday
22. Aug. Tuesday
23. Aug. Wednesday
24. Aug. Thursday
25. Aug. Friday
26. Aug. Saturday
 
I asked in your previous thread which data you wanted to plot where in what
kind of chart. This doesn't answer my question, only restates the initial
problem.

- Jon
 
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
 
Back
Top