Data Analytics with Excel | Excel Tutorial

  During the 2025 Intergalactic Learn with the Nerds Day hosted by Pragmatic Works, Emily Taylor delivered an engaging session on data analytics using Excel. This tutorial explored powerful tools like Power Query, Power Pivot, and Excel functions, offering practical insights for transforming raw data into actionable intelligence.

Getting Started with Power Query

• Emily begins by importing data using the Power Query Editor, emphasizing its ability to clean and transform data before loading it into Excel.
• She demonstrates how to rename queries, change data types (e.g., zip codes to text), and apply transformations that persist with future data refreshes.
• Advanced users can leverage the Advanced Editor to script transformations using M code, such as inserting spaces in column names while preserving identifiers like “Project ID.”

Loading and Exploring Tables

• After cleaning the data, Emily uses Close & Load to bring the queries into Excel as structured tables.
• She highlights the benefits of tables, such as filtering and sorting, and demonstrates how to analyze employee data by location or project.

Mastering Excel Functions

1. XLOOKUP: Used to find employee names and salaries based on employee IDs. Emily explains how it simplifies lookups compared to VLOOKUP.
2. VLOOKUP: Demonstrated with a focus on column index numbers and formatting results as currency.
3. COUNTIF: Used to count the number of project tasks assigned to a specific employee.

Leveraging Copilot in Excel

• Emily introduces Copilot as a tool to generate formulas and uncover insights, such as identifying whether an employee’s salary is above or below their department’s average.
• She explains how to verify Copilot availability by checking the Excel version and license, and ensuring autosave is enabled.
• Copilot also helps generate formulas like SUMIF to calculate total hours per project.

Building a Data Model with Power Pivot

• Emily walks through enabling the Power Pivot add-in and adding tables to the data model.
• She switches to Diagram View to create relationships between tables (e.g., Employee ID, Project ID, and Location).
• A Date Table is added to enable time-based filtering in pivot tables.

Creating Pivot Tables

• Emily builds pivot tables to analyze projects by year, department, and task hours.
• She demonstrates formatting options, such as adding thousand separators and customizing field settings.
• Multiple pivot tables can be created on the same sheet to compare different dimensions of the data.

Conclusion

Emily wraps up by encouraging viewers to explore more with Power BI in the next session. Her tutorial showcases how Excel, when combined with Power Query, Power Pivot, and Copilot, becomes a powerful tool for data analytics. For those interested in deeper learning, Pragmatic Works offers an Excel Bootcamp and on-demand courses that integrate Excel with Power BI.

Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on Excel and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks.