Course Content
Advanced Word
0/9
Advanced Document Setup and Formatting Concepts
Advanced Text and Paragraph Formatting
Styles, Templates and Automated Features
Tables, Columns, Tabs and Document Layout
Mail Merge and Labels
Advanced Find, Replace and Document Automation
Advanced Graphics, Images and Objects
Reviewing, Protection and Collaboration
Test
Advanced Spreadsheets
This topic covers the ICDL Advanced Spreadsheet module: conditional formatting, advanced functions, charts and tables, data analysis and what-if scenarios, data validation and auditing, macros and templates, and collaboration features.
0/9
Advanced Workbook and Data Setup
Named Ranges, 3D References and Linking
Logical and Conditional Functions
Statistical, Mathematical, Date/Time and Text Functions
Data Management: Tables, Sorting, Filtering and Subtotals
Advanced Charts and Visualisation
Data Analysis Tools and What-If Scenarios
Auditing, Protection and Macros
Excel Test
Advanced Presentation
This topic covers the ICDL Advanced Presentation module: planning professional presentations, using slide masters and templates, working with graphical objects, charts and diagrams, integrating multimedia and animation, embedding and linking data from other sources, and managing presentations for different audiences.
0/9
Presentation Planning
Slide Masters & Templates & Themes
Advanced Text & Paragraph Formatting
Working With Images, Graphics & Design
Animations and Transition
Working With Charts, Tables and SmartArt
Reviewing, Comments, Proofing and Collaboration
Output & Distribution
Test
Advanced Databases
This topic covers the ICDL Advanced Database module: designing and planning relational databases, creating tables and relationships, developing advanced queries, building forms and subforms, generating complex reports, using macros and SQL for automation, and implementing database security and linking data from external sources.
0/9
Advanced Database Concepts and Design
Advanced Tables and Field Properties
Relationships and Referential Integrity
Advanced Select Queries
Action Queries: Update, Append, Delete and Make-Table
Advanced Forms and Controls
Labels and mailing reports
External Data, Macros and Final Database Tools
Test
00:45:00
Test
Protected: Icdl advanced

Advanced Spreadsheets

Lesson 6 – Advanced Charts and Visualisation

This lesson focuses on creating and formatting charts so that data is communicated clearly and professionally. You will learn how to choose appropriate chart types, format key elements, use advanced chart features such as combined charts and sparklines, and link charts to dynamic data.

Overview of chart types and visualization including column chart, line chart, pie chart, combined chart, and sparkline examples. Each chart type should be clearly labeled and visually distinct, arranged in a clean grid layout with a white background. Use blue and gray tones for consistency and professionalism. Include axis lines for column, line, and combined charts. The sparkline should be minimal and without axes. Only include one sparkline example.

1. Creating charts from data

Charts are created from data in a worksheet. Good chart design starts with a clean, well-structured data range.

  • Select appropriate data ranges, including headings for categories and values.
  • Create common chart types such as column, bar, line and pie charts using the Insert → Charts options.
  • Move a chart between the current worksheet and its own separate chart sheet when you need more space for the chart.
  • Change the chart type if the original choice does not communicate the information clearly (for example, change a column chart to a line chart).

Creating a column chart from a selected data range in a spreadsheet. Show a spreadsheet with a clean, well-structured data range including category and value headings. Highlight the selected data range. Display the Insert → Charts menu with the Column Chart option chosen. Show the resulting column chart with vertical bars representing the data, placed either within the worksheet or on a separate chart sheet. Use blue and gray tones for clarity and professionalism.

2. Choosing the right chart type

Different chart types are suited to different messages. Selecting the right chart helps your audience understand the data quickly.

  • Column and bar charts – compare values across categories (for example, sales by region).
  • Line charts – show trends over time (for example, monthly sales through the year).
  • Pie charts – show proportions of a whole (for example, market share percentages).
  • Combined charts – show two related series with different visual styles in one chart (for example, column for sales amount and line for number of units).
  • Understand the difference between clustered charts (side-by-side bars/columns for comparison) and stacked charts (parts of a whole within each category).

Comparison of column, line, pie, and combined charts using the same dataset. Show a simple dataset with four categories (A, B, C, D) and two data series: Sales Amount and Units Sold. Create four separate chart panels: Column Chart, Line Chart, Pie Chart, and Combined Chart. Each chart should be clearly labeled and use consistent blue and gray tones. Column chart shows sales amount as vertical bars. Line chart shows sales amount over categories. Pie chart shows proportion of sales amount. Combined chart shows column bars for sales amount and a line for units sold. Use a clean layout with white background.

3. Formatting chart elements

