Intermediate Excel. Knowledge of pivot tables
LessonsDesign a dashboard with the various options and items Excel offers to monitor management changes more closely.
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;
- Traffic lights;
- 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.
All registration cancellations made within five (5) working days of the date of the course are billable at 100%. Synesis reserves the right to cancel any public courses due to lack of sufficient participants.
1440, rue Ste-Catherine Ouest 10th Floor