formatting – Page 3 – HowtoExcel.net

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.

Hate Merged Cells? Here’s an Easy Alternative!

January 23, 2021

by howtoexcel with No Comment formatting intermediate tips & tricks

Merged cells are great for presentation purposes but not so ideal when you’re creating a model or a complex spreadsheet. It can make copying cells or formulas painful as you can get an error or unintended results when doing so. However, there’s a way that you can achieve the same result as a merged cell without actually having to merge anything.

Align cells rather than merge them

When merging cells, you would select the cells you want to merge, and then click on the following button:

What happens in that case is that the cells you selected now become one. In the below example, if I click on Merge & Center, the text would get spread across multiple cells:

And become this:

By doing so, I can no longer enter a value individually in cells B1:F1 as the merged cell effectively takes over. If you never plan to copy over these cells then that’s fine but if you do, then you could end up with some annoying errors along the way.

However, there’s an easier, less intrusive way of accomplishing the same result. I would still select cells A1:F1 but instead of using the Merge & Center button I’ll go into the cell formatting menu (CTRL+1) which will give me the following options:

From here, I’ll adjust the Horizontal Alignment and select Center Across Selection:

Format cells menu selecting center across selection.

Now, my text is spread across those cells but it isn’t merged, it only looks that way. For example, I can still click individually on cell B1 and enter data there:

Selecting a cell when text is aligned across multiple cells.

And if I do that, it takes over the center alignment and pushes back the other text into cell A1:

Entering data in a cell that was already center aligned across multiple cells.

The advantage of using an alignment rather than merging cell is this avoids the errors that can come when you’re modifying or copying data and you get notifications that the cells are not the same size.

By using the horizontal alignment, you have the flexibility of making your text appear as if you’ve merged it across multiple cells without you needing to unmerge data later on if you want to change the text or add data.

If you liked this post on an alternative to merged cells 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.

How to Make a Line Break in Excel

November 9, 2020

by howtoexcel with No Comment data entry formatting tips & tricks

Are you entering multiple lines of text in Excel and want to break it up into multiple lines? You don’t have to adjust the cell size to do it and below I’ll show you some ways that you can manage cells that contain a lot of text, including how to make a line break in Excel.

Creating a line break

Here’s an example of a cell that could use a line break:

Currently, this bleeds onto where the start of the next cell should be. But rather than adjusting the length of my cell, I can position my cursor right after the period and before the ‘T’, hit ALT+ENTER, and now my cell looks like this:

Please note that if you want to create another line, you can’t just click on the cell and enter ALT+ENTER, you actually need to be inside the cell entering in values. To get into edit mode you can either click into the formula bar with the cell selected or click F2. Then, it’s a matter of selecting where you want to insert the line break. In the above example, the optimal position is just before the start of the second sentence. Then, once you’re there, you’ll click ALT+ENTER to move the following text down a line. You can repeat these steps to create as many lines as you’d like.

When creating an extra line, Excel automatically expands my cell vertically and selects the option to Wrap Text which is on the Home tab:

Using the ALT+ENTER shortcut tells Excel that you want to wrap your text and create a new line, which is what I’ve done in this example. Once wrap text is selected, your data will automatically conform to its cell size; the contents won’t bleed over into adjacent cells. For example, if I shrink my cell size then it no longer goes into the next cell, it just simply doesn’t show up:

If I were to double-click and auto-fit the column, then my cell would expand horizontally to accommodate the contents:

Wrapped text that's long enough horizontally.

However, if I were to double-click on the row and use auto-fit there, then the row would get larger and then my cell looks as follows:

Wrapped text that is expanded vertically rather than horizontally.

As you can see, once you’ve enabled Wrap Text, you don’t have to worry about your cell’s values moving into other cells. But at the same time, you may not necessarily want Wrap Text enabled for every cell since there’s the possibility that text gets cut off.

A good benchmark I normally use, especially for headers and where text may span multiple lines is to set the row height to 30. If that’s not enough, then I would at that point look at expanding the cell horizontally.

Another option that you have at your disposal if you want to accommodate a large value of text is to use Merge Cells. Generally, I’m not a big fan of merging cells because it can be problematic with formulas. But if you’re reserving this primarily for headers and text where there won’t be numbers in or near it, then it could be a practical alternative. That being said, I’d still keep this as a last resort.

If you liked this post on how to make a line break 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 Calculate Payback Period

October 30, 2020

by howtoexcel with No Comment cashflow financial analysis forecasting formatting intermediate

In previous posts, I went over how to calculate the internal rate of return and how to discount future cash flows to arrive at a net present value. Today, I’ll go over another way you can evaluate projects, and that’s using the payback period. The payback period calculation is a simpler method than the other two approaches in that it just looks at how long it’ll take for you to recoup your money from an investment, or when you’ll hit breakeven.

