Thursday, February 20, 2020

MICROSOFT EXCEL

Excel

Microsoft Excel
Excel is a software program from Microsoft that is part of the Microsoft Office suite of productivity software developed by Microsoft. Excel was originally code-named Odyssey during development at Microsoft, and it was first released on September 30, 1985.
Excel is capable of creating and editing spreadsheets that are saved with a .xls or .xlsx file extension. General uses of Excel include cell-based calculation, pivot tables, and various graphing tools. For instance, with an Excel spreadsheet, you could create a monthly budget, track business expenses, or sort and organize large amounts of data.
Unlike a word processor, such as Microsoft Word, the Excel documents consist of columns and rows of data, made up of individual cells. Each of these cells can contain either text or numerical values that can be calculated using formulas.

Excel overview

Below is an example of Microsoft Excel with each of its major sections highlighted. See the formula barcellcolumnrow, or sheet tab links for further information about each of these sections.
Microsoft Excel spreadsheet

MICROSOFT EXCEL- 2007

Excel 2007 Main menu
Even though you already have a new document open click on the New icon. The New Document window will open and give you template options to choose from.
Excel 2007 Templates
Click on Installed Templates. This will give you a list of templates you can uses that are already installed on your computer and you do not have to download from the internet. These can be time savers if you are in a hurry to create a professional looking spreadsheet with the formulas already in place.
Below the Installed Templates in a list of other templates you can download off the internet. These are also great time savers. It just takes a few extra clicks to download the document online.
We are going to do this tutorial in the order of the tabs at the top. So now that we have a blank document open click on the Microsoft Office logo at the top left and place your mouse over Save As.
Excel Save As
The Excel 2007 Save As is a little different than past versions. If you are planning to send the document via email or save it to a disk and use another computer to open it click the Excel 97-2003 Document. If the other computer does not have Excel 2007 but has an older version this selection lets you open the document in an older version of Excel.
If you plan on using Excel 2007 to open the document each time click the top Excel spreadsheet selection.
Excel 2007 Save As browser
Select the file location you would like to save your spreadsheet. Usually the file location defaults to My Documents. Type the in the File name: box and click save.
You can also change the spreadsheet type to open in an older version of Excel in the Save As Window by clicking the drop Save as type: down menu and selecting Excel 97-2003 spreadsheet.

nce you have the spreadsheet saved let’s learn to close and reopen the document.
Click on the Microsoft Office icon in the upper left corner and at the very bottom click Close.
There are 3 ways to reopen a document.
1. Double click on the My Documents folder from your desktop and then browse to where you saved your spreadsheet and double click on the file. This will automatically open Excel 2007 and your saved document.
Excel 2007 Open
2. Open Excel 2007 and click on the Microsoft Office icon in the upper left corner. The dropdown menu will appear and you can either click on the Open Icon and it will take you to your My Document folder where you can browse to your spreadsheet then double click on the file and open it, or
3. In the right hand column of the dropdown menu you will see Recent Documents. Scan down the list of documents until you find greeting card.docx file and click.
Excel 2007 Open spreadsheet
I know we have not got to the heart of Excel 2007 yet but we have found the basics we need to know for every Excel spreadsheet we create. Click the Next arrow to move onto the Home tab.

Excel 2007 Home Tab

In these Microsoft Excel 2007 tutorials home tab addition you will find most of your basic features. These are the tools you will find yourself using the most when creating the look and feel of an Excel spreadsheet. With this tab you are able to change the style, size and color of your font, align test, create bullets and numbers, and cut and paste text.
Open Excel 2007. We are going to create a monthly budget sheet just for practice using the features. We will start at the Far left and move through each section. Click the Microsoft Logo and click Save As. Type a name for your spreadsheet and click Save. Now Type Personal Monthly Budget in the cell A1
Excel 2007

The first section is the Clipboard. The Clipboard is a very useful tool to move text, pictures, and charts from one place to another on a page or from one document to another.
Let’s practice using the clipboard with the text we just typed. Highlight “Personal Monthly Budget” by double clicking in the A1 cell to allow editing in the cell then click in front of the text and holding the left mouse button down in front of the text and dragging the cursor to the end and let go.
Once you have done that notice that the scissor icon and the two pieces of paper icon are no longer grayed out. (when an icon is grayed out it you are not able to use it)
If you have copied or cut several objects from a document you can view and select them by viewing your entire clipboard. Click on the little arrow icon in the bottom right corner of the Clipboard section.
Excel 2007 Clipboard
This will open the Office Clipboard Task Pane. You will see a list of everything that had been copied or cut during your session in Microsoft Excel 2007. If you click on any of the items in the list it will paste them into the spreadsheet.
Click the x in the upper right corner of the Clipboard Task pane to close it.

Excel 2007 Font


Excel 2007 font
The Font section of Excel 2007 works a lot like it does in Word 2007. You can change the font style, size, bold, italic, underline, background color, or font color. The difference in Excel is how you select the text to make the changes. In Excel you can change one cell at a time, change just part of the text in a cell or change multiple cells.
Select the cell with your heading and then click the down arrow for the font style and choose a new style for your heading. I chose Ariel Black.
Excel 2007 Font style
Now we want the heading to be a little larger than the rest of the cells so click the drop down arrow for the size. Select a new size for your heading.
Moving right along, the two next buttons (the large A and then small A) will increase or decrease the size of your font if you want to change the font size that way.
The bottom buttons are the B which will bold your text, the I which will italicize the font and the U which will underline the font.
The next button is used for putting borders around cells.
Excel 2007 cell borders
Look at the different options and play with them a little to see what they do. You can also select multiple cells to place boarders around. Try doing this also. If you decide you don’t want a border you can always delete them by click the border button down arrow and selecting No Border.
The next button on is the bucket with the colored line under it. This button will fill in the background color of the cell. See if you can change the background color of your title cell.
The next button is the A with the colored line under it. This button will change the font color. If you would like to change all of the font in the cell just click the cell then select your color. If you would only like to change part of the font in the cell double click the cell then highlight the font you would like to change then select a color.

Excel 2007 Alignment

Excel 2007 alignment
Excel 2007 Cell alignmentThese 3 buttons will align you text in the cell either at the top of the cell, the middle of the cell, or the bottom.
Excel 2007 cell orientation
The Orientation button will change the text in the cell so you can align text at an angle, vertical, up, down, or open the Format Cell Alignment window to do more in depth modifications.

These 3 buttons will align you text horizontally in the cell. You can align the text right, center or left.
Excel 2007 IndentationThe next 2 buttons are used for indentation. The button on the left will decrease the indent from the left margin on your text. The button on the right will increase your indent.
The last 2 buttons in the Alignment section are used to make your text fit in the cells. Notice in the budget that I am creating some of my words are cut off, I have a few options to choose from to fix this.
Excel 2007 Word Wrap and cell sizing
  • I can use the Wrap Text button on the top next to the orientation button to wrap my text to make it fit.
  • I can place my cursor in between the A and B column on the line so it makes a cross with an arrow on each side and double click. This will make the column as big as my largest cell
  • Or I can place my cursor like I did above and click and drag to make the cell a custom size. These options work with the rows also. Just place your cursor between two of the numbered rows and either double click or click and drag.
Now I would like my title to be centered across the top of my page. To do this you can merge cells together with the last button in the Alignment section. The Merge and Center Button.
Click and drag your mouse across the cells you would like to merge and center, then click the Merge and Center button.
Excel 2007 Merge and Center
Notice that I did the same thing with the Monthly Income cells. Then I highlighted the cells and place a boarder around them and then selected the 2 monthly income cells and centered them vertically.

Excel 2007 Numbers

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.
Excel 2007 Cell format
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.




Excel 2007 Styles


Excel 2007 styles section of the home tabThe 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.
Excel 2007 Conditional Formatting
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.
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.
Excel 2007 Format a table button in the home tab
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.
Excel 2007 Cell Styles button
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.

Excel 2007 Cells.

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.
Excel 2007 insert cell, row, column, or sheet
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.

by default.
Excel 2007 delete cell, row, column, or sheet
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.

Excel 2007 Format button
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.

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.
Excel 2007 Move & Copy
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.


Excel 2007 Protect Sheet
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.
Excel 2007 Format cells

Excel 2007 Editing



Exel 2007 Editing section of the home tabNow 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.


Excel 2007 Sum button
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.
Excel 2007 Copy down
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.
Excel 2007 clear all, eraser
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.
Excel 2007 Sort & filter
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.
Excel 2007 Find & select
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.


Excel 2007 Insert Tab




Excel 2007 insert tab tables
The first section is Table. The first button in the Table section is Pivot Table. Pivot tables are used to summarize data. You would use a pivot table for complex amounts of data. A beginner computer user will probably not get into Pivot tables but if you have questions about them please email me.
Next is the Table button. This button will set up your tables like the Format as a Table button in the Home tab the only difference with this button is it makes you do more of the work. You can select the data you want to create as a table and it will give you the default color and the down arrow at your column heading so you can sort and filter the data.
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.
Excel 2007 Clip Art Navigation Pane
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.
Excel 2007 Shapes
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.
Excel smart art
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.


Excel 2007 charts
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.
Excel 2007 Insert Chart
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.
Excel 2007 select data source



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.
Excel 2007 Chart
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.

Excel 2007 Links

Excel 2007 Hyperlinks
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.
Excel 2007 Hyperlink
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.
Excel 2007 hyperlink inserted
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.

Excel 2007 Text


Excel 2007 insert text section in insert tab
This section works a lot like the functions in Word 2007. The first button is Text box. If you don’t want to use the cells in your spreadsheet you can insert a Text Box. The text box will be place over the top of the cells and give you the opportunity to type and adjust the font styles without having to adjust any of your cells. Click the Text Box button to get an idea of what will happen. If you don’t want a text box you can always delete it when you are finished playing with the features.
The next button in the Text section is Header & Footer. Click this button to see how you create a header and footer on your document.

Excel 2007 create header and footer
Notice that I now have a different view of my budget spreadsheet. I am going to enter the date for a header and page number for a footer. To do this click the current date button in the Header & Footer Elements section. Then click Go to Footer in the Navigation section. It will shift your view to the footer then click the Page Number button, which is the 1st button in the Header & Footer Elements section. When you are finished click any cell in your spreadsheet to return to the normal view.
The next button is Word Art. This button will allow you to insert decorative text into your spreadsheet. To insert Word Art click the down arrow and select a style. This will put in a box that looks like a text box in your spreadsheet on top of the cells. The Drawing Tools Format Tab will appear for you to edit your Word Art.
Excel 2007 signature
The next button is the Signature Line. This is useful if you would like to ad a line to get a signature from some one. To add a Signature Line click the down arrow and select Microsoft Office Signature Line. This will open the Signature Line window so you can fill in the name and title of the required signature. Click OK once you are finished. This will add a box on top of the cells that you can adjust the size and place where you want in the spreadsheet.


Excel 2007 insert object
The next button is Object. If you would like to embed an object such as an Adobe File, Media Clip, or Word File into your spreadsheet you can do it with this button. If you Click the Object button the Object window will open. You can select the type of Object you would like to embed in your spreadsheet and click OK. Once you have embedded the object you can double click on the Object to edit or view. If you need help with this feature email me.

The last button is the Symbol Button. Click this button to see the Symbol window that it brings up.
Excel 2007 insert a symbol
The Symbol window allows you to select symbols that you cannot enter with your keyboard into your Excel cells. Select a symbol that you would like to try and click Insert. This will put the symbol in the cell you have selected. If you do not want the symbol there just hit the back button to delete it.




















Excel 2007 Page Layout

The Excel 2007 Page Layout tab in Excel lets you make changes to the overall page, including margins, colors, printing and views.
Excel 2007 Themes
The first section in the Page Layout tab is Themes. Click the Themes button down arrow to see a list of themes you can choose from. The themes button will change your whole document design as far as color, font, and effects.
The other 3 buttons let you edit the specific aspect of the design of your spread sheet. To change the Colors in your Excel document click the down arrow on colors button.
Excel 2007 change color theme
If you click the Create New Theme colors at the bottom you will open the Create New Theme Colors window. This window gives you an idea of what each color in the row changes. You can also use this window to create a custom theme color for your spreadsheet by clicking the down arrow in each category and selecting a new color. To save your changes name your theme then click Save.
To exit without saving click Cancel.

Excel 2007 theme fonts
To create a new theme font click the down arrow in the Fonts button. You will see a list of default themes you can choose from these themes will change the font for the heading and body text.
You can also click on the Create New Theme Fonts you customize you font theme.
The next button in Themes is theme effects. Theme effects are sets of lines and fill effects. You are not able to customize your own theme but when you place your mouse over one of the default themes you can see the changes in your document and when you are happy with one click on it and the changes will take effect

