Data Visualization in Excel

Data Visualization in Excel

While Excel isn’t visualization software, it’s a versatile, powerful tool for professionals of all levels who want to analyze and illustrate datasets. With the recent adoption of python by microsoft, all Microsoft products, including excel have seamless integration making it even more powerful than ever before.

This course will attempt to cover both traditional and modern approaches to data visualization using MS Excel.

Learning Outcome

By the end of this course, the user should be able to create:

• Interactive dashboards that contain at least:
  • Bar & Column charts
  • Histograms & Pareto charts
  • Line charts & trend lines
  • Area charts
  • Pies & Donuts
  • Scatter plots & Bubble charts
  • Box & Whisker charts (Office 365, Excel 2016 or Excel 2019)
• Create powerful Dashboard Apps with Excel (frontend) and Python (backend)*
• Write UDFs (user defined functions) and use Numpy, Pandas and Machine Learning Libraries directly in Excel*
• Excel task automation with python*
• Load data from Web APIs directly into Excel*

*Optional

Pre-requisites

• 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)

Course Outline

Duration:

• With modern integration: 3 days
• Without Modern Pandas integration: 1 day

Timing:

One day of training encompasses a start time of 9am and an end time of 5 pm with an hour’s lunch break at 1pm and 2x 15 minutes breaks during lecture time.

1. Recap of basic excel
   1. Overview
   2. Data exploration
   3. Best practices
2. Prerequisites to creating the Dashboard
   1. Understanding the Dataset for the Dashboard
   2. Creating the visualizations
   3. Cumulative representation
   4. Drilled down representation
   5. Segmented representation
3. Designing the Dashboard in Excel
   1. Adding Charts to Dashboard
   2. Adding Filters/Slicers
   3. Fine tuning and finalizing

The following topics are relevant if the learner wants to deal with the latest in Excel technology using Python:

1. Basic Prerequisites
   1. Basic Python Overview
   2. Introduction to Pandas
   3. Series
   4. DataFrames
   5. Missing Data
   6. Group By with Pandas
   7. Merging, Joining, and Concatenating DataFrames
   8. Pandas Common Operations
   9. Data Input and Output
   10. Introduction to Visualization in Python
   11. Matplotlib Basics
   12. Matplotlib Introduction
   13. Line Plots
   14. Scatter Plots
   15. Seaborn Visualizations
   16. Pandas Visualization Overview
   17. Pandas Time Series Visualization
2. Xlwings
   1. How to connect to an Excel Workbook
   2. How to read and write single Values
   3. How to assign a name
   4. How to write Excel Functions with Python
   5. Range Shortcuts
   6. One-dimensional Data Structures
   7. How to write Values vertically
   8. Rows and Columns
   9. How to read two-dimensional Data Structures
   10. Advanced Reading with expand
   11. How to write two-dimensional Data Structures
   12. Range Indexing and Slicing
   13. Efficiency
3. Integration Essentials
   1. Running Python Scripts with "Run main"
   2. VBA Macros essentials
   3. Running Python Scripts with "RunPython"
   4. Run main vs RunPython
   5. Excursus
4. Visualizations
   1. How to write a Matplotlib Plot into Excel
   2. How to update the Plot
   3. How to change Size and Position
   4. How write a Seaborn Plot into Excel
   5. How to create Excel Charts with Python
5. User Defined Functions
   1. Preparations and your first UDF
   2. How to change the Name and Location of the Python Module
   3. Troubleshooting (UDF)
   4. UDFs - Behind the Scenes
   5. More complex UDFs and the @xw.arg Decorator
   6. How to create Numpy UDFs
   7. UDFs and Array Formulas
   8. How to create Dynamic Arrays with xlwings UDFs
   9. How to create Pandas UDFs
   10. How to add Docstrings