YoVDO

Excel for Accountants - VLOOKUP & PivotTables - Complete Lessons from Basic to Advanced

Offered By: ExcelIsFun via YouTube

Tags

Microsoft Excel Courses Data Validation Courses VLOOKUP Courses PivotTables Courses MATCH function Courses

Course Description

Overview

Dive into a comprehensive 1 hour 33 minute seminar on Excel for accountants, focusing on VLOOKUP and PivotTables. Master essential skills from basic to advanced levels, including number formatting, keyboard shortcuts, and Excel efficiency tricks. Learn to create exact and approximate match VLOOKUPs, build invoices, handle commission calculations, and manage payroll data. Explore PivotTables for creating summary reports with multiple criteria, cross-tabulated reports, and data analysis techniques. Practice grouping dates, calculating percentage changes, and displaying various statistical measures. Download provided start and finished files to follow along with 16 detailed examples, enhancing your Excel proficiency for accounting applications.

Syllabus

) Introduction.
) Why lookup is so common.
) Exact Match VLOOKUP to get Product Price & Data Validation Dropdown List.
) VLOOKUP & IFERROR Functions to build Invoice. Learn about Relative and Absolute (Locked) Cell References..
) Approximate Match VLOOKUP to get Commission Amount. Discussion of importance of Number Formatting in Excel..
) Approximate Match VLOOKUP to get Commission Rate. More about Number Formatting. Learn about General Number Formatting to wipe away all Number Formatting..
) Approximate Match VLOOKUP to get Tax Amount.
) Approximate Match VLOOKUP add Helper Column to Payroll Data Set..
) VLOOKUP & MATCH function to retrieve an Employee Record.
) VLOOKUP "Helper Column" to "Extend" Data Set for Complicated Reports. See 5 examples of Helper Columns to build complete Data Set for a PivotTable Report..
) Introduction to PivotTables as the best tool to take data in a Proper Data Set and create Summary Reports that contain Calculations with Conditions or Criteria. Learn how to use Tabular Report Layout and how to add Number Formatting to a PivotTable..
) Create Cross Tabulated PivotTable Reports. This is a PivotTable Summary Report that has Calculations made with two Criteria..
) Pivoting a PivotTable.
) Add Slicer to PivotTable. This is a PivotTable Summary Report that has Calculations made with three Criteria. Discussion about logic of criteria and how it applies to PivotTable..
) Create PivotTable Reports for Large Data Sets on a New Sheet..
) Grouping Dates by Year and Month.
) Using Show Values As to Create % Change Reports.
) PivotTable to show Total, Average, Min and Max Values for each Region.
) Summary of all 16 examples.


Taught by

ExcelIsFun

Related Courses

Using Covid-19 Data to Make Supply Chain Logistics Decisions in Spreadsheets
Coursera Project Network via Coursera
Analyze Data with Microsoft Excel
Codecademy
Conditional Formatting, Tables and Charts in Microsoft Excel
Coursera Project Network via Coursera
Data for Business Analysts Using Microsoft Excel
Microsoft via Coursera
Create Charts and Dashboards Using Microsoft Excel
Coursera Project Network via Coursera