archive

Archive for the ‘Excel’ Category

Excel: Find MIN for multiple ranges, with offset of 365 days

Wednesday, April 29th, 2015

Screen Shot 2015-04-29 at 10.20.23

To remember….

Extract Data From PDF Tables

Thursday, July 17th, 2014

Screen Shot 2014-07-17 at 10.23.06

Screen Shot 2014-07-17 at 10.23.14

Tabula: Very nice utility to extract data from a PDF table: Just upload the PDF file, highlight the table you want to extract, you’ll get a preview, and download the data in CSV format. Pretty cool!

Raw Data Visulaizations – From Excel to RAW to Ilustrator

Tuesday, October 15th, 2013

Interesting application which can bridge a bit the space between Excel data and Illustrator visualization work.

Indicator Display

Tuesday, September 17th, 2013

Nice example of an attractive and both visual and textual display of indicators.

From an Excel-Chart to a nice looking one

Monday, September 2nd, 2013

Nice evolution from a standard (and as such published) Excel chart to a nice, innovative looking one (although perhaps a bit more challenging to read).

Formula for counting percentages between negative values

Wednesday, August 14th, 2013

Say, you have “300” and “-300” as two values and want to calculate the percentage change between them. With the formula

  "=(A2 - B2) / ABS(B2)"

it’ll do it.

How to Visualize and Compare Distributions

Thursday, November 15th, 2012

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

Excel: Making a Panel Chart (Instead of a Broken Chart)

Friday, November 9th, 2012

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

Animated Graph: How-to

Monday, April 23rd, 2012

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 “,”

Excel: How to “Unpivot” a Table

Thursday, January 12th, 2012

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

Excel: Automatically alternate row colors (one shaded, one white)

Monday, August 29th, 2011

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.

Excel: Don’t Chart NoData (NULL) Values

Thursday, July 7th, 2011

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

Change Units

Thursday, June 30th, 2011

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

Number Formats in Microsoft Excel

Tuesday, June 28th, 2011

Good source of information about custom number formats in Excel.

Excel: Constant Annual Growth Rate (CAGR)

Friday, February 25th, 2011

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

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

Data Converter

Tuesday, September 7th, 2010

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

Boxplot

Thursday, August 26th, 2010

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

Excel: Concatenate Multiple Fields

Tuesday, August 24th, 2010

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

Sub ConcatenateAll()

Dim x As String, rng As Range, cel As Range, v_new As Integer, v_old As Integer

With ActiveSheet

Set rng = .Range("F2:F2736")

For Each cel In rng

v_new = Int(cel.Value)

If (v_new <> v_old) Then
x = x & "," & Int(cel.Value)
End If

v_old = v_new

Next

.Range("G2").Value = x

End With

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]) & "' />"

Excel: Change between Two Cells in Percentages

Thursday, October 15th, 2009

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

Excel Scatter Labels

Friday, February 13th, 2009

picture-155.pngA 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.