How to Round Up in Excel 2007

1. Open the spreadsheet that contains the data that you want to round up.
2. Right-click the column heading of the column directly to the right of the column that contains the data that you want to round.
3. Click 'Insert' in the pop-up menu to insert a new column to the right of the data column. You'll use this column to store the rounded version of your original data.
4. Click the cell that is directly to the right of the first value that you want to round up.
5. Type the following function in the cell: =ROUNDUP(B1,1)Substitute the address of the first to-be-rounded data cell in place of 'B1' in the above example. The integer after the comma represents the number of decimal points to which you want to round. In the example, the value 3.60 in cell B1 would be rounded to 3.6, while the value 3.65 in cell B1 would be rounded to 3.7.
6. Right-click the cell that contains your roundup function and click 'Copy' in the pop-up menu.
7. Highlight all cells in your function column that are adjacent to the to-be-rounded data. Right-click the highlighted area and click 'Paste' in the pop-up menu. The rounded values will appear in the pasted cells.

Read more ►

How to Merge Cells With Different Formulas in Excel

Concatenate Function
1. Open the Excel file in which you want merge cells.
2. Right-click on the column header directly to the right of the columns to be merged. For example, if the A2 contains 'First name,' B2 contains 'Middle name,' and C2 contains 'Last name,' right-click on the header for Column D.
3. Select 'Insert' from the menu that opens. This will move all columns to the right and will insert a blank column that will become the new Column D.
4. Click in cell D1 and type 'Full name.'
5. Click in cell D2 and type the following formula:=CONCATENATE(A2,' ',B2,' ',C2)This formula will return the combination of cells A2, B2, and C2 with a single space in-between each cell's contents. For example: If A2 contains 'Mickey,' B2 contains 'M' and C2 contains 'Mouse,' then the formula will return 'Mickey M Mouse.'If you do not need spaces between the cells, change the formula to the following:=CONCATENATE(A2,B2,C2)The formula will return 'MickeyMMouse.'
6. Copy and paste the formula down all rows you want to merge.
Function
7. Complete Steps 1 through 4 from Section 1.
8. Click in cell D2 and type the following formula:=SUM(A2' 'B2' 'C2)This will return 'Mickey M Mouse' in cell D2.
9. Copy and paste the formula to all rows in which the data needs merged.

Read more ►

How to Import Data From MS Word to MS Excel

1. Open the Microsoft Word document. Click the 'File' tab in Word 2010, 'Microsoft Office Button' in Word 2007 or 'File' menu in Word 2003. Click 'Save as.' In the 'Save as Type' menu, select 'Plain Text,' then click 'Save. This will save the Word document as a text file that Excel can read. It will remove any formatting you added in Word.
2. Open a blank worksheet in Microsoft Excel.
3. Click 'File' or the 'Microsoft Office Button, then 'Open.' Select 'Text Files' from the file type list. Select the text file you just saved and click Open.' Excel will automatically launch the Import Text Wizard.
4. Choose the original data type. Select 'Delimited' if each piece of data in your text file is separated by tabs, spaces, commas or other characters. Choose 'Fixed Width' if each field of data is the same width. Click 'Next.'
5. Choose which delimiter is used by your data. This is the character that separates values of data in the text file. For instance, if your data is separated by tabs, select 'Tab.' This will determine how Excel will separate the data into cells. See how the text is affected in the preview at the bottom. Click 'Next.'
6. Select the column data format you want Excel to use for each column that appears in the Data Preview. If a column contains a mix of numbers and letters, Excel will convert the column as 'General.'
7. Click 'Finish' to import the text.
8. Click 'File' or the 'Microsoft Office Button,' then 'Save as' to save the Excel file.

Read more ►

How to Use a ListView Control

1. Open Microsoft Excel.
2. Click 'Tools,' 'Macro' and then 'Visual Basic.' This will open the VBA editor.
3. Click 'Insert' and 'UserForm.' This will place a UserForm on your screen, including the VB ToolBox that contains the controls to use on the UserForm.
4. Add the ListView object onto the ToolBox by clicking 'Tool' and 'Addition Controls.' This opens the ActiveX control box. Scroll down and select 'Microsoft Listview Control' and click 'OK.' This will add the ListView object to the ToolBox.
5. Select the 'ListView' from the ToolBox and draw it onto your UserForm.
6. Double-click on the UserForm. This will open the UserForm's code window.
7. Highlight and select all the content within the Form's code window and press 'Delete' on your keyboard.
8. Type or copy and paste the following code in the code module:Private Sub UserForm_Activate()With ListView1.ListItems.Add.Text = 'Item'.Add.Text = 'Item'.Add.Text = 'Item'.Add.Text = 'Item'.Add.Text = 'Item'.Add.Text = 'Item'.Add.Text = 'Item'.Add.Text = 'Item'End WithEnd SubThe code adds a ListView object onto a UserForm and then uses the ListView's listitems method to populate it with several items. The code is inserted in the UserForm's activate event procedure so the code populates the ListView object instantly at the running of the program.
9. Press 'F5' on your keyboard to run your program; you should have a UserForm on your screen. You should see your ListView object populated with a number of items.

Read more ►

How to Use the BINOMDIST Function in Microsoft Excel

1. Start Microsoft Excel. You can do this by clicking on Start, highlighting Programs, and clicking Microsoft Excel, or double-clicking the Microsoft Excel icon on your computer's desktop.
2. We just need one cell for this function, so we will use cell A1. Select cell A1. At the top of the screen, click on Insert, and click on Function. Type BINOMDIST in the Search for a function dialog box, click Go, and click OK. Note that you do not want to choose NEGBINOMDIST since it is a separate function. It is the negative binomial distribution, which deals with the probability of a number of failures before a success.As a second possibility, you could select Statistical from the Or select a category drop-down menu, highlight BINOMDIST, and click OK.
3. A dialog box appears, asking you to input the function arguments. Arguments are values used to perform a calculation. The first is Number_s, which is the number of successes in trails. Type 5 here. The second is Trials, which is the number of independent trials. Input 10 here. The third is Probability_s, the probability of a 'success' in each trial. Here, it would be the probability of getting a head, which is .5. The last argument is Cumulative, and you enter TRUE or FALSE here. Since we are interested in the chances of obtaining EXACTLY five heads, enter FALSE. Entering TRUE would give the probability of getting AT MOST five heads.
4. Click OK. The answer, rounded to four decimal places, is .2461. Again, this is the probability of getting EXACTLY five heads on ten flips of a balanced coin.

Read more ►

How to Improve the Print Resolution of Excel Charts

File Prep
1. Finish drawing up your Microsoft Excel chart as you would normally.
2. Expand the size of the chart if necessary by grabbing a corner of the image with your mouse and dragging it outward.
3. Position the chart on the page exactly as you want it to look when it is printed. Confirm that the chart is positioned inside of the printable page by choosing 'Print Preview' from your menu.
Acrobat Method
4. Go to your Acrobat tab in Excel. Select 'Preferences' and then 'Advanced Settings' to check the resolution. If you are using Adobe's online PDF creation tool, called 'CreatePDF,' go to 'Set Options' and then 'Document Options' under your preferences. Select 'Print' as your desired profile. Click 'Edit Settings' and then 'Edit' once more to make changes to the resolution.
5. Assure that the resolution is set to at least 300 dpi (600 if you want a high-quality print).
6. Convert the file to PDF. Print your Excel chart from the PDF file to get a high-resolution print.
Photoshop Method
7. Install a postscript printer driver to your computer (see direct link in the Resources section).
8. Go to 'Print' on your Excel menu and find the postscript printer, which will likely be named 'General Postscript' or similar.
9. Click 'Properties' to the right of the printer name in your dialog box then 'Advanced.' Scroll down to 'Document Options' and click the plus sign beside 'PostScript Options.' Under 'PostScript Output Option' change the selection to 'Encapsulated PostScript (EPS).'
10. Press 'OK' to go back to the main print dialog box and select 'Print.' Save the file in a name that you will remember with the extension '.eps' at the end. (To find the file you may have to do a search of your computer files and folders.)
11. Open the EPS file in Adobe Photoshop. You will be prompted to enter the desired resolution before the file is opened. Set it to a minimum of 300 dpi and then open the file.
12. Save the file as either a PDF or TIF file in CMYK format (go to 'Image' and then 'Mode,' and choose 'CMYK Color' on the Photoshop menu). Print the Excel chart from Photoshop or another image-editing program.

Read more ►

How to Sort a Row or Column in Excel 2003

1. Open your Excel worksheet after logging on to your computer. You should be able to locate the program by using your 'Start' button. When you cannot find it there, use your 'Search' or 'Find' function.
2. Click on any cell within the range of cells you would like to sort. Go to 'Data'. Select 'Sort' from the appearing menu.
3. Choose 'Options'.
4. Select 'Sort Left to Right' from the displayed 'Orientation' menu. Choose 'OK'.
5. Select the rows to sort from the 'Sort By' and 'Then By' boxes. Sort one column without affecting others by clicking the column heading of the column to sort.
6. Choose 'Sort Ascending' or 'Sort Descending'. A 'Sort Warning' will be displayed.
7. Select 'Continue with the Current Selection'. Then click 'Sort'.

Read more ►

How to Disable a Hyperlink Warning in Excel 2003

1. Click 'Start.' Select 'Programs' or 'All Programs' and click 'Run' in the 'Accessories' folder. The 'Run' pop-up dialog window will open.
2. Type 'regedit' in the 'Run' dialog box and click 'OK.' The 'Registry Editor' will open.
3. Navigate to 'Hkey_Current_User >Software >Microsoft >Office >11.0 >Common.' Click 'Edit' at the top of of the 'Registry Editor' window and select 'New,' then 'Key.'
4. Type 'Security' in the 'New Folder' field and press 'Enter.' Click 'Edit' at the top of of the 'Registry Editor' window and select 'New,' then 'DWORD Value.'
5. Type 'DisableHyperlinkWarning' in the 'DWORD' name field. Right-click 'DisableHyperlinkWarning' and click 'Modify' from the pop-up menu. Select the 'Decimal' radio button in the 'Edit DWORD Value' dialog box
6. Type '1' in the data value field. Click 'OK.'

