Create a CRM System in Google Docs

Spreadsheets,Tips 8 September 2011 0 Comments

Having a customer relationship management (CRM) system is a must for anyone in sales. These systems allow you to manage your leads, contacts, accounts, and opportunities. Some provide reporting, such as showing your sales pipeline. There are desktop and client server solutions like ACT!. is probably the premier on-line CRM. The problem is, these solutions are often too expensive or too complicated for small businesses. SugarCRM is a good open source alternative, but it can be a little beyond the small business owner to install and deploy (it requires a web server or a hosting account). We also like Zoho CRM: it integrates with Google Apps and there is a free version which is more than enough to get you started.

A recent post at the Software Advice blog outlines how small businesses can use Google Docs to create a CRM. It’s compelling: easy to set up, easy to customize, and easy to use. Perhaps the best feature of this approach is the ability to share your CRM system, since Google Docs has sharing and collaboration built in:

By using Google Docs you can build your own CRM system on a platform that will allow for sharing and same-time editing by many users. What this means is that if both Bob and Joe are using the CRM system at the same time and Bob inputs some new information into the CRM system, Joe will immediately see the changes.

Head over to the Software Advice blog for complete instructions on How to Create a CRM System Using Google Docs!


Tagged in , , , , , ,

Track Your Expenses Using Gmail and Google Docs

Forms,Spreadsheets,Tips 12 May 2011 0 Comments

Here’s an awesome tip for tracking expenses across multiple credit cards and back accounts.  You basically create a form in Google Docs for entering an expense and then email the form to your email account. In your email client, save the email in a convenient location and then simply fill out the form attached to the email and submit it each time you have an expense. If you use Gmail, you can set up a Quick Link and quickly access the form in Gmail. The same principle could be used for all kinds of applications:

  • Keeping track of tracking numbers.
  • Entering investment purchases or sales.
  • Creating a grocery list.
  • Generating a to do list.
  • and more!

If you are like me you spend a lot of time in your inbox, so being able to keep track of stuff using email is a huge convenience.

Use Gmail and Google Docs to Track Expenses


Tagged in , , , , , , ,

How to Scrape Website Content

Spreadsheets,Tips 15 April 2011 11 Comments

While this tip might be a little more advanced, it shows how easy it is to “scrape” content from websites into your spreadsheets. We’ll be relying on the fact that HTML is a form of XML (strictly speaking XHTML is a valid form of XML, but for all intents and purposes we can assume HTML is close enough to a form of XML).

  • Go to the website you want to scrape content from. In this example, we’ll be taking data from a person’s twitter stream, for example
  • By looking the page source, determine what html element on the page the content you want is found in. For example, the twitter updates on my twitter page are found in span elements of the class “entry-content”:

  • In your Google Docs spreadsheet, enter the following formula:
  • The spreadsheet will fill in with the content from the imported xml:

The importXML function allows us to scrape the content from my twitter page. It takes two arguments:

  1. The url of the xml you want to import (in this case, its the html from the page at!).
  2. An xpath query that specifies what part of the xml you want to import. This is the trickiest part. In my example, the xpath simply specifies any span element with a class attribute equal to “entry-content”. It’s easy enough to hack around with the xpath, but you also might like reading up on how to create xpath queries.

Because my twitter page is constantly updated, the spreadsheet will constantly be updated to reflect this “live” data. Of course web page content isn’t the only kind of xml you can import using the importXML function. Many web apps (like twitter, Google Apps, and more) provide xml based API’s which you could also import from using the same method described above. If you’ve got an interesting example, please share it in the comments!

As always, you can check out the spreadsheet used in this example.

Tagged in , , , , , , , , ,

Restore An Older Version Of A Document

Documents,Drawings,Forms,Presentations,Spreadsheets,Tips 18 March 2011 0 Comments

I can’t tell you how many times I have seen document files with the following kind of name: BudgetV5BA.xls. What’s typically going on here is that a document, in this case a budget spreadsheet, is being saved as a new file every time a change is made, and the person doing so is adding a version number, and perhaps the initials of the person who made the change to the file name. Clearly, the goal is to be able to retrieve older versions of the document. It’s like using the file system to set up a rudimentary version control system.

In Google docs you never have to do any of this. Each document persists all the revisions ever made, and it is a simple matter to restore an older version of the document. In the following example, let’s say you shared the document with Brick Andrews, and upon reviewing the changes he made, you decide you don’t like them. You’d like to revert to the version of the document before Brick made his changes. Here’s how:

  • In the File menu, select See revision history:

  • You will see the revision history view of the document. You can click a time stamp in the Revision history sidebar on the right to see what changes were made at a given time (bonus tip: use the arrow keys to scan through the revision history quickly). If the option to Show Changes is not selected only a single revision is highlighted and selected:

  • By electing the Show Changes option, the current revision is highlighted, and the previous version it is comparing itself to is also selected shown as a dark bar on the left of the Revision history sidebar (if you’ve been sharing this document, the changes each collaborator has made are color coded. In this case Brick’s changes are in pink):

  • To restore a previous version, select it in the Revision history sidebar and select the option to Restore this revision:

Restoring your document to an earlier version like this does not eliminate what were the “newer” versions of your document. The restored version simply moves to the top of the revision history, maintaining all previous versions of the document, including the current version.

  • To return to editing the document, select the X in the top right corner of the Revision history sidebar.

Tagged in , , , , , , , ,

How To Format Multiple Currencies

Spreadsheets,Tips 15 March 2011 0 Comments

We’ve already shown you how to convert between different currencies in a spreadsheet. But how do we format the resulting figures such that 100 US dollars is shown as $100 and 100 British pounds is shown as £100? Google docs spreadsheets allows you to set a global setting for the currency, and then override that setting on specific ranges of cells. Here’s how:

  • To set the global settings for the spreadsheet, click on the File menu and select Spreadsheet settings…:

  • The Spreadsheet Settings dialog will appear. In the Locale drop down list select the country which you’d like to use for the default currency:

  • In this example we’ve selected South Korea. Pick whatever country you would like for your default currency. Click the Save Settings button on the Spreadsheet Settings dialog to save your choice and close the dialog.
  • The Format as Currency button on the toolbar will now show the currency symbol for the country you chose above. In our case we see the symbol for the South Korean Won on the Format as Currency button on the toolbar. If we select a range of cells in the spreadsheet and click on the Format as Currency button, the values in these cells will be represented as South Korean Won:

Thats’s pretty good, but you’ll also notice that any other values in the same rows will also be formatted as the same default currency! What if other values in the spreadsheet need to be formatted as a currency other than the default currency? It’s easy, and here’s how:

  • Select the range of cells which you’d like to format as another currency. Click on the More Formats button on the toolbar (it’s the one with the 123 on it). Select More currencies from the menu that appears. That will cause another menu listing other currencies to appear. Select the currency with which you would like to format the range you had selected. In the example below, we’re selecting British Pounds:

Continue formatting in other currencies using the same technique. Here’s an example where we set the default currency to US dollars and overrode the currency format for columns C (pounds) and D (euros):

You can find the spreadsheet used in this example here.

Tagged in , , , , , , , , ,

How To Calculate Roman Numerals

Spreadsheets,Tips 14 March 2011 0 Comments

Given a number, are you able to express it as a roman numeral? In roman, numerals are represented as letters (I for 1, V for 5, X for 10 and so on). These roman numerals are additive; the number is calculated by adding all the letters together, for example: LX = 50 + 10 which equals 60. If a smaller number precedes a larger number, the smaller number gets subtracted first before adding the letters together. For example: IV = 5 – 1 which equals 4.

Figuring out how to represent a number as a roman numeral is easy with Google Docs spreadsheets:

  • In a cell use the roman() function. You provide the number, or a reference to a cell with the number, as a parameter to this method. For example, let’s say you would like the roman numeral for the number in cell A2 to appear in B2. Use the following formula in B2:

There are strict rules when specifying roman numerals, specifically which kind of characters can precede another kind of character. For example, following the strictest rules, 999 in roman numerals is CMXCIX. You might notice that 999 is one less than 1000 (which is represented as M in roman numerals). If you relax the rules, you might represent 999 as IM, i.e. subtract 1 from 1000. It’s not strictly correct, but is definitely more succinct! You can relax the rules by providing a 0, 1, 2, 3 or 4 as a second parameter to the roman() method, where the higher the number, the more the rules are relaxed. For example, the get the simplest form of roman numeral for the number in A2 in cell B3:

  • Use the roman() function in cell B3, using A2 as the first parameter and 4 as the second parameter:
=roman(A2, 4)

The actual spreadsheet used in this example can be found here.

Tagged in , , ,

How To Split The Contents Of A Cell

Spreadsheets,Tips 11 March 2011 0 Comments

File this one under useful formulas: sometimes in a spreadsheet we want to split the contents of a cell. For example, perhaps the you have pasted a list of names into a column, like “John Doe”, but what you really want is the first names in one column and the last names in another. This can easily be achieved using the split() function:

  • Set the cell equal to the value of the split() function where the first parameter is the text or a reference to the cell whose contents you wish to split, and the second parameter is the delimiter, i.e. the character upon which you will split the contents of the cell. For example, to split the contents of cell A2 at each instance of a space character, you would type in the following formula:
=split(A2," ")

Notice that the cell is split into a new cell for every occurrence of the delimiter. So, in the above example, since there are two space characters in A2, the contents will be split into three cells (B2, C2 and D2). What if there are multiple characters which act as delimiters? Easy:

  • Set the cell equal to the value of the split() function like the last example, but for the second parameter, include all of the delimiters. For example, if you want to split the contents of A2 on every occurrence of a space, underscore, or comma, use ” _,” as the second parameter to the split() function like so:
=split(A5," _,")

To review how the split formula works on an actual Google spreadsheet, you can access the spreadsheet used in the above examples here.

Tagged in , , , ,

Accessing The Browser Context Menu

Documents,Drawings,Presentations,Spreadsheets,Tips 10 March 2011 0 Comments

If I were a more poetic person, I suppose this tip could have been called The Tale of Two Context Menus. A context menu (often called a pop-up menu) is a menu that appears when you click the right mouse button. It usually offers a limited set of choices, but they usually pertain to the current selection, or context. When you are surfing the web, right clicking in a web page usually brings up the browser’s context menu with helpful options like navigating back, forwards, reloading the page, printing the page, viewing the page source, saving a selected image or searching the web for the selected text. The contents of the browser’s context menu will differ slightly from browser to browser and also depends on what is selected on the page, if anything.

Some web sites override the right click on the mouse and provide their own context menu. Google docs is one of them. When in a document, spreadsheet, presentation or drawing, right clicking on the mouse accesses the Google docs context menu:

You can still access the browser’s context menu when editing a Google doc. Here’s how:

  • Hold the Shift key while right clicking on your mouse. The browser’s context menu will appear:

Accessing the browser context menu using Shift + Right Click is probably one of the lessor known shortcuts available in Google Docs. Some of them are more obvious like Cntl+b for making the selected text bold which is the same keyboard shortcut as many other word processors like Microsoft Word. There are many keyboard shortcuts for both Google documents and spreadsheets.

Tagged in , , ,

How To Calculate Foreign Exchange

Spreadsheets,Tips 9 March 2011 6 Comments

Gone are the days or keying in and manually updating an exchange rate in a cell of a spreadsheet and using that to perform currency conversions. Many of the formulas and functions available in Google docs spreadsheets are plugged into the real time web. Currency conversion calculations can always be up to date without having to look up an exchange rate. Here’s one way to do it:

  • In a Google Docs spreadsheet use the GoogleFinance() function to get an exchange rate. For example, to get the exchange rate from US dollars to Euros you would enter:
  • To convert the value in a cell representing an amount in one currency to another currency, multiply the cell reference by the currency conversion formula above in another cell. For example, if the amount in US dollars is in cell A2, we can put the equivalent amount in Euros in cell B2 by entering the following formula in B2:

Why This Works

Currency conversions using the GoogleFinance() function take the following form:

=GoogleFinance("CURRENCY:<from currency symbol><to currency symbol>)

For example, to go from Canadian dollars to Philippine pesos, you’d use the following formula:


where CAD is the currency symbol for Canadian dollars and PHP is the currency symbol for Philippine pesos. You can find a complete and searchable list of currency symbols that can be used with this formula here.


Tagged in , , , , , ,

Get The Weekday Name From A Date

Spreadsheets,Tips 8 March 2011 2 Comments

Have you ever wanted to determine what kind of day it was from a date in a Google Docs spreadsheet? For example, suppose you would like to know if March 15, 1976 was a Monday or a Tuesday or a Wednesday, etc.? You can use the weekday() function, but it will return the weekday number, for example:


sets the cell value to 2, being the number of days since Sunday where Sunday = 1. What if you want to get “Monday” from your formula? Here’s how (for the following examples, we’ll be referencing a date in cell A2; substitute the cell reference of any cell with a date in it):

  • If you want to get a three letter abbreviated weekday name, such as “Mon” for Monday, you can use the following formula:

  • If you want the full name of the weekday, as in “Monday”, you can use the following formula:

Why This Works

The text() function will convert a date into text. You must also provide the function with a format string. This tells the text() function how you want the date formatted when converting it to text. For example, yyyy returns the year in four digits and yy returns the year in two digits. There are format strings for months, days, hours, minutes, etc. For example:

=text(A2, "mmm dd yyyy")

returns Mar 15 1976 to the cell. eee is the format string for a three letter abbreviated day name and eeee is the format string for the full name name. Unfortunately, there seems to be a bug in Google Docs and the following formula:

=text(A2, "eeee")

returns the incorrect value (in our case eeee27834) to the cell. To get around this, you provide a format string that contains other date components and then strip those extra date parts away in the result.


Tagged in , , , ,