How to Find and Replace Text in Microsoft Excel 2003

1. Access the 'Find and Replace' menu. Scroll to the “Edit” tab on the command bar and select “Find.” A “Find and Replace” properties menu will open.
2. Type in the text you want to find. Under the “Find” tab, you can type in the text that you wish to find.
3. Type in the text that you wish to replace. Under the “Replace” tab, you can type in the text that you wish to replace.
4. Find and replace desired text. You can do this in two different ways. One way is by pressing the “Find All” button, which will find all occurences of the text you have typed, and another method is by using the “Find Next” button, which will find each separate occurrence of the text. Choose the desired option and, when you find text to replace, click on the “Replace” button to replace it. You can also click on “Replace All” to replace all occurrences of the text inside of the spreadsheet.

Read more ►

How to Override an Excel Password

1. Make a new spreadsheet. Leave it entirely blank. If it is a protected workbook, instead of just one sheet, make a new workbook.
2. Click on the top left corner of the protected spreadsheet's header bars (above the 1, left of the A) to highlight the whole sheet.
3. Copy the spreadsheet, either by pushing 'Ctrl' 'C' or by selecting 'Edit' from the menu bar and clicking on 'Copy.'
4. Click on your new blank spreadsheet. Click the top left corner to highlight the whole sheet, as in Step 2.
5. Paste the copied spreadsheet by pushing 'Ctrl' 'V' or by selecting 'Edit' from the menu bar and clicking on 'Paste.' You now have an identical copy of the protected spreadsheet---only it's not protected.
6. Rename the new spreadsheet to match the name of the old one (you may have to first rename or delete the old one to do this, if they are in the same workbook).
7. Manually copy all of the page setup settings, if you need to print this spreadsheet just like the old one. With the old, protected spreadsheet pulled up, select 'File' from the menu bar and click on 'Page Setup.' Go through the tabs and write down all the settings. Return to the new spreadsheet. Open the page setup box and enter the settings.
8. Repeat Steps 1 through 7 for each spreadsheet, if it is a whole workbook being copied. Rename the new workbook to match the old one when you are finished.

Read more ►

How to Make Pie Graphs in Excel

1.
Enter the data that you want your pie graph to represent into a spreadsheet in Microsoft Excel. Make sure to include column headings that explain what the data signifies. Try to be as descriptive with your data entry as possible because these elements will automatically feed into the pie graph. To help explain, we will construct an example in which we create a pie graph that breaks down a favorite color list by percentage.
2.
Click on the cell that contains your first column heading. Then, from the “Insert” menu, click on the arrow underneath the word “Pie” and the picture of a sample pie graph. Another menu will open.
3.
Choose the type of pie graph that you want to create with your data. For this example, we will choose the simplest of the two-dimensional options. Once you have made this selection, the pie graph will appear in your spreadsheet.
4.
Right-click on the pie graph and another menu will appear. Use the options in this menu to make any modifications that you wish to the format. For our example, we will change the wording in the title and apply labels to the pie graph.
5.
Save your Excel file so that you do not lose your work.

Read more ►

How to Make Address Labels From Excel 2007

1. Open Excel by clicking 'Start,' then 'All Programs,' then 'Microsoft Office,' and finally 'Microsoft Office Excel 2007.'
2. Click the 'Microsoft Office Button,' then select and click 'New,' and under the Template > Microsoft Office Online section click 'Labels.'
3. Click 'Mail and shipping,' then select and click 'Business.'
4. Select and double click the icon 'Avery 8160 Template' and the label template will begin downloading onto your computer system. Once it has completely downloaded, 30 labels will appear on a letter-size, 8.5-inch by 11-inch, Excel worksheet window.
5. Type the address information onto the address labels.
6. Print out the typed labels using the Avery 8160 labels.

Read more ►

How to Compare Two Excel Spreadsheets for Duplicate Rows

