Automatically set a border on the bottom line of a page break when printing in Excel (Conditional Formatting)

Page updated :

Prerequisites

First of all, there are prerequisites for what to do. Make sure that the following conditions are true:

  • The height of all lines must match exactly

Steps

Check the number of printed rows per page, such as print preview or page break preview (if there is a page header, refer to the second page or later because the number of rows is different on the first page and the second page or later). To check, try to display the print preview from the file menu.

印刷プレビュー

When you return to cell view, the range of one page is displayed as if you were dotted. In this example, there is a page 59 line without a header.

印刷範囲

Select the A1 cell.

A1セル選択

Select the Home menu tab and select Manage Rules from Conditional Format.

ルールの選択

Click New Rule.

新規ルール

Select Use formulas to determine which cells to format from the rule.

数式を使用して、書式設定するセルを決定

In the formula field, enter =MOD(ROW()-<Header Rows>,<Number of Rows per Page>)=0". (<> is set to any number)

数式

Click the Format button at the bottom right.

書式

Sets the underline that you want to appear when printing from the Borders tab.

下線

Apply the rule to the entire print area. For example, =A1:<Cell >in the lower left corner of the sheet. (<> is set to any value)

適用先

If you look at the print preview, you can see that a border appears at the bottom of each page.

印刷プレビュー