how to plot a graph in excel 2016 from a table

#How to plot a graph in excel 2016 from a table how to#
#How to plot a graph in excel 2016 from a table plus#
#How to plot a graph in excel 2016 from a table download#

You can download the template file to highlight the maximum and minimum values on the chart below. You have your column chart ready that highlights the highest value and lowest values on the chart. Select the different segments of the chart and format it as per your requirements.Īnd it is done. Do the same for the minimum series.ĥ: Format the chart to suit your dashboard:

#How to plot a graph in excel 2016 from a table plus#

Select the max series individually -> click on the plus sign and check data labels. To make the columns a little bit thicker, reduce the gap width.Ĥ: Show data labels of max and min values: Do the same for the minimum series.ģ: Decrease the Gap width to make columns look bulky In series option, make the max series overlap value 100%. Right-click on the max series and click on the format series option. But in reality, they are just hiding the actual series. So the idea is to cover the Sales series with max and min series so that it looks like the max and min columns or bars are highlighted by different colors. You can see an orange column and a grey column adjacent to the max and minimum values respectively. Go to insert tab -> Charts -> Clustered Column Chart or Clustered Bar Chart. Select the entire data set, including the supporting columns. The only difference is in formatting.ġ: Select the Data and Insert a Column Chart or Bar Chart To highlight the highest value in a bar or column chart we use almost the same technique as the excel line chart.

#How to plot a graph in excel 2016 from a table how to#

How to Highlight Max and Min on Excel Column Chart I have changed the colors to suit my dashboard. Now you can do a little bit more formatting of components of the excel graph if you want or leave at it is. Finally, your chart will look like something this. Thicken the line if you like.ĭo the same for the minimum value data series and change the color of the border line to anything that depicts low. I increased the size of the Maximum Marker to 10.įrom the border, choose a solid line and color of line Green. Choose an oval marker (you can choose any shape you like). Right-click on the orange marker and click on the format series option. If we have used 0 or blanks, you will get markers at the bottom of the chart.ģ: Formatting the Chart to Highlight Maximum Value and Minimum Value: Why #NA? The reason we use #N/A instead of 0 or blank is to avoid 0 values markers. Or you can do a little bit of formatting to make your chart look attractive. So the basic chart for highlighting the maximum and minimum value on the range is ready.

You can see that the highest value on the line is highlighted with an orange dot and minimum value with a grey dot. Select the data-range and go to insert -> Charts-> Line-> Line with markers Similarly, in the Minimum column, write this formula: = IF( NA())Ģ: Insert a line chart with markers from the table This formula will return the maximum value in the relevant cell, all other cells in the column will be #NA. If you have a normal table then use the normal range formulas but it will not be dynamic. This property is available with Excel tables Read about it here in detail. Note: Here we are using structured names. The Formula in Max column will be: = IF( NA()) 1 to for Maximum and another for the minimum. To highlight maximum and minimum values in the excel line chart, we need to add two supporting columns. I want to have a line graph that highlights the top sales and bottom sales in the year.ġ: Add Supporting Columns for Max and Min Values Here I have a data set of sales of different months of a year in Excel Table. How to Highlight Max and Min on Excel Line Graph In this article, we will learn how to highlight the top and bottom values on excel Line charts and Column Chart both. It is always a good idea to highlight the key values on the graph so that the max and min values can easily be spotted visually. But in line and column Excel charts, it often gets difficult to identify, which value is highest and which value is lowest. Knowing the highest and lowest value in a data set is essential in almost all kind of reports. As they say, a picture worths a thousand words, this is true in all fields.