How to Count How Many Times a Word Appears in Excel

1. Open the Excel document containing the text that you want to analyze. Select the first cell that contains the text. Hold down the 'Shift' key and select the last cell that hold your text. This will create a selection box around all of your text. Press 'Ctrl' 'C' to copy this information to the clipboard.
2. Click on a new worksheet at the bottom of the spreadsheet. If all the worksheets are currently being used, press the new worksheet button, which is located directly to the right of the last worksheet.
3. Select cell 'B1' on the new worksheet. Press 'Ctrl' 'V' to paste the information to this sheet. Select the letter 'B' above the pasted information to select the entire column.
4. Click on the 'Data' tab and then select the 'Text to Columns' button. This will bring up a small wizard window. Select 'Delimited' on the first page of the wizard and click 'Next.' Click on 'Space' from the given options and click 'Finish.' Each word of the text will now have its own cell.
5. Select cell 'A1.' Enter in the following formula: =COUNTIF(B:Z, 'x') where 'Z' is the last column that contains text and 'x' is the word you are searching for. Press enter and the number of times that word appears in the text will be shown in cell 'A1.' Select cell 'A2' and enter in the following formula: =COUNTIF(B:Z, 'x?') to also count instances of the word that are followed by a piece of punctuation.
6. Select cell 'A3' and enter in the following formula: =sum(A1, A2) to get the final count of how many times the word appears in the text.

Read more ►

How to Create Checkbook Registers in Excel

1. Launch Excel 2007. Select the Insert tab.
2. On the Insert tab, click 'Table.' In the create table dialog box enter =$A$1:$G101 in the 'Where is your data?' field. Check the 'My table has headers' box then click 'OK.' Excel will create a table with seven columns and 101 rows.
3. Create labels for your checkbook register on row 1. Beginning with cell A1, replace each generic label with the following labels: Check No., Date, Transaction Description, Statement, Payments, Deposits, Balance. The balance label should be in cell G1.
4. Format the check register rows by first turning off the filters, which are automatically turned on when you create tables in Excel. To remove the filters, click the Data tab then click 'Filter.' Click the Design tab to begin color banding your rows and the balance column. In the table style options, check Last Column. Header Row and Banded Row should have a check. If they do not, place a check in the header and banded row boxes.
5. Format the check register columns by first clicking the Home tab then select column A and B. Click 'Center' in the alignment grouping. Select column C then click 'Format.' Select Column width and enter 35---or any any width you prefer. Select column E, F and G. In the number groupings, select Accounting Number format. With E, F, and G still selected, click 'Format' and increase the width of the selected columns.
6. Enter beginning balance and balance formula. Type 'Beginning Balance' in cell C2 and enter your beginning balance amount in cell G2. In cell G3 enter the following formula: =IF(AND(ISBLANK(E3),ISBLANK(F3)),'',G2-E3 F3). Copy this formula to cells G4 to G101.

Read more ►

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.

Read more ►

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.

Read more ►

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.

Read more ►

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.

Read more ►

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%.'

Read more ►

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,'

Read more ►

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.

Read more ►

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.

Read more ►

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.

Read more ►

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.

Read more ►

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.

Read more ►

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.

Read more ►

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.'

Read more ►