Excel: Pivot Tables and VLookups

Microsoft Excel has many features that can help you make aggregating data or information. Pivot tables are one of the most powerful features of Excel. A pivot table is a great reporting tool that allows you to sorts and sums independent of the original data layout in the spreadsheet. This cuts down on the manual counting and human errors. Pivot tables are also useful for quickly creating unweighted cross tabulations.

Pivot Tables: How a pivot table works

Find a spreadsheet you have with lots of data that include column headers.
Select all the data in the worksheet by clicking in the box at the top left hand corner of the spreadsheet.
Go to Insert and click on Pivot Table.
It will verify the data selection and ask you if you want to create this on a new spreadsheet.

On a new spreadsheet, you will see a new sheet with a box labeled Pivot Table 1.
On the other end you will see a screen that has the fields. On the bottom of that you will see four items: Report Filter, Column Labels, Row Labels, and Values.
Drag and drop the items you want at the top Column Labels. Then, what Values do you want counted.
You will notice on your spreadsheet your data starts to display information.

Need more information, watch this excellent tutorial on Pivot Tables.

VLoopup

MS Excel has a function that allows you to find information in a table of data. Using a vloopkup returns a value from a table that you have in the workbook using the first column as your index or primary identifier, the table range and the column which contains the value to return. It's an excellent function for trying to assemble data for simpler viewing or referencing.

Here's an excellent video that covers vlookups.

Comments

Schools of the Future: Acquiring and Representing Knowledge

This year's Schools of the Future conference was an information-rich event. When we think of the future, more than a few of us probably also think of technology. That association certainly carries over in the interpretation of the conference title Schools of the Future . Indeed, many topics besides educational technology were covered; however, the technological advances were a strong highlight at the conference in my view. As mentioned in one of Cassie's previous posts , there are online classes everywhere. The first few links on my list reference ways to learn on your own (i.e., ways to acquire knowledge). The second set of links refer to ways of representing knowledge. **Note: All icons link to the affiliated website. Academic Earth has hundreds of free online lectures from prominent university professors, including Carnegie Mellon, Stanford, The University of Houston, Cornell, Dartmouth, Harvard, MIT, Michigan State, Princeton, Rice, UCLA, UCSF, and the list

Geotagging

A "wouldn't it be nice" idea that's been around a while is the ability to tag a map with images that are linked to more information about the site. Kelly C suggested it as a way for students to share knowledge about a cultural/historic site or the geographic area they live in with classmates. (right, no addresses!) There are now cameras and even storage media that embed latitude and longitude into images as they are shot. But you don't need fancy new toys to do this. I tried Flickr's geotagging map and it's fun and supereasy. Want to try it? Log in to Flickr http://www.flickr.com Sign in as " techedine " password " wist101 " yea, corny. Click the " You " tab then the " Your Photostream > Map " or Organize > Your Map links. Click the Satellite link in the upper right. Cool view! (you may need Google Satellite downloaded). Images along the bottom of the screen with colored dots have already been droppe

Zoho Creator

I thought I’d share this web application I came across in my quest to find a relatively simple app to help us manage our mentoring data in the DL Orientation. Our specific needs were that it be a free online database, password protected, had the capability of rapid form development without too much programming knowledge and was easy for the end user to use. The application that met these requirements was Zoho Creator ( http://www.zohocreator.com/ ) a part of a suite of online applications including word processing, spreadsheets, presentations, wikis, organizers and more. What’s great about Zoho Creator is that they have an online repository of applications already developed that you can download to your account and use for free. Initially, I tried some of these applications, but it was overkill for our needs. Zoho Creator can do a lot if you know programming and they have a pay model where it allows you more flexibility and features. I think Zoho Creator would be a useful tool for both

Kamehameha Schools Distance Learning (KSDL) Owau

Search This Blog