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…

    Highlights from the Adobe Photoshop SkillPath Seminar

    Last week, Jenny Tanaka and I attended an Adobe Photoshop seminar in Waikiki at the DoubleTree hotel.

    A  few major benefits of attending seminars like this include the following: seeing what is possible in the program, becoming better equipped to do research into Photoshop's features, and watching a "Photoshop guru" put some tricks into action.





    In reviewing the highlights of the seminar, this post will focus on 3 things having to do with beginner-level use of Photoshop:
    I.  ShortcutsII.  TricksIII.  Applications

    I. Shortcuts 
    One of the wonderful (albeit daunting) things about Photoshop is that there are multiple ways to do just about anything that needs doing. This can be pretty intimidating for a beginner, so it is good to start learning keyboard shortcuts if you want to start learning Photoshop. The early part of the conference went over a few of the shortcuts that our lecturers would be using throughout the day.

    Basically, we were given a very small taste of the many, …