|
Check out those microsoft excel tips and tricks: Quick Wizard
To activate the Function Wizard quickly...
Type part of your equation (i.e. =sum( ) then press [Shift][F3] to start the Function
Wizard already at the second step.
How to control the enter key
By default, you move down one cell when you press Enter, or up one cell if you press
Shift-Enter. To change this, choose Tools|Options, then the Edit tab. From the drop-down
list under the Move Selection After Enter check box, pick the direction you want your
cursor to move. Whatever direction you pick, pressing Shift-Enter will move you in the
opposite one. If you don't want the cursor to move at all, remove the check.
Extending A Selected Range
Often times, when selecting cell ranges with the mouse, you can underselect or overselect
the intended range. When this happens, most people reselect the desired range from scratch
This is not necessary. You can use the Shift Key and the Arrow Keys To modify and fine
tune your selection.
To Do This, While Holding down the Shift Key, hit the arrow key of the direction you wish
to expand or contract your selection.
Tie it Together
To tie cell references and label text together in a formula...
Use the ampersand (&) in between the reference and the text. e.g. (=A1&"
Employees") will display "25 Employees" (given that cell A1 contains the
number 25)
Go There Now
To move immediately to a cell...
Press the [F5] key to open the "Go To" dialog box. Then simply type the cell
address (e.g. a23) and press [Enter], you then be taken to that cell.
Inserting a New Column or Row
Highlight column (to insert a column to the left) or a row. (to insert a line above).
Press CTRLand the plus (+) key OR
Click on Insert and then select Columns/Rows.
Quickly create a Graph
Quickly create a graph by selecting the data range in the worksheet and press the F11 key
on the keyboard. This will place a default graph on a separate sheet in your workbook!
Not Boxed In
Many dialog boxes and wizards in excel 97 let you select cells from your spreadsheet while
working in a dialog box. Look for the small grid button with a red arrow in it. When you
click on it, the wizard or dialog box shrinks to a single line so you can select a cell or
range of cells. Click on the grid button again to expand the dialog box.
Spreadsheet Stop Lights
To examine data with special highlighting, select the data and choose format, conditions
and specify three conditions: Use red font color or cell shading for values less than a
specified minimum, yellow for values within the specified range and green for values
greater than a specified maximum.
Snare Data From the Web
You can capture live data from Web-based spreadsheets and manipulate it in your own
spreadsheet. To open the Web spreadsheet, type or paste its URL into the File Open dialog
box. Select and copy the cells you want. In your spreadsheet, select Edit/Paste Special
from the menu bar and click the Paste Link button.
New Slant on Spreadsheets
You can rotate spreadsheet column headings to a 45-degree angle. Select the cells
containing the column headers, right-click the selection and choose Format Cells. Click on
the Alignment tab and set Orientation to 45 degrees. Click on OK.
Deleting a Column or a Row
Highlight the Column or Row you want to Delete
Press CTRLand the Minus (-) key OR
Click on Edit and then select Delete.
dates & time
Current Date: Type CTRL+; to have current date placed in a cell.
Current Time: Type CNTL+Shift+; to have the current time placed in a cell.
Date/Time Formula: To have the current date and time in a cell and update when the
document is opened and when edited. You will need to type one of the following formulas:
For date: =today() For Date and Time: =now()
close ALL open spreadsheets
Hold down the SHIFT key and click on FILE and then select CLOSE ALL. This also works in
WORD to close all open documents.
adding data to chart
simply drag and drop. Just select the data you want to add to an existing chart, and then
drag the selected cells until the mouse pointer is over the chart. Release the mouse
button and the chart redraws to accommodate the new data. Cool stuff!!!
Move data between sheets
Highlight the data you want to move, press alt and drag it to the new
sheet.
Move a column between two columns
Highlight the column and drag it while holding the shift. You will get a vertical line,
then place the line between the two columns.
Quick copy a formula in table
Double click on the black little point on the edge of the cell. This will automatically
copy the formula down the table (cool!!).
Quick fill of large area with one data
Highlight the entire area, write the data and press ctrl+enter.
The data will feel the entire area!
How to change ToolTip text for custom buttons
When you add a custom button to a toolbar, the button's ToolTip text
reads Custom. You can change this-or the text for any other button-with a macro. If the
button is on the standard toolbar, say, in the second position from the left, and you want
the text to read Hi There, the macro would be as follows:
Sub DoIt
Toolbars ("Standard").
ToolbarButtons(2).Name
="Hi There"
End Sub
Once you run the macro (with the correct toolbar name, button position, and ToolTip text),
the change sticks until you alter it again. You also can change the text for the status
line, by replacing the .Name argument with the .StatusBar argument, which takes the form
.StatusBar="Text".
How to mix chart types
Sometimes it's useful to mix chart types, such as putting a pie chart for a year's total
sales behind a column chart for individual quarters. To do this, create the column chart,
double-click on the chart to select it, then click on one data series (one set of
columns). Next, choose Format|Chart Type (or right-click and choose Chart Type from the
context menu), and finish by choosing a chart type, such as a pie chart, and then clicking
on OK. To return to the original chart, once again select the data series you want to
change, choose Format|Chart Type, and redefine it to match the original chart type.
How to strip spaces from cells
Extra spaces before cell entries can create problems when you sort and filter tables. To
remove these spaces, select the cells and run this macro:
Sub TrimIt()
For Each c In Selection.Cells
c.Value = Application.Trim(c.Value)
Next c
End Sub
Perfect Fit
To make a column fit its contents...
Double-click on the right edge of the column reference. |