QuickBooks Tech Trends

Interactively Map Your QuickBooks Data in Excel – Part 1

Written by David Ringstrom

If you’re using Excel 2013 or Excel 2016, then you’re only a few mouse clicks away from creating interactive maps. You can also create such maps in Excel for iPad, but as of this writing the feature isn’t available in Excel for Android or Excel Mobile for Windows 10. The maps are created by way of a free Bing Maps app within the supported versions of Excel. Most data that you export out of QuickBooks will need some clean-up first, but then you’ll be just a few clicks away from your first map. You’ll soon be seeing where your customers, vendors, or employees are located across the country—or even the world.

Supported Excel Versions

To be clear, you won’t have access to the feature I discuss here if you’re using Excel 2010 or earlier, or—as of this writing—any version of Excel for Mac. The Bing Maps feature is accessed through the Apps store that was introduced in Office 2013, which hasn’t yet made its way to Excel 2016 for Mac. Excel for iPad users do have access to this feature, but the clean-up procedures necessary may be tedious to accomplish on an iPad—once you get through the gauntlet of getting data out of QuickBooks to Excel.

The other important caveat I need to make here is that the mapping feature will only work with spreadsheets that are saved in the modern document formats, which include:

  • Excel Workbook (.XLSX)
  • Macro-Enabled Excel Workbook (.XLSM)
  • Binary Excel Workbook (.XLSB)

You won’t be able to use the Bing Maps feature, or access the app store, in Excel 2013 and later if your workbook is saved in the antiquated .XLS workbook format. Until recently, QuickBooks Online could only export reports in .XLS format, but you can now export reports to the XLSX format directly. This isn’t a consideration in the desktop versions of QuickBooks, as data is sent directly to a new, blank Excel workbook in the proper format. As shown in Figure 1, the Apps store is disabled when the active workbook isn’t in a supported format. As a side note, the last time I checked, Xero still only allows users to export reports to the .XLS format, so you’ll need to convert any such reports to a supported format. I recently discussed managing the Protected View for QuickBooks Online, as well as converting XLS workbooks if you need guidance on either issue.

Maps Figure 1

Figure 1: The Bing Maps feature only works with modern workbook formats, meaning .XLSX, .XLSM, or .XLSB.

Mappable QuickBooks Data

A good starting point for your first map might be the Customer Contact List in QuickBooks:

  • QuickBooks Online:
    • Log into your books (or use the sample company)
    • Select Reports
    • Select All Reports
    • Select Review Sales
    • Select Customer Contact List
    • Click the Export button, and select Excel (XLSX)
    • Save the file and then open it in Excel
  • QuickBooks Desktop:
    • Select Reports
    • Select Lists
    • Select Customer Contact List
    • Click the Export button, and then click OK

Once either report is open in Excel, the next step is to isolate a column that contains complete addresses (meaning street, city, state, and ZIP code). Both of the aforementioned reports include such a column:

  • QuickBooks Online: Addresses appear in a separate column separate from the customer name.
  • QuickBooks Desktop: Customer name and address together in single column.

In this case, QuickBooks gives you a jump start on the required format, as many programs divide addresses up into street, city, state, and ZIP code columns. In such cases you’ll need to concatenate—in layman’s terms, combine—the columns into one. Some users rely on Excel’s CONCATENATE function, but I prefer to simply use an ampersand instead, as shown in Figure 2.

In this case, the formula combines cell A2 together with a comma with a space and then cell B2. An IF statement checks to see if cell C2 is blank, and if so, returns two double-quotes, otherwise a comma and a space, along with cell C2:

=A2&, &B2&IF(C2=“”,“”,,&C2)

The combination of double-quotes and commas makes the formula tough to interpret, but here’s how the IF statement breaks down:

  • Logical_test: C2=“”
  • Value if true: “”
  • Value if false: “, “&C2 (that’s a comma with a space inbetween the double-quotes)

Fortunately, QuickBooks provides prebuilt columns with the addresses, but you also have the tools to assemble the data together should you need to. When you copy and paste a column of concatenated data, be sure to choose Paste Special, and then double-click on Values.

Maps Figure 2

Figure 2: Addresses you wish to map must appear in a single column, such as column D.

To create your first map:

  1. Copy the addresses to a single column in a new, blank worksheet.
  2. Eliminate blank rows—sorting is an easy way to do so.
  3. Eliminate duplicate addresses—select the list of addresses and choose Remove Duplicates from the Data menu within Excel. Click OK as needed to eliminate duplicates.
  4. Select the cells that you wish to place on a map and choose Bing Maps from the Insert.
  5. Ignore the Insert Sample Data button, and instead click the Show Locations button in the top right-hand corner of the resulting map object to display your points, as shown in Figure 3.
Maps Figure 3

Figure 3: Ignore the Insert Sample Data button and click the Location button instead.

As shown in Figure 4, you can now click on points to view the name and address, as well as zoom in and out of the map. Controls along the top allow you to zoom in and out as well as filter the map itself. Maps you create in Excel 2013 and later can be viewed in earlier versions of Excel, but will appear as static pictures without any interactivity.

Maps Figure 4

Figure 4: Click on a point on the map to view the address.

In the second and final part of this series I’ll show you how go to beyond the basics of displaying points on a map to reflect sales or expense figures by location, with amount-based indicators on the map.

About the author

David Ringstrom

David Ringstrom, CPA, is the president of Accounting Advisors, Inc., an Atlanta-based spreadsheet and database consulting firm he started in 1991. Throughout his career, David has spoken at conferences on Excel, and he currently leads dozens of webinars each year on Excel, QuickBooks, and other software. He has served as the technical editor for over 25 books, including several editions in Wiley’s QuickBooks for Dummies and Quicken for Dummies series. In addition to writing for QuickBooks and Beyond, David is the Tech Editor at Large for AccountingWEB and Going Concern. He also offers live webcasts and self-study courses through CPE Link. His freelance articles on spreadsheets have been published as far afield as Pakistan. During training sessions, you’ll often hear David state, “Either you work Excel, or it works you!”

2 Comments

  • Does the Excel maps app allow for the creation of polygons based on US Census data? I’d like to plot customers overlaid on top of CBSAs (Core Based Statistical Areas) OR MSAs (Metropolitan Statistical Areas).

    • Dave,

      As far as I can tell what you’re requesting is beyond the scope of what the Bing Maps app offers. Part 2 of the series will show how to integrate revenue or cost figures into the maps, but it appears to only work with addresses, and doesn’t allow for integrating census data.

      David