How To Calculate Mortgage Payments in Excel

1. Start a new, blank workbook in Excel. In Excel 2007, click the 'Office' button and click 'New'; then click 'Microsoft Online.' In Excel 2003, go to the 'File' menu and click 'New'; then go to the 'Templates' section of the task pane.
2. Type 'mortgage calculator' in the 'Search Microsoft Online' box and click 'Go.' A list of possible templates will appear.
3. Select the 'Mortgage Payment Calculator' template and click the 'Download' button. The Mortgage Payment Calculator template will open as a new Excel spreadsheet.
4. Enter the mortgage loan amount in cell C7. Enter the interest rate in C8. Type the number of years of the mortgage loan in cell C9. The worksheet will calculate the monthly payment amount, as well as other data, such as the number of payments and equity.
5. Enter up to five different mortgage-loan amounts into columns C through G. This will make it easier to compare options, such as a 15-year mortgage vs. a 30-year mortgage, or different interest rates or loan amounts.

Read more ►

How to Prepare an Excel PivotTable

Using Excel Data
1. Open the Excel file that has the information that you want to make into a PivotTable.
2. Edit the range of data that you will use to remove any blank rows. Also place a column name in the top row, for any column that doesn't have one.
3. Select any cell in the range of data that you want to use.
4. Click on the 'Insert' tab at the top of the screen, and then click the 'PivotTable' button that appears on the toolbar. A small window will appear, and Excel will automatically select the entire range of data. Press 'OK' to continue, and the PivotTable will be created on a new worksheet.
Using External Data
5. Open the Excel 2010 program by double-clicking the Excel icon.
6. Click the 'Insert' tab at the top of the screen and then click the 'PivotTable' button on the left side of the toolbar.
7. Select the 'Use an External Data Source' radio button and then press the 'Choose Connection' button just below the radio button. A list of available connections will show up.
8. Click the connection you want to use and then click 'Open.' If you do not see the connection that you need, click on 'Browse for more' and then navigate to the connection file and click 'Open.'
9. Select 'New Worksheet' to place the PivotTable on a new worksheet, or select 'Existing Worksheet' and then input the cell where you want the PivotTable to go. Click 'OK' when you are done.
Placing Information in the PivotTable
10. Select a cell anywhere in the PivotTable placeholder graphic to bring up the field list on the right side of the screen.
11. Place checkmarks on the field list to the right side of the window, next to the fields you want to add to the PivotTable. Each field that you see is equal to one column of data from your original data set. When you place a checkmark, you will see the field appear both on the PivotTable in the main window, and in one of four boxes to the right of the field list. Each box corresponds to a different area on the PivotTable.
12. Drag and drop fields between the boxes on the right of the screen to place them where you want them on the PivotTable. Fields in the 'Column Labels' box will appear at the top of the PivotTable, while fields in the 'Row Labels' box will appear along the left side. Any field in the 'Values' box will have its data make up the body of the PivotTable, and any field in the 'Report Filter' will appear in a small drop-down box above the PivotTable, where you can filter the entire table.

Read more ►

How to Turn Off Sharing in Excel 2007

1. Launch Microsoft Excel and locate the 'Review' tab on the ribbon located on the top of the screen.
2. Click the 'Share Workbook' tool located in the 'Changes' group to launch the 'Share Workbook' dialog box. You should see a check mark next to the 'Allow Changes' check box.
3. Click the 'Allow Changes' check box to clear the check mark. Click 'OK' to save your changes and disable sharing.

Read more ►

How to Interpret the Linear Regression Summary in Microsoft Excel 2003

