When receiving a list of data, it can be desirable to find unique values. This can be accomplished in Excel using the UNIQUE() function, or by using other methods for getting unique values. But, what happens if you’d like a …
Tag: excel
BYROW() in Excel: A Marginal Tax Schedule Example
The BYROW() function allows calculations to be done on a row by row basis. This allows for new functions to be created that are similar to the SUMPRODUCT() function, but are much more customizable. In this example, we will be …
Unique Values from 2 Lists in Excel – Multiple Methods
There are many times where you will need to acquire a unique value from not just one list of values, but from two separate lists. Getting a unique value from a single list is straightforward, and can be accomplished in …
LAMBDA() in Excel – Generating an Amortization Schedule
Use the LAMBDA() function to create a loan amortization schedule!
Make a KPI Card like Power BI – In Excel!
In Power BI, one of the frequently used visuals is a key performance indicator, or KPI card. It allows to quickly hone in on key values for your dashboard. To avoid overwhelming users with incredibly high values, numbers are also …
Using XLOOKUP and TRANSPOSE
With XLOOKUP , it is now possible to return multiple values from a single function. However; when these values are returned, they show in multiple columns. If we want to have them appear by row, we have to use the …
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 …
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 …