How to print worksheet without fill color?
In Excel, it is very common to format cell data using various colors in order to highlight important information, categorize values, or simply make large datasets easier to navigate. However, when you send your worksheet to the printer, all the background fill colors, colored fonts, and other formatting are printed as they appear on the screen, as shown below:
Printing with these colors can consume a significant amount of ink or toner, which may not be ideal—especially if you only need a simple black and white hard copy for review or distribution. To address this, Excel offers multiple approaches to produce a printed version of your worksheet without the fill colors. These solutions can help you save ink, increase readability, and ensure that your reports have a professional, clutter-free appearance. In this article, we will introduce several practical ways to print your worksheet without fill color, allowing you to choose the method that best fits your needs.
Table of Contents
- Print worksheet without fill color in Excel
- VBA Code - Temporarily Remove Fill Colors Before Printing
Print worksheet without fill color in Excel
Excel’s built-in print settings include an option that allows you to ignore fill colors and print all content in black and white. This is a straightforward approach that works well for most worksheets, especially when you need to quickly switch between colored and black-and-white prints without changing your actual data formatting. However, some limitations exist: the “Black and white” print setting applies to the entire sheet, so you can’t selectively keep color formatting for a specific area. This is suitable for reports, analysis, or financial documents where you want to minimize ink usage and keep printouts simple and easy to read.
How to use the Black and White print option:
1. Activate your worksheet that you want to print.
2. Then click Page Layout tab in the ribbon, and click the small Page Setup button in the lower-right corner of the Page Setup group. This will open the Page Setup dialog box. (See screenshot below for reference.)
3. In the Page Setup dialog, go to the Sheet tab, then check the box next to Black and white under the Print section. (See screenshot below.) This option instructs Excel to ignore all cell colors—including background fills, font colors, and border colors—and print the worksheet strictly in black and white.
4. (Optional but recommended) Click Print Preview in the Page Setup dialog to see how your worksheet will look when printed.
5. Click OK to confirm the settings, then proceed to print your worksheet as usual. All colored cell backgrounds and some color-dependent features (such as conditional formatting fills) will be omitted from the printout. Only the text, numbers, borders, and other non-color formatting will be retained.
Note: This method is quick and easy for most standard printing needs. However, if you need more granular control (such as removing fill color from only some areas, or making certain areas appear shaded in grayscale instead), consider using other solutions below.
VBA Code - Temporarily Remove Fill Colors Before Printing
Sometimes, you might want to print your worksheet without any fill colors, but the built-in "Black and white" option may not fit your needs, especially if you need to apply this setting to a specific area or want to ensure certain shapes or chart elements are also printed without fill. In these cases, you can use a VBA macro to temporarily remove all cell fill colors before printing, then restore them right after printing. This offers a high level of customization and is particularly useful for advanced users working with complex worksheets or automating recurring print tasks.
Advantages: This solution offers very detailed control because you can choose which areas of the sheet to affect, and you can further customize the code to work with specific sheet or workbook features.
Disadvantages: Requires enabling macros, and users should be comfortable working in the VBA editor. Extensive use of macros may not be suitable for shared workbooks or in highly regulated environments.
1. Open your workbook and press Alt + F11 to open the Microsoft Visual Basic for Applications window. In the VBA window, click Insert > Module to create a new module. Then, paste the following code into the module:
Sub PrintWithoutFillColor()
Dim rng As Range
Dim arrColors() As Variant
Dim i As Long, j As Long
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rng = Application.InputBox("Select the range to print (leave blank for entire sheet):", xTitleId, Selection.Address, Type:=8)
If rng Is Nothing Then
Set rng = ActiveSheet.UsedRange
End If
' Store the current fill colors
ReDim arrColors(1 To rng.Rows.Count, 1 To rng.Columns.Count)
For i = 1 To rng.Rows.Count
For j = 1 To rng.Columns.Count
arrColors(i, j) = rng.Cells(i, j).Interior.Color
rng.Cells(i, j).Interior.ColorIndex = xlNone
Next j
Next i
' Show Print Preview
ActiveSheet.PrintPreview
' Restore fill colors
For i = 1 To rng.Rows.Count
For j = 1 To rng.Columns.Count
rng.Cells(i, j).Interior.Color = arrColors(i, j)
Next j
Next i
End Sub2 After pasting, close the VBA editor. To run the code, go back to Excel, press Alt + F8 to open the "Macro" dialog, select PrintWithoutFillColor, and click Run. When prompted, select the target range to print without fill color. If you click OK without selecting a range, the entire used range on the sheet will be included. The macro will remove fill colors from the chosen area, open Print Preview, and then restore the original colors afterwards. This minimizes risk of accidental data loss or unwanted format changes.
Tips: If running this macro on very large ranges, it may take a few seconds. If you have charts or graphical objects that also need fill colors removed, further VBA customization will be required. Always save your work before running or editing macros.
Troubleshooting & Tips:
- If you find colors are still appearing in your printout, ensure your printer settings (outside Excel) are not overriding the black-and-white or monochrome choice.
- Always preview your document before final printing to avoid wasting paper or ink.
- If you regularly need to print in black and white, consider saving a duplicate of your workbook for print purposes.
Summary: Depending on your needs, you can print without fill color using Excel’s built-in black-and-white print option (best for the whole worksheet), or using VBA for automated and temporary formatting changes. Choose the method that fits your scenario to balance ink savings, ease of use, and control over your print layout.
Related articles:
How to print multiple workbooks in Excel?
How to quickly print current page in Excel?
How to print long column on one page in Excel?
How to print title (top row) on every page repeatedly in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in