Lookups – x, v, h, and index match.
When you have data in one place, and you want to collect a certain piece of that information and return it somewhere else based on a given value, you probably want a lookup. Depending on the situation there are options about how to do that. The VLOOKUP (the V stands for Vertical) formula used to […]
Maths and Paste Special, together! What?
You can use Paste Special to do maths! Say you have a list of numbers, and for some reason you want to divide them all by 100. You could write a formula to do that, you could type a few answers and use flash fill, or you can use Paste Special. Type 100 into a […]
An easy way to pull information together: Use consolidate.
If you have data in different places that you would like to bring together as one, Consolidate is a great option. You can choose data from different ranges and combine it together either by the headings in the top row, the left column or both. Note that the ranges you select, do not need to […]
3D Reference in Excel
You can create formula in excel that get data from multiple worksheets without having to specifically reference each sheet by using 3D references. In the example above all the values in C3 will be added together from each sheet from Holiday1 to Holiday3. No matter what the sheets are called, if they sit between Holiday1 […]
Increase accuracy of data entry using Data Validation.
Using data validation in Excel means you can put restrictions on what can be entered into the cell, increasing accuracy. There are a lot of different options and settings within the data validation area. Here we have a drop down list of choices that the user can select from. You could also require the data […]
Time saver: Enter data in multiple cells at once.
Select cells to enter the data into (Holding down the ctrl key if they are not in one continuous block). Type into one cell. Press Ctrl + Enter, the data is entered into all the selected cells.
Make your life easier: give a cell a name.
You can give a cell a name in excel. So, rather than being called A5 or E3 the cell could be GST or Total. This makes you formulas easier to understand. See below in the formula bar for an example, this means more than E10+E23+E34+E40. This also means you can use the name box to […]
Save time: Link to a cell
You can insert a link within excel. This link could be to a web site, or a place within the spreadsheet. That way you can navigate around the worksheet or even to a different sheet without lots of scrolling and clicking. If you have a large sheet or a workbook with a lot of sheets […]
Quick Information: Use the Status Bar
In the bottom right hand corner of your excel sheet is the status bar. When you highlight a group of cells the status bar tells you some information about them, such as the average, count(how many cells are highlighted) and the sum. You can add extra pieces of information to show here by right clicking […]
Avoiding manual data entry
Instead of doing lots of typing, did you know you can fill a series of cells based on just one manually entered cell? For example, if you write in a date, and take your mouse to the bottom right hand corner of the cell, (it should turn into a small plus sign) and RIGHT click, […]