Free Microsoft Office Tutorials | Online Office Learning Guide | Microsoft Office Tips and Tricks

You’ll often use simple formulas that contain just two values and a single operator. In practice, however, most formulas you use will have a number of values and operators. In these more complex expressions, the order in which the calculations are performed becomes crucial. For example, consider the formula =3+5^2. If you calculate from left to right, the answer you get is 64 (3+5 equals 8, and 8^2 equals 64). However, if you perform the exponentiation first and then the addition, the result is 28 (5^2 equals 25, and 3+25 equals 28). As this example shows, a single formula can produce multiple answers, depending on the order in which you perform the calculations. To control this problem, Excel evaluates a formula according to a predefined order of precedence. This order of precedence enables Excel to calculate a formula unambiguously by determining which part of the formula it calculates first, which part second, and so on.

The Order of Precedence

Excel’s order of precedence is determined by the various formula operators outlined earlier. Following table summarizes the complete order of precedence used by Excel.

Table :: The Excel Order of Precedence

From this table, you can see that Excel performs exponentiation before addition. Therefore, the correct answer for the formula =3+5^2, given previously, is 28. Notice also that some operators in Table 3.4 have the same order of precedence (for example, multiplication and division). This means that it usually doesn’t matter in which order these operators are evaluated. For example, consider the formula =5*10/3. If you perform the multiplication first, the answer you get is 25 (5*10 equals 50, and 50/2 equals 25). If you perform the division first, you also get an answer of 25 (10/2 equals 5, and 5*5 equals 25). By convention, Excel evaluates operators with the same order of precedence from left to right, so you should assume that’s how your formulas will be evaluated.

Controlling the Order of Precedence

Sometimes, you want to override the order of precedence. For example, suppose that you want to create a formula that calculates the pre-tax cost of an item. If you bought something for $10.65, including 7% sales tax, and you want to find the cost of the item minus the tax, you use the formula =10.65/1.07, which gives you the correct answer of $9.95. In general, the formula is the total cost divided by 1 plus the tax rate, as shown in the following figure.

Above figure shows how you might implement such a formula. Cell B5 displays the Total Cost variable, and cell B6 displays the Tax Rate variable. Given these parameters, your first instinct might be to use the formula =B5/1+B6 to calculate the original cost. This formula is shown (as text) in cell E9, and the result is given in cell D9. As you can see, this answer is incorrect. What happened? Well, according to the rules of precedence, Excel performs division before addition, so the value in B5 first is divided by 1 and then is added to the value in B6. To get the correct answer, you must override the order of precedence so that the addition 1+B6 is performed first. You do this by surrounding that part of the formula with parentheses, as shown in cell E10. When this is done, you get the correct answer (cell D10).

In general, you can use parentheses to control the order that Excel uses to calculate formulas. Terms inside parentheses are always calculated first; terms outside parentheses are calculated sequentially (according to the order of precedence).

To gain even more control over your formulas, you can place parentheses inside one another; this is called nesting parentheses. Excel always evaluates the innermost set of parentheses first. Here are a few sample formulas:

Notice that the order of precedence rules also hold within parentheses. For example, in the expression (5*2–5), the term 5*2 is calculated before 5 is subtracted.

Using parentheses to determine the order of calculations enables you to gain full control over your Excel formulas. This way, you can make sure that the answer given by a formula is the one you want.

Bookmark it:

Popularity: 15% [?]

Sorting and Filtering allow you to manipulate data in a worksheet based on given set of criteria.

Basic Sorts
To execute a basic descending or ascending sort based on one column:

• Highlight the cells that will be sorted
• Click the Sort & Filter button on the Home tab
• Click the Sort Ascending (A-Z) button or Sort Descending (Z-A) button

Custom Sorts
To sort on the basis of more than one column:

• Click the Sort & Filter button on the Home tab
• Choose which column you want to sort by first
• Click Add Level
• Choose the next column you want to sort
• Click OK

Filtering
Filtering allows you to display only data that meets certain criteria. To filter:

• Click the column or columns that contain the data you wish to filter
• On the Home tab, click on Sort & Filter
• Click Filter button
• Click the Arrow at the bottom of the first cell
• Click the Text Filter
• Click the Words you wish to Filter

• To clear the filter click the Sort & Filter button
• Click Clear

Bookmark it:

Popularity: 11% [?]

Insert Cells, Rows, and Columns
To insert cells, rows, and columns in Excel:

• Place the cursor in the row below where you want the new row, or in the column to the left of where you want the new column
• Click the Insert button on the Cells group of the Home tab
• Click the appropriate choice: Cell, Row, or Column

Delete Cells, Rows and Columns
To delete cells, rows, and columns:

• Place the cursor in the cell, row, or column that you want to delete
• Click the Delete button on the Cells group of the Home tab
• Click the appropriate choice: Cell, Row, or Column

Find and Replace
To find data or find and replace data:

• Click the Find & Select button on the Editing group of the Home tab
• Choose Find or Replace
• Complete the Find What text box
• Click on Options for more search options

