functions Archives - Page 4 of 5

How to Use COUNTIF in Excel

May 4, 2021

by howtoexcel with No Comment beginner count countif functions

Do you want to learn how to quickly count the number of cells that meet certain criteria? How about partial matches using wildcards? Below, I’ll show you how you can do this using the COUNTIF function in Excel along with similar tasks.

How does the COUNTIF function work?

As the name suggests, the COUNTIF function in Excel will count the values in a range if they meet certain criteria. It is not case-sensitive and in most cases, people use it for entire matches. However, you can also use it if you want partial matches.

In the data sample below, I have a list of the largest stocks on the North American markets along with the sectors that they are in:

List of the largest public companies in the world.

In total, my list contains 1,000 companies. To count the number that are in the Computers and Technology sector, I can do the following formula:

=COUNTIF(B:B,”computers and technology”)

Column B is where the sector name is. The above formula returns a value of 170. You’ll notice in the formula I didn’t bother matching the case because it isn’t case-sensitive and doesn’t matter how you enter the criteria in.

A better way to set this up is to reference an actual cell rather than hard-coding the criteria. This can help prevent errors and you don’t have to go into the cell to see what it is searching for. Here’s what the formulas look like:

I also added a SUM function at the bottom to see how many of the sectors are accounted for. With these formulas in place, I can easily copy down these functions to accommodate more sectors if I need more. This is what the COUNTIF function looks like in its simplest form. Next, let’s use wildcards to take it to the next level.

Using wildcards in a COUNTIF formula

There are two sectors in this data set that are similar — consumer discretionary and consumer staples. If I use the approach above, I would need to create COUNTIF formulas for both of them and then total them up:

This isn’t optimal and since the word ‘consumer’ is in both sectors, I can just have the COUNTIF function look for that, rather than creating two separate formulas and then a third to total them. To accomplish this, I’m going to use a wildcard to just look for the word ‘consumer’ :

=COUNTIF(B:B,”consumer*”)

You’ll notice the asterisk at the end of the word ‘consumer’ which will ensure that it will also include any text that comes after it. But how can this work to make the formula dynamic and reference a cell? To do that, I’ll use the & to connect the string to the asterisk:

D2 is where the consumer value is, and by linking that with the asterisk (*) it still allows the cell to be dynamic. In the following example, I put the asterisk at the end of the text but you can also put it at the beginning if you want the value to end with the word:

In my data, there is nothing that starts with trucks, but there are 30 values that end with it. The second formula counts those that end with the value. But what if you don’t care and just want to count every instance, regardless of where it is in the text? In that case, just add the asterisk before and after the criteria:

Using wildcards before and after a string of text in a COUNTIF formula.

Suppose I just wanted to count all the sectors that included the letter ‘s’ :

A total of 709 sectors include the letter ‘s’ in their descriptions.

Using COUNTIF with blanks

You may also want to calculate how many of the cells are blank, nonblank, or don’t contain anything. Let’s cover those sections below:

Counting blanks cells

To count all the blank values you have two options. You can use the COUNTIF along with an empty string (“”) or you can use the COUNTBLANK function if it is available on your version of Excel. Both can generate the same results:

Since I’m looking at the entire column, there are many blank cells in my entire range.

Counting nonblank cells

If you want to count the cells that have values in them, this is what the COUNTA function is used for:

My data set had 1,000 values in it and with the header, and so the formula returns a correct result of 1,001.

Using COUNTIF to count numbers

So far, I’ve covered how you can use the COUNTIF function in Excel with text. But you may also want to count numerical values as well. In this example, I am going to pull in the market capitalization of each of the stocks listed earlier. Here’s what that looks like:

You can use the COUNTIF function like with text but exact matches aren’t as useful when it comes to numbers. Neither are wildcards. Using the greater than (>) or less than (<) operations will be much more helpful in this situation.

Let’s start with a scenario where I want to count all the stocks that are worth more than $1 trillion. To do this, my formula looks as follows:

=COUNTIF(B:B,”>1000000000000″)

Like with the wildcard, the greater than sign goes within the quotes, as does the number. You can also connect this to a cell using the & sign to make it more dynamic:

By referencing a cell and applying a number format, it is also easier to read the value than having to rely on counting the right number of zeroes within the formula. This formula correctly returns the number 5, indicating the number of stocks on the list with valuations of more than $1 trillion. I can copy this formula down and apply it to other valuations as well:

Counting the number of companies based on their valuations.

Each threshold tells me the number of stocks that are worth at least that value. But what if I don’t want to overlap and just want to know the number of companies between $500 million and $1 trillion? To do this, you will want to use the COUNTIFS function, which allows you to enter multiple criteria. It works similar to the COUNTIF function and you just continuing adding a pair of arguments (one for the range, the other for the criteria) until you are done. To count the number of companies that fall within $500 million and $1 trillion, my formula would look as follows:

=COUNTIFS(B:B,”>500000000000″,B:B,”<=1000000000000″)

In this example, I also included the equals ‘=’ operator so that it includes values that are less than or equal to $1 trillion.

This is how it might look in a table where you want the values to update dynamically:

Counting the number of companies that fall within a range of valuations.

In the first row, the COUNTIFS function isn’t needed since that is only looking at one criterion. But for the other calculations, it is pulling in only the values that fall within that range ensuring that they don’t overlap with other categories.

If you liked this post on how to use COUNTIF 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.

Creating a Stock Screener in Excel

April 27, 2021

by howtoexcel with No Comment advanced functions investing stocks template vbafree

A stock screener allows you to filter through stocks that meet your investment criteria. It can help you find undervalued stocks and great dividend investments. But sometimes it can be cumbersome to always go back to a website and re-apply filters, even if you save them. In this post, I’ll go over how you can populate a list of stock data into Excel and then run your own filters on it, and thus, creating a screener you can easily access from within your own spreadsheet.

Step 1: Populating the list

