Tag Archives: 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 […]

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

Case Study: Printerbase Ltd

The Client’s problem Printerbase is a very successful printer, print consumables & stationery supplies company. Peter, the MD had realised that one of the issues that was constraining the growth & development of the company was him! He’d realised that whilst he was the “Key” person in the business, he’d become a “bottleneck” so, amongst […]

Case Study: Gericke Ltd

Summary Using Excel to create a configurator tool to speed up quotations The Client’s problem Gericke Ltd. produce sifters, mainly for the food industry. Each model that they manufacture can have over 20 features that can be selected and various logical rules impact on which combinations are allowed. This, of course, means that the physical […]

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