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.

Speed up a macro

Just saw an Excel newsletter post from Kevin Jones from https://www.dataautopros.com/about-us/

He found that turning off the VBA interactive setting can speed up some macros. I tested it on a one minute macro and it cut it down to 40 seconds.

Worth a try if you have a longer running macro. You can add it to your opening and closing routines.

Code to turn it off

Application.Interactive = False

And then turn it back on at the end of your code.

Application.Interactive = True

Thanks Kevin for sharing.

Related Posts

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.

Name box shortcut

Just found out you can double click the re-size control on the Name Box. This quickly shrinks the Name Box width.

I have no idea how long that feature has been there, but I have just found it. Woohoo!

Related Posts

 

Excel Power Query and Multiple Files [one hour video]

Includes detailed pdf manual

Getting data into Excel and in the correct layout and format is the important starting point for any Excel project.

Power Query can automate the importation process. It is now a vital skill to possess.

This is a follow up session to the Introduction session which you can view here.

This session focuses on importing multiple files from a folder. The session uses CSV and Excel files plus Excel tables.

This is a video of a free live training session I ran in May 2021 – over 100 people attended – please share this resource with friends and colleagues. No editing has been done to the video.

(Apologies I did have a coughing fit – I thought I had turned off the microphone but I hadn’t.)

Use the button below to download the pdf manual and example files so you can follow along.

We need to get the word out on this powerful Excel feature.

(Note: Power Query is also part of Power BI, the skills you learn also apply to that.)

Download Materials

Introduction To Excel Power Query [one hour video]

Includes detailed pdf manual

Getting data into Excel and in the correct layout and format is the important starting point for any Excel project.

Power Query can automate the importation process.

It is now a vital skill to possess.

This is a video of a free live training session I ran in May 2021 – over 100 people attended – please share this resource with friends and colleagues. No editing has been done to the video.

Use the button below to download the pdf manual and example files so you can follow along.

We need to get the word out on this powerful Excel feature.

(Note: Power Query is also part of Power BI, the skills you learn also apply to that.)

Download Materials

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.

The Proof is in the Plants (Penguin, 2021)

A great book that brings together all the science that supports a plant-based diet.

The book is easy to read and accessible with lots of good analogies.

Plant foods are great for you and the planet and you can read about the science that backs that up.

The book pushes a mostly plant-based diet. It doesn’t push vegan or vegetarian. Being mainly plant based is the aim. The more the better.

Highly recommend the book.

I follow the Plant Proof podcast.

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.

Open the Power Query Editor

If you have cell selected in the output table from a Power Query, you can press, in sequence (not held down) the following keys Alt P U E to open the Power Query Editor window.

Related Posts

 

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.