www.ablebits.com
Open in
urlscan Pro
54.165.213.88
Public Scan
URL:
https://www.ablebits.com/office-addins-blog/add-line-excel-graph/
Submission: On April 17 via manual from ZA — Scanned from DE
Submission: On April 17 via manual from ZA — Scanned from DE
Form analysis 8 forms found in the DOM
<form>
<fieldset>
<legend class="visuallyhidden">Consent Selection</legend>
<div id="CybotCookiebotDialogBodyFieldsetInnerContainer">
<div class="CybotCookiebotDialogBodyLevelButtonWrapper"><label class="CybotCookiebotDialogBodyLevelButtonLabel" for="CybotCookiebotDialogBodyLevelButtonNecessary"><strong
class="CybotCookiebotDialogBodyLevelButtonDescription">Necessary</strong></label>
<div class="CybotCookiebotDialogBodyLevelButtonSliderWrapper CybotCookiebotDialogBodyLevelButtonSliderWrapperDisabled"><input type="checkbox" id="CybotCookiebotDialogBodyLevelButtonNecessary"
class="CybotCookiebotDialogBodyLevelButton CybotCookiebotDialogBodyLevelButtonDisabled" disabled="disabled" checked="checked"> <span class="CybotCookiebotDialogBodyLevelButtonSlider"></span></div>
</div>
<div class="CybotCookiebotDialogBodyLevelButtonWrapper"><label class="CybotCookiebotDialogBodyLevelButtonLabel" for="CybotCookiebotDialogBodyLevelButtonPreferences"><strong
class="CybotCookiebotDialogBodyLevelButtonDescription">Preferences</strong></label>
<div class="CybotCookiebotDialogBodyLevelButtonSliderWrapper"><input type="checkbox" id="CybotCookiebotDialogBodyLevelButtonPreferences" class="CybotCookiebotDialogBodyLevelButton CybotCookiebotDialogBodyLevelConsentCheckbox"
data-target="CybotCookiebotDialogBodyLevelButtonPreferencesInline" checked="checked" tabindex="0"> <span class="CybotCookiebotDialogBodyLevelButtonSlider"></span></div>
</div>
<div class="CybotCookiebotDialogBodyLevelButtonWrapper"><label class="CybotCookiebotDialogBodyLevelButtonLabel" for="CybotCookiebotDialogBodyLevelButtonStatistics"><strong
class="CybotCookiebotDialogBodyLevelButtonDescription">Statistics</strong></label>
<div class="CybotCookiebotDialogBodyLevelButtonSliderWrapper"><input type="checkbox" id="CybotCookiebotDialogBodyLevelButtonStatistics" class="CybotCookiebotDialogBodyLevelButton CybotCookiebotDialogBodyLevelConsentCheckbox"
data-target="CybotCookiebotDialogBodyLevelButtonStatisticsInline" checked="checked" tabindex="0"> <span class="CybotCookiebotDialogBodyLevelButtonSlider"></span></div>
</div>
<div class="CybotCookiebotDialogBodyLevelButtonWrapper"><label class="CybotCookiebotDialogBodyLevelButtonLabel" for="CybotCookiebotDialogBodyLevelButtonMarketing"><strong
class="CybotCookiebotDialogBodyLevelButtonDescription">Marketing</strong></label>
<div class="CybotCookiebotDialogBodyLevelButtonSliderWrapper"><input type="checkbox" id="CybotCookiebotDialogBodyLevelButtonMarketing" class="CybotCookiebotDialogBodyLevelButton CybotCookiebotDialogBodyLevelConsentCheckbox"
data-target="CybotCookiebotDialogBodyLevelButtonMarketingInline" checked="checked" tabindex="0"> <span class="CybotCookiebotDialogBodyLevelButtonSlider"></span></div>
</div>
</div>
</fieldset>
</form><form><input type="checkbox" id="CybotCookiebotDialogBodyLevelButtonNecessaryInline" class="CybotCookiebotDialogBodyLevelButton CybotCookiebotDialogBodyLevelButtonDisabled" disabled="disabled" checked="checked"> <span
class="CybotCookiebotDialogBodyLevelButtonSlider"></span></form><form><input type="checkbox" id="CybotCookiebotDialogBodyLevelButtonPreferencesInline" class="CybotCookiebotDialogBodyLevelButton CybotCookiebotDialogBodyLevelConsentCheckbox" data-target="CybotCookiebotDialogBodyLevelButtonPreferences"
checked="checked" tabindex="0"> <span class="CybotCookiebotDialogBodyLevelButtonSlider"></span></form><form><input type="checkbox" id="CybotCookiebotDialogBodyLevelButtonStatisticsInline" class="CybotCookiebotDialogBodyLevelButton CybotCookiebotDialogBodyLevelConsentCheckbox" data-target="CybotCookiebotDialogBodyLevelButtonStatistics"
checked="checked" tabindex="0"> <span class="CybotCookiebotDialogBodyLevelButtonSlider"></span></form><form><input type="checkbox" id="CybotCookiebotDialogBodyLevelButtonMarketingInline" class="CybotCookiebotDialogBodyLevelButton CybotCookiebotDialogBodyLevelConsentCheckbox" data-target="CybotCookiebotDialogBodyLevelButtonMarketing" checked="checked"
tabindex="0"> <span class="CybotCookiebotDialogBodyLevelButtonSlider"></span></form><form class="CybotCookiebotDialogBodyLevelButtonSliderWrapper"><input type="checkbox" id="CybotCookiebotDialogBodyContentCheckboxPersonalInformation" class="CybotCookiebotDialogBodyLevelButton"> <span
class="CybotCookiebotDialogBodyLevelButtonSlider"></span></form>GET /office-addins-blog/search.php
<form role="search" method="get" class="bemSearchForm bemSearchForm--Blog" action="/office-addins-blog/search.php">
<input type="search" id="id-search-form-input" class="bemSearchForm__Input bemSearchForm__Input--Blog" required="" placeholder="Search (powered by Google)" value="" name="search">
<button style="cursor: pointer;" type="submit" aria-label="Search in blog" class="bemSearchForm__Button bemSearchForm__Button--Blog"></button>
</form>POST https://www.ablebits.com/office-addins-blog/wp-comments-post.php
<form action="https://www.ablebits.com/office-addins-blog/wp-comments-post.php" method="post" id="commentform" class="a20commentform-From">
<input class="a20commentform-Input a20commentform-Input--Name sFormCheckValue" type="text" name="author" id="author" value="" size="22" placeholder="Your name" required="" autocomplete="name">
<input class="a20commentform-Input a20commentform-Input--Email sFormCheckValue" type="email" name="email" id="email" value="" size="22" placeholder="E-mail (not published)" autocomplete="email">
<textarea required="" class="a20commentform-Input a20commentform-Input--Body sFormCheckValue" name="comment" id="comment" rows="7" placeholder="Your comment"></textarea>
<label class="a19ContactForm-Checkbox a19ContactForm-Checkbox--Privacy">
<input type="checkbox" name="agreement" required="" class="a20commentform-Input a19ContactForm-CheckboxInput sFormCheckValue">
<span class="a19ContactForm-CheckboxStyle"></span> I have read and accept the <a class="a19Section-Link" target="_blank" href="https://www.ablebits.com/docs/ablebits-terms-of-use/">Terms of use</a> and
<a class="a19Section-Link" target="_blank" href="https://www.ablebits.com/docs/ablebits-privacy-policy/">Privacy Policy</a>
</label>
<div id="submit-frame">
<input class="a20commentform-Button" id="sendcomment" type="submit" value="Send">
<input type="hidden" name="comment_post_ID" value="16781" id="comment_post_ID">
<input type="hidden" name="comment_parent" id="comment_parent" value="0">
<br><br>
</div>
<p style="display: none;"><input type="hidden" id="akismet_comment_nonce" name="akismet_comment_nonce" value="c28dc98469"></p>
<p style="display: none !important;" class="akismet-fields-container" data-prefix="ak_"><label>Δ<textarea name="ak_hp_textarea" cols="45" rows="8" maxlength="100"></textarea></label><input type="hidden" id="ak_js_1" name="ak_js"
value="1713344885146">
<script>
document.getElementById("ak_js_1").setAttribute("value", (new Date()).getTime());
</script>
</p><small class="b22PostNote b22PostNote--Form">Thank you for your comment!<br> When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to<br> your query. We cannot guarantee that we will
answer every question, but we'll do our best :)</small>
</form>Text Content
Powered by Cookiebot
* Consent
* Details
* [#IABV2SETTINGS#]
* About
COOKIES AND SIMILAR TECHNOLOGIES ON OUR WEBSITE.
When you use and access our website, we may place several cookie files in your
web browser. We use cookies for the following purposes: to enable certain
functions of website, to collect analytics, to store your preferences, to enable
advertisements delivery, including behavioral advertising.
Consent Selection
Necessary
Preferences
Statistics
Marketing
Show details
* Necessary 17
We used necessary Cookies to operate the core functions of our website, so
that you may visit and move around it, and use its features. We do not
require your consent to use these Cookies, but you may be able to block these
Cookies yourself on your device. However, without these Cookies, our website
is unlikely to work as you would expect and certain services that you may ask
for, for example, signing into your online account, cannot be provided.
Where we process personal data using these necessary Cookies, we do so on the
basis of our legitimate interests to provide a website for visitors to use
and to promote our business. You can find more information in our Cookies
Policy (https://www.ablebits.com/docs/ablebits-cookies-policy/).
* Cookiebot
1
Learn more about this provider
CookieConsentStores the user's cookie consent state for the current domain
Expiry: 1 yearType: HTTP
* Google
1
Learn more about this provider
test_cookieUsed to check if the user's browser supports cookies.
Expiry: 1 dayType: HTTP
* Microsoft
4
Learn more about this provider
#-ARRAffinityUsed to distribute traffic to the website on several servers
in order to optimise response times.
Expiry: SessionType: HTTP
ENecessary for the sign-up function on the website.
Expiry: SessionType: HTTP
wla42Allows the website to access the camera of the device for video
calls.
Expiry: 7 daysType: HTTP
x#Necessary for the shopping cart functionality on the website.
Expiry: SessionType: HTTP
* Zendesk
9
Learn more about this provider
__zlcmidPreserves users states across page requests.
Expiry: 1 yearType: HTTP
__zlcstoreNecessary for the functionality of the website's chat-box
function.
Expiry: SessionType: HTTP
zte#Saves a Zopim Live Chat ID that recognises a device between visits
during a chat session.
Expiry: SessionType: HTTP
__cfruidThis cookie is a part of the services provided by Cloudflare -
Including load-balancing, deliverance of website content and serving DNS
connection for website operators.
Expiry: SessionType: HTTP
ZD-buidUnique id that identifies the user on recurring visits.
Expiry: PersistentType: HTML
ZD-storeRegisters whether the self-service-assistant Zendesk Answer Bot
has been displayed to the website user.
Expiry: PersistentType: HTML
ZD-suidUnique id that identifies the user's session.
Expiry: PersistentType: HTML
__zlcidThis cookie is necessary for the chat-box function on the website
to function.
Expiry: SessionType: HTTP
AWSALBCORSRegisters which server-cluster is serving the visitor. This is
used in context with load balancing, in order to optimize user experience.
Expiry: 7 daysType: HTTP
* www.ablebits.com
static.zdassets.com
2
__zlcstore [x2]This cookie is necessary for the chat-box function on the
website to function.
Expiry: PersistentType: HTML
* Preferences 1
We may use preferences Cookies to remember information that changes the way
the website behaves or looks, such as the "Show Chat" functionality of a user
or other preference.
* Ablebits
1
Learn more about this provider
bn-suite-smallAppearance of Ultimate Suite advertising banner (false -
show full banner, true - minimized banner)
Expiry: 6 monthsType: HTTP
* Statistics 11
Statistic Cookies help us to understand how you interact with our website by
collecting and reporting information anonymously.
* Ablebits
5
Learn more about this provider
ab-lpage [x2]Pending
Expiry: 188 daysType: HTTP
ab-refPending
Expiry: 188 daysType: HTTP
ab-uslp-v [x2]Pending
Expiry: 6 monthsType: HTTP
* Google
3
Learn more about this provider
_gaRegisters a unique ID that is used to generate statistical data on how
the visitor uses the website.
Expiry: 2 yearsType: HTTP
_ga_#Used by Google Analytics to collect data on the number of times a
user has visited the website as well as dates for the first and most
recent visit.
Expiry: 2 yearsType: HTTP
tdRegisters statistical data on users' behaviour on the website. Used for
internal analytics by the website operator.
Expiry: SessionType: Pixel
* Microsoft
3
Learn more about this provider
c.gifCollects data on the user’s navigation and behavior on the website.
This is used to compile statistical reports and heatmaps for the website
owner.
Expiry: SessionType: Pixel
MR [x2]Indicates whether to refresh MUID.
Expiry: 7 daysType: HTTP
* Marketing 28
We may use marketing Cookies to test new advertisements, pages, features, or
new functionality of the website to see how our users react to them.
* Google
5
Learn more about this provider
_gcl_auUsed by Google AdSense for experimenting with advertisement
efficiency across websites using their services.
Expiry: 3 monthsType: HTTP
IDEUsed by Google DoubleClick to register and report the website user's
actions after viewing or clicking one of the advertiser's ads with the
purpose of measuring the efficacy of an ad and to present targeted ads to
the user.
Expiry: 1 yearType: HTTP
pagead/landing [x2]Collects data on visitor behaviour from multiple
websites, in order to present more relevant advertisement - This also
allows the website to limit the number of times that they are shown the
same advertisement.
Expiry: SessionType: Pixel
pagead/1p-user-list/#Tracks if the user has shown interest in specific
products or events across multiple websites and detects how the user
navigates between sites. This is used for measurement of advertisement
efforts and facilitates payment of referral-fees between websites.
Expiry: SessionType: Pixel
* Microsoft
8
Learn more about this provider
MUID [x2]Used widely by Microsoft as a unique user ID. The cookie enables
user tracking by synchronising the ID across many Microsoft domains.
Expiry: 1 yearType: HTTP
SRM_BTracks the user’s interaction with the website’s search-bar-function.
This data can be used to present the user with relevant products or
services.
Expiry: 1 yearType: HTTP
SRM_L [x2]Tracks the user’s interaction with the website’s
search-bar-function. This data can be used to present the user with
relevant products or services.
Expiry: 1 yearType: HTTP
ANONCHKRegisters data on visitors from multiple visits and on multiple
websites. This information is used to measure the efficiency of
advertisement on websites.
Expiry: 1 dayType: HTTP
SMRegisters a unique ID that identifies the user's device during return
visits across websites that use the same ad network. The ID is used to
allow targeted ads.
Expiry: SessionType: HTTP
xidSets a unique ID for the visitor, that allows third party advertisers
to target the visitor with relevant advertisement. This pairing service is
provided by third party advertisement hubs, which facilitates real-time
bidding for advertisers.
Expiry: SessionType: HTTP
* YouTube
15
Learn more about this provider
#-#Pending
Expiry: SessionType: HTML
iU5q-!O9@$Registers a unique ID to keep statistics of what videos from
YouTube the user has seen.
Expiry: SessionType: HTML
LAST_RESULT_ENTRY_KEYUsed to track user’s interaction with embedded
content.
Expiry: SessionType: HTTP
nextIdUsed to track user’s interaction with embedded content.
Expiry: SessionType: HTTP
requestsUsed to track user’s interaction with embedded content.
Expiry: SessionType: HTTP
yt.innertube::nextIdRegisters a unique ID to keep statistics of what
videos from YouTube the user has seen.
Expiry: PersistentType: HTML
ytidb::LAST_RESULT_ENTRY_KEYUsed to track user’s interaction with embedded
content.
Expiry: PersistentType: HTML
YtIdbMeta#databasesUsed to track user’s interaction with embedded content.
Expiry: PersistentType: IDB
yt-remote-cast-availableStores the user's video player preferences using
embedded YouTube video
Expiry: SessionType: HTML
yt-remote-cast-installedStores the user's video player preferences using
embedded YouTube video
Expiry: SessionType: HTML
yt-remote-connected-devicesStores the user's video player preferences
using embedded YouTube video
Expiry: PersistentType: HTML
yt-remote-device-idStores the user's video player preferences using
embedded YouTube video
Expiry: PersistentType: HTML
yt-remote-fast-check-periodStores the user's video player preferences
using embedded YouTube video
Expiry: SessionType: HTML
yt-remote-session-appStores the user's video player preferences using
embedded YouTube video
Expiry: SessionType: HTML
yt-remote-session-nameStores the user's video player preferences using
embedded YouTube video
Expiry: SessionType: HTML
* Unclassified 6
Unclassified cookies are cookies that we are in the process of classifying,
together with the providers of individual cookies.
* Microsoft
6
Learn more about this provider
BPPending
Expiry: SessionType: HTTP
LDPending
Expiry: SessionType: HTTP
LDHPending
Expiry: SessionType: HTTP
pltmodePending
Expiry: 1 dayType: HTTP
failedsessionPending
Expiry: SessionType: HTTP
srPending
Expiry: SessionType: HTTP
Cross-domain consent[#BULK_CONSENT_DOMAINS_COUNT#] [#BULK_CONSENT_TITLE#]
List of domains your consent applies to: [#BULK_CONSENT_DOMAINS#]
Cookie declaration last updated on 15.04.24 by Cookiebot
[#IABV2_TITLE#]
[#IABV2_BODY_INTRO#]
[#IABV2_BODY_LEGITIMATE_INTEREST_INTRO#]
[#IABV2_BODY_PREFERENCE_INTRO#]
[#IABV2_LABEL_PURPOSES#]
[#IABV2_BODY_PURPOSES_INTRO#]
[#IABV2_BODY_PURPOSES#]
[#IABV2_LABEL_FEATURES#]
[#IABV2_BODY_FEATURES_INTRO#]
[#IABV2_BODY_FEATURES#]
[#IABV2_LABEL_PARTNERS#]
[#IABV2_BODY_PARTNERS_INTRO#]
[#IABV2_BODY_PARTNERS#]
Cookies are small data files that are sent to your web browser by a website you
visit. A cookie file is stored in your web browser and allows our website or a
third-party to recognize you and make your next visit easier and make our
website more useful to you.
The law states that we can store cookies on your device if they are strictly
necessary for the operation of this site. For all other types of cookies we need
your permission. This means that cookies which are categorized as necessary, are
processed based on GDPR Art. 6 (1) (f). All other cookies, meaning those from
the categories preferences and marketing, are processed based on GDPR Art. 6 (1)
(a) GDPR.
For latest Cookies Policy, go
to: https://www.ablebits.com/docs/ablebits-cookies-policy/
You can at any time change or withdraw your consent on our website.
Please state your consent ID and date when you contact us regarding your
consent.
Do not sell or share my personal information
Deny Allow selection Customize
Allow all
Powered by Cookiebot by Usercentrics
1. Ablebits blog
2. Excel
3. Excel charts
HOW TO ADD A LINE IN EXCEL GRAPH (AVERAGE LINE, BENCHMARK, BASELINE, ETC.)
by Svetlana Cheusheva, updated on September 6, 2023
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 how to make a line graph in
Excel. 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, Excel 2016 and Excel 2019
provide a special Combo chart type, which makes the process so amazingly simple
that you might wonder, "Wow, why hadn't they done it before?".
* Draw an average line in Excel graph
* Add a line to an existing Excel chart
* Plot a target line with different values
* How to customize the line
* Display the average / target value on the line
* Add a text label for the line
* Change the line type
* Extend the line to the edges of the graph area
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:
1. Calculate the average by using the AVERAGE function.
In our case, insert the below formula in C2 and copy it down the column:
=AVERAGE($B$2:$B$7)
2. Select the source data, including the Average column (A1:C7).
3. Go to the Insert tab > Charts group and click Recommended Charts.
4. 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 MEDIAN
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.
1. 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:
2. Right-click the existing graph, and choose Select Data… from the context
menu:
3. In the Select Data Source dialog box, click the Add button in the Legend
Entries (Series)
4. 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.
5. 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:
6. 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.
Done! 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:
1. 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.
2. On the Format Data Series pane, go to Fill & Line tab > Line section, and
select No line.
3. 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):
4. Set the marker Fill to Solid fill or Pattern fill and select the color of
your choosing.
5. 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: How to
customize a graph in Excel. 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:
1. Click on the line to select it:
2. 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:
3. 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:
1. Select the last data point on the line and add a data label to it as
discussed in the previous tip.
2. Click on the label to select it, then click inside the label box, delete the
existing value and type your text:
3. Hover over the label box until your mouse pointer changes to a four-sided
arrow, and then drag the label slightly above the line:
4. Right-click the label and choose Font… from the context menu.
5. Customize the font style, size and color as you wish:
When finished, remove the chart legend 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:
1. Double-click the line.
2. 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:
1. Insert a new row before the first and after the last row with your data.
2. 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.
3. 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:
Tip. If you want to draw a vertical line in a scatter plot, bar chart or line
graph, you'll find the detailed guidance in this tutorial: How to insert
vertical line in Excel chart.
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
* How to add secondary axis to Excel chart
* How to draw a graph from different sheets
* How to make a pie chart in Excel
* Excel trendline types, equations and formulas
* How to do trend analysis in Excel
* How to create a histogram in Excel
EXCEL: FEATURED ARTICLES
* Merge multiple sheets into one
* Combine Excel files into one
* Compare two files / worksheets
* Merge Excel tables by matching column data or headers
* Merge rows without losing data
* Create calendar in Excel
(drop-down and printable)
* 3 ways to remove spaces between words
* Compare 2 columns in Excel for matches and differences
* Sum and count cells by color
Table of contents
* Draw an average line in Excel graph
* Add a line to an existing Excel chart
* Plot a target line with different values
* How to customize the line
* Display the average / target value on the line
* Add a text label for the line
* Change the line type
* Extend the line to the edges of the graph area
* Related articles
* Products
Windows
Ultimate Suite for Excel Shared Email Templates for Outlook Mail Merge for
Outlook Link Checker for Word
Mac Os
Text Toolkit for Excel Shared Email Templates for Outlook Mail Merge for
Outlook
Outlook Online
Shared Email Templates for Outlook Mail Merge for Outlook
Google Workspace
Add-ons for Google Sheets Add-ons for Google Docs
Ultimate Suite
70+ professional tools for Microsoft Excel
This comprehensive set of time-saving tools covers over 300 use cases to help
you accomplish any task impeccably without errors or delays.
* Merge multiple worksheets
* Compare sheets for differences
* Remove duplicates
* Merge cells in Excel
* Calculate dates and times
* Combine sheets by column headers
* Trim extra spaces
* Count characters and words
* Convert text to number
* See all tools
Works for: Windows
Read more about Ultimate Suite
Shared Email Templates
Reply to routine emails faster than ever
Do not waste your time on composing repetitive emails from scratch in a
tedious keystroke-by-keystroke way. Type your response just once, save it as
a template and reuse whenever you want.
* Quick replies for routine emails
* Fully customizable templates
* Gorgeous html-based designs
* Dynamic templates with fillable fields
* Comfortable template sharing
* Custom-tailored bulk mailings
* Company and team-wide signatures
* Easy user and team management
Works for: Windows•macOS•Outlook Online
Read more about Shared Email Templates
Link Checker for Microsoft Word
Manage links in your document with ease
Find all links in your document, get them verified, correct invalid ones and
remove unnecessary entries with a click to keep your document neat and up to
date.
* Search for all link types
* Define link states
* Edit multiple hyperlinks at a time
* Sort and filter links by different criteria
Works for: Windows
Read more about Link Checker
Text Toolkit for Excel
35+ handy options to make your text cells perfect
Instead of building formulas or performing intricate multi-step operations,
start the add-in and have any text manipulation accomplished with a mouse
click.
* Trim extra spaces
* Convert text to numbers
* Count characters and words
* Change letter case
* Extract text or numbers
* Remove text by its position
* Split cells by symbol, string, or mask
* Replace unwanted characters
* Eliminate leading zeros
* Swap text in your cells
* Find, extract, replace, and remove strings by means of regexes
* Merge columns, rows, or cells into one
* Combine rows by duplicates
Works for: macOS•Excel Online
Read more about Text Toolkit
40+ tools to boost your Google Sheets
This smart package will ease many routine operations and solve complex
tedious tasks in your spreadsheets.
Read more about Power Tools Try it for free
Add-ons for Google Sheets
Merge Sheets Function by Color Remove Duplicates Compare Sheets Substring
Batch Toolkit Table Styles Advanced Find and Replace See all products
Add-ons for Google Docs
Search & Navigate Doc Tools Styles for Google Docs
Mail Merge
Ideal for newsletters, proposals, and greetings addressed to your personal
contacts
Mail Merge is a time-saving approach to organizing your personal email
events. It offers:
* Customizable and adaptive mail merge templates
* Personalized merge fields depending on the recipient or context
* Dynamic attachments and images
* Mailing lists based on Excel tables
* "Send immediately" and "send later" scheduling
Works for: Windows•macOS•Outlook Online
Read more about Mail Merge
* Downloads
* Current versions
* Archived versions
* Documentation
* Support
* Contact support service
* Report a bug
* Frequently asked questions
* Blog
* About us
* Purchase
* Buy online
* Customer reviews
* Purchasing FAQ
* Licensing FAQ
* Get a Formal Quote
* Purchase Orders
* Trusted resellers
* License Agreement (EULA)
* Ultimate Suite for Excel
* Shared Email Templates for Outlook
* Power Tools for Google Sheets
* Downloads
* Documentation
* Support
* Blog
* About us
* Purchase
28 COMMENTS
1. erfan says:
2021-11-19 at 5:04 pm
how can i change the range of the line?
foe example only for last two months
Reply
* MWT says:
2023-11-05 at 10:45 pm
My solution was to put blanks as the value where the line shouldn't
appear.
Reply
2. Michelle says:
2021-07-24 at 4:07 pm
I’m trying to figure out how to add a run chart median to my pivot table or
data model. The median needs to be across the time observations potted on
graph. Not the median for values aiming each observation, ie a straight
line.
The dashboard I’m creating is based on extremely large data set and needs
to be dynamic and not require manipulation when data are updated each week.
Reply
3. Muhammad Junaid says:
2021-07-16 at 7:24 am
Hello there, is it possible to draw 2 reference lines to a same bar chart
graph. Means some times the reference line may be a range of value instead
of a single point. Please guide me about this, will be thankful.
Reply
4. Pavlin Borisov says:
2021-07-02 at 10:53 am
Hello there, Is there a way to make the chart to change colour?
For example : If it is below the target to be yellow and when it is above
the target to be green?
Reply
5. Peter says:
2021-05-16 at 11:44 am
Svetlana, Thanks for your awesome instructions.
Reply
6. Nalir Mohomed says:
2021-03-08 at 7:38 am
Many thanks for your good work.
Reply
7. Wen says:
2020-10-28 at 11:59 am
Thank you, Svetlana! Super helpful!
Reply
8. Frank says:
2019-12-25 at 11:38 am
Hello, is the demonstrated separate bars of target, by using Fill&Line and
Marker, from Excel later than 2013? Is it possible to do the same thing in
Excel 2013
Reply
9. Mushac says:
2019-11-30 at 5:53 pm
Hello , i need to add diffrent categorie for my histogram,for exemple from
0 to 7 doubtfull,from 7 to 12 medieum and from 12 to 18 good .
Reply
10. Ravi Kadam says:
2019-11-03 at 11:58 am
Hi, I have the Length & Breadth of a Rectangle defined in cell A1 & B1
respectively. How do I draw the Rectangle on the current sheet using values
defined in the cell A1 & B1?
Reply
11. Georgi Moshelov says:
2019-10-25 at 8:04 pm
It works the same way. Excel didn't allowed me to add the new series
(lines) properly. Just redid the same chart. Thank you again!
Reply
12. Georgi Moshelov says:
2019-10-25 at 7:52 pm
Svetlana, thank you for the new tutorial! I have a chart with 3 series and
I would like to add 3 additional series (average lines) one for each. Any
suggestion?
Reply
13. Avi Perl says:
2019-08-28 at 1:22 pm
I have a spreadsheet that keeps track of scores to tests I took. I want a
horizontal line on my chart at the 65 value, to visually show where my mark
was relative to a passing grade. However, I don't want to add entire column
titled "Passing grade" with a 65 written in every row, because it looks
really ugly. I'd prefer to have a single box with the value 65 in it.
However, when I try to use that box as a value in my chart in order to
create the line, the line only shows over the first value in my chart,
since the number 65 only appears once in my spreadsheet and is not written
for each column. Is there any way to fix this?
Reply
14. Dave Pritchard says:
2019-08-03 at 10:41 am
Can you I get a limit line drawn in an Area Chart?
Reply
15. Jagadeesh says:
2019-07-04 at 5:05 am
Hi All,
I have multiple data and i draw a excel graph.
My X axis vales are 10, 20 30 , 40 and corresponding Y axis vales are
plotted.
Now I want to know through excel graph,what is the correspondence vale of
25.
Can anybody explain how to draw line.
Reply
16. dave says:
2019-06-12 at 9:43 am
hy how can i plote run chart
Reply
17. Pete says:
2019-05-22 at 2:26 pm
What if I have a single cell containing the benchmark. Is there a way that
I can create this horizontal line from just this one cell. Rather than
having to add a whole extra column with the same benchmark number listed
multiple times.
Reply
* Nachi N says:
2020-03-28 at 10:36 am
Did you ever figure out how to do this?
Reply
18. Gordon Sutcliffe says:
2019-04-03 at 6:52 am
Hi, excellent tutorial as usual. I want to apply a single value target line
to a CLUSTERED BAR chart but this method seems only to work with a column.
Is there a method to apply the Custom Combo to a clustered column? Thanks
Reply
* Svetlana Cheusheva says:
2019-05-15 at 10:43 am
Hi Gordon,
If my understanding is correct you are looking to draw a vertical line in
a bar chart, right? If so, we have just published the tutorial that
explains how to do this:
How to add vertical line to Excel bar chart
Reply
* Nachi N says:
2020-03-28 at 10:37 am
Is there a way of adding a line without adding a whole column?
Reply
19. Harinath kolle says:
2019-03-14 at 1:30 pm
Hi all,
How to make trend line upto few bars in excel chart. I do not want trend
line for entire bars what ever available in chart.
Regards,
Harinath
Reply
20. Samuel Holmes says:
2019-02-05 at 9:13 pm
I can't get the last bit of your tutorial to work (Extending the line to
the edges of the chart data). I did as you said but my axis labels are not
lined up (i.e. the first label for the first bar with values in it is
actually lined up with the empty bar beside it).
Reply
* Samuel Holmes says:
2019-02-05 at 9:17 pm
Never mind, got it to work!
Reply
* Shawn Webb says:
2020-12-10 at 11:03 am
HI,
Can you share your work around ?
I am facing the same issue
Reply
21. Mary Trifuntova (Ablebits Team) says:
2018-12-20 at 11:43 am
Hello, Amit.
Unfortunately, there is no way to move this line manually. However, if you
change the values it refers to, it will be moved to the corresponding
position automatically.
Please let us know if you have any other questions.
Reply
22. Amit Sharma says:
2018-12-01 at 4:04 am
can i put this line anywhere. I want this line to be moved from current
location to other location. Is it possible. Please let me know. 9958612547
Reply
Post a comment
Click here to cancel reply.
I have read and accept the Terms of use and Privacy Policy
Δ
Thank you for your comment!
When posting a question, please be very clear and concise. This will help us
provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do
our best :)
Privacy policy Cookies policy Cookie settings Terms of use Legal
Contact us
Copyright © 2003 – 2024 Office Data Apps sp. z o.o.
70+ professional tools for Excel
Excel tasks taking too long?
Speed up with Ultimate Suite!
-
70+ professional tools for Excel
365–2016 to do your daily work
-
These should all come standard with Excel
Alex Lugan
I've been with Ablebits since 2018
Marv Wilson
Excellent customer service!
Vette Yates
I love Ablebits because it turns a 5-hour project into 5 minutes
Amy Harris
Well worth every dollar
John Graham
Ablebits is a major time saver
Erin Cole
The best Excel extension package!
Markus Pålsson
I prefer the simplistic and efficient way Ablebits handles tasks
Troy Cockrell
It has already paid for itself in less than 48 hours
Andrew Biebuyck
Still loving it after all these years...
Chris
Ablebits is a must-have Excel toolkit for everyone
Matt A
Ablebits support is second to none!
Craig Dennis
Buy this product! It's worth every penny!
Kevin Jacobs
Professional solutions!
Douglas
I wouldn't be able to do my job without Ablebits!
Julie Barie
Fantastic program at a reasonable price
Cliff
A+ product and service team
T Shaner
5 Stars from me. Great add-in that I use daily
Derek Balfe
Need Excel, you will want Ablebits Ultimate Suite
Kory Hirak
Time saver and excellent support makes Ultimate Suite a no-brainer
Santosh
Hooked on this product
Vicki R
I've been using the Ablebits product for several years
Lynette
AbleBits automated my entire Excel life
Andrew Binno
Ultimate Suite turns Excel into what it should have always been
Rick Polete
Inexpensive with TONS of value!
Debbie Foster
Can't live without it
Julie Bradley
Ablebits occupies a unique place for Excel users
Barry Beiles
Absolutely worth every penny!
Dave
Just one great product and a great company!
Don Scarpetti
A wonderful feeling to be amazed by a product
Deon van Niekerk
The Ablebits Excel add-in is an absolute must have
Dave Tarantelli
The software is the real deal
Christopher Rotolo
I would be lost without it
Marc Lajoie
It's a lifesaver!
Lauri Pantos
Great product - even better service
Alan Perestrello
Ablebits has saved me countless hours!
PJ Kuyper
A life and time saving tool with great customer service!
Paul Gittens
We discovered a gold mine!
Rebecca D. Becker
I highly recommend the Ablebits Ultimate Suite
Adam Pressman
Would recommend it to anyone who works with Excel
Judy Groom
Save hours of your life
Pennelope Blakely Kunkle
Ablebits makes magic
Tracey Miller
I have found the Ablebits app and website to be extremely useful
James F Joyner III
Ablebits Ultimate Suite is invaluable if you work with spreadsheets
Jodi V.
Extremely useful add-in with extensive functionality
Doug Walters
The best add-in for anyone
Jeff Garris
Exceptional product and service
Michael Toney
Excellent product and support!
Daniel Klaus
It is a productivity multiplier!
Jess Fowler
If that's not good service, I don't know what is
Jason Velez
My Excel life changed a lot… for the better!
Marco Antonio Gonçalves
Ultimate Suite is a treasure chest of useful tools
Mark Newbold
The best tools. Exactly what I needed
Emily Brown
AbleBits has been a huge time saver
Martin Weinberg
That one program has given me years of convenience
Liz Kaminski
Ablebits is a dream come true for any Excel user
Ilse Assmann
This add-in is really valuable for a very reasonable cost
James Kennedy
We couldn't imagine being without this tool!
Pat Rorabeck
Ablebits has allowed us to reduce timescale from hour to around 5-10 minutes
Thomas Savine
This software is by far the best I have ever purchased
Gary Loar
This product changed my working and investing experience
Tony Brinker
Ablebits offers amazing services!
Lanita Warner