How to Add a Watermark to an Excel Spreadsheet

Insert Your Watermark
1. Place your cursor where you would like the watermark to appear. Choose to put this in a header or footer or in the middle of a page.
2. Choose 'Picture' under the 'Insert' menu.
3. Browse for and select your image. Hit 'OK.' Your image should now be on the spreadsheet.
Adjust the Coloring to Make the Image Look Like a Watermark
4. Click on the photo. This will make the picture toolbar appear.
5. Click 'Format Picture' on the toolbar.
6. Under 'Image Control/Color,' select 'Watermark.' This will lighten the colors in your image to look like a watermark.

Read more ►

How Can I Make a Dynamic Drop

1. Launch Excel 2003 and click the Tools menu. Select Macro and then Visual Basic Editor. Insert a new module by clicking the Insert menu and selecting Module. Type the following to create a new procedure:Private Sub createDropDownList()On Error GoTo Err_createDropDownList:
2. Type the following to dynamically create a new ComboBox control in the active Excel worksheet:With ActiveSheet.OLEObjects.Add(ClassType:='Forms.ComboBox.1', Link:=False, _DisplayAsIcon:=False, Left:=70, Top:=60, _Width:=100, Height:=25)With .Object.AddItem 'Item List 1'.AddItem 'Item List 2'.AddItem 'Item List 3'End WithEnd WithThis code will also add three items to the ComboBox control.
3. Type the following to exit the procedure and handle errors:Exit_createDropDownList:Exit SubErr_createDropDownList:MsgBox Err.DescriptionResume Exit_createDropDownList:End Sub
4. Press 'F5' to run your procedure. You will see a new ComboBox control created in your worksheet.

Read more ►

How to Copy Paste a Range to a VBA Excel Workbook

1. Create two different workbooks in Excel. The first workbook will contain the range that you want to copy while the other one is where you will paste the range into. Keep the workbook that currently has the range open. The other workbook doesn't have to be open to complete the rest of the steps.
2. Open the Visual Basic Editor. Click on 'Tools > Macros > Visual Basic Editor' in Excel 2003 or earlier. In Excel 2007, you will need to show the 'Developer' tab first. Click on the 'Office' button and then 'Excel Option.' Click the checkbox next to 'Show Developer tab on the ribbon' under the 'General' tab and then click 'OK.' Click on the 'Developer' tab and then click 'Visual Basic' to open the editor.
3. Click on 'This Workbook' in the left-hand pane to open it in the code window. Add the following code to the code window:Public Sub CopyRange()Workbooks('Book1')._
Worksheet('CurrentSheet') _
Range ('A1:C10').Copy_
Destination:=
Workbooks('Book2')._
Worksheets('PasteSheet')._
Range('A1')
End SubChange the information in the parenthesis to match your workbooks. 'Book 1' is the workbook that you are currently working in. 'CurrentSheet' is the worksheet that has the range you want to copy. 'A1:C10' is the range of cells that you want to copy. 'Book2' is the workbook that you want to paste the range. 'PasteSheet' is the worksheet that you want to paste the range into. 'A1' is the beginning cell that you want to paste the range into.
4. Save and run the code. Click on 'Tools > Macro > Macros' in Excel 97 to 2003. In Excel 2007, click on the 'Macros' button on the 'Developer' tab. See the 'CopyRange' function in the list of macros. Click on it and then click 'Run.' The code will run and copy the range from one worksheet and paste it into another.

Read more ►

How to Make a Chart in Excel to Show 2009 2010 Data Side

1. Open the Excel 2010 spreadsheet that holds your 2009 and 2010 data.
2. Click and hold the mouse on 'A1' above the first column and then drag the mouse to column 'C' and release the button. Right-click 'A' and choose 'Insert,' creating three new columns at the front of the spreadsheet.
3. Click cell 'B1' and type '2009.' Then click cell 'C1' and type '2010.' Click cell 'A2' and type in the first period for which you have data. This can be a day, week, month, quarter or any time period, less than a year, that you need. Continue down the column until you have added all your time periods.
4. Click cell 'B2' and enter the data that corresponds to your time period for 2009. Continue down the column, adding your data as you go. Repeat this process in column 'C,' but use the data from 2010.
5. Click any cell in your data table. Click the 'Insert' tab at the top of the screen, then click the 'Column' or 'Bar' buttons in the 'Chart' area, depending on whether you want your data displayed respectively, vertically or horizontally. Choose the 'Clustered Column' or 'Clustered Bar' chart buttons from the small menu that appears. Your chart, which will contain the 2009 and 2010 numbers side-by-side, will appear on the spreadsheet.

Read more ►

How to Unprotect a Password Protected Word Document

