5 Ways to Print Only Filled Columns in Excel

Excel is a powerhouse for data manipulation and analysis, yet one of the common challenges users face is printing spreadsheets that contain a mix of filled and empty columns. Printing entire worksheets can lead to pages filled with unnecessary blank space, which not only wastes paper but also makes the information harder to digest. In this post, we'll delve into five effective methods to print only the filled columns in Excel, ensuring your printouts are concise, professional, and data-focused.

1. Using Excel’s Built-In Feature: Hide Unused Columns

The simplest approach to printing only filled columns is to manually hide those columns that are empty.

• Select the column(s) you wish to hide by clicking on the column headers.
• Right-click and choose Hide from the context menu or press Ctrl+0 for Windows or Cmd+0 for Mac.
• Go to File > Print Preview to see how your worksheet will look when printed.

📝 Note: This method is straightforward but can become tedious if you have numerous columns to hide. Also, remember to unhide them when you need the full dataset.

2. Filtering Columns

Excel’s AutoFilter feature can help you display only the filled columns temporarily for printing:

• Highlight your data range or the entire worksheet by pressing Ctrl+A.
• Go to the Data tab and click Filter. This will enable drop-down menus in your headers.
• In the filter menu for each column, uncheck the (Blanks) option to filter out empty cells.
• After filtering, proceed to print your worksheet.

3. Using VBA for Automated Column Printing

For those familiar with VBA, automating the process can save time. Here’s a basic macro to print only filled columns:

Sub PrintFilledColumns()
    Dim ws As Worksheet
    Dim lastCol As Long, i As Long, col As Long
    Set ws = ThisWorkbook.ActiveSheet
    lastCol = ws.Cells(1, Columns.Count).End(xlToLeft).Column

For col = lastCol To 1 Step -1
    If WorksheetFunction.CountA(ws.Columns(col)) = 0 Then
        ws.Columns(col).EntireColumn.Hidden = True
    Else
        col = 1 'stop once we reach a filled column
    End If
Next col
ws.PrintOut
ws.Columns.Hidden = False


End Sub

💡 Note: This macro will temporarily hide columns that are empty, print the sheet, and then unhide all columns. Ensure you run this macro only when you need to print.

4. Creating a Print Area Manually

You can define a print area to include only the necessary columns:

• Select the range that includes only the filled columns.
• Go to Page Layout tab and click Print Area > Set Print Area.
• When ready to print, go to File > Print. Excel will now only print the defined area.

📝 Note: Remember to clear the print area after you're done printing to avoid confusion in future print jobs.

5. Custom Excel Template

For repetitive tasks, setting up a custom template with pre-configured print settings can streamline the process:

• Create a new Excel workbook with your data.
• Hide or filter out the unwanted columns using any of the methods above.
• Save your workbook as an Excel Template (.xltx or .xltm if you’re using VBA).
• When you need to print, just open this template and update it with current data.

In summary, these five methods provide different ways to tackle the common problem of printing only filled columns in Excel, each with its own advantages. Whether you prefer manual adjustments, automated solutions, or pre-configured templates, Excel offers flexibility to fit your workflow. By implementing these techniques, you not only save paper but also present your data in a more organized and visually appealing manner, making your reports more digestible for readers.

What’s the quickest way to print only filled columns in Excel?

+

The fastest method for many is to use the Filter feature. After filtering out blank cells, your print preview will show only the data-filled columns.

Can I automate the process of hiding empty columns?

+

Yes, by using VBA scripts, you can automate the process. The macro provided in the article is one example of how to do this.

Is it possible to set up an Excel file to automatically print only filled columns without VBA?

+

While you can’t make Excel automatically hide or print columns without VBA, you can set up a template with a predefined print area or filter settings to simplify the process.