Excel

Browse » Home » All posts

Saturday, March 17, 2012

How to Make an Amortization Table in Excel

1. Click on the 'Office' button in Excel 2007 and select 'New.'NOTE: If you do not have Excel 2007, click the 'Excel Amortization Schedule Template' link in the 'Resources' section below to download and save the amortization template. Continue to Step 4.
2. Scroll and select 'Schedules' on the left side of the template window.
3. Double-click to select and open the Amortization Schedule template that you want to use.
4. Input the 'Loan amount,' 'Annual interest rate,' 'Loan period in years,' 'Number of payments per year,' and 'Start date of loan.'
5. Press the 'Enter' key to complete each entry and see the Amortization Schedule automatically calculate in the table below.

Friday, March 16, 2012

How to Delete Duplicate Rows in Excel

1. Select the entire spreadsheet by dragging an outline around it with the mouse. Click the 'Data' tab, then click the 'Advanced' button in the 'Sort Filter' box.If you are using Excel 2002 or 2003, click 'Data,' 'Filter,' 'Advanced Filter.'
2. Click the radio button labeled 'Filter the List, in Place' and check the box labeled 'Unique Records Only.' Click 'OK.' All of the duplicate rows will be hidden.
3. Select the entire spreadsheet, and then press the 'Ctrl' and 'C' keys on the keyboard to copy the selection.
4. Press the 'Ctrl' and 'N' keys on the keyboard simultaneously to create a new workbook.
5. Click in the top-left cell (A1) of the new workbook, and press the 'Ctrl' and 'V' keys on the keyboard to paste the spreadsheet into it. All duplicate rows will be gone from the spreadsheet.

How to Create a Simple Report in Excel

1. Launch Excel with a new, blank workbook.
2. Click the 'Insert' tab and choose the 'Header Footer' icon in the Text group. Type a name for your report in the text field at the top of the report that appears.
3. Click 'Go To Footer' in the Navigation group on the Design tab. Type a report footer or click one of the choices in the 'Header Footer Elements' group such as 'Page Number' or 'Current Date' to insert that information in the footer.
4. Click any of the data cells where it says 'Click to Add Data' and then click 'Normal' in the Workbook Views group on the View tab.
5. Type some data labels into the first row and first column of the spreadsheet and press 'Tab' to save the cell and move to the next column or press 'Enter' to save the cell and move to the next row.
6. Point the mouse cursor at the small line between the column letters until the cursor turns into a vertical line with two arrows pointing in opposite directions. Click and hold the mouse button and slide the column width to adjust it as needed. Release the mouse button when the column width is the right size for the data.
7. Click in any cell to enter data into that cell and press 'Tab' to save the cell and move to the next column or press 'Enter' to save the cell and move to the next row.
8. Point the mouse at the top-left corner of a range of cells that contain data that should be grouped together. Click and hold the mouse button down and drag the cursor to the lower-right corner of the data and release the mouse button to highlight (or 'Select') the range of cells. Right-click on any cell in the highlighted range and click 'Format Cells.'
9. Select a number format from the 'Category' list on the 'Number' tab of the 'Format Cells' dialog box. Select from choices for the alignment of the data within the cells on the 'Alignment' tab.
10. Change the selected font size and typeface and apply a font color or attribute from the 'Font' tab. Create a border for the selected data on the 'Border' tab. Click 'OK' when finished formatting the selected cells and repeat for other ranges of data on the spreadsheet.
11. Select a range of cells containing data and labels for the data if desired. Click 'Other Charts' from the 'Charts' group of the 'Insert' menu and click 'All Chart Types' to display a list of all available charts. Click any of the listed charts and click 'OK' to create a dynamic chart of the selected data automatically. Note that the chart automatically updates to reflect any changes made to the data in the selected region.

How to Print an Excel Spreadsheet With Background

