conditional formatting Archives

How to Calculate the PEG Ratio in Excel

February 13, 2024

by howtoexcel conditional formatting formatting investing stocks

What is the PEG Ratio?

The Price/Earnings to Growth (PEG) Ratio is a metric that enhances the traditional price-to-earnings (P/E) ratio by incorporating the company’s earnings growth rate into the calculation. This ratio is calculated by dividing the P/E ratio by the annual earnings per share (EPS) growth rate.

This calculation provides a more nuanced view of a stock’s valuation by factoring in future earnings growth, offering a more comprehensive perspective compared to the P/E ratio alone, which only considers the current price relative to earnings.

Why Investors Find the PEG Ratio Useful

Investors use the PEG ratio for several reasons. It allows for a more balanced comparison between companies with differing growth rates. A high P/E ratio might suggest a stock is overvalued, but when accounting for strong anticipated growth (as the PEG ratio does), the stock might actually be undervalued. This makes the PEG ratio a favored tool for identifying stocks that might offer a better return on investment, particularly when looking for good growth stocks.

The PEG ratio also aids in evaluating the potential overvaluation or undervaluation of a stock in relation to its growth prospects. A PEG ratio below 1 is often interpreted as a stock being undervalued given its earnings growth, whereas a ratio above 1 might indicate overvaluation. This simple benchmark can guide investors in making more informed decisions.

What is the Formula to Calculate the PEG Ratio?

The PEG ratio includes two components: the stock’s P/E ratio and the annual EPS growth rate. This is what the formula looks like:

Creating a template in Excel to calculate the PEG Ratio

Calculating the PEG ratio in Excel is straightforward, allowing investors to efficiently assess multiple stocks’ growth prospects against their valuations. Here’s a step-by-step guide to setup a worksheet to help you do this:

Input Data: Begin by entering the necessary data into Excel. You’ll need the current stock price, EPS, and the annual EPS growth rate. Ideally, you’ll want to setup the inputs first, followed by the formulas at the bottom. This will make it easier to enter the data in logical steps: first the ticker, the stock price, the EPS, and then the annual EPS growth.
- You can get the stock price into excel using the STOCKHISTORY function.
- You can get the EPS from most financial websites. If you’re doing this on Google Sheets, you can directly pull the P/E value as well as the stock price using the GOOGLEFINANCE function.
- For the annual EPS growth rate, you can pull this from a site such as Yahoo Finance (it’s under the ‘Analysis’ section). The percentage used is based on the next 5 years. Although it is percentage, enter it as a number (i.e. 100% would be 100).
Calculate P/E Ratio: In the first calculation cell, I’ll calculate the P/E ratio by dividing the stock price by the EPS.
Calculate PEG Ratio: The next calculation cell is the PEG ratio. This is calculated by taking the P/E ratio and dividing it by the annual EPS growth rate. If a stock is expected to grow at a 50% growth rate, the value should be 50, not 0.5 (i.e. don’t enter it as a percentage). Otherwise, this won’t calculate correctly.
Conditional Formatting. This is an optional step, but one which can help with your analysis. Use conditional formatting rules to highlight the PEG ratio based on its value. If it is less than 1, I’ll apply a green highlighting, a red highlight if it is more than 3, and yellow for anything in-between. Here is how you might set that up with an icon set

Creating conditional formatting rules using icon sets.

Here is how the template looks based on their stock prices and data as of Feb. 1, 2024:

In the above example, we have a fast-growing stock in NVDA, a moderate-growing stock in AAPL, and a slower-growing one in KO. Essentially what we are doing here is looking if the EPS growth rate is higher than the P/E ratio. If it is, that suggests it is not an expensive buy. NVDA, for example, is expected to more than double each year for the next five years, as is evident by its 102% EPS growth rate. While that would make it look like a cheap buy, you’re also assuming that it really can achieve that kind of a growth rate, which would be no easy feat. That leads us to an important part section: the limitations of this calculation.

Limitations of the PEG Ratio

While the PEG ratio offers valuable insights into a stock’s potential value by incorporating growth into the valuation equation, it’s important to recognize its limitations. Understanding these constraints can help investors use the PEG ratio more effectively alongside other analysis tools.

