Finding and Correcting Errors in Calculations

You can find errors by clicking on the Error button that appears next to it. You can also find errors by tracing the cell's precedents. You can also trace the cells dependents. Another way to find errors is by using the Error Checking dialog box. You can also find and correct the errors by using the evaluate formula dialog box. You can use the Watch window to display the values in a cell.

Summarizing Data That Meets Specific Conditions

You have to click on the cell to hold the formula and open the Insert function dialog box. Once you are in the dialog box select IF and click ok. Then when the function arguments dialog box opens you enter the formula within the three boxes. Then once you are done you can click ok. You can also just click on the cell and type in whatever you want the formula to be.

Creating Formulas to Calculate Values

To create a formula you can click on a cell and type what you want the formula to come out as. You can also go to the formula bar and type in the formula you want. You can also use the insert function button located on the insert tab to insert the formula you want. You can also use the names of any ranges you defined to supply values for a formula.

Naming Groups of Data

To name groups of data first select the cells you want to name. Then in the name box to the left of the formula bar type in what you want the data to be called. You can also name a group by clicking on the Define Name and typing the name in the New Name dialog box.

Excel Quiz

1. The simplest way to enter data in a worksheet is by clicking on the cell and typing the data in.
2. The purpose of AutoFill is to enter data and copy and paste it and the purpose of Fill Series is to enter two values in a series and use the fill handle to extend the series in the worksheet.
3. The available Auto Fill Options are 1. Copy Cells 2. Fill Formatting Only 3. Fill Series 4. Fill Without Formatting
4. A Pick from Drop-down List lets you choose a value from existing values in a column
5. The Paste Options button appears in the lower right corner when you paste something and it is used for choosing different paste options, such as: Keep Source Formatting, Use Destination Theme, Match Destination Formatting, Values and Number Formatting, Keep Source Column Widths, Formatting Only, and Link Cells
6. You locate specific data on a worksheet by using the find and select button on the home tab
7. The Replace table enables you to substitute one value for another
8. 1. Spelling 2. Thesaurus 3. Translate
9. You create a data table in Excel by typing a series of column headers in adjacent cells and then typing a row of data below the headers and then you format it as a table
10. The purpose of the Total row in a table is to calculate the sum of the values in the columns or rows

Defining a Table

• Excel 2003 included a structure called data list that has evolved to table in Excel 2007
• To create a data table type a series of column headers in adjacent cells and then type a row of data below the headers
• Home tab in the styles group click on format as table
• The cells in the where is the data in your table? field reflect your current selection and that the my table has headers check box is selected and then click ok
• Excel 2007 can also create a table from an existing data list- different formatted header and no spaces
• Add data to a table by selecting the cell in the row below the last row in the tabel
• Add roew and columns to a table or remove a table by dragging the resize handle
• Adding a total row excel creates a formula that calculates the sum of the values in the rightmost column
• Add a name to a table by clicking the Design contextual tab and in properties group edit the value in the Table name field
• Convert a table back to range of cells click any cell in the table and then on the table tools contextual tab in the tools group click convert to range

Correcting and Expanding Upon Worksheet Data

• Spelling checker highlights a word and offers a suggestion when it encounters a misspelled word
• Use spelling checker to add new words to a custom dictionary
• After you make a change you can remove the change as long as you haven't closed the workbook
• Undo a change by clicking the Undo button on the Quick Access Toolbar
• If you decide to keep a change you can do it by clicking the Redo button on the Quick Access Toolbar
• Use thesaurus to make sure the meaning of the word is used correctly or right and to use alternative words
• Thesaurus also has Microsoft Encarta encylcopedia
• Review tab in the proofing group click on the Research button to display the research task pane
• Translate a word to another language you can select the cell that contains teh value you want to translate by displaying the Review tab and in the proofing group click translate

Finding and Replacing Data

• Excel worksheets contain more than one million rows of data
• You can locate specific data on a worksheet by using the find and replace dialog box
• If your looking for cells that the entire cell value matches the value you're searching for you can click on the options button to expand the find and replace dialog box
• Use find for data you specify
• Use replace for substituting one value for another
• Change a value by hand select the cell and then type a new value in the cell or on the formula bar you select the value you want to replace and type in the new value
• Find what field contains the value you want to find or replace
• Find all button selects every cell that contains the value in the Find what field
• Find next button selects the next cell that contains the value in the find what field
• Replace with field contains the value to overwrtie the value in the find what field
• Replace all button replaces every instance of the value in the find what field with the value in the replace with field
• Replace button replaces the next occurence of the value in the find what field and highlights the next cell that contains the value
• Options button expands the find and replace dialog box to display additional capabilites
• Format button displays the find format dialog box- used to specify the format of values to be found or replaced
• Within list box enables you to select whether to search tha active worksheet of entire workbook
• Search list box selects whether to search by rows or columns
• Look in list box selects whether to search cell formulas or values
• Match case check box requires that all matches have the same capitalization as the text in the find what field- when checked
• Match entire cell contents requires that the cell contain exactly the same values as in the find what field
• Close button closes the find and replace dialog box