1. Open Microsoft Excel and open the spreadsheet you want to add the background to.
2. Select the 'Insert' tab in Excel's main window and select the 'Header Footer' option.
3. In the new view, position you mouse under the word 'Header' and click within that box.
4. Click the 'Picture' button located on the now showing 'Design' toolbar. Navigate to and select the picture you want to use as a background.
5. You will see text similar to '[Picture]' in the header box. This is the place holder for your image.
6. Select the 'Format Picture' button on the Design toolbar. In the window that appears, adjust the size of the image using the Height and Width input boxes or by using the Scale input boxes. When you are finished click anywhere inside the spreadsheet to exit out of the header display. Your picture will appear to the size you just adjusted it to.
7. Click the Microsoft office logo in the top left corner of your window and select the 'Print' option followed by 'Print Preview.' If the picture looks fine with its current size, you can finish by printing your spreadsheet.

How to Calculate Percents in Excel 2003

1. Determine the part and whole of the percentage you want to calculate. For example, assume a student receives 37 out of 75 points on a test.
2. Type the smaller number in cell A1. In the example above, you would type '37' in A1.
3. Type the larger number in cell A2. In the example above, you would type '75' in A2.
4. Type '=A1/A2' in cell A3. This is your percentage in decimal form. In this example, you will see 0.4933. Keep your cursor on this cell.
5. Press the '%' on the top of the window to convert the decimal to percentage. In the example, '0.4933' will change to '49.33%.'

How to Use Sumif MS Excel Together

