When you are working on a large scale for data collection such as city, state, etc. you will get familiar with the term Demographic Data. It is a visual representation of these long datasets. However, the problem occurs when we need to analyze demographic data as they are occupied with the bulk of information. Therefore, Microsoft Excel has made it easier for us. In this article, we will explore how to analyze demographic data in Excel with 5 essential methods.
What Is Demographic Data?
Demographic data is the set of information that has a statistical socio-economic nature. It may incorporate data on population, income, education, employment, fertility, mortality, etc. over a period of time in specific geographical locations. For example, if you want to get information about employment in Europe, we will need to get the data on employment and unemployment rates, engagement according to gender, ethnicity, salary scale, etc. All of these are called demographic data of Europe.
How to Analyze Demographic Data in Excel: 5 Essential Methods
To describe the process of analyzing demographic data, we have prepared a sample dataset in Excel. It represents the information of the Total Population, Male Population, and Female Population as of 1 July in 10 cities of the USA. It also shows the Male and Female Income Rates. All of them are provided in cell range B4:G14.
As you can see, the dataset is really long to pick specific information. Therefore, we will go through the 5 methods below to analyze demographic data in Excel.
1. Apply Sort & Filter Tool to Analyze Demographic Data
The Sort & Filter tool is very effective in the event of analyzing demographic data. Let’s see how it works.
- First, select your whole dataset.
- Then, go to the Data tab and click on Filter from the Sort & Filter tool.
- You can also get this tool from Home > Editing > Sort & Filter > Filter.
- Afterward, you will notice the Filter icons beside the header title like this.
- Now, click on the filter icon beside the City title.
- Then, select Chicago as the Text Filter.
- That’s it, you can now analyze the demographic data only for Chicago city in a more summarized way.
- Following the same procedure, you can analyze any city’s demographic data.
Read More: How to Calculate Population Growth Rate in Excel
2. Insert Pivot Table to Evaluate Demographic Data in Excel
Pivot table is a powerful tool in Excel. It is the most efficient way of evaluating demographic data. To perform this, simply follow the process below.
- In the beginning, select cell range B4:G14.
- Then, go to the Insert tab and click on PivotTable from the Tables group.
- Here, select the option From Table/Range.
- Following, you will see the PivotTable from table or range dialogue box.
- Here, provide the location where you want the new pivot table.
- For example, we marked the Existing Worksheet and gave the Location cell I4 as we want the pivot table beside our original dataset.
- Afterward, press OK.
- Now, you will see the PivotTable Fields panel on the right side of your workbook.
- In this section, drag City and Total Population in the Rows and Values boxes respectively.
- That’s it, you can see the summarized data of the cities and their total population.
- In this way, you can drag any fields according to your preference and analyze demographic data.
Read More: Population Projection Formula in Excel
3. Analyze Demographic Data with Conditional Formatting
If you want to analyze demographic data on the basis of certain conditions, then Conditional Formatting is the efficient one for this. Let’s see the process below.
- First, select cell range C5:E14 as we want a comparative analysis of the populations.
- Then, go to the Home tab and click on Conditional Formatting.
- After that, select Data Bars from the drop-down menu.
- Along with it, select the type of Gradient Fill or Solid Fill according to your preference.
- Finally, you will see the presence of data bars inside the cells to get a visual comparison between the values.
- Also, if you want to highlight certain values in the Income Rate, simply select Conditional Formatting > Highlight Cells Rules > Between.
- Then, insert the two values of which you want highlighted cells in between them.
- Lastly, press OK and you will see the values between 20 and 22 are highlighted.
- Explore other options of Conditional Formatting to analyze demographic data.
Read More: How to Make Age Pyramid in Excel
4. Create Excel Chart to Inspect Demographic Data
At this stage, let us create an Excel Chart to analyze the demographic data of our dataset. Just, follow the steps below.
- First, select cell range B4:C14. You can also select any other cell range according to your preference.
- Then, go to the Insert tab and click on Recommended Charts from the Charts group.
- Next, select Pie from the All Charts tab in the Insert Charts window.
- Following, hit OK.
- That’s it, we have got our initial Pie Chart like this.
- Next, set the location of the Data Labels in the Chart Elements.
- Also, set the location of Legend in the chart.
- Finally, you will get the Demographic Data in a Pie Chart for easy analysis.
- To get more specific information, simply click on the Filter icon beside the chart.
- Here, select the cities that you want values in the chart.
- Finally, you will get the output below.
Read More: How to Create Age Distribution Graph in Excel
5. Insert Excel Formulas for Demographic Data Analysis
In this last method, we will apply an Excel Formula to analyze demographic data. We will use the SUM, MAX, MIN, and AVERAGE functions in this process. Follow the steps below.
- First, insert this formula in cell C15.
=SUM(C5:C14)
- Next, put this formula in cell C16 and press Enter.
=MAX(C5:C14)
- Afterward, apply this formula in cell C17.
=MIN(C5:C14)
- Lastly, put this formula in cell C18 and hit Enter.
=AVERAGE(C5:C14)
- Finally, we will get the output using the formulas like this.
- Lastly, select cell range C15:C18 and apply the AutoFill tool horizontally up to cell G18 to apply the same formulas to get output against all demographic data.
Read More: How to Create Age and Gender Chart in Excel
Things to Remember
- Make sure there is no empty row in between your Demographic Data. Otherwise, it will result in a false analysis.
- Keep all the data under each column in a similar format to get an accurate output.
Download Practice Workbook
Get this sample file to practice by yourself.
Conclusion
Finally, we have come to the end of our article on how to analyze demographic data in Excel. Let us know if you have any other methods for this. Follow us for more Excel blogs.
