Create an Excel In-Cell Chart for a 52-Week Stock Range
Question details
Create a compact chart that shows where the current stock price falls within its 52-week 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.
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.
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.
Enter the 52-week low in A2, the current price in B2, and the 52-week high in C2.
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.
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.

Insert an Excel line sparkline for a price trend
Use a line sparkline when you want to show a series of historical prices rather than a proportional range marker.
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. Open your worksheet: Open WPS Spreadsheet and use Open to select your stock worksheet, or create a blank workbook.
- 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. 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. 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.

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.




