AutoUpdateChartsinPQ

Automatically Update Your Excel Chart With New Data Using Power Query

When you add data to an Excel spreadsheet, with regular charts, you often need to trigger a refresh to make sure that your chart reflects the latest data. But, by using Power Query, you can automate that process without the need for macros. In this post, I’ll walk you through the process of getting your data into Power Query, and how to set it up so that your charts will update automatically.

Step 1: Importing Data into Excel with Power Query

The first step involves pulling data into Power Query. This can be from various sources like databases, web pages, or local files. In this example, I’m just going to use data that’s on another sheet, but it can also be from another workbook. Here is an excerpt of some sales data since the start of the year:

Table showing daily sales data in Excel.

To get this into Power Query, I just need to click on any of the cells in the table and then under the Data tab, under the Get & Transform Data section, select the From Table/Range option. Now my data is in Power Query.

But before loading the data back into Excel, I’m also going to group the totals by week. To do this, I’ll click on the Group By option in the Home tab in Power Query. I’ll create a column name called Weekly Total and sum the Sales Amount:

Grouping sales by week in Power Query.

Now I have sales broken down by week which I can import back into Excel.

Sales data in Power Query broken down by weeks.

At this stage, I’ll click on Close & Load. Now I have another table of the data in Excel, this time, linked to Power Query and broken out by week:

Sales data from Power Query broken down by week.

Step 2: Creating a Chart from Imported Data

Next, let’s go ahead and create a chart to show these daily sales totals. For this example, I’ll use a simple column chart showing the weekly sales. To do this, click on your data set anywhere and on the Insert tab, select the option for a Column Chart. After applying some formatting, this is what my chart looks like:

Chart showing weekly sales data in Excel.

Step 3: Setting Up Automatic Refresh

With my chart and table now setup, I can go ahead and set the automatic refresh. When a query is created in Power Query, you will see it under the Queries & Connections pane. To show this pane, go under the Data tab and click on Queries & Connections. Then, right-click on the query and select Properties. You’ll now see the following options:

Query properties in Excel.

As you can see, there is an option to specify how often you want to refresh the data. You can have it refresh when you click on the Refresh All button but you can also set it to refresh when the file first opens. And you can even specify it to refresh every few minutes. I can even set it to refresh every minute:

Query properties in Excel set to refresh every minute.

Depending on how often your data may change, you may want to adjust this accordingly. But one thing to keep in mind is that whatever changes you made in Power Query, refreshing the query will trigger all those steps, which can make it time consuming if there are many steps for the query to go through.

But by setting up a rule to refresh every x number of minutes, you can have control over how often your data updates. And since it’s linked to a chart, your chart will also automatically update.


If you like this post on How to Automatically Update Your Excel Chart With New Data Using Power Query please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.

SecondAxis

How to Add a Second Y-Axis in Microsoft Excel

Using a second Y-axis in Microsoft Excel can significantly enhance your data presentation, particularly when dealing with variables of differing scales. This guide will walk you through the steps to add a second Y-axis in Excel, helping you display your data more clearly and effectively.

Why Use a Second Y-Axis?

1. Scale Variation: When charting different types of data together, one variable might be significantly lower or higher in magnitude than the other. Using two Y-axes allows each variable to be scaled according to its own range, making the chart easier to read and interpret.

An Excel chart showing website visitors versus page views.

The above chart shows website visitors, which are measured in thousands, versus page views, which are in tens of thousands. By using a second axis, it makes it possible plot both of the values on the chart without making one series look far smaller than the other.

2. Different Units: If your data variables are measured in different units (e.g. dollars versus percentages), a second Y-axis can help represent each in an appropriate context without confusing the reader. A common example may be where you want to plot the revenue on one axis and the year-over-year growth rate on a separate axis, as is the case in the chart below.

An Excel chart showing monthly revenue and a year-over-year growth rate.

