Pivot Chart Bar Width

Is it possible to stabilize the width of the bars when using a Pivot Chart?

When there is very little data to chart - based on the values I select from page data pull-downs - the bars are really fat.  Other times they are thin.  I would really like to have a standard width.

tia
Pat Watson
0
anonymous (74722)
12/1/2003 7:56:05 PM
excel.charting 18370 articles. 0 followers. Follow

1 Replies
809 Views

Similar Articles

[PageSpeed] 47

Pat -

The bars are based on a percentage of the spacing between categories. 
If you have fewer categories, the bars get wider.  If you decreased the 
bar width (by increasing gap width on the Other tab of the format series 
dialog), they'd be much further apart.

You can't adjust these automatically, but you can run a simple macro 
every time you adjust the chart.  This one assumes a clustered column 
chart type:

Sub FixClustColWidth()
     Dim Npts As Integer
     Dim Nsrs As Integer
     With ActiveChart
         Npts = .SeriesCollection(1).Points.Count
         Nsrs = .SeriesCollection.Count
         .ChartGroups(1).GapWidth = WorksheetFunction. _
             Max(0, 3000 / Npts - Nsrs * 100)
     End With
End Sub

If it's a clustered bar chart (horizontal) you might change the factor 
in the last line in the With construct from 3000 to 2000.  If you have a 
stacked column or bar type, let Nsrs = 1.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

PWatson wrote:
> Is it possible to stabilize the width of the bars when using a Pivot Chart?
> 
> When there is very little data to chart - based on the values I select from page data pull-downs - the bars are really fat.  Other times they are thin.  I would really like to have a standard width.
> 
> tia
> Pat Watson

0
12/2/2003 2:43:36 PM
Reply:

Similar Artilces:

Pivot based chart and adding secondary axis
Hi I would love to have a pivot based chart and superimpose another set of 'regular' series on a secondary axis. Is there a trick to get around doing this retaining the 'pivot' features? MTIA You can't add data from outside the parent pivot table to a pivot chart. It is possible to add pivot data to a regular chart, if you use the Source Data dialog, Series tab to add the data. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "DKS" <DKS@discussions.microsoft...

Progress Bar Chart
Hi everybody I have a problem for creating a chart. this is a problem of Worldskil Helsinki 2005 IT-Software application. Problem: http://i10.tinypic.com/2zyfqkh.jpg And data: Phase Activity Start Date Duration Planning Phase Planning Meeting 02-Jan 1 Planning Phase Develop Questionnaire 03-Jan 11 Collection Phase Mail Questionnaire 17-Jan 9 Collection Phase Receive Responses 20-Jan 15 Analysis Phase Data Entry 20-Jan 18 Analysis Phase Data Analysis 07-Feb 4 Analysis Phase Write Report 13-Feb 12 Action Phase Distribute to Board 27-Feb 1 Action Phase Board Meeting 01-Mar -- vb_4_ne ---------...

Chart in Excel
Is it possible to convert a Chart in Excel so that it is "fixed", ie. still looks the same, but is no longer connected to the source data ? I know that I could save it as a jpg, and then insert it as a picture, but is there a way to do it more directly. Thanks, Rob I have a note in my notes after drawings chart in conventional way 1.click the chart 2.highlight the formula in the fomrula bar 3.hit F9 4.hit enter now the chart is based on actual data and not cells. try a few small experiments. "OM" <OM@youknowwhere.com> wrote in message news:44f57d15$0$11970...

Combination chart with three data ranges
Hey all... I'm trying to create a combination chart with three data ranges on 2 axis... the primary data source is a volume category represented on the primary "Y" axis in hundreds, the secondary data source is a percentage category represented on the secondary "Y" axis in 0.00%... when using these first 2 data sources everything is fine... I need to add a third data source , also to be represented as a percentage category consistent with the same scale as the previous percentage... Excel will allow me to add the third data source, however, when I do so I no...

Removing errors from a Pivot table
I apologise if this has been asked before, but Could someone please advise me how to remove items from within a pivot table The first box of my pivot table has a pull down arrow which if I click on, it shows all the info it has found. at the top of the info there is a box which says "Show all" Beside each individual entry there is a tick box which results in the info either being used by the table (if ticked) or ignored by the table (if left clear) After several months of use there are now lots of these items which I will never refer to again and I would dearly love to delet...

