Category Archives: Microsoft Excel

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

Analysing data with Excel Pivot Tables

In the past couple of tips, we’ve looked at how to create Tables & use the tools that then become available (such as Filters and Slicers) to begin to extract information from the data that we hold. We’re now going to look at how to take this much further by looking at Pivot Tables. What […]

Analysing Tables with Slicers

A brief reminder In this Tip, we first remind ourselves some of the basic analysis tools for tables before moving on to discuss Slicers. Tables, you’ll hopefully recall, are Excel’s way of holding data in a clear & structured manner; If you need a “refresher”, here’s a link to November’s tip when we discussed Tables […]

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