(ebook PC) 300 excel tips.pdf

300 office tips • Special feature

Microsoft Excel

columns

You can convert rows to columns (and

columns to rows) by highlighting the

cells you want to switch around,

clicking on Edit, Copy , selecting a new

cell and then going to Edit, Paste

Special… Finally, place a tick in the

Transpose box on the dialog box and

click on OK .

2 Calculate time

the running total of the figures in A1 to

A5 in the adjacent column.

5 Remove hyperlinks

Pick the function you require from the

list box and click on OK . Now

highlight the cells on which you want

the target to perform the function and

click on OK .

from your work

If Excel has already converted your

written URL into a hyperlink, you can

cancel it by right-clicking on the

offending address and selecting

Hyperlink, Remove Hyperlink from

the menu that pops up.

6 Fit wide tables to the

between dates

Enter in a cell the formula =A2-A1 ,

where A1 is the earlier date, and A2

the later one. Don’t forget to convert

the target cell to number format – do

this by highlighting the cell, clicking on

Format, Cells… , picking on the

Number tab and selecting Number

from the Category: list.

3 Enter URLs as text,

page width

To make your tables fit neatly on the

page, click on File, Page Setup… ,

select the Page tab, click on the Fit to:

radio button and pick 1 page wide .

Click on the tall box and press

[Delete] , leaving the box empty.

7 Hide your data from

not hyperlinks

To prevent Excel from converting

written Internet addresses into

hyperlinks, add an apostrophe to the

beginning of the address, for example

‘www.futurenet.com .

4 Calculate

prying eyes

If you want to hide from view any

sensitive data, highlight the relevant

cell and click on Format, Cells… Click

on the Numbers tab, select Custom

from the Category: list, double-click

on the Type: input box and enter ;;; .

Undo the operation to make your data

visible again.

8 Use template

AutoFills

If you use the same list over and over

in different worksheets, you might want

to add it to your AutoFill list – this will

save you heaps of time in future.

Highlight your list, click on Tools,

Options… and select the Custom Lists

tab. Click on Import , then OK .

Excel converts Web site

addresses to hyperlinks

automatically. Right-click

on them to remove the link.

running totals

Enter the numbers to be added in

column A, say A1 to A5, then enter

=SUM($A$1:A1 ) into column B.

Highlight the cells beside the ones with

numbers in (in our example, B1 to B5)

and go to Edit, Fill, Down . This places

worksheets

Templates can save you considerable

time when you’re setting up a new

worksheet. Click on File, New… , select

the Spreadsheet Solutions tab and

choose a template from the list.

9 Access help for Lotus

The Paste Function button

enables you to browse

formulas and is the easiest

way to enter Functions.

users

If you’ve converted from Lotus 1-2-3

and find Excel confusing, you can

access help specific to your situation by

clicking on Help, Lotus 1-2-3 Help…

10 Use the

12 Use the

AutoCalculator

If you need to calculate a sum based

on a row or a column of figures and

you can’t be bothered typing in a

function, just select your figures and

glance down at the status bar – you’ll

find the sum of the selected cells there.

formula browser

Select a cell and click on the Paste

Function button on the main toolbar.

Filling Down – the easy way to perform

dynamic calculations.

April 2001

PC Answers

75 tips and tricks for your favourite spreadsheet.

1 Convert rows to

11 Customise your

Special feature • 300 office tips

What’s more, if you right-click on the

sum a pop-up menu will appear offering

additional quick calculation functions.

13 A quick way to

automatically

Make your work look neater by selecting

the relevant column, and clicking on

Format, Column, AutoFit Selection .

18 Fast copy, no

your selection

You can have Excel display just the area

you’re working in by highlighting the

relevant cells, clicking on the arrow on

the Zoom box on the toolbar and

selecting Selection from the list.

26 Another default

Excel has two distinct date

calculators: one returns the

current date, the other

returns the creation date.

clipboard

Pressing [Ctrl] + [‘] is a speedy way to

duplicate the formula or figure in the

cell above the one you’re in.

19 Fast and easy

entry direction

When editing cells the cursor moves

down when you press [Return] . To

change the direction click on Tools,

Options… and select Edit . Click on the

Move selection after Enter list box and

choose another direction from the list.

27 Launch Excel, open

multiple entry

If you need to enter a formula in several

cells at once, simply select all the target

cells, type your formula as normal and

press [Ctrl] + [Enter] .

20 Pick cells for

your file

Launch Excel , open the file you use

most often, and click on File, Save

As… In the Save As dialog, navigate to

the Xlstart folder in your Office folder

and click Save . Now the file will open

automatically when you launch Excel .

28 Set decimal points

Click on Tools, Options… and select

the Edit tab. Place a tick in the Fixed

decimal tick box and use the arrows in

the Places: input box to set the number

of decimal places.