Excel 2007 Page Setup


In the Excel 2007 Page Setup section of the page layout tab the first button is Margins. Click the down arrow to see the default selections. These are the margins that you need to set for printing your document.
Excel 2007 margins
You can also set custom margins by clicking the custom margins selection at the bottom of the Margins button.
The Page Setup window will open automatically to the Margins tab. In this tab you can set the top, bottom, left and right margins. In this tab you can also set the spacing for the header and footer or center the spreadsheet either horizontally or vertically on the page. The page in the center will give you a preview of how your spreadsheet will print.
Excel 2007 Orientation
The next button is Orientation. Click the down arrow on the Orientation button. This button lets you change the orientation of your spreadsheet to portrait or landscape. If you are following the Budget tutorial Change the spreadsheet to Landscape then click print preview to make sure the whole spreadsheet fits on the page. If it does not fit we will get to that soon.
Excel 2007 Page setup size button
The next button in the Page Layout tab, page setup section is Size. This button will let you select the size of the paper you will print your spreadsheet on. Printing small spreadsheets on note cards for school exams is where I found this option most valuable.
Click the down arrow to see all of the page size options.
The 8.5” x 11” paper size is the default selection. You can use the scroll bar on the right had side of the drop down menu to see more sizes or if you would like more options click the More Paper sizes option at the bottom of the list.
Moving right along to the Print Area button, click the down arrow and you will see 2 options, set print area and clear print area. Sometimes when creating a spreadsheet you may want to print on a certain section of the spreadsheet. To do this select the area of cells you would like to print and click the down arrow on the Print Area button, then select Set Print Area. You will see a dotted line around the area you selected. If you click the print preview button in the Microsoft logo button at the top of Excel 2007 you will only see the section you selected. All other cells will still be available for you to modify and use but they will not print.
To clear this print area select the cells again and click the clear print area selection.
After selecting print area you will get another option in the print area button. You will see an option to add to print area. Simply select more cells and click Add to Print Area and you then be able to see the newly selected cells in your print preview or when you print the spreadsheet.
Excel 2007 breaks button
The next button is Breaks. Click the down arrow in the Breaks button. This button lets you separate a long spreadsheet into multiple pages.
Once you have inserted the page breaks you can remove them by clicking the Remove Page Breaks.
Once you have inserted and removed page breaks the Reset All Page Breaks button will no longer be grayed out. This selection will reset you page to the way you saved it last.
The Background button is next. This button will open a Background window for you to browse through your folders for a background image for your spreadsheet. This image will be placed behind all of the cells and will not show through if you have cells that are colored.
Excel 2007 Print titles button
The last button in the Page Setup section is the Print Titles button. Click this button and the Page Setup window will open to the Sheet tab. The Print titles options allow you to repeat rows or columns on each page when you print multiple page spreadsheets. To do this, click the icon at the far right next to the Rows to repeat at top. This will bring you back to your spreadsheet. Click and drag your mouse over the cells you want to print at the top of each page of the spreadsheet then press enter to return to the Sheet tab. It will place the formula for the cells you choose in the text box. Then click OK. Print your spreadsheet to see that each page you printed has the selected rows at the top.





Excel 2007 Scale to fit

Excel 2007 scale to fit section
Excel 2007 Scale to Fit section allows you to adjust how large you want your spreadsheet to be on the printed page. There are 2 ways of working with these features. If you prefer to make changes in a window click the little diagonal arrow in the lower right hand corner. This will open the Page Setup window with the Page tab already selected. You can make changes to the scale of your spreadsheet in the scaling section of this tab.
Or you can click the down arrow next to Width: in the Scale to Fit section and make a selection for the number of pages wide you would like your spreadsheet to print on. Do the Same for Height. If you would like to scale the entire document you can change the spreadsheet with the up and down arrows next to the Scale selection. You can also type a percentage into this section and press enter to make the change. Once you have made your selections click print preview to see how the changes have effected the printing of your spreadsheet.

Excel 2007 Sheet Options


Excel 2007 sheet options
This section is Excel 2007 Sheet Options. You click the diagonal arrow at the lower right corner of the sheet Options section to open the Page Set up Window to the Sheet tab. Then you can make the modification in the Print section.
The Gridlines section is the lines in between each cell. To view the gridlines check the box next to view or uncheck the box to hide them. You can also print the gridlines to make your spreadsheet easier to read. Heading are the letters and numbers at the top and side for the cell identifications. Most people like to see the cell headings. View is check by default. You can also print the headings by checking the print box under headings.

Excel 2007 Arrange


Excel 2007 arrange
The last section in the Page Layout tab is Arrange. Most of the arrange buttons are for objects you have inserted into your spreadsheet. The first button is Bring to Front. This button will be grayed out until you insert an object into your spreadsheet. You can review the Insert Tab tutorials to learn how to insert an object. Once you have an object inserted into your spreadsheet you can select it and click the Bring to Front button. This will place the object in front of everything so no part of it is hidden. The Send to Back button is just the reverse. Once you select your object click the Send to Back button and it will send the object behind everything.

The next button is Selection Pane. Click the Selection button and notice you get a pane on the right side of your spreadsheet.
Excel 2007 selection pane
The Pane will list all of the objects you have on your spreadsheet. You can move them front to back using the arrows at the bottom of the pane.
Excel 2007 align
The Align button will let you align the edges of objects so you can have a uniform look. If you click the down arrow you can see all of your options. You can align the left edges, center the objects, align the right edges, align the top middle or bottom of objects






Excel 2007 Formulas

In this section the first button is the Insert Function button. This button allows you to edit the formula in the cell that you have selected. Formulas are equations that perform calculations with numbers in the cells in your spreadsheet. A formula always starts with an equal sign (=). For example 4+2*5 will multiply 2 and 5 then add 4.
Excel 2007 auto sum
Auto Sum is a common function that even beginner users find helpful. If you click the down arrow on the Auto Sum button you will see a list of formulas you may find useful. To use these formulas click on the cell you want your answer to appear. (If the cell you choose is not directly below a list of numbers Excel will place the formula and let you highlight the numbers you want to use.) Then select the Auto Sum function you want to use; Sum will add your numbers, Average will provide an average to a list of numbers, count numbers will count how many numbers you have, Max will find the highest number in your list, and Min will find the lowest number. Once you click your formula your work is done and Excel will provide your answer.
I am going to go through the rest of the Functions quickly. Please email me if you have any questions.
Excel 2007 function library buttons
  • The Recently Used button will change as you use more functions. The down arrow will list your most commonly used formulas.
  • The Financial button will give you a list of financial functions such as accrued interest.
  • Logical will give you true or false results.
  • Text functions can do things like capitalize the first letter in each word, find a word, or give you the phonetic characters from a string of text. This is just a few examples.
  • The Date button will give you options to calculate the number of days between two dates, give you a serial number for today’s date, or will give you the number of work days. Again these are just a few options.
  • Lookup & Reference can do things like create a hyperlink, reference a column or row, or create an index.
  • The Math & Trig feature I used a lot when I was in college. This will help you with many of the formulas you are learning in advanced math classes.
  • More Functions includes formulas for statics, engineering, and much more.
I went through this very fast so email me if you have specific questions and I will add them to the tutorial.
Excel 2007 Defined Names
The next section in the Formulas tab is the Defined Names section. This section we will learn about finding names in your spreadsheets and defining cells with names instead of cell numbers.
Click on the first button Name Manager. This will open another window.
Excel 2007 name manager
In the Name Manager window you can create, edit, delete and find all of the names in your workbooks. You can use the names in your formulas instead of cell references.
The Name Manger function is a great tool if you have several sheets, tables, and formulas in your spreadsheet. It makes it easier to memorize names rather than hunting for cell numbers.
Excel 2007 Cell auditing
To remove the arrows from the Trace Precedents and Trace Dependents buttons click the Remove Arrows button. If you just want to remove one or the other click the down arrow for those selections. The next button is Show Formulas. Click this button and you will see the cell formula instead of the value or result. Click the button again to hide the formulas and see the results again.
The Watch window is a neat feature. If you have several formulas in your spreadsheet and would like to see what happens to a series of formulas when you are changing cells in your spreadsheet. Click the Watch Window button. A window comes up. Click on a cell that has a formula and click the Add Watch button in the watch window. It will add the cell to the watch window. You can add as many cells as you like to this window and as you make changes to your spreadsheet you have a central location to look at the values of your formulas.
Excel 2007 Calculations
The next section in the formula tab is Calculation. Calculation Options button give you the choice of having values in a formula be calculated automatically when changes are made to cells that affect the formula, automatically change them except if the formula is in a table, or to have you manually update formulas by clicking either the calculate now button to update the calculations in the workbook you are working on or the Calculate sheet button to update the formulas in the sheet you are working on

