Excel - Format as Table with multiple color themes?

Enc

So I got an odd request from a faculty client to have a sortable spreadsheet using the format as table option of excel, which is pretty simple stuff. What gets wonky is that they want to have half of the sheet using one color theme (gray/white) and the other using a different color theme (yellow/cream). Normally, if this was just for print I'd just manually recolor things to be looking the way it was requested but as this is a constantly accessed, and resorted, spreadsheet manual recoloring won't cut it as the every-other tinting the faculty member likes won't be there and as it gets resorted a lot, it will look ugly real fast with manual coloring.

From my searching it doesn't appear having two table themes going in the same table is particularly possible. If that's the case no worries, but it would be pretty great to get this done as requested if possible.

Anyone have any ideas or insights?

Enc

whoops, wrong button.

Orogogus

Conditional formatting will override format as table, and you can use and(), isodd(), iseven(), row() and column() functions to handle things like alternating columns or ranges. Something like =AND((ISODD(ROW())=TRUE), (COLUMN()>5)).

Tofystedeth

Does that still work if a filter is applied to the table?

Orogogus

Ugh. No.

If you want it to work when filters are applied, you can do so by creating two extra columns. One that's all filled with ones, and another with a =SUBTOTAL(9, [cell reference]) function, and add a conditional formatting rule based on that cell. But that only works with filters, not hidden cells.

tastydonuts

Enc wrote: »

So I got an odd request from a faculty client to have a sortable spreadsheet using the format as table option of excel, which is pretty simple stuff. What gets wonky is that they want to have half of the sheet using one color theme (gray/white) and the other using a different color theme (yellow/cream). Normally, if this was just for print I'd just manually recolor things to be looking the way it was requested but as this is a constantly accessed, and resorted, spreadsheet manual recoloring won't cut it as the every-other tinting the faculty member likes won't be there and as it gets resorted a lot, it will look ugly real fast with manual coloring.

From my searching it doesn't appear having two table themes going in the same table is particularly possible. If that's the case no worries, but it would be pretty great to get this done as requested if possible.

Anyone have any ideas or insights?

You should be able to use the vanilla highlight and "Format as table" from the ribbon on adjacent regions. What it likely won't do is apply the colorations unless at least one of the rows that encompass the the other table are also filled in.

What version of excel are we talking?

edit: just tested this in Excel 2013 and it worked, except for the above since it considers them two different tables.