How to Update Conditional Formatting in Excel 2007

1. Highlight the cells with the conditional format you want to change. To make changes throughout the worksheet, highlight the entire spreadsheet by pressing 'Ctrl' and 'A' at the same time.
2. Click the 'Home' tab in the Office Ribbon and select 'Conditional formatting' from the 'Styles' box.
3. Select 'Manage rules' from the menu that appears. This opens the conditional formatting rules manager dialog box.
4. Click on the rule you want to change.
5. Click 'Edit rule' and select from the dialog box options to update your conditional formatting.
6. Click 'Ok' to save your change, then click 'Apply' to apply the rule to the spreadsheet.
7. Click the next rule and repeat the 'Edit rule' and saving processes.
8. Click 'Ok' to save all changes and close out of the conditional formatting rules manager when you are finished.

Read more ►

How to Convert Lotus 123 to Excel

1. Click the “File” drop-down menu in Excel. Select “Open.” Double-click the Lotus file in the tree that pops up. Spot check the file to see if formula and formats look the way they should. If the formatting is wrong, keep in mind that WK1 and WK3 files save formatting to a separate file. You’ll have to have that file in the same directory as your WK1 or WK3 file.
2. Click the “File” drop-down menu in Excel. Select “Save.” The Save panel will pop up.
3. Select the .XLS file type in the Save panel and click OK. Excel will then convert the workbook to Excel format.
4. Open the new .XLS file. Search on the phrase “formula failed to convert.” If Excel couldn’t convert a formula, it displays the value that the formula produced and leave the “formula failed” comment. Since these are comments, you’ll have to find them by clicking the following sequence: Edit (drop-down menu), Find, Look in: Comments. Return these cells to formulas if possible.
5. Print out the newly converted spreadsheets and compare the numbers to a printout of the original spreadsheet to make sure every formula converted correctly.

Read more ►

How to Update a PivotTable Using a Cell Reference

1. Open the PivotTable you want to change.
2. Click on a cell within the PivotTable you want to update.
3. Run the PivotTable and PivotChart Wizard by clicking on the 'Tools' menu and then selecting 'PivotTable and PivotChart Wizard.' It automatically takes you to the wizard for your PivotTable.
4. Skip Step 1 and go directly to Step 2. This step allows you to redefine the cell reference for the source data. Input the new cell reference in the box for source data and click 'Next' and then select 'Finish.' The PivotTable automatically updates with the new data in the new cell reference.

Read more ►

How to Use Excel Templates

1. Turn on your computer and open Excel.
2. Click on 'File' in the tool bar and select 'New.' This will open a sidebar to the right of your Excel document.
3. Under 'Templates' click on the link that says 'On my computer.' This will open the 'Templates' window.
4. Click on the tab labeled 'Spreadsheet Solutions.' This will display the list of templates available on your computer. Select the template of your choice and then click 'OK.'
5. A new window should now be open and the template you selected should be displayed. Now you can fill in the template, and then save and name it accordingly.

Read more ►

How to Put Radio Buttons in an Excel Spreadsheet

1. Open Microsoft Excel.
2. Click the Microsoft Office button located in the top left corner of the window. Click 'Microsoft Excel Options.'
3. Click the 'Popular' category and the select check box for 'Show Developer Tab in the Ribbon.' This option is located under the 'Top Options for Working with Excel' heading. Click 'OK' when you are finished.
4. Click the Developer's tab on the menu bar and select 'Insert' from the Controls group. Select the 'Option' button, which is located under 'Form Controls.'
5. Click the spreadsheet cell where you want to insert the radio button.

Read more ►

How to Create a Password/Username for Excel 2003

Create an Excel 2003 Username
1. Open Excel. Go to 'Tools' and select 'Options.'
2. Click 'General' and enter your new username in the username field.
3. Click 'OK.'
Create an Excel 2003 Password
4. Open Excel, and open the spreadsheet you wish to password protect. Click 'Tools,' then select 'Options.'
5. Click the 'Security' tab. Enter a password in the 'Password to Open' field and click 'OK.'
6. Retype the password when Excel prompts you to confirm, and click 'OK.'

Read more ►

How to Use Excel to Create a Bell Curve

