5

I am trying to create a risk (5 by 5) matrix that only should show integer values for x and y axis 1 - 5.

How do I display only 1,2,3,4,5 on each axis to occur between grid lines, by only displaying integers?

enter image description here

EDIT I changed to a line chart as opposed to a scatter chart which allowed for displaying axis values between tick marks (but only on x axis).

How can I do this for the y axis as well?

enter image description here

enter image description here

enter image description here

Dataset for grid and plot series (L = Likelihood, C = Consequence, PL = Planned Likelihood, PC = Planned Consequence)

    1   2   3   4   5   L   C   PL  PC
1   1   1   1   1   1   1   1.5 0.5 1.5
2   1   1   1   1   1   2   1.5 1.5 2.5
3   1   1   1   1   1   3   3.5 2.5 2.5
3   1   1   1   1   1   3   3.5 2.5 3.5
4   1   1   1   1   1   5   4.5 4.5 3.5
5   1   1   1   1   1   5   4.5 4.5 4.5
4
  • 1
    Change the display of chart axes
    – DavidPostill
    Commented Mar 5, 2017 at 12:27
  • Can this be applied to the y axis as it is for the x axis, or do I need to fake it by plotting some points on x=0?
    – Vahe
    Commented Mar 6, 2017 at 3:42
  • Try it and see.
    – DavidPostill
    Commented Mar 6, 2017 at 8:59
  • Unfortunately on a line chart which allows discrete points between grid lines on x axis I cannot disconnect the plot that occurs on x=0 from the rest of the graph. It just connects the points on the x=0 to the graph points.
    – Vahe
    Commented Mar 10, 2017 at 3:12

2 Answers 2

4
+50

You can get a result like this using standard Excel formatting:

enter image description here

Method:

  • Plot the data in a scatter plot
  • Add vertical grid lines
  • On both axes, make the major unit = 1
  • Add axis titles
  • Rotate the Y axis labels as shown (this is important)
  • Change the number format on each axis to be (without quotation marks):

.

"#                     "

That format says to show the number, then add lots of spaces afterwards. The spaces cause the axis title to be recentered. Add more or less spaces to match the width of your plot.

4
  • Thank you very much for this creative solution, I believe it is sufficient. I hope it can be used on dynamic sized charts like 6 by 6, or 10 by 10, etc. My ultimate goal is to adapt it to dynamic chart size with variable domain range.
    – Vahe
    Commented Mar 15, 2017 at 4:07
  • Could be a problem if the range varies dramatically. In my tests, changing from 5 to 8 was ok, but more than 10 would probably need less spaces or else the plot to be resized. Standardising the likelihood and consequence scales to always be consistent may be an advantage? Commented Mar 15, 2017 at 4:14
  • I believe this answer can be adapted. My only last challenge remains to dynamically color the stacked cells (bar chart values) without manual means. My only thought is VBA on this.
    – Vahe
    Commented Mar 15, 2017 at 4:16
  • The colours are a different question. But I'd approach it by making the plot background transparent and having colour behind. Either as a dynamic image, or as cells sized to match the chart, with conditional formatting. Having a dynamic range could complicate things though. Commented Mar 15, 2017 at 4:24
1

This is currently not possible in Excel. When you select a graph type Line you are telling excel that your X-axis will be considered a category axis which let you display the values and lables between tick marks but because Y-axis is not a category one you can't do this on it.

There were some work around in Office 2013, you can check by googling:

"excel vertical category y-axis"

and see something like this link or like this link but I am not sure if this is working nowadays.

5
  • thank you for pointing me in the right direction. I will see what I can come up with. But your answer seems very close to what I am intending so I will momentarily award the bounty.
    – Vahe
    Commented Mar 11, 2017 at 2:00
  • I added other link you could check. If this answer is something you could consider, let me know and I will complement my answer with this workaround.
    – Joe
    Commented Mar 12, 2017 at 4:47
  • Please see edits, actually I found the second link via independent research to be more likely to solve my issue. My colored matrix still exhibits the issue. Not sure how to resolve without using a commercial solution.
    – Vahe
    Commented Mar 12, 2017 at 5:00
  • Could you tell me where you fine this alternative? I have also been checking for you if maybe some macros in VB might have a workaround.
    – Joe
    Commented Mar 12, 2017 at 10:49
  • Thank you Joe, second link was a link via google i found via searching for y axis category as suggested. I believe it was your google search suggestion that led me to the link #2. But as far as VBA, I was too thinking of it, but did not figure it out yet.
    – Vahe
    Commented Mar 12, 2017 at 15:17

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .