Excel 2010 – Sparkline Charts

Excel 2010 has a new feature called Sparkline charts that allows you to place a mini-chart inside a cell on your sheet. There are 3 type of sparkline charts: line, column and win/loss. The line and bar charts are very useful to show you a trend about your information while win/loss charts show you positive or negative numbers across a horizontal axis, represented by squares that show positive values if they are above the horizontal axis or negative if they appear below.
To create a sparkline chart, just click on the cell where you want your chart to appear. Click on the Insert tab on your menu bar and click on the type of sparkline chart that you want. A “Create Sparklines” dialog window will appear. The cell that you’ve selected on your sheet will already appear on the field “Location Range” of the dialog window. Click on the “Data Range” field and select on your sheet the range that you want to use on your chart. The range will appear on the “Data Range” field. Click OK and you’re done.
On this picture, you can see on cell F3 a line sparkline chart and on cell G3 a bar sparkline chart.

sparklines
On the next picture, you can see on cell F3 an example of the win/loss sparkline chart.

sparklines2

Create a Self-Expanding Chart

This happens very often with most of the Excel users. You have a set of data that you use to create a chart. If you latter want to add more data to the chart, you need to redefine your chart data series, unless if you didn’t add rows or columns in the middle of your original data where in this case, Excel will assume them on your chart.
To avoid this, just create your chart as usual. Then select any cell inside the range of data that you used to create the chart. Go to Insert-Tables to convert the range of data to a table. This is all you need to do for Excel to recognize the data as a table and the data added to the table can be reflected on the chart without the need to redefine your source.

REPT() - Repeat Function

The REPT() function is unknown for most of Excel users. This function repeats the given text of characters a given number of times. The syntax is like this:

REPT(text,number_times)

text is the text string that you want to repeat. number_times is the number of times you want to repeat the text on the cell.

Example:

=REPT("*",3) repeats the "*" character 3 times on the cell. The result will be "***".

A good use of this function is to make cell "bar" charts that will illustrate the dimension of the number on an adjacent value. Here's how it would look:

REPT
On this example, I use the character "■" from the Character Map and used the function like this:
=REPT("■",C3/1000)

I divided the value by 1000 so that the size of the "bar" stays at a reasonable size.