Calling an Excel expert please.....

  cheechman 18:52 07 Nov 2007

I've got a spreadsheet with lots of information and 4 levels of sub-totals.

Previously I have formatted the sub-total rows manually by adding different colours to each level of sub-total (pain!!)

I can format the cells which contain the subtotal but this excludes the blank cells within the row.

I've found the attached link.

Can anybody explain the 2nd half of the instructions as when I enter =ISBLANK($D1) the whole data table is affected regardless of the subtotal level.

I also don't understand what "repeat for the second argument means"??

click here

  Technotiger 18:54 07 Nov 2007

I expect if you wait a while VoG™ will be out there somewhere ...

  VoG II 19:01 07 Nov 2007

I don't understand either.

=ISBLANK($D1) is checking if D1 is blank but presumably your subtotal isn't in D1. If your subtotal was in A33 for example you would use


by omitting the $ (for an absolute column reference) and adding a $ before the row number to make the row reference absolute you could then use the Format Painter (paintbrush icon) to apply the same CF to other subtotals in the same row.

"Repeat these steps for the second argument" - I have absolutely no idea. What second argument???

  VoG II 19:02 07 Nov 2007

Evening Technotiger :o)

  Technotiger 19:14 07 Nov 2007

Good evening Friend, I guessed you would not be far away :-)

  cheechman 19:43 07 Nov 2007


Thanks for your help.

I've set the Styles using the summary cell. And set the conditional formatting on the whole row where the sub-total appears changing the cell reference as you described. However this does not affect the cells which contain the total. (because they aren't blank).

Until now (I've only done this for a few months) I format one row and then use the format painter on other rows within that level.

I was trying to avoid clicking on every row as I have to create approx 20 of these spreadsheets and in level 4 and 5 of the subtotals there are sometimes 30/35 different items. A whole lot of clicks.

I'd appreciate any suggestions and in the meantime I'll carry on clicking.

Thanks in anticipation.


  VoG II 22:00 07 Nov 2007

To be honest, I don't know as I rarely use subtotals. I suggest that you post your question on Mr Excel click here where you can post a snapshot of your sheet using this click here

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

The Evil Within 2 review-in-progress

InVision Studio takes on Adobe XD and Sketch

Camera tips to take better iPhone photos

Comment transformer un iPhone en borne Wi-Fi ?