If your work involves capturing and organizing information but you don’t have the budget (or the need) for a full-blown SQL database, Excel can do the job for you. With a bit of setup, you can create an automated, searchable database that saves you time, reduces errors, and simplifies your workflow.
In this guide, I’ll walk you through building a professional Excel system step by step. You’ll also get the full working template free so you can use it straight away or customize it to suit your exact needs.
Table of Contents
- Watch the Video
- Get the Free Excel Database Template
- Why Build a Database in Excel?
- Step 1: Create the Client Form
- Step 2: Build the Database
- Step 3: Automate Data Entry with Office Scripts
- Step 4: Add Duplicate Checks in the Form
- Step 5: Protect the Form
- Step 6: Analyze Your Data
- Recap: What We Built
- Take Your Skills Further
Watch the Video
Get the Free Excel Database Template
You don’t have to build it from scratch. Grab the free working template I used in this tutorial:
Enter your email address below to download the free file.
Why Build a Database in Excel?
Most of us already use Excel for lists, reports, and analysis. But with a few smart tricks, Excel becomes more than just a spreadsheet: it turns into a structured database.
This setup works for all kinds of tasks:
- Client management
- Inventory tracking
- Expense tracking
- Employee information
- Project management
- …and more
The key is to build three components:
- A data entry form
- A structured database
- A little automation to tie them together
Step 1: Create the Client Form
We’ll start with a clean workbook and add a sheet called Client Form. This is where users will enter information.
Add the following fields:
- Full Name
- Service
- Industry
- Date
- Notes
Some key touches to make the form easy to use (see the video for step by step instructions):
- Data validation: add a dropdown list for Industry (e.g. Education, Finance, Healthcare, Retail, Technology).
- Date field: use the TODAY function: =TODAY() so today’s date is automatically inserted.
- Formatting: use shapes like rounded rectangles, icons, and turn off gridlines to make it look like a real form.
- Unlocked cells: use worksheet protection to unprotect only the input cells so users can tab smoothly between fields.
Step 2: Build the Database
Next, create another sheet called Database. This will store all client records.
- Add column headers: Full Name, Email, Service, Industry, Date, Notes.
- Convert the range into an Excel Table (Insert > Table) and name it ClientData.
- Format the Date column as Short Date (Home > Number Formats).
- Apply Conditional Formatting to highlight duplicate emails (since duplicates can creep in).
For extra protection, add a warning banner with this formula in cell B3:
=IF(B6="","",
IF(COUNTA(UNIQUE(ClientData[Email]))<>COUNTA(ClientData[Email]),
"⚠️ Duplicate Customers Exist",""))
Then apply red fill + white text using a formula with conditional formatting to make the warning stand out.
Step 3: Automate Data Entry with Office Scripts
Here’s the magic. Instead of manually copying form data into the database, we’ll automate it.
Note: I’ll be using Office Scripts so the file can be used in Excel Online or Excel for the Desktop, but you can also use VBA/Macros if you don’t need to use the file in Excel Online.
1. Go to Automate > Record Actions and record yourself copying the form fields to the database.
2. Stop the recording and edit the script (see video for step by step).
We need to adjust it so that:
- If the first row of the table is empty, it pastes data there.
- Otherwise, it adds a new row and pastes into the last row.
- After saving, it clears the form (except the Date field).
💡 Tip: I recorded part of the script, then used ChatGPT to rewrite and optimize it. This saved time because I didn’t have to write out all the sheet names and cell references manually.
Finally, add a button to the form using the ‘+ Add in workbook’ button in the Code Editor to run the script with one click.
Step 4: Add Duplicate Checks in the Form
It’s great that the database warns us about duplicates - but even better if the form does too.
You can use XMATCH to check if a client already exists:
=IF(ISNUMBER(XMATCH(D5,ClientData[Full Name],0)),
"⚠️ This customer is already in the database.","")
Copy and adapt this formula for the Email field too.
Format the text in red so it’s impossible to miss.
Step 5: Protect the Form
Before sharing with users, protect the form so only the input fields can be edited.
- Select the input cells → Ctrl+1 → Protection tab → Uncheck Locked.
- Go to Review > Protect Sheet, tick Select unlocked cells, and apply protection.
Now users can only enter data where intended - and pressing Enter or Tab (desktop only) jumps to the next input cell.
This works in both Excel Desktop and Excel Online (with a slightly different setup for Excel Online under Review > Manage Protection) – see video for detailed step by step instructions.
Step 6: Analyze Your Data
With your ClientData table, you can now:
- Filter and sort records instantly
- Build PivotTables and charts for insights
- Create automated reports linked to the database
This transforms a simple Excel file into a lightweight Excel CRM system.
Recap: What We Built
✅ A clean, user-friendly Client Form
✅ One-click automation to save and clear entries
✅ A structured, filterable Client Database
✅ Built-in duplicate detection
✅ Sheet protection for smooth data entry
✅ Ready for analysis with PivotTables and charts
This isn’t any old spreadsheet - it’s a scalable system you can adapt to manage clients, projects, inventory, or any other information you need to track.
Take Your Skills Further
If you’re excited about building smart, automated spreadsheets like this, you’ll love my Excel Expert course.
It’s designed to take you beyond the basics and into pro-level Excel. with step-by-step lessons, real-world examples, and downloadable workbooks so you can follow along.
Ta
You’re welcome, Amy!
Another amazing video. You are an excellent teacher. I use Excel 2024 on a Mac, and, like a previous comment, I do not have an “Automate” button. So, I assume I use the Macro record button?
Thank you again for the excellent resource. I will also look up how to convert PDF bank statements to Excel on your website.
Thanks for your kind words, Michael! Yes, if you don’t have Automate, you can use Macros instead of Office Scripts. Still get ChatGPT to help you round out the code the Macro Recorder gives you, just as I did with the Office Script. Good luck!
I have downloaded the Excel sheet and it will not transfer the client form data into the database. It says to reach out to owner.
Hi Andrew,
I’ve never seen an Excel error that says “Reach out to owner”. Please contact us via email to share a screen shot and we can help you further: website @myonlinetraininghub.com (delete the space after ‘website’).
Mynda
Great tutorial, and the answer to my search for an easy contact database. I have needed to create a contact database for my team to put in SharePoint, but Access was too daunting for me.
I did run into two issues –
1. All formatting and validation data was cleared when I ran the script. CoPilot helped me rewrite that portion of the script to delete the entries and not the data.
2. If I start from a blank database, the formula replaces “B6” with “REF!”. I haven’t figured out that one yet.
I will put the finishing touches on this when I get back to my office.
Thank you!
Glad it was helpful, Bob. Sounds like when your code runs it’s deleting cell B6, which results in a REF! error. You need to clear the cell, not delete it.
Great Excel Database tutorial. I am finding the walk through great, only thing is as the others “Automate Tab” I tried the scrips add in not working as it should you need to have it open and click tje run button. Copilot helps convert the script to VBA and use a macro as looks and runs great.
It could do with a “remove information ” functionality as people leave for GDPR compatability so data held can be removed when asked.
I agree with one of tbr comments above to hide the headings and columns for a clean look and move the “file exists warning ⚠️ ” within the table itself maybe underneath the input cell only showing when it finds the duplicate else nothing for cleaner than on the side just my opinion as no professional just learning yhr advanced stuff.
Any help with the remove information code would be nice
Cheers, Dave! Great additions. I also thought I should have moved the warning to under the field after I’d recorded the video!
Very interesting article, I would like to take advantage of this opportunity.
However, it seems that the options
– Add in Workbook
– or Add Button
– and others
are not included in personal and family-based Office versions.
My Excel 365 version 2508 includes “Automate” and I can also work with scripts, but some options are missing.
From 2023, the functionality should be available for desktop and online, but probably only for versions with so-called “business accounts.”
Yes, that’s probably the issue. Microsoft see this as a business tool and not required for Personal and Family licenses. You can use macros instead.
با درود و احترام
ضمن تشکر فراوان بابت انتخاب هدف والای شما، آموزش شما بسیار برایم مفید بود. و علاوه بر نحوۀ تدریس فوقالعادۀ شما، درسهای دیگری همچون انسانیت، بزرگواری و … از شما آموختم.
با آرزوی بهترینها برای شما
آرمان کیانی
از کشور ایران
Glad you found it helpful.
Thank you so much for this! It was very helpful and straightforward. The only issue I had was that new entries in my database do not appear with banded rows. Each new entry is created with no fill color. I’m guessing it’s something that was added to the script once I used AI to optimize it.
Sounds like you didn’t set the database up as a Table (the step after I inserted the headings). Select the headings and press Ctrl+T to format as a table.
Thank you for this database example! I am looking for ideas on how best to approach building a database for cataloging construction items and tracking the unit prices over time. Any tips would be greatly appreciated!
You can use these same techniques. The key will be to store your data in a tabular layout.
I have Excel 2010. Before I read all the content, can this database be generated on that version?
Thank you……..
Yes, but you’d have to use a Macro instead of Office Scripts. Just use the same steps, except record a macro for the copying and pasting and then use ChatGPT to complete the coding like I did for the script.
Great video and technique! Do you also have a video on how to modify / update or delete a record in a form like the one you showed ?
I don’t have a video on this, but you can use the same techniques to create a lookup form that populates the record from the database and then allows the user to update/delete the record with an Office Script or Macro.
I did something like this recently. I didn’t think of the duplicate warning banner; great idea. One thing you could add (or should I say”remove”?), is hiding the column and row headings (at least on the client form sheet) to give it a cleaner look.
Great idea thanks for sharing.
This is an amazing tutorial, I’ve been wanting to create an Excel database for a long time!
Awesome to hear you found it helpful!