29 Calculate the

AutoCalculator

You can select unconnected cells by

holding down [Ctrl] while you click on

the individual cells.

21 Hide comments to

into Excel

If you want Excel to enter the current

date or time and fix it at that point – for

example, to show the last date the sheet

was modified – click on a cell and press

[Ctrl] + [;] for the date and [Ctrl] + [:]

for the time.

15 Find the currently

reduce clutter

You can hide notes and other comments

by using the Insert Comment function.

Highlight a cell, click on Insert,

Comment and type your text. Click

away from the input box when you’ve

finished. The comment will appear

whenever you pass your mouse over

the relevant cell.

22 Re-colouring the

active cell

If you’ve been scrolling around your

spreadsheet and you lose your place,

you can jump back to the currently

active cell by pressing the [Ctrl] +

[Backspace] keys.

16 See the

median value

To calculate the median value of a

number of figures, say A1 to A10, enter

=median(A1:A10) into the target cell.

30 #NAME! and

grid lines

You can change the colour of the grid

by clicking on Tools, Options… ,

selecting the View tab, clicking on the

Color: list box and choosing a new

colour from the palette. Choosing white

effectively removes the grid completely.

23 Angle your

big picture

If you’re working on a large sheet you

might want to switch to Full Screen

mode: simply click on View, Full

#NUM! errors

Excel returns a #NAME! or #NUM!

error whenever a formula refers to non-

existent names or numbers. To sort it

out, re-enter the formula correctly.

31 Quick sort your

entries…

To make Excel display the text in cells at

an angle of 45 degrees, right-click on

the toolbar, select Chart , and pick either

of the ab icons on the new toolbar.

24 …to any degree

columns

The quickest way to sort a column into

a hierarchy is to select it and click on

the Sort Ascending or Sort Descending

buttons (the ones with the AZ arrows).

32 Quickly change

you like

If you want custom angles right-click on

the cell and select Format Cells…

From the pop-up menu, click on the

Use angled text to add

visual interest to your lists

and graphs.

cell dimensions

The quickest way to change a column’s

PC Answers

April 2001

Screen . Click on it again to return to a

normal window.

17 Fit text

Alignment tab, and then drag the Text

pointer in the Orientation window.

25 Zoom in to

enter the time

To enter the current date or time click

on a cell and type =today() or =now() .

Excel updates the result every time you

open the sheet, so it’s always current.

14 Enter a fixed time

300 office tips • Special feature

width or a row’s height is to place your

mouse pointer close to the join between

two columns or rows, then click and

hold down the mouse button, and drag

the column or row to the desired size.

33 Erasing entries

in the Category: pane, click on the

Type: input box and press [M]

repeatedly until the desired format is

displayed in the Sample area above.

40 Quick

Handy keyboard tips

30 shortcuts to speed up your calculations.

while typing

If you mistype your formula, press

[Esc] to erase the cell’s content.

34 Enter number

column copy

You can fill out the cells in a column by

double-clicking on a cell’s handle.

Excel will place copies of the clicked

cell in each cell below, stopping when

it reaches a cell with blanks either side.

(Confused? Try this: enter a figure in

cell A1, then enter figures in cells B1 to

B5, then double-click on A1’s handle.)

41 Merge cell

Select the current column [Ctrl] + [Space]

Select the current row [Shift] + [Space]

Edit the active cell [F2]

Move to the beginning of the worksheet [Ctrl] + [Home]

Move to the last cell on the worksheet [Ctrl] + [End]

Paste a name into a formula [F3]

Paste a function into a formula [Shift] + [F3]

