How to Center a Worksheet Horizontally and Vertically in Excel

puneet-gogia-excel-champs

- Written by Puneet

Let’s say you’re working in an office and need to prepare an Excel report to present at a meeting. Your report includes data about monthly sales figures, and you want it to look neat and professional when printed.

To ensure it looks right on the page, you center the worksheet content horizontally and vertically. This way, when your colleagues or boss see the printed sheets, the information is visually balanced and easily read.

page-setup-dialog-box

When you open a worksheet in Excel, you can enter data from left to right. A worksheet is aligned left horizontally and top vertically. But you can align a worksheet data at the center horizontally and in the middle vertically.

Steps to Make a Worksheet Horizontally at the Center

  1. First, go to the page layout tab and click on the margin option drop-down.
    click-margin-option-dropdown
  2. Now from the margins, click on the custom margins option. This opens the page set-up dialog box.
    click-custom-margin-options
  3. After that, from the page set-up dialog box, tick mark center on page options (Horizontally and Vertically). The moment you do this, you’ll see, it shows you that data from the worksheet is now horizontally at the center and vertically in the middle.
    tickmark-horizontally-and-vertically-options
  4. In the end, click OK to save these settings.

Note: If you set the print area and then change the settings to Horizontally and Vertically at Center, Excel will print using both settings.

This will not change the data you have in the worksheet, but when you make a printout (you can see it in the print preview section), it will be horizontally at the center and vertically in the middle.

And if you are using Excel for Mac:

  • Go to the Layout tab, click on Margins, and choose Custom Margins from the dropdown.
  • In the Page Setup window, go to the Center on page options under the Margins tab.
  • Check the box for Horizontally to center your worksheet content on the page horizontally. Press OK to save.

Using the Print Preview Option to Make a Worksheet Horizontally at the Center

The same option can be changed from the print preview section.

  1. Go to the File Tab and click on the Print option.
  2. After that, click on the “Page Setup”.
  3. Now, click on the Margin Tab.
  4. Next, tickmark the checkboxes horizontally and vertically.
  5. In the end, click OK to save settings.
make-worksheet-horizontally

It’s the same option we used in the first method, but this is a different way to open it.

Keyboard Shortcut

If you want to make a worksheet horizontally at the center, you use a keyboard shortcut to open the page setup and then apply the settings:

AltPSPM

When you press this shortcut key, it opens the page setup dialog box and the margin tab. From there, you can apply the setting to make your worksheet horizontally at the center and vertically in the middle.

As I said earlier when you apply these settings, it will not change the data in the worksheet but the alignment you have while printing it. You see this setting in the print preview as well.

VBA to Print Worksheet Horizontally at the Center

Use the following code to center a worksheet horizontally and vertically in Excel. This code adjusts the page setup settings for the active worksheet to center the sheet when it is printed.

Sub CenterWorksheet()
    With ActiveSheet.PageSetup
        ' Set horizontal and vertical centering
        .CenterHorizontally = True
        .CenterVertically = True
    End With
End Sub

You can run this macro by pressing ALT + F8, selecting CenterWorksheet, and clicking Run. This will adjust the settings for whichever sheet is active in your workbook. Let’s say you want to apply this setting to multiple sheets or a different specific sheet.

In that case, you can modify the ActiveSheet reference to another sheet like Worksheets(“SheetName”) where “SheetName” is the name of your target sheet.

And if you want a code that automatically applies these settings to every worksheet in a workbook, you can use this code:

Sub CenterAllWorksheets()
    Dim ws As Worksheet
    ' Loop through each worksheet in the workbook
    For Each ws In ThisWorkbook.Worksheets
        With ws.PageSetup
            .CenterHorizontally = True
            .CenterVertically = True
        End With
    Next ws
End Sub
Last Updated: May 13, 2024