| Faster access to worksheets - Excel | | Print | |
When working with large workbooks in which all of the worksheet tabs are not visible, your users may get frustrated with scrolling to find the sheet they need. Here's a really quick tip to pass on. Right-click on any of the tab-scrolling buttons (to the left of the sheet tabs) and a shortcut menu will pop up. You can then select the tab you want.
Inserting the full file path in headers and footers
Excel 97 doesn't offer you the option of displaying the full file path in worksheet headers and footers. With a little basic VBA editing, you can add this functionality to users' systems.
1. Go to Tools | Macro | Visual Basic Editor.
2. Go to the View menu and select Code.
3. Type the following:
Sub PathFooter()
ActiveSheet.PageSetup
LeftFooter=ActiveWorkbook
FullName
End sub
4. Go to File | Close.
Return to your workbook and save the file. You can now run your macro by navigating to Macros and choosing the one named PathFooter.
Exceeding the 30-argument limit
Excel has a 30-argument limit for statistical functions. It's easy to get around, though, if you group some of your arguments within parentheses.
Instead of entering AVG(A1,A2,A3…,A33), you can enter AVG((A1,A2,A3)A4…,A33), and Excel will accept the grouped arguments as a single argument within the formula.
Ask Mr. Excel
Excel power users and support pros: Check out MrExcel.com. Mr. Excel answers one reader's question per week, according to what piques his interest, often including full macros to be copied and put to use.
Because this is a side project for the site's operator, he can't answer every question submitted, but he has provided a message board for other Excel users to exchange information. Other site features include past week's tips, quick answers, book reviews, and the opportunity to find out whether you can answer the monthly challenge. If you work extensively with Excel and Visual Basic, this may be the one resource you've been looking for.
| Users' Comments (0) |
|
No comment posted
