Excel Worksheet with Inline Charts & Graphs (Part 1)

Most people know that Excel has a solid chart system that can draw variety of chart types ranging from simple pie chart to a more complex 3D Radar chart and for each chart types there are tens if not hundreds configuration options. Most people also know that these charts are auxiliary to the Excel worksheet itself. One can overlay a chart on top of a worksheet but one can not embed a chart into one of the cells. It usually looks like the following:

2013-10 Excel Worksheets with Inline Charts & Graphs (Part 1).1

Note that the bar chart is somewhat disconnected from the source data and the chart is an overlay on top of the worksheet. Well, there are better options to do this…

The first way to embed a bar chart in a cell is the old way. It is really a workaround and it is by dynamically generating a string that will look like a chart. We can do that with the function REPT to repeat a character such as “|” and generate the needed bar. This is something I saw first at the following place:

http://omberg.wordpress.com/excel/excel-guides/how-to-create-an-in-cell-chart/

Let’s assume that cell D2 contains a value and we want cell E2 to include a graphical representation of that value, then we can use the function:

=REPT(“|”,D2)&” “&D2

Which means: Repeat the “|” character D2 times and at the end add the string representation of D2.

If we repeat that calculation for every cell in the D column we will have something like this:

2013-10 Excel Worksheets with Inline Charts & Graphs (Part 1).2

I cheated a little bit as I used conditional formatting for the colors and used one of the narrow fonts like “Arial Narrow”.

Now let’s continue. Replace “|” with the character “g” but use the funky font Webdings. Also reduce the font size to 4 or so and we get something that looks closer to charts:

2013-10 Excel Worksheets with Inline Charts & Graphs (Part 1).3

That is nice but we can do better. In Excel 2010 there is a new thing called Sparklines. I will talk about this next time…

Advertisements

Author: dave

Consider myself kid in soul and naive by choice. I am interested in people, technology and business and thrive when they all work together. My favorite quote and motto is that “You can fool some people some times but you cant fool all the people all the time” ― Bob Marley

2 thoughts on “Excel Worksheet with Inline Charts & Graphs (Part 1)”

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