Microsoft Excel can help you analyze data, even without having to do any computations. By simply setting up conditional formatting rules, you can easily visualize data and identify trends. Doing that can help focus your attention on key numbers and make your analysis process much more efficient. In the following data set, I have a list of investment returns by year. I’ll show you how you can setup a rule to highlight which return was larger in each year:
Creating the conditional formatting rule
To create a conditional formatting rule to highlight the largest values, I’m going to select column B. Then, I’m going to go into the Conditional Formatting menu on the Home tab and will click on New Rule.
Under the option for the rule type, I’m going to select use a formula to determine which cells to format, and I’ll enter the following formula:
=B1>C1
Then I’ll adjust the format so that the cell has a dark grey color and a white, bolded text.
After clicking apply, the formatting will take effect:
A similar rule needs to be setup for column C. And in this case, I’ll highlight the values in blue.
Now there is highlighting applied to both columns:
However, the header is also highlighted in column C. To fix this, I’ll add a condition to check to make sure that it is a number:
Now the conditional formatting is properly applied, and ignores the header row.
If you like this post on How to Highlight the Largest Values in Excel With Conditional Formatting, 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.
If you want to create conditional formatting rules but want to easily change the cutoff values for them, you can link your rules to specific cells, to make that process easy. Below, I’ll show you how to make your conditional formatting rules dynamic. Rather than going into the settings each time, you can just update specific cells, which will change your cutoff points.
Creating conditional formatting rules for changes in stock prices
A common example where you might want to adjust the cutoff values for conditional formatting rules is when dealing with stock prices. Suppose you want to highlight stocks when they have risen by more than 5%. But in other cases, such as when you’re looking at a long time frame, you may want the threshold to be much higher than that. This is where linking conditional formatting rules can be advantageous, by making the update process easier.
In the spreadsheet below, I have a list of stocks and their respective returns between December 31, 2024 and January 31, 2025. I have pulled in their performances using Excel’s STOCKHISTORY function.
In this scenario, I may want to highlight stocks which have returns of more than 5% as green, and those which are down by 5% in red. I’ll create two conditional formatting rules to highlight both the return and the stock. Here’s how one of the rules looks:
The downside of this rule is that the 0.05 value is hardcoded. If I want to change the threshold, I would need to go back into the conditional formatting rules and modify it. There is an easier way around this, and that’s by just linking to a specific cell.
In the above example, I’ve entered the values that I want to link my conditional formatting rules to. In the case of a green highlight, I’m going to link to cell E2. And when I’m formatting the cells red, I’ll link to cell E3. Here’s how my updated conditional formatting rule will look:
Now, I’ve removed the hardcoded value and my conditional formatting rule now points to a specific cell, which is frozen and doesn’t change. To do the same thing for my red highlight rule, when the values are down 5%, I do the same thing, except flip the sign and refer to cell $E$3:
My conditional formatting rules are correctly applied to my data set, highlighting returns of more than 5% in green, and returns of less than negative 5% in red:
The advantage here is I can easily update my thresholds by just modifying the values in column E. If I change them to 10% for green and -10% for red, I simply make the changes in those cells and my conditional formatting updates immediately:
By setting up my conditional formatting rules this way, it’s easy to update the process in seconds and at the same time, I can see what my threshold are.
If you like this post on How to Apply Dynamic Conditional Formatting and Link Rules to Specific Cells, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.
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.
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
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.
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.
