How to use Excel as a database (and when to stop)
Set up an Excel database in 9 steps, from tables and drop-down lists to XLOOKUP and PivotTables, with a layout to copy and the signs it's time to move on.
Excel isn’t a database, but you can run a simple Excel database if you set it up like one. Give each kind of thing, such as customers or jobs, its own table, with one record per row. Add an ID to every record, drop-down lists for repeated values, and XLOOKUP to link the tables. It works for a small team.
This guide is for the owner or office manager whose business keeps its customers, jobs or stock in a workbook. It builds a small customers-and-jobs database in Excel step by step. Then it covers the signs it’s time to stop and what to move to.
How we researched this: in October 2026 we checked every menu name, step and limit in this guide on Microsoft’s and Airtable’s own help pages. Steps are for Excel for Microsoft 365 on Windows and Excel for the web; older versions and Excel for Mac can differ.
Can you use Excel as a database?
Yes, for simple lists, but Excel is a spreadsheet, not a database. Microsoft’s own guide to choosing between Access and Excel says Access is better for managing data and Excel for analyzing it. It also says Excel can’t require every order to have a matching customer. Our guide to the difference between a database and a spreadsheet covers the rest.
Set up with care, though, a workbook can keep lists clean and lookups working. Here’s how.
Excel database template: the layout to copy
The example is a client database for a small service business, such as a plumber: its customers and the jobs it does for them. Copy this layout into a blank workbook, one sheet per table, with the column names in row 1. The steps below set up each column.
Customers sheet, one row per customer:
| Column | What goes in it | Entry |
|---|---|---|
| Customer ID | A unique code, such as C-101 | Typed, unique |
| Name | The customer’s business or full name | Typed |
| Phone | Main phone number | Typed |
| Main email address | Typed | |
| Address | Site or billing address | Typed |
Jobs sheet, one row per job:
| Column | What goes in it | Entry |
|---|---|---|
| Job ID | A unique code, such as J-2041 | Typed, unique |
| Customer ID | The customer the job is for | Drop-down of Customer IDs |
| Customer | The customer’s name | XLOOKUP (step 5) |
| Job | The work, such as Boiler service | Typed |
| Date | The day the job is booked for | Dates only |
| Status | Booked, In progress or Done | Drop-down |
| Price | What the job costs | Numbers only |
A stock list works the same way: a Parts table with Part ID, Part and On hand. For other ready-made workbooks, Microsoft’s free Excel templates include inventories, planners and trackers.
How to create a database in Excel, step by step
1. Give each kind of thing its own sheet
Make one sheet for customers and one for jobs. Put the headings in row 1 and one record on each row below, with no blank rows, titles, merged cells or subtotals in between. Keep one kind of value per column: dates in Date, amounts in Price. Don’t copy a customer’s phone or address onto the Jobs sheet; jobs will look them up (step 5).
2. Format each list as an Excel table
Click a cell in the customer list, choose Home > Format as Table, pick a style, and confirm that your table has headers (create and format tables). Ctrl+T does the same in Excel for Windows, and Ctrl+L in Excel for the web (keyboard shortcuts). Then, on the Table Design tab, type Customers in the Table Name box. Do the same for Jobs.
A table is what makes a range of cells behave like a list of records (overview of Excel tables):
- Filter and sort buttons appear on every heading.
- New rows join the table when you type in the row just below it (resize a table).
- Formulas fill the column. Type a formula once in a column, and Excel applies it to every row in that column.
- Formulas can use names.
Customers[Name]means the Name column of the Customers table, and these structured references adjust as you add or remove records (structured references).
3. Give every record an ID
Names change and repeat. IDs don’t, so they’re what links a job to its customer. Add a Customer ID column (C-101, C-102) and a Job ID column (J-2041, J-2042). Excel doesn’t number records for you (numbering rows), so whoever adds a record types the next ID.
To spot an ID used twice, select the column and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. To clean up an imported list, Data > Remove Duplicates deletes repeated rows for good, so copy the data to another sheet first (find and remove duplicates).
4. Add drop-down lists and entry rules
Drop-down lists keep values consistent: people pick Booked from the list instead of typing their own version of it. Select the Status column, choose Data > Data Validation, set Allow to List, and type Booked,In progress,Done in the Source box. On the Error Alert tab, choose Stop so people can’t type anything else (create a drop-down list).
Give the Jobs table’s Customer ID column a drop-down too, with its source set to the Customer ID column of the Customers table, without the heading. A drop-down based on an Excel table updates as you add items, so new customers appear in it. Other rules can allow only a date, a whole number or a decimal (apply data validation).
There’s one gap. Validation checks what people type, but values that are copied in or filled down skip it, with no message (more on data validation). Now and then, in desktop Excel, choose Data > Data Validation > Circle Invalid Data to find them; Excel for the web doesn’t have it (circle invalid data).
5. Link the tables with XLOOKUP
Add a Customer column to the Jobs table and type this formula in its first row:
=XLOOKUP([@[Customer ID]], Customers[Customer ID], Customers[Name], "Not found")Read it left to right: take this job’s Customer ID, find it in the Customer ID column of the Customers table, and bring back the Name from that row. If there’s no match, show “Not found” instead of an error. Because Jobs is a table, the formula fills the column for every job. XLOOKUP looks for an exact match by default, and it isn’t in Excel 2016 or 2019, where VLOOKUP does the job (XLOOKUP function).
Now each customer’s details live in one place. Rename Bay Bakery once on the Customers sheet, and all its jobs show the new name, because jobs store the ID, not the name.
The catch: Excel won’t stop someone deleting a customer who still has jobs. Those jobs just show Not found, so filter the Customer column for it now and then.
6. Add records with a form
Typing across a wide table invites mistakes. Excel has two kinds of form:
- The data form, in Excel for Windows. It shows one record at a time, with a box for each column and buttons for New, Delete, Criteria (to search) and Find Next. It isn’t on the ribbon: open the Quick Access Toolbar menu, choose More Commands, then All Commands, select Form and click Add. It handles up to 32 columns, and Undo can’t bring back a record you delete in it (data form).
- Microsoft Forms, in Excel for the web. With the workbook saved in OneDrive for work or school, sign in with your work or school account and choose Insert > Forms > New Form (create a form). The workbook keeps a live connection to the form, so new responses show up in it when it’s open in Excel for the web. People can send job requests without opening the file (form data in Excel). A form only adds records, though; changing one still means opening the sheet.
7. Find records with filters, sorting and slicers
Every table heading has a filter button: pick Booked and In progress in the Status filter to see open jobs, or sort by Date. For one-click filters, select a cell in the Jobs table, choose Insert > Slicer and tick Status. Each status becomes a button. Excel for the web can create slicers for PivotTables but not for tables (slicers).
8. Report with PivotTables
To count jobs by status or total the price by customer, use a PivotTable. Click in the Jobs table, choose Insert > PivotTable, and tick the columns you want in the PivotTable Fields list (create a PivotTable). Because the source is a table, new jobs are included when you refresh: right-click the PivotTable and choose Refresh. Until then, it shows the old numbers.
9. Share it with your team
For several people to work in the workbook at once, save it to OneDrive or SharePoint. Everyone then edits in Excel for the web, or in Excel for Microsoft 365 with AutoSave on, which saves changes to the cloud as people work. On the desktop, co-authoring needs a recent version of Excel, such as Excel for Microsoft 365, and an .xlsx, .xlsm or .xlsb file (co-authoring).
In a shared file, other people’s sorts and filters change what you see. A Sheet View (View > Sheet View > New) lets you sort and filter without changing everyone else’s view (Sheet Views).
When should you stop using Excel as a database?
A workbook set up this way holds together for one person or a small team. These signs mean it’s time to move the records out:
- Several people edit all day. Everyone works in one grid, so one bad paste or sort can land on everyone’s records.
- Customers have several sites or contacts. One row per customer can’t hold three sites and two contacts without repeating the customer.
- Jobs point to deleted customers. Excel can’t block the delete, so you find out when Not found turns up on an invoice.
- Some people should see only part of the data. Sharing a workbook shares all of it, and Microsoft says sheet protection isn’t intended as a security feature.
- Crews update jobs on the road. Changing a job means scrolling a big grid on a small screen, and a form can only add new jobs.
- The file is slow, or split by year. Last year’s jobs sit in another workbook, so nobody sees a customer’s whole history. Each sheet stops at 1,048,576 rows anyway.
If two or more sound familiar, the records have outgrown the workbook.
What should you use instead of an Excel database?
Microsoft Access, if your team is on Windows
Access is Microsoft’s database. The same Microsoft guide says it can require every job to have a matching customer and lets several people edit different records at once. It’s included only in certain Microsoft 365 plans and runs on Windows desktop. In Access, External Data > New Data Source > From File > Excel copies a worksheet into a new table and leaves the workbook unchanged (import from Excel).
Our Microsoft Access vs Excel guide compares the two side by side. If you’re on a Mac, see our guide to Microsoft Access for Mac.
An online database
Online databases such as Airtable keep records in tables that link to each other and open in a browser, and Airtable imports Excel files up to 5 MB. You set up the tables and links yourself and work in a grid, which suits teams that like spreadsheets.
Our roundup of Excel alternatives compares Airtable and other tools that work like a database.
Google Sheets, if you’d rather stay in a spreadsheet
If your team wants a shared grid in the browser, you can use Google Sheets as a database the same way. It’s still a spreadsheet, though, so most of the signs above apply.
Turn your Excel database into an app with GoodTaco
A workbook set up like this one is ready to become an app. Upload it on our spreadsheet to app page, no account needed. Then say what the app should do, such as “Book jobs for each customer and track their status.” GoodTaco imports each sheet as a table of records in a built-in database and turns columns of repeated choices like Status into drop-downs. Lookups such as XLOOKUP can carry over, and it rebuilds the formulas it can reproduce exactly. In minutes you have a working web app with screens, forms, search and dashboards.
Your Customer ID column comes across, and the XLOOKUP becomes a lookup in the app, so each job still shows its customer. Want jobs to pick their customer from a list, or to stop anyone deleting a customer who still has jobs? Ask for it in plain words, and GoodTaco changes the app.
Your crew opens the app in any browser, including a phone’s, and updates jobs from a form instead of a grid. Private apps ask people to sign in with Google or an emailed link before they see any data, and you choose which users or roles get in. You change the app by asking in plain words, like “Add a page of today’s jobs by technician,” and every version is saved, so you can roll back.
From then on, your team adds and edits records in the app instead of the workbook. Macros, charts and PivotTables stay in the workbook, so ask GoodTaco for the dashboard you want. If you’d rather keep a live spreadsheet, GoodTaco reads and writes a connected Google Sheet.
GoodTaco has a free plan, and paid plans start at $9 a month, priced per workspace rather than per app user.
Keep the setup you built in Excel and give your whole team one app to work in. Start free, no credit card needed.
Frequently asked questions
Can Microsoft Excel be used as a database?
Yes, for lists a few people keep up, such as customers, jobs or stock, if you set the workbook up like one: a table for each kind of thing, an ID for every record, drop-down lists, and XLOOKUP to link the tables. Move the records out when several people edit all day or some should see only part of the data.
How do I create a database in Excel?
Give each kind of thing its own sheet, with headings in row 1 and one record per row. Format each list as a table with Home > Format as Table, add an ID column, add drop-down lists with Data > Data Validation, and link the tables with XLOOKUP. Then use filters and slicers to find records and PivotTables to report on them.
Is there a free Excel database template?
You can build one from the layout in this guide: a Customers table and a Jobs table, with the columns, drop-down lists and XLOOKUP column it lists. Microsoft's free Excel templates also include inventories, planners and trackers you can start from.
How do I make a searchable database in Excel?
Format the list as a table, so every heading gets a filter and sort button. In the Excel desktop app, add slicers with Insert > Slicer for one-click filters such as status. In a workbook shared through OneDrive or SharePoint, a Sheet View lets you sort and filter without changing what everyone else sees.
How do I create a database in Excel that updates automatically?
Keep the records in an Excel table. Rows typed just below it join the table, a formula typed once fills its whole column, and formulas that use table names adjust as records are added. PivotTables built on the table pick up new records when you refresh them. A form made with Microsoft Forms in Excel for the web, with the workbook in OneDrive for work or school, sends its responses into the workbook.
How do I add a data entry form in Excel?
In Excel for Windows, add the Form command to the Quick Access Toolbar (More Commands, All Commands, Form), click a cell in your table and open it. It shows one record at a time, for up to 32 columns. In Excel for the web, with the workbook saved in OneDrive for work or school, Insert > Forms > New Form creates a Microsoft Form whose responses go into the workbook.
How do I turn an Excel spreadsheet into an Access database?
In Access, choose External Data > New Data Source > From File > Excel and import each worksheet into a new table. Access copies the data and leaves the workbook unchanged. Access is available only on Windows desktop, so Mac users need a different tool.
Start from the database template
Get your own copy of a working database app with sample data, ready to make yours. Free to start.
Use the database template, free