How to Remove a Formula or Program in an Excel Spreadsheet

Removing Custom Functions Macros
1. In the Excel file containing the custom features you want to remove, click on the 'Developer' tab and select 'Visual Basic' in the ribbon. This opens a new window.
2. Look at the window on the left titled 'Project - VBAProject.' The VBAProject may have a different name; however, the initial 'Project -' should still be part of the title. In this box you see 'Microsoft Excel Objects' and 'Modules.'
3. Double-click the first listed item under 'Modules.' This brings up a new window containing VBA code. You can remove individual custom functions by deleting everything from the 'Function' before the function's name to the next 'End Function,' inclusively. To remove a macro, select everything from 'Sub' to 'End Sub.'
4. Right-click on the module name, and select 'Remove (the module's name)' to remove all the functions or macros within the module.
5. Click on the items under 'Microsoft Excel Objects.' This is where code specific to sheets or workbooks is placed. You can remove individual functions and macros in the same manner as those in the 'Modules.' You cannot, however, remove all of them by removing the module. To remove all of the functions or macros, simply select all of the code and press 'Delete.'
6. Select 'File' then 'Save and Return to Microsoft Excel' to save the changes.
Removing Formulas
7. Find the cell containing the formula you wish to remove.
8. Left-click on the cell.
9. Press 'Delete' to remove the contents of the cell, formula included. To remove just part of a formula, select the portion in the 'formula bar' and press 'Delete.'

Read more ►

How to Remove Color Fills from Cells in Microsoft Excel 2003

1. Activate the cell with the color fill. To activate a cell that you wish to change the color fill in, simply left-click on that cell.
2. Access the color fill palette. The color fill palette is located on the command bar and appears as an icon of a paint bucket spilling out paint. To access this palette, simply left-click on the icon and the palette will open.
3. Remove the color fill. To remove the color fill, left-click on “No Fill” inside of the color fill palette.
4. Make sure to save your changes. You can easily save your spreadsheet by pressing the hotkeys “CTRL-S.”

Read more ►

How to Take Out Hyphens From a Cell in Excel 2007

1. Select a blank cell next to the desired cell containing text (source cell).
2. Type '=substitute(' and then click the cell containing text (source cell).
3. Type a comma followed by a space and a hyphen between quotation marks. Type a comma and then press the 'Space' key. For example, the formula in the cell will now read '=substitute(A3, '-', '
4. Type the desired characters to replace the hyphen, enclosed in quotation marks, or simply type two quotation marks to remove the hyphens. Type ')' to complete the formula. For example, to remove hyphens and replace them with a period, the formula will read '=substitute(A3, '-', '.')' or to remove hyphens the formula will read '=substitute(A3, '-', '')' in the cell.
5. Press the 'Enter' key to complete the SUBSTITUTE formula.

Read more ►

How to Use Excel's Indirect Function

1. Open your worksheet and find the cells that you want to add value to with Indirect functions.
2. Access the 'function text box' for a specific cell. In some Excel programs, the text box is visible at the top of the page. If not, you'll have to go to the menu, select 'Insert' and then 'Function.'
3. Utilize the function INDIRECT the same way as other functions for a direct cell reference. If you want the content of cell B6 to echo the content of cell C5, your command for B6 in the function text box will look like this: INDIRECT(C5)
4. Use INDIRECT nested in brackets for in-depth cell referencing. For example, to use INDIRECT to make versatile values within a SUM function, you would use SUM on the 'outside' and INDIRECT nested in parentheses, for a command like this: SUM (INDIRECT (A1, A2)).
5. Pass parameters to an INDIRECT statement for 'on the fly' cell changes. This step gets a little complicated. The thing to remember is that when you are passing values into an INDIRECT statement, you use ampersands to turn strings into references. Here's how it works: if you want B1 and B2 to contain the cell values for one of the A row cells, you put those into the B1 and B2 cells. Then, you create a statement like this: INDIRECT ('A'B1':A'B2). If this seems totally unclear, look at it carefully. In this case, INDIRECT is working like a computer programming function. The Excel function will 'see' the combination of text in quotations and cell references and interpret it to pass the cell you're building the values of the A row cells you selected and put into B1 and B2.

Read more ►

How to Create a Frequency Histogram in Excel

