Excel: Charting with Formulas

August 14th, 2006

I picked this tip up from a coworker, who found it at Lifehacker.com.

This allows you to create in-cell bar or point charts using a simple formula instead of Excel's normal chart features. In my mind, the problem with Excel's charts is that they do not integrate well with the worksheet. In most cases I have a large data table and I am giving the user the option to chart a couple of datasets from the larger table. I want the entire table displayed, and the chart displayed so that the columns in the bar chart align perfectly with the data in the table. So you end up spending too much time getting the chart aligned, only to have it derail as soon as you change something in the chart.

Using the REPT formula solves this. REPT just repeats a character a given number of times. Using a font like Webdings you can thus create bar charts, or simple point charts. Using Conditional Formatting allows you to color the bars based on the underlying value.

Click here to open the example Excel file.