How to Use VLOOKUP in Excel
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…
Wildcards in XLOOKUP
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…
SUBTOTAL Examples
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.
XLOOKUP Case Sensitivity
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…
Grouping Rows in Excel
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…
Fast Data Load
Fast Data Load is a Power Query feature that makes loading queries faster by utilising additional CPU power and RAM. However, the drawback is Excel will become unresponsive for this duration, so it’s not recommended if you want to work…
