How to Analyze Demographic Data in Excel (5 Essential Methods)

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. But 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.


Download Practice Workbook

Get this sample file to practice by yourself.


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.


5 Essential Methods to Analyze Demographic Data in Excel

To describe the process to analyze 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.

5 Essential Methods to Analyze Demographic Data in Excel


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.

Apply Sort & Filter Tool to Analyze Demographic Data

  • You can also get this tool from Home > Editing > Sort & Filter > Filter.

Apply Sort & Filter Tool to Analyze Demographic Data

  • 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 Use Analyze Data in Excel (5 Easy Methods)


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.

Insert Pivot Table to Evaluate Demographic Data in Excel

  • 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.

Insert Pivot Table to Evaluate Demographic Data in Excel

  • 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: How to Analyze Data in Excel Using Pivot Tables (9 Suitable Examples)


Similar Readings


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.

Analyze Demographic Data with 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.

Analyze Demographic Data with Conditional Formatting

  • 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: [Fixed:] Data Analysis Not Showing in Excel (2 Effective Solutions)


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.

Create Excel Chart to Inspect Demographic Data

  • Next, select Pie from the All Charts tab in the Insert Charts window.

Create Excel Chart to Inspect Demographic Data

  • 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 Add Data Analysis in Excel (with 2 Quick Steps)


5. Insert Excel Formulas for Demographic Data Analysis

In this last method, we will apply 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)

Insert Excel Formulas for Demographic Data Analysis

Here, we applied the SUM function to get the total value of population in cell range C5:C14 for the 10 cities.
  • Next, put this formula in cell C16 and press Enter.
=MAX(C5:C14)

Insert Excel Formulas for Demographic Data Analysis

In this formula, the MAX function helps to find the highest value in the cell range C5:C14.
  • Afterward, apply this formula in cell C17.
=MIN(C5:C14)

Here, the MIN function finds the minimum number of populations from the selected cells.
  • Lastly, put this formula in cell C18 and hit Enter.
=AVERAGE(C5:C14)

The AVERAGE function returns the value of the average count from the selected cells.
  • 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 Enter Data for Analysis in Excel (2 Easy Ways)


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 the accurate output.

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. Keep excelling with Exceldemy. Follow us for more excel blogs.


Related Articles

Guria

Guria

Hello! Welcome to my blog. I have completed my Bachelor in Architecture from Ashanullah University of Science & Technology, Dhaka. I am a passionate, goal-oriented person with an immense thirst for knowledge and an attitude to grow continuously. Besides Architectural work, I find it very enthusiastic to work in Excel blogging. Exceldemy is a platform where I have got the opportunity to flourish my skill in Microsoft Excel and therefore, here I will be posting blogs related to it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo