Excel Tutorials

The first part of my new Excel 2007 Basics Tutorial is now online. I am interested in feedback because it is not a traditional online tutorial. I'm experimenting with using links to the online help system to serve as pointers to reference material. Like most tutorials, the principle is that you learn by doing, and part of that learning is getting familiar with where to go for help. The idea is to describe a task to perform and reference the documentation in the help that explains how to do it. Hopefully, the side benefit is that if you read the documentation in the help, you will learn much more than if you just followed a detailed step-by-step tutorial.

You will need to have the Beta installed on your computer for the links to the online help to work.

Have you ever distributed a workbook to many clients, only to find out that they are not enabling macros? Well in the linked video, I show you a relatively simple way to force your users to enable macros with only four lines of code.

http://www.datapigtechnologies.com/flashfiles/forcemacros.html

I put this little utility together so that I could split my Excel datasets into separate Excel workbooks or separate tabs without cutting and pasting. For example, with this add-in, you can create a separate workbook for each Employee in your dataset or each City, or State...etc.

Feel Free to Download this add-in and share it with others

http://www.datapigtechnologies.com/dpeeinstall.htm

I read a blog a few days ago that hinted that Excel 12 will increase the number of undo actions to 100. That means that you will be able to undo 100 actions. In current versions of Excel the "undo stack" is limited to 16, so you can see this will be a big improvement.

But for those of you who can't wait, I made a tutorial to show you how to increase the number of undo actions in Excel by tweaking the registry. With this trick, you can increase the number of undo actions up to 100 actions.

http://www.datapigtechnologies.com/flashfiles/increaseundoactions.html

Timelines have been created in Excel using drawing tools and stacked bar charts. This article, "How to Create a Timeline in Excel", demonstrates a new approach: using an XY chart, with error bars as leader lines. You can either create a timeline from scratch by following the tutorial, or download the ready-made Excel Timeline Template.

Some Excel users have the habit of giving their column headings the dual roles of label and data. For example, they'll call their columns January, February, March, etc. Unfortunaely, this causes a whole host of problems when trying to analyze the data. The video linked below will show you how to easily transpose these datasets to convert these month names into a single column called month.

http://www.datapigtechnologies.com/flashfiles/Transposetables.html

Here is a fun spreadsheet I created that does a few magic tricks. Can you figure out how they work?

www.datapigtechnologies.com/downloads/magicspreadsheet.xls

You may of heard of dynamic ranges and even seen some in action. But do you know how they really work? This video will finally explain dynamic ranges.

http://www.datapigtechnologies.com/flashfiles/dynamicranges.html

As of Excel 2003, there is no inherent functionality that allows you to sort by color. However, with an easy custom function, you can gain this functionality without breaking a sweat.
Click on this link to see how: http://www.datapigtechnologies.com/flashfiles/SortonColor.html

This functionality is, in my opinion, underestimated and underutilized.
Easy to set up and maintain, data validation can guide your users through data inputs, help avoid common errors, and keep your data clean.
Watch the Video below to learn about Data Validation with Excel.

http://www.datapigtechnologies.com/flashfiles/datavalidation.html

Sending emails with Excel is easier than you think. Check out this tutorial that shows you how to use the SendMail method of Excel to create email functionality in your Excel spreadsheets.

http://www.datapigtechnologies.com/flashfiles/excelsendemail.html