Chart & Visualization Issues

Create an Excel In-Cell Chart for a 52-Week Stock Range

Kushani NimanthikaKushani Nimanthika Nov 20, 2026 869 views

Question details

Create a compact chart that shows where the current stock price falls within its 52-week range.

Create an Excel In-Cell Chart for a 52-Week Stock Range
Product
Microsoft Excel
Device & OS
not provided
Scenario
The user wants a straight in-cell line with a current-price marker between the 52-week low and high, similar to a stock quote display.
Observed behavior
A standard sparkline may place the current-price point in the middle rather than at its proportional position within the price range.
Before you start

Enter the 52-week low, current price, and 52-week high as numeric values in separate cells. Confirm that the high is greater than the low and that the current price falls within the range.

Solution 1Recommended

Build a proportional stock range marker with a formula

Use this formula to display a straight line and place the current-price marker according to its position between the low and high.

The formula divides the range into 20 intervals. The marker position is rounded to the nearest interval, so it is an approximation rather than a pixel-precise chart.

1
Set up the prices

Enter the 52-week low in A2, the current price in B2, and the 52-week high in C2.

2
Enter the range formula

Select D2 and enter =IF(OR(C2<=A2,B2<A2,B2>C2),"Check prices",REPT("─",ROUND(20*(B2-A2)/(C2-A2),0))&"●"&REPT("─",20-ROUND(20*(B2-A2)/(C2-A2),0))). Press Enter.

3
Format and copy the display

Widen column D if needed, apply a monospaced font using Home > Font, and drag D2's fill handle down to display ranges for additional rows.

Build a proportional stock range marker with a formula
Check prices: If the formula displays “Check prices,” verify that the high exceeds the low and the current price is within that range.
Create a stock range display

Build a 52-week stock range marker in WPS Spreadsheet

Use a formula in WPS Spreadsheet to show a straight range line and a current-price marker inside one cell.

  1. 1. Open your worksheet: Open WPS Spreadsheet and use Open to select your stock worksheet, or create a blank workbook.
  2. 2. Enter the stock prices: Place the 52-week low in A2, the current price in B2, and the 52-week high in C2.
  3. 3. Add the range marker: Select D2 and enter =IF(OR(C2<=A2,B2<A2,B2>C2),"Check prices",REPT("─",ROUND(20*(B2-A2)/(C2-A2),0))&"●"&REPT("─",20-ROUND(20*(B2-A2)/(C2-A2),0))). Press Enter.
  4. 4. Format and fill: Widen column D, choose a monospaced font from the Home tab, and drag the fill handle down to apply the formula to other rows.
Position the marker proportionally between the 52-week low and highOpen and edit Microsoft Excel XLSX and XLS filesCopy the formula down for multiple stocksUse a familiar spreadsheet interface for free
microsoft office alternative - wps office

Frequently Asked Questions

Why does the current price appear in the middle of my sparkline?

A sparkline places points according to their order or dates, not their proportional position within a price range. Use the REPT formula above to position the marker between the low and high.

How do I make the range marker more precise?

Replace every occurrence of 20 in the formula with a larger number, such as 40, and widen the output column. More intervals provide finer positioning but make the line longer.

What should I do if the formula says “Check prices”?

Check A2, B2, and C2: the high must be greater than the low, and the current price must be between them. Enter the prices as numeric values rather than text.

Can I show price changes over time instead of a 52-week range?

Yes. Select an empty cell and choose Insert > Sparklines > Line, then select your historical price cells. For a regular chart with points spaced by date, insert a chart and configure its horizontal axis as a date axis.