Learn Dashboards, Excel, Power BI, Power Query, Power Pivot
The first powerful function I learnt back in my accounting days was VLOOKUP. It gave me a sense of power and cemented my love of Excel. Oh, how I wish I’d had the fortune of the new Excel XLOOKUP function back then. XLOOKUP is everything VLOOKUP is and much more. XLOOKUP can look up to …
The Excel XOR function, or ‘exclusive OR’ as it’s known in the programming world, performs a test on an array, range or one or more expressions. It was introduced in Excel 2013. Syntax: =XOR( logical1, [logical2],...) logical1 An expression (a formula that results in a single value), array or reference that evaluates to TRUE or …
Comparing two lists is easily done with Power Query, but maybe you prefer to use a formula to extract values present in two lists. Before dynamic array formulas this was a daunting formula to write, but the new FILTER function makes it dead easy. In this tutorial I’ll show you both methods. Note: The FILTER …
Creating a reducing Data Validation list is easy with the new dynamic array formulas. Let’s say we have a list of jobs currently in progress (column B in the image below). Each contractor can work on no more than two jobs and we have four contractors available (column E). As we assign contractors to jobs …
Typically if you need to lookup a value from a table you'd employ VLOOKUP or INDEX/MATCH. However if your data looks like this and you are looking up by Employee name then you'll run into some issues because there are two employees with the name John. Both the VLOOKUP and INDEX/MATCH approach will have problems …
Excel Goal Seek is another tool in the What-if Analysis suite on the Data tab of the ribbon. Goal Seek will find the value required to meet a target value you specify. For example, let’s say your 13 year old son wants to save $10,000 to buy a car when they graduate school. They have …
Dates and times are stored in Excel as decimals known as serial numbers. The date is represented by the whole number part of the serial number and the time by the decimal part. In Excel dates start from 1 Jan 1900, which is represented by the number 1. When you see a date or a …
Excel Data Tables are one of the What-if Analysis tools that we have available to aid our decision making. They don't require knowledge of any new fancy formulas and are super quick to build. With Excel Data Tables we can perform what-if analysis with one or two variables, which makes it quick and easy to …
The Excel CONCAT function, new in Excel 2019, replaces the legacy CONCATENATE function*. It’s a simple function that joins the values from two or more cells together into a single text string. Syntax: =CONCAT(text1, [text2], …) Where text1 is the contents of a cell or cells containing text, numbers, references to other cells, formulas, or …
If we want to find whether or not a character or string occurs within another string we can use FIND or SEARCH. But these functions only tell us if a string (or character) exist in another string, they don't tell us if the string we are looking for occurs multiple times. For example, we have …
