How to add background color to headers or footers in Excel sheet while printing?
When working in Excel, adding headers or footers enhances the professional appearance of your printed documents by including important information like page numbers, file names, or company logos. While Excel’s built-in features allow you to insert text and images into headers and footers that will appear during printing, you may find that you want to highlight these sections with a background color for improved readability or branding purposes. However, Excel currently does not provide a direct option to apply background colors to the header or footer areas in the printout. This can be frustrating if you wish to visually separate these sections from your data when printing. Below, you’ll find some practical workarounds to mimic a colored header or footer in your Excel printouts, catering to different needs and technical comfort levels.
Add background color to header or footer by Fill color
Add background color to header and footer by VBA
Add background color to header or footer by Fill color
This approach involves formatting worksheet cells to visually imitate a colored header or footer. Since actual headers and footers created via Page Layout do not support background color, formatting the top and/or bottom rows provides an effective alternative. This method is best when your main goal is to enhance the visual separation or branding of printed sheets, and when your header/footer content can be shown in worksheet rows rather than true print headers/footers.
Limitations of this method include the inability to use dynamic header/footer fields (like page numbers) and the need to manually repeat these steps for each sheet.
1. Click Page Layout > Page Setup.
Before proceeding, it’s important to set all margins to 0 in order to maximize usable worksheet space and ensure the colored areas are positioned flush at the top and/or bottom of each printed page. This helps create the illusion of a true header or footer background when the document is printed.
2. In the Page Setup dialog, go to the Margins tab and type 0 in the Top, Header, Bottom, and Footer textboxes. This ensures there is zero space reserved for Excel's built-in headers and footers, and positions your worksheet content at the very top and bottom of the printed page.
3. Click OK to apply the margin changes. Select the first row of your worksheet (this row will visually act as your header), then go to Home > Merge & Center to combine all the columns in this row into a single cell. This provides a large area for your header text and background color.
Tip: For documents with multiple columns, ensure you select and merge the correct range of cells to cover your entire data area.
4. With the merged cell selected, click the Fill Color button (paint bucket icon) in the Home tab and choose your desired background color from the palette. This will add a visible color band at the top of your sheet, imitating a header background.
5. If you also wish to add a colored footer, scroll to the last row of your print range, select it, and repeat the merging and fill color steps to create a colored area at the bottom of the page.
For best results, consider adding your custom header or footer text directly into these colored and merged cells. You may adjust font size, alignment, or formatting as needed to match your printing requirements. Keep in mind that this method defines the header and footer only on the visible worksheet area. If your document spans multiple pages, you might need to repeat these formatting steps for each printed page manually, or use the "Rows to repeat at top" feature in Page Setup for headers only.
Now, when you go to File > Print or press Ctrl + P, you should see a colored band at the top (and/or bottom) of the preview page, which serves as a simulated background for your header or footer.
If the color does not appear as intended in Print Preview, double-check that your printer settings and worksheet range align, and verify that the print area covers your formatted rows.
Add background color to header and footer by VBA
For more complex worksheets, or when dealing with multiple pages and wanting a more automated way to create colored header and footer areas on every printed page, you can use a VBA macro to quickly add these effects throughout your sheet. This VBA solution adds pre-defined colored rows at the start and end of each page range, simulating the look of colored headers and footers in the printout.
This approach is particularly suitable for larger worksheets, legacy reports, or situations where uniform styling is needed across many pages. However, it requires enabling macro functionality and some experience with the VBA interface. Note that this still does not affect the actual Excel header/footer but works by modifying the worksheet content directly for print simulation.
1. Press Alt + F11 to open the Microsoft Visual Basic for Applications editor window. If this is your first time using VBA, you may need to enable the Developer tab first.
2. In the VBA window, click Insert > Module. In the code area of the new module, copy and paste the following code:
VBA: Add background color to header and footer
Sub FakeHeaderFooter()
'UpdatebyExtendoffice20180628 Dim I, J As Long Dim xRg As Range Dim xRow, xCol As Long Dim xRows, xCols As Long Dim xDivRow, xDivCol As Long Dim xTopArr, xButtArr As Variant Dim PageSize1, PageSize2 As Integer On Error Resume Next xTopArr = Array("Top Left", "", "", "Top Center", "", "", "", "")
xButtArr = Array("Bottom Left", "", "", "Bottom Center", "", "", "", "")
PageSize1 =46 PageSize2 =8 With ActiveSheet.PageSetup .TopMargin = Application.InchesToPoints(0)
.BottomMargin = Application.InchesToPoints(0)
.HeaderMargin = Application.InchesToPoints(0)
.FooterMargin = Application.InchesToPoints(0)
.BlackAndWhite = False End With Set xRg = ActiveSheet.UsedRange If xRg Is Nothing Then Exit Sub xRows = xRg(xRg.Count).Row xCols = xRg(xRg.Count).Column If xRows <46 Then xRows =46 If xCol <8 Then xCol =8 xDivRow = Int(xRows / PageSize1)
xDivCol = Int(xCols / PageSize2)
If ((xRows Mod PageSize1) >0) And (xDivRow <>0) Then xDivRow = xDivRow +1 If ((xCols Mod PageSize2) >0) And (xDivCol <>0) Then xDivCol = xDivCol +1 If xDivRow =0 Then xDivRow =1 If xDivCol =0 Then xDivCol =1 Set xRg = Range("A1").Resize(xDivRow * PageSize1, xDivCol * PageSize2)
xRow =1 xCol =1 Cells.PageBreak = xlPageBreakNone For I =1 To xDivRow * PageSize1 Step PageSize1 +1 For J =1 To xDivCol * PageSize2 Step PageSize2 Cells(I, J).Resize(1, PageSize2) = xTopArr Cells(I, J).Resize(1, PageSize2).Interior.ColorIndex =34 Cells(I + PageSize1, J).Resize(1, PageSize2) = xButtArr Cells(I + PageSize1, J).Resize(1, PageSize2).Interior.ColorIndex =34 Rows(I + PageSize1 +1).PageBreak = xlManual Columns(J + PageSize2).PageBreak = xlManual Next NextEnd Sub
3. Press F5 or click the Run button in the VBA toolbar to execute the code. The macro will automatically insert colored rows at the top and bottom of each page range as determined by the set PageSize1 (number of rows per page) and PageSize2 (number of columns per page). The first and47th row on each "page" will be filled with the specified color and predefined text, which together will display as a "fake" header and footer when you go to print preview or print your worksheet.
You can further customize this macro to suit your requirements. For example, to change the header or footer content, adjust the text within these lines:
xTopArr = Array("Top Left", "", "", "Top Center", "", "", "", "")
xButtArr = Array("Bottom Left", "", "", "Bottom Center", "", "", "", "")
If you’d like a different background color, modify the ColorIndex value here:
Cells(I, J).Resize(1, PageSize2).Interior.ColorIndex =34
Cells(I + PageSize1, J).Resize(1, PageSize2).Interior.ColorIndex =34
The macro uses standard Excel color indexes. To preview different colors, consult Microsoft’s ColorIndex reference or use Excel’s color picker to identify values that match your brand or design preferences.
Note: Save your work before running VBA macros, as actions cannot be undone with the standard Undo function. Additionally, because this macro alters worksheet content, confirm your print area is correct and adjust the PageSize1 and PageSize2 parameters for your specific paper and layout needs. This method will add extra rows and may shift existing data down, so review your layout after running the macro.
If you encounter issues with colored areas not displaying as expected when printing (such as color missing or cutting off), verify that background color printing is enabled in your printer’s advanced settings, and ensure your worksheet's print area is properly set. Both the fill color and VBA approaches require caution regarding merged cells and print range settings, as improper setup may result in misalignment or partial coloring.
Choose the solution that best fits your Excel version, page layout, complexity of your header/footer needs, and whether automation or design control is more important for you. Header/footer colorization works best for presentations, cover pages, or branded printouts when the actual header/footer fields are not required to update dynamically across pages.
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