Slick Tricks in No-Cost "Ultimate Excel Add-In"

Tired of trying to find Excel features hidden among the options pages and dialog boxes? Putting all those options up front is just one of the many benefits of Ultimate Add-in, a collection of over 80 utilities and 20 custom functions (nearly 7000 lines of code) is available. The utility adds lots of smart shortcuts, from swapping cells and ranges to manipulating sheets and formulas.
Among the user-interface changes available: You can show and hide the formula bar, status bar, gridlines, and horizontal or vertical scroll bars, plus quickly enable AutoFilter, check worksheet protection settings, reveal hidden sheets or cells, or find unlocked cells.
UAI adds a host of features for working with individual cells or ranges. You can select cells by content (all equal to 100, for example) or format property (font name, font color, bold or italic, among others), find those with errors, find (and remove) cells with links, or reverse the signs in all numeric cells (turn positive values to negatives, and vice versa). Text manipulation includes removing spaces (leading, trailing, excess, or all) or characters (leading, trailing, all alpha, or all numeric).
You can create ranges or change their dimensions and location. For quickly switching between relative and absolute addressing, UAI has an option to switch between the two methods for selected cells, or to change formulas to relative rows and absolute columns or to relative columns and absolute rows. One of our favorites: converting a long column into several columns (you tell the program how many elements in each column and the new starting location; it does the rest).
Working with more than one sheet is also easier. Imagine sorting sheets by name or a custom order you define, add serial sheets (Mon, Tues, Wed ... or Jan, Feb, Mar ...). You can also insert sheets using a custom naming convention; you specify the leading text, starting number, and increment, plus how many sheets you want; UAI then creates sheets, such as “Region 2, Region 4, Region 6, and so on. If you’d prefer, UAI can create sheets using values already entered in a cell range. Pretty slick.
You can insert the current date in a variety of formats, find and delete conditional formats or data validation, use the full file path or the contents of a cell as the header or footer when printing a worksheet, or reset what Excel thinks is the last cell in a worksheet.
A Range Info toolbar is a handy feature, which includes the ability to select a range and with a single click view the average, numeric count, minimum, maximum, total cell count, and sum of values in that range. Select a new range, click on the numeric-keypad icon, and UAI recalculates the values.
Among the custom functions are some to show the full path of the current file, the ability to insert text into other text (you supply the starting position as well), and calculating the sum of the user-defined top values in a range.

COMPUTING WORK DAYS IN A DATE RANGE

How to find the number of weekdays between two dates, excluding holidays. Since he didn’t specify which Office application he was using, I’ll start with Excel, since that’s the easiest. Believe it or not, the Excel Analysis ToolPak contains a Visual Basic for Applications (VBA) function designed for this very purpose!

In a cell, enter: =NetWorkDays(“01/01/2001”,”03/31/2001”) and tab out of the cell. It should show 65, the number of weekdays in the first quarter of 2001. Changing your function to =NetWorkDays(“01/01/01”,”03/31/01”,{“01/01/01”,”02/19/01”}) eliminates New Year's Day and President's Day, yielding 63 work days.

To eliminate a longer list of holidays, replace the third function parameter with a range of cells containing the dates to be excluded. For example, =NetWorkDays(“01/01/01”,”12/31/01”,E1:E12) where column E contains a list of holidays in rows 1 through 12.

If the NetWorkDays function is not available, run the Setup program to install the Analysis ToolPak. After you install the Analysis ToolPak, you must enable it by using the Add-Ins command on the Tools menu. More information on the NetWorkDays function can be found in Excel’s help file.

            Public Function WorkDays(D1 As Date, D2 As Date) As Long
            Dim vDate As Date, vHolidays(2) As Date, I As Integer
            'Initialze variables
            vHolidays(1) = "01/01/01"
            vHolidays(2) = "02/19/01"
            WorkDays = 0
            vDate = D1
            'Loop through the rnge of dates
            While vDate <= D2
            If Weekday(vDate) > 1 And Weekday(vDate) < 7 Then
            WorkDays = WorkDays + 1
            End If
            For I = 1 To 2
            If vDate = vHolidays(I) Then
            WorkDays = WorkDays - 1
            End If
            Next I
            vDate = DateAdd("d", 1, vDate)
            Wend
            End Function
            

The array v Holidays could be expanded to hold more dates and it could be loaded from a table if you wish.

SECURITY WARNING: SELF-EXECUTING WORD MACROS

