charts – Page 2 – HowtoExcel.net

How to Create a Dashboard to Track Las Vegas’ Visitor Data

September 26, 2023

by howtoexcel analysis charts dashboard financial analysis formatting

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:

A table of monthly tourism indicators for Las Vegas.

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:

Selecting all the blank values on a table.

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:

Options for the field list and 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:

Charts in Excel that have background images.

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.

Key numbers displaying above a dashboard.

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.

How to Make a Histogram Chart in Excel

August 1, 2023

by howtoexcel charts statistics

What is a histogram?

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:

Selecting a histogram chart to insert into Excel.

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:

Automatically generated histogram chart in Excel.

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:

Modifying the bins on an excel histogram chart.

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:

Excel histogram chart with bin widths set to 10.

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 greater than 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.

How to Create a Column Chart Within Another Column Chart

June 27, 2023

by howtoexcel charts

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:

Table showing brewpub openings and closings 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:

Column chart showing brewpub closing and opening values.

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:

Setting a secondary axis for a column chart.

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:

Two column charts overlapping one another.

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:

Column charts after changing the gap width.

Next, I’ll set the orange columns to a gap width of 80:

One column chart embedded within another one.

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:

Two blue column charts, one within the other.

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.

World cup attendance figures between 1930 and 2018.

Use Drop-Down Lists With Charts in Excel to Make Them Dynamic

December 20, 2022

by howtoexcel charts dropdown

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:

Excel chart showing World Cup attendance figures.

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:

Excel chart showing World Cup attendance numbers by tournament.

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.

How to Create and Track KPIs in Excel Using Donut Charts

November 29, 2022

by howtoexcel advanced charts financial analysis

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:

Selecting the fields to include in an Excel chart.

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:

Donut chart with green slice indicating progress.

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:

The format data series settings for a chart in Excel.

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:

A donut chart that starts from the left and that has a smaller hole size.

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:

Donut chart with text boxes linking to the description and amount.

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.

How to Create a Dynamic Chart Range in Excel

October 4, 2022

by howtoexcel with No Comment arrays charts

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:

Data that has been converted into a table.

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.

How to Apply Conditional Formatting to Charts in Excel

September 6, 2022

by howtoexcel with No Comment charts conditional formatting

Conditional formatting cells can be an effective way to highlight values so that they can easily stand out. You can apply similar logic to charts, and in this post, I’ll show you how you can use conditional formatting with Excel charts. By doing so, you can highlight gaps and key numbers.

Create more than one series to categorize your results

Excel’s conditional formatting isn’t designed to work on charts. But one way you can still achieve the same results is by categorizing results, and creating a series for each category. Here’s an example, using Amazon’s sales growth. Below are the year-over-year growth rates it has achieved over the past 12 quarters:

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

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

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

However, suppose you wanted to highlight the high-growth periods (30% or more), with the more moderate ones (15%), and the quarters which were below that. To do that, I’m going to add a few more columns and use IF statements to populate the columns based on the growth rate.

Now, if I populate these values on a chart, they shows up like this:

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

These column charts are skinnier and that’s because they are taking up more space as there are three different series for each quarter. To get around this, I can just change the charts so that they are stacked. Since only one of these columns will ever contain a value, there’s no danger they will actually ever stack. But by changing the chart type, they won’t take up as much space.

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

The advantage of this approach is that you don’t even need to rely on the axis to determine what range the growth rate falls within. Although you have to create additional columns by doing this, you can hide any columns that you don’t need to see. You can apply this type of logic to other types of charts as well.

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

How to Create a Chart With a Target Line

August 10, 2022

by howtoexcel with No Comment charts formatting Uncategorized

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:

Chart showing monthly and cumulative sales.

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:

Excel table showing monthly and cumulative sales alongside a target.

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.

Chart showing monthly and cumulative values with a target line.

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:

Changing the dash type for a line chart.

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.

How to Make a Waterfall Chart in Google Sheets

June 28, 2022

by howtoexcel with No Comment charts google sheets waterfall

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:

Amazon's earnings for Q1 2022 and Q1 2021.

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:

Amazon's change in quarterly net income from Q1 2021 to Q1 2022.

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:

Selecting a waterfall chart in Google Sheets.

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:

Changing the fill color of a waterfall chart in Google Sheets.

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:

Amazon's change in quarterly net income from Q1 2021 to Q1 2022, starting with the prior-period net income.

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.

Changing the subtotals in a Google Sheets chart.

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.

How to Create a Chart Showing a Range of Values

May 31, 2022

by howtoexcel with No Comment charts

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:

Dog expense data summarized in an Excel table.

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.

Stacked bar chart showing highs and lows.

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

Setting a bar chart's fill option to no fill.

Upon doing this, that first bar chart disappears:

Stacked bar chart with first bar chart being invisible.

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.