Spreadsheet Wisdom: Teaching and Learning with Excel

What I’ve learned from working with the Penn community – students and faculty – is that Excel can be customized to projects across vastly different disciplines. If you’ve been to one of my workshops, you’ve probably heard me say that the best way to deal with Excel is to learn to “think like Excel.” Approaching problems like a computer program can not only help give you new perspective but it can also provide a practical method for troubleshooting Excel’s functionality on your own.

If you can figure out what Excel is missing or misinterpreting that is making your data look funny, you can go in an quickly fix the isolated issue. This takes some initial practice but can be accomplished with a bit of patience and the help of Penn Libraries’ newly updated Excel guide. Here you’ll find sample files and notes to guide you through performing tasks ranging from basic to advanced.

Tips and Tricks

Below is a list of spreadsheet wisdom covering frequently asked questions and a few tips and tricks for optimizing your Excel workflow.

Hidden Commands

• In Excel, commands can often be found in multiple places. If you can’t find the button you’re looking for in the top ribbon, try right clicking to see what Excel suggests.

---

Chart/Graph/PivotTable

To add a graph to your sheet, simply click and drag to select the data, navigate to insert on the top ribbon and choose whether you want a table, chart, or PivotTable.

---

Paste Special

Remember when pasting charts into other Office programs that they remain dynamic using a simple paste, meaning the chart will change if you edit the spreadsheet data. To create a static chart, go to paste special and choose a picture format (JPEG or PNG).

---

Freeze Panes

Freeze panes to allow a header row or column by navigating to view in the top ribbon and selecting freeze panes.

---

Functions

Insert a function by clicking on the fx button beneath the top ribbon. You can select cells by clicking and dragging after choosing the mathematical function you’d like.

---

Autofill

Autofill data by hovering over any corner of a cell until you see a black cross then click and drag to where the cells you’d like to populate.

• Autofilling formulas automatically adjusts the cell references.
• Autofilling number series or days of the week automatically fills in the next member of the set.
  

  ---

Filters

Add filters by selecting the first cell in the column you’d like to sort and then selecting filter from the sort & filter menu on the home ribbon.

---

When using Excel, keep in mind that this program can function as a calculator, chart maker, list creator, and more! If you think there should be a way to perform a task in Excel, there probably is. Be patient while exploring and experimenting with different ways to manipulate the data. Finally, don’t hesitate to ask for assistance from WIC if the problem becomes too time consuming. We can always be reached at wic1@pobox.upenn.edu.

Got Tableau?

Tableau is a professional-level data visualization and analysis software. It’s easy to learn, easy to use, and 10-100x faster than existing solutions. It represents your data table like a spreadsheet. So if you are familiar with Excel, the way Tableau organizes data will feel adaptive. Once you have imported data into Tableau, you can easily create graphs, filter data dynamically and go deeper into your data with built-in statistics functions.

Filter beyond ‘Filter’

Filter – the little funnel shaped icon in Excel, may be one of the most frequently used functions in Excel. Tell Excel one or several conditions and let Excel return those rows that meet your criteria. It works perfectly well in one single column.

However, sometimes you may want to filter one column according to the corresponding value in another column – in such a case, Filter may not be the most convenient way.

Summer in Weigle

This guest post by Amanda Gisonni, a junior in the College studying Psychology, describes her experiences over the summer using various resources in the Weigle Information Commons to improve her technology skills.

If you have ever been in the Weigle Information Commons before, you know it is a great place to work with a group. There are booths, study rooms and free-standing tables, plus talking is always welcome. But did you know it is a technology hub, too? It’s a place where you can get access to the latest gadgets, use top-notch software programs, and take hands-on workshops. Ultimately, you can learn how to use a new program like Excel, Photoshop, iMovie and more, which is exactly what I did this summer.

At the start of the summer I barely knew how to use Photoshop, InDesign, Illustrator or WordPress. Now I can navigate my way through all three Adobe programs, and I even created my own WordPress website. How did I accomplish this? I spent time in Weigle. I took some WICshops, watched Lynda.com tutorials, and experimented with my own projects in some of the software programs.