Setting up the spreadsheet

To do this calculation, I’ll again use the discounted cash flow spreadsheet from my earlier example. The key difference in calculating the payback period is that you don’t need to worry about present value since this won’t take into account the time value of money.

Let’s assume a scenario where you invest $1,000,000 into a project and generate cost savings of $100,000 every year. Here’s how that might look like over a 25-year period:

This is a really simple setup but let’s set up a formula to determine when the investment reaches breakeven. In this scenario, since the cash savings are always $100,000 every year, you can simply take the initial investment and divide it by the annual cost savings. The formula looks as follows:

After 10 years, the investment will be paid back in its entirety and reach breakeven. If your cash flows will vary over the years, what you can do is use an average to try and smooth it out and get to an approximate payback period. Another alternative is to create another column that shows your cumulative savings or cash inflows and how much is left to reach breakeven. To calculate a cumulative sum, just use a regular summation formula but freeze the first cell so that your formula will always start from the same position. Here’s how you might set this up:

You’ll see that cell C6 is frozen as that’s where my first value is, and that’s where the $1,000,000 outflow of cash is. I’ve also changed my cash flows so that they’re different amounts each year, and under this scenario, you’ll notice that it’s not until year 22 that I reach breakeven. A better way to illustrate this is through conditional formatting, by highlighting the negative values and the positive ones in different colors.

You can do this by selecting all the values in the cumulative field and under Conditional Formatting, selecting Format all cells based on their values, which gets you to this menu:

The first thing I’ll do here is to change the color scale so it shows three colors instead of just two. Then, I’ll set it up so that the lowest value is red, the midpoint is set to 0 and white, and the maximum is set to green:

Conditional formatting with a 3-color scale.

Then, after clicking on OK, my values look like this:

Cumulative cash flow with conditional formatting.

Using the conditional formatting, I can easily see the progression of the red into white (breakeven), and then into green. It’s a lot easier on the eyes and allows you to quickly see the progress. If you want to look into more ways you can do this, check out this post on conditional formatting.

Payback period when factoring in time value

If you just want to calculate the payback period using a simple formula and your cash flow / savings is the same every year, then simply dividing your total investment by that amount will suffice. Then, it’s simply a matter of determining whether the number of years in the payback period is acceptable to you. If it is, you can move forward with the project. If the payback period is too far into the future, then you may want to re-consider it.

However, when you’re looking at a longer timeframe, you may want to consider incorporating discounted cash flow to give you a more realistic picture of the payout period over time. And while the typical payback period calculation doesn’t incorporate the time value of money, that doesn’t mean you can’t do it. In this example, I’ll calculate the present value of the cash flows like I did in the earlier post which looked at discounted cash. Using a discount rate and raising the cash flow to a negative power (years in the future), I can arrive at the present value. Here’s how that looks in an additional column, with the respective formulas off to the right:

Note that I used a named range for the discount rate. Column B relates to the Year field and Column C is the cash flow value in the future.

This time I use Excel’s built-in present value function, which requires you to enter the rate, the number of periods, payments (not applicable here), and the future value (which needs to be negative for this to calculate correctly). Using a 5% discount rate, I’ve populated the present values of each of the future cash flows.

Now, I can add a column to track the cumulative values:

When factoring in the time value of money, my payback period is now well over 25 years. It’s an important reminder of just how important time value is. Under the previous payback period calculation that didn’t factor in the time value of money, the payback period was 22 years. In order for my payback period in this example to get to breakeven within 25 years, I’d have to set my discount rate to less than 1%. At 0.5%, this is what the schedule looks like:

Cumulative present value when at a 0.5% discount rate.

Only after 24 years does the project attain breakeven in this situation, and that’s with a minuscule discount rate. Normally, in a payback period calculation, you’ll just stick to the investment total divided by the savings or cash flow that the investment will generate. However, there are significant drawbacks to doing so when it may take many years for an investment to breakeven. In that case, it may be worthwhile to consider the time value of money.

If you liked this post on how to calculate payback period 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 Do Sensitivity Analysis in Excel

September 12, 2020

by howtoexcel with No Comment conditional formatting financial analysis formatting intermediate

Sensitivity analysis is a powerful way to make your template or Excel model update to reflect changes in variables. It makes it easy to run various what-if scenarios at once. In this post, I’ll show you how you can conduct sensitivity analysis in Excel in a way that’s user friendly and that can make your spreadsheet that much more versatile.

