If you have values that you are plotting on a chart and some of them are zeroes, you will likely notice your chart sliding all the way down to the bottom. It can be problematic when you are entering in year-to-date data which will inevitably lead to blank or zero values. That’s why in this post, I’ll show you how to hide zero values from showing up on a chart in Excel.
Let’s start with the following example:
In this case, we have values for just a few months. From April through to December, the values aren’t necessarily zero — we just don’t have data yet. But the problem is that on the chart, the line graph shows them as being zeroes. If we were to get rid of those zero values, it would fix the issue:
But the problem is that this may not be a convenient solution. If you want to create formulas to calculate the totals for each month, going back and deleting the ones with no values and remembering to put the formulas back in for future months isn’t going to be a very convenient option. There is a way that the calculations can be adjusted so that you can still get the zero values not to show.
Let’s suppose you have a SUMIF calculation for each month which looks as follows:
One way to fix this issue is to add an IF statement to avoid the zero values. But returning a blank value won’t fix the issue. Instead, what we’ll want to do is return an #N/A value. To do that, you just need to use the following formula:
=NA()
That just needs to be incorporated into the formula to say that if the sum is equal to 0, an NA value is returned:
=IF(SUMIF(E:E,A3,F:F)=0,NA(),SUMIF(E:E,A3,F:F))
Although this solves the problem, it creates a bit of an eyesore with the #N/A values showing up in our data set. If you want to get rid of that, there’s a solution for that as well. Using conditional formatting, we can adjust the values so that any #N/A values show up blank. To do this, I’ll select column B and under the Conditional Formatting in the Home tab, select the option for a New Rule:
Select the option to Use a formula to determine which cells to format and enter the following:
=ISNA(B1)
I need to use B1 since that is the start of the range that I have selected. Next, you can just click on the Format button and set the font color to white so that the #N/A values don’t show up. This is what my sheet and chart looks like after applying the formatting:
If you liked this post on How to Hide Zero Values on an Excel Chart, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
It’s time for an updated dashboard post. My original post is now three years old and probably overdue for an update. This time around, I’m going to start from scratch using a real data set from the Bureau of Labor Statistics, where I’ll walk you through my process from start to finish. To follow along, you can download the data I’m going to use from here (I’m going to use the 2020 state data. This is the XLS link).
Preparing the data
If your data is no good, then it won’t matter how great your charts and visuals look. That’s why it’s important to have a look through the data to see how usable it is. And you may not notice any issues until you start populating your charts. But one of the things that are noticeable right of the bat in this data set is that instead of empty values on this sheet, there are # or * signs.
That’s going to be a problem if you want to do any computations on this data. You can use Find and Replace to replace the data with empty values. Note that for the *, you’ll need to find ~* rather than just *, otherwise Excel will interpret the * as a wildcard and find everything.
One other thing that I am going to do is create another column for the occupation titles. In column J, there are more than a dozen titles for the ‘major groups’ (major is indicated in column K). I am going to create a table to group them even further. I’ve put this on a separate lookup sheet:
Now, what I am going to do is insert a column on my main data sheet, after column J, which will do a lookup on this table. The formula will be as follows:
=IF(L2=”major”,VLOOKUP(J2,Sheet1!A:B,2,FALSE),””)
Now, I have a category field in column K for the ‘major’ group classifications:
Next, I’m going to convert the data into a table. To do this, click on any of the cells in your data set, and on the Insert tab, click on Table:
Once done, you should notice some default table formatting gets applied to your data set:
And to make it easy to reference, I’m going to click on the Table Design tab, and under the Table Name section on the left, I’m going to re-name the table to tblData:
To change the name of a table, all you need to do is click on it and make your changes, then press enter.
Creating the pivot tables
For this dashboard, I’m going to create pivot tables and use charts to show the following:
Median salary for the specified position.
Wages by percentile.
Median salary for the specified state based on job categories.
A pie chart showing how many jobs there are by category.
A gauge chart showing how the median salary compares to the national average.
A map chart showing the median wages by state.
Median salary for the specified position
To create this visual, I’m going to create a pivot table from the tblData and put it on a new ‘PT’ tab. For this, I am just going to take the average of the A_MEDIAN column. I will also filter the O_GROUP field so that it only includes the ‘detailed’ group to avoid including the categories. I will also adjust the formatting so that it uses the accounting format. The pivot table itself contains just one value:
I only want this value to show up in a box but what I’m going to do is create a column chart from this. For just the number to be visible, I’m going to add a data label and then remove everything else, including the legend, gridlines, and make the column a clear color. Lastly, I’ll copy my first visual onto a new ‘Dashboard’ tab and put the words ‘Median Salary’ directly above it:
Wages by percentile
Next, I’m going to create a bar chart that shows the wages for a position by the various percentiles that are in the data set. For this, I’m going to grab all the different percentile fields, including the median:
A_PCT10
A_PCT25
A_MEDIAN
A_PCT75
A_PCT90
I’ll need to set these calculations to be averagesjust like on the earlier calculation. I can re-name these to ’10th percentile’, ’25th percentile’, and so on, to make it easier to read. Then, I’m going to create a 3-D bar chart, change the colors, and add some labels so it looks like this:
Median annual wage for the specified state based on job categories
Now, I’m going to create a pivot table and chart to show what the median annual wage is across the different categories I specified earlier for the selected region. This is a simple pivot table set up, all that’s needed is the A_MEDIAN average in the values section of the pivot table, the CATEGORY in the rows, and the O_GROUP to filter just the ‘major’ jobs. This will result in the creation of the following column chart:
A pie chart showing how many jobs there are by category
One of the interesting metrics in the data set is the number of jobs there are per 1,000 jobs in the given region. This is going to be similar to the previous chart, except this time I am going to use the JOBS_1000 field. I’m going to use a pie chart for this visual just to change it up a little bit.
A gauge chart showing how the median salary compares to the national average
I’m going to use a gauge chart to compare the median salary against the national median and how it compares. For detailed steps on how to create a gauge chart, please check out this post. For this visual, I need to create one pivot table just for the national median wage. To do this, I just need to grab the median value and filter the O_GROUP by ‘total.’
For the actual gauge chart, I need to set up a table for the slices and the ranges. I will go with a setup as follows:
The % of completion will take the median value and divide it by the national average. But to avoid it going over 100, I’ll use the MIN formula. And for the ‘high end’ value, I take 200 (think of 100 as the top half of the circle and the other 100 the bottom half) and subtract the % of completion and add the size of the slice. Here is what it looks like when the median salary is greater than the national median:
This is what the gauge chart looks like once it’s been set up following the steps in the previous post:
A map chart showing the median wages by state
Creating a map chart is pretty easy in this situation because we have all the state names and all I need to do here is create a pivot table with the A_MEDIAN value. Here’s what my pivot table looks like:
However, you can’t create a pivot chart directly from a pivot table. But there is a way around that. I’m going to create another table that copies the values from the pivot table. They simply equal the values to the left:
Now, I can create a map chart based on this table:
I now have all of my charts set up:
What’s next is to set up the slicers.
Adding and linking the slicers
I’m going to add two slicers for the dashboard, one for the state and one for the job title.
To insert a slicer, all that’s necessary is to click on any one of the pivot tables and on the Insert tab, click on the Slicer button:
Then, select the fields you want to add. Generally, I add the fields that have the most selections and longest names going down vertically. In this case, that’s the OCC_TITLE field. For the State and Category slicers, I have those going across:
I’ve also added a title just below the slicers to give the dashboard a name. The last piece of the puzzle here is to link the slicers to the pivot tables. Previously, I linked them to all of the tables. But for some of these charts, I don’t want them to link to everything.
For the State and Category slicers, I want them to update everything except the national median pivot table. And for the OCC_TITLE slicer, it should also not update the jobs per 1000 pivot table or the median wage by category. The reason being is that those charts will lose their value if only one job is selected, as the point is they should give an overview of the different categories. Similarly, you could also unlink the state slicer to the map chart.
To manage these connections, you can slicer and select Report Connections:
From there, you can select with pivot tables you want the slicer to link to:
And to keep your slicers from staying in put despite any changes, you can also right-click and select Size and Properties and then select the option to Don’t move or size with cells:
Now, the dashboard is ready to go!
If you liked this post on Making Dashboards in Excel With Map and Gauge 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.
A box plot chart can show you lots of information in just one visual: the minimum, maximum, median, and interquartile range of a data set. It can be a great way to visualize your data to see its range and how narrow or broad the values are. In this post, I’ll show you a couple of ways to create a box plot. The first is through the box and whisker chart on newer versions of Excel, and also how you might create this just from a stacked chart.
For this data set, I am going to create some random test results to compare an easy test versus one that is average, and another that is difficult.
Creating the box and whisker chart
This is the sample data I’m going to work with for this example:
The way the data is formatted, it is already ready to use in a box and whiskers chart. Simply click anywhere on the data set and on the Insert tab, you can click on the pop out button for Charts or just click on the Recommended Charts:
Then, under the All Charts section, there is an option for Box & Whisker. Click on the only chart available in that section:
And then that will put your data into a box plot:
Besides adjusting the range so that it does not go higher than 100, the box and whiskers chart is ready to use immediately with minimal adjustments. From the above chart, we can see that the easy exam had the smallest range, highest min and max values, and a broader interquartile range than the moderate exam — this suggests more variability. The real value in box plots is in being able to compare them against other data sets.
Creating a box plot using a stacked chart
Another way to make a box plot in Excel is by using a stacked chart. This involves more steps but it allows you to make this work on older versions of Excel. If you have trouble following along, you can download the sheet I created for this purpose here.
First, we’ll need to organize the data by quartiles. A table is needed that starts off with the minimum value, and then the size of each quartile. For the minimum value, you can just use the MIN function and put in the entire range in there. The quartile functions may be a bit unfamiliar for many users but the calculations aren’t complex. The QUARTILE.INC function would look as follows if you want to pull in the first quartile:
=QUARTILE.INC(A1:A100,1)
If your range is in A1:A100, then the above formula would return the position of the first quartile. Changing the last argument to 2 and then 3 will give you the position of the remaining quartiles (you don’t need quartile 4 for this calculation). Once you have the value of each of the quartiles, then the next step is to calculate their respective sizes.
For the first quartile, you’ll take the first quartile and subtract the minimum value. The size of the next quartile will be the Median value (for this you can use the MEDIAN function) less the first quartile. The third quartile size will take the third quartile and subtract the median. Finally, the last quartile size will take the maximum value and subtract the third quartile.
Based on those calculations, the table that will be used in the stacked chart is as follows:
If I go to create a stacked chart using that table, by default it will look like this:
The first thing I need to do is flip the rows/columns. To do this, right-click on the chart and click Select Data. And then, click on the button that says Switch/Row Column, which will transform the chart into this:
This is a bit better but I still need to remove the min section out of sight. To do this, I can right-click on that part of the chart and change the fill color to be blank. Then, my chart starts from the minimum value, rather than from 0:
For the bottom (orange) series, I will do the same and change the fill colour to orange. I will also take an additional step afterwards and under the Chart Design tab, select Add Chart Element and click on Error Bars and then Standard Deviation:
That will create the following line leading up to the next quartile:
However, this is not exactly what is desired since it should only start from the minimum value. To correct this, right-click on the error bar and click Format Error Bars and change the settings to the following:
Direction: Minus
End Style: No cap
Error Amount: Percentage: 100%
Now, the line starts from the minimum:
Follow the same steps for the blue quartile range at the top and then the chart will look as follows:
The last steps are really optional but I will get rid of the legend and also change the colors and outlines of the yellow and grey quartiles so they are all one color. My finished box plot looks as follows:
If you liked this post on How to Make a Box Plot in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
A big advantage of using Google Sheets is that the data is readily accessible online and you don’t need to worry about if people are running different versions of it like you would with Excel. One of the areas where it may be lacking is in creating dashboards. Although you can incorporate slicers, they’re not as user-friendly or nice looking as what you would get in Excel. But in this post, I’ll go over how to make dashboards in Google Sheets quickly and easily.
Here is a sample of what my data set looks like. If you want to view the data plus the dashboard I created here, you can check out the Google Sheets file here.
Step one is to create some pivot tables. Like with Excel, I prefer to create a pivot table for each view that I want. I will set up four pivot tables, categorizing sales by:
Store
Salesperson
Product
Date
To keep things simple, you can put each one of those fields in the ROW section while the sales can be in the VALUES section:
When creating the pivot tables, be sure to un-check the option to Show totals (this is so that they don’t show up in the charts):
What you may want to do is create one pivot table and then copy and paste others, and just change the rows. One additional step you will need to do for the pivot table that contains the dates is to also group them by month. To do that, right-click on any of the dates and select Create Pivot Date Group:
Then, from the following menu, select Year-Month:
This is how your pivot tables might look like once you are done:
Where you put these pivot tables isn’t important. The key is leaving enough space between them so that they don’t potentially overlap should your data get bigger. Otherwise, you will run into errors and have difficulty updating your data. Since my pivot tables won’t get any wider based on the selections I’ll make, there doesn’t need to be any extra columns between any of them.
Now that the pivot tables are set up, the next step is to set up the different charts for each of them. For the sales by store, I will create a pie chart to show the split among the stores:
The one thing you will want to pay attention to for each chart is the range. Since your pivot table could expand, it’s a good idea to make the range bigger than it needs to be, even if it will contain blank values. For example, changing this:
To this:
This will ensure that additional data gets picked up by the chart should your pivot table get bigger. This is also why it is important to ensure you don’t place any other pivot tables below one another. Ideally, you’ll want to keep them side by side rather one on top of the other.
For the pivot table that shows sales by salesperson, I’ll use a bar chart since the names can be long:
For the product sales, I’ll mix it up and have those as column charts:
And for the sales by date, I will set those up as a line chart:
I will also add a scorecard chart, using any of the pivot tables. For this, I just want to pull the total sales:
Now that these charts all set up, it’s just a matter of organizing how you want to see them on your worksheet:
The one thing missing to make this dynamic: slicers. To add slicers to all these pivot tables, click on any of them and click on the Data tab and select the Add a Slicer button:
Then, select the columns you want to filter by:
As long as you are referencing the correct data range, then the slicer will apply to all the pivot tables correctly. And now, if I add a slicer for the stores and only select stores A and B, my dashboard updates as follows:
One thing to remember when you are applying changes: don’t forget to click on the green OK button on the bottom, otherwise your selections won’t be applied:
If you liked this post on How to Make Dashboards in Google Sheets, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
An Excel chart can provide lots of useful information but if it isn’t easy to read, people may skip over its contents. There are many simple things you can do that can quickly add to the visual to make it fit seamlessly within a presentation and that makes it more effective in conveying data. If you want to follow along, in this example, I am going to use data from the Bureau of Economic Analysis. In particular, I am pulling data on automobile sales both in units and average dollars. Here is what my data set looks like right now:
And this is my chart, which shows unit sales by month:
It’s a pretty basic chart that can show me the breakdown between the sales. These are the following changes I can make to improve the look and feel of it:
1. Add a legend
Unless you are just charting one item, most visuals will benefit from a legend. Otherwise, it will be difficult to know which data is represented where. To add a legend, all you need to do is select the chart and go into the Chart Design tab and select the Add Chart Element button, there you will see an option to determine where you want it to show up:
In most cases, you’ll probably want this on the top or bottom as that will help make it blend in easier with the chart. Here’s how it look after I add the legend:
Since my descriptions are long, putting them at the bottom will make more sense. Now I can easily see which bars relate to the foreign sales and which ones relate to domestic.
2. Shrink the gaps (for column charts)
If you have column charts, it can help to shrink the space in-between the bars. That will eliminate white space plus you can fit more items in your chart. To adjust the gaps, right-click on any of the bars and select Format Data Series.
I normally set the Gap Width to 50%. Upon doing so, my chart changes to the following:
3. Adding a descriptive title and subheader
I haven’t set a title for my chart and that’s one thing you shouldn’t overlook doing. Although it may not seem necessary, doing so can help ensure that your chart can stand on its own and not have to rely on the context it is used in to give the reader the right information. A good example in this case can be as follows:
The main title is bolded and shows the reader what the chart is about. And the subheading further distinguishes the different groups of data.
4. Adding data labels
You may want to consider adding data labels to make it easy for the reader to see the exact numbers your chart is showing. This prevents having to make any estimates or rounding off and quoting an incorrect number. To insert data labels, right-click on one of the column charts and select Add Data Labels. Do this for each data series you want to add labels for. This is how my chart looks, with labels:
You can modify the labels if you want to add more information besides just the value. This will depend on the type of chart you have and how much space is available. In this example, you probably wouldn’t want to add more information. However, what I will do is shrink the text size so that it is a bit smaller and so that everything looks less cluttered. To do that, I just click on any of the data labels and under the Home tab, make changes to the font size or color the way I normally would with any other data in Excel. After shrinking the font to size 7 and making it grey, here’ show it looks:
5. Adding a data table
If you don’t want to add data labels, another thing you can do is add a data table. This avoids putting any numbers or labels over top of your data series and still gives the user a helpful table summary. This is a great alternative if you don’t want to crowd too much information into one place and prevent your chart from looking too busy. To add a data table, just go back to the Add Chart Element drop-down option and select Data Table, where you can specify if you want to include the legend key or not. This is how the chart looks with the table:
If you want to avoid the repetition in the axis labels without deleting them and losing those headers, one thing you can do is to change the text format. To do that, right-click on any of the axis labels and select Format Axis. Then, in the Number section, enter three semicolons in the Format Code section and click the Add button:
The three semicolons will remove any formatting and now the axis and data table wouldn’t double up on the names:
6. Remove the border
If you are using the chart in a Word document, presentation, or even Excel, eliminating the border around it can make it blend much easily with the background and other information. To remove the border, right-click on the chart, select Format ChartArea, and under the Border section, select No line. After making the change, this is what my chart looks like now:
With my gridlines turned off, you can no longer see the lines that show where the chart starts and ends.
7. Use a secondary axis with multiple chart types
So far, I’ve only used column charts to show the number of units sold. However, now, I will also include the average selling price. But because the selling price can be in the thousands, I’ll want to move this onto another axis. Otherwise, the number of units sold, which are in millions, won’t show up because of the scale as it will need to accommodate values that are in the tens of thousands.
When you want to put a data series onto another axis, you will need to go to where you select the chart type. If you go to the bottom, select the Combo option. There, you can specify which chart type should be used for each data series. That’s also where you can specify which one should be on a secondary axis. In this example, I’m going to use a line chart for the average price and continue using a column chart for the number of units sold. It doesn’t matter which data set I put on the secondary axis. However, note that the one that is secondary will be on the right-hand-side of the chart.
This is what my updated chart looks like:
In this case, I’ve gotten rid of the data labels for the column charts so that it doesn’t interfere with the line charts.
8.Move the axis categories down
In the examples thus far, I haven’t had any negative values. However, suppose I change my data to now show the change in units sold from one month to the next:
For this example, I combined the data so that it totals both domestic and foreign cars. The above chart shows the month-over-month change. But one problem you’ll notice is that the date labels run along the middle of the chart. This makes it difficult to read when there are negative values.
To make this easier to read, I am going to move the axis labels to the bottom This is useful when dealing with negatives. To make this change, right-click on the axis and select Format Axis. Then, under the Labels section, set the Label Position to Low.
Now, when my chart is updated it looks like this:
9. Showing negative values in a different color
One other change that is going to be helpful when dealing with negatives is to change the color depending on if the value is positive or negative. All you need to do to make this work is to right-click on the column chart, select Format Data Series and switch over to the Fill section. There, you will want to check off the box that says Invert if negative:
Once you do that, you should see two different colors you can set aside for the color section. If you don’t, try and setting one color first, and then toggling the Invert if negative box. With the two different colors, my chart looks as follows:
While you can obviously tell if a chart is going up or down, adding some color to differentiate between positives and negatives just makes the chart all that more readable.
If you liked this post on 9 Things You Can Do to Make Your Charts Easier to Read, 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.
Charts can be great tools to help visualize data. And sometimes, you will want to combine different types of information in one place. That can be tricky because if the scales are different, information may not display the way you would like it to. If something is shown in percentages while another value is in thousands, it isn’t going to be helpful to show that all on the same axis. That is where having a secondary axis can help you show all of that information on just one visual.
Below, I’ll go over how to do that using data from the Bureau of Labor Statistics. I will plot the unemployment rate against the average hourly earnings.
Creating the chart
The first step involves putting all the data together. If you want to follow along, you can download my data file here. This is an excerpt of what my DATA tab looks like:
Next up, I’ll create a Bar Chart by clicking on the data set, selecting the Insert tab and then choosing the option for a clustered column. At first glance, the chart doesn’t look terribly easy to read:
Since the hourly earnings are always above 25, those bar charts aren’t terribly helpful as they make it more difficult for the unemployment rate numbers to stand out. One thing I can do to make this a bit easier to read is to change the chart types.
Use a combo chart and a secondary axis to help display the data more effectively
Before I add another axis, I will first change up the look of these charts by using a combination. Rather than using bar charts for both data series, I’ll use a line chart for the average hourly earnings. Since those values are higher than the unemployment rate, it will help separate the data.
To change the chart type, right-click on the chart and select Change Chart Type
Select Combo on the bottom and off to the right you will see the an area where you can choose the chart type you want for each data series:
By default, Excel has determined I probably want to use a line chart for the average hourly earnings, which is correct. However, I could change it to something else altogether. You will notice this is also where you can check off to use a secondary axis.
While the chart will work fine even without this option, you can see from the preview there is a big gap between the bar graph and the line chart. In the interest of minimizing white space, I will check off the secondary axis for the average hourly earnings. Once I do that and click OK, my chart looks as follows:
You can see the chart now tells a much different story and shows that in the early months of the pandemic, the average hourly earnings spiked. This could possibly be due to a combination of higher-paid earners being less impacted by layoffs and being able to work from home and at the same time, low-wage workers who weren’t laid off may have received bonus pay if they worked in some retail stores. Either way, it definitely shows a much different story than if I didn’t use the secondary axis.
The axis to the left is the primary axis and relates to the unemployment rate. The one on the right is the secondary one and is for the average hourly earnings. This is an important distinction to keep in mind as you can easily be confused if you are not sure which axis relates to which chart. But having the secondary axis makes a big difference to my chart. This is what it would have looked like if everything was just on a single axis:
As you can see, it’s not as easy to visualize the data because of that big gap between the two chart types and them sharing the same scale. As a result, the spike in average hourly earnings is less pronounced than when using a secondary axis.
If you have yet another data series, you can also decide whether to plot that on the primary or secondary axis as multiple charts can be plotted on a single axis. However, if neither one is a good fit then that may be a sign that it is time to consider making a separate chart altogether.
If you liked this post on how to add a secondary axis in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
If you’re creating a dashboard or just want to visualize your data by what part of the world it’s coming from, a map chart can be a great way to accomplish that. Below, I’ll show how you can use a map chart to show data points at regional and global levels. And while you technically can’t use them with pivot tables, I’ll show you how you can use slicers to seamlessly drill down and dynamically update your chart.
Creating a global map chart
It should be easy to tell if the map chart is available on your version of Excel as the Maps icon stands out in the chart section:
If you see the maps option, then you have a compatible version to work with.
To set up the data to work with the maps chart all you need is a simple table that shows a location along with a value. In this example, I’ll focus on different country data. My data set shows the GDP per capita (in U.S. dollars) by country, courtesy of the World Bank. Here’s what a glimpse of what it looks like:
This table doesn’t look terribly great in text and it’s an ideal thing to visualize on a map. As long as your data looks like this and the country names are correct, you can just select this data set and go to insert the map chart, and you’ll get something that looks like this:
As you can see, the dark blue parts of the world have the highest GDP per capita while the lowest shades are on the bottom end of that scale. And the areas in grey do not contain data.
The map automatically adjusts based on how many countries you have included in your data set. If I only include data for Canada, the U.S., and Mexico, my map looks like this:
One of the cool things is you can really zero in on specific regions depending on your data set.
The one thing you might be disappointed to learn is that this type of chart does not work with pivot tables. But in the next section, I’ll show you how you can still drill down on the data and the chart using slicers.
Using slicers to break down the data
Using data from the Bureau of Economic Analysis, I downloaded data for per capita income by county in the U.S., below is what the table looks like:
I had to do a bit of cleaning up the data to ensure that every line contained the state. And it’s also important to convert this into a table so that slicers will work with it.
With the maps chart, one of the things you’ll notice is you need to provide enough of a trail for Excel to be able to determine which location you are referring to. Cities, for example, could have the same names in multiple states or countries. And that’s why whether you’re looking at counties or cities, the more information you provide Excel, the more likely the chart will come out as you want it to.
When you first create a map chart it may not look as you expect it to as it could get the categories all wrong, especially if you have multiple fields. You’ll want to make sure that your series and categories are correctly set up if something looks off.
Under the Series, you should only have your values, such as in my example where it only contains per capita income:
If there’s anything else in there, you may need to delete it and adjust your range. And for your values to show up as a scale (which I’d recommend, otherwise you’ll see a big legend with many colors), you’ll want toedit the legend and make sure the following option is selected:
You may also need to adjust the Horizontal Axis to ensure it includes all of your category columns. Again, this is if your map doesn’t look correct and the regions aren’t showing up correctly. Here is how my horizontal category axis looks like, showing both state and county:
If it’s all set up correctly, your map chart should look something like this:
Now, because this is county-level data, it’s not easy to conceptualize what you’re looking at. But with the help of slicers, you can easily jump to different states. Since the data is in a table, you can add a slicer for the state. If you’re not familiar with how slicers work, check out this post. Although that’s for pivot tables, they’ll work the same within a table.
Once the slicers are in place, you can easily jump through and toggle between the different states. Doing so will automatically adjust your map chart which will now focus in on that specific state, just like when it narrowed in on North America when I only had data for three countries:
Just like with any other chart, you can hover over and see what the values are and the name of the county.
If you liked this post on how to make a map chart in Excel please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
The bulk of Excel users will likely stick to 2D charts that just have an x and y axis. But if you want to add a third element and give your visuals a bit more context, you can create a 3D bubble chart in Excel. Below, I’ll show you how to do just that.
In the following example, I’m going to compare the average mobile data cost in select countries versus their average speeds. I’ll also include what percentage of their populations are smartphone users. Often you hear about one of these metrics but not all three. This is a good example of how adding in more data can provide more context and a more complete picture.
For instance, Canada has one of the highest costs per GB while India has the lowest. But how does that stack up when you consider speed and how much of the country actually uses a smartphone. Using a 3D bubble chart, I can easily add that extra context.
Setting up the data
I pulled in these numbers from a variety of sources to make this work (there were some gaps that I also had to estimate based on older data) and using a series of vlookup formulas I connected them all together.
As far as setting up the data for a chart goes, it’s not much different than what I would do for a 2D chart. Enter the headers and then all of the values. Here’s a summary of what the table looks like with all three data points per country:
Unfortunately, if I try to just create a 3D chart the way I normally would a 2D chart, I’d get a bit of a mess:
Setting up the chart
As you can see, this initial set up isn’t very intuitive. There’s a bit more manual entry involved when setting up a 3D bubble chart in Excel for the first time. What I’m actually going to do is remove all the default data and delete all these items.
You’ll get the above dialog box if you right-click on the chart and click Select Data. I then click remove from all the Legend Entries until it’s all empty:
Now, I’ll click on the Add button to add each entry individually to ensure my data’s pulling correctly:
Here’s an example of one that’s filled out:
After clicking on OK, then I’ll start to see a bubble chart forming:
As I repeat and continue adding more items, my bubble chart ends up looking like this:
What the chart tells us
India went from being in the top-right quadrant to the bottom left. This incidates that while it’s low cost per GB, it’s average speed is the slowest of the countries on this list. And its relatively small bubble also tells us that a small fraction of the population uses smartphones compared to the other countries here.
It also confirms Canada’s still the most expensive. Australia offers much cheaper rates while having similar speeds and a similar portion of the population using smartphones.
You can easily see the details of each individual bubble. If you hover over any one of these bubbles you’ll now get information showing all the values related to them. Here’s the U.S. one:
It may seem like a painstaking effort to create all the bubbles but once you’ve got it set up you don’t have to repeat these steps. You can change the values and the 3D bubble chart will automatically update. The problem is that Excel doesn’t make it easy to set it up initially. But once you’ve selected which values you want to pull, then it gets easier to update the information and use the format again.
The big advantage of using a bubble chart in Excel is that it provides much more information in just one snapshot than a simple 2D chart would give you. Using a 2D chart, I’d only be able to display two data points for each country. A 3D chart allows me to add even more information into a single chart. And that makes it much more useful to the reader.
If you liked this post on how to create a 3D bubble chart in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
Whether you’re tracking sales or costs in excel, it’s important to capture not just your monthly totals but your cumulative year-to-date amounts as well. And to do that in excel, you’ll need to calculate a cumulative sum. Ideally, you’ll want to see a current month’s total alongside the year-to-date figure. Below, I’ll show you how to do that as well as how to make cumulative totals work with multiple years.
Calculating the current month and cumulative sums
First thing’s first, let’s start with a data set. This time around, I’m going to pull the monthly tourist information for Las Vegas. This year, that could prove to be interesting given the impact of COVID-19 on tourism in the city. Here are what the numbers looked like for 2019:
If we wanted to calculate the total visitor volume it would be as simple as the following formula:
=SUM(B:B)
However, if we want the cumulative totals then we can’t just grab the entire column. Instead, we’ll need to add another column that has the cumulative amounts for each month. The formulas will still involve the SUM function but they will need to be from January up until the current row. Here’s what the formulas look like:
The formulas for column C are shown in column D. The key here is freezing the first cell (B2) so that as you copy the formula down in C2, it won’t move while the other cells will.
Calculating cumulative values isn’t too complicated, but it’s a bit trickier when your data set spans multiple years.
Calculating the cumulative sum when working with multiple years
The above scenario works well if you have just one year. But it won’t work if you decide to add next year’s data without resetting the formula. Here’s how it would look if we added the 2020 data:
As you can see, it just keeps on adding on to the previous year’s data, which is not what we want.
There are multiple ways that you can calculate the cumulative sum per year and so that the calculation resets on its own. Let’s start with the easiest route: adding an extra column for the year. Using the YEAR function we can extra what the year is in column A. Then, rather than using the SUM function, we will use the SUMIF function to do the cumulative count, but only if the year is the same:
The logic similar to the earlier formula, we’ve just added a condition where the year in column C has to match the year that specific row belongs to. That’s why once we hit 2020, it resets. For this to work, we still need the months to be in order.
Another way that you can calculate the cumulative total without a helper column is by using an array:
We need to evaluate every cell to see if it relates to the correct year, and if it does, it gets included in the range to sum. The array allows us to do two calculations in one: an IF calculation embedded within a SUM calculation which doesn’t require the helper column.
A big advantage of having multiple years on your data set rather than separating them out is then you can put them into a pivot table and create a pivot chart that helps plot both of them:
From this, we can see that there was a sharp drop off in March due to the outbreak of COVID-19 and that the cumulative figures are now well under 2019’s numbers. By having both cumulative and monthly totals available, we can display them both on one chart that helps to summarize the information quickly and easily.
If you liked this post on How to Calculate Cumulative and Year-to-Date Totals in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
Creating charts and graphs is a great way to display data visually and make it easier for users to read and understand it. However, in some cases, you don’t want or need a big chart, and something smaller would be more useful. This is where sparklines can come in to play and help you get your point across without a big chart in the way. Below, I’ll show you how to quickly and easily make sparklines in Excel that can quickly add context to your data.
*Please note that sparklines were a new feature of Excel 2010. If you’re running an older version of Excel, you won’t have these options available*
Getting the data set ready
I’ll show you how to create sparklines using my data, which is a download of Amazon’s income statement over the past 10 quarters. Here’s what it looks like:
From afar, it’s not the easiest thing to analyze to identify any trending. And ideally, we’d like to have some trending shown for each major income and expense category. Adding a chart just isn’t useful in this case, and this is where sparklines can help.
Creating sparklines in just one click
I’ll start by selecting the row that has revenue and then on the Insert tab and under the Sparklines category I’ll select the Line button:
It will then show me the range that I’ve selected and it will allow me to select where I want to place my sparklines:
In most cases, you’ll probably want this right next to your data. And that’s what I’m going to do — put it in the next cell to the right of the data, L3. Now it’s created my sparkline:
But there’s just one problem:
The sparkline is showing a downward trend. Amazon’s revenue has been increasing, not decreasing. One solution is to re-arrange my data, but that’s not necessary. To fix this, I select the sparkline and then under the Sparklines tab I click on Axis and select the option that says Plot Data Right-to-Left:
Now my sparkline looks a lot better:
This is an optional step and it depends on which direction you want your sparkline to go in.
Applying sparklines to other rows
Now that I’ve got one sparkline setup, it’s time to set up the sparklines for the rest of the income statement line items as well. Surprisingly, this is as easy as just dragging the sparkline down and copying it down to the other rows:
One of the cool features of sparklines is you can quickly add trending to every item without having to add a separate chart or graph for each row. And even for the rows where there was no data, it doesn’t result in an error, either.
If you prefer a column chart to a line chart, then you can easily make the change as well in the Sparklines menu:
It will quickly change the format of the charts:
There’s also a win-loss chart that you can use if you have negative and positive values. However, in most situations, you’re going to use either line or column charts, especially when you’re looking to show trending. But
You can change the color and other features of the sparklines just like with other charts. And all those options are available from within the Sparklines tab.
If you liked this post on how to create a drop-down list in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
