How to Learn Macros in Excel 2007

1. Click on the 'Developer' tab to bring up the ribbon.
2. Click the 'Record Macro' button in the Code frame of the Developer ribbon.
3. Type your desired macro name into the 'Macro Name' text box, then type the shortcut key you want to use to access your macro in the 'Shortcut Key' text box. Click 'OK.' For example, you might name your macro 'AddEmphasis' and set the shortcut key to 'E.'
4. Format your spreadsheet in whatever way you want. Whatever you do now will be repeated when you run the macro in the future. For example, you might italicize the text in a cell and change the background to yellow.
5. Click the 'Stop Recording' button in the Code section of the Developer ribbon.
6. Press 'Ctrl' and the shortcut key you chose in Step 3 at the same time. For the example, if you press 'Ctrl' and 'E' at the same time, the active cell will become italic and the background will turn yellow. You now have a working macro.

Read more ►

How to Exit From a Sub in VBA

1. Open the VBA editor. Double-click on one of the Microsoft Office programs that you want work with. Click on the 'Developer' tab and then click on 'Visual Basic.' If you don't see the 'Developer' tab, click on the 'File' tab and then 'Options.' Click on the 'Customize Ribbon' tab in the 'Options' dialog box. Click on 'Main tabs,' and then click on the 'Developer' check box. Click 'OK' to close the 'Options' dialog box.
2. Navigate to the sub procedure that you want to work with in the Visual Basic Editor. Double-click on the page in the right pane to open it. To work on a new procedure, click on 'Insert' and then 'Sub Procedure.'
3. Add the 'Exit Sub' command in the sub procedure where you want to exit a certain block of code. This will be between the Sub and End Sub commands. Usually, the Exit Sub command is added right after the code handler. Here is an example of a code block with an Exit Sub command:'Sub message ()MsgBox 'Hello World!'On Error GoTo Error_Handler:Worksheets('Welcome Message').ActivateExit SubError_Handler:If Err.Number = 9 ThenWorksheets.Add.Name = 'Welcome Message'ResumeEnd IfEnd Sub'The sub procedure shows a message box that says 'Hello World.' If for some reason the program can't display the message, it is directed to the 'Error_Handler' block. To stop the code from continuing to cycle through the same bits of code, the 'Exit Sub' command is given.

Read more ►

How to Create a Venn Diagram in Excel or Word

1. Open a document in Microsoft Word or Excel.
2. Click the 'Insert' tab. In the 'Illustrations' group, click 'SmartArt.' Click the 'Relationship' tab. Scroll down and select the icon for 'Basic Venn.' Click 'OK' to insert the Venn diagram.
3. Click 'Text' in the Text pane on the left. Type the text you want to place inside the first circle, which should be the name of the thing you are comparing. Click the next item down to enter text for the next circle. You also can click a circle in the graphic and type the text there.
4. Add or remove circles from the Venn diagram if you have more or less than 3 circles you want to compare. To add a circle, select the circle nearest to where you want to add one. Click the 'Design' tab. In the 'Create Graphic' group, click 'Add Shape.' Click either 'Add Shape After' or 'Add Shape Before,' and a circle will be added to overlap the selected circle. If you need to delete a circle, select it and then press 'Delete.'
5. Edit the circles in your diagram by manually moving them around. Click a circle you want to move, then drag it to the proper location. This will change the location and size of the overlapping areas of the circles.
6. Format the diagram by clicking the graphic, then clicking the 'Design' tab. To change the color combination, click 'Change Colors' in the 'SmartArt Styles' group and select a theme. Apply a SmartArt Style to change the look of it. Scroll through the styles in the 'SmartArt Styles' group and click the one you want. To change the look of a particular circle, right-click the border of the circle. Select 'Format Shape.' Here you can edit the line color, line style and fill color.
7. Click the 'File' tab or 'Microsoft Office Button,' then 'Save as' to save your Venn diagram.

Read more ►

How to Fit an Excel Sheet on One Page

1. Open the Excel spreadsheet that you want to print. Click on 'File' in the Excel toolbar, or use the keyboard shortcut 'Alt' and 'F.'
2. From the 'File' menu, select 'Page Setup.'
3. In the 'Page Setup' dialog box, click the 'Page' tab.
4. Under 'Scaling,' click the radio button to the left of 'Fit To.'
5. Enter your desired number of pages in the 'Pages Wide' and 'Pages Tall' drop-down menus. For example, you may want to change only the width of your page so that the last columns do not print onto a separate page. In this case, you would enter '1' into 'Pages Wide' and delete the number in the 'Pages Tall' menu.
6. Click OK. Your Excel spreadsheet will now fit the way you want it to.

Read more ►

How to Modify a Worksheet in Excel 2007

