I have a sheet with two column. Column A has around 300 zip codes. Some of them are duplicates. Column B has median income figures related to each zip code.
The only way I can think of that such data would make sense graphically is to make some kind of map chart, where each zip code on the map is coded a shaded color that matches an income range. That’s not a standard feature that Excel offers though.
Other than that, I can’t think of a meaningful graphic. ZIP codes don’t represent any numerical relationship between the areas they represent - so putting them in order or groupings won’t mean much. If you grouped them by the first two digits you’d have a group that roughly represents a region of the country but that’s about it.
ETA: If you want to remove your duplicate records, depending on the version of Excel you would go to the Data tab and in the Data Tools group there is a tool called “remove duplicates.”
Yeah, my first question is how important are the duplicates? If all you want is the zip code and its associated median income then I’d simply delete all the duplicates, sort ascending on income and make a bar chart with zip codes on the x axis and income on the y.
The wealthiness (or otherwise) of the given population, basically. I have a mean calculation now ($81,549) compared with the median for the county ($93,500) - so slightly below average.
The only logical way I can think of to illustrate this is a map of income by zip code, like Skammer said.
ESRI (a company that makes software to analyze geographic representations of data) has this already made for you as a free example using 2012 census data: http://bit.ly/135yffb
Keep in mind that a zip code isn’t really a geographic area (they’re postal carrier delivery routes, more like lines), so it’s not easy to just map them out. The census instead uses approximations called ZCTAs, and few online mapping solutions seem to be aware of these, usually just plotting ZIP to single lat/long point instead (which makes for horrible heat maps). If you want to do it right like ESRI did, you would probably have to learn some rudimentary GIS instead of using Excel. (But if I’m wrong and there is a simple solution, do let me know). If you customized the map, for example, you could change the color gradient to be something like “white=close to county median”, “green=above median”, “red=below median”.
Can you get a latitude and longitude for each zip code? Then you could put them into a 2-d map without any special mapping software. Convert the median incomes into color zones: White for the highest 20%, yellow for the next 20%, green for the next 20$, etc. Or something like that.
And be careful about averages: unless every zip code has an identical population (unlikely), just averaging the numbers is meaningless.