Making Dynamic Formatted tables using Conditional Formatting

Hello Everyone,

This time I have come up with another conditional formatting trick. The Trick is to make Formatted & Bordered tables without using any VBA Code and that too are Dimensionally Dynamic. It means you can add as many rows and columns as you want, just by changing the rows and columns attributes by changing Reference cells as you can see in the above picture. So here are the steps:-

1. Make some Rows and columns Attribute references as I have done in my example i.e. Cell B1 and B2.

2. Select a Multi Columnar Range say $4:$1048576. It would decide the Maximum No. of Rows and Columns that your Formatted Table would have. In the attached example, I have covered all full rows from 4 to 1048576 (till end). So from here on, Multi columnar range is our Base Range on which Conditional formatting applies.

3. Once selected, we will proceed for applying Conditional formatting. Go to Home Tab >>  New Rule >> Use a Formula to determine which cell to format. 

4. Write following formula there:-

=AND(COLUMNS($A:A)<=$B$2,ROWS($4:4)<=$B$1)

5. Select the desired formatting and click OK.

Explanation:-

I have used  AND Operator to cover Two Conditions that would tell Excel whether to format a cell or not. First condition is COLUMNS($A:A)<=$B$2. The Columns Function gives the no. of columns in the Range provided ie. $A:A. This Range is Dynamic Range or COLUMN Relative Range. It changes when Excel interpretates this condition in context of each cell in the Base Range, on which Conditional Formatting applies. The $A:A range changes to $A:B, $A:C and so on, when Excel considers cells going column wise to left in the Base Range.

AND

So is the case with ROWS($4:4) which is a ROW Relative Range. The $4:4 Part changes to $4:5, $4:6 when Excel considers cells going down to bottom in the Base Range. The reason I have used $4:4 instead of $1:1 or else, that the Base Range starts from 4th Row.

So COLUMNS and ROWS functions give Columns and Rows count which changes, when excel interprates their dynamic parameter ranges while going left and down on each cell in the Base Range. Now, To restrict the Formatted Table dimensions to specified number of rows and columns, I have used the conditional operators with Columns and Row Attribute References (reference cells B2 & B1).

Now when excel goes to left while interprating conditional formatting formula over each cell, it will cover only 4 columns as COLUMNS($A:A)<=$B$2 condition will return TRUE till 4 columns. Similarly, when Excel goes to bottom while interprating conditional formatting formula over each cell, it will cover only 8 rows as ROWS($4:4)<=$B$1 will return TRUE till 8 Rows. So cells formed by intersection of  4 columns and 8 rows will only fulfil the condition. Hence the desired formatting will apply on them and a table will be the result.

I hope, you will like the trick and try it. Suggestions and feedback are always invited, so do comment. 

here is the example file:-

Dynamic Formatted Tables.xlsx

Regards,
Vikas Gautam.