In this example, I’m going to compare two dividend stocks. One that pays a high yield right now versus one that pays a lower yield but that grows its payments over the years. I’ll look at how long it’ll take for the growing dividend to become larger than the one that’s higher today. I’ll also look at what the projections are when I make changes to my assumptions.

Setting up the analysis

First thing’s first, let’s start with the basic analysis. Once that’s setup, then we can move on to adjusting the variables and setting up the visuals. To make things simple, we’ll assume that the investment in both stocks is going to be a nice, round, $10,000.

Let’s say that in our example, Stock A pays a dividend yield of 3% per year and on average it will increase its payouts by 5% ever year. Stock B, however, won’t increase its dividend payments but it currently yields 7%.

Here’s how much dividend income each stock would generate annually over the years:

Under these assumptions, it would take 18 years before Stock A begins producing more in annual dividend income.

All that this spreadsheet is doing is just taking the total investment of $10,000 and multiplying it by the dividend yield for the first year. And for subsequent years, it’s adding on the compounded annual growth rate (CAGR). That will determine what the dividend payment will be after factoring in any increase. With Stock B, since there aren’t any increases, the dividend income remains the same. Stock A, however, increases by 5% every year.

To prove the calculations out: 1.05^18 * $300 = $721.99.

Now, suppose we change these assumptions and say that Stock A’s yield is 4% and that it grows by 6%, and Stock B’s yield remains the same. With those assumptions, it would take just 10 years before Stock A’s yield becomes the larger payout:

Comparing two dividend stock yields in excel.

But rather than updating our model each and every time, we may want to have a quick glimpse as to what these differences will look like at different dividend yields.

Adding in the comparables

Instead of repeating these steps over and over for different stocks, to do a sensitivity analysis, I can quickly compare Stock A against a series of other stocks. For instance, I’m going to keep the assumptions for Stock A the same, and now I’ll simultaneously compare it to stocks that yield 5% all the way to 10%. I’m going to create a column for each percentage and then calculate the difference between that column and Stock A. Here’s how that looks:

Sensitivity analysis of multiple stock yields.

All that I’m doing for these different columns is taking the value from Stock A and subtracting from it the dividend income earned at a 5% yield, at a 6% yield, 7% yield, and so on. The difference between a 4% yield and a 5% yield on $10,000 is just $100 (this the first value under the 5% column). But as the dividend rates rise, that delta grows. At a 10% yield, there’s a difference of six percentage points. That means the non-growing dividend stock pays $600 more in year 0.

One thing that helps a sensitivity analysis chart is some formatting. First, I’ll change the format of these numbers so that negatives show up in red. I can select the cells in the other columns and change their formatting to Currency and select the red option for negative numbers:

Formatting cells to show negatives in red.

I also removed the decimals to save space. Now, it becomes easier to see my data and when the numbers flip from positive to negative:

Applying formatting to sensitivity analysis.

Another thing I can do is add conditional formatting. Color scales can be really helpful here, such as these ones:

Using color scales to add conditional formatting.

Now it’s even easier to see the progression and how it relates from one dividend yield to the next:

Applying conditional formatting to sensitivity analysis table.

You can adjust the formatting to how you prefer. These are just some of the ways you can help your numbers pop out.

Changing your data becomes much easier

Now, what if the stock you’re comparing changes? You’ve found one that pays 4.5% and grows by 4%. You can easily change Stock A and now the rest of the values and the formatting will update:

Changing variables in the sensitivity analysis spreadsheet.

By being able to easily update your base stock (Stock A) and then just see the changes update for all your other comparables, you can easily run through various what-if scenarios on the fly without having to update all your other formulas. That’s where a sensitivity analysis becomes very useful; it prevents you from having to repeat steps over and over to compare different scenarios. It does it all at once for you and avoids the inevitable follow-up questions you may receive in your analysis of what about this scenario or that one.

And Another way to visualize the data, is of course, through charts. And rather than a boring line chart, one that I found particularly effective to demonstrate these differences is the 100% Stacked Area Chart. Here’s how it looks like:

I only mapped the first 20 years. That’ because by that rate, it’ll capture the year when Stock A surpasses the 10% dividend yield. The chart does a great job of showing the size of the differences over the years and just how much longer it’ll take for Stock A to overtake a 5% yield versus a stock that’s yielding 10%. It’s certainly not the only chart that might work. However, it definitely has a nice effect that helps it stand out and summarizes the data well.

If you’d like to follow along, you can download the spreadsheet I created for this example here.

If you liked this post on how to do a sensitivity analysis 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.

Color Calculator Template: Sum by Color in Excel

July 11, 2020

by howtoexcel with No Comment conditional formatting formatting template

