Category: Excel

Chip Pearson, an Excel MVP, has written a helpful explanation of array formulas in Excel. His site also contains other helpful Excel tips, links, and info.

Arguably, one of the most powerful features in Excel is the ability to create dynamic ranges, which expand or contract as you add or delete data. You can create a dynamic range that consists of a single row by choosing Insert | Name | Define, then enter MYRANGE in the Names In Workbook field, then enter the following formula

=OFFSET(Sheet1!$B$5,0,0,1,COUNTA(Sheet1!$5:$5))

in the Refers To field, then finally click Add to save the reference. in addition, it's even possible to take this method a step further and make both the rows AND columns dynamic, with the following syntax

=OFFSET(Sheet1!$B$5,0,0,COUNTA(Sheet1!$B:$B),COUNTA(Sheet1!$5:$5))

where the range counts how many cell entries are in column B, and how many cell entries are in row 5, then expands or contracts the range automatically.

The =EOMONTH function is a handy way to determine the last day of a given month. However, that function requires that you have the Analysis ToolPak installed and enabled (users often don't). Instead, use the =DATE function. For instance, =DATE(2000,1,0) will return 12/31/99. You can also use this technique in your VBA macros and functions.

You can copy a formula or text to adjacent cells by clicking on the fill handle (the notch in the right-hand corner of a worksheet cell) with your left-mouse button and dragging either down or across. (Our thanks go to Kedar Desai, who got paid for submitting this tip!) For even more options, hold down the right-mouse button while you click and drag…when you release the mouse button, a shortcut menu will appear.

Working on multiple files today? Open them all at once tomorrow by creating an Excel Workspace File. Choose File, Save Workspace to group all of the files you currently have open into a workspace. You can open the group of files all at once by choosing File, Open, and then change the File Type to Workspaces to access your saved workspaces.

Sometimes it's helpful to see two sheets within the same workbook at the same time. To do so, choose Window, and then New Window. Next, choose Window, Arrange, and then choose Horizontal. Click Windows of Active Workbook, and then click OK. If you want to see the windows side by side, choose Tiled or Vertical in the Arrange dialog box.

The Paste Special feature on Excel's Edit menu can save signficant time. For instance, you can convert a column of numbers with implied decimals, e.g. 1000 to whole numbers, 10.00. To do so, enter 100 in a worksheet cell, and then copy that cell to the clipboard (Edit, Copy is one way). Next, highlight the cells that you want to divide by 100, and then choose Edit, Paste Special, Divide, and then click OK. You can also use Paste Special to perform multiplication, addition, or subtraction.

You can document your worksheet by right-clicking on a cell, and then selecting Insert Comment. Add a message or instruction associated with that cell. This is a great way to share your thoughts with multiple users of the spreadsheet. Cell comments can serve as a helpful memory aid. Right-clicking on a cell that already contains a comment enables you to add, edit, or delete the comment.

Double-clicking the narrow button above the scroll-bar on the right, or to the right of the scroll-bar along the bottom will allow you to create a split view of your worksheet, allowing you to see two portions of the same sheet at once.

To alter the font or color of any part of a cell, use the Formula Bar at the top of the screen to highlight a portion of the text within a cell. Next, click Format, and then Cells. You can use Font tab to change the font or apply characteristics such as superscript/subscript, underlining, bold, or color.

To easily navigate between worksheets, press Ctrl+Page Up to move to the right, or Ctrl+Page Down to move to the left.

Copy a range of cells to the clipboard, then hold the Shift key and click the Edit menu. Click the Paste Picture Link. (This option is not available unless you hold the Shift key) This creates a picture that is linked to the cells you copied. You can move this picture around as you would a graphic. The picture updates automatically when you change the related cells. This is a helpful way to combine data from multiple locations on to a single sheet, particularly for printing purposes.

To create a workbook where the first page summarizes the rest of the workbook, make sure that your workbook has at least 4 sheets. Name the second sheet Start and the last sheet End. Hide these two sheets, which should left be blank. On the first sheet, the formula =SUM(Start:End!B2) will sum cell B2 on all other sheets in the workbook. Additional sheets inserted later will automatically be included in the summary.

You can use Excel's conditional formatting feature to highlight every other row in a spreadsheet, similar to the old-style green bar paper. To do so, select the portion of the spreadsheet that you wish to format, and then choose Format | Conditional Formatting. Change Cell Value Is to Formula Is and enter this formula:
=MOD(ROW(),2) <> 0
Click the Format button, and then under Patterns select the color of your choice. Click OK twice.

When copying a sheet full of formulas that refer to other worksheets and using paste special | transpose to place them in a new worksheet, the formulas reference incorrect cells. A simple trick is to use ctrl-h (replace) to change all the “=” signs to a benign value (such as AAAA or 9999), then copy and transpose. Once in the new worksheet, you can use the same process to change the 9999 back to = and all your formulas reference the right cells.