MultipleSort

How to Sort Data by Multiple Columns in Excel

Sorting data in Excel is relatively easy, and can be done with a click of just a button. However, it can be a bit more challenging when you’re trying to sort data by multiple columns. Once you’re familiar with the process, it’s not a whole lot more difficult. In this post, I’ll show you how you can do that.

How to sort just one field or column

In this data set, I have multiple fields that I can sort by:

Data set before applying a sort.

To sort by any field, it’s as easy as clicking on any column and clicking either the ascending button (the first button below) or the descending button (the second one shown):

Ascending and descending order buttons in Excel.

The ascending order button will sort values from A->Z, lowest to highest, or oldest date to newest date. The descending order button will do the reverse, and sort values from Z->A while amounts will go from highest to lowest. Doing this will sort one column at a time. If I sorted the data above by dates in ascending order, this is how it would look:

Data sorted by date.

This shows me the data from oldest to newest entries.

How to sort multiple columns in Excel

If I wanted to sort by date and then by store. I would need to apply multiple sorting rules. Even if I wanted them all to be in ascending order, I can’t just go and click on each column and click the ascending order button. If I did that, this is how my data would be sorted:

Data after applying multiple sorting rules.

The data isn’t sorted by date anymore. You can see that only the store names are sorted properly. This is because it’s the most recent sort that has been applied. And the last field I clicked on to sort was store, so that’s what it will be sorted by. There are a couple of ways I can fix this.

The first method is by going in reverse. Since the last column that I click in is what I’m sorting by at the top, that needs to be the first one I click on, not the last. If I click and sort (by ascending order) Store and then the Date field, this is what the data set will look like:

Data after applying multiple sorting rules.

Another way you can accomplish this is by clicking the Sort button:

Sort button in Excel.

Then, you’ll have the ability to specify your sorting rules. To accomplish the same sort as above, you would set it up as follows:

Creating sorting rules in Excel.

The advantage of this approach is you don’t have to work backwards. It can be simpler to plan out how you want to sort your data without having to worry about remembering the sorting rules in reverse. For larger, more complex sorting rules, using the Sort button is going to be easier. If, however, you only have a few fields you want to sort, it may not make a difference which method you choose.


If you liked this post on How to Sort Data by Multiple Columns 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.

H2EconvertMonthNumber

How to Convert Month Number to Month Name in Excel

Do you have a report in Excel that lists the months as the numbers 1 through 12 and you want to convert that into the actual month names? Below, I’ll show you how you convert a month number into a month name in Excel.

Here’s an example of data that shows monthly sales but it only lists the number as opposed to the name:

Sales by month with the month number showing in digits.

If you had the entire date in a cell you could format it so that it showed the month. For instance, what I could do is type in =TEXT(A1,”MMM”) which would convert the value in cell A1 into a three-letter abbreviation for the month. But the numbers 1 through 12 will return values of “Jan” as Excel will think that you are referring to the first month of the year.

However, that changes once you get to the number 32. Since there are only 31 days in January, the number 32 will return a value of “Feb” if you were to continue on with that formula. And so the trick is to multiply these values all by a factor of 28. Since that’s the minimum number of days every month will have, it ensures that jumping by 28 each time will put you into each month of the year. This is what my values will look like:

Month numbers multiplied by 28.

To prove this out, here is which dates those days of the year would correspond to:

Day of the year along with the corresponding date.

In month 12, we barely make it in December using this approach but that’s good enough. And even in a leap year, multiplying by 28 still works. In this example, I include 2024, the next year that February gets an extra day:

Day of the year along with the corresponding date, including a leap year.

So now that we’ve confirmed that those numbers will fall within the correct months, we can use the TEXT formula noted above to convert those numbers into month dates, and this is what we end up with:

Month numbers converted into month names.

You can also multiply by 29 and this logic will still work. But if you use 27 then your months will be wrong by the time you hit September and if you use a multiple of 30, then in non-leap years you will be jumping too quickly and you will have two dates in March.