Unless you’re still stuck on old versions of Excel, you probably know that you can filter data using colors. And by doing so you can use the SUBTOTAL function in excel to tabulate those amounts. But in this post, I’m going to show you something a little different. By using a macro, I’ll highlight cells containing values and then sum them by color in Excel, without needing a subtotal or a filter. This free template will just use VBA to populate the totals.

How the template works

In this template, all you need to do is enter your data and then assign whatever formatting you want to use to identify a cell to the corresponding category. As long as a cell has the exact same formatting as the category, it will be included in that category’s calculation.

I got the idea when I was trying to quickly analyze expenses and didn’t want to go through a whole process of putting it into a complex budget template. Rather than setting up logic to classify whether an expense falls into one category or another, I thought color-coding could be another way you could quickly group expenses.

Here’s a quick video showing you the template in action from color-coding cells to calculating the totals:

Setting up the data

In the template, there’s one section dedicated to the raw data where you’ll enter your inputs:

You can input data up until column N, although I’ve left the column blank for a buffer. In this example, I’ve put in random numbers and grouped them based on a store value in column A. This can be all numbers, it doesn’t really matter, I just preferred to have some grouping.

Next to the data entry, I’ve got a list of categories set up in column O:

You can add as many categories as you like. How they’re color-coded here is how you’ll need your cells will need to look to ensure they’re in the correct categories. For instance, any cells that are highlighted in light blue will go into Category I. Whereas anything in a dark red will belong to Category E.

You may think this would be a painful process to try and color-code your data based on all these different categories. After all, what if you get the shade wrong or the font color is wrong. The solution’s really simple and you just need to use our trusty friend, the Format Painter. If you’re not familiar with it, this is what it looks like:

It’s on the left-hand-side of the Home tab where the copy buttons are. What you can do is select the category you want to be assigning data to, click once on the Format Painter and then click on the cell you want to highlight with that exact same formatting.

If you’ve got multiple cells that you want to apply the formatting to and don’t want to keep repeating this exercise, then Double-Click on the Format Painter. You can now continue selecting cells and the Format Painter will take care of the formatting for you. You won’t need to re-select it each time. Once you’re done with the formatting and want to stop applying it, click on the Format Painter again to stop.

I’ve color-coded some of my data based on the categories, and here’s how it looks:

Updating the calculations

On the right-hand-side of the page there’s a button that says Update. This will update the totals based on the color-coding. You’ll need to have macros enabled for this to work. Above the button you’ll see the total of all the values in columns and how much is unallocated.

Clicking on the update button will trigger the macro to run the calculations. After pressing the button, here’s what my categories look like and the totals corresponding to their color-coding:

You’ll notice I’ve also created visuals to see the relative size of each category using the REPT function. If you’re interested in learning how to use this function, check out this post.

Anytime you make changes to the color-coding, be sure to hit the Update button. I didn’t want the formulas to update every time there was a change on the sheet because that can sometimes slow a spreadsheet down, especially since it would involve recalculating all the totals.

The code

Here’s the VBA code itself on how the update button works:

Sub Oval1_Click()

Dim clvalue, clcolor As Double
Dim cl, colorrange As Range
Dim lstrow As Integer

lstrow = ActiveCell.SpecialCells(xlLastCell).Row

Set colorrange = Range("colorrange")

'clear data range
colorrange.Offset(0, 1).ClearContents


For Each cl In ActiveSheet.Range("A1:O" & lstrow)

    If IsNumeric(cl) Then
    
        clcolor = cl.Interior.Color
        
            For Each lookupcl In colorrange
    
                If lookupcl.Interior.Color = clcolor Then
                    lookupcl.Offset(0, 1) = lookupcl.Offset(0, 1) + cl.Value
                    GoTo nextcl:
                End If
    
            Next lookupcl
    End If

nextcl:

Next cl

End Sub

There’s a named range called “colorrange” that it cycles through, and those are the categories in column O on the spreadsheet.

Using the template

This isn’t a terribly complex template to use. However, it can help if you want to quickly group items. It’s a unique way to classify expenses rather than just using drop-downs and complicated formulas. And it makes it easy to sum data by color in excel. The way I found it useful was to list your vendors or stores in column A. Then, arrange transactions by date (e.g. the first transaction is in column B, then C, and so on). But this can be used in a variety of different purposes to help classify data.

If you want to reset the calculations, just change the data back to the default formatting or something that doesn’t correspond to a category you already have, and then click update.

Download

The template is free to download and it’s available here.

If you liked this post on how to sum by color 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 Transpose Data in Excel

May 30, 2020

by howtoexcel with No Comment beginner formatting

