How to Create Data Bars for Percentage Ranges in WPS Spreadsheets and Excel
Question details
The user needs to visualize a percentage range stored in a single cell, such as 25–50%, using Excel or WPS data bars. The task requires converting the text range into a numeric value or using a formula-based text bar.
- Product
- WPS Spreadsheets / Microsoft Excel
- Device & OS
- not provided
- Scenario
- A user is working with percentage ranges and wants a visual bar that represents the range in one cell.
- Observed behavior
- A percentage range stored as text cannot be used directly by conditional formatting data bars because data bars require a numeric cell value.
Confirm whether the percentage range is stored as text or as a number. Data bars only accept numeric values, so decide whether you will convert the range to a midpoint or upper limit, or use a text-based REPT bar instead.
Convert the Percentage Range to a Numeric Value and Apply Data Bars
Use this method when you want true conditional formatting bars that automatically scale in WPS Spreadsheets or Excel.
Data bars cannot read a text value such as "25–50%". Converting the range to a representative number—usually the midpoint or upper limit—lets conditional formatting compare the cell against the selected range.
Keep the original range text in its own cell and use a helper column if you need to preserve the display.
Click an empty cell next to the percentage range, such as B2 next to A2.
Type the midpoint or upper limit of the range as a number, for example 37.5% or 50%. You can also type =VALUE(LEFT(A2,FIND("–",A2)-1)) to extract the lower bound, but verify the dash character first.
Select the numeric helper cell, go to the Home tab, click Conditional Formatting, and choose Data Bars.
Select either Gradient Fill or Solid Fill. The bar appears immediately in the selected cell.
Go to Home > Conditional Formatting > Manage Rules, select the data bar rule, click Edit Rule, and set Minimum and Maximum to Number with values 0 and 1 for a true 0–100% scale.
Create a Text-Based Bar with the REPT Formula
Use this formula when you want a lightweight, cell-contained bar that does not require conditional formatting.
Control Data Bar Scaling Across Multiple Percentage Cells
Use this when several percentage values are selected and you want consistent 0–100% scaling instead of automatic min-max scaling.
Create Percentage Range Data Bars in WPS Spreadsheets
Turn percentage ranges into visual data bars by adding a numeric midpoint, applying conditional formatting, or using the REPT formula. WPS Spreadsheets gives you the same data bar tools as Excel with a familiar ribbon interface and full .xlsx compatibility.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office, click Spreadsheets, and open the workbook that contains the percentage range.
- 2. Create a numeric helper column: In an empty column next to the range, type a representative numeric percentage such as the midpoint or upper limit.
- 3. Apply data bars: Select the helper cells, go to Home > Conditional Formatting > Data Bars, and choose a gradient or solid fill.
- 4. Adjust the bar scale: Go to Home > Conditional Formatting > Manage Rules, click Edit Rule, and set Minimum and Maximum to Number with values 0 and 1.
- 5. Save the workbook: Press Ctrl+S to save the file as an .xlsx workbook that remains compatible with Microsoft Excel.
Frequently Asked Questions
Can Excel data bars show a percentage range like 25–50% in one cell?
No. Data bars require a numeric value, not text. Convert the range to a representative number such as the midpoint or upper limit, or use =REPT("|",K15*100) to create a text-based bar.
How do I stop 50% from displaying as a full data bar?
Edit the data bar rule and set Minimum and Maximum to Number with values 0 and 1. This creates a fixed 0–100% scale instead of scaling against the selected range.
Does the REPT formula work in WPS Spreadsheets?
Yes. In WPS Spreadsheets or Excel, use =REPT("|",K15*100) when K15 contains a decimal percentage, and omit *100 when it contains a whole-number percentage.
Where are data bars in WPS Spreadsheets?
Select the numeric cells, go to Home > Conditional Formatting > Data Bars, and choose a gradient or solid fill style.
What is the best numeric value to use for a percentage range?
The midpoint is usually best for a balanced visual, but you can use the upper limit if the bar should emphasize the maximum value in the range.
