How to build a pivot table to extract specific information

I am needing help! I am a non traditional student who is struggling to extract the necessary information from an excel spreadsheet. I have tried YouTube and I honestly have zero idea what I am doing!

I am supposed to be:

1: determine the top 100 zip codes overall as selected by population density;
2: at least 3 franchises must be sold in each of the listed 14 states (and in that 100 zip codes);
3: identify the "top two" in each of the 14 states based on population density.

I understand that I need a pivot table to accomplish this.

The current titles on my table/spreadsheet are:
zip codes
y-latitude
x-longitude
city
state
population
population density
national rank.

How do I decide which of the information goes into rows, columns, etc.? Is there some sort of rule that helps with this so I can make a good decision compared to just guessing?

Please help! Thank you!

Hi,

The best way to go about it is to try it out. Play with the different settings in order to learn how Pivot Tables and Charts work.

In your case, I'd start the following way:
1. Drag zip codes in rows
2. Drag population density to Values. The data there will be automatically aggregated to Sum. Depending on your data, you may want to change it to Average, in order to get the average population density per zip code. Depends on your data. To do that, just click on field in Values, go to Value Fields Settings and select Average.
3. Now you just need to sort the chart column with the population density Z to A in order to get the highest result first.

This should get you started.
You can then plot the state in column, as a row (above the zip code), or even as a filter, so that you can drill down more in the data.

Hope this helps!