1. Open the Word document with the password that was previously set. If you don't have the password, you will not be able to open the document or remove the password.
2. Click on 'File' then 'Info.' Under the 'Permissions' area click on 'Encrypt with Password' if you are using Microsoft Word 2010. If using Word 2007, click on the circular 'Office' button, 'Prepare' then the 'Encrypt Document' button. In Word 2003 select 'Tools,' 'Options' then 'Security.'
3. Highlight all characters in the password field and delete them. This removes the password from being prompted at start up. Click on the 'OK' button to close window.
4. Save the document. Check to see that the password has been successfully removed. If not, go back to the security settings and try it again, since your changes may not have been saved.

Read more ►

How to Create a Map From an Excel Spreadsheet

1. Insert your data into an Excel worksheet. Include a column heading. For example, put a list of city names in column A (with the heading 'City names' in cell A1), and put a list of ZIP codes in column B (with the heading 'ZIP codes' in cell B1).
2. Click 'File' and then click 'Save.'
3. Click on the top left cell of your data. In the above example, that would be cell A1. Drag the cursor down over your data, to the bottom right corner.
4. Click 'Insert' and then click 'Map.'
5. Click on the area of the worksheet where you want your map to be. Hold the left mouse button and drag the cursor from the top left to the bottom right until the map is the size you want.
6. Let go of the cursor. Excel will display a pop-up menu.
7. Select the type of map you want by clicking on it. Click on 'OK.' Excel will insert the Excel map into your worksheet.

Read more ►

How to Repeat an Action

1. Start Excel by clicking the Windows 'Start' button and typing 'excel' into the search bar. Click the 'Microsoft Excel 2010' result that appears.
2. Click the green 'File' tab, click the 'Help' heading, and then click 'Options.'
3. Click the 'Quick Access Toolbar' entry. Click the 'Choose commands from' drop-down box and then select 'Popular Commands.'
4. Select 'Repeat' from the list of commands, and then click the 'Add' button. Click 'OK' to close the dialog box.
5. Point the mouse cursor to the top of the Excel interface and then click the blue 'repeat' button that has appeared there.

Read more ►

How to Use OLAP in Excel

1. Launch Microsoft Excel. Click the “Data” tab on the main menu ribbon. Click “From Other Sources” in the “Get External Data” group. Click the “From Analysis Services” option in the drop-down list.
2. Type the name of the remote OLAP server into the “Server Name” input field in the Data Connection Wizard. Click the check box next to “Use the following User Name and Password.” Enter your database username and password into the applicable boxes. Alternatively, click “Use Windows Authentication” if the remote server is set up to work with your Windows username and password. If in doubt, check login details with the server administrator. Click “Select Database and Table” followed by “Next.”
3. Click on the OLAP database that contains the data you want to import to Excel. To access a specific table or data cube within the database, click and select the “Connect to a Specific Cube or Table” check box. Select your preferred table or cube from the list of available items. Click “Save Data Connection File and Finish.” Click the “Next” button.
4. Type your preferred file name for the imported data into the “File Name” box, or skip this step to retain the default file name. Click “Browse” to set the download location, or use the default location of “My Data Sources.” Type a description of the data, a data name and relevant keywords into the applicable input fields. Click the check box beside “Always attempt to use this file to refresh this data” to enable this option.
5. Click the “Finish” button. Select your preferred option from the list under “Select how you want to view this data in your workbook” in the “Import Data” dialog box. You can choose to create a PivotTable Report or a PivotChart and PivotTable Report. Select “Only Create Connection” if you just want to save the OLAP data connection without importing data.
6. Select “Existing Worksheet” or “New Worksheet” in the “Where do you want to put the data” section. Type the worksheet cell reference of the first cell in the output table range into the input box; for example, type “A1” or 'A22.'
7. Click the “OK” button to import the data from OLAP to Excel.

Read more ►

How to Divide Excel Pivot Table Data Into Separate Spreadsheets Within the Same File

1. Open Excel 2007 and select a workbook. Select the 'Office' button and click 'Open.' Browse your network and click the file. Select the 'Open' button. The workbook opens.
2. Highlight the data you want included in your pivot table. Select the 'Insert' tab and click 'Pivot Table.' Select 'Pivot Table' again. The Create Pivot Table dialog appears. Click 'OK.' A blank pivot table appears.
3. Add fields to the pivot table by checking field names from the right 'Pivot Table Field List.' Add fields that you want calculated or summarized into the pivot table by checking them. Manually drag the field from the field list to the 'Sum Values' section of the right 'Pivot Table Field List.'
4. Drag one of your fields into the 'Report Filter' section of your right 'Pivot Table Field List.' A filter appears above your data. Select the 'Pivot Table Tools' tab. Click the 'Option' tab. Select the 'Options' button in the Pivot Table group. Select 'Show Report Filter Pages.' Click 'OK.' The report filter pages are inserted as a worksheet in your workbook.

