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.
Mar 25
Combining Multiple Sheets into One Printed Page
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
Mar 25
Show Path and File Name on Printouts
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.
Mar 25
Displaying File Name and Path
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
Mar 25
Eradicating Links – Part III
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
Mar 25
Eradicating Links – Part II
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
Mar 25
Eradicating Links – Part I
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 …
Mar 25
Selecting Data
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 …
Mar 25
Determining the Number of Months Between Two Dates
=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 …
Mar 25
Quickly Copy Data Down a Column
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.
Mar 25
Excel Programming Reference Book
Excel Programming Weekend Crash Course is a great reference book for anyone interested in learning how to create macros in Excel. Even experienced VBA users will pick up a few new tricks. The material is broken down into chapters that take 30 minutes to complete, so it’s easy to work through the material at your …
Mar 25
Navigating Multi-Sheet Workbooks Quickly
Did you know that you can quickly navigate multi-sheet workbooks by right-clicking on the arrow buttons at the bottom left-hand side of the screen? Doing so causes a menu with all sheet names to appear. Left click on the sheet of your choice, and then that sheet will be immediately activated.
Mar 25
Excel Shortcut Keys
Learn about over 270 Excel shortcut keys on the Accessibility section Microsoft web site at http://www.microsoft.com/enable/products/keyboard/keyboardsearch.asp
Mar 25
Excel Formula Debugging Trick
Be sure to enter formulas in Excel in all lowercase letters. When you press enter, Excel converts all valid portions of the formula to UPPER CASE. For instance, in the case of =sum(jj1:j10), pressing enter causes Excel to return the #NAME error, with =SUM(jj1:J10). Notice that the incorrect JJ1 is still in lower case, while …
Mar 25
Automated tasks in Excel
Tommy Flynn’s VBA site offers many VBA routines that automate various tasks in Excel.