Posts Tagged ‘excel’

NodeXL: Network Visualizations in Excel (Visual Business Intelligence)

Thursday, February 25th, 2010

[Editor's note: Visualizing complex connection topologies is made easier with a new plugin for Microsoft Excel. Now someone needs to port it to Flash ActionScript 3!]

Republished from Visual Business Intelligence.

This blog entry was written by Bryan Pierce of Perceptual Edge.

The chances are good that you’ve seen network visualizations before, such as the one below in which the circles and octagons represent large U.S. companies and each connecting line represents a person who sits on the board of both companies.

(This image was created by Toby Segaran: http://blog.kiwitobes.com/?p=57)

While these types of graphs have become more common in recent years, there’s still a good chance that you’ve never created one yourself. This is because, traditionally, to create network visualizations, you’ve either needed specialized (and often unwieldy) network visualization software or a full-featured (and usually expensive) visualization suite. That’s no longer the case. A team of contributors from several universities and research groups, including the University of Maryland and Microsoft Research, recently released NodeXL, a free add-in for Excel that allows you to create and analyze network visualizations.

Using NodeXL you can import data from a variety of file formats and it will automatically lay out the visualization for you, using one of twelve built-in layout algorithms.

Continue reading at Visual Business Intelligence . . .

VBA Field Calculator Tips in ArcMap (Kelso)

Tuesday, November 24th, 2009

fieldcalculator2I’ve been relying on the Advanced logic options in ArcMap’s Field Calculator to wrap up Natural Earth Vector. Because of the diacritical (accent) marks present on many placenames around the world, care must be taken to ensure they don’t get corrupted.

With basic SHP files, I often edit thematic data in Excel and then join it back with the SHP. However, Excel mangles diacritical marks, especially if you’re going cross platform between the Mac and PC. Even when everything is setup right, ArcMap the get Info panel displays ? marks for many diacriticals. The attributes table view shows them correctly, though.

Much of the base data and name attributes are compiled in Adobe Illustrator and exported with MaPublisher using custom scripts I’ve written to take the name from the text that’s been grouped with the feature. This allows contributors who don’t have ArcGIS at home or in the office to take part in the project. Because we often have other attributes (maybe a second name, scale rank, type of feature), I use an “_” underscore character to concatenate these into one string. This string then needs to be parsed back into separate attribute columns.

Note: SHP files use Windows 1252 character encoding. If you’re on a Mac, change your MP export options from “System” to that. If you’re on a PC, you’re already good.

Splitting strings:

In VBA prelogic area (advanced checkmark on):

myString = [ColumnName]
myPosition = InStr(myString , “_”)
myLeft = myPosition – 1

myLen = Len(myString)
myRight = myLen – myPosition
country = Left( myString, myLeft )
provNumber = Right( myString, myRight )

In the field results area: provNumber or country.

Note: If the result is destined for a number formatted column, either caste provNumber as a number or use a temporary holding column that is string formatted, then rerun the field calculator on the number formatted column deriving the value from the temp field. ArcMap with auto-caste for you in that case.

After the jump: Find and replace, counting substrings, hit tests, and changing case.

(more…)

Microsoft to patent Tufte’s sparklines?! (Kottke)

Friday, November 20th, 2009

tufte_sparklines

[Editor’s note: This is total bullsh*t, the US Patent office should not be granting Microsoft patents for basic concepts that are already out in the market place as prior art and in fact invented / popularized by someone else, namely Edward Tufte. Shame on Microsoft and boo on lazy patent clerks! Thanks Melissa.]

Republished from Kottke and Tufte.

In an absurd twist, on their own Microsof blog, Tufte is credited as inventor:

For Excel 2010 we’ve implemented sparklines, “intense, simple, word-sized graphics”, as their inventor Edward Tufte describes them in his book Beautiful Evidence.

Another way to read their patent application is regarding not just placement of a sparkline in an Excel spreadsheet but any digital document. Also a bizarre claim since other tools already do that as add ins, and you can even hack this work now in Google Docs with the charting API.

Someone should start a free the sparkline petition ;)

Finding Duplicate Points in a Shapefile

Wednesday, September 2nd, 2009

[Editor's note: When building the 6,600 cities for Natural Earth vector, we had 6 extra townspots than town labels. Bound to happen on larger projects. One could take the halving approach and select half, see if the number of symbols matches the number of text objects, if so skip, if not subdivide in 1/2 again and reevaluate. Or if you use MaPublisher with Illustrator and/or Vectorworks to export out as a SHP file, we can open the DBF up in Excel and use the "countif" function and "conditional formatting" to quickly identify the exact features to resolve. By sorting the resulting "true" and "false" columns on lat, long, and feature name, we can quickly evaluate if there are multiple features at the same geographic location and compare their names. If they are the same name, assume 1 is a duplicate and remove it.]

Republished from Microsoft.

You can locate duplicates in a range of data by using conditional formatting and the COUNTIF function. Here are the details on how to make that work.

Set up the first conditional formatting formula

I’ll start by setting up a conditional format for the first data cell. Later, I’ll copy that conditional format for the whole range.

In my example, cell A1 contains a column heading (Invoice), so I will select cell A2, and then click Conditional Formatting on the Format menu. The Conditional Formatting dialog box opens. The first box contains the text, Cell Value Is. If you click the arrow next to this box, you can choose Formula Is.

Example

After you click Formula Is, the dialog box changes appearance. Instead of boxes for between x and y, there is now a single formula box. This formula box is incredibly powerful. You can use it to enter any formula that you can dream up, as long as that formula will evaluate to TRUE or FALSE.

In this case, we need to use a COUNTIF formula. The formula to type in the box is:


=COUNTIF(A:A,A2)>1

This formula says: Look through the entire range of column A. Count how many cells in that range have the same value as cell A2. Then, compare to see if that count is greater than 1.

When there are no duplicates, the count will always be 1; because cell A2 is in the range, we should find exactly one cell in column A that contains the same value as A2.

Note In this formula, A2 represents the current cell — that is, the cell for which you are setting up the conditional format. So, if your data is in column E and you are setting up the first conditional format in cell E5, the formula would be =COUNTIF(E:E,E5)>1.

Choose a color to highlight duplicated entries

Now it is time to select an obnoxious (that is, obvious) format to identify any duplicates that are found. In the Conditional Formatting dialog box, click the Format button.

Example

Click the Patterns tab and click a bright color swatch, like red or yellow. Then click OK to close the Format Cells dialog box.

Example

You will see the selected format in the preview box. Click OK to close the Conditional Formatting dialog box, and…

Example

Nothing happens. Wow. If this is your first time setting up conditional formatting, it would be really nice to get some feedback here that it worked. But, unless you are lucky enough that the data in cell A2 is a duplicate of the data in some other cell, the condition is FALSE and no formatting is applied.

Continue reading at Microsoft . . .

The Town Spreadsheet (Cartastrophe)

Thursday, July 30th, 2009

Blooming Grove Town Map

[Editor’s note: I’ve never laughed as much when reading a map as I did when I saw this Excel-drawn map of Blooming Grove, Wisconsin (yes you read that right). Daniel Huffman dissects it in his new blog, Cartastrophe.]

Republished from Cartastrophe.
Download Excel version of map.

The Town of Blooming Grove, Wisconsin. A magical, pixellated land full of jagged lakes and rambling, rustic acres:Setting aside for a moment the fact that the map has about as much detail as a Pac-Man level, I want to point out that this map is actually provided by the town as a Microsoft Excel spreadsheet. I am not kidding.

Bullet Graphs for Not-to-Exceed Targets (Visual Business Intelligence)

Wednesday, January 28th, 2009

[Editor’s note: The bullet graph is an alternative to circular gauges and meters commonly used on dashboards with a graph that provides a richer data display using less space. The bullet graph consists of five primary components: text label, a quantitative scale along a single linear axis, the featured measure usually as dark black line, one or two comparative or target measures of performance (optional), and from two to five ranges shown as background fills along the quantitative scale to declare the featured measure’s qualitative state like bad, satisfactory, and good (optional) All use the same quantitative scale. Flex component with source code from Agile UI. Google charts version from Dealer Diagnostics. Excel version from Excel User.

Correction on 2010 March 18: The image of the dashboard above is from Robert Allison. Get the SAS/Graph code to create the dashboard.]

Republished from Visual Business Intelligence. Monday, February 4th, 2008 at 2:54 pm.

Image above from Stephen Few’s Information Dashboard Design book seen here.
View PDF format Bullet Graph Design Specification from Stephen Few on the bullet graph. He’s got other useful information design writeups in his Library.

When I designed the bullet graph back in 2005, I did it to solve a particular problem related to dashboard displays. The graphical widgets that software vendors were providing to display single measures, such as year-to-date sales revenue, consisted mostly of circular gauges and meters, which suffered from several problems. Most of them conveyed too little information, did so unclearly, and wasted a great deal of space on the screen. The bullet graph was my alternative, which was designed to convey a rich story clearly in little space.

Bullet Graph Description

Since their introduction, a number of dashboard vendors now support bullet graphs, either as a predesigned display widget or as a display that can be easily constructed using their design tools. Now that bullet graphs are being used a great deal, they are being put to the test, and best practices are being developed to use them effectively.

One challenge that is faced by any graphical display of a single measure compared to another, such as a target, is the fact that the target usually functions as a point that the measure should reach or exceed, such as a sales target, but sometimes it functions as a point that the measure should stay below, such as an expense target. Here is a series of bullet graphs, which are designed in the typical manner:

Bullet Graphs for Not-to-Exceed Targets 1.jpg

Two of the measures—expenses and defects—work the opposite of the others in that the goal is to remain below the target. The background fill colors on these bullet graphs, which vary from dark gray to indicate “poor performance” through to the lightest gray to indicate “good performance,” are arranged from poor on the left to good on the right for revenue, average order size, and new customers, but in reverse for expenses and defects. The reversed sequence serves as a visual cue that expenses and defects should remain below the target. This cue, however, is not strong. It would be useful if something that stood out more signaled this difference.

We might be tempted to replace the varying intensities of a single color—in this case varying intensities of gray—with distinct hues, to make the reversal of the qualitative scale more apparent, such as by using the traditional traffic light colors that are so popular on dashboards.

Bullet Graphs for Not-to-Exceed Targets 2.jpg

This does cause expenses and defects to more clearly stand out as different from the other measures, but at what cost? Even if we ignore the fact that most people who are colorblind (10% of males and 1% of females) cannot distinguish green and red, we are still left with an overuse of color that makes the dashboard appear cluttered and visually overwhelming, as well as a dramatically weakened ability to use color to draw viewers’ eyes to particular areas that need attention. Is there a better way to make certain bullet graphs look different without introducing other more troubling problems?

Here’s a suggestion: not only reverse the sequence of the qualitative scale, but also the direction of the quantitative scale. Using expenses as an example, the quantitative scale could run from 0 at the right of the bullet graph with values increasing leftwards. The bar that encodes the expense measure would then also run from the right edge of the bullet graph leftwards. The bar running from right to left serves as a stronger visual cue that the target works differently, as you can see below:

Bullet Graphs for Not-to-Exceed Targets 3.jpg

Although it is not conventional for a quantitative scale to run from right to left, except in the case of negative values, this is easy to read and the unconventionality actually causes it to pop out more clearly. In fact, expenses and manufacturing defects are measures that we can easily think of as negative values (for example, expenses reduce profit and defects reduce manufacturing productivity).

I would like to encourage all the vendors out there that support bullet graphs to support this functionality and for those who use them to take advantage of it.

Take care,

Signature