Bar width on existing chart changes when changing source data

I have a spreadsheet with a bar chart already in it (it's showing the number 
of quotes sent monthly w/ trendlines).  In column A, I have a month-year and 
in column B, I have the number of quotes sent in that month.  I changed the 
source data to display a different set of months, and the bar widths narrowed 
down to the width of a line.  I undid the change and the bar widths went 
back.  I then tried to edit the chart formula one cell at a time and found 
that when I reached a particular month forward in time, that that's when the 
change occurred (row 40).  

I thought maybe it was because I was adding too many months to fit in the 
chart, so I started removing months on the front end of it to keep the total 
number of bars static but that didn't make an impact.

Then I noticed that as soon as I add that one month to the source data, the 
bottom axis (is that X or Y, I forget) switches from monthly to weekly which 
is why all the bars change width.

So my question is, what is it about that cell is causing my chart to switch 
like that?  I only have monthly numbers in column A, no weekly numbers.
0
9/30/2008 2:41:01 PM
excel.charting 18370 articles. 0 followers. Follow

5 Replies
678 Views

Similar Articles

[PageSpeed] 7

Hi,

Don't know what's causing the problem, but you might try this as a solution.  
1. Double-click the x-axis (across the bottom)
2. Choose Scale tab and make sure the Base unit is set to Month(s)
3. Make changes to the Major units and Minor unit if necessary

If when you come into the Scale tab you don't see these options then exit 
the dialog box and choose Chart, Chart Options, Axis, Time-scale for the 
Category (X) axis.
-- 
Thanks,
Shane Devenshire


"Robert Brown" wrote:

> I have a spreadsheet with a bar chart already in it (it's showing the number 
> of quotes sent monthly w/ trendlines).  In column A, I have a month-year and 
> in column B, I have the number of quotes sent in that month.  I changed the 
> source data to display a different set of months, and the bar widths narrowed 
> down to the width of a line.  I undid the change and the bar widths went 
> back.  I then tried to edit the chart formula one cell at a time and found 
> that when I reached a particular month forward in time, that that's when the 
> change occurred (row 40).  
> 
> I thought maybe it was because I was adding too many months to fit in the 
> chart, so I started removing months on the front end of it to keep the total 
> number of bars static but that didn't make an impact.
> 
> Then I noticed that as soon as I add that one month to the source data, the 
> bottom axis (is that X or Y, I forget) switches from monthly to weekly which 
> is why all the bars change width.
> 
> So my question is, what is it about that cell is causing my chart to switch 
> like that?  I only have monthly numbers in column A, no weekly numbers.
0
9/30/2008 3:40:05 PM
I am having the same problem with the column bars automatically narrowing to 
a line. I tried your fix suggestions, but no matter how I edit the x axis 
values & scale, I cannot get the columns back. The only way I can restore the 
column widths is to remove the x axis labels altogether (which makes the 
chart useless).

Help!
Juli B

"ShaneDevenshire" wrote:

> Hi,
> 
> Don't know what's causing the problem, but you might try this as a solution.  
> 1. Double-click the x-axis (across the bottom)
> 2. Choose Scale tab and make sure the Base unit is set to Month(s)
> 3. Make changes to the Major units and Minor unit if necessary
> 
> If when you come into the Scale tab you don't see these options then exit 
> the dialog box and choose Chart, Chart Options, Axis, Time-scale for the 
> Category (X) axis.
> -- 
> Thanks,
> Shane Devenshire
> 
> 
> "Robert Brown" wrote:
> 
> > I have a spreadsheet with a bar chart already in it (it's showing the number 
> > of quotes sent monthly w/ trendlines).  In column A, I have a month-year and 
> > in column B, I have the number of quotes sent in that month.  I changed the 
> > source data to display a different set of months, and the bar widths narrowed 
> > down to the width of a line.  I undid the change and the bar widths went 
> > back.  I then tried to edit the chart formula one cell at a time and found 
> > that when I reached a particular month forward in time, that that's when the 
> > change occurred (row 40).  
> > 
> > I thought maybe it was because I was adding too many months to fit in the 
> > chart, so I started removing months on the front end of it to keep the total 
> > number of bars static but that didn't make an impact.
> > 
> > Then I noticed that as soon as I add that one month to the source data, the 
> > bottom axis (is that X or Y, I forget) switches from monthly to weekly which 
> > is why all the bars change width.
> > 
> > So my question is, what is it about that cell is causing my chart to switch 
> > like that?  I only have monthly numbers in column A, no weekly numbers.
0
B1 (229)
8/24/2009 4:04:01 PM
Change from a time/date scale axis to a category/text axis.

