Faysal AzizData Visualisation and Dashboards in Excel
Core Excel charts with an optional Python and xlwings extension
Why this course
Design an Excel dashboard that presents a prepared dataset clearly using charts, filters and slicers. The one-day core focuses on conventional workbook analysis and visual presentation.
An optional two-day extension introduces local Python, pandas and xlwings for data preparation, plots and workbook automation, making the full programme three days. This is distinct from Microsoft cloud-based Python in Excel.
Learning outcomes
- Choose suitable chart types for the question and dataset.
- Combine charts and filters/slicers into a usable dashboard.
- Present cumulative, drill-down and segmented views where the data supports them.
- In the extension, manipulate sample data with pandas and transfer values between Python and Excel.
- In the extension, automate selected workbook tasks and add Python-generated plots.
- On the supported Windows UDF track, create small NumPy/pandas user-defined functions and document their behaviour.
Prerequisites
- Licensed copy of Excel
- Experience in using Excel for querying and manipulating data
- Basic scripting in Excel
- Experience and understanding of filing system
- Internet connection
- Basic education in mathematics (understanding of linear and non-linear mathematics at high-school level)
The optional track requires a supported desktop Excel installation, compatible Python/xlwings packages and approved macro/add-in settings. Complete xlwings UDF practice requires Windows; macOS does not support that UDF feature.
4 modules
01Core day — Excel dashboard foundations3 topics
- Overview
- Data exploration
- Best practices
Prepare the dashboard dataset
- Understanding the Dataset for the Dashboard
- Creating the visualizations
- Cumulative representation
- Drilled down representation
- Segmented representation
- Bar/column, histogram/Pareto, line/trend, area, pie/doughnut, scatter/bubble and box-and-whisker charts where supported by the chosen Excel edition.
- Add charts, filters and slicers to the dashboard; fine-tune presentation and review the result.
02Optional extension — Python and pandas preparation17 topics
- Basic Python Overview
- Introduction to Pandas
- Series
- DataFrames
- Missing Data
- Group By with Pandas
- Merging, Joining, and Concatenating DataFrames
- Pandas Common Operations
- Data Input and Output
- Introduction to Visualization in Python
- Matplotlib Basics
- Matplotlib Introduction
- Line Plots
- Scatter Plots
- Seaborn Visualizations
- Pandas Visualization Overview
- Pandas Time Series Visualization
This is a focused introduction for the workbook exercise, not a complete Python or machine-learning course.
03Optional extension — Workbook connection and integration13 topics
- How to connect to an Excel Workbook
- How to read and write single Values
- How to assign a name
- How to write Excel Functions with Python
- Range Shortcuts
- One-dimensional Data Structures
- How to write Values vertically
- Rows and Columns
- How to read two-dimensional Data Structures
- Advanced Reading with expand
- How to write two-dimensional Data Structures
- Range Indexing and Slicing
- Efficiency
- Running Python Scripts with "Run main"
- VBA Macros essentials
- Running Python Scripts with "RunPython"
- Run main vs RunPython
Use a small supplied web-API data example where authorised and available; external services and libraries are not universally accessible from every Excel environment.
04Optional extension — Charts and user-defined functions5 topics
- How to write a Matplotlib Plot into Excel
- How to update the Plot
- How to change Size and Position
- How write a Seaborn Plot into Excel
- How to create Excel Charts with Python
- Preparations and your first UDF
- How to change the Name and Location of the Python Module
- Troubleshooting (UDF)
- UDFs - Behind the Scenes
- More complex UDFs and the @xw.arg Decorator
- How to create Numpy UDFs
- UDFs and Array Formulas
- Native dynamic-array behaviour versus legacy xlwings expansion options; use the supported approach for the installed Excel version.
- pandas UDF conversions and docstrings.
- Review a small Excel front end with local Python processing, including dependency, macro and maintenance considerations.
A programme built around your team.
Share your training goals and requirements.