Category: Excel

This formula will return the name of the current worksheet: =MID(CELL(“filename”,INDIRECT(“rc”,)),FIND(“]”,CELL(“filename”,INDIRECT(“rc”,)))+1,255) Make sure that you've saved your file with a name other than Book1, otherwise the formula won't work.

The default setting for sorting in ascending or descending order is by row. Occasionally, you may want to sort by column instead. To do so, choose Data, Sort, then click the Options button. Choose Left to Right, then click OK. Select the row that you want to sort by, then click OK.

If you drag the fill handle of a date with the right mouse button, a special menu appears option to Fill Weekdays, along with other choices. The right-drag menu that appears offers other options if you're dragging text or numbers.

Dragging between sheets can be difficult with the mouse unless you use the Alt key. If you have a workbook maximized, e.g. only one sheet is visible, you can drag a selection to another sheet by holding the Alt key while dragging and pointing to the sheet tab you want to go to. If you hold Ctrl and Alt you will copy instead of move. You can also press the Alt while dragging with the right mouse button. Pressing Alt is also useful if you have two windows visible side by side and are copying between them.

Excel's Data Validation feature allows you to restrict inputs within a cell. For instance, to limit inputs between 0 and 100, select a cell, then choose Data, then Validation. On the Settings tab, allow Decimal, then specify 0 for the Minimum and 100 for the Maximum. Fill in the Input Message and Error Alert tabs if you want to customize the prompts for that cell. Entering anything other than a number between 0 and 100 will result in an error prompt.

You can calculate part of a long formula by highlighting a portion of the formula bar and pressing F9. Be sure to press the escape key afterwards, otherwise the calculated result will replace the formula. This is a great way to isolate problems in a long formula.

This article on the Microsoft web site shows how you can combine print areas from multiple sheets into a single page print out: http://office.microsoft.com/assistance/2000/ExPrintAreas.aspx

If you're using Excel 2002 or later, you can add &[Path]&[File] to the Header or Footer fields under File, Page Setup. This will print the location and file name of your spreadsheet, e.g. C:My DocumentsMy Spreadsheet.xls.

Enter =CELL(“filename”) in a worksheet cell to display the name and path of your spreadsheet. If you save the file in a new location, the formula will update automatically. (Our thanks go to Manprit S.) https://www.acctadv.com/go.mv?id=tip

Here's the last tip in our series of eradicating links. Microsoft offers a free Delete Links Wizard, which is available from https://www.acctadv.com/go.mv?id=deletelinks

If you're interested in the technical details on how this utliity works, read the article available at http://www.microsoft.com/europe/vba/downloads/mod199806dr.doc

Using a spreadsheet that contains links that you can't identify can be frustrating. Fortunately, we have a free download that will give you some relief. To install the add-in, download the file, then in Excel, click Tools, Add-Ins, Browse, locate and double-click the findlink.xla file, then click OK. A Find Links choice will appear on your Tools menu. An alternate way to find links is to search for exclamation marks (!) or “*.xls” without the quote marks.

Selecting Data – Rather than using your mouse to highlight a range of data, try holding down the Shift key, then at the same time use the arrow keys or Page-Up/Page-Down keys to select your data. Using the keyboard to select data offers much more control over the cursor, so that your mouse doesn't end up at row 38,472 when you're only trying to highlight down to row 148.

=DATEDIF – If you ever need to determine the number of months between two dates, the simplest way is with the =DATEDIF function. Here's a sample scenario: =DATEDIF(A1,B1,”m”) will return 12 if cell A1 contains the date 01/01/02 and cell B1 contains the date 12/31/02. If this is a function you can use, make sure to make a note of it in a place where you'll remember, because it's only documented in Excel 2000! You won't find mention of it in any other Excel version. Chip Pearson offers some additional background on this function, including its storied history.

To quickly copy data down a column, try double-clicking on the fill handle. Excel will automatically copy down the current column, and stop at the last non-blank cell in the preceding column. The Fill Handle is the little notch in the bottom-right hand corner of the active worksheet cell.