Build Database with Excel Table Feature and VLOOKUP to Get Invoice Detail

Overview

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

Grab it

Learn how to build an invoice database using Excel 2016's Table feature and utilize VLOOKUP for retrieving invoice details in this comprehensive 26-minute video tutorial. Master the Excel Table feature to automatically apply formatting and formulas to new records, understand Table Formula Nomenclature for structured references, and create dynamic ranges. Explore proper data set creation, custom formatting, formula implementation, and the power of VLOOKUP and MATCH functions for efficient data lookup. Follow along with step-by-step instructions to create a functional invoice database, complete with data validation and lookup formulas, while gaining valuable insights into Excel's database capabilities.

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.