It’s not often that you’ll need to transpose data in Excel, but when you do you’ll be happy to know how easy it is to do. In this post, I’ll go over not just transposing data but converting text to columns and showing you how you can change a block of text into data that you can use for analysis. After all, there’s no use trying to transpose data if it isn’t in a workable format.

If you want to follow along, the data I’m going to be using comes from the United States Census Bureau. In particular, I’ll be pulling the monthly retail and food services sales data for the past couple of decades. You can download that information here.

Copying the data into excel

Let’s start with the first step, and that’s to get the data into Excel. It’s in text, but the information is workable since it’s in the format of a table. I’m not going to copy the seasonal factors, I just want the raw, unadjusted sales numbers. To get the data into Excel, I’ll just highlight the sales data, copy it, and then paste it into a blank Excel sheet. Here’s how it looks:

The first thing that needs to be fixed is that everything is in column A. The data as it is won’t be useful for data analysis and needs to be cleaned up before it would make sense to try and transpose it.

Use text to columns to spread data across multiple columns

In Excel, there’s a Text to Columns button right on the Data tab that will help you to quickly and easily spread the data onto many columns. In our example, select column A and click on this button or something that looks similar to it if you’ve got an older version of Excel:

You’ll then see an option for how you want to break out the data. The default is Fixed Width:

However, that’s not an ideal way to split the data and it rarely ever is. Unless the data is always the same length it won’t be very useful. At best, it’ll be time-consuming to get the output in the format that you’re after. Instead, change the option to Delimited. Click on Next, and then you’ll see various options for splitting the data:

The data isn’t separated by a semicolon, comma, or any other distinctive character. There is, however, a space between each amount. That’s why we’ll want to unselect Tab and tick off the box for Space instead. We see a preview of how the data will be separated, which looks to be what we’re after:

At this point we can just click the Finish button and our output will now look like this:

Now we’ve got data that’s much more usable as every number is in its own individual cell. The one thing we’ll want to do before we get to transposing it is to get rid of row 2. The blank row effectively separates the headers from the data, and that’s not ideal.

Next up, let’s get to the actual transposing part.

Transposing the excel data

By transposing data, you’re flipping the rows and columns around. And to do that is pretty straightforward. First, copy the data, including the headers, and then click on a blank cell — I’m going to pick O1. Then, right-click and select Paste Special. There, you’ll see an option at the bottom that says Transpose:

Then your data will be flipped, or transposed. You can also select Transpose right from the Paste Special menu and select the icon from there. You can also use shortcut keys S and T to select the menu and then select the transpose button.

Now the data looks like this:

The years are now spread across the columns while the months are going down the rows.

Ultimately, whichever way you want to see the data comes down to personal preference. And by transposing it, you can change the view easily to make that happen.

If you liked this post on how to make a transpose data 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 Lock Cells in Excel

March 14, 2020

by howtoexcel with No Comment formatting intermediate ranges security

One of the most frustrating things that can happen to the spreadsheet you’ve spent hours or days on is when someone overrides a formula or a cell with important information. That why it’s important for people making and designing spreadsheets to be familiar with how to lock cells in Excel. It’s not a difficult thing to do and it can save you a lot of headaches down the road. There are two ways you can go about doing it. I’ll start with the approach that should work even on older versions of Excel:

Step 1: Unlock all the cells

By default, Excel sets that status of every cell to locked. However, until you actually protect a sheet none of that goes into effect. But you’ll actually want to undo that. The first thing you should do is select all the cells (CTRL+A) in your sheet and then go into Format Cells (CTRL+1). From there, go into the Protection tab where you’ll see this:

The protect cells tab on the format cells option.

Untick the ‘Locked’ box and this will leave all the cells unlocked. It sounds like the opposite of what you want to do. However, it’s easier to unlock everything and then re-lock the cells you really need locked. Doing the reverse is going to be more time-consuming.

Step 2: Select the cells you want to protect and then lock them

Now, its time to select the cells that you want to lock. Formulas, any sort of rate schedules or information that shouldn’t be changed are things you’ll want to identify. Once you’ve selected them all, you can go back into Format Cells and this time you can re-check the box to have them locked. If any of these cells contain formulas that you don’t want people to see, you can also tick off the Hidden box. Doing this will prevent anything from showing up in the formula bar when someone looks at one of these cells.

Step 3: Lock your worksheet

Even though you’ve already selected which cells you want to be protected, nothing’s technically happened just yet. What you still have to do is lock the worksheet itself. On the Review tab, you’ll have a button to Protect Sheet. Click on that, and you’ll have the following options to choose from:

First thing’s first, you’ll want to put a password in. Otherwise, there’s really no point in protecting these cells if someone can just unlock the sheet without any password.

