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

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