Weekly bar charts assumes i mean daily bar chart

Hi,
I'm trying to creat a simple bar chart that shows open tickets and 
closed tickets week by week.  I have 3 weeks of data and the bars 
themselves are ok (20 open 6 closed).  However, when using the date as 
the horizontal axis, Excel inserts the 6 days of the week between each 
miles stone.  So, if I want to see the number of tickets each Monday, 
Excel creates the chart with all the other days of the week, which have 
no data.  I'm left with 3 bars standing apart.  How can I get each 
Monday to stand next to each other and hide the intevening days? 
P.S.  The cells the chart is based on do not contain the intevening 
days.
thanks
0
SwampYankee
7/20/2007 7:39:19 PM
excel.charting 18370 articles. 0 followers. Follow

4 Replies
562 Views

Similar Articles

[PageSpeed] 59

Then, go to Chart Options, Axes Tab

Change X Axis to Category


"SwampYankee" wrote:

> Hi,
> I'm trying to creat a simple bar chart that shows open tickets and 
> closed tickets week by week.  I have 3 weeks of data and the bars 
> themselves are ok (20 open 6 closed).  However, when using the date as 
> the horizontal axis, Excel inserts the 6 days of the week between each 
> miles stone.  So, if I want to see the number of tickets each Monday, 
> Excel creates the chart with all the other days of the week, which have 
> no data.  I'm left with 3 bars standing apart.  How can I get each 
> Monday to stand next to each other and hide the intevening days? 
> P.S.  The cells the chart is based on do not contain the intevening 
> days.
> thanks
> 
0
7/20/2007 8:14:01 PM
In article <9686F6C8-4E49-4412-85BA-FAF2343EABF7@microsoft.com>, 
SeanTimmons@discussions.microsoft.com says...
> Then, go to Chart Options, Axes Tab
> 
> Change X Axis to Category
> 
> 
> "SwampYankee" wrote:
> 
> > Hi,
> > I'm trying to creat a simple bar chart that shows open tickets and 
> > closed tickets week by week.  I have 3 weeks of data and the bars 
> > themselves are ok (20 open 6 closed).  However, when using the date as 
> > the horizontal axis, Excel inserts the 6 days of the week between each 
> > miles stone.  So, if I want to see the number of tickets each Monday, 
> > Excel creates the chart with all the other days of the week, which have 
> > no data.  I'm left with 3 bars standing apart.  How can I get each 
> > Monday to stand next to each other and hide the intevening days? 
> > P.S.  The cells the chart is based on do not contain the intevening 
> > days.
> > thanks
> > 
> 
To clarify I am using Excel 2007,  I don't know if this makes a 
difference but I right clicked on the chart and don't see a 'chart-
options'.  However, I think what you are suggesting is to switch the X 
and Y axis.   In 2007 this is done with the select data option.  
However, it does not give me the chart I want.  I want to have the X 
axis show a week, and the Y axis show bar representing a number of 
units.  Switching the Axis gives me a chart that shows the dates on the 
Y axis and the number on the X axis.
0
SwampYankee
7/25/2007 5:14:52 PM
In Excel 2007, select the axis and press the Ctrl+1 (numeral one) shortcut 
to open the format dialog. On the dialog tab that lets you set the axis 
scale, change the 'date scale' selection to 'text' or 'category' (I forget 
the new nomenclature).

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"SwampYankee" <1@2.com> wrote in message 
news:MPG.21114f15f2312e4598969d@news.giganews.com...
> In article <9686F6C8-4E49-4412-85BA-FAF2343EABF7@microsoft.com>,
> SeanTimmons@discussions.microsoft.com says...
>> Then, go to Chart Options, Axes Tab
>>
>> Change X Axis to Category
>>
>>
>> "SwampYankee" wrote:
>>
>> > Hi,
>> > I'm trying to creat a simple bar chart that shows open tickets and
>> > closed tickets week by week.  I have 3 weeks of data and the bars
>> > themselves are ok (20 open 6 closed).  However, when using the date as
>> > the horizontal axis, Excel inserts the 6 days of the week between each
>> > miles stone.  So, if I want to see the number of tickets each Monday,
>> > Excel creates the chart with all the other days of the week, which have
>> > no data.  I'm left with 3 bars standing apart.  How can I get each
>> > Monday to stand next to each other and hide the intevening days?
>> > P.S.  The cells the chart is based on do not contain the intevening
>> > days.
>> > thanks
>> >
>>
> To clarify I am using Excel 2007,  I don't know if this makes a
> difference but I right clicked on the chart and don't see a 'chart-
> options'.  However, I think what you are suggesting is to switch the X
> and Y axis.   In 2007 this is done with the select data option.
> However, it does not give me the chart I want.  I want to have the X
> axis show a week, and the Y axis show bar representing a number of
> units.  Switching the Axis gives me a chart that shows the dates on the
> Y axis and the number on the X axis. 


0
jonxlmvpNO (4558)
7/25/2007 6:02:42 PM
In article <OLuLFYuzHHA.4928@TK2MSFTNGP03.phx.gbl>, 
jonxlmvpNO@SPAMpeltiertech.com says...
> In Excel 2007, select the axis and press the Ctrl+1 (numeral one) shortcut 
> to open the format dialog. On the dialog tab that lets you set the axis 
> scale, change the 'date scale' selection to 'text' or 'category' (I forget 
> the new nomenclature).
> 
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> Peltier Technical Services, Inc. - http://PeltierTech.com
> _______
> 
> 
> "SwampYankee" <1@2.com> wrote in message 
> news:MPG.21114f15f2312e4598969d@news.giganews.com...
> > In article <9686F6C8-4E49-4412-85BA-FAF2343EABF7@microsoft.com>,
> > SeanTimmons@discussions.microsoft.com says...
> >> Then, go to Chart Options, Axes Tab
> >>
> >> Change X Axis to Category
> >>
> >>
> >> "SwampYankee" wrote:
> >>
> >> > Hi,
> >> > I'm trying to creat a simple bar chart that shows open tickets and
> >> > closed tickets week by week.  I have 3 weeks of data and the bars
> >> > themselves are ok (20 open 6 closed).  However, when using the date as
> >> > the horizontal axis, Excel inserts the 6 days of the week between each
> >> > miles stone.  So, if I want to see the number of tickets each Monday,
> >> > Excel creates the chart with all the other days of the week, which have
> >> > no data.  I'm left with 3 bars standing apart.  How can I get each
> >> > Monday to stand next to each other and hide the intevening days?
> >> > P.S.  The cells the chart is based on do not contain the intevening
> >> > days.
> >> > thanks
> >> >
> >>
> > To clarify I am using Excel 2007,  I don't know if this makes a
> > difference but I right clicked on the chart and don't see a 'chart-
> > options'.  However, I think what you are suggesting is to switch the X
> > and Y axis.   In 2007 this is done with the select data option.
> > However, it does not give me the chart I want.  I want to have the X
> > axis show a week, and the Y axis show bar representing a number of
> > units.  Switching the Axis gives me a chart that shows the dates on the
> > Y axis and the number on the X axis. 
> 
> 
> 
That did it!  Thanks, chart looks great now.
0
SwampYankee
7/29/2007 2:02:41 PM
Reply:

Similar Artilces:

Unstable charts size
I am using in excel 2007 some charts developed in 2003 version; they are positioned in a single chart sheet (used..location ..option); at the first opening in 2007 I observed they were all contracted in size, and some of them missing (or too small to be found even at 400% zoom); after re-arranging, tried to fix their dimensions using "lock aspect" (looked around for another option but didn't find); finally, charts seems to be on their own, as their size is shrinking even while working in another sheet! Maybe there is someone to know a hidden option/command/trick in order ...

How to do this kind of chart in excel?
I wonder if this is possible. I have two gaussians represented by points and joined by a smooth line in excel. however, ideally I want the area under the curves to be shaded, and best of all, if possible, be semi transparent so that the overlap between the two curves is highlighted a bit darker than the area under the individual curves. x values are wavelength, y values are intensity is this possible? t.i.a Lee - Excel doesn't do transparent shading of area charts; otherwise that would be the ideal approach. You can get semitransparent column charts using an autoshape as an area...

Chart Labels Don't Show
This is really a beginner question I am sure. Would really appreciate some help. When I create a chart in Excel 2003, sometimes the plot area is too small and crowded and not all my data labels show up. Is there any way other than dragging the corners to make the chart bigger (which doesn't always work) to make it big enough for all my labels to show up? THANKS!! As answered in .charting: You can format the x-axis: Click on the x-axis to select it Choose Format>Selected Axis On the Scale tab, for Number of categories between tick-mark labels, enter 1 On the Font tab, you can...

Need help with complicated chart (xy chart? z axis? not sure!)
I have two groups of people from a survey I recently conducted, "probably" people, and "definitely" people. Each one provided a title, as well as choosing the price range they deemed fair for a particular service. I want to create a chart that captures how many of each group of people have a certain category of title and chose a specific price range. Thus, the data to chart looks something like this: Definitely: $1000 $ 3000 President 2 6 VP 8 3 Probably...

why i can't see menu bar?
Thank you why I can't see menu bar in office 2007 specially (Access 2007) Because there isn't one? Office 2007 uses a ribbon rather than menus. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "a" <youb@hotmail.com> wrote in message news:e1KbIk0LIHA.5328@TK2MSFTNGP05.phx.gbl... > Thank you > why I can't see menu bar in office 2007 specially (Access 2007) > > ...

using office 2007 chart in office 2003
I have created a Line chart using Excel 2007 in compatibility mode. When I open the workbook using Excel 2003 it does not know the chart sub-type, and when the chart is regenerated by my macro it defaults to 'Line with markers displayed at each data value'. I need to distribute my chart to users running Office 2003, and I need the chart to retain the original chart type. Any ideas please? The rule of thumb if your workbooks are to be shared with users of different Excel versions, is to create your workbook in the oldest version that any of the users has. Unfortunately Compatibi...

How can I keep my bar charts formatting when data is refreshed?
Hi all, I am running Excel 2002. I have a spreadsheet with a database query from a Navision database. From the data I have created a bar chart on a seperate sheet and whenever the data refreshes the chart loses it's formatting. Any ideas of how I can retain the formatting? Many thanks in advance for your help. Have you tried using an intermediate data set that references your input data and plot the intermediate data set? "Richard Thorneycroft (Husky Products)" wrote: > Hi all, > > I am running Excel 2002. > > I have a spreadsheet with a database query...

integration in scatter charts
With reference to the following webpage: http://www.trentweirproject.co.uk/archgraph.html The chart shows the level of the river (blue line) along with the optimum river level (horizontal lines) needed for various locations along the river suitable for kayaking. The effect I am trying to acheive is for excel to automatically shade in the area between the current level (blue) line and any of the horizontal lines. So, for example, when the current level reaches above 3, the gap between the blue line and the pink line would automatically be shaded in a [pink] colour. Is this possible? Does this ...

Dynamic Chart With Autofilter and Formulae
Hi, I've searched through the groups but van't find the answer anywhere! I have a spreadsheet as an output from Access (I can not get access to do the charts....). The spreadsheet contains extra colums that do running total sums of the data. I want to produce a chart that reflects the data in the spreadsheet based on my autofilter but the formulae that do the running total do not work because they are still using the hidden row data not the new filtered data. Anyone overcome this? I am familiar with VBA but only in Access. Cheers ...

May you help me? output data MS Access query to excel and create chart.
You can help me. output query in MS Access to new worksheet in MS Exce and create any chart. plz tell me. How to do it. it improtant for m Attachment filename: chart.jpg Download attachment: http://www.excelforum.com/attachment.php?postid=54454 -- Message posted from http://www.ExcelForum.com In Access, get the result of the query in a datasheet form. Copy. Switch to XL. Paste. Alternatively, in Excel, use Data | Import External Data > New database query... and follow directions on using MS Query to get data from the Access database. -- Regards, ...

surfact chart color
Is it possible to set the color of a surface charts lines (area) to the same color as the data font? Some data point of the same series having different colors. Looking at pivot table of hourly average data by month (hours on horizontal axis & months on the vertical). Each month certain hours are grouped into period A, others in period B, and still other hours into group C. These are contiguous hours. Month 2 has similar groupings, etc. I'd like the surface plot to color code group A, B & C hours. Is this possible? Thanks in advance. ...

Weekly Score
I have a calendar where the user picks a range of dates to see a weekly or monthly score. However I don't want the dates to show because it spikes out the scores for each day. If a user has 12 items that were qa'd during the week of 7//16 and 7/20. I want to be able to pick those dates on the calendar but for the report to say 12 and score not 7/16 2 95% 7/17 3 100% 7/18 2 88% 7/19 4 72% and 7/20 1 100% my code says : DoCmd.OpenReport "RWeeklySummary", acViewPreview, , "[DateQA] between #" & Me.txtbegindate & "# and #" & Me.txtenddate &...

Pivot Bar Chart
Hi, Using Excel 2002 SP3 to make Charts with pivot tables: it works fine to display data, but I can not manage to get error bars in the plot, or to put it differently: I make a pivot table bar chart and using another column in the data-row, I would like to add this to the pivot table bars as an indication of the error on these bars. It would be valuable if you would have idea's or suggestions, Thanks in advance, Roge ...

Setting reverse order for axis in OWC 11 chart component
I must say , working with the component is pretty much a nightmare and waste of time, as the structure is convoluted, different than Excel, and there's no macro recorder to aid in figuring out the bizarre calls. That off my chest I can't seem to nail down or find anywhere, the command to reverse the axis plotting, which is available from the wysiwyg under the Axis editor, as order - Show values in reverse order. The excel version reverseplotorder , is not available. Hi, I hate to show my ignorance but what is OWC 11? -- If this helps, please click the Yes button C...

Why does the Y axis invert in charting?
Hi, In a spreadsheet, I have a list of variables down column A, with information out to the left. When I chart that information in a stacked bar graph (so the variables are on the Y-axis of the graph), Excel inverts the order of the variables. So the variables that were at the bottom of the spreadsheet are now at the top of the chart. Is there anything I can do to fix this, other than reordering my variables in the spreadsheet? Thanks, Nathan Right-click the vertical axis, select Format axis, click on the Scale tab, then also select the bottom 2 checkboxes: Categories in reverse order...

Displaying dates by weeks in Report
I have to display data by weeks in a report. What is the best way to do it? weeks 5/24/2010 - 5/28/2010 5 time sheets returned On Fri, 28 May 2010 06:10:01 -0700, Alaska1 <Alaska1@discussions.microsoft.com> wrote: I use a function that turns a date into a weeknumber in the format yyyy-ww, which I can then group by. The function uses the Format function to do this conversion. -Tom. Microsoft Access MVP >I have to display data by weeks in a report. What is the best way to do it? > >weeks >5/24/2010 - 5/28/2010 5 time sheets returned Is that a...

what does $ operation mean
Check out help on relative and absolute references. Gord Dibben Excel MVP On Thu, 15 Dec 2005 15:33:01 -0800, "JO" <JO@discussions.microsoft.com> wrote: ...

How to get daily rates from Bank Website automatically?
It would be great If I could get daily rates (Dollar/Rubble, Euro/ Rubble) from a Web Site of an Official Russian Bank automatically into Access. I searched the group to find out an answer how to get a specific data from a web page into access. I understand that it needs massive coding. Do you have any idea to make it easier? Thanks Steve.. Better to update the TextBoxes manually on my form by using the data from Web Site. ...

Pivot Chart Help!
I have an Excel Pivot Table which is showing the average spending on the Canadian provinces by the fiscal year. The Fiscal Year is on the row area and the Province is on the column area and the average spending is a calculated field from the sum of spending divide by the population of a given fiscal year. At the bottom of the pivot table it show the "Grand Average" spending of all fiscal years for a given province. I use this Pivot table to create a Pivot Chart with the fiscal year on the x-axis and the avg spending on the y-axis. Diff color of line for each province is plotted on th...

Chart Niggle
Good evening all, I have created charts using JW's 'Chart From Combo Box' in chapter 15 from his Excel 2000 functions CD. This has worked great, being able to choose the required chart from the drop-down list, which I have adapted. However, the 'chart titles' do not change to the appropriate value when I choose the suitable chart, but remain the same throughout my choices, eg every chart title is called 'March', when in fact I am looking at a chart produced for February. I have tried naming the title in Options/Chart Title, but still all charts remain with the sa...

First time making a chart
Hello and thank you for your time. I would like to add a chart or graph to an excel sheet I am using now showing deposit growth every week but I am not sure if it is possible. Here is how the excel file works. D1 E1 F1 Month A Month B Month C $9,000 $11,000 $(4,000) Now the problem is this. Month A-C represent the months in a quarter for the year. So when you begin the 2nd quarter, Month A-C will go blank and start all over again with information being put in weekly. So my question is thi...

dynamic chart on user selected data range in Excel 2007
I am trying create a chart 'on the fly' based on the user selected data range in Excel 2007. I was trying to use Jon Peltier's 'interactive chart creation' VB scripts. Somehow it does not work on Excel 2007. After Jon's interactiveChartCreation.xls opens, both input boxes does not work, meaning one can not input any data by mouse selection. I tried to click 'ok', but it only selected the input box. I hope Jon or some one else can see this and offer some help. Thank you all very much for your tips. This worked fine in a test I just ran. Did this wor...

How do I show break in bar graph to show large and small numbers #2
I want to show sales results of twelve branch offices on one bar chart. One office has large sales numbers as compared to others. To show the branches with small sales numbers on the graph I need to add a break in the bar graph of the branch with large sales numbers. How can I do that? Thanks GK I've posted an example solution on my web site: http://peltiertech.com/Excel/Charts/BrokenYAxis.html At the end of the page are links to Andy Pope's and Tushar Mehta's solutions. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutio...

Lock pivot chart formatting colors
Is there anyway to lock the color etc. When I do a refresh it changes them all back to their originally created colors! thanks robert Things to try -- if they don't work, you could record a macro as you refresh and reformat the pivot table. Then, run that when you want to update. Set the pivot table to preserve formatting: --On the pivot toolbar, choose PivotTable>Table Options --Add a check mark to 'Preserve formatting', click OK Depending on your version of Excel, you may also have to enable selection (From the Pivot toolbar, choose PivotTable>Select, and click on...

Charts in closed groups shrinking
Having a wierd problem with charts - pie charts specifically - I've included in collapsible groups in a spreadsheet I use to do analysis. Hoping someone here can help. Basically I'm finding that if I size a pie chart that's in a collapsible group (row group), collapse that group, save the XLS, then uncollapse the group the pie chart is approximately half its size. If I do it again the chart is half again its original size. Does anyone know what's going on? Is there a way to make sure Excel preserves set chart sizes? I can't find any setting around this, and its annoyi...