Chart Examples 1- Conditional bar charts, Chart builder

Please sign up for the course before starting the lesson.

Exercise 1

In the sheet Basic Bar Chart

  • Create a formula in cells F7 to G24 that will split the numbers in column E such that anything that says Index in column C goes to the Index column and the others go to the Share column.
  • Create a BAR chart that shows the numbers with a different colour for shares versus index movement.

Chart Exercise

Exercise 2

A

In the sheet ‘Choose Metrics”

  • Create a chart based on the information in A10 to D18, where the Revenue and Net Profit are shown as a column chart and % Net Profit is shown as a line chart.

Chart Exercise

B

  • In cell B24 we have inserted a drop down choice of Revenue or Net Profit.
    • In B27 to B36 create a formula so depending on what we choose (Revenue or Net Profit), those numbers come through.
    • Create a chart that will show this choice as a column chart and the % Net Profit as a line chart.
    • Make the chart title equal to the choice in B24

Chart Exercise

C

  • In cell C44 to D44 we have included a Yes/ No dropdown. If it is Yes we want to see that information in the chart, if No we don’t want to see it.
    • In B45 to D54 create a formula that will look at the Yes / No option and pull the correct data through from the original data (B10 to B18)
    • Create a chart with Revenue and Net Profit as a Column Chart and % Net Profit as a line chart.
    • Switch off Revenue and % Net Profit

Chart Exercise

D

  • We want to show the column chart with the Line chart but now we want to include a benchmark line based on the average % Net Profit.
    • In E67 to E75 we want to pull through the average shown in D76
    • Create the chart (column chart for Revenue and Net Profit and Line Chart for % Net Profit) and add a Line Chart for the Average

Chart Exercise

To see the full course contents click here.

Back to: Advanced Excel Course > Charting In Excel