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.


The 5-Minute Excel Challenge

The task is simple. Start with some prepared data and create a fully functional dashboard/report in 5 minutes or less.

To participate in this contest, you can submit a video of you building out a report or dashboard. All videos will be loaded to an official 5-Minute Challenge YouTube Playlist.



All submitted videos will be judged by the Excel community. The number of “Likes” on each video posted will determine the winning entries.

So what can you win?

1st prize: Xbox One


2nd prize: Fitbit Fitness Wristband


3rd prize: Amazon Fire HD 8


3 Honorable Mentions:

Your choice of one of these Excel products.

DataPig Add-ins Gift Pack


Mr. Excel Gift Pack


Ken Pul’s Pivot Table Course


Contest Rules

  • Your video must be no more than 5 minutes long (it can be less than 5 minutes).
  • Your video must be real-time. No edits! No fast-forwarding. No tricks.
  • Your video must end with a reasonably useful working Excel dashboard or report.
  • You can start with staged/prepared data on your worksheet or in a PowerPivot data model. However, your presentation layer/canvas must be empty.
  • All work must be done real-time. No copy and pasting pre-cooked VBA, Charts, PivotTables etc. However, you can copy and paste objects created during the video.
  • You can use all tools available to you: Formulas, Macros, VBA, Power Pivot, Power Query, Power BI, and even Add-ins. I want to showcase the versatility of Excel, and how Excel can be used at all levels of skill. If you’re an expert at Power BI, show that off. If you’re a charting wizard, show that off! If you’re the king of Pivot Tables, show that off!
  • You don’t have to narrate your video, but it will definitely help your chances of winning likes from the Excel community.
  • You can submit as many videos as you’d like (no limits on the number of submissions).


How do you Submit your Videos?

Just upload your video to OneDrive, Google Drive, Dropbox, or any content sharing service.

Then send me an email with the subject line “Excel 5 Minute Challenge”. Include a link to the actual video file (no links back to your website). If you want to advertise your site, then give me that info and I’ll put it in the video description when I post your submission.

Email to

I will then upload your entry to the official 5-Minute Challenge YouTube Playlist.

I will send you a reply when I’ve posted your entry.

If you’re video does not meet the contest rules above, or if I can’t get to your video, I’ll let you know.


Contest Deadline

All entries must be submitted to me by Sunday, February 5th
(11:59pm US Central time).

I will announce winners on Friday, February 10th (6:00pm US Central time).


Winner Selection

Winners will be selected based on the number of “Likes” each video receives.

In the event of a tie, I’ll make the final determination based on the video content.



  • Prizes are subject to availability and shipping costs to your area. I’ll use Amazon to distribute prizes, so if I can get the prize to you from there, great. If you live in Papua New Guinea and the shipping cost is more than the value of the prize, then I’ll replace the prize with cash value.
  • You can only win one prize (regardless of how many submissions you enter).
  • By submitting a video, you agree that none of the data you present is confidential or under NDA.

I’m very much looking forward to seeing all the cool things our Excel community can create!

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.


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

1. Get some data with Latitudes and Longitudes.

2. Create two new columns to round the latitudes and longitudes. This essentially reduces the number of unique values so that your PivotTable doesn’t choke.


3. Create a new PivotTable, placing your Rounded latitude field in the Rows area and the Rounded longitude field in the Columns area.


4. Once your PivotTable is created, sort your Rows so that they run from Largest to Smallest.


5. Go into the PivotTable Options and remove all GrandTotals. Also be sure to clear the “Autofit columns… check box.


6. Click inside the PivotTable and select Conditional Formatting. In the Rules Manager dialog box, select the third option under the Apply Rules To section. For Rule Type, choose to Format all cells based on their values. Finally, choose the 3-Color Scale format Style. Adjust the formatting as needed. Your Rule manager should look something like this.


7. At this point, all there is left to do is clean up.

  • Change the number formatting of the values so that no values show (select Custom and enter ;;;
    in the Type field).
  • Hide the Row and Column fields of the PivotTable
  • Adjust the sheet row and column widths to get the aspect ratio right.


8. Add a Slicer and make all your nerd friends jealous.


Now obviously, this techniques doesn’t produce a strictly accurate map. I mean, come on, the first step is to round the latitudes and longitudes. That being said, it does generate a directionally correct map profile that looks pretty cool.

You can play with a sample file by downloading it.

You kids have fun.

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…


…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 MyImportantBook.xlsx with Bacon and Pork.


Once your file is tagged, you can open Windows Explorer and enter a search term. Any files tagged with your search term will pop up. You can then double-click the file to open it.


So if you’re constantly searching for files, consider tagging them in the Document Properties to help in searching.


Using VBA to tag Documents

If you’re feeling especially geeky, you can set up an Excel Table called FileTags that holds your tags.

Be sure your name the Table FileTags.

Then you can run this code to automatically add the tags you define in the table.

  1. Sub CreateDocumentTags()
  2.   Dim lstKeywords As ListObject
  3.   Dim strTags As String
  4.   Dim docTags As DocumentProperty
  6.   'capture values from the FileTags table
  7.  Set lstKeywords = ThisWorkbook.Sheets(Range("FileTags").Parent.Name).ListObjects("FileTags")
  8.   For i = 1 To lstKeywords.ListRows.Count
  9.   strTags = strTags & lstKeywords.DataBodyRange(i, 1).Value & ", "
  10.   Next i
  12.   'set document tag with the captured values
  13.  Set docTags = ThisWorkbook.BuiltinDocumentProperties("Keywords")
  14.   docTags.Value = Left(strTags, Len(strTags) - 2)
  16. End Sub


Playing with the Mysterious Document Location widget.

If you’re bored, and want to delve into quirky territory, you can play around with the Document Location widget.

You’ll first have to add it to the Quick Access Toolbar.

File->Options->Quick Access Toolbar.

You can find the Document Location widget under Commands Not in the Ribbon.


I have to be honest. This thing is practically worthless.

As far as I can tell, it’s meant to show you the full path of the currently open document.

Unfortunately, you can’t resize the widget so it’s not as useful as you would think.


The redeeming attribute of this widget is that it basically works like the Windows Explorer address bar.

So you can enter basically any path or URL to automatically open a new window with the content you request.

For instance, you can open directly from Excel by simply entering the URL.


Like I said…practically worthless (I mean the widget…not dailydoseofexcel)


Interestingly enough, you can use the Document Location widget to enter a file search on the fly.

Simply enter:


Your search term goes after the query argument.

Then you can specify HOMEPATH to tell windows only look in the folders under the userid you’re logged into.

Excel will raise a warning.

Like all other warnings Excel gives me, I promptly ignore it and click YES.


Windows Explorer will pop into action, showing you all the files that match your search term (files that contain your search term or are tagged with your search term).


For the location argument, you can use any of the Windows Common File Names. For instance, this search will be limited to the Documents folder:


This search will be limited to the Application Data folder:



This is fun to play with, but I’ve yet to use this trick in any meaningful way.

Another thing I should mention is that I have not been able to get this trick to work with more than one search term at a time. There is probably more robust syntax that needs to be used, but it’s probably just easier to enter your search directly into Windows Explorer.


Wow…what a useless trick for my first blog post after 3 months.

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

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