How to Use Excel to Calculate Beta Coefficient

1. Create a new spreadsheet in Microsoft Excel. Type the historical data for the stock in question and the benchmark in two separate columns.
2. Find the percent change of the data for the benchmark and the stock using the following formula:=((Cell2-Cell1)/Cell1)*100Cell 1 indicates the previous period data point, and cell 2 indicates a current period data point. Drag the formula with the mouse down the two columns.For example, if the stock's daily stock price was $100 per share in June 2010 and is $125 in June 2011, the $125 is cell 2 and the $100 is cell 1. Subtract 100 from 125 to get 25. Divide 25 by 100, which equals .25. Multiple this by cell 1 or 100. The percent change for this particular stock is 25 percent.Complete this formula for the SP 500 as well, which is the benchmark. When finished, you will have a percent of change for your stock and the SP 500.
3. Figure out the beta coefficient by using the 'SLOPE' function in Excel. The slope function is '=SLOPE(range of % change of equity, range of % change of index).' For example, if there were daily changes to Apple's stock price in cells A1:A260, and the daily changes to the SP 500 were in column B1:260, the function would be '=SLOPE(A1:A260,B1:B260).'

Read more ►

How to Add an Axis Title to an Excel Chart

1. Start Microsoft Excel 2007 and open a spreadsheet from your files that contains a chart to which you would like to add an axis title.
2. Select the entire Excel chart by clicking in the white area of the chart. A light blue line will surround the entire chart indicating it has been selected.
3. Choose the 'Layout' tab at the top of the Excel screen and locate the 'Labels' group within the 'Layout' ribbon.
4. Click the 'Axis Titles' button in the 'Labels' section of the 'Layout' ribbon to display a drop-down list.
5. Point to 'Primary Horizontal Axis Title' to create a title for your 'X'-axis or point to 'Primary Vertical Axis Title' to create a title for your 'Y'-axis.
6. Click to choose where you want the selected title to be displayed in relation to the rest of the chart. The selections you see will depend on how your chart is formatted. A default title for the chart will appear the in the location you have chosen.
7. Add your own title by clicking on the default title and typing your new title with your keyboard. Press the 'Enter' key when you are finished typing the title.

Read more ►

How to Open .Xlsx Files in .Xls

1. Download the Microsoft Office Compatibility Pack to your computer.
2. Launch the older version of Excel.
3. Click the 'File' menu and select 'Open.' Use the 'Look in' menu to find the folder in which the XLSX file is saved. Highlight the XLSX file name and click 'Open.'

Read more ►

How to Create a Stacked Column Chart With the Data Plotted in Rows Using Excel

1. Open the Excel 2010 file that hold the data that you want to use in a chart.
2. Check the data to ensure that you have headers in the first column. If you don't, right-click on the letter above the first column and choose 'Insert.' Type in a header for each row into the column you just created.
3. Click on any cell that is part of your data field. Excel will automatically expand the selection to all adjacent cells when you create the chart. If you need to chart a specific area of your data, click on the top left cell of the area you want to chart, then hold 'Shift' and click on the bottom right cell.
4. Select the 'Insert' tab at the top of the screen. Locate the 'Charts' area, and click on the 'Column' button to bring up a pop-up menu that includes all of the column chart options. Select the 'Stacked Column' option to create your chart.
5. Right-click anywhere on the chart, and choose 'Select Data.' A small window appears on the screen. Click the 'Switch Row/Column' button in the middle of the screen, and then click 'OK.' Your graph changes to show each row of data as a stacked column.

Read more ►

How to Put a Command Button on a Spreadsheet in Excel

1. Open the spreadsheet in Excel that you want to add a command button to. Click the 'Office File' button on the main ribbon and click 'Excel Options.' Enable 'Show Developer tab in the ribbon' if it isn't already checked and click 'OK.'
2. Click the 'Developer' tab, then click 'Insert.' This brings up the list of controls that you can insert. Click the 'Button' link from the list of options.
3. Click in the spreadsheet where you want the command button to be placed.
4. Choose a macro from the list to assign to your command button, then click 'OK.'
5. Right-click the button and click 'Format Control' to format how the button looks.

Read more ►

How to Make Ledgers on Excel

1. Open a new Microsoft Excel spreadsheet. Type 'Opening Balance' into Cell A1. Type the opening balance into cell D1.
2. Type the name of the first credit or deduction into cell A2. Type the amount -- positive for additions, negative for deductions -- into cell C2. Type the formula =(A1 C2) into cell D2.
3. Input the names of amounts additional additions and credits as you become aware of them. Each time click cell D2 and drag the black border around it down into the 'D' column of the row where you notate the new transaction to display the new running balance in the 'D' cell of that row.

Read more ►

How to Password Protect an Excel 2007 File

1. Open the Excel 2007 file. Click the 'Office Button' located in the top-left corner.
2. Select 'Prepare' from the list. Click the option to 'Encrypt Document.' Enter a password. Click 'OK.' Re-enter the password. Click 'OK.'
3. Select 'Save As' from the list. Click 'Tools' located in the lower-left corner of the 'Save As' box. Select 'General Options' from the list. Set a password to open or modify the file. Click 'OK.' Re-enter the password. Click 'OK.'
4. Click the Microsoft Office Button and select 'Save.'

Read more ►

How to Insert a Tick Mark in Excel

1. Reveal the Developer tab, if necessary. In Excel 2007, click the 'Office' button and choose 'Excel Options.' In the Popular options set, check the box labeled 'Show Developer tab in the Ribbon.' In Excel 2010, click 'File' then 'Options.' Choose 'Customize Ribbon' in the Categories pane and check 'Developer' in the list of tabs.
2. Click the 'Developer' tab. Choose 'Insert' and from the Form Controls group choose the small box with a check mark in it.
3. Draw a box with the cursor to define the location and size of tick mark and its label. These remain changeable.
4. Click on the text, marked 'Check Box 1,' delete that text and add your own.
5. Right-click anywhere in the form and choose 'Format Control...' to set the form's formatting properties, such as color and lines. Choose the 'Control' tab to determine whether the box begins with a tick or without one. Add 3-D shading if you like.

Read more ►

How to Copy VLookup to Multiple Rows

1. Open the Excel 2010 file where you want to copy the VLookup function down to multiple rows.
2. Click on the cell that contains your VLookup formula. Place your cursor into the formula bar located just above the spreadsheet so that you can edit the formula.
3. Place dollar sign symbols in front of any ranges that you don't want to change when you copy your formula down. Excel uses relative references when copying formulas, so if your VLookup function referenced the range A1:D10 and you copy that formula down one cell, the range would change to A2:D11. By placing dollar signs in front of each reference element, such as $A$1:$D$10, you ensure that the reference will not change when you copy the formula. This is especially important when copying VLookup formulas, as the table range should remain constant. Press 'Enter' when you are done making changes.
4. Click the cell that contains the VLookup formula. Move your mouse to the small, black box, called a fill handle, in the lower right corner of the cell. Your mouse cursor will change into a plus sign when you are correctly positioned over the box.
5. Click and hold the mouse button on the fill handle. Drag the mouse down the spreadsheet until you reach the last row where you want the VLookup formula to reside. Release the mouse button, and the formula will be instantly copied to all the rows between the original cell and the cell where you released the button.

Read more ►

How to Enable Scrolling in Excel 2007

1. Open the Microsoft Excel 2007 file on which you want to enable scrolling.
2. Click on the 'Microsoft Office' button from the top of the page and click on the 'Excel Options' button.
3. Click on the 'Advanced' button from the left side of the Excel Options dialog box.
4. Click on the box next to the 'Zoom on Roll with Intellimouse' option so it's deselected.
5. Click on the 'OK' button, and you should now be able to scroll throughout your worksheet.

Read more ►

How to Change the Case in Microsoft Excel 2007

