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

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

Apple Ipad

The new Apple Ipad has the internet, email, and you can watch videos. It is similar to the iphone. You can easily access the itunes store. Some new additions are the book reader and being able to take notes. You can purchase the new Ipad in the spring time when it becomes available. The pricing will run from $500 to $829. I think that this will have a big impact on technology because it offers a lot of features that are similar to those on a computer. I think that this will have an impact on society because everyone will want one. I also think that this will effect society because business people will start buying them for their jobs. I think this will effect education because college students will start buying them to use for their classes. I also think that it will effect education by having the needs for college students. I think that this will effect me personally in the future when I might need one in college. I think that the new Ipad is a great addition to the world of technology.

Current News

Newspaper- El Paso Times, Friday January 22- 11:30 a.m., Business Section

Title: Time Warner Offers Free Calls to Haiti
Time Warner cable is offering free calls to people contacting others in Haiti. This will last until February 2010. The customers do not need to make any changes to take advantage of the program. The cable company expects to provide 200,000 dollars to provide relief to the affected customers. However the program is only for direct- dial calls.

This is important to business because they will be making money to help provide relief. It is also important because people here in the U.S. will be able to make free calls to relatives they might have in Haiti.

Excel Intro 1 Tasks 1-10

1. Apply Style

To apply a style first make sure you are on the home tab. Under the styles group click on the cell styles button. Then choose whatever style you want.



2. AutoCorrect

First go to the Microsoft Office button. Then go down to Excel options and click on it. On the side bar click on proofing. Then once you are in proofing go to auto correct options and make any changes you want then click ok.

3. Bold Cell Contents

First select the cell contents you want bolded. Then make sure you are on the home tab and under the font group click on the bold button.

4. Cell Formatting Remove

First select what cell contents you want removed. Then make sure you are on the home tab. Under the editing group click on the arrow next to the clear button. Then go down to clear formats and click on it.

5. Center Across Cells

First select what you cells you want centered. Then make sure you are on the home tab. Under the alignment group click on the Merge & Center button.

6. Chart- Create

Go to the Insert tab. Under the charts group click on the button of whatever kind of chart that you want to create.

7. Clear Cells

First select the cells that you want cleared. Then make sure you are on the home tab. Under the editing group click on the arrow next to the clear button and then click on the button that says clear contents.

8. Color Text

First select the text that you want colored. Then go to the home tab and under the font group click on the arrow next to the font color button and choose whichever color you want.

9. Copy Cells

First click on the cell that you want to copy. Then go to the home tab and under the clipboard section click on copy and then drag the cursor until what you want is copied.

10. Create New Workbook Blank

Click on the Microsoft Office Button. Then click on new. Then the box willl pop open and click on create.

Excel Intro 2 Tasks 11-20

11. Enter Number


First choose the cell that you want to have a number. Then on the keyboard type the number you want in that cell.



12. Exit Excel


Go to the Microsoft Office Button and go to down to exit excel and click on the button.



13. Fill Series Drag and Drop


First select the cell that you want to fill the series. Then the cross arrow will appear at the bottom of the cell and just drag and drop until what you want is filled.



14. Font Size


First select the cell you want to change the font size. Then under the home tab under the font group go to font size button. Click on it and pick the font size you want.



15. Fonts


First select the cell that you want to change the font. Then go to the home tab and under the font group go to the font button. Then choose which font you want.



16. Function- Autosum


First select what you want to autosum. Then go to the home tab. Under the editing group click on the arrow next to the autosum button and click on which one you want to be autosummed.



17. Go To

Go to the home tab. Under the editing group go to find & select. Click on the find & select button. Then click on the go to button.

18. Help- Display Help Contents

Go to the Microsoft Office Excel Help button. Click on it. Then click on the show table of contents button. Then look for whatever you need help with.

19. Help- Search

Go to the Microsoft Office Excel Help button and click on it. Then type in what you need help with in the box and click on the search button.

20. Modify Formula

Click on the cell that you want to modify the formula. Then go to the formula bar and erase the cell numbers and cell letters until the first parenthesis and change the cell numbers and cell letters to the formula that you want.

Current News

Newspaper- 1/12/10
Business Section
The article I read about was about the Helen of Troy profits. The profits jumped by 64%. It jumped because of lower operating costs and recent product acquisitions. The total sales in the third quarter was $189.4 million compared to the same time last year.

It is important because this company is doing well despite the recession. It is also important because they are making more money and they are a local company.

Excel Plan

This six weeks my plan is to connect everything I have learned from Microsoft Word and put it into Excel. I plan to get help if I don't understand something about Excel. I will also use the knowledge that I have learned in Word and apply it to things in Excel. I also plan to use the studying method that I used with Word and do the same with Excel. Also the programs that we used in Word I will use them to help me with the Excel program.