Bacon Bits

Here’s a quick tip for quickly wrapping all formulas in IFERROR without VBA.

1. Select the cells that contain the formulas you want wrapped in IFERROR.

2. Press F5 on your keyboard to activate Read more

Happy New Year everyone! I’m back from a few long months of moving into a new house. Now that I’m all settled in, I’d like to start this New Year with a bang.

By that, I mean a new Excel Contest!

The idea for this contest came from this YouTube video posted by John Michaloudis (MyExcelOnline). In his video, he builds a dashboard in 3 minutes, giving us an interesting way to see fundamental techniques in action. Neato!

The goal of this contest is to see the various techniques the Excel community uses to quickly pull together reporting. I’m hoping to showcase the versatility, flexibility, and ease of creating reports and dashboards with Excel. Read more

I was puttering around on the internet when I came across Daniel Ferry’s old post on Location mapping in Excel. The idea is that you can use latitudes and longitudes to plot points on a chart. Cool trick.

Then I wondered if you could achieve a similar result with a PivotTable. Well…it turns out you can.

This is a screenshot of a map visualization I pulled together using a normal a PivotTable.
With slicers, this PivotTable becomes a dynamic location intelligence component for your dashboards.

Nifty!

The steps to create this kind of map are pretty straight forward: Read more

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 Read more

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 Read more

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 Read more

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 Read more

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 Read more

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 Read more