The one thing you’ll want to do before you can create the screener in Excel is to download an array of stock data from a database. Personally, I like using Barchart because it has lots of useful information on there and you can get a wide range of data, and it is easily downloadable into an Excel format. It lets you do five free downloads each day and you can download 1,000 rows at a time. That’s thousands of stocks you can add. Using that in conjunction with the STOCKHISTORY function, and you can create a pretty versatile template. After all, since data like earnings, dividends, and other fields won’t often change, downloading a snapshot from Barchart once a month or even less frequently shouldn’t be a big issue. You can obviously use other databases but I’m going to use a free example for the purpose of this post.

On Barchart, I’ve customized the fields I want to use for my downloads, and this allows me to re-use them again and make subsequent downloads easier. To keep it simple, I am going to download just the top 1,000 North American stocks based on market cap. This is what my download looks like in Excel:

Now that the data is loaded, the next step is to create the layout.

Step 2: Organizing the stock screener and setting up the fields

I find it most convenient to always put any inputs on a spreadsheet on the top of the page, and the results below. This way, you can freeze panes to make it easy to scroll through all the rows while seeing your selections.

To start, I will create a field for each major field I have downloaded. After formatting some of my values, this is how my screener looks thus far:

Off to the right, I’ve added a date field because I am going to utilize Excel’s STOCKHISTORY function to pull in the price. This will allow me to calculate the current price to earnings ratio without having to download it from the screener as that multiple will change every day based on the stock’s price.

When downloading so many stock prices, it may take a while for the formulas to update. But once they are loaded, then I can calculate the P/E ratio by just taking the stock price and dividing it by the earnings per share.

Step 3: Creating the formulas to evaluate the criteria

The part that will take the most time is to now evaluate each of the criteria to determine if a stock meets all of it and whether it should be included in the results. Rather than trying to do this in one large formula, I’m going to break this up into one formula per field. I’m going to name these fields exactly the same so that it is easy to reference them.

For the first criteria, Market Cap, my formula looks as follows:

=IF(E2=””,TRUE,IF(D2=”>”,C9/1000000>E2,C9/1000000<E2))

D2 is where I have the dropdown for the > or < symbol and E2 is the value that I want to filter for market cap. C9 is the first row of data. My goal here is to evaluate to either a TRUE or FALSE value. I also divide the value in C9 by 1,000,000 just to make it easier to filter the market cap by millions.

For the % change calculations, I will do a similar calculation. Except this time I don’t need to divide by 1,000,000 and so it looks a lot simpler:

=IF(E3=””,TRUE,IF(D3=”>”,D9>E3,D9<E3))

D3 is my > or < dropdown while E3 is the percent change I am entering. Since I will enter a percentage here, I don’t need to make any special calculations. This is the same format that I will follow for the other fields.

Once I have set up all my calculations for the various criteria, I’m going to add one column that will check to see if the stock meets all of them. This is a simple formula where I can multiple all the values. A TRUE value will compute as 1 and a FALSE will be 0. And so even if there is one FALSE value, the entire result will return FALSE and not meet the criteria. The formula looks as follows:

=(T9*U9*V9*W9*X9*Y9*Z9*AA9*AB9*AC9*AD9*AE9*AF9*AG9)>0

Step 4: Converting it into a table

The final step is a simple one but it’s also important to make this sheet work smoothly. Select anywhere on the data set and on the Insert tab, click on Table. Hit OK and now you should see Excel’s default table applied to your data.

The reason for converting this into a table is that now we can apply slicers to it. And really, only one is needed here. If you go to the Table Design tab, there is a button to Insert Slicer. Click on it and select the one for the field that checks all the other criteria. In my example, it is called Criteria Met.

After hiding all the criteria fields, changing some of the formatting and adding the slicer, this is now how my screener looks like:

Stock screener after applying a table slicer to it.

The beauty of this stock screener is that by clicking on the TRUE button in the slicer, you are automatically refreshing the data in Excel and updating your filters based on the selections. All this is done without macros and it makes the screener easy to change with the press of a button.

You can download my completed template here. Please note that if you do not have STOCKHISTORY available on your version of Excel, some of the values will not populate.

If you liked this post on creating a stock screener 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.

Create an Excel Drop Down List Without Blanks

April 21, 2021

by howtoexcel with No Comment dropdown forms functions intermediate names offset ranges tips & tricks

In a previous post, I covered how to create a form in Excel. Although I didn’t go over drop down lists specifically, they are one element you could incorporate into them. The problem is that your list can change over time, getting bigger or smaller. And that can make it difficult to maintain if your list isn’t dynamic as it will involve you always having to manually change the range of your drop down list. Otherwise, it could be incomplete or contain blanks. Below, I’ll show you how you can manually change your drop down list in excel and create it without blanks while also making it dynamic so that you don’t need to worry about whether it changes over time.

Setting up the drop down list

First, let’s start with the basics — creating the list. To create a drop down list in Excel, you just need a series of options to choose from. My list is going to be made up of the top 30 places to visit. I’m just going to put those names in a list.

After entering in the list of places into Excel, the next thing I will do is select all the values, and create a named range. This is as simple as just entering a value next to the formula bar, where you see the cell location. I will call this range VacationSpots:

Named range in Excel containing popular vacation spots.

There is no need to add headers or anything else. Just select the values, enter in a name for the list, and hit enter. A longer approach would be to go to the Formula tab and select Name Manager:

Clicking this will show you all of the named ranges in the workbook:

Name manager showing a list of all the named ranges in an Excel workbook.

It shows you the named range I created. However, I could also create one from this screen and also Edit my existing range. This is where you would go to make the change manually. Clicking on the Edit button would give you this screen:

As you can, here I can manually change the address as needed in case the list changes. However, this is obviously not optimal as it can be a tedious process if your list changes frequently.

Creating the drop down

Now that my list has been created, I can set up the actual drop down. To do this, I’m going to select a cell and under the Data tab, click on Data Validation. Here, there is a place to enter your list of values:

Under the Allow section, I choose List. And for the Source, I enter the ‘=’ sign followed by my named range, VacationSpots. Now, when I click OK and go to the cell that contains the data validation, this is what I see when I select it:

