Apply Separate Data Bars to Each Excel Column
Question details
Apply the same data-bar style across many Excel columns while comparing values separately within each column.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet has hundreds of columns, each of which needs its own data-bar scale.
- Observed behavior
- The desired result is for data bars in each column to reflect that column’s values rather than a shared range across multiple columns.
Identify the numeric rows to format and exclude headers or totals that should not affect the scale. Check whether the destination cells already have formatting, because pasting formats can overwrite it.
Copy data-bar formatting from the first column
Use Paste Special to quickly distribute the same formatting, then verify that each column has a separate rule range.
Select the numeric cells in the first column, then choose Home > Conditional Formatting > Data Bars and select a bar style.
Select the formatted cells again and choose Home > Copy or press Ctrl+C.
Select the corresponding rows in the remaining columns, then choose Home > Paste > Paste Special > Formats.
Choose Home > Conditional Formatting > Manage Rules and select This Worksheet. Check the Applies to ranges: each data-bar rule should cover only one column’s numeric cells. If a rule covers multiple columns, use the separate-rule method below for those columns.

Create a separate data-bar rule for each column
Use this method when copied formatting produces a rule shared by multiple columns.
Create column-specific data bars in WPS Spreadsheet
Use conditional formatting in WPS Spreadsheet to add data bars to each column separately, so each column can use its own value range.
- 1. Open the workbook: Open WPS Spreadsheet and choose Open to select your Excel workbook.
- 2. Select one column: Select the numeric cells in the first column, excluding headers and totals.
- 3. Add data bars: Choose Home > Conditional Formatting > Data Bars, then select a bar style.
- 4. Repeat for other columns: Select the numeric cells in each remaining column and apply the same style through Home > Conditional Formatting > Data Bars.
- 5. Save the workbook: Choose File > Save As and select the XLSX format to save an Excel-compatible copy.

Frequently Asked Questions
Why do my data bars compare values across different columns?
A single data-bar rule may apply to the entire selected range. Open Home > Conditional Formatting > Manage Rules and check whether its Applies to range spans multiple columns.
Can I copy data bars without copying cell values?
Yes. Copy the formatted cells, select the destination, and choose Home > Paste > Paste Special > Formats. This also copies other cell formatting and may overwrite destination formatting.
How can I confirm that each column has its own scale?
Open Home > Conditional Formatting > Manage Rules, select This Worksheet, and inspect each rule’s Applies to range. For automatic scaling, each rule should cover only the intended cells in one column.
Should I include column headers when applying data bars?
Select only the numeric data rows when creating each rule. Exclude headers and any totals that should not influence the column’s minimum and maximum.




