You have a large data set in Excel, it can be difficult to read. One way to make it easier to read is to apply alternating row colors. if you need to create visual interest in your data you can choose alternate row colors in excel.
Use an excel table to alternate row colors
Whenever you create a dashboard, excel based tools and display tables in your report. It will be the best practice to apply alternate row colors to your range of data.
Please follow the below steps to apply alternate row colors to your excel data set using the table format.
- Select your data range A1:E27
- Go to Home Tab
- Choose Format as Table
- Choose any desired format, in this example you have to select Light Blue. To popup Create Table dialog box
- Make sure it has selected your entire data range
- Select the check box My table has headers
- Press OK
You can see your data has been converted as an excel table and applied alternate row colors. Later you can convert it back to your range instead of keeping it as an excel table.
Use Conditional Formatting to alternate row colors
You can use excel conditional formatting feature to apply alternate row colors in excel. When using Excel, you may want to use alternating row colors to help make your excel table more readable.
Follow the below steps to apply alternate row colors
- Select the cells that you want to format
- Go to the Home tab
- Click on the Conditional Formatting button
- Select New Rule… – to popup New Formatting Rule dialog box
- Select Use a formula to determine which cells to format
- Enter the formula =MOD(ROW(),2)=0
- Click the Format button and Select your desired format
- Press OK.
The MOD function returns the remainder value and the Row function returns the row number. By using the above combinations of MOD and ROW functions, you will get a boolean value True or False.
Eg. For the 7th row in your excel; MOD(7,2) returns 1; 1 = 0 is FALSE.
For the 8th row in your excel; MOD(8,2) returns 0; 0 = 0 is TRUE
Conclusions
You learned two different methods to apply alternate row colors in excel. The simple method is to convert your range as an excel table, apply the table format then convert it back to the range.
If you have any questions, please leave a comment below and I will get back to you as soon as possible.
