Excel Tips

Pivot Tables 101 – what is a Pivot Table?

2014-06-03T19:23:21+01:00

Pivot tables are powerful, so they must be hard to use, right?  Wrong.  You can start making the most of them very quickly.  This introduction to Pivot Tables will give you enough to get started with analysing those large blocks of data.

Pivot Tables 101 – what is a Pivot Table?2014-06-03T19:23:21+01:00

Creating a Perpetual 12-month Calendar

2011-01-13T08:51:02+00:00

This spreadsheet was created at request of a site visitor. It shows how to create a 12-month calendar from just the year number. It's not the finished article, just an example and a method, so you can have a go! I suggest you have a look at the post on using Excel Dates, so that you understand what I've done any how it works. Excel template containing the start point: Perpetual Calendar Workings

Creating a Perpetual 12-month Calendar2011-01-13T08:51:02+00:00

Excel Dates – Calculations and Functions

2010-11-17T14:05:37+00:00

Dealing with dates in Excel is easier than you think. For a start, dates are just numbers. The number 1 is 1 Jan 1900, 2 is 2 Jan 1900, and so on - we're now over 40000. (In the help files and function descriptions, they are referred to as 'serial numbers'). That means that you can add and subtract dates, or move along a calendar by adding to/subtracting from a date. Secondly, if you want to know day of the week, month of the year, or even number of working days between two dates, there's a function to do it. Excel [...]

Excel Dates – Calculations and Functions2010-11-17T14:05:37+00:00

Writing formulae between books – the easy way

2010-11-03T17:27:49+00:00

Trying to type out a formula that relates to a different book can be a nightmare - how do you get the file name, the exclamation mark, the apostrophes, etc, all in the right place? Well, why not let Excel do the hard work for you? [youtube https://www.youtube.com/watch?v=YnzIGRAIpK8&hl=en&fs=1]

Writing formulae between books – the easy way2010-11-03T17:27:49+00:00

Writing formulae between sheets – the easy way

2010-11-03T10:15:55+00:00

Trying to type out a formula that relates to a different sheet can be a nightmare - how do you get the exclamation mark, the apostrophes, etc, all in the right place? Well, why not let Excel do the hard work for you? [youtube https://www.youtube.com/watch?v=6xrY0cq_SD0&hl=en&fs=1]

Writing formulae between sheets – the easy way2010-11-03T10:15:55+00:00

An alternative to Radio Buttons

2010-11-02T19:59:55+00:00

Radio buttons are a neat way to enable only one thing to be selected. The downside is that you need to add macros on the back end to make them work. The tutorial below (recorded in Excel 2007, but 2003 options also explainded) offers you a way to use data validation to ensure that only one cell has data entered. It's not quite as good as a radio button, because in order to change your selection you have to delete the original then insert the new, whereas radio buttons do that for you. But if that's not a big overhead, then [...]

An alternative to Radio Buttons2010-11-02T19:59:55+00:00

Why don’t I like macros?

2010-11-02T15:27:09+00:00

Actually, I do like macros, used in the right place at the right time.  Excel macros can be incredibly powerful, but I try to avoid using them if possible. Why? Well, there are several reasons: Undo - There are a few things that break the 'undo' chain, by which I mean that you can't undo anything done before them.  Guess what - running a macro falls into that category.  And it doesn't matter how good you are at Excel, being able to Undo is useful! Audit trails - you can't audit actions taken by a macro, unless you've written the macro [...]

Why don’t I like macros?2010-11-02T15:27:09+00:00

Data Validation Lists on a separate sheet

2010-10-15T13:40:28+01:00

This tutorial shows you how to apply data validation using a list on a different sheet. It assumes you already know how to use data validation. [youtube https://www.youtube.com/watch?v=tPaDFbWYEWY&hl=en&fs=1]

Data Validation Lists on a separate sheet2010-10-15T13:40:28+01:00

Categorising data with VLOOKUP

2010-10-14T14:20:04+01:00

This video tutorial shows you how to categorise a list of data into groups defined by a value of ranges. The example categorises people into age bands such as 17-34,35-44, 45-50, etc. http://www.youtube.com/v/XgjIrWgkrgE?hl=en&fs=1

Categorising data with VLOOKUP2010-10-14T14:20:04+01:00

Exact difference between two dates

2010-10-10T06:45:27+01:00

Dates are just numbers in Excel.  To calculate the number of days between two dates, just subtract one from the other (you might find Excel automatically formats the answer as a date, in which case you need to change the number format back to 'General' or 'Number'). However, it's also often the case that you need to know the number of whole years or months between two dates.  A recent example - has this person turned 17?  In this case, you can either write a complicated formula to calculate it, or you can use the Excel function DATEDIF() - read it [...]

Exact difference between two dates2010-10-10T06:45:27+01:00

Title

Go to Top