Excel for Accountants - Power Query & PivotTables to Import & Clean Data and Build Reports

Overview

Save Big on Coursera Plus. 7,000+ courses at $160 off. Limited Time Only!

Grab it

Learn advanced Excel techniques for accountants in this comprehensive 51-minute video tutorial. Master Power Query to import and clean multiple text files, merge lookup tables, and create a single dataset for PivotTable reports. Explore PivotTable basics, including monthly profit change calculations, cross-tabulated reports with slicers, and gross profit analysis using calculated fields. Discover how to efficiently update reports with new data and leverage the Excel Data Model to significantly reduce file size. Gain practical skills to streamline data processing, create insightful financial reports, and enhance your Excel proficiency for accounting applications.

Syllabus

) Introduction to video topics and Excel files and how to navigate large workbook and video.
) Overview of all Power Query & PivotTable steps in the video, including looking at Text Files that we need to import..
) Power Query To Import and Append Multiple Text Files with Sales Data. See how to use the From Folder feature to import many Text Files into a Single Proper Data Set thatr you can then use for PivotTable Reports..
) Power Query To Clean Data. Clean imported data before it is loaded to an Excel Sheet or the Data Model..
) Power Query To Merge Lookup Tables With Sales Table. See how to use the Combine, Merge, Left Outer Merge Feature to replace the VLOOKUP Function..
) PivotTable Basics and Monthly Profit Change and % Change Report using Show Values As feature and the PivotTables Group by Month feature..
) Build Cross Tabulated Report With Slicer and learn how to use the “Show Report Filter Page” option to create many PivotTables, each on a new sheet with a single click..
) Gross Profit Report, including a Formula in a PivotTable (Calculated Field).
) Add New Data and Update Reports.
) Excel 2013 or 2016 Data Model to Reduce File Size Dramatically.
) Conclusion & Summary.