How to Secure Excel Data

1. Turn on your computer, open Excel, and find the spreadsheet that you need to secure.
2. Click on 'Tools' in the toolbar and then select 'Protection'.
3. Now you have several options on how to protect the spreadsheet. If you have a single sheet, then choose 'Protect Sheet'. If you have several tabs on the spreadsheet then select 'Protect Workbook'. Finally, if you want the recipients to be able to alter data, choose 'Allow users to edit ranges'. In this example you should choose 'Protect Sheet'.
4. When the Protect Sheet windows opened, type in a password in the password box, then check the appropriate boxes for the options that you need for the spreadsheet. Normally if you are protecting a sheet from being altered you should only check 'Select locked cells' and 'Select unlocked cells'. Click 'OK' to continue.
5. Save the spreadsheet, and if you want extra protection simply choose 'Save as', then click 'Tools' and then 'General Options'. This will open a Save Options window where you can add an additional password and make the document 'Read-Only' as well.
6. Once you have entered the password and selected the 'Read-Only' box, click 'OK' and then 'Save'.

Read more ►

How to Use Excel's Covar Function

1. Learn the syntax for Covar. It is Covar (array_1,array_2) where array_1 is the first range of integers and array_2 is the second range of integers. The covariance of these two arrays will be returned.
2. Study the restrictions on the arguments. They must be arrays, references, names or numbers. If the arguments contain empty cells, logical values or text, those values will be ignored. However, the value zero is included.
3. Compare the number of values between array_1 and array_2. If they are not equal, Covar will return the #N/A error value. If either array is empty, Covar will return the #DIV/0! error value.
4. Calculate the covariance. Covar(X,Y) = The sum of (x - Average (array_1))(y - Average (array_2))/n where x and y are individual values in array_1 and array_2 respectively and n is the sample size.
5. Look at the following example: Data_1 = (3, 2, 4, 5, 6) and Data_2 = (9, 7, 12, 15, 17). Average (array_1) = 4 and Average(array_2) = 10. The sum of (x-4)(y-10) for all values of data_1 and data_2 is (3-4)(9-10) (2-4)(7-10) (4-4)(1-10) (5-4)(15-10) (6-4)(17-10) = 1 6 0 5 14 = 26. 26/5 = 5.2 so Covar (Data_1,Data_2) = 5.2.

Read more ►

How to Generate Random Numbers in Excel 2003

1. Open an Excel spreadsheet.
2. Click on a cell where you want your range of random numbers to start.
3. Enter the following into the formula box:=RAND()and hit 'Enter.' This is the default RAND function and will generate the first random number between 0 and 1 to 9 decimal places. You can expand the cell to see all 9 decimal places. To generate a whole random number between numbers other than 0 and 1, enter the following into the formula box: =INT(RAND()*(b-a)) awhere 'a' is the minimum random number you want and 'b' is the maximum random number you want. For example, to generate random numbers between 1 and 100, you would enter: =INT(RAND()*(100-1)) 1.
4. Click on that cell, then move your mouse to the bottom right corner until a solid, black cross appears.
5. Drag this black cross down or across the number of cells that you want. For example, if you want to generate 20 random numbers, drag the cursor down 19 additional cells. All the cells, including the first one should now have a new random number.

Read more ►

How to Change the Default to Excel 2003 Instead of Excel 2007

1. Click on the Office button at the upper left-hand corner of the screen. A menu will come up.
2. Click on 'Excel Options' at the bottom of the menu that comes up. A two-pane dialog window appears.
3. Click 'Save' in the left-hand side of the pane. On the right-hand side of the pane, the panel will change to show save options.
4. Select 'Excel 97-2003 Workbook (*.xls)' from the menu on the first option on the panel.
5. Click 'OK' at the bottom of the panel.

Read more ►

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 ►