When you change the margins, you can fit more data on each page, reducing the number of pages needed. This can save paper and make the workbook easier to read. If you want to print a paper report, you have the option to change the page margins according to your need.
You can change these margins as per your requirement. In this tutorial, we will look at all the different options and methods that you have in Excel to change the page margins.
Default Margins in Excel
By default, the margins that you have are 1.78 cm for the left and right, and 1.91 for the bottom and top. These settings work well for most standard printing needs, ensuring your data is centered on the page and looks neat.
Steps to Change Page Margin in Excel
In Excel, changing margins doesn’t require you to follow any complex steps. There’s an option available on the ribbon that you can use to enter the values you want to set.
- Click on the “Page Layout” tab on the ribbon. In the Page Layout tab, go to the “Margins” button in the Page Setup group.
- You can select from predefined options like Normal, Wide, or Narrow. To create a custom margin, click “Custom Margins” at the bottom of the drop-down.
- In the Page Setup dialog box, enter your margins for the top, bottom, left, and right. Click “OK” to apply the changes.
- In the end, make sure to go to “File” > “Print” or use the print preview keyboard shortcut to see how your new margins look in the print preview.
There are three major pre-defined settings you can use: Normal (Default), Wide, and Narrow.
Understanding Custom Page Margins in Excel
As I Said, you can also use custom page margin values. When you margin drop-down from the page layout, click on the “Custom Margin” option, and open the dialog box. You can also use the keyboard shortcut, Alt ⇢ P ⇢ M ⇢ A.
In the dialog box, you can change the page margin values and set a new one.
You can also change the values for the header and footers and make content at the center vertically and horizontally.
Adjust Margins from Print Preview Window
When you open the print preview, you have the option there also to change the margins. In the settings section, scroll down to the bottom and click on the margin drop-down.
When you click on it, you will have the pre-defined settings, and custom margin option, and you also select the last used setting.
This is the same list of options that you have in the page layout tab on the ribbon.
Show Margins and Change them with Mouse
You can also use the “Show Margin” option in the print preview, to change margins with your mouse cursor. On the bottom right of the print preview window, there’s a tiny button, click it to activate.
The moment you click on it, it gives you a preview of all the margins on the page, and you change it with the mouse cursor.
When you have multiple worksheets in your workbook, and you want to use a custom margin value for the print, well, you need to change it for each sheet.
VBA Code to Change Margin for All the Worksheets
When you change margins, the changes apply only to the workbook or worksheet you are working on, not all the workbooks.
Each workbook has its own margin settings. If you want the same margin settings for multiple workbooks or worksheets, you’ll need to adjust the margins for all one by one.
Sub SetUniformMargins()
Dim ws As Worksheet
Dim topMargin As Double
Dim bottomMargin As Double
Dim leftMargin As Double
Dim rightMargin As Double
Dim headerMargin As Double
Dim footerMargin As Double
' Define your margin sizes in points (1 inch = 72 points)
topMargin = Application.InchesToPoints(1) ' 1 inch
bottomMargin = Application.InchesToPoints(1) ' 1 inch
leftMargin = Application.InchesToPoints(0.75) ' 0.75 inch
rightMargin = Application.InchesToPoints(0.75) ' 0.75 inch
headerMargin = Application.InchesToPoints(0.5) ' 0.5 inch
footerMargin = Application.InchesToPoints(0.5) ' 0.5 inch
' Loop through each worksheet in the workbook
For Each ws In ThisWorkbook.Worksheets
With ws.PageSetup
.topMargin = topMargin
.bottomMargin = bottomMargin
.leftMargin = leftMargin
.rightMargin = rightMargin
.headerMargin = headerMargin
.footerMargin = footerMargin
End With
Next ws
MsgBox "Now all the wokrsheets have same margin setting."
End SubThe above below code will change the top, bottom, left, right, header, and footer margins for all worksheets in the active workbook. You can adjust the margin values as needed by changing the values assigned to topMargin, bottomMargin, leftMargin, rightMargin, headerMargin, and footerMargin.
To run this code, press Alt + F11 to open the VBA editor, insert a new module, and paste the code into the module. Then run the SetUniformMargins macro.
Important Points to Consider
- Check the print preview after changing margins to make sure everything looks good.
- Adjust header and footer margins separately, especially if you have important information.
- Margins might need adjustment if you change the page orientation (portrait or landscape).
- Combine margin change with the “Fit to Page” option for the best print layout.