Charting in a cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Does anybody have some information in creating a line chart that resides
within a cell that reflects the pattern of a row or column of numbers?
Someone at work referred to such a thing as a "spark line". Any VBA out there
to help with something like that ?
 
Put some numbers in A1:A6 (1,5,8,10,15,12)
In B1 enter =REPT("-",A1)
Copy down to B6
Adjust column width of B as needed
Try other symbols in place of "-"
That's how we did it in Fortran in the 60's
OR get Excel 2007
best wishes
 
Put some numbers in A1:A6 (1,5,8,10,15,12)
In B1 enter =REPT("-",A1)
Copy down to B6
Adjust column width of B as needed
Try other symbols in place of "-"
That's how we did it in Fortran in the 60's
OR get Excel 2007
Click to expand...

Bernard, that's not a sparkline. That's one number in a cell, expressed
as a column of a certain length. A sparkline is a series of numbers,
expressed as a tiny line or bar graph.

I have implemented sparklines in Excel, by simply creating a minimal
line or bar chart as an embedded frame, and dragging its corners into a
single cell while pressing the Alt key, to snap the frame to the cell
corners. The frame can then be moved, sized and copied with the cell,
although you'll have to change the data series it refers to by hand.

I find it's difficult to shrink the entire chart into one regular height
cell, so I double all the cell heights up and use 20 point text. If you
like, you can just snap it to two cells instead, but a true sparkline
should be the height of a single line of text.

Bissantz & Company GmbH offer a sparkline add-in for Excel called
SparkMaker:

http://www.bissantz.de/sparklines/sparkmaker.asp

This product takes a different approach: Bissantz have created a custom
font with bars, pies, dots and lines that, when used with their user
defined functions, creates a sparkline in the cell where the function is
entered. bonavistasystems.com also has a product called Microcharts, but
I haven't evaluated it.
 
Thanks for the suggestions. Seems to be a blog thread in Daily Dose of Excel
that gets me part of the way there through a VBA function,
 
Dave -

There are a few of these threads in Daily Dose. I had a little fun with it,
but haven't gotten anything really useful yet.

The third party sparkline utility I tried (Bissantz, but there are others)
led to problems with phantom VB projects, that is, projects remained in the
VB Editor after the parent workbook was closed. The only thing that removed
these phantom VB projects was removing the utility.

- Jon
 
Try the character "g"
as in
=REPT("g",A1/x)
where x is used to scale the lines

Then use the Webdings font for a bar...
 
Fernando -

This is EXCELLENT!

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


fernando cinquegrani said:
[Dave_AD] scrive in
http://www.google.it/groups?threadm=E036AE99-0F69-45A3-9999-44385B682240@microsoft.com
Does anybody have some information in creating a line chart that
resides within a cell that reflects the pattern of a row or column of
numbers? Someone at work referred to such a thing as a "spark line".
Any VBA out there to help with something like that ?
Click to expand...

http://www.prodomosua.eu/zips/sparklines.xls
(press F9)
.f
Click to expand...
 
Copy the range of cells under the chart
Hold Shift, select the Edit menu, select Paste Linked Picture
Move and resize the pasted picture over the desired cell.

The pasted linked picture updates automatically thanks to the link.

A quick note to Excel 2007 users. If you use Print or Print Preview of a
range containing linked pictures of a chart, the linked picture and the
original chart will be corrupted. This bug has been filed with Microsoft.

- Jon
 
Back
Top