1. Determine what you need to modify. You have just received correspondence from some of your friends that have changed their address. You now need to update their information in your workbook.
2. Open your worksheet and find the information that needs to be modified.
3. Click on 'Edit' and 'Find and Replace'. When the box appears type in the first or last name of the person you need to locate in the 'Find' box and click ok.
4. Let Excel find and take you to the information.
5. Select the columns that need to be updated. Fill in the new street, city, state and zip code with the new information.
6. Decide if you need to add more information. If your friends have included their phone number in the new information that they sent you, but you haven't put in a column for phone numbers, you can add this column now.
7. Add the new column. Go to the title row and type in a heading such as phone. You can then go back to the row you were in and add the phone number that you have just received.
8. Continue to add new information, by adding new columns or rows to the information that is already there. This is all it takes to modify your worksheet.

Read more ►

How to Print on One Page in Excel 2003

1. Click 'Start' from the bottom-left of the screen. Click 'All Programs' and then click 'Microsoft Excel 2003.'
2. Click 'File' and then 'Open.' Click the Excel file of choice and click 'Open.'
3. Click and drag the mouse over the group of cells of the worksheet you wish to print. If you do not choose the group of cells when you print, Excel will print both cells with data as well as cells that are blank.
4. Click 'File,' then click 'Print Area' and then 'Set Print Area.'
5. Click 'File' and then click 'Page Setup.'
6. Click the option 'Fit to' in the 'Page' tab. Enter the value of '1' page wide by '1' tall.
7. Click the 'Margins' tab. Check the box next to 'Horizontally' under 'Center on page' and click 'OK.' This ensures the worksheet prints in the center of the page for the standard portrait paper layout.
8. Click 'File' and then 'Print.' The Print window launches. Click 'OK' to print your worksheet.

Read more ►

How to Add Data to a Pivot Table

1. Double-click the Excel file that contains your PivotTable to open it with Excel 2010.
2. Navigate to the source data for the PivotTable. This may be in a separate area of the worksheet, or on a separate worksheet. If your data is on a separate worksheet, you can chance worksheets by clicking on the correct worksheet tab at the bottom of the window.
3. Add your desired row or column of data. You can also modify the existing data, if you need to.
4. Navigate to your PivotTable and select any cell in the table. Click the 'Options' tab at the top of the screen, and then click the 'Change Data Source' button. Excel 2010 will automatically move to your source data and open a 'Move PivotTable' window.
5. Click on the top-left cell in your source data, and hold the mouse button as you drag the mouse to the bottom-right cell. Then release the mouse button and click 'OK' on the 'Move PivotTable' window. The PivotTable will now update to include the added data.

Read more ►

How to Use the Color of an Active Cell in Excel

1. Highlight the range of data you want to sort.
2. Select the Home tab at the top of the page, then click on 'Sort Filter' in the Editing group and then click 'Custom Sort...' to open the Sort dialog box.
3. Check the box labeled 'My data has headers' if you highlighted data header rows when selecting your data.
4. Choose the column by which you want to sort your data under 'Sort by' and then select Cell Color under 'Sort On.'
5. Choose the color by which you want to sort under 'Order.' All colors present in your data range, including 'No Cell Color,' will be available as options. Choose whether you want that data placed on top or on the bottom.
6. Add additional sorting levels by clicking 'Add level' and or 'Copy level,' if desired.
7. Click 'OK' to view your sorted data.

Read more ►

How to Link Word 2007 Excel 2007 Documents

1. Open the Microsoft Word 2007 file on your computer that you want to link an Excel 2007 file into. Click on the area in your document where you want the link to appear.
2. Click on the 'Insert' tab and then click on the arrow next to the 'Object' field.
3. Click on the 'Text from File...' option from the drop-down menu and then the Insert File dialog box will appear.
4. Select the Excel 2007 file on your computer that you want to link into your document.
5. Click on the 'Insert as Link' option and then the Excel 2007 will appear as a clickable link within your document.

Read more ►

How to Calculate Range in Excel

1. Open a new worksheet in Microsoft Excel. For the purposes of this article, type a series of numbers into column A, beginning with '1' in cell A1 and ending with '10' in cell A10.
2. Click on cell B1, which should be blank, and type '=MAX(A1:A10)' to find the highest number in the data sample you entered. In this case, it will be 10.
3. Click on cell B2, which is also blank, and type '=MIN(A1:A10)' to find the lowest number in the data sample. In this example, it will be 1.
4. Click on the blank cell B3 and type '=B1-B2' to calculate the range by subtracting the minimum value of your data from the maximum value. In the above example, the range is 9.

Read more ►

How to Hide Formulas in an Excel Spreadsheet

1. Locate the cells with the formulas you want to hide.
2. Select those cells by clicking and dragging with your mouse.
3. Right-click on your selection, and select 'Format cells' from the resulting menu. In the window that appears, select the tab titled 'Protection' and check the check box labeled 'Hidden.' Click 'OK' to finish.
4. Finally, select the 'Review' tab in Excel's main window, and select the 'Protect Sheet' option. This final step will ultimately hide your formulas. If you want to view them again, disable the protection on the sheet. You can also password-protect the sheet if need be.

Read more ►

How to Sort in Microsoft Excel 2007