This is the front and back of a business card I created with Adobe InDesign in the “Making Business Cards with Adobe InDesign” WICshop in July.

Weigle is a great resource for students, but it’s disappointing that not everyone takes advantage of it. Students often get bogged down with school work and claim they simply do not have time. I disagree. I think if students knew how to use the resources available at Penn, they would.

Here is a simple guide to get you started:

• Sign up for a WICshop (aka a Weigle Information Commons workshop). Check them out this September! Try WordPress Basics, Photoshop Layers, Making mini iMovies, and Crafting a better resume with InDesign and more!
• Spend some time in a booth or group study room using the software programs on all of the computers. Experiment with InDesign, Photoshop, Excel and more. Reserve a spot here!
• Don’t have the time to take a WICshop? Reserve a time slot on Lynda.com and learn at your own pace and on your own time! Check out all the videos that Lynda has to offer on the Lynda.com website.
• Lastly, if you have any questions, just walk in! The Weigle Information Commons staff are friendly and eager to help you out! For those who do not know, Weigle is located in Van Pelt Library on the first floor. Enter through the turnstiles and take a left after the elevators, and then continue straight and you are there!

Check out Amanda’s website at www.amandagisonni.wordpress.com or follow her on Twitter @amandagisonni

Remove Duplicate Records in Excel

When you extract a large amount of data from a database, the first thing you may want to do is to clean the data and remove duplicates.

In this blog post, I will introduce you to two easy ways to remove duplicate values and get a list with unique records only in Excel.

Learning how to teach (Excel)

This guest post comes to us from Nicolette Tan, a junior in the College studying political science. She wrote this reflection essay during her participation in MGMT 353 Wharton Field Challenge in fall 2013. WIC staff assisted students in the seminar taught by Arjun Bhaskar and Samaira Sirajee with guidance from Professor Keith Weigelt in learning how to present Excel skills to small business owners in Philadelphia.

It’s one thing to know how to use Excel yourself; it’s another to be able to teach it.  Today’s workshop definitely showed me that teaching is hard, and even more so when you’ve only met these people for the first time. The class got off on a high note, when Grace asked the class to “Raise your hand if you’re excited about learning Excel!” and people cheered and raised their hands enthusiastically. One thing that strikes me every time is the positivity that the students bring to the class, and how eager they are to improve themselves – regardless of age or background, and I have so much respect for that.

Welcome to Allison Snyder

Please welcome Allison Snyder who is staffing the WIC Desk through the summer. Allison comes to us from Binghamton University’s Bartle Library, where she was a collection development intern. She is a SUNY graduate with a background in Business, and has just completed her Masters in Library Science from Simmons College’s  Graduate School of Library and Information Science. Please stop by to say hi and introduce yourself!

Allison will soon join our Program Intern Xuanyao Cindy Jiang in presenting Excel training.

 

The World Cup is coming!

Cheers! The 2014 World Cup is coming this summer! Although I am not a fan of soccer, I know many of you are! So in this post, let me share the official schedule and a useful tip in Excel so that you won’t miss any games!

You can download the 2014 World Cup Final Tournament Schedule here.

There are so many games packed into June! Let us explore Conditional Formatting in Excel so you can quickly find the games happening on each day, like this:

  After applying Conditional Formatting: 

No more legends? Charts in Excel contd.

When creating a chart in Excel, have you ever found that you have so many sections that a legend is more confusing than helpful? It can be so inconvenient to link the legend to your chart. Here are “Before” and “After” examples of what I would suggest:

Before                                                                            After

You can download the example file (Guide Arc.xlsx) here to practice!

Workshop videos: Infographics and Excel

Video recordings for three recent workshops are now available on Youtube:

Infographics Design with Caitlin Shanley

Excel Formulas and Functions with Anu Vedantham

Excel Charts and Graphs with Anu Vedantham

Join us for our next online workshop on Finding Patterns with NVivo with Shimrit Keddem on January 17. Have a suggestion for an online workshop topic? Please let us know!