7
$\begingroup$

I am working for a big company with a restrictive internet policy and Excel-addicted colleagues. I am currently working on evolution of market correlations which implies some statistics, data analysis, clustering, data visualization ... From what I have seen on the internet it's not a good idea to do it in Excel. (see here a general study: Excel as a statistics workbench)

After a struggle of 2 weeks, I have finally got from IT a working version of R and some interesting packages. My market data are stocked in a .txt file, I work on it with R and create a results.txt file, then I load the results.txt file in Excel and I plot what my boss wants.

I admit that Excel is useful for manipulating a lot of data sets and graphs at the same place. It's the only good point compared to R for what I want to do. I think my cheap .txt solution to do calculations in R is correct and simple ... (for the anecdote things like Rexcel to connect R and Excel are forbidden where I work - don't ask why - so I have tried a macro which create a .bat to launch R and do the calculation; too complex for my colleagues)

But for data visualization Excel is very poor; I really miss some graphs I have in R. Dendograms, boxplots, histograms, correlation circles, summarized correlations, and heatmaps are very interesting for me, but not available. So my question is how to get them in Excel ? (Remember the strict internet policy; I can't download any add-ins). Is there a (easy) way to plot complex things with macro or workbooks ? Do you have some sources?

$\endgroup$
  • 2
    $\begingroup$ Great answer from @whuber. But I don't understand why you don't produce the graphics in R too? $\endgroup$ – Peter Ellis Mar 29 '13 at 0:21
  • $\begingroup$ I can only give excel documents to my colleagues... $\endgroup$ – were_cat Mar 29 '13 at 12:24
  • 1
    $\begingroup$ What I meant was, why not PowerPoint or Word with PNGs generated by R inserted into it? Or even better, HTML with PNGs embedded in them by knitr (doesn't need any new software other than R packages if you are ok with the end result in HTML) along with any tables and explanatory text you want to incorporate. $\endgroup$ – Peter Ellis Mar 29 '13 at 20:44
  • $\begingroup$ you can use conditional coloring functionality, you do not need add ins etc. There some other tricks too see the blog (excelgraphs.blogspot.com/2013/03/…). $\endgroup$ – John Apr 4 '13 at 16:16
  • $\begingroup$ Some very clever answers. But is it really worth re-inventing the wheel? $\endgroup$ – Scortchi Apr 4 '13 at 22:58
11
$\begingroup$

The short answer is no, there is no easy way to create most of the graphics you mention. But in any graphics environment where you can draw line segments (such as the pen plotter drivers from the 60's, 70's, and 80's), you can construct workable visualizations. So one method is to focus on joined scatterplots (which is the principal mechanism for creating line segments in Excel). Writing macros can help, if that's allowed.

I haven't gone far in this direction, but some years ago created spreadsheets with side-by-side box-and-whisker plots, showing this approach is feasible.

AL batting averages 2004

This graphic, summarizing individual batting averages in baseball teams, was created by copying and arranging summaries of the team batting averages as needed to allow them to be plotted as scatterplots. For this to happen, you need to work out the $(x,y)$ coordinates of the endpoints of each line segment you want to appear in the plot, arrange those in pairs of rows of columns, and add them as new series to the graphic. Here, to illustrate, is a portion of the worksheet that drives this graphic:

Spreadsheet extract

(Original data are shown in blue; everything else is calculated.)

For instance, the left side of the "Red Sox" boxplot (at the far right) is given by the coordinates in columns U:V, the right side in W:X, the middle bar (showing the median) in M3:M5 and O3:O5, etc. In all, this graphic displays $98$ series of data: seven series per boxplot. As I recall (this is from a few years ago), some manual editing was required to format the names of the outlying players, but otherwise the boxplots were produced automatically using a (very crude) macro. This macro copied the summary data (seen in columns I:L) into the requisite columns. Another macro systematically set the graphics styles for the series, and so on. Little expertise in VBA is needed to write such macros: you just "record" what you're doing in order to create one basic element of your graphic and then edit the resulting macro to make its specific cell references into relative cell references.

I don't recommend any of this and anticipate never doing it again, but I can attest that the process of creating statistical graphics in such a primitive environment is educational.

$\endgroup$
8
$\begingroup$

As the way I've found to plot heatmaps in graphs is very simple and can be used to plot almost everything, I think it will interet some people. For curious people (and those who have time to lose).

My idea is to create a "screen" pixel by pixel.

Create a table with the same size of what you want to plot, filled with ones. Plot it as stacked columns.

screen

reduce the gap between columns to 0. (right click on the plot area, format, gap width to 0)

screen

Use a macro to change the color of each "pixel".

Sub Macro6()

Application.ScreenUpdating = False
ActiveSheet.ChartObjects("Your table").Activate

   For i = 1 To row
   For j = 1 To col

   red = Int(WorksheetFunction.Max(ActiveSheet.Cells(j + col0, row0 + i), 0) * 255)
'' positive value in red
   blue = Int(-WorksheetFunction.Min(ActiveSheet.Cells(j + col0, row0 + i),  * 255)
'' negative value in blue  
 ActiveChart.SeriesCollection(i).Points(j).Interior.Color = RGB(255 - blue, 255 - red -   blue, 255 - red)
'' gives each pixel a colour based on the value in a cells in a table starting in row0,col0

   Next
   Next

   Application.ScreenUpdating = True

End Sub

and obtain:

Heatmap

Now, do it again with bigger data (33 * 2681):

TADAAA !

My results

Try to enjoy and save it before the crash:

Resource consuming

It's usefull to plot an image and works well with few data (no problems with 33*33), but it will slow down your computer with too much datas. As you can see I did't had the patience to works on the details with such a slow computer.

If someone can test the method with a good computer, it would be interesting to know if it can be used with real life computer.

And now, dendograms !

$\endgroup$
  • 1
    $\begingroup$ +1 I have used this with matrices up to about 10,000 elements, but after that things bog down too much. Excel can even contour these data (it does an awful job) and produce pseudo-3D surface plots (yet another awful job). $\endgroup$ – whuber Mar 29 '13 at 16:39
  • 1
    $\begingroup$ I've done something in the past similar, that is coloring cells, to produce trees (similar to the sort you would need for dendrograms). See page 7 here. I've been in this boat before, although not quite as strict (some upper management only wanted Excel graphs because they liked the way Excel graphs looked). Good luck. $\endgroup$ – Andy W Mar 29 '13 at 17:11
  • $\begingroup$ I will try to use the fatc that dendograms looks like small columns recovering medium columns recovering big ones... seems doable. But Tuesday :D $\endgroup$ – were_cat Mar 29 '13 at 17:39
  • $\begingroup$ @Andy W Your accomplishment is substantial: not only have you produced good visualizations, the dendrogram doesn't even look like it was made with Excel! $\endgroup$ – whuber Mar 30 '13 at 15:14
6
$\begingroup$

Jon Peltier has a really great site with a lot of guides on how to bend Excel to make some more exotic charts. For example, Box plots and Heat maps. You don't need his plug-in to execute a lot of them.

Excel can't do a lot of things, and sometimes the things that you can make it do are hard to maintain and update, but as in your case you sometimes just don't have a choice.

$\endgroup$
  • $\begingroup$ Reaction of my manager about Jon Peltier Heat maps: "Nice, but it's a coloured workbooks, we need a graph"... $\endgroup$ – were_cat Mar 29 '13 at 9:45
  • $\begingroup$ If you can deal with a static image, you can select the range, then copy and paste special as a metafile or an image into Powerpoint/Word. Otherwise, probably not doable. $\endgroup$ – hgcrpd Mar 29 '13 at 11:24
  • $\begingroup$ Static image not alowed... but I've found a way to do it, i will post it after some finalisation work ! $\endgroup$ – were_cat Mar 29 '13 at 12:29
-1
$\begingroup$

There a few tools out there to create treemaps in Excel. Try Treemap or Sparklines

$\endgroup$
  • $\begingroup$ Thanks, but the title is explicit. I am not looking for add-ins. $\endgroup$ – were_cat May 2 '13 at 13:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.