XLOOKUP vs INDEX and MATCH

Introduction For decades, INDEX and MATCH have been the go-to solution for handling complex lookup problems. Unlike VLOOKUP, INDEX and MATCH are based on numeric positions: the MATCH function locates the position of a value, and the INDEX function retrieves a value at that position. This approach makes INDEX and MATCH highly versatile, at the cost of more configuration. Author  Dave Bruns

Posted on: 7 April 2023 | 10:03 am

XLOOKUP vs VLOOKUP

Introduction Excel offers a vast number of functions that cater to different users and their unique requirements. Among these functions, VLOOKUP has long been the go-to choice for basic lookups in a table or range. In almost every industry, millions and millions of existing spreadsheets use VLOOKUP to do something useful. Author  Dave Bruns

Posted on: 2 April 2023 | 1:41 pm

How to concatenate in Excel

One of the most important operations in Excel formulas is concatenation. In Excel formulas, concatenation is the process of joining one value to another to form a text string. The values being joined can be hardcoded text, cell references, or results from other formulas. There are two primary ways to concatenate in Excel: Author  Dave Bruns

Posted on: 7 May 2022 | 12:23 pm

How to ask a question about Excel

Every day, thousands of questions about Excel are posted on the internet. Many go unanswered because they are unclear, or too complicated to understand. Below are six tips to write a better question that will attract good answers quickly. 1. Search for existing solutions Try a search first to see if you can find an existing solution you can use or adapt. You can save a lot of time by finding a path others have already travelled. 2. State your goal clearly and briefly Be clear about what you want. For example: Author  Dave Bruns

Posted on: 13 March 2018 | 5:47 pm

29 ways to save time with Excel formulas

Formulas are the bread and butter of Excel. If you use Excel on a regular basis, I bet you use a lot of formulas. But crafting a working formula can take way too much time. In this article, I share some good tips to save you time when working with formulas in Excel. Video: 20 tips to save time with Excel formulas Author  Dave Bruns

Posted on: 29 January 2018 | 10:35 pm

CONCAT & TEXTJOIN

I've been playing around with the TEXTJOIN and CONCAT functions this week. These are both new functions in Excel 2016, introduced in the Office 365 subscription service. Both of these functions let you join (concatenate) text in different cells together. TEXTJOIN lets you join values with a delimiter of your choice, and has an option to ignore empty values. CONCAT simply mashes all values together without options. Author  Dave Bruns

Posted on: 14 October 2016 | 9:30 am

How to find text with a formula

Question: What formula tells you if A1 contains the text "apple"? This is a surprisingly tricky problem in Excel. The "obvious" answer is to use the FIND function to "look" for the text, like this: =FIND("apple",A1) Then, if you want a TRUE/FALSE result, add the IF function: Author  Dave Bruns

Posted on: 29 April 2016 | 8:56 am

20 very popular Excel shortcuts

We ran a survey on Excel shortcuts. Here are the results, based on over 800 replies. I've also compiled a list of the most 20 popular Excel shortcuts below. This is a tricky business, because people often don't know what to call shortcuts, or how to refer to them. So I've had to "interpret" many of the shortcuts people mentioned. That said, the patterns are clear, and the best shortcuts naturally bubble up again and again. It's a great list. Dave Author  Dave Bruns

Posted on: 14 January 2016 | 11:11 pm

How to turn off Scroll Lock in Excel

What is Scroll Lock? Usually, the arrow keys will move you one cell at a time in whatever direction you wish. However, when Scroll Lock is enabled, the worksheet is "scrolled" instead. The up and down arrow keys scroll one row up and down, and the right and left arrow keys scroll one column right and left. The active cell never changes. If you don't understand what's going on, this can be quite distressing :) Author  Dave Bruns

Posted on: 10 December 2015 | 4:33 pm

Top 10 reasons to learn Excel formulas

If you've ever wondered whether learning Excel formulas is worth your time, this list is for you! Formulas are the heart of spreadsheets, so your skill with them can help you in many ways. Read below for 10 reasons you should take time to increase your skill with formulas. 1. Formulas are important in many jobs. In a recent survey we conducted about formulas, over 90% of respondents said that Excel formulas were "important", "very important", or "vital" to their job. Author  Dave Bruns

Posted on: 23 November 2015 | 9:15 pm