{"id":2304,"date":"2020-12-09T22:23:21","date_gmt":"2020-12-09T21:23:21","guid":{"rendered":"http:\/\/jakisproblem.pl\/?p=2304"},"modified":"2020-12-09T22:23:21","modified_gmt":"2020-12-09T21:23:21","slug":"how-to-add-vertical-line-to-line-chart-in-excel","status":"publish","type":"post","link":"http:\/\/u239160.webh.me\/jakisproblem.pl\/index.php\/2020\/12\/09\/how-to-add-vertical-line-to-line-chart-in-excel\/","title":{"rendered":"How to add vertical line to line chart in Excel"},"content":{"rendered":"\n<figure class=\"wp-block-embed-wordpress wp-block-embed is-type-wp-embed is-provider-excel-tutorials-functions-and-formulas-for-beginners-and-advanced-users-ablebits-com-blog\"><div class=\"wp-block-embed__wrapper\">\n<blockquote class=\"wp-embedded-content\" data-secret=\"ztRZ1kmDmz\"><a href=\"https:\/\/www.ablebits.com\/office-addins-blog\/2019\/05\/15\/add-vertical-line-excel-chart\/\">How to add vertical line to Excel chart: scatter plot, bar chart and line graph<\/a><\/blockquote><iframe loading=\"lazy\" title=\"&#8220;How to add vertical line to Excel chart: scatter plot, bar chart and line graph&#8221; &#8212; Excel tutorials, functions and formulas for beginners and advanced users - Ablebits.com Blog\" class=\"wp-embedded-content\" sandbox=\"allow-scripts\" security=\"restricted\" style=\"position: absolute; clip: rect(1px, 1px, 1px, 1px);\" src=\"https:\/\/www.ablebits.com\/office-addins-blog\/2019\/05\/15\/add-vertical-line-excel-chart\/embed\/#?secret=ztRZ1kmDmz\" data-secret=\"ztRZ1kmDmz\" width=\"600\" height=\"338\" frameborder=\"0\" marginwidth=\"0\" marginheight=\"0\" scrolling=\"no\"><\/iframe>\n<\/div><\/figure>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/cdn.ablebits.com\/_img-blog\/vertical-line\/inretactive-graph-vertical-line.png\" alt=\"An interactive line chart with a vertical line and scroll bar\"\/><\/figure>\n\n\n\n<p>To insert a vertical line in a line graph, you can use either of the previously described techniques. For me, the second method is a bit faster, so I will be using it for this example. Additionally, we will make our graph interactive with a scroll bar:<br><\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Insert vertical line in Excel graph<\/h3>\n\n\n\n<p>To add a vertical line to an Excel line chart, carry out these steps:<\/p>\n\n\n\n<ol class=\"wp-block-list\"><li>Select your source data and&nbsp;<a href=\"https:\/\/www.ablebits.com\/office-addins-blog\/2018\/08\/29\/make-line-graph-excel\/\">make a line graph<\/a>&nbsp;(<em>Inset<\/em>&nbsp;tab &gt;&nbsp;<em>Chats<\/em>&nbsp;group &gt;&nbsp;<em>Line<\/em>).<\/li><li>Set up the data for the vertical line in this way:<ul><li>In one cell (E1), type the text label for the data point at which you want to draw a line exactly as it appears in your source data.<\/li><li>In two other cells (D3 and D4), extract the&nbsp;<strong>X value<\/strong>&nbsp;for the target data point by using this formula:<\/li><\/ul><code>=IFERROR(MATCH($E$1,$A$2:$A$7,0), 0)<\/code>The&nbsp;<a href=\"https:\/\/www.ablebits.com\/office-addins-blog\/2016\/12\/21\/excel-match-function\/\">MATCH<\/a>&nbsp;function returns the relative position of the lookup value in the array, and the&nbsp;<a href=\"https:\/\/www.ablebits.com\/office-addins-blog\/2017\/09\/27\/excel-iferror-function-with-formula-examples\/\">IFERROR<\/a>&nbsp;function replaces a potential error with zero when the lookup value is not found.<ul><li>In two adjacent cells (E3 and E4), enter the&nbsp;<strong>Y values<\/strong>&nbsp;of 0 and 1.<\/li><\/ul>With the vertical line data in place, please follow steps 3 &#8211; 13 from the&nbsp;<a href=\"https:\/\/www.ablebits.com\/office-addins-blog\/2019\/05\/15\/add-vertical-line-excel-chart\/#vertical-line-bar-chart\">bar chart example<\/a>&nbsp;to plot a vertical line in your chart. Below, I will briefly walk you through the key points.<\/li><li>Right-click anywhere in the chart, and then click&nbsp;<strong>Select Data\u2026&nbsp;<\/strong>.<\/li><li>In the&nbsp;<em>Select Data Source<\/em>&nbsp;dialogue box, click the&nbsp;<strong>Add<\/strong>&nbsp;button.<\/li><li>In the&nbsp;<em>Edit Series<\/em>&nbsp;window, type any name you want in the&nbsp;<em>Series name<\/em>&nbsp;box (e.g.&nbsp;<em>Vertical<\/em>&nbsp;<em>Line<\/em>), and select the cells with&nbsp;<strong>X values<\/strong>&nbsp;for the&nbsp;<em>Series values<\/em>&nbsp;box (D3:D4 in our case).<br><\/li><li>Right click anywhere in the chart and choose&nbsp;<strong>Change Chart Type<\/strong>&nbsp;from the pop-up menu.<\/li><li>In the&nbsp;<em>Change Chart Type<\/em>&nbsp;window, make the following changes:<ul><li>On the&nbsp;<em>All Charts<\/em>&nbsp;tab, select&nbsp;<strong>Combo<\/strong>.<\/li><li>For the main data series, choose the&nbsp;<strong>Line<\/strong>&nbsp;chart type.<\/li><li>For the&nbsp;<em>Vertical Line<\/em>&nbsp;data series, pick&nbsp;<strong>Scatter with Straight Lines<\/strong>&nbsp;and select the&nbsp;<strong>Secondary Axis<\/strong>&nbsp;checkbox next to it.<\/li><li>Click&nbsp;<em>OK<\/em>.<\/li><\/ul><\/li><li>Right-click the chart and choose&nbsp;<strong>Select Data\u2026<\/strong><\/li><li>In the&nbsp;<em>Select Data Source<\/em>&nbsp;dialog box, select the&nbsp;<em>Vertical<\/em>&nbsp;<em>Line<\/em>&nbsp;series and click&nbsp;<strong>Edit<\/strong>.<br><\/li><li>In the&nbsp;<em>Edit Series<\/em>&nbsp;dialog box, select the X and Y values for the corresponding boxes, and click&nbsp;<em>OK<\/em>&nbsp;twice to exit the dialogs.<br><\/li><li>Right-click the secondary y-axis on the right, and then click&nbsp;<strong>Format Axis<\/strong>.<\/li><li>On the&nbsp;<em>Format Axis<\/em>&nbsp;pane, under&nbsp;<em>Axis Options<\/em>, type 1 in the&nbsp;<strong>Maximum bound<\/strong>&nbsp;box to ensure that your vertical line extends to the top of the chart.<\/li><li>Hide the right y-axis by setting&nbsp;<em>Label Position<\/em>&nbsp;to&nbsp;<strong>None<\/strong>.<\/li><\/ol>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/cdn.ablebits.com\/_img-blog\/vertical-line\/line-chart-vertical-line.png\" alt=\"Excel line chart with a vertical line\"\/><\/figure>\n\n\n\n<p>Your chart with a vertical line is done, and now it&#8217;s time to try it out. Type another text label in E2, and see the vertical line move accordingly.<br><\/p>\n\n\n\n<p>Don&#8217;t want to bother typing? Fancy up your graph by adding a scroll bar!<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Make a vertical line interactive with a scroll bar<\/h3>\n\n\n\n<p>To interact with the chart directly, let&#8217;s insert a scroll bar and connect our vertical line to it. For this, you will need the&nbsp;<a href=\"https:\/\/www.ablebits.com\/office-addins-blog\/2019\/06\/05\/add-developer-tab-excel\/\">Developer Tab<\/a>. If you don&#8217;t have it on your Excel ribbon yet, it is very easy to enable: right-click the ribbon, click&nbsp;<em>Customize Ribbon<\/em>, select&nbsp;<em>Developer<\/em>&nbsp;under&nbsp;<em>Main Tabs<\/em>, and click&nbsp;<em>OK<\/em>. That&#8217;s it!<\/p>\n\n\n\n<p>And now, perform these simple steps to insert a scroll bar:<\/p>\n\n\n\n<ol class=\"wp-block-list\"><li>On the&nbsp;<em>Developer<\/em>&nbsp;tab, in the&nbsp;<em>Controls<\/em>&nbsp;group, click the&nbsp;<em>Insert<\/em>&nbsp;button, and then click&nbsp;<strong>Scroll Bar<\/strong>&nbsp;under&nbsp;<em>Form Controls<\/em>:<br><\/li><li>On top or at the bottom of your graph (depending on where you want the scroll bar to appear), draw a rectangle of the desired width using the mouse. Or simply click anywhere on your sheet, and then move and resize the scroll bar as you see fit.<\/li><li>Right click the scroll bar and click&nbsp;<strong>Format Control\u2026<\/strong>.<br><\/li><li>Link your scroll bar to some empty cell (D5), set the&nbsp;<strong>Maximum Value<\/strong>&nbsp;to the data points total and click&nbsp;<em>OK<\/em>. We have data for 6 months, so we set the&nbsp;<em>Maximum Value<\/em>&nbsp;to 6.<br><\/li><li>The linked cell now shows the value of the scroll bar, and we need to pass that value to our X cells in order to bind the vertical line to the scroll bar. So, delete the IFERROR\/MATCH formula from cells D3:D4 and enter this simple one instead:&nbsp;<code>=$D$5<\/code><\/li><\/ol>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/cdn.ablebits.com\/_img-blog\/vertical-line\/connect-scroll-bar-vertical-line.png\" alt=\"Bind the vertical line to the scroll bar.\"\/><\/figure>\n\n\n\n<p>The&nbsp;<em>Target month<\/em>&nbsp;cells (D1 and E1) are not needed any longer, and you are free to delete them. Or, you can return the target month by using the below formula (which goes to cell E1):<\/p>\n\n\n\n<p><code>=IFERROR(INDEX($A$2:$A$7, $D$5, 1), \"\")<\/code><\/p>\n\n\n\n<p>That&#8217;s it! Our interactive line chart is completed. That has taken quite a bit of time, but it&#8217;s worth it. Do you agree?<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/cdn.ablebits.com\/_img-blog\/vertical-line\/chart-interactive-vertical-line.png\" alt=\"Interactive line graph with a vertical line\"\/><\/figure>\n\n\n\n<p>That&#8217;s how you create a vertical line in Excel chart. For hands-on experience, please feel free to download our sample workbook below. I thank you for reading and hope to see you on our blog next week!<\/p>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p class=\"excerpt\">To insert a vertical line in a line graph, you can use either of the previously described techniques. For me, the second method is a bit faster, so I will be using it for this example. Additionally, we will make our graph interactive with a scroll bar: Insert vertical line in Excel graph To add&hellip;<\/p>\n<p class=\"more-link-p\"><a class=\"more-link\" href=\"http:\/\/u239160.webh.me\/jakisproblem.pl\/index.php\/2020\/12\/09\/how-to-add-vertical-line-to-line-chart-in-excel\/\">Read more &rarr;<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[35],"class_list":["post-2304","post","type-post","status-publish","format-standard","hentry","category-bez-kategorii","tag-excel"],"_links":{"self":[{"href":"http:\/\/u239160.webh.me\/jakisproblem.pl\/index.php\/wp-json\/wp\/v2\/posts\/2304","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/u239160.webh.me\/jakisproblem.pl\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/u239160.webh.me\/jakisproblem.pl\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/u239160.webh.me\/jakisproblem.pl\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/u239160.webh.me\/jakisproblem.pl\/index.php\/wp-json\/wp\/v2\/comments?post=2304"}],"version-history":[{"count":1,"href":"http:\/\/u239160.webh.me\/jakisproblem.pl\/index.php\/wp-json\/wp\/v2\/posts\/2304\/revisions"}],"predecessor-version":[{"id":2305,"href":"http:\/\/u239160.webh.me\/jakisproblem.pl\/index.php\/wp-json\/wp\/v2\/posts\/2304\/revisions\/2305"}],"wp:attachment":[{"href":"http:\/\/u239160.webh.me\/jakisproblem.pl\/index.php\/wp-json\/wp\/v2\/media?parent=2304"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/u239160.webh.me\/jakisproblem.pl\/index.php\/wp-json\/wp\/v2\/categories?post=2304"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/u239160.webh.me\/jakisproblem.pl\/index.php\/wp-json\/wp\/v2\/tags?post=2304"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}