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 ►

How to Enter Formulas in Excel Divide the Sum of Several Numbers by a Number

1. Click on an empty cell in your spreadsheet.
2. Type an '=' sign.
3. Type the following formula into the cell: UM(A1:A10).
4. Replace 'A1:A10' with the cell locations of the numbers you want to add. In this example, the series of numbers is in cells A1 through A10.
5. Type a division sign '/' followed by the number you want to divide by. For example, if you wanted to divide the total of cells A1 to A10 by 3, type '/3.'
6. Press the 'Enter' key; Excel will perform the calculation.

Read more ►

How to Create a Flow Chart in MS Word

1. Open Microsoft Word, which automatically defaults to a blank portrait-oriented page on the screen. To change your flow chart to landscape orientation, click the 'Page Layout' tab at the top of the screen and click the 'Orientation' button directly below it. The page changes to landscape.
2. Type the name of the flow chart at the top of the page, such as 'Weather Closing Phone Tree.' Highlight the words, click the 'Home' tab at the top of the screen and change their appearance using the options in the 'Font' section of the ribbon/toolbar, such as font style and text color.
3. Click the 'Insert' tab at the top of the screen. Click the 'SmartArt' button on the ribbon/toolbar below it, which opens the 'Choose a SmartArt graphic' window.
4. Scroll through the different flow chart options, including vertical hexagons, triangles of different sizes and boxes and arrows in a horizontal line. The flow chart options in the 'Process' section of the window may be especially applicable to a flow chart.
5. Double-click a SmartArt shape group and it appears on the Word window. Enlarge the flow chart to fit the page by grabbing a corner and dragging it toward one of the edges of the Word workspace.
6. Click one of the '[Text]' words within the SmartArt flow chart shape and type the shape's information, such as a worker's name. Repeat this to fill the rest of the flow chart with labels for each shape.
7. Recolor the flow chart (optional) by double-clicking anywhere on it to bring up a new 'SmartArt Tools' toolbar. Click the 'Change Colors' button on the toolbar and hover your cursor over the options in the drop-down menu. As you hover over each group of colors, the flow chart changes. Click a color group to commit the change.
8. Click the 'File' tab, click 'Save As,' give the flow chart a name and save it to your computer.

Read more ►

How to Exit Out of Header Footer Option in Excel 2007

1. Add a header or footer. Click 'Header Footer' in the 'Text' area of the 'Insert' tab. Click on the document in the area marked 'Click to add header' or 'Click to add footer.'
2. Design the header or footer. Select preformatted options from the menu, type in your own text, or use a combination of menu items and typed text. When you finish the header or footer, click anywhere on the body of the document.
3. Change to Normal View. Click 'Normal' in the 'Workbook Views' area of the 'View' tab. You will leave header/footer mode and return to your worksheet.

Read more ►

How to Print Address Labels in Excel

1.
Open a blank worksheet in Excel. Go to the 'File' menu, click on 'Page Setup' and go to the 'Margins' tab in Excel 2003. In Excel 2007, go to the 'Page Layout' tab and click 'Margins.'
2.
Enter '0' in the Top and Bottom boxes. Change the margins in the left and right boxes to '.19.' Under Center on Page, select 'Horizontally' and 'Vertically.' Apply these changes by clicking 'OK.'
3.
Select cells A1 through A10 with the mouse. Go to the 'Format' menu in Excel 2003, point to 'Row' and select 'Height.' In Excel 2007, click 'Format' on the Home tab and click 'Row Height.' Enter '72' and click 'OK.' In the same manner, change the Column Width to 35. Repeat the steps in cells C1 through C10 and E1 through E10.
4.
Use the mouse to select the cells in B1 through B10. Change the width of the column to 1.29. Repeat with cells D1 through D10.
5.
Select all of the cells from A1 through E10 with the mouse. Click the 'Borders' drop-down on the Format toolbar in Excel 2003 or the Font group on the Home tab in Excel 2007. Click 'All Borders.'
6. Enter the names and addresses into the label cells. The cells in columns A, C and E are the label areas. Columns B and D are the margins between labels. Print onto the label paper.

Read more ►