1. Type the word 'Mean' into cell E1 and 'Standard Deviation' into cell G1.
2. Type the desired mean and standard deviation for your bell curve into cells F1 and H1. The mean represents the average number from the entire data set. In a bell curve, this is often similar to the median, or the number which occurs most often. The standard deviation is a statistical property based on likelihood of occurrence. A deviation of 1 will include 68 percent of all the data in a collection. By the third deviation, almost all the data is included. For example, a mean of 5 with a deviation of 2 means that 68 percent of all the data will fall between the numbers 3 and 7, which are 2 removed from the mean of 5.
3. Type the number '-4' into cell A2. Select the cell after entering the data by clicking on it once. The desired numbers are arbitrary so long as the subsequent formulas are entered accurately for Excel to generate normally distributed data appropriate for the desired bell curve.
4. Click the 'Edit' menu and select the 'Fill' sub-menu.
5. Choose the 'Series' command from the 'Fill' sub-menu. A pop-up window will appear.
6. Select the 'Columns' option in the 'Series in' section of the 'Series' pop-up window. Select the 'Linear' option in the 'Type' section, and type '0.25' into the 'Step value' field. Type '4' into the 'Stop value' field and press the 'OK' button. The 'Step value' is customizable. Enter a smaller number to generate a curve with greater detail and more points, such as '0.1'. A higher number will show fewer data points.
7. Type the formula=A2*$H$1 $F$1into cell B2. Type the formula=NORMDIST(B2,$F$1,$H$1,FALSE)into cell C2. These functions generate the complex distribution of data necessary to form a true statistical bell curve.
8. Select cells B2 and C2 by clicking once on B2 and dragging the mouse to cell C2. Release the mouse.
9. Copy the formulas down through the entire data range. Hover the mouse over the lower-right corner of cell C2. The cursor will change to a black plus sign. Click and drag the plus sign down to the last row which contains data in column A.
10. Select columns B and C by clicking on cell B2 and dragging down to the last row that contains data, and over one column to include C.
11. Click the 'Chart' button at the top of the Excel program window. A pop-up window will appear.
12. Select the 'XY (Scatter)' chat type and press the 'Finish' button. The bell curve is created.

Read more ►

How to Bring an Image to the Front in Excel

1. Start Microsoft Excel 2007, and open a workbook from your files that contains images in an order you would like to change, or start a blank workbook and insert at least two images.
2. Click to select the image that you would like to bring to the front of the Excel workbook. This means if you put this image over another image it will remain on top, or in the front. White sizing handles will form around the image once you select it.
3. Select the 'Format' tab at the top of the Excel screen. This will display the Format ribbon which contains all the options for formatting an image in Excel.
4. Click the small arrow to the right of the 'Bring to Front' button in the 'Arrange' section of the Format ribbon. A drop-down list will be displayed.
5. Choose the 'Bring to Front' option to bring the picture to the front, so it overlaps all other images on the worksheet. Select the 'Bring Forward' option to bring the image to the front of any other image except those that are set to be brought to the front. Once clicked, the option will immediately take effect to the selected image.

Read more ►

How to Fix Column Width in a Pivot Table

1. Launch the 'Microsoft Excel' program and open a spreadsheet in which a pivot table is already created.
2. Right-click the pivot table and choose 'Table Options' from the resulting menu.
3. Uncheck the 'Auto Format Table' box and click 'OK.'
4. Adjust the column widths and refresh the pivot table. The columns will stay fixed at the width you specified instead of reformatting to fit the data.

Read more ►

How to Create Control Charts in Excel

1. Create a two-columned table; enter the dates or other time measuring criteria in the right column, and the data to be graphed in the left column. For example, to begin creating a control chart to measure the number of products produced on an assembly line per day, list the respective dates on the left and the corresponding production numbers on the right.
2. Calculate the average of your entire data series by entering '=AVERAGE(first value,last value)' into any cell, and establish your desired upper and lower control limits. In our production example, you may find that the average number of products made per day is 100, and you may wish to produce no more than 150 per day and no less than 50. In this case 150 would be your upper control limit and 50 would be your lower control limit.
3. Create a second two-columned chart to list your average data value and control limits. Since these values will be the same for all dates, list only the first and last date in your data series in the left column, once for each value, and the corresponding values in the right column. In our example, assuming your first date is 1/1/09 and your end date is 2/2/10, you would enter '1/1/09' in any cell, '2/2/10' in the cell directly beneath it, and '100' directly to the right of both dates. You would then repeat this procedure twice, directly under the first entries; once for the upper control limit, and once for the lower.
4. Create an XY chart by highlighting the data in your first table and clicking 'Insert -> Charts -> XY Chart'. You will now have a control chart showing the data values at each time interval. In the example, you would have a horizontal XY chart showing the number of products produced each day.
5. Add your data average and control limits by highlighting each of the four-cell blocks in your second table, one at a time, and dragging them over to the chart. Click 'OK' in the Paste Special dialog box that pops up each time, and you will have a complete statistical process control chart. Following the example, you would highlight the start date, end date, and both instances of '100' for the average, then drag the group over the chart. You would then repeat the process for the upper and lower control limits.

