Create a website
Scratch Programming
Microsoft Office
Pivot Tables 101 – what is a Pivot Table?
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.
Creating a Perpetual 12-month Calendar
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
Excel Dates – Calculations and Functions
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 has a whole range of date functions built in, to make your life easier.
And thirdly, the date you see is just the serial number, formatted in a particular way. If you want to see the day of the week, change the number formatting. If you only want to see the day number, change the number formatting…
In this tutorial, we perform a few date calculations and use some of the more common date functions to build a perpetual calendar in Excel – just enter the month and year, and it will display the day numbers in the familiar Sunday-Saturday calendar grid.
If you want to build a 12-month calendar from just the year, take a look here (assumes you understand the concepts described in the tutorial on this page).[youtube https://www.youtube.com/watch?v=nyvtre4MXgI&hl=en&fs=1]
Writing formulae between books – the easy way
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 sheets – the easy way
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]
An alternative to Radio Buttons
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 please consider this option over radio button macros.
The second part of the tutorial shows you how to set up conditional formatting so that the other selection turns grey once you’ve filled one in.
Leave a comment if you want to know how to do this for more than 2 cells, and I’ll record another tutorial to demonstrate.[youtube https://www.youtube.com/watch?v=dS_ZTx7H58I&hl=en&fs=1]
Demo spreadsheet: not_radio_buttons.xls
Why don’t I like macros?
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 to create its own audit trail. Once run, things have changed on your workbook, and there’s no evidence as to why. Which means trying to work out why that key result looks odd is very difficult!
- Time – unless you’re a whizz with Visual Basic (the language of macros), anything but the simplest recorded keystrokes take a while to work out.
Having said all that, there’s a time and a place. Yes, I’ve used macros, and yes, they save you time if used correctly. If you want to start using macros in anger, why not take a look at our suggested starter book.
Data Validation Lists on a separate sheet
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]Categorising data with VLOOKUP
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
Exact difference between two dates
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 as DateDif, not DatedIf. Just to be awkward, you won’t find this one in the function library!
DATEDIF has three arguments (stuff in brackets separated by commas):
- Date 1 (earlier date)
- Date 2 (later date)
- Output options
The output options are ‘d’, ‘m’, ‘y’, ‘yd’, ‘ym’, or ‘md’, as shown below
DATEDIF() funtion options - formula bar shows cell D3
There are many practical uses for this function: one recent instance was in determining whether someone was eligible for a particular wage, defined by their age. In the example below, the formula checks whether a person has reached the age of 17. In words,
If there are 17 or more whole years between their birth date and today, show Y, otherwise show N.
DATEDIF() in action - formula bar shows cell B12
Self-expanding data ranges 2 – use Tables
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 in the data, then ‘Insert’ tab, ‘Tables’ group, ‘Table’ command).
The vlookup is looking for a 5 (exact match) and cannot find it - hence the error. Notice it's looking in Table1, not a range
Now, when writing a formula and wanting to include the table, just highlight it as you would when entering a range – rather than putting in the cell range, Excel will enter something like Table1
Take a look at the two graphics. The formula bar shows what’s in cell D2. Initially, the vlookup can’t find a 5 (the lookup value) so it returns an error because it’s looking for an exact match.
All I did was type in the 5 and the z - the table automatically expands, which means the vlookup can now find a 5
As soon as I type 5 and z underneath the table, the table automatically expands and the VLOOKUP now works because it can find a 5.
Structuring your spreadsheets
When you first start using Excel, it seems like a good idea to break your data up as much as possible so that it’s easy to find/read. It’s not uncommon to have lots of sheets containing similar data for different departments / people / products / stores / months etc.
However, as you start to learn the power of Excel, using functions such as pivot tables and filters, you’ll find that it begins to make sense to keep source data in one big table that’s easy to analyse – it’s much easier to summarise a big block of data than it is to consolidate data fragmented across sheets. Answering the question “How many staff have a First Aid Certificate?” is no longer a time-consuming trawl across multiple sheets (or books), but the work of a few clicks of the mouse.
As a standard approach, my workbooks have blocks of data on one sheet, with analysis on one or more others; validation data goes on its own sheet too (“but I have to keep a validation list on the same sheet as the range I’m validating, don’t I…?” No – there is a way…). So, the sheets I have are typically:
- Input_Data – a huge block of data, with only column titles at the top, with a named, self-expanding range
- Analysis_sheet(s) – containing the calculations, pivot tables, etc
- Validation_List(s) – one or more sheets with lists for use in data validation (restricting what people can enter into certain cells – e.g. a list of store names)
- Parameters – those key inputs that I have to put somewhere, e.g. VAT
- Output_Summary – if required, a sheet summarising the key outputs I want people to see
…but with rather more meaningful sheet names, of course….
Importing legacy data
Example legacy data (formulae are shown as they appear in row 2)
Many legacy systems export their data in human-readable form, rather than in blocks of data that are efficient for analysis. This graphic is an example, where the product code is listed once, then the variants are listed next to it.
For analysis, we need to generate a column that has the product code in every row that has other data. The formula in the middle box of the graphic does this. It roughly translates as
- “if the cell to the right is not blank, use it” (this picks up the product code where it appears)
- “otherwise, if the corresponding price cell is blank, give a blank” (this gives blanks in the rows that don’t have any data)
- “otherwise, use the cell above” (this is what puts the product codes in the rows that don’t currently have one).
Having generated the product code in each row, we now need to generate a cell with a unique id for the combination of product and variant. This uses the shorthand & to join together bits of text. In this example, we have joined the product code, a hyphen, and the left-hand two characters of the variant (using the LEFT() function).
We can now use the unique code to reference the data, e.g. with a VLOOKUP() function.
Selecting large ranges – use the keyboard!
There are a few things that many people struggle with. One of them is using the mouse to select a large range of data. How many times have you dragged to the bottom of the screen, only to scroll past the end of the data? And it’s arguably worse when scrolling off to the right… Okay, Excel 2007 and later slow down at the end of your data, but there is an even better way, using the keyboard – by combining two simple shortcuts, you can select a large range of data with two keypresses.
Here are the two keyboard tools, both of which use the four arrow keys normally found towards the right of the keyboard.
Shift-Arrow expands your currently selected range in the direction of the arrow.
Ctrl-Arrow moves a number of cells in the direction of the arrow. How far it moves depends on what’s in the currently-selected cell: if it contains data, then Ctrl-Arrow moves to the end of the data; if there is no data in the next cell, then it moves to the next cell containing data.
By combining these two into Ctrl-Shift-Arrow, you can move to the end of the data and expand the range.
Therefore, to select a very large block of data, select the cell in one corner of the data, then hold Ctrl and Shift down, and hit the two arrows that will take you to the diagonally opposite corner. Simple, but brilliant!
NB Don’t forget this stops at blank cells… To move past a blank cell, just keep holding Ctrl+Shift, and hit the arrow key again to select to the top of the next block of data – then again to select to the bottom of that block.
Don’t type cell references – let Excel do it for you
I try to avoid typing cell references wherever possible, because I know I’ll get them wrong. Much better to let Excel type your cell references for you. It’s quite straightforward.
Start typing your formula, until you get to the point where you want to add a cell reference, e.g. =SUM(
Now, either use the mouse or the keyboard to select the cells, row or column that you want to include – you’ll see that Excel writes the cell reference in.
If you select the wrong thing by mistake, don’t worry (and don’t hit any keys in desperation!) – just select the right thing, and Excel will update the formula.
Finally, finish typing the formula – in the example above, the SUM function needs a ).
Hit Enter.
If you’re using multiple cell references in a formula, that works too. In the example below, the operators ( = , + , etc) are typed on the keyboard; the bits in square brackets are instructions to use the method above:
= [select first cell] + [select next cell] + [select next cell] + …. and so on.
Use columns in formulae to automatically include new data
Usually, when you write a formula, you refer to a range of cells, e.g. =SUM(A1:A5). However, if you are continually adding new rows to the data, you don’t want to be constantly updating the formula.
Well, why not make the formula refer to the column, rather than a range – then it’ll include anything in the column. Example: =SUM(A:A) will add up everything in column A. It works for rows, too, e.g. =SUM(1:1). Note that, when defining a column or row in Excel, you need to define the start and finish column/row, separated by commas – even if they are the same.
As always, don’t type in your references – select them, and let Excel type them in for you. (See here for details).
Occasionally, even using rows isn’t enough, and you need to use tables or even define a self-expanding range.
Self-expanding data ranges
Isn’t it irritating when you keep having to go back into wizards, dialog boxes and formulae to manually change data ranges?
You can often get round this problem by using column references or tables.
There are times when you want a range to expand itself based on the size of the data, and here’s a way to do it.
Because of the way this works, it’s best to have your data on its own sheet, without anything else with it. You also need to make sure there’s always data in the first row and first column. Put some data in and select the data sheet before you start the next bit.
First, you need to know about Names. In Excel 2003, you’ll need to go to ‘Insert > Name > Define’ to open the Name Manager. In 2007, you’ll need the ‘Formulas’ tab, the ‘Defined Name’ group, ‘Name Manager’ button.
Create a new meaningful name using the dialog box, for example ‘tbl_mydata’ – try to stay clear of names that might be in common usage, such as ‘data’, ‘table’, etc. You can’t use spaces, but can use underscore, as in my example name.
Now, usually names refer to ranges. In this case, the name will refer to a formula, so make the name refer to:
=OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))
What? To explain…
The OFFSET() function defines a range.
- The first parameter shows a ‘start point’, and in this case I’ve chosen the top-left cell, which I’m assuming is the top-left cell of your data.
- the 0,0 are the number of rows and columns to move from the start point before definining the range
- the last two are the height and width of the range, using the COUNTA() function, which counts all non-blank cells – the first counting everything in column A to give the height, the second counting everything in row 1 to count the width.
Having named this formula, you can now use the name in other formulas and dialog boxes. So, for example, =COUNTA(tbl_mydata) will count the number of non-blank cells in the range. This is quite a good one to use to test your name definition, because it’ll change as you add data on.
You can also test it with Go To
(2003: Edit>Go To ;
2007: Home tab, Editing group, Find & Select, Go to; for both the shortcut is Ctrl-G or F5)
– type the name into the ‘go to’ box and hit OK. Add some more data into the first row and/or column, try it again, and you’ll see it get bigger.