By default, Excel selects the first to options when protecting cells, allowing users to select both locked and unlocked cells. However, you can untick the locked cells if you don’t want them to be able to even select locked cells. If your goal is just to protect cells, these two options should suffice. But you can also restrict formatting cells, inserting and deleting rows as well.

Once you click OK, if you’ve entered a password, you’ll be prompted to re-enter it again to confirm it. Now your cells are protected. If you attempt to change the value on any of them you’ll get the following error message:

Error message in Excel when trying to change the value of a locked cell.

If you want to be able to change those cells, you’ll now have to go back to the Review tab and click on Unprotect Sheet. There, you’ll be asked for a password if you’ve entered one.

Optional step: highlight the cells you plan to lock

To prevent frustrating your users, you may want to take an additional step and highlight the cells that they either should or shouldn’t enter values in. This will avoid them getting frustrated with error messages popping up when they try and make changes. Highlight cells can make it a lot easier for users to identify which cells they should edit.

Tip: here’s a quick way to to find all your formulas

If you don’t want to hunt down all the formulas in your sheet that you want to protect, the good news is it’s easy to find them. You can use CTRL+~ to toggle showing formulas or values on your worksheet. However, there’s an even easier way to select all your formulas. By pressing F5 you’ll get the Go To box:

Click on Special (this is greyed out once you’ve locked the worksheet, so you’ll want to do this beforehand). Then, click on Formulas in the next menu:

Selecting formulas in the Go To Special box.

Click OK and now all your formulas will be selected. From here, you can go to the Format Cells options and make sure they’re locked or hidden.

What if you’ve made a mistake and protected the wrong cells?

If you need to make a correction to which cells you’ve selected, all you need to do is to unprotect the sheet. Then repeat steps 1-3. But you may notice something odd when you go back to unlock all the cells:

Protected cells showing a mix of unlocked and locked cells as well as hidden and unhidden.

This is how the checkboxes will show up if not all cells are locked and not all cells are hidden. You’ll only see the boxes blank or ticked off if all the cells have the same values (e.g. protected or hidden). Clicking once on each of these text boxes will turn them into checkmarks:

All selected cells are hidden and locked.

If you were to click OK then all the cells would be locked and hidden. Clicking these checkboxes an additional time will make them all unlocked and unhidden (but not until you protect the worksheet):

All the cells are set to be unhidden and unlocked.

The alternative approach: Protect cells in Excel using the Allow Edit Ranges option

Below, I’ll show you another way how to lock cells in Excel that doesn’t require unlocking and re-locking cells. Rather than following steps 1-3 what you can first do is use the Allow Edit Ranges button on the Review tab. Pressing that button will give you the following options:

From here, you can click on the New button. Next, select a range that you want users to enter data on:

Creating a new range that users can edit.

In this example, I’ve set a name of Range1 to everything in column A. I’ve also put no password for this range. Now, if I go to protect my entire worksheet, I can still edit any cell in column A (regardless of it is set to locked or not). If I had added a password for that range, I’d be prompted to enter it before gaining access to the range:

You also have the option to designate certain users who don’t need a password to edit the range. You can do this by clicking on the Permissions button:

Changing the permissions of who doesn't need a password to access a specific range.

What’s also convenient about the Allow Edit Ranges feature is that you also can protect the sheet right from its options.

Which method should you use?

Ultimately, both methods can help you protect your cells and formulas in Excel. If your worksheet is very structured and there are only certain places where users should make changes, then using the Allow Edit Ranges approach probably makes a lot more sense.

If, however, most of your cells should be editable and there are only a few cells that you need to lock or they’re spread out all over the place, then the first method may be preferable.

Either approach can work and it may just come down to your personal preference on how to lock cells in Excel.

Adding additional controls—locking down the workbook

If you want to take things a bit further and prevent people from modifying, viewing, or deleting worksheets, what you can do is protect your workbook as well. This is a simpler than how to lock cells in Excel and all that you need do here is just click on the Protect Workbook button and put a password on your entire workbook. Doing this will prevent users from renaming, moving, deleting, or hiding worksheets.

If you’d just prefer users don’t see certain worksheets and don’t even see that they’re there, you may want to consider using VBA to hide them without having to lock your entire workbook down. In some cases, locking a workbook may work well at preventing worksheet changes, but it’s an all-or-nothing approach: you can’t pick and choose which worksheets they can change — that’s where VBA can help.

If you liked this post on how to how to lock cells 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 Hide Zero Values in Excel

February 1, 2020

by howtoexcel with No Comment conditional formatting errors formatting iferror intermediate tips & tricks