Read more ►

How to Link Data to Different Worksheets in Microsoft Excel

1. Launch Microsoft Excel and open each of the source spreadsheets that contain information to link into the target spreadsheet. Open the target spreadsheet last.
2. Click on the cell in the target spreadsheet that is to contain linked information from a source spreadsheet and press the 'Equal' key ('='). Switch to the source spreadsheet and click on the cell that contains the information to be linked and then press 'Enter.' The target spreadsheet now contains a link to the data in the source spreadsheet as indicated in the formula bar. Changes made to the source spreadsheet will be reflected in the target spreadsheet automatically.
3. Click on a cell in the target spreadsheet that is to contain calculated information from cells in multiple source spreadsheets and press the 'Equal' key ('='). Switch to the first source spreadsheet and click on the first cell that contains the information to be linked. Type an operator such as the 'Plus' key (' ') and click on the next cell in the formula, from the same source spreadsheet or a different one, or even from the target spreadsheet if desired. Continue to add operators and cell references until the formula is complete, then press 'Enter' to store the formula and display the results of the calculation.

Read more ►

How to Create a Shortcut for Symbol Use in Excel

1. Make sure your Microsoft Excel security level is set to 'Low' or 'Medium.' On the 'Tools' menu, click 'Options.' In the 'Options' window, choose 'Macro Security' and click the 'Security Level' tab and set it to 'Medium' or 'Low.' If Excel is on 'High' security, then it won't allow your macro to run.
2. On the 'Tools' menu, hover over the 'Macro' option, and select 'Create New Macro.' (If you're using Excel 2007, go to the 'View' tab, click 'Macro,' and select 'Record Macro.'
3. Enter a name for your macro in the space provided. Make it something memorable and ensure that it begins with a letter. Do not make the macro name a cell reference--otherwise, it will create errors when run.
4. Enter a letter for your macro under 'Shortcut Key.' When you run your macro, you'll hold 'Ctrl' and press the key you provide in this box. You cannot use a number for the macro.
5. Choose where you want to store the macro. If you want to be able to use it with any workbook, choose 'Personal Macro Workbook.'
6. Enter a description for the macro. Be as concise as possible.
7. Click 'OK.' Now Excel is ready to record the macro. Carry out the steps needed to create your symbol or functions. Remember, the macro tool can record a number of functions in one round, so you can create complex routines to run with every step of the shortcut key.
8. Now that you're finished recording your Macro, click 'Stop Recording' in the Stop Recording toolbar. If you're using Excel 2007, click on the 'Macro' button in the 'View' ribbon, and select 'Stop Recording' from the drop-down menu.
9. Practice using the shortcut and make sure it works correctly. If it doesn't, go to 'Macros,' click on 'View Macros,' and click 'Edit' to make any necessary changes.

Read more ►

How to Reset Excel 2003 to Print DPI

1. Open the Microsoft Excel 2003 spreadsheet file that you want to reset dpi settings for.
2. Select the 'File' option from the top toolbar menu and then click the 'Page Setup' option. The Page Setup dialog box will then appear.
3. Select the '600 dpi' option in the 'Print quality' section to return to the standard dpi setting.
4. Click the 'OK' button to close out of the Page Setup dialog box and return to your document.
5. Click the 'File' button and then select the 'Save' option to save you changes made to the Excel 2003 spreadsheet.

Read more ►

How to Specify the Templates Folder in Excel 2007

1. Open Microsoft Excel 2007.
2. Click the 'Microsoft Office' button located in the upper left corner of the screen.
3. Select 'Excel Options' from the menu.
4. Click 'Advanced' from the 'Options' menu.
5. Select 'File Locations' from the general section.
6. Select 'User Templates.'
7. Press the 'Modify' button.
8. Browse to the new templates folder location.
9. Press the 'OK' button.
10. Click the 'OK' button again.

Read more ►

How to Use Bullets in Excel 2007

1. Open an Excel 2007 spreadsheet. Insert the cursor in a cell where you want to use bullets.
2. Press 'ALT 0149' to insert a bullet point into the cell. Press the space bar to put a space and then type in the bullet item.
3. Press 'ALT Enter' to put a line break in the same cell and then press 'ALT 0149' to insert another bullet point, or press 'Enter' to submit the text in the current cell and move to the one below it.

Read more ►