SOLVED

Global conversion of 'fill colour' needed, e.g. find 'fill colourA' ' replace with 'fill colour B'

%3CLINGO-SUB%20id%3D%22lingo-sub-3111933%22%20slang%3D%22en-US%22%3EGlobal%20conversion%20of%20'fill%20colour'%20needed%2C%20e.g.%20find%20'fill%20colourA'%20'%20replace%20with%20'fill%20colour%20B'%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3111933%22%20slang%3D%22en-US%22%3E%3CP%3EI%20had%20an%20Excel%20spreadsheet%20created%20in%20October%202015%20which%20contained%20a%20lot%20of%20conditional%20formatting%2C%20specifically%20assigning%20colours%20to%20cells%20depending%20on%20their%20numerical%20values.%20It%20consisted%20of%20nearly%2040%20worksheets.%20The%20colour%20coding%20is%20significant.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20is%20also%20a%20lot%20of%20ordinary%20(i.e.%20not%20conditional)%20colour%20formatting%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOver%20time%20the%20spreadsheet%20grew%20to%20over%203gb%20and%20took%20a%20long%20time%20to%20open%20so%20I%20created%20a%20new%20workbook%2C%20created%20new%20sheets%20within%20it%20and%20copied%20the%20data%20across.%20I%20selected%20the%20data%20on%20each%20sheet%20and%20using%20copy%20and%20paste%20I%20copied%20the%20data%20to%20the%20new%20workbook%E2%80%99s%20sheets.%20The%20new%20workbook%20is%20significantly%20smaller%20at%201.5gb%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEverything%20worked%20OK%20except%20for%20the%20colour%20assignments.%20For%20instance%20pale%20blue%20and%20green%20have%20been%20transmuted%20to%20magenta%20and%20orange!%20Not%20only%20do%20the%20new%20data%20sets%20look%20awful%20but%20the%20significance%20of%20the%20colours%20has%20been%20lost.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EHow%20can%20I%20globally%20convert%20the%20new%20colours%20to%20the%20desired%20ones%20and%20why%20did%20they%20change%20in%20the%20first%20place.%20At%20a%20guess%20there%20are%20probably%2060%2C000%20cells%20affected%20scattered%20across%20the%20worksheets%20so%20a%20global%20solution%20is%20necessary.%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20properties%20of%20both%20workbooks%20give%20the%20file%20type%20as%20Office%20Open%20XML%20spreadsheet%20and%20I%20use%20Microsoft%20365%20(ex%20Office%20365).%20I%20use%20an%20iMac.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EVersion%201%20of%20the%20original%20workbook%20was%20created%20in%20March%202013.%20It%20may%20have%20been%20on%20a%20PC.%20The%20type%20was%20Microsoft%20Excel%2097-2004%20Workbook%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBefore%20and%20after%20shots%20showing%20how%20the%20colours%20have%20changed%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22DEllis1440_0-1643905477868.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F345048i44030E4679D4D41B%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22DEllis1440_0-1643905477868.png%22%20alt%3D%22DEllis1440_0-1643905477868.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22DEllis1440_1-1643905477868.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F345049i12DD74C0302445DC%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22DEllis1440_1-1643905477868.png%22%20alt%3D%22DEllis1440_1-1643905477868.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3111933%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3112942%22%20slang%3D%22en-US%22%3ERe%3A%20Global%20conversion%20of%20'fill%20colour'%20needed%2C%20e.g.%20find%20'fill%20colourA'%20'%20replace%20with%20'fill%20colour%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3112942%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1296241%22%20target%3D%22_blank%22%3E%40DEllis1440%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20old%20workbook%20probably%20had%20a%20custom%20color%20palette.%20You%20can%20import%20it%20into%20the%20new%20workbook%3A%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EOpen%20the%20old%20and%20new%20workbooks.%3C%2FLI%3E%0A%3CLI%3EActivate%20the%20new%20workbook.%3C%2FLI%3E%0A%3CLI%3ESelect%20File%20%26gt%3B%20Options.%3C%2FLI%3E%0A%3CLI%3ESelect%20Save%20in%20the%20navigation%20pane%20on%20the%20left.%3C%2FLI%3E%0A%3CLI%3EUnder%20'Preserve%20visual%20appearance%20of%20the%20workbook'%2C%20click%20Colors...%3C%2FLI%3E%0A%3CLI%3EClick%20the%20'Copy%20colors%20from'%20dropdown.%3C%2FLI%3E%0A%3CLI%3ESelect%20the%20old%20workbook.%3C%2FLI%3E%0A%3CLI%3EOK%20your%20way%20out.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S1124.png%22%20style%3D%22width%3A%20675px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F345103i0997ABB3B9B04AEB%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22S1124.png%22%20alt%3D%22S1124.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3113067%22%20slang%3D%22en-US%22%3ERe%3A%20Global%20conversion%20of%20'fill%20colour'%20needed%2C%20e.g.%20find%20'fill%20colourA'%20'%20replace%20with%20'fill%20colour%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3113067%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20but%20I%20can't%20find%20any%20mention%20of%20colour%20palette%2C%20nor%20do%20I%20have%20File%20%26gt%3B%20Options.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20all%20I%20have%20under%20File%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202022-02-03%20at%2019.43.13.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F345116iD251EE1685310905%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Screenshot%202022-02-03%20at%2019.43.13.png%22%20alt%3D%22Screenshot%202022-02-03%20at%2019.43.13.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%E2%80%83I%20tried%20the%20Save%20option%20but%20there%20were%20no%20options%20like%20the%20ones%20you%20showed.%3C%2FP%3E%3CP%3EI'm%20using%20Excel%20for%20Mac%20version%2016.54%20(Microsoft%20365%20subscription)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3113455%22%20slang%3D%22en-US%22%3ERe%3A%20Global%20conversion%20of%20'fill%20colour'%20needed%2C%20e.g.%20find%20'fill%20colourA'%20'%20replace%20with%20'fill%20colour%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3113455%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1296241%22%20target%3D%22_blank%22%3E%40DEllis1440%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETry%20Excel%20menu%20%26gt%3B%20Preferences%20%26gt%3B%20Color%20%26gt%3B%20Copy%20Colors%20From%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I had an Excel spreadsheet created in October 2015 which contained a lot of conditional formatting, specifically assigning colours to cells depending on their numerical values. It consisted of nearly 40 worksheets. The colour coding is significant.

 

