Acuity Training recently published the results of their research into Excel usage in the workplace. They polled 1,000 office workers earlier this year across a range of industries to find out numerous things relating to competence and time spent in…
The VLOOKUP function enables you to search for a value in one column and return another from the same row in a different column. As it works from top to bottom, the ‘V’ stands for vertical. Arguably, it’s the most…
XLOOKUP is most commonly used for exact matches, but what if you don’t fully know what you’re searching for? Fortunately, it supports wildcards, which are characters that take the place of text so you can perform partial matches. This is…
Excel allows you to easily sum a column of numerical values via the ‘Total Row’ option in the Table Design tab. This generates a SUBTOTAL formula with numerous options including SUM (ignores hidden and unfiltered values) and COUNTA (counts non-empty cells). Awareness of these functions’ behaviour is crucial, as they handle hidden rows differently.
By default, XLOOKUP performs a case-insensitive lookup, meaning it finds the first match in a range irrespective of whether it’s uppercase, lowercase or mixed case. To circumvent this, it can be used in conjunction with EXACT. EXACT checks two text…
Table data is prone to being disorganised and clogging up worksheet space. Fortunately, you can create a multi-level hierarchy that breaks it down into groups so each is summarised separately. In Excel, these are called outlines, and the feature set…
