Category Archives: Microsoft Excel

Errors in Excel and how to use them effectively

Making use of errors OK, so logic says that you don’t want to have any errors in your spreadsheet & broadly speaking, that’s correct… however, there can be occasions when an “error” can be helpful. As we saw in last month’s tip, the #N/A error in a VLOOKUP Function means that the item that you’re […]

Dealing with Errors in Lookups

The #N/A Error If we use the VLOOKUP Function to match between two sets of data – then occasionally, Excel will return an #N/A error where it can’t find the value in one list in the other one In this example, we have two lists of employee data where in the “Month #2” block, there’s […]

Conditional Formatting in Excel

Highlighting cells in accordance with a set of rules That is, in a “nutshell” what Conditional Formatting is all about – the formatting changes based on a set of rules that you specify. This could be whether a cell value is higher or lower than a target, or a date before or after another or […]

Comments in Excel

Making your spreadsheet understandable and easy to update For many of us, building a spreadsheet can be a one-off exercise to answer a specific issue or calculate a value. However, for others, spreadsheets can be designed to capture a year’s worth (or more) of data. And then there can be an added issue… whilst we […]

Fixing cell references with a $ sign

Fixing cell references Fixing cell references (otherwise known as Absolute Referencing) is something that seems to confuse a lot of people – being able to change a cell reference within a formula to a fixed Cell, Row or Column… The reason why you’d want to consider doing this is where part of your formula always […]

A novel use of Find & Replace

Using Find & Replace to solve a date problem Excel’s Find & Replace feature (which you’ll find by clicking on the “Find & Select” button on the far right-hand side of the Home Tab) can be used in many ways… to replace names, codes, products, etc however one use that I’ve found for it is […]

Useful keyboard shortcuts in Excel

Excel shortcuts As we’re talking Excel shortcuts, I’ll keep this tip very short! As your spreadsheet increases in size, you’ll want to find quick ways to move around it without needing to use the scroll buttons (especially with the latest versions having 1,048,576 rows and 16,384 columns!), so here are a few that I regularly […]

Working with Sheets

Working with Sheets Depending on which version of Excel you use, when creating a new workbook, there will usually be either one or three blank sheets (or Tabs) entertainingly named Sheet1, Sheet2 & Sheet3. These can be added to, deleted, renamed & coloured as necessary Adding a new Sheet (Tab) By far the quickest & […]

Freeze titles for added visibility in Excel

Freezing Titles (or panes) Whenever you’re working with a lot of data, you can find that when you scroll down your spreadsheet, your headings disappear. By understanding how to freeze panes in your worksheet, you’ll be able to work quickly without the need to continually scroll up or to the left. You may want to see certain rows […]

BODMAS and why it’s important to understand it in Excel

BODMAS BODMAS is a way of remembering the order of operation that Excel follows when it evaluates values in a formula. BODMAS stands for: B – Brackets O – Order (Indices such as 22 etc.) D – Division (in an Excel formula we use a /) M – Multiplication (in an Excel formula we use […]