# Free Excel Tutorial I am building for Beginners



## MrKowz (Aug 8, 2008)

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 (Aug 8, 2008)

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 (Aug 8, 2008)

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 (Aug 14, 2008)

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 (Aug 14, 2008)

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 (Aug 14, 2008)

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 (Aug 15, 2008)

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 (Aug 15, 2008)

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 (Aug 15, 2008)

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 (Aug 15, 2008)

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.


----------



## MrKowz (Aug 8, 2008)

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 (Aug 15, 2008)

gauntletxg said:


> - 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 (Aug 17, 2008)

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 (Aug 18, 2008)

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 (Aug 18, 2008)

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 (Aug 18, 2008)

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 (Oct 21, 2008)

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


----------



## MrKowz (Oct 21, 2008)

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 (Oct 22, 2008)

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.


----------

