Since AggData has so many uses, it is sometimes important to know how many stores are located within particular states, countries, or zip codes. It is quite easy to create a table that will give you a clear view of this information. Open your AggData in Excel, and then click on the Insert tab. Once that has opened locate the Pivot Table on the far left. Click on Pivot Table to begin the process.
A window will open. It will be set up to use the data you have opened, and it will create a new worksheet from said data. Click OK and the program will create the pivot table.
When the pivot table opens, it will provide you with several options, but for this particular process it is fairly straight forward. You will need to familiarize yourself with column, row, and values to create the table.
For this tutorial we will create a State listing; Country or Zip Code listings can be created in this same way. To begin we can either click on State, and it will automatically appear in the Column Label location, or we can click and drag State to the Column Box. Once that is done the Pivot Table will create columns for each of the states listed in the AggData.
To get the count for each State, click on State again, and drag it down to the Values location. Once “Count of State” is in the Values location, the table will be created with each state having the number of locations listed below it.
If you prefer a vertical listing instead of a horizontal listing, you can click and drag the State into the Row Label and the Value Locations; then the table will be created vertically using rows, instead of horizontally using columns, to list the states and their values.
The Defer Layout Update button will let you add items to or remove items from the pivot table but hold off on applying those changes to the pivot table until you have organized it to your liking. This will also help to avoid any potential system slow-downs until you have completed any changes you need to make to the pivot table. To use this option, click on Defer Layout Update. Then you can change the table according to your needs. Once you have finished, in order to apply your changes to the pivot table, click the Defer Layout Update button again to disable the function, and then click the Update button to complete the changes.
Now you have completed the AggData pivot table which lists how many locations exist within each state, country, or zip code.