Read more ►

How to Convert XLS to PRN Files

1. Use Microsoft Excel to open the XLS file you want to convert.
2. Select Print from the File menu. Do not use the Print button on the toolbar because the toolbar button will bypass the print dialog window.
3. In the Print dialog window check the Print to file checkbox. Normally this checkbox is off, in which case Excel would print to the printer. Click the OK button.
4. In the Print to File dialog window type the Output file name. This will be the name of your file on the disk. Excel does not automatically add the '.prn' to the file name so you must type that in yourself; it will still be a PRN file even if you don't give it the '.prn' extension. Click the OK button. You now see a dialog window that tells you that you are printing; when that window disappears the conversion is complete.

Read more ►

How to Add ActiveX Control in Excel 2007

1. Open the Microsoft Excel 2007 application from your computer and then click on the “Microsoft Office” button.
2. Click on the “Excel Options” button and then click on the box next to the “Show Developer tab in the Ribbon” field. Click on the “OK” button.
3. Click on the “Developer” tab and then click on the “Insert” option from the “Controls” group. Click on the “More Controls” option below the “ActiveX Controls” group.
4. Click on the ActiveX Control that you want to add from the list of controls in the More Controls dialog box.
5. Click the location on your worksheet where you want the ActiveX control to appear. The control will then appear in your worksheet.

Read more ►

How to Remove Duplicate Lines in Excel

Excel 2003
1. Open the Excel file that has the duplicate data that you need to remove.
2. Highlight the data that you want to work with, then right-click and select 'Copy.' Click on an empty cell on your worksheet, right-click and choose 'Paste.' This step creates a backup copy of all the data.
3. Highlight the original data again.
4. Click the 'Data' menu and then click 'Filter.' Scroll over and click 'Advanced Filter.'
5. Place the radio button next to 'Filter the list, in place' in the 'Advanced Filter' window. Put a check mark next to 'Unique records only.'
6. Click the 'OK' button.
7. Delete the backup copy of your data once you are satisfied with the filtered list.
Excel 2007
8. Open the Excel file with the duplicate data.
9. Highlight the data you want to work with, then right-click and select 'Copy.' Move to an empty cell, right-click and choose 'Paste.' This step creates a backup copy of all your original data.
10. Highlight the original data again.
11. Click to select the 'Data' tab. Click the 'Remove Duplicates' button.
12. Choose the rows from which you want duplicates removed. Click the 'OK' button, then 'OK' again in the information box that appears indicating how many duplicates Excel found.
13. Delete the backup copy of your data once you are satisfied with the filtered list.

Read more ►

How to Resize Cells in Excel 2007

1. Click on the cell(s) from the columns you want resized.
2. Click on the 'Home' ribbon, and then click 'Format' in the 'Cells' group.
3. Click the 'Width' option in the 'Cell Size' graph that appears.
4. Type the length you would like the cells to be in the box that appears.
5. Click 'OK' to resize the cells.

Read more ►

How to Password Protect Excel Worksheets

1. Go to 'Tools' on the main menu.
2. Select 'Options.'
3. Select the 'Security' tab.
4. Under 'File encryption options for this document,' enter the password in the field called 'Password to open' and click 'OK.'
5. In the Confirm Password window, enter the password in the field called 'Reenter password to open' and click 'OK.' In the Confirm Password window, Excel gives two cautionary notes: first, that a lost or forgotten password cannot be recovered; second, that passwords are case-sensitive. This means that a password such as 'abcd' is not the same as 'Abcd.'
6. Save the password by saving the document. To do this, click the 'Save' icon.
7. If you want to set a password so that only authorized personnel can modify the worksheet, go to 'Tools' on the main menu, select 'Options,' select the 'Security' tab, and under 'File sharing options for this document,' enter the password in the 'Password to modify' field and click 'OK.'
8. Notify authorized personnel of the password required to open and/or modify the worksheet.

Read more ►

How to Make Excel 2007 Your Default Program

1. Hold the Windows orb key, and press 'R' to open the 'Run' dialog window.
2. Type the exact phrase in the Run dialog window, including quotation marks:'C:\Program Files\Microsoft Office\Office12\excel.exe' /regserverIf you installed Excel 2007 in a different folder, adjust the path accordingly. If you are using a 64-bit version of Windows 7, change 'Program Files' to 'Program Files (x86)'.
3. Click 'OK' to have Excel register itself as the default program.

Read more ►