Weekly Sales Chart
Charts and Visualization

Weekly Sales Chart in Excel

In this article you will learn how to create below given beautiful weekly sales chart. In this chart we have displayed employee wise weekly sales as well as Mon to Fri sales trend of each employee in small columns.

Weekly Sales Chart
Weekly Sales Chart

Below is the data set of this chart.

Data Set for Weekly Sales Chart
Data Set for Weekly Sales Chart

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

  • Add a support column “Bottom Line” as keep values as 0
Bottom line support column
Bottom line support column

 

  • Select the Range “B3:I9”
  • Go to Insert >>Charts>>Column Chart>>Insert a 2D clustered column chart
Insert 2 Clustered Column Chart
Insert 2 Clustered Column Chart
  • After inserting the chart will look like below image
2 Clustered Column Chart
2 Clustered Column Chart

 

  • Right click on the chart and click on Select Data.
Select Data Option
Select Data Option
  • Select Data Source window will opened.
  • Click on Switch Row/Column
Select Data Source window
Select Data Source window
  • After clicking on Switch Row/Column button our chart will look like below image
Chart After clicking on Switch Row/Column button
Chart After clicking on Switch Row/Column button
  • Right click on Total column and click on Change Series Chart Type.
Change Series Chart Type option
Change Series Chart Type option
  • Change Chart type window will be displayed.
Change Chart type window
Change Chart type window
  • Select the Line chart for Total Series.
Change Chart Type Window
Change Chart Type Window
  • Take the same chart type (Line Chart) to Bottom Line Series also.
Change Chart Type Window
Change Chart Type Window
  • After changing the chart type our chart will look like below image.
After change the chart type
After change the chart type
  • Select the the Total line chart (Green Line)
  • Click on the Chart Elements button (Plus button of chart)
  • Check the Up/Down Bars
Up/Down Bars Option
Up/Down Bars Option
  • Right click on the line and click on Format Data Series.
Format Data Series option
Format Data Series option
  • Change the Gap Width around 30% (make sure small columns should go behind the down bars)
Change Gap Width
Change Gap Width
  • Right click on line and click on Add Data Labels.
 Add Data Labels
Add Data Labels
  • Data labels will be added.
  • Right click on data label and click on Format Data Labels.
 Add Data Labels
Add Data Labels
  • Select Above in Label Position.
Change Label Position
Label Position
  • Go to the Fill and Line option and select No line.
No line option
No line option
  • Our chart will look like below given image after choosing the No line for Total Series.
Chart after choosing the No line for Total Series
Chart after choosing the No line for Total Series
  • Double click on the Total in Legends and press Delete button in your keyboard.
  • Double click on the Bottom Line in Legends and press Delete button in your keyboard.
Remove Total and Bottom Line from Legends
Remove Total and Bottom Line from Legends
  • Right click on Down Bar (Black Bar) and click on Format Down Bars.
Format Down Bars option
Format Down Bars option
  • Fill the Gradient Fill in the Down Bars with two Stops.
  • Choose Type Linear and Angle 90°.
  • Choose First Stop color Purple.
  • Choose Second Stop color white with 100% Transparency.
Down Bars Formatting
Down Bars Formatting
  • Choose Border for down bars as No line.
Border for down bars as No line
Border for down bars as No line

Now we can change the data label shapes-

  • Right click on data label and choose a shape from Change Data Label Shape.
Change Data Label Shape
Change Data Label Shape
  • Now change the data label background color as purple.
  • Change the data label Font color as white.

Our weekly Sales chart is ready it will look line below image.

 

Weekly Sales Chart
Weekly Sales Chart

Click here to download Weekly Sales Chart.

Watch the video tutorial for Weekly Sales Chart

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