1. Type the title of your data in cell A1 and enter your data below it. For example, if you were creating a frequency histogram of grades on a test, you would type 'Grades' in cell A1 and then type the numerical grades in the cells below.
2. Enter the bin values that you want to use in a separate column. The bins are the values that will be grouped together. For example, if your grading scale was: 90 and up is an A; 80 to 89 is a B; 70 to 79 is a C; 60 to 69 is a D, and 59 and below is an F, you would enter 59, 69, 79, and 89.
3. Select the 'Data Analysis' option from the Data tab then select 'Histogram.' Enter the cells that you typed your data in, for example A2:A19, into the 'Input Range' and then enter the cells that you typed your bins into the 'Bin Range.'
4. Chose whether to have the histogram appear as an item in the existing spreadsheet or on its own in a new sheet then click 'OK.' The histogram will show up where you have specified.

Read more ►

How to Eliminate Duplicate Records in Excel

1. Open the Excel spreadsheet.
2. In the 'Data' tab, choose the 'Advanced' button in the group marked 'Sort Filter.' (In Excel 2003, click 'Data,' then 'Filter,' then 'Advanced Filter.')
3. Confirm that your data range--all the rows you want to filter--is noted in the dialog box that appears. If it isn't, simply click in the first cell of the range (the dialog box will shrink to the cell selection field temporarily) and drag the mouse cursor down to select. When you release the button, the dialog box will restore.
4. Eliminate duplicate entries by clicking on the 'Data' tab, then clicking the 'Advanced' button in the 'Filter' button set. Excel should automatically select your entire data set, but if it doesn't you can select it yourself by clicking and selecting the range.
5. Leave the radio button next to 'Filter the list, in place' filled, and place a check mark next to 'Unique records only.' Click 'OK.' Excel will delete any duplicate entries seamlessly, without leaving blank rows.

Read more ►

How to Create an Auto Loan Calculator

How to Start the Auto Loan Calculator
1. Click on cell A1 and type “Purchase Price.”
2. Click on cell A2 and type “Down Payment.”
3. Click on cell A3 and type “Amount of Loan.”
4. Click on cell A4 and type “Interest Rate.”
5. Click on cell A5 and type “Number of Monthly Payments.”
6. Click on cell A6 and type “Monthly Payment.”
7. Click on cell B3 (currency amount of loan) and, with the cell highlighted, left-click in the formula bar area and enter: =B1-B2.
8. Click on cell B6 (monthly payments of loan) and, with the cell highlighted, left-click in the formula bar area and enter: =PMT(B4/12,B5,B3).
9. Left-click on the A column to highlight it, then right-click and choose “column width,” enter the number “25” and click return. This will increase the width of the column so all the characters can be seen.
10. Left-click on the B column to highlight it, then right-click and choose “format cells,” click on the “Number” tab, locate the category “Currency” and click on it. Verify that the decimal places are set to “2” and the symbol is set to “$.” Click “OK” to return to the worksheet.
11. Left-click on the B column to highlight it, then right-click and choose “format cells,” click on the “Number” tab, locate the category “Currency” and click on it. Verify that the decimal places are set to “2” and the symbol is set to “$.” Click “OK” to return to the worksheet.
12. Left-click on the B5 cell to highlight it, then right-click and choose “format cells,” click on the “Number” tab, locate the category “Number” and click on it. Verify that the decimal places are set to “0.” Click “OK” to return to the worksheet.
13. Left-click and drag on cells A1 through A6 to select those cells, then right-click and choose “Format Cells,” click on the “Patterns” tab, locate the color box for yellow and click on it. Click “OK” to return to the worksheet.
14. Left-click and drag on cells B1 through B6 to select those cells, then right-click and choose “Format Cells,” click on the “Patterns” tab, locate the color box for green and click on it. Click “OK” to return to the worksheet.
15. Left-click and drag on cells A1 through B6 to select those cells, then right-click and choose “Format Cells,” click on the “Border” tab, locate the preset with the “Inside” and click on it. Locate the preset with the “Outline” and click on it. Click “OK” to return to the worksheet.
16. Save the worksheet for later use.
How to Use the Auto Loan Calculator
17. Open the worksheet.
18. Click on cell B1 and enter the amount of the purchase price of the car and click return.
19. Click on cell B2 and enter the down payment amount of the car and click return.
20. Click on cell B4 and enter the yearly interest rate of the car loan and click return.
21. Click on cell B5 and enter the number of monthly payments that will be made on the car and click return. The monthly payment will be calculated in cell B6.

Read more ►

How to Restore a Deleted Worksheet in Excel

