The Complete Excel Course with AI Integration
How to build an employee database in Excel
Most staff lists break the same way: stray spaces, one department spelt three ways, duplicate IDs, names crammed into one cell. The Complete Excel Course with AI Integration builds one that does not: the Dunder Michael Paper Company employee database, an industry-standard project designed after talking with people from many different industries. You clean the list, sort it, filter it and guard it with drop-down lists, in Excel alone, and the section where it begins, Managing data in Excel, is free by email.
Taught by Sagnik Bhattacharya and Virginia Thorn
What you will be able to do
You will be able to:
- Turn a staff list into an Excel table that takes in each new employee and keeps its total row counting them.
- Sort by department and then by name, and filter by branch, status, manager or a salary band with AND and OR conditions.
- Clean a raw staff list with TRIM, Find and Replace scoped to one column and Paste Special values, and fix its case with PROPER.
- Find duplicate employee IDs with conditional formatting and remove each repeated row whole with Remove Duplicates.
- Split full names with Flash Fill and Text to Columns, join fields with CONCAT and TEXTJOIN, and rebuild every email to one pattern.
- Stop bad entries at the source with drop-down lists for department, status, location and job title.
Projects
What you will build.
The course has 14 industry-standard projects. These three matter most here.
-
Project 06
PeopleInsight HR workbook
Employee data joined across sheets with one formula.
An HR workbook, and where a clean staff list starts paying out: XLOOKUP fills in each employee's city, state and region from another sheet, and FILTER lists everyone who meets your conditions as the data changes.
-
Project 11
FormatWizard
One click that checks a whole sheet for you.
A VBA project on sales data, for anyone set on code: recorded macros first, then loops and validation rules that check a sheet row by row and mark the problems they find.
-
Project 02
Dunder Michael Paper Company employee database
The database this page builds: a staff list of IDs, names, departments, job titles, managers, branches, status, salaries, hire dates and emails, taken from raw and messy to clean and guarded.
Lessons
The lessons that matter, in course order.
The Complete Excel Course with AI Integration has 186 lessons. This page picks out 19 of them, 1 h 57 min of video. Each lesson keeps its number in the course.
Where to start
Lesson 19 Excel tables
Begin at Lesson 19, where the staff list becomes a table: that whole section, Managing data in Excel, is free by email with its practice files, so you can build the first part of the database before you buy. New to typing data into Excel? Take Lesson 5 and Lesson 6 first. The text functions and drop-down lists that finish the database run from Lesson 41 to Lesson 47.
19 of 186 lessons · 1 h 57 min
01 Microsoft Excel fundamentals: building a foundation from scratch How Excel reads what you type, practised on a budget: text, numbers and dates in cells, AutoFill for a run of dates, and number and date formats, the groundwork for hire dates that sort as dates.
- 5 Your first Excel spreadsheet Cells, rows, columns and data types in a budget 8 min
- 6 Number formatting in Excel Currency, date and percentage formats for a budget 5 min
03 Managing data in Excel: sort, filter and tables (Dunder Michael Paper employee database) The database begins, in the section that is free by email: an Excel table with a total row, single and multi-level sorts, filters on text and numbers with AND and OR, a raw sheet cleaned with TRIM, Find and Replace and Paste Special, duplicates found and removed, names split with Flash Fill and Text to Columns, frozen headers and print settings.
- 19 Start here Excel tables Create and format a table, structured references and total row 8 min
- 20 Sorting data in Excel Single and multi-level sort on an employee database 6 min
- 21 Filtering data in Excel AutoFilter, text and number filters on employee data 10 min
- 22 Cleaning data in Excel TRIM, Paste Special values and Find and Replace 13 min
- 23 Remove duplicates in Excel Find and delete duplicate rows in an employee list 6 min
- 24 Flash Fill in Excel Split first and last names automatically 3 min
- 25 Text to Columns in Excel Split data by delimiter into separate columns 6 min
- 26 Freeze panes in Excel Lock header rows and columns while scrolling 3 min
- 27 Printing in Excel Print area, page orientation, scaling and page layout 6 min
04 Essential formulas and functions (IF, SUMIF and more) SUMIF and COUNTIF, practised on sales records: a headcount for each department or branch and the salary bill behind it, one formula apiece.
- 36 SUMIF and COUNTIF in Excel Conditional sum and count with one criterion 8 min
05 Text functions and data validation (enhancing data quality) The database finished: CONCAT, the ampersand and TEXTJOIN to join fields, UPPER, LOWER, PROPER and TRIM for case and spaces, FIND, SEARCH and SUBSTITUTE to locate and swap text, a location split into city and state, drop-down lists that refuse anything off the list, and a Ninja Challenge on a messy employee list.
- 41 CONCAT, the & operator and TEXTJOIN in Excel Combine text from multiple cells 6 min
- 42 TRIM, UPPER, LOWER and PROPER in Excel Clean messy text and fix capitalisation 6 min
- 43 FIND, SEARCH and SUBSTITUTE in Excel Locate and replace text with formulas 5 min
- 44 Text to Columns wizard in Excel Split a column by a comma delimiter 4 min
- 45 Data validation in Excel Create drop-down lists for consistent data entry 4 min
- 46 Ninja Challenge: problem Clean messy data with text functions and validation 4 min
- 47 Ninja Challenge: solution PROPER, Text to Columns and drop-down lists 7 min
Instructors
Who teaches it.
Instructor, Excel with AI
Virginia Thorn
Emmy Award winner. Teaches Excel with AI at Coding Liquids.
Questions
Asked before starting.
How do I create an employee database in Excel?
Build it as The Complete Excel Course with AI Integration from Coding Liquids does in its Dunder Michael Paper Company project, an industry-standard staff database: put the records in an Excel table, clean them, sort and filter them, and guard every new entry with drop-down lists. You start from a raw data set with the finished database to compare, and the section where it begins, Managing data in Excel, is free by email with its practice files. This page's path is 19 lessons, 1 hour 57 minutes of video, and needs nothing but Excel.
What columns should an employee database have in Excel?
One column per fact, one row per person, and a unique employee ID first. The database in The Complete Excel Course with AI Integration keeps the ID, first and last name, department, job title, manager, branch or location, status (active or inactive), salary, hire date and email, each in a column of its own so it can be sorted, filtered and checked. Anything stored together, such as a city and state in one cell, is split into separate columns (Lesson 44), and a column like department takes its values from a fixed list (Lesson 45).
Is there a free employee database template for Excel?
Better than a template: a whole section of The Complete Excel Course with AI Integration that builds the database with you, Managing data in Excel, is free by email with every lesson and its practice files. Ask for it on the course page, confirm your address and it opens in your account: the raw staff list, the cleaned one and the lessons that turn one into the other. A template gives you empty columns; this gives you the method, so you can do the same to your own staff list the next time it arrives in a mess.
How do I clean employee data in Excel step by step?
In the order The Complete Excel Course with AI Integration cleans its raw staff list. TRIM the stray spaces and paste the results back as values (Lesson 22); fix misspellings, mixed case, doubled @ signs and stray dashes with Find and Replace, scoped to one column when it should not touch the rest; remove duplicate rows by employee ID (Lesson 23); then fix case with PROPER and nest it inside TRIM to clean both at once (Lesson 42). The Ninja Challenge from Lesson 46 hands you a messy employee list to clean alone, with the solution after.
How do I make an employee database searchable in Excel?
Filters make it searchable at once, and formulas make it searchable from anywhere. In The Complete Excel Course with AI Integration the table's filters show one branch or two, a salary band with Between, only the active staff, or everyone whose manager field contains a name, with OR to catch two at once (Lesson 21). The PeopleInsight HR workbook then goes further: XLOOKUP fills in each employee's city, state and region from another sheet (Lesson 69), and FILTER lists every employee who meets your conditions, AND and OR included (Lesson 76).
Can an Excel employee database update itself as staff join?
Yes, when it lives in an Excel table. In The Complete Excel Course with AI Integration, a new employee added at the foot of the staff table joins it straight away, and the salary total counts the new row as soon as the total row is switched back on; an employee can be inserted mid-list the same way (Lesson 19). A headcount by department with COUNTIF (Lesson 36) then moves with every joiner, so no total is left counting an old version of the list.
How do I stop wrong entries in an employee list?
With data validation: each column that should hold a fixed set of values offers a drop-down list and refuses anything typed that is not on it. The Complete Excel Course with AI Integration builds the lists from a sheet of approved values, departments first (Lesson 45), then status, location and job title in the Ninja Challenge, where existing locations that break the rule are caught and replaced from the list (Lesson 47). Moving someone to another department becomes a choice from the list, and a department can no longer be spelt three ways.
Do I need VBA or macros to build an employee database in Excel?
You can build every part of it without code, which keeps the database easy for anyone to maintain. The table, the filters, the cleaning and the drop-down lists in The Complete Excel Course with AI Integration are plain Excel that any colleague can open and follow. VBA earns its place when you want one click to check a whole sheet, and the course teaches that too in FormatWizard, from Lesson 136 on: recorded macros first, then VBA with loops and If statements that checks a sales sheet, highlights problems and handles errors.
Excel with AI
See the whole course.
The Complete Excel Course with AI Integration: 16 hours of video in 186 lessons, built around 14 industry-standard projects, with lifetime access, a Certificate of Completion and a 14-day money-back guarantee.
The same course
For another job, goal, level, topic or comparison.
By job
Finance and accounting
- Accountants and finance teams
- Bookkeepers and accounts assistants
- FP&A and budget analysts
- Auditors and fraud examiners
- Payroll administrators
- Tax accountants and advisers
- Banking professionals
- Cost and management accountants
- Financial controllers
- Credit control and receivables teams
- Accounts payable teams
- Financial planners and advisers
- Credit analysts and loan officers
- Insurance underwriters
- Insurance claims handlers
- Reconciliation analysts
- Treasury analysts
- Medical billing and coding specialists
- Fund accountants
- Billing administrators
- AML and KYC analysts
- Actuarial analysts
- Insurance agents and brokers
- Retirement planners
HR and office administration
Operations and supply chain
- Operations and project managers
- Procurement and purchasing officers
- Logistics coordinators
- Inventory controllers and storekeepers
- Supply chain analysts
- Warehouse managers and supervisors
- PMO analysts
- Import and export coordinators
- Production planners
- Demand planners
- Fleet and transport managers
- Apparel merchandisers
Data and reporting
Sales, marketing and customer service
Business and management
Engineering, construction and manufacturing
- Quantity surveyors and estimators
- Civil and site engineers
- Quality managers and engineers
- Construction project managers
- Health and safety officers
- Document controllers
- Production managers and supervisors
- Mechanical and electrical engineers
- Industrial engineers
- Planning engineers
- Sustainability and ESG analysts
- Maintenance planners
Retail, property and hospitality
Healthcare and life sciences
Education and careers
Public sector, non-profit and legal
By goal
- Instructor support
- Certificates
- How long it takes
- Is Excel worth learning
- A retirement calculator
- A budget tracker
- A task tracker
- A KPI dashboard
- A budget vs actual report
- A sales goal tracker
- A chart dashboard
- An employee lookup
- A self-refreshing sales report
- A checking macro
- A Power Automate flow
- A compound interest calculator
- A SIP calculator
- An Excel test for a job
- A step-up SIP calculator
- Practice exercises
- Excel skills for a CV