Thursday, February 17, 2011

How to Disable Addins in Excel 2007


1. Click the 'Microsoft Office' button in the top left corner of the Excel screen, select 'Excel Options' and choose 'Add-Ins.'
2. Scroll down the list of add-ins displayed in the new window until you locate the add-in you wish to disable. Make a note of the name in the Type row next to the add-in's name.
3. Select the type you noted in the previous step in the Manage box, then click 'Go.'
4. Uncheck the check box next to the name of the add-in in the new window that pops up and click 'OK.' This will disable the add-in. Repeat this process as necessary until you have disabled all the add-ins you wish removed from Excel.
Read more ►

How to Password Protect Excel 2007


1. Open your Excel document if it's not open already.
2. Click the 'Office' button in the upper left corner of the window.
3. Highlight 'Prepare.' A list of options will appear on the right.
4. Click 'Encrypt Document.'
5. Type any password of your choosing into the 'Password' text box. The password can be any length up to 255 characters. Longer passwords are more secure because they're harder for people (and computers) to guess, but a more complex password may be more difficult to type and remember.
6. Click 'OK.' Retype the password in the confirmation window that appears.
7. Click 'OK' and save your password-protected Excel file.
Read more ►

How to Convert MS Excel 2003 Files to PDF Format


1. Install a PDF writing program to your computer. While most PDF writing programs are suitable for converting Excel files to PDF, some are available for free and others have a fee associated with the download. Adobe Acrobat programs (see Resources section) are popular, but also expensive ($199 to $799). Free programs that work just as well as Acrobat for conversion purposes are also available. PDFill or CutePDF, both of which may be downloaded for free from their respective Web sites, are two options.
2. Open the Excel 2003 document that you want to convert to PDF. Click the 'File' menu and select 'Open.' This action will open the 'Open' pop up window. Use the 'Look in' menu to locate the folder in which the Excel file is saved. Highlight the file and click the 'Open' button.
3. Click the 'File' menu and select the 'Print' option. This action will open the 'Print' dialogue window. Use the 'Name' drop down menu to select the PDF writing program you chose in Step 1; for example 'CutePDF.' Click the 'Print' button. This action will open the 'Save As' dialogue window.
4. Click the 'Save in' menu to locate a folder in which to save the PDF file. Enter a name for the PDF file into the 'File name' field and click the 'Save' button to complete the process of converting an Excel 2003 file to PDF format.
Read more ►

How to Expand Collapse in Excel 2007


Hide Rows and Columns
1. Open the saved Excel document.
2. Click on the rows or columns you wish to hide from view.
3. Click on the 'Home' tab on the Ribbon.
4. Click on the 'Format' button in the 'Cells' group. A drop-down list will appear.
5. Point to the 'Hide Unhide' section to display a list.
6. Click on the 'Hide Rows' or 'Hide Columns' option. The selected rows or columns and the respective headers are temporarily removed. A short, bold line between the row or column headers indicates where the rows or columns are hidden. For example, a line between headers A and C indicates column B is hidden.
Unhide Rows and Columns
7. Open the saved document.
8. Click on the two headers adjacent to the hidden rows or columns. For example, if rows 10 to 15 are hidden, click on row headers nine and 16.
9. Click on the 'Home' tab.
10. Click the 'Format' button in the 'Cells' group. A drop-down list will appear.
11. Point to the 'Hide Unhide' section. A list of commands will appear.
12. Click either 'Unhide Rows' or 'Unhide Columns'. The hidden rows or columns will appear. The spreadsheet will expand.
Read more ►

Wednesday, February 16, 2011

How do I Recover a Microsoft Excel Spreadsheet That I Accidentally Saved Over?