Read more ►

How to Calculate PMT in Excel

1. Open Excel 2010, and click the 'fx' button on the formula bar. Type 'PMT' to locate the PMT function. Press 'Enter' on the keyboard. Click the 'PMT' function.
2. Add the variables to the PMT function. Enter the current interest rate for the loan in the 'Rate' section. Enter the number of payments in the 'Nper' section.
3. Enter the present value of the loan in the 'PV' section. Click 'Ok' to see the loan payment appear in the Excel worksheet.

Read more ►

How to Set a Default Format for Excel Charts

1. Open Microsoft Excel by opening the 'Start' menu, clicking 'All Programs,' opening the 'Microsoft Office' folder and then clicking on the Microsoft Excel icon.
2. Click the tab labeled 'Insert' at the top of the Microsoft Excel window.
3. Click the 'Create Chart' button on the bottom-right of the 'Charts' section on the Insert tab, below 'Other Charts.'
4. Select the chart type that you would like to use from the preset templates.
5. Click 'Set As Default Chart' to set the selected chart as the default format of all created charts. Click 'OK' to save your changes and to exit the window.

Read more ►

How to Use Excel for Project Timelines

1. Open the Excel worksheet.
2. Click the 'Insert' tab on the command ribbon.
3. Click the 'SmartArt' button in the 'Illustrations' group. A dialog box with a list of commands and timeline diagrams appears.
4. Click 'Process' in the list.
5. Click the preferred timeline diagram.
6. Click 'OK.' A 'SmartArt Tools' ribbon appears. The timeline template appears on the worksheet.
7. Type the text in the '[Text]' area. A text pane appears to the side. If the text pane does not display, click the left-arrow control on the side of the timeline to open. The text box can display dates, events or other project news for your timeline.
8. Click the 'Design' or 'Format' tabs on the ribbon for more options, such as changing the layout or color. For example, the timeline can appear at an angle when you click the 'Bird's Eye Scene' button in the 'SmartArt Styles' group in the 'Design' tab.
9. Move or re-size the template by pulling on the frame handles.

Read more ►

How to Record a Macro in Excel 2003

1. Open Excel. Either open the program itself or open an existing file.
2. Set your security level. Go to 'Tools > Options.' Go to the Security tab and click on 'Macro Security.' Go to the Security Level tab and change the level to medium or low.
3. Go to 'Tools > Macro.' Choose 'Record New Macro' from the options.
4. Type a name into the macro name box.
5. Type the location of the macro into the box labeled 'Store Macros In.' Click 'OK.'
6. You are ready to begin recording your macro. Perform the series of tasks you would like to create the macro for.
7. When you are finished, click 'Stop Recording' on the toolbar. You may make as many macros as you need.

Read more ►

How to Turn a Picture into a background or watermark

1. Open the MS Word program.
2. Click the 'Page Layout' tab on the command ribbon.
3. Click the 'Custom Watermark' option. A 'Printed Watermark' dialog box opens.
4. Select the 'Picture watermark' option. The 'Select Picture' button becomes accessible.
5. Select the 'Picture watermark' option. The 'Select Picture' button becomes accessible.
6. Click the 'Select Picture' button. The picture library file opens.
7. Select the picture file.
8. Click 'Insert.'
9. Select the scale value. For example, 50 percent.
10. Select the check box for 'Washout' if you prefer a very faint watermark effect.
11. Click 'OK.' The picture file becomes a watermark on the Word document.

Read more ►

How To Freeze All Top Cells In Excel 2003

1. Open your Excel spreadsheet. You can open a spreadsheet by double-clicking the XLS file on your computer, or you can open in the 'Open' dialog box when you click the 'Open' icon at the top of your window.
2. Click the row letter on the left of the spreadsheet to highlight the row you want to freeze. To highlight more than one row, hold the 'Ctrl' key and click each row letter with your mouse.
3. Click the 'Window' menu item. Click 'Freeze Panes' to freeze the rows. Scroll down the spreadsheet. Notice the rows you froze are always displayed at the top of your workspace.

Read more ►

How to Compare Multiple Groups of Cells in Excel

1. Open the Excel 2010 spreadsheet that holds the cell groups you want to compare.
2. Decide which group of cells will be the main group, which all other groups will be compared against.
3. Click the top left cell in the second group of cells. Hold the 'Shift' key and click the bottom right cell from that group. This will highlight the entire group of cells.
4. Click the 'Home' tab at the top of the screen, then click the 'Conditional Formatting' button and choose 'New Rule' from the drop-down menu.
5. Click 'Use a formula to determine which cells to format' at the top of the New Formatting Rule window. Enter the following into the 'Format values...' box:=not(a1=d1)Change 'a1' to the top left cell in your master group of cells and change 'd1' to the top left cell in the current selection. Click the 'Format' button in the lower right corner of the window, choose the 'Fill' tab and select a color that you want to use to highlight the differences in the groups. Click 'OK' when you are done. The differences in the second group will now appear highlighted.
6. Repeat this process for every group of cells that you want to compare against the original group.

Read more ►