Excel colour schemes are making me go blind

October 18, 2012 1:51 AM

How do I permanently change the colour scheme in Excel (2010) so that I don't keep on going blind due to other people's spreadsheets?

I work with a ridiculous amount of spreadsheets, and my boss likes to colour entire rows to get his point across. Red means the products are deleted, yellow means they're new, etc.

Unfortunately, we're talking #FF0000 Red and #FFFF00 Yellow in huge swaths across a spreadsheet and...ow. My eyes. Here's a rough mocked-up example, so that you can see my pain.

I know I can change the colour scheme for each individual spreadsheet, but we're talking dozens of spreadsheets and, often, I'm just quickly looking at them to check one item.

So how do I set up my Excel so that, no matter what opens, the colours Excel reads as "Red" and "Yellow" are much more...sedate? Is this even possible?

posted by Katemonkey to Computers & Internet (16 answers total)

Not an excel expert but a couple of pointers. Excel spreadsheets are xml these days so it could be possible to run text manipulation tools on the xml to alter the colour codes. An xml guru would know exactly what to do.

Secondly does your boss use styles? I'm guessing not, he probably selects a row and chooses a colour from the menu (if not what exactly does he do?). I doubt if wild horses would get your boss to change but if he could use theme colours rather than selected colours, the choices are more muted.

Perhaps, and your best hope, you could use conditional formatting. Is there a cell whose value indicates new or deleted products? If so, you could colour the rest of the row based on that cell's value. Perhaps you could add a product status cell to each row and use that to colour the row? There appears to be nothing in conditional formatting which allows you to say "if this is cell is red, then make it light red", unfortunately.

If not (he just colours it because he knows) then he is doing it very, very wrong and is using ad-hoc spreadsheets as a database, this always ends in tears.

If you tell us how your boss chooses colours and how products are marked as new, deleted etc, perhaps we could offer specific advice.
posted by epo at 3:15 AM on October 18, 2012

Maybe Cell Styles will help you achieve some comfort. Find them in Ribbon - home - styles.
posted by Ness at 3:31 AM on October 18, 2012

Oh but that would require you to select the rows. I'd either try and get boss to put information in a column instead of using colours and then add some conditional formatting to keep him in happy colour land, or Id ask him to buy me a copy of ASAP Utilities. One of the add-ins allows you to select rows based on fill colour.
posted by Ness at 3:37 AM on October 18, 2012

You could do bulk colour changes with a VBA macro, but, so far as I know, you'd have to manually import the script and run it for every spreadsheet. But I am no guru.
posted by Diag at 3:51 AM on October 18, 2012

Response by poster: He selects the cells, then chooses the Fill Colour and fills the cells. He doesn't actually put in any data indicating that this is a new product or a deleted product or anything like that - he just changes the colour.

I am aware he is using Excel wrong in this way. However, getting him to change anything is extremely unlikely.

I just wish there was a way to say "If Fill Colour = #FF0000, change Fill Colour = #996666"

But I'm guessing there isn't.
posted by Katemonkey at 3:58 AM on October 18, 2012

Best answer: Oh my bad, I see that Excel 2010 lets you find and replace based on formatting now, ignore the bit about needing addons. Its not instant but you could at least tone down existing spreadsheets in a few clicks. Perhaps you can record it in a macro if boss tends to use one or two colours.
posted by Ness at 3:59 AM on October 18, 2012

What @Ness said, I didn't know it did that these days.

This link shows you how to record keystrokes in a macro for later use.

Could you sell him on the idea of a product status code (and hence conditional formatting)? Sorting by product status is one obvious advantage.
posted by epo at 4:15 AM on October 18, 2012

Stepping completely outside the idea of changing the Excel chart, you could change the brightness setting on your monitor before opening his files. A few taps of the monitor button might fix this issue for you?
posted by Houstonian at 4:34 AM on October 18, 2012

Sunglasses is quicker :-)
posted by epo at 4:37 AM on October 18, 2012

You should seek mediation to resolve whatever the real underlying problems are between you and your boss.

Failing that, just buy an anti-glare screen filter.
posted by roofus at 6:21 AM on October 18, 2012

Quick suggestion is to do a Ctrl+ A as soon as it's open. That instantly takes the edge off the gawdiness.

Or a Ctrl+ A and "No Fill", then toggle colour/plain with Ctrl+ Z/Y.
posted by guy72277 at 6:22 AM on October 18, 2012 [2 favorites]

Response by poster: Sadly, Ness, although the find/replace does work, it does have a tendency to get weird with his spreadsheets and start going through thousands of cells before it decides that, yes, it can change them.

(Right now, it's going through one and it's on cell CT830379. What.)