1. Try pressing the 'Ctrl' and 'Z' keys together on your keyboard to undo the latest keyboard strokes. If you've just made the change, this keyboard combination may work for you.
2. Check the folder where you have saved the Excel spreadsheet to for a temporary file. There's a small chance that the save process got interrupted and the original file still exists in the folder. For example, if you saved your document in your 'My Documents' folder, check that folder for a duplicate file name with the file extension .tmp. If you find it, double-click on the icon to open the original file and save it to a new location.
3. Download software that recovers your data for you. There are a variety of options to choose from, including Active Undelete, WinUndelete and File Scavenger (see Resources for links). Although these all offer free trial versions, you may have to purchase the full version to successfully recover your data.
Read more ►

How to Look Up a Date in MS Excel 2003


1. Click an empty cell where you want the date to be displayed, for example if the first row is empty, click 'B2.'
2. Type '=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup).'
Type the location such as 'A1' instead of 'lookup_value.' This will be the cell where you type your search term. Type the coordinates of your table, such as 'A2:D6' instead of 'table_array.' Type the column number you wish to see such as '3' instead of 'col_index_num.'Type 'FALSE' instead of 'range_lookup' to find an exact result.
3. Type a search term into cell A1. The VLOOKUP function in cell B2 will then find that term in the table array specified, and then display the information from the cell specified as 'col_index_num.' If you had a table of names in column A, hair colors in column B, and dates of birth in column C, then this VLOOKUP function would display for you the date of birth in cell B2 (as it is the third column of the specified table array).
Read more ►

How to Group Worksheets in Excel


1. Start Microsoft Excel 2007, and open a workbook that contains multiple worksheets that you would like to group together.
2. Locate the sheet tabs for the worksheets. They are on the bottom, left side of the Excel screen. You may have named them or they may be named as their default names: 'Sheet1,' 'Sheet2' and so on.
3. Select the first tab of the sheet that you would like to include in the group of worksheets.
4. Press down the CTRL key on your keyboard and use your mouse to click on the next sheet that you would like to be include in the group. Continue to do this until all the sheet tabs are highlighted that you want to include in the group. The selected sheet tabs will appear white.
5. Release the CTRL key when you are finished selecting the worksheets that are to be included in the group. The worksheets are automatically grouped when you select them. Notice that '(group)' is beside the name of the file that appears in the workbook title bar.
Read more ►

How to Use Excel's Solver


1. Make sure that the Microsoft Excel solver add-in is available. If not select 'Options' from the main Menu and choose 'Solver Add-in' from the options menu. Restart Microsoft Excel and the Solver option should be available under the data menu.
2. Construct a model that contains and specifies variables, constraints and an objective.
3. Determine the variables on the solver construct model. These numbers can change given a specified constraint or constraints. For example, the variable cells or changing cells could be the amount of money spent by each department of a company. These cells do not need to be defined, just labeled and designated as changing cells.
4. Establish the constraints for the Excel solver model. These are the specific constraints placed on cells within the Solver model. These constraints can apply to the variable cells or the objective cells. For instance, in budget model it would be the overall budget limit or specific limits that are placed on each department.
5. Ascertain the objective of the solver model. The objective is what the model is intended to 'Solve.' Again using the budget example, it would be the overall budget limit. Essentially, it is the answer to the original question posed when using Solver.
6. Input the information into the Excel Solver model. Select 'Data' from the main menu and then choose the 'Solver' add-in. The Solver dialogue box should appear. First, input the information into the target cell or objective cell. Then set limits of that cell; in the budget case it would be the maximum amount of money to be spent. Next, select the cell ranges that are the changing cells. Finally, enter any constraints that will limit the changing cells.
7. Solve the model with Microsoft Excel Solver. After all the data is correctly entered click the 'Solve' button on the Solver dialogue box.
Read more ►

How to Scan Documents Into Excel


