How to Creatively Highlight Above and Below Average in Excel Line Chart?
Tags: tocreativelyhighligh
Published: 2021-08-21 04:14:43

---
title: How to Creatively Highlight Above and Below Average in Excel Line Chart?
published: true
date: 2021-08-12 17:02:43 UTC
tags: ToCreativelyHighligh
canonical_url: https://geekexcel.com/how-to-creatively-highlight-above-and-below-average-in-excel-line-chart/
---

In this tutorial, we will guide you to know the step-by-step instructions to **Creatively Highlight Above And Below Average In Excel Line Chart.** Get an official version of MS Excel from the following link: **[https://www.microsoft.com/en-in/microsoft-365/excel](https://www.microsoft.com/en-in/microsoft-365/excel)**

## Insert Data

- Firstly, you need to **enter the data** in the Excel spreadsheet.
- For instance, you have to create the **sample Workers detail data**.

![](https://geekexcel.com/wp-content/uploads/2021/08/Workers-details.png)<figcaption>Workers details</figcaption>

## Add Three helper columns

- Firstly, you need **three helper columns**.
- Here, the **first helper column** will contain the average data.
- Then, the **second helper column** will have **all the points** where **sales are larger than the average**.
- Now, the **third column** will have the **points** where the **sales are less than the average**.
- Then, in **Cell C2** , you have to write this **formula** and drag it down.

```
=AVERAGE($B$2:$B$7)
```

![](https://geekexcel.com/wp-content/uploads/2021/08/Click-on-average-value.png)<figcaption>Average column</figcaption>

- Now, in the **next column** , you need all the **UP values** , and it will be a **positive difference** between **average and sales data**.
- After that, you have to use the formula in **D2** and **drag it down**.

```
=IF(B2>C2,B2-C2,"")
```

![](https://geekexcel.com/wp-content/uploads/2021/08/UP-column.png)<figcaption>UP column</figcaption>

- You need a **DOWN column** that has **all the points** , and it will be a **negative difference** between the **average and sales data**.
- At last, you have to use the formula in **E2** and **drag it down**.

```
=IF(B2<C2,B2-C2,"")
```

![](https://geekexcel.com/wp-content/uploads/2021/08/Down-column.png)<figcaption>Down Column</figcaption>

## Select Sales Adviser and Sales and Insert a Line Chart

- In Sample Data, you need to **select the range A1:C7**.
- Then, you have to go to **insert** tab.
- Now, you need to click on the **Line and Area** chart and choose **Line with Markers** under the chart section.

![](https://geekexcel.com/wp-content/uploads/2021/08/Click-on-line-chart-1024x588.png)<figcaption>Click on Line with marker chart</figcaption>

- At last, you will get the **Line with Markers** result.

![](https://geekexcel.com/wp-content/uploads/2021/08/Line-with-marker-chart.png)<figcaption>Line with marker chart</figcaption>

## Add Average Series one more time

- You need to **right-click** on the **chart** and click on the **select Data option**.

![](https://geekexcel.com/wp-content/uploads/2021/08/Click-on-select-data-5.png)<figcaption>Click on the Select Data option</figcaption>

- Then, in **legends and entries** , you have to click on the **add** button.

![](https://geekexcel.com/wp-content/uploads/2021/08/Click-on-add-1.png)<figcaption>Click on Add</figcaption>

- Now, you have to select **average range C2:C7** as values of the series.

![](https://geekexcel.com/wp-content/uploads/2021/08/set-the-value-1024x759.png)<figcaption>Click on the Average range</figcaption>

- Finally, it will **insert a line** on the original average line.

![](https://geekexcel.com/wp-content/uploads/2021/08/final-line-output.png)<figcaption>Final result</figcaption>

## Add Up and Down Series to the Data

- You need to **add the Up and Down series** one by one to the series.
- Finally, after **adding all the series** , your charts looks like given below.

![](https://geekexcel.com/wp-content/uploads/2021/08/adding-all-series.png)<figcaption>Add all series</figcaption>

## Change Second Average Series, Up, & Down Series to Stacked Area Chart

- You need to **right-click** on any **data series** and click on **Change Series Type**.

![](https://geekexcel.com/wp-content/uploads/2021/08/click-on-change-series-type-1.png)<figcaption>Click on the Change series type</figcaption>

- Finally, this will **change the chart** a lot, and it will start taking a form that we want this **Excel line** chart to be.

## Select No Fill for Average Area Stacked Chart

- You need to **right-click** on the area of **average part** and click on the **format data series**.
- In line with the **fill option** , you have to go to **fill** and select **no fill** option.

## Format the chart to suit the theme

- You have to remove the **average legend** from the chart.
- Then, you need to **remove the grid lines** from the chart.
- Now, you need to name the **chart title**.

![](https://geekexcel.com/wp-content/uploads/2021/08/add-title-1.png)<figcaption>Add title</figcaption>

## Closure

Hope you like this article on how to **Creatively Highlight Above And Below Average In Excel Line Chart.** If you have any **questions** or **suggestions** regarding this lesson, feel free to use the **comments** section below to let us know.

Thank you so much for visiting  **[Geek Excel](https://geekexcel.com/)!! **If you want to learn more helpful formulas, check out [**Excel Formulas**](https://geekexcel.com/excel-formula/) **!!**

**Related Articles:**

- **[When to Use Bar of Pie Chart in Excel 365? – Easy Tricks!!](https://geekexcel.com/when-to-use-bar-of-pie-chart-in-excel-365-easy-tricks/)**
- **[How to Create a Stacked Chart with Showing Percentages in Excel?](https://geekexcel.com/how-to-create-a-stacked-chart-with-showing-percentages-in-excel/)**
- **[Excel Formulas to Create the Series of Dates by Month ~ Simple Guide!!](https://geekexcel.com/excel-formulas-to-create-the-series-of-dates-by-month-simple-guide/)**
- **[Formulas to Create a Series of Dates by Year ~ Complete Guide!!](https://geekexcel.com/excel-formulas-to-create-a-series-of-dates-by-year/)**
- **[Excel Formulas to Generate Series of Date by Day ~ Easy Tricks!!](https://geekexcel.com/excel-formulas-to-generate-series-of-date-by-day/)**