Comparison charts are invaluable tools in Excel, widely used across business, education, and research to visually represent data. These charts not only simplify complex information but also highlight key trends and comparisons. A comparison chart in Excel is a visual representation that allows users to compare different items or datasets. These charts are crucial when you need to show differences or similarities between values, track changes over time, or illustrate part-to-whole relationships.
In this article, we’ll compare a company’s sales, expenses, and overall profits by year. Here is some sample data:
Types of Comparison Charts in Excel
There are many types of charts you can use in Excel to compare data. Here are a few examples of common charts you might use when comparing data, and how they look:
Bar Chart: Creating a bar chart in Excel starts with selecting your data and choosing the ‘Bar Chart’ option from the ‘Insert’ tab. Bar charts are particularly useful for comparing individual items or categories. To enhance readability, consider adjusting the bar colors and adding data labels. In the bar chart below, you can easily compare sales versus expenses versus profits, and also compare those values by year.
Column Chart: Similar to bar charts but oriented vertically, column charts are ideal for showing changes over time. After selecting your data, choose ‘Column Chart’ from the ‘Insert’ tab. Play with colors and axes to make your chart stand out. Whether you prefer to go with a column chart or a bar chart may simply come down to your preference.
Line Chart: Line charts are perfect for tracking trends over periods. Select your data, click ‘Insert’, and then ‘Line Chart’. Customize your line chart by changing line styles and adding markers for key data points. Line charts may be more useful when there are fluctuations that you want to plot. Here is the chart based on the current sample data:
Here’s a look at the chart when there are greater fluctuations in the data:
Pie Chart: For part-to-whole comparisons, pie charts are your go-to option. After selecting the data, find ‘Pie Chart’ under the ‘Insert’ tab. Enhance your pie chart by experimenting with different slice colors and adding a legend for clarity. This is ideal when you want to compare individual parts of a greater total. Suppose you wanted to analyze what made up the company’s sales. This is where a pie chart might be most appropriate:
Excel has many more charts available for you to use, but these are good starting options when doing analysis. After you’ve selected the right chart, there are further enhancements you can focus on.
Tips for creating effective comparison charts
Here are some tips and things you can focus on to make your charts even better:
Simplify and Focus: Avoid cluttering your chart with too much information. Focus on the key data points you want to compare. This can sometimes mean creating multiple charts instead of trying to fit everything into one.
Use Appropriate Scale and Axes: Ensure that your axes are scaled properly to accurately reflect the differences in data. Misleading scales can lead to incorrect interpretations.
Color and Design: Use color effectively to differentiate data sets and draw attention to key points. However, be mindful of color blindness and avoid using colors that might be hard to distinguish.
Clear Labels and Legends: Use labels and legends that clearly describe what each part of your chart represents. Avoid jargon or abbreviations that might not be understood by all viewers.
Consistent Formatting: Keep formatting like font size, color schemes, and line styles consistent across all charts, especially when they will be viewed together.
Data Integrity: Ensure your data is accurate and up to date. Misleading or incorrect data can harm credibility.
Accessibility: Make your charts accessible to everyone, including those with visual impairments. This can involve using larger text, high-contrast colors, and providing alternative text descriptions where necessary.
Checklist for creating comparison charts
[ ] Chart Type Selection: Choose the most appropriate chart type for your data.
[ ] Data Accuracy: Verify the data for accuracy and relevance.
[ ] Simplification: Remove unnecessary data or split into multiple charts if needed.
[ ] Scaling and Axes: Check that axes are scaled properly to accurately represent the data.
[ ] Color Usage: Use distinct colors to differentiate data sets; consider color blindness.
[ ] Labels and Legends: Ensure all parts of the chart are clearly labeled.
[ ] Consistent Formatting: Maintain consistent formatting across all elements.
[ ] Review for Clarity: Check if the chart conveys the intended message clearly.
[ ] Accessibility Compliance: Ensure the chart is accessible to all audiences.
[ ] Feedback: If possible, get feedback from others to see if the chart is easily understandable.
If you like this post on How to Create Effective Comparison Charts 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 me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.
A good way to gauge the strength of the U.S. economy and how well it is returning to normal level is by looking at Las Vegas’ visitor data. The Las Vegas Convention and Visitors Authority (LVCVA) has plenty of important metrics that it tracks on its website. From the number of visitors to the city to occupancy levels to daily room rates, and other key performance indicators (KPIs). Using data from that website, which you can find here, I’ll guide you through the step-by-stop process as to how you can build a dashboard to track some of those key metrics.
Step 1. Preparing and consolidating the data
One of the most important parts of data analysis is to clean up your data from the beginning. By doing this, you’ll avoid headaches later on. It’ll also make it easier for you to do analysis in the first place. To get a proper glimpse of how Las Vegas is doing now, it’ll be useful to track multiple years. On the LVCVA website, you can download data for multiple years. For this example, I’m going to download data from 2019 through to 2023 YTD.
This is what one of the files looks like:
As of writing this article, data for 2023 is available up until the end of July. Since the data is organized in the same format on all of the files I’m downloading, I can just copy and paste one year after another. The key is for the rows to line up.
But I still need to clean up this data. One problem is that there are gaps between the months. Once I’ve loaded all the years together, I’ll remove those blank columns. The easiest way to do this is the highlight the top row. Then, press F5 and select Special. There will be an option to select Blanks:
Then, all those gaps are selected:
If your right-click on any one of them, select Delete, choose Entire Column, and press OK. Now those columns are gone:
There’s still one problem here. The way the data is structured right now isn’t useful when creating pivot tables. And if you’re creating a dashboard, you’ll want to be able to create pivot tables easily. Doing so can make it easy to create reports on the fly and easy to make changes. It’s easier to have dates going vertically than horizontally to scroll through data. So what I will do is use the TRANSPOSE function to flip it. All that’s necessary here is to use the function and select your entire data set. Then, voila:
Before I make any further changes, I want to convert this into values. Since I used the TRANSPOSE function, it’s sitting as an array. To change this, I’ll select the entire data set, press CTRL+C, and then press CTRL+SHIFT+V to paste as values. If you don’t have that functionality on your version of Microsoft Excel, right-click and select Paste Special and click on Values.
I will also add a few more columns to make the analysis easier. I’ll create a column for the month and year. This will involve using the MONTH and YEAR functions. The only argument that is needed is the original date, which in the screenshot above, appears under ‘Tourism Indicators.’
And since I want to compare 2023 to 2019, I’ll add a column for ‘Current Period’ and ‘Comparable Period.’ The point of this is to make sure that I can filter the current YTD values against the same values from 2019. Since I have data up until July, any comparisons should also run up until July 2019. For the Current Period, I’m using the MAXIFS function to grab the maximum value for the Month field for the current year (I can use the TODAY function to make it dynamically pull in the current year). Then, for the Comparable Period column, I can compare the Month field to see if it is less than or equal to the Current Period. If it is, then I’ll set the value as a “Y” to indicate it falls in the comparable period or “N” if it doesn’t. This way, if I come across month 8 and my current period only goes up to 7, it will mark that as an “N” which will allow me to easily filter out those results.
Lastly, I will convert all this data into a table. The purpose of this is so that I can easily reference the different fields later on, without having to remember column letters. To convert this into a table, select Insert and click on Table. Then, on the Table Design tab, you can name the table something that’s easy to remember. In my example, I’ll refer to it as tblConsolidated.
Step 2: Identifying the KPIs to track
Before rushing out to create the pivot tables, it’s important to know what you want to track. You don’t want to create a pivot table and track everything possible, otherwise it won’t be a useful summary, which is what a good dashboard should aim to do. That’s why you should devote some time to identify what some of your KPIs should be.
There are a lot of metrics on here and these are the ones that I am going to use, which will help gauge how active and busy Las Vegas is:
Visitor volume. Obviously the number of people visiting the city is a great indication of how many people there are.
Occupancy levels. If hotels are booked up, that’s another positive sign that the city is busy.
RevPAR. This takes the room revenue divided by the number of available rooms. It shows how well a hotel is filling up its rooms at a given rate.
Average Daily Rate. This is partly reflected in RevPAR but it can be a useful indicator as people are more familiar with room prices than they are with RevPAR, especially those who visit Las Vegas often.
En/Deplaned Passengers. This is a helpful metric to know how much out-of-town traffic there is coming to the city.
Average Daily Auto Traffic. With this metric, readers can see how busy the roads are.
Gaming Revenue (Las Vegas Strip). This is another important KPI because it tracks how much people are spending at casinos.
Step 3: Creating the pivot tables
Now it’s on to creating a pivot table for each KPI you want to track. To make this process easier, just create a pivot table one time, and then copy it for as many charts that you want to create. This way, you don’t have to go back and select Insert->Pivot Table over and over again. Just make sure to leave enough room so that they don’t overlap, otherwise you’ll encounter errors.
It’s also a good idea to label your pivot tables by going into the PivotTable Analyze tab. For a pivot table to track visitor volume, you might want to call that ptVisitorvolume, for example. This will be helpful later on if you want to change charts and aren’t sure what PivotTable1 relates to. You’ll also likely want to change the default formatting for a pivot table:
To change the format, don’t just highlight the cells and make the changes, otherwise they’ll revert back once you update the data. Instead, right-click on one of the values and select Value Field Settings. Then, select Number Format and apply the formatting you want to apply to that field.
What I also like to do is put all the pivot tables on a separate tab to keep them organized, while all the charts will go on a main tab dedicated for the dashboard.
Step 4: Creating the charts
When creating your charts, one thing to consider is how you want the data to be visualized. You can do this as part of the stage to identify KPIs. For visitor volume, for example, I’ll use a line chart since I want to see the month-over-month progression. This will also make it easy to compare against multiple years.
Since these are charts created from pivot tables, they are pivot charts, and they come with drop-down options:
They aren’t terribly appealing and to get rid of them, click on the chart, select the PivotChart Analyze tab and unselect the option for Field Buttons:
One thing that can help with creating charts is by using Excel’s existing Chart Styles, which are on the Design tab (which is visible if the chart is selected):
This can be an easy way to customize your charts without having to do so manually.
You may also want to adjust how the data is displayed. Visitor volume, for example, may make sense to leave as the default, which is a summation. But when looking at ADR or RevPAR, you wouldn’t want to sum those values up. Instead, you may want to calculate the average instead. To do that, right-click on one of the fields and select Summarize Values By and select Average
Now, you’ll see an average based on period, which makes more sense than summing up prices.
At this point, it comes down to your personal preferences as to how you want to design the charts, and it would be far too deep to try and get into all those options here. However, I’d suggest mixing up a bit of bar and column charts and also changing up the colors so your dashboard doesn’t look like the same item over and over.
Some additional things you may want to consider are:
Adding data labels. And if you do use them, consider not using axis labels;
Using legends where and when make sense to do so;
Adding background images to your charts to have a different look and feel to them;
Having descriptive titles to help summarize what the chart is displaying;
Not plotting too much on on chart. You may want to consider plotting years instead of months;
Not using a border color so that your charts blend in with the background.
Here are a couple of charts I created with images in the background to make it clear what they are showing:
Step 5: Adding key numbers at the top for further emphasis
Charts are good, but what can also be useful is to put key numbers right at the top so that readers don’t have to spend much time looking for the most important metrics. For example, using formulas, you can pull in the total number of visitors for the period, the occupancy rate, the ADR, RevPAR, and other items, based on the latest information.
While these can be good to include in charts, by making them big and allowing them to stand out as soon as you open up the dashboard, it can help drive the point home even further.
In the example above, I have a list of the current metrics along with the growth rate or comparable percentage from 2019, to help show how the metric is doing compared to that year. You could also add conditional formatting to this to highlight where there is an improvement and where things may have worsened.
Step 6: Finishing touches
Once you’ve got your charts and metrics all on there, the last piece of the puzzle is to add a title as well as any icons or images that may be relevant to help give some added pop to your dashboard. If you go to the Insert tab, you can use that to pull in pictures from the internet. Excel also has built-in icons and stock images that you can use, just by doing a search:
This can be an easy way to help your dashboard stand out even further. Here’s a snapshot of the dashboard I created:
If you liked this post on How to Create a Dashboard to Track Las Vegas’ Visitor Data, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.
A histogram is a type of chart used to visualize the frequency distribution of a dataset. It represents how often different values occur within specific intervals or “bins” in a dataset. Histograms are particularly useful when you want to understand the distribution of continuous or discrete data and identify patterns, trends, or outliers in the data. They provide a clear and concise way to see the shape of the data and assess its central tendency and spread.
What the uses for a histogram?
Frequency Distribution
Histograms help you understand how data is distributed across different ranges or bins, revealing patterns or clusters in the data.
Identifying Outliers
With histograms, you can easily spot extreme values, or outliers, that may skew the chart.
Data Exploration
Histograms are great for data exploration and initial analysis, providing insights that may guide further investigation.
Data Comparison
You can compare multiple datasets or subsets of data to understand differences in their distributions.
How do you define bins for histograms?
One of the most important questions to ask yourself when creating histograms is how the bins should be defined and how big they should be.
Creating bins for histograms involves grouping the data points into intervals or ranges so that you can analyze the frequency distribution of the data effectively. The choice of the number of bins and their width can significantly impact the insights you gain from the histogram. There are various methods to determine the number and width of bins, and some common approaches include:
Square Root Rule
The square root rule suggests that the number of bins should be approximately the square root of the total number of data points. This method provides a simple way to determine the initial number of bins.
Sturges’ Formula
Sturges’ formula is a commonly used method to calculate the number of bins. It suggests that the number of bins (k) can be calculated as follows: k = 1 + log(n) where “n” is the number of data points. Sturges’ formula automatically adjusts the number of bins based on the data size.
Scott’s Normal Reference Rule
Scott’s rule considers the data distribution’s variability and suggests bin width based on the sample standard deviation (σ) and the number of data points (n): bin width = 3.5 * σ / (n^(1/3))
A larger standard deviation or more data points will result in wider bins.
Freedman-Diaconis’ Rule
This method takes into account the data distribution’s interquartile range (IQR) and the number of data points (n) to calculate the bin width.
Bin width = 2 * IQR / (n^(1/3))
The interquartile range is the difference between the 75th and 25th percentiles of the data.
Manual Selection
Depending on your domain knowledge and the specific insights you are seeking, you can manually choose the number of bins and their width. Adjusting the number of bins can highlight different aspects of the data distribution. With Excel, you can also do trial and error to see how many bins may be the best option for your chart.
When determining the bins, you should consider the following points:
Avoid too few bins, as this may oversimplify the data distribution and hide important details.
Avoid too many bins, as it may result in overfitting and obscure the underlying patterns.
Consider the data range and the resolution you want to achieve in the histogram.
Once you have determined the number of bins or their width, you can create the bins in Excel by manually specifying the bin ranges in a new column or using Excel’s built-in histogram function, which will automatically calculate the bins for you based on the data.
Creating a histogram chart in Excel
In creating a histogram in Excel, I’m going to use test scores on an exam as an example. This is an excerpt of my data set:
Here are the step-by-step instructions to creating a histogram chart in Excel.
Step 1. Select the histogram chart
Excel makes it easy to create a histogram. All you need to do is select the entire data set and then click on the option to insert a chart from the histogram section:
As you can see from the preview, Excel has already set up some bins based on the data, so you may not even need to worry about setting them up yourself.
Step 2: Modifying your bins (if necessary)
This is the chart that Excel has created for me based my data set:
It has created bins of equal size based on the data set. However, you may not agree with the cutoffs given they are a bit random (e.g. 8-26, 26-44, etc.). To change this, you can right-click on the x-axis and select the option to Format Axis. From there, there is a section for the different bin options:
The default options is set to automatic. However, in this situation you may want to consider using either a set bin width or changing the number of bins. As you can see from the greyed out numbers, Excel has created 5 bins with a width of 18 each. If you change the bin width to 10, then Excel starts from the lowest value of 8 and adds 10, and continues on:
This has created 9 bins. But suppose you want 10 bins. You can change the number of bins to 10 manually. And when doing so, this is the chart Excel creates:
Now the bin width is set to 9. For test scores, this still may not be ideal, as the cutoffs don’t make logical sense. Ideally, they would be in increments of 10 and be round bin numbers. To fix this, what you can do is to set a bin width of 10. And then, set the underflow bin to 10. This means that anything less than or equal to 10 will fall into the first bin. This becomes a catch all for any values of 10 and under, even if the data starts at 8. Now, the histogram looks like this:
This is a much cleaner look with cutoffs that make more sense. One thing to note is that while there does appear to be an overlap in the bins, that’s not the case. For the (10,20] bin, it counts the number of values that are greaterthan 10 up to and equal to 20. For the (20,30] bin, it counts values greater than 20 that are up to and including 30.
Step 3: Apply formatting (optional)
Once you’re satisfied with the number of bins and their width, the last step is to change the formatting, assuming you want to change the look of it. This can involve changing the histogram’s colors, adding or removing gridlines, adding data labels, as well as any other changes you might normally make to a chart.
In my example, I’ve modified the title, added vertical gridlines, and added data labels to show the frequency count, and also removed the axis to the left:
If you liked this post on How to Make a Histogram 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 me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.
A column chart can be useful in data analysis to show growth, compare values, and display values by period. One of the drawbacks of a column chart is that as you add more data series and values to compare against, it starts to stretch out your chart. And when that happens, the data becomes more difficult to read. One way to conserve some space and to create a nice visual is by embedding one column chart within another. By doing this, you can show relative values. For example, one column chart might show total sales, while the embedded chart can show how much a particular product or geographical area accounted for.
Creating the column chart
In this example, I’m going to use the data set from the following webpage, which shows brewpub openings and closings by year (https://www.brewersassociation.org/statistics-and-data/national-beer-stats/).
I’ve created a table of the data by year:
In this example, I’m going to use the openings as my outer column chart and the brewpub closing values as my inner column chart. This is because I know the closing values will be less than the opening values. When I first create the chart, I get both series showing up side by side:
This is how you might normally look at these values when using column charts. But this time, I want the orange columns to be within the blue ones. Before I merge them together, I’m going to put the other chart on a secondary axis by right-clicking the chart and selecting Change Chart Type. At the bottom, I select Combo and make sure that they are both set to Clustered Column with the Closing series set to the Secondary Axis:
You can see that there is already a bit of an overlap in the charts. But the problem is that the axis have different ranges. To fix this, I’ll click on the secondary axis and select Format Axis. I will adjust it so the maximum value is set to the same as the maximum for the initial axis — 500. After doing that, my chart now looks like this:
This is already getting close what I wanted initially. However, I still want to have more of an embedded effect, for the closing series to be within the opening series. Now, if I right-click on the blue column chart and select Format Data, I’ll have an option to modify the Gap Width. What the gap width does is shrink the amount of white space between the columns. After setting it to 25, it looks like this:
Next, I’ll set the orange columns to a gap width of 80:
The only thing left is possibly changing the color. To show that the two items are related, I prefer to use colors that are similar, with one being a darker shade than the other. I’ll also change the secondary axis font to white so that it is not visible, and add some vertical gridlines. That leaves me with this end result:
If you liked this post on How to Create a Column Chart Within Another Column 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 me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.
A drop-down list is a way you can control a user’s input in Excel, to ensure that they don’t make a mistake when entering in data. It can also serve as a helpful way to make your chart more dynamic. In this post, I’ll show you how that’s possible.
Starting with a regular chart
For this example, I’m going to use the following table in Excel that shows historical World Cup attendance between 1930 and 2018. It shows the total, average, and highest attendance at each tournament:
Now, you could chart this out but the problem is that things can get a bit crowded:
Another issue here is because the chart is looking at total attendance along with average and highest numbers, the scales will distort the chart, making it difficult to compare averages and highest attendances. The solution to this is to use a drop-down list where the user can select which metric they want to see.
Setting up the drop-down list
Creating a drop-down list is simple and it involves just going into the Data tab and selecting the Data Validation button, where you can select the List option and enter all the possible selections you want a user to be able to choose from:
The key is to use the user selection and then populate a column with those values. For example, I’ll set a column header so that it is linked to the drop-down selection. That way, if someone selects Total Attendance, that will be the the header for the new column. I will also use the OFFSET function to determine which of the columns that I’m copying the values over from:
=OFFSET(A2,0,MATCH($F$1,$A$1:$E$1,0)-1)
In the above formula, I’m looking for cell F1 (the header that’s referencing the drop-down selection) within the range A1:E1, to see which one of the headers it matches up with. Using the OFFSET function, I can then pluck the value from the correct column. If I copy the formula down, then my new column will be based on the drop-down selection and it will automatically update based on the selection that is made
And that column, which is highlighted in yellow, is now the only one that is used in my chart. Now, the chart is cleaner and only includes the selected series rather than all three of them:
If you liked this post on Use Drop-Down Lists With Charts in Excel to Make Them Dynamic, 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 key performance indicator (KPI) is a way to track how well you’re progressing towards a particular goal. Oftentimes, you might have many KPIs that you will want to track. You can create these visuals in Excel using donut charts, and below, I’ll show you how you can also have them change color based on whether you’re on track for hitting your goal or not.
Start by categorizing your results
One thing you should consider doing is to create different groups to categorize your results. For example, suppose a key metric was to ensure operating expenses were no more than $10,000 for the current period. If my actual expenses are at $9,000, I would want the chart to show green and to indicate I’m on track versus if my actuals were over $15,000 and I was way over budget.
I can classify these values based on how close they are to the target amount. Here are three categories I will set up and the rules for them:
On Target: If the actual amount is <= 100% of the target.
Slightly Over: If the actual amount is >100% and <=125%.
Well Over: If the actual amount is >125%.
One field I will also create to help track the progress will be % of Target where I take the actual and divide it by the target. Your rules could vary depending on KPI. With expenses, obviously the goal will be to come in under them whereas with sales the incentive will be to come in higher. So you don’t want to assume that your calculations will always be the same in every situation.
I also created a field called Remainder which will capture the unfilled part of the circle. Think of the top half of a circle adding to 1 and the bottom half to another 1, together they total 2. And for the remainder, I use the a formula that takes the maximum of 0 and 2 – the % of Target amount. The purpose of this is to ensure that the remaining amount isn’t negative and that everything adds up to 2.
By creating these classifications, it will be easier to set up the chart to show different colors based on which category a result falls into. Here’s an example of how this might look on Excel. These categories have been created using IF statements based on the rules noted earlier.
The key goal of creating these categories is by ensuring no result shows up in more than one place. For Expense 1, it was on target so that’s the only category it falls under. Expense 2 was 20% higher than the target, so it goes into the ‘slightly over’ category. And Expense 3, which was 50% higher, it falls into the ‘well over’ category.
Now that these categories are set up, I can go about and create the actual chart.
Creating the donut chart
Using the table shown above, I’ll create a donut chart for Expense 1.
This includes all the categories I have set up, which isn’t what I want. There are multiple changes I’m going to make to this chart:
Remove the unneeded fields.
Apply different colors for the categories.
Adjusting the chart so it goes from left to right.
Adding some text boxes.
To remove the fields that aren’t needed, I’ll right-click on the chart and click on Select Data. Then, uncheck the first three field:
Next up, I’ll adjust the colors. The easiest way to do this is to click on the different colors in the legend box:
I’ll click on the blue box for ‘On Track’ series and select the color Green from the Home tab for that (note: you’ll first have to select the legend, and then click on the individual series). After setting all the different colors, this is what my chart looks like thus far:
I still need to adjust the starting point of the chart as the green slice starts from the middle, not the left. To fix this, I right-click on the chart and select Format Data Series. Then, I’ll change the angle of the first slice to 270:
Here you can also change the hole size. The smaller the hole, the larger the slices will be. If I adjust it down to 50%, here’s what my updated chart looks like:
At this point, the legend really isn’t necessary anymore since the colors will do the job and I don’t really need the labels.
One final step you may want to consider is to use a text box instead of a label. Once you’ve added a text box, you can link it to the name of your metric (this can be done through the formula bar). Repeat the same steps for the Actual, and you can have both the name of the metric and the value to automatically update:
When using textboxes, I always format them to remove the background fill and remove the border. You can do this by right-clicking Format Shape and select No Fill and No Line
Now if I were to update the Actuals for Expense 1 to $15,000, pushing me into the ‘well over’ category, my chart would automatically update:
If you liked this post on How to Create and Track KPIs in Excel Using Donut Charts, 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.
Do you have a chart that you want to easily modify the range on, without needing to manually select the data again? Thanks to a new Excel feature, there are now multiple ways you can do that.
1. Creating the chart range as a table
One way you can set up a dynamic chart range in Excel is to put your data into a table. That way, Excel can easily see where you data starts and ends. Suppose you have the following data:
You could show this on a chart but if you needed to add or remove rows from it, your chart wouldn’t automatically re-size. If you deleted data, then there would be gaps on your chart. And if you just added a row, Excel wouldn’t add it to your chart unless you re-selected your range.
To fix this, you can convert you data into a table. To do this, go to the Insert tab on the ribbon and select Table. You may see some default formatting applied afterwards:
Tables will automatically expand as you add or remove data, and formulas will also copy down by default to any new rows. Currently, this is what my chart looks like for this table:
In the below example, you’ll see data being added and removed from the table, and the change to the corresponding chart.
2. Using an array
If you don’t want to convert you data into a table, Excel has now made it possible to dynamically update your chart using just an array, which is new functionality. From the earlier example, I can create an array that populates the data using the following formula:
=OFFSET(A1,0,0,COUNTA(A:A),2)
The first argument reflects the starting point of the data. The next two are left as zero since I don’t want to actually offset the range. The last two arguments indicate the size of the array, and this is key to making the chart automatically update.
By using the COUNTA function, the formula will automatically adjust based on the number of items in that column. That way, if you add or remove items, the offset function will adjust your range. The last argument (2) indicates that the data set is to two columns wide. Now by updating the source data, both my array will update and so too will the chart:
Arrays are not new to Excel but the ability for them to dynamically update a chart is a new feature. As of now, this feature hasn’t fully rolled out to the public and is only available through the Office Insiders program. To get access to that, you can sign up to be an Insider (free of charge) and then moving forward, you will have Excel’s latest and greatest features as soon as they become available.
If you liked this post on How to Create a Dynamic Chart Range in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
Conditional formatting 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.
