FUNDOO EXCEL

Shade every other or alternate rows in Excel

Highlighting alternate rows in Excel 2013 worksheet makes the data visually much more appealing. Instead of doing this manually, we can use Conditional Formatting feature to automatically shade every other row in Excel.

Conditional Formatting gives us flexibility to change the shade colour in case it is required. The biggest advantage of using Conditional Formatting is that the shading is dynamic i.e. it automatically updates if we insert or delete the rows.

Also read: How to Trace Precedents and Dependents in Excel 2013

Below steps will help you highlight alternate rows:

Select the data range where we want to highlight alternate rows.

Go to "HOME" tab >>> Conditional Formatting >>> Click "New Rule..."

This will open conditional formatting dialog box. Click "Use a formula to determine which cells to format"

Enter the formula =MOD(ROW(), 2)

Click on "Format" button and choose the shade or format for alternate rows

Click OK >> OK and you will see that alternate rows in selected data range are shaded as per desired format (here with GREEN fill)

More interesting articles on this website:

Excel 2013 has introduced some awesome new functions that can help professionals save time.

Some of the amazing new functions include: DAYS, IFNA, ISFORMULA, XOR, ISOWEEKNUM and many more.

Also read: Top 7 new features in Excel 2013 that every professionals must know

The new ISFORMULA function detects if the cells contains formula. We can use CONDITIONAL FORMATTING along with ISFORMULA function to identify and highlight cells containing formula.

Syntax: ISFORMULA (reference)

Reference can be a cell reference, a formula, or even a name that refers to a cell.

Suppose we have data-set as shown below, with expense occurred in each month and column H have formula for total expenses.

How to find and highlight cells with formula in Excel 2013

Follow the below steps to identify and highlight cells with formula:

Select cell range A4:H4 and keep A4 as active cell
Go to "HOME" tab >>> Conditional Formatting >>> Click "New Rule..."
This will open conditional formatting dialog box. Click "Use a formula to determine which cells to format"
Enter ISFORMULA function giving reference to active cell (here A4 cell) =ISFORMULA(A4)
Click on "Format" button and choose the format for the cells with formula
Click OK >> OK and you will see that all cells with formula are coloured with desired format (here with GREEN fill)