Welcome to Excel Jet Consult blog post. In this tutorial, we are going to learn how to create simple and dynamic charts in Excel. Let’s get started
Video
Sample Data
In the screenshot below, we’ve got sales report showing the Total Sales by Month and Products. In cell C15, we’ve got dropdown list data validation that houses all the product list which we can select from. In addition, in cell K1, we use equal to operator to reference the Product in cell C15 dropdown
- So, in cell K2, we want to use the XLOOKUP Function to return the Total Sales for Months by selected Product in cell K1.
- To do that, in cell K2, execute the formula: =XLOOKUP(J2:J13,A2:A13,XLOOKUP(K1,B1:H1,B2:H13))
- Click Enter.
That would have populated the Total Sales for Month by Snickers as seen below
If we change the Product in cell C15 to, for instance, Laptop, the result in cell K2:K13 will change dynamically.
Create Column Chart
The next thing we wanna do is to create column chart based on the Total Sales by Month and Product.
- To do that: Select a single cell inside the J1:K13 result data
- In the Charts group of the Insert tab, click on Insert Column or Bar Chart and Select 2D Clustered Column Chart
Based on the chart inserted, Style 9 was selected in the Chart Design contextual ribbon tab of the Chart Stlyes. In addition, the chart grid-line and the Y-axis range were turn off and the Data Labels was enabled.
Create 3D Pie Chart
Next, we want to create 3D Pie Chart based on the Total Sales by Month and Product.
- To do that: Select a single cell inside the J1:K13 result data
- In the Charts group of the Insert tab, click on Insert Pie or Doughnut Chart and Select 3D Pie Chart
Based on the chart inserted, Style 7 was selected in the Chart Design contextual ribbon tab of the Chart Stlyes. In addition, the chart Legend was placed on the right of the chart from the default bottom the Data Labels was enabled.
Finally, choosing Laptop from cell C15, the charts dynamically updated and we can gain insight into the Sales pattern and make decision
See you in the next video