Moving Data Within a Workbook

• Click the cell that you want to move
• The cell you click will be outlined in black and the contents will appear in the formula bar
• An active cell is a cell that is outlined and you can modify its contents
• Cell range
• Once you select the cells you can cut, copy, and delete, and change the formatting
• To move an entire column of data at one time you click the column's header and it enables you to copy or cut the column and paste it somewhere else in the workbook
• Use Destination Theme -Pastes the contents of the Clipboard (which holds the last information selected via Cut or Copy) into the target cells and formats the data using the theme applied to the target workbook.
• Match Destination Formatting -Pastes the contents of the Clipboard into the target cells and formats the data using the existing format in the target cells, regardless of the workbook’s theme.
• Keep Source Formatting-Pastes a column of cells into the target column; applies the format of the copied column to the new column.
• Values Only- Pastes the values from the copied column into the destination column without applying any formatting.
• Values and Number Formatting-Pastes the contents of the Clipboard into the target cells, keeping any numeric formats.
• Values and Source Formatting- Pastes the contents of the Clipboard into the target cells, retaining all the source cells’ formatting.
• Keep Source Column Widths- Pastes the contents of the Clipboard into the target cells and resizes the columns of the target cells to match the widths of the columns of the source cells.
• Formatting Only- Applies the format of the source cells to the target cells, but does not copy the contents of the source cells.

Entering and Revising Data

• Enter data by clicking on a cell and typing a value
• You can use AutoFill to enter data and copy and paste it easier instead of going one by one
• Fill Series is letting you enter two values in a series and use the fill handle to extend your series in the worksheet
• You have control over how Excel extends the values
• You can hold down the control key while you drag the fill handle
• Autocomplete detects a value you're entering is similar to that of the previous
• Pick from Dropdown List lets you choose a value from existing values in a column
• Ctrl and enter allows you to enter a value in more than one cell simultaneoulsy
• Copy Cells
• Fill Series
• Fill Formatting Only
• Fill Without Formatting

Customizing Excel 2007

Customizing Excel

• You can change how Excel 2007 displays your worksheet
• You can zoom in on worksheet data
• Add commands that are used frequently to the Quick Access Toolbar
• Change Excel's 2007 program zoom level
• Clicking the zoom level button increases the size by 10%
• Maximum zoom level is 400%
• You can switch windows if you have multiple workbooks open
• Arrange your workbooks so that most of the active workbook is displayed
• You can display two copies of the same workbook at the same time
• Add buttons to the Quick Access Toolbar
• Add buttons to the Quick Access Toolbar by going to the Microsoft Office Button and going to Excel Options buttton
• You can choose whether your Quick Access Toolbar affects your workbooks or just the active workbook

Modifying Worksheets

Modifying Worksheets

• Change the width and height of columns and rows
• Adding space between the edge of worksheet and cells makes the workbook's contents less crowded.
• Insert row, column, or cell in a worksheet with the existing format the Insert Options button will appear.
• Format Same as Above is applying the format of the row above the inserted row to the new row
• Format Same as Below is applying the format of the row below the inserted row to the new row
• Format Same as Left is applying the format of the column to the left of the inserted column to the new column
• Format Same as Right is applying the format of the column to the right of the inserted column to the new column
• Clear Formatting is applying the default format to the new row or the new column
• Deleting and hiding and unhiding can be accessed by right clicking
• Insert individual cells
• Move data in a group of cells to another place in the worksheet

Excel Step By Step Book

Intro
• When you create a workbook by default it comes with 3 sheets and you can add or delete sheets.
• You can create a new workbook by clicking the Microsoft Office Button.
• You can customize the Excel 2007 program window
• You can customize the Quick Access Toolbar for your needs

Creating Workbooks
• When you start Excel it displays a new blank workbook
• You can save your file in different formats
• Once you have created a file you can set up different properties to make your file easier to find
• You can set up the properties by using the prepare button and then the properties button from the Microsoft Office button
• Use custom tab of the advanced properties to type a new property
• Save workbook and work once you are done creating it

Modifying Workbooks
• To display a worksheet click on the worksheet’s tab
• Excel workbooks have three worksheets
• When you create a worksheet Excel gives it a generic name
• Change worksheets name by double- clicking the worksheet’s tab
• Copy one worksheet from another workbook to current workbook
• You can change the worksheet’s location
• Change the tab color of a worksheet
• Delete worksheets
• Hide and unhide worksheets