Build Database with Excel Table Feature and VLOOKUP to Get Invoice Detail
Offered By: ExcelIsFun via YouTube
Course Description
Overview
Syllabus
) Look at finished database and VLOOKUP formula to understand what the goal of the video is..
) Database must be a Proper Data Set: 1) Field Names in First Row, 2) Records in subsequent Rows, 3) Empty Cells or Excel Column/Row Headers Around Data Set.
) Add Bold Format to Field Names / Column Headers so database interprets headers correctly.
) Custom Date Format to Date Field. This is carried forward to new records in Excel Table Database..
) Add word wrap to Product Description. This is carried forward to new records in Excel Table Database..
) Add Number Formatting: Currency and Percentage to number fields. This is carried forward to new records in Excel Table Database..
) Add formula for Amount owed after discount, tax rate and shipping. See the ROUND function. This is carried forward to new records in Excel Table Database..
) Convert Proper Data Set to Excel Table with Ctrl + T. Excel Table feature is Excel’s Database feature..
) Add new column to database for Balance Due. Learn about Table Formula Nomenclature / Structured References for Excel Table formulas..
) Name the Table..
) Add new records to test database..
) Add Proper Field names using Table Formula Nomenclature / Structured References for Excel Table formulas in the lookup area. Learn about relative references in Table Formula Nomenclature / Structured References for Excel Table formulas..
) Add Data Validation Drop Down List for invoice lookup..
) Create VLOOKUP and MATCH Lookup formula to lookup Invoice Detail. Learn about how to lock (absolute) references in Table Formula Nomenclature / Structured References for Excel Table formulas..
) Test database and lookup formulas..
) Summary and Conclusion.
Taught by
ExcelIsFun
Related Courses
A Beginner’s Guide to Data Handling and Management in ExcelPackt via FutureLearn Introduction to Financial Accounting: The Accounting Cycle
University of California, Irvine via Coursera ADP Entry-Level Compensation and Benefits Analyst
Coursera Microsoft Excel: Advanced Data Analysis and Visualisation
Cloudswyft via FutureLearn Advanced Data Visualisation and Analysis in Excel
Packt via FutureLearn