TIP: Make Excel in-cell graphs look like bars (2004/2008)

The “bar” graphs in the tip In-cell bar graphs for Excel aren’t very bar-ish, seeing as how they are a series of bullets. Even using the | character (Shift-\) doesn’t work, since the lines won’t touch each other. But some fonts have a “block” character that works perfectly to create bars: all you have to know is how to insert it into an Excel formula with the Character Palette.

If the Character Palette is not in your Input menu (at the right side of your menu bar, with a flag icon)—or if you don’t have an Input menu in the menu bar, check the tip Put the Character Palette in the Input menu. (Note that Excel is one of the programs that doesn’t provide a command to open the Character Palette, so you’ll have to set up the Input menu for this.)

As you type your REPT formula ( =REPT(“•”,A1) ), insert the block character for the first argument instead of a bullet:

With the formula active and the blinking text cursor ready to type the block character, open the Character Palette from the Input menu. At the bottom of the Palette, type 2588 into the Find field; this is the Unicode number for the block character. If a menu pops up to give you a choice between an Asian character and Unicode, click Unicode, and press Return.

Find the block character by typing its Unicode ID in the Character Palette

Find the block character by typing its Unicode ID in the Character Palette

Click on the “character well”—the big sample of the character in the Character Info area of Character Palette. This inserts the character wherever the blinking text cursor is waiting. Finish typing the formula if it’s incomplete.

That’s all, really—if you haven’t formatted the cell for a font that contains the block character, the Mac does it for you—not by formatting the cell, but by changing the font for that single character. (Lots more about that kind of stuff in my Take Control of Fonts in Leopard ebook.) If your spreadsheet is going to be viewed on a Windows machine, format the cells with the blocks for Arial, a common cross-platform font that has the block character.

A better-looking bar graph with the block character. The cell was formatted for blue text.

A better-looking bar graph with the block character. The cell was formatted for blue text.

Coming soon: the bar graph with bars shown relative to a goal:

An even better bar graph

An even better bar graph

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: