What Excel calls a database is really just organized data in rows and columns
Excel does not have a separate "database mode" — you create a database by organizing information into a table format that Excel can sort, filter, and search. The simplest way is to use Excel's built-in Table feature, which turns a range of data into a structured list that behaves like a database. Once you do this, you can add formulas to pull information out, filter what you see, and keep your data organized as you add more rows.
The difference between a random spreadsheet and a database in Excel is structure. A database has column headers in the first row, one piece of information per cell, no blank rows in the middle, and consistent formatting. Excel's Table feature enforces this structure and gives you tools to work with the data as a unit.
Key Takeaways
- Create a Table by selecting your data range and using the Format as Table option in the Home tab, which turns raw data into a searchable, sortable structure.
- Add column headers in the first row so Excel knows what each column contains, and keep one piece of information per cell with no blank rows.
- Use the filter buttons that appear in the header row to show only the records you need, or sort by any column in ascending or descending order.
- Write formulas like VLOOKUP or INDEX/MATCH to search the table and pull information from specific rows based on what you type in a search cell.
Turn your data into a Table in three steps
Start by entering your data into Excel with column headers in the first row. For example, if you are tracking customer information, your headers might be Name, Email, Phone, and Date Added. Each row below should contain one complete record, with no blank rows in between.
Select the entire data range, including the headers. Click anywhere in your data, then press Ctrl+A (or Cmd+A on Mac) to select all connected data, or manually click and drag to select the range you want. Go to the Home tab in the ribbon and click Format as Table. Choose a table style from the menu — the color does not matter, only the structure. Excel will ask you to confirm the data range and whether the first row contains headers. Make sure My table has headers is checked, then click OK.
You now have a Table. Notice the small dropdown arrows that appeared in each header cell. These are filter buttons. Click any of them to sort or filter that column. Your table will also expand automatically when you type data into the row below the last entry, so you do not have to recreate the table each time you add records.
Set up column headers that describe what each column holds
Column headers are the first row of your table and they tell Excel (and you) what information is in each column. Use clear, short names: "Customer Name" instead of "Name of the person who bought something", "Order Date" instead of "When", "Amount Paid" instead of "Money". One or two words per header is usually enough.
Headers should be text only, not formulas or numbers. Do not leave a header cell blank. If you have a column you are not sure about yet, give it a temporary name like "Notes" and you can rename it later by right-clicking the header and selecting Rename. Consistent headers make it much easier to write formulas later that search or summarize your data.
Use filter buttons to find and sort records
Once your data is in a Table, click any filter dropdown arrow in the header row. You will see a list of every unique value in that column. Uncheck the values you want to hide and click OK. For example, if your table has a "Status" column with values like "Pending", "Completed", and "Cancelled", you can uncheck "Cancelled" to see only active orders. The rows are hidden, not deleted — click the filter button again and check "Cancelled" to bring them back.
To sort, click the filter arrow and choose Sort A to Z or Sort Z to A for text, or Sort Smallest to Largest for numbers. You can sort by multiple columns at once by going to the Data tab and clicking Sort. This opens a dialog where you can set a primary sort (like by date), then a secondary sort (like by name within each date). Sorting rearranges the rows but does not change your data.
Write a formula to search the table and return matching information
Once your table is set up, you can use formulas to find information without scrolling. The most common approach is VLOOKUP, which searches for a value in the first column and returns a value from another column in the same row. For example, if you have a customer table with Name in column A and Email in column B, you can type a customer name in a search cell and use VLOOKUP to pull their email.
The formula looks like this: =VLOOKUP(search_cell, table_range, column_number, FALSE). If your table is named "Customers" and you want to search for a name typed in cell E2 and return the email from the third column, write =VLOOKUP(E2, Customers, 3, FALSE). The FALSE at the end tells Excel to find an exact match. If the name is not found, the formula returns an error — you can wrap it in IFERROR to show a message instead: =IFERROR(VLOOKUP(E2, Customers, 3, FALSE), "Not found").
VLOOKUP only searches the first column, so if you need to search a different column, use INDEX and MATCH together instead. This is more flexible but takes a bit longer to write. Ask yourself what you are searching for and what you want to get back, then choose the formula that matches that task.
Add new rows and let the table expand automatically
One advantage of using a Table is that it grows with your data. When you type information into the row immediately below the last row of your table, Excel automatically includes that row in the table. The formatting, filter buttons, and any formulas that reference the table all update without you having to do anything.
If you add a new row and it does not automatically join the table, click anywhere in the table and go to the Table Design tab (which appears when a table is selected). Click Resize Table and drag to include the new rows. This is rare but can happen if there is a blank row between your data and the new entry.
Keep your database clean and consistent
A database only works well if the data inside it is consistent. Use the same format for dates (like MM/DD/YYYY), the same capitalization for repeated values (all "New York" or all "new york", not a mix), and the same units for measurements. If you have a "Status" column, decide on the exact values you will use — "Pending", "In Progress", "Complete" — and stick to them.
Delete blank rows and blank columns. If a cell does not apply to a record, leave it empty rather than typing "N/A" or "—", because empty cells behave differently in formulas and filters. Review your data every few weeks and fix typos or inconsistencies, because a misspelled customer name will not match when you search for it later.
Frequently Asked Questions
Can I use Excel as a database instead of buying database software?
For small datasets — a few hundred to a few thousand rows — Excel works fine. Once you have tens of thousands of rows or multiple people editing at the same time, Excel slows down and becomes difficult to manage. For that scale, dedicated database software like Microsoft Access or a cloud database is more reliable.
What is the difference between a Table and just formatting cells?
A Table is a named range with built-in filter buttons, automatic expansion, and the ability to reference it by name in formulas. Regular formatting is just colors and fonts. Tables make filtering, sorting, and writing formulas much faster because Excel treats the data as a unit.
How do I rename a Table?
Click anywhere in the table, go to the Table Design tab, and look at the left side of the ribbon for the table name (usually "Table1", "Table2", etc.). Click the name box and type a new name like "Customers" or "Orders". Use a name that describes what the table contains.
Can I have multiple tables in one spreadsheet?
Yes. Each table is independent. You can have a Customers table, an Orders table, and a Products table all in the same workbook, even on the same sheet if you space them apart. Use formulas to connect them — for example, look up a customer name from one table and find their orders in another.
What happens if I delete a row from my table?
The row is removed and the data below it moves up. If you have formulas that reference that specific row, they will either update to the new row number or show an error if the data they were looking for is gone. If you think you might need the data later, hide the row instead of deleting it — right-click the row number and select Hide.