Growth Rate Estimations: The PEG ratio is heavily dependent on the accuracy of the earnings growth rate projections. These forecasts can be highly speculative and vary widely among analysts. Overly optimistic or pessimistic growth estimates can skew the PEG ratio, leading to potentially misleading conclusions about a stock’s valuation.
Historical Growth vs. Future Potential: The PEG ratio typically uses historical data to predict future growth, but past performance is not always a reliable indicator of future results. Companies in rapidly changing industries or facing new competitors may not sustain their previous growth rates.
One-Size-Fits-All Approach: The simplicity of the PEG ratio, while a strength, can also be a drawback. It does not account for the nuances of different industries or the specific risks and opportunities facing individual companies. A low PEG ratio does not guarantee success, nor does a high PEG ratio always indicate a bad investment.
Dividend Exclusion: The PEG ratio does not consider dividend payments. For income-focused investors, a company’s dividend yield and the stability of its dividend payments can be as important as growth. Companies with high dividend yields might be undervalued by the PEG ratio, which only focuses on earnings growth.
Market Conditions: The effectiveness of the PEG ratio can also be influenced by the overall market conditions. During bull markets, growth stocks tend to perform well, and their high PEG ratios may be justified by the market’s momentum. Conversely, in bear markets, value stocks with lower PEG ratios might be more favorable, regardless of growth projections.
Quantitative Focus: The PEG ratio is a purely quantitative tool and does not take qualitative factors into account. Elements such as management quality, brand strength, market position, and industry trends can significantly impact a company’s future performance but are not reflected in the PEG ratio.

If you liked this post on How to Calculate the PEG Ratio in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

How to Add Flags to Conditional Formatting Rules

September 1, 2023

by howtoexcel conditional formatting

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:

Creating a new conditional formatting rule in Excel.

On the next screen, there are many different options for creating rules:

Selecting the type of formatting rule in Excel.

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:

Creating a conditional formatting rule when a cell value is greater than zero.

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:

A budget table where conditional formatting has been applied to amounts that are overbudget.

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.

Clearing conditional formatting rules from an 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.

Selecting a flag to insert into an Excel spreadsheet.

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:

A budget table in Excel with red flags highlighting overbudget amounts.

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.

How to Find Duplicates and Unique Values in Google Sheets

December 6, 2022

by howtoexcel conditional formatting countif formatting google sheets

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.

Removing duplicate values in Google Sheets.

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:

Creating a conditional formatting rule in Google Sheets.

I’ll leave the default highlighting options, and now it will highlight all the values that show up more than once in column B:

Table with conditional formatting rules applied.

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:

Conditional formatting showing only 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:

Using the Unique function in Google Sheets to extract a list of unique values.

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.

How to Apply Conditional Formatting to Charts in Excel

September 6, 2022

by howtoexcel with No Comment charts conditional formatting

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:

Table showing year-over-year revenue growth by quarter.

Charting the data out would show the highs and lows effectively:

Chart showing year-over-year revenue growth by quarter.

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:

Green chart showing year-over-year revenue growth by quarter.

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.

Multi-colored chart showing year-over-year revenue growth by quarter.

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.

Historical Stock Returns by Month

February 2, 2022

by howtoexcel with No Comment conditional formatting google sheets investing stocks

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:

=GOOGLEFINANCE("AMZN","price",TODAY()-5000,TODAY())

Now I have the following values:

Amazon's historical stock price 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:

Google Sheets table with dates showing no time next to share price.

Creating a date matrix

Next, what I’m going to do is create a matrix that has years going vertically and months going across:

A matrix in Google Sheets with months going across and years going down vertically.

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:

=MATCH(EOMONTH(DATEVALUE(F$1&" 1, "&$E2),0),$C:$C,1)

And lastly, inside of an INDEX function that will return the corresponding price from column B:

=INDEX($B:$B,MATCH(EOMONTH(DATEVALUE(F$1&" 1, "&$E2),0),$C:$C,1),1)

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:

=INDEX($B:$B,MATCH(EOMONTH(DATEVALUE(F$1&" 1, "&$E2),-1),$C:$C,1),1)

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:

=INDEX($B:$B,MATCH(EOMONTH(DATEVALUE(F$1&" 1, "&$E2),0),$C:$C,1),1)/INDEX($B:$B,MATCH(EOMONTH(DATEVALUE(F$1&" 1, "&$E2),-1),$C:$C,1),1)-1

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:

Matrix showing monthly returns while also utilizing conditional formatting.

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:

Matrix showing monthly returns, summarized by win rates and averages.

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.

Creating a Stock Market Dashboard in Excel

September 28, 2021

by howtoexcel with No Comment conditional formatting dashboard formatting google sheets investing parse power query stocks substitute

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:

Stock market indicators in Google Sheets.

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:

CNBC: https://www.cnbc.com/id/10001147/device/rss/rss.html

WSJ: https://feeds.a.dj.com/rss/RSSMarketsMain.xml

NYT: https://feeds.a.dj.com/rss/RSSMarketsMain.xml

I will pull them all in the same way, using the IMPORTFEED function. Here’s an example with the CNBC feed:

=IMPORTFEED(“https://www.cnbc.com/id/10001147/device/rss/rss.html”,”items”,true,10)

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:

News articles pulled into Google Sheets using the IMPORTFEED function.

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:

Publishing data to the web from Google Sheets.

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:

Creating a query in Excel using the from web option.

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:

Selecting a table for Power Query to pull data from.

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:

Market indicators imported into Excel from Google Sheets.

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:

Market indicators imported into Excel from Google Sheets with negative values showing up in red.

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:

Stock dashboard showing stock market indicators and the latest business news.

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:

Changing the color scheme of a treemap 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:

Treemap chart in Excel showing ticker symbols and percent changes.

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:

Power Query menu showing standard calculation operators.

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

Column from Examples button in Power Query.

That will create a new column:

Power Query editor after adding a new column from examples.

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.

The Power Query column from examples filling in the rest of my values.

After creating the new column, I can change the format from text to currency by clicking on the ‘abc’ letters in the title:

Changing a column's format in Power Query.

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:

Stock trading volumes showing letters and numbers.

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:

IF(RIGHT([@Volume])=”B”,1000,IF(RIGHT([@Volume])=”M”,1,0.000001))

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:

(IF(RIGHT([@Volume])=”B”,1000,IF(RIGHT([@Volume])=”M”,1,0.000001))*SUBSTITUTE(SUBSTITUTE([@Volume],”B”,””),”M”,””)

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:

IF(RIGHT([@[Avg Vol (3 month)]])=”B”,1000,IF(RIGHT([@[Avg Vol (3 month)]])=”M”,1,0.000001))*SUBSTITUTE(SUBSTITUTE([@[Avg Vol (3 month)]],”B”,””),”M”,””))

The last part involves putting all this together:

=(IF(RIGHT([@Volume])=”B”,1000,IF(RIGHT([@Volume])=”M”,1,0.000001))SUBSTITUTE(SUBSTITUTE([@Volume],”B”,””),”M”,””)/(IF(RIGHT([@[Avg Vol (3 month)]])=”B”,1000,IF(RIGHT([@[Avg Vol (3 month)]])=”M”,1,0.000001))SUBSTITUTE(SUBSTITUTE([@[Avg Vol (3 month)]],”B”,””),”M”,””)))-1

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:

Stock market dashboard showing top and bottom gainers.

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.

How to Make a Checklist in Excel

September 7, 2021

by howtoexcel with No Comment conditional formatting formatting forms

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:

The Insert menu under the Developer tab.

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:

Check boxes next to all the different task items.

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:

The control section in the Format Control settings.

Then, when the checkbox is ticked or unticked, here’s how the values in the will appear in the linked cell:

Two check boxes, one ticked and one unticked.

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:

Multiple check boxes linked to one 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:

Creating a new conditional formatting rule.

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

Applying conditional formatting using a formula.

Then, under the Format options, I will apply a strikethrough effect:

A strikethrough effect applied under the font settings.

Now, when a checkbox is ticked, the text will have a strikethrough effect:

Task list with completed tasks showing 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:

Checklist created with tasks, checkboxes, and strikethrough effects.

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.

How to Hide Zero Values on an Excel Chart

August 30, 2021

by howtoexcel with No Comment charts conditional formatting formatting

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:

Excel sheet showing a SUMIF calculation by month.

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))

Chart hiding blank values but #N/A values still showing.

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:

Creating a new rule for conditional formatting.

Select the option to Use a formula to determine which cells to format and enter the following:

=ISNA(B1)

Creating a conditional formatting rule using a formula.

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:

Excel chart with the zero values hidden.

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.

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

May 11, 2021

by howtoexcel with No Comment beginner charts conditional formatting formatting tips & tricks

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:

Changing the gap width on column charts.

3. Adding a descriptive title and subheader

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

Adding a title and subheader for an Excel chart.

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

4. Adding data labels

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

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

Excel chart after adjusting data labels.

5. Adding a data table

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

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

Setting the format code for an Excel chart.

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

Excel chart with data table but no duplicate axis labels.

6. Remove the border

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

Excel chart without a border surrounding it.

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

7. Use a secondary axis with multiple chart types

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

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

Setting up multiple chart types in Excel.

This is what my updated chart looks like:

Multiple chart types in a single visual.

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

8. Move the axis categories down

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

Change in number of vehicles sold, month over month.

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

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

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:

How to invert colors if negative on an Excel chart.

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

Excel chart with negatives in red, positives in green.

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

If you liked this post on 9 Things You Can Do to Make Your Charts Easier to Read, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

How to Do Sensitivity Analysis in Excel

September 12, 2020

by howtoexcel with No Comment conditional formatting financial analysis formatting intermediate

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

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

Setting up the analysis

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

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

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

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

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

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

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

Comparing two dividend stock yields in excel.

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

Adding in the comparables

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

Sensitivity analysis of multiple stock yields.

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

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

Formatting cells to show negatives in red.

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

Applying formatting to sensitivity analysis.

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

Using color scales to add conditional formatting.

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

Applying conditional formatting to sensitivity analysis table.

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

Changing your data becomes much easier

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

Changing variables in the sensitivity analysis spreadsheet.

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

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

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

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

If you liked this post on how to do a sensitivity analysis in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.