How to Copy and Paste for Conditional Formatting in Excel

Copy Conditional Formatting
1. Select the Excel cell that contains the conditional formatting rules you want to copy. Hold the 'Ctrl' button and press the letter 'C' to copy the cell to the clip board.
2. Right click on the cell you want to copy the conditional formatting to. If you are copying it to a range of cells, left-click on the first cell and hold the mouse button down. Then move the mouse to the last cell and release the button. Finally, right-click on the selected cells.
3. Move your mouse over 'Paste Special' in the pop-up menu to reveal additional pasting options. Select the icon that has a percentage symbol and a paint brush. The icon is located under the 'Other Paste Options' area of the menu and reads 'Formatting (R)' when you move your mouse over it. Once you select this icon, the conditional formatting will now affect the new cells.
Combine Multiple Groups of Conditional Formatting
4. Select the top-left cell in the first group and hold the mouse button down. Move the mouse to the bottom-right cell in the second and release the mouse button, selecting the entire area. If the cells you need to select are not right next to each other, you can hold down the 'Ctrl' button to select additional cells and areas.
5. Select the 'Home' tab at the top of the screen and then press the 'Conditional Formatting' button in the 'Styles' area. Choose 'Manage Rules' from the menu that appears. You will see a screen with several of the same conditional formatting rules listed.
6. Select one of the duplicate rules and press the 'Delete Rule' button. Continue until you only have one conditional formatting rule remaining. Select the small box next to the 'Applies to' field, which will move your cursor back to the Excel worksheet.
7. Select the exact same area of the worksheet as you did before you brought up the conditional formatting rules manager. Press the small box again once you have completed your selection. Click 'Apply' at the bottom of the rules manager followed by 'OK.' All the selected cells are now a part of the same conditional formatting group.

Read more ►

How to Show Grid Lines When Printing in Microsoft Excel

Printing
1. Open the worksheet or worksheets in Excel that you wish to print. You can do this by clicking on theworksheet tab at the bottom of the screen. To select more than one worksheet, after you select the first worksheet, press Shift and then select the second or multiple worksheets.
2. Go to the 'Page Layout' tab on your menu bar for Microsoft Excel 97-2003 or interface for Microsoft Excel 2007.
3. Select the 'Sheet Options' group and click on the 'Print' check box under 'Gridlines.'
4. Preview the grid lines to view how they will print by pressing the 'Ctrl F2' keys to open the Print Preview window. Remember that grid lines are only designed to print around inputted data in a worksheet. To print grid lines around empty cells you must change the print area.
5. Select the empty cells on the worksheet that you want to include with grid lines as well. Go to the 'Page Layout' tab. In the 'Page Setup' group, select 'Print Area' and then click 'Add to Print Area.'
6. Go to the Microsoft Excel button for the 2007 version and click 'Print.' For Excel 97-2003, go to the menu and select 'Print.'
Troubleshooting
7. Go to 'Page Setup' and 'Sheet' tab. Make sure that the 'Draft' quality box is not checked.
8. Download the latest driver for your printer from the manufacturer's website if the grid lines still are not printing. Sometimes a problem with the printer driver interferes with printing instructions.
9. Change and apply borders to the cells if you are having trouble with grid lines.

Read more ►

How to Create a Form in Excel 2007

1. Open the Microsoft Excel 2007 application. Click the “Microsoft Office” button and select “Open.”
2. Locate the Excel 2007 file stored on your computer for which you want to create a form. Select the file and click on the “Open” button.
3. Add any column headers to the columns within the spreadsheet. Click on any cell in the data table where you want to add the form.
4. Click on the arrow next to the Quick Access Toolbar and click on the “More Commands” option. Click on the “All Commands” option and select the “Form” button from the list.
5. Click on the “Form” button from the Quick Access Toolbar. The data form will appear as a dialog box within the spreadsheet.
6. Click on the 'New' button within the data form dialog box if you want to enter data for a new row. Enter the data into the new field that appears in the dialog box and the press 'Enter.'
7. Click on the 'Find Next' or 'Find Prev' buttons to navigate to each row in the data form dialog box. Click on the 'Criteria' button to enter any data you want to search for within the form.
8. Change data in a row in the data form dialog box by clicking on any row and entering the new data. Press 'Enter' and the row will be updated on the form and in your spreadsheet.

Read more ►

How to Find Out How Often a Number in a Set Appears in Excel

1. Highlight your data set. For example, if your set of numbers is in cells A1 to A50, highlight cell A1 with your mouse.
2. Choose 'Data' from the ribbon. Click on 'Advanced' to bring up a pop-up box.
3. Check the 'Copy to another location' radio button, and check the 'Unique records only' box. Enter the range of your data set. In this example, cells A1 to A50 would be entered as $A$1:$A$50. In the 'copy to' box, enter where you would like the data to be output. For example, enter $B$1:$B$50 (which will return the result in cells B1 to B50). Press 'OK.' This returns a list of numbers in your data set.
4. Enter the following formula into cell C1: =COUNTIF(A$1:A$50,B1). This counts the number of times the item in cell B1 appears in the data set from cell A1 to cell A50, and returns the result in cell C1.
5. Copy the formula in the cell from Step 4 (in this example, cell C1) for all cells in column B. For example, if you have 10 cells filled in column B, select cell C1 by clicking with the mouse, and drag the fill tool (the little black square in the bottom right corner) down to cell C10.

Read more ►

How to Use Hyperlinks in Dropdown Menus in Excel

Adding Links to Drop-down Menus
1. Access editing capabilities by double-clicking the cell in the menu that contains the URL that you would like to hyperlink.
2. Activate the URL by pressing 'Enter.' This will convert the text URL to an active hyperlink.
3. Click that now-active hyperlink one more time to complete the process.
4. Repeat Steps 1 through 3 for each menu item.
Alternate Method
5. Choose a menu cell, for example, cell B6.
6. Insert the following function into cell C6 (the cell below the chosen menu cell):=HYPERLINK(B6, 'Goto Link')
7. Change 'GoTo Link' to your choice of text.

Read more ►

How to Use Excel to Make a Percentage Bar Graph

1. Open Excel to a blank spreadsheet.
2. Enter your numbers in cells in table form and add labels for each column and row. For example, if you want to compare men to women and the percent who smoke vs. the percent who do not smoke, you would create a 2x2 table. You would label one column 'Men' and one column 'Women.' To the left, you would label the first row 'Smoke' and the second row 'Don't smoke.' When entering numbers in each cell, add the percent sign to let Excel know that these are percentages. For example, you should enter '32%' instead of '32.'
3. Select the table you created by clicking and dragging to highlight the cells.
4. Click the 'Insert' tab on the ribbon.
5. Click 'Bar' in the Charts group. Select a bar style that suits your needs. Excel will automatically create a bar graph with percentages for either your X or Y axis.
6. Click inside the chart to customize colors, sizes, fonts and background. Click the 'Design,' 'Layout' or 'Format' tabs in the Chart Tools section of the ribbon to select custom features.

Read more ►

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 ►