Excel 2003: Chart menu > Chart Options > Axes tab, change Automatic to 
Category.

Excel 2007: Right click axis > Format > first tab, select Text type axis.

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



Juli B wrote:
> I am having the same problem with the column bars automatically narrowing to 
> a line. I tried your fix suggestions, but no matter how I edit the x axis 
> values & scale, I cannot get the columns back. The only way I can restore the 
> column widths is to remove the x axis labels altogether (which makes the 
> chart useless).
> 
> Help!
> Juli B
> 
> "ShaneDevenshire" wrote:
> 
>> Hi,
>>
>> Don't know what's causing the problem, but you might try this as a solution.  
>> 1. Double-click the x-axis (across the bottom)
>> 2. Choose Scale tab and make sure the Base unit is set to Month(s)
>> 3. Make changes to the Major units and Minor unit if necessary
>>
>> If when you come into the Scale tab you don't see these options then exit 
>> the dialog box and choose Chart, Chart Options, Axis, Time-scale for the 
>> Category (X) axis.
>> -- 
>> Thanks,
>> Shane Devenshire
>>
>>
>> "Robert Brown" wrote:
>>
>>> I have a spreadsheet with a bar chart already in it (it's showing the number 
>>> of quotes sent monthly w/ trendlines).  In column A, I have a month-year and 
>>> in column B, I have the number of quotes sent in that month.  I changed the 
>>> source data to display a different set of months, and the bar widths narrowed 
>>> down to the width of a line.  I undid the change and the bar widths went 
>>> back.  I then tried to edit the chart formula one cell at a time and found 
>>> that when I reached a particular month forward in time, that that's when the 
>>> change occurred (row 40).  
>>>
>>> I thought maybe it was because I was adding too many months to fit in the 
>>> chart, so I started removing months on the front end of it to keep the total 
>>> number of bars static but that didn't make an impact.
>>>
>>> Then I noticed that as soon as I add that one month to the source data, the 
>>> bottom axis (is that X or Y, I forget) switches from monthly to weekly which 
>>> is why all the bars change width.
>>>
>>> So my question is, what is it about that cell is causing my chart to switch 
>>> like that?  I only have monthly numbers in column A, no weekly numbers.
0
jonNO1 (306)
8/28/2009 3:17:45 AM
Jon - THANK YOU!!

That also fixed the problem I had running 2 axes with a data table populated 
at the bottom... it kept wanting to force the data table into source order 
rather than the way it needed to be. Crazy!

Appreciate the response!
Juli B

"Jon Peltier" wrote:

> Change from a time/date scale axis to a category/text axis.
> 
> Excel 2003: Chart menu > Chart Options > Axes tab, change Automatic to 
> Category.
> 
> Excel 2007: Right click axis > Format > first tab, select Text type axis.
> 
> - Jon
> -------
> Jon Peltier
> Peltier Technical Services, Inc.
> http://peltiertech.com/
> 
> 
> 
> Juli B wrote:
> > I am having the same problem with the column bars automatically narrowing to 
> > a line. I tried your fix suggestions, but no matter how I edit the x axis 
> > values & scale, I cannot get the columns back. The only way I can restore the 
> > column widths is to remove the x axis labels altogether (which makes the 
> > chart useless).
> > 
> > Help!
> > Juli B
> > 
> > "ShaneDevenshire" wrote:
> > 
> >> Hi,
> >>
> >> Don't know what's causing the problem, but you might try this as a solution.  
> >> 1. Double-click the x-axis (across the bottom)
> >> 2. Choose Scale tab and make sure the Base unit is set to Month(s)
> >> 3. Make changes to the Major units and Minor unit if necessary
> >>
> >> If when you come into the Scale tab you don't see these options then exit 
> >> the dialog box and choose Chart, Chart Options, Axis, Time-scale for the 
> >> Category (X) axis.
> >> -- 
> >> Thanks,
> >> Shane Devenshire
> >>
> >>
> >> "Robert Brown" wrote:
> >>
> >>> I have a spreadsheet with a bar chart already in it (it's showing the number 
> >>> of quotes sent monthly w/ trendlines).  In column A, I have a month-year and 
> >>> in column B, I have the number of quotes sent in that month.  I changed the 
> >>> source data to display a different set of months, and the bar widths narrowed 
> >>> down to the width of a line.  I undid the change and the bar widths went 
> >>> back.  I then tried to edit the chart formula one cell at a time and found 
> >>> that when I reached a particular month forward in time, that that's when the 
> >>> change occurred (row 40).  
> >>>
> >>> I thought maybe it was because I was adding too many months to fit in the 
> >>> chart, so I started removing months on the front end of it to keep the total 
> >>> number of bars static but that didn't make an impact.
> >>>
> >>> Then I noticed that as soon as I add that one month to the source data, the 
> >>> bottom axis (is that X or Y, I forget) switches from monthly to weekly which 
> >>> is why all the bars change width.
> >>>
> >>> So my question is, what is it about that cell is causing my chart to switch 
> >>> like that?  I only have monthly numbers in column A, no weekly numbers.
> 
0
JuliB (1)
8/28/2009 2:37:01 PM
Jon:
Agreed!  This fixed my problem as well.  Thank you.

Dan H.

"Juli B" wrote:

> Jon - THANK YOU!!
> 
> That also fixed the problem I had running 2 axes with a data table populated 
> at the bottom... it kept wanting to force the data table into source order 
> rather than the way it needed to be. Crazy!
> 
> Appreciate the response!
> Juli B
> 
> "Jon Peltier" wrote:
> 
> > Change from a time/date scale axis to a category/text axis.
> > 
> > Excel 2003: Chart menu > Chart Options > Axes tab, change Automatic to 
> > Category.
> > 
> > Excel 2007: Right click axis > Format > first tab, select Text type axis.
> > 
> > - Jon
> > -------
> > Jon Peltier
> > Peltier Technical Services, Inc.
> > http://peltiertech.com/
> > 
> > 
> > 
> > Juli B wrote:
> > > I am having the same problem with the column bars automatically narrowing to 
> > > a line. I tried your fix suggestions, but no matter how I edit the x axis 
> > > values & scale, I cannot get the columns back. The only way I can restore the 
> > > column widths is to remove the x axis labels altogether (which makes the 
> > > chart useless).
> > > 
> > > Help!
> > > Juli B
> > > 
> > > "ShaneDevenshire" wrote:
> > > 
> > >> Hi,
> > >>
> > >> Don't know what's causing the problem, but you might try this as a solution.  
> > >> 1. Double-click the x-axis (across the bottom)
> > >> 2. Choose Scale tab and make sure the Base unit is set to Month(s)
> > >> 3. Make changes to the Major units and Minor unit if necessary
> > >>
> > >> If when you come into the Scale tab you don't see these options then exit 
> > >> the dialog box and choose Chart, Chart Options, Axis, Time-scale for the 
> > >> Category (X) axis.
> > >> -- 
> > >> Thanks,
> > >> Shane Devenshire
> > >>
> > >>
> > >> "Robert Brown" wrote:
> > >>
> > >>> I have a spreadsheet with a bar chart already in it (it's showing the number 
> > >>> of quotes sent monthly w/ trendlines).  In column A, I have a month-year and 
> > >>> in column B, I have the number of quotes sent in that month.  I changed the 
> > >>> source data to display a different set of months, and the bar widths narrowed 
> > >>> down to the width of a line.  I undid the change and the bar widths went 
> > >>> back.  I then tried to edit the chart formula one cell at a time and found 
> > >>> that when I reached a particular month forward in time, that that's when the 
> > >>> change occurred (row 40).  
> > >>>
> > >>> I thought maybe it was because I was adding too many months to fit in the 
> > >>> chart, so I started removing months on the front end of it to keep the total 
> > >>> number of bars static but that didn't make an impact.
> > >>>
> > >>> Then I noticed that as soon as I add that one month to the source data, the 
> > >>> bottom axis (is that X or Y, I forget) switches from monthly to weekly which 
> > >>> is why all the bars change width.
> > >>>
> > >>> So my question is, what is it about that cell is causing my chart to switch 
> > >>> like that?  I only have monthly numbers in column A, no weekly numbers.
> > 
0
Utf
4/30/2010 6:18:01 PM
Reply:

Similar Artilces:

I want to unlock my word doc to make changes its protected
I am writing a word document the other night. I went to carry on with it tonight but found it has protected the document and won't let me continue writing or editing Word 2007? Assuming that you have activated Office, it seems your trial version of the application has expired. Time to pay the piper! -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> ...

How to select series in chart?
I know I asked this question before, but (sigh) I cannot find the answer now, when I need it of course. How can I select a series in an Excel chart (XY Scatter) using the keyboard, not the mouse? The issue is: I have overlapping series, so it is difficult for me to select a series by moving the mouse cursor to a point in one series and right-clicking it, as I normally do. Someone once mentioned a ctrl and/or shift key combination (I think) that would allow me to select each series explicit in round-robin fashion. That is what I am looking for again. More generally, how could I have found...

Changing SQL Server for CRM 1.2
As my SQL server is currently on its last breath I am in the process of moving our CRM 1.2 databases to a new dedicated serever. I have detatched the databases from the old server, copied the files to the server and attached them. I have then gone into CRM deployment manager and changed to the SQL server to the new server. That all appears to work fine, no error messages. But when users try to access the CRM system they get an error and the CRM server event log fills with error messages. A few are listed below: dmLog: Failed to execute the SQL batch in the file sqlbatch.sql. MSCRM P...

Linking a Chart with graphics to ppt
Dear group, when I create a chart with scatters, which carry graphics like Excel-Block-Arrows and link this to Powerpoint, the graphics are shown there in a very low quality with large pixels. Any ideas how to improve this? Thanks in advance, Holger. ...

Territory Change
Our Sales VP has restructured all of the geographical territories that we had set up in Microsoft CRM. Our many thousands of Accounts are associated with territories. Obviously it is ludicrous to think that we would have to go one by one and change the territory on each account to the new territories. However, I have heard that there are strict rules for making changes directly to the backend SQL database. If we develop a SQL statement to change the territories assigned to each account to the new territories, are we going to be breaking anything? We don't want to screw up any de...

Multiple stacked bar charts
I have a project where i have to chart the compliance of three groups over a number of months. So for each data set there is a Compliance#1 stacked upon NonCompliance#1, then Compliance#2 stacked upon NonCompliance#2 and finally, you guessed it, Compliance#3 stacked upon NonCompliance#3. When i design a chart in excel it wants to place everything on top of each other producing one column of six not the required three columns of two for each week in the display. Can anyone point me i the right direction for this one?? Hi you can achieve this effect by having two layers in your x axis the at...

chart MAcro to change on activecell
Here is the macro below.. The only problem is that the columns change every month. so range (columns)keeps widening.... I have sorted it by selecting range upto column N. so it is provided for all 12 months. But now the "Grand total" column which is always the last column also gets included..(.which shouldnt be included in the range for the chart) Is there a way to modify this macro ? Sub updatechart1() Dim ThechartObj As ChartObject Dim Thechart As Chart Dim Userrow As Long Dim CatTitles As Range Dim SrcRange As Range Dim SourceData As Range If Sheets("summary").Ch...

transparent areas in charts?
How do you create transparent areas in imbedded charts? (for Excel 2003). When I click on "Format Chart area", then "Fill effects", there is a place where you can change the transparency settings, but it is faded out and will not work. Any Ideas? You can make most elements totally transparent. Ignore the transparency button, which is only for decoration. Instead, if you set an element's area to none, the element will be transparent. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech....

printing 2 charts on single page
Using Excel 2k, Win 2k I was trying to print to line charts on a single page to demonstrate the corelation between the two data sets. I would like to print in portait mode with one chart on top of page and one on bottom. Excel only seems to offer putting chart 2 on a separate page or embedding it in chart one. I am not very familiar with excel charting capabilites can anyone suggest a resorce? Thanks ...

double clicking and draging a column in a chart to chg data
in Excel 2003, double clicking on a column in a chart and then dragging the column up or down would change the data in a table upon which the chart depended. How does one do this in excel 2007? Tom Hi, That feature has been removed in 2007, there is no way to do it. If this helps, click the Yes button. -- Thanks, Shane Devenshire "Tom of inns" wrote: > in Excel 2003, double clicking on a column in a chart and then dragging the > column up or down would change the data in a table upon which the chart > depended. > > How does one do this in excel 2007? &g...

Changing font in Money 2004 register?
Is there any way to change the font(s) used in MS Money 2004's registers? The default font is too small and difficult for me to read. Also, is there any way to change the color scheme to something more pleasing to my eye (like you can do in Quicken...) Thanks. Nope and Nope. See http://umpmfaq.info/faqdb.php?q=136. "Debbie R." <debbimsr@bellsouth.net> wrote in message news:f5ff01c43e15$e2ae3700$a401280a@phx.gbl... > Is there any way to change the font(s) used in MS Money > 2004's registers? The default font is too small and > difficult for me to read....

transfer data from multiple columns to singlr column
I have data in form a d g b e h c f i (but larger scale) and I need it in a single column going a to z. Hi, highlight you data, copy, go to the column where you want to see the data, paste special, transpose "lc85" wrote: > I have data in form a d g > b e h > c f i (but larger scale) > and I need it in a single column going a to z. You up for using a macro? Sub ToOneColumn() 'dantuck Mar 7, 2007 &...

Problems migrating BCM data into CRM SB edition
Hi There I am having a problem migrating data from Business Contacts Manager (BCM) into CRM 3.0 Small Business edition. I have downloaded the BCM data migration pack and have followed the data migration documentation to the letter. I even cleaned up the BCM database prior to copying the files, checking them for errors using the Manage Database option in the Business Tools menu. It gets so far through the migration process and then bombs out. Here is the final few entries from the log file: 28/10/2006 12:18:53------>Transitioning to next screen. From: ConfigurationSummary screen. To: ...

changing values of one field based on another
How can I best change the values of one field in a table based on values of another field of the same table. We have an existing table of thousands of entries and I would like to use the following logic to populate a new boolean field. If field1 = "Done" Then BooleanFieldCompleted = True I have some Excel VBA experience but limited Access. I dont want to do this manually! Any assistance appreciated. In general, you'd use an Update query. However, in this case I don't see why you'd need such a field. Why not just create a query with a computed field that returns True...

Change the Exchange Virtual Directory to different website
I would like to remove the exchange virtual directory default website and move it another website which is currently redirecting to the website I want to delete. Meaning rather than logon to OWA as http://www.wheresmylunch.com/exchange (current default website) I want to move to http://www.getyourownsandwich,com/exchange. I am using Exchange 2000 server. Rube You would change the host header on the current website. -- Hope that helps, Dan Townsend This posting is provided "AS IS" with no warranties, and confers no rights. Please do not send email to this address, post a reply t...

Can't open 2005 data file after reinstalling Money 2005
I am experiencing a recurring problem. I have had to reinstall Windows XP and MS Money 2005. I am now unable to open my previously converted 2005 file or restore any backup version. I consistently get the following error message: "Money cannot locate filename or cannot open it, possibly because it is a read-only file, you do not have permission to change it, or your disk drive is write- protected. If you have chosen the correct file and it cannot be accessed, you will need to click OK and then Restore your most recent backup file." Any help or thoughts would be greatly ap...

Resolving Email Address to existing contact problem
Has anyone else come across this issue in MSCRM3.0? - when you attempt to resolve an email address to an existing contact in CRM (that does not currently have an email address stored in their details) the contact resolves OK but does not update the email address in the contacts details. Is this by design, or have I found a bug? ...

Business Portal Error-SQL server does not exist or access denied
Hi, We are running business portal 4.0 for one of our customer. It was running correctly, however, they have changed the SQL server port (previously it was set as default 1433). After that the business portal becomes very slow and while creating a new request (purchase requisition) if we open the item pop up; it is showing exception "SQL Server does not exist or access denied...." Can any body tell me how can I provide the new port number to business portal connection to the database. Thanks and Regards, Waliullah, Thanks for using the newsgroups. I have a...

Associating Multiple Existing Contacts to an Account, Until CRM 2.x
I have racked my pea-sized brain for endless days for a way to associate multiple existing contacts (or accounts) to an account. My client has accounts that have many contacts (or accounts) involved at different stages of acquiring a new account. The logical method would be to add a custom attribute with lookup functionality into the existing CRM contact or account objects but this is not possible with CRM 1.x. The resulting UI would allow an end user to associate different contacts (or accounts) to an account. Example: Account: ABC - AccountPhysician: (existing account or con...

Copying Data in a cell in one sheet to a cell in another sheet
I've run into a problem trying to copy data from a cell in one sheet to another. I have a spreadsheet called "rating" which contains a number of formula that calculates a final number. I also have a spreadsheet called "Final" that copies over the information from "Rating". In "Final", I'm trying to copy a number from "Rating" into a particular cell. I put in =Rating! G89, but it won't work. When I press enter, a window pops up "Update value:Rating". I press enter again and in the cell where I want the number ...

Customer check data
When customers pay by check RMS asks for specific information such as drivers license number, routing number, account number, address and phone number. Does anyone have a report and or a way to extract this info from the database for cases when the check is returned for NSF? Please advise, Scott We can write you this report. Contact me for detail on price . Afshin Alikhani - [ afshin@retailrealm.co.uk ] CEO - Retail Realm = = = = = = = = = = "Scott Santorio" <scott@tt-newyork.com> wrote in message news:e8ZKkR6$HHA.3716@TK2MSFTNGP03.phx.gbl... > When customers pa...

Money 2000 Account Balance Changed Inexplicably
Opening Money 2000, which I've used without problem since late 1999, I noticed that my checking account balance was overstated by almost $2000! I went to the register to see if there was a false transaction entered and it appears that this balance change goes back years with no obvious single entry being the culprit. When I run the "balance this account" function, it shows that the closing balance from my last statement, which was correct and rectified, is now also wrong and reflects the higher balance. What do I do now? The only thing that I can think of is to restore...

Macro to seperate data
Hi I seem to be struggling to find a macro that will work in previous threads. In sheet 1 is a list of data in columns A:N and the number of rows will vary. It is a list of sales with each sale record ocuppying one row. The salesperson's name is in column C and each salesperson will have multiple entries. What I am trying to do is create a seperate summary sheet in the workbook for each salesperson. Therefore sheets 2 to 20 are templates that already exist with a different salesperson's name entered into cell C3 on each of them. I am trying to find a macro that ...

Need HELP! for Linking data
Could someone please direct me to where I can learn how to link date in a work book. i.e., I have individual pages for each subject but I need the data that is entered in these individual pages to transfer to the Master page without having to manually in put it.........TNX Bubey, There are not too many bits about linking worksheets or workbooks that I can find. But have a look at the links below, in case they give you the information you need. I think it is frustratingly one of those things which is very easy when you know how, or if you can get someone to actually show you, but if you hav...

Changing language
I am running the Swedish version of Excel for XP and I need the US (or English) version. Is there a way to convert the language and all the settings associated with it? Using the swedish version is rather annoying since the formula names are translated to swedish. Thank you, Magnus ...