Category Archives: Microsoft Excel

Tables in Excel

What are Tables Tables are Excel’s way of allowing you to manage and analyse a group of related data in a structured way. You can turn a range of cells into an Excel table at the click of a couple of buttons & this then provides a wide range of analysis possibilities. They also make […]

Changing case in Excel

Changing case to help printing I’ve recently been doing some consultancy work which has involved copying some data from another software program into Excel. Unfortunately, the text was all in upper case & not only is this difficult to read, it physically takes up more space, so I decided to change its case it to […]

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 & […]