1. Decide which cells you would like to sum. For example, you might want to sum cells in the range A1:A10.
2. Choose a criteria to sum by. For example, you might want to sum numbers that are less than 100. Other relevant criteria might be more than or equal to.
3. Place the SumIf statement in an empty cell:=SUMIF(A1:A10,'

Thursday, March 15, 2012

How to Apply Formatting Across Multiple Cells in Excel

1. Open the Excel 2010 file that contains the cells where you want to change the formatting. Right-click one of the cells you want to change and choose 'Format cells' from the pop-up menu.
2. Select the tab at the top of the 'Format Cells' window that corresponds to the type of formatting you want to do. You can choose from 'Number,' 'Alignment,' 'Font,' 'Border,' 'Fill' and 'Protection.' Once you select a tab, change the options in the window to get the cell formatted as you like it. Once all of your formatting changes are done, click 'OK.' You will see the cell change according to your formatting.
3. Click the cell you just changed, then click the 'Home' tab at the top of the screen. Locate the 'Clipboard' area on the left end of the ribbon. Double-click the 'Format Painter' button and a small paintbrush will appear next to the cursor. Double-clicking, instead of single-clicking, will let you change the format of multiple cells.
4. Select the cells to which you want to copy the formatting of the original cell. Select multiple cells at once by clicking and dragging the mouse or select each cell one by one. When all the desired cells have the same format, click the 'Format Painter' button again to return the cursor to normal.

How to Write a Macro in Word That Reads Data From Excel

1. Open Excel, then type any value in the first cell of the first spreadsheet. Click the Office button's 'Save' command, then type 'c:\ReadFromExcel.xlsx' for the filename. Click 'Save' to save the workbook, then close Excel.
2. Click the Office button in Word, then click the 'Options' button. Click the 'Developer' checkbox to display the 'Developer' tab, which holds Word's macro commands.
3. Click the 'Developer' tab's 'Visual Basic' button to enter the Visual Basic development environment.
4. Click the 'Tools' menu, then click the 'References' command. Click the 'Microsoft Excel objects' item, which lets your macro access the virtual objects of Excel.
5. Type the following program in the code window. This program creates a link to the Excel application, then close the link. The program doesn't yet do anything with the established link. You'll now use the link to open the Excel workbook you created in step 1.Public Sub ReadExcelData()Dim pgmExcel As Excel.ApplicationSet pgmExcel = CreateObject('Excel.Application')pgmExcel.QuitEnd Sub
6. Type the following new statements after the 'Set' statement. These statements open the Excel workbook you created in step 1, then close the workbook. The revised program doesn't yet read any information from the workbook. The next statement you'll write will read information from the workbook.PgmExcel.Workbooks.Open 'c:\ReadFromExcel.xlsx'
7. Type the following statements after the 'Open' statement. This statement uses the pgmExcel object to read the value of the first cell in the workbook from step 1. The 'MsgBox' statement displays that value.MsgBox pgmExcel.ActiveWorkbook.Sheets(1).Cells(1, 1)
8. Click any of the program's statements, then click the 'Run' command of the 'Run' menu. Your program will run and display the value of the cell into which you typed in step 1.

Wednesday, March 14, 2012

How to Create a Basic Inventory Spreadsheet With Excel

1. Launch Microsoft Excel and click on 'Create Header' to create a header or title for your inventory spreadsheet.
2. Click inside cell 'A1.' Type the word 'Items.' You have now created a column for your items to be listed. If you want the column to stand out, you can also highlight the text, make it boldface or underline it.
3. Create columns that show the date of inventory and the quantity of items on that specific date. Click inside cell 'B1.' Type the text 'Date.' Click inside cell 'C1' and type the text 'Quantity.' Move to cell 'D1' and type the text 'Date.' Continue this pattern as much as you like. You can always add more columns later if necessary.
4. Type the name of the first item being inventoried inside cell 'A2.' Type the date of the inventory and the amount of the item that is stocked inside cell 'B2.' Move to the next item being inventoried by typing the name of the item inside cell 'A3.' Type the date and quantity inside of cell B3.' Continue this pattern until you complete your inventory listing.
5. Save your inventory spreadsheet to your computer's hard drive.
6. Start in column 'C1' for your next inventory. The next inventory after that is column 'D1.' Continue this pattern for each inventory.

How to Create a Break Even Chart in Excel

Label Data
1. Type 'Fixed Costs' into cell A1.
2. Type 'Unit Expense' into cell A2.
3. Type 'Unit Revenue' into cell A3.
4. Type 'Units' into cell C1.
5. Type 'Revenue' into cell D1.
6. Type 'Expense' into cell E1.
Input Data
7. Input your fixed costs of a production into cell B1. For example, if rent for the factory is $25 per month, then type '25.'
8. Input the cost of each unit you produce into cell B2. For example, if each widget costs you $5 to manufacture, type '5.'
9. Input the revenue you make for selling each unit into cell B3. For example, if each widget sells for $10, type '10.'
10. Input the number of units sold in column C, under the words 'Units.' For example, you might write '1' in cell C2, '2' in cell C3, and so on until you write '10' in cell C11.
11. Input the following formula into cell D2:
=$B$3*C2
12. Copy the formula from the previous step by left clicking on D2 and pressing 'Ctrl' and 'C' at the same time.
13. Highlight cells D3 through D11 by clicking D3 and dragging down to D11.
14. Paste the formula by pressing 'Ctrl' and 'V' at the same time.
15. Input the following formula into cell E2:
=$B$1 $B$2*C2
16. Copy the formula from the previous step into the cells in column E by following the same steps as copying and pasting the previous formula.
Create the Chart
17. Click 'Insert' > 'Scatter' > 'Scatter with smooth lines and markers' to insert a blank chart into your spreadsheet.
18. Right click on the chart and click 'Select Data' to display the Select Data Source dialog.
19. Click 'Add' under Legend Entries to display the Edit Series dialog.
20. Type 'Revenue' into the 'Series Name' text box.
21. Click the 'Series X Values' text box, then highlight cells D2 through D11, the entire Revenue column.
22. Click the 'Series Y Values' text box, then highlight cells C2 through C11, the entire Units column.
23. Click 'OK' to exit the Edit Series dialog.
24. Click 'Add' under Legend Entries to display the Edit Series dialog.
25. Type 'Expense' into the 'Series Name' text box.
26. Click the 'Series X Values' text box, then highlight cells E2 through E11, the entire Expense column.
27. Click the 'Series Y Values' text box, then highlight cells C2 through C11, the entire Units column.
28. Click 'OK' to exit the Edit Series dialog.
29. Click 'OK' to exit the Select Data Source dialog.

How to Extract Data Onto a Table Using Excel 2007

Create a Table
1. Open the spreadsheet and highlight the data you want to put into a table.
2. On the 'Insert' tab, click 'Table.' If you want to include the headers in your data, choose the box next to 'My Table Has Headers.' Click 'OK.'
3. Your data will appear as a table. If you've excluded any data, it will still appear. If this isn't what you had in mind, try creating the table again, this time including all your data. Then, when your table is created, choose in the next step to exclude and hide any additional data.
4. Format the table by using the drop-down boxes that appear next to the headers or in the 'Table Tools Design' tab, which appears at the top of the screen when you make a table. Here you can change the color of the table, sort the data and choose to exclude data.
Convert Table Data Back to a Range of Data
5. If you decide to revert back to a range of data, select a cell in your table.
6. Click on the 'Design' tab, then choose 'Convert to Range.' This action removes the table but leaves the data and color formatting intact. The 'Design' tab will disappear.
7. Your data is now in a simple Excel format. You can manipulate it as you would a traditional spreadsheet.

How to Change the Color of a Cursor in Excel

1. Click the 'Start' button in Microsoft Windows.
2. Click 'Control Panel.'
3. Click the 'Ease of Access' category.
4. Click 'Ease of Access Center.'
5. Click the 'Make the Mouse Easier to Use' link under 'Explore All Settings.'
6. Select the color scheme you want to use for the mouse cursor. Choose from a selection of white, black or inverting, in different sizes.
7. Click 'Apply.' Click 'OK.'
8. Open Microsoft Excel.

Tuesday, March 13, 2012

How to Make Excel Calculate Division With Remainders

1. Launch Microsoft Excel and open a new, blank worksheet.
2. Enter the top number of a fraction or the number to the left of the division sign (the dividend) into cell A1. For the division problem '9/4,' type '9' (no quotes) and press 'Enter.'
3. Enter the bottom number of a fraction or the number to the right of the division sign (the divisor) into cell B1. If you are solving '9/4,' type '4' (no quotes) and press 'Enter.'
4. Use the Quotient function to find the integer portion of the result. Type the following into cell C1:=QUOTIENT(A1,B1)Press 'Enter.' For the problem '9/4,' you see a result of '2.'
5. Use the Mod function to find the remainder. Type the following into cell D1:=MOD(A1,B1)Press 'Enter.' If you are dividing 9 by 4, there is 1 left over, and you see a result of '1.'
6. Show the quotient and remainder in a single cell with the letter 'R' designating the remainder. Type the following into cell E1:=C1' R 'D1Press 'Enter.' For the division problem '9/4', you see '2 R 1.'

How to Compare Two Excel Spreadsheets

1. Open Excel and use 'File,' and then 'Open' to open the first file to use for comparison.
2. While the first document is still open, use 'File,' and then 'Open' to open the second file.
3. Select the 'View' tab, and then select 'New Window.'
4. Select 'View Side by Side.'

How to Make a Timeline in Excel

1. Plan your timeline ahead of time. Sketch it out on a piece of paper so you can refer to it as you design the timeline in Excel.
2. Open a new worksheet in Excel.
3. Go to 'File' then 'Page Setup' and change the page orientation to landscape. This will turn your worksheet horizontal.
4. Drag your cursor from the first cell to the length you expect your timeline to take up. Click 'Format,' 'Cells,' 'Alignment' and then check the box next to 'Merge Cells' and click 'OK.'
5. Place your cursor in the merged cell you just created and type the title of your timeline. Click the center alignment button under alignment options to center the title. You can format the size and font for the title under the 'Home' tab and the 'Font' group.
6. Skip down two or three rows and then enter the dates you want included on your timeline, skipping a cell between each one. Skipping a cell will help space out the dates so you can type in the information for the event and add graphics.
7. Drag your cursor over the row of cells above the dates you entered to highlight them. Start at the first date and drag it over to the last date. Click the paint can under the 'Font' option and choose a color for your timeline.
8. Type short descriptions of the events that coincide with each date in the row below that date. You can change the size and font of the descriptions under 'Home' and then 'Font.'
9. Drag your cursor over the rows with the dates and descriptions to highlight them. Go to the 'Orientation' button under the alignment option to rotate the dates and descriptions. The button is a slanted arrow with the letters 'a' and 'b' over it. Choose one of the options shown or click 'Format Cell Alignment' to customize the angle. You can type in the angle you want or drag the line in the 'Orientation' box to the angle you want.
10. Go to 'Insert,' 'Illustrations' and 'Pictures' to add a graphic to your timeline. You can choose a file you have saved or go to 'Clip Art' instead of 'Pictures' and choose a Microsoft graphic. Once you have selected the file or clip art, click on it to insert it on your timeline worksheet.
11. Click on and manipulate the corner controls around the graphic to resize the image.
12. Click on the image and drag it to the location you want it on your timeline.
13. Save your timeline when you finish.

Subscribe to: Posts (Atom)