How to print Excel sheets in black and white easily

How to print Excel sheets in black and white easily. While colors are useful for enhancing readability onscreen in Microsoft Excel, they may not be ideal when printing the sheets. The formatting applied to improve usability can hinder readability on printed copies. Luckily, it’s straightforward to print a color sheet in black and white. In this article, I’ll demonstrate how to manually print in black and white and also provide a VBA procedure for automation.

How to print Excel sheets in black and white easily

Manually printing in black and white in Excel:

1. Click on the “Page Layout” tab.
2. In the “Page Setup” group, click on the dialog launcher.
3. In the resulting dialog box, go to the “Sheet” tab.
4. Under the “Print” section, check the “Black and White” option (Figure B).
5. Click “OK.”
   

   How to print Excel sheets in black and white easily

To see the results of this setting, click on the “Print” option in the File tab (Backstage area). As shown in Figure C, no background or font colors will be sent to the printer, despite what is displayed on the screen.

If you always want to print in black and white and there are no other sheets in the file to consider, you can save the workbook with this setting checked, and you’re done. However, there may be situations where more flexibility is needed.

How to print Excel sheets in black and white easily

Automating black and white printing in Excel:

If your workbook has multiple sheets with different printing requirements, you can use a simple Visual Basic Editor (VBE) procedure to print only a specific sheet (e.g., Sheet1) in black and white. Follow these steps:

1. If you’re using a ribbon version of Excel, save your workbook as a macro-enabled file (.xlsm).
2. Press “Alt+F11” to open the VBE.
3. Choose “ThisWorkbook” in the Project Explorer (if not visible, press “Ctrl+R” or select “Project Explorer” from the “View” menu).
4. In the ThisWorkbook module, enter the code provided in Listings A and B (see below).
   

   How to print Excel sheets in black and white easily

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

End Sub

Manually enter the code in the VBE or copy it from a text editor after copying it from this web page. Trying to directly copy from this page may introduce unseen web characters that the VBE cannot interpret.

Return to Excel and print the sheet. Print Preview may still show the colors, but don’t worry; it should print in black and white. If your printer is not easily accessible, print to a PDF file and open it to check the output.

The macro will utilize the Black and White option set earlier through the interface. Saving the workbook will retain this setting. The BeforeSave procedure disables the setting when saving the workbook so that users can save their work without affecting the functionality of the first procedure. The macro may seem redundant at this point, but it becomes valuable when dealing with more complex situations where both black and white and color printing are required.

Stay tuned for a future article where I’ll demonstrate how to handle sheets with a need for both black and white and color printing, building upon the concepts covered in this article.