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

Resize 01---Chart
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

Resize 02--PasteSpecial
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

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

Functions

Resize 04---Fx
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

05 - 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

Resize 06---Filter
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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s