Do you want to create a budget which tracks income and expenses on just one chart? There’s an easy way you can combine them where you can show positive and negative values in a single column. Suppose we have the following income and expenses over a 14-day period:
You might be tempted to plot these values on a simple column chart like this:
This, however, means you’re using up two columns for each day. One for income, and one for expenses. While it is effective, what you can also do is combine the income and expense amounts into a single column. The key is to change the chart type and instead of using the default Clustered ColumnChart, you select the option for a Stacked Chart. By doing this, you’ll now have both values on a single column:
This is still not optimal, however, as now we are just adding the income and expense together. Even though they are color coordinated, there is a better way to display this. Ideally, we will show expenses being a negative outflow on a given day while income will be positive. To fix this, let’s flip the expenses so that they are negative. But rather than doing this manually, you can do this with just a few steps.
To flip values from positives to negatives in Excel, do the following:
1. Enter a value of -1 into a cell.
2. Copy that cell.
3. Select the range which contains the values you want to flip from positive to negative.
4. With those cells selected, right-click and select Paste Special and select Multiply
Doing this will flip your values negative by multiplying all the values by a factor of -1. You can also follow these steps if you want to flip negative values into positives.
After updating the formatting, my table now looks like this:
And now my chart has also updated to show negatives beneath the income.
This ensures I’m not taking up extra space with an extra column and it still makes it easy to compare the outflows versus the inflows.
If you like this post on How to Track Income and Expense in a Single 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.
Heat maps are visual representations of data where individual values are represented by colors. They are particularly useful for identifying patterns, trends, and outliers in large data sets at a glance. By using a spectrum of colors, typically ranging from cool (blue) to warm (red), heat maps make it easy to see which values are higher or lower, helping users quickly understand the data’s distribution and key insights.
Why heat maps are useful
Visual Clarity: Heat maps turn complex data sets into easy-to-understand visual formats.
Quick Analysis: They allow for the rapid identification of trends, patterns, and outliers.
Enhanced Decision Making: By highlighting critical data points, heat maps aid in making informed business decisions.
Comparative Insights: They facilitate the comparison of different data points within a set.
How to create heat maps in Google Sheets
Follow these steps to create a heat map in Google Sheets:
Step 1: Prepare Your Data
Ensure your data is organized in a clear and structured manner. Each column should represent a different variable, and each row should represent a different observation or data point. You should give Google Sheets enough data so that it can determine the location for your data points. In the example below, I have U.S. states listed in column A and values in column B. Since I’ve labeled column A as ‘State’ that gives Google Sheets sufficient information to map the data points. If I had a list of countries, then I would label the header as ‘Country.’
Step 2: Insert the chart
Select any data point on your table and on the Insert menu, select Chart. Google Sheets will create a default chart but you can change it by selecting the Chart Type and then selecting Geo Chart from the Map section.
Step 3: Select the correct map
When the map chart is created, it may not automatically detect the correct area. In my example, it selects the entire world.
The chart looks incorrect as nothing is filled in, but this is because I’m at too high of a level to see the values. I need to adjust my chart to focus just on the United States. To fix this, edit the chart and under the Customize tab, select the Geo settings and change the region to United States.
You can adjust the range per your individual data set. But in this example, since my data has U.S. states only, then I need to select the United States. And once I do so, now my chart is filled in:
Step 4: Adjust the color scales
By default, the chart shows green and red colors for high and low values, respectively. I can customize this in the Geo section as well. You can modify this so that blue colors are for the low values, green for the maximum values, and yellow for the mid-range values:
Step 5: Modify other chart settings
The last step is, as with any other chart, modifying the font, background color, border color, and any other settings for the chart. These can also be changed in the Customize section of the chart settings.
Once you’re done, the heat map chart is ready to go:
If you like this post on How to Create a Heat Map 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 me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.
If you have sales data organized by country, you can create map charts in both Excel and Google sheets. These charts can make it easy to visualize sales and identify patterns and trends. Below, I will compare the different ways to create map charts in Excel and Google Sheets, and highlight any similarities and differences.
For this example, I’m going to use a data set which just includes two fields, one for the country and one for the sales data.
Creating a map chart in Excel
To create a map chart in Excel, all you need to do is click anywhere on your data set and insert a chart. Excel will likely automatically detect the data and recommend a Filled Map as an option. But if it doesn’t, you can select a Map option under the All Charts tab:
You’ll now have a chart that displays the values based on a color scale. In this example, the larger values are in a darker shade of blue whereas the smaller values are in light blue. And if there is no data, the countries are filled in grey.
As with other Excel charts, you can specify a different color scheme and chart layout. In the chart below, I’ve used a theme which has a black background.
By using the dark theme, it makes it easier to focus on areas where there is data, as those countries stand out more prominently. You can also manually adjust the color scheme for the chart by formatting the data series. To do so, right-click on the chart, select Format Data Series and under the option for Series Color, you can specify a 3-color range. And you can adjust what the minimum, midpoint, and maximum values should look like. This logic is similar to how you might set up conditional formatting rules in Excel.
With more colors, readers can now see more variation in visualization.
Creating a map chart in Google Sheets
To create a map chart in Google Sheets, the process is comparable to Excel’s. Simply select a cell on your data set and when you create a chart, select the option for Geo Chart under the Map section
The result is similar to Excel, with the countries being shaded based on their values:
Under the Customize section of the chart settings, you can specify what the max, min, mid values should look like. In addition, you can specify how countries without values should be displayed.
In Google Sheets, you also have a bit more flexibility in how to zoom in on data. In the region drop down, you can specify whether you want to look at the entire world, or narrow in on specific continents.
If I select North America, then I will only get a view of that continent, even if there is data for other countries.
Google Sheets also allows you to create a Geo chart with markers, which is a bit similar but the difference is the countries are not filled in. Instead, there are circles representing the values.
With this type of chart, you can add another field to track the size of the circles. In the following data table, I also have a field for the average sale price.
The average sale prices are highest in North America and smallest in Asia, and that is visually represented in the chart below. In addition to having the colors indicating the overall sales values, I can compare the average prices by looking at the size of the circles.
Overall, creating map charts is easy whether you’re making them in Excel or Google Sheets. In Google Sheets, however, there is some added flexibility, and the ability to use markers allows you to utilize an additional field in map charts.
If you like this post on Creating Map Charts in Excel and 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 me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.
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:
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:
Now I have sales broken down by week which I can import back into Excel.
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:
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:
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:
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:
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.
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.
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.
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.
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.
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.
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:
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.
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.
Right now, the line charts are still a bit too close together:
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.
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.
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.
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:
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:
Now, this data can be put into a chart, which displays those percentages.
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.
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
By setting it up this way, we can now create the following donut chart in Excel:
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.
Comparison charts are invaluable tools in Excel, widely used across business, education, and research to visually represent data. These charts not only simplify complex information but also highlight key trends and comparisons. A comparison chart in Excel is a visual representation that allows users to compare different items or datasets. These charts are crucial when you need to show differences or similarities between values, track changes over time, or illustrate part-to-whole relationships.
In this article, we’ll compare a company’s sales, expenses, and overall profits by year. Here is some sample data:
Types of Comparison Charts in Excel
There are many types of charts you can use in Excel to compare data. Here are a few examples of common charts you might use when comparing data, and how they look:
Bar Chart: Creating a bar chart in Excel starts with selecting your data and choosing the ‘Bar Chart’ option from the ‘Insert’ tab. Bar charts are particularly useful for comparing individual items or categories. To enhance readability, consider adjusting the bar colors and adding data labels. In the bar chart below, you can easily compare sales versus expenses versus profits, and also compare those values by year.
Column Chart: Similar to bar charts but oriented vertically, column charts are ideal for showing changes over time. After selecting your data, choose ‘Column Chart’ from the ‘Insert’ tab. Play with colors and axes to make your chart stand out. Whether you prefer to go with a column chart or a bar chart may simply come down to your preference.
Line Chart: Line charts are perfect for tracking trends over periods. Select your data, click ‘Insert’, and then ‘Line Chart’. Customize your line chart by changing line styles and adding markers for key data points. Line charts may be more useful when there are fluctuations that you want to plot. Here is the chart based on the current sample data:
Here’s a look at the chart when there are greater fluctuations in the data:
Pie Chart: For part-to-whole comparisons, pie charts are your go-to option. After selecting the data, find ‘Pie Chart’ under the ‘Insert’ tab. Enhance your pie chart by experimenting with different slice colors and adding a legend for clarity. This is ideal when you want to compare individual parts of a greater total. Suppose you wanted to analyze what made up the company’s sales. This is where a pie chart might be most appropriate:
Excel has many more charts available for you to use, but these are good starting options when doing analysis. After you’ve selected the right chart, there are further enhancements you can focus on.
Tips for creating effective comparison charts
Here are some tips and things you can focus on to make your charts even better:
Simplify and Focus: Avoid cluttering your chart with too much information. Focus on the key data points you want to compare. This can sometimes mean creating multiple charts instead of trying to fit everything into one.
Use Appropriate Scale and Axes: Ensure that your axes are scaled properly to accurately reflect the differences in data. Misleading scales can lead to incorrect interpretations.
Color and Design: Use color effectively to differentiate data sets and draw attention to key points. However, be mindful of color blindness and avoid using colors that might be hard to distinguish.
Clear Labels and Legends: Use labels and legends that clearly describe what each part of your chart represents. Avoid jargon or abbreviations that might not be understood by all viewers.
Consistent Formatting: Keep formatting like font size, color schemes, and line styles consistent across all charts, especially when they will be viewed together.
Data Integrity: Ensure your data is accurate and up to date. Misleading or incorrect data can harm credibility.
Accessibility: Make your charts accessible to everyone, including those with visual impairments. This can involve using larger text, high-contrast colors, and providing alternative text descriptions where necessary.
Checklist for creating comparison charts
[ ] Chart Type Selection: Choose the most appropriate chart type for your data.
[ ] Data Accuracy: Verify the data for accuracy and relevance.
[ ] Simplification: Remove unnecessary data or split into multiple charts if needed.
[ ] Scaling and Axes: Check that axes are scaled properly to accurately represent the data.
[ ] Color Usage: Use distinct colors to differentiate data sets; consider color blindness.
[ ] Labels and Legends: Ensure all parts of the chart are clearly labeled.
[ ] Consistent Formatting: Maintain consistent formatting across all elements.
[ ] Review for Clarity: Check if the chart conveys the intended message clearly.
[ ] Accessibility Compliance: Ensure the chart is accessible to all audiences.
[ ] Feedback: If possible, get feedback from others to see if the chart is easily understandable.
If you like this post on How to Create Effective Comparison Charts in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.
A good way to gauge the strength of the U.S. economy and how well it is returning to normal level is by looking at Las Vegas’ visitor data. The Las Vegas Convention and Visitors Authority (LVCVA) has plenty of important metrics that it tracks on its website. From the number of visitors to the city to occupancy levels to daily room rates, and other key performance indicators (KPIs). Using data from that website, which you can find here, I’ll guide you through the step-by-stop process as to how you can build a dashboard to track some of those key metrics.
Step 1. Preparing and consolidating the data
One of the most important parts of data analysis is to clean up your data from the beginning. By doing this, you’ll avoid headaches later on. It’ll also make it easier for you to do analysis in the first place. To get a proper glimpse of how Las Vegas is doing now, it’ll be useful to track multiple years. On the LVCVA website, you can download data for multiple years. For this example, I’m going to download data from 2019 through to 2023 YTD.
This is what one of the files looks like:
As of writing this article, data for 2023 is available up until the end of July. Since the data is organized in the same format on all of the files I’m downloading, I can just copy and paste one year after another. The key is for the rows to line up.
But I still need to clean up this data. One problem is that there are gaps between the months. Once I’ve loaded all the years together, I’ll remove those blank columns. The easiest way to do this is the highlight the top row. Then, press F5 and select Special. There will be an option to select Blanks:
Then, all those gaps are selected:
If your right-click on any one of them, select Delete, choose Entire Column, and press OK. Now those columns are gone:
There’s still one problem here. The way the data is structured right now isn’t useful when creating pivot tables. And if you’re creating a dashboard, you’ll want to be able to create pivot tables easily. Doing so can make it easy to create reports on the fly and easy to make changes. It’s easier to have dates going vertically than horizontally to scroll through data. So what I will do is use the TRANSPOSE function to flip it. All that’s necessary here is to use the function and select your entire data set. Then, voila:
Before I make any further changes, I want to convert this into values. Since I used the TRANSPOSE function, it’s sitting as an array. To change this, I’ll select the entire data set, press CTRL+C, and then press CTRL+SHIFT+V to paste as values. If you don’t have that functionality on your version of Microsoft Excel, right-click and select Paste Special and click on Values.
I will also add a few more columns to make the analysis easier. I’ll create a column for the month and year. This will involve using the MONTH and YEAR functions. The only argument that is needed is the original date, which in the screenshot above, appears under ‘Tourism Indicators.’
And since I want to compare 2023 to 2019, I’ll add a column for ‘Current Period’ and ‘Comparable Period.’ The point of this is to make sure that I can filter the current YTD values against the same values from 2019. Since I have data up until July, any comparisons should also run up until July 2019. For the Current Period, I’m using the MAXIFS function to grab the maximum value for the Month field for the current year (I can use the TODAY function to make it dynamically pull in the current year). Then, for the Comparable Period column, I can compare the Month field to see if it is less than or equal to the Current Period. If it is, then I’ll set the value as a “Y” to indicate it falls in the comparable period or “N” if it doesn’t. This way, if I come across month 8 and my current period only goes up to 7, it will mark that as an “N” which will allow me to easily filter out those results.
Lastly, I will convert all this data into a table. The purpose of this is so that I can easily reference the different fields later on, without having to remember column letters. To convert this into a table, select Insert and click on Table. Then, on the Table Design tab, you can name the table something that’s easy to remember. In my example, I’ll refer to it as tblConsolidated.
Step 2: Identifying the KPIs to track
Before rushing out to create the pivot tables, it’s important to know what you want to track. You don’t want to create a pivot table and track everything possible, otherwise it won’t be a useful summary, which is what a good dashboard should aim to do. That’s why you should devote some time to identify what some of your KPIs should be.
There are a lot of metrics on here and these are the ones that I am going to use, which will help gauge how active and busy Las Vegas is:
Visitor volume. Obviously the number of people visiting the city is a great indication of how many people there are.
Occupancy levels. If hotels are booked up, that’s another positive sign that the city is busy.
RevPAR. This takes the room revenue divided by the number of available rooms. It shows how well a hotel is filling up its rooms at a given rate.
Average Daily Rate. This is partly reflected in RevPAR but it can be a useful indicator as people are more familiar with room prices than they are with RevPAR, especially those who visit Las Vegas often.
En/Deplaned Passengers. This is a helpful metric to know how much out-of-town traffic there is coming to the city.
Average Daily Auto Traffic. With this metric, readers can see how busy the roads are.
Gaming Revenue (Las Vegas Strip). This is another important KPI because it tracks how much people are spending at casinos.
Step 3: Creating the pivot tables
Now it’s on to creating a pivot table for each KPI you want to track. To make this process easier, just create a pivot table one time, and then copy it for as many charts that you want to create. This way, you don’t have to go back and select Insert->Pivot Table over and over again. Just make sure to leave enough room so that they don’t overlap, otherwise you’ll encounter errors.
It’s also a good idea to label your pivot tables by going into the PivotTable Analyze tab. For a pivot table to track visitor volume, you might want to call that ptVisitorvolume, for example. This will be helpful later on if you want to change charts and aren’t sure what PivotTable1 relates to. You’ll also likely want to change the default formatting for a pivot table:
To change the format, don’t just highlight the cells and make the changes, otherwise they’ll revert back once you update the data. Instead, right-click on one of the values and select Value Field Settings. Then, select Number Format and apply the formatting you want to apply to that field.
What I also like to do is put all the pivot tables on a separate tab to keep them organized, while all the charts will go on a main tab dedicated for the dashboard.
Step 4: Creating the charts
When creating your charts, one thing to consider is how you want the data to be visualized. You can do this as part of the stage to identify KPIs. For visitor volume, for example, I’ll use a line chart since I want to see the month-over-month progression. This will also make it easy to compare against multiple years.
Since these are charts created from pivot tables, they are pivot charts, and they come with drop-down options:
They aren’t terribly appealing and to get rid of them, click on the chart, select the PivotChart Analyze tab and unselect the option for Field Buttons:
One thing that can help with creating charts is by using Excel’s existing Chart Styles, which are on the Design tab (which is visible if the chart is selected):
This can be an easy way to customize your charts without having to do so manually.
You may also want to adjust how the data is displayed. Visitor volume, for example, may make sense to leave as the default, which is a summation. But when looking at ADR or RevPAR, you wouldn’t want to sum those values up. Instead, you may want to calculate the average instead. To do that, right-click on one of the fields and select Summarize Values By and select Average
Now, you’ll see an average based on period, which makes more sense than summing up prices.
At this point, it comes down to your personal preferences as to how you want to design the charts, and it would be far too deep to try and get into all those options here. However, I’d suggest mixing up a bit of bar and column charts and also changing up the colors so your dashboard doesn’t look like the same item over and over.
Some additional things you may want to consider are:
Adding data labels. And if you do use them, consider not using axis labels;
Using legends where and when make sense to do so;
Adding background images to your charts to have a different look and feel to them;
Having descriptive titles to help summarize what the chart is displaying;
Not plotting too much on on chart. You may want to consider plotting years instead of months;
Not using a border color so that your charts blend in with the background.
Here are a couple of charts I created with images in the background to make it clear what they are showing:
Step 5: Adding key numbers at the top for further emphasis
Charts are good, but what can also be useful is to put key numbers right at the top so that readers don’t have to spend much time looking for the most important metrics. For example, using formulas, you can pull in the total number of visitors for the period, the occupancy rate, the ADR, RevPAR, and other items, based on the latest information.
While these can be good to include in charts, by making them big and allowing them to stand out as soon as you open up the dashboard, it can help drive the point home even further.
In the example above, I have a list of the current metrics along with the growth rate or comparable percentage from 2019, to help show how the metric is doing compared to that year. You could also add conditional formatting to this to highlight where there is an improvement and where things may have worsened.
Step 6: Finishing touches
Once you’ve got your charts and metrics all on there, the last piece of the puzzle is to add a title as well as any icons or images that may be relevant to help give some added pop to your dashboard. If you go to the Insert tab, you can use that to pull in pictures from the internet. Excel also has built-in icons and stock images that you can use, just by doing a search:
This can be an easy way to help your dashboard stand out even further. Here’s a snapshot of the dashboard I created:
If you liked this post on How to Create a Dashboard to Track Las Vegas’ Visitor Data, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.
A histogram is a type of chart used to visualize the frequency distribution of a dataset. It represents how often different values occur within specific intervals or “bins” in a dataset. Histograms are particularly useful when you want to understand the distribution of continuous or discrete data and identify patterns, trends, or outliers in the data. They provide a clear and concise way to see the shape of the data and assess its central tendency and spread.
What the uses for a histogram?
Frequency Distribution
Histograms help you understand how data is distributed across different ranges or bins, revealing patterns or clusters in the data.
Identifying Outliers
With histograms, you can easily spot extreme values, or outliers, that may skew the chart.
Data Exploration
Histograms are great for data exploration and initial analysis, providing insights that may guide further investigation.
Data Comparison
You can compare multiple datasets or subsets of data to understand differences in their distributions.
How do you define bins for histograms?
One of the most important questions to ask yourself when creating histograms is how the bins should be defined and how big they should be.
Creating bins for histograms involves grouping the data points into intervals or ranges so that you can analyze the frequency distribution of the data effectively. The choice of the number of bins and their width can significantly impact the insights you gain from the histogram. There are various methods to determine the number and width of bins, and some common approaches include:
Square Root Rule
The square root rule suggests that the number of bins should be approximately the square root of the total number of data points. This method provides a simple way to determine the initial number of bins.
Sturges’ Formula
Sturges’ formula is a commonly used method to calculate the number of bins. It suggests that the number of bins (k) can be calculated as follows: k = 1 + log(n) where “n” is the number of data points. Sturges’ formula automatically adjusts the number of bins based on the data size.
Scott’s Normal Reference Rule
Scott’s rule considers the data distribution’s variability and suggests bin width based on the sample standard deviation (σ) and the number of data points (n): bin width = 3.5 * σ / (n^(1/3))
A larger standard deviation or more data points will result in wider bins.
Freedman-Diaconis’ Rule
This method takes into account the data distribution’s interquartile range (IQR) and the number of data points (n) to calculate the bin width.
Bin width = 2 * IQR / (n^(1/3))
The interquartile range is the difference between the 75th and 25th percentiles of the data.
Manual Selection
Depending on your domain knowledge and the specific insights you are seeking, you can manually choose the number of bins and their width. Adjusting the number of bins can highlight different aspects of the data distribution. With Excel, you can also do trial and error to see how many bins may be the best option for your chart.
When determining the bins, you should consider the following points:
Avoid too few bins, as this may oversimplify the data distribution and hide important details.
Avoid too many bins, as it may result in overfitting and obscure the underlying patterns.
Consider the data range and the resolution you want to achieve in the histogram.
Once you have determined the number of bins or their width, you can create the bins in Excel by manually specifying the bin ranges in a new column or using Excel’s built-in histogram function, which will automatically calculate the bins for you based on the data.
Creating a histogram chart in Excel
In creating a histogram in Excel, I’m going to use test scores on an exam as an example. This is an excerpt of my data set:
Here are the step-by-step instructions to creating a histogram chart in Excel.
Step 1. Select the histogram chart
Excel makes it easy to create a histogram. All you need to do is select the entire data set and then click on the option to insert a chart from the histogram section:
As you can see from the preview, Excel has already set up some bins based on the data, so you may not even need to worry about setting them up yourself.
Step 2: Modifying your bins (if necessary)
This is the chart that Excel has created for me based my data set:
It has created bins of equal size based on the data set. However, you may not agree with the cutoffs given they are a bit random (e.g. 8-26, 26-44, etc.). To change this, you can right-click on the x-axis and select the option to Format Axis. From there, there is a section for the different bin options:
The default options is set to automatic. However, in this situation you may want to consider using either a set bin width or changing the number of bins. As you can see from the greyed out numbers, Excel has created 5 bins with a width of 18 each. If you change the bin width to 10, then Excel starts from the lowest value of 8 and adds 10, and continues on:
This has created 9 bins. But suppose you want 10 bins. You can change the number of bins to 10 manually. And when doing so, this is the chart Excel creates:
Now the bin width is set to 9. For test scores, this still may not be ideal, as the cutoffs don’t make logical sense. Ideally, they would be in increments of 10 and be round bin numbers. To fix this, what you can do is to set a bin width of 10. And then, set the underflow bin to 10. This means that anything less than or equal to 10 will fall into the first bin. This becomes a catch all for any values of 10 and under, even if the data starts at 8. Now, the histogram looks like this:
This is a much cleaner look with cutoffs that make more sense. One thing to note is that while there does appear to be an overlap in the bins, that’s not the case. For the (10,20] bin, it counts the number of values that are greaterthan 10 up to and equal to 20. For the (20,30] bin, it counts values greater than 20 that are up to and including 30.
Step 3: Apply formatting (optional)
Once you’re satisfied with the number of bins and their width, the last step is to change the formatting, assuming you want to change the look of it. This can involve changing the histogram’s colors, adding or removing gridlines, adding data labels, as well as any other changes you might normally make to a chart.
In my example, I’ve modified the title, added vertical gridlines, and added data labels to show the frequency count, and also removed the axis to the left:
If you liked this post on How to Make a Histogram Chart in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.