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 list of only values where there are duplicates? There is no function in Excel to automatically create a list of non-unique values. However, with a combination of some existing functions, we can create a way to get a list of all duplicates in a an existing list. The Example File The example file that we will be using is a list of 100 random generated names. While all the nam ..read more

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 creating a LAMBDA() function which uses the BYROW() function to calculate income taxes payable. The LAMBDA() function, along with the example on this post is in the file below. MarginalTaxDownload This function, which will be explained in the post below, is as follows: LAMBDA(taxableamount, taxgrid, SUM( BYROW( taxgrid, LAMBDA(row, LET(lb ..read more

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 Excel using the UNIQUE() function in Excel. The UNIQUE() function can take a single column, and extract unique values from the column. However; when there are multiple columns, the UNIQUE() function will look at the entire set of data, and will look row-by-row. This will not provide a usable unique list! We will be looking at 3 methods to get unique values from two separate co ..read more

LAMBDA() is one of the most exciting functions to be developed in Excel since the introduction of dynamic arrays. This special function allows a function to be created by a user and then re-used throughout the workbook. These functions are similar to user-defined functions in VBA but do not require elevated security permissions. In this example, we will be creating a loan amortization schedule, which is a commonly used financial schedule written in Excel. However; in the example, we will be using in this post, we will be generating the entire schedule dynamically with 1 LAMBDA() function. This ..read more

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 easier to read, but faster as well! Prerequisites The LET function is available in the following versions of Excel: Office 365 Office 365 for Mac Excel Online Unfortunately, this function is available only in subscription versions of Excel at this time. Excel 2019 users do not get to take advantage of this exciting new formula! The Example Data To demonstrate the LET fu ..read more

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 automatically rounded off to the nearest billion, million or thousand. The visual rounds to the nearest million…or the nearest thousand if there is less than a million! However; dashboards in Excel are more likely to use the values that are in cells instead of on cards like in Power BI. To take your dashboards to the next level, we can create cards that look and beha ..read more

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 example, we will be using random data to simulate scores of people playing a game. The formulas we will use include: SEQUENCE() RANDARRAY() SORT() FILTER() LARGE() Other than LARGE(), these functions are only available in Office 365. Getting Started Before getting into the heavier Excel formulas, we start by setting up the number of players. We also set up the number of top scores to get ..read more

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 are more technical in nature (e.g. proper use of a CALCULATE function). Others are more conceptual in nature (e.g. pie graph vs. line graph). While having some real world experience with Power BI will make some questions easier, there will likely be some scenarios that you have not experienced in your daily job! Setting up for the DA-100 Exam at Home As I wrote this ex ..read more

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, we’ll compare the differences between how to get data from cells in Excel and Excel Online. Getting Started Before getting started with Office Script, you need to ensure that Office Scripts are available for recording. This is detailed in a prior post; recording your first Office Script. For VBA, no special settings are required. The Test Output Since we’re just looking t ..read more

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 to use experience for getting started in automating your Excel Online workbooks! Getting Started You need to have the following before getting started in Office Script and Power Apps: An educational, business or enterprise license of Office 365 This sort of license is required in order to have Power Apps and to run Office Scripts. Office Scripts must be enabl ..read more