How to Specify That the Cell Address Will Not Change in Excel

1. Select the cell that contains the formula you want to change. Place the cursor in the formula bar, located directly above the spreadsheet, in order to alter the formula.
2. Insert a dollar sign, '$,' in front of the reference's column or row in order to make it an absolute reference. For example, you can enter '$A1,' 'A$1' or '$A$1.' Only the part of the reference with the dollar sign in front of it becomes absolute. '$A1' will always reference column A, but the row will change if the formula moves.
3. Press 'Enter' or select another cell on the worksheet to complete the changes to the formula.

Read more ►

How to: Watermarks in Excel 2007

1. Click the 'Page Layout' tab, then click the small down arrow at the bottom right of the 'Page setup' panel. Excel will display a dialog box with options for setting the header and footer of your workbook. Inserting a picture in a header creates a watermark for the workbook.
2. Click the 'Header/Footer' tab, then click the 'Custom header' button. Excel will display a dialog box allowing you to specify different headers for the left, right and middle sections of your workbook.
3. Click in the 'Center' text box, then click the icon above the text box that appears as a mountain with a rising sun over it. This button is the 'Insert picture' tool.
4. Click the button just mentioned, then use the controls in the 'Insert' dialog box that appears to move to a folder on your hard drive containing a picture file you would like to use as a watermark.
5. Click the picture file to select it, then click the dialog box's 'Insert' button to load the picture into the header for your workbook. Excel will display the text '(Picture)' in the 'Center' text box, which indicates it successfully loaded the picture.
6. Click 'OK' to exit the dialog boxes and complete the insertion of the picture into your workbook. Excel will not display the picture while in draft view, which Excel documentation refers to as 'Normal' view.
7. Click the 'View' tab, then click the 'Page layout' button of the 'Workbook views' panel. Excel will display the picture you inserted in the header's center section, but only on the first page of the preview.
8. Click a cell on any of the pages where Excel displays the text 'Click to add data.' Excel will insert your watermark graphic onto the page you clicked.

Read more ►

How to Remove Duplicate Values From a List in Excel

Excel 2007 and 2010
1. Open your Excel document. Click on the column title above the column from which you want to remove duplicate values. If you do not have column titles, click on any of the data cells in the list. You can highlight multiple columns.
2. Click on 'Data' in the menu bar, then click on the 'Remove Duplicates' button in the Data Tools section. This will open the 'Remove Duplicates' dialogue box. If you highlighted more than one column, you can use the check boxes to specify which columns or lists you want Excel to remove duplicate values from.
3. Click 'OK' to remove all duplicate values from your list. If you have only selected one data cell, Excel will remove duplicate values from the entire list. If you selected more than one column, Excel will only remove values from the columns you specified.
4. Click 'OK' to close the message box that Excel created. If the list had any duplicate values, this message box tells you how many duplicate values it removed and how many unique values remain.
Excel 2003 and Earlier
5. Open your Excel document. Click on the column title above the column where you want to remove duplicate values. Select multiple column titles to remove duplicate values from more than one list.
6. Click on 'Data' in the menu bar, click on 'Filter,' then click on 'Advanced Filter.' This opens the Advanced Filter dialogue box.
7. Click on 'Filter the list, in place' and select the 'Unique records only' check box. Click 'OK.' This will hide the rows containing any duplicate values from previous entries.
8. Highlight the list without any duplicates showing. Click on 'Edit' and choose 'Copy,' or press 'CTRL C,' to copy the list.
9. Click on 'Data' in the menu bar, click on 'Filter' and then click on 'Show All.' This will display the entire list, including the duplicate values. Press the 'Delete' key to delete the list.
10. Click on the first blank cell underneath the column title. Click on 'Edit' and click 'Paste,' or press 'CTRL V,' to paste the list back in without any hidden duplicate values.

Read more ►

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 ►