Excel 2007 Data

This tab enables you to import data from other programs, update your spreadsheet when changes are made to the external data sources, sort, filter, and organize your data. Lets' get started with the first section, Get External Data.
Excel 2007 get external data
You can import spreadsheet information into Excel 2007 from about any program. Excel 2006 gives you a button to import from Access, a Web site, or Text file. Click one of those buttons and a window will open for you to select the location of the data you want to import. If your data is in a source not listed click the down arrow on From Other Sources and make a selection. If you need help please email me and I will walk you through your specific question. The last button is Existing Connections. The benefit of using a data source connection is it can make it less time consuming to analyze data in Excel from other programs. Usually you would have to cut and paste data into Excel with the Data Source connections you don't have to do that.
Excel 2007 ConnectionsLet's Move on to the Connections section of the data tab. The first button is Refresh All. If you have your spreadsheet connected to a data source and you make changes to your data source then you will need to update your spreadsheet. Click the Refresh button you will be given the option to update all data sources, update just the work book, check on the status of the refresh, or cancel the refresh.
The Connections button will open a window that gives you a list of all of your data connections a description of the connection, where the connection is located and the last time it was refreshed.
The Properties button will be grayed out until you have a data source connected to your spreadsheet. Properties with tell you how cells that are connected to a data source will be updated, what contents from the source will be displayed, and how changes in the number of rows or columns in the data source will be handled in the workbook.
The Edit Links button will open a window for you to view all other files the spreadsheet is linked to and how they are linked and it will allow you to make changes to the links.
Excel 2007 sort & filterThe next section is Sort & Filter. If you have large amounts of data in your spreadsheet it is helpful to be able to organize it and find what you are looking for. To use any of the sort buttons you can click the top of the column or highlight all of the data you want sorted then click the button you would like to organize your data with. The AZ button with the down arrow will sort your data from lowest to highest. The ZA button with the up arrow will sort your data from highest to lowest.

Excel 2007 sort window
The Sort button opens the Sort window which will give you more options when organizing your data. You can sort on multiple levels if you have several columns of data. For example you could sort my last name, then date, then amount owed. Make you selections then click the OK button to finish and your data will be sorted.
Excel 2007 filter menuTo use the filter button select the cell at the top of the column you want to filter then click the filter button. This will place an arrow in the cell. When you click the down arrow you will get a menu to make your filtering selections. In this example I am filtering a date column. It gives me the option to just show 2010 dates, all dates, etc. You can either check the boxes provided to filter or click the Date Filters option to have more choices such as filter by quarter, month dates past today, etc. Play with the options and when you are finished click ok for the filter to take place. To clear a filter click on the down arrow and select Clear filter From "column name" or click on the clear filter from the sort and filter section of the data tab.

Excel 2007 Data Tools



Excel 2007 Text to Columns buttonNext is the Data Tools section. The first button is Text to Columns. This button will separate words into 2 columns. To try this type you first and last name in one cell. Then click the Text to Columns button. It will open the Convert Text to Columns Wizard.
Excel 2007 test to columns wizard
The first step of the wizard has you tell Excel how your words are separated in the cell. I need to click Fixed width because I just used a space. Then click next. In the next window lets you set the column widths. You can adjust the widths by moving the arrows. When finished click next for the third window. The third window has you set the type of cell format, ie: text, currency, etc. make your selection and click finish. It will return you to your spreadsheet with all of the selected cells separated into 2 cells.
Excel 2007 Remove DuplicatesExcel 2007 Remove Duplicates WindowThe next button is Remove Duplicates. If you have columns in your spreadsheet that may have duplicate data you can click the Remove Duplicates button and the Remove Duplicates window will open.
In the Remove Duplicates window check the boxes next to the columns you would like the duplicate data removed. Then click OK and all duplicates will be deleted from the spreadsheet.
Excel 2007 Data ValidationThe next button in Data Tools is Data Validation. The Data Validation button prevents invalid data from being entered in cells. Click the down arrow on the Data Validation button to see the options then click on the Data Validation option. This will open the Data Validation window to the settings tab. In this tab you can customize the data that you will allow to be entered into the cells you have selected. The down arrow under Allow will let you make your selections. Excel 2007 Data Validation WindowSome examples are decimal, whole numbers, date, time, text or you can customize. After making a selection The Data selections will no longer be grayed out. Use the drop down arrow to select the type of data allowed. Depending on the selection made will depend on the variables you will be allowed to enter. I selected between. This gave me a minimum and maximum text box to fill in. I typed 3.2 in the mininum and 5.7 in the maximum.

