Excel on steroids: NodeXL and PowerPivot

By Hilary Niles
@nilesmedia

Excel has two free, plug-ins for Windows users that can dramatically help reporters: NodeXL and PowerPivot. (Sorry Mac devotees, nothing for us.)

Tom Torok, CAR editor of The New York Times, and Peter Aldhous, New Scientist’s San Francisco Bureau Chief demoed the two plugins at the 2012 CAR Conference.

NodeXL is a network analysis tool compatible with Windows 2007 and 2010 that allows you to visualize, quantify and otherwise describe connections between people, organizations, or really anything. Because of its broad applicability, Aldhous chose to not refer to it as “social network analysis,” but you can think of it that way, too.

You can get as complicated as you’d like with the software, but to get started all you need to know is what you want to measure: who’s connected to whom. You can create your own database of connections or import a data set and set the program to filtering it.

What you get might look like a giant hairball at first, Aldhous admits. But there’s an app for that. Several different graphing algorithms are built into NodeXL, so you don’t have to know how to write them. Just choose one and see how it looks.

This gets to one of Aldhous’s main points about this type of analysis: it’s exploratory. There is no one single right way to map the voting pattern of the United States Senate, for example — which he’s done. Whatever you get the graph to look like is based on a series of calculated decisions you make about what you want to show from it. 

The best advice is to approach the data from different directions and apply different algorithms until you see it clearly — or until you see something new. A lot of the work involved in getting the data into shape for network analysis is valuable in itself, but Aldhous has said that he still finds new patterns in networks when he uses this tool. His online tutorial can be found here.

PowerPivot doesn’t so much introduce new functionality as it does a new means of achieving it. With this add-in — compatible only with Windows 2010, nothing earlier — you can actually join tables in Excel, and work with much larger datasets — even millions of rows. And it all works really fast.

Torok summed up the size capacity this way: Your data limitations are really determined by the amount of RAM you have. The joining function is somewhat more limited, as it can only be applied to inner joins.

One caution: When working in PowerPivot, only use the PowerPivot tab of the ribbon. Be careful not to slip into the other Excel tabs, as the calculations could compromise your data.

Torok recommends the online documentation and tutorials from Excel to help get you started. The add-in and demos from Microsoft can be found here. 

Hilary Niles is a graduate student at the University of Missouri School of Journalism.