2

How can I use Excel to produce charts like the images below? Is it possible?

I want to have two different x-axis scales with major tick marks every 10 values. The only data I would want to display would be a single data point, like a dot centered on the line, or a bar going from 0 to the value.

enter image description here enter image description here

My desire is to generate a series of these images with dynamic ranges for temperature, weight, distance, and speed. My goal is to be able to produce these as cheat sheets or overlays to videos where one unit of measure is being discussed and people can quickly relate to what it may be in another unit of measure. In some cases I may want to show a line with a weight range of 10 kg to 80 kg, and another time may be 0 kg to 100 kg.

If this isn't possible in Excel, can it be done in Power BI or another system that is readily available?

Ben
  • 433

2 Answers2

1

After posting my question I did some more searching and eventually came across some information that helped me make the chart (link below). It never formatted as nicely as I would have liked. My ultimate goal was to produce images at various scales that are publish quality. This one gets wonky depending on the input values and so I kind of lost interest in the battle. Now years later I decided I would share the file so other's can have a starting point.

It's unfortunate that getting this to work requires a lot of tweaking of the Excel chart object and there's no easy way to explain it as a textual answer here, especially 4 years later.

Excel image of a conversion chart with two x-axis scales

I have included a link to the Excel file that you can experiment on. If you can figure out how to get the labels to alternate or line up consistently I'd like to see how it's done.

Download the sample Excel file

How to set chart axis based on a cell value

Ben
  • 433
-1

1.Select a chart to open Chart Tools.

2.Select Design > Change Chart Type.

3.Select Combo > Cluster Column - Line on Secondary Axis.

4.Select Secondary Axis for the data series you want to show.

5.Select the drop-down arrow and choose Line.

6.Select OK.

Sorry, if it is not useful, found on the internet. Still learning.

Source: Microsoft Support

devnikesh
  • 1
  • 2