New Contest for 2017: The Excel 5 Minute Challenge

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

Creating Map Visualizations within Standard PivotTables

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

Easy File Search with Tags and the Document Location Widget

I’m back from a long hiatus from blogging. While I was gone, I had a project that involved helping a client tag hundreds of Excel files for easier searching.

The Basics of Using Document Tags

For those of you that don’t know, you can go into the properties of an Excel file…

File->Info

…then enter keywords that can be used by the built in Windows Explorer search feature.

Specifically, enter your keywords in the Tags input box under Properties (to the right of the Info screen)

In this example, I’ve tagged an Excel workbook called Read more

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

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

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

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