Excel Tricks and Tips - UCL



UCL

Education & information support division

information systems

Excel

Tricks & Tips

Document No. IS-026 v2

 Contents

Moving around a worksheet/workbook 1

Shortcut keystrokes 1

Go To 1

Scrolling 2

Selecting data 2

Shortcut keystrokes 2

Select all 2

Select nonadjacent cells or cell ranges 2

Extended selection 2

Entering data 3

Shortcut keys 3

Making multiple entries 3

Setting limits for data entry 3

Entering numbers as text 3

Multi-line cell 3

Centre and merge 3

Shrink to fit 4

AutoComplete 4

Working with dates 5

Cell comments 5

Reviewing toolbar 6

Editing data 6

Clipboard 6

Printing 7

Formatting 8

Shortcut keys for formatting 8

Format painter 8

Conditional formatting 8

Clear formats 8

Formulae and functions 9

Precedence of operators 9

Displaying and printing formulae 9

Converting formulae to values 9

Displaying zero values 10

Functions 10

Working with large worksheets 11

Split screen 11

Freeze panes 11

Zoom 12

Custom Views 12

Filtering data 13

Working with multiple workbooks 14

Create workspace files 14

Close all files 14

Grouping worksheets 14

Entering data in multiple worksheets 15

Charts 16

Create a quick chart 16

Change the default chart format 16

Additional information 17

Charts 17

Validation 18

Auditing features 19

Trace errors 20

Set default options 20

Learning more 22

Introduction

This course aims to help you find quicker ways of doing everyday tasks. This fast-paced course is also a good all-round revision course for experienced Excel users. You are expected to have good general knowledge of Microsoft Excel.

As this is a tricks & tips/revision course, many topics are not covered in detail. Please see the Excel pages on the Staff Training website for details of other Excel courses.



Moving around a worksheet/workbook

Shortcut keystrokes

|To move |Keystroke(s) |

