Excel

Browse » Home » All posts

Thursday, November 15, 2012

How to Calculate Compound Interest in Excel

1. Create a new blank Excel document by clicking on the 'File' menu and selecting 'New.'
2. Enter a column heading corresponding to the following values in the first row of your document starting in column A.Column - Heading
A - Amount Invested
B - Annual Percentage Rate
C - # Times Compounded Annually
D - # of Years
F - Future Value
3. Enter the desired values for calculation in row 2 starting in column A. Example values are listed below for each column. This represents $1,000 compounded quarterly at a rate of 2.25% for 10 years.A - 1000
B - .0225
C - 4
D - 10
4. Enter the following function in row 2 of column F.=A2*((1 B2/C2)^(C2*D2))The result of this formula should be approximately 1251.53.
5. Apply this formula to more rows by clicking on it and dragging your mouse down the desired number of rows. When you have highlighted enough rows in one column hit the Control (Ctrl) and D keys at the same time to 'Fill Down' the formula.

How to Enter Formulas in Excel to Show Not Less Than Zero

Hide Negative Values With Conditional Formatting
1. Select the cell(s) in your Excel spreadsheet you wish to format.
2. Click “Conditional Formatting” under the “Format” menu.
3. Select “Cell value is” from the first drop-down menu for Condition 1.
4. Select “less than or equal to” from the second drop-down menu.
5. Enter 0 in the next box.
6. Click on the Format button and select white in the color drop-down menu. If the background color of your cells is something other than white, select a color that will blend in and make it appear as if the cell is empty.
7. Click OK. Any cells you have applied this conditional formatting to will appear to be blank if the value in the cell is equal to or less than zero. If you place your cursor in the cell you will be able to see the cell’s actual value in the formula bar at the top of the worksheet.
Display Negative Values As Blank Cells or Dashes
8. Use the IF function to specify the format of a result that is equal to or less than zero. The syntax of the IF function is IF(logical_test,value_if_true,value_if_false)
9. Enter 1 in cell A1 and enter 2 in cell A2 of your worksheet.
10. Create an IF formula in cell A3 by typing: =IF(A1-A2>0,A1-A2,“-”). In this example the logical test is whether cell A1 minus cell A2 is greater than zero. If it is the “value_if_true” it will display; if it isn’t, the “value_if_false” will display. In our formula we defined the value_if_true as the difference between cells A1 and A2 and the value_if_false as a dash. Therefore, if A1-A2 is zero or less you’ll see a dash in cell A3, if the result is positive, you will see a number.
11. Change the values in cells A1 and A2 to see how it affects what displays in cell A3.

How to Create a One Way ANOVA in Excel

1. Open the Excel 2010 file where you want to create your one way ANOVA. Enter your data sets into the Excel cells. Separate your data sets by columns with the top cell in each column describing the set.
2. Click the 'File' tab at the top of the screen. Choose 'Options' from the list that appears on the left side of the screen. Select 'Add-Ins' from the left side of the window that appears. Click 'Go' at the bottom of the window.
3. Place a check mark next to 'Analysis ToolPak' and click 'OK.'
4. Click the 'Data' tab at the top of the Excel window. Find the 'Analysis' area and click the 'Data Analysis' button found there. Click on 'Anova: Single Factor' in the pop-up window and click 'OK.'
5. Place your cursor into the 'Input Range' box. Click and hold on the top-left cell in your data sets. Drag your mouse to the bottom-right cell and release the button. If you included the headers in your selected range, click the 'Labels in First Row' box in the ANOVA window.
6. Click the radio button next to 'Output Range' and then place your cursor into the text box next to the radio button. Select a cell on your worksheet where you want the ANOVA information to go. Click 'OK' to create the one-way ANOVA.

Tuesday, November 13, 2012

How to Remove Extra Rows in Excel 2003

1. Open Excel 2003. Click the 'File' menu and select 'Open'. Locate the spreadsheet with the extra rows and double-click its file name.
2. Press and hold down the 'Ctrl' key on the keyboard. Click once on each row number, in the leftmost column, to delete.
3. Right-click any of the highlighted rows. Select 'Delete Row'. The rows are deleted and the rest of the spreadsheet bumps up to replace those rows.

How to Use Excel 2007

1. Enter text or numbers into a cell by clicking in the cell, typing the desired text, and press 'Enter.' Press 'Tab' to move to the next cell.
2. To insert a row of cells into your spreadsheet, select where you wish your new row to be, click the Home tab and the 'Insert' button, and then click 'Insert Sheet Row.' Create a new column by clicking 'Insert Sheet Column' under 'Insert.'
3. To merge multiple cells together, either vertically or horizontally, select the cells you wish to merge, and click 'Merge Cells,' which appears under the 'Merge and Center' button in the Alignment group under the Home tab.
4. Add two or more cells together by first clicking in a cell. Type '=SUM(' (open parenthesis), and enter the cells you wish to be added together, followed by ')' (close parenthesis). For example, =SUM(F4,F5,F6) would add the values of cells F4, F5 and F6. Write a long list of cells quickly by entering the starting cell of a row or column, typing ':' (colon), and entering the ending cell, for example, F4:F13. Use calculation operators to multiply, divide, add, subtract, or exponentiate multiple cells. For example, a cell with the formula =B7/B3 would divide the value in B7 by the value in B3.
5. Use a variety of functions to fit your needs. Click the 'Insert Function' button on the formula bar to pull down a long list of functions, and click the function you want. To average two or more cells, go down to AVERAGE, and type the cells you wish to average, between parentheses.

How to Enter Dates in SUMIF

1. Open the Microsoft Excel 2010 spreadsheet where you want to create your SUMIF formula.
2. Click on the cell where you want to create your formula. Enter the following into the cell, without quotes: '=SUMIF(A1:A10,'. Change 'A1' to the first cell in the column of dates that you are searching through, and 'A10' to the last cell in that column.
3. Enter the following formula, without quotes, after the comma in your SUMIF formula: 'DATE(2011,x,y),'. Change '2011' to your desired year, 'x' to your desired month and 'y' to your desired day. The DATE function will ensure that your spreadsheet will still work if someone using a different date format opens it.
4. Use a range of dates, instead of a single date, by placing a greater than or less than sign before the date. Since you are using the DATE function, you will need to connect the two commands with an ampersand. For example, to sum all the dates on or before March 30th, 2011, enter the following after the first comma in the SUMIF formula: ''
5. Complete the formula by entering the range of cells that hold the actual values you want to add. Enter this range of cells as follows, without quotes: 'B1:10)'. Change 'B1' to the first cell you want to sum, and 'B10' to the last cell. Press 'Enter' to complete your formula.

How to Construct Yield Curves in Excel 2007

Preparation
1. Identify yield data for the construction of the yield curve. This data requires clear yield information associated with time factors. Yahoo! Finance provides yield data as part of its free service for sharing bond information.
2. Open Excel 2007.
3. Create a new blank worksheet. Press the 'New' button at the top of the program window, or choose the 'File' menu and the 'New' option.
Import Yield Data
4. Type or import the yield information into a new spreadsheet in Excel 2007. If using Yahoo! Finance, Excel 2007 can pull this information directly from the website. Click the 'Data' tab followed by the 'From Web' command. A pop-up window will appear.
5. Type in the URL of the Yahoo! Finance web page that features the yield data you wish to import. Press the 'Go' button.
6. Locate the table on the web page which contains the yield data. A yellow button with an arrow will appear in the upper left corner of the data table.
7. Click the yellow button and then press the 'Import' button in the lower right of the window. The yield data is imported.
Format the Data
8. Copy the 'Yield' column in your data set. Click the column letter at the top and press the 'Copy' button at the top of the program window, or the 'Control-C' keyboard combination.
9. Click the first cell in the first empty column to the right of the data set. Press the 'Paste' button or the 'Control-V' keyboard combination. This places the 'Yield' data as the last column, which Excel requires for creating the proper yield curve chart.
10. Delete the first 'Yield' column. Right-click on the column letter and choose the 'Delete' function.
11. Change the 'Maturity' data in the first column to numeric data only. For example, delete the word 'Month' from the cell which contains the content '3 Month' so the result is simple '3.'
Create Yield Curve
12. Hold down the mouse and drag from cell A2 to cell B9. Press the 'Control' key and do the same from cell E2 to cell E9.
13. Click the 'Insert' tab at the top of the Excel 2007 window. Locate the 'Charts' group and click the 'Scatter' option. Choose any of the 'Scatter' design formats. The Excel Chart Wizard will open.
14. Complete the Chart Wizard. It is acceptable to choose the 'Next' button for each step and use all the default settings.
15. View the resulting yield curve.

