Microsoft Excel: An introduction



Microsoft Excel: An introduction

Hi. Let’s open MS Excel. You should see a blank spreadsheet. The letters represent columns – the vertical set of data. The numbers represent rows, the horizontal set of data.

Entering data:

When creating your own spreadsheet, you generally need a few things. First, you need column headings. These go in row one, and they identify all the columns in your data. You’ll also likely need labels for your rows. These typically go in Column A.

Let’s enter some data. Below is the number of households that earned more than $1 million in 2012, broken down by select counties, according to the Franchise Tax Board.

|County |Million$ |

| |earners |

|Marin |1667 |

|San Francisco |3023 |

|Contra Costa |1842 |

|San Mateo |3370 |

|Alameda |1736 |

|Santa Clara |5393 |

|San Diego |3211 |

|Sacramento |422 |

|Fresno |496 |

| | |

| | |

The Basics: Sorting and Summing.

We can do a few simple things real quick with this data. First, we’ll get Excel to tell us how many million-dollar earners we are talking about. Click on cell B2; hold down the left mouse button, and drag down until the blue box gets to the end of the rows. Look in the bottom center of the spreadsheet. You should see something labeled Sum, and it should read “Sum: 21160” That’s the combined number of big earners.

Notice that if you only drag over a few of the numbers, it’ll give you the sum of those numbers alone. Try dragging over the first four numbers in your list. What do you get?

You can also use the control key to sum up particular numbers. Press control and click on cells B2, B4 and B6. See how the sum of those three numbers appears?

We can also get Excel to tell us which county has the most millionaires. Click on cell B2. At the top of the spreadsheet, click on “data;” then highlight “sort.” Choose sort by “Million$ earners” and click “Largest to Smallest.” Which had the most big earners? Which was in fourth place?

Now, a couple on your own. 1) Sort the counties alphabetically by name. 2) Get Excel to tell you the total big earners mentions for San Mateo, Santa Clara and San Francisco counties.

Advanced Sorting and Formulas

Open up the “PaloAlto” file in the basket on your computer. This lists the salaries of all city of Palo Alto employees during 2012, according to the state controller.

Let’s do a couple of simple things first. Click on the letter “K” in the column headings row. Now look at “Sum” in the bottom of your spreadsheet. This instantly tells you the total payroll in Palo Alto.

Now click on cell K2, click on Data(Sort, and sort “Largest to Smallest.” Who is the highest paid employee in Palo Alto?

Let’s do a more complicated sort. We want Excel to show us which Palo Alto employees make the most within each department.

So, click on “data” and then “sort.” Now click on “Department” and “Smallest to Largest.” Then, in second slot a bit down – you may need to click “Add Level” – choose “Total Wages” and “Largest to Smallest”.

Now, on your own, do the same for job classifications.

Next, we’re going to start using formulas. First, let’s figure out how many employees in Palo Alto earned more then the maximum for their job classification in 2010.

Right click on the letter “L” in the top header row and choose “Insert.” Now, in cell L1, type “OverMax”.

Now, click on cell L2. We’re going to enter a formula. All formulas start out with an equal sign. Once you put that = in there, simple formulas are just like entering numbers into a calculator.

Type “=k2-f2” This will subtract actual compensation from salary maximum.

In row L2, you should see the results of your calculation.

The next step is cool – it’s where spreadsheets save you a bunch of time. Click on the L2 cell– the cell you just filled in. Hover over the tiny box on the bottom right hand corner of the cell. Your cursor should turn into a black cross. Double-click on that cross. This performs the same calculation for all salaries.

Now, click on cell L2 and do a sort from largest to smallest. Did many people earn more than the maximum posted salary?

Next, let’s say we want to add health benefits to the pay of each employee, to get a sense of their total compensation.

Right-click the letter “Q” in the top header row and choose “Insert.”

In cell Q1, call the column “PayandHealth” In cell Q2, type “=P2+K2” After getting an answer for the first cell, hover over the bottom right hand corner of Q2 and double click when you see the black cross.

Now, on your own, add up and sort Total wages, and total retirement and health benefits for all employees in Palo Alto.

Pivot Tables

Often, you’ll want to group data for calculations. To do this, you’ll need to create a pivot table.

At the top of the spreadsheet, click on “Insert,” then click on “Pivot Table” You’ll get a wizard showing the range of data that we will create the table from. Just make sure your entire spreadsheet is highlighted and click “OK”

The next screen will vary depending on your version of Excel. In Excel 2010, you’ll see a field list on the right and a report form on the left.

We want to see which Departments have the largest payroll. In the field list, click and drag “Department” into row labels. Now, click and drag “Total Wages” into the values column.

This should give you a sum of each department’s payroll. Let’s click on cell B4 and sort from highest to lowest. Who has the highest payroll?

Now, let’s count how many employees each department has. Once again, click and drag “Total wages” down to the values box. Then, right click or double click on the item you just dragged down, and choose value field settings. Let’s change the summary from “Sum” to “Count.” This simply counts the number of salary fields in the data, giving us the number of employees in each department.

We can use the same process to figure out averages. Click and drag “Total wages” down to the values box. Then, right click or double click on the item you just dragged down, and choose value field settings. Let’s change the summary from “Sum” to “Average.” Who has the highest average pay?

Now do several on your own. Find the average health benefits by department. Find the most common job classification in the city. Find the average pay for each job classification, and rank it from highest to lowest.

More complex formulas: Percentages and Ratios

Open up the file marked “PaloAltoStats” in your folder.

Finding percentages is one of the most common functions in Excel. Here’s how to do it.

Say we want to find the percentage of Palo Alto adults who have less than a ninth-grade education. Click on cell C5. We want to divide the number of adults with little education by the total number of adults in the city. So type an equal sign, then click on cell B5, then hit the / sign to divide, then type 47678. It should read “=B5/47678”. Hit enter. Now, click on C5 and hit the % sign at the top of Excel to convert to a percentage.

Now, double-click the right hand corner to apply the formula to all education levels.

Now we want to calculate a year-to-year percentage change in Palo Alto residents biking to work. First we want to subtract the number of bike riders in 2000 from the number in 2007. This reads “=(B16-B15)”. Then we divide our answer by the old number – Year 2000 bike riders. But you have to use parenthesis or the answer comes out screwy. So the whole formula reads “=(B16-B15)/B15.” Hit enter. Double click the box in the corner. Hit the % button. Highlight all the new cells, click the square on the bottom and drag right five cells; you just calculated the percentage change in bike riders over five years.

Now calculate a total percentage change in bike riders from 2000 to 2012.

Importing data

Lots of times, data will come to you in text format. Getting this into Excel is easy. Let’s open up the schedule.txt file in Excel.

Opening this file should cause a “Text Import Wizard” to pop up. This will ask you whether the data is “delimited.” In other words, is there something between the data that marks where column breaks should be?

In this case, there is – commas. So click “delimited” and then hit “next.” It asks you for what the delimiter is, so choose “comma.” Also note the text qualifier field. In this case, our text fields are wrapped in double quote marks, so make sure that is highlighted. Hit “next.”

The last part of the wizard asks you to choose what type of data is in each column. This is usually not something you need to worry about. Let’s click finish. Viola.

Another cool thing about Excel is that it is pretty smart about tables pasted from the internet. So it’s not too hard to cut and paste stuff you find online into excel and to run formulas like the ones above.

Let’s do an example. Go to Google or Yahoo and search for “Santa Clara County coroner.” Click on the first link. On the left side of the page, click “statistics.” See the table? Click the top left hand corner of the table and drag diagonally so the whole table is highlighted. Go up to the edit tab at the top of your browser and click “copy.”

Now open up a new Excel spreadsheet. Click on cell A1, go to “Edit” and paste the data.

Review: Working with data.

Let’s find out some stuff with this coroner data.

1: Sort the data to find out what year had the most homicides.

2: Find the total number of homicides and suicides for each year.

3: Find the total number of deaths for each year, minus accidents.

4: Find the percentage of deaths each year that were accidents.

5: Find the percentage change each year in motor vehicle fatalities.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download