Free Excel Tutorial I am building for Beginners

[MrKowz]

Hey all,

I am progressively working on an Excel Tutorial that could be very useful for beginning users of Excel and possibly for some early-intermediate users. The original reason for me creating this file was so I could hold a few meetings with my department to teach them some of the basics of Excel so they don't have to call me over every time they need to do a vlookup!

Let me know what you think so far, and I hope that some people are able to find this useful!

Download link - Updated 8/14/2008:
http://www.box.net/shared/xpe76u452h

Macros must be on for the file to work properly, as it uses a UDF to copy formula text into adjacent boxes.

As of 08/08/08, the file contains some examples in the following topics:

• Keyboard Shortcuts
• Terminology
• Math Operations
• Data Organization
• Data Reference
• Summing & Counting
• Logic
• String Operations
• Date Operations

Also, if there are any requests for topics for me to add to this, please post your request here. Currently, I am working on developing a PivotTable tutorial.

Thanks, and enjoy!

News:
Updated 8/14/2008 with suggestions from Schielrn as well as a short tutorial on Pivot Tables.

 

[schielrn]

Unfortunately it looks like that file share site is blacked at my work. Could you e-mail me a copy at %&Raven%&Wlfpc@%&yahoo%&.com

Just remove the %&.

Thanks.

 

[schielrn]

The layout is very nice. Some things that I noted that might be useful:

Is more of the keyboard shortcuts. People always love Ctrl+Page Up/Down for being able to switch between sheets.

For out adding $ signs under terminology, you may want to add that it is called an absolute reference as that is the teminology for it.

One other topic you may want to touch upon is CSE (Array) formulas.

Also you mention Vlookup, but that can only look right, so you may want to do something using index/match to look left.

It looks great and I have always wanted to create something like this, but never got around to it. The furthest I got was a sheet of some basic formulas which I can send you if you'd like.

Thanks for sending this to me. If I come across anything else I will let you know.

 

[MrKowz]

The tutorial has been updated!

It now contains some of the suggestions above (thank you for the suggestions, Schielrn!) as well as a very short tutorial on Pivot Tables!

New download link:

http://www.box.net/shared/xpe76u452h

(If an administrator is able to edit my original post, please PM me and I will reply with what I want the body of it to look like, as this is going to be an ongoing project/post)

 

[gauntletxg]

Nice work thus far. Would you mind if I shared this with some co-workers of mine?

A few suggestions I have after looking through it briefly:

- For Vlookup, mention that your data must be sorted if you use the TRUE argument. An example of using this argument would be nice too. I have a basic one floating around somewhere where Vlookup is used to return a tax bracket based on someone's income. I can send it to you if you'd like to piggyback off of it.

- For "drag the box", I think a screenshot of what you need to click would be helpful. I've tried to show people how to do that, and some just do fill>down or do copy>paste because they forget what they need to click.

- For If statements, mention that you can only nest 7 of them in versions prior to 2007.

- For dates, a favorite trick of mine is using MONTH(1&Cell) where cell contains just a month name to return the month's number. Not really sure if this belongs in a beginners tutorial, but I'll throw it out there.

- A favorite keyboard shortcut of mine, Ctrl+Shift+End

 

[MrKowz]

Feel free to share it. Just make sure that your coworkers know where to find updates if needed. Also, I will update the file with your suggestions (also in the next update, I am going to make a contributions page, so anyone who contributes ideas will have their name listed in the contributions. )

 

[gauntletxg]

Cool, thanks. A few more:

- Conditional formatting

- Update the summing/counting tab to include AVERAGE/AVERAGEA. You can also mention functions that are available in just 2007 (AVERAGEIF, SUMIFS, etc.)

- Drag and fill can be expanded a bit to explain the logic behind it (i.e. selecting a cell with 1 and dragging vs. selecting 2 cells, one with a 1 in it and the other with 2 in it and dragging)

- Update some of the instructions so they apply to both 2003 and 2007

- This is just personal preference, but I don't think you should mention array formulas in the shortcut list. It's a pretty advanced subject, and I think it would just confuse beginners, especially when there's no corresponding tab on the topic.


Also, as for the updates, maybe a check for updates button

ActiveWorkbook.FollowHyperlink Address:="http://www.mrexcel.com/forum/showthread.php?t=335177", NewWindow:=True

If you want I can provide hosting so the code will link to a static address, that way the user won't have to navigate through this thread.

 

[MrKowz]

I don't think I will put any Excel 2007 things in here, as I only have access to copies of 2003, and that is the version I am most familiar with. I will see what I can update over the course of the next week or so and upload it.

 

[gauntletxg]

After your next update, I can update your instructions that work only with 2003 with information on how to do it in 2007 (mainly navigating around the ribbon) if you'd like.

 

[starl]

Keith - report your post. In the field for what you are reporting

1. Mention my name that i've told you to do this
2. List what you would like changed in your original post.