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 [...]
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 [...]
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?
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?
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 [...]
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 [...]
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.
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.
Oct 102010
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 [...]
In Excel 2007, Microsoft have given much more prominence to Tables. They are useful for formatting data, filtering and producing summary statistics, but they have another use – if you refer to a table (rather than a range) in a formula, they automatically expand when you add data! First, create your table (select a cell [...]