How to Convert Numbers From Billions to Millions to Thousands in Excel

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:

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:

1. Get rid of any letters.
2. 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:

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:

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:

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.