Page Breaks in Excel

Turn them off

When you set your print area or use the Page Break Preview View, Excel will show you the page breaks on the grid. If this annoys or distracts you, here is how to remove them.

It’s a one-line macro that turns off the page breaks in the current sheet.

Sub TurnOffPageBreaks()
 
ActiveSheet.DisplayPageBreaks = False
 
End Sub

If you are new to macros then this blog post can take you through how to use them.

The above code can be copied and pasted into the code window.

If you want to turn page breaks off for all the sheets in the file use the code below.

Sub TurnOffAllPageBreaks()
 
Dim ws
 
For Each ws In Worksheets
 
  ws.DisplayPageBreaks = False
 
Next ws
 
End Sub

 

I hope these code snippets are useful.

One Minute to Excel #16 – Create a useful macro

Wow - a replacement for Merge cells in less than a minute

The Merge cells format has lots of issues. It can crash macros and stop you copying and pasting.

In less than a minute you can use a macro to solve the problem.

Center Across Selection takes a few mouse click to apply but it doesn’t have the issues Merge cells does.

A macro can do it much quicker – see how in this quick video.

One Minute to Excel #15 – Replace Colours

It can be done

Replacing colours manually can be a tedious task.

Did you know you can use Excel’s built-in Find & Replace to do the job for you?

See how in this short video.

One Minute to Excel #14 – Flash Fill

Excel's Super Hero

Joining names; extracting codes or converting dates is usually done with formulas, but there is now a formula-free solution called Flash Fill.

See how you can do four transformations in less than a minute.

Missing Chart Data in Excel

Another macro to the rescue

The default setting for charts in Excel is to hide the data on the chart if it is hidden on the sheet. I forgot that recently when I created a few charts using a workings area to hold the chart data. I later hid the workings with column grouping. Oops – when you hide the data in the charts go blank.

One Minute to Excel #13 – Create and Use a Check Box

Tick it to the next level

A check box is an easy interface to create and use.

See how to add one to a sheet and use it in a calculation.

One Minute to Excel #12 – Hide Cell Entries

It is a special format

You can’t hide a cell, but you can stop the cell value from displaying on the sheet.

It involves a custom number format.

One Minute to Excel #11 – Add % to a Pivot Table

It is not intuitive

Often people perform calculations off to the right of Pivot Tables to calculate percentages.

In this short video I show you those calculations can be done inside the Pivot Table itself.

The solution is not intuitive, but it is easy.

This example builds upon the previous One Minute to Excel post.

One Minute to Excel #10 – Create a Pivot Table

It is easy

Note sure why, but Pivot Tables are often seen a “hard” or “advanced”.

In the short video we see how easy they are.

Oops – I go over my one minute time limit by a few seconds because I format the Pivot Table as well.

Save and Close in Excel

Let's create a macro

I started using Excel in the late 80’s on a Mac. It had a Save and Close button. When I discovered VBA in Excel on the PC, the very first macro I ever made was save and close.

I thought this would be a good example to take you through creating a macro from scratch and sharing a technique to make it easy to use.

One Minute to Excel #9 – Drop down list in a cell

Three techniques

This short video covers different ways to insert a drop down list into a cell.

I go over my one minute time limit by a couple of seconds, but I do cover three techniques.

Excel’s Formatted Tables [Video]

Free webinar recording

Learn about Excel’s powerful Table feature that treats tables like little databases.

I ran this free live webinar in March 2021.

You can access the pdf manual and example file by clicking the button below the video.

Download Materials

One Minute to Excel #8 – Incrementing a Long List of Dates

It is easy when you know how

In this short video I cover how to increment dates in long ranges.

It uses a little know dialog.

One Minute to Excel #7 – Copy Date down a Long Range

Quick and easy technique

In this short video I cover how to insert dates in long ranges.

Its simple and quick.

One Minute to Excel #6 – Add Subtotals Automatically

Built-in feature

In this short video I cover how to insert automated subtotals.

Its a built-in feature, that is easy to use.