You're off to a great start! I'm really looking forward to the end product. It's taken me a long time to learn about what you've listed here.

I've offered to host similar training for my coworkers, but management doesn't seem to want to make the time available. Oh well, I made the offer... but I digress. I also struggled with encapsulating all of this. I did teach a few classes on pivot tables a couple of years ago.

It was just a couple of weeks ago that my manager and I were discussing the development of an Excel knowledge test we could use in the screening of candidates for general accounting and analyst roles, so I was pleased to read your post.

Oh, and don't forget about HLOOKUP and MATCH. Perhaps you could have a section with some tips on automating the whole data collection and final presentation via VBA, if time allows.

Thanks again for this great summary of "need-to-knows"!

This is terrific and I look forward to the final product!

Though you probably have this built into a number of the topics, one thing I didn't see in the list is approaches for building error-checking into complicated spreadsheets (or even not-so-complicated ones).

You might want to reference https://www.cpearson.com as he posts great references and examples of advanced uses of excel. One that I would suggest adding are date based functions and calculations. Especially if companies are looking to manipulate and analyze data for a business that is very seasonal.

Chris:
Great iniative, and I wd be pleased to help wherever I can. Here is a quick brain dump of what's on my wish list.

There is one single subject which is barely scratched in books, and that's PLAN YOUR WORKSHEET in advance. How to design yr worksheet. Making separate cells for variables as well as constants (and name them, for easier formula reading than these cell address references).

Second, I miss data entry tricks which is essential if others are going to use the spreadsheet. create a look-up table (dropdown list in a cell), cell locking, hidden sheets. Make entry cells appear differently (e.g. by using a yellow fill pattern for the cell). Also, autofill a range with the fill handle.

Third, make a template with all formatting you need. Not so important as in Word, but still ...

Fourth suggestion: how to add time-saving buttons to the menu (saving you mouse clicks).

Fifth: copy a color scheme from another workbook - great for companies that want to use a distinctive style.

Sixth: get rid of this partial menu drop down ("selecting Always show full menu"). Why anyone wants to show only the last used commands is completely beyond me... It tool me 15 minutes to find how to get rid of it (Excel has it by default ON).

Seventh: more short-cut keys, like:
- ALT+Enter to force a line break in a cell (also works in charts)
- SHIFT + mouse drag to move columns/rows without overwriting
- CTR+mouse wheel to zoom in/out
- SHIFT+CTRL+END to select a data row/column (data only)

Eighth: Freeze column and row labels.

I will probably pop up with more ideas .... But maybe our fellow readers can give a clue what/what not. We don't want a book with hundreds of pages.

To get a ref guide quickly, I suggest you distribute some tasks to any volunteers, you doing the final editing. Let me know what you want to drop on my head, and I will do my best to deliver.

I can\'t wait to have the time (sounds like an oxymoron, no?) to go through your stuff. In the meantime, I would like to add my most-used functionality when I have workbooks with many (many) pages, Formula Auditing. Especially nice because it is so intuitive, just have to know that it is there.

This is a lot to get through in just 3 hours, but I think you need a little more.

In my past experience, pure technical training misses a key element, which is that data analysis has to be correct, and provably correct. My prime directive is therefore to make the workbook easy to check.

So I would include the need to document anything that could be unclear, hard to find, or possibly confusing, eg
- say whether it is annual or monthly, don't make them guess)
- include links to, or screencaps of, documents that support key assumptions or business logic

Oh, and no hardcoding of numbers in formulae...

And, above all, DON'T BE CLEVER. Keeping it simple is the mark of an expert.

regards

Dermot

Juice,

This post is awesome, keep up the good work. There's just a few quick things I recommend adding which I don't see above (maybe I missed them).
- Filling down by highlighting a range and clicking the "jughandle" (the bottom right corner of the bottom cell in the range, there's probably a better technical term for this).
- Modifying the default toolbars to add icons for things like "paste values" and "freeze panes"
- Web queries to grab data from a website
- Formatting printouts

I can't tell you how many self proclaimed Excel "guru's" I've seen that don't know how to do these things well. These same people are the ones who's eyes pop out of their heads when they see the real guru's take the keyboard and make the magic happen.

Again keep up the good work and if you decide to "divide and conquere" just let me know what I can do to help.
Rich Murnane
http://www.sqlquery.com

My previous post was a crude brain dump just to trigger more ideas from other readers. We don't need to be complete: there are zillions of books available. The overriding factor shd be the usefulness of the hints/tips/tricks with respect to data analysis / visualization.

I wrote earlier this year an internal guide how to use Word. I bring this up because I chose a format that people found attractive to use. Consistently three parts: 1. What is it? 2. Why you need it? 3. How to do it? All in all a concise 70 pages with focus on what you need for writing reports.
BTW, one of the subjects in this Mastering Microsoft Word is dynamic linking to Excel sheets/charts: if the underlying Excel sheet changes, the word file automatically updates. If people see this possibility for the first time their life changes forever: it's not only a huge time-saver but also ensures consistency throughout the report.
Is this an interesting feature for including in your core knowledge base?

Thanks for the openness. You guys are sharp, and I enjoy reading what you write.

I'm looking forward to seeing the lessons you put together. I have been interested in returning to my alma mater to teach some intermediate and advanced Excel tricks to the business students (not for cash, just for kicks). Your materials would likely make a great starting place.

Perhaps you should publish the lessons under a creative commons license.

Great site. I refer to it daily. One thing I belive would be very useful is trending and forecasting. For those of us in the financial industry it would be great to learn what excel can do for us without having to thru the pain of reading excel help menus. keep up the good work!

I would add using recorded macros (with relative references turned on) to the 'Moving and manipulating data' category. Sometimes forcing data into a usable table format requires a particular series of actions repeated several times on a set of data. Recording a recorded macro can greatly reduce the pain.

Can I come please? It never ceases to amaze and delight me that there's always more to learn.

Given that you're covering so much in such a short time, my only suggestion would be that particularly in the keyboard shortcuts to simply have the descriptions in the notes (preferably collected into functional groups) and simply refer participants there. Saves the hassles of varying levels of competence in your audience (and I'm certain everyone will pick something useful up).

Seeing Excel charting horrors @ various companies I have worked at, I would suggest a one pager primer on what charts to use for what type of data coparison etc. How to keep them clean and how to save user-defined versions.

I second Joe's motion regarding the one page primer on what charts to use for what type of data comparison! I have been searching unsuccessfully for this very item. It would be great to find it on this website!

Joe, Linda:
Do you mean a simple table with something like:
[Type of Chart] - [Typical Use] - [Remarks / Example]?
It can be swiftly derived from Stephen Few's "Show Me The Numbers".

I could not see anything about putting $s in formulae for absolute vs relative cell references, and using the F4 key in edit mode to quickly change cells & ranges, this is something which I find is not always understood.
Very useful list though, but that is the trouble / fasination of excel, there is always something more to learn.
You mention interviews here I think, and trying to ascertain the level of excel knowledge a candidate has. Has anyone else interviewed someone who claims to know 'everything about excel' I have and this was a 2nd year university student, and he did not get the job!

Hi

Any news on when the training spreadsheets will be available? They sound like they would be really useful.

Thanks
Helen

Any idea when the spreadsheet will be released? I can use it shore up my own skills and understand what things I can teach to my co-analysts and admins.

[...] Click here to download our much-delayed Excel training document. It is chock-full of tips, tricks, and exercises to sharpen your Excel skills.The training covers many of the areas discussed in our post on “Essential Excel Skills.” Here’s the outline: [...]

[...] a training page for “essential Excel skills” [...]