Formatting chart elements improves clarity and makes charts easier to read.

  • Edit chart titles so they clearly describe what the chart shows (for example, Monthly Online Sales – 2025).
  • Add and format axis titles to explain the meaning of category and value axes (for example, Month, Sales (£)).
  • Show, reposition and format legends so that series are easy to identify.
  • Adjust axis scales (minimum, maximum and major units) to present the data clearly without distortion.
  • Change the display units on the value axis (for example, display values in thousands or millions) to make large numbers easier to read.
  • Format data series, including line style, marker style and column/bar width, to highlight important series.
  • Add and format data labels to show values on the chart where appropriate.
  • Format columns, bars, pie slices, plot area or chart area to display an image (for example, a company logo or a themed background) where this adds value.

Formatted column chart showing Monthly Online Sales – 2025. Include a clear chart title, axis titles labeled 'Month' and 'Sales (£)', a legend identifying two series (Online and In-Store), formatted data labels showing values on each bar, adjusted axis scale with display units in thousands, and styled bars with varying widths and colors. Add a subtle company logo in the chart area background. Use blue and gray tones for clarity and professionalism. Ensure all elements are clearly visible and properly aligned. Use realistic monthly sales data for both series, with Online sales generally higher than In-Store sales. Example: January – Online: £800k, In-Store: £500k; February – Online: £850k, In-Store: £520k; March – Online: £900k, In-Store: £550k; April – Online: £870k, In-Store: £530k; May – Online: £920k, In-Store: £560k; June – Online: £950k, In-Store: £580k. Ensure the axis scale matches the data values accurately, with appropriate spacing and tick marks (e.g., 0 to 1000 in increments of 100).

4. Advanced chart features

Advanced features help you show trends, highlight key points and display data with different scales.

  • Create combined charts, such as column and line, or column and area, to show different series in a single visual.
  • Add a secondary axis for data series with very different scales (for example, sales amount vs number of units) so that both series can be read clearly.
  • Add trendlines (for example, linear trendline) to show the overall direction of data over time.
  • Add or remove data series from a chart when the underlying source data changes.
  • Change the chart type for a specific data series (for example, main series as columns and comparison series as a line on the same chart).
  • Change chart layout and style quickly using built-in chart layout and style options.
  • Emphasise key data points with special formatting (for example, different colour for a target value or the current month).

Combined column and line chart showing monthly sales amount and number of units sold. Include a secondary axis for units sold, a linear trendline for sales amount, and highlight the current month with a different color. Use a clear chart title, axis labels ('Month', 'Sales (£)', 'Units Sold'), a legend identifying both series, and formatted data labels. Use blue for columns, orange for line, and red for the highlighted current month. Display the chart on a white background with gridlines.

5. Sparklines – mini charts in cells

Sparklines are small charts displayed inside a single cell. They are useful for showing trends at a very compact scale alongside the data.

  • Insert sparklines (for example, line or column sparklines) to show patterns of change for each row of data.
  • Place sparklines next to the related data so that the trend can be seen at a glance.
  • Format sparklines (for example, show markers for high and low points) to highlight important changes.
  • Clear or edit sparklines when the underlying data or analysis needs change.

Row of sparklines showing trend for each product. Display a table with product names in the first column and monthly sales data in the next columns. In the final column of each row, include a sparkline showing the sales trend for that product. Use line sparklines with markers for high and low points. Use a clean white background and blue tones for the sparklines.

6. Linking charts to dynamic data

Charts linked to tables or well-defined ranges will update automatically as the data changes.

  • Base charts on tables or structured data ranges so that new rows and columns are included automatically.
  • Observe how charts update when underlying values change, helping to keep reports current.
  • Extend the data range and update chart sources when adding new time periods or categories.
  • Refresh charts after changing the source data or adding new series.

7. Good design principles

Well-designed charts are clear, focused and easy to interpret.

  • Keep charts simple and uncluttered – remove unnecessary gridlines and decorations.
  • Avoid unnecessary 3D effects that can distort perception of values.
  • Use clear labels, legends and titles so the viewer does not need to refer back to the data table.
  • Use colours consistently across multiple charts (for example, always use the same colour for a particular region or product).
  • Ensure sufficient contrast for readability when charts are printed or projected.
  • Check that the chosen chart type and formatting support the key message of the data.

8. Practical Activity

Complete this activity to apply the lesson skills.

  1. Create a column chart from a monthly sales table, including month names and sales values.
  2. Add axis titles (for example, Month and Sales (£)) and a meaningful chart title (for example, Monthly Sales – Current Year).
  3. Add data labels to show exact values and adjust the axis scale and display units (for example, thousands) if necessary to improve readability.
  4. Create a line chart from the same data to highlight the trend over time and compare it with the column chart.
  5. Create a combined column and line chart showing sales (£) as columns and number of units as a line on a secondary axis.
  6. Insert a row of line sparklines next to each product or region to show the sales trend in miniature.
  7. Experiment with chart styles and layouts to improve clarity, and remove any elements that do not add useful information.

Previous
Next