Microsoft has issued a security bulletin (and program patch) about a flaw that lets macros automatically execute in Word (versions 97 through 2002) documents. The potential for hacker mayhem is significant.
In typical use, Word's security mechanism guards against automatically running macros. In Word 97, for example, a pop-up window appears whenever Word opens a file and discovers a macro if that's the security setting you've chosen.
Here's how Microsoft describes it: "By default in Word 2000 and 2002, only macros that are signed by a trusted party are enabled; all others are disabled. In Word 97, if the document contains macros, the user is prompted regarding whether to enable them or disable them."
Unfortunately, it turns out that it is possible to modify a Word document so the security scanner won't recognize an embedded macro -- and lets the macro execute no matter what. (Microsoft doesn't give any details about what that modification is.) Given the power of the macro language, that's a huge security hole.
The company has posted patches for Word (for both Windows and the Mac) at: 
While we don't know how widely such hacked files may be circulating, we suggest you apply the fix right away.

Shortcut function keys in excel

Here is a list of shortcut keys in Microsoft Excel.

1. F1: Opens a help task pane


2. F1 + Ctrl: Opens or closes the help window


3. F2: To edit the text in the active cell


4. F2 + Shift: To write a comment in the active cell


5. F2 + Alt: Opens the save as dialog box


6. F3 + Shift: opens the insert dialog box


7. F3 + Ctrl: Opens the define name dialog box


8. F3 + Shift + Ctrl:  Opens the create name dialog box


9. F4:  To redo the last command


10. F4 + Ctrl: To close the workbook window


11. F4 + Alt: To close the excel program


12. F5: Opens the Goto dialog box


13. F5 + Ctrl: brings back to the first level of workbook


14. F6: To shift between help taskbar and the excel window


15. F6 + Shift: To goto the previous square in the open worksheet


16. F6 + Ctrl: If more than one workbook is open to switch between them


17. F7: To open spelling correction window


18. F7 + Ctrl: When workbook window is not maximised it will move the window


19. F8: To activate / deactivate extended mode


20. F9: Will calculate all the worksheets in all open workbooks


21. F9 + Shift: Will calculate the active worksheet


22. F9 + Ctrl: To Minimise the workbook window


23. F10: Will select the menu bar


24. F10 + Ctrl: To open minimised window


25. F11: Will create a chart using the current range


26. F11 + Shift: To select a new worksheet


27. F11 + Alt: To switch between visual basic editor and the worksheet


28. F11 + Alt + Shift: Opens microsoft script editor


29. F12: Opens save as dialog box


30. F12 + Shift: Saves the active workbook


31. F12 + Ctrl: To open the Open dialog box


32. F12 + Ctrl + Shift: Opens the print dialog box

SIMPLIFY PRINTING WITH RANGES.

EXCEL: SIMPLIFY PRINTING WITH RANGES

Last week I was working on a spreadsheet for a friend of mine, trying to compare the costs of two health plans. With myriad options, deductibles and scenarios presented by the insurance company the spreadsheet quickly grew to be really ugly. All I wanted to print was just a few rows of data, plus some headings so my friend could make a decent evaluation. I also wanted to print a different set of cells for my own use.

Each time I wanted to print my selected data I found I had to individually select the cells for my friend or the cells I wanted to review. Things got even more tedious because many of the cells weren't contiguous – that is, I needed a row here, a few cells there, and so on. What a pain!