If you’ve got a big spreadsheet with lots of numbers to look at, it can sometimes be a bit difficult to look at a large chunk of data. That’s where knowing how to hide zero values in Excel can be helpful in reading and analyzing data in Excel. By not seeing the zero values, you can easily focus your eyes on the more important numbers that may need more analysis.

However, it doesn’t have to be just zero values that you hide. Any number that’s insignificant for your analysis can also be hidden. For instance, you can be analyzing a company’s financial performance and choose to hide any movements that are less than 5%. The same principles apply as you would use to hiding zero values. Below, I’ll show you how you can hide not just zero values but any values that you don’t want showing up in your data while still factoring them into totals and any other calculations. You won’t be deleting anything, just masking the information.

First, let’s take a look at how to hide a potentially even bigger nuisance: errors.

How to hide errors on a spreadsheet

To help illustrate how to hide zeo values in Excel as well as other numbers, I’m going to use some real-world data — Amazon’s most recent annual earnings report, which the company released last month. Here’s the company’s income statement from the past three years:

Amazon's income statement over the past three years in Excel format.

If you’d like to follow along, you can download the data from the SEC. First up, I’ll add a few columns showing the change from 2018 to 2019 and from 2017 to 2019. Here’s how it looks just copying the formulas straight down:

Analyzing Amazon's income statement using Excel with error values showing.

I have divide by zero errors as there are rows that have no data. I could just remove these cells but as with anything in Excel, it’s good to be consistent. Rather than deleting those error values, I can get rid of the errors in one of two ways.

The first is by using an IF statement to say that if the denominator is 0 or blank, to ignore the calculation. The second is just to use an IFERROR statement.

Here’s what my formula looks like for the 2018 to 2019 change:

=C5/D5-1

Where C5 is the 2019 data and D5 the 2018 numbers. I don’t need any parenthesis as order of operations ensures the formula will calculate properly. However, it doesn’t prevent me from getting a divide by zero error. Since the numerator can be blank or zero, what I’ll want to focus on is fixing the denominator in D5. To do this, I can add an IF function that looks at whether the denominator is a number. Here’s what my formula will need to look like to remove that error:

=IF(OR(D5=0,D5=””),””,C5/D5-1)

The formula now checks to see whether D5 is either a zero or blank, and if it is, it returns a blank value. Otherwise, it calculates as normal. Now I can copy this formula down and get rid of the error values.

An alternative way to fix this is by using IFERROR. Introduced in Excel 2007, the function can be an easy way to replace errors on your spreadsheet with another value. In this example, I’m going to use empty quotes (“”). The benefit is obvious: it’s a lot easier to use IFERROR than an IF statement, especially combined with an OR function as well. Here’s what my formula would look like with IFERROR:

=IFERROR(C5/D5-1,””)

It’s a whole lot easier and quicker. I don’t have to worry about the logic and all the reasons why the formula might error out. However, it’s not a perfect solution and here’s why: it will correct errors, but it’s possible they’ll be errors you’re not expecting. For instance, if I copied the data wrong or keyed something over and put text in a field where it should be a number, the IFERROR will correct that and you won’t be able to tell whether it’s blank because it is a divide by error problem or something else. That’s where it can be a little dangerous in using this one-size-fits-all approach to fixing error values. As long as you’re okay with that, it’s a perfectly good approach to fixing the divide by zero errors.

Here’s the data now that it’s been cleared of errors:

Analyzing Amazon's income statement using Excel after error values have been hidden.

That looks a lot better but the problem is that it’s still a lot of percentages to look at and it’s difficult at a glance to see what are the big changes are from the prior year. This is where it’s also important to hide zero values in Excel, as well as low values that aren’t useful for analysis.

How to hide zero values in Excel and other numbers that you don’t want or need to see

In order to hide data, it’s useful to use conditional formatting. If you’re not familiar with how to use conditional formatting, check out this post. Conditional formatting won’t remove or erase any data, which makes it a good solution that will keep all the data and calculations intact.

In the Amazon example, there are some pretty large-moving items in the list. Removing zero values won’t do anything and the threshold needs to be big for it to be helpful in hiding the lower values. Let’s start by removing the percentages that are less than 20%. Here’s the formula that I will use in the conditional formatting to accomplish this:

=AND(F5<0.2,F5>-0.2)

Column F is where the % Change from 2018 values are. I need to use the AND function because if I just look at anything that’s less than 20% this will also capture negative movements that are more than 20%. And for now, I want to keep those. I want to remove anything that’s between -20% and +20%, which is what the above formula will capture. If I was only looking to remove the zero values then the formula would be as simple as F5=0.

The next step is to adjust the formatting so that the cell font is white. Changing the color is an easy way to hide a cell’s value if it’s on a white background. While that data is still there, it won’t be visible:

