When displaying data, using percentages isn’t always optimal, especially if you’re dealing with a very tiny number. In those cases, it may be more effective to display data as a rate per 100,000 or per 1 million, depending on how small of a number you have. Below, I’ll show you how you can move between percentages and rates.
Converting between percentages and rates
To convert percentages into rates, it’s as simple as multiplying the percent by the population you’re trying to calculate a rate for. Some common examples are calculating rates per 10,000, per 100,000, or per 1 million.
Let’s start with a simple stat: there are approximately 50 million Microsoft 365 subscribers in the world. Out of a global population of 7.9 billion people, that is 0.6% of the total population. Let’s frame this a different way, as a rate. To do this, I can multiply that percentage by 100,000, which returns a value of 633. That tells us that for every 100,000 people, 633 of them have Microsoft 365 subscriptions. You can multiply this by 10 to say that for every 1 million people, more than 6,300 will be subscribers.
Now let’ do the reverse. The odds of winning the Powerball jackpot are approximately 1 in 292 million. To convert this into percentages, we’ll need to divide 1 by 292,200,000. The result is a very tiny value of 0.0000000034. As you can see, this isn’t very helpful in using this as a percentage. And this is why using a rate is more appropriate.
Calculating 1 per a larger base
If you’re working with that incredibly small value, you can convert that into a rate of 1 per some larger number. All you need to do is calculate the inverse. To do that, take 1 and divide it by the value. In the above example, it would be 1/0.0000000034, which would return a value of 292 million.
Creating a quick template
If you have some small percentages you want to convert into percentages, you can create a quick template to help you determine which rate you may want to use. In some cases, you may not want to just use 1 per x but instead x per 100,000, or some larger figure. You’re communicating the same value, it’s just a matter of how you decide to do it.
Below, I’ve collected some data showing the percentage of dog owners in the world, the percentage of people who have green eyes, and the percentage of people with red hair:
I’m going to create four additional columns, one to calculate the inverse (1 per x), rate per 10,000, rate per 100,000, and per 1,000,000. For the last three columns, all you need to do is to multiply the percentage by those base numbers. And here’s what the results will look like:
A quick way to check these results is by calculating the percentages. 60,000/1,000,000 is equal to 6%, 20,000/1,000,000 is 2%, and 15,000/1,000,000 is 1.5%.
The advantage of using the larger population number is that your results will stand out more and can be easier to visualize on a chart:
If you liked this post on How to Show Percentages as Rates Per 100,000, 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.
Did you know that you can group numbers in Excel using tags? By just listing all the categories an item should belong to, you can make it easier to group them. In this post, I’ll show you how you can use tags in Excel to efficiently summarize different categories.
Creating tags
Suppose you wanted to list all the possible streaming services you might subscribe to. You might have a list that looks something like this:
This is fine if you want to compare them or even tally them all up. But what if you wanted to look at different scenarios, such as what if you select some of these services, but not all of them? This is where tags can be really helpful. Let’s say I want to create the following categories:
Basic
Kids
Tier 1
Tier 2
Tier 3
Each category will have a different mix of services. Here’s how I can use tags to make that happen. I’ll create another column next to the price where I specify all the categories a service will fall under:
In the above example, Netflix is included in every package but HBO Max is only included in Tier 3. Next, what I’m going to do is create columns for each one of these tags, such as follows:
Without using tags, you might be tempted to put a checkmark to determine which service belongs in which category. But that’s not necessary here. Instead, I’m going to use a function to determine whether to pull in the price or not.
Using a formula to determine if a tag is found
The key to making this work is the SEARCH function. This will look within the tag values to see if there is a match. If there is, then the price will be populated within the corresponding category. To check if the ‘basic’ keyword is found within the tags related to Netflix (assume this is cell C2), this is how that formula would look:
=SEARCH(“basic”,C2,1)
This will return a value of 1, indicating that the term is found at the very start of the string. If you use the function to look for the word ‘kids’ then it would return a value of 8 as that comes after ‘basic in my example.’ Of key importance here is that there is a number. If there isn’t a number and instead there is an error, that means that the tag wasn’t found. I will adjust the formula as follows to check if there is a number:
=ISNUMBER(SEARCH(“basic”,C2,1))
This will return a value of either TRUE or FALSE. But the formula needs to go further than just identifying if the tag was found. It needs to pull in the corresponding value. To do this, I’ll need an IF statement to extract the value from column B:
=IF(ISNUMBER(SEARCH(“basic”,C2,1)),B2,0)
By freezing the formulas and copying this across the other categories, this formula will now allow me to pull in the amounts correctly based on the tags:
But let’s say you don’t even want to do this, you just want to quickly group the totals without these extra columns. You can also do that with the help of tags.
Summarizing the totals by category
You don’t need to create a column for each group if you don’t want to. You summarize the total in just an array formula. Simply use the formula referenced earlier and include it within a SUM function, while referencing the entire range:
This is the same logic as before, except this time the values will be totaled together. On older versions of Excel, you may need to use CTRL+SHIFT+ENTER after entering this formula for it to correctly compute as an array. But if you’re using a newer version, you don’t need to. If you copy the formula to the other categories, you’ll be able to sum the values by without the need for additional columns:
If you liked this post on Using Tags 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.
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:
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:
To prove this out, here is which dates those days of the year would correspond to:
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:
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:
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.
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:
And this is my chart, which shows unit 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:
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:
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.
I normally set the Gap Width to 50%. Upon doing so, my chart changes to the following:
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:
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:
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:
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:
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:
The three semicolons will remove any formatting and now the axis and data table wouldn’t double up on the names:
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 ChartArea, and under the Border section, select No line. After making the change, this is what my chart looks like now:
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.
This is what my updated chart looks like:
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:
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.
Now, when my chart is updated it looks like this:
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:
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:
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.
In a previous post, I covered how to create a form in Excel. Although I didn’t go over drop down lists specifically, they are one element you could incorporate into them. The problem is that your list can change over time, getting bigger or smaller. And that can make it difficult to maintain if your list isn’t dynamic as it will involve you always having to manually change the range of your drop down list. Otherwise, it could be incomplete or contain blanks. Below, I’ll show you how you can manually change your drop down list in excel and create it without blanks while also making it dynamic so that you don’t need to worry about whether it changes over time.
Setting up the drop down list
First, let’s start with the basics — creating the list. To create a drop down list in Excel, you just need a series of options to choose from. My list is going to be made up of the top 30 places to visit. I’m just going to put those names in a list.
After entering in the list of places into Excel, the next thing I will do is select all the values, and create a named range. This is as simple as just entering a value next to the formula bar, where you see the cell location. I will call this range VacationSpots:
There is no need to add headers or anything else. Just select the values, enter in a name for the list, and hit enter. A longer approach would be to go to the Formula tab and select Name Manager:
Clicking this will show you all of the named ranges in the workbook:
It shows you the named range I created. However, I could also create one from this screen and also Edit my existing range. This is where you would go to make the change manually. Clicking on the Edit button would give you this screen:
As you can, here I can manually change the address as needed in case the list changes. However, this is obviously not optimal as it can be a tedious process if your list changes frequently.
Creating the drop down
Now that my list has been created, I can set up the actual drop down. To do this, I’m going to select a cell and under the Data tab, click on Data Validation. Here, there is a place to enter your list of values:
Under the Allow section, I choose List. And for the Source, I enter the ‘=’ sign followed by my named range, VacationSpots. Now, when I click OK and go to the cell that contains the data validation, this is what I see when I select it:
Clicking on the drop down arrow will show me my list of options, in the same order that they appear in my list:
I can select any of the values and my cell will update accordingly. This is great, but what if I decided to add more items to my list, perhaps 10 or 20 more locations I want to visit? Next, I’ll go the different ways you can create drop down lists in Excel without blanks.
Option 1: Create extra spaces in your drop down list at the very end
Technically this step involves blank spaces, which is not what this post is supposed to be about. However, I just wanted to show you how this could work. If your list has dozens of items, then having extra blanks may not be that big of a deal. For example, say I edit my named range so that it goes to 50 rows. If you do that and include empty cells, this is the biggest problem you’ll face:
My list no longer starts from the top, it goes to the first blank cell. This can be an annoying problem because now it looks like all of my options aren’t there (they are, but I have to scroll up every time). This is probably the main reason people want to avoid having blank values in their lists. If the blank values simply came after all of your selections, that might be more tolerable. But because they impact where your drop down list begins from, it can be a nuisance.
The good news is there is a simple way to get around this. For all your empty cells, enter just a single empty character. Select a cell, hit the space bar, get out of the cell, and copy that value down. Now, your empty cells technically aren’t empty because they contain a space. And by doing so, the drop down list now starts from the top again. You will still have blank values, but this time they will show at the bottom of your drop down list:
If this is acceptable then you can stop here. If you are still intent on getting rid of any possible blank value whatsoever, then head over to the next option.
Option 2: Creating a table to create a nonblank list
This option is the easiest method for getting rid of blank values. What you need to do here is convert your list into a table. Select a cell on your list, click on the Insert tab and then click Table:
Leave the option for headers unchecked and then click OK. You should see something like this:
By default, Excel will apply its formatting and design but you can change the look of it to make it blend in more with your spreadsheet. You can also re-name the header from Column1. Either way, you can now create a new drop down list from this table. Since the values are in range A2:A31 in my spreadsheet, that is what I will enter for my new Data Validation list:
You can either select the range, or enter it in yourself. But if you enter it, you need to include the $ signs otherwise it will not auto-update properly. Now, I’ll go to my list add ‘New City’ to the bottom of the table. When I do that, the table automatically expands which you can notice because I haven’t changed the design and so the colors change:
And if I go back to the Data Validation settings, my source has automatically been updated:
This is a really easy way to make your drop-down list automatically update without the need for any formulas.
If the table you are referencing isn’t on the same sheet as your drop-down list, then you will need to use the INDIRECT function reference it. For instance, if you have created a table called Table1 (which should contain just one column for your list) on a different sheet, you can reference it the following way:
This will allow you to reference the list even if it is on a different sheet.
Option 3: Using a formula to remove the blanks in your drop down list
If for whatever reason creating a table isn’t an option for you, you can still create a dynamic list using a formula. Here, I’ll go back to creating a named range. Except rather than selecting a fixed set of cells, I will rely on a single formula. First, I’ll go back to the Name Manger. I’ll create a new named range. The formula for this can look a bit complex so I will break it down into parts.
First, I’m going to use the OFFSET function. This is because it can allow me to specify a height and width, which is crucial to making this work. My data starts in cell A1, and that’s where my formula will begin:
=OFFSET(A1,0,0
A1 is my starting point and that is the first argument. The next two arguments are whether I want to offset and move to any adjacent rows or columns. Since I don’t, I leave those values as zeros. It is the next argument that is critical, as it relates to the height. Here is where I will use a COUNTA function. I want to count the number of nonblank values in my list. My formula looks as follows:
=COUNTA(A:A)
I will embed this within my previous formula:
=OFFSET(A1,0,0,COUNTA(A:A)
For the width, I will set the last argument to 1, since I don’t want to include any extra columns. Here is my completed formula:
You always want to used $ signs in named ranges so that they don’t move on you. Now that this is set up, I can use this NewRange as my Data Validation source. And just like with a table, whether the list gets bigger or smaller, my named range and the drop down list will automatically update.
However, what if your list contains some formulas that look blank but really aren’t? Formulas are a great example of cells that can look empty even if they aren’t. The COUNTA function will count these values and you could again be back to square one with additional blank values. One way you can get around this is by counting the cells that are blanks, and subtracting that from the total rows. The formula would look as follows:
=ROWS(A:A)-COUNTIF(A:A,””)
Using this, you should correctly arrive at the number of cells that contain text and that aren’t blank as a result of a formula You can then insert that formula in your named range, in place of the COUNTA formula:
As you can see, this method isn’t the easiest and that is why I left it for the end. However, there are multiple different ways you can create a drop down list in excel without blanks. But it’s important because by removing blanks, it will make your form or spreadsheet look more polished by not having blank values in them.
If you liked this post on how to create a drop down list in Excel without blanks, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
Charts can be great tools to help visualize data. And sometimes, you will want to combine different types of information in one place. That can be tricky because if the scales are different, information may not display the way you would like it to. If something is shown in percentages while another value is in thousands, it isn’t going to be helpful to show that all on the same axis. That is where having a secondary axis can help you show all of that information on just one visual.
Below, I’ll go over how to do that using data from the Bureau of Labor Statistics. I will plot the unemployment rate against the average hourly earnings.
Creating the chart
The first step involves putting all the data together. If you want to follow along, you can download my data file here. This is an excerpt of what my DATA tab looks like:
Next up, I’ll create a Bar Chart by clicking on the data set, selecting the Insert tab and then choosing the option for a clustered column. At first glance, the chart doesn’t look terribly easy to read:
Since the hourly earnings are always above 25, those bar charts aren’t terribly helpful as they make it more difficult for the unemployment rate numbers to stand out. One thing I can do to make this a bit easier to read is to change the chart types.
Use a combo chart and a secondary axis to help display the data more effectively
Before I add another axis, I will first change up the look of these charts by using a combination. Rather than using bar charts for both data series, I’ll use a line chart for the average hourly earnings. Since those values are higher than the unemployment rate, it will help separate the data.
To change the chart type, right-click on the chart and select Change Chart Type
Select Combo on the bottom and off to the right you will see the an area where you can choose the chart type you want for each data series:
By default, Excel has determined I probably want to use a line chart for the average hourly earnings, which is correct. However, I could change it to something else altogether. You will notice this is also where you can check off to use a secondary axis.
While the chart will work fine even without this option, you can see from the preview there is a big gap between the bar graph and the line chart. In the interest of minimizing white space, I will check off the secondary axis for the average hourly earnings. Once I do that and click OK, my chart looks as follows:
You can see the chart now tells a much different story and shows that in the early months of the pandemic, the average hourly earnings spiked. This could possibly be due to a combination of higher-paid earners being less impacted by layoffs and being able to work from home and at the same time, low-wage workers who weren’t laid off may have received bonus pay if they worked in some retail stores. Either way, it definitely shows a much different story than if I didn’t use the secondary axis.
The axis to the left is the primary axis and relates to the unemployment rate. The one on the right is the secondary one and is for the average hourly earnings. This is an important distinction to keep in mind as you can easily be confused if you are not sure which axis relates to which chart. But having the secondary axis makes a big difference to my chart. This is what it would have looked like if everything was just on a single axis:
As you can see, it’s not as easy to visualize the data because of that big gap between the two chart types and them sharing the same scale. As a result, the spike in average hourly earnings is less pronounced than when using a secondary axis.
If you have yet another data series, you can also decide whether to plot that on the primary or secondary axis as multiple charts can be plotted on a single axis. However, if neither one is a good fit then that may be a sign that it is time to consider making a separate chart altogether.
If you liked this post on how to add a secondary axis 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.
One way you can make a form in Excel more user-friendly is by adding checkboxes to it. There are a few different ways you can do this which I’ll cover and I will also show you how you might incorporate this into a sample pricing sheet.
What you need to do first before you can add checkboxes
Before you can access the necessary form controls, you first need to ensure that the Developer tab is enabled on Excel. If you already have it enabled, you can skip to the next section. If you don’t see it on the Ribbon, then you will need to first go into Excel Options (on Office 365, you’ll find this on the File tab and then at the bottom there will be an Options button; other versions will vary). Once there, you’ll want to select the Customize Ribbon button. Off on the right, you will see a checkbox for the Developer tab:
Once you click OK, you should see the Developer tab on your Ribbon. If you’ve left it at the default position, it should be right next to the View tab.
Next, let’s set up add some checkboxes in Excel!
Method 1: Using ActiveX Controls
On the Developer tab, you’ll see a section for Controls and by clicking the Insert button you’ll see two areas: one for Form Controls and the other for ActiveX. There are checkboxes for both. First, I’ll cover how to use the ActiveX checkbox.
Once you click on the checkbox, your mouse will turn into a cross and what you will need to do is drag and create a rectangle/box for your checkbox to go in:
To modify the checkbox, you will need to right-click on it and select Properties. There, you will have the following options:
There are a lot of fields here but the main ones to focus on for now include the Caption field and LinkedCell. The caption is what shows up next to the checkbox. To keep things clean, I suggest leaving this blank as that will allow you to make the size of the checkbox control smaller. It will be easier to fit it inside of a cell and prevent it from taking up much room. The LinkedCell value is which cell you want to update once you click on the checkbox. There, it will return a True/False value depending on if the checkbox has been ticked or not.
In my example, I have cleared the value of the caption and set my LinkedCell to B1 — the checkbox has been shrunk down to fix into cell A1.
But before you can interact with the checkbox, you need to get out of Design Mode. When you first start inserting the ActiveX control, Excel puts in you this mode so that you don’t accidentally trigger the control. This is also found on the Developer tab, and you will just need to click it so that it is unselected. This is what it looks like if you are in Design Mode:
And this is with it off:
Now that it is turned off, you can test your checkbox. Initially, it may show a greyed out image like this:
This is because your linked cell doesn’t contain a true or false value yet but that will change once you click it:
If you decide you want to change how your checkbox looks, you’ll need to go back into Design Mode, otherwise clicking it will just toggle it from checked to unchecked. If you want more checkboxes, just follow these steps (or copy the existing checkbox) and reference a different linked cell. This is important because if you just copy the checkbox without entering a new cell to link to, they will all link to the same one.
Next, let’s go over the other way to insert checkboxes.
Method 2: Using Form Controls
To create a checkbox using a form control, you will again go to the Developer tab except this time you will select the checkbox from the form controls section. The process will be similar in that your cursor will convert to a crosshair and you can expand the control to be as large or as small as you need it to be.
One main difference you’ll notice with the form controls — they don’t put you into design mode immediately. This means your checkbox is live right away. To set the properties, right-click on the control and click on Format Control. Here, you will want to go under the Control tab, where you will see the following:
The only thing you will need to worry about here is selecting the cell you want the checkbox to link to. You can type it in or click on the up arrow to select the cell. The 3-D shading option will make the control look more like the ActiveX checkbox, which looks sunken. Otherwise, the default form control checkbox looks as follows:
As for the caption that shows up next to the checkbox, if you want to change that, right-click on the checkbox and select Edit Text.
Overall, there isn’t a huge difference in which check box you decide to use. The form control may be preferable only because you don’t have to fumble around with the Design Mode. But either one can get the job done for you.
Using the checkboxes in a pricing template in Excel
Now that you know how you can create the controls, I’ll show you an example of how you might implement them in a spreadsheet. A good one is a pricing list that you might give a customer to determine which options they want to choose. Here’s an example of what that might look like:
The way the template works is by having the linked values hidden; I don’t want the user to see a series of true/false values. And then using IF statements, I can do a lookup on a pricing table to say that if something is checked off, the price gets pulled into the pricing sheet. Since you can’t alter the linked cell without impacting the checkbox itself, using another cell to bridge the gap helps bring everything together.
If you liked this post on how to add checkboxes 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.
There are a few different ways you can pull stock prices into Excel. You can use the new STOCKHISTORY function, pull data from Google Sheets which has a native stock function, or you can also use Power Query. In this post, Power Query is what I am going to focus on and show you how you can pull data right from Yahoo Finance. Of course, if you don’t want to do it yourself, I also have a template that is ready to use and download (at the bottom of the page).
Creating the query
For this example, let’s pull Apple’s stock history for the past month. To do this, we can simply go to the Yahoo Finance page that shows the stock’s recent price history, located here and select any interval, whether it is five days, one month, or three, it doesn’t matter.
To get the complete data set, I’m going to copy the actual CSV download link from that page, not simply the URL. That way, it is possible to pull a much wider range than the default of 100 days.
I’m going to use that link to set up the query. To create it, go into the Data tab, select the From Web button next to Get Data:
On the next page, you’ll be given a place to enter a URL, and this is where I am going to enter the download link from Yahoo Finance:
Click on OK and Power Query will connect to the web page. Next, you will see a preview of the data and if it looks okay, you can just click on the Load button:
Then, the data will load into your spreadsheet and it should look something like this:
If that’s all you need, you can stop here. The only downside is if you wanted to look at a different ticker or change the date range, you would need to get a new link, and update the query manually, which is not ideal at all. This can be automated and takes a little more effort but it can be done by adding some variables and making some tweaks to the query.
Setting up the variables
In Power Query, you can utilize named ranges. In this case, I’ll set them up for the ticker symbol, as well as the start and end dates. That way, I can pull up a stock’s history for a specific time frame. The three named ranges I’m going to create are called Ticker, StartDate, and EndDate which can be entered all in the same place:
For the dates to work on Yahoo Finance, they need to be converted to a timestamp. This is what that calculation looks like:
=(A1-DATE(1970,1,1))*60*60*24
Where A1 is the date. This is what the dates look like when converted into this format:
Those timestamps are needed for the Yahoo Finance URL to populate properly. These are the values that need to be tied to a named range.
Next, these ranges need to be coded into Power Query. To do this, click anywhere on the table that the query created, and you should now see a section for Query in the Ribbon and click on the Edit button:
That will launch the editor. From there, you will want to click on the Advanced Editor button:
Then, you’ll see how the query is coded:
You can see the source variable is where the URL goes. To insert a named range from the Excel document into this code, we need to use the following format:
To keep things simple, I kept the name of the variable the same as the named range within the Excel file. Here is what the editor looks like after adding in the variables for the ticker, start date, and end date:
The one thing that I still need to adjust is the source. This is a hardcoded URL and it needs to be more dynamic, utilizing the variables.
In this part, I’ll need to adjust the query carefully to ensure that it is generated correctly. I will put the ticker variable where the ticker should go, and put the start and end dates (in Unix format). This is an excerpt of how the updated source data looks like:
Note that for the start and end date named ranges, I included the = sign to ensure the variable is read as text.
Now that the source is changed, all you need to do is update the variables and click on the Refresh All button on the data tab, and the table will update based on what you have entered.
If you want to download my template, you can do so here.
If you liked this post on how to get stock quotes from Yahoo Finance using Power Query, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
Depending on what kind of data you are working with and how you need to present it, you may need to show numbers in thousands, millions, or billions. Below, I’ll show you how you can quickly and easily make those conversions. You’ll also learn how to add a letter behind each number to indicate either B for billions, M for millions, or k for thousands.
Converting between billions, millions, and thousands
If you are dealing with raw numbers, to convert millions into billions you only need to multiply them by 1,000. And you don’t need to convert one value at a time. To multiply an entire range, copy a cell that contains the number 1,000 (or whatever factor you want), select the range you want to multiply, right-click paste special and you will see the following options:
Selecting the Multiply option will multiply the cell against each one of the values in the range. If you wanted to reverse the calculation and convert billions into millions, then you would follow the same steps except instead of selecting Multiply, you would choose to Divide. You can use this for other operations as well, including addition and subtraction.
Another potential use you may have for this is if you have numbers that Excel is recognizing as text. Multiplying all of them by a factor of 1 could fix that. And multiplying by -1 would flip their signs if you needed to switch them from positive to negative, or vice versa.
However, in some cases, things can be a little more complicated and you need to do more than just multiplication. When you are looking at stocks and trading volumes, for example, you may see abbreviations such as B or M. Here’s a look at some of the best-performing stocks from March 10 and their trading volumes, as per Yahoo! Finance:
While most of them contain M for million, some of the numbers are in thousands. Simply getting rid of the M wouldn’t fix this problem as then the numbers in millions would appear smaller than those that are in thousands. To fix these values, we’ll need to do two things:
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.
Now I can copy this formula down across my data set, and this is how it looks:
The numbers that were in millions simply lost the ‘M’ at the end of their values. And those that were in thousands now are in decimals, indicating how much of 1 million they are. For 342,271, it now shows 0.342271.
This is a complex example where you are dealing with text and the important thing to remember is that once there are letters involved in a number, the value automatically becomes text. If you want to apply some sort of calculation, it is going to be necessary to convert it back to a number — after you have gotten rid of any letters.
How to show numbers with B, M, or k
Next up, let’s take a look at how you can add letters to an existing number. Essentially, I am going to undo what I did above. Let’s start with turning our decimals into thousands. To do this, I can look for if a value if less than 1. If it is, then I will multiply it by 1,000 and add the letter ‘k’ to the end of it. Here’s how that formula will look:
=IF(A1<1,A1*1000&”k”
My value of 0.342271 becomes 342.271k. However, if I don’t want the decimal places and I want to round, I can adjust my formula accordingly:
=IF(A1<1,ROUND(A1*1000,0)&”k”
Using the ROUND function and setting it to 0 decimal places, I round up and now my value shows as 342k.
Next, I’ll need to add an “M” if the number is in millions. If any of the numbers were in billions, what I could do is check if a number is 1,000 or greater (e.g. 1,000 million). But since I don’t have billions in this data set, I can just simply add an “M” on to everything that is not in the thousands:
=IF(A1<1,ROUND(A1*1000,0)&”k”,A1&”M”)
This is what my values look like after this latest conversion:
For argument’s sake, I’ll change the first value so that it is 1,536 and show you how I would adjust for this calculation if that were $1 billion. As mentioned above, I would check if the value was more than 1,000. And if it is, I will divide it by 1,000 and add a “B” to the end of it. My formula, accounting for millions, billions, and thousands, will look like this:
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.
Is your spreadsheet running slow or constantly freezing and crashing on you? There are many ways you can make it quicker and more efficient. Below, I’ll cover seven different ways you can fix some of the more common reasons your file may not running well.
1. Turn off calculations for the workbook
The first thing you can do is to turn off calculations. If you don’t need your data to constantly be updating, then the easiest way to ease the load of your spreadsheet is to make sure those formulas don’t keep calculating. To do this, you can go to the Formulas tab and under the Calculation group, select Calculation Options and choose Manual:
You can always turn the formulas back on and use F9 to do a force calculation. But if you don’t want to re-calculate everything, a quick trick you can do is to use Find and Replace and replace the equals sign (“=”) with equals. It seems redundant but doing this will refresh your formulas and recalculate the range you selected. This can be an easier alternative to enabling calculations for everything and then having to wait for every calculation to update.
The danger with turning off calculations is that you could end up looking at data that hasn’t been updated and potentially incorrect values as a result of that. This is why you’ll always want to be careful when turning off calculations for an entire workbook.
2. Turn off calculations for individual worksheets
If you don’t want to turn off everything, you can turn off the calculations for specific worksheets. Although there isn’t a way from the calculation options to specify which sheets to turn off, you can do this through a macro.
To do this, go into visual basic (ALT+F11) and go into the ThisWorkbook object:
We’ll want to turn off calculations when the workbook is first opened so you don’t have to remember to do it later. The initial subprocedure looks as follows:
Private Sub Workbook_Open()
End Sub
To turn off a calculation, you just need a single line of code:
Worksheets(“Sheet1”).EnableCalculation = False
Then entire subprocedure look as follows:
Private Sub Workbook_Open()
Worksheets(“Sheet1”).EnableCalculation = False
End Sub
Where Sheet1 is the name of the sheet that you want to turn calculations off for. And if you want to turn them back on, you change the value from False to True.
This isn’t the easiest option, especially if you aren’t familiar with macros or don’t want to worry about coding anything. But with VBA you can turn off the calculations and then use a button to turn them back on.
3. Separate the data into multiple tabs
If you have a data set that includes multiple years worth of data, you may want to consider breaking it up. Have one tab for the current year, one for the previous year, and so on.
Using the INDIRECT function, you can refer to different worksheets in your file. By naming a header the same name as a tab (e.g. ‘2020’), you can make your formulas dynamic and pointing to a different worksheet as opposed to one very large tab. Looking up 10,000 rows in one tab versus 50,000 rows in a massive collection of all your data can significantly improve the time it takes for your calculations to run.
4. Separate data into multiple files
A more drastic move than breaking up data into different tabs is moving them onto completely different files. If you’re carrying lots of old data into one big spreadsheet, consider archiving some of it and saving it in another file. Unless you really need access to the old data all the time, it might make a lot of sense to break up your files and to keep your current version as lean as possible. You could even have your data on one file and a separate file for your reporting while using PowerQuery to create a snapshot of your data and simply refreshing it as you need it.
5. Use COUNTA and INDIRECT to limit the scope of your calculations
The INDIRECT function can also help you to ensure your formulas don’t include too many rows or columns. If you select an entire column to run your calculations on rather than just the first couple hundred rows that actually have data in them is another way you can slow down your spreadsheet.
To do this, you can use the COUNTA function on a range to determine how many cells have values in them. The formula is a simple one that you can run on an entire column:
=COUNTA(“A:A”)
Then, using the INDIRECT function, you can write a formula that does something like this:
=SUM(INDIRECT(“A1:A”&B1))
B1 in the example above contains the COUNTA formula and will give you the total number of rows that are used. Rather than doing this:
=SUM(A:A)
and selecting everything in column A to sum up, you can shrink your range down using a combination of the INDIRECT and the COUNTA function. This can be a time-saver when you’re dealing with complex calculations, especially if there are thousands of rows of data.
6. Minimize formulas and utilize pivot tables where possible
Another way to cut down on resources is to make use of pivot tables. Pivot Tables are more efficient than formulas and using them can help make your spreadsheet run smoother. The one drawback is that they’re not as flexible as formulas are. Refreshing the data also takes seconds and you don’t have to worry about turning calculations off.
7. Make sure your worksheets aren’t too big
Large files can take long to open and they freeze up a lot. A common problem I see is that worksheets are sometimes thousands of rows long even though there’s nothing there. This can happen if you download a large data set into a sheet and clear it out later. And although those rows may no longer be occupied with data, they’re still technically taking up space. This can add several MB onto a file and make it a lot more difficult to run.
How can you spot this issue? An easy way is to cycle through the tabs and click CTRL+END. You’ll be taken to the last cell in your sheet. If that takes you far beyond your last row or column of data, then that tells you your sheet is using up more data than it needs to. What you can do is delete all the cells in between the last cell (using CTRL+END) and the last cell that actually contains information in it. Then, save the file. You should see your file come down in size. Do this for each worksheet that has this issue. Even though the cells appear to be empty, they can be making your file unnecessarily large.
If you liked this post on 7 ways to fix a slow spreadsheet 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.
