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.
Are you creating a chart that shows progress, with a certain goal in mind? In this post, I’ll show you how to create a chart with a target line so that you can see how close you are progressing toward your goal.
A common example for this type of chart is where you are reporting monthly sales and have a goal you want to reach for the year. Here’s a chart that shows the monthly revenue and has a cumulative total as well:
Creating the target line
To create a target line, I need to add another series to this chart. For example, let’s say your goal is for sales to hit $50,000 for the year. To do that, you just need to create another series. I’ll call it ‘Target’ and for each of the values, I’ll enter in $50,000:
You don’t need to enter $50,000 manually into each cell. You could use the autofill to copy the values down. However, a more flexible way to do this is to enter $50,000 into the first cell, and use a formula to refer to that cell. That way, if you change your target amount, you only need to make the change in one cell.
If you’ve already created your chart and want to add the line to your chart, you’ll need to right-click on the chart and click Select Data. Then, adjust your chart range so that it includes the extra column, and then you’ll see your chart update with the line. If you are creating a chart from scratch, then you just have to select the correct range when first creating it.
One additional thing you may want to do at this stage is to adjust the formatting of the target line. A good idea can be to make it look different from the other lines on your chart. One way you can do this is by using dashes. If you click on the target line, you will see a pane show up on the right-hand side showing you options to format the data series. Click on the paint bucket icon and you’ll see various settings for the line. There is one option for the Dash type which will allow you to show the line as breaking up as opposed to being solid:
After also changing the color to a solid black, this is what my chart looks like with these changes:
If you like this post on How to Create a Chart With a Target Line, 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.
Waterfall charts are an effective way to display data visually. They are particularly useful if you’re analyzing an income statement and want to see which parts accounted for the bulk of the change in profitability from one period to the next. In this example, I’m going to use Amazon’s first-quarter earnings of 2022, which saw the company’s bottom line fall into the red for the first time since 2015. Using a waterfall chart, we can quickly analyze what were the big drivers behind the drop in profitability — and the results may surprise you.
Step 1: Preparing the data for a waterfall chart
In a waterfall chart, you want to calculate the change in values. To start with, I’ve entered all the main income statement line items from Amazon’s Q1 earnings for 2022 and 2021, side by side:
I’ve grouped some expenses together for the sake of not having too many items. With waterfall charts, there are a couple of dangers. The first is that your descriptions run too long and it’s hard to display the line items. The second is that you have too many items and your chart needs to become excessively wide to accommodate all the changes.
One thing you’ll notice here is that at the bottom I have the net income (loss) line. This is a summation of the above items to ensure that it correctly ties out to the profit or loss that the company reported. This is an important step to make sure that you’ve entered your data correctly. Expenses should be negative (outflows) while income should be positive (inflows).
The next step is to now calculate the difference between the two periods, which can be done in a change column that takes the current value and subtracts from it the prior period’s value:
At the bottom, I’ve summed up all the changes. These figures are in millions, and so this is a significant $11.951 billion change in net income from a profit of $8.1 billion in the prior-year period to a loss of $3.8 billion.
Now that the data looks correct, the next step is to plot these values on a waterfall chart.
Step 2: Plotting the waterfall chart
To create the chart, I’ll select the data in the change column along with the related headers. From there I can either click on the image of a chart in the menu bar or I can go to the Insert menu and select Chart. If it doesn’t detect which chart I want to use, then I can select the image of waterfall chart from the Chart type drop-down option in the Setup tab:
Now it will show this:
The chart looks correct, however there are multiple changes we can make to help this look better.
Step 3: Modifying the waterfall chart
To start with, I’m going to modify the colors. While red makes sense for negatives, I’m going to change the blue to green, to better reflect a positive inflow of cash. This can be done by double-clicking on the chart and in the Chart Editor, going to the Series section, and scrolling to the Positive label. There, I can change the fill color:
This also gives me the option to change the line color and transparency using the opacity percentages. At this point, I’ll remove the legend since the green and red values are sufficient to tell you whether it was a positive or negative change.
The next thing I’ll change is the grey subtotal bar at the end. Ideally, you would have a starting and ending point on the chart to better show where one period started and where the other ended. But by default, the subtotal just adds up the sum of the change. To adjust this, I’m going to add a row to my table above Net Sales, called Q1 2021 Net Income. In the change column, I will simply put the amount, no change. This is what my updated table looks like:
If the chart doesn’t automatically update, you may need to update the range. This can be done by double-clicking on the chart and in the Setup section, modifying the range for the Series and/or the X-axis. But the bar charts for the totals still need adjusting. The first one shows green. To fix this, I’ll double-click on the chart to edit it and under the Series section, select the box to Use first value as a subtotal. Now the first bar chart will turn grey.
In the same section, I’ll also uncheck the box that says Add subtotal after last value in series. That will remove the last bar chart. Then, I’ll click on the option to Add new subtotal. Select to add it after the last item. By doing this, I can now specify the name of that total, as opposed to just showing ‘Subtotal.’ In this space, I’ll enter Q1 2022 Net Loss.
The only thing left now is to adjust the chart and stretch it out sufficiently so that the labels display horizontally. And I’ll also add a title — this can be done in the Customize section and under the Chart & Axis Titles area. Here is my completed waterfall chart in Google Sheets:
Now, from looking at this, you can see that Amazon was still at a profit until it reached the other income and expenses line. This would still require additional digging to see the reason for the loss, but it would point us in the right direction. And Amazon’s breakdown of these other expense items tells us that it incured a $7.6 billion loss on its investment in Rivian Automotive — the key reason its net profit from a year ago turned into a loss. While other expenses increased, they alone weren’t enough to pull the company into a net loss position.
If you liked this post on How to Make a Waterfall Chart 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.
A chart in Excel can be a quick and easy way to display information. In this example, I’m going to use a bar chart to show a range of values, displaying both the highs and lows. Whether you want to show the range of a stock price’s highs and lows over the past year, or just a range between possible prices of something, this can apply to either one of those approaches.
In this example, I’m going to use first-time dog expenses, which can be very broad, with some items costing as little as $10 while others being well into the hundreds.
Creating the charts
First off, I’ll download the data into a spreadsheet. Here’s how it looks:
This format can easily be converted into bar charts. However, I don’t want two different bar charts, and so I’ll use the option to create a Stacked Bar Chart instead.
This doesn’t at first look like the chart that I want to create since this is adding both the highs and lows together. What I can do to make this work is by making the bar chart for the low amount to be invisible. To do this, I’ll right-click on one of the blue bar charts and select the fill option to No Fill
Upon doing this, that first bar chart disappears:
Without a scale, it doesn’t matter that the ranges are stacked since it effectively only shows the difference from the end of the first bar chart (which would be the start of the range) to the end of the second bar chart (this would cover the difference in value between the first bar chart and the second one). What I will do at this point is get rid of any legends and values along the x-axis.
To more effectively display the data, I’ll also add data labels. For the second bar chart, which is highlighted in orange, I’ll right-click and select Add Data Labels. By default, it’ll put the value in the middle. However, I’ll adjust it so that it goes towards the end of the bar chart. To change the appearance of the labels, simply right-click on any of them and select Format Data Labels. And under Label Position, pick the option to show Inside End. This will now move the data label to the end of the bar chart. After modifying some of the colors, this is what my chart looks like now:
You could also remove some of the gridlines. And you may want to add data labels for the first bar chart to show where the starting point is. But given that some of these bar charts are small, they may not be large enough to accommodate both values.
If you liked this post on How to Create a Chart Showing a Range of 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.
Dashboards in Excel can update when a user makes a selection on a slicer or refreshes data. You can even use macros to automatically update a chart or dashboard for you. In this post, I’ll share with you a template that I’ve created that will allow you to effectively play your dashboard, updating it from one period to the next, and showing the change in the chart over time. Here it is in action:
The template has three sections: one for pivot tables, one for the data, and one for the dashboard. You can set the file up however you want, the main area that needs to remain largely the same is the dashboard sheet. Every chart on this sheet only will automatically get updated. And for it to work properly, all the charts need to be linked to the one timeline chart in here (i.e. there cannot be more than one). For information on how to set up your timeline (or any other slicer for that matter) so that you can link it to multiple charts, you’ll need to learn about how to adjust Report Connections in this post.
Once you’ve got the charts you want to be connected to the timeline, then it’s a matter of just updating the settings section on the Dashboard tab. This is off to the left, with the values that you need to enter/update highlighted in yellow.
These simply specify what date you want to start from, where you want to end at, and by which interval you want to jump (e.g. x days/months/years). Depending on the frequency you select, your dashboard can either play very quickly, or very slowly.
The last step is to just click the Animate Dashboard button at the end of the home tab:
Upon clicking this, the timeline will jump by the intervals you specified. No other changes will be made to any filters or slicers you have selected. The only changes will take place to the timeline, at which point, you should see something similar to the video posted at the top of this post.
If you liked this free template that helps you animate your dashboards, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
In Excel, you can create quick and easy visuals with only a formula. You don’t need to insert charts or worry about if they are set up correctly. In this post, I’ll show you how you can quickly create both bar and column charts.
For this example, I’m going to use data from Apple’s most recent earnings report, to see the split between sales of its different categories. Here’s what the data looks like from its most recent filing:
To create a simple bar chart, I’m going to use the REPT function, which allows me to repeat text. The character I’m going to repeat is the “|” line, which on most keyboards is the button above the enter key. Holding shift and that key should give you that line. The number of times I want to repeat the character will be the value in column B. But because it’s too large, I’m going to divide it by a factor of 100. Here’s how that formula will look:
=REPT("|",B4/1000)
If I copy this down, my bar chart remains a work in progress:
One way to make this look like more of a bar chart is by changing the font. In column C, I’m going to change it to Britannic Bold. And now, this looks like a proper bar chart:
If I sort the values from largest to smallest, then it’s easier to see the progression:
This is good, but this is also still a bar chart. To convert this into a column chart, I need to make a couple of changes. The first thing is I need to arrange the data differently so that the fields and the values are going horizontally rather than vertically. To do this, you can just transpose the data. You can do this using the TRANSPOSE function. Now, my data is better suited for a column chart:
Now, I’ll add back the REPT function and use the same font. Except for this time, I will modify the cells so that the alignment is vertical:
Now, after compressing the columns, I have a column chart set up:
Here’s a quick video showing the steps:
If you liked this post on How to Create Column Charts in Excel With Just a Formula, 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.
The MACD line and chart is a popular tool for technical analysts who buy and sell stocks. And in this post, I’ll show you how you can create it from start to finish. In my example, I’ve downloaded Apple’s stock price history for the past year from Yahoo Finance, and I’ll use that to calculate its MACD line. Here’s a sample of what I’m starting with:
Calculating the exponential moving averages
To calculate the MACD line, I’ll need to create multiple exponential moving averages (EMAs). One for 9 days, 12 days, and for 26 days. The logic will be the same so I can start with creating a formula for the 9-day EMA and then apply that to the others.
I’m going to create a couple of variables. The first being the n for the number of days. And the second one is for the weighting that you’ll apply to more recent values, and thus, turning it from a simple moving average into an exponential one. The weighting is calculated as follows:
=2/(1+n)
I’ll start my formulas to calculate the 9-day EMA by first checking to see if I have at least 10 data points. If I don’t, then I’m only calculating a simple moving average. Here’s how the start of that formula looks, assuming my closing stock prices start from cell B5 and my variable n is in cell C1:
IF(COUNTA($B$5:$B5)<=C$1,AVERAGE($B$5:$B5)
A key part of the formula is freezing cells properly. Cell $B$5 won’t move, but $B5 will as I drag it down. And this allows me to calculate the cumulative number of data points, and the corresponding average. The next part of the formula is what happens if I have more than nine data points. In that case, I will take the weighting (this is cell C2) on my sheet, and multiply that by the difference between the most recent stock price and the previous EMA. This will then get added to the previous day’s EMA:
C$2*($B5-$C4)+$C4
Column C is the one that contains the EMAs. My complete formula is as follows:
I can now copy this logic across multiple columns to calculate the 12 and 26 day EMAs as well:
Now, I’ll set up the calculations for the final three columns:
MACD: This involves taking the 12-day EMA and subtracting the 26-day EMA from that.
Signal Line: This is a 9-day EMA of the MACD line.
Histogram: This is calculated as the difference between the MACD line and the Signal line.
With all those columns set up, here is my completed table:
Creating the charts
With all the columns set up, the next part is to put the key data into a chart to illustrate the MACD line, Signal line, and Histogram. To make the chart look like a typical MACD chart, I’ll need to set the MACD line and Signal line to be line charts, and for the Histogram to be a column chart.
Initially, when the chart is created, there’s too much data in there since the data set is bigger than it needs to be:
To fix this, I right-click on the chart and click Select Data. Then, I remove all the series except for the last three: MACD line, Signal line, and Histogram. My updated chart looks as follows:
There are still a few more changes that I am going to make here. The first is to fix the axis, as there are gaps between the column charts. That’s because Excel is recognizing the axis as a date axis. And while that’s correct, that means there will be gaps since stocks don’t trade every day of the week, and thus, those gaps, are weekends. To fix this, right-click on the axis and click Format Axis. And then, change the Axis Type so that it is a Text axis:
And then, under the Labels section, I set the position so that it is Low and at the bottom of the chart. My updated chart looks a bit better:
The last change you may want to consider is adjusting the column chart gap, to shrink it so the chart looks more like a histogram. If you right-click on them and click Format Data Series, there’s an option to change the Gap Width. I find that setting this to 50% normally results in a good gap size::
And now we’ve got a chart that resembles what you might find on major finance sites when looking at MACD. If you want to follow along with the sheet that I’ve created, you can download my MACD chart template here.
If you liked this post on How to Create a MACD 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.
A pie chart can be a great way to show percentages, such as how much significant an individual’s product sales are to the total number. However, if you’re dealing with more than 10 items, a pie chart can start to get a bit messy. But there is a way to address that, and that’s with a second pie chart. In this post, I’ll show you how to make a pie of a pie chat so that it’s easier to display more items.
At what point should you consider another chart?
There’s no magic number as to how many items is the limit for a pie chart. It will ultimately depending on how big your slices are, and the size of your text. For my example, I’m going to use data from healthcare company Pfizer‘s most recent quarterly results to show sales of its top-selling products. For the third quarter of 2021, the revenue of its top products were as follows:
If I were to create a pie chart using this data, this is how it would look like:
Since there are many small items accounting for 3% or less of revenue, a case could be made for creating a second pie chart here. You don’t have to do so, but it could help make the visual more readable for users.
Creating a second pie chart
To add a second pie chart, what I need to do is select the chart. Then, go into the Chart Design tab and select the Change Chart Type option. From there, I will select the Pie of pie chart:
Upon making the selection, I now get a second chart that offloads some of the items onto there:
Customizing the size of the pie chart
By default the smallest slices will be on to the second pie chart. However, you can specify how you want to split the charts. If you right-click and select Format Data Series, you can specify the number of items that show in the second chart:
In the above example, there are 4 values in the second chart. But you can change this higher or lower and ultimately that will be discretionary; it will depend on how you want your data to look. If you have many small items in your summary that you want to show in a smaller pie chart, then you may want to increase the number of values in the second plot. If that’s not the case, you could opt for a smaller number.
This may seem a bit arbitrary and there are other rules you can apply instead. For example, you can select to Split Series By value:
In this case, any value that’s less than 1,000 will be moved off to the second pie chart. Here’s what that looks like:
Another option is to split the chart based on the percentage of each slice:
In the above example, I’ve selected any items that account for les than 3% of the total, they will be pushed onto the second pie chart:
If you want to manually move items from one chart to the other, you can also change the Split Series By option to ‘Custom’ and then click on a slice you want to move. You’ll see an option there to set whether it belongs to the Second Plot or to the First Plot.
Making these changes will move the slice to either the first pie chart or the second one.
If you liked this post on How to Make a Pie of a Pie 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.
In Excel, there are multiple different stock charts you can create. All you need is some combination of the date, opening price, high price, low price, closing price, and volume to generate what you need. In this post, I’ll show you how you can utilize Power Query to pull that data in and transform it, without having to apply any manual changes to it every time. For an overview, you can check out this post on How to Get Stock Quotes From Yahoo Finance Using Power Query. I’m not going to repeat those steps and will assume that you’re familiar with that process.
In this example, I’ve downloaded the stock prices for Apple (NASDAQ:AAPL) for the month of October 2021:
This already has all the data that is needed to create the four types of stock charts in Excel:
High-Low-Close
Open-High-Low-Close
Volume-High-Low-Close
Volume-Open-High-Low-Close
The key to making these different charts is just ensuring that you’ve got the correct fields in your download, and in the right order. For the High-Low-Close chart, you only need three fields to generate the following chart:
If you’re creating the open-high-low-close chart, all you need is to add the open field to the data set:
And for the volume-high-low-close chart, it’s just the volume instead of the open that goes at the start, and then you get something that looks like this:
The last chart includes both the volume and the open before the high, low, and close values:
These charts are fairly straightforward to generate once your data is in the right order. But rather than moving around different fields, you can make all the changes within Power Query so that right when your data loads, it’s in the correct format.
Using Power Query to adjust your download
To modify an existing query, go to the Data tab and select Queries & Connections. Off to the right, you should see your query, where you can right-click and Edit it:
The first thing I’ll edit is the date range. In my earlier post, I just downloaded the full year of data. But if I want to filter only for October, then I can click on the drop-down for the Date field and select Date Filters to filter Between a range of dates.
Using the calendar icon, I can specify the range of dates I want to include in my download:
Next, if I want to just include the data for the most basic chart, the high-low-close chart, I’ll right-click on the Open, Adj Close, and Volume columns to remove them. Then, I’m left with the following:
Now, if I were to load the data in Excel, I would already have all the columns I need to create the chart:
Note: if you want to get rid of the gap in dates on the chart, click on Format Axis and for the Axis Type, select Text axis:
This prevents Excel from trying to fill in any missing dates from your data set Another thing you may want to do is format the date field so that it is a custom format showing MMM DD so that it saves space:
Now, let’s go back into Power Query and this time create the more complex download, for the volume-open-high-low-close chart. I’ll start by removing the last step I applied which removed more columns than I need for this current download. To remove any steps in Power Query, click on the ‘X’ next to it:
In this example, it’s just the Removed Columns step I will eliminate. The only column I need to remove from the download this time around is the Adj Close. So that’s what I’ll do, right-click and remove that column. However, my table still is not in the correct order:
The Volume column needs to go before the Open column. This is as easy as just dragging the column and putting it in front:
Now that it’s in the right order, I can load and close this into Excel. And now, the volume-open-high-low-close chart can easily generate:
Suppose I want to adjust my chart to include data from September as well. Again, I can go back to edit my query. This time, I’ll select the gear icon next to the Filtered Rows step:
There, I can just modify my date range using the calendar:
Now, re-loading the data into Excel automatically updates my chart with a simple refresh:
The beauty of this is this query will update with new data and your chart will also update, taking out the manual steps of having to make any changes yourself. And if you incorporate named ranges like in my post covering pulling stock prices, then the data will easily refresh based on the variables that you’ve entered.
If you liked this post on How to Create Stock Charts in Excel Using Power Query, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
In a previous post, I showed how to make a forecast chart in Excel with a dotted line. This time around, I’m going to go one step further and show you how to create a chart that shows a range of possible values. This is useful in the event that you want to show some flexibility in your forecast and where providing a range might be a more realistic option.
For this example, I’m going to project a company’s future dividend payment. Below, I have a a record of the past dividend payments along with the annual rate of increase:
In order to create a range, I’m going to set both a high rate of growth and a low one. Since the company has made 10% increases in the past, I’m going to use that as the high. And for the low rate of growth, that will be 5%. Using those different rates, I can set up additional columns for what the dividend would be if the low rate were used and if the high one were applied. I will also create a column to calculate the difference between the high and low amounts, as well as one for a base amount — which will just be equal to the low amount. This will be used for stacking the difference on top of it to create the desired area chart:
Creating the chart
Now that the data is set up, I’m going to start creating the chart. Using a combination approach, I’ll set a line chart for the actual, low, and high columns. And for the base and difference amounts, I will set those to be stacked area charts. The growth rate I’ll leave as is because I will remove that once the chart is created:
Next, I’ll right-click on the chart and click on Select Data. From the next screen, I will untick the box for the Growth Rate:
Then, I will right-click on the x-axis, select Format Axis and select the option to put Categories in reverse order. Now my chart looks as follows:
Now, I’ll remove the legend and format the base color, which is currently grey, to a blank fill color:
I’ll change the line color for the high amount to green, the low amount to red, and apply dashed lines to both. For the actuals, I’ll set that to a black line. And for the area chart that is in green right now, I will apply a Pattern Fill and use a checkered pattern:
With all those changes, my updated forecast chart now looks like this:
If you liked this post on How to Make a Forecast Chart in Excel With a Dotted Line, 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.