1. Open your spreadsheet and make sure you know what columns the data you want compared are in. This example will assume the data is in column A, and that the two lists to be compared are in two spreadsheet tabs, labeled Sheet1 and Sheet2 of the same workbook.
2. Enter the following formula in cell B1 of Sheet1. =COUNTIF(Sheet1A:A,Sheet2A:A). Hit Enter. If the record is unique (it doesn't appear on both lists), this will evaluate to 0.
3. Copy the formula from cell B1 through as many rows as you need on Sheet1.
4. Select columns A and B, and click on the Home tab (in Excel 2007) and select Sort and Filter. Choose to filter the results by Column B in ascending order. This will move all the results with a '0' in column B to the top, putting all your duplicate records at the bottom of the list.

Read more ►

How to Draw in Excel 2007

1. Open Microsoft Excel 2007.
2. Click on the 'Insert' tab. This is located near the top right-hand corner of the screen.
3. Click on the 'Line' drawing tool. The 'Line' tool is a good place to start, because you can use basic line shapes to draw a plethora of other custom-drawn shapes.
4. Click 'Lock Drawing Mode,' located underneath the main 'Line' heading. This option lets you connect several lines to predetermined nodes.
5. Click on the document where you want the line to start.
6. Drag your cursor across the spreadsheet to form a line and double-click when the line is long enough. Repeat this process as needed, and click on existing lines to create connection nodes. If you want to move a line or a node, click and drag the entire line or one of the circular nodes.
7. Press the 'Esc' key when you are finished connecting lines. This takes you out of the line editing mode.
8. Click on the 'Shapes' tool if you need to make a simple, pre-drawn shape.
9. Select the desired shape from the list of icons. The 'Shapes' tool gives you access to squares, circles, triangles and oblong shapes, each indicated by different icons underneath the 'Shapes' tool heading.
10. Click and drag the selected shape across your spreadsheet.
11. Click on the 'Home' tab when you are finished. This returns you to the main Excel window, letting you type text, adjust fonts and use Excel macros.

Read more ►

How to Make a Checkable Survey in Excel

1. Open a new Microsoft Excel 2010 worksheet.
2. Enter your desired text into the cells on the spreadsheet. You can click and drag the sides of the letters at the top of each column or the numbers at the left side of each row to resize the cells as you need to. This text is what the survey taker will see when they are using the survey. Leave spaces next to the text so that you can add your checkboxes.
3. Click the 'File' tab at the top of the screen and choose 'Options' from the list that appears on the left side of the window. Select 'Customize Ribbon' from the list on the Excel Options window. Look at the column that appears on the right side of the window and place a check mark next to 'Developer.' This will allow you to use the Developer tools with your spreadsheet. Click 'OK' to go back to your spreadsheet.
4. Select the 'Developer' tab at the top of the spreadsheet. Click the 'Insert' button on the ribbon and choose the small checkbox under 'ActiveX Controls.' Click and hold the mouse button anywhere on the spreadsheet, then drag the mouse down and to the right to create a box that will become your checkbox. Release the mouse button and the checkbox will appear.
5. Click on the checkbox to select it. Press 'Ctrl' and 'C' to copy it to your clipboard, then press 'Ctrl' and 'V' to paste a second copy of the checkbox. Press 'Ctrl' and 'V' repeatedly until you have created all the checkboxes that your survey needs.
6. Move your mouse over a checkbox until the pointer turns into a set of four arrows. Click and hold the mouse button down, then drag the checkbox to wherever you want it located on the spreadsheet. ActiveX objects exist above the spreadsheet level, so you do not have to place the checkbox within any particular cell or set of cells. Move each checkbox to your desired location.
7. Right-click a checkbox, move your mouse over 'Checkbox Object' and choose 'Edit.' You can now change the default text in the checkbox to whatever you desire. Repeat this process for every checkbox. If you don't want to use any text, click and hold the mouse button over either of the corners on the right side of the box. Drag the mouse to make the checkbox smaller until just the actual checkbox is visible, essentially hiding the text. Repeat this process for every checkbox.
8. Click the 'Design Mode' button in the ribbon to exit design mode. Your checkboxes will now be locked in place, and clicking on them will only add or remove a check. You will need to click 'Design Mode' again if you want to edit the boxes any further.

Read more ►

How to Turn an Excel Spreadsheet Into a Standalone Application

1. Download and install the XCell Compiler using the link in the Resources section.
2. Launch Microsoft Excel and open the spreadsheet that you would like to convert to a standalone EXE file.
3. Click the Office jewel in the upper-left corner of the window. Click 'Excel Options.'
4. Click the 'Trust Center' link on the left side of the window, then click the 'Trust Center Settings' button.
5. Click the 'Macro Settings' link on the left side of the window, then place a check in the box labeled 'Trust access to the VBA project object model.' Click 'OK.'
6. Click the 'Add-Ins' tab at the top of the Excel window.
7. Click the 'DoneEx' menu, then click 'XCell Compiler' and 'Compile.' If XCell Compiler is not registered yet, a window will be displayed. Click 'Close.'
8. Click the '...' button next to 'Target Path,' and browse to the location where you would like the converted file to be saved. Click the 'Compile' button.
9. Click 'OK' and close Excel. Find the converted EXE file in the location that you specified in the previous step. The program will open when double-clicked just as a standard application.

Read more ►

How to Copy and Paste Tables From Excel into Microsoft Word 2003

1. Highlight and copy the table from Excel. Left-click and hold as you highlight the entire table in Excel. Release the mouse button and the table will remain highlighted. Right-click on the highlighted table, and then click on “Copy.”
2. Select the area in Word that you wish to paste the table into. Click on the portion of the document that you wish to paste the table into.
3. Paste the table into Word. Scroll to the Edit tab and then select “Paste.”
4. Choose your table paste attributes. On the lower-right corner of the table you just pasted, there will be a clipboard icon. Click on this to change the table paste attributes. You can choose from the following table paste attributes by clicking on the corresponding radial button: Keep Source Formatting (which will paste that table the same way you copied it), Match Destination Table Style (which will copy the formatting of any existing tables in Word), Keep Text Only (which will only copy the text from the table), Keep Source Formatting and Link to Excel (which will update the table in Word any time you make changes to the same table in Excel) and Match Destination Formatting and Link to Excel (which will match the formatting of any existing Word tables, and update the table as you make changes to it in Excel).

Read more ►

How to Check for Duplicates in Excel

1. Click the Windows 'Start' button and select 'All Programs.' Click the 'Microsoft Office 2010' folder to expand the contents of the folder. Click the 'Microsoft Excel 2010' option to launch the Excel application.
2. Click the Microsoft Office button, and click 'Open.' Navigate to the spreadsheet file to search for duplicates.
3. Click the top leftmost cell to select the entire worksheet. To select a range of cells, click the top leftmost cell and drag the cursor to select the desired columns and rows.
4. Click 'Data' from the top navigation ribbon. Click 'Filter' and then click 'Advanced Filter.'
5. Click the 'Filter the List, in Place' option.
6. Check the check box in front of the 'Unique Records Only' option, and then click 'OK.'
7. Click the 'Edit' option on the top navigation ribbon, and then click 'Office Clipboard.'
8. Click the 'Copy' icon on the top navigation ribbon, or press the 'Ctrl' 'C' keys to copy the Excel data to the clipboard in the left pane of the Excel interface.
9. Click 'Filter' and then click 'Show All' from the Data pane in the top navigation ribbon. The original data are displayed, including duplicates.
10. Press the 'Delete' key to delete the original list.
11. Click the filtered list in the clipboard pane. The filter list replaces the original list in the Excel file. Duplicate records are removed.
12. Click the 'Microsoft Office' button, and then click 'Save' to save your new filtered list.

Read more ►

How to Recover a VBA Excel Password

1. Navigate to the A Pass Cracker Atomic Visual Basic for Applications (AVBA) link (see Resources). AVBA recovers passwords for VBA passwords associated with Microsoft Excel and Word. The software promises to recover passwords instantly regardless of length. Although there is a trial version of the software available, it will only show the first two symbols of the cracked password: you must purchase the full version to see the rest of the password.
2. Click on Last Bit's VBA Password link (see Resources) and download the software. VBA Password uses a combination of six different techniques to crack your password, including dictionary attack which tries to match your password against every word in the dictionary. The software uses brute force (trying every possible character and letter combination) to recover some passwords; this process may take days so you can choose to reset the password instantly for access. VBA password works with all versions of Excel.
3. Navigate to Elcomsoft'sAdvanced VBA Password Recovery (AVPR) website (see Resources). AVPR can help you to recover Excel VBA passwords from Excel 97, 2000, XP, 2003. The software can also find the password for any Excel add-ins. The software works by utilizing the 'backdoor' method which bypasses the password and recovers the file. Download the trial version of the software and use it free for 30 days. The trial version is limited to cracking passwords of 3 letters or less.

Read more ►

How to Get Solver for Excel 2007

1. Open a spreadsheet in Excel.
2. Click 'Add-ins' on the Tools menu. Select 'Solver Add-in' check box.
3. Click 'OK,' and the Solver installation will begin automatically.
4. Click on 'Solver' on the Tools menu to run the program.

Read more ►

How to Protect a Pivot Table

1. Open the worksheet with the pivot table. Select the pivot table information in your Excel worksheet that you would like to protect.
2. Click 'Review' from the ribbon menu. Under the Changes group, click 'Protect Sheet.'
3. Click the 'Use PivotTable Report' check box. Type a password in the 'Password to Unprotect Sheet' box.
4. Click 'OK' and re-enter the password. Your pivot table is now protected.

Read more ►

How to Calculate Mode Using Excel

1. Add the numbers in the first column of your Excel 2007 document. For example, add the numbers 9,6,5,9,4,9,2 and 1 in A2, A3, A4, A5, A6, A7, A8 and A9.
2. For this example, click on the 'A11' cell. This is the cell where you will calculate the mode. When you calculate another mode, choose any cell at the bottom of the list of numbers you are using.
3. Click 'Insert Function' on the top, left-hand side of the Excel spreadsheet. The 'Insert Function' window will pop open. With Microsoft Excel 2007, click on the 'Formulas' tab and then 'Insert Function.'
4. Click on the drop-down menu of 'Or select a category.' Select 'Statistical' from the drop-down menu.
5. Scroll down the 'Select a function' window. Choose 'MODE,' which is the function of mode.
6. Click 'OK.' The 'Functions Arguments' window will pop open. Ensure that on 'Number 1' cell, A2:A9 is populated. If A2:A9 is not populated, enter A2:A9 manually. Click 'OK.'
7. The mode has been successfully calculated. In this example, the calculated value of the mode is 9.

Read more ►

How to Add a Calendar Date Picker to an Excel Spreadsheet

1. Download the free 'Calendar' tool available at isamrad.com/ExcelCal/default.htm. This plugin is a standard Excel add-in program. It is offered as a compressed ZIP file which must be unzipped after download. Once the XLA file is placed into the Excel 'XLSTART' directory in the 'Program Files' folder of Windows, Excel will show a date picker icon next to cells that are already formatted as dates. Additionally, the date picker can be launched by right-clicking on any cell and choosing the 'Pick from Calendar' option. The applet also installs a toolbar button for the feature.
2. Activate a free 30-day trial of the Pop-up Excel Calendar created by Office Kit. This program offers a robust calendar interface that includes quick jumps to any month or year as well as shortcut buttons. Additionally, the program installs a unique calendar toolbar which features a quick launch for the Pop-up Excel Calendar as well as other features, including a settings button. The applet works on all major versions of Excel. If you are pleased with the demo, Pop-up Excel Calendar costs $20 as of February 2010.
3. Download the WinCalendar utility. The software comes in multiple versions, including a free option. The program is a robust date picker that displays multiple calendars simultaneously for quick selection of any date within a three-month period. Additionally it highlights holidays and offers user customization of new holiday entries. The calendar can be re-sized to suit the user's preference. It also functions in other Microsoft Office programs, or as a standalone program. The free version is functional but excludes emphasis of some holidays and limits the size of the pop-up interface. The free version also limits the number of entries that may be made.

Read more ►