The line charts are fantastic tools in Excel to visualize records over time. And while working with line charts, we get the need to add a vertical line to mark something (let's say a data point).
We can draw a vertical line on the chart manually but that's just not smart. We would like to add vertical lines dynamically to mark a certain data point, say the max value. And in this article, we will learn how to insert a dynamic vertical line in the chart that changes its location. Believe me, it is easier than you think.
So, let's start:
Here I have a record of sales in different months of a year. The data is stored in an Excel Table so that our chart can be dynamic.
I want to have a line chart that shows the sales in different months on the timeline and mark the maximum value by adding a vertical line.
Follow the steps below:
1: Add a support column to Identify the max number. To insert a vertical line on the max point, we need a supporting column that identifies the max value. Insert a column in the table. And insert this formula to identify the max value.
| =IF(MAX([Sales])=[@Sales],1,"") |
Since I have used the Excel Tables, I get structured data to use in the formula. This formula will enter 1 in the cell of the supporting column when it finds the max value in the Sales column.
2: Select the table and insert a Combo Chart: Select the entire table, including the supporting column and insert a combo chart. Goto--> Insert-->Recommended Charts. In the recommended charts, click on chart with line bars.
3: Change Sales series to the line and supporting column to column: Right-click on the chart and go to change chart type. Change the sales series to the line and supporting column to a column chart. Now it has started to look like what we want it to be.
4: Fix the secondary axis's maximum value to 1: Right-click on the secondary axis and click on the format axis option. In the axis option, set it to 1 from 1.2. Now the bar touches the ceiling of the chart. this is what we wanted.
5: Add error bars to column chart: While having selected the column chart, click on the plus sign-->Error Bars--> more error bars option. Here, in direction select minus and make error amount 100%. And here we go. We have a verticla line inserted in a line chart. (If you want you can just have the column chart but I wanted a thin line.)
6. Make Column Invisible: Select the column chart and go to formatting. In border and fill option, select no fill and no border.
7: Finally, Make Secondary Axis Invisible: Since we don't need to see the secondary axis, let's make it invisible (don't delete it). Select the secondary axis-- go to formatting option--> under Labels --> select None.
That's it. You have added a vertical line to the line chart. Now you only need to beautify it.
Beautify the Chart with Vertical line
Select the different components of the graph that you want to format.
In the chart below, I have made the markers of line chart larger with no fill and borders with white color.
The background fill is black. I colored the sales line green and vertical marker line orange. I removed legends as we don't need them here.
And that's it. We have inserted a vertical line to a chart that changes its position to the maximum value.
Finally, hide the supporting column as you don't need it to be shown. Don't hide it, just make it thin enough so that it doesn't show.
You can download the template for the above chart below:
So yeah guys, this is how we can insert a thin vertical line in an excel chart that changes its position dynamically. I tried to be explanatory. Let me know if it helped you, in the comments section below.
If you have any doubts regarding excel charts, formulas, function or VBA topics, ask that too in the comment section below.
Related Articles:
How to Highlight Maximum and Minimum Data Points in Excel Chart | 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.
How to Create Milestone Chart in Excel | A milestone chart shows the date or time when a milestone is achieved in a graphical way. This graph must be easy to read, explanatory, and visually attractive.
Pareto Chart and Analysis | The Pareto analysis states that your 80% problem is caused by 20% of factors. Or we may say that by working on only 20% of cases you can solve your 80% of problems. Fascinating, right? The method is also famous for the 80/20 rule
Waterfall Chart |This chart is also known as the flying bricks chart or as the bridge chart. It’s used for understanding how an initial value is affected by a series of intermediate positive or negative values.
Excel Sparklines: The Tiny Charts in Cell | The Sparklines are the small charts that reside in a single cell. Sparklines are used to show trends, improvement, and win-loss over the period. The sparklines are charts but they have limited functionalities as compared to regular charts.
Creative Column Chart that Includes Totals | To include the total of the clustered column in the chart and compare them with another group of the columns on the chart is not easy. Here, I have explained how to smartly include totals in the clustered column chart.
4 Creative Target Vs Achievement Charts in Excel |Target vs Achievement charts is a very basic requirement of any excel dashboard. In monthly and yearly reports, Target Vs Achievement charts are the first charts the management refers too and a good target vs Achievement chart will surely grab the attention of management.
Popular Articles:
50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make you work even faster on Excel.
The VLOOKUP Function in Excel | This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.
COUNTIF in Excel 2016 | Count values with conditions using this amazing function. You don't need to filter your data to count specific value. Countif function is essential to prepare your dashboard.
How to Use SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
