Completed
) Look at finished database and VLOOKUP formula to understand what the goal of the video is.
Class Central Classrooms beta
YouTube videos curated by Class Central.
Classroom Contents
Build Database with Excel Table Feature and VLOOKUP to Get Invoice Detail
Automatically move to the next video in the Classroom when playback concludes
- 1 ) Look at finished database and VLOOKUP formula to understand what the goal of the video is.
- 2 ) 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
- 3 ) Add Bold Format to Field Names / Column Headers so database interprets headers correctly
- 4 ) Custom Date Format to Date Field. This is carried forward to new records in Excel Table Database.
- 5 ) Add word wrap to Product Description. This is carried forward to new records in Excel Table Database.
- 6 ) Add Number Formatting: Currency and Percentage to number fields. This is carried forward to new records in Excel Table Database.
- 7 ) 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.
- 8 ) Convert Proper Data Set to Excel Table with Ctrl + T. Excel Table feature is Excel’s Database feature.
- 9 ) Add new column to database for Balance Due. Learn about Table Formula Nomenclature / Structured References for Excel Table formulas.
- 10 ) Name the Table.
- 11 ) Add new records to test database.
- 12 ) 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 / Structur…
- 13 ) Add Data Validation Drop Down List for invoice lookup.
- 14 ) 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.
- 15 ) Test database and lookup formulas.
- 16 ) Summary and Conclusion
