Same same, but different

Nice tutorial with a few interesting ideas about how to display distributions.

Interesting idea to use a “panel chart” instead of a broken chart.

In order to use the “dygraphs“, do the following:

1. Prepare your values (for example, divide by 1.000.000; add “Date” to the right field, concatenate(Field-with-year,”-01-01″))
2. Copy from Mac to Windows, insert into Excel, change date again to right format
3. Save as CSV
4. Replace “;” with “,”

How to convert this:

to this:

Great VisualBasic script for Excel which does exactly that:

Option Explicit
Sub MoveData()
Dim LC As Long, LRO As Long, NR As Long, HowMany As Long, a As Long, b As Long
Dim c As Range, rng As Range
Application.ScreenUpdating = False
LRO = Sheets("Output").Cells(Rows.Count, 1).End(xlUp).Row
If LRO > 1 Then Sheets("Output").Range("A2:E" & LRO).ClearContents
NR = 2
With Sheets("Input")
LC = .Cells(1, Columns.Count).End(xlToLeft).Column
For Each c In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
Set rng = .Range(.Cells(c.Row, 3), .Cells(c.Row, LC))
HowMany = Application.WorksheetFunction.CountIf(rng, ">-10000")
If HowMany > 0 Then
.Range("A" & c.Row & ":C" & c.Row).Copy Sheets("Output").Range("A" & NR & ":A" & NR + HowMany - 1)
b = NR
For a = 4 To LC Step 1
If .Cells(c.Row, a).Value > -10000 Then
Sheets("Output").Range("D" & b) = .Cells(1, a)
Sheets("Output").Range("E" & b) = .Cells(c.Row, a)
b = b + 1
End If
Next a
NR = NR + HowMany
End If
Next c
End With
Sheets("Output").Select
Application.ScreenUpdating = True
End Sub

Source

Good tip here!

Most importantly:

When the Conditional Formatting window appears, select “Formula Is” in the drop down. Then enter the following formula:

=mod(row(),2)=1

Next, we need to select the color we want to see in the alternating rows. To do this, click on the Format button.

Just use “#N/A” in the cell to avoid charting NoData values.

Really useful and nicely designed website to convert between different units.

Good source of information about custom number formats in Excel.

In order to calculate the (average) annual growth rate over multiple year, use this formula:

CAGR: =((End Value/Start Value)^(1/(Periods - 1)) - 1

Takes CSV or tab-delimited data from Excel and converts it into several web-friendly formats, include JSON and XML.

Great script (in CSS and Javascript) which makes it really easy to display boxplots on a website. Great!

Wonna concatenate numerous fields to obtain all values in a single cell? Here’s the script:

Sub ConcatenateAll()

End Sub

And in order to insert a little bit more complicated string in ArcGIS’s Field Calculator, you need to do this:

"<img src='http://localhost/graph.php?data=36,36,37," & Int([wohnfl_per]) & "' />"

Change = A2/A1 - 1 (Cell format: Percentage)

A very commonly requested Excel feature is the ability to add labels to XY chart data points. The XY Chart Labeler adds this feature to Excel. The XY Chart Labeler provides the following options:

Add XY Chart Labels – Adds labels to the points on your XY Chart data series based on any range of cells in the workbook.

Move XY Chart Labels – Moves the entire set of data labels or individual labels in any direction and in the increment of your choosing.

Manual Labeler – When you don’t want to label an entire data series, but rather use data labels to highlight specific data points, the manual labeler allows you to do this.

Even though this utility is called the XY Chart Labeler, it is capable of labeling any type of Excel chart series that will accept data labels.

Just a quick comparison of two methods: indexation and standardized normalization. Given are three variables – two on emissions and GDP per Capita. The s.n. gives differences in small values a higher visibility, whereas the indexation allows to read more precisely the evolution (“a raise of 35%”). Disadvantage of latter is surely the choice of the “common” year…

The use of sparklines has been already highlighted. Here is a nice add-on (not for free) that allows to use different graph styles. Looks really nice and clean.

Great idea and easy approach to develop a lightweight data exploration tool in Excel. Here and here.

Nice graphic from the New York Times.