Home>Blogs>Charts and Visualization>Weekly Sales Chart in Excel
Weekly Sales Chart
Charts and Visualization

Weekly Sales Chart in Excel

In today’s business world, charts and graphs have become an essential part of data analysis. They help to present complex data in a clear and concise manner, allowing us to quickly analyze and draw conclusions from the information presented. In this article, we will learn how to create a weekly sales chart in Excel. This chart will display employee-wise weekly sales and Mon to Fri sales trend of each employee in small columns. By the end of this article, you will have the knowledge to create an informative employee-wise weekly sales chart that will help you to better understand your business’s sales data.

 

Weekly Sales Chart
Weekly Sales Chart

Data Set for Weekly Sales Chart in Excel

To create the weekly sales chart in Excel, we will first need a data set. Below is the data set that we will use for this example:

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

Steps to create Weekly Seles Chart in Excel-

Inserting the Chart

  • Add a support column “Bottom Line” as keep values as Zero
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

Formatting the Chart

  • Right click on the chart and click on Select Data.
Select Data Option
Select Data Option
  • Select Data Source window will be 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 changing the chart type

 

Adding Up/Down Bars

  • Select 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 the 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

  • 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
  • Delete the Total and Bottom Line in Legends by double clicking and pressing 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.
employee-wise sales chart
employee-wise sales chart

Fill colors

  • Fill the Gradient Fill in the Down Bars with two Stops.
  • Select the Type as Linear and put the Angle as 90°.
  • Keep the color for the First Stop as Purple.
  • Choose Second Stop color white with 100% Transparency.
Down Bars Formatting
Down Bars Formatting
  • Choose Border for down bars as No line.
Mon-Fri sales trend
Mon-Fri sales trend

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

Visit our YouTube channel to learn step-by-step video tutorials

Youtube.com/@PKAnExcelExpert

 Watch the video tutorial for Weekly Sales Chart

PK
Meet PK, the founder of PK-AnExcelExpert.com! With over 15 years of experience in Data Visualization, Excel Automation, and dashboard creation. PK is a Microsoft Certified Professional who has a passion for all things in Excel. PK loves to explore new and innovative ways to use Excel and is always eager to share his knowledge with others. With an eye for detail and a commitment to excellence, PK has become a go-to expert in the world of Excel. Whether you're looking to create stunning visualizations or streamline your workflow with automation, PK has the skills and expertise to help you succeed. Join the many satisfied clients who have benefited from PK's services and see how he can take your Excel skills to the next level!
https://www.pk-anexcelexpert.com

Leave a Reply