TIP #1: SHORTCUT TO QUICKLY SHOW THE CONTEXTUAL TAB RIBBONS
If you want to quickly show the ‘contextual tab’ ribbons to select options after inserting clipart, tables, pictures, etc, simply doubleclick on the object or the border of the object. This saves you from clicking to select the object, then clicking the Contextual tab that appears above the ribbon.
TIP #2 – TURN ON THE DATA FORM FEATURE
In Excel 2007, the macro security is set to medium as a default and an [OPTIONS] button appears below the ribbon commands when a file is opened.
To ‘Enable the content’ after you open a file, the [OPTIONS] button must be clicked and ‘Enable the Content’ radio button selected, for the file to enable macros, etc.
To change the macro settings for the file, you can do the following – this will eliminate the [OPTIONS] button from appearing when the file is opened. BUT BE CAREFUL!! This can be dangerous if you don’t know the file you’re opening – malicious code could be embedded in files someone has sent to you.
- Launch Excel 2007 and open the file
- From the OFFICE button, select [Excel Options]
- Select the TRUST CENTER category at the left
- Click [Trust Center Settings] button
- Click ‘Macro Settings’
- Select the radio button ‘Enable all macros (Not recommended – potentially dangerous code can run)
- Click [OK] and exit Excel Options
TIP #3 – ADD TEXT TO ANY SHAPE
When you draw shapes, you can add text within the shape(s) by doing the following:
- To insert your shape, from the Insert tab, select Shapes, then select the desired shape – using the mouse crosshair, draw it on the screen.
- When you have your shaped created, simply begin to type the text
OR
<you can right click on the shape and then select Edit Text> - Type the text that you would like to add to the shape, then click anywhere outside the shape
TIP #4 – Shortcut to Insert a New Worksheet
You can quickly insert a new worksheet in Excel 2007 – simply touch SHIFT/F11 and a new sheet tab will appear. You can then drag the new sheet tab to a different tab location within your worksheet. (Tip: CTRL/drag will ‘copy’ the sheet)
TIP #5 – Search and Replace Cells Formatted with a Specific Color
You can use Excel’s ‘find and replace’ feature to find formatting (such as color) within cells and replace the formatting with something else. Here’s how:
- From the Home tab, select Find & Select, then select Replace (or to bring up the Find and Replace dialog box quickly, you can touch CTRL/f and click Replace tab). The dialog box appears.
Note: The ‘Find What’ area and the ‘Replace With’ area should be left blank.
- On the ‘Find What’ line, select Format. (Note: If you don’t see Format, select Options to view the Format button)
The Find Format dialog box appears.
- Click the Fill tab (if finding/replacing color)
- Choose the color you want to find. Click [OK] to close.
- On the ‘Replace With’ line, select Format.
The Find Format dialog box appears.
- Click the Fill tab (if finding/replacing color)
- Choose the color you want to replace the original color with. Click [OK] to close.
- If you want to replace ALL the cells, you can click Replace All.
- It will automatically replace the original color in the cells with the new replacement color!
NOTE: If you don’t want to replace ALL the cells formatting at once, you can click [Find All] instead and randomly replace cell formatting
Close the dialog box when finished
You can use the following Nested If Function to read grade points (stored in column B in this example) and assign appropriate letter grades based on the grade points earned.
Type =IF(B2<60,”F”,IF(B2<70,”D”,IF(B2<80,”C”,IF(B2<90,”B”,”A”)))).
This function says that if the grade point average is less than 60, assign the letter grade F. If between 60 and 69, assign D. If between 70 and 79, assign C. If between 80 and 89, assign B. Else, assign an A.
TIP #7 – SETTING A TRUSTED LOCATION FOR EXCEL 2007 – AUTOMATICALLY ENABLES CONTENT
In Excel 2007, the default security setting when opening a file is set to Medium and to enable the content within the file (such as macros), you need to click the [OPTIONS] button that appears below the Ribbon and select ‘Enable the Content’ click [OK] after opening the fileso that macros/automatic processes, etc. will run properly.
In Excel 2007, you can identify various ‘safe’ locations and bypass the security settings to eliminate clicking the OPTIONS button and enabling content when opening each file.
To add a specific location (drive/folder, etc.) as a TRUSTED area to bypass the default security setting, do the following:
- From the OFFICE button, select [Excel Options]
- <the Excel Options window appears>
- Click Trust Center category, then click [Trust Center Settings]
- NOTE: You can identify a specific folder or area of your hard drive. The checkbox ‘Allow Trusted Locations on my network (not recommended)’ must be checked if you identify a network drive (share):
- From the Trust Center Settings window, select Trusted Locations category, then click [Add New Location]
- When the Trusted Location window appears, click [Browse] and select the location you want to identify as ‘trusted’, and check the box ‘Subfolders of this location are also trusted (optional) if you want to have the subfolders trusted as well. You can tab to the Description area and add a description, then click [OK].
- <the trusted location is now added to the list> Click [OK] to exit window.
- Click [OK] and exit Excel Options
The settings will be used on your machine’s installation of Excel. The files stored in that location will NOT require that you click the OPTIONS button when opening Excel files. The security warning will not appear when opening files from that location.
TIP #8 – QUICKLY GO TO A SPECIFIC SHEET
If you have many sheet tabs in your Excel workbook, there may be times when it’s frustrating navigating from one sheet to another. Here’s one shortcut to get to the tab you want quickly: Right-click on the sheet tab (navigation) controls at the left of the tabs at the bottom of the screen. This will display the first 15 sheet tab names and you can select the sheet you want from the list.
Computer Tricks & Tips 