How to show X values on the X axis

massimodfd · #1 11-21-2020, 11:47 AM

Hi all!!
Please, can you tell me how to show X values on the X axis ?
example:
X Y
1,2 20
2,4 23
4,5 15
9,1 22
When I create the graph, on X axis automatically the scale is set from 0 to 10 but the values 1,2 2,4 4,5 9,1 do not appears.
I need to have numeric values on X axis, so the values need to be at the right scaled position from 0 and not at a constant space between each ones.
How is it possible?

Thanks

Purfleet · #2 11-21-2020, 01:55 PM

can you upload an example?

massimodfd · #3 11-21-2020, 02:40 PM

here is is
thanks!
example.jpg

Purfleet · #4 11-21-2020, 03:48 PM

thats a picture, i need the workbook

massimodfd · #5 11-22-2020, 01:41 AM

here it is!
thanks

Purfleet · #6 11-22-2020, 02:52 AM

you can change the Major units to 0.2 (or even 0.1) but due to the range of numbers it soon begins to look messy

Do you really need it as a line chart?

massimodfd · #7 11-22-2020, 06:18 AM

thanks !
but I need just X values 1,2 2,4 4,5 9,1 showed on the X axis .... not all the values!
I wonder if there's a way to do that
regards

Purfleet · #8 11-22-2020, 09:02 AM

Right, how about this?

p45cal · #9 11-22-2020, 09:28 AM

You can cheat. See attached, button and notes in the code.

Code:

Sub blah()
Sheets("Foglio1").Copy After:=Sheets(Sheets.Count)    'makes a copy of the sheet; remove when development finished.
With ActiveSheet.ChartObjects("Grafico 1").Chart
  PlotTop = .PlotArea.Top
  OrigTop = .Axes(xlCategory).Top
  .Axes(xlCategory).MajorGridlines.Delete
  .Axes(xlCategory).Delete
  .PlotArea.Top = PlotTop - 10
  .FullSeriesCollection(1).ApplyDataLabels
  With .FullSeriesCollection(1).DataLabels
    .ShowCategoryName = True
    .ShowValue = False
    .Position = xlLabelPositionBelow
  End With
  For Each pt In .FullSeriesCollection(1).DataLabels
    pt.Top = OrigTop
  Next pt
End With
End Sub

massimodfd · #10 11-22-2020, 10:01 AM

Yes! that's what I am looking for. Thanks a lot. Topic solved.

massimodfd · #11 11-23-2020, 02:48 AM

I am just interested now how to apply the macro to another sheet, with much more data..... how to adapt the macro ?
Some questions:
1. how to adapt the macro to a specific data sheet ?
2. where is the instructions creating the "button" ?
3. where can I change the colour of the vertical line of the graph (currently grey)?

I have some experience of programming but in this case I cannot see any instructions easy to understand!

Thanks for your feedback

p45cal · #12 11-23-2020, 03:46 AM

Quote:

Originally Posted by massimodfd

1. how to adapt the macro to a specific data sheet ?

The macro works on a chart, not a sheet. Record a macro selecting the sheet and then the chart. Copy the code here and I'll show you how to tweak it.

Quote:

Originally Posted by massimodfd

2. where is the instructions creating the "button" ?

Developer tab, Controls section, Insert dropdown: choose top left button (Form Controls, (Button, Form Control). Then click on the sheet, an Assign Macro dialogue box pops up: choose the macro from the list, Click OK.

Quote:

Originally Posted by massimodfd

3. where can I change the colour of the vertical line of the graph (currently grey)?

Manually, select one of them (all will be selected), right-click and choose Format Leader lines… you can select the colour in the pane which appears on the right. Automated, you need to use the likes of one of these:

Code:

    ActiveChart.FullSeriesCollection(1).LeaderLines.Format.Line.ForeColor.RGB = vbRed
    ActiveChart.FullSeriesCollection(1).LeaderLines.Format.Line.ForeColor.RGB = RGB(23, 185, 65)
    ActiveChart.FullSeriesCollection(1).LeaderLines.Format.Line.ForeColor.RGB = 11817066

To get the values after the equals sign for a specific colour of your liking, record a macro of you changing the colour of any line, fill of a cell, font colour, preferably using the More Colours… option, then the Custom tab (where you can see the individual Red/Green/Blue values) and you should see amongst the lines of recorded code the likes of:

Code:

.Color = 10605937
.ForeColor.RGB = RGB(46, 218, 46)

Use those values.

massimodfd · #13 11-23-2020, 09:49 AM

Thanks ! Very kind of you to explain all. I will review and try and in case of need I will get back to you.
Again, thank you!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to set specific axis values in graphs	Esaylikepie	Excel	0	10-16-2016 04:31 AM
Format Chart X axis - inconsistent values	TimTDP	Excel	2	12-01-2015 02:56 AM
X axis does not show Years from data	MARENG	Excel	8	01-20-2015 10:45 AM
Secondry Axis overlapping in bar chart dual axis in Powerpoint 2010	sharath_ms	PowerPoint	0	07-08-2013 10:06 PM
how do I add data values in Col A into X axis on bar graph?	hazz	Excel	1	04-27-2010 01:42 PM