digest
Short Summary
Main recommendation: Build a simple Excel "database" by formatting the field names, entering one record with the correct number formats, converting the range to an Excel Table (Ctrl+T), and adding the Form button to the Quick Access Toolbar so new records can be entered through a data entry form. The video notes that Excel does not technically treat this as a database, but it works as a table for storing data.
Key Points
- Bold the field names (column headers) first so Excel recognizes them as headers, not records.
- Set the correct number formats on the first record (for example, a 10-digit ID displayed with dashes, currency for salary, and a phone number format), because the formats are copied down as new records are added.
- Convert the data range into an Excel Table so new records added directly below it are automatically included and formatted.
- The Form command is not on the Ribbon; it must be added to the Quick Access Toolbar through the "All Commands" list, then used to enter records into the table.
Details Worth Noting
- Concrete instructions:
- Step 1: Type the column header names and apply bold (select them and press Ctrl+B).
- Step 2: Type the first record's ID, then press Ctrl+Enter to keep the cell selected. Go to Home > Number group > dialog launcher > Format Cells > Number tab > Special. Choose Social Security to start, then select Custom and enter the code for the 10-digit ID: four zeros, dash, two zeros, dash, four zeros (0000-00-0000). Click OK. The number is stored without dashes; only the display changes.
- Continue with Tab to enter the first name, last name, and hire date (for example 8/2/2019). For full-year dates, open the Number dialog launcher and choose a Date format with a four-digit year.
- Enter the starting salary (for example 69,430), then Ctrl+Enter and use the Number drop-down to select Currency.
- Enter the phone number, Ctrl+Enter, then in the Format Cells dialog under Special choose Phone Number.
- Enter the email address; Excel automatically makes it a hyperlink. Right-click and choose Remove Hyperlink if you do not want that.
- Step 3: Click any single cell in the data, then Insert > Table (or press Ctrl+T). Confirm "My table has headers" is checked and click OK.
- Name the table in Table Design > Properties (the speaker uses "Employee Table").
- Step 4: Add the Form button: move the Quick Access Toolbar below the Ribbon (drop-down > Show Below the Ribbon). Open More Commands (or right-click > Customize Quick Access Toolbar). Under "Choose commands from," select All Commands, type F to jump to the F's, select Form, click Add, then OK.
- Click Form, then New to enter records. Ctrl+semicolon inserts today's date in a form field. Tab through fields; pressing Enter on New adds the record. Click New Record to add another, and Close when finished.
- The form entries appear in the Excel table already formatted, so you enter raw values (such as an unformatted phone number) and the table's formats are applied.
- The speaker says the form is an older feature that is not on the Ribbon, and that the "All Commands" list lets you add any favorite commands to the toolbar.
- The speaker points out that adding a table and naming it makes it easier to reference later.
Bottom Line
Excel can serve as a simple records store: format once, make it a named Excel Table, and use the Form button on the Quick Access Toolbar for consistent data entry.
