Skip to main content

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

    Popular posts from this blog

    E pule kakou . . .

    Aloha all,
    I was trying to think so hard of a "techie" tip and finally gave up. I even googled "tips and tricks" for various programs and then thought "I can't blog about something I don't actually use!" Then, as I was sitting in my Papa Makua class, doing all kinds of protocal and thought about how we keep looking for a short pule to do to open our meetings. I had `A`ali`i write a pule in Hawaiian. He was worried about the grammar and structure of it so I asked Kelly C. to kökua by editing and doing an audio recording so you can hear the pronunciation. Hope it's helpful :)

    E ho`omalu käkou
    E kö mäkou makua i loko o ka lani
    Mahalo no nä pömaika`i a pau. Mahalo no ke ali`i lokomaika`i o Pauahi a me këia kula nei. E `olu`olu, e kia`i iä mäkou i ke alahele küpono me ka lökahi.
    Ke nonoi ha`aha`a nei mäkou i ka inoa o Iesu Cristo
    `Ämene

    `Unuhi (translation):
    Let us pray
    Our Father in heaven
    Thank you for all the many blessings. Thank you for the generous Pri…

    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 the Leucaena Leucocephala tree. I…

    Blackboard World 2008: The Power of Web 2.0

    I thought I'd share a couple of Web 2.0 tools that came from a presentation that I went to at Bb World this past July that was titled "The Power of Web 2.0." The presenter was a high school teacher in a San Diego public school. She described many free tools and how she used them in her class. A couple that I thought were intersting were ToonDoo (creates comic strips and is the basis of their social network) and bubbl.us (creates mind maps). The great thing is that it's free, platform independent and no software installs. The presenter also gave her web site that lists even more Web 2.0 tools. You don't need an account to try out bubbl.us, though it does require an account for ToonDoo. I created a generic account for ToonDoo, username "vsdl", password "vsdl2008". I know mapping software is used in some of the 'Ike Hawai'i courses so maybe this would be a useful alternative. As for ToonDoo, at the very least it's entertainin…