1. Right-click on the regression line in your chart, and choose Properties. Check 'Display equation on chart' and 'Display R-squared value on chart'. Click OK.
2. Look at the R-squared value displayed next to the regression line. The R-squared value represents the amount of variability in the data that is explained by the linear regression analysis. If all the data lies exactly on the regression line, the R-squared value will be 1. If the R-squared value is 0, that means there is no correlation between the two datasets.
3. Turn your attention to the equation listed above the R-squared value. It will be of the form 'y = m x b', where m and b have been replaced by numbers. This equation describes the linear regression line. The 'm' value is the slope of the line, and the 'b' value is the location where the line crosses the vertical axis. You can use this equation to predict values in the dataset based on their value on the horizontal axis; just multiply their horizontal location by the 'm' value and then add the 'b' value to the result; this will give you the best estimate of the location of that point based on the linear regression analysis.
4. Look at the slope of the line. If it slopes downwards to the right, the data is 'negatively correlated,' if it slopes upward, the data is 'positively correlated.' Positive correlation means that the datasets tend to agree with or reinforce each other; negative correlation means that they tend to be at odds or mutually exclusive.

Read more ►

How to Create an Excel Spreadsheet to Figure Out Sick Leave

1. Type 'Vacation and Sick Leave Record' in cell E1. In cell E2, write the period that the sick leave worksheet is applicable to. For example, write '1/1/2010 to 12/30/2010.'
2. Type the employee's name in cell A4. Optionally, type their maximum sick leave accumulation on the next line in days or hours.
3. Type the word 'Month' in cell A6. Type the months January through December directly underneath, in the same column, with one month per row.
4. Type the word 'Amount' in cell B6, the word 'Used' in cell C6 and the word 'Balance' in the cell D6.
5. Enter the total number of the employee's available sick leave hours in cell B7. For example, if the employee has 120 hours, write '120.'
6. Type the following formula into cell D7:=B7-C7.Copy the formula to cells D8 to D17 by dragging the fill handle (the little black square in the bottom right corner of the cell) to cell D17.
7. Click on cell B8, type '=', then click on cell D7. This transfers the balance from the previous month to the 'amount' column for the beginning of the second month. Drag the fill handle of cell B8 to cell B17.

Read more ►

How to Password Protect Excel 2003

1.
In order to protect your Microsoft excel worksheet go to TOOLs menu select PROTECTION. From there you should see a menu like the picture on your left.
2.
From there you will be given a series of options that will allow you to protect a Microsoft excel worksheet, workbook or a range on your specific file. As you can see from the image on your left you can get very granular with this.
3. Make your appropriate adjustments and then click OK. You should be prompted to retype the password. Once that is done make sure you save your changes and close the document and reopen it to see if your password took.

Read more ►

How to Create a Box Plot in Microsoft Excel 2007

Set Up the Plot Data
1. Create a table with a column for each data set. In the table rows, add formulas for the calculations of (in order) the minimum, first quartile, median, third quartile and maximum for each data set using the Excel functions MIN, MAX, MEDIAN and QUARTILE (or PERCENTILE). This is the 'summary table.'
2. Create a second table with the same rows and columns as above. This table will contain the values used for the plot. This is the 'plot data table.'
3. Add a formula for each data set's maximum value in the plot data table that is the maximum minus the third quartile values from the summary table.
4. Add a formula for each data set's third quartile value in the plot data table that is the third quartile minus the median values from the summary table.
5. Add a formula for each data set's median value in the plot data table that is the median minus the first quartile values from the summary table.
6. Add a formula for each data set's first quartile value in the plot data table copying the first quartile value from the summary table.
7. Add a formula for each data set's minimum value in the data table table that is the first quartile minus the minimum values from the summary table.
Create the Plot
8. Select the range containing the third quartile, median and first quartile of all the data sets in the plot data table.
9. Open the Insert ribbon. Click 'Column' on the charts. Select 'Stacked Column' from the '2-D Column' type charts.
10. Click 'Select Data' under 'Design' on the 'Chart Tools' section of the ribbon. Modify the 'Series' order so 'Series 3' is the bottom segment on the chart and 'Series 1' is the top segment on the chart.
11. Click on the bottom segment of one of the columns in the chart. Select 'Layout' under 'Chart Tools' on the ribbon. Click 'Error Bars' and select 'More Error Bars Options.' The 'Format Error Bars' window appears.
12. Select 'Minus' for the 'Direction.' Select 'Custom' for the 'Error Amount.' Click 'Specify Value' and select the range for all the minimum values in the plot data table for the 'Negative Error Value' in the 'Custom Error Bars' window. Click 'OK' to exit this window and 'Close' on the next window to return to the chart.
13. Click on the top segment of one of the columns in the chart. Go to the 'Format Error Bars' window as above. Choose 'Plus' for the 'Direction' and use the maximum values in the plot data table for the 'Positive Error Value' range for the 'Custom Error Amount.'
14. Right-click on the bottom segment of one of the columns on the chart and select 'Format Data Series.' Set the 'Fill' to 'No fill.' Set the 'Border Color' to 'No line.' Close the window.
15. Delete the chart legend. Add polish by formatting the chart colors, adding a title and other finishing touches.
16. Select the rows containing the plot data table. Under 'Home' on the ribbon, click 'Format' and select 'Hide Rows' under the 'Hide Unhide' sub-menu.

