Unable to see more than 1 vert bar when selecting 30 days cells of dates and data

[DBenz]

Hi
New to charts, watched some you tube videos, select the cell range and then go insert column and choose top left one. he gets a chart that is sized to suit the data .

I have first column M with dates and second column N with gas usage, I highlight for example the data below and go Insert>column and choose first option and see 1 bar dated 31 january 2014,

see image..I am unable to alter width of bar, or get more than 3 days to show, after much trialling. Why is the one cell outlined in purple yet if I click the chart the entire 32 days and gas use cells get highlighted. I have tried widening the chart but the bar just gets wider as well.
rALGzvH.jpg

01 January 2014 0
02 January 2014
03 January 2014
04 January 2014
05 January 2014 9.935
06 January 2014
07 January 2014
08 January 2014
09 January 2014
10 January 2014 8.734
11 January 2014 9.62
12 January 2014 11.2
13 January 2014
14 January 2014
15 January 2014
16 January 2014 9.355
17 January 2014
18 January 2014
19 January 2014
20 January 2014 8.75
21 January 2014
22 January 2014
23 January 2014
24 January 2014
25 January 2014
26 January 2014
27 January 2014
28 January 2014
29 January 2014
30 January 2014
31 January 2014 10.65545455

I thought I would start with a few cells, get the hang of it ! created as paste value from a vlink. I had a formula for the cells #N/A that removed that, and these cells are now blank having done a paste value into M and N.
I have in fact an entire 365 to display for 2014, a years worth, so how do I get all 365 on x axis, dates maybe every 7th day. Then again the 2015 usage sitting on top of this, or behind if more than 24, else it will block the view of 2014. maybe line chart better as have 2014 2015 2016 2017 2018 all in these two columns.

I see no examples on internet of plotting years data per day into a chart, their simple examples work but try it on something real world ! doesnt fit chart ! No means I can see of altering width of X axis items.

I try a test, copy paste the dates to another column paste value, type in a load of numbers, try again and get a decent graph, again with gaps, ditton, and the whole cel range gets the purple outline thing. Try again on that at left, get 1 bar, copy paste it to rhs paste value, and it gives no bar, note the last cell purple lined, what is going on ?
q7pmzN6.jpg

DBenz

[modytrane]

It would help if you can attach a sample file with data and state your needs.
I created a quick chart for you. See attached. It shows 31 days of data.

[DBenz]

Hi,
after lengthy testing I find its the true value numbers causing it, they came from a V Link on a simple calculation of gas reading minus gas reading /date minus date.

If I type them out to 3 dec places all is good, if I format the column as number to 3 dec places, still fails, as clicking on them still reveals the long number.

How can I alter these to just 3 dec places ?

attached a sample excel file

DBenz.

[modytrane]

Here's one way.
I have added a formula in column C to convert the numbers to three decimal places.
Using these numbers to create graph works.

See the attached file.

This is one workaround. If you have large database or need this to happen in the same column, you could write a macro to do the conversion and put the numbers back in the same column.

[modytrane]

Ok, so here's a file that has the macro to convert your numbers.

You can load your numbers in column B and click on the "Convert" button.

Let me know if it helps.

Here's the simple macro.

Please Login or Register  to view this content.