Excel tips & tricks

May 27, 2008 8:41 AM

I signed up to give a 90-minute Powerpoint presentation on Microsoft Excel 2007. It's open-ended as to the specific content, and I am stuck.

The target audience is a group that uses Excel almost every day, so I've resigned myself to the fact that most people will already be familiar with most of the course content. But I'm hoping to give a presentation that offers at least one new thing for each attendee. Here's the list of things I've come up with to cover:

Auto filter

Advanced filters

Conditional formatting

Text-to-columns

Absolute vs. relative cell references

White backgrounds, and other tips to make spreadsheets look pretty

Vlookup

An overview of macros

This doesn't seem like it is enough to fill 90 minutes. The audience doesn't work with numbers much, but rather uses Excel to document and present information, so financial and mathematical or statistical functions aren't going to be too useful. What else should I include? Also, how can I make this seem like a coherent course, and not just a list of tips and tricks?

posted by Dec One to computers & internet (15 comments total)

Is there any way you can do some kind of pre-survey of those who will be attending to find out what they'd like to learn?
posted by All.star at 9:00 AM on May 27

Pivot Tables.
posted by mkultra at 9:06 AM on May 27

Also, plan on 1/3 to 1/2 of your time being taken up by Q&A.
posted by mkultra at 9:07 AM on May 27

How about covering some useful online resources.

Useful keyboard shortcuts.

Importing data from other source e.g. CSV files.

If you can use case studies that use a lot of the things you're teaching, that would help to tie the different points together.
posted by Gomez_in_the_South at 9:25 AM on May 27

Seconding pivot tables (something about which I need to learn).

I think a great way to fill up five minutes would be a listing of what not to use Excel for.
posted by adipocere at 9:43 AM on May 27

Converting ranges to tables.

Exchanging data with Access and text file formats.

What's new/different from 2003 to 2007.

Using more than one sheet in a workbook (including referencing cells on other sheets).

Mail merge.

Charts.

What-If and Goal Seek analysis (maybe too much toward financial analysis).

Interaction with other Office products, eg. Word and PowerPoint (in addition to exchanging data with Access as mentioned above).

I've got an Office 2007 textbook sitting right here next to me, so let me know if I can send you the chapter headings, objectives, or the actual PowerPoint presentations. (I don't use the slides when I teach - because they suck - but you might have the patience to do something with them.) I've got example and homework exercises and their solution files too.
posted by SuperSquirrel at 9:46 AM on May 27

Some keyboard shortcuts,
ctrl-!, (ctrl-shift-1), general formatting with two decimals
ctrl-@, (ctrl-shift-2), format for time
ctrl-#, (ctrl-shift-3), apply date formatting
ctrl-$, (ctrl-shift-4), currency format
ctrl-%, (ctrl-shift-5), apply percentage formatting
ctrl-^, (ctrl-shift-6), scientific formatting
ctrl-&, (ctrl-shift-7), applies a single border
ctrl-*, (ctrl-shift-8), select a contiguous range of cells

ctrl-1, format cells dialog box
ctrl-`, toggles to formula auditing and back
posted by geekyguy at 9:52 AM on May 27

Seconding pivot tables -- with good real world examples of how/when to use them...

I don't know why, but people seem to crap their pants when they discover how easy these are to do..
posted by twiggy at 10:46 AM on May 27

All.star has a great idea--try and assess your audience and their needs ahead of time. Hopefully that's possible.

Regardless, I think you'd be well served if you can bucket the various features you want to show off into logical/memorable/useful categories. In my experience, if you structure the presentation around features people will get glazed-eyed and might tune out. So rather than say "pivot tables" maybe have a section on "Analyzing data" in which you might talk about pivot tables and also using formulas, etc. The core point, is to take that 90min and chop it into somewhat self-contained sections (maybe 3? 4?), each with a theme, concrete examples of what/why/when you'd need to do X & Y, and maybe a couple of goals for the section.

Also, covering chart building--or rather "Presenting your data"--is an important thing to cover, I'd say.

Also useful: Examples of good v. bad uses of Excel.
posted by donovan at 10:47 AM on May 27

1) Maxims on how to design spreadsheets and models so they're transparent to others and/or interpretable later, e.g., never bury assumptions in equations, use unit labels, etc.

2) How to export tables, graphs etc. into Word and Powerpoint without tears (e.g., using a metafile format, removing gridlines, etc.

3) How to use Excel to make process diagrams and other graphics.
posted by carmicha at 11:16 AM on May 27

Have these people used the Office 2007 Ribbon before? You could easily spend 20 minutes on that single topic.

Make sure to send your users to this Excel 2003 to 2007 command finder. Also, the Office 2007 Search Commands utility is indispensable for new 2007 users.

Nthing the Charts recommendation that's been made repeatedly here. You should also check out what Microsoft has to say about Excel 2007's new features.
posted by cnc at 12:55 PM on May 27

90 minutes? That is a long time to listen to one person. I would break things up in the middle with a short piece of group work -- for instance, people form groups of 4 or 5 sitting together and swap their top excel tips, with their two favorites fed back to the entire audience at the end of this segment. Or, if you are brave enough, get them to choose their top questions -- this would be very helpful if you haven't had much chance to assess their actual level of skill.
posted by Idcoytco at 2:11 PM on May 27

Ditto on basics of formulas.
posted by hazyspring at 2:16 PM on May 27

Group the content so that it has a logical flow. If you've got an agenda that people understand you'll look better prepared. Also, it helps people know when to ask a question. That way they don't ask a question that you plan to address later.

You might go with the following topics -

Working with Data - pivot tables, filters, vlookup
Presentation - backgrounds, conditional formating
Faster easier - shortcuts, macros, exporting to Work, ppt.
posted by 26.2 at 10:13 PM on May 27

The one thing I didn't know about Excel that absolutely made my year is the Arrange All, and Save Workspace features. These allow you to instantaneously arrange your open Excel files into a layout conducive to analysis and then save the layouts for later use.I can't believe I ever attempted data analysis without knowing about those features. In 2007 these features are located in the View tab.
posted by lyam at 7:19 AM on May 28