Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

Quickly format cells or rows when cell value change in Excel

For example, you want to change shading or add borders when cell value changes in a column, normally you can apply conditional formatting to settle this matter. But what if inserting page breaks or blank rows when a column value changes? Here, Kutools for Excel’s Distinguish differences (by key column) feature will solve all these problems easily with several clicks only.

Office Tab Enable Tabbed Editing and Browsing in Office, and Make Your Work Much Easier...
Kutools for Excel Brings 300 Advanced Features to Excel, and Increase Your Productivity by 80%
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns and Keeping Data; Split Cells Content; Combine Duplicate Rows and Sum/Average... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Favorite and Quickly Insert Formulas, Ranges, Charts and Pictures; Encrypt Cells with password; Create Mailing List and send emails...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • Kutools works with Office 2007-2019 and 365. It supports all languages, and very easy to install or deploy. Full features 60-day free trial.

 Click Kutools > Format > Distinguish differences. See screenshot:

Distinguish Differences, insert page breaks, blank cells, or format cells/rows when cell value changes in the key column


 Insert page break when cell value changes in Excel

With the Distinguish Differences (by key column) feature of Kutools for Excel, you can easily insert multiple page breaks when the cell value changes in the specified key column. Please do as follows:

1. Click Kutools > Format > Distinguish differences to open the Distinguish differences by key column dialog box.

2. In the opening dialog box, please do as follows:
(1) In the Range box, please specify the selection that you will insert page breaks into.
(2) In the Key column box, please select the column whose cell values you will insert page breaks based on;
(3) In the Options section, please check the Page Break option.
Distinguish difference dialog

Tip: If you need to distinguish differences with matching case exactly, please check the Case sensitive option.

3. Click the Ok button.

Now you will see page breaks are inserted when cell value in the specified column changes. See screenshot:
Insert page breaks when cell value changes in the key column


 Insert blank row when cell value changes in Excel

You can also insert blank rows based on cell value changes in the specified key column with the Distinguish differences (by key column) feature. Please do as follows:

1. Click Kutools > Format > Distinguish differences to open the Distinguish differences by key column dialog box.

2. In the opening dialog box, please do as follows:
(1) In the Range box, please specify the selection that you will insert blank rows into.
(2) In the Key column box, please select the column whose cell values you will insert blank rows based on;
(3) In the Options section, please check the Blank row option, and specify the number of blank rows as you need in below box. See screenshot:
Insert blank rows when cell value changes

Tip: If you need to distinguish differences with matching case exactly, please check the Case sensitive option.

3. Click the Ok button.

Now you will see blank rows are inserted when the cell value in the specified key column changes.
Insert blnak rows when cell value changes


 Add bottom border when cell value changes in Excel

With the Distinguish differences (by key column) feature, you can also add bottom border to cells, rows in the specified selection, or entire rows when cell value changes in the specified key column. Please do as follows:

1. Click Kutools > Format > Distinguish differences to open the Distinguish differences by key column dialog box.

2. In the opening dialog box, please do as follows:
(1) In the Range box, please specify the selection that you will add bottom borders into.
(2) In the Key column box, please select the column whose cell values you will add bottom borders based on;
(3) In the Options section, please check the Bottom Border option, specify the border style, and choose the border color.
(4) In the Scope section, choose the format scope as you need: A. if you choose Key column, it will add bottom border to cells in the specified key column when the cell values in the specified column change; B. if choose Selection, it will add bottom borders to rows in the specified selection; C. if choose Entire row, it will add bottom borders to entire rows. See screenshot:
Add bottom border when cell value changes

Tips:
(1) If you need to distinguish differences with matching case exactly, please check the Case sensitive option.
(2) If you need to clear existing borders, please check the Clear existing borders option.

3. Click the Ok button.
Now bottom borders are added when the cell values in the specified key column change. See screenshot:
Add bottom border when cell value changes in the key column


 Highlight cell or row when cell value changes in Excel

This Distinguish differences (by key column) feature also supports to highlight cells, rows in the specified selections, or entire rows when the cell value changes in the specified key column. Please do as follows:

1. Click Kutools > Format > Distinguish differences to open the Distinguish differences by key column dialog box.

2. In the opening dialog box, please do as follows:
(1) In the Range box, please specify the selection that you will highlight differences.
(2) In the Key column box, please select the column whose cell values you will highlight cells or rows based on;
(3) In the Options section, please check the Fill Color option, and specify the highlight color.
(4) In the Scope section, choose the format scope as you need: A. if you choose Key column, it will highlight cells in the specified key column when the cell values in the specified column change; B. if choose Selection, it will highlight rows in the specified selection; C. if choose Entire row, it will highlight entire rows. See screenshot:
Fill cell or row when cell value changes

Tips:
(1) If you need to distinguish differences with matching case exactly, please check the Case sensitive option.
(2) If you need to clear existing fill color, please check the Clear existing fill color option.

3. Click the Ok button.

Now you will see the cells or rows are highlighted when the cell value in the specified key column changes.
Fill cell or row when cell value changes


 Notes

This feature supports undo.


 Demo: format cells or rows when cell value change in Excel

Kutools for Excel: with more than 300 handy features, free to try with no limitation in 60 days. Download and free trial Now!

Productivity Tools Recommended
The following tools can greatly save your time and money, which one is right for you?
Office Tab: Using handy tabs in your Office, as the way of Chrome, Firefox and New Internet Explorer.
Kutools for Excel: More than 300 Advanced Functions for Excel 2019, 2016, 2013, 2010, 2007 and Office 365.
Classic Menu for Office: Bring back familiar menus to Office 2007, 2010, 2013, 2016, 2019 and 365, as if it were Office 2000 and 2003.

Kutools for Excel

The functionality described above is just one of 300 powerful functions of Kutools for Excel.

Designed for Excel(Office) 2019, 2016, 2013, 2010, 2007 and Office 365. Free download and use for 60 days.

Screen shot of Kutools for Excel

btn read more      btn download     btn purchase

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.