## Datediff in Excel

Another article on Excel idioms; this one’s about dates. It’ll be useful for some.

## Encrypting Excel

How to protect an Excel File. File » Info » Protect » Encrypt with Password.

## On D’Hondt

For the European Parliament election in Great Britain  and Greater London Authority City wide list, a form of PR is used. Parties register lists of candidates, and voters vote for their preferred party. The counting is done using a method called the Dhondt method. This note describes the counting method and shows how an excel spreadsheet…

## How to unpivot in Excel

The need to unpivot data is a common requirement. There are a number of reasons as to why. I and my customers are primarily using  Excel on Windows and so the examples below are shown in excel and rely on excel functionality.

## Analysing the 2015 General Election

I am fed up hearing about the PLP’s 9m votes mandate. A large number of these 9m votes will have voted for Labour candidates that lost. So I decided to calculate the number of votes cast for the PLP. So something for both political geeks and excel nerds. Here’s how I did it.

## OMG Excel Arrays (a UNIQ filter)

Right. I needed to write a UNIQ filter in an excel spreadsheet and this needed to be implemented using functions i.e. not VB for two reasons; I can’t and the customer doesn’t want me to.

## Date Differences in Excel

I was looking to calculate if someone was under 27 years old; this can Be quite tricky but excel has just the function, =DATEDIF().

## VLOOKUP vs INDEX ( MATCH)

How awesome is this! Excel =INDEX, is a matrix retrieve i.e. =INDEX(array, row, col) retrieves an entry from the two dimensional array. If we combine this with MATCH which returns a row number we can use these functions instead of =VLOOKUP, its allegedly faster too. MATCH operates on a column or linguistically a list.

## Microsoft Office Templates

With Office 2010, the location of the “My Templates” folder for Excel is managed using Word’s Options dialogue box, and it seems that you can no longer specify a search path, solely one folder. What Microsoft say at answers.microsoft.com!