Subhanshi conducted a comprehensive Excel training session focused on private tables, Power Query, and dashboard creation. The class began with a review of private tables, covering how to create dynamic subtotals using slicers and filters, followed by a recap of creating pivot tables with calculated fields and conditional formatting. Subhanshi then demonstrated how to work with multiple tables by creating relationships between them using customer ID as the common key, allowing data from separate tables to be combined in a single pivot table. The session continued with detailed instruction on building dashboards, including creating multiple pivot tables with different views (city-wise sales, category-wise sales, product-wise sales, and sales representative-wise sales) and customizing charts with proper formatting, data labels, and titles. After a brief break, Subhanshi introduced Power Query, explaining its purpose for automating data transformation processes and demonstrating how to use it to combine multiple Excel files from different retailers into a single, clean dataset. The Power Query portion covered various transformation techniques including column multiplication, date calculations, data type changes, and text splitting, with emphasis on how applied steps are recorded and can be reused when new data is added to the folder. Subhanshi concluded by showing how the automated Power Query process allows dashboards to be updated automatically when new data files are added, eliminating the need for manual re-creation of complex data transformations and dashboards.
Dynamic Subtotals with Power Query
Subhanshi conducted a class on private tables and Power Query, but realized she had previously covered the topic with Ivan and needed to focus on advanced private table concepts. She demonstrated how to create dynamic subtotals using slicers and the SUBTOTAL function in Excel, showing how to filter data by region and calculate totals that automatically update based on the selected region. The class focused on practical applications for creating MIS reports with dynamic filtering capabilities.
Pivot Tables and Conditional Formatting
Subhanshi provided a recap of creating pivot tables and adding a target column with conditional formatting. She explained how to use calculated fields to create a target column based on amount thresholds and apply formatting to display "pending" or "target complete" status. Subhanshi then outlined plans to cover creating pivot tables from multiple tables, using customer details and sales data as an example.
Pivot Table Data Relationship Demonstration
Subhanshi demonstrated how to create a pivot table by combining data from two separate tables: the customer detail table and the sales table. She explained the importance of linking tables using a unique common column, such as customer ID, to ensure accurate data relationships. Subhanshi showed how to create a relationship between the two tables using the PivotTable Analyze tab and emphasized the need to select a unique common key for proper linking.
Excel Dashboard and Data Management
Subhanshi demonstrated how to create relationships between multiple tables in Excel and build dashboards with pivot tables and charts. She explained the process of joining tables using customer ID and creating private tables, then showed how to create different types of charts (line, pie, column, bar) and customize them with proper formatting and data labels. The session concluded with an introduction to Power Query, where Subhanshi explained how to combine data from multiple Excel files into a single table, transform the data using various operations like multiplying columns, splitting text, and changing data types, and automatically update the dashboard when new data is added to the folder.
Continue Your Excel Learning Journey
Master Excel with practical, hands-on training designed for real business scenarios. Learn advanced reporting, data analysis, dashboards, Power Query, Pivot Tables, automation, and more through guided exercises and industry-focused projects.
Ready to become an Excel expert?
π Explore the Advanced Microsoft Excel Training Program and start your learning journey today.
Β