Read more ►

How to Do Percentages With Excel 2003

1. Open Excel 2003, and open a workbook that contains a column with amounts and another column with totals. Click 'File' on the menu bar, and click 'Open.' Browse your files, and locate the workbook. Click the workbook and select the 'Open' button. The workbook opens.
2. Click in the next available column in your workbook. Type '=.' Click in the first cell that contains the first amount. Type '/.' Click in the first cell that contains the first total, and press the 'Enter' key. A value is generated.
3. Highlight the column containing this new formula. Click the '%' symbol on the standard toolbar. Your value is transformed into a percentage.

Read more ►

How to Maximize a Sheet in Excel 2007

1. Open an Excel worksheet by clicking on the round 'Office' button in the top left-hand corner of Excel 2007. Select 'Open' from the drop-down menu on the left. Choose the file name of the Excel document that you wish to open.
2. Click on the 'maximize' button in your Excel work window. You'll find it just to the left of the 'close' button in the top right corner. An 'X' identifies the close button, and a square, the maximize button. The maximize function changes your worksheet to maximum size within your open Excel document.
3. Click on the 'restore down' button to make your worksheet smaller. The restore down function changes your worksheet to smaller size within your open Excel document. You'll find the restore down button in the same location as the maximize button. The two buttons function as a toggle: When you have maximized the sheet, the button represents restore down.

Read more ►

How to Sort and Filter in Microsoft Excel

1. Open Microsoft Excel and go to 'File' and 'Open' to open the document containing data you want to sort and filter. Highlight the document and click 'Open.' The document will open in a new window.
2. Highlight the data you want to sort. You may select an entire worksheet, a column, multiple columns or specific data within a column.
3. Complete a simple sort by selecting the 'Home' tab from the ribbon and from the 'Editing' group selecting 'Sort and Filter.'
4. Click the appropriate option that matches your preferred sort. For example, if your data is a group of dates, click 'Sort Oldest to Newest' or 'Sort Newest to Oldest.' If your data is text, select either 'Sort A to Z' or 'Sort Z to A.' If your data consists of numbers, select 'Sort Smallest to Largest' or 'Sort Largest to Smallest.' The sort will occur upon clicking this command.
5. Complete an advanced custom sort by selecting 'Editing', 'Sort and Filter' and 'Custom Sort.' Select the appropriate options for your sort from options that include columns, value or order---with or without the column headers. You may also sort by multiple levels. For example, your data may be sorted alphabetically at the first level and by date at the second level.
6. Filter your data by selecting 'Home,' the 'Editing' group and 'Filter.' A drop-down arrow appears next to the first cell of data. Click the drop-down and uncheck any data that you want to filter out. You may perform a sort based on the data that remains.

Read more ►

How to Use Absolute References in Microsoft Excel

