Excel 2016: Creating Charts and Diagrams In Excel 2016, charts and diagrams can show trends, averages, high and low points, and more. Not only do they make your worksheets more visually appealing, they also serve a definite function. Sep 22, 2016. You can create a graph or chart right inside Excel rather than exporting it into some other tool. Line Graphs: Both 2 dimensional and three dimensional line graphs are available in all the versions of Microsoft Excel.
This short tutorial will walk you through adding a line in Excel graph such as an average line, benchmark, trend line, etc. 聽 In the last week's tutorial, we were looking at. In some situations, however, you may want to draw a horizontal line in another chart to compare the actual values with the target you wish to achieve. The task can be performed by plotting two different types of data points in the same graph. In earlier Excel versions, combining two chart types in one was a tedious multi-step operation. Microsoft Excel 2013 and Excel 2016 provide a special type, which makes the process so amazingly simple that you might wonder, 'Wow, why hadn't they done it before?'
. How to draw an average line in Excel graph This quick example will teach you how to add an average line to a column graph. To have it done, perform these 4 simple steps:.
![Types Types](https://cdn.ablebits.com/_img-blog/graph-excel/apply-chart-template-folder.png)
Calculate the average by using the. In our case, insert the below formula in C2 and copy it down the column: =AVERAGE($B$2:$B$7). Select the source data, including the Average column (A1:C7). Go to the Insert tab Charts group and click Recommended Charts. Switch to the All Charts tab, select the Clustered Column - Line template, and click OK: Done!
A horizontal line is plotted in the graph and you can now see what the average value looks like relative to your data set: In a similar fashion, you can draw an average line in a line graph. The steps are totally the same, you just choose the Line or Line with Markers type for the Actual data series. Tips:. The same technique can be used to plot a median For this, use the function instead of AVERAGE. Adding a target line or benchmark line in your graph is even simpler. Instead of a formula, enter your target values in the last column and insert the Clustered Column - Line combo chart as shown in this example. If none of the predefined combo charts suits your needs, select the Custom Combination type (the last template with the pen icon), and choose the desired type for each data series.
How to add a line to an existing Excel graph Adding a line to an existing graph requires a few more steps, therefore in many situations it would be much faster to create a new combo chart from scratch as explained above. But if you've already invested quite a lot of time in designing you graph, you wouldn't want to do the same job twice.
In this case, please follow the below guidelines to add a line in your graph. The process may look a bit complicated on paper, but in your Excel, you will be done in a couple of minutes. Insert a new column beside your source data. If you wish to draw an average line, fill the newly added column with an Average formula discussed in the previous example. If you are adding a benchmark line or target line, put your target values in the new column like shown in the screenshot below:. Right-click the existing graph, and choose Select Data from the context menu:.
In the Select Data Source dialog box, click the Add button in the Legend Entries (Series). In the Edit Series dialog window, do the following:. In the Series namebox, type the desired name, say 'Target line'.
Click in the Series value box and select your target values without the column header. Click OK twice to close both dialog boxes. The target line series is added to the graph (orange bars in the screenshot below). Right-click it, and choose Change Series Chart Type in the context menu:.
In the Change Chart Type dialog box, make sure Combo Custom Combination template is selected, which should be by default. For the Target line series, pick Linefrom the Chart Type聽drop-down box, and click OK.
A horizontal target line is added to your graph: How to plot a target line with different values In situations when you want to compare the actual values with the estimated or target values that are different for each row, the method described above is not very effective. The line does not allow you to pin point the target values exactly, as the result you may misinterpret the information in the graph: To visualize the target values more clearly, you can display them in this way: To achieve this effect, add a line to your chart as explained in the previous examples, and then do the following customizations:. In your graph, double-click the target line.
This will select the line and open the Format Data Series pane on the right side of your Excel window. On the Format Data Series pane, go to Fill & Line tab Line section, and select No line. Switch to the Marker section, expand Marker Options, change it to Built-in, select the horizontal bar in the Type box, and set the Size corresponding to the width of your bars (24 in our example):. Set the marker Fill to Solid fill or Pattern fill and select the color of your choosing. Set the marker Border to Solid line and also choose the desired color. The screenshot below shows my settings: Tips to customize the line To make your graph look even more beautiful, you can change the chart title, legend, axes, gridlines and other elements as described in this tutorial:.
![Charts Charts](/uploads/1/2/4/2/124277627/252783978.png)
And below you will find a few tips relating directly to the line's customization. Display the average / benchmark value on the line In some situations, for example when you set relatively big intervals for the vertical y-axis, it may be hard for your users to determine the exact point where the line crosses the bars. No problem, just show that value in your graph. Here's how you can do this:. Click on the line to select it:. With the whole line selected, click on the last data point.
This will unselect all other data points so that only the last one remains selected:. Right-click the selected data point and pick Add Data Label in the context menu: The label will appear at the end of the line giving more information to your chart viewers: Add a text label for the line To improve your graph further, you may wish to add a text label to the line to indicate what it actually is. Here are the steps for this set up:.
Select the last data point on the line and add a data label to it as discussed in the. Click on the label to select it, then click inside the label box, delete the existing value and type your text:. Hover over the label box until your mouse pointer changes to a four-sided arrow, and then drag the label slightly above the line:.
Right-click the label and choose Font from the context menu. Customize the font style, size and color as you wish: When finished, remove the because it is now superfluous, and enjoy a nicer and clearer look of your chart: Change the line type If the solid line added by default does not look quite attractive to you, you can easily change the line type. Here's how:.
Double-click the line. On the Format Data Series pane, go Fill & Line Line, open the Dash type drop-down box and select the desired type. For example, you can choose Square Dot: And your Average Line graph will look similar to this: Extend the line to the edges of the chart area As you can notice, a horizontal line always starts and ends in the middle of the bars. But what if you want it to stretch to the right and left edges of the chart?
Here is a quick solution: double-click the on the horizontal axis to open the Format Axis pane, switch to Axis Options and choose to position the axis On tick marks: However, this simple method has one drawback - it makes the leftmost and rightmost bars half as thin as the other bars, which does not look nice. As a workaround, you can fiddle with your source data instead of fiddling with the graph settings:. Insert a new row before the first and after the last row with your data. Copy the average/benchmark/target value in the new rows and leave the cells in the first two columns empty, as shown in the screenshot below.
Select the whole table with the empty cells and insert a Column - Line chart. Now, our graph clearly shows how far the first and last bars are from the average: That's how you add a line in Excel graph. I thank you for reading and hope to see you on our blog next week! You may also be interested in:.
Microsoft Excel 2016 has brought us six new built-in chart types. The addition of these new charts is largely due to user feedback and requests. Here is the list of the new charts: 1.
Box & Whisker 5. Sunburst Each of these has its own special scenario-based application, but they all take data visualization to a far more professional level than the typical bar, line and pie charts that have become ubiquitous in data analysis. Prior to Excel 2016, the creation of these charts was either impossible without an add-in or required using Excel tricks developed by experts over the years.
Now they can be created, modified and customized just like the other built-in charts. To get the most out of this guide, try the new charts out for yourself by downloading the exercise file below. #1 - The Waterfall Chart The waterfall chart provides a great method to visualize the impact of multiple data points (typically a series of positive and negative values) as a running total. This is common when analyzing financial data like what would be found in an.
But the waterfall chart is generally useful for visualizing data over time to see where you started versus where you are currently and how you got there. Let's take a look at a generic data set that includes and initial starting amount with various measurements that add and subtract from that amount. The data also has a subtotal along the way along with a final 'Total' value. To create a waterfall chart from this data, we first need to highlight the entire data table. (See also: for Excel) #2 - The Histogram Chart A histogram chart is another variation of the bar chart like the waterfall but instead shows the frequency of data. It does this by showing data as a range of values or 'bin'.
We looked at a very generic generalized example to illustrate the waterfall chart but let's dig into a more specific scenario with the histogram. Let's consider the top 20 players in MLB by batting average.
Our objective is to separate out the frequency of players within 'bins' that span the range from lowest batting average to the highest. Notice that the lowest average of the top 20 players is.308 while the highest is.358. It follows that the total range is 50 points. This would fit quite nicely into 5 bins of 10 points each. Doing so will allow us to visualize the number of players within each 10 point range and see the distribution of the top 20 players across those ranges.
The first thing we need to do is simply highlight our entire table of data and follow the same steps we did with the waterfall chart except select 'Histogram' this time. (See also: ) #3 - The Pareto Chart The Pareto Chart is another variation of the bar chart that mixes in a line chart.
The bars of the chart represent data values in descending order while the line chart represents the progression of the cumulative percentage of the total. For example, let's say we want to look at the annual sales across different product categories for a music store chain.
The Pareto chart will show us the dollar amounts in descending order by category while superimposing a line chart that traces the cumulative percentage of total sales from one category to the next. (See also: for Excel) #4 - The Box and Whisker Chart The box and whisker chart is used in statistics to show the distribution of data into quartiles. The chart also highlights the mean and any outliers.
The first quartile is indicated by the horizontal line on the lower whisker up to the bottom line of the box. The second quartile is from the lower line of the box to the meanwhile the 3rd quartile runs from the mean to the top of the box.
The last quartile begins with the top of the box up to the length of the upper whisker. Any outliers are plotted as points above or below the length of either of the whiskers. We can use single or multiple data series with box and whisker charts. To illustrate, we will look at individual sales data by purchase price for three different musical instrument product categories: electric guitars, keyboards, and pro audio.
Our data table looks like the following. Note that the variation in price is quite wide for keyboards yet fairly narrow for electric guitars. Like the previous charts, this is another very powerful chart type for visualizing data that would otherwise be rather difficult by simply looking at the table of data itself. This is especially true when analyzing a very large data set. (See also: template for Excel) #5 - The Treemap The treemap chart is one that visually represents data in a hierarchical fashion allowing for the recognition of patterns.
The treemap chart represents data with rectangles sized by a value or quantity and it can also make use of different colors to represent different categories. Treemap charts are great for visualizing hierarchical data within categories as compared to other categories. For example, let's consider a small grocery store that wants to visualize sales data across different grocery categories: Fruits, Vegetables, Nuts, Dairy, and Meats. Each of these categories has specific food products classified to them that we have quarterly sales data for.
Notice that even though our data table is organized and sorted by product category, the fact that it isn't sorted by sales amount really doesn't give us a sense for which products have the best or worse sales volume or what kind of patterns are being established. At least not at-a-glance. A treemap chart can help us with this. As before, we simply highlight our data table and select 'Treemap' from the 'All Charts' tab of the 'Insert Chart' dialog box. You should be pretty familiar with this part of the process at this point. The result is the following chart. This chart is a bit more colorful than our previous examples and this is by design.
Each product category has its own color for better differentiation. Notice also how the chart is not only organized largest to smallest in regards to product rectangles within each product category but also how each product category rectangle is organized in the same fashion. Again, the treemap is a fantastic chart for providing a quick glance representation of data in hierarchical fashion within categories. Furthermore, as this example illustrates, it also provides additional visualization at the overall level.
Clearly, dairy is the sales volume leader at the category level. But we can also focus in on the nuts category to see that pecans have the lowest quarterly sales within that category. #6 - The Sunburst Chart The final new chart available in Excel 2016 that we are going to take a look at is the sunburst chart. This is yet another chart that provides a hierarchical visualization of our data. The sunburst map represents data in the form of concentric circles. When visualizing data that is organized into multiple levels of categories, the sunburst chart shows how outer rings relate to inner rings.
Let's look at home run totals within the American League where we have some data broken down to the player level with a few teams. Note the size and relationships of the player boxes to their team box. Since each player only contributes a portion of the team total for home runs, the sunburst chart accurately represents this. Compatibility Considerations A note of caution when sharing your files containing your new brilliant charts in Excel 2016: they are not cross compatible with previous versions of Excel.
If an Excel 2010 or 2013 user opens a file with the new Excel 2016 charts embedded, they will not only not see the actual chart but will instead see a warning: 'This chart isn't available in your version of Excel. Editing this shape or saving this workbook into a different file format will permanently break the chart.' The best workaround for this issue is to simply copy and paste the chart as an image into any file you intend to share with another user that does not have Excel 2016. It is as simple as clicking on the chart in question and going to Home Copy Copy as Picture. If you are pasting into another Office program (like Word or PowerPoint) then you can copy using Ctrl+c and then use Paste Special to Paste as a Picture. This will allow other users with compatibility issues to view the charts but not see any dynamic changes if they make additions, deletions, or other changes to the source data. Final Thoughts There's little doubt that Microsoft has definitely hit it out of the park with these 6 new charts that have been released for Excel 2016 users.
In direct response to user feedback, Microsoft has finally caught up to some of the greatest demands for data visualization. We've created an that gives you a complete overview of the 6 new charts. Spend a bit of time learning more about each of these new charts and it won't be long before you begin to discover new and more opportunities to make use of them.