How to Automate Sales Meeting Logs using Backstop Excel Toolkit

Kenneth Lo, PMP
May 21, 2019 · 5 min read
Image for post
Image for post

It is no secret that I’m a big fan of leveraging Backstop Excel Toolkit (ETK) and Report Builder to extract actionable insight out of Backstop CRM. Fresh data comes out on a silver platter with a simple mouse click; life can hardly get better than that.

It can actually get better than that! Let’s first run through a quick Pros & Cons analysis of this workflow.


  • On-demand access to fresh raw data to all end users with ETK installed
  • Division of labor between administrators/power users and end users
  • Opportunities to develop custom reporting and analytics within Excel


  • Report Builder lacks formatting options. Everything boringly comes out in alternate blue/white rows
  • Every single column used in the report, hidden or not, is displayed in ETK reports
  • Incapability to sort any column descendingly or by more than one column
Image for post
Image for post

Backstop provides comprehensive documentation and webinars on how best to build advanced custom reporting in Excel. I highly recommend watching those excellent webcasts run by Richard and Fidel.

So what are we doing here? This serves as a field guide for those Backstop practitioners in the wild. We will illustrate a use case by grabbing Meetings/Calls, format the raw data with Excel macros and Visual Basic Application (VBA), and most importantly have fun with it.

Are you buckled up?

Image for post
Image for post

The Plot

The raw data natively out of Backstop Report Builder, although complete, could be prettier. The ask is to

  1. Add an eye-catching report title and current date stamp
  2. Display dates in MMM-DD format (eg May-12)
  3. Abbreviate attendee names with their initials
  4. Abbreviate activity tags
  5. Delineate past and future meetings with a subtle visual clue
Image for post
Image for post

The Excel file template is freely available on Github.

Technical Approach

  1. Build an ETK report to pull Meetings/Calls out of Backstop > _meetings
  2. Build an ETK report to pull MOM Capital employees and their initials >_employees
  3. Create a worksheet (aka tab) to house mappings between Activity Tags to display values >_tags
  4. Create a worksheet to house the gorgeous and curated output >Meetings
Image for post
Image for post

Before deep-diving into any VBA code, be forewarned this might feel a little overwhelming and over-engineered. Well, we are planting the seeds for even more legendary scenarios in upcoming blog posts. Stay tuned!

Under the Hood

Image for post
Image for post

Main Subroutine


To ensure a clean slate, we remove pre-existing data and formatting options in the destination worksheet before each ETK refresh.


Clone raw data to the destination worksheet.


Abbreviate values to enhance report readability based on a Dictionary object.


Rename column names. This could also be achieved globally within Entity Definition Manager or Report Builder. The benefit of renaming it here leaves everything else intact, except this Excel file.


Find the threshold between the latest past event and nearest future one, and insert a black line as a quick visual clue.


Set numeric and date formatting options.


ETK returns both visible and hidden columns from the Report Builder. This function removes those columns not used in the final printable version.


Add a report title and date stamp.


Set printing options such as margins, orientation, scaling, and gridlines.

Helper Functions


Instantiate and return a Worksheet object based on a friendly name.


Using a Dictionary object to map activity tags to display values.


Using a Dictionary object to map attendee names to initials. Instead of being hardcoded, initials are stored as Other ID and pulled in using ETK.


Using a Dictionary object to map columns headings. We could also easily revise the column names in Report Builder. However, some occasions mandate consistent naming conventions; this gives us more flexibility.


Using a Dictionary object to map columns to deleted from the final output. I wish ETK does not dump all columns indiscriminately onto the report.


Identity the non-blank cell range (e.g. A1:K18)


Identity the last non-blank column (e.g. K).


Identity the last non-blank row (e.g. 18).

Source Code

Originally published at on May 21, 2019.

Backstop CRM

Articles on Backstop CRM best practices, tips and tricks.

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store