Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 2
Default merged cells and creating charts

I have tried to create a chart with data that is in merged cells where I have
put in a formula to calculate the average of 4 cells in the row above. When I
create the chart, it recognizes all 4 of the merged cells. So the graph has 4
spaces for the data for each number I am trying to plot. I just got 2007
version and do not recall this problem at all in 2003 version
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: merged cells and creating charts

Hi there! I can definitely help you with this issue.

When you have merged cells in your data, Excel treats them as a single cell, which can cause issues when creating charts. However, there is a workaround that you can use to plot your data correctly.

Here are the steps to follow:
  1. Unmerge the cells that contain your data. To do this, select the merged cells, right-click, and choose "Unmerge Cells" from the context menu.
  2. Copy the formula that calculates the average of the 4 cells in the row above.
  3. Select the first cell where you want to paste the formula and press Ctrl + Shift + Down Arrow to select all the cells in that column that contain data.
  4. Press Ctrl + 1 to open the Format Cells dialog box.
  5. In the Alignment tab, check the "Wrap text" checkbox and click OK.
  6. Paste the formula into the first cell and press Enter to apply it to all the selected cells.
  7. Now that your data is no longer merged, you can create your chart as usual. Select the data range, including the column with the averages, and choose the chart type you want to use.
  8. Once the chart is created, you can format it to your liking, including adding axis labels, titles, and legends.

I hope this helps you create your chart successfully!
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 1,104
Default merged cells and creating charts

Merged cells are to be avoided at all costs.
They can be used for column heading but for little else.
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Troubled excel user" <Troubled excel wrote
in message ...
I have tried to create a chart with data that is in merged cells where I
have
put in a formula to calculate the average of 4 cells in the row above.
When I
create the chart, it recognizes all 4 of the merged cells. So the graph
has 4
spaces for the data for each number I am trying to plot. I just got 2007
version and do not recall this problem at all in 2003 version



  #4   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 8,651
Default merged cells and creating charts

I'd be willing to guess that Excel 2003 would have behaved the same.
Thank you for adding yet another example to the countless demonstrations
(listed in the archives of these Excel newsgroups) of why merged cells
should be avoided like the plague.
We keep advising people, but they keep shooting themselves in the foot by
using merged cells when there are usually much better alternatives (such as
the alignment option to "Center across selection").
--
David Biddulph

"Troubled excel user" <Troubled excel wrote
in message ...
I have tried to create a chart with data that is in merged cells where I
have
put in a formula to calculate the average of 4 cells in the row above.
When I
create the chart, it recognizes all 4 of the merged cells. So the graph
has 4
spaces for the data for each number I am trying to plot. I just got 2007
version and do not recall this problem at all in 2003 version



  #5   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 461
Default merged cells and creating charts

The online help should say in size 16 type:

DON'T USE MERGED CELLS

- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/



David Biddulph wrote:
I'd be willing to guess that Excel 2003 would have behaved the same.
Thank you for adding yet another example to the countless demonstrations
(listed in the archives of these Excel newsgroups) of why merged cells
should be avoided like the plague.
We keep advising people, but they keep shooting themselves in the foot by
using merged cells when there are usually much better alternatives (such as
the alignment option to "Center across selection").
--
David Biddulph

"Troubled excel user" <Troubled excel wrote
in message ...
I have tried to create a chart with data that is in merged cells where I
have
put in a formula to calculate the average of 4 cells in the row above.
When I
create the chart, it recognizes all 4 of the merged cells. So the graph
has 4
spaces for the data for each number I am trying to plot. I just got 2007
version and do not recall this problem at all in 2003 version





  #6   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 1
Default Create Excel chart from merged cells

I was looking for an answer to creating a chart from merged cells. After searching and not finding an answer I came across the Union method.

This sub creates a chart for a series of two merged cells expanding across the whole worksheet apart for the first two cells

Sub subCreateChart()
'This sub creates a chart for merged cells taking the information from the cell listed
'eg the two merged cells C37 and D37 the Excel address is C37 that holds the data.
Dim rngAvg As Range
Dim rngTemp As Range
Dim i As Integer

For i = 3 To 253 Step 2

Set rngTemp = Range(Cells(37, i), Cells(39, i)) 'Set the first and subsequent merged cells as a range.
If i = 3 Then
Set rngAvg = rngTemp 'set the first selected range to an accumulating range
Else
Set rngAvg = Union(rngAvg, rngTemp) 'after the first range has been assigned to rngAvg
'add all over ranges by looping through the required data.
End If
Next i

Charts.Add
ActiveChart.ChartType = xlLine

ActiveChart.SetSourceData rngAvg, PlotBy:=xlRows 'when collection is complete use the accuumulated
'range to create chart.
ActiveChart.DisplayBlanksAs = Excel.XlDisplayBlanksAs.xlInterpolated
ActiveChart.Location Whe=xlLocationAsObject, Name:="Sheet1"
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
End Sub




Bernard Liengme wrote:

Merged cells are to be avoided at all costs.
08-Oct-09

Merged cells are to be avoided at all costs.
They can be used for column heading but for little else.
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

Previous Posts In This Thread:

On Thursday, October 08, 2009 12:34 PM
Troubled excel user wrote:

merged cells and creating charts
I have tried to create a chart with data that is in merged cells where I have
put in a formula to calculate the average of 4 cells in the row above. When I
create the chart, it recognizes all 4 of the merged cells. So the graph has 4
spaces for the data for each number I am trying to plot. I just got 2007
version and do not recall this problem at all in 2003 version

On Thursday, October 08, 2009 12:39 PM
Bernard Liengme wrote:

Merged cells are to be avoided at all costs.
Merged cells are to be avoided at all costs.
They can be used for column heading but for little else.
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

On Thursday, October 08, 2009 12:53 PM
David Biddulph wrote:

I'd be willing to guess that Excel 2003 would have behaved the same.
I'd be willing to guess that Excel 2003 would have behaved the same.
Thank you for adding yet another example to the countless demonstrations
(listed in the archives of these Excel newsgroups) of why merged cells
should be avoided like the plague.
We keep advising people, but they keep shooting themselves in the foot by
using merged cells when there are usually much better alternatives (such as
the alignment option to "Center across selection").
--
David Biddulph

On Thursday, October 08, 2009 3:24 PM
Jon Peltier wrote:

The online help should say in size 16 type:DON'T USE MERGED CELLS-
The online help should say in size 16 type:

DON'T USE MERGED CELLS

- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/



David Biddulph wrote:


Submitted via EggHeadCafe - Software Developer Portal of Choice
WMI - VB 6.0 Windows Management Instrumentation
http://www.eggheadcafe.com/tutorials...dows-mana.aspx
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Creating Serial Additions of Numbers Formatted "M-N" In Merged and Unmerged Cells Patient Guy Excel Worksheet Functions 2 May 29th 09 02:56 PM
How can I sort an Excel Doc containing merged & non-merged cells? KellyH Excel Discussion (Misc queries) 11 June 10th 08 04:12 AM
Autofit Merged cell Code is changing the format of my merged cells JB Excel Discussion (Misc queries) 0 August 20th 07 02:12 PM
how do i link merged cells to a merged cell in another worksheet. ibbm Excel Worksheet Functions 3 April 27th 06 11:40 PM
Sorting merged cellsHow do I sort merged cells not identically siz Laval Excel Worksheet Functions 1 November 3rd 04 10:40 PM


All times are GMT +1. The time now is 07:15 AM.

Powered by vBulletin® Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright 2004-2023 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"