Home
Blog


Using Excel as a Simple CRM



I tend to keep track of data in Excel, and lately I've been using it as a CRM. I keep different sheets in order to answer different kinds of questions: Who asked about cell formatting? Who did I first talk to on our online chat? How many users do we have in Chicago?

In theory, each sheet is synced with a master sheet containing all customer data. In practice, maintaining everything by hand was too time-consuming, and my data became scattered.

When I realized this system wasn't scalable, I began looking for a replacement. My two main requirements were flexibility and ease-of-use. If I suddenly need to know which customers are on the West Coast, I want to be able to do it without inputting the data repeatedly or cross-checking a single sheet.

After trying some other products, I was inspired by my friend Peter's blog post to build my own solution in Datanitro. This let me extend my original ad-hoc system within Excel by keeping all my data synced in a spreadsheet, while letting me interact with any view I want.

The script

The design is straightforward - the user interacts with one sheet, which displays and organizes data on the fly. Another sheet stores all data not currently displayed. For example, here's a spreadsheet Darth Vader might have on the Rebel Alliance:

The active part of the CRM data.  The full
					   dataset is stored on a different Excel sheet.
(Download the sample to follow along with the example. You'll need to have DataNitro installed.)

This is the 'View' sheet, which displays the active part of the data. The full set of data is on the 'Data' sheet.

This is the full CRM dataset.

To add a new record, just type it in the 'View' sheet:

Adding a record to the CRM

Here's how to add a note on Yoda. Open the Python shell and run

from db import *
show_col('notes')
This will add the 'notes' column from the database to the view. If there were no notes column, one would be created. We can then type in the note.

Adding a field to the CRM

Let's say we want to pick the Death Star's first target. We can sort the records by homeworld by running

sort_records('homeworld')

Sorting the CRM in Excel

Alderaan looks like a good candidate - let's investigate it further. The filter_records matches records to a regular expression. It can match either against records in the current view, or against all records in the database. (The last parameter is filter_from_view, which is True by default.)

filter_records('Alderaan', 'homeworld', False)

Filtering the CRM in Excel

Whether you're suppressing the Rebel Alliance or planning a barbecue, try out db.py to keep track of your data.