How to Create a Data Source in Excel (with Simple Steps)

A group of records used to store data is known as a data source, sometimes known as a data file. Any document that has been prepared to offer a structure for the receiving application to pull data from can be a data source. A typical scenario we can assume for a construction company that wants to make a dataset to track its running project. So, this article will teach you how to create a data source in Excel.

How to Create a Data Source in Excel: 6 Steps

Let’s assume we have a dataset, namely “Running Project of DPR Construction”. We will recreate the following dataset through today’s discussion. However, you can choose any other dataset that may suit your interest.

how to create a data source in excel

Here, we have used the Microsoft Excel 365 version; you may use any other version according to your convenience.

Step 1: Create a Basic Outline

The first step to making a dataset is to establish a basic outline of our datasheet. It may comprise font changing, size increment or decrement, table or border creation, etc. However, we chose to work on the default font size and type. However, we will demonstrate how to create all borders in your Excel sheet so that your dataset looks meaningful and easy to read.

  • First, select the intended cell for which you want to make the borders.

Create a Basic Outline how to create a data source in excel

  • Then, go to Ribbon > Border > All Borders.

Create a Basic Outline

  • Press Enter and see the output given below.
  • Now, it’s time to make a heading on your dataset.
  • To do so, first, merge some cells to create space to accommodate your heading.
  • Select B2 to G2 cell for instance and then go to the Merge & Center feature.
  • Now see the output as given below.

Create a Basic Outline

  • Now we input the heading titled “Creating Data Source”. You may choose whatever seems appropriate to you.
  • Now look at the dataset given below and see what it looks like.

Step 2: Input Numerical Data

There are multiple types of numerical data that you can use to create your data source in Excel, such as integers, decimals, currency, dates, and so on. However, to prepare this data source first, we will show you how to input integer data into your Excel sheet.

  • Enter the digit 100242 in cell B5.

Input Numerical Data how to create a data source in excel

  • All of our Project ID data is tagged as 100242. So, we drag the Fill Handle tool to get the other value.

Input Numerical Data

Anyway, if you want to increment 1 from the previous cell, just type 100242, and 100243 in the first two cells and drag the Fill Handle tool.

Step 3: Enter Dates

Apart from integer data, you can add a date to your data source in Excel. The following step will show you how to do that.

  • Write 1/2/2024 in cell C5 with the help of your keyboard.

Enter Dates

  • Similarly, do the same process from the C5 cell to C13.

Step 4: Create a Drop-Down List

Now we are going to talk about how to create a drop-down list using the Data Validation feature in your Excel sheet. Data from a pre-defined item list is entered into a spreadsheet using an Excel drop-down list. It enables a user to input his data faster and more accurately than if he did so manually.

  • To proceed with this process, first select Data > Data Validation from your Ribbon.

Create a Drop-Down List how to create a data source in excel

  • Subsequently, a dialog box will appear. Now select Settings tab > choose List from the drop-down options of Allow.

Create a Drop-Down List

  • Thus, an additional box will appear named Source in the dialog box.
  • Click on the Upwards arrow.

Create a Drop-Down List how to create a data source in excel

  • Now type your drop-down list data in this box. For this specific dataset, we input Arizona, Hawaii, Ohio, Washington, Idaho, Arizona, Kansas, Arizona, and Florida.

  • Press OK afterward.

  • Now, see the output given below.

  • Drag the Fill Handle tool from D5 to D13 to enable those cells with the drop-down feature that we have created in D5.

  • After inputting our data, see the output below.

Step 5: Input Currency

To input any currency data, you have to add a currency sign in addition to the numerical values. In this step, we will show you how to do that.

  • First, select the column in which you would like to add a dollar sign.

Input Currency

  • Now go to the Home tab, then select Number Format > Currency.

  • Now input your data on each cell and see the output with the dollar sign.

Input Currency

Step 6: Format the Input Data

While inputting text data in a specific cell, multiple factors such as font size, font type, and special formatting such as italic, bold, images, or other formats should be kept in mind. However, in this step, we will input center-aligned text data into a specific cell.

  • Select the region in which your text data will reside.
  • Next, select Middle Align and Center from the Alignment ribbon group.

Format the Input Data how to create a data source in excel

  • Now input your text data in those cells.
  • See the output below.

Format the Input Data

Lastly, we add a column where you can type “Yes” or “No” data indicating whether a specific region is prone to flooding.

  • To do so select the different cells of column G.
  • Then type “Yes” or “No” in those cells.

Format the Input Data

Finally, your data source is ready for further use!

Read More: How to Create Excel Data Connection to Another Excel File

Practice Section

We have provided a Practice section on the right side of each sheet so you can practice yourself. Please make sure to do it yourself.

Do it yourself how to create a data source in excel

Download Practice Workbook

You can download and practice the dataset that we have used to prepare this article.


In this article, we have discussed how to create a data source in Excel. As you have already understood, there are multiple data types that the user needs to work on. Further, If you have any queries, feel free to comment below and we will get back to you soon.

Related Articles

<< Go Back to Excel Data Connections | Importing Data in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Mohammad Shah Miran
Mohammad Shah Miran

Mohammad Shah Miran has a professional background spanning over a year at Softeko. Initially starting as an Excel and VBA writer, he authored more than 50 articles for the ExcelDemy project. Currently, Miran is engaged in the LinuxSimply project as a Linux content developer, completed over 40 articles. His analytical approach extends across various domains, including Excel, VBA, Bash scripting, Linux, data analysis, and Python programming. In his leisure time, Miran enjoys watching movies and series or listening... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF