Step 2: Highlight the entire data and apply conditional formatting.In this particular example, we have shaded all odd rows. =ISODD(CEILING(ROW() –offset,n/n) Example All you need to do is to replace the ISEVEN function with its ISODD counterpart. Instead of starting with the second, you can as well shade the first group of n rows. For those that are not even, the conditional formatting is not triggered, therefore, they do not change. The conditional formatting is the triggered by the ISEVEN function which forces a TRUE for all the even rows in the groups. Now to count by groups of n, which in our case is 3, the count above is divided by n, 3, starting with 1 as illustrated below įigure 3: Illustration of how to shade alternating groups in excel.This function is responsible for rounding incoming numbers up to a specified multiple of n.įigure 2: Example of how to shade alternating rows in excel The result of the ROW function goes to the CEILING function.Notice that the first row of our data in the figure above is in row 6, so we can use an offset of 5. ROW function- this is used to normalize the row numbers to start with 1.This formula is composed of three functions, and they play the following functions: offset- is the number used to normalize the first row.This article provides a clear guide on how you can shade alternating groups of n rows while working in excel.įigure 1: Shading every n rows in excel General syntax of the formula When we want to shade every n rows in excel, we can use a formula that is based on ROW, CEILING and ISEVEN functions together with conditional formatting.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
March 2023
Categories |