Excel, Dashboard EX-030

Course Overview

Design a dashboard with the various options and items Excel offers to monitor management changes more closely.

Prerequisites

Intermediate Excel. Knowledge of pivot tables

Syllabus

Understand the issues related to dashboards.

Take a moment to:

• Evaluate, plan, structure;
• Know and understand the data sources;
• Define the objective;
• Indicators and results.
• Choose the appropriate visual items.

Create and use interactive named ranges:

• Create and manage names.
• Use named ranges in:
  • Statistical functions;
  • Lookup functions (INDEX, MATCH, CHOOSE);
• Put functions in a name:
  • OFFSET, COUNT, COUNTA, COUNTIF, IF, INDIRECT;
  • A pivot table;
  • An interactive graph.

Go beyond standard graphs;

• Shapes or images related to a cell or name;
• Histograms using the REPT function;
• Thermometers
• Traffic lights;
• Speedometers;
• Juxtaposed graphs;
• Line graphs or columns with varying colours;
• Graphs connected to a drop-down list.

Use conditional formatting:

• In a data table;
• With formulas (IF, AND, OR, GRANDE.VALEUR, etc)
• On a histogram that uses REPT ;
• Use specific icons.

Use other functions based on the item and the data:

• LINE, COLUMN, ADDRESS, CAR;
• TEXT (and formats) CONCATENATE, LEFT, RIGHT, MID;
• FIND YEAR, MONTH, DAY, NETWORK;
• DAYS IFERROR, NA.

Use form controls to interact with items;

• Checkbox, drop-down list, scroll bar;

Use simple command macros:

• Change the type of graph;
• Update the data;
• Change the datasheet;
• Manage the connection to external data.

Finalize the dashboard.