Leading the way in Microsoft Office Development

Entering Data

You can multiply a cell range by a given number. Enter the number in a cell and copy it. Select (Edit > Paste Special) and select "multiply" and click OK.

You can decrease all the values in a range by 10 percent, type 0.9 in a cell. Copy the cell and select (Edit > Paste Special) and click multiply.

You can enter 32,000 characters into a cell however if you try to copy the entire sheet then any cells containing more than 255 characters will be truncated. To avoid truncation, copy the cells of the source sheet to the destination instead of copying the sheet as a whole.

Remember that a cell being left blank or empty is not the same as a cell being equal to zero as the result of a formula, or entering a zero in a cell (which are both values).

Copying Data

To copy the contents of the active cell to all the other cells in a selected range, press F2 and then (Ctrl + Enter).

Displaying Data

Sometimes if a number starts with a negative sign then they are only displayed as a dash if the column width is not wide enough for the whole number. Just F2 the cell or convert it to a formula (prefix with an equals).

If you have used row and column range names to provide a quick way to get parameters into worksheet functions, then "NUM!" is displayed although the correct value is obtained ??

Selecting Cells

You can use the F8 key to select a cell range. Select the top-left cell, press F8. Click the bottom-right cell. Press F8 again to cancel the highlighting.

The (Edit > GoTo)(Special dialog box) has numerous different options for selecting cells of a particular type, including: All, Formulas, Values, Formats, Comments, Validation. All except borders, None, Add, Subtract, Multiple, Divide, Skip blanks, Transpose, Paste Link.

If you are deleting cells or modifying cells on a worksheet it is always a good idea to switch the calculation to manual first.

Selecting Cells - Visible Only

When you've filtered data or hidden rows or columns, selecting a block of cells will also, disappointingly, select the cells that are not visible. There is a hidden command that will let you select only the visible cells. It's hidden in that you have to add this command to your toolbar in order to use it.

Right-click in the gray area of the toolbar to the right of Help

Choose Customize

Go to the Commands tab

Choose the Edit category

Scroll all the way to the bottom of the list of Edit Commands

You should find the Select Visible Cells command

Click and drag this command and drop it where you want it to appear on a toolbar.

Now anytime you have cells hidden, you can click the Select Visible Cells icon to select all the currently visible cells WITHOUT selecting those cells you can't see.

Navigating

Hiding Data from being Printed

Need to produce a printout where certain cells (not an entire row or column, you could right-click and Hide them) in the print area need to be hidden? One trick is to set the font color in those cells to white.

Select the cells, (Format > Cells). In the Format Cells dialog box click the Font tab

Click the arrow at the right side of the Color list box and select white from the list

Click OK to close the dialog box and apply your new font colour

Print the worksheet without showing the unwanted cells. After you finish printing, select the cells again and set the colour back to black (or automatic).

Moving Data

If you copy a range of cells to the clipboard. You can highlight twice as many cells before pasting to obtain a duplicate set of data. For example if you copy two cells, highlight four cells and then paste, the data will be duplicated.

You can quickly copy the contents of the first cell in a selection to all the other cells by pressing F2 followed by (Ctrl + Enter).

You can move through a text string one word at a time by pressing Ctrl and then the Left or Right arrow keys.

To paste a range of cells as a picture into another application, select the cells and then hold down Shift while selecting (Edit > Copy Picture).

If you have a large range of cells and you need to copy the formulas without the references changing you can replace the equal signs with hashes..

Highlight the cells, press (Edit > Replace), choose "=" with "#".

Then copy and paste the references. Press (Edit > Replace), choose "#" with "=".

There is a quicker way to freeze formulas to values on a worksheet than using the Edit Copy, then Edit Paste Special and choosing the Values option.

After making a selection, right click its edge and drag it away slightly.

Then place it back in its original position. When you do that, a popup menu appears.

Select the Copy Here as Values option and you are finished.

Linking a Cell

You can reference a cell in another cell if you copy the cell.

Select the cell you want to link and select (Edit > Paste Special > Paste Link).

Adding Links

You can copy a range of cells and paste a picture onto a worksheet. The picture is then updated automatically with any changes in values or formatting. Hold down the Shift key and select (Edit > Paste Picture Link).