1. Click on the 'Start' button on the taskbar and select 'All Programs' from the next menu. Select 'Microsoft Office' and 'Office Tools' from the subsequent page.
2. Click the 'Microsoft Office Document Imaging' option from the screen. Click 'File' at the top of the page and click the 'Open' tab. Select the file that you want to scan into Excel and click 'Open' once more. Select the icon with an eyeball on it from the lower-right corner of the screen to run the document through the OCR (Optical Character Recognition) process. This process is required prior to putting the document into an Excel file.
3. Highlight the entire document with the mouse once the OCR process is complete. Click 'Edit' from the top menu and select 'Copy' from the subsequent menu.
4. Open Microsoft Excel and open the workbook that you want the data added into. Click 'Edit' from the top menu and select 'Paste' from the available options. The document will now appear in the Excel workbook.
Read more ►

Tuesday, February 15, 2011

How to Make One Checkbox Uncheck Another in Excel 2003


1. Open Excel 2003, and launch your Excel spreadsheet. Make sure the Forms toolbar is switched on by clicking 'Views > Toolbars > Forms.'
2. Click the 'Combo Box' form control, and drag it to your worksheet.
3. Right-click the box, and click 'Format Control.' Set the 'Input range' to the range of cells that contains the check box values.
4. Set the 'Cell link' to the cell that will return the result of your check box. Click 'OK' to insert the combo check box.
Read more ►

How to Change the Password on a Shared Excel Document


1. Run Excel and open the shared document.
2. Click the 'Review' tab and click 'Unprotect Shared Workbook' under the 'Changes' group.
3. Type the workbook's password and click 'OK' to remove password protection.
4. Click the 'Share Workbook' link located on the 'Changes' tab.
5. Uncheck 'Allow changes by more than one user' on the 'Editing' tab. Click 'Yes' to unshare your Excel document.
6. Click 'File' in the menu bar, then click 'Info' and 'Encrypt with Password.'
7. Type a new password in the 'Password' box and click 'OK' to save your new password.
8. Click the 'Review' tab and click 'Share Workbook' in the 'Changes' group.
9. Click the 'Editing' tab and check 'Allow changes by more than one user' to re-enable the Excel document sharing feature.
Read more ►

How to Write Formulas For Excel 2003


1. Launch Excel 2003 and open the worksheet that you would like to add a formula to. Click to highlight (put a black box around) the cell that you would like to display the result of your formula in when it is complete, and press the equal sign (=) key. You will notice that the equal sign appears both in the cell that you have clicked and in the formula ('fx') bar at the top of the screen.
2. Click another cell to reference it in your formula, type a number to enter a constant value, and type math signs on the keyboard to manipulate those values. For example, if you wanted the cell that you clicked to begin the formula to display what the total would be if you subtracted 20 from the value displayed in cell A2, you would first click cell A2. The cell will now have a moving dotted line going around it. After clicking the cell, type the minus sign (-) then type the value that you want to subtract (20).
3. Press the 'Enter' key when your formula is complete. The cell that you clicked to begin the formula will automatically change to display the result of the formula that you typed. If you referenced another cell when typing the formula (such as in the example), the result of your formula will change automatically if the value in the referenced cell ever changes.
4. Click the original cell again if you need to make changes to your formula. Note that the formula bar at the top of the screen shows the full formula that you entered; after clicking the cell, click in the formula bar, back over any mistakes that you have made using the 'Backspace' key, and enter the desired values. Press 'Enter' to recalculate the formula when you are done. Alternatively, you can click the cell that you want to change, press the 'Delete' key, and start the formula over.
Read more ►

How to Use Subtotals in Excel