3. Clarity and Emphasis: Using a second Y-axis can help emphasize the relationship between two different variables, making your analysis clearer and more impactful. In the following example, it’s easy to see the relationship between a rising customer satisfaction score and higher product sales.

An Excel chart showing product sales versus customer satisfaction.

Step-by-Step Process to Add a Second Y-Axis

Here’s how you can add a second y-axis to your charts.

Step 1: Prepare Your Data

  • Organize your data in Excel with your independent variable (e.g., time, dates, categories) in one column and the dependent variables in adjacent columns.

Step 2: Create a Combo Chart

  • Highlight your data range.
  • Go to the Insert tab.
  • Click to expand the Charts section and select the Combo chart from the bottom.

Step 3: Add the Secondary Axis

  • When selecting your chart types, check off the option for a Secondary Axis for at least one of the series.
  • While it’s not necessary to use a different chart type, setting it up that way can be helpful to distinguish the values more easily from one another.
Selecting a secondary axis for a chart.

Step 4: Customize the Secondary Axis

  • Once your chart is loaded into Excel, click on the secondary axis (now visible on the right) to select it.
  • Right-click and choose Format Axis to open the Axis Options pane.
  • Adjust scale options such as minimum and maximum values, tick mark spacing, and number formats to better align with the secondary data series.
  • You can also add axis titles by selecting the chart and clicking the Add Chart Element option from the Ribbon (under the Chart Design tab). Then, select Axis Titles and either Secondary Vertical for the secondary axis or Primary Vertical for the primary axis.

Adding a second Y-axis in Excel can turn a confusing overlap of data into a clear and insightful visualization, perfect for presentations or in-depth analysis. By following these steps, you can master the use of dual Y-axes in your charts, making your reports and presentations more professional and effective.


If you like this post on How to Add a Second Y-Axis in Microsoft 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.

MultipleLineChartsGrowthvsActual

Create Multiple Line Charts in Excel Showing Actuals and Growth Rates

A line chart in Excel can be a great way to display a time series in Excel. But did you know that you can show a series’ actual values along with the year-over-year growth rate, or change? By using a second axis and adjusting the scales, I’ll show you how you can create this effect in Excel.

Setting up the data

For this example, I’m going to use the personal savings rate as an example, specifically, to show what it was from 2017 until the end of 2019, and how much it changed. The raw data shows me the savings rate per month:

Personal savings rate by month, shown in Excel.

To calculate the year-over-year change, I’ll add a formula to determine the difference. In this case, I’m going to show the change in percentage points. To do this, all that’s necessary is to take the current month’s savings rate and deduct the savings rate from the same period last year.

Personal savings rate by month along with the percentage change, shown in Excel.

Plotting the data into a line chart

Next, after selecting one of the data points, I can go into the Insert tab to select a chart. To balance both of these line charts, it’s important to put each series on a different axis. When selecting a chart, got to the Combo section and have line charts selected for each series, but put one on a Secondary Axis.

Plotting line charts on multiple axis.

Right now, the line charts are still a bit too close together:

Two line charts displayed in Excel.

To create more a buffer, the next thing I’ll do is adjust the axis scales. This can be done by right-clicking on an axis and selecting the option to Format Axis. The key is to stretch one axis so that it doesn’t overlap with the other one. For the orange line chart (showing change), I can adjust the scale so its minimum value is -10 and the maximum is +30. This pushes the line chart down to within a fairly narrow range.

Two line charts showing on two different axis.

Next, I’ll add some data labels to show the values. I’ll also adjust the savings rate so it starts at the same point as the line showing the year-over-year change. Now, it’s easier to see both the actual savings rate and the change, displaying both the actual and the year-over-year change.

Two data series in Excel plotted on line charts showing the growth rate and the actuals.

If you like this post on how to Create Multiple Line Charts in Excel Showing Actuals and Growth Rate 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.

ProgressChart

Create Progress Charts in Excel

Whether you’re managing projects or working towards a goal, visualizing progress is important to ensure you’re on track for meeting your target. And by using charts in Excel, you can easily track your progress, making it easy for you to view and share it with your stakeholders. This post will walk you through the steps to create insightful progress charts in Excel.

Using a bar chart to track progress

One example where you might want to track progress is if you’re tracking how much progress a sales rep may be making with a customer account. You may have various stages in the process, such as making an initial contact, obtaining an in-person interview with management, all the way to getting a signed and approved contract. Rather than having someone verbally track this progress for you, you can use a combination of checkboxes and charts to help visualize progress.

With the help of Excel’s checkboxes, we can create a table which looks like this, making it easy to simply tick off boxes to indicate progress by prospect:

Table showing sales rep progress by prospect.

This table, while it’s helpful, isn’t easy to visualize the progress. Using a COUNTIF function, we can count the number of times a checkbox is set to TRUE. With 6 possible values, if there are 6 checkboxes ticked off, that tells us the sales rep has fully completed all the stages in the funnel and the deal is now closed. By then dividing this number by 6, we can numerically display the percentage of completion:

Table showing sales rep progress by prospect along with a percentage of completion.

Now, this data can be put into a chart, which displays those percentages.

Progress chart showing a sales funnel's percentage of completion.

Another way to display this progress is by adding another field, simply to show the total number of stages. Then, we can plot on a chart the # of checkboxes that have been ticked off, along with the number of total stages.

Progress chart showing overlapping bar charts to indicate the level of progress.

To make this work, make sure you do the following:

  • Select both the series for the # of checkboxes ticked off, and total stages. When selecting the data, make sure that the the field which contains the total is on top, in the legend entries.
  • Format the data series and ensure that series overlap is set to 100%. This way the bar charts will completely overlap with one another.

Creating a circle progress chart

Bar charts can be effective when you want to track multiple projects and tasks at once. But if you want to track just one project individually, or your overall total progress, then a circle chart may be more effective for that purpose.

Going back to the previous example, let’s suppose we want to track the progress of one prospect at a time. Company A is at 83%. Here’s how we could show that on a circle chart. We can display the information in the following way, to show what the progress is, in both raw numbers and as a percentage

Excel table showing progress and remaining percent.

By setting it up this way, we can now create the following donut chart in Excel:

Donut chart in Excel showing the level of progress.

With the percent %, I used that as a data label to put within the middle of this chart.

Using a donut chart, you can easily set up progress for one particular project. But it doesn’t have to be for just one particular item; this can also be part of a greater set of key performance indicators.


If you like this post on How to Create Progress 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.

ComparisonCharts

How to Create Effective Comparison Charts in Excel

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:

Excel table showing sales, expenses, and profit by year.

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.
A bar chart in Excel.
  • 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.
A column chart in Excel.
  • 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:
A line chart in Excel.

Here’s a look at the chart when there are greater fluctuations in the data:

A line chart in Excel with fluctuations.
  • 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:
A pie chart in Excel that compares revenue by product.

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.

ExcelDashboardTitle

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

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:

Selecting all blank values in a range.

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:

A table after removing blank columns.

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:

A transposed table.

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:

An unformatted 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:

A line chart showing values by year.

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):

Excel's chart styles.

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:

A table of icons built-in within Excel.

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.

HistogramChart1

How to Make a Histogram Chart in Excel

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:

Test scores in Excel.

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:

Excel histogram chart with 10 bins.

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:

Histogram chart with an underflow bin.

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:

Histogram chart with data labels.

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.

ChartWithinaChart

How to Create a Column Chart Within Another Column Chart

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

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:

World cup attendance figures between 1930 and 2018.

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:

Creating a drop-down list in Excel.

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.

H2EKPIs

How to Create and Track KPIs in Excel Using Donut Charts

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.

Table categorizing KPIs in Excel.

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.

Donut chart in Excel.

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:

  1. Remove the unneeded fields.
  2. Apply different colors for the categories.
  3. Adjusting the chart so it goes from left to right.
  4. 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:

A chart legend showing different colors.

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

The format shape settings in Excel.

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.