Gmail Chat Smileys

November 6th, 2008 by bill · 322 Comments

»Gmail Chat has a new robot emoticon and a whole new category of graphics for chat.

Gmail Chat emoticons are the most popular search terms here at And now there’s a new robot smiley.

In the past we’ve covered Gmail Chatβ€”and its emoticons in particular. Of course everyone who uses Gmail knows it now supports smileys in email, but there have been a couple minor changes for the chat smileys too.

New Gmail Chat Smiley Category

First, there is a new category in the smiley chat popup that has alternate renderings of the graphics.

Also, there is a new robot smiley, [:|], that was left here today in a comment by “Pat.” Thanks, Pat. Are there any more new ones?

Okay, kids. The “poo” is now included, thanks to an anonymous comment posted on the article, New Gmail Chat Smilies. Or, maybe it’s a beehive. Whatever.

List of All Gmail Chat Emoticons

So, given the robot and that there is now a new category, and especially since the partial lists in prior articles, here is the complete list of Gmail Chat Smileys.

Gmail Chat Smileys

(This image seems to have been lifted and posted elsewhere. If you use post this elsewhere, please provide a link back to this article.)

If any are missing, be sure to leave them in a comment.

→ 322 CommentsTags: Google · Tips

Wildcard Namespaces In XPath

November 1st, 2008 by bill · 7 Comments

»How to select an element in an XML document without having to deal with XPath namespaces. In otherwords, how to wildcard namespaces in XPath.

Select namespaced elements in XPath without specifying the namespace.

Every once in a while you need to get to an XML element that is tucked within some namespace or another, but you really are not in the mood to deal with namespaces. You really don’t want to mess with XmlNamespaceManager or its ilk; or maybe you only have XPath to work with.

When this comes up, it usually takes me a few moments to remember, but the magic is the local-name() function.


<gw:bookstore xmlns:gw="">
		<gw:title>Warming Up To The New Ice Age</gw:title>
		<gw:author>A. Nother Lemming</gw:author>
		<gw:title>The Sky Is Falling</gw:title>
		<gw:author>Chicken Little</gw:author>
		<gw:title>Glaciers Will Burn Us All</gw:title>
		<gw:author>Chicken Little</gw:author>
		<gw:title>Data: A Cookbook</gw:title>
		<gw:author>Grant Me Money</gw:author>



This basically says to select any element where the local name, i.e. the element name minus the namespace, is equal to ‘title’.

This can be extended with other XPath functions. In the following example we want to get the titles of books by Chicken Little.

//*[ local-name() = 'title' and (../*[local-name() = 'author'
	and contains( text(), 'Chicken Little' ) ]) ]

Here we’re selecting elements with a local name of ‘title’ and a sibling element named author and where that sibling’s value contains ‘Chicken Little’.

