If he opened a workbook created on his old computer, that workbook would print in color. If he then inserted a new worksheet into the old workbook, the new worksheet would only print in black and white.
He opened File, Options in both a new workbook and an old workbook in order to compare the two and determine if any of the settings were different, but there was nothing obvious that allowed some workbooks to print in color and others to be forced to print in black and white.
His frustrating workaround was to open an old workbook that prints in color. He would do a “Move or Copy” to copy the worksheet that prints in color. He would erase the data on the color worksheet and then copy and paste the data from the broken worksheet. As he noted in his email to me, “This is a workaround, but a sorry one.”
Although only printing in black and white seems to be a niche problem, the template that was causing the issue could have other rogue settings. For example, you might find that all future workbooks start in landscape mode, or they all display two decimal places by default, or all their pivot tables begin with hideous formatting of two rows of orange followed by four rows of yellow. So while this article is about printing in black and white, the solution can be generalized when all new workbooks created with Ctrl+N or any new worksheets inserted in an existing workbook exhibit a similar wrong setting.
SETTINGS HIDING ELSEWHERE
While the member was thorough in checking through the properties in the Excel Options, there are other places where settings can hide. In this particular case, if you go to the Page Layout tab and click Print Titles, you’ll be taken to the Sheet tab in the Page Setup dialog. There’s a Print section with checkboxes where you can choose to print gridlines, print row and column headings, print comments, suppress cell errors, and print in black and white (see Figure 1).
There are often many ways to accomplish the same task in Excel. In order to save on color ink, I often will click the Printer Properties link in the File, Print panel and choose grayscale printing. To my surprise, the grayscale setting from printer properties is stored in the worksheet. In my testing, I printed Sheet1 in grayscale and Sheet2 using color. After saving the workbook, closing Excel, and reopening the workbook, each worksheet uses the grayscale setting from before.
BOOK AND SHEET TEMPLATES
When I give my Power Excel seminar to an IMA chapter, my final trick before the first morning break is to show how to save your favorite settings in a template so that all future workbooks have the correct header and footer. The process involves creating a new single-sheet blank workbook. Choose all of the proper Page Setup settings, and then save the workbook as a template with the names of sheet.xltx and book.xltx.
Any new workbooks that you create with Ctrl+N will inherit all of the settings from book.xltx. Any new worksheets that you create by inserting a worksheet in an existing workbook will inherit all of the settings from sheet.xltx. It’s a good trick—unless you happen to accidentally include in the template the setting for only printing in black and white.
Whether you’ve created the sheet and book templates or want to find if there are errors in the existing template files, there are only two places where templates can be stored. To find the first place, go to File, Options. Choose Advanced from the left navigation. Scroll down to the section called General. As shown in Figure 2, there is a setting called “At Startup, Open All Files In.” If there is a folder specified in this section, look in that folder for a file called Book or Sheet.
The other place for templates is a system folder. The path to this folder will be different for each user. To find the path, follow these steps:
- Create a blank workbook.
- Use File, Save As, and click the Browse icon.
- In the “Save as type” dropdown, choose Excel Template (*.xltx). The Save As dialog will automatically change to the correct folder. For my Windows 7 computer running Excel 2013, the path is C:UsersJelenDocumentsCustom Office Templates. Yours will be different.
To correct the problem, such as printing in black and white, delete the book and sheet templates from these folders.
September 2016
