Excel LogoMy co-worker Becky Wiegand already gave you some tips for working more efficiently in Word and PowerPoint, so I've been given the honor of doling out advice for perhaps the most powerful and complex application in the Office suite: Excel. Most people know Excel as Microsoft's spreadsheet application, and even children use the software to create simple tables and charts for classwork. But most users don't realize how much functionality the software includes.

Smaller nonprofits may be familiar with using Excel spreadsheets to keep track of donors and related information. Even though it might be frequently used for this purpose, it's not ideal for the task (as the knowledgeable Robert Weiner explains here), and organizations drowning in Excel workbooks might want to consider a true donor database.

So it's not perfectly suited for managing donors and gifts, but Excel is great for plenty of other tasks. With the launch of Office 2010 last year, Microsoft has made Excel even more powerful and simplified some of its more advanced tools. I'll go over some of these capabilities as well as shortcuts and tips that will allow you to save time and better analyze and display your organization's data. You can also share your own Excel tips and secrets in the forums.

1. Inserting Sparklines

Excel 2010 allows you to create small charts in a single cell next to your data. This provides a simple visualization to highlight trends in data.

You can choose from line, bar, or win/loss graphs and format them as you choose. Sparklines are simple to insert into any cell, and Excel will just ask you for the range of data covered by the Sparkline. Check out this tutorial from Microsoft if you're not sure how to get started.

2. PivotTables and PowerPivoting

Most Excel users have heard of PivotTable reports, but it's probably a safe bet that most have never tried to create one. PivotTables are an extremely useful way to analyze data. In just a few seconds, you get subtotals for hundreds of figures and easily view results by month, quarter, or any span of your choosing.

In Excel 2010, PivotTables are even easier to use than in previous versions. Excel makes some guesses at how you want your data compared that further automate PivotTable creation. See this quick-start guide from Microsoft for the full how-to.

And for the professional Excel users out there, Microsoft has created a free add-in called PowerPivot for Excel 2010. PowerPivot ratchets up the capabilities of PivotTables and PivotCharts, allowing you to process huge amounts of data and turning Excel into a business intelligence tool. The add-in has its own website with lots of tutorials and neat-o graphics. It's possible most smaller nonprofits won't have much use for these features (I probably won't use them, other than to play around), but maybe some of the larger organizations will.

3. Printing Selected Areas of a Worksheet

If you frequently print just a select portion of a worksheet (or wish you knew how), you can use Excel's Set Print Area function to define a print area. Afterward, you can add cells to an existing print area or clear a print area easily using buttons on the Ribbon. See this Microsoft how-to for the specifics.

4. Excel Web App

Like Word and PowerPoint, Excel has an online Office Web App version as well. The Excel Web App allows you to view, edit, and collaborate on an Excel workbook via your web browser. You can see who else might be editing the document at the same time right inside the web app.

The Excel Web App has its own set of keyboard shortcuts that are similar to the keyboard shortcuts in the full version of Excel. Which brings me to the final Excel tip, in fact.

5. Keyboard Shortcuts

The full list of keyboard shortcuts for Excel is quite lengthy, but it contains lots of really useful commands. Some of my picks:

  • F2: This is a simple but useful one. When you click on a cell that contains data in an Excel spreadsheet, by default you're going to overwrite that data if you type anything. If you press F2, the insertion point automatically moves to the end of the cell's contents, allowing you to add to the data in a cell rather than overwriting it.
  • Ctrl + `: That's control plus the accent mark that shares keys with the tilde. This shortcut toggles the display between showing cell values and formulas. Really handy for reviewing all cells with formulas at once.
  • F6: This doesn't work in versions of Excel without the Ribbon interface, but it switches between an Excel worksheet, the ribbon, the task pane, and zoom controls, allowing you to fly around the interface without a mouse. Hardcore.

Additional Resources

Microsoft has created some pretty great Excel tutorials for each version of the software. They even include guided exercises that allow you to test your learning. The time to finally learn PivotTables has arrived.

Want to learn more about the Microsoft software donation program or other resources available to nonprofits? Check out their site for nonprofits to see all of their resources to help nonprofits use technology. There are also some great training and user readiness materials available from Microsoft at the following sites:

Let us know how you use Excel by sharing your favorite tips, shortcuts, and features in our forum discussion.

Carlos Bergfeld
Product Content Manager, TechSoup Global