Excel is the most common data tool used in businesses and personal productivity across the world. Since Excel is so widely used and available, it tends to get used frequently to store and manage data as a makeshift database. This is especially true with small businesses since there is no budget or expertise available for more suitable tools. This post will show you what a database is and the best practices you should follow if you’re going to try and use Excel as a database. Get the example files used in this post with the above link and follow along below!
What is a Database?
A database is a structured set of data that is often in an electronic format and is used to organize, store. and retrieve data. For example, a database might be used to store customer names, addresses, orders, and product information. Databases often have key features that make them an ideal place to store your data.
Database vs Excel
An Excel spreadsheet is not a database, but it does have a lot of great and easy-to-use features for working with data. Here are some of the key features of a database and how they compare to an Excel file. This is not a comprehensive list of features that a database will have, but they are some of the major features that will usually make a proper database a more suitable option. The features that a database has depends on what database it is. Not all databases have the same features and functionality. You will need to decide what features are essential for your situation in order to decide if you should use Excel or some other database tool. 💡 Tip: If you have Excel for Microsoft 365, then consider using Dataverse for Teams as your database instead of Excel. Dataverse has many of the great database features mentioned above and it is included in your Microsoft 365 license at no additional cost.
Relational Database Design
A good deal of thought should happen about the structure of your database before you begin to build it. This can save you a lot of headaches later on. Most databases have a relational design. This means the database contains many related tables instead of one table that contains all the data. Suppose you want to track orders in your database. One option is to create a single flat table that contains all the information about the order, the products, and the customer that created the order. This isn’t very efficient as you end up creating multiple entries of the same customer information such as name, email, and address. You can see in the above data, column G, H, and I contains a lot of duplicate values. A better option is to create separate tables to store the Order, Product, and Customer data. The Order data can then reference a unique identifier in the Product and Customer data that will relate the tables and avoid unnecessary duplicate data entry. This same example data might look like this when reorganized into multiple related tables. This avoids duplicated data entry in a flat single table structure and you can use the Item or Customer ID unique identifier to look up the related data in the respective Products or Customers tables.
Tabular Data Structure in Excel
If you’re going to use Excel as your database, then you’re going to need your data in tabular format. This refers to the way the data is structured. The above example shows a product order dataset in tabular format. A tabular data format is best suited for Excel due to the row and column structure of a spreadsheet. Here are a few rules your data should follow so that it’s in tabular format.
1st row should contain column headings. This is just a short and descriptive name for the data contained below.No blank column headings. Every column of data should have a name.No blank columns or blank rows. Blank values within a field are ok, but columns or rows that are entirely blank should be removed.No subtotals or grand totals within the data.One row should represent exactly one record of data.One column should contain exactly one type of data.
In the above orders example data, you can see B2:E2 contains the column headings of an Order ID, Customer ID, Order Date, Item, and Quantity. The dataset has no blank rows or columns, and no subtotals are included. Each row in the dataset represents an order for one type of product. You’ll also notice each column contains one type of data. For example, the Item column only contains information on the name of the product and does not include other product information such as the price. ⚠️ Warning: If you don’t adhere to this type of structure for your data, then summarizing and analyzing your data will be more difficult later. Tools such as pivot tables require tabular data!
Use Excel Tables to Store Data
Excel has a feature that is specifically for storing your tabular data. An Excel Table is a container for your tabular datasets. They help keep all the same data together in one object with many other benefits. 💡 Tip: Check out this post to learn more about all the amazing features of Excel Tables. You will definitely want to use a table to store and organize any data table that will be a part of your dataset.
How to Create an Excel Table
Follow these steps to create a table from an existing set of data. This will open the Create Table menu where you will be able to select the range containing your data. When you select a cell inside your data before using the Table command, Excel will guess the full range of your dataset. You will see a green dash line surrounding your data which indicates the range selected in the Create Table menu. You can click on the selector button to the right of the range input to adjust this range if needed. 📝 Note: The My table has headers option needs to be checked if the first row in your dataset contains column headings. Otherwise, Excel will create a table with generic column heading titles. Your Excel data will now be inside a table! You will immediately see that it’s inside a table as Excel will apply some default format which will make the table range very obvious. 💡 Tip: You can choose from a variety of format options for your table from the Table Design tab in the Table Styles section. The next thing you will want to do with your table is to give it a sensible name. The table name will be used to reference the table in formulas and other tools, so giving it a short descriptive name will help you later when reading formulas. Each table in your database will need to be in a separate table, so you will need to repeat the above process for each.
How to Add New Data to Your Table
You will likely need to add new records to your database. This means you will need to add new rows to your tables. Adding rows to an Excel table is very easy and you can do it a few different ways. You can add new rows to your table from the right-click menu. This will insert a new blank row directly above the selected cells in your table. You can add a blank row to the bottom of your table with the Tab key. A new blank row will be added to the bottom of the table. But the easiest way to add new data to a table is to type directly below the table. Data entered directly underneath the table is automatically absorbed into the table!
Excel Workbook Layout
If you are going to create an Excel database, then you should keep it simple. Your Excel database file should contain only the data and nothing else. This means any reports, analysis, data visualization, or other work related to the data should be done in another Excel file. Your Excel database file should only be used for adding, editing, or deleting the data stored in the file. This will help decrease the chance of accidentally changing your data, as the only reason to open the file will be to intentionally change the data. Each table in the database should be stored in a separate worksheet and nothing else except the table should be in that sheet. You can then name the worksheet based on the table it contains so your file is easy to navigate. 💡 Tip: Place your table starting in cell A1 and then hide the remaining columns. This way it is clear the sheet should only contain the table and nothing else. The only other sheet you might optionally include is a table of contents to help organize the file. This is where you can list each table along with the fields it contains and a description of what these fields are. 💡 Tip: You can hyperlink the table name listed in the table of contents to its associated sheet. Select the name and press Ctrl + K to create a hyperlink. This can help you navigate the workbook when you have a lot of tables in your database.
Use Data Validation to Prevent Invalid Data
Data validation is a very important feature for any database. This allows you to ensure only specific types of data are allowed in a column. You might create data validation rules such as.
Only positive whole numbers are entered in a quantity column.Only certain product names are allowed in the product column.A column can’t contain any duplicate values.Dates are between two given dates.
Excel’s data validation tools will help you ensure the data entered in your database follow such rules. Follow these steps to add a data validation rule to any column in your table. When you hover the mouse cursor over the top of your column heading in a table, the cursor will change to a black downward pointing arrow. Left-click and the entire column will be selected. The data validation will automatically propagate to any new rows added to the table. This will open up the Data Validation menu where you will be able to choose from various validation settings. If there is no active validation in the cell, you should see the Any values option selected in the Allow criteria.
Only Allow Positive Whole Number Values
Follow these steps from the Data Validation menu to allow only positive whole number values to be entered. 💡 Tip: Keep the Ignore blank option checked if you want to allow blank cells in the column. This will apply the validation rule to the column and when a user tries to enter any number other than 1, 2, 3, etc… they will be warned the data is invalid.
Only Allow Items from a List
Selecting items from a dropdown list is a great way to avoid incorrect text input such as customer or product names. Follow these steps from the Data Validation menu to create a dropdown list for selecting text values in a column. 📝 Note: If you place the list of possible options inside an Excel Table and select the full column for the Source reference, then the range reference will update as you add items to the table. Now when you select a cell in the column you will see a dropdown list handle on the right of the cell. Click on this and you will be able to choose a value from a list.
Only Allow Unique Values
Suppose you want to ensure the list of products in your Products table is unique. You can use the Custom option in the validation settings to achieve this. The formula counts the number of times the current row’s value appears in the Products[Item] column using the COUNTIFS function. It then determines if this count is equal to 1. Only values where the formula evaluates to 1 are allowed which means the product name can’t have been in the list already. 📝 Note: You need to reference the column by name using the INDIRECT function in order for the range to grow as you add items to your table!
Show Input Message when Cell is Selected
The data validation menu allows you to show a message to your users when a cell is selected. This means you can add instructions about what types of values are allowed in the column. Follow these steps to add an input message in the Data Validation menu. When you select a cell in the column which contains the data validation, a small yellow pop-up will appear with your Title and Input message text.
Prevent Invalid Data with Error Message
When you have a data validation rule in place, you will usually want to prevent invalid data from being entered. This can be achieved using the error message feature in the Data Validation menu. 📝 Note: The Stop option is essential if you want to prevent the invalid data from being entered rather than only warning the user the data is invalid. When you try to enter a repeated value in the column your custom error message will pop up and prevent the value from being entered in the cell.
Data Entry Form for Your Excel Database
Excel doesn’t have any fool-proof methods to ensure data is entered correctly, even when data validation techniques previously mentioned are used. When a user copies and pastes or cuts and pastes values, this can override data validation in a column and cause incorrect data to enter into your database. Using a data entry form can help to avoid data entry errors and there are a couple of different options available.
Use a table for data entry.Use the quick access toolbar data entry form.Use Microsoft Forms for data entry.Use Microsoft Power Automate app for data entry.Use Microsoft Power Apps for data entry.
💡 Tip: Check out this post for more details on the various data entry form options for Excel. Tools such as Microsoft Forms, Power Automate, and Power Apps will give you more data validation, access, and security controls over your data entry compared with the basic Excel options.
Access and Security for Your Excel Database
Excel isn’t a secure option for your data. Whatever measures you set up in your Excel file to prevent users from changing data by accident or on purpose will not be foolproof. Whoever has access to the file will be able to create, read, update, and delete data from your database if they are determined. There are also no options to assign certain privileges to certain users within an Excel file. However, you can manage access and security to the file from SharePoint. 💡 Tip: Check out this post from Microsoft about recommendations for securing SharePoint files for more details. When you store your Excel file in SharePoint you’ll also be able to see recent changes. This will open up the Changes pane on the right-hand side of the Excel sheet. It will show you a chronological list of all the recent changes in the workbook, who made those changes, when they made the change, and what the previous value was. You can also right-click on a cell and select Show Changes. This will open the Changes pane filtered to only show the changes for that particular cell. This is a great way to track down the cause of any potential errors in your data.
Query Your Excel Database with Power Query
Your Excel database file should only be used to add, edit, or delete records in your tables. So how do you use the data for anything else such as creating reports, analysis, or dashboards? This is the magic of Power Query! It will allow you to connect to your Excel database and query the data in a read-only manner. You can build all your reports, analysis, and dashboards in a separate file which can easily be refreshed with the latest data from your Excel database file. Here’s how to use power query to quickly import your data into any Excel file. This will open a file picker menu where you can navigate to your Excel database file. ⚠️ Warning: Make sure your Excel database file is closed or the import process will show a warning that it’s unable to connect to the file because it’s in use! Clicking on the Import button will then open the Navigator menu. This is where you can select what data to load and where to load it. The Navigator menu will list all the tables and sheets in the Excel file. Your tables might be listed with a suffix on the name if you’ve named the sheets and tables the same. This will open the Import Data menu where you can choose to import your data into a Table, PivotTable, PivotChart, or only create a Connection to the data without loading it. Your data is then loaded into an Excel Table in the new workbook. The best part is you can always get the latest data from the source database file. Go to the Data tab and press the Refresh All button to refresh the power query connection and import the latest data. 💡 Tip: You can do a lot more than just load data with Power Query. You can also transform your data in just about any imaginable way using the Transform Data button in the Navigator menu. Check out this post on how to use Power Query for more details about this amazing tool.
Analyze and Summarize Your Excel Database with Power Pivot
Power Query isn’t the only database tool Excel has. The data model and Power Pivot add-in will help you slice and dice relational data inside your Excel pivot tables. When you load your data with Power Quer, there is an option to Add this data to the Data Model in the Import Data menu. This option will allow you to build relationships between the various tables in your database. This way you’ll be able to analyze your orders by category even though this field doesn’t appear in the Orders table. You can build your table relationships from the Data tab. Now you’ll be able to analyze multiple tables from your database inside a single pivot table!
Conclusions
Data is an essential part of any business or organization. If you need to track customers, sales, inventory, or any other information then you need a database. If you need to create a database on a budget with the tools you have available then Microsoft Excel might be the best option and is a natural fit for any tabular data because of its row and column structure. There are many things to consider when using Excel as a database such as who will have access to the files, what type of data will be stored, and how you will use the data. Features such as Tables, data validation, power query, and power pivot are all essential to properly storing, managing, and accessing your data. Do you use Excel as a database? Do you have any other tips for using Excel to manage your dataset? Let me know in the comments below!
