Forum Discussion
Full Dark Mode in Excel
The easiest way to do this is to use templates.
Firstly, you have to declare where you want personal templates to be saved.. Using file explorer, create a folder under Documents called Personal Templates, go into that folder and click the location bar so that you can copy that full path.. you'll need it in the next bit..
Now back in Excel go to File > Options > Save and paste the path value in the 'Default Personal Templates Location', and press ok.. this is where any templates you create will be stored.
Create a brand new spreadsheet so everything is completely empty and only a single sheet1.
Press CTRL-A to select all of the sheet and set the background colour, font colour, and border colour
To set the background colour, press the arrow next to the Fill Colour option and choose More Colours > Custom Tab, and set the background to #101010 which is RGB(16,16,16), and press Ok.
Ensuring all of the sheet is still selected, set the font colour to white in the usual way.
Finally set the border colour. For the border, press the arrow and choose More Borders (at the bottom of the border list). Set the Color to the bottom left of the grey cells "White, Background 1, Darker 50%", then press the Outline and Inside buttons to select all cell borders and press ok.
So now we have a dark background sheet with visible cell lines and white font.
Ensure you clear an test text before the next step..
To save it choose File - Save As and use Browse to select the folder you set in the first part for personal templates. Change the Save as Type dropdown to 'Excel Template (XLTX)', and give the file a meaningful name such as Blank Dark.xltx and press ok. Now when you do a file New, you can choose that template to start by looking in the personal tab.. once you've used it once, it will appear in the top of the screen to make it easier to select.
If you need to create multiple sheets, then simply highlight sheet1 and Copy, insert a new sheet and then Paste, the new sheet will then be dark too. If you regularly use multiple sheets, you can always add a 2nd sheet and save it as another template "Blank Dark 2Sheet.xltx" for example.
Hope this helps someone.. As a developer, I do everything I can to avoid looking at white screens all day.
--
Adrian Parker
Slightly more complex is how to set the dark template to be the default when you open either a new spreadsheet or just add a sheet.
In Excel, go to File > Options > Trust Center and click Trust Center Settings. Then select Trusted Locations on the left.
Double click the "Excel default location Excel Startup" and it will display the path so that you can copy it.
Now, the template you created for the dark theme with a single sheet on it, copy it to that folder and rename it to Book.xltx And also copy it and name it Sheet.xltx.
Now, whenever you create a new spreadsheet or even a new sheet, it will have the dark theme.
--
Adrian Parker
AdrianParker Nice work. The only trouble is that if you ever need to print something, you have to either convert it back or waste a heap of ink. Not worth the hassle for me. Microsoft really ought to create a proper dark-mode for older versions of Excel.
AdrianParker Thank you so much!
- Okay so in reviewing, it looks like this macro will do a nice job of changing colour of the worksheet to dark mode - but when it's time to print something, we're CERTAINLY not going to want to print what we're seeing on the screen! So if I want to work in dark theme/mode but print in a "normal" view, I'm going to have to toggle each and every time I want to print my worksheet? This is not a good workaround for the scenario outlined here. I too struggle to work on documents that have a glaring white background with dark text, and with Word and Outlook I don't have to - and when I need to print an email or Word document, it automatically prints it in "standard" acceptable formats. How do we create a macro that will apply the appropriate stylings to an Excel worksheet BUT still prints with a white background and black text??? THIS is the solution that is truly needed in order to be functional in practical applications...
- As long as Microsoft hasn't added a dark mode for Excel you are stuck with the only workaround I think is a little feasible: Settings Windows to high contrast mode, using the High contrast Black theme. That will affect the way everything in Windows is displayed, but it will leave your print unchanged. But I doubt if this is what you want to use 🙂
AdrianParker This part of your suggestion (where you mention the Trust Center locations) did not work for me. I'm a Microsoft 365 subscriber so I'm using the latest version of the Office suite.
The previous suggestion works for new workbooks but I need the dark mode enabled for opening existing workbooks as well.- The earlier steps worked for me very well. But this one for making it default didn't work...
Don't know if it maters, but I'm running office 2019.. perhaps you're using a different version that needs different settings ?
It's worth checking your areas and filenames.. I don't know if the case of the filenames matter but I was careful to set them to Book.xltx and Sheet.xltx.. worth a check.- This step didn't work for me either (using the proper case), unfortunately. I'm using Office 365, so whatever their current release is. That said, USDA has so much extra stuff on these computers that strange conflicts sometimes happen.
But I am grateful to at least have the template that I can load, so thank you for that!
AdrianParker thanks, but I would prefer a true black mode. I know we can fake it, but I don't want to change the cell formatting. I want a different display of the grid.
- doesn't copy and paste correctly when needed when you need to use copy/paste features.
- as far as I can tell, using the template is just setting the defaults for everything, which is what they'd have to do for a 'default' dark mode, so I don't really see any difference.
Then there's this lovely nugget! AdrianParker
- Thanks! I also had to uncheck to "Show the Start screen..." in general settings to get the first sheet to appear properly.
