H2Econvertpercent

How to Convert Percent to Decimal

There are numerous ways to display numbers in your reports. Using percentages and decimals are two common ways to do so. In this post, I’ll cover when it might makes sense to use percentages and when to decimals may be more appropriate. I’ll also provide you with some easy-to-use formulas that will allow you to convert percent to decimal, and vice versa. You can do these calculations whether you’re in Excel or just have a calculator handy.

Converting between percent and decimal

If you want to convert numbers between percent and decimal, the process is incredibly simply in Excel. Select the value(s) you want to change and then select the format you want.

However, if you’re not using Excel, you can still accomplish this manually. To convert a percent into a decimal, all you need to do is to pretend you’re moving the % sign two spots to the left, and then convert it into a period.

In the case of 50% it becomes .50, or 0.50, depending on whether you want to display the 0 in front. This also works with large percentages, such as 1,000%. That’s a significant percent, but the same logic applies, and following the same steps would convert the value to 10. That tells you that the new value is 10 times the size of the original value.

To convert back into percent, you multiply the value by 100 and drop the % sign.

Formula for converting decimal to percent:

Formula to convert decimal to percent.

Formula for converting percent to decimal:

Formula to convert decimal to percent.

How to calculate percent of something versus percent change

One important distinction you should consider is to determine whether you’re looking at a portion of something, or a change in value. If you’ve eating half of a pizza, that’s 0.5 of it, or 50% of it. But if you’re talking about the price of something going up by 10%, that’s a slightly different calculation.

Let’s take the stock price of Microsoft as an example. At the start of 2020, its stock price opened at $158.78. By the end of 2021, it finished the year at $339.32. If we take the ending price and divide by the beginning price (339.32/158.78), then that gives us 2.14, or 214%. It would be correct to say that $339.32 is 2.14 times $158.78. But it would not be correct to say the stock price increased by that amount. That’s because you’re not calculating the actual increase in value. To do that, you need to subtract 1, to arrive at 1.14, or 114%.

If the stock didn’t increase at all, you would be left with an equation of 158.78/158.78, which would be equal to 1, or 100%. The ending value was 100% of the beginning value, but it certainly wasn’t an increase of 100%. Thus, the need to deduct 1 will give us the correct answer in that case — a 0% change. The same goes for decreases. Suppose the stock fell by 30% to $111.15. Dividing $111.15 by $158.78 would tell you the price is now 70% of the value it was at the start of the year. That’s correct, but to get the percent change, you deduct 1 from that, which tells you it declined by 30%.

The formula for percent change:

Formula to calculate percent change.

The formula to calculate the portion or relative size of something:

Formula to calculate portion or relative size.

It’s a subtle difference but it’s an important one to note, which can prevent you from making a mistake in your calculations.

What to do if your decimals or percentages are very small

If you’re dealing with numbers that go to four or five decimal places, it may not be helpful to display them as percentages or even as decimals. For example, if you were to say the odds of getting struck by lightning in your life were 0.00654% or 0.0000654, whether you use decimal or percent isn’t going to be helpful in conveying those adds.

One thing you can do is flip those numbers around by calculating the inverse. By taking 1/0.0000654, that returns a value of approximately 15,300. Stated another way, it tells us that the odds of getting struck by lightning are 1 in 15,300. It’s a far more effective way of communicating the odds as you’re no longer dealing with miniscule percentages that can be hard to visualize.

The formula for converting from a decimal value to a rate:

Formula for converting a decimal value to a rate.

As you can see from these formulas, they are fairly simple and can be incorporated into your spreadsheet and even done just on a calculator. You could create LAMBDA functions with these formulas, but they involve so few steps that the time savings may not amount to much.


If you liked this post on How to Convert Percent to Decimal, 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.

APRExcel

How to Calculate APR in Excel

The Annual Percentage Rate (APR) on a loan tells you what your total borrowing cost is per year. It’s different from just the interest rate because it will include other expenses and fees relating to a loan. And for that reason, the APR will be higher than the interest rate. If there are no additional fees, then it will be the same. In this post, I’ll show you how you can calculate APR in Excel and how it compares with just the interest rate itself.

Functions used to calculate APR in Excel

In order to calculate APR in Excel, there’s a simple function that can allow you to do that quickly and easily; there’s no need to draw out and complicated formulas for APR that you might find online. The key is to just determine your inputs and the variables you will be using for the calculation.

