graph merged cells, without graphing gaps or spaces?

**matthewdoucette** · 10-04-2008, 09:50 AM

How can I graph merged cells, without graphing gaps or spaces of the skipped cells?

**Andy Pope** · 10-04-2008, 10:48 AM

Don't graph the merged cells.

Create a set of data specifically for the chart.

**matthewdoucette** · 10-04-2008, 11:25 AM

Originally Posted by Andy Pope

Don't graph the merged cells.

Create a set of data specifically for the chart.

Is that the only way?

**Andy Pope** · 10-04-2008, 11:29 AM

err, if there was an alternative I would have suggested it

Why are the chart values in merged cells anyway?
maybe posting an example would help.

**matthewdoucette** · 10-04-2008, 11:45 AM

Well I appreciate your suggestion, it looks like I'll have to do it that way.

My example: There are weekly totals of days (each day on its own row). So picture every 7 cells down merged in a new column, for weekly totals.

**Andy Pope** · 10-04-2008, 11:49 AM

can you post an example?

**matthewdoucette** · 10-06-2008, 11:59 AM

sure...

9/14/2008 178
9/15/2008 997
9/16/2008 397
9/17/2008 50
9/18/2008 717
9/19/2008 528
9/20/2008 625 3491
9/21/2008 497
9/22/2008 966
9/23/2008 243
9/24/2008 245
9/25/2008 227
9/26/2008 197
9/27/2008 476 2852
9/28/2008 12
9/29/2008 40
9/30/2008 945
10/1/2008 910
10/2/2008 559
10/3/2008 587
10/4/2008 270 3323
10/5/2008 963
10/6/2008 624

I want to graph the weekly totals (3rd column) without gaps.

**Andy Pope** · 10-06-2008, 01:49 PM

How can I tell the merge cells from your post???

**matthewdoucette** · 10-06-2008, 01:57 PM

i'm not sure how to post excel into here.

there are no merged cells in my post... but, i had merged each weekly total with the 6 blank cells above it, so it spans 7 cells and sits next the the daily counts it represents. i merged them hoping that graphing those merged cells would not create gaps in the graph.

i hope that explains it?

**Andy Pope** · 10-06-2008, 02:09 PM

There is a Manage Attachements button you can use when posting a reply.

As I said you can not chart directly from the range containing merged cells as it will include the empty one.

The example uses formula to extract the weekly totals into single table of values.

**matthewdoucette** · 10-06-2008, 04:04 PM

Thank you, Andy, for all your help. These forums are extemely helpful, I must say!

The only downfall to this solution, is the data doesn't stack the same height. So, as I add new daily totals to the bottom, imagine it being pages and pages long, I will have to scroll up and update the re-formatted data in a different spot.

Any solutions to that?

**Andy Pope** · 10-07-2008, 05:32 AM

Extend the formula down to an extent that will cover future periods.
Then use a named range to define which cells to use in the chart.

See attached.

**Cheeky Charlie** · 10-07-2008, 05:55 AM

You might want to look at a pivot table and pivot chart, you can use a dynamic named range for these too, and they have inbuilt capacity to do things like group days in 7s

**paulmagarey** · 01-11-2019, 12:36 AM

Hi there Andy Pope,

that was indeed very helpful, but in my spreadsheet, I can't get this OFFSET function working unless I delete all my header rows. I've looked at excelfunctions net to get an explanation of the OFFSET function, but can't seem to work out how to interpret the part of the formula that reads '(ROW()-1)*7'. I can't seem to attach my spreadsheet using either Firefox or Chrome to show you what I've done. Are you able to give a layman's explaination for how that bit of the formula works?

Kind regards,

Paul

**FDibbins** · 01-11-2019, 12:59 AM

Paul, welcome to the forum

Unfortunately your post does not comply with Rule 4 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.