Difference between revisions of "Microsoft Excel Tips and Tricks"

Revision as of 13:22, 24 August 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.