Faculty / Staff Resources

Title III Helpful Tips for MSExcel

Entering data

1. Select, or activate, the cell in which you want to display the data; use the mouse pointer to point to a cell and then click to select it, or use the arrow movement keys on your keyboard to select a cell.
2. Type data.
3. Enter the data into the cell by using any of these techniques: press the Enter key; click on the Enter button in the formula bar (the boxed checkmark); or press any of the keyboard movement keys, such as Right Arrow or Tab.

Replacing data entered into a cell

One method you can use to correct an item after it is entered into a cell is to retype the entry and enter it again. The new entry replaces the old entry.

Changing entries as you type

To change an entry before it is entered into a cell:

Press Backspace to delete individual characters
Press Esc or click on the Cancel button (the X in the formula bar) to clear the entire entry.

Saving a file or Updating a file

Until your file is saved it only exists in temporary storage. It is very important to save your work often. You can choose to save your work 1 of 3 ways:

• Click on “File” on the menu bar and then select save.
• Click on the disk icon on the Standard toolbar to save your document.
• Utilize a hot key – press the control button (Ctrl) and the “S” key on your keyboard at the same time. This is a quicker option.
  To update a previously save file, click on the Save button or choose file, Save.

Using Formulas

Formulas are instructions that you enter to perform calculations. It is preferable to construct formulas that refer to worksheet cells (A1 + b2). This way, the results of the formulas update automatically when you change numbers in your worksheet. In Excel, you create formulas by preceding the expression with an equal sign (=).

When you are building formulas, use:

+ for addition

- for subtraction

* for multiplication

/ for division

^ for exponents (for example 3^2 is 3 raised to the 2nd power)

Order of Operations – formulas

When you are creating formulas, you should keep in mind that there is a specific sequence that Excel follows when it performs calculations. This sequence is known as the order of operations.

• Parentheses: Computations enclosed in parentheses are first priority.
• Exponents: Computations involving exponents are performed next.
• Multiplication and division: These operations are performed next. Because they are “equal” with regard to the order in which they are performed, they are performed in the order in which they are encountered (from left to right)
• Addition and subtraction: These operations are performed last. They are also performed in the order in which they are encountered (from left to right).

Using Functions

Functions start with the equal sign (=) and generally two components:

The function name: ex. =SUM, =AVERAGE
The arguments, which are required data enclosed in parentheses
= Average(a4:a10)

Identifying cell contents

To view the true contents of a cell, select the cell and observe its contents in the formula bar.

Clearing cell contents

To remove the contents of a single cell, select the cell and press Delete; select the cell and choose Edit, Clear, Contents; or right click on the cell you would like to erase and choose Clear Contents from the shortcut menu.

Navigation techniques

• Press Home To move the active cell to column A of the current row.
• Press Tab To move the active cell to the right, one cell at a time.
• Press Ctrl + Home To move the active cell to A1 in the active worksheet.
• Press Alt +Pg Up
• or To move the active cell one screen to the left or right.
• Alt+ Pg Dn

Using Window, Freeze Panes

To keep certain rows at the top of your screen, select the cell in column A that is directly beneath the last row you would like to lock in place. Choose Window, Freeze Panes.

 

To keep certain columns at the left of your screen, select the cell in row 1 that is to the right of the last column you would like to lock in place. Choose Window, Freeze Panes to freeze columns of information.

Selecting a range of cells

With the mouse pointer, point to the center of the cell in one corner of the range; press and hold the mouse button while dragging to the opposite corner of the range; and release the mouse button.
To select a group of separate (nonadjacent) ranges that you want to affect at the same time, select the first range, press and hold the Ctrl key, and select the next range.

Inserting Rows and Ranges

To insert entire rows or columns, select one or more rows or columns, and choose Insert from the shortcut menu. You can delete columns and rows by choosing Delete from the shortcut menu.

Moving/Copying Data

Select the data; choose Edit, Cut or Copy; select the destination; and choose Edit, Paste. Note: The doted lines around the selection notify you there has been a selection made. If they do not disappear once you have completed your task then find an empty cell, click on it, and press Ctrl + D, to deselect the “ants”.

Copying formulas by using the fill handle

Located on the lower-right corner of the cell border. The mouse pointer changes into a thin +(plus sign). To copy the formula, move the mouse pointer on the fill handle and drag to select the range of cells.

Formatting - Working with nonadjacent cells

To select nonadjacent cells, hold down the Ctrl key as you click on the desired cells. After selecting the cells, apply the formatting of your choice.

Change font and font size

To change the font for a selected area, select a different font from the Font dropdown list on the formatting toolbar. You can also right click on the selected area to make font changes.

Rotating and Indenting Text

To rotate text, display the Format Cells dialog box, and select the Alignment tab. To indent text, use the Increase Indent or Decrease Indent button on the Standard toolbar, or use the Format Cells dialog box, select the Alignment tab, change the amount of indent in the Indent text box, and click on OK.’’

Removing cell formatting

Select the area and choose Edit, Clear, Formats from the main menu.

Adding borders and color to cells:

Select the cells that you want to affect; click on the drop-down arrow next to the Borders or Fill Color buttons on the Formatting toolbar; and select an option from the palette.

Column Widths

You can change the size of columns by placing the mouse pointer on the boundary to the right of a column heading, and drag the divider to the right (to expand the column) or to the left (to shrink the column).

Checking Spelling

To check the spelling of an entire worksheet, select cell A1, and then click on the Spelling button on the Standard toolbar. If you do not select cell A1, when the spelling checker reaches the end of the worksheet, you will be prompted to have the spelling checker continue checking from the beginning of the worksheet.

Printing a large worksheet

You can change the margins by entering new values in the Page Setup dialog box or by clicking on the Margins button and dragging the margin lines on the worksheet preview.

 

Excel defaults all documents to portrait when printing. If you need to change the justification of a document click on File, Page setup, then click on the Page tab. Here you can make additional changes to your document.

Print titles

If your printout contains multiple pages, you might want a title heading or column headings on top of each page, or row heading in the first column of each page.

Position the worksheet so that the rows or columns that contain the title(s) are displayed. Choose File, Page Setup, and select the Sheet tab. In the Print Titles box, enter the rows or columns that you want printed as titles. Click OK.

Using Page Breaks

When a worksheet printout is too large to fit on one piece of paper, Excel inserts automatic page breaks based o the paper size, margin settings, and scaling options in the Page Setup dialog box. To adjust a page break while in Print Preview, click on the Page Break Preview button, and drag the page break to the correct row.

Using headers and footers

To add a header or footer, choose file, Page Setup (or click on the Setup button in the Preview window) and click on the Header/Footer tab.

Create a quick Chart

To create a quick chart, select the data that you want to chart and press F11. By default, when you create a chart, the data is displayed in a column chart with a legend. To change the chart type, you can click on the Chart toolbar’s Chart Type button and choose a different type of chart from the list.