If you liked this post on How to Convert Month Number to Month Name 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.

H2Etax

How to Calculate Tax Included in an Invoice

If you are creating an invoice and need to account for taxes, usually you just need to multiply the subtotal by the percentage due for taxes. However, it gets trickier when the tax amount is already included within the invoice total and you need to work out what the amount relating to tax is. This is important if you need to determine how much in taxes you need to claim on an expense or how much you need to collect if you’re the seller. Below, I’ll go over a sample invoice calculation to show how can determine the tax amount whether it is included in the total or not.

Calculating taxes on an invoice

Let’s start with the basic calculation. This is how you might normally determine the taxes on an invoice and the total invoice value:

Sample invoice calculation including taxes.

The calculation is straightforward as what you do is just take the subtotal, multiply that by the tax rate, and add that back to the subtotal. Another way is to just take the subtotal and multiply it by a factor of 1 + the tax rate. In this case, it would $100 x 1.10. But let’s pretend we don’t know the subtotal and just know that the invoice total is $110.00 and the tax rate is 10%. In order to calculate the pre-tax amount, we need to do the steps in the opposite order. To prove this out, let’s use a bit of algebra:

$100 + ($100 x 10%) = $110

This can be simplified as follows:

$100 (1 + 10%) = $110

Now let’s solve for $100 which I will assign a variable of ‘y’ to:

y (1 + 10%) = $110

To solve for y, all we need to do is move the factor of 1 + the tax rate and divide $110 by that:

y = $110/(1 + 10%)

Taking $110 and dividing by 1.1 will give us a value of $100. And so what our end result comes out to is essentially this:

invoice total / (1 + tax rate) = pre-tax amount

To calculate the tax, all that’s needed then is to take the total and subtract the pre-tax amount.

Now that the logic is set up, let’s convert this into an Excel formula:

Invoice calculation when the tax amount is included.

Similar to how multiplying by a factor of the pre-tax amount by 1.1 (when the tax rate is 10%) would get you to the invoice total, dividing the total by 1.1 would get you to the amount before taxes. If the tax rate were 5%, then you would use 1.05, etc.


If you liked this post on How to Calculate the Tax Amount When it Is Included in the Total, 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.

h2eVariances

How to Calculate Variances in Excel

In this post, I’m going to show you how you can easily calculate variances in Excel. I will also go over how to group variances and how using pivot tables, charts, and conditional formatting can help save you time in reviewing them.

For this example, I’m going to use data from the S&P 500 as stock prices frequently fluctuate. To start, I’m going to download the data from the past year. I’m going to remove everything except the closing values just to keep this example simple:

Download of the S&P 500 closing prices over the past 12 months.

Calculating the variances

The calculate the variance in these data points, what I need to do is to take the current closing price, and subtract the previous day’s closing price from it. That will tell me how much of a move there was that day. On June 7, for instance, the S&P 500 fell from 4,229.89 on June 4 (the previous trading day) to 4,226.52. If I minus the current day’s close from the previous, I get a value of -3.37.

But we can dig a lot deeper than just looking at the difference in price. Let’s also create a field to indicate whether these variances are positive or negative. To do that, I’ll create another column called ‘Direction.’ For this calculation, I will take a look at the value in column C (where my variance is) and create a simple IF formula:

=IF(C2>0,”Positive”,”Negative”)

Here’s what my sheet looks like now:

Table of variances showing positive and negative values.

Although you can determine whether it is positive or negative from the variance field, by creating another column you can quickly filter if you want to look at all the negative or positive values. Another column I’ll insert here is for the percentage change.

To do this, what I will do is take the variance amount and divide it by the previous day’s closing price. This will tell me how much the price has moved as a percentage of what its value was the day before — which is much more useful than just looking at the raw value. After inserting the column, I have the total variance, variance %, and which direction it went in:

Variances by raw amount, percentage, and positive or negative indicator.