The RATE function in Excel will take care of the calculation but it requires the following inputs:

  • Number of periods
  • Payment amount
  • Present value
  • Future value

The only one of these that may require some additional work is the payment amount. However, there’s also a function for that too, called PMT.

Calculating APR using an example

I’ll illustrate how you can calculate APR in Excel by using an example. Suppose you’re taking out a loan for $200,000 where you’ll incur financing fees of $30,000. The interest rate will be 4% and term is 10 years. And payments are made on a monthly basis.

For starters, we need to calculate the monthly payment amount. That requires similar inputs to the RATE function except instead of a payment amount, we need the interest rate. And since payments are going to be made on a monthly basis, both the interest rate and the term needs to be expressed in months. Instead of 4%, the rate we’ll need to use is 4%/12 and the term will be 120 months.

The PMT formula will look as follows:

=PMT(0.04/12,120,-200000,0)

Instead of entering in the interest rate to the decimal point, I’ve left it is a fraction so that the calculation is more precise. The present value is entered as a negative since the 200,000 is what is currently owed, while the future value (0) is what it will be when the loan is paid off.

The result of this calculation results in a monthly payment amount of $2,024.90.

In this scenario, there are no finance charges included. To factor those in, simply change the present value to -230,000. By doing that, you’ll arrive at a monthly payment amount of $2,328.64.

Using those payment amounts, we can now calculate the APR, using the RATE function:

=RATE(120,2024.9,-200000,0)*12

The result of this formula is multiplied by 12 to get to annual rate. This is because the RATE function gives you the rate per individual period. At the monthly payment of $2,204.90 (i.e. when there are no financing charges), the formula for APR results in a value of 4%, which is equivalent to the interest rate. Since there are no additional fees here, it makes sense that the percentages are the same.

However, when using the higher payment amount for the loan that includes finance charges, there will be a more noticeable difference. For that calculation, the formula is as follows:

=RATE(120,2328.64,-200000,0)*12

What you’ll notice here is that while the payment amount has changed, the loan remains the same. This is because while we need to pay for the extra finance charges and they are added to the monthly payment, the value we would be receiving remains just $200,000. Through this calculation, we arrive at an APR of 7.06%.

The higher the additional fees and charges, the bigger the delta will be between the APR and the interest rate.

Creating the amortization tables

The differences in these rates can also be demonstrated through an amortization table to show how the loan is paid off. For an amortization table, we need the following fields:

  • Payment #
  • Beginning Balance
  • Principal Payment
  • Interest Expense
  • Ending Balance

The beginning balance will be the total that needs to be paid off. The interest expense is the calculated by taking the balance and multiplying it by the interest rate. What’s left over from the monthly payment goes towards the principal. How much is paid off is reduced from the beginning balance to arrive at the ending balance. Here’s how the table will look in the first scenario, where there were no additional finance charges:

Amortization table with no financing charges on a 200,000 loan.

This excerpt shows the first 20 payments. The following is the amortization table for the second scenario, where finance charges total $30,000:

Amortization table with financing charges of $30,000 on a 200,000 loan.

Here you can see that with a higher balance, more of the payments are going towards interest at the start. Although it takes the same amount of time to pay off the loan, higher payments are necessary to account for the increase in the beginning loan amount.


If you like this post on How to Calculate APR in Excel, 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.

NestedIF

How to Avoid Using Nested IF Statements in Excel

Nested IF statements aren’t always the most efficient way to structure your formulas. And they can make it difficult later on if you need to fix a formula or make a change to it. What’s worse, is if you inherit someone’s spreadsheet and try to dissect their nested IF functions. In this post, I’ll show you how you can get around using nested IF statements and the different alternatives you can use.

How nested IF statements works

To start, let’s look at how you might construct nested IF statements. Here’s a data set that has different cardholders and their related expenses:

Data set showing cardholder expenditures by store and category.

I’m going to create a column with a series of IF statements to see how much Bob spent on home repair since the start of 2021. With a nested IF statement, I might first check if the cardholder is Bob. Then, if that’s true, check if the category is Home Repair. And then, check if the date is after Jan 1, 2021. Here’s how that would look inside of a formula:

=IF(B2="Bob",IF(D2="Home Repair",IF(A2>DATE(2021,1,1),E2,0),0),0)

You can see this starts to get pretty messy. And the IF statements could continue going on if you have even more criteria you want to fit into here. If I were to copy this formula down, I could get a total of all the values where Bob spent money on Home Repair. However, this wouldn’t be terribly efficient.