|One cell up, down, left or right |( ( ( ( |

|Up one screen |Page Up |

|Down one screen |Page Down |

|Left one screen |Alt+Page Up |

|Right one screen |Alt+Page Down |

|To edge of the worksheet (or current block of data) |Ctrl+relevant arrow key |

|To column A in the current row |Home |

|To cell A1 |Ctrl+Home |

|Move to the next worksheet |Ctrl+Page Up |

|Move to the previous worksheet |Ctrl+Page Down |

|To last used cell in worksheet |Ctrl+End |

|To a particular cell |F5 or Ctrl+G (Go To) then type the reference for the cell required and |

| |press Enter. |

| |Or, type the cell reference into the Name Box and press Enter. |

|Switch to the next pane in a worksheet that has been split (Window|Split). |F6 |

|Switch to the previous pane in a worksheet that has been split. |Shift+F6 |

|Scroll to display the active cell. |Ctrl+Backspace |

Go To

To move to a specific cell, press F5 or Ctrl+G then type the reference for the cell required and press Enter.

Go To Special

This is a method of selecting cells with particular contents or properties.

Find cells that don't match the active cell in a row or column

1. Select the range in which you want to compare cells.

2. Ensure the cell you want to compare is the active cell.

Helpful hint:

Use the Tab key to move the active cell in a selected range.

3. On the Edit menu, click Go To, or, press Ctrl+G.

4. Click Special.

▪ To compare the cells in a row, click Row Differences.

▪ To compare the cells in a column, click Column Differences.

Excel highlights any cells that are different from the active cell.

Scrolling

Scroll lock

When you use the scrolling keys (such as Page Up and Page Down) with Scroll Lock turned off, your selection moves the distance you scroll. If you want to preserve your selection while you scroll through the worksheet, turn on Scroll Lock first, i.e., if Scroll Lock is on, you can use the navigation keys without affecting the current selection.

Scrolling with the mouse

To scroll long distances hold down the Shift key while dragging the scroll box.

Selecting data

Shortcut keystrokes

Sometimes, selecting with the keyboard gives you more control over the amount of data you select.

The table below lists the more useful keys for selecting:

|To select |Keystrokes |

|The active cell plus one cell up, down, left or right |Shift+(, Shift+(, Shift+(, Shift+( |

|To the edge of the worksheet |Shift+Ctrl+relevant arrow key |

|(or current block of data) | |

|To the beginning of the row |Shift+Home |

|To the beginning of the worksheet |Ctrl+Shift+Home |

|Extend the selection to the last used cell on the worksheet |Ctrl+Shift+End |

|Extend the selection down one screen |Shift+Page down |

|Extend the selection up one screen |Shift+Page up |

|Whole row |Shift+Spacebar |

|Whole column |Ctrl+Spacebar |

|Whole worksheet |Ctrl+A |

Select all

A button to select all cells is located at the top left of the worksheet.

Select nonadjacent cells or cell ranges

Select the first cell or range of cells, and then hold down the Ctrl key and select the other cells or ranges.

Extended selection

Press F8 to turn extend mode on or off. In extend mode, EXT appears in the status line, and the arrow keys extend the selection.

Shift+F8 adds another range of cells to the selection; or use the arrow keys to move to the start of the range you want to add, and then press F8 and the arrow keys to select the next range.

Entering data

Shortcut keys

|To enter |Keystokes |

|Today’s date |Ctrl+; |

|Current time |Ctrl+Shift+; |

|Same as cell above |Ctrl+’ |

Making multiple entries

When the same data needs to be entered into lots of cells, you can do it by selecting them first, typing in the data and then confirming the entry with a special key combination.

1. Select the cells where you want the entries to appear (use the Ctrl key if there are several nonadjacent blocks to fill in).

2. Type the entry – it will initially appear in the active cell.

3. Press Ctrl+Enter.

The text will appear in all selected cells.

Setting limits for data entry

When you want to limit the cells into which data should be typed, you can do this by selecting them. When you type in a selected area, the entry appears in the active cell (the cell that remains white). You can then move the active cell down or right within the selection to continue. When Excel hits the edge of the selected block, pressing Enter or Tab moves you to the next column or row within the selection.

1. Select the cells where the entries should be made.

2. Type the first entry. The entry will appear in the active cell.

3. Press Enter to move the active cell down.

Or Press Tab to move the active cell right.

You can continue using Enter or Tab to move the active cell to the next cell within the selection where you want data. If you need to go back up or left, use Shift+Enter or Shift+Tab.

Helpful hint:

Don’t use arrow keys to move within the selection as they will deselect the block.

Entering numbers as text

Move to the cell where you want the entry and type the numeric value preceding it with an apostrophe, e.g. ‘2006. Excel will treat your entry as text and line it up accordingly.

Multi-line cell

If you want to break a line within a cell, press Alt+Enter.

Centre and merge

Select the cells you wish to merge into one cell and click on the [pic] icon. Where there are entries in all the selected cells, Excel will warn you that it will only keep the entry in the top left cell in your selection when you merge them together. The text in that cell will be centred across the merged cells.

Shrink to fit

This feature reduces the apparent size of font characters so that all data in a selected cell fit within the column. The character size is adjusted automatically if you change the column width.

Format menu|Cells|Alignment|Shrink to Fit

Before [pic] After [pic]

AutoComplete

When you type the first few letters of an entry into a cell, Excel can complete the entry automatically using AutoComplete. It does this by building a list based on the entries already entered in a column. If Excel suggests an inappropriate entry, you can pick a different entry from the list.

1. Position your mouse over the next blank cell in a column.

2. Begin typing the entry – Excel will try to match what you type with other items already entered in the current column and will automatically complete the entry for you.

3. Press Enter to accept Excel’s proposed entry.

Or

Continue typing to replace Excel’s proposed entry with your own entry.

4. Press Enter to complete your entry.

Pick from list

You can get AutoComplete to display a list of possible entries built up from previously entered column data and select the one you want without typing anything.

1. Click the right mouse button in the required cell.

2. Choose Pick from List.

3. Choose the entry required with the left mouse button.

Excel can only AutoComplete column entries if there are no gaps in your data. If you leave a gap, the next cell in which you type won’t AutoComplete, nor will you be able to pick from a list.

AutoFill

The fill handle allows you to copy and clear data but also to fill in series of data (dates, weekdays etc). It appears in the bottom right-hand corner of the selection or active cell. When your mouse is over the fill handle, the white plus pointer changes to a black plus.

Examples of AutoFill:

Helpful hints:

If you want to copy data rather than extend the series, hold down Ctrl as you drag the fill handle.

If ever you find that the fill handle is missing, on the Tools menu, click Options, and click the Edit tab. Make sure Allow cell drag and drop is selected.

Custom AutoFill

You can create a custom AutoFill series from existing items you've listed in a worksheet, or you can type the list from scratch.

If you've already entered the list of items you want to use as a series:

1. Select the list, then from the Tools menu, select Options and click the Custom Lists tab. Existing lists are displayed in the Custom Lists box on the left.

2. To use the selected list, click the Import button.

3. The new data will be added to the lists on the left, and listed in the List Entries box.

4. Click OK to accept the new custom list.

Creating a new list from the custom lists tab

1. Select Tools|Options then click the Custom Lists tab.

2. Click on NEW LIST in the Custom lists box on the left.

3. Key in the new items in the List Entries box, beginning with the first entry.

4. Press Enter after each entry.

5. When the list is complete, click the Add button.

Working with dates

Remember to remove the date formatting too when deleting dates. From the Edit menu, select Clear and All.

To format a date, select the Format menu and the Number tab. From the Category column select Date and then choose the type of format you want.

Helpful hint:

You can perform calculations on dates in Excel as underlying every date there is a number. This is based on number 1 being 1 Jan 1900. 367 would therefore represent 1 Jan 1901. The 1 Jan 2006 will be 38709.

Cell comments

When a cell has a comment attached, it is automatically displayed when the mouse pointer rests over the cell. You can tell which cells have comments attached as they have a comments indicator (a red indicator in the top-right corner of the cell).

If you create a cell comment and an indicator does not appear in the cell, check that either the Comment indicator only, or, Comment & Indicator, option is checked in the View tab by selecting Options on the Tools menu.

Printing cell comments

You can print cell comments either At end of sheet, or, As displayed on sheet.

1. Select File|Page Setup and click the Sheet tab.

2. In the Print section of the dialog box, click in the Comments box and select the required option.

3. Click OK.

Reviewing toolbar

Editing data

To edit in a cell:

1. Select the cell to be changed, then either double-click in the cell or press F2 to edit it.

2. Notice that the cursor appears in the cell, ready for editing.

3. On completion press Enter or click on the green tick mark in the Formula bar to update the changes.

4. To cancel your change press Esc or click on the red cross on the Formula bar. [pic]

Clipboard

In Excel 2002 and 2003 you can copy up to 24 items at a time onto the clipboard and paste them into any Microsoft Office application.

To display the Clipboard, select Edit|Office Clipboard or press Ctrl+C twice. The Clipboard task pane will be displayed on the right of your screen.

Use the Option button on the Clipboard to set the following options:

Insert paste

Method one

1. Select the cells you want to move or copy.

2. Point to the border of the selection.

3. Hold down Shift (if moving) or Shift+Ctrl (if copying) as you drag. You’ll see a fuzzy line that you can position horizontally (between rows) or vertically (between columns).

4. Release the mouse, and then the Shift (+Ctrl) key.

Your cells will jump to their new location.

Method two

1. Select the cells you want to move or copy.

2. Select Cut or Copy in the normal way.

3. Right-click where you want to move or copy the data to, and from the shortcut menu, select Insert Copied Cells.

Paste Special

The Paste Special option gives you the ability to choose what should be pasted into the destination cells. For example, you may want to paste data without its formats, or you might want to convert a formula to a static value – Paste Special gives you the choice.

You can also use Paste Special to carry out basic arithmetical operations (add/subtract/divide or multiply) on the copied cells and the destination cell data.

1. Select the cells you want to copy.

2. Right-click anywhere within the highlighted block and choose Copy from the shortcut menu.

3. Click the right mouse button over the cell where you want the copied data to appear.

Helpful hint:

If you have copied a block of cells, the cell you click before you paste is the cell to which you want the top-left cell in the copied block to move.

4. Choose Paste Special. The following dialog box will appear:

5. Select an option in the Paste section of the dialog box to choose what will be pasted into the destination cells.

6. Select an option from the Operation section of the dialog box to choose the mathematical operation you want to perform on the copied data.

7. Check the Skip Blanks box to stop empty cells being pasted.

8. Click OK to close the dialog box and paste the data.

Transpose data

If you need to change the way data is stored in a worksheet, i.e., you may want to display rows as columns, or vice versa, you can use the Transpose option. The transposed version of the data cannot overlap the original data, i.e., you need to paste it into another area of the worksheet.

Printing

Using the Page Setup dialog box you can:

• Print gridlines.

• Print column and row heading.

• Repeat the heading on every page.

• Centre horizontally and/or vertically on the page.

• Adjust the size of your print out.

Page break preview

When printing large worksheets select View|Page Break Preview to see where your page breaks will occur. You can drag blue lines to change the page breaks.

Select View|Normal to return to the normal screen.

Formatting

Shortcut keys for formatting

Excel has some shortcut keys that can be used for formatting data. See the list in the table below:

|To |Keystrokes |

|Display the Cells command (Format menu). |Ctrl+1 |

|Apply the General number format. |Ctrl+Shift+~ |

|Apply the Currency format with two decimal places (negative numbers appear in parentheses). |Ctrl+Shift+$ |

|Apply the Percentage format with no decimal places. |Ctrl+Shift+% |

|Apply the Exponential number format with two decimal places. |Ctrl+Shift+^ |

|Apply the Date format with the day, month and year. |Ctrl+Shift+# |

|Apply the Time format with the hour and minute, and indicate am or pm. |Ctrl+Shift+@ |

|Apply the Number format with two decimal places, 1000 separator, and minus (–) for negative values. |Ctrl+Shift+! |

|Apply the outline border. |Ctrl+Shift+& |

|Remove all borders. |Ctrl+Shift+_ |

|Apply or remove bold formatting. |Ctrl+B |

|Apply or remove italic formatting. |Ctrl+I |

|Apply or remove an underline. |Ctrl+U |

|Apply or remove strike-through formatting. |Ctrl+5 |

|Hide rows. |Ctrl+9 |

|Unhide rows. |Ctrl+Shift+9 |

|Hide columns. |Ctrl+0 |

|Unhide columns. |Ctrl+Shift+0 |

Format painter

The format painter tool can be used to copy all formats from one area of a worksheet to another.

1. Select the cell that has the formatting you want to use.

2. Click the Format Painter button on the Standard toolbar. Your mouse pointer will change to display a paintbrush next to the selection pointer.

3. Select all the cells to which you want to apply the formats by dragging over them. As soon as you release the mouse, the formats will appear.

If you want to apply the format to several separate cells, double-click the Format Painter icon, then click in the cells you want to apply the formats to. Click on the Format Painter icon again to turn it off.

Conditional formatting

You can make Excel format cells in certain ways according to whether their data meet a specified condition. For example, in a table of numbers, you might want to display numbers over 100 in red, numbers below 50 in green and anything in-between in blue. You can use Conditional Formatting on the Format menu to achieve this.

Clear formats

If you want to keep the cell contents, but remove all the formatting from those cells, on the Edit menu, select Clear then Formats.

Formulae and functions

Precedence of operators

Terms of precedence refers to the order in which Excel performs calculations in a formula. Excel evaluates operators following the conventional rules – it will apply the calculations in a formula in the following order:

BODMAS: Brackets Of Division Multiplication Addition Subtraction

Example:

▪ Expressions within parentheses are processed first, e.g. 10*(6+2)=80

▪ Multiplication and division are performed before addition and subtraction, e.g. 6+2*10=26

▪ Consecutive operators with the same level of precedence are calculated from left to right.

Take care to observe these rules when creating your own formulae.

Incorrect syntax will result in error.

Displaying and printing formulae

It is possible to display formulae on a worksheet rather than the results of the formulae. This can be useful if you need to check for errors.

1. From the Tools menu, select Options and click on the View tab.

2. Under Window Options, select Formulas and click OK.

3. If necessary, print the worksheet as normal. You may need to make the columns wider to show the formula. Print on landscape paper if necessary.

To set the worksheet back to normal, repeat steps 1 and 2 but deselect the Formula option.

Or

To switch between showing the formulae in cells, and showing their results, press Ctrl+` (the key to the left of 1).

Press Ctrl+` to return to the value.

Converting formulae to values

Replacing a formula with its value permanently removes the formula. If you accidentally replace a formula with a value and want to restore the formula, click Undo Entry on the Edit menu (or press Ctrl+Z) immediately after entering or pasting the value.

To convert a formula permanently, in a single cell, to its resulting value:

1. Double-click the cell that contains the formula you want to replace with its value.

2. Press F9.

3. Press Enter.

To convert formulae permanently, in a range of cells, to their resulting values:

1. Select the cells that contain the formulae you want to replace with their resulting values.

2. Copy the cells to the Clipboard.

3. On the Edit menu, click Paste Special.

4. Under Paste, select the Values check box.

Displaying zero values

If you would prefer cells with calculations that amount to zero to be left blank, rather than displaying a 0, deselect the Zero Values option as follows:

1. From the Tools menu, select Options and click on the View tab.

2. In the Windows Options section of the dialog box, deselect the Zero Values option.

Functions

AutoSum [pic]

The AutoSum feature allows you to add all numbers in a contiguous row or column. To use AutoSum:

1. Click a cell below the column of numbers or to the right of the row of numbers.

2. Click the AutoSum button [pic] on the Standard toolbar, and then press Enter.

Excel automatically guesses the range of cell references that you wish to sum (these can be amended if necessary).

Helpful hint:

Always check automatically generated formulae before accepting them, as Excel doesn’t always guess correctly.

Functions on status bar

When a range of numeric cells are selected the sum of those cells is automatically displayed on the right of the status bar.

By right-clicking on the status bar you can change the function to Average, Count, Max or Min.

Working with large worksheets

Split screen

The visible worksheet area is relatively small. If the data you are working with spans a large number of columns and rows, you may find it difficult to move and copy information between areas, or even to view data in non-adjacent columns or rows on the same screen. Splitting the screen gives you the ability to scroll the data on one side of the split independently of the data on the other side – so you could be viewing cells A1 – G16 on one side of your screen, and cells M50 – Z76 on the other.

Splitting the screen horizontally

1. Position the mouse along the top edge of the upward pointing arrow at the top of the vertical scroll bar – your pointer should display as double headed arrow.

2. Drag down – you will see a grey bar that follows your mouse down. Release the mouse when the line is at the position you want to split the screen.

Or Select Window|Split. Drag the bars to the required position.

Splitting the screen vertically

1. Position the mouse along the top edge of the upward pointing arrow at the right of the horizontal scroll bar – your pointer should display as a double headed arrow.

2. Drag left – you will see a grey bar that follows your mouse across. Release the mouse when the line is at the position you want to split the screen.

Or Select Window|Split. Drag the bars to the required position.

When the screen is split, you get scroll bars in each horizontal and/or vertical section of your window. These can be used to move the display for that particular section.

Removing the split

Double-click on the grey split bar.

Or Select Window|Remove Split.

Freeze panes

When you want certain rows or columns to remain static on screen while you scroll down or across data on a worksheet, you can use Freeze Panes.

1. Select the cell below and to the right of the cells you want to freeze.

2. Select Window | Freeze Panes. You will see solid lines appear below and to the right of the frozen rows and columns.

If you only want the freeze for rows, select the cell in column A below the rows you want to freeze. If you only want the freeze for columns, select the cell in row one to the right of the columns you want to freeze.

Unfreeze panes

Select Window|Unfreeze Panes. If you have both columns and rows frozen, the Unfreeze command removes both.

Zoom

If you need all your columns to fit onto one screen, select them, and then from the View menu select Zoom to access the zoom dialog box.

Choose the Fit Selection option and click OK. Your screen will be scaled so that the selected columns are all visible without scrolling.

Custom Views

Creating Custom Views allows you to save different display and print settings, and apply them quickly and easily to the worksheet at any time.

The settings, which can be saved, include print settings, row heights and column widths, display settings, selected cells, window size and positions, and settings for panes and frozen titles. This can be advantageous when dealing with large worksheets where switching from one area to another might otherwise be awkward. It also allows a number of different print settings – including the print area – to be saved as part of the same worksheet file.

Defining a view

Before defining a view, you should ensure that the display options, zoom percentage, print settings, etc., are as you wish to record them.

1. From the View menu select Custom Views. The Custom Views dialog box will appear:

2. From the Custom Views dialog box select Add.

3. Choose whether or not to include print settings or hidden rows and columns as part of the view by checking the required options.

4. Enter the name under which this view is to be saved and click OK.

Once a view has been defined the display and print settings of the worksheet can be changed (for example, you could hide rows and/or columns so that a chart is displayed next to its data with an appropriate header when printing). You could then set up a view that would save those settings.

Showing a view

Having defined as many views as are needed for the current worksheet, you can switch between them.

1. Select Custom Views from the View menu, and then highlight the name of the desired view.

2. Click on the Show button. If the Print or Print Preview commands are executed, the correct settings (including the header) will be applied to each view.

Deleting a view

1. Select Custom Views from the View menu, and then highlight the name of the view you want to delete.

2. Click the Delete button.

Filtering data

AutoFilter

When you use the AutoFilter command, drop-down list arrows are displayed next to each of the column labels in the list. When you open a drop-down list, a list of all the unique entries for that column is displayed. By selecting one of the entries from the drop-down list, called a filter criterion, you instruct Excel what to search for. Excel then filters the list so that only the sets of data that contain the entry you selected will be displayed.

To apply an AutoFilter:

1. Place the active cell anywhere within your list.

2. From the Data menu, select Filter, then AutoFilter. Your list column labels will appear with drop-down list arrows to the right.

3. Click the drop-down arrow for the column by which you want to filter to display the unique values from that column. Select the value you are filtering for.

4. Repeat step 3 until you have set filter criteria for all columns that you wish to filter by.

5. The list will show only those rows that match your criteria.

If you print the worksheet whilst a filter is active, only visible rows will be output, so you can print out multiple views of your data from a single list.

To remove a single column filter:

1. You can see which columns have filter criteria active because the drop-down list arrows are blue. Click the drop-down list arrow for the column whose criteria you wish to remove.

2. Select All from the top of the list.

To remove all column filters:

From the Data menu, select Filter and then Show All.

Working with multiple workbooks

Create workspace files

You can open a group of workbooks in one step by creating a workspace file. A workspace file saves information about all open workbooks, such as their locations, window sizes, and screen positions. When you open a workspace file by using the File|Open command, Excel opens each workbook saved in the workspace. The workspace file does not contain the workbooks themselves, and you must continue to save changes you make to the individual workbooks.

To create a workspace file:

1. Open the workbooks you want to be able to open as a group.

2. Size and position the workbook windows as you want them to appear the next time you use the workbooks.

3. Select File|Save workspace.

4. In the File name box, enter a name for the workspace file.

To open the workbooks automatically each time you start Excel, save the workspace file in a folder called XLStart. On WTS this can be found in your P:/Microsoft Office/Office10 folder. On a stand-alone machine this can usually be found in the C:/Program files/Microsoft Office/Office10 folder.

Save only the workspace file, not the workbook files, in the XLStart folder.

Close all files

When you have several files open, you can close them all by holding down the Shift key as you click File from the menu bar. Choose the Close All option to close all currently open files.

Grouping worksheets

Selecting a worksheet

Click on the sheet tab to select a sheet.

Selecting multiple adjacent worksheets

Click on the sheet tab of the first sheet you want to select.

[pic]

Hold down the Shift key and click on the last sheet tab in the required selection. All the sheets between your first and last click are selected.

Selecting multiple non-adjacent worksheets

1. Click on the sheet tab of the first sheet you wish to select.

2. Hold down the Ctrl key and click on the next sheet you wish to select. Repeat this procedure until all sheets you require have been selected.

After you select a group of worksheets, such as those illustrated above, the word Group appears in the title bar of the workbook.

Deselecting the group

Click on any sheet tab.

Or

Right-click to reveal the sheet tab shortcut menu and select Ungroup Sheets.

Entering data in multiple worksheets

Once you have selected more than one sheet, it is possible to enter the same data in all of the selected sheets simultaneously. This is helpful for setting up several sheets which will store similar information.

1. Select the sheets into which you wish to enter the data. (Notice the word Group appears in the title bar.)

2. Enter your data into the cells as normal.

3. Click on any tab to deactivate group mode.

4. Once the data have been entered, you can click on any of the sheets to see the updated contents.

WARNING: Ensure the cell(s) in the area of the grouped worksheets in which you are about to enter data are blank. Any data in these cells will be overwritten without warning.

Entering formulae

As with data entry, if the sheet is laid out in the same way, it is possible to enter formulae into several sheets at once.

1. Select the sheets into which you wish to enter the formula.

2. Enter the formula into the cell on the top sheet and copy the formula to the required cells as normal.

3. Once the formulae have been entered click on any tab to deactivate group mode.

4. Click in any of the sheets to see the formulae updated.

Fill data across worksheets

You can copy data to the same position on multiple sheets using the Fill command. This is particularly useful if you need to decide what gets copied (everything, or just the formats). It also saves time on occasions when you accidentally deactivated group mode, typed your entries and then realised that they are only on one sheet!

1. Select the cells you want to copy to the other worksheet(s).

2. Select the worksheets you want the copies to appear on by clicking the sheet tabs (use Shift to block select or Ctrl to pick non-adjacent sheets).

3. Select Edit|Fill|Across worksheets.

4. The following dialog box will appear:

5. Choose the appropriate option and click OK.

When you have finished filling the data, remember to deactivate group mode!

Charts

Create a quick chart

The quickest way to create a chart is to:

1. Click anywhere in the data from which you want to create a chart.

2. Press F11.

A column chart will have been created automatically on a separate worksheet.

Change the default chart format

The default chart format for Excel 2002 and 2003 is a column chart with a legend. Although you can modify this format after you create the chart, you can often save time by changing the default chart format, if you routinely use a different format. You can use either the format of the active chart or another format you've already created as the new default format.

Make the format of an active chart the default chart format

1. Select the chart and then choose Chart Type from the Chart menu.

2. Click the Set as Default Chart button.

3. Click Yes to confirm and then click OK.

Choose a different default chart format

1. Select the chart and then choose Chart Type from the Chart menu.

2. Select the Type and/or Sub Type required.

3. Click the Set as Default Chart button.

4. Click Yes to confirm and then click OK.

The formats listed include the built-in formats. Any custom formats you've added will be shown on the Custom Types tab.

Additional information

The following pages contain some additional tips and tricks that advanced Excel users may find useful.

Charts

Create a chart from visible worksheet data

You can create and update charts that plot only visible data on your worksheet. For example, you can hide rows and columns or use AutoFilter to display only part of your data, and then you can create a chart from only the rows or columns displayed.

1. If the chart is embedded in the worksheet, double-click it. If the chart is on a separate sheet, click the Chart sheet tab.

2. Select Tools|Options, then click on the Chart tab.

3. Select the Plot Visible Cells Only check box.

Helpful hint:

To keep your chart from being updated whenever you hide or display data, clear the Plot Visible Cells Only check box.

Picture fill effects

You can use pictures to enhance the appearance of your data series.

1. From the Picture tab, click the Select Picture button.

2. Locate the picture you want to use and click Insert.

3. You can stretch the picture to fill the data series, stack the picture, or, if you want the picture stacked according to the scale, select the last button in the Format area of the dialog box.

4. Specify how many pictures per unit you want.

5. Click OK.

Unattached text

Floating text may be typed directly onto the chart, and then dragged to the desired position.

To add floating text to a chart:

1. With your chart selected, type the text you want to see displayed on it and press Enter. (The text will initially appear in the formatting bar.)

2. Move the text to the desired location by clicking and dragging it.

Validation

Restrict cell entries to the data from a list

You can restrict entry in a cell by creating a list (somewhere else on the worksheet) and only allowing entries from the list.

1. On the same worksheet, type the list of valid entries down a single column or across a single row. Do not include blank cells in the list.

2. Select the cells that you want to restrict.

3. From the Data menu, select Validation, and then click the Settings tab.

4. In the Allow box, select List.

5. In the Source box, enter a reference to your list of valid data.

▪ If you want to select from a list of the entries when you click into a restricted cell, make sure the In-cell drop-down check box is selected.

▪ To specify that the entry is not valid if the restricted cells or the cells that contain your list of valid data are blank, clear the Ignore blank check box.

▪ To display messages to prompt for entries and to explain or prevent incorrect entries, specify the types of messages you want on the Input Message and Error Alert tabs.

▪ To suppress messages, clear the Show input message when cell is selected check box on the Input Message tab, and clear the Show error alert after invalid data is entered check box on the Error Alert tab.

When the cell with the validation rule is selected, a drop-down arrow appears. Clicking on it displays the list of options.

Helpful hints:

If the list of valid entries is short, you can type them in the Source box, instead of typing the entries on a worksheet, separated by the Windows list separator character (comma by default). For example, you could type Yes, No, in the Source box instead of entering the two words on a worksheet.

If the list of valid entries might change, name the list range, and then enter the name in the Source box. When the named range grows or shrinks because of changes you make to the list on the worksheet, the list of valid entries for the cell automatically reflects the changes.

If the list of valid entries is in another worksheet or workbook, define a name for the external data on the active worksheet. In the Source box, you can then refer to the name on the same worksheet. For example, if the list of valid entries is in cells A6:A12 on the first worksheet in a workbook named Budget.xls, you could define the name ValidData on the active worksheet to be =[Budget.xls]Sheet1!$A$6:$A$12 and then enter =ValidData in the Source box.

Auditing features

Using the auditing tools you can examine the relationship between cells and formulae on your worksheet and identify errors. When you use the auditing tools, tracer arrows point out cells and provide data to formulae (the precedents) and the cells that contain formulae that refer to the cells (the dependents).

A box is drawn around the ranges of cells that provide data to formulae.

Auditing toolbar

When you use the buttons on the Auditing toolbar, tracer arrows point to cells referred to by formulae. If those cells contain formulae, click the button again to see the next level of cells that provide data to the formula.

To display the Auditing toolbar:

From the Tools menu, point to Auditing, and then select Show Auditing Toolbar.

Precedents and dependents

Precedents are cells referred to by other formulae. Dependents are cells containing formulae that refer to other cells.

▪ A direct precedent is a cell referred to by the formula in the active cell. An indirect precedent is a cell referred to by a formula in a direct precedent cell or another indirect precedent cell.

▪ A direct dependent is a cell containing a formula that refers to the active cell. An indirect dependent is a cell that contains a formula that refers to a direct dependent cell or another indirect dependent cell.

Tracer arrows

The precedent, dependent and error tracers display arrows on the worksheet to represent the flow of computations:

Blue tracer arrows show cells that provide data to the formula.

Red arrows point to formulae that cause error values.

Helpful hint:

Before using the auditing tools, check that the Hide All check box on the View tab in the Options dialog box is NOT selected.

Auditing for direct and indirect precedents

1. Click on the cell you want to audit.

2. Click the Trace Precedents button on the Auditing toolbar.

3. To identify the next level of cells that provide data to the active cell, click the Trace Precedents button again, and so on.

Auditing for direct and indirect dependents

1. Click on the cell you want to audit.

2. Click the Trace Dependents button on the Auditing toolbar.

3. Click the button a second time to view the indirect dependents of the active cell.

Removing the tracer arrows

Click the Remove Precedent Arrows button to remove precedent tracers, [pic]

or the Remove Dependant Arrows to remove dependant tracers, [pic]

or the Remove All Arrows icon to remove both precedent and dependent tracers. [pic]

Trace errors

If a formula displays an error value, such as #DIV/0!, you can use the Tracer Error icon to locate all cells referenced by the formula. Red arrows point to formulae that cause error values, blue arrows point to cells that contain values creating the error.

1. Select the cell that contains the error.

2. Click the Trace Error icon on the Auditing toolbar.

or

Select Tools|Auditing|Trace Error.

To select the cell at the other end of an arrow, double-click the arrow.

Trace data to another workbook

If the reference you are tracing is in another workbook, you must first have that workbook open.

1. Double-click the arrow that displays and points to the active cell.

2. In the Go To box, click the reference you're tracing and click OK to go to the precedent or dependent cell.

Set default options

You can set options in Excel to control what appears on screen, how Excel edits data and also to allow you to use some Lotus functionality within the Excel environment.

From the Tools menu select Options. The following dialog box will appear:

General tab

Recently used file list

Select this option if you want your most recently used files listed at the bottom of the File menu.

Set the number of files you want listed. The default is four.

Sheets in new workbook

Select the number of sheets for future new workbooks by clicking the up and down arrows to increase or decrease the figure. The default is three.

Font and Size

Select a standard font and size for all new workbooks by selecting a font and point size from the list boxes.

Default file location

Specify which folder you want Excel to default to when you open and save files, by typing a location.

View tab

Show

▪ If you want the task pane open whenever you start Excel, ensure the Start-up Task Pane option is ticked.

▪ If you wish each document to be shown as a separate window on the Windows Taskbar, check the Windows in Taskbar box.

Comments

If you are using comments, ensure the Comment indicator only box is checked so that red indicators appear in the cells containing cell notes.

Window options

▪ To display page breaks that have been set automatically by Excel, select the Page breaks check box, and click OK.

▪ You can change the colour of the Gridlines, or remove them all together, by using the Window options at the bottom of the dialog box.

▪ By default, row and columns headers are displayed in Excel worksheets. To switch then off, deselect the Row & column headers check box, and then click OK.

▪ If you would prefer cells with calculations that amount to zero to be left blank, rather than displaying a 0, deselect the Zero values option.

▪ Sheet tabs are normally displayed at the bottom of the worksheet. They can be switched off by deselecting the Sheet tabs check box. If your workbooks only contain one worksheet, you do not need to display sheet tabs. When the sheet tabs are not displayed, the horizontal scroll bar will run right across the bottom of the worksheet.

Learning more

Central IT training

Information Systems run courses for UCL staff, and publish documents for staff and students to accompany this workbook as detailed below:

|Getting started with Excel |This 3hr course is for those who are new to spreadsheets or to Excel, and wish to explore the |

| |basic features of spreadsheet design. Note that it does not cover formulae and functions. |

|Getting more from Excel (no formulae or |This 3hr course is for users of Excel who wish to learn more about the non-mathematical features |

|functions) |of Excel and to work more efficiently. |

|Using Excel to manage lists |This 3hr course is for those already familiar with Excel who would like to use some of its basic |

| |data-handling functions. |

|Excel formulae and functions |This 3hr course is aimed at introducing users, who are already familiar with the Excel |

| |environment, to formulae and functions. |

|More Excel formulae and functions |This 3.5hr course is aimed at competent Excel users who are already familiar with basic functions |

| |and would like to know what else Excel can do and try some more complex IF statements. |

|Advanced formulae and functions |This 3.5hr course is aimed at competent Excel users who are already familiar with basic functions.|

| |It aims to introduce you to functions from several different categories so that you are equipped |

| |to try out other functions on your own. |

|Excel statistical functions |This course aims to introduce you to built-in Excel statistical functions and those in the |

| |Analysis ToolPak. The course covers major descriptive, parametric and non-parametric measures and |

| |tests. |

|Excel statistical formulae |This course covers best practise in constructing complex statistical formulae in spreadsheets |

| |using common statistical measures as example material. |

|Excel tricks and tips |This is a 2hr interactive demonstration of popular Excel shortcuts. It aims to help you find |

| |quicker ways of doing everyday tasks. This fast-paced course is also a good all-round revision |

| |course for experienced Excel users. |

|Pivot tables |Pivot tables allow you to organise and summarise large amounts of data by filtering and rotating |

| |headings around them. This 2hr course also shows you how to create pivot charts. |

|Advanced Excel – Data analysis tools |This course aims to help you learn to use some less common Excel features to analyse your data. |

|Advanced Excel – Setting up and automating |Would you like to customise and automate Excel to perform tasks you do regularly? If you are an |

|Excel |experienced user of Excel, then this course is for you. |

|Advanced Excel – Importing data and sharing |Do you share workbooks with others? Would you like to see who has updated what? Do you know how to|

|workbooks |import data from text files or databases? This course aims to show you how. |

These workbooks are available at the Help Desk.

Open Learning Centre

• The Open Learning Centre is open every afternoon for members of staff who wish to obtain training on specific features in Excel on an individual or small group basis. For general help or advice, call in any afternoon between 12:30pm – 5:30pm Monday – Thursday, or 12:30pm – 4:00pm Friday.

• If you want help with specific advanced features of Excel you will need to book a session in advance at: ucl.ac.uk/is/olc/bookspecial.htm

• Sessions will last for up to an hour, or possibly longer, depending on availability. Please let us know your previous levels of experience, and what areas you would like to cover, when arranging to attend.

• See the OLC Web pages for more details at: ucl.ac.uk/is/olc

Online learning

There is also a comprehensive range of online training available via TheLearningZone at: ucl.ac.uk/elearning

Getting help

The following faculties have a dedicated Faculty Information Support Officer (FISO) who works with faculty staff on one-to-one help as well as group training, and general advice tailored to your subject discipline:

• Arts and Humanities

• The Bartlett

• Engineering

• Life Sciences

• Maths and Physical Sciences

• Social and Historical Sciences

See the faculty-based support section of the ucl.ac.uk/is/fiso Web page for more details.

A Web search using a search engine such as Google (google.co.uk) can also retrieve helpful Web pages. For example, a search for Excel tutorial would return a useful selection of tutorials.

-----------------------

Split screen button

[pic]

[pic]

[pic]

[pic]

[pic]

[pic]

[pic]

[pic]

[pic]

[pic]

[pic]

[pic]

Split screen button

[pic]

[pic]

[pic]

[pic]

[pic]

Mouse pointer when positioned over fill handle

[pic]

Fill handle

[pic]

[pic]

[pic]

[pic]

[pic]

Freeze lines

Click on the first sheet tab and, whilst holding down the Shift key, click on the last sheet tab.

Click on each sheet while holding down the Ctrl key.

New

comment

Previous

comment

Create

Outlook task

Next

comment

Show

comment

Show all

comments

Update

file

Delete

comment

Send to mail

recipient as

attachment

[pic]

[pic]

[pic]

[pic]

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download