Solved Excel 2010 - Moving Indicator Along a Horizontal Scale

September 16, 2013 at 20:17:28
Specs: Windows 7
Dear Sirs
I am trying to find out how to move an object or other indicator (a circle or a line marker etc) along a formatable horizontal line (so I can change the appearance) in an excel chart or spread sheet to indicate a set value against this line. The position of this object would be dependent upon a set value.
Eg. if the horizontal line represented zero to 20 (left to right) and the set value of the point was say 6.5 then the object would move to just less than half way along the line.


See More: Excel 2010 - Moving Indicator Along a Horizontal Scale

Report •

September 17, 2013 at 07:20:40
You could use the =REPT() function, it simply repeats a text string.

So if you have the number 10 in cell A1 then in Cell B1 enter: =REPT("|",A1)
and you will get something like:


Change the number in cell A1 and you change the length of the bar string.


Report •

September 17, 2013 at 07:36:45
✔ Best Answer
Check out this thread for a couple of options. You'll need to register (free) to download the files that show some examples.

In addition, I tried something with Conditional Formatting that seems to work. It will need some finessing, but this might get you started:

1 - Insert a line through C10:V10 (20 Cells)
2 - Enter 1 through 20 in C11:V11
3 - Find a character in a Wing Ding type font that you want to use as your indicator. e.g. an R will produce a "spiked circle" in Wing Dings font.
4 - Fill C10:V10 with that character
5 - Select C10:V10 and apply a Conditional Formatting rule of =C11<>$A$1
6 - Set the Format to Font Color = White

Whe you enter a number in A1 that matches a number in D11:V11, your indicator will appear in Row 10 in the matching cell. All non-matching values will be formatted as White and not show up.

With 1 - 20 in C11:V11, you'll only be able to match whole numbers. If you need values such as the 6.5 that you posted, you can make the columns smaller and insert 1, 1.5, 2, 2.5, etc. in Row 11 and use as many columns as you need. The smaller your increments, the more columns you'll need.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

September 17, 2013 at 17:34:12
mmcconaghy and DerbyDad03

Thank you both for your proposals.



Report •

Related Solutions

September 17, 2013 at 18:04:23
There is also a simple Conditional Formatting rule you could use.

Try this:

If your data looks like this:

 1)   100
 2)   200
 3)   300
 4)   400
 5)   500
 6)   600
 7)   700
 8)   800
 9)   900
10)  1000

Conditional Formatting 2007

1) Select your cell or range of cells, IE A1 thru A10
2) On the ribbon click Conditional Formatting
3) Click on New Rules, it’s near the bottom of the dialog box.
4) Click Format All Cells Based on their value, should be top of list.
5) In the Format Style Box, select Data Bar
6) The Type Shortest Bar box should say Lowest Value, leave it alone.
7) The Type Longest Bar box should say Highest Value, leave it alone.
8) In the Bar Color at the bottom, select a color.
9) Click OK

Your list of numbers should now be shaded according to the number in the cell.

Changing a number, changes the length of the shading.


Report •

Ask Question