SPREED SHEET 3

NUMBERS SECTION:

This section is Numbers. Notice how I didn’t type a dollar sign or put decimals in my amounts. You can change the number format in any cell. You will especially need to do this if you are putting formulas or calculations into your spreadsheet.
Highlight the cells you want to change the number format. Then click the down arrow where it says General.


This will give you a list of options. Sometimes Excel 2007 will even automatically change a number format after you are finished typing. To prevent a change put an apostrophe (‘) in front of what you are typing and it will not change the format. I run into this all of the time with dates.
I would like a Currency so I will make that selection and it will change my selected cells to have a $ and 2 decimal places.
If you would like to see other options click the More Number Formats selection at the bottom of the menu.
These 5 buttons from left to right give you the following functions: The $ will place the currency mark in front of your numbers. If you click the down arrow you will have other currency options; The % will change the number into a percentage; the , will place a comma to separate the thousand mark; The next button will decrease the decimal places shown and the last button will increase the decimal places shown. This concludes the Numbers Section. Let’s move onto Styles.



The first button in Excel 2007 styles section is the Conditional formatting. I love this because once you have place formulas in your spreadsheet you can send up red flags with this conditional formatting. Click the down arrow on the Conditional Formatting button to see the options.

With these options you can change the color of a cell if it does not meet the criteria you set. I have set my budget to be highlighted red if I go over. To do this select the cells you want this formatting option, then click the Highlight Cells Rules, then click Less Than, then input 0 and select the color and click OK.
You can put in multiple conditional formatting options. Notice the red, green, and yellow buttons next to the numbers these are done with the Icon Sets then selecting more rules. If you need help with any of your conditional formatting please email me.
The next button in the Styles section is Format as Table. If you are creating a table You can start with this option and select one of the styles or you can highlight your table and then select the styles to change the look and feel of your table.

Notice in my budget spreadsheet I highlighted my section on household expenses and Entertainment expenses then I selected the medium green table format.
Now move onto the next button Cell Styles. Click the down arrow on Cell Styles.

This button will give you quick predefined styles for each cell. Or if you don’t like any of these styles you can create your own style by selecting New Cell Style. This option will let you format a cell and Save it. Style 1 and Style 2 are styles I created for my spreadsheet. Now try and create your own cell style.

 CELL SECTION:
This section of the Home tab is Cells. In this section you can add, delete or format cells. Click the down arrow on the Insert button.
To insert a cell click on a cell next to the one you would like to insert. Then click the Insert Cells button another window will come up asking you which direction you would like to shift the cells once you insert your new cells. The window also gives you the options to just shift the cells or shift the entire row or column.
You can insert an entire row with the Insert Sheet Rows button or Insert a whole column with the Insert Sheet Columns button. The Insert Sheet option lets you add another sheet to your Excel 2007 document. The sheets end up at the bottom. You can see Excel automatically give you Sheet1, Sheet2, and Sheet3 by default.
The Delete down arrow gives you the same options that the Insert button gave. You can delete single cells with the Delete Cells option. The same window will appear asking what direction you would like to shift the cells after you delete the selected cell.
The Delete Sheet Rows will Delete an entire row and the Delete Sheet Columns will delete a whole column.
Delete Sheet will remove the selected sheet from the bottom tabs.
Click the down arrow on Format in the Cells section. The 1st selection is the Row Height. This button will open another window for you to type in a height number.
The AutoFit Row height will do the same thing as it did when you double click in between the rows it will take the longest text and adjust the row to accommodate the text. It will also make the row smaller if the text is smaller.
Column Width will open the same window as the Row Height button did and let you type in a number for the column width.
AutoFit Column Width will take the longest text and adjust the column to fit the text. It will also make a column smaller if the text is smaller than the column.
The Default Width will put the column at 64 pixels or 8.43.
If you have a large number of rows or columns you will find the Hide & Unhide feature useful. You can select a column or row by clicking on the row number or column letter then select hide row or hide column. You can get your rows and columns back by highlighting the row or columns on either side and selecting unhide row or unhide column. You also have the option to hide an entire sheet.
The next selection is Rename Sheet. Click on this selection and notice at the bottom of your Excel document the Sheet 1 is highlighted. Type your new name for the sheet and press Enter. You can also do this by double clicking the sheet name.

To change the order of the Excel sheets click the Move or Copy Sheet button. This will bring up the Move or Copy window. The window gives you the option to move the sheet to another book with the drop down arrow or you can select the sheet you would like the sheet you are moving to appear before. By checking the box to Create a copy you will leave the sheet in the original place and move a copy of the sheet to the new location you selected.
The tab color selection will let you organize your Excel sheets by color. Select the sheet you would like to change the tab color and then click the over arrow to select your color.


The next option is Protect Sheet. Click this option to see the Protect Sheet window. If you have information in your spreadsheet that you don’t want others to be able to change you can password protect them. Then the only way the selected cells can be changes is if you enter the password. You can lock a cell completely or just check certain options that another person will not be able to change.
Browse through the options if you don’t want to password protect your spreadsheet click cancel.
To make the Lock Cell button work you need to have the Protect Sheet setup. Then you are able to lock cells in your spreadsheet as you create it.
The last selection in the Format button is the Format cells button. This will open the window you see below which gives you all of the options we have already covered to format your cell. This is nice because all of the format options are in one spot.

