Excel

Browse » Home » All posts

Tuesday, November 13, 2012

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.

How to Change the Name Order From First to Last in Excel

1. Open your list of names in Excel. The list of names should be in column A.
2. Enter the following formula in column B1:=RIGHT(A1,LEN(A1)-FIND(' ',A1))Note that there is a space between the quotation marks in the find function.
3. Enter the following formula in column C1:=LEFT(A1,LEN(A1)-LEN(B1)-1)
4. Enter the following formula in column D1:=CONCATENATE(B1,', ',C1)
5. Copy the contents of cells B1, C1 and D1 down for as many rows as you have names. For example, if your name list goes to row 200, make sure you copy the contents of cells B1, C1 and D1 down through B200, C200 and D200.

How to Do Fractions in Microsoft Word

1. Place the cursor in the document where you would like to insert a fraction.
2. Click the 'Insert' tab above the Ribbon. Notice that the options in the Ribbon change.
3. Click the 'Equation' button in the Ribbon. New options will appear.
4. Select 'Fraction' from the Structures section.
5. Choose the desired fraction type from the resulting drop-down menu. An equation box will appear where your cursor is.
6. Type the fraction's denominator and numerator into the equation box. Click outside of the box when finished.

How to Split a Cell in Excel 2007

1. Open the Excel document that has the cell you need to change.
2. Click the 'Home' tab in the Office ribbon at the top of the screen.
3. Click on the merged cell you want to split. The button labeled 'Merge Center' in the alignment group is highlighted in orange by default, indicating that you've selected a merged cell.
4. Click the 'Merge Center' button to split the cell into separate cells.

How to Unprotect an Excel Spreadsheet

1. Open the Excel workbook with the worksheet you want to unprotect.
2. Right-click on the worksheet's tab at the bottom of the window.
3. Select 'Unprotect sheet.'
4. Enter the password, if applicable, and press 'Enter.'

Subscribe to: Posts (Atom)