Home
Blog


Better Excel Charts



Excel charts are one of the most common tools for visualizing and presenting data. While they're fine for basic analysis, you can make much better graphs with matplotlib or another high-caliber graphing tool. You can use DataNitro to output pylab charts directly into Excel, avoiding Excel charts altogether.

Plotting a lot of data

Here's what a decaying sine curve looks like in python:

Decaying since curve plotted in Excel with Python
f(t) = sin(2*pi*t)e^(-t/100)

And here's the same plot in Excel:

Decaying sine curve plotted in Excel

Excel's rendering engine makes the graph line unreadable; this is unacceptable for important data.

Setting up the chart is just as easy in Python as in Excel. If your data is in columns A and B, here's one way to do it:

import numpy as np
import nitroplot

active_sheet("sine")
t, s = Cell("A1").vertical, Cell("B1").vertical

nitroplot.plot(t, s)
nitroplot.graph()

While you may not graph a sine curve every day, nice graphs are also helpful in more common applications. For example, here's three years worth of stock data for Apple, with the Python plot on the left and the Excel chart on the right:

Comparing Excel charts to Python charts
Stock data, plotted in Python on the left and in Excel on the right

Even with a basic line chart, the Python plot is clearer. If you start adding more information to the chart, the Excel chart can quickly become unreadable, while the Python plot doesn't.

Advanced Charts

Another advantage of sophisticated plotting is the range of plots you can use. While line charts are more or less the limit of what Excel can do, Python can do much more. For example, here's a graph of a mandelbrot fractal:

Fractal chart in Excel
The mandelbrot fractal, plotted in Python

There's no way to make something like this in Excel. You can handle the calculations and plotting in Python, with key parameters stored in Excel for easy updating:

from numpy import *
import nitroplot

def mandelbrot( h,w, maxit=20, d = 2 ):
    y,x = ogrid[ -1.4:1.4:h*1j, -2:0.8:w*1j ]
    c = x+y*1j
    z = c
    divtime = maxit + zeros(z.shape, dtype=int)

    for i in xrange(maxit):
        z  = z**d + c
        diverge = z*conj(z) > 2**2            # who is diverging
        div_now = diverge & (divtime==maxit)  # who is diverging now
        divtime[div_now] = i                  # note when
        z[diverge] = 2                        # avoid diverging too much

    return divtime

active_sheet("fractal")
nitroplot.imshow(mandelbrot(400, 400, d = Cell("B1").value))
nitroplot.graph()

This makes it easy to create interactive visualizations:

Interactive charts in Excel
Interactive charting

Do you have data stuck in Excel that's hard to chart natively? Give this a try!
(You can download the examples from this post here.)