Pivot tables #11
I've never got the hang of pivot tables. It's as if there's just one fundamental concept that I've missed. Can anyone recommend a good intro on the internet. Thanks. Tod Here are a few links: Debra Dalgleish's pictures at Jon Peltier's site: http://peltiertech.com/Excel/Pivots/pivottables.htm And Debra's own site: http://www.contextures.com/xlPivot01.html John Walkenbach also has some at: http://j-walk.com/ss/excel/files/general.htm (look for Tony Gwynn's Hit Database) Chip Pearson keeps Harald Staff's notes at: http://www.cpearson.com/excel/pivots.ht...

Pivot Table combining multiple columns
I have 20 columns that contain the same information Column B through Column U Column A Column B Column C Column D Column E Name of Customer Printer 1 Printer 2 Printer 3 Printer 4 I want to combine all 20 columns to give me a count of items that are equal in all the columns and have the ability to expand out to see the customers. So in this case: Column A Column B Column C Column D Column E Joe Blow Epson 7600 Epson 4000 HP 5500 C80 Petey Pablo Epson 4000 Epson 9600 E...

Excel Menu Bar Disappears after Powerpoint Use
Excel is open Using PowerPoint slide w/ embedded excel spreadsheet in it Launch spreadsheet from PowerPoint by double-clicking Switch back to Excel Menu Bar and Taskbars have disappeared, can't find any "Close Full Screen" floating menu option. What is the trick to restore the "normal" Excel view? ...

Excel
I'm charting a few investments. I want a simple line that goes up and down for the value, and that's easy to do. But how do I get the chart to display specific years (not days and months) across the bottom of the chart? Seth - Do you have a time-scale axis? Go to Chart Options on the Chart menu, click on the Axes tab, and see what's checked for Category Axis. Check Time Scale and continue. Double click on the axis. On the Scale tab, Major Unit should be in years. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http:...

How to show multiple data sets on one bar chart
I'm trying to compare three different values in four sets of individuals-female and male locusts and human beings. I'd like to plot the power/mass, velocity and acceleration alues for these three on the same x-axis, but the range of values (300-3) makes the graph look distorted unless blown up. Also error bars cannot be seen. Help please!!! You could normalize your data on a scale of 0 to 1, where 1 is the max of a given parameter across all of your data. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.c...

how do i draw a textbox on chart and copy onto word?
I drew a textbox on my chart but whenever, i click copy and paste onto microsoft word, the textbox disappears. how do i make sure it stays? or else, how do i draw a textbox directly on the picture copied? Was the textbox selected together with the chart when you Copy? I convert the chart to graphic by holding down Shift, click Edit -> Copy Picture, and Paste it in your workbook. Draw the textbox you want over the graphic, and make sure both the chart graphic and the textbox are selected when you Copy. "miko" wrote: > I drew a textbox on my chart but whenever, i click ...

Optimizing Spreadsheet for Pivot Table
Hello, I am trying to create pivot tables from existing spreadsheets that track staff labor hours. Yet, because these spreadsheets' cells of hours (in effect, a single 'record') reference both the columns (individual staff person) and rows (the tasks), MS Excel cannot create pivot tables (if I understand this functionality well enough): Excel needs to have all that data (i.e. a single 'record' with person, task, & hours) in a single row with the appropriately identified columns headers to reference to build the pivot table. 1) Am I correct in my assessment tha...

Change individual series from bar to line
I have created a chart with a secondary y axis, but now I need to change how each series is displayed. I thought it would be possible to highlight an individual series (bar in this case) on the chart, then change the display from bar to line for that series only. I do not see this option anywhere. Anybody know how to solve this? -- CLK-user1 ------------------------------------------------------------------------ CLK-user1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=36551 View this thread: http://www.excelforum.com/showthread.php?threadid=563030 Hi, ...

minimze to status bar.
Hi there, Can outlook 2003 be minimized into the taskbar rather than have it sit in the status bar? So there is just a little icon rather than taking up valuable status bar real estate? - thanks for any commens You can elect to not have the Outlook icon in the status bar but the minimization in the taskbar will not be an icon. -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After furious head-scratching, NS asked th...

Bar chart by month
I have a spreadsheet that has month & year in column A and values (KWH) in column B like: Jun-00 1185 Jul-00 2636 Aug-00 3009 etc. I would like to create a chart (and/or spreadsheet) that is grouped on the months and shows the average value for that month and where each year is in relation to that average. I envision a chart/speadsheet something like: 3000 2000 1000 Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec with bars on each month for that months data and a line/curve showing the average for each month. Thanks. DCH Let's assume...

Multple Tables-One Chart
I need to make a line chart that displays data from more than one table. I think my problem is getting the program to understand what I want as my x and y axis. The tables are in the same sheet. They are plotting water levels along a river. One table is for water levels on structures, taken by the people who own the structures. The other table contains the water levels as measured by the TVA, this table salso contains data on the avereage water level for the past 100 years. I need a table that has river miles ( the distance from a certain point in the river) as the x (bottom) axis, the water l...

Stacked column and column combo chart
I have the following data set: 2001 2002 Sales Product A 45 55 Sales Product B 102 123 Sales Product C 66 78 Total Gross Margin 60 77 I want the sales to be in stacked columns and the gross margin a separate column next to them, as it represents total gross margin for all three products; 2001 and 2002 are the X category labels. I can easily show the gross margin series as a line or area but when I attempt to show it as a column, the entire chart is converted to clustered columns. Is there a work-around? KG, Seems to me Stephen Bullen has an example of that at ...

showing a target value on a stacked bar
I'm migrating some old files from several sites and want to show progress against three criteria (Number of sites migrated, number of files removed and size of files removed). This is fairly straightforward as I simply plot the number achieved against the number remaining to get a stacked bar for all three criteria. My problem is that I need to show the planned progress on each bar (e.g. in week 3 we have migrated 3 sites but had planned to migrate 4) as a line on each bar. Reading through the forum, I can do this by plotting a stacked column and then changing the 'target' ...

Chart to new sheet
How can I move a chart from one sheet to another without the series refer to the first sheet? Copy the whole sheet that contains the chart, then copy the new data over the original data on the copied sheet. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "KBA" <KBA@discussions.microsoft.com> wrote in message news:0CD217AA-8E6E-4047-BBEC-5A5395483D85@microsoft.com... > How can I move a chart from one sheet to another without the series refer > to > the first sheet? ...

Pivot Chart won't save order change of data
I have created a Pivot Chart from data I've extracted from an external source and to wich I've added some columns of calculations. The default order of presenting data in the chart is not how I'd like it. I've tried changing the order by selecting a data series and changing the order in the Format/Data Series menu. This works fine until I save the changes and close the report. When I re-open the report the changes have not been saved. I've tried this over and over again and the same thing happens every time. How can I secure changes to the order of the data ser...

How can I delete a line in a power point organizational chart?
I have never used Powerpoint and I am trying to create an organizational chart. When I have moved some boxes the lines are crooked and I need to either figure out how to straighten them or delete and recreat them. Please help. What version are you using and how did you create the chart? Lucy -- Lucy Thomson PowerPoint MVP MOS Master Instructor www.aneasiertomorrow.com.au "MVP" <MVP@discussions.microsoft.com> wrote in message news:9EA0DE10-5E3F-4ABC-9367-ECF9D41BF1CA@microsoft.com... >I have never used Powerpoint and I am trying to create an org...

navigation bar in
Hi I have a bottom navigation bar on all my 10 webpages but on pages 1 and 10 when I preview the webpage the navigation bar temporarily shows up when the page first loads at the very top of the page. It disappears after 1-2 seconds. -- Thanks very much Fiona Publisher web pages, and especially text load from the top by design. If the navbar is still obscured after fully loading and rendering then adjust your layout by moving the navbar further down. If you have a follow up question or other questions about Publisher webs, then in the future please post in the web group...

Charts
Hi, Im trying to make a chart in Excel, I have a list of addresses and states, I want to make a pie chart of the states to show what percentage of all the states each state is. But if I select the columns and click on the chart and choose pie, in the end I just have a line. Ok im not that great at this yet, just looking for a little help. -- Sincerly, Nick Pavlovich You can create a pivot table from the data, with State in the Row area, and State again in the Data area, where it will become Count of State. There are pivot table instructions and links here: http://www.peltie...

Outlook 2007 To-Do-Bar display
I can't figure out why my appointments that I have scheduled do not show up in an outlined bar for a 2 month period in the To-Do-Bar. I have 2 months selected in the View options To-Do-Bar. My current months show up however; I created them in January and they showed up during that month. I am not sure I am explaining this right so I am including a link to an image of my screen. You can see on the upper right hand side how my 2 February appointments show up but not two that I have scheduled for March. Also the 2 month display doesn't highlight the dates that have appo...

Pivot Tables: Avoiding Cross-tabs (Nesting) in Row Variables
How can I place two variables in the row area so that in the data area I don't get a cross-tabs (nesting). For example, suppose I put variables A and B in the row area, while in the data area is a variable that I want summed. I want the sum for each value of A and the sum for each value of B, but I do not want the sum of each combination of an A-value and B-value. In my intended application, using a separate Pivot Table for each row variable is not desirable. I may have read this wrongly, but if you mean you have two sets of data one on top of the other, and what you really wan...