[Libs-Or] Tech-Talk: EXCEL - Quick Data Analysis with Pivot Tables

Tue Jul 3 16:21:29 PDT 2018

Greetings!

The Tech-Talk e-newsletter for Oregon library staff is distributed weekly via the Libs-OR and TechTalk mailing lists. Please do not forward. Know someone who might be interested in receiving a copy but they’re not on Libs-OR? Have them contact darci.hanning at state.or.us<mailto:darci.hanning at state.or.us> or visit http://listsmart.osl.state.or.us/mailman/listinfo/techtalk to subscribe to just the Tech-Talk newsletter!

This paid subscription service is supported in whole by the Institute of Museum and Library Services (IMLS) through the Library Services and Technology Act (LSTA), administered by the State Library of Oregon. Enjoy!

[http://files.constantcontact.com/ee1208b4001/34916cb2-412f-43fa-8294-1f7ecb0e9a3d.png]

 This Week's Topic: EXCEL

1. VIDEO ... Quick Data Analysis with Pivot Tables
2. ARTICLE ... Quick Data Analysis with Pivot Tables
3. COMMUNICATING ... Success leaves traces (Armand Morin)

[http://files.constantcontact.com/ee1208b4001/3abf38e0-7643-480b-a089-216a0535c101.png]<http://r20.rs6.net/tn.jsp?f=0018EFDmOI0HwftzDQCexmpv61z8kH7mYtYBpFsRl22XVgf8M3nLZl1PR1fMQNwqQFtHevvK_K1hQ2aU3oZTKc2hVenFK50oAa11LGZtEfwA_NWi0ZyunsAe4pdHdNF-mAaEAu2sjh_j-DQnVlgZsOZeDyT2oKVKswFN_JpIFtQxywsGeNiRBZZp1nir_vJl3sIbp6yjO13reKeUoED6I7yIA==&c=kRPMgRh3Gl845-hAl4wU6RoWPDfYLJK1N5XjqAEXR6aLH0Y3ITt46g==&ch=_iMAHRTTbRb5tTNfN0FnkTX4pkDesJ2aJQKECiHT0IDiwo3He4z4Jg==>

Photo by Picsea<http://r20.rs6.net/tn.jsp?f=0018EFDmOI0HwftzDQCexmpv61z8kH7mYtYBpFsRl22XVgf8M3nLZl1PbZWdl61c7i5akDabHqTAtjavMOSf1zxj6u1n7l4sRuCr-Onc8lsVZ0QpTfzV_BacMwu-DZefpzNlqnqqmJNL2VtPjgT4TPU50s5ggH_M4rFWKg2GQCsbdnFF0IuWykbVzdHBN6_FX2yMorjTZvFGwMurHhdsOzVbZGRgUxg5mX6Wbin_krCLK56x9ppjzEtVqVD3pRL_T-nErSKeSyDmh8OyYeUk3M9tA==&c=kRPMgRh3Gl845-hAl4wU6RoWPDfYLJK1N5XjqAEXR6aLH0Y3ITt46g==&ch=_iMAHRTTbRb5tTNfN0FnkTX4pkDesJ2aJQKECiHT0IDiwo3He4z4Jg==> on Unsplash<http://r20.rs6.net/tn.jsp?f=0018EFDmOI0HwftzDQCexmpv61z8kH7mYtYBpFsRl22XVgf8M3nLZl1PbZWdl61c7i5DHg1_FW0KRl7g51Ydgdvn7ch1_JntZnR00uWyFyxU5CxJsOoL1FT1O2f8opFyJYYJgpUKTEQkWtzpWTRKkBn8fJsiS6_qlK3SQSXcK-G0HjZewNzrDwxfH8G00BNvrXx3VZYylFwxjfFN8Qyl30iN4C7oEE4VcS-IPB5Hmlz92J0IVmsF6dKIHxqWs0OJcWb6Yy4cQReT1dt8OxAUt9gehbIM6b1v8PM&c=kRPMgRh3Gl845-hAl4wU6RoWPDfYLJK1N5XjqAEXR6aLH0Y3ITt46g==&ch=_iMAHRTTbRb5tTNfN0FnkTX4pkDesJ2aJQKECiHT0IDiwo3He4z4Jg==>

Brought To You By State Library of Oregon

[State of Oregon]

Visit Tech-Talk.com Archives<http://r20.rs6.net/tn.jsp?f=0018EFDmOI0HwftzDQCexmpv61z8kH7mYtYBpFsRl22XVgf8M3nLZl1PbZWdl61c7i5YBkJglLF4iOsDwUouBnERhEZpKfjKi77XlRhWwK2S5Jlbn_yFhgZyDW-UESWr3O69EYe0L0da7QLys93rOCcaJNtv18kT80o&c=kRPMgRh3Gl845-hAl4wU6RoWPDfYLJK1N5XjqAEXR6aLH0Y3ITt46g==&ch=_iMAHRTTbRb5tTNfN0FnkTX4pkDesJ2aJQKECiHT0IDiwo3He4z4Jg==>

User: ORLIBTECH
Pass: ORLIBTECH

Questions about Tech-Talk?
darci.hanning at state.or.us<mailto:darci.hanning at state.or.us>

Tech-Talk: EXCEL - Quicker Data Analysis with Pivot Tables
Advanced

[http://files.constantcontact.com/ee1208b4001/8f74482d-3a46-455a-aa5a-8abc109ad412.jpg]

Sure, you know how much you spend on the big budget items like utilities, phone and shelter. But what about all the miscellaneous stuff? How can you figure it out easily?

Yes, you can total columns, create formulas and count items. Or, you could use a pivot table. It makes analyzing your expenses a breeze. You'll discover all kinds of relationships within the data with just a click or two. It's like a magic picture box, revealing new insight.

But of course, it's not just about expenses! In fact, you can use a Pivot Table to analyze almost any data you can get your hands on. Just put it in a spreadsheet, then pull out all the relationships.

For instance, you may have data on programs you run, volunteers you use, events you sponsor. Whether it's a business calculation, operational tracking or a personal report, if you've got numbers or text in a spreadsheet, a pivot table can give you loads of valuable information.

Volunteer & Staff Data for Events Across the State

[http://files.constantcontact.com/ee1208b4001/86974dc4-9140-4056-83b0-863a3e6975de.png]

What is a Pivot Table?

In its basic form, a Pivot Table summarizes data (or pieces of it) so you can make sense of your information, without typing any formulas! You drag and drop key ideas into boxes to reveal answers.

You're essentially asking questions and getting results ... in the form of a "report" (also known as a pivot table). You want to know what the data is telling.

Actually, the purpose of a pivot table is to build a "report". In this case, the report is not a text document, but a small table with the analyzed data displayed. Your answers!

Two Pivot Table Reports: Volunteer Hours & Percentage Hours

[http://files.constantcontact.com/ee1208b4001/afe4fde9-9326-46d4-b34d-c714ad54e3af.png]

Pivot Tables Answer Questions

One of the best ways to get started using a pivot table is to jot down a list of questions that you want to be answered. Here are some examples:

1.  How many volunteers vs. staff donated time?
2.  What percentage of hours has been spent on each of the programs?
3.  How much time was donated to the Green Clean project?
4.  How many attendees were there at each event?
5.  How much revenue was generated by each offering?
6.  What percentage of cost is each element?

You get the idea. Look at the data that you've collected in a spreadsheet and make a list of the questions for which you'd like answers. Then create a pivot table for each question to get a PivotTable Report. (NOTE: From Internet research it appears that Pivot Table, pivot table and PivotTable are all correct and used interchangeably.)

[http://files.constantcontact.com/ee1208b4001/34ead41e-2c90-43eb-b364-f895cd513396.png]

Get These Basics ... Then Follow the Microsoft Tutorial

Now before going into how to create a pivot table, there are a few important concepts and key terminology you need to know. When you understand these, putting together your first pivot table will go more smoothly.

In fact, Microsoft has done an excellent job creating a new and simple tutorial for you, right at your fingertips. We'll show you where to find that after we've given you a solid understanding of what is going on, how pivot tables are different from anything else... and introducing you to the language of pivot tables.

Key Terms To Know

·     There are two master elements in a Pivot Table: PivotTable Area and PivotTable Report. You do your work (add the elements you want to analyze) in the area. You get the results in the report.

·     Fields is the term used instead of columns. (Fields = Columns). Any column heading you have in the spreadsheet will show up in the fields listing.

·     Value Field requires a number, so the best thing to drag there is the amount field, often with such items as costs, commissions, expenses, hours and so on.

·     Amount is the area where you identify what you want summed up, or counted or have the percentage calculated.

·     PivotTable Fields box. This is the area that lists your different fields, each of which can either be checked (and they will automatically appear in the boxes below and in the report ... or dragged down to the box you want.

NOTE: You can literally play with all the variables. Just click or drag and see what the report on the left says. If that's not the info you want, click or drag something else.

[http://files.constantcontact.com/ee1208b4001/1d0a7b52-ebf9-4627-a253-215981b34672.png]

[http://files.constantcontact.com/ee1208b4001/1db8ffd9-1ce7-4c1f-8d5f-b52bdbf913c1.png]

Three Important Concepts

1. Get your spreadsheet in good shape before creating a pivot table. This means getting rid of duplicate entries; empty rows, columns and cells; merged cells.

2. Then when you insert a pivot table, it will automatically find and list the fields (column headings) in your spreadsheet. A pivot table is very intuitive.

3. Don't panic ... when you insert a pivot table, it does not show up in your spreadsheet. Actually, it opens a new spreadsheet in the workbook (unless you tell it otherwise).

[http://files.constantcontact.com/ee1208b4001/650921df-01d6-47f9-adbf-505ea2c6cfbe.png]

Three More Helpful Ideas

All right, here's where it gets a little tricky. The next three concepts are important basics to remember, but they will make much more sense after you've run through the short (and effective) tutorial by Microsoft. (See below for instructions.) We put these ideas here so you have everything you need in one place when you're ready to start!
~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~

1. In working with the data, start by creating a value field by dragging the Amount field to the Values area. Remember, the fields that are best for the value fields are ones with numerical amounts.

2. Then you want a condition to divide the value field by. To do that, drag any of the other fields down to the Rows area.

3. When you make changes to the data source, you can update the pivot table by clicking anywhere in it, and press the F5 key (just as you would to refresh an Internet page).

[http://files.constantcontact.com/ee1208b4001/293fb111-b3ca-44cc-93a5-494a9984422b.png]

Creating a Pivot Table

Frankly, Microsoft did an excellent job of creating a PivotTable tutorial right within Excel. It's so good, we want you to know about it and use that ... rather than our re-interpreting it for you. We've given you the foundation, now visually see how to put it all together.

Here's how you find this tutorial and use it.

·     Open your Excel spreadsheet full of the data you want to analyze.
·     Click on File. Choose New.
·     Select "Make Your First PivotTable" (or if you don't see this, just type those words in the search box).
·     Click on "Create" -- then "Let's Go" and move through the slides.

Going through this tutorial will get you ready to start exploring all that Pivot Tables can do for you.

[http://files.constantcontact.com/ee1208b4001/f8f1ecc8-4884-4c21-8ed0-cf647e6db944.png]

Communications
Success Leaves Traces

Here's an interesting ... and very powerful ... concept: "success leaves traces".

This is a phrase coined by Armand Morin for a method he has developed for analyzing what works really well, and using that information, to create something equally successful.

The basic idea is simple. If you want to create something exemplary, something world-class, find existing examples and observe everything about them to find common elements. You then have a skeleton of items you need to address in your own effort.

Success leaves traces. When you discover the signs that are similar to all (or most) of the successful examples you've chosen, you've unlocked the formula.

Armand's Legendary Test

Armand Morin is a world-renowned online marketer. He also loves to sing ... Country Western. He decided to make a CD and, of course, expected it to be a hit. How to make it that way?

Following his belief that "success leaves traces", he observed everything he could about the top male country artists:
·     Their names (he discovered they had one-syllable first names)
·     What color they wore on CD covers
·     Who produced the inserts for CD packages

He analyzed the song titles of the best hits.
·     How many words
·     The theme of the cover song
·     The rhythm of the most popular hit

Did it work? Absolutely! You can do it too.

When you want to create something new, take a look at other exemplary models. See what traces are there for you to emulate. Learn from the success of others.

[http://files.constantcontact.com/ee1208b4001/b13a7a6a-7445-4d8e-b741-4b475385ef5b.png]<http://r20.rs6.net/tn.jsp?f=0018EFDmOI0HwftzDQCexmpv61z8kH7mYtYBpFsRl22XVgf8M3nLZl1PR1fMQNwqQFtehbSN1NihAHGN7mal9TtKdXv9T4QcPZYCN-3Z6QauAXuQeJpa8DcuePxzO2JIJTAaRi_mGGV6KX9aMcpwIXhqJ-xrRmRDPrmU_LaAk_fDec=&c=kRPMgRh3Gl845-hAl4wU6RoWPDfYLJK1N5XjqAEXR6aLH0Y3ITt46g==&ch=_iMAHRTTbRb5tTNfN0FnkTX4pkDesJ2aJQKECiHT0IDiwo3He4z4Jg==>

[Ask a question]<http://r20.rs6.net/tn.jsp?f=0018EFDmOI0HwftzDQCexmpv61z8kH7mYtYBpFsRl22XVgf8M3nLZl1PR1fMQNwqQFt7nBvtsfpdfgZrWh7jLAIT4NtSbzigTAZkRNLu9b-qDKD8Xnz3KRB0olXJrx1oi05s85bx1y8zYsonWvDcPEsj1ro7rvOmj0JBxpxagPgd1w=&c=kRPMgRh3Gl845-hAl4wU6RoWPDfYLJK1N5XjqAEXR6aLH0Y3ITt46g==&ch=_iMAHRTTbRb5tTNfN0FnkTX4pkDesJ2aJQKECiHT0IDiwo3He4z4Jg==>

Copyright 1996-2018 Shared Results International. Published weekly. Do not forward this issue of Tech-Talk.com without prior permission from Shared Results International. Distribution is limited by contract. Forwarding it to unauthorized recipients constitutes copyright infringement.
For information on site licenses or how to include additional recipients, email darci.hanning at state.or.us<mailto:darci.hanning at state.or.us>

Cheers,
Darci Hanning, MLIS
Technology Development Consultant
darci.hanning at state.or.us<mailto:darci.hanning at state.or.us> | 503-378-2527 | www.oregon.gov/osl/ld<http://www.oregon.gov/osl/ld>
Follow us: Facebook<http://fb.me/orlibsupport> | Twitter<https://twitter.com/ORLibSupport> | Tumblr<http://www.statelibraryor.tumblr.com/> | Pinterest<https://pinterest.com/statelibraryor/>
Ask me about the Edge Initiative<http://oregonedge.net/>!
[cid:image005.png at 01D31C27.4B9FEA30]

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://omls.oregon.gov/pipermail/libs-or/attachments/20180703/d3fa6e17/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.png
Type: image/png
Size: 15548 bytes
Desc: image001.png
URL: <https://omls.oregon.gov/pipermail/libs-or/attachments/20180703/d3fa6e17/attachment.png>