How to Make an Automated Data Entry Form in Excel (Step-by-Step Tutorial)

About this video

Kevin builds an automated cookie order form for the Kevin Cookie Company in Excel from a blank workbook. He inserts a logo, adds a title and fields for name, cookie type, quantity, delivery method and delivery date, then formats input cells with light grey fill and outside borders, using Format Painter to copy that formatting. He adds checkboxes for three cookie types — chocolate chip, sugar and oatmeal raisin — and uses conditional formatting with a formula rule so a quantity input box only appears when its checkbox is true; he edits the rule to remove the dollar sign before the row number to make it a relative reference so it can be copied down. Data validation restricts delivery method to a list of three options and delivery date to a date between today and today next year. On a Responses sheet he links cells back to the form with simple = formulas, then records a macro named CopyFormData that inserts a new row, copies the current form values, pastes values only, reformats the date, and clears the form (setting checkboxes to false). He assigns that macro to a ‘Place Cookie Order’ shape button, tests it with sample orders, unlocks only the input cells via Format Cells > Protection, protects the sheet and workbook structure, hides gridlines for a kiosk look, and saves as an Excel macro-enabled workbook, noting macros must be enabled when reopened.

Key takeaways

• Use checkboxes for selections and conditional formatting based on the checkbox’s TRUE/FALSE value to show a quantity field only when an item is selected, making the reference relative before copying the rule down.
• Restrict entries with Data Validation: a comma-separated List for fixed choices like delivery method, and a Date range for delivery dates.
• Create a Responses sheet whose top row links to the live form with = formulas, so current form values are mirrored automatically.
• Record a macro instead of coding: insert a row, copy the mirrored values, Paste Values only, reformat the date, then clear the form and reset checkboxes to FALSE.
• Before sharing, unlock only the input cells, protect the sheet and workbook structure, and save as a macro-enabled workbook — users must enable macro content for the submit button to work.

Episode details: published January 30, 2025 · 29:54 long. Summary written by Self Growth Videos from the episode itself. Business results and any income figures mentioned are Kevin Stratvert’s own claims, shared for education — they are not typical or guaranteed.

← Previous: Notion AI Tutorial: Is It Really Worth It? · Next: How to Use DeepSeek →

All Kevin Stratvert videos · Kevin Stratvert profile · Kevin Stratvert on YouTube