Excel 2007 Test How To's

1. Absolute Reference

To include an absolute reference to a cell you first have to select the cell that you want to include an absolute reference. Then in the formula bar put a dollar sign in front of the cell letter and put another dollar sign in front of the cell number. Then click enter and the cell will have an absolute reference.

2. Connect Two Shapes With a Double Point Arrow

To connect two shapes with a double point arrow first click on one of the shapes. Then you click on the insert tab or format tab in the drawing tools tab. Then click on the shapes button drop down arrow. Then click on the double point arrow in the line group. To add a certain thickness of line click on shape outline and then click on weight. Then you can choose how thick or thin you want the line to be.

3. In a Cell Average a Range of Cells That Are Greater Than 1000

Click on the cell where you want the average to be at. Then go to the formulas tab and click on insert function. Then type in average ifs and click go. Then click on the average ifs when it is highlighted. Then enter in the cells you want to average in the average range box and in the criteria range 1 box type in the range you want to average. Then in Criteria 1 type in > 1000. Then click ok and the formula will be entered.

4. Convert Comma Delimmeted Text to Columns

First select the cells that you want to convert into columns. Then got ot the data tab. Under the data tools group click on text to columns button. Then make sure delimetted is selected and click next, then under delimetters click on comma and click next. Then under step 3 of the wizards dialog box click on finish. The text is now converted into columns.

Adding Images to a Document

To add an image to a document go to the insert tab. Once you are in the insert tab go to the illustrations group and click on the picture button. Then you choose which picture you want and insert it. To add a background go to the page layout tab and in the page setup group click on the background button. You can then choose which picture you want to put as the page background. You can also delete a page background by going to the page layout tab and under the page setup group click on delete background.

Changing the Appearance od Data Based on Its Value

To change the apperance of data first select what you want to change the appearance. Then go to the home tab. In the styles group click on conditional formatting. Then point to one of the choices and you can pick which appearance you want for the group of data that you have and what the data's value is based on.

Making Numbers Easier to Read

To make numbers easier to read you have to first select the cell that you want to change. Then you go to the home tab. In the font group click on the dialog expander. Then when the dialog box opens you have to click on something in the category list depending on what you want to change. Once you choose something from the category list then you can choose something in the type list.

Applying Workbook Themes and Table Styles

To apply an existing theme go to the page layout tab and in the themes group click on the themes button. You can then click on the theme that you want to apply to your workbook. You can apply a new table style by going to the styles group on the home tab and clicking on the format as table button. Then click on the new table style button and you can then customize how you want to your new table style to be.

Defining Styles

To define a style first make sure you are on the home tab. Then go under the styles group and click on cell styles. There are multiple styles that can help you with the style you want on your worksheet. You can also create a new style by going to the home tab and going to the styles group and clicking on cell styles. From here you click on new cell style. The style dialog box will pop up and you name the new style. Then click on the format button and the format cells dialog box will pop up and you can customize the new style the way you want by clicking on the different tabs and creating it the way you want it to look.

Formatting Cells

To format a cell the way you want it first select the cell or cells you want to format. Then under the home tab in the font group click on the buttons that you want to use to format the cell or cells. You can also click on the dialog box launcher in the font group under the home tab to get to the format cells dialog box to format cells.

Excel 2007 Practice Exam

• modifying the formula from one worksheet to another worksheet
• modifying the formula from a worksheet to another worksheet
• creating a formula to calculate something from one worksheet to another workbook and creating a formula to calculate something from one worksheet to another worksheet
• saving a specific workbook in another workbook
• copying and pasting a comment
• calculating the average of a column based on another column
• modifying the cell range in a completed chart
  

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