Well, I found a much easier way to switch between the sets of cells for printing: I used named ranges. A named range is a set of cells to which you assign a name. The range name (and the cells it refers to) is stored as part of your worksheet. (Ranges offer a host of benefits besides easier printing, as we'll see next week.)

The cells in a range can be contiguous or non-contiguous. In my case, I wanted to print cells A1 through D5 and C25 through H25 for my friend, so I called this range Final. I created a different range named Analysis for the cells I wanted to print for my own review.

Creating a Range

To create a range, choose the cells you want to include. I first selected cells A1 through D5 (you can do this with the mouse or from the keyboard). Then I held down the Ctrl key and selected the remaining cells (C25:H25). From the main menu select Insert, Name, Define. Enter a range name in the "Names in Workbook" text box (I entered Final) and select OK.

Your range name can be up to 255 characters long, but the first character must be a letter or the underscore character. The rest of your range name can consist of letters, numbers, periods, and underscores. Spaces, however, are NOT allowed. Had I wanted to call the range Final Costs I would have to settle for something like Final_Costs or Final.Costs.

To print the named range, be sure the range is selected. In the upper left corner of your screen you'll see a small box that usually displays the name of the currently selected cell. To select your named range, click on the down-pointing arrow to the right of this box and select the range name from the drop-down list. (If your name is short, you can also enter the range name directly into the box.) Excel highlights your range automatically.

From the main menu, choose File, Print. In the Print dialog box choose Selection in the Print What area.

You'll notice a couple of things where printing is concerned.

1. Print Preview doesn't show just your selected range unless you set the range to be the Print Area. To do this, choose the range and then use the main menu and choose File, Print Area, Set Print Area. If you're working with a brand new sheet, Print Preview shows you the entire worksheet if no print area has been selected. 


2. If your range includes non-contiguous cells, the Print command will print a new page for each contiguous group of cells. Thus, in this example, printing the Final range resulted in two printed pages. To date I have found no simple way to combine non-contiguous groups of cells into a single printed page.

Inserting Picture into Slide

You can also insert picture into a slide to make the presentation more meaningful and beautiful. The picture can be inserted from the clipart or from any image file stored on the disk.

1. To insert picture into slide from clipart, follow these steps. 

• Select the slide into which you want to insert picture.


• Select "Picture" from "Insert" menu and then select "Clip Art" from sub­menu, Clip Art dialog box appears.

OR 

• Click Insert Clip Art on button the drawing toolbar.


• Select picture category and choose required picture and right click mouse on the picture, a popup menu appears.


• Select Insert command from the menu.

2.    To insert picture from file stored on disk, follow these steps.

1. Select the slide into which you want to insert picture.


2. Select "Picture" from "Insert" menu and then select "From File" from sub menu, Insert Picture dialog box appears.


3. Select image file and click Insert button of dialog box.

Change AutoRecover interval in Office 2007 and 2010

Among the many useful features of the Microsoft Office 2007 applications is AutoRecover.  It involves saving all the data in the Office 2007 application after a specific time interval so that it is recovered in case there is a power outage or a system crash. All your work or data in the Office application is saved automatically after that specific time interval. This feature keeps you from losing work you have done. By default, this AutoRecover interval is set to 10 minutes in all the Office 2007 applications. However, it is possible to change AutoRecover interval in Office 2007.  If you change AutoRecover interval in Office 2007 to less than 10 minutes such as after every 5 minutes, you will be able to save your work or data frequently. It will prove very helpful if you are facing problems in the power supply or your operating system.

The option to change AutoRecover interval in Office 2007is offered for every application. Here we will use the example of Excel 2007. Follow these steps to change AutoRecover interval in Office 2007:

1.     Click on the Office button.

2.     Click on the "Excel Options"  button on  the bottom of the resulting menu

.3.     The Excel Options dialog box will appear. Select Save on the left hand side of the menu

.4.      Check the box for "Save AutoRecover information"     .5.  Select how often you would like Excel to save the data for AutoRecover. 

 

6.     Click OK.

 

You should not set the time to a very short time interval. Remember that when working on larger worksheets or documents don't set the AutoRecover to a very short time interval such as 1 minute because that may begin to slow down the computer, as saving a large amount of data after each minute will strain the computer.

 

Protecting Document with Password

You can protect your document by applying password so that unauthorized person can not display as well as modify your document. You can apply two types of passwords:

Password to open the document: 

If it is applied then you have to give the correct password to open the document, otherwise you cannot open the document.

Password to modify the document: 

If it is applied then you have to give the correct password to modify the document, otherwise your document is opened but you cannot modify the document. It means that your document becomes read-only.

To apply a password to document, follow these steps.

* Open Save As dialog box by selecting "Save As" command from File menu.

* Click "Tools" button of Save As dialog box and choose "General Options" from drop down menu, "Save" dialog box appears as shown in figure below.

* Enter first password in "Password to open" text box and second password in "Password to modify" text box (if required) and click "Ok" button of dialog box. Microsoft Word will open "Confirm Password" dialog box for the confirmation of passwords. The maximum length of password is 15 characters.

* Re-enter the password to open and password to modify and click "Ok" button of Confirm Password dialog boxes one by one.

* Click "Save" button of Save As dialog box.

How to select text Vertically in Word 2007?

Almost everybody who writes their documents in Microsoft Word knows how to select the text horizontally. You have to select the text horizontally in order to delete, underline, italicize etc.  But many times you face a situation in which you have to select the text vertically. For example you might have to delete the first few words in every line in a list in order to delete it. You need to do that as you reformat a Word document. Many people think that it is not possible to select text vertically in Word 2007 document. It is very easy to select text vertically in Word 2007. You just need to do this trick in order to make the vertical selection:

Point the mouse to the beginning of the text you want to select.

Hold down the Alt key.

Drag the mouse pointer downwards around the required area while still holding down the Alt key.

The text will be selected vertically.

Record Narration for Microsoft PowerPoint Presentation

Microsoft PowerPoint is used for normally presenting complex data in graphical form, with the help of charts, graphs. There are various helpful tools in Microsoft PowerPoint2010; there is one in particular that is also very handy. If you want to add narration that is voice over to your presentation, that can be beneficial for various purposes. You can do that by doing few simple steps, please read step by step guide below.

Note: Make sure microphone is working properly. If you do not have built in microphone, please make sure that plug is secure.

Instructions:

From the “Start” Menu, click on “All Programs” option and Select “Microsoft PowerPoint 2010”. Either browse an existing file or complete the new one.