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 …
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!
LET Function in Excel: A Name Parsing Example
The LET function is one of the most exciting formulas to be added to Excel in 2020. This formula allows a spreadsheet maker the ability to create names for calculations (within a cell). These names not only make your formulas …
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 …
Top N Results from Excel using Dynamic Arrays
Getting the Top N results from a list of data, such as a Top 10 list is a very common Excel request. While this was possible before dynamic arrays, it has been made much easier using dynamic arrays. In this …
DA-100 – The Certified Data Analyst Exam
On September 25, 2020, I wrote and passed the DA-100 exam to become a Microsoft Certified Data Associate. This exam measures skills that are required in order to be successful at modeling data in Power BI. Some of the questions …
Ranges in Office Script – They are different than VBA!
Most of the time that you are working in Excel, you will be working with ranges. And working with ranges in Office Script is very different than working with ranges in Visual Basic for Applications! Using a very simple macro, …
Recording an Office Script in Excel Online
The newest form of automation in Excel is Office Script; this uses the power of TypeScript and the web to automate spreadsheets! In this post, we’ll be recording an Office Script using the script recorder. This recorder provides an easy …