Difference between revisions of "Microsoft Excel Tips and Tricks"

Revision as of 19:21, 13 December 2009

Refer to cell in another worksheet

Reference the worksheet name like this: sheet1!C12

In the example there is a worksheet for each month of the year. To reference a value in cell E7 from the January worksheet in the February worksheet do this:

 =January!E7

Increment month by one

Easy way but not always accurate, just add the number of days to next month:

 =DATE(2009,1,15)+31

Use EDATE from Analysis Toolpak (Tools>Add-ins and check Analysis Toolpak)

 =EDATE(A1,1)

With the Date function

 =DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))

Hide / Unhide Columns

To hide columns just right click on the columns (the letter header at the top) and select hide. But once they are hidden how do you unhide them? Since they are not visible you cannot right click and select unhide.

To unhide column A in the worksheet in Excel versions 97 to 2003

1. Type the cell reference A1 in the Name Box and press the Enter key on the keyboard.
2. Click on the Format menu.
3. Choose Column > Unhide in the menu.
4. Column A will become visible.

To unhide multiple columns in the worksheet - all versions of Excel

For example, you want to unhide columns B, D, and F.

1. In the column header drag select to highlight columns A to G.
2. Right click on the selected columns.
3. Choose Unhide from the menu.
4. The hidden columns and the column letters will be visible.

Even though you can't see the hidden columns as long as you highlight the column before the hidden columns and the column after the hidden columns, then select unhide, you will effectively unhide all of the columns in between.

How Cell Lock and Worksheet Protection Works

By default an Excel worksheet is not protected. Although the state of every cell is locked by default, the locked status is benign unless the worksheet is protected. You must first turn on protection to make the locked state active.

Note: Once the worksheet is protected, you will not be able to change which cells are locked and which cells are unlocked. First, unlock all the cells you wish to be edited before activating protection.

• TO LOCK OR UNLOCK CELLS: Right click on a cell or highlighted area. From the shell menu choose "Format Cells" and click the "Protection" tab.

• TO ACTIVATE WORKSHEET PROTECTION: From the TOOLS menu at the top, choose PROTECTION and enter a simple password.

Editing the locked cells will not be allowed until sheet is unprotected.