1. Enter a value into the cell you want to keep constant.
2. Select another cell in the Excel spreadsheet for the formula that will use the absolute reference.
3. Enter the formula. Use the dollar sign ($) in front of the row reference to keep to keep the row absolute or in front column reference to keep it absolute. For instance, '$C$21' is an absolute reference for cell 'C21,' while 'C$21' an absolute reference to row '21' but but a relative reference column 'C.'

Read more ►

How to Convert Word to Excel 2007

1. Launch Microsoft Word and open the document that you want to convert to Excel. Click once on the Microsoft Office button and select the 'Open' option. Locate the folder in which the document is saved in the 'Look in' section of the 'Navigation Pane.' Double click on the file name to open it. If you do not have either program, a free trial may be downloaded from the Microsoft site.
2. Save the Word document as a text (TXT) file. Click once on the Microsoft Office button and select the 'Save As' option. Select a folder in which to save the text file using the 'Save in' menu. Use the 'Save as type' menu to select the 'Text (TXT)' option. Type a name for the text file in the 'File name' field and click once on the 'Save' button.
3. Launch Microsoft Excel 2007 and import the text file. Click once on the 'Data' tab and locate the 'Get External Data' section. Click once on the 'From Text' option. Use the 'Look in' menu to locate the folder in which the text file is saved. Double click on the file name to open it.
4. Use the 'Text Import Wizard' to convert the information from the Word document to Excel. Select the 'Delimited text files (.txt)' option. Click once on the 'Next' button. Depending on the type of data that was in the Word document, select the appropriate separator, for example commas or tabs, to separate the text into Excel fields. Click once on the 'Finish' button to complete the process of converting the Word document to Excel 2007. Remember to save the Excel 2007 file by clicking once on the Microsoft Office button and once on the 'Save As' option.

Read more ►

How to Unlock Grayed Out Menus in Excel 2007

1. Position your cursor so that it is on one of the sheet tabs at the bottom of the screen. Make sure the sheet tab is highlighted.
2. Right-click the sheet tab. Choose 'Ungroup Sheets' from the drop-down menu.
3. Click one of the menus. All of the options should now be visible. If the options are still grayed out, right-click the sheet tab again and choose 'Ungroup Sheets.'

Read more ►

How to Remove a Contribute Toolbar From Excel 2003

1. Open Excel. Customizations to the toolbar interface are always accomplished within the program. However, it does not matter what file is opened within Excel, or if the program window is open without any files loaded.
2. Locate the 'Contribute' toolbar. It is important to see the toolbar when it is active so you can easily verify if the removal process was successful. As there are many toolbars, removing a single toolbar is not always obvious unless you are aware of its precise location in the program window.
3. Click the 'View' menu. Select the 'Toolbars' submenu. If the 'Toolbars' submenu is not listed, the menu is set to automatically collapse and show only the most frequently used features. Click the double arrow at the bottom of the 'View' menu to fully expand the list of items and select the 'Toolbars' submenu.
4. Locate the 'Contribute' item in the 'Toolbars' submenu. If the 'Contribute' toolbar is turned on, it will show a check mark next to its listing. Click on the 'Contribute' item in the list and the check mark will disappear. The toolbar is no longer active and it is removed from the Excel 2003 screen.
5. Right-click on any toolbar area or button in the Excel 2003 window to display the toolbars context menu as an alternative to using the 'View' menu. Remove the 'Contribute' toolbar in the same fashion using this pop-up menu.

Read more ►

How to Open WB3 Files in Excel 2003

1. Launch 'Excel.'
2. Go to 'File.' Select 'Open,' choose 'Quattro Pro/DOS' from the 'Files of Type' drop-down menu. Navigate to the folder where your WB3 file is located, and double-click to select the file.
3. Go to 'File,' and select 'Save As.' Choose 'Excel 97-2003 Workbook (*.xls)' from the 'Save as Type' options. Click 'Save.'

Read more ►