To create a pivot table, every column of your data must have a variable label; in fact, it is always good practice to put in a variable label as soon as you insert or add a new column.
First, you make sure your cursor is on some cell in the table. Then go to the tool bar, click on the "Insert" tab, and then click on the "Pivot Table” icon. A window will pop up that looks like this: Normally, all you need to do is hit "OK" This will open a new sheet that looks like this: To build a pivot table, you should visualize the piece of paper that would answer your question. Our example data shows 103 provinces in the 20 Territories of Italy. Imagine that you wanted to know the total number of murders in each Territorio. The piece of paper that would answer that question would list each Territory, with the total number of murders next to each name.
To build this pivot table, we would use the mouse to pick up “Territory” from the list of variables in the floating box to the right, and place it in the “Row Labels” box below. We would then take the “Murders” variable and put it in the “Values” box. This would be the result: If you click the cursor into the “Total” Column and hit the Z-A button to sort, you will get this:
It is possible to make very complicated pivot tables, with multiple subtotals. But I recommend making a new pivot table for each question you want to answer; several simple tables are easier to understand than one very complicated table that tries to answer many questions at once. The little black down-arrow button on the "Values" variable opens up a box that will let you make a variety of other choices about how to summarize and display the result. Click on "Value Field Settings" and you get this: OTHER EXCEL TIPS Excel will import data that comes in a variety of formats other than the native *.xls or *.xlsx that Excel uses. For instance, Excel can readily import text files in which the data columns are separated by commas, tabs, or other characters, like this: If you find a web page with data in table format (rows and columns), Excel can open it as a spreadsheet. Copy the table and then paste it into Excel; very often it will flow properly into the correct columns.
FINDING DATA Government agencies are starting to make some of their data available in Excel or other formats. For example, ISTAT.IT has very comprehensive data about Italian demographics, economy, crime, etc. Many of their tables can be downloaded directly as Excel files. One trick to find interesting data would be to use Google and add these search terms: site:.gov filetype:xls. NEED HELP? Feel free to send me an email at [email protected] . I will be glad to give you advice if I can.
You've reached the end of your free preview.
Want to read all 14 pages?
- Spring '18
- Dr. Moez