One of the most tedious little tasks in Excel that no one ever talks about? Extending your data table formatting as you enter new rows!! Let's learn a conditional formatting trick that will automatically extend our table formatting to any new rows we add, so we can focus on the data that we're entering!
Check out the gif above to walk through an example. Every time we enter a new row in this table, we've got to update the new row's borders and fill color. To avoid doing this manually each time, let's leverage conditional formatting - first, we'll select the entire area that our table could potentially take up. Don't be afraid to overestimate here! The more rows, the merrier. Next we'll open the conditional formatting dropdown and select "new rule" and then "use a formula" to tell Excel which cells to format. Enter the formula ‘=$B3<>””’ to check to see if the cell in the first column of the row is not blank - this is how Excel determines the presence of data in the row, which cues it to check for and update the rest of the row's formatting details.
Finally, we'll set the formatting to match our data table by selecting the format button, adding borders, adding our fill color, and then hitting OK. Now, as we add rows to our table, they match our existing formatting seamlessly. So refreshing!
