ConditionalFormattingCharts

How to Apply Conditional Formatting to Charts in Excel

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.

H2EHistoricalMonthlyReturns

Historical Stock Returns by Month

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.

stockdashboard

Creating a Stock Market Dashboard in Excel

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:

Removing a column from Power Query.

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:

Treemap chart in Excel.

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.

Multiplying values in Power Query.

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.

Stock market dashboard in Excel.

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:

External data properties in Excel.

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.

H2Echecklist

How to Make a Checklist in Excel

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:

List of tasks in Excel.

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:

Enabling the developer tab in Excel.

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.

H2Ezerochart

How to Hide Zero Values on an Excel Chart

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:

Line chart showing zero values.

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.

9tips

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

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:

Auto sales by month.

And this is my chart, which shows unit sales by month:

Chart showing auto 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:

Adding a legend to an Excel chart.

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:

Excel chart with legend added.

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.

Formatting the 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:

Labels added to an Excel chart.

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:

Excel chart with a data 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.

Setting the label position to low.

Now, when my chart is updated it looks like this:

Chart with axis labels at the bottom.

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.

sensitivityanalysis

How to Do Sensitivity Analysis in Excel

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:

Comparing two dividend stock yields.

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:

Sensitivity analysis in a chart.

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.

colorcode1

Color Calculator Template: Sum by Color in Excel

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

How the template works

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

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

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

Setting up the data

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

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

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

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

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

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

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

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

Updating the calculations

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

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

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

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

The code

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

Sub Oval1_Click()

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

lstrow = ActiveCell.SpecialCells(xlLastCell).Row

Set colorrange = Range("colorrange")

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


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

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

nextcl:

Next cl

End Sub

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

Using the template

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

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

Download

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


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

hiding values in excel

How to Hide Zero Values in Excel

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

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

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

How to hide errors on a spreadsheet

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

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

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

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

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

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

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

=C5/D5-1

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

=$C5<1000

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


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

rowcolor

How to Alternate Row Color in Excel

Looking at numbers on a plain white spreadsheet can sometimes make it difficult to differentiate one row from another, especially from afar. It can also make the spreadsheet look a bit bland. That’s why in this post I’ll show you how in Excel you can alternate the color of both rows and columns.

The first step is to create a new conditional formatting rule in Excel

Ultimately, all you need to alternate the row color in an Excel spreadsheet is a little bit of conditional formatting. It just comes down to getting the logic and the formula right in ensuring that the correct rows are highlighted.

To get things started, select Conditional Formatting from the Home Tab and then Create New Rule

home tab excel selecting new rule from conditional formatting menu

Once there, you’ll want to select the option to Use a formula to determine which cells to format:

selecting a formula for conditional formatting in excel

This is where we’ll now enter the formula that we’ll want to use for the alternating rows.

Use the MOD function along with ROW to determine which row to apply a different color to

The key function that we’ll need to use is the MOD function. What this function does is it tells us what the remainder is after a number has been divided by a divisor. This is important because what we need the conditional formatting to do is to evaluate each individual row to tell us whether it is an odd or even number, and the MOD function allows us to do that.

For example, the following formula will return a value of 0:

=MOD(6,2)

Since two divided by six will return a result of three and have no remainder, the result of the formula is 0. If, however, we change the formula to this:

=MOD(6,4)

The formula will now return a value of two, since four only goes into six one time, leaving a remainder of two.

Now, it’s simply a matter of applying this logic to each row. To do this, we need to incorporate the ROW function into our formula as well. The end formula is actually not very complex:

=MOD(ROW(),2)=0

The above formula will be true if the row is an even number, and thus, any conditional formatting we have set for that rule will apply. If we wanted to modify every odd row, then the formula could be tweaked as follows:

=MOD(ROW(),2)=1

You could have two sets of rules, one for odd rows and one for evens, but that’s really not necessary. Instead, you can simply select all the rows and then apply the formatting you want for the even rows, and then create a conditional formatting rule for the odd rows. This way, the formatting you apply to the entire sheet will be overwritten by the conditional formatting rules for the odd rows anyway and your original formatting will end up applying only to the even ones.

Applying a different color to the different rows

Once you’ve created your rule, then it’s just a matter of selecting the formatting you want to use and how you want to highlight the rows. I’d suggest a color that is light so that you don’t have too much contrast. This is how my Excel spreadsheet looks after applying a light blue color to every alternate row. I’ve left the default formatting in place for the odd rows.

conditional formatting alternating rows highlighted

If your spreadsheet doesn’t look like this, check to make sure that you have applied the conditional formatting to the entire sheet and that it isn’t only to a select few rows or cells. If you’re unsure about this, refer back to my earlier post on conditional formatting to help give you a better idea of how it works.

You can highlight alternate columns in a different color too

As you may have guessed, the same logic and conditional formatting rules that we used above can be applied to columns as well. Instead of the ROW function we just need to use the COLUMN function in our formula. That’s really the only difference as the formula will look nearly identical:

=MOD(COLUMN(),2)=1

You’ll follow the same steps as far as creating a new conditional formatting rule, but the process is largely the same. The one thing that you’ll notice, however, is that if you have both rules in place, your conditional formatting has now overlapped:

conditional formatting overlap for both columns and rows in blue

In the above example, the formatting is the same color, but if they were different, the overlap would stand out even more. And that’s where it may take some experimenting with different formats to ensure that you get the right overlap and that the different formatting rules blend well together. Assuming, of course, that you want both rules in place.

Other options to alternate the row and column color in Excel

While this post showed you how to change colors for odd and even rows and columns, you can certainly extend that logic even further. For instance, you could decide to highlight every third row by changing the MOD function so that you’re dividing by three rather than two. As long as the logic is sound, you can modify these formulas so that they alternate the rows that you want.

For example, if you wanted to alternate the color of every fifth row and every 13th row on your Excel spreadsheet, you could create one conditional formatting rule to apply to every fifth row and then another for every 13th one as well. Although putting this into one larger formula is possible, it would a bit cleaner to put them into different rules.

When it comes to conditional formatting, there’s a lot of flexibility in how you can structure how your spreadsheet works. However, you also don’t want to get too carried away and make the spreadsheet too colorful and difficult to read. Otherwise, it may end up defeating the purpose and making your spreadsheet less user-friendly.


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