1. Click 'File > Save As...' to save your workbook with a different file name.
2. Click 'File > Open' and select your original workbook, that still contains the deleted worksheet.
3. Right-click the sheet you want to get back, and choose 'Move or Copy...'
4. Select your newer workbook with the missing sheet from the drop-down list labeled 'To book.'
5. Press 'OK.' Your newer workbook now has the recovered worksheet in it.

Read more ►

How to Convert Quattro Pro to Text Delimited

1. Click 'Start' on the desktop and click 'Settings,' 'Control Panel' and 'Add/Remove Programs.'
2. Click 'Microsoft Excel 2003' and click 'Add/Remove.' Click 'Add or Remove Features' and click 'Quattro Pro 5.0 Converter.'
3. Click 'Run From My Computer' and click 'Update Now.' Launch Microsoft Excel, and click the File menu at the top of the Excel window.
4. Click 'Open' and locate the Quattro Pro file with the WB1 file extension. Double-click the file to open it in Excel.
5. Click the File menu and click 'Save As.' Click the 'Save as Type' drop-down menu, and click '*.txt.'
6. Click 'Save' to convert the WB1 file to the TXT format.

Read more ►

How to Multiply Cells in Excel

Multiply with Cell References
1. Click on cell A1. Type the number 12 into cell A1. Type the number 6 into cell B1.
2. Click on cell C1. Type the equal sign (=) at the beginning of your equation.
3. Click on cell A1. This action places A1 in cell C1 on the right side of the equal sign.
4. Type an asterisk (*) sign into cell C1.
5. Click on cell B1. This action places B1 into cell C1, after the asterisk.
6. Press the 'Enter' button on the keyboard or click on the check mark on the tool bar to display a result. The number 72 should appear in cell C1.
Multiply Numbers in a Cell
7. Click on a blank cell. Type in the first number you want to multiply, for instance, 6.
8. Type in an asterisk (*).
9. Type the second number, for instance, 3.
10. Press the 'Enter' key or click on the check mark button on the toolbar. The result should appear in the cell.
Multiply a Group of Numbers by One Number
11. Enter the numbers you would like to multiply in a group of cells. For instance, enter the numbers 5, 4 and 8 into cells B1, B2 and B3.
12. Type the number 7 into cell A1.
13. Select cell A1. Open the 'Edit' menu and click on 'Copy.'
14. Select cells B1, B2 and B3.
15. Open the 'Edit' menu and select 'Paste Special.' In the 'Operations' section of the 'Paste Special' box, click on 'Multiply.'
16. Click 'OK' to perform the calculation. The numbers in cells B1, B2 and B3 should now be 35, 28 and 56, respectively.
17. Delete the number in A1.
Multiply Numbers in Different Cells with the PRODUCT Function
18. Enter the numbers you want to multiply. Type 4, 8 and 10 into cells A1, A2 and A3 respectively. Type the number 2 into cell B1.
19. Click on cell A4. Type =PRODUCT(A1:A3).
20. Press the 'Enter' key to perform the calculation. The PRODUCT function will multiply all the numbers in the range. Cell A4 should display 320.
21. Use the PRODUCT function to multiply a group of numbers in a range by a number. To multiply the numbers in cells A1, A2 and A3 by 2, click on cell A4. Type =PRODUCT(A1:A3,2).
22. Press the 'Enter' key to display the result. Cell A4 should show 640.

Read more ►

How to Protect a Worksheet in Excel

1. Open the Excel workbook in which you want to convert and replace the date data with day-of-week data. If this workbook is not available, open up a new file for practice. If using a practice workbook, enter some sample data.
2. Select 'Tools' from the main menu.
3. Select 'Protection' from the drop-down list.
4. Select 'Protect Sheet' from the drop-down menu.
5. Left-click with your mouse in the box labeled 'Protect worksheet and contents of locked cells.'
6. Enter a password to unprotect the sheet in the 'Password to unprotect sheet' field. If you do not enter a password, any user will be able to unprotect the sheet and make changes to the data or formulas.

Read more ►

How to Do a Budget Spreadsheet With Excel 2003

