Posts in category: Uncategorized

A new feature Excel has recently rolled out is the ability to create Lambda functions. In short, this is a way to create custom functions within Excel that utilize variables. Previously, you would have needed to use VBA to accomplish this. But as long as you’re running Microsoft 365 and have the latest version of Excel, you can do that yourself. Here’s a quick overview of how it works.

Creating a simple Lambda function

Similar to how you might create named ranges, you can easily create a new Lambda function within the name manager in Excel. Click on the Formulas tab and then click on the Name Manager button:

Then, click on the New button, which will then give you the following dialog box:

I’ll set the name equal to times5 because what I want to do is take a value and multiply it by 5. In a Lambda function, I need to specify any variables, and then, what I want the function to do. I’ll use x as my variable and multiply it by 5 in this formula:

=LAMBDA(x,x*5)

I’ll plug that into the named range so that it is filled out as follows:

When I click OK, the function is created. Now I can go into Excel and reference the function by using its name:

=times5(10)

That will return a value of 50 as it will take the x value (10) and multiply it by 5.

Creating a Lambda function with multiple variables

Let’s move on to a more complex function. This time, I will calculate the compounded annual growth rate. For this, I will need a percent change and the number of years over which it will happen. Let’s say an investment grew by 200% over a period of 10 years. I want to know how much it would have averaged each year to compound to such a return. The formula to calculate compounded annual growth rate is as follows:

(1 + percent change)  ^ ( 1 / # of Years) - 1

The calculation in this case ends up being 3^(1/10)-1 which returns a value of 0.116, or 11.6% after converting the value into a percentage.

To convert this into a function, I’ll set the percent change as x and the # of years as y:

=LAMBDA(x,y,(1+x)^(1/y)-1)

The first two arguments are variables as there is no calculation taking place. But the Lambda function recognizes that what follows afterward is the actual computation that needs to happen. I’ll create this as another named range, using CAGR as the name of this function:

Now, to reference this within Excel, I’ll call the function as follows:

=cagr(2,10)

Where 2 refers to the percent change (200%) and 10 is the number of years. This returns the same value of 11.6% as when doing it manually through the calculation. The benefit of using a Lambda function is that it can easily be reused within the workbook without having to regenerate the formula over and over again.

If you liked this post on What Are Lambda Functions, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

Conditional formatting in Excel can help you do a lot of things. If you have a range of data, using colors can help show where the high values are and where the low ones are. It can also be used for more complex analysis, especially since you can also use formulas in conditional formatting. In this post, I’ll show you how you can use it to analyze a company’s financial data.

Use conditional formatting to find changes in values

In this spreadsheet, I have some key financial numbers from Nike’s recent quarterly results;

You may be tempted to create additional columns or rows or even another sheet to help calculate the percentage changes and differences from prior periods. But instead of doing that, you can use conditional formatting to highlight important movements. For example, I’m going to create a conditional formatting rule to highlight cells that are higher than the previous period. That will allow me to easily see values that are increasing. And at the same time, a lack of formatting would suggest that they are declining.

To set this up, I’ll select the rows I want to analyze (in this case, anything from row 3 down), and under the Conditional Formatting drop down on the Home tab, I’ll create a new rule. I’ll select the option to use a formula to determine which cells to format:

I have a place to enter my formula. Since the first value in the range I selected was cell B3 (I started from the third row, second column), everything in my formula will be relative to that starting point. If I want to see if there has been an increase in value from the previous period, I’ll use the following formula:

=B3>C3

It’s a simple formula that checks if the current value is greater than the cell next to it. And because it’s relative, when the rule is applied to cell C3, it will look at whether C3 is greater than D3. It will then adjust for all the other cells. If I apply a green background and white text formatting for this rule, then my spreadsheet now looks like this after applying the conditional formatting:

Right away, you can spot the green-highlighted cells that show values that increased from the previous period. And any that aren’t highlighted in green, we can see have been declining. For example, we can see that the company’s sales for the past three quarters aren’t highlighted in green. That tells us sales have been falling for three straight periods. In the Apparel row, before this most recent quarter, sales were increasing for three straight periods as shown by the three consecutive green-highlighted cells.

We can apply more complex highlighting than this. For instance, let’s also emphasize any values that jumped by more than 10% just to make it even more evident when the company had a strong performance. To do this, I’ll again create a new conditional formatting rule. But this time, I’ll use the following formula:

=B3>(1.1*C3)

If the current cell is more than 10% of the cell next to it, then I will highlight a darker green formatting, italicize, and bold the value. Here’s what that looks like after adding that rule:

Now, it’s easier to see large changes in values. For example, there was a significant increase in sales in Greater China in Q3, and the level of inventories also moved by more than 10% compared to the previous period. We can see both rules being applied, both the 10% increases and the non-10% increases that were positive.

Another rule I’ll also add is to strictly look at the profit margin (row 20) and to highlight any values that are less than 10%, as Nike normally generates profits that are higher than that. So this will help highlight any periods that weren’t terribly strong. My formula for this rule is as follows:

=B20<0.1

When applied, now my data looks like this:

There’s even more formatting now. And the inevitable problem with adding too many rules is that they may end up overlapping one another. For example, the 9.5% profit margin, while below 10% and highlighted in yellow, was also higher than the 8.38% profit margin in the previous period. Technically, multiple rules apply. This is where it’s important to manage your hierarchy.

How to rank the priority for conditional formatting rules

To determine which rule gets more importance, you’ll need to go to the Conditional Formatting drop down again and go to the Manage Rules section. If you don’t see all of them there, make sure you have the entire worksheet selected:

Once you see all the rules, you can use the arrow keys to move them up and down. By default, the more recent ones go to the top. But let’s suppose I want the profit margin rule to be last in terms of importance. I can move that one down so my hierarchy is now as follows:

With this hierarchy, now I only see one cell highlighted in yellow:

Since the 9.5% profit margin was a 10% increase from the previous period, the first conditional formatting rule applies and takes priority over the others.

If you liked this post on How Conditional Formatting Can Help Analyze Financial Data in Seconds, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.