There is also a lot of ordinary (i.e. not conditional) colour formatting

 

Over time the spreadsheet grew to over 3gb and took a long time to open so I created a new workbook, created new sheets within it and copied the data across. I selected the data on each sheet and using copy and paste I copied the data to the new workbook’s sheets. The new workbook is significantly smaller at 1.5gb

 

Everything worked OK except for the colour assignments. For instance pale blue and green have been transmuted to magenta and orange! Not only do the new data sets look awful but the significance of the colours has been lost.

 

How can I globally convert the new colours to the desired ones and why did they change in the first place. At a guess there are probably 60,000 cells affected scattered across the worksheets so a global solution is necessary.

 

The properties of both workbooks give the file type as Office Open XML spreadsheet and I use Microsoft 365 (ex Office 365). I use an iMac.

 

Version 1 of the original workbook was created in March 2013. It may have been on a PC. The type was Microsoft Excel 97-2004 Workbook

 

Before and after shots showing how the colours have changed

 

 

DEllis1440_0-1643905477868.png

 

DEllis1440_1-1643905477868.png

Thank you

4 Replies

@DEllis1440 

The old workbook probably had a custom color palette. You can import it into the new workbook:

  • Open the old and new workbooks.
  • Activate the new workbook.
  • Select File > Options.
  • Select Save in the navigation pane on the left.
  • Under 'Preserve visual appearance of the workbook', click Colors...
  • Click the 'Copy colors from' dropdown.
  • Select the old workbook.
  • OK your way out.

S1124.png

@Hans Vogelaar 

Thanks but I can't find any mention of colour palette, nor do I have File > Options.

 

This is all I have under File

Screenshot 2022-02-03 at 19.43.13.png

 I tried the Save option but there were no options like the ones you showed.

I'm using Excel for Mac version 16.54 (Microsoft 365 subscription)

best response confirmed by DEllis1440 (New Contributor)
Solution

@DEllis1440 

Try Excel menu > Preferences > Color > Copy Colors From

Great! Thank you so much. It's restored the original colour palette.

Note for anyone else needing to do this, it's actually under
Excel menu > Preferences > Compatibility > Copy colours from