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

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:

=MAXIFS(A:A,B:B,”>=”&startprice,B:B,”<=”&endprice)

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:

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.
• 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.
• 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.
• There is an Amount vs Vendor chart which shows you 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.

## 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.

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 for converting percent to decimal:

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

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

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:

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

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:

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.

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

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

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.

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:

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:

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

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

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

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.

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

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:

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

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

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:

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:

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:

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

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:

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:

Now I can click on Close & Load to get the data into 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.

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:

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:

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:

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:

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:

Now a new column has been created:

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:

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.

### 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;

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.