Excel
Tips and Techniques
Compiled by
January 2004
Sources: PC Magazine, Microsoft’s online
tutorials, Karen Jolly’s “Excel 97 for Windows”
Formatting:
Move the formatting menus: The font color, border, and background color drop-down menus are moveable. Left click above the drop down arrow on the menu bar to display the menu palette. Click on the double lines at the top of the palette and drag.
Format numbers as
fractions: To enter a fraction, start with a space. To
enter mixed numbers and fractions into cells: type in the integers, press
Spacebar, then type in the fraction. To see the
decimal equivalent of the fraction: click on the cell and look on the Formula
bar.
Automatic formatting of
dates and time: If you enter a value containing a slash (/) or hyphen
(-), and that value also complies with the Windows standard date format, Excel
will interpret your entry as a serial date value and format the entry
accordingly. Similarly, Excel will format any value that contains a colon (:)
as a time value. Or you can follow the time value with an A or P, representing
AM and PM, respectively.
Replace formulas with
values: To replace a formula or function with the actual value of
a cell, select the cell, then choose Copy from the Edit menu. Next, from the
Edit menu, select Paste Special, Values. OK.
Wrap text in cells: To prevent
text from spilling over into adjacent cells, select a group of cells,
right-click then choose Format Cells. Click on the Alignment tab then check the
Wrap Text option.
Limit types of
information entered into a worksheet: To limit information that can
be entered into a worksheet, highlight the cell, column, or row that you wish
to limit. Open the Data menu. Select Validation. Click the Settings tab in the
Data Validation dialog box that appears. Click the down arrow next to the Allow
box to select the type of numbers to allow. In the Data box, enter the range of values to allow. Customize the error box by clicking
on the Error Alert tab. Click OK to save settings.
Adjust column widths
when pasting: When you copy
a range of data (several columns), you can't automatically paste the column
widths that accommodate your data. You must first select the data you want to
copy and press Ctrl + C. Click a destination cell, and press Ctrl + V to paste
the data to the new range. Right-click the new range and choose Paste Special.
Select the Column Widths option in the Paste section and click OK. When you
return to your worksheet, the columns in the new range will be the same width
as the columns in the original range.
To format a cell as a
pull down list: Data Menu, Data Validation, Settings tab. For Allow
criteria choose list. In the Source box, type in a list of items separated by
commas. Or select a worksheet range that contains the list.
To remove all custom
formatting from a cell or range: Edit Menu, Clear, Formats.
To add color to the
worksheet tabs: Right click on a tab. Choose Tab Color. Pick a color.
Working with Data
To quickly cross-foot data ranges with AutoSum:
Select
the data with the mouse, including a blank column or row, or both, adjacent to
the range of data. Click the AutoSum button.
Using the IF function:
Using
comparative signs (equals, greater than, less than) within a formula produces
an output of either ‘true’ or ‘false’. The < or > sign must precede the = sign if both are used. For example, g6 >=
k8 will give values of either true or false, depending on the values in cells
g6 and k8. This type of formula is used as a test condition for the IF
function. Depending on the true/false value of the formula an IF function
generates different output.
Utilize Excel's goal seek feature: Select the cell with the formula to be forced to produce a specific value. Open the Tools menu and choose Goal Seek to display the Goal Seek dialog box. The active cell will be displayed in the Set Cell text box. In the To Value box, enter the target value for the formula cell to reach. In the By Changing Cell box, enter the input cell. Click OK and the Goal Seek process will start and will state whether it found a solution. If it did, the worksheet values will be changed accordingly.
Select cells while
viewing information in a dialog box: To select cells in a worksheet
while viewing information in a dialog box or wizard, click the small grid
button with a red arrow in it, usually to the right of a field box. The dialog
box or wizard will then shrink to a single line. Use the mouse to select a cell
or range of cells. Then click the grid button again to restore the dialog box
or wizard.
To select multiple
ranges of data in a dialog box: Use the above technique. A new grid
automatically opens for adding additional data ranges.
To link a text box to a
worksheet cell: first, create a
text box. Next, click inside the text box. Then, in the Formula bar, type an
equal (=) sign. Finally, select the worksheet cell that contains the data or
text you want shown in your text box and press Enter. You may instead choose to
type the reference to the worksheet cell. But remember to include the sheet
name followed by an exclamation point, for example, Sheet1!F2,
before pressing Enter. You can add a text box to your worksheet by using the
text box tool on the Drawing toolbar.
To move the cell selector to an edge of a data range. Move your
mouse pointer to the edge of a cell in the data range and double click on the
right side or bottom of a cell.
To separate first and
last names from one cell into two: Highlight the data that needs to be
separated. Now go to the Data menu, Text into Columns choice.
To get subtotals of
individual categories: Click in the column where you want subtotals. Click on Data, then Subtotals. Select from the
list boxes.
Working with comments: Edit or create
a comment in the current cell: Shift-F2.
Move a comment that's being edited: Esc, Arrow keys.
Close a comment that's being edited: Esc, Esc.
Delete a comment: Select the cell with the comment. Edit Menu,
Clear submenu, Comments
Transposing ranges of
data in a table format: Highlight the range of data, including
the titles. Copy it. Move the cursor to a new location. From the Edit Menu,
Paste Special, Transpose, Enter. Press Escape to clear the marquee around your
original table.
To enter data or format
into multiple worksheets simultaneously: Hold Control
while selecting multiple worksheet tabs. Changes made in one worksheet will be
made in all of the selected worksheets. Hold Control and click on any worksheet
tab when finished.
Printing
To copy print settings
from one sheet to another, select the sheet with the settings
you want to copy. Then, hold down the Shift (or Ctrl) key and click on the tab
(or tabs) of the other sheet (or sheets) you want to copy the settings to. At
this point, choose File, Page Setup, and click OK. By doing so, you will copy
all the basic print settings from the active sheet to all the others in your
temporary group. (You'll also copy header and footer settings.)
Print column heads on
each Excel page: Open the File menu and choose Page Setup. Click the Sheet
tab. Click the Rows to Repeat at Top button and press Enter, then click Print.
To print Workbook or
Worksheet names on the hard copy: Right click on any tab and choose
select all sheets. In File menu, Page Setup Submenu, Headers/Footers Tab,
select the desired option from the header pull down box for the Work Book name.
Use the footer pull down box to add the Work Sheet names.
Data Entry
Copy a table from the
web: Highlight the data and copy it. In Excel, choose an empty sheet.
Paste in cell A1. If this doesn’t copy your entire table: Undo Control-Z,
select the entire worksheet Control-A, and paste Control V. Click on Ok to
clear the “size” error message.
Filling a data range
quickly: Highlight a range. Type in first piece of data and press
enter. Data appears in upper left hand cell. Enter additional items of data,
pressing enter after each one. The data entry points moves down, then back to
top of range in adjacent right-hand column.
Enter information into
several cells: To enter a formula or date into several cells at once,
select the desired range, type the information, then press Ctrl + Enter.
Enter data to multiple
sheets. Click on the tabs while holding control. Data entered in
one sheet will appear in all selected sheets. You can enter into more than one
cell. When finished, click a tab to select a specific sheet.
Using AutoFill to
enter a data series: Highlight a row of cells containing a
series of data. Move the mouse to the lower right hand corner of the
last cell on the right. The AutoFill handle + appears. Drag
the AutoFill handle to the right.
Using AutoFill to
the left of the data: Select data as above. When
AutoFill handle appears, drag from right to left, continuing past the starting point of
your data.
AutoFormat width or
height: Highlight several columns and double clicking to right of
the right hand one. Select rows then double click below the row furthest down
the sheet.
“Point and Click”
method of entering formulas: Type in =. Click on the cell you are using
as a reference in the formula. To make it an absolute cell reference, select a
cell then use the F4 key (it cycles through options). Type in any required
symbols between mouse clicks. Note that odd shaped sets of individual cells
and/or ranges can be separated by a comma. Example: =sum(a2:d3,c7,g5)
To quickly copy a
formula into a column or range: Enter the formula in the upper row of
the range or cell in the column. Highlight the formula and the area below it
where you want to copy the formula. Control-D.
To quickly copy a
formula into a row or range: Enter the formula in the left-hand
column of the range or cell of the row. Highlight the formula and the area to
the right where you want to copy the formula. Control-R.
To add a comment (documentation)
to a formula: Edit the formula, adding +N (“Your comment”). Include
quotation marks and parentheses. The comment can be read in the formula box but
won’t affect your calculations. For example: =A2+B3 +N(“Sums
the values of cells A2 and B3”).
To move ranges of data: Select, drag, and drop.
To copy ranges of data
with Mouse: Select, drag, and drop while pressing the Control key
To copy a range of data
with keyboard: Select the range then click on upper left corner cell of
the new data range and hit enter.
To copy to multiple
areas: Hold down the Control key while repositioning the cursor and
pressing Enter.
To embed data from
Excel into an Office application: Copy the data range from the source
document. From the Edit Menu, Choose Paste Special. Click the Paste button.
Select Microsoft Excel Worksheet Object. Click Ok. This is a static copy.
Changes to the data in either file do not affect the date in the other file.
To link data from Excel
into an Office application: Copy the data range from the source
document. From the Edit Menu, Choose Paste Special. Click the Paste Link
button. Select Microsoft Excel Worksheet Object. Click Ok. This is a
dynamically updated copy. Changes to the data in either file will change it in
both.
Data Selection:
Data selection of
multiple areas: Press the Control key while using the mouse to select
multiple cells, data ranges, columns, rows, or worksheet tabs.
Modify the area of a
selected range: If you use the mouse to highlight a range of too many or
too few cells, use the Shift and Arrow keys to modify your selection.
To select a single
column: Put the cursor in the column then control-spacebar, or
click on the column letter.
To select a single row:
Put the
cursor in the row then shift-spacebar, or click on the number.
To select the entire
worksheet: Control A or click on the blank cell in upper left
corner.
To select a range:
Click
the cell in the upper left corner. Hold down the shift key. Click the opposite corner. Using the F8 key
instead of Shift also works. Press F8 again when finished.
To select multiple
ranges: Press Control then repeat the
above procedure in another area.
To select all
worksheets: Right click on any tab and
choose Select All Sheets.
Move entire columns: Highlight the column letters. Hold down the Shift key.
Drag.
Move entire rows: Highlight the row numbers. Hold down the Shift key. Drag.
Keyboard Shortcuts:
Keyboard Formatting
·
Control 0 = Hide column
·
Control 1 = Format Menu
·
Control 2 = Bold
·
Control 3 = Italics
·
Control 4 = Underline
·
Control 5 = Strikethrough
·
Control 9 = Hide Row
Enter
the date: Keyboard Shortcut: Ctrl + ; (semicolon)
Enter the Time: Keyboard Shortcut:
Ctrl + : (colon)
To move between
worksheets: Control + PageUp or PageDown
Copy the value from the
cell above the current cell: Ctrl-Shift + "
(quotation mark).
Copy the formula from
the cell above the current cell: Ctrl + ' (apostrophe).
Create a graph: Select the data
range in the worksheet. Press the F11 key.
Open the format dialog
box: Press Ctrl + 1. (That's the number 1, not a lowercase L.)
To auto-format data:
Type a
$, -, decimal point or % sign when entering numbers.
Currency format:
Control
+ Shift + $
General number format:
Control
+ Shift + ~
Comma number format:
Control
+ Shift + !
Percentage number
format: Control + Shift + %
Unhide column:
Shift +
Control + )
Unhide row:
Shift +
Control + (
AutoSum:
ALT +
Enter
To apply/remove borders
to all cells: Control + Shift + & / Control + Shift + _
To access the Paste
Function dialog box: Shift + F3
To trace errors: Select the
cell with the formula in question. Press Ctrl + Shift + [
Excel highlights all cells that play into the value of the
selected cell.
To apply/remove strike-through
formatting to a
cell: Control + 5 Example: strikethrough
Charts
To create a log-log
chart: Us
the xy scatter diagram option to build the chart.
Right click on the axis and a tab that says scaling becomes available. This one allows you to check logarithmic on both
axes .