Now we are on the last section of the Excel 2007 Home tab, Editing.
The first button in the Editing tab is the sum symbol. Click the down arrow to see the options.

In my budget spreadsheet I have put a total row at the bottom of each section. It is very easy with this button. All you need to do is click the sell below the numbers you would like to total, average, count, find the max, or min the click the down arrow of the sum symbol and make you selection. It will do the formula for you.

The next button is a down arrow. This will copy cells for you in any direction. The feature I like with this button is the Series selection. If you have a row of dates you don’t have to type each date. Just type the first 3 and highlight how far you want the dates to go then select Series. A window will come up and let you select the date unites you want to count by then select ok and the cells you selected will automatically be filled in with the dates.
This selection does not always have to be a date it can be counting by 2’s or what ever series of numbers you would like.

The next button in the Editing section is the Eraser. Click the down arrow to see the options. This feature lets you selectively erase parts of your spreadsheet. You can Clear everything, Clear only the formatting, Clear the Contents of selected cells, or if you have comments (we have not learned about these yet) you can just clear those.

The next button is the Sort button. This button will let you sort your data from highest to lowest depending on what type of data you have you can also alphabetize columns of data with the sort button. Notice in my budget spreadsheet I sorted my Household data from lowest to highest. You can also do this if you have created a table with the format table button it gives you the sort arrows above each column. If you have not created a table you will need to highlight the area you would like to sort.
You also have the option of doing a custom sort. This will open a new window for you to input your selections. If you need help with this email me.
The filter button is what gives you the arrows above each column. If you have not used the table feature you might want to filter a row. Try filtering a row and looking at the options the arrows in the cells give you.
The last button is Find & Select. This button works like a search. It will find specific numbers or words in your workbook. Click the down arrow to see the options.

If you have large amounts of data this is a quick way to find what you are looking for. I also find the Replace feature very useful. The Replace button will open another window and let you type the text you are looking for then the text you want to replace it with. It will go through the entire document and replace all matching text.
If you would like more details on this section please email me. The other features can get very advanced.

 CHART SECTION:

 The next section is Illustrations. The first button will insert a picture from a file. When you click on the Picture button it will open a window for you to browse your files and find a picture. The browser window will default to your my Pictures folder. Select the picture you want to insert and click insert.


The clip Art button will open a window that will allow you to search for pictures that are either part of the clip art file or find clip art on the internet. The Clip Art navigation pane will open on the side of the working screen. In the Search for section type a key word for the image you are looking for. Click Go to start the search. If you would like to look at a category of clip art click the down arrow in the search in section to narrow your search. Results should be is where you will select the type of media you want your clip art to find. You can select a search for sounds, movies, photos or clip art with this drop down menu. Your results will be displayed in the open box below Results should be section
The Organize clips button will open the favorites - clip art explorer window for you to view all of the clip art located on your computer and move them to different folders.
Clip art on Office Online will only display images available online. You will need an internet connection to view these clip art images.
Tips for finding clips is a help menu.

Click the down arrow on the Shapes button to see a variety of shapes you can insert into your Word 2007 document. When you click on the shape your cursor will change. Click anywhere in your spreadsheet to insert the shape. When the shape is inserted into your document the Drawing tool tab will appear in the tab selections for you to edit your inserted shape.
The next button is SmartArt. If you click the SmartArt button the Choose a SmartArt Graphic window will open.
SmartArt will give you several choices to visually display your data. You are able to create flow charts and text based graphics with easy to use predesigned templates. Click on one of the charts. This will insert the graphic into your spreadsheet and open the SmartArt tools tab options. In the spreadsheet canvas in each section of the graphic that says text you can click and modify the text entered on top of that graphic.

The next section is used to create charts. The options in the Charts section will let you create different styles of charts. Let me show you how to create a chart using the data I have entered for my budget spreadsheet.
I want my chart to be on a different sheet so I selected another sheet. To create my chart I am going to use the Column option. I clicked the down arrow on the column button and chose a cylinder style chart to compare my actual cost verses my budgeted costs.

Doing this placed my chart area in the center of my Chart tab. Now I need to click the select data button at the top in the Chart Tools.



The Select Data Source window will open. I clicked on my Budget tab then highlighted the cells I wanted to include in my table. Make sure you highlight your title so it will appear in the table.
Then click OK and your chart will be created.

That is the basics of creating a chart. Creating a chart works the same with all of the options you see in the chart section. The different types of charts can be used to provide various images for your data. If you would like more detail on creating charts please email me.

Lets move onto the Links section. The Hyperlink button lets you link a cell in your spread sheet to a website, document, email address, or program. I am going to show you how to link my electricity cell to the website where I pay my bill. First click the cell you would like to create the hyperlink in then click the Hyperlink button and the Insert Hyperlink window will come up.

In the Insert Hyperlink window you have the option to create any type of link you want. The buttons on the left column allow you to select the type of link and the right hand options let you give the link details. To create my web link I will type my web address down in the Address section then click OK.

Notice that my Electricity cell is now blue. If I click once on the cell it will open the website where I pay my bill if I click and hold the mouse button down I can edit the cell.
We are almost finished with the Insert tab. Let’s move onto Text section.