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.
Below is the data set of this chart.
Below are the easy steps to create this chart in Excel-
- Add a support column “Bottom Line” as keep values as 0
- Select the Range “B3:I9”
- Go to Insert >>Charts>>Column Chart>>Insert a 2D clustered column chart
- After inserting the chart will look like below image
- Right click on the chart and click on Select Data.
- Select Data Source window will opened.
- Click on Switch Row/Column
- After clicking on Switch Row/Column button our chart will look like below image
- Right click on Total column and click on Change Series Chart Type.
- Change Chart type window will be displayed.
- Select the Line chart for Total Series.
- Take the same chart type (Line Chart) to Bottom Line Series also.
- After changing the chart type our chart will look like below image.
- Select the the Total line chart (Green Line)
- Click on the Chart Elements button (Plus button of chart)
- Check the Up/Down Bars
- Right click on the line and click on Format Data Series.
- Change the Gap Width around 30% (make sure small columns should go behind the down bars)
- Right click on line and click on Add Data Labels.
- Data labels will be added.
- Right click on data label and click on Format Data Labels.
- Select Above in Label Position.
- Go to the Fill and Line option and select No line.
- Our chart will look like below given image 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.
- Right click on Down Bar (Black Bar) and click on Format Down Bars.
- 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.
- Choose 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.
- 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.
