Formatting Issues

Apply Separate Data Bars to Each Excel Column

Algirdas JasaitisAlgirdas Jasaitis Nov 13, 2026 868 views

Question details

Apply the same data-bar style across many Excel columns while comparing values separately within each column.

Apply Separate Conditional Formatting Data Bars to Each Excel 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.
Before you start

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.

Solution 1Recommended

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.

1
Format the first column

Select the numeric cells in the first column, then choose Home > Conditional Formatting > Data Bars and select a bar style.

2
Copy the formatted cells

Select the formatted cells again and choose Home > Copy or press Ctrl+C.

3
Paste formats across columns

Select the corresponding rows in the remaining columns, then choose Home > Paste > Paste Special > Formats.

4
Check the rule ranges

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.

Copy data-bar formatting from the first column
Check the scale: In Manage Rules, select a data-bar rule and choose Edit Rule. Automatic minimum and maximum settings should calculate the scale from that rule’s own range.
Work with data bars in WPS

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. 1. Open the workbook: Open WPS Spreadsheet and choose Open to select your Excel workbook.
  2. 2. Select one column: Select the numeric cells in the first column, excluding headers and totals.
  3. 3. Add data bars: Choose Home > Conditional Formatting > Data Bars, then select a bar style.
  4. 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. 5. Save the workbook: Choose File > Save As and select the XLSX format to save an Excel-compatible copy.
Apply data bars to individual columns for separate comparisons.Open and save Microsoft Excel XLSX files.Use familiar spreadsheet formatting controls.Work with worksheets in the free WPS Office suite.
microsoft office alternative - wps office

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.