Alternate value/formula view [Ctrl] + [`] (on key [1])

Calculate all sheets in all open workbooks [F9]

Display the Go To dialog box [F5]

Display the Find dialog box [Shift] + [F5]

Display the Format Cells dialog box [Ctrl] + [1]

Create a chart [F11]

Insert a new sheet [Alt] + [Shift] + [F1]

Repeat the last action [F4]

Repeat Find [Shift] + [F4]

Open [Ctrl] + [F12]

Exit [Ctrl] + [F4]

Check spelling of current cell [F7]

Activate the menu bar [F10]

Display the Macro dialog box [Alt] + [F8]

Apply outline to active cell [Ctrl] + [Shift] + [&]

Convert to a percentage [Ctrl] + [Shift] + [%]

Select all filled cells around active cell [Ctrl] + [Shift] + [*]

Move to next sheet

sequences

Enter the first number in a cell, press

the [Ctrl] key, and then click, hold and

drag the cell’s handle until you’ve

reached the desired number of cells.

Release the mouse button first, and

then the [Ctrl] key.

35 See formulas at a

contents

To merge the contents of cells A1 and

B1, click on cell C1 and enter =A1&B1 .

The result is not a sum but a text

string, so merging 10 and 7 will return

107, rather than 17.

42 Use dynamic

glance

You can view all of your formulas at

once by clicking on Tools, Options… ,

selecting the View tab, and placing a

tick in the Formulas tick box.

36 Highlight cells of

[Ctrl] + [Page Down]

formatting

You can set Excel to alert you to critical

figures in cells by setting it up so the

colour or size of text in a given cell

changes whenever a certain condition is

reached. Select a cell (say, the cell

containing the total of your bank

balance) and click on Format,

Conditional Formatting… Choose the

conditions in the pop-up dialog (say

‘Cell value is less than 0’) and click on

the Format… button. Now choose a

new colour in the Color: list box and

click on OK . Click on OK again to

finish. From now on Excel will display

your chosen figure in the new colour

whenever the figure falls below zero.

43 Border connected

Move to previous sheet

[Ctrl] + [Page Up]

Complete a cell entry and move up

[Shift] + [Enter]

Complete a cell entry and move right

[Tab]

Complete a cell entry and move left

[Shift] + [Tab]

a kind

You can select all the cells of a certain

type by using the Special functions.

Click on Edit, Go To… , click on the

Special… button, choose the cell type

from the dialog box and click on OK .

37 Switch off

Edit a cell comment

[Shift] + [F2]

the zeroes

You can prevent zeroes from cluttering

your sheets by clicking on Tools,

Options… , selecting the View tab, and

unticking the Zero values tick box.

38 Protect

to support files

To place links in cells that enable you

to load other documents with one click,

enter a name for the link in a cell and

press [Ctrl] + [K] . Click on the File…

button. Navigate to the file you wish to

link to. Double-click on it and click

OK . From now on, whenever you click

in that cell, Excel will jump to the file.

your work

Open the sheet you wish to protect and

click on File, Save As… Click on the

General Options… item in the Tools

menu (in the top right corner of the

Save As dialog) and enter a password

in the Password to open: input box.

Finally, click on the Save button. You

might want to make a note of the

password before continuing.

39 Customise

cells

You can place a border around a group

of related cells – all the totals, for

example – by selecting them and

clicking on Format, Cells… , then

selecting the Border tab and clicking

on the Outline button.

44 Shrink entries to

Prevent cell

content from

overlapping

neighbouring

cells by shrinking

it to fit.

fit their cells

You can force Excel to display the full

text of a cell by using the Shrink to fit

option. Click on Format, Cells… , click

on the Alignment tab and place a tick

in the Shrink to fit tick box. Obviously,

the more text you have, the smaller it’ll

your dates

You can customise the format of a date

in a cell using the Custom cell formatter.

To do this, enter a date in a cell, click

on Format, Cells… , click on Custom

April 2001

PC Answers

be, so this isn’t practical for small cells

with lots of content.

45 Create hyperlinks

Special feature • 300 office tips

Outlook, PowerPoint,

Outlook

separate spell-checking options – tick

each box for the optimal spelling

system. If you want to switch the whole

thing off, you should untick the second

box in the list.

4 Automatically

sizes, no mouse!

Getting the font sizes right is paramount

to a well-designed layout, and if you’re

constantly shifting and resizing frames,

it can become a bit of a headache.

However, there is a speedy method for

switching font sizes without any mouse

operations: highlight the text you wish

to change and press [Ctrl] + [right

square bracket] to increase the font size

by a point, and [Ctrl] + [left square

bracket] to decrease it by one point.

1 Find the contact

you’re after quickly

To locate a contact from just a fragment

use the Find a Contact input box on

the main toolbar. Just click on the box

and enter what you remember of the

contact’s name. Outlook will find the

contact and open his or her details in a

dialog box.

addressed email

Click on Folder, Contacts , select the

contact you want to email and click on

Actions, New Message to Contact . A

new message pane will open with the

contact’s address details already

entered for you.

5 Track

your mail

You can find out when your mail is read

by sending a receipt request with your

messages. Click on Tools, Options…

and select the Preferences tab. Click on

the E-mail options… and Tracking

Options… buttons on the following

dialog. Finally, place a tick in the

Request a read receipt for all

messages I send tick box.

You can use the design gallery to store your

own page elements.

8 Build your own

Find a Contact brings up

the Contact Properties.

Here you can add, edit or

remove the contact from

your address book.

don’t bin it

Outlook doesn’t really delete messages,

it places them in the Deleted items

folder. To delete them click on Tools,

Options... , select the Other tab, and

place a tick in the Empty the Deleted

Items folder upon Exiting tick box.

3 Spell check your

Publisher

Design Gallery

The Design Gallery is Publisher’s library

of professionally-designed elements and

objects that help to bring consistent

design to your projects. It’s handy for

dabblers but pretty useless for

more committed users. To

get the best from the

Design Gallery, you should

use it to store your own

objects. To add an object

to the Gallery, select it and

click on Insert, Add

Selection to Design Gallery…

Enter a name and category in the input

boxes and click OK .

9 Check your design

6 Resize objects using

central origin

To keep the centre of an object

in the same place while you

resize it, hold down [Ctrl]

while you are dragging the

object’s handles.

outgoing mail

To spellcheck your email before you

send it, click on Tools, Options… and

then on the Spelling tab. There are five

Let Publisher solve your layout

problems for you with the

Design Checker.

Outlook shortcuts

Five email shortcuts no one should be without,.

for problems

Once you’ve finished your project it

pays to run it through its paces in the

Design Checker and check it for flaws:

select Tools, Design Checker… and

click on Options… Select the Check

Display Inbox

[Ctrl] + [Shift] + [I]

Check for new mail

[F5]

Reply to selected mail

[Ctrl] + [R]

Create new mail

[Ctrl] + [N]

Send mail

[Ctrl] + [Enter]

PC Answers

April 2001

Bringing up the rear are Microsoft’s email client, presentation, and

DTP software. Here are the hottest 15 tips and 35 shortcuts…

7 Quickly adjust font

2 Shred your waste,

300 office tips • Special feature

and Publisher

all problems radio button and then

both OK buttons. Publisher now checks

the integrity of your frames, bringing

attention to any potential problems,

and suggesting remedies by way of a

pop-up dialog box.

10 No need to

and slides to the new button will appear

once you’ve released the mouse button.

Publisher shortcuts

Five essential DTP shortcuts.

Toggle frame opacity

[Ctrl] + [T]

Nudge frame

[Alt] + [Arrow] (any of the four directions)

Format copy

[Ctrl] + [Shift] + [C]

Format paste

[Ctrl] + [Shift] + [V]

cut and paste

To copy an object simply select it,

press [Ctrl] and drag it to a new

location. The copy is left behind in the

original position.

Toggle current/actual page view

[F9]

the same place as the line. This

means you can align all the

pieces of your document with

very little effort. Once your

elements are in position you

can drag the Guides to new

locations or you can disable

them. The frames are

unaffected. To activate the

Guidelines click on View, Guides .

Adding buttons is a simple two-step process.

PowerPoint

13 Just pack

11 Get the most from

and go

If you want others to be able to watch

your presentations and you’re not sure

whether they have PowerPoint , use the

File, Pack and Go… command. This

Pack and Go Wizard enables you to

collate all of the presentation elements

into one standalone, portable program.

14 Make your text

your videos

If you’re using a video clip in your

presentation, make sure it’s in the same

folder as the presentation, otherwise it

will display a Find Files dialog when

run. Once you’ve loaded your film you

can access its properties by right

clicking on it and selecting Edit Movie

Object… from the pop-up menu. The

dialog displays the clip’s total playing

time and playing options.

PowerPoint shortcuts

Five shortcuts to increase your speed.

Insert slide

[Ctrl] + [M]

stand out

To add instant pizzazz to your headings

go to WordArt on the Drawing Tools

menu. You can configure WordArt text

to run in a line, around a circle or in any

other shape. It can be multi-coloured,

3D or textured. Used sparingly,

WordArt can be an eye-opener.

15 Use Guides to

Move to the next pane

[F6]

Move to the previous pane

[Shift] + [F6]

Insert a hyperlink

[Ctrl] + [K]

Start slide show

[F5]

Generic shortcuts

These shortcuts work in all Office applications, as

well as some third-party software.

place frames

Guidelines are non-printing lines that

enable you to align frames with ease.

When you drop a frame close to a

Guideline it automatically snaps into

Open

[Ctrl] + [O]

Exit

[Alt] + [F4]

Save

[Ctrl] + [S]

[Ctrl] + [P]

Select All

[Ctrl] + [A]

Add impact to your presentation with a

movie object.

Cut

[Ctrl] + [X]

Copy

[Ctrl] + [C]

Paste

[Ctrl] + [V]

12 Add buttons to

Undo

[Ctrl] + [Z]

your slides

Buttons make your presentation look

and feel professional. Activate the

Action Buttons sub-menu on the Slide

Show menu and hover your mouse

pointer over each of the available

buttons to view their descriptions. To

use one. click on the button and draw

a square on your slide. A dialog that

enables you to link sounds, programs

Redo

[Ctrl] + [Y]

Help

[F1]

Open pop-up menu

[Shift] + [F10]

Open window control menu

[Alt] + [Space]

Scroll one page length down

[Page Down]

Scroll one page length up

[Page Up]

Delete

[Delete]

Switch applications

[Alt] + [Tab]

Activate menus

[F10]

Close menu

[Alt]

Share your presentations more easily with

Pack and Go.

Select first/last menu item

[Home] / [End]

April 2001

PC Answers

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika: