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.

The Power of the Automator

Have you ever been in a situation where you’ve received, say,  30 separate PowerPoint slides from 30 individuals and needed to combine them into one polished PowerPoint presentation? This often happens for lightning rounds or PechaKucha presentations, where multiple people are contributing to a talk (usually in a very fast-paced manner).

Another common use for combining PowerPoint slides is in the classroom, with student-created slides that a professor wants to merge into one presentation for demonstration or teaching purposes. In one of my recent PowerPoint workshops, I received exactly this question from an instructor: How do I take 30 disparate slides, arrange them how I’d like, and quickly insert them into a single PowerPoint presentation?

WIC Tech Tips

Recently in WIC we’ve started thinking about fresh ways to spread information to users, so when we saw Johns Hopkins’ clever Innovative Instructor series, we thought we’d try something similar! Thus introduction: WIC Tech Tips, where we’ll give an introduction to topics about which we’re frequently approached. Our first issue is on making and using screen videos. We’ll be putting one out each semester, so make sure to check back for next semester’s topic soon!

You can download “Screen Videos” here!