You could use a pivot table to quickly summarize the data by cardholder spending and category. But for this example, let’s assume that you need to do it within a formula and can’t rely on creating a pivot table when doing these types of calculations.

Using the AND function to group multiple criteria

An effective option in making your nested IF functions shorter is by using the AND function. It allows you to put all your conditions in one neat formula that you can embed within an IF function. Within the AND function, I can enter all these arguments:

AND(B2="Bob",D2="Home Repair",A2>DATE(2021,1,1))

You can keep on adding to conditions to the AND function for as many rules as you’d like to apply. This can make it cleaner to see all your criteria. All of the criteria within the AND function need to be met for the formula to return a TRUE value. Similarly, you can use the OR function if you want to check if any criteria are met.

The above formula can easily be embedded within the IF function as follows:

=IF(AND(B2="Bob",D2="Home Repair",A2>DATE(2021,1,1)),E2,0)

This does the same job as the nested IF formula except it’s a lot cleaner. However, the drawback here is that like with the nested IF statement, if you wanted to calculate all of the instances where Bob spent money on Home Repair, you would need an extra column and ad all the values up. That’s still not very efficient.

Using an Array function

Another option you can use for quickly tabulating these results is by using an array function. This can apply the logic to every cell and calculate the total for you. Rather than IF and AND statements, you can evaluate each argument, force a 1 or 0, and then multiply that by the amount to arrive at a total. Here’s how that formula might look:

=(B2:B100="Bob")*(D2:D100="Home Repair")*(A2:A100>DATE(2021,1,1))*(E2:E100)

This formula extends to the bottom of my data set. How it works is that each group of parentheses represents an argument. If it evaluates to TRUE (i.e. the criteria is met) then the value becomes a 1. If the criteria is not met, then it evaluates to a 0. So if all the criteria is met, the results will be 1*1*1 multiplied by the amount in column E. If any one of the conditions is not met, then the result will be a 0. This is the same method as the earlier examples.

The downside of an array is that it will automatically extend to the bottom of the data set:

Array formula automatically extended to the bottom of the data set.

This again runs into a similar limitation where your formula of using up more cells than you might want to occupy. But to get around this, you could add the SUM function before your array formula:

=SUM((B2:B110="Bob")*(D2:D110="Home Repair")*(A2:A110>DATE(2021,1,1))*(E2:E110))

Using SUMPRODUCT

Another function that can do the job is SUMPRODUCT. With this function, it can take care of all the criteria while also summing up the total in just one cell. The logic is similar to how the array formula was calculated above. The key difference here is to put that all within the SUMPRODUCT function. Here’s how it looks:

=SUMPRODUCT((B2:B110="Bob")*(D2:D110="Home Repair")*(A2:A110>DATE(2021,1,1))*(E2:E110))

