Quickly format cells or rows when cell value change in Excel
Kutools for Excel
Boosts Excel With 300+
Powerful Features
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.
Insert page break when cell value changes in Excel
Insert blank row when cell value changes in Excel
Click Kutools > Format > Distinguish differences. See screenshot:
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.
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 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:
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.
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:
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:
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:
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.
Notes
This feature supports undo.
Demo: format cells or rows when cell value change in Excel
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 2021, 2019, 2016, 2013, 2010, 2007 and Office 365.
Kutools for Excel
The functionality described above is just one of 300 powerful functions of Kutools for Excel.
Designed for Excel(Office) 2021, 2019, 2016, 2013, 2010, 2007 and Office 365. Free download and use for 30 days.