I changed the variance % field to show percentages and I added a few decimal places since the percentages are fairly small. To add decimal places, go to the Numbers group on the Home tab and click the following button on the left:

Button to increase or decrease the number of decimal places.

The one on the left will add decimal places while the one on the right will remove them.

However, what if you don’t care about positives or negatives and are just interested in the absolute value of the changes? I’ll cover that next.

Calculating changes in absolute value

With absolute value, you remove the positive or negative indicator. And to calculate a variance this way, you just need to add a formula to the calculation in the variance field. Rather than this:

=B2-B3

You would enter this:

=ABS(B2-B3)

Now, my variances update and I no longer have a use for the Direction field since all the values will be positive:

Variance table when only calculating absolute values.

Alternatively, you could also just create another column specifically for the change in absolute value.

Now that the variances have been created, what you may want to do next is to group them.

Grouping variances

Why would you want to group variances? The big advantage in doing so is they can make it easier to analyze a large data set by showing you where the bulk of the variances are.

Rather than creating a bunch of IF statements, what I’ll do is create a table to show where the variances belong:

Table grouping the variances.

I’ve created a named range called VarianceTable for this. And now, all I need to is use a VLOOKUP formula to find which category a variance belongs in. Since I’m not using an exact match, I will set the last argument in the function to ‘TRUE’ :

=VLOOKUP(D2,VarianceTable,2,TRUE)

Now I have a category field instead of the Direction:

Table with variances grouped by category.

But this doesn’t tell me a whole lot. I could filter by the category. However, a better approach is to create a quick pivot table that shows me a summary of where the values fall:

A pivot table showing the count of the different variances groups.

And from that, I can quickly display these variances on a chart:

A chart showing variances by category.

Another way you can help identify extreme values in variances is by using conditional formatting. To apply conditional formatting, select either the variance column or the variance % column and under the Conditional Formatting button on the Home tab, you can select either Data Bars or Color Scales. I prefer using Data Bars since there are fewer colors:

Selecting data bars under the conditional formatting section.

Then, my variances are easier to visualize and to see where the highs and lows are:

When you are analyzing variances, using conditional formatting, pivot tables, and charts can help you summarize your findings.


If you liked this post on How to Calculate Variances 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.

9tips

9 Things You Can Do to Make Your Charts Easier to Read

An Excel chart can provide lots of useful information but if it isn’t easy to read, people may skip over its contents. There are many simple things you can do that can quickly add to the visual to make it fit seamlessly within a presentation and that makes it more effective in conveying data. If you want to follow along, in this example, I am going to use data from the Bureau of Economic Analysis. In particular, I am pulling data on automobile sales both in units and average dollars. Here is what my data set looks like right now:

Auto sales by month.

And this is my chart, which shows unit sales by month:

Chart showing auto sales by month.

It’s a pretty basic chart that can show me the breakdown between the sales. These are the following changes I can make to improve the look and feel of it:

1. Add a legend

Unless you are just charting one item, most visuals will benefit from a legend. Otherwise, it will be difficult to know which data is represented where. To add a legend, all you need to do is select the chart and go into the Chart Design tab and select the Add Chart Element button, there you will see an option to determine where you want it to show up:

Adding a legend to an Excel chart.

In most cases, you’ll probably want this on the top or bottom as that will help make it blend in easier with the chart. Here’s how it look after I add the legend:

Excel chart with legend added.

Since my descriptions are long, putting them at the bottom will make more sense. Now I can easily see which bars relate to the foreign sales and which ones relate to domestic.

2. Shrink the gaps (for column charts)

If you have column charts, it can help to shrink the space in-between the bars. That will eliminate white space plus you can fit more items in your chart. To adjust the gaps, right-click on any of the bars and select Format Data Series.

Formatting the data series.

I normally set the Gap Width to 50%. Upon doing so, my chart changes to the following:

Changing the gap width on column charts.

3. Adding a descriptive title and subheader

