Tag Archives: Excel

Insert blank rows in Excel

To insert blank rows in an Excel spreadsheet is a relatively straightforward process, as is inserting a block of consecutive rows, but what if you need to insert them alternatively? If it’s a small spreadsheet, than that’s fine, you can work your way down it, but what if the spreadsheet has already got lots of […]

Dealing with increased file size in Excel due to Excess Cell Formatting

When formatting your Excel spreadsheet (changing colours, backgrounds, gridlines, alignment, etc) how you do it can have a massive impact on its file size. What’s more you may even be unaware of the impact (being honest, how many of us actually check file sizes on a regular basis?). Fortunately, there’s a way to help reduce […]

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