Excel 2007 Data Validation Input Message
Now click on the Input Message tab. If the check box is marked it will let you customize a message that will appear when the cell with data validation is selected. The message will appear like a note. The title will be bold at the top and the message will appear below. If the check box is not marked there will be no message displayed to tell the user of the data valitation for the cell.
Excel 2007 Data Validation Error Alert

Click on the Error Alert tab. I would recommend always checking the box to show an error when invalid data is entered. It this box is not checked the user will not know why the data will not show up in the cell. Once the box is checked use the drop down arrow in the style section to choose the icon that will appear in a pop up window when invalid data is entered. Then type the title and message in the text boxes. Click Ok once you have entered the title and message. Now type a number that is invalid and press enter. You should get a window similar to this.
Excel 2007 Invalid Data errorIf you choose not to have an error alert to prevent invalid data from being entered you can have the data circled. Click the down arrow on the data Validation button in the drop down menu click the Circle invalid data option. Excel 2007 Data Vaildation Circle Invalid DataYou will need to go back and change you settings in the data validation window to allow invalid data to be entered. When the invalid data is entered a red circle will be placed around the cell. To clear the circles use the last option Clear Validation Circles.
Excel 2007 ConsolidateThe next button is Consolidate. This button is used to merge spreadsheets. In order for the consolidate button to work the data being consolidated needs to be in identical order in the two worksheets. When the data is in the same order click the consolidate button this will open the Consolidate window. Excel 2007 Consolidate windowIn this window use the drop down menu in the Function section. Select the function you would like preformed when the data is merged. In the Reference section you have the option to click the table button just to the right of the text box to select a section of cells in the current workbook or click the Browse button to open a different work book to make you selection. After the selection is made click the add button to add the cells to the All references section of the Consolidate window. Check either top row or left column to tell Excel where your labels are located in the spreadsheets you are going to merge. Click OK when you are finished. The will take all of the data in each selected worksheet and perform the selected function and place the answer in the selected cells on the active worksheet.
Excel What-If Analysis ButtonsThe What-If Analysis tool in Microsoft Excel 2007 gives you the option to use different values in one or more formula to view the results. There are three options to choose from. We will go through each one individually.

Excel 2007 Scenario Manager Window



Click the down arrow on the What-If Analysis button and select Scenario Manager. This will open the Scenario Manager window. Once scenarios have been entered they will appear in the scenarios section of the window
Example of how to use scenario manager
I am using a simple spreadsheet to calculate a mortgage payment and comparing interest rates to see how it will affect the payment.
Excel 2007 Add Scenario Window
If a scenario has not already been created click the add button. This will open the Add Scenario window. Type a name for your scenario in the Scenario name field. In my example I am using the percentage for the name. I typed 4.25% for the first scenario I created then created two more using 5.5% and 6.75%. In the Changing cells field type the cell or cells name(s) that you would like to see how changing them will affect your formula. I changed cell A3 in my example. You can also click the graph icon to the right of the Changing cells text box to select the cells. By clicking the icon the Excel spreadsheet will be activated for you to select any cells you wish without having to type the cell reference. The comment box allows you to type a comment for your reference when making changes to the scenarios. Once you have entered all of the information click OK and the Scenario Values window will open.
Excel 2007 Scenario Values Window
In the Scenario values window type the number you would like to replace the current value in the selected cell. Click OK to return to the Scenario manager window. Add a couple more scenarios then click Summary.
Excel 2007 Scenario Summary Window

The Scenario Summary window will open. In this window select the way you would like your scenarios to be displayed and click OK. This will open a new tab spreadsheet titled scenario summary. It will look something like this.