This syntax is not as convenient as something like /*:title, but at least it does the trick.

→ 7 CommentsTags: Programming · XML

Standard and Hidden Yahoo Emoticons

October 28th, 2008 by bill · 11 Comments

»Yahoo! has been around much longer than Google, and so have their emoticons. This post shows all of Yahoo's famous smileys, normal and hidden, including a few new ones for Messenger 9.

Are Yahoo Chat Smileys as Popular As Gmail Chat Smileys?

Well, since the most popular posts on this site are, by far, the gmail chat and smiley articles, i am curious to see if Yahoo emoticons will have the same draw. And yes, i know that almost everyone knows these. The value add here will be that they’re on one page. Nice, huh?

Yahoo Instant Messenger

Yahoo has had chat since the dark ages and is still going strong. It has grown to be an enormous memory hog, but sports enough features to almost justify its weight. There are the smileys of course, but also photo and file sharing, games, webcam and voice support, voicemail, conference chats, shared web video viewing, avatars, audibles, graphic enhancements called IMVironments, SMS messenging, and more.

Yahoo Emoticons

Yahoo IM has large standard and “hidden” sets of emoticons. The standard smiley list is listed by Yahoo but included below as well.

New Yahoo Emoticons

There are several standard smileys new to Yahoo! Messenger 9, including “I don’t want to see,” “hurry up,” “rock on,” “thumbs down,” “thumbs up,” “it wasn’t me,” and the pirate. The pirate, however, is only available in Yahoo! Messenger for the Web.

Yahoo Hidden Emoticons

The hidden emoticons are hidden only in that that they’re not on the popup smiley list. They are, however, listed by Yahoo. (This is unlike hidden Gmail chat smileys, which are mostly only detailed on sites like this one.)

Two new hidden emoticons include the Bee and the Transformer, though the Transformer is only available in Yahoo! Messenger for the Web.

→ 11 CommentsTags: Yahoo!

ESV Bible Via REST

March 7th, 2008 by bill · 1 Comment

»A tutorial and demos covering the use of Javascript, jQuery, CSS, and PHP to access, search, and style scripture with the English Standard Version (ESV) REST API.

The ESV Bible REST Web Service

The English Standard Version (ESV) of the Bible is a literal word-for-word translation from Crossway Bibles, who have made it available via a REST web service. There is no charge for non-commercial applications, such as on a personal or church website, though use is limited to 500 verses per query and 5000 queries per day.

The ESV API is a nice rich interface to the Bible. The basic queries include the following:

  • passageQuery – takes a verse reference (or range) and returns the text.
  • query – performs a word search or passage lookup on the search criteria and returns the results.
  • readingPlanQuery – given a date, starting date, and reading plan, such as one-year-tract or chronological, this returns the passage(s) for the date.

There are a lot of options, result formats, etc. so be sure to visit the API page for more details.

Notes On Making This Work

Getting the examples below to work presented some challenges or required some work-arounds or otherwise might be an interesting bit.

Cross-site Ajax requires the use of a proxy. In this case, I’ve got a PHP proxy using Curl to make the actual web service call to the ESV API. The PHP is something along the lines of the following pseudo code:

function geturl($url){
	// typical curl get url code
function request_cache($url, $dest_file, $timeout=604800){
	// Cache code, modified from yahoo developer code
	// Essentially, looks for cached requests and returns them
	//  Otherwise, make the request (then cache it)
$apikey = "IP"; // use your own ESV API key here
// build $cache_fullpath
// build $v cache file name
// init $options to default
foreach ($_GET as $key => $value){
	// find $cmd and build $options key=value string
$url = "$cmd?key=$apikey&$options";
$cache_timeout = 604800; // one week, in seconds
$response = request_cache($url, $cache_fullpath .
	"__" . $v, $cache_timeout);
if ($response === false) {
	die('Request failed');
echo $response;

Caching of requests is also a good idea in this case, and handled by the PHP proxy. This speeds things up a fair bit and reduces load on the ESV API site, but also helps to keep us below the daily request limit of 5000. It’s probably too short, but I’m caching these requests for a week. If you’re interested, I modified some code I found at Yahoo’s developer center. See above for pseudo code integration with PHP proxy.

Embedded link twiddling is accomplished in the some jQuery Javascript embedded in this post. The ESV API takes a link-url parameter that it uses to build links into the query method results, but since I didn’t want to go to the trouble in this example of building a full ESV search facility, I’m pointing the urls back to the ESV site. The code below is part of the success handler for the query .ajax() call. The first line dumps the HTML result into the #esvtext2 div, then the second line executes the inline function for each anchor tag within the results, monkeying with the href.

$("#esvtext2").html( data );
$("#esvtext2 a").each( function(){
	var link = $(this).attr("href");
	$(this).attr("href", link.replace("/esv/search/?",
		"") +
		String.fromCharCode(38) + "key=IP");

Dealing with ampersands in WordPress was causing severe headaches. Their online support insists that encoding them as & or & makes for perfectly wonderful URLs, but the ESV REST API did not agree and simply hated the resultant URLs I was sending its way. That is, until I used String.fromCharCode(38) to inject them into the URL string (see above).

Styling Scripture returned from the API was handled with a CSS style element embedded within this post. One of the things I really appreciate about the ESV API is that the results are so richly-styled. Footnotes (.footnote and .footnotes), chapter and verse numbers (.chapter-num and .verse-num), search results (.search-result), and the words of Christ (.woc) and all easily styled into something that looks pretty decent. (Yes, my CSS is a little junky. Any suggestions are welcome.)

<style type="text/css">
#esvtext1,#esvtext2,#esvtext3 { border:1px solid #ddd; padding:1em; }
.esv * { font-family:Georgia,serif;margin:0;padding:0; }
.esv h2 { font-size:1.3em;margin:0;padding:0; }
.esv h3 { font-size:1.1em;margin:0;padding:0; }
.esv .woc { color:red; }
.esv-text { font-size:1em; }
.esv .search-result{border-top:1px dashed #ccc;}
.esv .search-result-text-heading{font-weight:bold;}
.esv .search-term-1{background-color:#cc9;}
.esv .footnote a{ vertical-align:top;font-size:.8em;color:#777; }
.esv .footnotes { font-size:.8em; }

Scrolling the demos after the Ajax call was complete involved using the scrollTo jQuery plugin and simply doing a $.scrollTo( “#demo1”, 500 ) in the Ajax success handler, after sticking the results in the demo results div. This scrolls the corresponding demo intro to the top of the page. Nice.

        $("#esvtext1").html( data );
        $.scrollTo( "#demo1", 500 );

Cleaning up results of previous requests is accomplished with an easy jQuery line. In the success handler for each Ajax call, the first thing that happens is setting the results div for each of the other two demos to an empty string. The code above is the Ajax success handler for the first demo.


The examples below are three forms and an empty div. The three forms are bound to some jQuery (of course), which submits Ajax requests to a PHP proxy, then sticks the results in the empty div. For search results, embedded relative links are changed to point to the ESV site.

Demo 1: passageQuery

The sample below simply requests the passage from the ESV REST interface via the PHP proxy and pops the results into the results div. In the absence of specifying otherwise, we get it back in default format (HTML) with default options. The basic format for a passage is Book Verse:Chapter, but the API supports multiple passages, such as John 14:1-10,gen 1,is 2.

Verse: (ex: John 14:1-10)


Demo 2: query

The sample below is similar to the passageQuery except that you can send either a passage or a query word such as Messiah or fisher and get back the results. Paging is supported by the API (though not in this demo), so if the results span multiple pages you can request arbitrary pages as well (page=3, etc.). Other available options include sending your own search URL base for embedded links, number of results per page, and more.

Search terms: (ex: John 14:1-10, or samaritan, or dragon)


Demo 3: readingPlanQuery

The readingPlanQuery sample simply allows you to select from a few different plans and sends the request. The default date is today, and the default start date is January 1st, so we’ll just use those.



→ 1 CommentTags: Programming

jQuery For Firebug

March 5th, 2008 by bill · 6 Comments

»A Javascript bookmarklet that loads jQuery on-the-fly for Firebug debugging (or snooping) of sites where jQuery is not in use.

Load jQuery in a Firebug session.

When debugging or poking around in sites where jQuery is not already used, I had been pasting the following code into the multi-line FireBug console and hitting Run. This makes jQuery available for debugging, etc.


If you use Firebug, this is a handy way to make it even more powerful.

Make it a bookmarklet.

Of course, that whole process got old fast, so I created a bookmarklet to do the same thing, but save me from having to paste and run it in Firebug.

Drag this link: Load jQuery to your Firefox bookmark toolbar, then just click it to inject jQuery into a page.

The bookmarklet code:

javascript:void((function(){j=document.createElement("SCRIPT"); j.src=""; document.getElementsByTagName("HEAD")[0].appendChild(j);})())

And nicely formatted:


Then in Firebug, you can use jQuery selectors and so forth to debug the page. Obviously you’ll have to re-inject each time the page (or a new page) is loaded.

Great, I’ve reinvented the wheel.

Of course, a bit of searching revealed that John Resig himself posted a bookmarklet called jQuerify–almost identical to this one–linked in this Ajaxian article.

The bookmarklet envelopes are different, but both do a simple script injection into the DOM.

→ 6 CommentsTags: Javascript · Programming

Conditionally-Colored Flex Charts

February 20th, 2008 by grant · 20 Comments

»Color the columns in a chart depending on the data in Flex 2 or 3!

The charting in Flex is a pretty good tool, but there are a few small features that it would be really nice if it had. One of these would be the ability to have a chart series that changed colors depending on it’s value. Conditionally colored charts are something that a lot of us (and our customers) have grown accustomed to and we shouldn’t have to give it up with Flex. If you are like me you have probably been “googling” every variation of “flex colored charts” that you can think of. I couldn’t find an answer anywhere, but hopefully your search will end right here.

I wanted the ability to color charts based on their value, giving them red/yellow/green colors based on if they were lower/equal to/higher than a given value. The ability to use gradients instead of solid colors was also a requirement. I basically wanted to do something like this:

The columns below the line are red, on the line are yellow, and above the line are green. The need might arise to do more colors than this, so the code should be flexible

But almost as important as the final look of the graph itself was the ability to re-use the code without having to rewrite parts of it for each implementation. And I wanted to be able to use it in a flex application using only mxml, but still be able to use it in ActionScript if needed.

In the end I wanted to be able to define the threshold or goal as part of the data that the graph used and the code would color the columns based on that data (if it was greater than/less than/equal to). So the code to use the “coloring object” would look something like this (where we define High/Mid/Low colors):

  <mx:ColumnSeries xField="Month" yField="Income" displayName="Income2">
        <utils:ColoredSeriesItemRenderer valueField="Income" thresholdField="Goal">
              <mx:High><mx:Object gradient="[0x55dd55,0x005500]" /></mx:High>
              <mx:Mid><mx:Object gradient="[0xdddd55,0x555500]" /></mx:Mid>
              <mx:Low><mx:Object gradient="[0xdd5555,0x550000]" /></mx:Low>
  <mx:LineSeries xField="Month" yField="Goal" displayName="Goal"/>

But to be flexible I also wanted to be able to define the goal in the code, where I could give it as many ranges with associated colors as I wanted. This code would look something like this:

  <mx:ColumnSeries  xField="Month" yField="Income" displayName="Income1">
        <utils:ColoredSeriesItemRenderer valueField="Income">
              <mx:Object lowerLimit="280" color="0xFF0000" />
              <mx:Object lowerLimit="240" upperLimit="280" gradient="[0xFFFF00,0x55FF55]" />
              <mx:Object lowerLimit="220" upperLimit="240" gradient="[0x5555FF,0x000099]" />
              <mx:Object lowerLimit="200" upperLimit="220" gradient="[0x0000FF,0x00FF00,0xFF0000]" />
              <mx:Object upperLimit="200" color="0xFFFF00" />

Creating a custom item renderer allowed me (and now you too) to change the colors of the columns as they were being created. This works so much better than other solutions I saw (laying a filter on top of the columns) because it automatically changes when the column changes.

I created a new object out of it so you can just drop it in the directory where you keep your other library files (and change the package name if needed) and start using it. I have included it along with some sample files in a handy zip file for you… πŸ˜‰

I hope this saves you all the time it could have saved me (if I would have found something similar).

Download the object with two full examples that you can compile and try in a handy zip file.

If you are not familiar with including custom made objects into an mxml file then you’ll need to know about packages. In the sample code I used a package name of “utils”, which means that the actionscript object is defined with an “utils” package declaration and is in an “utils” subdirectory. Also, the example files will require you to define the xml namespace in the Application tag. This is done by adding something like this: xmlns:utils=”utils.*”

→ 20 CommentsTags: Flex · Free · Programming · Tips

New Gmail Chat Smilies

December 8th, 2007 by bill · 311 Comments

»With the recent update to Gmail chat, Google has added (both round and squared) graphic emoticons. What's more, some new hidden smilies are showing up.

Google Adds New Smilies to Gmail Chat

Update: For a complete list of Gmail chat emoticons, including the new category four smileys and the new robot and poo smileys see Gmail Chat Smileys.

With the recent update to Gmail chat, Google has added (both round and squared) graphic smilies. What’s more, some new hidden smilies are showing up.

Old-timey smilies are still the default.

The standard old-timey animated text smilies are still very popular amongst quite a few Gmail users, and are still the default set. Now you can view them in the smiley popup window (accessible from the lower right corner of the chat window).

Of course this window does not show the hidden smilies, which are detailed below.

New round and square faces are now available.

Now, however, you can switch to rounded yellow and squared yellow smiley faces by clicking the appropriate tab in the smiley popup window.

What do you think of these new smilies? Twelve standard emoticons in three flavors. Wow. Kind of underwhelming.

What a fancy smiley interface you have.

Popups. Tabs. Well, the new smilies do add a bit of color to chat sessions, but they feel kind of limited compared to, say, Yahoo emoticons. The tabbed popup interface seems a bit complicated just to select from the twelve standard emoticons.

Along those lines, the difference between the original faces and the round yellow faces is pretty stark, but between yellow round and yellow square is awfully small. Really, why bother? Has there been a serious clamoring for square smiley faces? It would be better to include Japanese smilies (face marks), like (-_-).

Hidden Gmail Chat Emoticons

Though sporting such a limited set of standard smilies, Gmail chat redeems itself by including some hidden emoticons. A slew of them turned up in comments to our Gmail Chat Fun article from last year.

The original hidden emoticons are still there. The equalizers ( =/ =( =p ), wince ( >.< ), the monkey ( :(|) ), the devil ( }:-) ), the heart (<3 ), and the ever-popular more cowbell ( +/’\ ).

But now some new hidden emoticons are showing up. The heart is becomes a broken heart if you put a slash through it ( </3 ). The kiss face is here ( :* ) as well as an alternate ( 😑 ). And, oddly enough, a crab has appeared ( V.v.V ).

Update: as ev noted in the comments, there is also a pig. :(:)

Update 2: and Crystal points out a mustached man. :{

Update 3: added the alternate kiss face. 😑

Update 4: added the robot and poo smileys. [:|] and ~@~

All of the hidden emoticons have their filled-in versions as well. Check out that monkey.

Have you found any others? If so, post them here.

Suggestions For The Gmail Chat Developers

First up, the hidden emoticons. You have the devil, so why not the angel or halo emoticon? O:) Really, it only seems right. Also, the chicken. ~:> Why on earth is there no chicken in the menagerie? You have monkeys and crabs, but no chickens?

Second, there is a UI design issue here. Take a look at that smiley interface. It’s nice and gets the job done, but something about it is just out of whack. You have three tabs on a popup panel on the popup chat window, but a dearth of smilies grace each tab. However, the big problem here is the similarity between the round and square faces. It’s like you give us the option of a pogo stick or a light gray rocket or a dull silver rocket. How about original smilies, yellow round smilies, and Japanese facemarks? Now that would be cool. And it would balance the choices a bit.

Overall, a welcome upgrade

The new smilies are welcome, the gee-whiz interface is okay — but it makes us a bit hungry for more tweaks and enhancements.

→ 311 CommentsTags: Google · Tips

Show Password Fields

November 21st, 2007 by bill · 16 Comments

»Here is an updated javascript bookmarklet to display password field values in the clear.

Update: had a case-sensitive bug in the code. Fixed now.

A simple Javascript bookmarklet shows password field values.

In form fields where a password is entered, it is usually obscured by asterisks or bullets to prevent someone from seeing what you type. This is a great feature but where a long password is being typed or a remembered password is filled in by the browser, you might want to actually see the actual password itself.

Show Password Bookmarklet

Here is a bookmarklet (a snippet of Javascript that can be pasted into the address bar of your web browser), that will show any passwords on a page that contain password form fields.

javascript: (function() { var s="",F=document.forms,j,f,i;for (j=0; j&lt;F.length; ++j) { f=F[j]; for (i=0; i&lt;f.length; ++i) { if (f[i].type.toLowerCase() == "password") s += f[i].value + "\n"; } } if (s) alert("Password fields:\n\n" + s); else alert("No password fields on this page."); } )();

Just paste the above javascript into the address bar of your browser and press Enter, or create a bookmarklet link with the javascript as the URL. On this page you’ll see the password for the mock sign on form below.

User Name: Password:

This is nothing new.

This or similar javascript has been around for a while. This particular one was on (via etc.), but it needed to be fixed for some forms where fields have a null type property (an example is the sign on form for LinkedIn). The fix was adding f[i].type && to the if statement inside the inner for loop.

Formatted Javascript Code

If you’re interested in what is happening to make this work the formatted code is below.

Essentially, the variable F is set to the collection of forms on the page, which is looped through, examining every input field in each form testing if it’s of type “password.” If it is a “password” field, its value is appended to the string variable s. Once all the input fields in all the forms on the page have been sniffed, the string value is displayed in an alert.

(function() {
  var s="", F=document.forms,j,f,i;
  for (j=0; j&lt;F.length; ++j) {
      f = F[j];
      for (i=0; i&lt;f.length; ++i) {
          if (f[i].type.toLowerCase() == "password") s += f[i].value + "\n";
  if (s) alert("Password fields:\n\n" + s);
  else alert("No password fields on this page.");

But Firefox already…

And yes, we know Firefox will show you passwords in the clear with Tools/Options/Security/Show Passwords.

This javascript, however, is easier to access if you have created a bookmarklet for it, plus it works in other browsers.

→ 16 CommentsTags: Javascript · Programming · Security

jQuery 1.2

October 4th, 2007 by bill · 1 Comment

»jQuery gets a major upgrade with great new features, a couple small deprecations, and its own brand new UI library.

Major Updates To jQuery

John Resig and the jQuery team have been busy releasing jQuery 1.2 and a bugfix version 1.2.1.

jQuery Version 1.2

Version 1.2 is a major update to the popular Javascript library. Many new features were added and a few were removed, so updating existing code may be required to use the new release.

Features Removed

The jQuery team tries to keep the library as small as possible. Unfortunately that means some things have been deprecated or moved into plugins.

A backwards compatibility plugin is available to assist or delay any changes needed to use the new version.

Several XPath selector features were removed, including Descendant (ex. $(“div//p”) ), Child (ex. $(“div/p”) ), Parent (ex. $(“p/../div”) ), Contains Predicate (ex. $(“div[p]”) ), and Attribute (ex. $(“a[@href]”) ). It is recommended that instead of these selectors, the corresponding CSS or other selectors be used. Or use the XPath Compatibility Plugin.

The parametized clone() method (ex. $(“div”).clone(false) ) has been deprecated. .clone() is still supported, but instead of using the false argument, use .clone().empty() instead.

DOM Traversal methods .eq(), .lt(), .gt(), and .contains() have been deprecated in favor of using .slice() and .filter(). Note that 1.2.1 brings back .eq().

The Ajax methods .loadIfModifed() and .getIfModifed() are deprecated. You should use the $.ajax() method with the “ifModified:true” option instead. Also, $.ajaxTimeout is now deprecated in favor of $.ajaxSetup(). .evalScripts() has been removed as it is no longer necessary; all scripts in HTML strings are automatically evaluated when injected into the document.

Features Added

Now for the good stuff.

Besides the new XPath Compatibility Plugin mentioned above, the core now supports :has(selector), :header, and :animated.

New DOM traversal methods include .map(), .prevAll() and .nextAll(), .slice(), .hasClass(), .andSelf(), and .contents().

New DOM manipulation methods include .wrapInner() and .wrapAll(), .replaceWith() and .replaceAll(), and .clone(true) Event Cloning.

New .offset(), .height() and .width() CSS methods were added.

Ajax support was improved with support for partial .load(), Cross-Domain getScript(), JSONP, and disabling caching. Also .serialize() has been reworked.

New effects features support .stop(), % and em animations, color animations, relative animations, queue control, the :animated selector, and custom animation types with the step Function.

Namespaced Events and .triggerHandler() are also new.


jQuery now has its own UI, including draggables, droppables, resizables, shadows, sliders, sortables, tabs, accordians, selectables, trees, and modals.

Well, it has a few bugs, but it really is nice. And, of course, they’re hard at work squashing the bugs as fast as possible.

jQuery Version 1.2.1

A bugfix release on September 16th fixes some bugs and adjusts the API in two places.

Due to overwhelming demand, .eq() has returned in version 1.2.1. Also, a problem with the relative animation API has been fixed.

Excellent Upgrade

This is a major step forward for jQuery. The loss of XPath and a few features in the core is a slightly bitter pill, but plugins help with this. The additional features are great, and the new UI library is welcome.

→ 1 CommentTags: Javascript

In-Cell Spreadsheet Graphs

September 23rd, 2007 by bill · 3 Comments

»How to create simple in-cell graphs in Excel, IBM Lotus Symphony, OpenOffice, and Google Spreadsheets.

Edward Tufte has promoted the use of inline, word-sized graphs called sparklines to communicate data more effectively, giving rise to several methods of creating inline graphs within documents, including spreadsheets.

This article describes one very simple way of incorporating dynamic bar graphs in your spreadsheets. It works in Excel, IBM Lotus Symphony, OpenOffice, and Google’s online spreadsheet application.

The Basic In-Cell Graph

Find the data cell you want to graph. This will probably be the first in a vertical series. It will be the reference used within the formula.

Say, for example, you want to graph the value in cell B2 (or B2-B6). Enter the following formula into cell C2 (or wherever you want the graph).

=REPT( "|", B2 )

For Symphony and OpenOffice, function parameters are separated by semi-colons instead of commas. The examples in this article use commas, so just change them to semi-colons and it should work okay. E.g. Instead of the above formula, use the following for Symphony and OpenOffice.

=REPT( "|"; B2 )

The next step is a matter of taste, but you will probably want to change the font of the graph to something like Arial 8 point. Since we are using the vertical bar as our graph element, a smaller font makes for a tighter-looking graph.

Then, depending on your numeric range of values and the general amount of space you want the graph to use, you will probably want to divide the cell value by some constant. This is entirely up to you and your needs. If your value range is between 0 and 20,000, you might want to divide by 500 or 1000; whereas if your values run from 0 to 100, dividing by 5 would make more sense.

=REPT( "|", B2 / 5 )

Now, copy the formula down from C3 through C6. (After each change to the formula, you’ll want to copy the change throughout your graph.)

In fact, you can use a formula to calculate the divider. Divide the maximum value of the graph by something like 40, then divide the cell value by that.

=REPT( "|", B7 / ( 10000 / 40 ))

Negative Values

Of course, you will probably want to be able to handle negative values as well. By simply spreading your graph across two cells and using the IF() and ABS() functions, negative values are no problem.

Let’s say you have a range of numbers in cells F2 to F6 and you want a graph in cells G2 and H2 down to G6 and H6. In G2, enter the following formula to handle negative numbers.

=IF( F2 < 0, REPT( "|", ABS( F2 / 5 )), "" )

And in H2, the following will take care of positive values.

=IF( F2 > 0, REPT( "|", ABS( F2 / 5 )), "" )

Next, format the text in G2 to be right-aligned and red.

Select G2 and H2, then copy them down to G3:H3 through G6:H6.

Just A Little Fancy

You can experiment with various fonts, sizes, colors, etc. to get the graphs you want. One little tweak that’s useful is using the CONCATENATE() function to mark the value at the graph’s endpoint.

As an example, the graph below uses a vertical bar for the value and hyphens for the graph bar itself. Note, that since the hyphen takes more horizontal space than a vertical bar, we use a larger divisor (seven, in this case) to keep the graph at about the same scale.

=CONCATENATE( REPT( "-", B2 / 7 ), "|" )

And the negative/positive graph uses a lower-case “o” for the value and the vertical bar for the bar.


=IF( F2 < 0, CONCATENATE( "o", REPT( "|", ABS( F2 / 5 ))), "" )


=IF( F2 > 0, CONCATENATE( REPT( "|", ABS( F2 / 5 )), "o" ), "" )

Note that the parameters to the CONCATENATE() function are reversed for negative and positive.

Also, as in the above graph, green sometimes looks nice for positive values.

What Else Can We Do With Graphs?

While much more complex graphing is available with your spreadsheet’s built-in graphing features, this technique provides simple graphs to enhance a boring block of numbers in a spreadsheet. It provides visual cues to the relative values of the numbers and helps to put things in context.

There is more that can be done with graphs like this. In a future article, we’ll look at similar lightweight graphing techniques. Do you have suggestions or any graphing tips you use? If so, please post them in the comments.

→ 3 CommentsTags: Software · Tips