But it does work after a fashion, which is better than nothing.
posted by Katemonkey at 6:24 AM on October 18, 2012

The CTRL-A hack may be the best you can hope for, just don't hit the delete key. I've noticed Excel gets strange with lots of data, where "lots" is lots of rows or lots of columns or even just lots of text in aggregate. At least put stuff in different tabs.

I hope you've got good backups 'cos at some point the straw that broke the camel's back will be put in place and it will all go horribly pear shaped. If I were doing IT support for you I would be smiling in anticipation of the impending train wreck.
posted by epo at 7:09 AM on October 18, 2012

Maybe he would go for this: You construct a custom palette made up of company colors - which are not as bright as the red and yellow in the Standard Colors selection, which, unfortunately, seems to be immutable. You make this the default color palette so he doesn't have to switch to it. He thinks this is so cool he starts using these colors instead of Standard Colors. It's a long shot, I know.
posted by lakeroon at 4:20 PM on October 18, 2012

If the coloring is always done by row, you can put in a macro like this, and even give it a button on the ribbon and/or a hotkey.

Sub SaveMyEyes()
    With ActiveSheet
        maxrow = 100
        For R = 1 To maxrow
            .Rows(R).Interior.TintAndShade = 0.4
            DoEvents
        Next R
    End With
End Sub

* The maxRow variable should be set to be the lowest number that realistically includes the biggest spreadsheet you'll encounter. You could have it work on every single row in a worksheet, but that takes a while. The larger the maxRow variable, the longer it takes (a few hundred is pretty fast though)

* The 0.4 for the TintAndShade function can be anything from -1 to 1. 0 is neutral (the original color), higher numbers are paler, and negative numbers are darker. I like .4, but you should experiment until you find something you like.

* If coloring isn't always by row, this is not your best bet, as it will make all the cells in each row the same color (but subdued) as the cell in column A.

* If you save the worksheet when you are done, the new colors will replace the old ones. If this will piss off your boss, I wouldn't do this.


Okay, so suppose you want to set this up so that it is always available for you to run at the press of a hotkey combination, like Ctrl-R (For 'Readable Worksheets'). Here's what you do:

If you don't have the 'Developer' ribbon visible (it should probably be the rightmost option on the menu bar in Excel):
- In Excel 2010:
-- go to File > Options > Customize Ribbon
-- On the right side, put a checkbox in the 'Developer' option
-- Click OK

When you're back in Excel,
- press Alt-F11 and the Visual Basic for Applications window will pop up.
- The top left pane should have an option for "VBA Project (PERSONAL.XLSB)", click on it.
- Click Insert > Module (note, not 'Class Module')
- In the window that pops up, paste the SaveMyEyes code from above
- hit Ctrl-S to save this right now.

At this point, if you had a worksheet open and active, you could pop your cursor somewhere inside the code you just pasted, and hit F5 and it will run the macro on that sheet. Do this on a test sheet before you screw with your boss's sheets!

If you want to make the lines paler or darker, or if you want to have it work on more rows, follow the guidance from earlier.

If you want to create a hotkey so that you can open one of his files and immediately hit Ctrl-r or Ctrl-Shift-Y or something (you should avoid using existing hotkeys like Ctrl-S or Ctrl-P), you can do the following:

Back in Excel (Not the VBA editor), click on the Developer tab, and click on "Macros" on the left.
- Toward the bottom of the pop-up window, you'll see a dropdown menu for "Macros In:", select "PERSONAL.XLSB"
- The listbox toward the top should list "PERSONAL.XLSB!SaveMyEyes", click on it one time.
- On the right side of the window, click on the 'Options" button
- Where it says "Shortcut Key" you can type a letter (or other character) in the tiny textbox after "Ctrl+". You can use 'r' or 'Y' to create the hotkeys discussed above.
- Click OK
- Close the next window


When you're back in Excel, remember to save the PERSONAL.XLSB file again. You can do this by clicking Alt-F11 again, then clicking the save icon or pressing CTRL-S.

Now, whenever you open one of the offending spreadsheets, you can press CTRL-r (or whatever you chose) and it will cool off the colors in the first 100 rows.
posted by i love cheese at 8:28 PM on October 19, 2012 [1 favorite]

Any chance you could get him to use the "theme colors" instead of the "standard colors"? If so it becomes very easy to change the shade / tint of all the colored items in a worksheet by going to "page Layout" and choosing "Colors".

One shortcoming of this is that the default theme colors don't have a bright yellow and bright red option, but you could change his default worksheet to use a different color scheme theme that did have those colors.
posted by vegetableagony at 1:22 PM on October 30, 2012