In my last post, I detailed how to replace existing VLOOKUP and other lookup formulas with XLOOKUP. However; XLOOKUP is more than just a replacement to the VLOOKUP and INDEX / MATCH functions! XLOOKUP adds some additional functionality that was …
How to use XLOOKUP in Excel to replace existing LOOKUP functions.
In September 2019, Microsoft introduced a new function to help speed up the way that data lookups are performed. As of February 2020, the XLOOKUP in Excel function is still in testing; however, upon release, it will change the way …
What is Advanced Excel?
Advanced Excel is a skill that is in high demand. A quick query on LinkedIn showed over 68,000 jobs worldwide requesting advanced Excel, as of January 2020. The jobs that were showing on LinkedIn are generally for financial jobs, such …
Power Query – Clean data for the Data Model
In previous exercises, we used the FILTER() function in order to remove the totals from our data model. Using Power Query, clean data is provided to the data model. Filtering before the data model improves the performance of the model, …
INDEX / MATCH – How does it work?
Up until the release of the XLOOKUP function, there has been significant debate in the Excel community around which function is better – VLOOKUP, or INDEX / MATCH. But, what is INDEX / MATCH? This is a combination of 2 …
ALL() and CALCULATE() – Percentage of a column
In the last post, we started using the CALCULATE() function to filter out some of the values that we didn’t want on our totals. This was done in order to get a pivot table that didn’t need to use the …
FILTER and CALCULATE – Removing pre-calculated totals.
In the last post, we started using the CALCULATE() function to filter out some of the values that we didn’t want on our totals. This was done in order to get a pivot table that didn’t need to use the …
Sum of a row using CALCULATE()
In the last post, we created a custom field in the Excel data model; however, we still needed to use Excel formulas to calculate the percentage of a row. In this example, we will be using a simple CALCULATE() function …
Custom Field for Quarterly Data in the Data Model
As part of the Quarterly Report case study, we built a report using the data model, but there were still some instances where we used Excel formulas. In this post, we will be creating a custom field so that there …
Quarterly Report – Using the Data Model
In this post, along prior two posts, I discussed a hypothetical case where data has been provided and your job (as the analyst) will be to create a quarterly report detailing sales of 2018 new vehicles in Canada. This post …