If a workbook contains many sheets you can create a table of contents to make navigating to the sheets easier. This is a fantastic idea when producing a final version of a report in Excel for a customer.

Excel does not yet contain a feature that produces a table of contents, but you can create a macro to get the job done.

This macro will create a new sheet at the start of the workbook named table of contents. If one already exists it will remove it. It will then list the names of all the sheets in the workbook and insert a hyperlink for each one.

Some of the techniques used within this macro include;

One of the main reasons to create a macro in Excel is to make a repetitive task easier and quicker. Such a task may be to loop through all files in a folder and perform an action on each one.

The code below will display the folder picker dialog box to allow the user to specify the folder (directory) that they want to use. It will then loop through all the files within that folder. It will open the workbook, perform an action and then close it saving the changes made.

Comments have been used to explain different areas of the code.

Worksheet protection is used in Excel to protect cells containing formulas, hide sensitive data and much more.

Unfortunately one of the problems that arises from protecting a worksheet is the inability to spell check a worksheet.

To be able to spell check a protected worksheet you need a macro to unprotect the sheet, perform the spell check, and then protect the sheet again.

Blank rows in your Excel spreadsheet are undesirable and can make it difficult to work with. Because of this, Excel provides many ways that we can locate and then delete the blank rows.

These techniques can include sorting the list to group the blank rows together, filtering for blanks and using Go To Special to locate blank cells for deleting.

However these can be time consuming and may not be the best approach for you. For example, sorting the list will change the order of the list. You may not want this.

By creating a macro to delete the blank rows of a spreadsheet this process will be effortless, and can also be performed by anyone when the macro is made available with a button. In this Excel Macros tutorial we show you how.

To create the delete blank rows in a spreadsheet macro;

A splash screen is a screen which loads when you open a program. A splash screen can be used to display a company logo, advertise and provide information to the user.

To create a splash screen in Excel, you need to create a userform and then use some simple VBA to show the userform when opening a workbook, and make it disappear after a certain amount of time.