|
Excel Tips |
| What is Excel | |
*The plain arrow buttons at the bottom of the window scroll the worksheet tabs one at a time.
· The arrows with vertical lines on their points scroll directly to the first or last tab.
Explanation:
An Excel file is called a workbook. When you load Excel, it displays the first sheet of the
workbook. A worksheet is one page of a workbook and consists of a grid of rows and
columns. Each workbook consists of 16 worksheets by default, but you can have up to 255
worksheets in a workbook file. Worksheets contain 256 columns and 16,384 rows.
|
|
The Excel Screen:
· The intersection of a row and a column is called a cell.
· Each cell has an address beginning with the column heading and then the row number.
· The first cell in a worksheet is cell A1.
· The active cell is indicated by a heavy border around it.
· The mouse pointer is shown on the worksheet as a thick cross.
· A row of tabs at the bottom of the window lets you select the active worksheet.
· A cell reference box, located under the Formatting toolbar, indicates which cell is active and the contents of that cell.
Explanation:
A workbook is automatically created when you start Excel and it is named Book1 by default.
Subsequent new workbooks that you open during an Excel session are labeled Book2, Book3,etc. You can have multiple workbooks open, each in their own window.
Moving, Copying and Renaming Sheets:
Tips and Shortcuts:
· It is a good idea to Rename all of the worksheets that contain data.
· Select a worksheet and click with the right mouse button to get a shortcut menu.
· Double-click a worksheet tab to Rename it.
· You can select more than one worksheet, by using Shift or Ctrl, to Move or Copy.
To insert a sheet into your workbook:
1. Excel will insert a new worksheet to the left of which ever worksheet is currently selected.
2. Select the Insert menu, the choose Worksheet.
To remove a worksheet from a workbook:
1. Place the cursor on the sheet tab.
2. Right Click. On the shortcut menu choose Delete.
To copy a worksheet within the workbook:
1. Select the worksheet you want to copy.
2. Hold down
the Ctrl key while dragging the sheet tab to the location for insertion. A black
triangle appears above
the receiving tabs.
3. Release the
mouse button. A copy of the worksheet is inserted in the new location. The name
of the copy is the original
worksheet name plus (#).
If Sheet 4 is selected, Excel will insert a new worksheet between Sheet 3 and Sheet 4.
To move a worksheet within the workbook:
1. Click and hold on the worksheet you want to move.
2. Drag the sheet tab to the location for insertion. A black triangle appears above the receiving tabs.
3. Release the mouse button. The worksheet is inserted in the new location.
To rename a worksheet:
1. Double-click the worksheet you to rename.
2. Type in the new name.
Tips and Shortcuts:
· To enter or edit data, point and click on a cell to make it active.
· Selected cells are highlighted. In a range of cells, all cell are highlighted with the active cell white with a bold border.
· Click on the row number to select a row.
· Click on the column heard to select a column.
· The button in the upper left corner of the worksheet will select the entire worksheet.
To select a range of cells:
1. Click the cell at one corner of the range.
2. Drag to the opposite corner of the range and release the mouse button.
To select a large area range of cells:
1. Click the cell at one corner of the range.
2. Scroll
the window so that the opposite corner appears. (Do not click in the worksheet,
the original corner must remain
active.)
3. Hold down the Shift key as you click the opposite corner. All cells between the two corners are selected.
To edit existing data in a cell:
1. To completely replace the contents of a cell, select the cell and begin typing. Your new text will overwrite the previous text.
2. To edit a small portion of the text in a cell, select the cell and then click in the Formula Bar to edit.
To enter data in a cell:
1. Select an individual cell.
2. Type the data and press Enter.
To enter data in a range of cells:
1. Select a range of cells.
2. Type the
data into the active cell and press Enter. The next cell in the range
automatically becomes the active cell.
3. When you
reach the lowest cell in the range, the selection point automatically moves to
the top of the next column
within the range, or if you are at the end of the range, back
to the first cell.
To enter duplicate data in a range of cells:
1. Select the range of cells you want to enter duplicate data.
2. Enter the data into the active cell.
3. Press Ctrl + Enter.
4. The data is entered into all of the cells in range.
To enter duplicate data using click and drag:
1. Type the data in the first cell.
2. Move the
mouse, with the first cell still selected, to the handle at the lower right
corner
of the cell, until the pointer becomes a crosshair.
3. Click and hold the left mouse button.
4. Drag to
fill the desired cells.
Formula Bar
To create a series:
1. Type the data in the first cell.
2. Press Enter or Tab.
3. Type the next increment in the next cell.
4. Select both cells.
5. Click on the handle in the lower right corner of the selection and drag to fill all of the desired cells.
To have text automatically wrap within a cell:
1. Select the cell(s).
2. Select Format the Cells...
3. Click on the Alignment tab.
4. Select the Wrap Text option.
5. Click on OK.
Tips and Shortcuts:
· A cell filled with ### signs indicates the cell is not wide enough for the number.
· To enter a fraction, you must type an integer, a space and then the fraction.
· Negative numbers can be preceded by a minus sign, or be enclosed within parentheses.
· Numeric entries are formatted to the right within a cell.
· Numbers can be formatted as currency ($), a percentage (%), or separated by commas (,) by clicking on [$], [%], or [,] in the toolbar.
To reformat a number:
1. Select the cell or range of cells containing the number(s) to be reformatted.
2. Select Format Cells...
3. Click on the Number tab.
4. Select a Category and a Format Code.
5. Click on OK.
To change the number of decimal places:
1. Click on the Decrease Decimal button.
Tips and Shortcuts:
· A cell filled with ### signs indicates the cell is not wide enough for the number.
· To enter a fraction, you must type an integer, a space and then the fraction.
· Negative numbers can be preceded by a minus sign, or be enclosed within parentheses.
· Numeric entries are formatted to the right within a cell.
· Numbers can be formatted as currency ($), a percentage (%), or separated by commas (,) by clicking on [$], [%], or [,] in the toolbar.
To reformat a number:
1. Select the cell or range of cells containing the number(s) to be reformatted.
2. Select Format Cells...
3. Click on the Number tab.
4. Select a Category and a Format Code.
5. Click on OK.
To change the number of decimal places:
1. Click on the Decrease Decimal button.
Tips and Shortcuts:
· Press Ctrl + ; to enter today’s date in a cell.
· Press Ctrl + : to enter the current time in a cell.
· Dates and times can be entered into the same cell as long as they are separated by a space.
To make a series of months:
1. Type a month into a cell.
2. Select the cell and drag down or across to fill the desired cells with successive months.
Tips and Shortcuts:
· The quickest way to clear the contents of a cell is to select the cells, and press the Delete key.
· Right-click on a cell to get a shortcut menu.
· Do not use Edit Delete to remove the contents of a cell. This will remove the actual cell from the worksheet like removing a brick from the wall.
4 Excel gives you choices when clearing cell contents. You can clear everything in acell or range, erase the format only, erase the formulas only, or erase the notes only.
To clear the contents of a cell:
1. Select the cell or range of cells you want to clear.
2. Right-click the mouse button on top of the highlighted cells. A shortcut menu pops up.
3. Select Clear Contents. Only the contents are deleted, formats and notes remain.
4. If you want to clear other cells immediately after this, you can select the other cell(s) and press F4 to repeat the Clear Contents command.
5. If you accidentally clear the contents of a cell, press Ctrl + Z to undo the last action.
To choose the part of the cell to clear:
1. Select the cell or range of cells you want to clear.
2. Choose the Edit Clear command. A Clear cascading menu appears.
3. Select the command that describes what you want cleared.
Tips and Shortcuts:
· Press Ctrl + X to cut the contents of a selected cell.
· Press Ctrl + C to copy the contents of a selected cell.
· Press Ctrl + V to paste the contents of a cut or copied cell to the selected cell.
· Once you have selected a cell, you can point to the edge of the selection until you get an arrow, click and drag it to a new location. If you hold down Ctrl while dragging, you copy the contents instead of moving them.
4 When you cut or copy a cell, you cut or copy any formulas it may contain. You can copy or paste to another cell on the same worksheet, or another sheet in the workbook.
To cut contents of a cell to the clipboard:
1. Select the cell or range of cells containing the contents to be cut.
2. Press Ctrl + X. A “dancing-ants” border will appear around the cells.
3. Select the cell or range of cells to place the cut contents
4. Press Ctrl + V to paste.
To copy contents of a cell:
1. Select the cell or range of cells containing the contents to be copied.
2. Press Ctrl + C. A “dancing-ants” border will appear around the cells.
3. Select the cell or range of cells to place the copied contents.
4. Press Ctrl + V to paste.
Insert and Delete Cells, Rows and Columns:
Tips and Shortcuts:
· Highlight rows or columns and right-click the mouse to quickly insert or delete rows or columns.
· To insert or delete cells, right-click the mouse to get a shortcut menu.
· Press Ctrl + - to quickly delete cells, rows, or columns.
· Press Ctrl + + to quickly insert cells, rows, or columns.
· Click row or column headings to select the entire row or column.
· Press Shift + Space Bar to select a row.
· Press Ctrl + Space Bar to select a column.
Deleting cells, rows, or columns removes the cells and their contents from the
worksheet. Cells will shift up or to the left to fill in the deleted cells place.
To delete cells:
1. Select a cell or range of cells you want deleted.
2. Press Ctrl + - or right-click the mouse button and select Delete.
3. Select the direction you want the remaining cells to move.
4. Click on OK.
To delete rows or columns:
1. Click on the row or column headers to select the rows or columns you want to delete.
2. Press Ctrl + - or right-click the mouse button and select Delete.
To insert cells:
1. Select a cell or range of cells where you need new cells inserted.
2. Press Ctrl + + or click the right mouse button and select Insert.
3. Select the direction you want selected cells to move when blank cells are inserted.
4. Click on OK.
To insert rows, or columns:
1. Click on the row numbers or column headers to select rows or columns.
2. Press Ctrl + + or click the right mouse button and select Insert.
3. Rows will be inserted above the selected rows and columns will be inserted to the left of the selected columns.
Changing Column and Row Sizes:
Tips and Shortcuts:
· Double-click between columns to AutoFit the width to the longest entry.
· Double-click between rows to AutoFit the height to the largest font size in an entry.
· You can hide columns or rows, so that they don’t appear on the screen and they won’t print, by highlighting the column or row and selecting Format to Row Hide or Column Hide.
There are three ways to set a different column width or row height: 1) Select a
column or row and type in the width or height. 2) Have the width or height set
automatically based upon the longest or biggest entry. 3) Set a standard column
width or height for the entire worksheet.
To adjust column width:
1. Select cells in the columns that you want to change. Change multiple columns by
selecting a cell in each column.
2. Select Format®Column®Width.
3. Type in a specific width, or select the Format®Column®AutoFit Selection command tofit the column width to the widest cell contents in the selection.
4. Click on OK.
To adjust row height:
1. Select a cell in each row you want to change.
2. Select Format®Row®Height. The Row Height dialog box appears.
3. Enter the height in the Row Height box.
OR
Select Format®Row®AutoFit.
Click on OK.
To create a title:
1. Select cell A1.
2. Type the title in cell A1.
3. Highlight cell A1 across to the last column that will contain data.
4. Click on the Center Across Columns button.
Tips and Shortcuts:
· Use Ctrl + E to center selected text.
· Use Ctrl + L to left justify selected text.
· Use Ctrl + R to right justify selected text.
The Format Cells command allows you to change a number, alignment, font, border,
pattern, or protect cell contents.
To format the contents of a cell:
1. Highlight the cell or range of cells you want to format.
2. Select Format Cells. The Format Cells dialog box appears.
3. Click on the tab that describes how you want to format.
4. Select the changes you want to make.
5. Click on OK.
Tips and Shortcuts:
· To select the entire worksheet, click on the Select All button in the upper-left corner of the worksheet.
AutoFormats are a combination of number, alignment, column widths, row heights,
fonts, borders, patterns, and colors that will quickly turn a plain worksheet into a
professional looking product.
To apply the AutoFormat:
1. Highlight all of the cells you wish to AutoFormat, or use the Select All button to
highlight the entire worksheet.
2. Select Format®AutoFormat. The AutoFormat dialog box appears.
3. Select the type of format you want from the Table Format list. A sample of the
selected format is shown in the Sample box.
4. Click on OK.
Tips and Shortcuts:
· You can display the formulas on a worksheet instead of the results by selecting the Tools Options command, clicking on the View tab, selecting the Formulas option, and clicking on OK. Ctrl + ‘ is the shortcut for toggling between views.
· Double-click on a cell to enter a formula directly into the cell.
· Always separate terms in a formula with operators or parentheses.
· You can name cells or a range of cells. Once a name has been created, you can use it as part of a formula making the formula easier to understand. Select Insert Name and select Define.
Formulas always begin with an = sign and can include numeric and text values,
arithmetic operators, comparison operators, text operators, functions,
parentheses, cell references, and names.
There are three types of operators:
1) Arithmetic:
+ Addition
- Subtraction
/ Division
* Multiplication
% Percent
^ Exponentiation
2) Comparison
= Equal
> Greater Than
< Less Than
> Greater Than or Equal To
< Less Than or Equal To
<> Not Equal To
3) Text Operators
& Joins two or more text values into a single combined text value.
To enter a formula:
1. Select the cell to contain the formula.
2. Type an equal sign.
3. Type a value, cell reference, function or name.
4. If the formula is complete, press Enter. If the formula is incomplete, go to step
5. Type an operator.
6. Type a value, cell reference, function or name.
To automatically sum:
1. Select the empty row below, or empty column to the right of the numbers you want to sum.
2. Click the AutoSum button in the toolbar. The range of target cells will be highlighted.
3. Press Enter.
Linking Worksheets and Workbooks:
Linking enables one workbook to share the data in another workbook. If the
source worksheet is changed, the value at the destination will automatically be
updated.
To link cells or a range of cells:
1. Open the workbooks that you want to link.
2. Activate the source workbook or sheet.
3. Select the range of cells that provide the data you want linked.
4. Select Edit Copy or Press Ctrl + C.
5. Activate the target workbook to receive the data.
6. Select the top-left cell of the range where you want the link to appear. Do not select an entire range to paste into.
7. Select Edit Paste Special. The Paste Special dialog box appears.
8. Select the Paste All option and the Operation None option.
9. Select the Paste Link button.
Any changes made to calculated cells in the Source worksheet data will be changed in
the Target worksheet data.
Tips and Shortcuts:
· Press Ctrl + P to display the Print dialog box.
You can control what is printed and what the final results will be. Setup controls
allow you to set margins, adjust alignment, add or edit headers and footers, and
set the page orientation.
To set up what you want to print:
1. Select File Page Setup...
2. Select each tab in turn and select the print options you desire.
3. Click on OK.
To define a single print area:
1. Select File Page Setup.
2. Select the Sheet tab to display the Sheet options dialog box.
3. Place the insertion point in the Print Area text box.
4. Select the range of cells you want to print. (Drag the Page Setup dialog box out of the way if necessary.) Excel enters the cell coordinates in the Print Area text box as you select the printed area.
5. Click on OK.
To select multiple print areas:
1. Select File Page Setup.
2. Select the Sheet tab to display the Sheet options.
3. Place the insertion point in the Print Area text box.
4. Select the first area you want to print.
5. Type a comma in the Print Area text box after the cell coordinates and select the next area you want to print. Select areas in the order that you want them to print.
6. Repeat step 5 until you have selected all the areas you want to print.
7. Click on OK.
Tips and Shortcuts:
· Press Alt + L to move the insertion point into the left section of the Header dialog box.
· Press Alt + C to move the insertion point into the center section of the Header dialog box.
· Press Alt + R to move the insertion point into the right section of the Header dialog box.
· You can create multiple line headers or footers by pressing Alt + Enter to break a line.
You can create headers and footers that place a title, date or page number at the
top or bottom of each page. You can also format them with different fonts, styles
and sizes.
To create custom headers and footers:
1. Select File Page Setup.
2. Select the Header/Footer tab to display the header and footer options.
3. Click on the Custom Header button to create a customized header.
4. Click on OK.
5. Click Custom Footer to create a customized footer.
6. Click on OK.
7. Click on OK.
Print Preview
Tips and Shortcuts:
¨ The magnifying glass button allows you to zoom in and out of the document.
It is a good idea to preview your document before you actually print it.
To preview your document:
1. Select File Print Preview.
2. The Preview screen shows you how the page will look when printed.
3. Click on the Zoom button to zoom in and out of the document.
4. Click on the Next or Previous buttons to change pages in the Preview mode.
5. If you want to make changes, Click on the Setup button.
6. Click on the Print button to print, or click on the Close button to return to the
worksheet.
To adjust margins in Print Preview:
1. Select File Print Preview.
2. Click on the Margins button. Column and margin markers appear on the preview page.
3. Click on the Zoom button to zoom in or out of the preview.
4. Drag the margin handles (black squares) or the dotted line to a better position.
5. Drag column handles (black T’s) or the column gridline to adjust column widths.
6. Click Close to return to the document or click on Print to print the document with these
settings.