Horizontal axis formatting on all charts EXCEPT scatter

[Prevost]

Hi There,

Maybe I am missing something, but I believe the only chart type that gives the flexibility to display all the data you want (when working with date & time values as the x-axis) is the xy scatter.

In this, I can set the increments to whatever I want (1 min, 1 hr, 1 day, etc...) and choose the start and stop times.

I cannot do this on any other chart. I found this link:

Understanding Date-Based Axis Versus Category-Based Axis in Trend Charts | Creating Charts in Excel 2013 That Show Trends | Que

which gave a workaround to format horizontal axis on other chart types.

Does anyone else have this problem or what do people do to overcome this? Sometimes I want to show a bar graph instead of a line!

Thanks!

 

[Worf]

Hi

Is this what you want?

 

[Prevost]

Yes, that would be one of the graphs I would be looking to create! As well as taking the true dates and inserting them into a bar graph and having the same sort of functionality control for the time (vs day, month).

So also format the horizontal axis to show time and then control the increment of that time so it is equal (and to what I set it at). Also, control start stop dates on horizontal axis?

 

[Worf]

Hi

Here is one way to work with times:

· Create the chart as shown on the image below
· Delete the horizontal axis
· Add data labels
· With Excel 2013, choose format data labels/value from cells, to specify a range to be used. In this example it’s A73:A78. With previous Excel versions you will need a third party add in to do it.
· Drag the labels with the mouse or pick a predefined position

Changing the start date on the horizontal axis was shown at post #2, it can be altered at the axis options section.

 

[Prevost]

Hi Worf,

My data has multiple values within each day. So when I plot it to bar, it only has one value for one day and doesn't show the whole picture. XY Scatter shows everything, so doubling back the previous example given wasn't exactly what I was looking for (my apologies for confusions). In the XY scatter chart below, I can format the horizontal axis to whatever increment I would like. The bar chart gives me limited options and I have to set the axis to text axis in order for it to show what is shown below. Then I lose a lot of control over how the horizontal axis is formatted in terms of increments. (When I go into Axis Options - Labels - Specify Interval Unit, it maxes out at 255 so I am unsure of how to work around that).

Thanks

[/IMG]

 

[Worf]

Hi

I’m at work and can’t see the image, probably due to the firewall.
Will try later at home.

 

[Worf]

Here is a take from a charting expert, for us to consider:

It's not you who are missing an important fact, but those making the
requests.
A line chart is very effective at showing variation of a quantity over
time. Use markers to emphasize the observed data, or remove them to
emphasize the lines and the trends they show.
Column and bar charts are better than line charts at showing values for
discrete categories.

- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
Peltier Tech Blog - Peltier Tech Excel Charts and Programming Blog

This is an excerpt from this thread:time scale proportional using a column chart - not just for scatte | PC Review

 

[Prevost]

I did some additional digging. Before Excel 2013, you could plot minute-by-minute interval data over multiple days to a column bar chart and format the x-axis as Text. Then format the horizontal axis values to what you wanted and under Labels you could Specify Interval Unit to whatever value you wanted. In Excel 2013, the upper limit is 255, which takes the horizontal axis increment control away from you.

I did however, find a work around by essentially filling the area underneath the line chart by plotting a column bar chart on the secondary axis's.