How to Generate A List of All The Sheets In A Excel Workbook

Start with a workbook that has lots of worksheets or chart sheets. Then follow these steps to create a list of the sheet names:

1. Insert a new worksheet to hold the list of sheet names.
2. Choose Formulas➜Define Name to display the New Name dialog box.
3. Type SheetList in the Name field.
4. Enter the following formula in the Refers To field :

     =REPLACE(GET.WORKBOOK(1),1,FIND(“]”,GET.WORKBOOK(1)),””)

5. Click OK to close the New Name dialog box.

Note that this formula uses the GET.WORKBOOK function — which is not a normal worksheet function. Rather, it’s an old XLM-style macro function intended for use on a special macro sheet. Using an argument of 1 returns an array of sheet names, and each name is preceded by the workbook name. The REPLACE and FIND functions remove the workbook name from the sheet names. To generate the sheet names, enter this formula in cell A1, and then copy it down the column:

=INDEX(SheetList,ROW())

Figure above shows this formula in the range A1:A10. The workbook has seven sheets, so the formula returns a #REF! error when it attempts to display a nonexistent sheet name. To eliminate this error, modify the formula as follows:

=IFERROR(INDEX(SheetList,ROW()),””)

The list of sheet names will adjust if you add sheets, delete sheets, or rename sheets — but the adjustment doesn’t happen automatically. To force the formulas to update, press Ctrl+Alt+F9. If you want the sheet names to adjust automatically when the workbook is calculated, edit the named for mula to make it “volatile.”

=REPLACE(GET.WORKBOOK(1),1,FIND(“]”,GET.WORKBOOK(1)),””)&T(NOW())

What good is a list of sheet names? Figure above shows a table of contents created by using the HYPERLINK function. The formula in cell B1 is

=HYPERLINK(“#”&A1&”!A1”,”Go to sheet”)

Clicking a hyperlink activates the worksheet and selects cell A1. Unfortunately, Excel doesn’t support hyperlinking to a chart sheet, so you get an error if a hyperlink points to a chart sheet.

Note : If you use this technique, you must save the workbook as a macro-enabled file (with an .xlsm or .xls extension).

Read More

How to Retrieve A List of Filenames in A Folder and Display Them in A Excel Worksheet

This technique uses an Excel 4 XLM macro function in a named formula. It’s useful because it’s a relatively simple way of getting a list of filenames into a worksheet — something that normally requires a complex VBA macro. Start with a new workbook and then follow these steps to create a named formula:

1. Choose Formulas➜Define Name to display the New Name dialog box.

2. Type FileList in the Name field.

3. Enter the following formula in the Refers To field :

     =FILES(Sheet1!$A$1)

4. Click OK to close the New Name dialog box.

Note that the FILES function is not a normal worksheet function. Rather, it’s an old XLM style macro function that is intended to be used on a special macro sheet. This function takes one argument (a directory path and a file specification) and returns an array of filenames in that directory that match the file specification. A normal worksheet formula cannot use these old XLM functions, but named formulas can. After defining the named formula, enter a directory path and file specification into cell A1. For example:

E:\Backup\Excel\*.xl*

Then this formula displays the first file found:

=INDEX(FileList, 1)

If you change the second argument to 2, it displays the second file found, and so on.

Figure above shows an example. The path and file specification is in cell A1. Cell A2 contains this formula, copied down the column:

=INDEX(FileList,ROW()-1)

The ROW function, as used here, generates a series of consecutive integers: 1, 2, 3, and so on. These integers are used as the second argument for the INDEX function. Note that cell A21 (and cells below it) displays an error. That’s because the directory has only 19 files, and the formula is attempting to display files that don’t exist. When you change the directory or filespec in cell A1, the formulas update to display the new filenames.

Note : If you use this technique, you must save the workbook as a macro-enabled file (with an .xlsm or .xls extension).

Read More

How to Forcing a Protected View Excel File to Open in Normal View

If you download a workbook and you’re absolutely certain that it’s safe, you can force it to open in Normal view. Here is the trick :

1. Right-click the workbook name (or icon) and choose Properties from the shortcut menu. The Properties dialog box appears.
2. Click the General tab.
   
3.  Click the Unblock button.
4.  Click OK to close the Properties dialog box.

After performing these steps, the workbook will open in Excel in Normal view (not Protected View).

Read More

How to Customize the Ribbon in MS Excel

You customize the Ribbon in the Customize Ribbon tab of the Excel Options dialog box . The quickest way to display this dialog box is to right-click anywhere in the Ribbon and choose Customize the Ribbon.

Customizing the Ribbon is very similar to customizing the Quick Access toolbar, the only difference is that you need to decide where to put the command within the Ribbon. Here’s the general procedure:

1. Right-click any part of the Ribbon, and choose Customize the Ribbon. Excel displays the Customize Ribbon tab of the Excel Options dialog box.
2. Use the drop-down list on the left (labeled Choose Command From) to display various groups of commands.
3. Locate the command you want in the list box on the left and select it.
4. Use the drop-down list on the right (labeled Customize the Ribbon) to choose a group of tabs. Main Tabs refers to the tabs that are always visible; Tool Tabs refers to the context tabs that appear when a particular object is selected.
5. In the list box on the right, select the tab and the group where you want to put the command. You must click the “plus sign” controls to expand the hierarchical lists. Remember that you cannot add commands to built-in groups, so you may need to use the New Tab or New Group buttons to add a tab or group.
6. Click the Add button to add the selected command from the left to the group on the right.

When you are finished making your Ribbon changes, click OK to close the Excel Options dialog box. New tabs and groups are given generic names, so you’ll probably want to give them more meaningful names. Use the Rename button to rename the selected tab or group. You can also rename built-in tabs and groups. Although you cannot remove a built-in tab, you can hide the tab by unchecking the check box next to its name.

Figure above shows a part of a customized Ribbon. In this case, I added a group to the View tab. The new Text To Speech group has five commands. I inserted this new group between the Zoom and the Window groups.

Read More

How to Customizing the Quick Access Toolbar in MS Excel

If you find that you continually need to switch Ribbon tabs because a frequently used command never seems to be on the Ribbon that’s displayed, this tip is for you. The Quick Access toolbar is always visible, regardless of which Ribbon tab is selected. After you customize the Quick Access toolbar, your frequently used commands will always be one click away.

By default, the Quick Access toolbar is located on the left side of the Excel title bar, and it includes three tools:

1. Save : Saves the active workbook.
2. Undo : Reverses the effect of the last action.
3. Redo : Reverses the effect of the last undo.

Commands on the Quick Access toolbar always appear as small icons, with no text. When you hover your mouse pointer over an icon, you see the name of the command and a brief description. As far as I can tell, the number of icons that you can add to your Quick Access toolbar is limitless. But regardless of the number of icons, the Quick Access toolbar always displays a single line of icons. If the number of icons exceeds the Excel window width, it displays an additional icon at the end: More Controls. Click the More Controls icon, and the hidden Quick Access toolbar icons appear in a pop up window. You can add a new command to the Quick Access toolbar in three ways:

1. Click the Quick Access toolbar drop-down control, which displays a down-pointing arrow and is located on the right side of the Quick Access toolbar. The list contains several commonly used commands. Select a command from the list, and Excel adds it to your Quick Access toolbar.
2. Right-click any control on the Ribbon and choose Add to Quick Access Toolbar. The control is added to your Quick Access toolbar, positioned after the last control.
3. Use the Quick Access Toolbar tab of the Excel Options dialog box. A quick way to access this dialog box is to right-click any Quick Access toolbar or Ribbon control and choose Customize Quick Access Toolbar.

The Quick Access Toolbar tab of the Excel Options dialog box. The left side of the dialog box displays a list of Excel commands, and the right side shows the commands that are now on the Quick Access toolbar. Above the command list on the left is a drop-down control that lets you filter the list. Select an item from the drop-down list, and the list displays only the commands for that item.

Some of the items in the drop-down list are described here:

1. Popular Commands: Displays commands that Excel users commonly use.
2. Commands Not in the Ribbon: Displays a list of commands that you cannot access from the Ribbon.
3. All Commands: Displays a complete list of Excel commands.
4. Macros: Displays a list of all available macros.
5. File Tab: Displays the commands available in the back stage window.
6. Home Tab: Displays all commands that are available when the Home tab is active.

In addition, the drop-down list contains an item for every other tab. Sometimes, you need to do some guessing to find a particular command. For example, if you want to  add the command that displays the Excel Options dialog box, you can find it listed as Options, not Excel Options. To add an item to your Quick Access toolbar, select it from the list on the left and click Add. If you add a macro to your Quick Access toolbar, you can click the Modify button to change the text and choose a different icon for the macro. Notice the drop-down control above the list on the right. This lets you create a Quick Access toolbar that’s specific to a particular workbook, which is most useful when you add workbook-specific macro commands to the Quick Access toolbar. Most of the time, you’ll use the setting labeled For All Documents (Default). The only time you ever need to use the Quick Access Toolbar tab of the Excel Options dialog box is when you want to add a command that’s not on the Ribbon or add a command that executes a macro. In all other situations, it’s much easier to locate the command on the Ribbon, right-click the command, and choose Add to Quick Access Toolbar. Only you can decide which commands to put on your Quick Access toolbar. In general, if you find that you use a particular command frequently, it should probably be on your Quick Access toolbar.

Read More

How to Change MS Excel Themes

1. Click File

2. Click Option

3. At the office theme options, choose the theme you want applied to MS excel

4. Click Ok button

Read More

How to Inserting a Watermark in MS Excel

A watermark is an image or text that appears on a printed page. A watermark can be a faint company logo or a word, such as DRAFT. Excel doesn’t have an official command to print a watermark, but you can add a watermark by inserting a picture in the page header or footer. Here’s how to do it:

1. Locate an image on your hard drive that you want to use for the watermark.
2. Choose View➜Workbook Views➜Page Layout View to enter Page Layout view.
3. Click the center section of the header.
4. Choose Header & Footer Tools➜Header & Footer Elements➜Picture. The Insert Picture dialog box appears.
5. Click Browse and locate and select the image you picked in Step 1 or locate a suitable image from other sources listed, then click Insert to insert the image.
6. Click outside the header to see your image.
7. To center the image vertically on the page, click the center section of the header and press Enter a few times before the &[Picture] code. You’ll need to experiment to determine the number of carriage returns required to push the image into the body of the document.
8. If you need to adjust the image (for example, to make it lighter), click the center section of the header and then choose Header & Footer Tools➜Header & Footer Elements➜Format Picture; use the Image controls on the Picture tab of the Format Picture dialog box to adjust the image.

You may need to experiment with the settings to make sure that the worksheet text is legible.

Displaying a watermark on a page.

Figure above shows an example of a header image (a copyright symbol) used as a watermark. You can create a similar effect with plain text in the header (for example, the word DRAFT).

Read More