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.

 

[arkusM]

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

You should mention that if the autofilter is on that the drag'n'fill does not continue a sequence, but will carry down the selected cell's data.
I have run into this quite a few times with some of my co-workers.

Cheers,

Mark

 

[Andrew Fergus]

Seeing as this is Bill Jelen's site, let's not forget that Bill developed a number of free video tutorials - a list of which can be seen here: http://www.mrexcel.com/excel_video_training.html

Or if you (or your colleagues) would rather learn Excel the iPod/mp4 way, then there are also a lot of podcasts from Bill available here: http://www.mrexcel.com/podcast/learnexcelpodcast.html. There is a handy search function to view topics older than the latest 50.

There are many many topics covered in the tutorials and podcasts, with each topic being succinct yet informative. You may want to include a hyperlink to these invaluable resources in your workbook....

Good luck
Andrew

 

[MrKowz]

Wow, thanks a lot for those links, Andrew. I will be sure to check them out when I get home so I can listen to any audio they may have! And yea, video tutorials are always very handy with Excel, especially when it comes to using any kind of wizard!

 

[barry houdini]

Hello Keith,

I see some useful stuff in there but I have to take issue with some of the MATCH functions used on the Data Reference worksheet. Where you have MATCH formulas like in E89

=MATCH("First Name",D91:G91)

D91:G91 contains unsorted text values and you're looking for an exact match so you can't guarantee that it will give the correct result - formula should be

=MATCH("First Name",D91:G91,0)

You can see the problems if you replace "First Name" with "Address". "Address" exists in the range but

=MATCH("Address",D91:G91)

gives a result of #N/A

This is because Excel expects a sorted range, which you don't have.

It's not true to say that the table must be sorted if numerical values are used. In fact the data needs to be sorted ascending if you use 1 as 3rd argument [or omit that argument, which is the same thing], whether data is numeric or not, and needs to be sorted descending if you use -1 as 3rd argument. If you want an exact match then you use a 3rd argument of zero and the range doesn't need to be sorted at all, even if it is numeric.

Excel help is actually quite useful for the MATCH function

 

[MrKowz]

Thanks for the advice, Barry! I am still somewhat a novice with the match function. I know it is a powerful tool, just need to learn more about it.

This change will be in the next release of the tutorial.

 

[Excelorate]

If you are still working on this tutorial could you add range and frame additons to word applications. It would be much appreciated.

 

[MrKowz]

Excelorate,

I have been slowly updating this, however, I do not intend to add anything regarding word applications. I know just about enough in Word to get me to type up reports. I plan to keep this tutorial mainly for beginners in Excel. I may one day do a more advanced tutorial that focuses more on VBA programming when I gain more experience.

Thank you for your interest, however!

 

[likhary]

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