Summary
Highlights
Renato Martínez, product manager at the Chamber of Commerce and International Training, welcomes attendees to the masterclass with Professor Sergio. He confirms audio and video quality, and announces an extension for students to complete their course material before the graduation ceremony. He also provides recommendations for optimal viewing of the masterclass, including screen orientation for mobile devices and adjusting video quality.
Professor Sergio begins the masterclass by outlining its two main parts: generating a report from multiple data sources using Power Query and designing dashboards. He explains that Power Query, available in Excel 2016 and later versions, allows users to connect and transform data from various formats. He demonstrates how to connect to an existing Excel file containing sales data spread across different sheets (September, October, November) and transform it into a single, unified dataset.
Sergio demonstrates how to filter the connected data in the Power Query Editor to include only tables, excluding sheets that are not relevant. He then shows how to select and expand the relevant data column, clean up column headers, and change the data types to ensure accuracy. He also adds a new 'Month' column derived from the 'Date' column and moves it next to the date for better organization. The process highlights the importance of using Excel's table format for easier data management.
After transforming the data in Power Query, Sergio shows how to load the cleaned and combined data into a new Excel sheet. He explains how to access the Power Query Editor again for any necessary corrections and introduces the 'Queries & Connections' panel. He then prepares for the dashboard creation by defining the report's objective: comparing the performance of two salespersons, Alexandra and Brenden. This involves creating pivot tables for each salesperson to summarize their total sales.
Sergio demonstrates how to create 3D pie charts for each salesperson's sales data from their respective pivot tables. He customizes the charts by removing buttons, changing titles, applying pre-defined styles, and selecting a color scheme. He also shows how to add a 'Month' slicer to filter data for both pivot tables simultaneously, emphasizing the importance of connecting the slicer to multiple tables if they share the same data source.
Sergio explains how to create a new sheet for the dashboard, remove gridlines, and apply a background color. He then moves the charts and slicer from the pivot table sheet to the dashboard sheet, arranging them for optimal layout. He demonstrates how to link dynamic total sales figures from the pivot tables to display clearly on the dashboard using text boxes and WordArt, ensuring that the displayed numbers update automatically with slicer selections. He also adds placeholder images for the salespersons to visually enhance the dashboard.
Sergio illustrates the main benefit of using Power Query: automatic data updates. He demonstrates how to add new sales data for December to the original source file. By simply refreshing the data in the main Excel sheet, Power Query processes the new information, updating the pivot tables and, consequently, the dashboard. This instantaneous update saves significant time and effort, transforming hours of manual work into a simple refresh operation.
Renato and Sergio answer common questions from the audience, including Power Query's availability across Excel versions, its non-modifying nature of source files, the flexibility to compare more categories/salespeople, connection capabilities to other data sources, and the benefits of using Excel's table format. Renato then transitions into promoting an advanced Excel certification program, highlighting its comprehensive modules, additional training in Power BI, Macros, and Office Suite tools, and a 70% scholarship opportunity for deserving candidates.
Renato stresses the value of the Microsoft-certified Excel program, encouraging attendees to apply for the scholarship by scheduling a consultation. He explains the application process, emphasizing the commitment required and the criteria for selection. He clarifies that while the process is intentionally selective, it ensures that only individuals truly dedicated to advancing their Excel skills and career prospects benefit from the program. He re-iterates deadlines and offers further assistance for any queries.