How to Disable Scroll Lock in Excel 2007

1. Click the Windows 'Start' menu.
2. Click 'Programs' and click 'Microsoft Office.' Click 'Microsoft Excel' to open Excel.
3. Press the 'ScrLk' or 'Scroll Lock' button on your keyboard. The abbreviation 'SCRL' will appear in the lower-right corner of Excel if scroll lock is turned on and will disappear when it is turned off.

Monday, November 12, 2012

How to Enter Sum Formulas into Multiple Cells in Microsoft Excel 2003

1. Select and activate an empty cell. Do this by left-clicking on that cell; you will also need at least two cells that contain data to be summed.
2. Enter the sum formula perquisites into the cell you have selected. Your first part of the formula should read as: =Sum(.
3. Highlight cells you wish to include in the sum formula. These cells will appear in a blue formula box.
4. After highlighting the desired cells to be included in the sum formula, press the enter button to implement this new formula.

How to Make a Date Change Color After Expiraton in Excel

1. Open Excel 2007 and select a workbook. Click the 'Office' button and select 'Open.' Browse your files and locate the workbook. Double click the workbook name. The workbook opens.
2. Highlight a range of cells that contains dates. Select the 'Home' tab and click 'Conditional Formatting.' Select 'New Rule.' Select 'Format only cells that contain.' In the Edit the Rule Description field, select 'Cell Value' and 'greater than.' In the next field, enter your expiration date.
3. Click 'Format' and assign a format to cells that match this condition. To change the color of the cell, modify the 'Fill' tab. Emphasize the text in the cell by changing font using the 'Font' tab. Click 'OK.' The changes are immediately applied to your cells. Cells in the range matching the condition will reflect the fill color selected.

How to Delete an Objects Worksheet in Excel Download

1. Double-click the Microsoft Excel file you downloaded. The file will open in the Excel program window.
2. Locate the 'Objects' worksheet. This will be presented as a tab at the bottom of the Excel window. The tab will contain a clear label called 'Objects'. A worksheet labeled 'Objects' may contain a variety of different content, depending on the intended use of the Excel file. Microsoft uses the 'Objects' term to refer to any integrated content based on another Office application. The 'Objects' worksheet could thus contain Microsoft Word text, or a PowerPoint presentation, displayed within the Excel environment. This could contrast other worksheets which contain conventional Excel-only data.
3. Click once on this 'Objects' worksheet tab to select it.
4. Right-click with the mouse on this 'Objects' tab label. A context menu will appear next to the mouse cursor.
5. Select the 'Delete' option from the pop-up context menu that appears. The 'Objects' worksheet is promptly deleted.

Sunday, November 11, 2012

How to Put Cells in a Microsoft Excel Header

1. Open your Excel spreadsheet. Type the data in the uppermost cells of the spreadsheet. For example, type 'test' in cells A1 and D1.
2. Click the 'Page Layout' tab. Click on the square with the arrow on the 'Page Setup' panel to see additional options.
3. Click the 'Header/Footer' tab, and then select the header to use from the 'Header:' drop-down box, or press the 'Custom Header' button and type your preferred header.
4. Click the 'Margins' tab, and adjust how far the header will be from the top of the page. Press the up or down arrow buttons beside the 'Header' box.

How to Create a Button Object in Excel Spreadsheet

1. Open Excel. Create a new spreadsheet or use an existing sheet.
2. Click the 'Insert' menu at the top of the Excel program window. Choose the 'Object' command. If the 'Object' command is not listed, the menu is automatically collapsed to show only the most frequently-used features. Click the double arrow at the bottom of the menu to expand and show all features. A pop-up window will appear after the 'Object' command is chosen.
3. Click the 'Create New' tab at the top of the 'Object' window if you wish to insert a blank object into the Excel spreadsheet. The object can be edited later, if desired. Alternately, click the 'Create from File' tab if you wish to insert an object from a file that already exists.
4. Choose the file type or the file name for your object. If creating a new blank object, scroll through the list of file types and click once on the program format you wish to create. For example, choose 'Microsoft Word Document' to create an object for Word in the Excel spreadsheet. If inserting an existing file, click the 'Browse' button on the 'Create from File' tab and locate the file on your computer and press the 'Insert' button.
5. Check the 'Display as icon' check box on either tab to create a button for the object. This will prevent the object from displaying as a full document within the Excel spreadsheet. Instead, the user will click a button to launch the object in its native program window.
6. Press the 'OK' button. The button object is created in the Excel spreadsheet.

How to Make a Combo Box in MS Word

1. Open a Word document. Click the “Microsoft Office” button and select “Word Options.” Choose “Show developer tab.” Click “OK.”
2. Click the “Developer” tab on the Word menu ribbon. Click “Design Mode” in the 'Controls' section. Click “OK” to enable macros.
3. Select the area on the document where the combo box will be placed and click the “Combo Box” icon in the Controls section. A gray box will appear on the document that reads “Choose an item.” This will be your combo box. Click on the words “Choose an item” and type in user instructions for the combo box. Click outside of the box when you have finished.
4. Click on the combo box and select “Properties” in the 'Controls' section. The properties options box will open. Type in a title for your combo box in the 'General' section.
5. Check the type of editing control that you want for the box in the 'Locking' section. These options can keep users from deleting the box or editing its contents.
6. Click “Add” in the drop-down 'Properties' section to add a drop-down option to the combo box. Type in a title for the option. By default the option’s title will become the option’s value (the title that appears when the mouse rolls over the option). If you want the option to have a different value, type it the “Value” box. Click “OK” to place the option in the combo box.Repeat the process until you have added all of the box’s options.
7. Click “OK” to save the changes made to the combo box.

How to Link Two Excel Spreadsheets

Create a Document with Multiple Spreadsheets
1. Create and name the first spreadsheet. At the bottom of the worksheet, right-click on the first tab (Sheet 1) and select 'Rename.' Type a new name. Example: Feb26.
2. Create and name the second spreadsheet. At the bottom of the worksheet, right-click on the second tab (Sheet 2) and select 'Rename.' Type a new name. Example: Feb27.
3. Make sure you are in spreadsheet Feb27. Enter numeric data in A1 and B1. In cell A1, type the number 2. Then in cell B1, type the number 3.
4. Enter a formula in cell C1 on the Feb27 spreadsheet. Click in the cell C1 and then click 'Formula' in the ribbon. Click AutoSum and then select Sum. In the formula field, you should see the formula '=SUM(A1,B1).' Press Enter.
5. Check to see that the formula functions correctly. If your formula has been entered correctly, you should see a 5 displayed in the C1 cell.
Make a Link from One Spreadsheet to the Other
6. Navigate to the Feb26 spreadsheet. Scroll to the bottom and click on the Feb26 tab. This will put you on the Feb26 spreadsheet.
7. Add a link from C1 on Feb26 to C1 on Feb27. On Feb26, click on C1 then click on the 'Insert' tab on the ribbon. Select the 'Hyperlink' icon.
8. Specify the cell on the Feb26 spreadsheet. In the formula bar, type: =Feb27!C1
9. Check the cell. Click the C2 cell. You should now see the value that came from cell C1 in Feb27 displayed in the cell C1 in Feb26; that is, you should see the number 5 in cell C1 Feb26.
10. Test to see if it's working properly. Navigate to spreadsheet Feb27 by clicking on the tab at the bottom. Change the contents of A1 to 3. You should see the 5 change to a 6. Navigate back to spreadsheet Feb26 by clicking on the Feb26 tab. You should see a 6 displayed in C1 Feb26.

Subscribe to: Posts (Atom)