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

Scratch - OWAU discussion 10/28

Aloha kākou, I while back, I learned about this product from a presentation at the eSchool conference. The speaker was so excited about Scratch and was having so much fun demonstrating it that my mind began racing immediately. "I could use it in `Ike Hawai`i courses and I bet I could use it to develop tons of activities for the A`o Makua `ōlelo Hawai`i courses". Then, reality set in as I returned to work intending to try it out after I finished my "next" task. Well, you know how that goes. 7 months later, I finally took a stab at it out of necessity of course. I really wanted some type of activity to teach my students about different Kapu in old Hawai`i without having them just read a list of them. So, what it Sratch? Simply put, it's a developer's tool (a very inexpereinced developer like myself). It allows you to create activities and games using "coding" that is in a drag and drop format. The codes are pre-written & range from phrases like ...

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...

Papa Kuʻi ʻai a me Pohaku

As part of our huakaʻi last month to Papahana Kuaola and the opportunity to work in the loʻi, I wanted to continue that thought by sharing my experience of making a papa kuʻi 'ai (poi-pounding board). In 2008 with the encouragement from me and my co-worker, Pili Wong, Earl Kawaʻa offered to teach a papa kuʻi ʻai papa to those of us that were interested in learning what our kūpuna did as a daily way of life. For our kūpuna they had loʻi in their yards and grew their own kalo, the major source of starch in their diet. They steamed it and pounded poi or kept it whole and sliced it and ate it like bread with butter or condensed milk. Kawaʻa was very specific on our kuleana and the commitment he required of us. Our first task was to find an au koʻi (handle) for our koʻi (adze tool). I found myself suddenly looking up at every tree I saw looking for the right branch for my koʻi. My husband found mine at a jobsite from a Haole Koa tree otherwise known as ...

Kamehameha Schools Distance Learning (KSDL) Owau

Search This Blog