Highlight row blocks in Excel
Plain tables are not easy on the eye. Without a visual hierarchy, they are difficult to follow and harder to analyse.
If you're using an official Excel table, you can toggle the Banded Rows option, which applies an alternating colour to each row, helping to enhance readability and interpretation.
However, this is rather limited, so what's the alternative?
In the example, a table contains a Region column with three rows each for 'Central', 'East', and 'West' before repeating themselves.
For this scenario, colouring in blocks of three is most appropriate, and conditional formatting lets us achieve this using custom formulas. Two separate rules are required — one for the yellow blocks and one for the green blocks.
Let's break down the first table row:
=𝙸𝚂𝙾𝙳𝙳(𝙲𝙴𝙸𝙻𝙸𝙽𝙶.𝙼𝙰𝚃𝙷(𝚁𝙾𝚆(𝟷:𝟷),$𝙴$𝟷)/$𝙴$𝟷)
𝘊𝘦𝘭𝘭 E1 𝘤𝘰𝘯𝘵𝘢𝘪𝘯𝘴 3.
• 𝚁𝙾𝚆(𝟷:𝟷) — returns the row number 1.
• 𝙲𝙴𝙸𝙻𝙸𝙽𝙶.𝙼𝙰𝚃𝙷(𝚁𝙾𝚆(𝟷:𝟷),$𝙴$𝟷) — rounds up 1 to the nearest multiple of 3, which is 3.
• 𝙸𝚂𝙾𝙳𝙳(𝙲𝙴𝙸𝙻𝙸𝙽𝙶.𝙼𝙰𝚃𝙷(𝚁𝙾𝚆(𝟷:𝟷),$𝙴$𝟷)/$𝙴$𝟷) — 3 ÷ 3 = 1, so TRUE returns because 1 is an odd number.
TRUE is also produced for the second and third table rows.
The first FALSE occurs in the fourth table row:
=𝙸𝚂𝙾𝙳𝙳(𝙲𝙴𝙸𝙻𝙸𝙽𝙶.𝙼𝙰𝚃𝙷(𝚁𝙾𝚆(𝟺:𝟺),$𝙴$𝟷)/$𝙴$𝟷)
As 4 is rounded up to 6 and 6 ÷ 3 = 2, FALSE returns because 2 is even.
These even values are deemed TRUE by the other rule, as the ISEVEN function is used instead of ISODD.
Finally, the advantage of using cell references instead of hardcoded numbers is connecting the cell to a spinner, making it easier to change the number of rows in a block.
#exceleration #excel #globalexcelsummit
---
The Global Excel Summit is the world's largest gathering of Microsoft Excel users and experts.
Find us on:
🌐 Website: https://globalexcelsummit.com
👔 LinkedIn: / global-excel-summit
🆇 X: https://x.com/ExcelSummit
🤳 TikTok: / globalexcelsummit
📸 Instagram: / globalexcelsummit
🟦 Facebook: / globalexcelsummit
🧵 Threads: https://www.threads.net/@globalexcels...