1. Open your data file in the Excel 2007 program. Place your mouse over the first letter that represents your first column (usually column A). A down-pointing arrow will appear. Left-click your mouse and hold, then drag the mouse across to select all of your columns. Let go of the mouse and look through the file to assure that all of your columns are highlighted (selected).
2. Click the 'Data' tab on the Excel 2007 main menu. Go to the 'Sort Filter' section. Click 'Sort'---a dialog box will appear.
3. Choose the column letter by which you want to sort your list under the 'Sort by' box. For instance, if you have a mailing list containing last name (as column A), first name (as column B), address (as column C) and phone number (as column D), you'll probably want to sort your list by last name (column A), so that you can refer to customers by name quickly. If you plan to call all customers within a certain area code, you'd sort the list by phone number (column D).
4. Select your 'Sort On' attribute. The choices are values, cell color, font color and cell icon. The values choice will be most commonly selected---this looks at the data inside of each cell. The other three options are format-based. For instance, if you have color-coded various cells in your list and want to display them all next to each other on the sheet, you would sort on 'cell color.' The same is the case for cell fonts and icons you have inserted into the cell using 'Conditional Formatting' (see Resources for more information on cell icons).
5. Pick the 'Order' that you want the information to sort by. For values, it is either A to Z or Z to A. For the other sort options (cell color, font, icon), you choose which color or icon you want to appear first or last and the rest will settle in between. Click 'OK' to sort the data.

Read more ►

How to Make All Excel Rows the Same Height

1. Open Microsoft Excel.
2. Hold the 'Ctrl' key and press 'A' to select all cells.
3. Right-click any row number and select 'Row Height...' from the pop-up menu.
4. Enter a point size for the cell in the 'Row Height' window and click 'OK' to set all rows to that size. Alternatively, you can click and drag the line between row numbers to change them visually. When you release your mouse button, all rows will appear at the selected height.

Read more ►

How to Calculate the Percent Change in Excel

1. Open Microsoft Excel.
2. Click the cell where you want the calculation to be performed.
3. Type '=(B-A)/A' and press 'Enter' to calculate the decimal change from 'A' to 'B.' Replace 'A' with the original number, and 'B' with the new number. As an example, to calculate the decimal change on a sale which reduced the original price of $120 to the sale price of $80, you would enter '=(80-120)/120' and press 'Enter.' This produces 0.3333. You can also use cell references, such as '=(A2-A1)/A1' where 'A1' references the first row of the 'A' column.
4. Right-click the cell, and select 'Format Cells...'
5. Click 'Percentage' under the 'Category' list of the 'Number' tab. Select the number of 'Decimal places' and click 'OK' to change the decimal change into percent change. The default number of decimal places is two. In the example, 0.3333 is converted into '33.33%.'

Read more ►

How to Create Check Boxes

Set Up Word or Excel
1. Click 'Start' > 'All Programs' > 'Microsoft Office' and then either 'Microsoft Word 2010' or 'Microsoft Excel 2010.'
2. Select 'Options' from the 'File' tab in Microsoft Office or Microsoft Excel's ribbon. Select 'Customize Ribbon.'
3. Select the 'Developer' check box under 'Main Tabs.' Click 'OK.'
Check Boxes in Word
4. Select 'Design Mode' from the 'Developer' tab of the ribbon.
5. Click the place on the document where you want to insert the check box.
6. Click the 'Check Box Content Control' icon in the 'Controls' group of the Developer tab.
7. Select 'Properties' in the 'Controls' group of the Developer tab. Enter an optional title or tag. Change any other optional settings pertaining to formatting and click 'OK.' Enter labels and instructions.
8. Add more check boxes if you desire groups. Select the group of check boxes by pressing the Ctrl key and clicking on each you want. Select 'Group' from the 'Controls' group of the Developer tab.
9. Select 'Save' from the 'File' tab.
Check Boxes in Excel
10. Select 'Insert' from the 'Developer' tab of the ribbon bar. Select the check-box icon in the 'Form Controls' group.
11. Select the cell of the Excel worksheet where you want the upper left corner of the check-box control located.
12. Select 'Properties' from the 'Controls' group of the Developer tab.
13. Select one of three initial states for 'Value' for the check box: unchecked (default), checked or mixed (applies for groups of check boxes where one or more values may be checked initially).
14. Enter the cell reference that contains the initial value of the check box under 'Cell link,' e.g., $C$1. You may click the icon and then click the cell in the spreadsheet if you prefer. Click 'OK.'
15. Click to the right of the check box to edit the label text. Select 'Save' from the 'File' tab.
Protect the Word Form for Distribution
16. Select 'Restrict Editing' on the Developer tab in Word.
17. Check the box for 'Allow only this type of editing in the document' in section two of the 'Restrict Formatting and Editing' pane located on the right of your screen. Select 'Filling in forms' from the drop-down list in section two 'Editing restrictions.'
18. Click 'Yes, Start Enforcing Protection' in section three 'Start enforcement.' You may enter a password or leave the password fields blank. Click 'OK.'

Read more ►