How to Use Subtotals in Excel
1. Open the Excel workbook in which you want to subtotal data.
2. Select data that you want subtotaled by left-clicking with your mouse in the upper left-hand corner of the data and then using your mouse to select to the right and down to highlight all of the data.
3. Select 'Data' from the main menu. Select 'Sort' from the drop-down menu.Data must be sorted to perform the subtotals function. Sort based on what you want the subtotals categories, not what you want subtotaled. For example, if you want to subtotal purchases by state, sort by state, not the purchase amounts.
4. In the 'Sort' pop-up window select the appropriate column heading in the 'Sort by' field. Select 'Ascending' or 'Descending' radio buttons, depending on whether you want the data sorted ascending or descending. Select 'Ok'.
5. Left-click with your mouse in the upper left hand corner of the data that you want subtotaled and then, using your mouse, highlight all of the data by moving your mouse to the right and down.
6. In the 'Subtotal' pop-up window, 'At each change in:' field select the category by which you want data subtotaled.In the example given above, this would be: 'State'.
7. In the 'Subtotal' pop-up window, select the function that you want performed on the data. The options are: Sum, Count, Average, Max, Min, Product, Count Nums (numbers), StdDev (standard deviation), StdDevp (standard deviation population), Var (variance), Varp (variance population).In the example given above, this would be: 'Sum'.
8. In the 'Subtotal' pop-up window, select the data that you want subtotaled (or the other functions available within this tool). The options will be all of the column headers in the data that was selected in step 5.In the example give above, this would be: 'Purchases'.
9. In the 'Subtotal' pop-up window, indicate if you want the current subtotals replaced (useful to keep data clean if you are re-subtotaling information), page break between groups (useful for disseminating specific data to specific individuals or departments), summary below data (useful to keep an overall number, whether it is sum, average, count, etc...).Select 'Ok.'
10. Notice that additional rows have been inserted into the spreadsheet for each change in category.
To the right of the data you will notice a small '1', '2' and '3'. The view automatically defaults to '3,' which lists all of the data that was subtotaled and the inserted rows mentioned above. View '2' lists all of the subtotals. View '1' lists only the grand total.
Read more ►

How to Put Images in Cells on Excel 2007


1. Click the 'Insert' tab on the Office Ribbon.
2. Select a cell on your spreadsheet by clicking it with the cursor. The picture you insert will appear at this location.
3. Click the 'Picture' button on the 'Illustrations' panel of the Office Ribbon to insert a picture into your spreadsheet. Use the 'Open Picture' dialog to select a picture from your computer and click 'Open' to insert the picture onto your spreadsheet.
4. Resize the cells around your image. Click and drag the row and column headers so that your image is contained within the border of a single cell. If your image is large, click on the image and use the 'Resize' buttons along its border to scale it to a smaller size.
5. Right-click on your image and select 'Size and Properties.' Click the 'Properties' tab in the dialog window and choose 'Move and size with cells' from the 'Object Positioning' options. Click 'Close' to apply the changes.
6. Resize the cell containing the picture. The image will scale with the cell as you adjust the row height and column length.
Read more ►

How to Show Duplicate Records in Excel


Formatting
1. Click your first data (not heading) cell and select 'Conditional Formatting' from the Format menu. These cells are identifiable by location. A cell in the first column and first row is 'A1.' Frequently. Excel users may reserve the top row ('1') to create headings for their data. If your 'A1' cell includes the heading 'Year' and your 'A2' cell includes the data '2010,' you will want to click 'A2.'
2. Select 'Formula Is' from the drop-down menu on the 'Conditional Formatting' box and type '=COUNTIF(A:A,A2)>1' into the field that appears.
3. Click the 'Format' button in the Conditional Formatting box.
4. Click the 'Patterns' tab and choose a color, such as pink, to highlight duplicate entries by clicking on any of the available the color swatches.
5. Click 'Ok' on the 'Format' and then the 'Conditionally Formatting boxes' to close both. Your cell will become highlighted because it contains the data you are highlighting.
Application
6. Click with the left mouse button to open the Edit menu and select 'Copy' with your cursor still in the same cell.
7. Highlight the entire column by pressing 'CTRL' 'Spacebar.'
8. Open the Edit menu. Select 'Paste Special.'
9. Select the radio button (circle) next to 'Formats' on the 'Paste Special' box and click 'Ok.' Duplicate cells will become highlighted.
Read more ►

Blogger news