Analyzing Amazon's income statement using Excel with error values fixed and low values hidden.

It creates a lot more white space, allowing me to see a lot more of the bigger values. The problem that I notice, however, is that there are some low values that are creating big movements in percentage. I can go a step further and create another conditional formatting rule that will also ignore the percent change for any item in 2019 that was less than $1 billion (1,000). This is how that formula will look:

=$C5<1000

I need to freeze column C because the conditional formatting will be used for the other change column as well and I don’t want the reference to move. Now, with this adjustment, it makes a much bigger difference and helps me narrow in on fewer items:

I can see that the significant changes from 2018, besides the totals, were in sales, technology and content, and marketing. However, since the growth rate from 2017 is even higher, I’ll need to adjust those percentages to also ignore anything that’s not at least a 50% improvement. Here’s how that formula will look (note that I’ll only apply it for the % Change from 2017 column):

=AND(G5>-0.5,G5<0.5)

Remember, since I’m analyzing percentages, these figures need to be in decimal point. Otherwise, I would be using whole numbers. With those changes, this is how my data looks:

Now I’ve also got a reasonable amount of items I can focus on for the % Change from 2017 column. In addition to the same items increasing from 2018, I notice that fulfillment costs have also shown a significant increase over two years.

The conditional formatting works great in clearing out numbers that I don’t want to see. However, there’s just one small problem…

Analyzing Amazon's income statement using Excel with hidden values showing on a dark background.

If I change the color to anything that isn’t white, those numbers that I hid become visible again. That leads me to another all-important section:

How to hide values in Excel that are on different background colors

You can create conditional formatting rules to address other background colors but that’s just not practical. If you use lots of colors on your spreadsheet the last thing you want to do is create a rule for every different color and make sure the cells are hidden in the same font color. There’s also a problem if someone changes the color too.

That’s why using font color to hide values in Excel isn’t a good idea. The good news is that there’s a much easier way to hide values that doesn’t involve you having to try and match up the color.

Rather than changing the color, what you should do is use a custom number format. Simply use three semi-colons and that will do the trick:

Choosing a customer number format to hide zero values in Excel.

Without going into the details of the different formats you can use, by using three semi-colons you’re telling Excel that you want no formatting to be used whether the amount is positive or negative. Now, my hidden data remains hidden regardless of the background color:

Analyzing Amazon's income statement using Excel with hidden values no longer showing on a dark background.

Now you don’t have to worry about background colors and can easily hide your data in any context.

If you liked this post on how to hide zero values in Excel as well as other values, 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.

This Macro Converts Your QuickBooks Export Into a Table-Friendly Format

December 22, 2019

by howtoexcel with No Comment accounting add-ins formatting macros template vba

QuickBooks does a good job when it comes to recording sales and doing day-to-day accounting tasks. You may be content with the reports that come out of QuickBooks, too. But if you’re looking for some more in-depth analysis to do of your own or to make your own reports, you’re likely going to want to move that data into Excel. And, unfortunately, the QuickBooks export into Excel can be less than optimal.

With many spaces, subtotals, and a non-tabular format, it’s not a very practical output to use in Excel. If you want to run a pivot table and do some serious analysis in Excel, you first have to clean up the data before being able to use it, and that can be a very tedious and tiresome process.

For example, this is what your QuickBooks report might look like when you’re pulling a simple summary of your customer sales:

There are a lot of things that need to be adjusted for this report to be useable in Excel, including getting rid of the blank spaces and ensuring that the customer information is repeated in the first column, as opposed to just in the first line and in the last line’s total. From afar, it’s a bit of a painful process to have to go in and clean this up. And while it’s not impossible, it’s not going to be quick, either.

That’s where a macro can help you make the task much quicker and it will save you a lot of time if you have to go through these steps often. Click the button on the ribbon and your data will convert into a more table-friendly format! Here’s how it works:

Using the macro to fix the QuickBooks export

Before running the macro, you’ll need to specify the columns where your customer names and dates are:

Then, run the Covert Data button:

Downloading the file

The free version of the QuickBooks macro will allow you to run the conversion if it doesn’t go past 100 rows. However, if you decide to purchase the full version please ensure that the macro and file works as expected. There’s no guarantee the QuickBooks export hasn’t changed or won’t change in the future. If there are changes that need to be made to the macro, please feel free to contact me so that I can make the necessary adjustments. Whether you prefer the add-In or the actual Excel spreadsheet itself, both versions are available both here and in the paid version as well.

Here is the download link for the add-in as well as the Excel file. For the paid versions, please visit the product page.

If you have another program or software that you’d like a similar add-in for, I can help with that as well.

If you liked this post, 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.