Employee wise deficit and surplus sales chart
Charts and Visualization

Employee Wise Deficit and Surplus Sales Chart

In this article you will learn how to create an Employee Wise Deficit and Surplus Sales Chart. This chart will give 2 types of analysis.

  1. This will give an employee level sales comparison.
  2. Sales deficit and surplus for each employee against the sale target.

This is a good-looking chart and can be used for business dashboard or presentation.

Below are the steps to create this chart in MS Excel-

Let’s say we have employee wise sales and target  data as given in below image.

Employee wise Sale and Target data
Employee wise Sale and Target data
  • Before creating the chart, we need to take below support columns-
    • Take a support column “Sales for chart” on column D.
    • Put formula “=MIN(B2:C2)” on cell D2.
    • Take a support column “Below from Target” on column E.
    • Put formula “=IF(C2>B2,C2-B2,NA())” on cell E2.
    • Take a support column “Above of Target” on column F.
    • Put formula “=IF(B2>C2,B2-C2,NA())” on cell F2.
    • Take a support column “Data Label” on column G.
    • Put formula “=MAX(B2:C2)+9” on cell G2.
    • Fill down the formula “D2:G11
Create Support columns
Create Support columns
  • Now select the range “A1:A11” and “C1:G11” by pressing Ctrl key.
  • Go to the Insert tab>>Charts>> Insert a 2D Stacked Column Chart

Insert a 2D Stacked Column Chart

Insert a 2D Stacked Column Chart

  • Right click on the chart and click on “Format Data Series”

 

Format Data Series option
Format Data Series option
  • Change the Gap Width as 40% in Format Data Series.

 

Format Data Series Window
Format Data Series Window
  • Remove the Gridlines, Legend and Vertical Axis from the chart.
Remove the Gridlines, Legend and Vertical Axis
Remove the Gridlines, Legend and Vertical Axis
  • Right click on the column of chart and click on Change Series Chart Type.
Change Series Chart Type option
Change Series Chart Type option

 

  • Change Chart Type window will be opened.
  • Change the Chart type for “Target” and “Data Label” series as “Line with Markers“.
Change Chart Type window
Change Chart Type window
  • Go to the Insert>>Shapes>>Insert a lines.
Insert a line
Insert a line
  • Drag a small line on the worksheet.
  • Select the line and go to Format tab>>Shape Outline.
  • Choose line color as Black.
  • Choose the weight of line as 1½ pt.
Line formatting
Line formatting

 

  • Copy the line (shape) and paste on the Target line chart.
  • Right click on the Target line chart  and click on Format Data Series.
Paste the line (shape) in Target line chart.
Paste the line (shape) in Target line chart.
  • Select No line under Line option in Format Data Series Window.

 

Format Data Series Window
Format Data Series Window
  • Right click on “Sale for Chart” series and click on Format Data Series.
  • Go to the Fill & Line option and choose the Gradient fill.
  • Choose the Type as Linear.
  • Choose the Angle as o°.
  • Take two Gradient Stops and fill Black and Teal color as given in below image.

Gradient fill for Sale for Chart Series.

Gradient fill for Sale for Chart Series.

  • Right click on “Below From Target” series and click on Format Data Series.
  • Go to the Fill & Line option and choose the Gradient fill.
  • Choose the Type as Linear.
  • Choose the Angle as o°.
  • Take two Gradient Stops and fill Black and Red color as given in below image.
Gradient fill for "Below from Target" Series
Gradient fill for “Below from Target” Series
  • Right click on “Above of Target” series and click on Format Data Series.
  • Go to the Fill & Line option and choose the Gradient fill.
  • Choose the Type as Linear.
  • Choose the Angle as o°.
  • Take two Gradient Stops and fill Black and Green color as given in below image.
Gradient fill for “Above of Target” Series
  • Now Right click on the “Data Label” Series line chart and click on Format Data Series.
Format Data Series option
Format Data Series option
  • Select the No line in the Line option.
No line in the Line option
No line in the Line option
  • Select the Marker.
  • Choose Built-in under Marker Options.
  • Choose Type as circular marker.
  • Choose Size as 30.
Marker Formatting
Marker Formatting
  • Now go to the Fill of Marker Options and fill the Gradient color from preset gradients as given in below image.

 

fill the Gradient in the markers
fill the Gradient in the markers
  • Choose the No line the Border.
No line the Border
No line the Border
  • Now go to Insert tab>>Shapes>>Insert a Rectangle.
  • Drag the rectangle over the chart as given in below image.
  • Fill the black color in the rectangle.
  • Choose No outline in Outline.

 

Insert a Rectangle.
Insert a Rectangle
  • Right click on the rectangle and click on Format Shape.
Format Shape option
Format Shape option
  • Go to the Effects option.
  • Go to 3D Rotation.
  • Go to Preset and select Perspective Relaxed
 select Perspective Relaxed preset
select Perspective Relaxed preset
  • Change Y Rotation as 299.6°

 

Y Rotation as 299.6°
Y Rotation as 299.6°
  • Now go to 3-D Format.
  • Choose the Top Bevel as given in below image.

  • Choose the Top bevel Width and Height as 12pt.
  • Choose the Depth as 12pt.

 

Format Shape Window
Format Shape Window
  • Right click on the chart and click on Send to Back.

 

Send to Back
Send to Back
  • Now we will add the data labels.
  • Right click on the Markers and click on Add Data Labels.
Add the Data labels
Add the Data labels
  • After adding the data label, right click on the data label and click on Format Data label.
  • Click on Value From Cells.
  • Select the range from “B2:B11“.
Value form Cells
Value form Cells
  • Click on Center available below Label Position.
Label Position
Label Position
  • Right click on the “Above of Target” series (Green Series) and click on Add data label.
  • Right click on the “Below from Target” series (Red Series) and click on Add data label.
  • Format the Data Label as White font color and font as Bold.
Employee Wise Deficit and Surplus Sales Chart
Employee Wise Deficit and Surplus Sales Chart

 

Click here to download the this excel file.

PK
My Name is PK. I am founder of PK-AnExcelExpert.com. I am a Microsoft Certified Professional. I have more than 12 years of experience in Data Visualization, Excel Automation and dashboard creation. Excel is my passion and I like to do always something innovative in Excel and share it with other people.
https://www.pk-anexcelexpert.com

Leave a Reply