Conditional formatting in Excel allows you to automatically format and highlight cells based on their values. You may want to apply custom rules to values that are too high or too low. You may also want to use conditional formatting for budgeting purposes, to show when something is overbudget. Users typically use colors when applying custom formatting. A cell with a high value might be highlighted red versus a lighter color when it is low.
What you can also do is add symbols, including flags, to your conditional formatting rules. This can add another element to make your conditional formatting stand out even more.
Creating conditional formatting rules
In the following example, I have some expense categories, budgeted amounts, actuals, and a field to show when an expense has run overbudget.
To create conditional formatting rules for this table, you’ll first need to select the cells you want to apply the formatting to. In this case, I’m going to select the Overbudget field and select all the values there. Next, I’ll select the Conditional Formatting button on the Home Screen and click on the option to create a New Rule:
On the next screen, there are many different options for creating rules:
I’m going to select the following option: Format only cells that contain. This allows me to specify a criteria, and then apply formatting to any cells that meet that criteria. Since I’m interested in values that are overbudget, I can create a rule for when the cell value is greater than 0:
Next, I’ll click on the Format button to determine what I want the cells that meet the criteria to look like. If I set the highlighting color to be red and the text to be white, here’s what my table will look like:
It’s a simple and effective way to alert your eyes to amounts that show a category is overbudget. But you don’t have to be limited to just changing colors.
Adding symbols to your conditional formatting
Instead of changing cell and text colors, I’ll simply add a red flag next to an amount when it is overbudget. First, I’ll clear off the conditional formatting rules I’ve already created. You can remove rules one by one or you can just delete all of them. To do that, go to the Conditional Formatting button and this time select the option to Clear Rules and to Clear Rules From Entire Sheet.
Now the conditional formatting is gone and I can start over. But before I do that, I need to find the symbol that I want to use in the custom formatting. If you go to the Insert tab, off to the right there is an option for Symbols.
By clicking on that, you’ll see many different symbols you can insert into your spreadsheet. If you scroll around you can find symbols that look like flags, which is what I’m going to use.
The one that I have highlighted above is for a Black Flag. I can click on Insert to put it into my spreadsheet.
To get this symbol into my custom format, I first need to copy it. To do that, double-click on the cell so that you are editing it, and then select the flag, and then press CTRL+C to copy. While it’s copied and in the clipboard, now is the time to setup the conditional formatting rules. The process is the same as before.
Except this time, when it comes to applying the custom formatting, go to the Number section, and select Custom. Then, enter a value of 0 and then paste the flag symbol. If you want to also highlight everything in red, you can add [Red] in front. Here’s what that custom number format could look like:
After applying the rule, now the table looks like this, with the custom formatting:
This is a bit of a cleaner format that you can use rather than having to highlight the entire cells. You can use this approach for other symbols in Excel. The key is just to find the symbol you want to use and then copy and paste it into your custom format.
If you liked this post on How to Add Flags to Conditional Formatting Rules, 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 me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.
Duplicate and unique values can be difficult to find in a large data set. In this post, I’ll show you how you can find and highlight duplicate values, as well as how to extract unique values, in Google Sheets. In this example, I’m going to use a list that shows historical World Cup results, including the winners of past years.
Highlighting and finding duplicate values
There is a built-in function in Google Sheets that allows you to filter out unique values. Under the Data menu, there is a section for Data cleanup where you can select the option to Remove duplicates.
However, by doing this, you will actually remove duplicates. And if you don’t want to remove data, this could lead to unintended results. If you simply want to find and highlight duplicate values, you’re better off using conditional formatting.
In this data set, I’m going to highlight the duplicate values in the champion field to identify repeat winners. To do this, I can create a conditional formatting rule in Google Sheets to apply formatting when criteria is met. My criteria will be to look at whether a value shows up more than once within a list. The formula utilizes the COUNTIF function:
=COUNTIF(B:B,B1)>1
This formula needs to be added when creating a conditional formatting rule. To set that up, I’ll select the entire column and under that Format menu, click on the option for Conditional formatting. In that section, there will be an option to Add another rule. And under the drop down for Format cells if…, I select the option that says Custom formula is. And in that box, I’ll enter in the above formula:
I’ll leave the default highlighting options, and now it will highlight all the values that show up more than once in column B:
As you can see, there are many repeat winners in this list. If I only wanted to see the winners that only won once, then I would adjust the formula so that it looks for a value of equal to one, as opposed to more than one.
=COUNTIF(B:B,B1)=1
By altering the formula, it will highlight only the values that show up once:
You could also go further and make even more specific conditional rules, such as highlighting countries that have won two or more times. Through conditional formatting, you can make your highlight rules as specific as you need them to be.
Extracting and counting unique values
If instead of getting the duplicates you wanted to just get a list of unique values, that’s an even easier process in Google Sheets. Using the UNIQUE function, all you need to do is select your range, and Google Sheets will give you a list of the unique values:
=UNIQUE(B2:B22)
This formula results in the following list:
There have only been eight countries that have won the World Cup heading into 2022. But suppose you only wanted to count the number of unique winners. For this, you can use the COUNTUNIQUE function, which takes the same range as the argument:
=COUNTUNIQUE(B2:B22)
The above formula returns a value of 8, which is the same if I were to count the number of values from the Unique formula. There’s also the COUNTUNIQUEIFS function that you can deploy which allows you to also apply an IF statement to the CountUnique function. Suppose I wanted to count the number of unique winners after 1980, that formula would be as follows:
=COUNTUNIQUEIFS(B2:B22,A2:A22,">1980")
Column A contains the year and this returns a value of 6, excluding the two countries that only won prior to 1980: England and Uruguay. Using this function, you can apply multiple criteria if you need to.
If you liked this post on How to Find Duplicates and Unique Values in Google Sheets, 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.
Conditional formatting cells can be an effective way to highlight values so that they can easily stand out. You can apply similar logic to charts, and in this post, I’ll show you how you can use conditional formatting with Excel charts. By doing so, you can highlight gaps and key numbers.
Create more than one series to categorize your results
Excel’s conditional formatting isn’t designed to work on charts. But one way you can still achieve the same results is by categorizing results, and creating a series for each category. Here’s an example, using Amazon’s sales growth. Below are the year-over-year growth rates it has achieved over the past 12 quarters:
Charting the data out would show the highs and lows effectively:
However, suppose you wanted to highlight the high-growth periods (30% or more), with the more moderate ones (15%), and the quarters which were below that. To do that, I’m going to add a few more columns and use IF statements to populate the columns based on the growth rate.
Now, if I populate these values on a chart, they shows up like this:
These column charts are skinnier and that’s because they are taking up more space as there are three different series for each quarter. To get around this, I can just change the charts so that they are stacked. Since only one of these columns will ever contain a value, there’s no danger they will actually ever stack. But by changing the chart type, they won’t take up as much space.
The advantage of this approach is that you don’t even need to rely on the axis to determine what range the growth rate falls within. Although you have to create additional columns by doing this, you can hide any columns that you don’t need to see. You can apply this type of logic to other types of charts as well.
If you like this post on How to Apply Conditional Formatting to a Chart 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.
Have you ever wondered how a stock has typically performed month over month? Using a spreadsheet, you can calculate monthly returns and identify patterns of which months are traditionally strong for a stock, and which ones aren’t. In this example, I’m going to use Google Sheets to pull in stock prices and calculate historical stock returns by month.
Start with pulling in historical stock prices
To get started, I’ll need to extract a stock’s price history. This can be done using Google Sheets’ GOOGLEFINANCE function. The key is in setting a start date that goes far enough back to ensure you get enough historical data to use in your calculations. A good function to use within that is the TODAY() function which ensures you will always be counting backward from today’s date and don’t need to hardcode a date. If I want to go back to 2008, for example, I can set my formula to deduct about 5,000 days.
To pull Amazon’s stock price going back that far, this is what my formula would look like in Google Sheets:
The one problem here is that the date values contain the time, 16:00:00, which represents the 4 pm closing time of the stock market. I only want the actual date since I’m going to be doing a lookup and don’t want to include time. To extract just the date, I can use the DATE() function and use the YEAR(), MONTH() and DAY() functions to refer back to the values in column A. For example:
=DATE(YEAR(A2),MONTH(A2),DAY(A2))
The above formula would give me the date in column A without the time. I’ll add an IF statement at the start so that in case the value in column A is blank, my formula simply won’t compute anything:
=if(A2="","",DATE(YEAR(A2),MONTH(A2),DAY(A2)))
Now I have a table that has just date values without any time:
Creating a date matrix
Next, what I’m going to do is create a matrix that has years going vertically and months going across:
I’m going to fill in these values with the stock’s returns in each of those months. The key to making this work is by using the DATEVALUE() function which allows me to enter a date. For example, if I entered the following formula:
=DATEVALUE("Jan 1, 2022")
It would result in the following output:
1/1/2022
In the first cell of my matrix, for the JAN 2021, I’ll combine the month abbreviation (JAN) with the year (2021) and the first day (1). Here’s how that would work if the month name is in cell F1 and the year is in E2:
=DATEVALUE(F$1&" 1, "&$E2)
However, let’s assume I don’t want to pull the first day of the month and instead want to pull the ending month’s value. For that, I can use the EOMONTH() function. And then I would enclose the current formula within that:
=EOMONTH(DATEVALUE(F$1&" 1, "&$E2),0)
The 0 value at the end indicates how many months I want to jump by. And since I just want the end of the current month, I don’t need to jump by any months, which is why I set it to 0. At this point, I have a date, and now I can use this inside of a MATCH() function to find the row that matches this date. Assuming the date values in are column C, here is the formula:
Now the formula will pull in the price for a given month. But if I want the month-over-month return, I need to take the month-end price and divide it by the previous month’s ending value. To get the previous month’s price, I use the same formula except instead of a 0, I’ll enter -1 for the number of months:
I’ll combine the two formulas now, taking the current month-end price and dividing it by the previous month’s value and also deduct -1 at the end to adjust for it being a percent-change calculation:
Now, copying this formula across the entire matrix, I can see the stock’s historical returns by month. I’ve added some conditional formatting to contrast the good months from the bad ones:
Besides relying on colors, I can also add a win rate % where I can count the times where the return was more than 0% (i.e. a ‘win’) and divide this by the total number of values. In column F, for January, the formula looks like this:
=COUNTIF(F2:F12,">0")/COUNTA(F2:F12)
I’ll also average the returns so that it’s easier to see the best and worst-performing months:
Judging from this, April looks to be the best time to own Amazon’s stock. It normally returns a positive return and its average over the past 11 years has been a gain of just under 8.5%. To re-create this analysis for other stocks, simply change the ticker symbol.
If you liked this post on How to Calculate Historical Stock Returns by Month, 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.
Want to create a dashboard to track the stock market and the latest business-related news? Below, I’ll show you how you can create a stock market dashboard using Excel and Google Sheets to pull in all the data you’ll need. If you’d prefer to just download the file, you can do so here.
Step 1: Compiling the data
You can get stock prices into Excel using the STOCKHISTORY function. However, that isn’t available on older versions of Excel and it also doesn’t pull in the current day’s prices. Using Google Sheets can be more effective for this purpose. Plus, on there, I can pull in business-related news as well.
To start, I’m going to pull in values for the Dow Jones, Nasdaq, and S&P 500. I’ll also download the values of a couple of exchange-traded funds (ETFs) that track healthcare and tech stocks. To get the latest price, you can use the built-in GOOGLEFINANCE function that’s only available on Google Sheets. To get the latest value of the Dow Jones, the following formula will work:
=GOOGLEFINANCE(“.DJI”,”price”)
And to calculate the percentage change:
=GOOGLEFINANCE(“.DJI”,”changepct”)/100
For the Nasdaq, you’ll use “.IXIC” and for the S&P 500 the ticker is “.INX”
For the ETFs, since they aren’t indexes, there is no period beforehand and I reference XLK for tech and XLV for healthcare. In my Google Sheets file, I have a simple layout for the values and their changes that I will later pull into Power Query:
Next, I’ll also download the latest business-related news. Google Sheets has another unique function for this: IMPORTFEED. All you need to do is find an rss feed from a website that you want to pull information from. Not every website has an rss feed but what you can do is just do a Google search for the name of a source and ‘rss’ to see if you can find a link. There are three sources I’m going to use for this dashboard:
In Google Sheets, the top articles from each of those rss feeds will show up, including the title, URL, date created, and even a brief summary:
Now, it’s time to pull all this data into Excel.
Step 2: Loading the data into Excel using Power Query
To import data from Google Sheets into Excel, you need to first share the sheet. While in Google Sheets, go into File -> Share -> Publish to web. Then, you’ll be prompted to select what you want to share. I’ll start with the Markets tab I created and then the News tab:
Copy this URL as you’ll need it to load the data into Power Query. While you’re back in Excel, go under the Data tab and click on the From Web button under the Get & Transform Data section. You’ll be prompted to enter a URL. This is where you’ll paste the link that you copied from Google Sheets:
On the next page, select Table 0 as where you want to extract data from. And if you want to do some cleanup (getting rid of extra columns), you can do so by clicking on the Transform Data button:
To remove any unneeded columns in Power Query, just right-click on a column header and click Remove:
Once you’re done, click on the button to Close & Load if you want the data to be loaded on a new sheet. If you want to control where it gets pasted, then use the drop down and select Close & Load To.
Repeat these steps for the other Google Sheets tab.
In addition, I’m also going to load data from a few other sources:
Top 100 Gainers on Yahoo Finance: https://finance.yahoo.com/gainers/?offset=0&count=100
Top 100 Losers on Yahoo Finance: https://finance.yahoo.com/losers?offset=0&count=100
Upcoming IPOs from IPOScoop: https://www.iposcoop.com/ipo-calendar/
The process for importing these links into the dashboard is the same as for Google Sheets. Go through Power Query, import from web, and paste in the URL plus make any formatting changes necessary. The next step involves putting all this data together in a dashboard.
Step 3: Creating the dashboard
In my spreadsheet, I’ve created two tabs: one that hold all my Power Query downloads (the ‘Data’ tab) and a ‘Dashboard’ tab for where all the information will be displayed.
To make the set up of the dashboard easy to manage, I’m going to change the column width to 10 for everything. To do that, press CTRL+A to select all the cells on the Dashboard tab, then right-click on any of the headers, and there you’ll be able to select column width.
First up, I’m going to get the indexes and market indicators as a starting point. To do this, all I need to do is link to the values and the percentages for the S&P 500, Dow Jones, Nasdaq, Tech, and Healthcare tickers I imported from Google Sheets. By default, I’ll set the formatting for all the cells to be green:
To make this more dynamic, I will add some conditional formatting so that if the percentage change is negative, the corresponding cells will highlight in red. For this, I can select all the cells in green above and create a conditional formatting rule the starts with where the first percentage is (in my spreadsheet, it is cell E6):
=E$6<0
This is a simple rule but by not freezing the column (E) and freezing only the row (6), it can be applied to all the cells above. I can apply a red background color so that if any of the percentages are negative, the cells will highlight accordingly:
For the next part of the dashboard, I will copy over the news stories that were also downloaded from Google Sheets. This time, I’m going to use the HYPERLINK function so that I can not just link to the title but also create a clickable link that will allow me to open the story should I want to open it in my default browser. The function itself is simple and involves just two arguments, one for the actual URL and another for what the text should show up. Since it’s shorter, I’m going with the title. After applying some formatting and copying all three sources, this is what my dashboard looks like:
For the last part of the dashboard, I’m going to pull in the tables from the other data sources (top 100 gainers, losers, and upcoming IPOs). If these are on the Data tab, you can just cut and paste them onto the Dashboard tab. And for each one of the tables, I’m going to create a chart based on the symbol and the percent change.
To do this, select the Symbol column and the % Change columns. Then under the Insert tab in Excel, open up the charts and select Treemap. If you selected too many columns or didn’t specify which ones you wanted, you might get a different look. But if you only selected those two, you should see something like this:
Since the chart includes the symbols, the legend can be deleted. Also, I’m going to change the color scheme so that it goes from dark green to light green. This change can be made by clicking the Change Colors button next to the chart:
To add the percentage to each of the boxes, right-click on one of the ticker symbols and click Format Labels. Then, check off the box for value so that the percentages will also show up next to the symbols:
These steps can be repeated for the other charts. However, for the losers table, since the percentage change is negative, it needs to be flipped to positive first. To do that, that query needs to be edited. If you click on Queries & Connections section under the Data tab, you’ll see a list of all your queries. Click on the one that takes you to the top losers query. Right-click edit and Power Query will open up.
Once in Power Query, select the % Change column and under the Transform column at the top, click on the Standard drop down, which will show you all the different calculations you can apply:
Click on Multiply and then for the value in the next box, enter -1. Pressing OK will then flip all the values to negatives.
Now, you can create the same Treemap chart for this table. For the IPOScoop download, the field I’m going to use is Est. $ Volume. This query will also need to be edited in order to use that field since it is text. Although it is a bit more complex since this field contains text and dollar signs, there’s a relatively easy way to parse out what you need.
In Power Query, select the column, and under the Add Column tab, click on the Column From Examples button (choose the option for From Selection):
That will create a new column:
In Column1, I can enter the value that I want Power Query to extract. If I just enter a few values to show what I want (in this case, I only need to enter 300), Power Query fills in the rest, figuring out what I am trying to do. It’s an easy way to parse data in Power Query.
After creating the new column, I can change the format from text to currency by clicking on the ‘abc’ letters in the title:
Now that I have the column created, I can remove the original one and load the data back into Excel and proceed with making a Treemap for this chart using the symbol and the newly created column.
The last thing I’m going to do is create a new column to show the change in volume to determine how much more (or less) trading there was for each stock on the day compared to the average. This will compare the average three-month volume with the current day’s volume. The one complication is that some of the values contain letters:
To convert these values, it’s important to first parse out the letters. If a value doesn’t contain a letter, then it is in thousands. I’m going to set everything to millions. So if the value doesn’t contain a letter, it will be multiplied by 0.000001 to convert it into a fraction of a million. And if it contains a ‘B’, it will multiply by a factor of 1,000. Otherwise, the value will remain as is. Here’s how the first part of the formula will look like, which involves determining the multiplication factor:
Since the letter is always at the end of the string, just using the RIGHT function (which looks at the right-most string) will suffice. This result needs to be multiplied by the remaining value. That value can be extracted by using the SUBSTITUTE function which will replace one value with another:
SUBSTITUTE([@Volume],”B”,””)
In the above formula, the value of B will be replaced with an empty string. This is the same as simply removing the value. To ensure that any ‘M’s are also removed, I will embed this formula within another one that will substitute out those values:
SUBSTITUTE(SUBSTITUTE([@Volume],”B”,””),”M”,””)
I multiply this by the first part of the formula, and my numerator is as follows:
For the denominator, I’m going to use the exact same formula, except instead of the current volume, I’m going to use the field for the three-month average:
The -1 at the end is to put the change in a percentage of less than 100%.
Another step you might consider at this point to help identify these changes is to format these numbers so they are easier to read. You can use conditional formatting (color scales) to easily highlight the highs and lows. And if you want to format the percentages so that they show commas and negative percentages show up red, use the following in the custom number format:
#,##0%;[Red]#,##0%
The semi-colon before the [Red] separates out what the percentages should look like when they are positive (the part before the semi-colon) and what they should like when negative (the part that comes afterward). The [Red] text indicates the value should be in red text.
Here’s how this section looks as part of my dashboard:
And here’s a snapshot of the dashboard as a whole.
One thing to remember: if you want to update the queries and the dashboard, make sure you go under the Data tab and click the Refresh All button. Otherwise, your data may not be up to date.
Also, to prevent your tables from stretching out when updating the queries, select each one of them and under the Table Design tab, click the Properties button (under the External Table Data section), where you should see this:
Make sure the Adjust column width checkbox is unticked. This will prevent your columns from stretching out and disrupting your layout.
If you liked this post on Creating a Stock Market Dashboard 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 many different apps to choose from if you want to create a checklist. But if you’re doing Excel work and have tasks associated with it, it may be easier to just include the checklist right within your spreadsheet. In this post, I’ll show you how you can make a checklist in Excel quickly and easily that you can re-use in many spreadsheets.
Step 1: Creating your list
Excel is an easy place to create a list since a spreadsheet is already in a grid format. You can use either numbers or letters as prefixes, or without anything at all:
Step 2: Add checkboxes
In order for this to look like a task list, we should add some checkboxes. If you don’t have the Developer tab enabled in Excel, make sure to do so. Under Excel Options, you’ll have an option to customize the Ribbon. This is where you can select which tabs you want to have enabled:
Once enabled, go to the Developer tab and click on the Insert button. Select the checkbox icon that is under the Form Controls section:
Then, use the mouse to drag and create a checkbox. It will automatically create some generic text to say ‘Check Box 1’ — you can remove this as it is unnecessary. Once you’ve got the checkbox in the position you want (and within its own cell), copy the entire cell and paste it over so that you have a checkbox next to each task:
Each checkbox can be linked to a specific cell. And every time you click the checkbox, the value of that cell will toggle between TRUE and FALSE, to indicate if the box is ticked or not. To create a link, right-click on a checkbox and select Format Control. Then, under the Control section, select a cell in the Cell Link section:
Then, when the checkbox is ticked or unticked, here’s how the values in the will appear in the linked cell:
The danger with copying these checkboxes after you have linked a cell, is that those cell links won’t change; multiple checkboxes will be linked to the same cell:
To correct this, you will need to modify the cell link for each checkbox. Once that’s done, it’s time to move on to the last step.
Step 3: Add conditional formatting
Right now, ticking the checkbox doesn’t do anything but show a TRUE or FALSE value. In this step, I’m going to add some conditional formatting to also cross out the item. To do this, I’m going to highlight the column that has the tasks (column B) and create some conditional formatting rules:
I’m going to create a rule that looks at the column that contains the cell link values (column C). It will check if the value is set to TRUE using the following formula:
=C1=TRUE
Then, under the Format options, I will apply a strikethrough effect:
Now, when a checkbox is ticked, the text will have a strikethrough effect:
The TRUE/FALSE values can be hidden since they don’t need to be visible in order for the checkboxes and strikethrough effects to work. The only other changes you may want to make at this point relate to formatting. This includes applying a header. Here’s what your finished checklist might look like with some additional formatting:
If you liked this post on How to Make a Checklist 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.
If you have values that you are plotting on a chart and some of them are zeroes, you will likely notice your chart sliding all the way down to the bottom. It can be problematic when you are entering in year-to-date data which will inevitably lead to blank or zero values. That’s why in this post, I’ll show you how to hide zero values from showing up on a chart in Excel.
Let’s start with the following example:
In this case, we have values for just a few months. From April through to December, the values aren’t necessarily zero — we just don’t have data yet. But the problem is that on the chart, the line graph shows them as being zeroes. If we were to get rid of those zero values, it would fix the issue:
But the problem is that this may not be a convenient solution. If you want to create formulas to calculate the totals for each month, going back and deleting the ones with no values and remembering to put the formulas back in for future months isn’t going to be a very convenient option. There is a way that the calculations can be adjusted so that you can still get the zero values not to show.
Let’s suppose you have a SUMIF calculation for each month which looks as follows:
One way to fix this issue is to add an IF statement to avoid the zero values. But returning a blank value won’t fix the issue. Instead, what we’ll want to do is return an #N/A value. To do that, you just need to use the following formula:
=NA()
That just needs to be incorporated into the formula to say that if the sum is equal to 0, an NA value is returned:
=IF(SUMIF(E:E,A3,F:F)=0,NA(),SUMIF(E:E,A3,F:F))
Although this solves the problem, it creates a bit of an eyesore with the #N/A values showing up in our data set. If you want to get rid of that, there’s a solution for that as well. Using conditional formatting, we can adjust the values so that any #N/A values show up blank. To do this, I’ll select column B and under the Conditional Formatting in the Home tab, select the option for a New Rule:
Select the option to Use a formula to determine which cells to format and enter the following:
=ISNA(B1)
I need to use B1 since that is the start of the range that I have selected. Next, you can just click on the Format button and set the font color to white so that the #N/A values don’t show up. This is what my sheet and chart looks like after applying the formatting:
If you liked this post on How to Hide Zero Values on an Excel Chart, 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.
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:
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:
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:
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:
Another thing I can do is add conditional formatting. Color scales can be really helpful here, such as these ones:
Now it’s even easier to see the progression and how it relates from one dividend yield to the next:
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:
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.
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.