I haven’t set a title for my chart and that’s one thing you shouldn’t overlook doing. Although it may not seem necessary, doing so can help ensure that your chart can stand on its own and not have to rely on the context it is used in to give the reader the right information. A good example in this case can be as follows:

Adding a title and subheader for an Excel chart.

The main title is bolded and shows the reader what the chart is about. And the subheading further distinguishes the different groups of data.

4. Adding data labels

You may want to consider adding data labels to make it easy for the reader to see the exact numbers your chart is showing. This prevents having to make any estimates or rounding off and quoting an incorrect number. To insert data labels, right-click on one of the column charts and select Add Data Labels. Do this for each data series you want to add labels for. This is how my chart looks, with labels:

Labels added to an Excel chart.

You can modify the labels if you want to add more information besides just the value. This will depend on the type of chart you have and how much space is available. In this example, you probably wouldn’t want to add more information. However, what I will do is shrink the text size so that it is a bit smaller and so that everything looks less cluttered. To do that, I just click on any of the data labels and under the Home tab, make changes to the font size or color the way I normally would with any other data in Excel. After shrinking the font to size 7 and making it grey, here’ show it looks:

Excel chart after adjusting data labels.

5. Adding a data table

If you don’t want to add data labels, another thing you can do is add a data table. This avoids putting any numbers or labels over top of your data series and still gives the user a helpful table summary. This is a great alternative if you don’t want to crowd too much information into one place and prevent your chart from looking too busy. To add a data table, just go back to the Add Chart Element drop-down option and select Data Table, where you can specify if you want to include the legend key or not. This is how the chart looks with the table:

Excel chart with a data table.

If you want to avoid the repetition in the axis labels without deleting them and losing those headers, one thing you can do is to change the text format. To do that, right-click on any of the axis labels and select Format Axis. Then, in the Number section, enter three semicolons in the Format Code section and click the Add button:

Setting the format code for an Excel chart.

The three semicolons will remove any formatting and now the axis and data table wouldn’t double up on the names:

Excel chart with data table but no duplicate axis labels.

6. Remove the border

If you are using the chart in a Word document, presentation, or even Excel, eliminating the border around it can make it blend much easily with the background and other information. To remove the border, right-click on the chart, select Format Chart Area, and under the Border section, select No line. After making the change, this is what my chart looks like now:

Excel chart without a border surrounding it.

With my gridlines turned off, you can no longer see the lines that show where the chart starts and ends.

7. Use a secondary axis with multiple chart types

So far, I’ve only used column charts to show the number of units sold. However, now, I will also include the average selling price. But because the selling price can be in the thousands, I’ll want to move this onto another axis. Otherwise, the number of units sold, which are in millions, won’t show up because of the scale as it will need to accommodate values that are in the tens of thousands.

When you want to put a data series onto another axis, you will need to go to where you select the chart type. If you go to the bottom, select the Combo option. There, you can specify which chart type should be used for each data series. That’s also where you can specify which one should be on a secondary axis. In this example, I’m going to use a line chart for the average price and continue using a column chart for the number of units sold. It doesn’t matter which data set I put on the secondary axis. However, note that the one that is secondary will be on the right-hand-side of the chart.

Setting up multiple chart types in Excel.

This is what my updated chart looks like:

Multiple chart types in a single visual.

In this case, I’ve gotten rid of the data labels for the column charts so that it doesn’t interfere with the line charts.

8. Move the axis categories down

In the examples thus far, I haven’t had any negative values. However, suppose I change my data to now show the change in units sold from one month to the next:

Change in number of vehicles sold, month over month.

For this example, I combined the data so that it totals both domestic and foreign cars. The above chart shows the month-over-month change. But one problem you’ll notice is that the date labels run along the middle of the chart. This makes it difficult to read when there are negative values.

To make this easier to read, I am going to move the axis labels to the bottom This is useful when dealing with negatives. To make this change, right-click on the axis and select Format Axis. Then, under the Labels section, set the Label Position to Low.

