Getting Rid of Ugly Page Break Lines

Have you ever sent an Excel report out to your audience just to find out that it prints on 20 pages? To avoid that embarrassment, I routinely adjust the page breaks on my reports so that users don’t have to kill 10 trees each time they print.

Unfortunately, each time I adjust page breaks, Excel tries to do me a favor and adds an annoying line to my report (supposedly to remind me of where my page breaks are).

Here’s an example. Let’s adjust the page breaks on this sheet.

 

All I’m doing is clicking Page Break Preview, and then dragging the blue box to where I want my page break.

 

Now look at weird line Excel draws. Annoying!

 

There are two ways to get rid of these page breaks.

You can go to Excel Options. Under the Advanced tab, there is a section called “Display options for this worksheet”.

There, you will find a checkbox next to Show page breaks. Uncheck that and press the OK button.

 

If you’re a hot-shot VBA kind of person, you can use the VBE Immediate window to remove the page breaks.

Simply enter Activesheet.DisplayPageBreaks = False then press enter.

 

Unfortunately, both of these techniques solves the Page Break issue for only that one worksheet.

There is no setting (that I know of) that inhibits Page Breaks for the entire workbook.

New DataPig Dashboard Tools Free this Week

I’ve completed updating my Dashboard Tools Add-in to add a few new useful tidbits.

With the new features, you can create nifty infographics like this one:

 

Here are a few highlights of the new Dashboard Tools Add-in:

New InfoGraphic Shapes

I added new infographic shapes that feel a bit more modern.

 

New Map Shapes

I added a bunch of new map shapes to the Infographics gallery.

These maps have been designed so that each State/Province can be formatted separately.

Simply click the State/Province you are interested in and then format as needed.

To format multiple shapes within the group, you can click on each individual shape while holding down the CTRL key.

For example, to recolor Texas, click on it and format as needed.


You also have the option of pulling a specific State/Province out of the map.

Click the State/Province, then press Ctrl+X on your keyboard.

Click your worksheet and press Ctrl+V to paste it separately.

 

New Vector Icons

I added a new group of shapes I call Vector Icons.

When you click the Vector Icons button, you will see a gallery of components that can be used as icons in your dashboards.

The neat thing is that these are formatted as drawing objects.

This means you can size, add shadows, add transparency, and generally use these icon components just as you would any other shapes you add to Excel.

In fact, you can think of these as another set of shapes you can use in Excel.

In celebration of me getting off my lazy ass and actually getting this add-in updated, I’m giving it away FREE until Saturday.

You have until midnight U.S. Central time on May 7th.

The offer to download this Add-in has expired.

But you can still get it for low price of $14.95.

Feel free to visit the Dashboard Tools product site to get the Add-In.

Power Pivot and Power Query for Dummies

Need to get a quick ramp-up on Power Pivot and Power Query? Good news…A new book just dropped on Amazon.

Learn how to:

  • Create your own database right in Excel with Power Pivot
  • Make pivot tables that use multiple data sources
  • Scrape websites and import hard-to-reach data with Power Query
  • Simplify and automate data cleanup and transformation
  • Create your own powerful formulas and functions to go beyond simple data imports

Now available on Amazon:

“Discover how Power Pivot and Power Query not only allow you to save time and simplify your processes, but also enable you to substantially enhance your data analysis and reporting capabilities. Rev up your reporting — fire up Power Pivot, build table relationships, and create robust pivot table reporting using multiple data sources Formulate a plan — find out how to add formulas to Power Pivot reports and get the know-how to share your BI solutions with others When in doubt — get the lowdown on Power Query, from choosing where to load Query results to refreshing and managing queries–and everything in between Can’t we all just get along? — discover how to make Queries work together, build your own Query functions, and make marvelous magic with M Query language Get what you need — transform your way to better data by adding Query filters, filling in blank spaces and empty values, splitting and merging columns, and transposing and pivoting data”

Editing Shape Points to Create Custom Graphics

I’m working on updating my Dashboard Tools Add-in to add more modern looking infographics and maps. I’ve discovered a neat feature called Edit Points.

When working with a shape, you can right-click and select Edit Points

This places little dots all around the shape. You can click and drag these dots to create a custom shape.

 

Here, I took my newly created shape and combined it with a circle with a wide border and no fill.

What can I do with this?

Glad you asked.

I made little dashboard gizmos to use as infographic shapes.

It’s Friday…put down your serious work and mess around with some shapes today!

If you’re interested, you can download these gizmos here.

 

 

 

Entering More than 31 Characters in a Worksheet Tab Name

When giving a worksheet tab a name, most people think that you can’t enter more than 31 characters.

But guess what…you can!

In this post, I’ll show you how to enter more than 31 characters in a worksheet tab name.

 

1. Start a new workbook

2. Double-click on the tab you want to rename

 

3. Enter “More than 31 Characters” without the quotes (as demonstrated below).

 

And there you have it. You have successfully entered More than 31 Characters in a Worksheet Tab Name.

Oh and by the way….

April fools.

 

 

 

 

Understanding the MOD function

After weeks of struggling to get this blog upgraded to the latest version of WordPress, it’s finally up and going again. I’ve had to constantly engage in hand-to-hand combat with WordPress over the last few months. I think I’ve beat it into submission, but who knows what nonsense will pop up next. I’ve come very close to completely closing up shop and becoming a monk.

But enough complaining. Back to business.

Erin (One of my 12 fans) asked me to explain the MOD function. She says:

“I’ve see MOD used in one form or another and I just take for granted that it works. But what does it exactly do?”

 

The Basic Concept behind MOD

MOD is actually Excel’s name for a mathematical term – Modulo. A Modulo is the integer-based representation of the remaining number of units after performing a division operation.

Let’s use some Excel shape art to help illustrate.

Say that I have 8 pieces of bacon. If wake up only one of my kids for breakfast, he’ll get all eight pieces. There are no pieces of bacon remaining, so the modulo is zero. In Excel, you could write this as =MOD(8,1).


 

If I woke up two of my kids for breakfast, they would share the 8 pieces of bacon – each kid gets 4 pieces. Again, all eight pieces are evenly distributed so there are zero pieces of bacon remaining. In Excel, the formula =MOD(8,2) would indeed return 0.

 

Now let’s say I woke up three of my children, forcing them to share 8 pieces of bacon. In this case, I can’t evenly distribute the bacon. Two children could have three pieces each, but that leaves a modulo of two pieces for the last monster. Anyone who has more than one child knows that this scenario is the start of a damned fight. So no…I don’t think I’ll invite that that third child to breakfast. MOD(8,3) = 2

 

At the risk of beating the point to the ground, let’s do one more. Inviting five children to breakfast ensures that each child gets one piece of bacon, leaving a modulo of three pieces.

Those three pieces go to me. After all, I’m the one that cooked the stupid bacon.

 

Understanding the Math

Although you can use the pre-canned MOD function in Excel, it is useful to know the math behind MOD.

A commonly used alternative expression for getting to a modulo is:

INT(Numerator/Denominator)*Numerator-Denominator

So….

=INT(8/5)*(8-5)

Will give the same result as

=MOD(8,5)

Clever Uses for the MOD Function

I’ve see the MOD function is used in all kinds of clever ways over the years.

You’ll often see this inconspicuous function in formulas, conditional formatting, and even VBA.

Here is just a sampling of nifty things you can do with the MOD function.

Count cells that contain odd numbers

Calculate which date Easter falls on

Conditionally Format or highlight every Nth row

Calculate Total Time Worked across AM and PM hours

Calculate Payroll Penalty for Every 15 Minute Increment Late

Check if a value is a Prime Number

 

So there you go. I hope this quick view into the MOD function changed your life. Or at least gave you a craving for bacon.

Use Power Query to Get a List of Hidden and System Directories

So here’s a nifty Power Query trick that lets you grab a listing of all Directories and their attributes.

If you don’t know, Power Query lets you get a list of files from a folder.

 

You get a nice listing of all the files in the directory you specify.

Notice in the Formula bar, the function used for the Source query step is Folder.Files.

Try changing that the Folder.Contents function.

What you get is a similar table, but now there are Table entries in the Content column.

These entries are essentially Directories!

Click any of these tables to drill into the directory.

Click the Source step (in the Query Steps pane) to drill back up.

 

OK…That’s handy, but how do you get a list of Directories without all those Binary files?

Don’t try to filter on the Content column – that won’t work.

Scroll to the right a few columns, and you’ll see a field called Attributes. Click the Expand icon and place a check next to Directory.

This essentially brings in a field that tags each row with TRUE or FALSE.

This tag will be TRUE if the object in the Content column is a directory.

Filter on this Directory attribute for TRUE.

You end up with a clean list of Directories.

Here’s a sampling on the Directories in my Program Files folder.

 

Now, there are other useful attributes you can call out from that Attributes column: Hidden, Encrypted, System, etc.

Here, I’ve pulled out SYSTEM Directories under C:\Users\MyUserName.

I’m not saying this is the most useful trick in the world, but it sure is good for an hour of playing around your file system.

 

Before I sign off, I want to mention a new book – Excel Power Pivot and Power Query For Dummies.

Perfect for anyone who needs a clean and easy crash course on all the sweet goodness Power Pivot and Power Query have to offer.

The book hits Amazon in March, but you can pre-order now.

Can’t wait till March?

Check out a few other recommendations from the intrepid Chris Webb.

Four Must-Know Options if You’re Using Power Query

Over the last year, Microsoft has added a few options to Power Query that you should definitely embrace (if you’re working extensively with Power Query). These options alleviate some of the annoying quirks in Power Query. I’m especially grateful for the option to turn off that damned Type Detection feature.

In any case, here is my list of must-know options if you’re using Power Query.

Setting a Default Load Behavior

If you’re working heavily with Power Pivot as well as Power Query, chances are you load your Power Query queries to the Internal Data Model a majority of the time. If you find that you always load your queries to the Data Model, you can tweak the Power Query options to automatically do that. Open Power Query options and select, Data Load under Global, and then choose to specify a custom default load setting. This enables the options to Load to the worksheet by default or Load to the Data Model by default.

 

Preventing Automatic Data Type Changes

One of the more recent additions to Power Query is the ability to automatically detect data types, and proactively change data types. This “Type Detection” is most often applied when new data is introduced to the query.

For instance, the screenshot below shows the Query steps after importing a text file. Note the Changed Type step. This step was automatically performed by Power Query as part of its type detection feature.

 

Although Power Query does a decent job at guessing what data types should be used, data type changes that can cause unexpected issues – especially when you’re working with lots of numbers that are really identifiers or textual in nature. I frankly find the type detection feature annoying. If data types need to be changed, I’m more than capable of figuring that out myself. I personally would rather handle data type changes without the help from Power Query’s type detection feature. So…I happily turn this feature off. In the Power Query options, look for Data Load under Current Workbook, and then uncheck the option to automatically detect column types and headers for unstructured sources.

 

Disabling Privacy Settings to Improve Performance

The privacy level settings in Power Pivot are designed to protect organizational data as it gets combined with other sources. When you create a query that uses an external data source with an internal data source, Power Query stops the show to ask how you’d like to categorize the data privacy levels of each data source. Most of us deal solely with organizational data, so the privacy level settings do little more than slow down queries and cause confusion.

Fortunately, there is an option to ignore privacy levels. Select Privacy under Current Workbook, and then choose the option to ignore privacy levels.

 

Disabling Relationship Detection

When building a query and choosing Load to Data Model as the output, Power Query will, by default, attempt to detect relationships between queries and create those relationships within the internal Data Model. The relationships between queries are primarily driven by the defined query steps. For instance, if you were to merge two queries then load the result into the Data Model, a relationship is automatically created. In larger Data Models with a dozen or so tables, Power Query’s relationship detection can affect performance and increase the time it takes to load the Data Model. You can avoid this hassle and even gain a performance boost by disabling relationship detection.

Select Data Load under Current Workbook, and then uncheck the option to create relationships when adding loading to the Data Model.

Books, Old Man Skywalker, and Plagiarism

Hey there! Remember me? I’m back from a long 6 weeks of finishing up several book projects.

 

Feels weird to be back at the office. I’m getting a lot of perky people asking me how my holiday went. Mrs. Pig sent me this pic, which sums up my feelings about all that obligatory post-holiday banter.

 

Anyway, as I mentioned before, I’ve been pushing to get a load of books out the door.

Some are available now, while most will be available within the next few weeks.

 

Dick Kusleika and I updated our Access 2016 Bible.

I don’t know if it ever got to #1 in Amazon’s Database category (like Dick was pushing for), but I just checked and it #6.

 

Bill Jelen and I updated our Pivot Table Data Crunching book.

We added new content, including a new chapter on Power Query.

 

It looks like John Walkenbach’s career as Old-Man Skywalker has taken off.

So he’s passed a couple of his books down to Dick Kusleika and me.

We’ve taken over J-Walk’s Formulas and Power Programming books.

Excel 2016 Formulas was an easy update.

There were only five new functions added to Excel 2016, so not a ton of changes from last version of this book.

We both added a decent bit of new content to Power Programming.

 

I also did a couple of solo projects.

I pushed out the 3rd edition of Excel Dashboards and Reports for Dummies (new content peppered throughout most chapters).

 

Finally, I’m wrapping up a new title: Power Pivot & Power Query for Dummies.

This one is fun for the entire family; great for anyone just starting to explore the Power tools in Excel.

My book hits Amazon at the end of March, but if you can’t wait that long, I recommend picking up the excellent M is for (Data) Monkey.

Ken Puls and Miguel Escobar co-authored this book. It’s very well written and with lots of excellent easy to follow examples.

I asked Ken to email me an electronic copy of his manuscripts so I can use them for my book. He must have forgotten, because I haven’t received them yet.

 

Speaking of plagiarism…reminds me of two Stewart Francis jokes

“People call me a plagiarist…their words, not mine”

“Anyone who accuses me of stealing their jokes can kiss my black ass”

 

Ahhh….feels good to be back in the blog saddle. It’s going be a great 2016.

Happy New Year everyone.

Oh and Ken….send me that manuscript. I’ve got 4 chapters due by Monday.

Adding Dynamic Annotations for your Charts

Here’s a quick idea for anyone who needs to show annotations or comments on their charts. Tie your annotations to a check box so that users can interactively decide to show them or not.

showannotations
The annotations themselves are simply chart data labels that are showing Category names instead of values. These labels are tied to an invisible data series in the chart. This means that as your chart data changes, the annotations move with the other chart elements. The check box is a Form control that drives a simple IF formula. IF the check box is TRUE then annotation series gets plotted on the chart, else the annotations series is ignored.

This neat trick involves no VBA. It uses a few simple modeling techniques that have been around for years. In fact, this trick uses the same steps described in this post I pushed out back in 2009. Follow that post to see how it’s done.

Also, feel free to download the sample workbook to see it in action.