Image: puruan, Getty Images/iStockPhoto

Colors are great for readability onscreen while working in Microsoft Excel. You can use them to denote input cells, formula cells, to delineate sections, and much more. All of those visual clues are helpful to the user, but not always for the person who’s viewing a printed copy. All of the formats that you’ve applied to make using the sheet easier can interfere with readability when printing the data. Fortunately, it’s easy to print a color sheet in black and white. In this article, I’ll show you how to print in black and white manually and using a VBA procedure.

Disclosure: TechRepublic may earn a commission from some of the products featured on this page. TechRepublic and the author were not compensated for this independent review.

I’m using (desktop) Office 365 but you can work with earlier versions. Neither way is supported by the browser edition. You can work with any workbook or download the demonstration .xls file.

LEARN MORE: Office 365 for business

How to print in black and white manually in Excel

The easiest way to print in black and white is to manually set the print setting. On the con side, your users must remember to set it if the setting isn’t saved. We can quickly illustrate this quick solution using the Excel template shown in Figure A. (I choose it because it has a lot of color formats.) Now, let’s walk through the process:

  1. Click the Page Layout tab.
  2. Click the Page Setup group’s dialog launcher.
  3. In the resulting dialog, click the Sheet tab.
  4. In the Print section, check the Black and White option (Figure B).
  5. Click OK.

Figure A

Figure B

To see the results of checking this setting, click the Print option on the File tab (Backstage area). As you can see in Figure C, there are no background or font colors being sent to the printer, despite what you see on screen!

Figure C

If you always want to print to black and white and you have no other sheets in the file to consider, you can save the workbook with this setting checked, and you’re done. However, you might have situations that warrant more flexibility.

How to automate black and white print in Excel

Easy is always the best way to go, but workbooks often have more than one sheet, and those sheets have different printing needs. If, for instance, you have more sheets and you want only sheet1 (our active sheet) to print in black and white, you can use a simple Visual Basic Editor (VBE) procedure to make that happen. Don’t worry if you’re not familiar with VBA; I’ll show you exactly what to do.

First, if you’re using a ribbon version, save your workbook as a macro-enabled file (.xlxm). Then, press Alt+F11 to open the VBE. Choose ThisWorkbook in the Project Explorer. If this isn’t open, press Ctrl+R or choose Project Explorer from the View menu. In the ThisWorkbook module, enter the code in Listings A and B.

Listing A

Private Sub Workbook_BeforePrint(Cancel As Boolean)

Print in black and white

With ActiveSheet

.PageSetup.BlackAndWhite = True

End With

End Sub

Listing B

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Disable black and white setting.

With ActiveSheet

.PageSetup.BlackAndWhite = False

End With

Don’t try to copy the code from this web page; the VBE won’t be able to interpret unseen web characters. Enter the code manually. Or copy the code from this web page into a text editor and then copy the code from the text editor into the module.

Return to Excel, and print the sheet. Print Preview may show the color, but don’t worry, it should print in black and white. If your printer is far away, print to a PDF file and then open it.

The macro will set the Black and White option that you set earlier via the interface. If you save the workbook, it will save the setting. The BeforeSave procedure disables the setting when you save the workbook. That way users can save their work without impacting how the first procedure works. At this point, the macro seems redundant, but only because I’ve not presented more complex situations where you might want to print in black and white and color. In this case, it’s important to preserve the Black and White setting.

Stay tuned

This article offers two easy ways to force Excel to print a specific sheet in black and white. If this works as is, great. But often, you will need even more flexibility. In a future article, I’ll show you how to build on what you’ve learned in this article to handle sheets where you need both options: To print in color and black and white.

Subscribe to the Developer Insider Newsletter

From the hottest programming languages to commentary on the Linux OS, get the developer and open source news and tips you need to know. Delivered Tuesdays and Thursdays

Subscribe to the Developer Insider Newsletter

From the hottest programming languages to commentary on the Linux OS, get the developer and open source news and tips you need to know. Delivered Tuesdays and Thursdays