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.
When using charts, sometimes you’ll want to highlight items differently depending on if they’re positive or negative, or may if they’re above or below a benchmark or average. While you can apply conditional formatting to cells, it’s not as easily done if you want to do the same to a bar chart. There’s no simple way without it involving a manual process. However, the good news is there is a workaround.
I’m going to use data from my 12 most recent stock picks on fool.ca and how they’ve done over the course of the month they were picked:
If I were to map the above table out in a bar graph, here’s how it would look:
Adding a column to add another series you can format
While the above chart is a good way to illustrate the performance, if I want to adjust the values so that they’re negative if they’re red, what I’ll want to do is add an extra column:
Then it’s just a matter of updating the chart so that the extra column is included, and changing the color of all the items on the negative series to red. This is also where you can apply whatever formatting you want to the specific series, and hence, conditionally format the results:
Make sure you’re using a stacked chart
The one thing that looks off in the above chart is that the red numbers are on the right-hand side and the blue (positive) numbers are off to the left. To fix this, I’m gonna the chart type to a stacked chart. Then my chart looks like this:
That looks a bit better and is more what I was hoping to achieve.
However, I can even take this a bit further and add more columns. As long as it’s in a stacked chart and only one number is filled in for a column, you can have a lot more customization.
Adding a column for above-average results to add more conditional formatting
What I can do to help further differentiate the results is to create a column for above-average returns. To do this, I’ll add another column. I could do an if calculation to see if the number was greater than the average of 6.65%. You can have as many columns as you need to help get the number of groups you need. For example, you might have one column for the return, then a separate column for each group that you want to classify the numbers in. In this example, I just used three columns:
Note that what’s key here is that an amount only shows up in one of the three columns. If there’s any overlap, you’ll have multiple colors per item and then that will defeat the purpose of having only one color.
Below is what the chart looks like now, with bright green showing above average, red being negative, and anything else being light green:
Although conditional formatting isn’t very intuitive when it comes to a bar chart, there are ways around it that you can make it work.
If you liked this post on How to Apply Conditional Formatting to a Bar 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.
Excel has a lot of charts and graphs that you can use to visually show data. However, there’s a way to create a graph using just a simple formula and applying some formatting to it. Below, I’ll show you how to make a bar graph easily without having to worry about legends, axis, or any other chart element you might otherwise need to manipulate.
The key function that makes this all possible is REPT, which just repeats a character a set amount of times. Here’s how it works in practice:
In the first argument, I specify the character that I want to be repeated. The | symbol, in this case, is repeated five times, which is what the second argument specifies. Right now, this doesn’t look anything like a bar chart, but that’s as easy as changing the font. Here’s how it looks like if the font is set to Britannic Bold and size 11:
If I had several of these values, I could make it look like a bar chart pretty quickly. Below are some random numbers from 1-10 and how I turned them into a bar chart using the earlier formula:
In the above formulas, I replaced the second argument with the numbers specified above. If an item had the number 10, the | character would be repeated 10 times.
Scaling the bar graph
Now, if you’re dealing with really small or really large numbers, your bar graph could look very skewed. What I’d suggest doing is determining how big you want your graph to look. For a column with a width of 15, I found that 35 characters would fill the bar chart all the way to the end of the cell (using the font type, size and character that I used above).
So to help make sure that my data was properly scaled, I’d calculate the maximum number from my data set, divide the specified number by that, and then multiply it by 35.
Here’s another example with numbers between 100-1,000, if I were not to adjust anything from the previous formula:
If I were to use the above formula as is, you can see my bar chart is going to explode with bigger numbers. So what I’d want to do is adjust the number of times the characters above repeat, to a maximum of 35.
First, I need to determine what value should maximize the cell or bar chart. In the above data set, 988 is the largest number. However, if my scale goes up to 1,000, that might be a better number to use as the maximum.
In that case, my formula for the number of repeats will look something like this: (value/1000)*35. That way, for the value 988, that will return 34.58 as the number of times I’ll repeat the | character. If I use that formula for the data, here’s how it looks now:
Adding more formatting
Now, I’ve got a bar chart that looks a lot more contained. However, this is still kind of a bit boring. So what I can do is add some conditional formatting to help make some of the items stand out a bit more:
In the above example, I set rules for anything below 300 to be highlighted red and anything above 800 to be green. When applying the conditional formatting, make sure you’re changing the fontcolor, not the fill color. For more information on how to set up conditional formatting, check out this post.
If you liked this post on How to Make a Bar Graph in Excel Using a Formula, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
Do you use Excel charts in PowerPoint presentations or want to use them in some other programs? Then you probably know it’s not something that’s very intuitive and often when you save a chart from Excel it’s not the high resolution image that that you were hoping for. And embedding charts within PowerPoint is a whole separate headache altogether.
The good news is that there’s a fairly easy solution. In the past, I’ve used code to save a chart as an image file, but even that didn’t always work very well and it involves running a macro. Once you try stretching it out you’ll still likely see a bit of a reduction in quality.
Let’s take this chart as an example:
If I use the method to save it as a chart using VBA, this is how it will look expanded:
The image starts to get a bit faded and it’s far from ideal.
How do you get around that? Save the file in Microsoft Paint. Select the chart, copy it, and paste it into Paint and then save it as an image file. Besides cropping it, I don’t do any editing or special changes to the chart. However, the chart still needs to be a decent size in Excel so if it still looks faded, try making the chart bigger and then repeat the steps.
Here’s how the same chart looks, but using Paint:
It’s a higher quality image and the process is a bit easier unless you’ve got lots of charts that you want to save quickly. Visuals can be useful tools in presentations and I hope this has helped you learn how to save an excel chart as high resolution image.
If you liked this post on How to Save an Excel Chart As a High Resolution Image, 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.
