Excel Charting Training Course

Default Charts That Stick

By default, using the keys Alt F1 will produce a column chart. To customise your own default chart based on any chart type, select the data then:

▪ Go to CHANGE CHART TYPE

▪ Select specific chart icon on the left

▪ Right click on one of the variant icons along the top and SET AS DEFAULT CHART

▪ Select data and use Alt F1 again to apply new default chart

Template Charts

1. Create a column chart based on selected data 2. Switch row/column and add a style you like to the chart. 3. Format the value axis to minimum 1000. 4. Add a Chart Title and a Data Table. 5. Remove all gridlines. 6. Format the chart (change series colours , and move the legend) 7. Right click inside the chart and select SAVE AS TEMPLATE 8. Now select the data and create a column chart 9.

Go to CHANGE CHART TYPE > TEMPLATES and click on specific template

Dynamic Data, Tiny Trends

Make charts dynamic by turning data into a table

1. Select a cell in the data range and go to HOME > FORMAT AS TABLE. Select a table icon (shortcut: Ctrl T)

2. Click OK in the ‘Create Table’ box

3. Insert a column chart from the table

4. Add a new row of data below the table and note the chart updates

Sparklines

Sparklines in Excel are tiny charts that fit within a single cell to provide a visual representation of data trends. They are useful for showing trends in a series of values, such as seasonal increases or decreases, economic cycles, or to highlight maximum and minimum values1.

There are three types of sparklines in Excel:

Line Sparklines: Show data trends over time.

Column Sparklines: Compare different values.

Win/Loss Sparklines: Highlight positive and negative values

Uncertainty and Relationships

Error Bars are graphical representations that show the variability of data. They help illustrate the uncertainty or error in the data points, providing a visual indication of the precision of measurements.

Customising Error Bars

1. Create average, maximum and minimum scores for test 1 from the source data using AVERAGE, MAXIFS and MINIFS functions

2. Create an ‘Uncertainty’ calculation:  =(Max test 1 – Min test 1)/2

3. Create Col Chart (alt F1)

4. Add Error bars

5. Right click an error bar > Format Error Bars

6. Click on Custom and in the 'Value' field select the 'Uncertainty Range' for both positive and negative fields

Scatter Charts

Use Scatter Charts to show correlations between specific pairs of data ranges.

1. Ensure your data is organized in two columns. The first column will be your X-axis data, and the second column will be your Y-axis data.

2. Select the data columns and go to INSERT > SCATTER CHART icon

3. Add a Trendline to the chart. The closer the scatter chart is to the trendline, the more correlation there is between the 2 data ranges

Waterfall Charts

A Waterfall chart in Excel is a type of chart that helps visualize how an initial value is affected by a series of intermediate positive or negative values, leading to a final result. It’s particularly useful for understanding financial data, such as tracking profits and losses over time