Go To Command
The Go To command takes you to a specific cell either by cell reference (the Column Letter and the Row Number) or cell name.

• Click the Find & Select button on the Editing group of the Home tab
• Click Go To

Spell Check
To check the spelling:

• On the Review tab click the Spelling button

Bookmark it:

Popularity: 11% [?]

Split a Worksheet
You can split a worksheet into multiple resizable panes for easier viewing of parts of a worksheet. To split a worksheet:

• Select any cell in center of the worksheet you want to split
• Click the Split button on the View tab
• Notice the split in the screen, you can manipulate each part separately

Freeze Rows and Columns
You can select a particular portion of a worksheet to stay static while you work on other parts of the sheet. This is accomplished through the Freeze Rows and Columns Function. To Freeze a row or column:

• Click the Freeze Panes button on the View tab
• Either select a section to be frozen or click the defaults of top row or left column
• To unfreeze, click the Freeze Panes button
• Click Unfreeze

Hide Worksheets
To hide a worksheet:

• Select the tab of the sheet you wish to hide
• Right-click on the tab
• Click Hide

To unhide a worksheet:

• Right-click on any worksheet tab
• Click Unhide
• Choose the worksheet to unhide

Bookmark it:

Popularity: 11% [?]

Adding a Picture
To add a picture:

• Click the Insert tab
• Click the Picture button
• Browse to the picture from your files
• Click the name of the picture
• Click Insert
• To move the graphic, click it and drag it to where you want it

Adding Clip Art
To add Clip Art:

• Click the Insert tab
• Click the Clip Art button
• Search for the clip art using the search Clip Art dialog box
• Click the clip art
• To move the graphic, click it and drag it to where you want it

Editing Pictures and Clip Art
When you add a graphic to the worksheet, an additional tab appears on the Ribbon. The Format tab allows you to format the pictures and graphics. This tab has four groups:

Adjust: Controls the picture brightness, contrast, and colors
Picture Style: Allows you to place a frame or border around the picture and add effects
Arrange: Controls the alignment and rotation of the picture
Size: Cropping and size of graphic

Adding Shapes
To add Shape:

• Click the Insert tab
• Click the Shapes button
• Click the shape you choose

• Click the Worksheet
• Drag the cursor to expand the Shape

To format the shapes:

• Click the Shape
• Click the Format tab

Adding SmartArt
SmartArt is a feature in Office 2007 that allows you to choose from a variety of graphics, including flow charts, lists, cycles, and processes. To add SmartArt:

• Click the Insert tab
• Click the SmartArt button
• Click the SmartArt you choose

• Select the Smart Art
• Drag it to the desired location in the worksheet

To format the SmartArt:

• Select the SmartArt
• Click either the Design or the Format tab
• Click the SmartArt to add text and pictures.

Bookmark it:

Popularity: 20% [?]

Most worksheets are created to provide answers to specific questions: What is the company’s profit? Are expenses over or under budget, and by how much? What is the future value of an investment? How big will an employee bonus be this year? You can answer these questions, and an infinite variety of others, by using Excel formulas. All Excel formulas have the same general structure: an equals sign (=) followed by one or more operands—which can be values, cell references, ranges, range names, or function names—separated by one or more operators—the symbols that combine the operands in some way, such as the plus sign (+) and the greater-than sign (>).

Formula Limits in Excel 2007

Although it’s unlikely that you’ll ever bump up against them, it’s a good idea to know the limits that Excel sets on various aspects of formulas and worksheet models. All of these limits have been greatly expanded in Excel 2007, as the following table shows.

Entering and Editing Formulas

Entering a new formula into a worksheet appears to be a straightforward process:

1. 1. Select the cell in which you want to enter the formula.
2. 2. Type an equals sign (=) to tell Excel that you’re entering a formula.
3. 3. Type the formula’s operands and operators.
4. 4. Press Enter to confirm the formula.

However, Excel has three different input modes that determine how Excel interprets certain keystrokes and mouse actions:

• When you type the equals sign to begin the formula, Excel goes into Enter mode, which is the mode you use to enter text (such as the formula’s operands and operators).
• If you press any keyboard navigation key (such as Page Up, Page Down, or any arrow key), or if you click any other cell in the worksheet, Excel enters Point mode. This is the mode you use to select a cell or range as a formula operand. When you’re in Point mode, you can use any of the standard range-selection techniques. Note that Excel returns to Enter mode as soon as you type an operator or any character.
• If you press F2, Excel enters Edit mode, which is the mode you use to make changes to the formula. For example, when you’re in Edit mode, you can use the left and right arrow keys to move the cursor to another part of the formula for deleting or inserting characters. You can also enter Edit mode by clicking anywhere within the formula.
• Press F2 to return to Enter mode.

After you’ve entered a formula, you might need to return to it to make changes. Excel gives you three ways to enter Edit mode and make changes to a formula in the selected cell:

• Press F2.
• Double-click the cell.
• Use the formula bar to click anywhere inside the formula text.

Excel divides formulas into four groups: arithmetic, comparison, text, and reference. Each group has its own set of operators, and you use each group in different ways.

Bookmark it:

Popularity: 20% [?]

Format Worksheet Tab
You can rename a worksheet or change the color of the tabs to meet your needs.
To rename a worksheet:

• Open the sheet to be renamed
• Click the Format button on the Home tab
• Click Rename sheet
• Type in a new name
• Press Enter
  

To change the color of a worksheet tab:

• Open the sheet to be renamed
• Click the Format button on the Home tab
• Click Tab Color
• Click the color
  

Reposition Worksheets in a Workbook
To move worksheets in a workbook:

• Open the workbook that contains the sheets you want to rearrange
• Click and hold the worksheet tab that will be moved until an arrow appears in the left corner of the sheet
• Drag the worksheet to the desired location
  

Insert and Delete Worksheets
To insert a worksheet

• Open the workbook
• Click the Insert button on the Cells group of the Home tab
• Click Insert Sheet
  

To delete a worksheet

• Open the workbook
• Click the Delete button on the Cells group of the Home tab
• Click Delete Sheet
  

Copy and Paste Worksheets:
To copy and paste a worksheet:

• Click the tab of the worksheet to be copied
• Right click and choose Move or Copy
• Choose the desired position of the sheet
• Click the check box next to Create a Copy
• Click OK
  

Bookmark it:

Popularity: 11% [?]

Excel allows you to move, copy, and paste cells and cell content through cutting and pasting and copying and pasting.

Select Data
To select a cell or data to be copied or cut:

• Click the cell
  

• Click and drag the cursor to select many cells in a range

Select a Row or Column
To select a row or column click on the row or column header.

Copy and Paste
To copy and paste data:

• Select the cell(s) that you wish to copy
• On the Clipboard group of the Home tab, click Copy
  

• Select the cell(s) where you would like to copy the data
• On the Clipboard group of the Home tab, click Paste

Cut and Paste
To cut and paste data:

• Select the cell(s) that you wish to copy
• On the Clipboard group of the Home tab, click Cut

• Select the cell(s) where you would like to copy the data
• On the Clipboard group of the Home tab, click Paste

Undo and Redo
To undo or redo your most recent actions:

• On the Quick Access Toolbar
• Click Undo or Redo

Auto Fill
The Auto Fill feature fills cell data or series of data in a worksheet into a selected range of cells. If you want the same data copied into the other cells, you only need to complete one cell. If you want to have a series of data (for example, days of the week) fill in the first two cells in the series and then use the auto fill feature. To use the Auto Fill feature:

• Click the Fill Handle
• Drag the Fill Handle to complete the cells

Bookmark it:

Popularity: 11% [?]

Set Print Titles
The print titles function allows you to repeat the column and row headings at the beginning of each new page to make reading a multiple page sheet easier to read when printed. To Print Titles:

• Click the Page Layout tab on the Ribbon
• Click the Print Titles button
• In the Print Titles section, click the box to select the rows/columns to be repeated
• Select the row or column
• Click the Select Row/Column Button
• Click OK

Create a Header or Footer
To create a header or footer:

• Click the Header & Footer button on the Insert tab
• This will display the Header & Footer Design Tools Tab
• To switch between the Header and Footer, click the Go to Header or Go to Footer button

• To insert text, enter the text in the header or footer
• To enter preprogrammed data such as page numbers, date, time, file name or sheet name, click the appropriate button
• To change the location of data, click the desired cell

Set Page Margins
To set the page margins:

• Click the Margins button on the Page Layout tab
• Select one of the give choices, or

• Click Custom Margins
• Complete the boxes to set margins
• Click Ok

Change Page Orientation
To change the page orientation from portrait to landscape:

• Click the Orientation button on the Page Layout tab
• Choose Portrait or Landscape

Set Page Breaks
You can manually set up page breaks in a worksheet for ease of reading when the sheet is printed. To set a page break:

• Click the Breaks button on the Page Layout tab
• Click Insert Page Break

Print a Range
There may be times when you only want to print a portion of a worksheet. This is easily done through the Print Range function. To print a range:

• Select the area to be printed
• Click the Print Area button on the Page Layout tab
• Click Select Print Area

Bookmark it:

Popularity: 13% [?]

Macros are advanced features that can speed up editing or formatting you may perform often in an Excel worksheet. They record sequences of menu selections that you choose so that a series of actions can be completed in one step.

Recording a Macro
To record a Macro:

• Click the View tab on the Ribbon
• Click Macros
• Click Record Macro
• Enter a name (without spaces)
• Enter a Shortcut Key
• Enter a Description

• Perform the Macro
• Click Marcos
• Click Stop Recording

Running a Macro
To run a Macro from the Keyboard shortcut, simply press the keys that you have programmed to run the Macro. Or you can view all macros and run by:

• Click Macros
• Click View Macros
• Choose the Macro and click Run

Bookmark it:

Popularity: 13% [?]