1. Press the “Windows” and “R” keys on your keyboard simultaneously, type in “Microsoft Excel,” and then click “Run” to open Excel 2003.
2. Click on the first square at the top left of the spreadsheet (A1) and then type in “Expense.”
3. Press the “Tab” key on your keyboard, this will put you on the square directly to the right of A1 (B1) and then type in “Sub Total.”
4. Press “Tab” to move to C1 and then type in “Actual Money Spent.”
5. Click on A2 and then type in “Savings.”
6. Press the “Enter” key on your computer keyboard, this will take you to the square directly below A2 (A3) and then type in “Savings Account.” Repeat this step for every type of savings or savings account you have including IRA, CD and Money Market accounts.
7. Type in every type of expenditure you have in all consequent squares within the A column until you have no more expenditures. Examples include groceries, gasoline, clothing, medical insurance, taxes, car repairs, credit card bills and utilities.
8. On the squares next to the expenditures and below “Sub Totals,” all the squares in the B column, type in the amount of money you estimate you will spend on those items for the month. For example, if you think you’ll spend $100 a week on groceries, type in “400” next to groceries.
9. Type in the actual amount of money that you spent on a certain item at the end of the month within the squares under the C column. For example, you actually only spent $370 on groceries so you would type “370” next to the 400 you estimated for groceries.

Read more ►

How to Change a Chart Axis in MS Word

1. Open the Word document that contains the chart you want to modify.
2. Click the chart axis you want to change. The axis will be outlined in black; right click over the axis to view options for editing the chart.
3. Select 'Format Axis' from the menu that pops up. The 'Format Axis' dialog window will open.
4. Select 'Axis Options' from the list of formatting options.
5. Select the axis scale minimum or maximum by checking 'Fixed' next to 'Minimum' or 'Maximum.' Type the minimum or maximum number you want to use for the scale.
6. Change the spacing in the tick marks on the axis by checking 'Fixed' next to 'Major unit. Enter the spacing you want for the axis tick marks. For example, if you want the tick marks to be spaced closer, enter a smaller number, such as '1.' If you want the tick marks to be spaced further apart, enter a large number, such as '5.' This is useful depending on the range of numbers on your axis.
7. Select how you want units on the axis displayed by clicking in the box next to 'Display Units' and selecting a measurement, such as hundreds or millions.
8. Click 'Close' after you finish making changes to your chart axis. The changes you selected will show up on your chart.

Read more ►

How to Find Links in Microsoft Excel

Links in Hyperlink
1. Open the Excel workbook containing the links you want to find.
2. Place your cursor in an empty cell and create a dummy hyperlink (so you can use its format to find other hyperlinks). To create the hyperlink, select the 'Insert' tab and click 'Hyperlink' in the 'Links' group. Select 'Existing File or Web Page' under 'Link to' in the 'Insert Hyperlink' dialog box. Type a website address (ex., 'http://some-address-123.com') into the 'Address' text field and click 'OK.'
3. Select the 'Home' tab and go to the 'Editing' group. Click 'Find Select' and 'Find.' The 'Find and Replace' dialog box appears. Click the 'Options' button to expand the dialog box.
4. Leave the 'Find what' text field blank. Click the arrow beside the 'Format' button and select 'Choose Format From Cell.' The dialog box minimizes and you are directed to your spreadsheet.
5. Click on the cell containing the dummy hyperlink you created in Step 2. The 'Find and Replace' dialog box reopens.
6. Select 'Workbook' from 'Within.' Select 'Values' under 'Look in.' Choose 'Find All.' All matching entries are displayed at the bottom of the dialog box.
7. Delete the dummy hyperlink from your spreadsheet.
Links in Cells
8. Repeat Step 3 of the previous section.
9. Type an open bracket '[' in the 'Find what' text field, representing the format of links in the formula bar.
10. Click 'Workbook' from 'Within' and 'Formulas' from 'Look In.' Press the 'Find All' button to display the matching links.
Links in Objects, Chart Titles or Data Series
11. Go to the text box, chart title, object or data series containing the link you want to find. To find it quickly, select the 'Home' tab and go to the 'Editing' group. Click 'Find' and 'Go To Special.'
12. Check 'Objects' in the 'Go To Special' dialog box and press 'OK.'
13. Press the 'Tab' key and check the formula bar for references to links.
Links in Names
14. Click the 'Formulas' tab in the ribbon and go to the 'Defined Names' group.
15. Click the 'Name Manager' button to display the 'Name Manager' dialog box.
16. Check the 'Refers To' tab for the link you want to find. Its cell address is displayed in the dialog box.

Read more ►

How to Make the Top Line Stay Visible in Excel

1. Open your spreadsheet in Microsoft Excel.
2. Click the 'View' tab in the ribbon.
3. Click 'Freeze Panes' in the 'Windows' group and click 'Freeze Top Row' to make the top line stay visible.

Read more ►