As was foretold, we've added advertisements to the forums! If you have questions, or if you encounter any bugs, please visit this thread: https://forums.penny-arcade.com/discussion/240191/forum-advertisement-faq-and-reports-thread/

Excel - Format as Table with multiple color themes?

EncEnc A Fool with CompassionPronouns: He, Him, HisRegistered User regular
edited December 2016 in Help / Advice Forum
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 on

Posts

  • EncEnc A Fool with Compassion Pronouns: He, Him, HisRegistered User regular
    edited December 2016
    whoops, wrong button.

    Enc on
  • OrogogusOrogogus San DiegoRegistered User regular
    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)).

  • TofystedethTofystedeth Registered User regular
    Does that still work if a filter is applied to the table?

    steam_sig.png
  • OrogogusOrogogus San DiegoRegistered User regular
    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.

  • tastydonutstastydonuts Registered User regular
    edited December 2016
    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.

    tastydonuts on
    “I used to draw, hard to admit that I used to draw...”
Sign In or Register to comment.