This will obtain the same result as if you were using the array function. SUMPRODUCT is used for multiplying arrays but it can be made to work in this fashion as well. The key is making sure you encompass all the arguments withing parentheses (hence why I opened and closed SUMPRODUCT with not one but two parentheses.

As you can see, there are many different ways you can make your formulas more efficient in Excel without having to rely on nested IF functions.


If you like this post on How to Avoid Using Nested IF Statements in Excel, 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.

ChartwithTargetLine

How to Create a Chart With a Target Line

Are you creating a chart that shows progress, with a certain goal in mind? In this post, I’ll show you how to create a chart with a target line so that you can see how close you are progressing toward your goal.

A common example for this type of chart is where you are reporting monthly sales and have a goal you want to reach for the year. Here’s a chart that shows the monthly revenue and has a cumulative total as well:

Chart showing monthly and cumulative sales.

Creating the target line

To create a target line, I need to add another series to this chart. For example, let’s say your goal is for sales to hit $50,000 for the year. To do that, you just need to create another series. I’ll call it ‘Target’ and for each of the values, I’ll enter in $50,000:

Excel table showing monthly and cumulative sales alongside a target.

You don’t need to enter $50,000 manually into each cell. You could use the autofill to copy the values down. However, a more flexible way to do this is to enter $50,000 into the first cell, and use a formula to refer to that cell. That way, if you change your target amount, you only need to make the change in one cell.

If you’ve already created your chart and want to add the line to your chart, you’ll need to right-click on the chart and click Select Data. Then, adjust your chart range so that it includes the extra column, and then you’ll see your chart update with the line. If you are creating a chart from scratch, then you just have to select the correct range when first creating it.

Chart showing monthly and cumulative values with a target line.

One additional thing you may want to do at this stage is to adjust the formatting of the target line. A good idea can be to make it look different from the other lines on your chart. One way you can do this is by using dashes. If you click on the target line, you will see a pane show up on the right-hand side showing you options to format the data series. Click on the paint bucket icon and you’ll see various settings for the line. There is one option for the Dash type which will allow you to show the line as breaking up as opposed to being solid:

Changing the dash type for a line chart.

After also changing the color to a solid black, this is what my chart looks like with these changes:


If you like this post on How to Create a Chart With a Target Line, 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.

LookupsPowerQuery

How to Merge Queries in Power Query

Do you want to do a lookup in Power Query, or just join multiple tables together? In this post, I’ll show you how you can do that. The first thing you need to do is set up each individual query so that it is accessible in Power Query.

In my data set, I have various indicators for countries across the world. In one table, I have the data and the country code:

Data table showing various indicators by country.

On another table, I have a list of those country codes and more detailed information about which parts of the world they relate to:

Table showing detailed region and country information.

Naturally, I want to combine this information. It’s the equivalent of doing a lookup, except within Power Query. I can do a lookup before populating the data into Power Query, but I can also just merge the queries.

Once you have the queries loaded in Power Query, you can go ahead and start merging them. There is a Merge Queries button on the Home Tab, in the Combine section:

The merge button in Power Query.

For best practices, you should switch to your main query, the one that holds the data you’ll primarily be using, and then click on the button. By doing this, you can avoid having to adjust the join type. Once you press the Merge Queries button, you’ll see the following options:

Merge query asking you which other table you want to merge data with.

The Data query is the initial one that shows up as that is the one I was on when clicking the merge button. I’ll have to select a table I want to merge with (in this case, it will be the one with the country information). After selecting the table to merge with, I’ll also need to highlight the columns that connects the two queries. In this case, it is the Country Code, which I’ve highlighted in both tables:

Selecting a common column between two queries.

It doesn’t need to be a one-to-one relationship but if it isn’t, then a single row will end up expanding into more for each match that’s found. The last thing you need to specify before deploying the merge is determining the join kind. There are several options for this:

Selecting the join kind in Power Query.

If you don’t want to lose any data from your main table, then you’ll want to look at one of the first three options. In this situation, where you’re adding data from another table, you’ll either use the Left Outer or Right Outer join. This is where first selecting your main table before clicking on the merge button will make this easier for you. That’s because since it would be the first table, a Left Outer join (the default option) would suffice. In a Left Outer join, you’re keeping all the records from the initial table and only adding matching ones from the second. If your first table is the main one you’ll want to be using, then the Left Outer join will work best. If you didn’t do that, then the Right Outer will be what you want.

When in doubt, look at the description in parentheses to guide your decision to see what each join will do. Once you’ve selected the join type, click on OK. Now, you should see a new column that contains tables for each row:

New column with the merged table contained within it.

To expand these tables, you can click on the button in the Countries header, which shows two arrows going in opposite directions:

Power Query button to extract fields from a table.

When you click on that, you’ll be able to select all the fields that you want to extract from the other table:

Selecting the fields you want to pull from another query.

For this purpose, I’ll only leave the Short Name checked off since I don’t want to make my query unnecessarily large. I’ll also uncheck the tick box at the bottom that by default will leave the original column name as a prefix. After clicking OK, I now have the short name populated in my main query. All that’s left is to move the short name back to the beginning, next to the country code. Now my merge looks complete:

Power Query table after merging multiple tables.

If you liked this post on How to Merge Queries in Power Query, 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.

3dbubblechart

How to Make 3D Bubble Charts in Excel

Regular 2D charts in Excel can be useful if you want to compare two metrics. But if you want a third, that’s where knowing how to make 3D bubble charts can be incredibly effective. It can allow you to pile in a lot of data into just a single visual. It can take a bit longer to set up the chart just how you want to, but it can pay off in the end. Here’s how to do it.

Determining which values to plot where

In a 3D bubble chart, you have an X and Y axis, plus you can also specify the size of the bubble. The field that contains the largest variances will probably be the most appropriate one to use as the bubble size, since that will make it easier to differentiate large values from smaller ones.

In my example, I have country data that shows GDP per capita, life expectancy, and alcohol consumption per capita. I suspect the biggest variances might be in GDP per capita, and so that’s what I’m going to use as my bubble. Then for the X and Y axis, I’ll plot life expectancy against alcohol consumption. Here’s an excerpt of what my data looks like:

Country data loaded into Excel showing GDP per capita, life expectancy, and alcohol consumption.

Be selecting in choosing which values to plot

In a 3D chart, the size of the bubbles can get large, and that means there can be limited space to work with. For that reason, it’s important not to plot dozens of different data points. In my data set, I have more than 160 countries, which is far too many to plot on a 3D chart.

One way to filter a large data set like this is by creating a separate table, one that utilizes a lookup to extract the same values. This can be a useful way to dynamically update your data. You can use the VLOOKUP function to extract the data. And rather than doing it one by one, you can use VLOOKUP to extract multiple columns through just a single formula. My filtered data set has the following countries in it:

Filtered table showing country GDP, life expectancy, and alcohol consumption.

Plotting the data onto a 3D bubble chart

The next step no involves creating the bubble chart itself. For this, go to the Insert tab and select the bubble chart from the X Y (Scatter) section. Initially, it may not look correct, but that’s fine. Remove all the series and start adding them one by one. To modify the data, right-click on the chart and click on Select Data. Click on Add to add a new series, where you will see the following places to enter data:

Adding a data series to a 3D bubble chart in Excel.

In my example, here’s how I will fill it out:

  • Series Name is the name of the country
  • X values will relate to the life expectancy
  • Y values will relate to the alcohol consumption per capita.
  • Series bubble size is the GDP per capita

Repeat these steps for each of the data points you want to add to your chart. Although this may be cumbersome, by using the VLOOKUP, these values can quickly be updated and changed by simply changing the lookup value (in my example, it’s the country name).

3D bubble chart in Excel.

If you liked this post on How to Make 3D Bubble Charts in Excel, 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.

H2E GoogleTrends

How to Get Google Trends Data Into Excel

Want to find the latest trending items on Google? You could go to trends.google.com, or you could import that data into your Excel file. In this post, I’ll show you how.

To get the latest trending topics on Google, you can go to https://trends.google.com/trends/trendingsearches/daily?geo=US. You can adjust the string at the end if you want to see trends from a different country, but the link I’m using here is for the U.S.

Use Power Query to import the data

One of the most effective ways of getting data into Excel from an external site is by using Power Query. The key is finding the right link. On the Trending Searches page in Google, there’s an RSS button that you’ll want to click on:

RSS button on the Google Trends page.

Then, you should see the following URL:

https://trends.google.com/trends/trendingsearches/daily/rss?geo=US

That is the URL you’ll need to use in Power Query. Back in Excel, go to the Data tab and select the From Web button. Put that link in the following box:

Entering the web location for a new Power Query session.

When the Power Query window loads up, you might be tricked into thinking there is nothing there:

Power Query showing only one table.

Expanding the data

The data is there, it’s just contained within a table. Click on Transform Data at the bottom of the Power Query window. Then, click on the icon in the channel field that shows that there are items within there that you can expand:

Selecting the icon to expand a table in Power Query.

Click on the OK button in the following dialog box where you can specify all the columns you can add. There are many embedded tables here and if you want to see what’s contained within them, simply click on one of them that says Table. For example, if I click on the Table shown under the channel.item field, I see all the values contained within that table:

Power Query showing a preview of an embedded table.

Within here, I can see that there are still multiple tables that I can expand, and there’s also a title column that looks to contain the actual trending item name. You can continue expanding depending on the fields you want to see. You can expand everything, and then at the end select which columns you want to keep. If you do expand everything, you’ll end up with some unnecessary columns such as channel.link which contains the same repeating value. To remove a column, simply right-click it and click on Remove:

Removing a column from Power Query.

Repeat this step for all columns you want to eliminate. Alternatively, you can select the columns you want to keep, and then select the option to Remove Other Columns. Either approach will work the same. The columns that I’ve chosen to keep include the title, the number of searches, the two description columns, as well as the time of publication:

Power Query window after removing several columns.

Cleaning up the table

There are a few items I can clean up to make this import nicer into Excel. The first is to make those numbers actually read as numbers. Because of the + sign at the end, I can’t convert them into whole numbers. What I will do is select the entire column, and then click on the Repace Values button under the Transform section in Power Query. Then I am effectively doing a find and replace, to swap the + signs with nothing:

Replacing values in Power Query.

Now, the format of that column can be changed to a whole number. I can do that by clicking on the ABC letters (indicating it is a text field) and selecting the option for Whole Number:

Converting a field in Power Query to a Whole Number.

Upon doing so, you’ll notice the values align to the right, meaning they are now being read as numbers. Now they can be sorted in descending order so that the most popular items are at the top.

Another change I will make is to replace some more values in the two description fields:

  • Replace ' with an apostrophe (‘)
  • Replace   with a space (‘ ‘)
  • Replace " with quotation marks (“)

There are also some duplicate items in the title field that I’ll remove. To do that, right-click on the field name and select Remove Duplicates. The last thing I’ll do is re-name the headers.

This is what my Power Query table looks like after all these changes:

Power Query window after removing duplicates.

Now I can click on Close & Load to get the data into Excel:

Power Query data loaded in Excel.

Now, all you need to do to load the latest trending data into Excel is just to refresh the query.


If you liked this post on How to Get Google Trends Data Into Excel, 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.

H2ENamedRangePQ

How to Use Named Ranges in Power Query

Power Query is a useful way to pull in data and make changes to it. The steps get recorded as you make them to your data set. But by doing so, you can also be hardcoding some of those steps along the way. That’s a no-no. When possible, you should make your formulas, macros, and templates as dynamic as possible. One way you can do that in Power Query is by using named ranges. In this post, I’ll show you how you to do that.

Start with creating a regular named range

In this example, I’m going to use a named range to make it easy to change the markup on products. Here is a list of some generic products and prices:

Excel table showing products and costs.

In Cell E2, I have my markup percentage. I’m going to create a named range called ‘markup’ for this cell.

Assigning that named range in Power Query

Next, I’ll create a Power Query connection by selecting any of the values above in columns A or B. Under the Data tab, selecting From Table/Range under the Get & Transform Data section will launch Power Query. From that screen, you can launch the Advanced Editor under the Query section of the Home tab. That will open up a new window where the code is stored:

Advanced Editor window in Power Query.

There is not a whole lot there right now just because the only thing I’ve done thus far is just link to the Excel table. Above the Source line, I’m going to add my named range. I’ll call this Markup as well. The formula to reference my cell is as follows:

Markup = Excel.CurrentWorkbook(){[Name="markup"]}[Content]{0}[Column1]

As you can see the named range of ‘markup’ is referenced and it’s important it is spelled exactly the same way as the code in Power Query is case-sensitive. The above code creates a table and by referencing {0}[Column1] I’m directing it to the value in the first row, and first column. It’s an important part of the code, otherwise, you will get something that Power Query doesn’t recognize as being a single value. Add the above line, along with a comma after it in the code. Here’s how it should look:

Advanced Editor in Power Query with a named range referencing markup.

When I close out of that and go back into the main Power Query window, there’s a new step called Markup where I’ve assigned my named range. Now all that’s left is to do something with this named range.

Create a custom column to use a named range in a calculation

On the Add Column tab in Power Query, there’s an option to create a Custom Column. If you click on that, you’ll see the following window, where you can create a formula:

Creating a custom column in Power Query.

You can double-click the available columns off on the right to insert them into the formula. This will be a fairly straightforward formula where I’ll take the cost and multiply it by 1 plus the Markup variable I created earlier:

Custom column created in Power Query to calculate price.

Now a new column has been created:

Power Query after adding a price column.

And that’s it. Now I can hit Close & Load to get back into the worksheet, where I’ll now have a price column. Changing the markup value in the sheet and then refreshing the data will update the price column to reflect the new markup percentage.


If you liked this post on How to Use Named Ranges in Power Query, 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.

H2ELambda

What Are Lambda Functions?

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:

The name manager button in Excel.

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

Creating a new named range in Excel.

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:

Lambda function created as a named range.

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:

Creating a Lambda function in Excel to calculate compounded annual growth rate.

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.

H2Efinancialdataconditionalformatting

How Conditional Formatting Can Analyze Financial Data in Seconds

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;

Nike's key financial data for the past two years.

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:

Creating a new conditional formatting rule using a formula.

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:

Nike's financial data after apply a conditional formatting rule.

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:

Multiple conditional formatting rules are applied to financial data.

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:

Three conditional formatting rules applied to Nike's financial data.

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:

Managing conditional formatting rules in Excel.

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:

Moving conditional formatting rules up and down to change their priority.

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

Financial data showing multiple conditional formatting rules applied to it.

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.