Mohammad Shah Miran
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.
Download Practice Workbook
You can download and practice the dataset that we have used to prepare this article.
6 Steps to Create a Data Source in Excel
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.
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 choose 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.
- Then, go to Ribbon > Border > All Borders.
- 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 and Center feature.
- Now see the output as given below.
- 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.
Read More: Excel Connections vs Queries: Main Dissimilarity with Example
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.
- As all of our Project ID data is tagged as 100242. So, we drag the Fill Handle tool to get the other value.
Anyway, if you want to increment 1 from the previous cell, just type 100242, 100243 in the first two cells and drag the Fill Handle tool.
Read More: How to Find External Data Connections in Excel (5 Quick Tricks)
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.
- Similarly, do the same process from the C5 cell to C13.
Read More: [Fixed!] External Data Connections Have Been Disabled in Excel
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.
- Subsequently, a dialog box will appear. Now select Settings tab > choose List from the drop-down options of Allow.
- Thus, an additional box will appear named Source in the dialog box.
- Click on the Upwards arrow.
- 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 afterwards.
- 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.
Read More: How to Create Excel Data Connection to Another Excel File
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.
- Now go to Home tab, then select Number Format > Currency.
- Now input your data on each cell and see the output with the dollar sign.
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.
- Now input your text data in those cells.
- See the output below.
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.
Finally, your data source is ready for further use!
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.
Conclusion
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.
