Excel do dynamic “merge & Center” based on values or through “Conditional Formating”

Question

is it possible to dynamically apply "merge & Center" based on values or through "Conditional Formatting"?

I made it possible to add colors but I want to do more advanced thing with Excel do dynamic "merge & Center" something like the image below

all the values are dynamic!

Answer 1

Short answer to the direct question is ... "No"

Conditional formating can apply formating to individual or already merged cells, but it cannot cause cells to be merged.

The only way to achieve what you are asking for on the raw data would be through a vba macro, either based on a sheet or workbook event (complex and probably not worth the trouble) or user triggerd

Answer 2

You can't do merge and center with conditional formatting. Pivot tables will achieve this effect. You can use calculated fields to generate the year and week fields on your dynamic data.

Answer 3

Not using merge cells (which is horrible anyway), but for your case you could far better anyway. This will take a bit of setting up but gives exactly the effect you want.

Have a column (A) for month but simply repeat the date in it (A2: =C2). Don't calculate the month here, we want to retain the date so you can simply format to show month only later.

For column B weeks, use WEEKNUM(C2,2) to calculate ISO weeks (Monday = day 1) Copy these formulas down A and B as far as needed.

Format both of these columns to use a custom number format of ;;; this means all those values completely disappear, and we will bring them back selectively using conditional formatting.

For column A you need to see the month name if:

1. this is the first row, and after the 15th of the month
2. this is the last row, and before the 15th of the month

3. otherwise (normally) show the month name against the 15th as roughly the middle of the month. So, you need to apply a condition to cells A2:A999 (or whatever the length of your column is) as follows:

   =OR(AND($C1=$C$1,DAY($C2)>15),AND($C3="",DAY($C2)<15),DAY($C2)=15)

Note the first condition checks if the current row date = "whatever the label of your date column is" rather than hardcoding as ="Date" or somesuch.

Set the format for these to use a custom number format of "MMM" (three letter months), or whatever is your preference.

For weeknumbers, these should show similarly on days at the top or bottom of the list, or normally on Thursdays (visually this is mid-week if you have Mon=1, but if you want them against Wednesdays instead as "mid-week" day, use 3 instead of 4 in formula below). Apply conditional format by formula:

=OR(AND($C1=$C$1,WEEKDAY($C2,2)>4),AND($C3="",WEEKDAY($C2,2)<4),WEEKDAY($C2,2)=4)

And use a suitable number format (0 or 00 probably best)

To alternate background colours, use the same sort of approach - colour them all then conditionally colour some back again. So colour all the cells with a light background fill then use conditional formats to colour them darker (or vice versa) based on the value of these columns is "even" ie formula for weeks is something like:

 =MOD(B2,2)=0

or in the case of months include the MONTH calculation ie condition uses

=MOD(MONTH(A2),2)=0

Do this separately for months and weeks. It should now look something like the below image.

Hope this helps!

Answer 4

ISO weeks cannot directly be calculated in Excel (it is not the same as 'week starts on Monday').

Use the following formula:

=INT((B4-DATE(YEAR(B4-WEEKDAY(B4-1)+4),1,3)+WEEKDAY(DATE(YEAR(B4-WEEKDAY(B4-1)+4),1,3))+5)/7)

Source: Week numbers in Excel