Example of Excel 2007 Scenario Summary
Excel 2007 Goal Seek
The next option in the What if Analysis is Goal Seek. If you know the result that you want from a formula, but are not sure what input value the formula needs to get that result you use the Goal Seek option in the What if Analysis button.
First we need to set up the spreadsheet to use the goal seek option. In your Excel spreadsheet enter the formula you would use to calculate a loan payment. In Cell A1 type Loan Amount, Cell A2 type Term in Months in Cell A3 type Interest Rate and in cell A4 type Payment. In cell B1 type the amount that you want to borrow. In cell B2 type the term you want to pay off the loan. In cell B4 type =PMT(B3/12,B2,B1) Now set the B3 cell to a percentage by clicking % button in the number section of the Home tab.
Excel 2007 Goal Seek Window
Click on the down arrow of the What if Analysis button and select Goal Seek. This will open the Goal Seek window. In the Set cell text box enter cell B4. In the To Value text box type the amount that you would like your payment to be. Type this amount as a negative number since it is a payment. In the By changing cell text box type B3. Click the OK button to return to your spreadsheet and see what your interest rate will be on the loan.
Excel 2007 Data Table
The last option in the What If Analysis button is Data Table. A data table is a range of cells that shows how changing one or two variables in your formulas will affect the results of those formulas. It helps you explore a set of possible outcomes in a worksheet. It lets you look at a range of possibilities easily. A data table can only accommodate 2 variables. The rows or columns that the variable data is entered in is important when using the Data Table feature.
Excel 2007 Data Table Example Spreadsheet
As an example I put together a table using a loan calculation formula. For the data table it will calculate the payments for the different interest rates. I entered the interest rates I would like calculated in column C. Highlight the column with the variable data along with the cell that the formula is entered into and the cells adjacent to the variables as shown. Excel 2007 Data Table WindowClick on the What-If-Analysis button and select Data Table. It will bring up the Data Table window. This is a Column based data table. In the example I would enter cell B3 by either typing it in the Column input cell or clicking on the table button next to the text box and selecting the cell from the worksheet. Once you have selected or entered the cell Click the OK button and you will return to your Excel workbook with the payments for the additional interest rates calculated in the D column.

Excel 2007 Outline

Excel 2007 charts
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.
Excel 2007 Insert Chart
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.
Excel 2007 select data source



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.

Excel 2007 Chart
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.

Excel 2007 Links


Excel 2007 Hyperlinks
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.
Excel 2007 Hyperlink
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.
Excel 2007 hyperlink inserted

Excel 2007 Text


Excel 2007 insert text section in insert tab

This section works a lot like the functions in Word 2007. The first button is Text box. If you don’t want to use the cells in your spreadsheet you can insert a Text Box. The text box will be place over the top of the cells and give you the opportunity to type and adjust the font styles without having to adjust any of your cells.
The next button in the Text section is Header & Footer. Click this button to see how you create a header and footer on your document.
Excel 2007 create header and footer
Notice that I now have a different view of my budget spreadsheet. I am going to enter the date for a header and page number for a footer. To do this click the current date button in the Header & Footer Elements section. Then click Go to Footer in the Navigation section. It will shift your view to the footer then click the Page Number button, which is the 1st button in the Header & Footer Elements section. When you are finished click any cell in your spreadsheet to return to the normal view.
The next button is Word Art. This button will allow you to insert decorative text into your spreadsheet. To insert Word Art click the down arrow and select a style. This will put in a box that looks like a text box in your spreadsheet on top of the cells. The Drawing Tools Format Tab will appear for you to edit your Word Art.
Excel 2007 signature
The next button is the Signature Line. This is useful if you would like to ad a line to get a signature from some one. To add a Signature Line click the down arrow and select Microsoft Office Signature Line. This will open the Signature Line window so you can fill in the name and title of the required signature. Click OK once you are finished. This will add a box on top of the cells that you can adjust the size and place where you want in the spreadsheet.


Excel 2007 insert object
The next button is Object. If you would like to embed an object such as an Adobe File, Media Clip, or Word File into your spreadsheet you can do it with this button. If you Click the Object button the Object window will open. You can select the type of Object you would like to embed in your spreadsheet and click OK. Once you have embedded the object you can double click on the Object to edit or view. If you need help with this feature email me.


The last button is the Symbol Button. Click this button to see the Symbol window that it brings up.
Excel 2007 insert a symbol

The Symbol window allows you to select symbols that you cannot enter with your keyboard into your Excel cells. Select a symbol that you would like to try and click Insert. This will put the symbol in the cell you have selected. If you do not want the symbol there just hit the back button to delete it.









Create by Rajsushant




No comments:

Post a Comment