How to make a dynamic horizontal line data range in excel?

[linerangeguy]

I have two data points, a low and a high. I want to create a line graph that recognizes these two points as a range. Then, I have a third data point which will be the only data point on the line (the only dot shown on this range line). The line itself will be showcasing the range between the low and high. The range line length should not matter as it will be the same length even if the low and high change (want to make this dynamic). Where the third point is will visually show where that data point is on that range (between the low and high data points).

So I would have a low, lets say 20, a high, lets say 69, and then a third data point, lets say 46. I want to make an excel line visual where it is dynamic to each of the figures (so if the low and highs change to lets say, 10 and 110, the line length will stay the same but will depict that new range). And then on this visual line, the 46 will be shown as a plot point on that line and will be dynamic (so if 46 changes to say, 78, then the dot positioning will change). Below is an example of what I'm trying to create in excel (and very bottom is the mini sheet).

And a follow up question would be how to incorporate this range within a cell. So looking at the image above, the "Low" "Range" High" and "Average" are all columns in excel with data points.

Thank you so much for your help in advance!

mrexcel line range question.xlsx
	A	B	C	D
3	Data Point	Low	Range	High
4	160	155		302
5	218	211		261
6	126	126		208
7	209	204		374
8	59	46		128
9
10
11	Data Point	Low	Range	High
12	171	164		258
13	140	140		231
14	92	89		187
15	109	109		204
16	77	77		160
17
18
19	Data Point	Low	Range	High
20	40	24		105
21	99	92		149
22	148	148		227
23	99	87		160
24	97	86		142
25	118	112		166
26	105	101		172
27	-	-		-
28	83	74		142
29	131	127		176
30
31
32	Data Point	Low	Range	High
33	108	103		170
34	113	96		177
35	99	91		143
36	94	92		167
37
38
39	Data Point	Low	Range	High
40	141	136		222
41	81	80		117
42	128	128		241
43	129	121		224
44	87	77		163
45	76	70		146
46	103	90		175
Table

 

[Eric W]

Welcome to the MrExcel forum!

You've got a pretty big ask. I'm sure that you can get exactly what you want with some clever VBA programming, but it'll be a lot of work. Instead you might be able to use Sparklines, which is basically a mini-graph that fits in a single cell. Here's a link that explains a bit about them:

Excel Sparklines - A Complete Guide with Examples

Excel Sparklines are tiny in-cell charts for showing a trend over a period of time. There are 3 types of sparklines: Line, Column, & Win-Loss.

trumpexcel.com

I played around a bit with them and came up with 2 options. Consider:

Spark.xlsx
	A	B	C	D	E	F	G	H
1	Data Point	Low	Range	High
2	159.93	154.91		301.9		154.9083	159.9283	301.902
3	218.19	210.98		261.05		210.9788	218.1876	261.0514
4	126.47	126.47		207.71		126.4742	126.4742	207.713
5	209.16	204.01		373.83		204.0119	209.1561	373.826
6	58.686	46.19		128.33		46.19039	58.6858	128.334
Sheet3

Cell Formulas
Range	Formula
F2:F6,H2:H6	F2	=B2
G2:G6	G2	=A2

Sparklines don't come across in XL2BB, but here's how it actually looks:

I placed the 3 points in the F:H columns. Then I clicked on the cell, went to the Insert Tab, and inserted a sparkline. I then gave it the F:H range, chose the Line type. I then clicked Markers to get the red dots, and changed the vertical axis to 0. The middle dot, your "data point" will always be exactly halfway through the graph, but the height will be relative to the end points.

Second option:

Spark.xlsx
	A	B	C	D	E	F	G	H	I
9	Data Point	Low	Range	High
10	170.56	164.18	#	258.32		164.182	258.317		2.032415
11	139.7	139.7	#	230.87		139.6964	230.867		0
12	178.1	89.063	#	187.4		89.0628	187.402		27.16227
13	109.07	108.75	#	203.83		108.749	203.827		0.102421
14	123	77.183	#	159.58		77.1831	159.577		16.68215
Sheet3

Cell Formulas
Range	Formula
F10:F14	F10	=B10
G10:G14	G10	=D10
C10:C14	C10	=REPT(" ",I10+15)&"#"
I10:I14	I10	=(A10-B10)/(D10-B10)*30

And how it actually looks:

I put the Low and High points in F:G, and a formula in I that calculates the distance that the data point will be from the low point. I then set up the Sparkline as before, using F:G as the range, and NOT clicking on Markers this time. Sparklines allow you to also put in a value in the cell. So I set the font in that cell to Consolas, which is a fixed pitch font, and changed the font size to 2. Which is very tiny! That allowed me to put 60 characters in the cell. The first 15 characters are before the line, then I used the distance formula from column I to figure out how many spaces to add, then I added a formula in column C. The cell is bolded, and I changed the font color to red. And if you change the value in column A, the red dot will automatically adjust.

So a couple of options! I hope one of them works for you. You can of course play around yourself with the other options and maybe come up with something more to your liking. Let us know how it works.

 

[linerangeguy]

ok got it - appreciate the detailed response and link! Went with the sparklines as an alternative and will brush up on my VBA. Thank you so much!!

Welcome to the MrExcel forum!

You've got a pretty big ask. I'm sure that you can get exactly what you want with some clever VBA programming, but it'll be a lot of work. Instead you might be able to use Sparklines, which is basically a mini-graph that fits in a single cell. Here's a link that explains a bit about them:

Excel Sparklines - A Complete Guide with Examples

Excel Sparklines are tiny in-cell charts for showing a trend over a period of time. There are 3 types of sparklines: Line, Column, & Win-Loss.

trumpexcel.com

I played around a bit with them and came up with 2 options. Consider:

Spark.xlsx
	A	B	C	D	E	F	G	H
1	Data Point	Low	Range	High
2	159.93	154.91		301.9		154.9083	159.9283	301.902
3	218.19	210.98		261.05		210.9788	218.1876	261.0514
4	126.47	126.47		207.71		126.4742	126.4742	207.713
5	209.16	204.01		373.83		204.0119	209.1561	373.826
6	58.686	46.19		128.33		46.19039	58.6858	128.334
Sheet3

Cell Formulas
Range	Formula
F2:F6,H2:H6	F2	=B2
G2:G6	G2	=A2

Sparklines don't come across in XL2BB, but here's how it actually looks:

View attachment 105775

I placed the 3 points in the F:H columns. Then I clicked on the cell, went to the Insert Tab, and inserted a sparkline. I then gave it the F:H range, chose the Line type. I then clicked Markers to get the red dots, and changed the vertical axis to 0. The middle dot, your "data point" will always be exactly halfway through the graph, but the height will be relative to the end points.

Second option:

Spark.xlsx
	A	B	C	D	E	F	G	H	I
9	Data Point	Low	Range	High
10	170.56	164.18	#	258.32		164.182	258.317		2.032415
11	139.7	139.7	#	230.87		139.6964	230.867		0
12	178.1	89.063	#	187.4		89.0628	187.402		27.16227
13	109.07	108.75	#	203.83		108.749	203.827		0.102421
14	123	77.183	#	159.58		77.1831	159.577		16.68215
Sheet3

Cell Formulas
Range	Formula
F10:F14	F10	=B10
G10:G14	G10	=D10
C10:C14	C10	=REPT(" ",I10+15)&"#"
I10:I14	I10	=(A10-B10)/(D10-B10)*30

And how it actually looks:

View attachment 105776

I put the Low and High points in F:G, and a formula in I that calculates the distance that the data point will be from the low point. I then set up the Sparkline as before, using F:G as the range, and NOT clicking on Markers this time. Sparklines allow you to also put in a value in the cell. So I set the font in that cell to Consolas, which is a fixed pitch font, and changed the font size to 2. Which is very tiny! That allowed me to put 60 characters in the cell. The first 15 characters are before the line, then I used the distance formula from column I to figure out how many spaces to add, then I added a formula in column C. The cell is bolded, and I changed the font color to red. And if you change the value in column A, the red dot will automatically adjust.

So a couple of options! I hope one of them works for you. You can of course play around yourself with the other options and maybe come up with something more to your liking. Let us know how it works.

 

[Eric W]

Glad I could help! If you come up with something interesting, let us know!