Clicking on the drop down arrow will show me my list of options, in the same order that they appear in my list:

Selecting from a list of drop down options in Excel.

I can select any of the values and my cell will update accordingly. This is great, but what if I decided to add more items to my list, perhaps 10 or 20 more locations I want to visit? Next, I’ll go the different ways you can create drop down lists in Excel without blanks.

Option 1: Create extra spaces in your drop down list at the very end

Technically this step involves blank spaces, which is not what this post is supposed to be about. However, I just wanted to show you how this could work. If your list has dozens of items, then having extra blanks may not be that big of a deal. For example, say I edit my named range so that it goes to 50 rows. If you do that and include empty cells, this is the biggest problem you’ll face:

My list no longer starts from the top, it goes to the first blank cell. This can be an annoying problem because now it looks like all of my options aren’t there (they are, but I have to scroll up every time). This is probably the main reason people want to avoid having blank values in their lists. If the blank values simply came after all of your selections, that might be more tolerable. But because they impact where your drop down list begins from, it can be a nuisance.

The good news is there is a simple way to get around this. For all your empty cells, enter just a single empty character. Select a cell, hit the space bar, get out of the cell, and copy that value down. Now, your empty cells technically aren’t empty because they contain a space. And by doing so, the drop down list now starts from the top again. You will still have blank values, but this time they will show at the bottom of your drop down list:

Drop down list with blanks at the bottom.

If this is acceptable then you can stop here. If you are still intent on getting rid of any possible blank value whatsoever, then head over to the next option.

Option 2: Creating a table to create a nonblank list

This option is the easiest method for getting rid of blank values. What you need to do here is convert your list into a table. Select a cell on your list, click on the Insert tab and then click Table:

Leave the option for headers unchecked and then click OK. You should see something like this:

By default, Excel will apply its formatting and design but you can change the look of it to make it blend in more with your spreadsheet. You can also re-name the header from Column1. Either way, you can now create a new drop down list from this table. Since the values are in range A2:A31 in my spreadsheet, that is what I will enter for my new Data Validation list:

You can either select the range, or enter it in yourself. But if you enter it, you need to include the $ signs otherwise it will not auto-update properly. Now, I’ll go to my list add ‘New City’ to the bottom of the table. When I do that, the table automatically expands which you can notice because I haven’t changed the design and so the colors change:

Table in Excel after adding a new value.

And if I go back to the Data Validation settings, my source has automatically been updated:

This is a really easy way to make your drop-down list automatically update without the need for any formulas.

If the table you are referencing isn’t on the same sheet as your drop-down list, then you will need to use the INDIRECT function reference it. For instance, if you have created a table called Table1 (which should contain just one column for your list) on a different sheet, you can reference it the following way:

This will allow you to reference the list even if it is on a different sheet.

Option 3: Using a formula to remove the blanks in your drop down list

If for whatever reason creating a table isn’t an option for you, you can still create a dynamic list using a formula. Here, I’ll go back to creating a named range. Except rather than selecting a fixed set of cells, I will rely on a single formula. First, I’ll go back to the Name Manger. I’ll create a new named range. The formula for this can look a bit complex so I will break it down into parts.

First, I’m going to use the OFFSET function. This is because it can allow me to specify a height and width, which is crucial to making this work. My data starts in cell A1, and that’s where my formula will begin:

=OFFSET(A1,0,0

A1 is my starting point and that is the first argument. The next two arguments are whether I want to offset and move to any adjacent rows or columns. Since I don’t, I leave those values as zeros. It is the next argument that is critical, as it relates to the height. Here is where I will use a COUNTA function. I want to count the number of nonblank values in my list. My formula looks as follows:

=COUNTA(A:A)

I will embed this within my previous formula:

=OFFSET(A1,0,0,COUNTA(A:A)

For the width, I will set the last argument to 1, since I don’t want to include any extra columns. Here is my completed formula:

Named range in Excel using the Offset function.

You always want to used $ signs in named ranges so that they don’t move on you. Now that this is set up, I can use this NewRange as my Data Validation source. And just like with a table, whether the list gets bigger or smaller, my named range and the drop down list will automatically update.

However, what if your list contains some formulas that look blank but really aren’t? Formulas are a great example of cells that can look empty even if they aren’t. The COUNTA function will count these values and you could again be back to square one with additional blank values. One way you can get around this is by counting the cells that are blanks, and subtracting that from the total rows. The formula would look as follows:

=ROWS(A:A)-COUNTIF(A:A,””)

Using this, you should correctly arrive at the number of cells that contain text and that aren’t blank as a result of a formula You can then insert that formula in your named range, in place of the COUNTA formula:

Named range in Excel using the countif and offset formula.

As you can see, this method isn’t the easiest and that is why I left it for the end. However, there are multiple different ways you can create a drop down list in excel without blanks. But it’s important because by removing blanks, it will make your form or spreadsheet look more polished by not having blank values in them.

If you liked this post on how to create a drop down list in Excel without blanks, 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 Numbers From Billions to Millions to Thousands in Excel

March 11, 2021

by howtoexcel with No Comment beginner formatting functions substitute tips & tricks

Depending on what kind of data you are working with and how you need to present it, you may need to show numbers in thousands, millions, or billions. Below, I’ll show you how you can quickly and easily make those conversions. You’ll also learn how to add a letter behind each number to indicate either B for billions, M for millions, or k for thousands.

Converting between billions, millions, and thousands

If you are dealing with raw numbers, to convert millions into billions you only need to multiply them by 1,000. And you don’t need to convert one value at a time. To multiply an entire range, copy a cell that contains the number 1,000 (or whatever factor you want), select the range you want to multiply, right-click paste special and you will see the following options:

Selecting the Multiply option will multiply the cell against each one of the values in the range. If you wanted to reverse the calculation and convert billions into millions, then you would follow the same steps except instead of selecting Multiply, you would choose to Divide. You can use this for other operations as well, including addition and subtraction.

Another potential use you may have for this is if you have numbers that Excel is recognizing as text. Multiplying all of them by a factor of 1 could fix that. And multiplying by -1 would flip their signs if you needed to switch them from positive to negative, or vice versa.

However, in some cases, things can be a little more complicated and you need to do more than just multiplication. When you are looking at stocks and trading volumes, for example, you may see abbreviations such as B or M. Here’s a look at some of the best-performing stocks from March 10 and their trading volumes, as per Yahoo! Finance:

Top-performing stocks for March 10, 2021, and their volumes.

While most of them contain M for million, some of the numbers are in thousands. Simply getting rid of the M wouldn’t fix this problem as then the numbers in millions would appear smaller than those that are in thousands. To fix these values, we’ll need to do two things:

Get rid of any letters.
Scale the numbers consistently.

To avoid the numbers getting too long, I’ll convert these numbers all into millions. That means for numbers that have an M, I only need to get rid of the letter. And for thousands, I need to convert those numbers into a fraction of 1 million.

This is going to require an IF statement to correctly convert all of the values. The first thing that needs to happen is to determine if the number is in thousands or millions. This just requires using the RIGHT function, which will tell us the last letter or number in a cell:

=IF(RIGHT(A1)=”M”

Where A1 is the cell that contains the value. If this test evaluates to true, then the next step will be to get rid of the letter using the SUBSTITUTE function. Since I’m leaving the values in millions, I won’t need to multiply or divide the value by anything besides 1. The formula will now look as follows:

=IF(RIGHT(A1)=”M”,SUBSTITUTE(A1,”M”,””)*1

I replaced the “M” with a blank value. I also need to multiply everything by a factor of 1 to make sure it reads as a number. Otherwise, it would simply be text.

If I also had billions in my data set, I might use another IF statement here and do the same thing, only instead of multiplying by 1, I would multiply by 1,000 to arrive at millions. For example, $1B would become $1,000.

However, the data set doesn’t include billions and so I only need to account for thousands. The remaining values that aren’t millions I can just divide by 1,000,000 to determine what fraction of 1 million they are. The factor has to be this large because the numbers are raw and aren’t in thousands.

Here is why my complete formula looks like

=IF(RIGHT(A1)=”M”,SUBSTITUTE(A1,”M”,””)*1,A1/1000000)

Now I can copy this formula down across my data set, and this is how it looks:

Volumes after converting them into millions.

The numbers that were in millions simply lost the ‘M’ at the end of their values. And those that were in thousands now are in decimals, indicating how much of 1 million they are. For 342,271, it now shows 0.342271.

This is a complex example where you are dealing with text and the important thing to remember is that once there are letters involved in a number, the value automatically becomes text. If you want to apply some sort of calculation, it is going to be necessary to convert it back to a number — after you have gotten rid of any letters.

How to show numbers with B, M, or k

Next up, let’s take a look at how you can add letters to an existing number. Essentially, I am going to undo what I did above. Let’s start with turning our decimals into thousands. To do this, I can look for if a value if less than 1. If it is, then I will multiply it by 1,000 and add the letter ‘k’ to the end of it. Here’s how that formula will look:

=IF(A1<1,A1*1000&”k”

My value of 0.342271 becomes 342.271k. However, if I don’t want the decimal places and I want to round, I can adjust my formula accordingly:

=IF(A1<1,ROUND(A1*1000,0)&”k”

Using the ROUND function and setting it to 0 decimal places, I round up and now my value shows as 342k.

Next, I’ll need to add an “M” if the number is in millions. If any of the numbers were in billions, what I could do is check if a number is 1,000 or greater (e.g. 1,000 million). But since I don’t have billions in this data set, I can just simply add an “M” on to everything that is not in the thousands:

=IF(A1<1,ROUND(A1*1000,0)&”k”,A1&”M”)

This is what my values look like after this latest conversion:

Volumes after converted with M for millions and k for thousands.

For argument’s sake, I’ll change the first value so that it is 1,536 and show you how I would adjust for this calculation if that were $1 billion. As mentioned above, I would check if the value was more than 1,000. And if it is, I will divide it by 1,000 and add a “B” to the end of it. My formula, accounting for millions, billions, and thousands, will look like this:

=IF(A1<1,ROUND(A1*1000,0)&”k”,IF(A1>1000,(A1/1000)&”B”,A1&”M”))

And this is how my calculations look:

Converted values showing B for billions, M for millions, and k for thousands.

The reason I leave the millions calculation last in that formula is that I know if it isn’t less than 1 (thousands) and if it isn’t more than 1,000 (billions), then it has to be millions.

Remember: by adding letters to these numbers, they can’t be used in any sort of calculations. And so before you decide to go that route, it’s important to consider those limitations.

If you liked this post on how to convert numbers from billions to millions to thousands 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 Extend and Increment Formulas

January 31, 2021

by howtoexcel with No Comment functions intermediate tips & tricks

When you have created formulas in Excel, you may want to extend and increment them down to other cells. In many cases, you can just drag down a cell that contains the formula and pull it down so that it automatically gets copied. However, there’s a problem if you need to increment a formula or number in an argument. Below, I’ll show you an example of this and how you can address it in multiple scenarios.

Let’s start with just pulling the largest value in a data set. Using the LARGE function, you can grab the largest value with a formula of =LARGE(A:A,1) — that will give you the largest value in column A. However, you’ll notice quickly that just copying this formula down won’t work as expected, it’ll keep copying the 1 in the second argument down; you will continue extracting the largest value. Sometimes Excel just isn’t good at detecting patterns. But there are ways you can adjust the formula to ensure your formula is incrementing correctly.

Option 1: Using an Additional Column

In a separate column, you could list the numbers 1 until 100 or whatever range you needed to and reference those. In the example below, I can pull the largest 10 items using the LARGE function and a referencing the cells to the left:

Using the LARGE function to pull the top 10 amounts in a data set.

The problem with this approach is that it isn’t always practical within a spreadsheet, especially if you don’t have room to spare and don’t want to be creating extra columns for the sake of what should be a simple calculation. It may also be difficult to determine what the upper limit should be. That’s where the second option is going to be a better one.

Option 2: Using the ROW Function

There’s an easy way to adjust the above calculation so that you don’t need to reference the actual number, nor do you need to manually enter it. The trick is to use the ROW function. It returns the row value of a specified cell. If I entered =ROW(A20), it would return a value of 20 for the row value. You don’t need to enter an argument and using =ROW() will just return the row of the cell that contains the formula. The function is particularly useful when you want to increment values because as you drag it down, the results will increase by one each time.

In calculating the largest values, we’ll use the ROW function as the argument to determine which value we want, whether it’s the fifth largest or 20th largest number. Here’s how the initial formula would look like, assuming we wanted to start with the largest value:

=LARGE(D:D,ROW(A1))

In the above formula, the calculation will return the largest value since ROW(A1) will return a value of 1. Now, if the formula is copied down, here’s what it will look like:

Using the LARGE and ROW functions to pull the largest values in a data set.

By copying down the formula, ROW(A1) changed to reflect the next row. Excel does a better job of detecting patterns when they’re part of a function as opposed to just standalone numbers. Using the ROW function eliminates the need for the extra column. And as you’ll notice, the results are the same as in the earlier example.

This is just one example and there are many others where you can use the ROW() function to your advantage and increment values down. If you need to increment as you’re going to the right instead of down, then you’ll want to use the COLUMN function, which effectively does the same as the ROW function, only it’ll pull the column number that a cell is in.

If you liked this post on how to extend and increment formulas 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.

Excel Automation: 10 Tasks You Can Automate Today!

November 29, 2020

by howtoexcel with No Comment functions intermediate macros tips & tricks vba

Spreadsheets can allow you to analyze data and create reports efficiently. But sometimes the tasks that are involved can be difficult or appear to be time-consuming. The good news is that there’s a lot of automation you can achieve in Excel, and it isn’t always necessary to know how to code in order to do so. Below, I’ll show you 10 types of tasks that you can automate in Excel either on your own or with our help.

1. Cleaning and parsing data

One of the more challenging things in Excel is when you’re dealing with a dataset that may not be easy to manipulate. For instance, if you’ve got text mixed in with numbers or dates that aren’t in the right format, Excel may not interpret or recognize the data properly. But there are many formulas that can help you with that. Rather than manually fixing the data, you can use functions like TRIM, CLEAN, LEFT, MID, and RIGHT to extract what you need while also getting rid of extra, unnecessary spaces and other characters.

If you’re looking for more of a walkthrough of the process, there’s a detailed explanation in this post of how to parse data.

Through the use of formulas, you can save hours that you might otherwise spend trying to clean up your spreadsheet. And the best part is that once you’ve set it up, you can re-use the formulas as you add more data. You don’t need to use macros or complicated coding to clean up; a well-structured template can be enough to do the job for you.

2. Creating simple reports

One of the best features of using Excel is that once you’ve entered data into a spreadsheet, it’s even easier to create a report from it. One example is through the use of a pivot table, where through just a few clicks you can easily summarize your data and split it along different categories. Slicers can make filtering and summarizing data even easier in pivot tables, especially for users who aren’t very familiar with Excel. Forget any manual work here; just a few clicks and you’ve got a report that can quickly summarize information in a table for you!

Alternatively, you can also insert charts easily and Excel will try and select the best one based on your data set. There’s also lots of formatting you can apply to charts so that they have the look and feel that you’re after. And once you’ve got a look that works, you can re-use it over and over again.

3. Creating dynamic dashboards

Dashboards are incredibly popular but they can be complex to set up. Then there’s also the challenge of updating it and making sure the data is up-to-date. It can easily take you hours every time to make sure the information is accurate.

However, in this post, I show you how to create a dynamic dashboard that not only won’t take you hours but that will automatically update as you add data to it. And then, you end up with a report that looks great to send to management to easily review and update.

4. Routine data entry

One of the biggest headaches people can face when using spreadsheets is when they hard-code calculations. A hard-coded calculation is where you don’t reference any cells and just put the result in the cell; it can make it nearly impossible to decipher how that number was calculated (especially if you’re not the person who entered the value). If you go to re-calculate it or update it, you could spend a lot of time just trying to figure out the calculation.

However, by using a formula, there’s no ambiguity as to how a value was calculated. Not only does that save you the time of entering in data but it also makes it easy to correct and update the figure. Ideally, you should minimize the number of places you’re manually entering data into. By doing that, you’ll have a much more robust template where your inputs are kept to a minimum which will eliminate the need for a lot of data entry and your other calculated fields will update automatically. This type of automation doesn’t require complex coding and just needs an Excel spreadsheet to be carefully constructed so that it is efficient and makes the most of formulas.

5. Conditional formatting

Oftentimes you’ll want to color-code your data to highlight things you should be paying attention to. If you’ve got an aged accounts receivable schedule, it is useful to highlight which accounts are more than 90 days overdue. You could manually filter the data and highlight all the cells or rows in red that are overdue, but you can just use conditional formatting to do that for you.

Through conditional formatting, you can create rules to determine when a cell or row should be highlighted in red, when you may want it to be in yellow, or when you may just want to hide the text so that you can easily skip over it. For example, hiding zero values can make it easy to focus on more important numbers.

You can apply many different formatting rules and can even put in a hierarchy to determine if you want to keep applying formatting rules or whether you want to stop if a specific criteria is met. Conditional formatting can be complex but it can be a huge time-saver by allowing you to focus on just the items that are important to you. And once you’ve set up the rules, you don’t need to worry about making changes every time you add new data.

Check out this post to learn about conditional formatting and how you can apply it to your spreadsheet.

6. Updating other workbooks and sheets

If you use multiple workbooks, then another area where you can avoid re-entering data is by linking both workbooks. There are numerous ways that you can do this. One approach involves just linking directly to another worksheet where data will automatically pull from another table.

You can also use the INDIRECT function to reference another worksheet or workbook. Just like with a template, once you set up these formulas and connections, they are there to stay and you can avoid having to manually make changes by yourself.

7. Audit tracking by logging changes

One of the neat features of many Office products is they allow you to track changes that are made. This is normally when you share a workbook with other users. However, through the use of macros, you can have a separate sheet that can tell you which values were changed, when, and by who.

Rather than manually noting these changes or relying on people to make the updates themselves, it doesn’t take much effort through a macro to create a log of what’s been changed.

8. Generating PDFs

One thing many advanced Excel users like to do is to use automation to export reports into PDFs. While there is a way to print to PDF, and it’s particularly easy on the newer versions of Excel, it can be a time-consuming process especially when you need to print out multiple sheets. Here again, with a simple macro, you can auto-generate PDFs and save them in a predefined folder all with the click of a button.

9. Sending emails

Another feature many users like is the ability to use automation to send out emails right from Excel. Through the use of macros, this is also possible. You can create a macro that will enter in the email of the recipient, attach a file, enter the body of the message, and even send the email itself. This can be even set up on a large scale, such as sending out invoices to dozens or hundreds of customers, a process that could easily save you hours worth of work.

10. Just about anything else with VBA

The power of programming in Excel can unlock many different possibilities with what you can automate. Whether it’s using automation to help import data and then manipulating it, creating custom reports, or following a series of complicated steps, there are many tasks in Excel that can be expedited with a few clicks of a button. As long as there’s some logic to the process that you can break into steps, then you can also build that into the code and automate it.

Don’t know where to start? Contact us!

There is significant potential in Excel but not everyone knows how to use automation to make the most of it and to make a spreadsheet as efficient as it can be. You can contact us if you have a certain Excel issue that you need help with or if one of the tasks above has perked your interest and you’d like to learn more. We can help create solutions for you that work efficiently and that can save you many hours, perhaps even days every month.

If you liked this post on 10 Tasks You Can Automate Today, 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.

2 Excel Functions That Can Eliminate the Need for an Amortization Table

November 17, 2020

by howtoexcel with No Comment accounting functions intermediate tips & tricks

An amortization table is a useful tool when you need to calculate interest payments, principal payments, and to track the balance that’s owed on a loan. However, you don’t have to create a full schedule to get these values and below I’ll show you two functions that can get you that information quickly and easily. First, let’s start with what a typical amortization schedule looks like.

Creating the amortization schedule

When you set up an amortization schedule, you’ll track the balances, interest, and principal payments. It often looks something like this:

You could use the table to determine what the balance is at the end of period 10 or to add up all the interest payments up until that point. However, there’s another way to arrive at those totals, and that’s using two functions that are available in the newest version of Excel: CUMIPMT and CUMPRINC.

Using the functions

In the amortization schedule, we can see that the ending balance of the $100,000 loan by the end of period 10 is $85,016.67. We can use the CUMPRINC function get to that total as well. The function takes on the following arguments:

To calculate the cumulative principal payments, I’ll enter the formula with the following arguments:

=CUMPRINC(0.05/12,60,100000,1,10,0)

This gives me a total of -$14,983.36. When added to $100,000, it nets out to a balance of $85,016.64 — within just a few cents of the amount on the amortization table. The function gives you the flexibility to specify which periods you want to extract and so you aren’t limited in just tabulating the totals for the first 10 periods or starting from the beginning. You can start from period 13, or the second year, and so on.

If you want to calculate the total interest payments, then that’s where you can use the CUMIPMT function. It has the same arguments as the CUMPRINC calculation, so the formula will look very similar to what’s above:

=CUMIPMT(0.05/12,60,100000,1,10,0)

This tells me that the cumulative interest payments during the first 10 payment periods is $3,887.87. This matches what I would get by adding the interest payments in my amortization table over the same period, this time to the penny.

Should you use these functions instead of an amortization table?

On older versions of Excel, you won’t have access to these functions but if you’re using Microsoft 365 or Excel 2019, then these functions are available and can potentially serve as replacements for an amortization table. Now, if you need the table for audit purposes it may not be possible for you to do without an amortization table completely. But if you’re only generating the table just to determine how much you’ve spent on interest or what your balance will be at some point in the future, then these functions can certainly replace doing a full-blown amortization table.

If you liked this post on 2 Excel functions that can eliminate the need for an amortization table, 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 the New Stock History Function in Excel

August 22, 2020

by howtoexcel with No Comment functions investing stocks

For a while, one of the big advantages Google Sheets had over Excel was the ability to pull stock quotes easily. But that’s no longer the case as there is a new function in Excel that allows you to pull in stock price history. Below, I’ll cover how to use the StockHistory function.

How the function works

The function itself is fairly simple and requires just two arguments at a minimum, and that’s the stock ticker and the start date. By default, the function will return the closing prices from the start date until today. For instance, if I want to pull Tesla’s share price since the start of the year, this is what my formula will look like:

=STOCKHISTORY(“TSLA”,”2020-01-01″)

The formula will then generate an array. Here’s a portion of what it looks like:

Stockhistory function returning Tesla's share price since the start of the year.

If you want to pull just the most recent share price, here’s what you can do:

=STOCKHISTORY(“TSLA”,WORKDAY(TODAY(),-1))

Using the WORKDAY formula you can ensure that you’re going back one business day. You may need to adjust this if you’re on a weekend but basically you just need to manipulate the date to make this work. Note that this doesn’t appear to give you the current day’s close. When I ran this on a Friday, the most recent closing price it returned was from Thursday’s close. It’s clear this function’s intended for historical data rather than live or even delayed stock prices.

If you want to specify an end date for your data, you can enter a date in the third argument, right after the start date.

The function gives you many options, including which data points you want to pull in and what intervals you want. You can pull prices on a monthly or weekly basis by selecting either a 0 (daily), 1 (weekly), or 2 (monthly) for the interval argument. Here’s how I’d pull monthly prices for Tesla:

=STOCKHISTORY(“TSLA”,”2020-01-01″,,2)

Tesla's monthly share price since the start of the year.

It’s important to note that these aren’t monthly averages, they’re just the stock prices as of the end of the specified month. Although the date for the first entry suggests January 1 (the markets weren’t open that day), that’s actually the January 31 closing price.

You can choose whether you want to see the headers and you can also add more fields, including the opening price, the high, the low, and the volume. You can even determine if you want to even see the date (although that’s probably not a good idea when you’re looking at historical data).

It’s easy to make a template with this function since it populates the data for you. Using variables for the ticker, the start date, and the end date, I can quickly set up a sheet that’s easily updatable:

Excel template using the stockhistory function.

The only formula that I enter is the one cell for the STOCKHISTORY function:

=STOCKHISTORY(C2,C3,C4,0,1,0,1,2,3,4,5)

Where C2, C3, and C4 refer to the stock, start, and end dates. The numbers 1 through 5 are needed to ensure that all the fields are extracted.

If you want more details about this function including the different arguments, you can check out Microsoft’s official page for this function.

How can I get other (non-US) tickers?

One of the things you’ll notice from the above examples is that I didn’t enter any prefix for the stock ticker. The StockHistory function knew I was looking for Tesla’s stock price. However, if you want to pull data from other exchanges, including those outside the U.S. markets, you’ll need to add a prefix to make sure that you’re getting the right quote. And since the function won’t actually return the company name, you need to make sure you’re entering the ticker correctly into the function.

Refer to this link for all the different market identifiers. For instance, if I wanted to pull the share price of Air Canada, which trades on the Toronto Stock Exchange, I’d need to enter the ticker as follows:

XTSE:AC

In most cases, it looks as though it’s just an X before the exchange’s usual prefix but you’ll want to double check to make sure.

Why you may not find the StockHistory function on your version of Excel

Since the function’s in beta, StockHistory is not available for most users. You can, however, sign up for Microsoft’s Office Insider program which will give you access to functions while they’re in beta. To join the program, follow the steps outlined here.

If you liked this post on How to Use the New Stock History Function 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.

3 Reasons You Should Still Use VLOOKUP

August 15, 2020

by howtoexcel with No Comment beginner functions index lookups match vlookup

Many people will tell you that you should use INDEX/MATCH instead of VLOOKUP or that you should use a new function like XLOOKUP instead. But you shouldn’t be so quick to ditch arguably the most popular function on Excel as it’s still very useful. Below are just three reasons why VLOOKUP is still incredibly valuable:

1. It’s really quick to set up

If you’re using a combination of INDEX/MATCH, you’re going to have to use two functions, correctly set them up and nest one inside the other. Especially if you’re not used to it, it can take some time to set it up. Sure, it’s not like it’s going to take hours or even minutes to do, but if you need a quick lookup and VLOOKUP can do the job, why not just use it? Here’s how quickly it takes to set it up:

In the above example, I do a VLOOKUP in about five seconds. If you’re setting up INDEX/MATCH, you might still be trying to figure out which column to use for your MATCH argument. Being able to do VLOOKUP without almost thinking is what makes it such a great function, its speed is through the roof. Since you know the first column of your range is where you’re looking up values, it simplifies the process of selecting the columns and then you’re just counting how many columns over you’re extracting data from.

A couple of ways I expedited the formula above is by not typing out the entire function name (just entering VL and then tab to autocomplete the name), using 0/1 instead of typing out True/False and by not closing the last “)” as Excel will automatically do this for you.

Sure, it won’t work in all scenarios such as if you need to go left, that’s a well-known limitation of VLOOKUP. But as long as that’s not the case, there’s really no reason you need to bother with INDEX/MATCH when VLOOKUP will do the job. I’ve been using Excel for decades and I still love to use it when I can because it’s so easy to set up.

2. VLOOKUP is very versatile and will work on old versions of Excel

VLOOKUP may not be able to go left, but it can do wildcard searches and it can work if you need to pull the closest value — this is really useful if you’re dealing with tax brackets or anywhere that you’re looking for the closest value without going over (e.g. where you set the last argument to TRUE to look for approximate matches). While many people may use it strictly for exact matches, VLOOKUP is much more powerful.

And here again, using VLOOKUP in these situations is likely going to be no more difficult than the alternatives. While the temptation may be to use an exciting new function like XLOOKUP, the one big disadvantage is that it’s not available on older versions of Excel. With VLOOKUP, even if you’re working on a version that’s 20 years old you won’t have to worry about whether the formula will work.

3. Ease of use makes it ideal for training novice users and making templates with

Not only is VLOOKUP easy to set up, but it’s easy to understand compared to other, more complicated functions. If you’re making a template or need to train users, you don’t want to worry about them knowing complex formulas, especially when it involves nesting functions. Or telling them about a formula that may not work on their version of Excel. VLOOKUP’s also a good stepping stone for beginners to get them accustomed to how Excel formulas work.

Complex formulas are easy to break and harder for inexperienced users to fix. That’s why VLOOKUP’s ease of use is a key reason it’s worth using. If you’ve ever had to fix someone else’s formulas, you can definitely appreciate that keeping formulas as simple as they need to be can go a long way in making it easy to maintain and fix a spreadsheet.

If you liked this post on why you should still use VLOOKUP, 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.

Why You Shouldn’t Bother With Excel’s New XMATCH Function

February 8, 2020

by howtoexcel with No Comment functions index intermediate match

Last year, Excel released some updates including the unveiling of XLOOKUP as well as XMATCH. In this post, I’ll show you how to use the XMATCH function and also why you may not have a need for it.

For this example, I’m going to use a list of the stocks with the largest market caps on the U.S. exchanges as of Feb.7, 2020. Here’s what my data looks like:

Top stocks listed on the NYSE and NASDAQ as sorted by market cap.

XMATCH can achieve the same results as MATCH does when looking for data, but if you wanted the same functionality you could just use MATCH. Instead, let’s start by looking at some of the other things that Microsoft claims XMATCH can do.

XMATCH is not a suitable COUNTIF replacement

One of the things that XMATCH can supposedly do is when you’re looking up numbers, it will count the number of times that values fall above or below a threshold. For this example, we will look at the number of stocks on this list with market caps of more than $1 trillion.

To do this, you would use $1,000,000,000,000 as your lookup value and set the third argument of the function, called match mode, to 1, which looks at an exact match or the next larger item. Here’s how the formula looks like:

Where L6 is the cell that has the number that XMATCH will search for (1,000,000). This formula correctly gives me five matches that are more than $1 trillion that appear on the list. However, if I include the header, the results change:

This leads me to believe that it’s still looking for the closest match and not really counting the number of values that meet the criteria. And indeed, when I changed some of the market cap numbers so that they were more than $1 trillion, XMATCH didn’t compute them correctly since they weren’t in descending order. I’m assuming what Microsoft is implying with XMATCH is that if your data is sorted in ascending order, it would be able to tell you where the smallest value is that meets your criteria. For example, The sixth row in the data set was $1.02 trillion and that was the lowest entry that was more than $1 trillion. Technically, if the data was in descending order then everything above that will be more than $1 trillion.

However, that’s very different from actually counting the numbers over that threshold. And that’s why COUNTIF is still vastly superior to XMATCH. Here’s how the two functions worked when I added four additional entries (not in order) of more than $1 trillion, bringing my tally to nine:

In the COUNTIF function, it still correctly counted nine instances where there was more than $1 trillion on the list. XMATCH, meanwhile, continued to point to the sixth row.

These issues are confirmed when we look at the number of values below $1 trillion:

The -1 argument in match mode is the opposite of 1, and it looks at the exact match or next smaller item. However, the results, as you can see, were very different and not what I would have expected. It appeared to point me to the closest number to $1 trillion without going over. COUNTIF, meanwhile, continued to correctly count the number of items that were below $1 trillion. And with 1,000 items in my data set, it makes sense that 991 were below if nine were above the threshold. Unfortunately, that same logic doesn’t work with XMATCH.

As a replacement for COUNTIF, XMATCH gets a fail as it’s clear that it’s not really counting the number of instances. Only under very specific circumstances would the function do that, such as if the data was in descending order. And even then, you’d still need to do a calculation for the header or if you’re looking at the number of items below a threshold. It’s more trouble than it’s worth and COUNTIF has the benefit of also being available in older versions of Excel, even going back to Excel 2000. That’s important if you’ll ever need to work on an older version of Excel.

Using XMATCH to search for text is not any better than using MATCH

If you’re using XMATCH for matching text, it won’t be able to count the instances but you can use it to find the first instance of it. Some companies trade under multiple tickers and you’ll notice Google’s parent company Alphabet shows up twice in this list. Here’s what happens when I try to use the XMATCH function to find the first instance:

I’m using a question mark after the text as that’s what Microsoft instructs users to do when looking for partial matches. However, if I ignore that advice and use an asterisk and specify I’m using a wildcard match, then it appears to fix the issue:

You may be wondering how the regular MATCH function did:

Besides changing the last argument, the functions are nearly identical in how they’re used to find partial matches.

Let’s compare how the functions work when we’re looking at exact matches. For this example, I renamed the multiple Alphabet names so that they only spell out Alphabet with no mention of share classes, e.g. so they’re exactly the same. Here’s how XMATCH does on a simple match calculation:

Comparing MATCH to XMATCH on exact matches.

Here again, there’s little distinction between the two functions.

Microsoft also advertises that XMATCH can be used in an INDEX/MATCH combination, but even that seems kind of pointless.

Using XMATCH with INDEX makes little sense

Let’s use these functions to grab the intersect between the company name and its dividend yield. The name is in column B while the dividend yield is in column G. All the headers are in the first row. Here’s how the formula looks like with the use of XMATCH:

In this example, XMATCH correctly pulled the right percentage for Visa’s dividend yield of 0.59%.

That would be really, really cool if the MATCH function didn’t already do the exact same thing. By getting rid of the X in the XMATCH function, thus making it just a MATCH function, and adding a 0 for the third argument, I get the same exact result:

XMATCH doesn’t improve upon anything when it involves the INDEX and MATCH combination. We’re talking a slight change to the syntax, that’s about it. And again, from a functionality point of view, there’s just no reason to swap a new function in when the existing one works just as well, especially since there’s no backwards compatibility on older versions of Excel for XMATCH.

What XMATCH can do well

Everything that the MATCH function can do, XMATCH can do as well. That’s the good news. There is, however, one thing that XMATCH can do better, and that’s look for data in the reverse order. Here’s a simple example of how both functions work when we’re looking for the first value that contains the word Alphabet:

Both functions correctly yield the same results. Again, the change here is mainly to do with syntax. Under the new XMATCH, if I set the third argument (match mode) to 0 and look for an exact match, I’ll get an error. But if I set it to 2, which is wildcard character match, it will produce the correct result: Alphabet, which first shows up on the fifth row. However, it’s easy to see how this will confuse users who are familiar with MATCH and just use 0 for the third argument, which will also produce the correct result in this case. This is another example of where the syntax has gotten more complicated and not given the user any additional advantage.

The one exception to that, however, is if you want to do a search in the reverse order. MATCH currently will go from the first row and work its way down. Once there’s a match, it will stop there. Here’s how the XMATCH function performs when we’re doing a last-to-first search, as indicated in the fourth argument where the value is -1:

This time XMATCH does correctly pull the sixth row, which is where Alphabet would first show up if we were looking from the bottom and moving up. MATCH, unfortunately, doesn’t have the option to do that and a user would have to rearrange their data to get the same result.

The reverse-order search is the only advantage I can see from testing out XMATCH. Unfortunately, the new function doesn’t add anything significantly new and at worse, it can lead to incorrect results, especially if you’re planning to use it to replace COUNTIF.

Why learning new functions may not be worthwhile, at least not initially

It’s possible that in future updates the XMATCH function will work better but for now, there’s not a whole lot of reason to use it. One of the biggest disadvantages of new functions is that they won’t be helpful to you if you’re working on an older file. It’s not uncommon for people to be working on Excel versions that are more than 10 years old. Not everyone needs the latest-and-greatest version, and mastering a new function may not prove to be worthwhile, especially when older functions work just as well, if not better.

If you liked this post on how to use XMATCH, 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.