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