Articles

All of our Excel articles can be found here

VLOOKUP using text strings

What is VLOOKUP? VLOOKUP is one of the functions in Excel’s vast function library. Its purpose is to search a table of values for a corresponding item and return a matching item from the table. An example might be searching a table of price data for a certain product name and returning the matching product’s

Read More »

Why you can’t effectively learn Excel 2016/365 using a Video Tutorial

Why you can’t effectively learn Excel 365 using a Video Tutorial Excel 365 uses the new Software as a Service (SaaS) business model In the new SaaS world, you don’t buy software any more – you effectively rent it via a subscription. The software then continually improves and changes via automatic online updates.  This means

Read More »

Separating text and numbers in Excel

You’ll sometimes encounter a situation where a cell contains both text and numbers, often when data has been imported from another system. In this example, you want to separate the names and numbers from column A into columns B and C. There are three ways that you could do this. Option 1: Flash Fill Flash

Read More »

Removing gaps between bars in an Excel chart

Excel column charts have gaps between their bars by default. Not everyone likes this default appearance, but fortunately it is possible to change the size of the gaps between bars and even remove them altogether. 1. Open the Format Data Series task pane Right-click on one of the bars in your chart and click Format

Read More »

Adding VAT to a value in Excel

Sales taxes are levied in many parts of the world. In the UK, this is known as VAT, or “Value-added Tax”, but it’s also known as sales tax, consumption tax and GST (Goods and Services Tax). Adding VAT to values is a very common requirement, but doing this in Excel isn’t very intuitive and confuses

Read More »

An Excel formula to check if a cell contains some text

Sometimes you need to search for a specific piece of text within a cell. One example I had to deal with in my professional career was a situation where a workbook combined invoice and credit note data from two different systems. In one system, credit notes had the code CN before the invoice number, but

Read More »

Subtracting percentages in Excel

If you’re new to Excel, it’s only a matter of time before you encounter a problem that requires the use of percentages. Using percentages in Excel formulas isn’t very intuitive for new users, which can result in a lot of confusion and frustration. Subtracting 10% from a number Imagine that you have the following example

Read More »

Why are the sort and filter options greyed out?

Sometimes Excel’s sorting and filtering options become grayed-out and unusable for no apparent reason. This problem doesn’t arise very often, but it can be very confusing when it does. Adding to the confusion, there are several reasons why this can happen! This article explains the reasons sorting and filtering can be disabled, and how to

Read More »

Why does Excel say “The value you entered is not valid”?

This is a question we’re asked surprisingly often. You’re editing a workbook, you enter a value into a cell, and Excel responds: The value you entered is not valid. A user has restricted values that can be entered into this cell. Recent versions of Excel have changed this message, so you might also see: This

Read More »

A Keyboard Shortcut for Merge and Center

A question we’re often asked is: What is the keyboard shortcut for Merge and Center? Merge and Center isn’t a tool that tends to be used very often, but creating a complex layout often means we need to merge and center multiple times. You would expect that Excel would offer a convenient keyboard shortcut to

Read More »

Excel Versions Explained

It’s important to know which version of Excel you are using As I write this, there have been 29 different versions of Excel, stretching from 1985 to the present day, on three different operating systems. Each version of Excel has been significantly different to the last, and the Mac and Windows versions are still very

Read More »

Office 365 Updates

Excel 365 is different to Excel 2016 and Excel 2019 Excel 2016 was sold using both a subscription and one time purchase license (called a perpetual license).  Though it wasn’t immediately apparent to some users, Microsoft differentiated between the different version by referring to them as:  Excel 2016 and Excel 2016 for Office 365 subscribers.  This caused

Read More »

Excel 2016 for Mac vs Excel 2016 for Windows​

Excel 2016 for Windows and Excel 2016 for (Apple) Mac are very different products You could be forgiven for thinking that the (confusingly named) Excel 2016 for Mac (suitable only for Apple computers) was “just the same” as Excel 2016 for Windows. Unfortunately, this is not the case. The Excel 2016 for Mac version has

Read More »
Scroll to Top