How To Make a Pivot Table
Sydney's Seminar
While Excel is easily one of Microsoft’s most useful products, many people don’t know how to get the most out of it, and we often get asked how to make a Pivot Table.
What is a Pivot Table?
A Pivot Table is a feature in Excel that allows you to create a manipulable matrix to view and analyze a larger set of data. This can be an invaluable tool for those of us who like to stay organized through excel. In this article, I will give instructions on utilizing this feature on a Windows device.
COLLECT YOUR DATA
First, you will need a data set with multiple rows and columns to make a Pivot Table.
How you organize the data is essential. For the sake of ease, always use the first row and column of the axes as labels for your date.
(See the image for an example of an organizational style.)
Notice that I keep the dates in columns rather than rows. Excel assumes this is the way you will format your data, and this will be a much more user-friendly style.
INSERT PIVOT TABLE
Once your dataset is complete and organized to your preference, you will want to click and highlight all the cells that you want to be included in your Pivot Table. With the cells highlighted, you will want to go into the Insert tab on the top banner. You will then click the icon that is labeled PivotTable. A pop-up screen will appear that will give you several options for your table.
The first section of the pop-up wizard will give you the option to change or add data to the set. If you highlighted the information before you clicked the icon, though, this should be auto-populated.
The next section lets you decide where you want the Pivot Table to be located. This will default to creating a new worksheet, which is essentially just a second tab that you can find at the bottom-left of the page.
This will be titled Sheet2. If you would prefer the Pivot Table to be somewhere else, you can click the box labeled Location and then click the cell that you want the top-left corner of your table to be located.
PIVOT TABLE FIELDS
Once you hit OK on the Create Pivot Table wizard, you will see all the fields you can create for your table on the right side of the page. First, you will want to select what information you want to analyze from the data. In my example, I want to know the average temperature on a Monday in December. I will click on the Day option and drag it into the Rows box to do this.
I then want the value for each day to be the average high temperature. I will drag that option into the Values box. It may default to a sum of the temperatures, and in that case, you will drop down on the item in the Values box. Click Value Field Settings, and there you can change the formula type. If you want to change the format of the setting, you will click on the Number Format button in this pop-up as well.
Now I can tell you that the average Wednesday in December in Hagerstown, MD is 43 degrees.
WHAT ELSE CAN I DO?
This is just one example of the many organizational and analytical features that Excel offers. Keep an eye out for more upcoming tutorials for Excel. Should you need any tips or advice sooner, never hesitate to give us a call at 301-456-6931 or send us an email at support@clarkcomputerservices.com.
Being raised by Clark’s owner, Darren, I have always been immersed in the world of technology. However, I have always followed it from a distance. I went to college to get my degree in Business Finance and Applied Economics, as I have always been a fan of research and statistics. I was even lucky enough to get my senior thesis in economics published. My next string of luck was getting a job straight out of college as a Researcher in Richmond, VA. I was able to pursue research and publish dozens of news articles in my field. Now, I am so excited to delve back into the world of technology that I was raised in, and look forward to honing my research in the technological field.