Setting the label position to low.

Now, when my chart is updated it looks like this:

Chart with axis labels at the bottom.

9. Showing negative values in a different color

One other change that is going to be helpful when dealing with negatives is to change the color depending on if the value is positive or negative. All you need to do to make this work is to right-click on the column chart, select Format Data Series and switch over to the Fill section. There, you will want to check off the box that says Invert if negative:

How to invert colors if negative on an Excel chart.

Once you do that, you should see two different colors you can set aside for the color section. If you don’t, try and setting one color first, and then toggling the Invert if negative box. With the two different colors, my chart looks as follows:

Excel chart with negatives in red, positives in green.

While you can obviously tell if a chart is going up or down, adding some color to differentiate between positives and negatives just makes the chart all that more readable.


If you liked this post on 9 Things You Can Do to Make Your Charts Easier to Read, 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.

H2Ecountif

How to Use COUNTIF in Excel

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:

COUNTIF function by category.

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:

COUNTIF function on similar criteria.

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:

COUNTIF function with a wildcard.

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:

Using wildcards at the start and end of a string of text.

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

Counting anything that contains just a single letter.

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:

Using the COUNTBLANK and COUNTIF functions.

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:

Using the COUNTA function.

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:

Companies listed by their market caps.

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:

Counting the number of companies worth more than $1 trillion.

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.

Convert1

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:

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

  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:

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.

H2Eerror1

Deleting a Formula in Excel? Do This First

When you’re dealing with complex spreadsheets in Excel, it can sometimes be difficult to tell which cells are safe to delete and which ones you need to keep to ensure everything is working properly. Even cells that look empty could contain formulas. And deleting them can cause problems and wreak your spreadsheet. Before you delete a formula, there’s one thing you can do to prevent that mistake:

Check for dependent cells

If you’re not sure if a cell is okay to delete and if it has any other cells that depend on it, you can check for dependents. Before deleting a cell, you can click on CTRL + ] which will highlight any cells that use the active cell in a formula (on the current sheet). Here’s a sample spreadsheet that lists price, quantity, and multiples them to get to a total price:

Spreadsheet that calculates the total by multiplying price by quantity.

The formula in column D multiples the value in B by C. So that means the value in D depends on the values in C and B (the exception is the subtotal, which depends on the values above it). If I select cell C2 and click on CTRL+], it takes me to cell D2:

The dependent cell is highlighted.

If there is more than one cell that depends on the active cell, then Excel will highlight all of them.

What if there aren’t any dependent cells? In that case, you’ll get the following message:

Message box saying no cells were found.

If you get this message, that means you’re safe to delete the current cell as nothing in the current sheet links to it. However, the one limitation of using the shortcut is that it may not be easy to see all the cells that depend on that one cell. It also won’t tell you if there is a cell on another sheet that uses it.

What you can do is use the Trace Dependents button in Excel, which is on the Formulas tab:

Trace Dependents button.

By clicking on this button, arrows will now show up telling me exactly where the dependent cells are:

Arrow showing the dependent cell.

In this situation, the arrow clearly shows an arrow pointing to cell D2. Let’s say I also use the cell in a formula in some place far off in a the same sheet:

Arrows showing multiple dependent cells.

Another line will point to the other cell. If you have a large data model that goes on for many rows and columns, it may not be obvious where the dependent cells are if you use the shortcut key. Using the shortcut can be helpful as a quick check but if you actually want to see all the cells that use the active cell, you’re better off clicking the Trace Dependents button.

Next, let’s go to the subtotal. Here, let’s assume I’m using this total somewhere on another sheet. Using CTRL+] won’t help me much in this case as it will tell me no cells were found (assuming no cells on the current sheet link to it). But if I click on Trace Dependents, it will show that there is a dependent cell on another sheet:

Dependent cell is on another sheet.

If you double-click on the dotted line (the portion that’s within the cell), the following box will pop up:

Go to box showing where the dependent cell is located.

This tells us that there is a dependent cell on Sheet2, cell B1. I can go there manually or I can click on the selection and then press OK. Then it will take me directly to the cell:

Dependent cell that links to another sheet.

This isn’t practical on a wide scale as you would have to go one by one and you could have arrows going all over the place. But if you’re not sure about a certain cell, using the Trace Dependents button can be a quick way to see if it’s safe to delete the cell.


If you liked this post on 1 mistake to avoid when deleting formulas 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.

H2Epercentages

How to Calculate Percentages in Excel

When doing any kind of data analysis, it’s important to be able to pull in not just raw data but also to show percentages. From a period-over-period percent change to how much an item represents of a total, showing a percentage can give readers multiple different viewpoints. Below, I’ll show you how to calculate percentages in Excel and to give your data more context.

In this example, I’m going to use data from Netflix’s most recent quarterly results. The streaming giant always releases its numbers in a friendly Excel format, making it easy to analyze the data. Here’s what its income statement tab looks like, unchanged for the second quarter of fiscal 2020, which includes previous periods:

Netflix income statement in Excel.

Showing period-over-period changes

Netflix’s numbers look impressive — $6.1 billion in revenue for the quarter ending June 30, 2020. However, that number on its own may not be very helpful. One way to add some context is to calculate the percentage change to show the increase or decrease from a previous period, aka its rate of growth.

I’ll add a column next to those quarterly results and add a formula that shows the percentage difference from the previous quarter (ending March 31, 2020). To calculate the percent change, all I need is to take the difference and divide it by the old number, or base amount. A good way to remember this is: (new-old)/old.

In this example, column Q contains the quarterly results for June 30 and column P is the previous period. And the revenue is in row nine. The formula for the first item looks as follows:

=(Q9-P9)/P9

Where Q9 is the new total, while P9 is the old number. This gives me the following:

Quarter over quarter change.

The $0 isn’t really helpful here, and it’s also not a dollar amount. Excel’s just defaulted it to that format based on the other numbers. To properly show it as a percent change, I need to change it to a % format. It’s as simple as selecting the entire column and clicking on the % sign on the Number tab:

Number group in Excel.

That will now give me the following results, after centering the column:

Quarter over quarter change in percent format.

However, this still may not be ideal. If I want a bit more detail, such as to show multiple decimal places, you’ll again want to go back to the format section and select the item to add decimal places:

Add decimal places button.

Clicking on this button twice will now give me a couple more decimal places:

Percent change with two decimal places.

Now, with my percentage change looking correct, I can copy the formula down for the rest of the items:

Quarter over quarter percent change across all items.

Showing the percentage of a base amount, or grand total

Another way you may want to show a percentage is how much an item makes up of a total. One common way to analyze financial statement is to look at items as a percentage of revenue. A company’s profit margin, for instance, takes its total profit and divides it by revenue to determine what percentage of its top line makes it through to the bottom line.

How to calculate percentages in Excel when just looking at how much an item makes up of a grand total is an easier process. In this example, the calculation just takes the current item and divides it by revenue. The key is just freezing the denominator, which in this case is revenue. Here’s how the formula looks like:

Percent of revenue by line item.

Using the % of revenue analysis, it’s easy to see that operating income was 22% of revenue and Netflix’s profit margin was 11.7%. Replicating these formulas for other periods can help compare multiple periods to see the % of revenue trends. Here’s how the current quarter looks against the previous one when looking at the percent of revenue:

Multiple periods percent of revenue analysis.

Compared to the earlier quarter, Q1, the profit margin becomes a bit less impressive in Q2 as it’s declined from the previous period. Despite a stronger overall Q2 performance, a higher tax bill led to a smaller overall profit margin than in Q1.

As you can see, by adding percentages to your analysis you can create very different viewpoints and add a lot more context to the numbers.


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

vlookup1

3 Reasons You Should Still Use 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.