1. Open the Excel worksheet that has the case you want to change.
2. Decide which case you want to use. You can choose from uppercase, lowercase or proper case (the first letter is capitalized).
3. Use a blank cell, row or column next to the cell you want to change. If there is not a blank cell, row, or column, right-click next to the cell you want to change, select 'Insert' and choose whether you want to insert a new row or column. You can delete this later. If you need to change the entire spreadsheet, start with the column available at the end of the spreadsheet.
4. Enter '=' and the function for the case you want to change to in the blank cell. Use 'LOWER,' 'UPPER,' or 'PROPER.' For example, if you wanted to switch to lowercase, you would enter into the cell '=LOWER.'
5. Next to the case function enter a parenthesis. Inside the parenthesis, enter the cell reference for the first cell where you want to change the case. For example, if you are changing the case of the text in cell A1 to lowercase, you would have entered '=LOWER(A1)' at this point.
6. Highlight the cell you entered the function in and the rest of the row(s) or column(s).
7. Click the 'Home' tab located at the top of the screen and click the 'Fill' button. Select 'Down,' 'Right,' 'Up,' or 'Left' depending on the direction of the cells in which you're changing the case. If you need to change cells in more than one direction, then click 'Fill' again and select the next direction. The text with the case change will appear.
8. Highlight the cells with the case change, right-click, and select 'Copy.'
9. Click the first cell with the old case, which would be cell A1 in the example. Click the 'Paste' button and select 'Paste Special.' Select 'Values' in the box that opens and click 'OK' to paste the converted text. This will paste the new case into all the cells.
10. Delete the duplicate cells by highlighting them and pressing the 'Delete' button. If you had to insert a new row or column, select the row or column, right-click, and click 'Delete.'

Read more ►

How to Use a Fill Handle for Numbers in Excel 2003

1. Open Excel 2003 and click in cell A1. Type '1' in cell A1 and '2' in cell A2. Highlight these two cells.
2. Notice the fill handle in the lower right hand corner of these highlighted cells. Point to the fill handle. Your mouse will become a thin black ' .' This is your indicator that the fill handle can be dragged to fill in the sequence.
3. Drag the fill handle down a few cells. Notice the rest of the sequence is automatically populated in the cells.

Read more ►

How to Delete a Named Range in Excel 2007

1. Open your Excel file.
2. Click 'Formulas.'
3. Select 'Name Manager.'
4. Highlight the named range.
5. Press 'Delete.'

Read more ►

How to Make a Decision Tree in Excel

1. Draw a square in a blank Excel worksheet. In Excel 2007, click 'Insert,' then 'Shapes' and then click on a square. In Excel 2003, click on the 'Rectangle' autoshape button on the drawing toolbar and drag the square to your worksheet.
2. Right-click on the square and click 'Add text.' Type the major decision into the box. For example, you might be deciding whether to save or spend a recent windfall, so write 'Save or Spend' in the box.
3. Select a 'Line' shape from the toolbar. Click on the right edge of the square and drag the line to a length of an inch or two (you can change the lengths of the lines at a later time by clicking and dragging on them). In the above example, you would draw two lines of equal length (one for 'Spend' and one for 'Save').
4. Draw two more squares at the end of the lines, repeating the process for inserting a square from Steps 1 and 2 to draw the square and insert text. In the given example, write 'Spend' in one text box and 'Save' in the other. This creates your first set of branches.
5. Repeat the above process to as many branches as you need to your tree.

Read more ►

How to Upgrade From Excel 2003 to 2010

Office 2010 Installation
1. Place the Office 2010 installation disc into your computer or download Office 2010 from the Microsoft website.
2. Follow the prompts to initiate the installation of Office 2010. If you have downloaded a version of the software, you may be prompted to unzip the files. For CDs, you will have to enter a product key, which can be found on your CD case.
3. Click 'Customize' in the 'Choose the installation you want' dialog box.
4. Right click and then click 'Not available' for all programs except for Excel 2010.
5. Click 'Install now' to install Excel 2010.
Excel 2010 Download
6. Visit the Microsoft Excel 2010 website.
7. Purchase a copy of Excel 2010. Click 'Download' to download a copy of Excel to your computer or click 'Ship it' to receive a disc.
8. Follow the instructions for installation. For CDs, place the CD into your CD drive and follow the on screen prompts. For downloads, follow the on-screen prompts after downloading the file from the Microsoft website.

Read more ►