Alphabet's historical stock price.

Use MAXIFS and MINIFS to Find Values Within a Range

Whether you’re using VLOOKUP or XLOOKUP, one limitation you’re going to face with those functions is that you can’t search within a range or use multiple criteria in your lookups. For example, suppose you’re looking at a stock’s history and wanted to know the last time it fell within a certain price range. You wouldn’t be able to do that with the aforementioned functions. But there is a way to accomplish that, using either MAXIFs or MINIFs. Here’s how.

Using MAXIFs and MINIFs as a lookup

With the MAXIFs and MINIFs functions, you are extracting either the smallest or largest data point in a range. And since you can apply multiple IF statements within these functions, you have the possibility to use multiple criteria. In the following example I have a list of Alphabet’s historical stock price going back multiple years:

Alphabet's historical stock price.

Let’s suppose I wanted to find the last time that the stock was trading between $70 and $80. This is how the formula would look, assuming the date is in column A and the closing price is in column B:


In column B, I have two criteria, one to check if the value is greater than or equal to the startprice variable ($70), and another to see if the value is less than or equal to the endprice variable ($80). Whenever that criteria is met, the value from column A is returned. And since the function is taking the maximum of those values, it will return the latest date in column A (i.e. the most recent, or the one closest to today’s date). If the date values were sorted in descending order rather than ascending order as they are above, then I would use the MINIFS function to get the same result.

Using the formula, it tells me that the last time Alphabet’s stock price was between $70 and $80 was on Oct. 29, 2020. And when looking at the range, it’s evident that looks to be correct:

Alphabet's historical stock price.

Without the use of ranges and utilizing MAXIFS, this would have been a much more difficult process. There are multiple ways to approach a lookup and it ultimately depends on the situation and what you need to accomplish. MAXIFS and MINIFS are particularly useful when working with dates. But in other situations, you may need to use a different function instead.

If you liked this post on Use MAXIFS and MINIFS to Create a Lookup With Multiple Criteria 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.


Free Expense Tracking Template in Google Sheets

Want an easy way to track your expenses? In this post, I’ll show you how you can use my free expense tracking template, which is based in Google Sheets. It makes copying and pasting expenses over simple, and you can quickly see reports that summarize your spending. No need for macros or even refreshing data.

How the expense tracker template works

There are just two tabs in the expense tracker template. One is Data tab where you enter all your expenses, and one is the Summary tab that has charts where no data entry is required.

Entering the data

On the Data tab, these are the following areas where you’ll enter in information:

  • Columns A & B are for the Vendor and Amount. Expenses should be positive and refunds are negative. This can just by copy and pasted from your bank or credit card statement downloads.

  • Column C is optional and only necessary if you don’t want an expense item to go to its default category. For example, you may have a one-time expense that throws off your budget for a spending category. You can check off the box for ‘Irregular’ and it will flow through to that category and bypass the default spending category.
Transactions entered on the expense tracking template.
  • Columns G and H are where you will set up your Vendors and which spending category they relate to. This is important so that when you enter your transaction data in columns A & B, the Category field (Column E) will automatically populate as well. Otherwise, everything will go into a “Not Categorized” bucket.
Vendor setup on the expense tracking template.
  • Column J (aside from the first row which is reserved for income) is where you will set up your spending categories. The spreadsheet accommodates 10 categories. Any more than that and the charts can become difficult to read.

  • In Cell K1 you can enter an income amount (if applicable). The other values in column K are simply a tally of the spending by category.

Once you have all your transactions entered, you can go to the Summary tab where you’ll see a summary of your spending.

The reports

There are three charts on the Summary tab:

  • A waterfall chart is the main chart that you’ll see on the page. It shows you your spending during the period. This starts with an income amount (this is entered on the Data tab) and every expense is negative afterwards, showing you how much of your income is left over. There are two totals, one before irregular expenses and one that includes everything. The purpose here is to show how your spending would have looked if not for one-time, irregular expenses, and how much of your income was left before and after those expenditures.
Waterfall chart showing income less expenses.
  • There is an Amount vs Vendor chart which shows you spending by Vendor.
Chart showing spending by vendor.
  • % of Expenses shows a breakdown of spending by the different categories. In the middle it shows the total spending during the period.
Pie chart showing spending by category.

There is no defined period for this template

There are no date fields in this template for the purpose of keeping it simple. Whether you want to look at one week, two weeks, a month, or several months, you can plug the data into here to see how much your spending was for that period. However, if you get past row 200, you will want to copy down the checkboxes and the formula in column E.

Download and use the template

The reports in the template don’t require a refresh and so as soon as you enter your data, you can begin viewing the Summary tab.

If you’d like to use the template, you can get a copy of it here.

If you like this Expense Tracking Template, 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.


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.


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:


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:


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:


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.


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))


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.


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.


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, 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 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:

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 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 &#39; with an apostrophe (‘)
  • Replace &nbsp; with a space (‘ ‘)
  • Replace &quot; 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.


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.


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:


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:


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:


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:


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.