How to Merge Data From Several Excel Workbooks

1. Click the Microsoft Office button on the screen's top-left corner, and select 'Excel options.'
2. Under 'Customize,' click 'All Commands' in the 'Choose commands from' list.
3. Check the box next to 'Compare and Merge Workbooks.' Click 'Add,' and then click 'OK.' The 'Compare and Merge Workbooks' icon will now appear in the quick access bar at the top of the window,
4. Move all workbooks to a single folder. If they share a name, rename them as necessary.
5. Open one workbook. Click the 'Compare and Merge Workbooks' icon.
6. Select the other workbooks whose date you want to merge. To select multiple workbooks, press the 'control' or 'shift' keys as you select them.
7. Click 'OK.'

Read more ►

How to Calculate Negative Times in Excel

1. Open your Excel 2010 worksheet. Click on an empty cell anywhere on the worksheet.
2. Type '-'1:30'' into the cell. The number '-.0625' should appear. Right-click the cell and choose 'Format Cells.' Choose 'Time' from the list on the left and click 'OK.' The number changes to '#####.'
3. Click on the 'File' tab at the top of the screen. Choose 'Options' from the list that appears.
4. Click 'Advanced' on the list on the left side of the Options window. Scroll down to the bottom of the 'Advanced' screen and look for the 'When calculating this workbook' heading. Click the box next to 'Use 1904 date system.'
5. Click 'OK' to close the Options window. The time that you entered now reads '-1:30.' You can perform normal mathematical operations on the number and it will behave exactly like a negative number would.

Read more ►

How to Make an Exponent on Excel

Insert Exponent into Formula
1. Open the Microsoft Excel spreadsheet that contains the formula where you want to add an exponent.
2. Click on the cell that contains your formula. The formula will appear in the bar above the Excel spreadsheet. Click on this bar and place your cursor where you want to add the exponential number.
3. Enter the following into the formula bar, without quotes: 'power(x,y)' where 'x' is the desired number and 'y' is the exponent. If needed, you can wrap the entire 'power' function in parentheses. Additionally, you can write this in shorthand as 'x^y', without quotes.
Have Numbers Display as Exponents
4. Open your Microsoft Excel 2010 spreadsheet where you want to make your numbers display as exponents.
5. Click the top-left cell of the area that you want to change, then hold shift and select the bottom-right cell of that area. If you want to change the entire spreadsheet, you can just click the small box that sits above the '1' and to the left of the 'A' in the top-left corner of the spreadsheet.
6. Right-click anywhere on the selected cells. Choose 'Format Cells' from the pop-up menu.
7. Click on 'Scientific' from the list on the left side of the Format Cells window. On the right side of the window, click the up and down arrows to determine how many decimal places you want to display, then click 'OK.' Excel will now display numbers in those cells like '1.25E 02,' which is the same as '1.25^2' or '125.'

Read more ►

How to Open a Password Protected Microsoft Office Excel Sheet

1. Browse to the location where the password-protected Excel document is saved using Windows Explorer.
2. Double-click the document you wish to open. Excel will launch and display a dialog box explaining that the document is protected and requires a password.
3. Enter the password in the space provided, and hit 'OK.' This will open the document and allow you to view and edit the contents.

Read more ►

How to Do an Excel Chart With Multiple Legends

1. Open your Microsoft Excel spreadsheet that already has your data entered into the cells.
2. Click on cell A1, followed by 'Control' and 'A.' This is a shortcut that highlights your complete set of data.
3. Click 'Insert' on your toolbar, followed by 'Bar,' 'Pie' or another form of chart that you like. The chart will appear in your spreadsheet.
4. Click on the chart. Click 'Layout' and then the Legend icon. Click the button that specifies the your legend's orientation such as 'Show Legend at Right.' The legend will appear in your chart.
5. Click on the legend table. Click 'Control' and 'C,' followed by 'Control' and 'V.' A second legend will appear in your graph.

Read more ►

How to Make a Graph From CSV

1. Launch Excel 2010, click 'File' in the top menu and select 'Open.' Click on the file types drop-down menu at the bottom right of the File Open dialog box. You will need to select either 'All files (*.*)' or 'Text files (*.prn, *.txt, *.csv)' because by default, Excel filters your file choices to show only its native file types. Navigate to your file and click 'Open.'
2. Find an empty spot in your spreadsheet and click a cell. The exact location is not important, as you are selecting a place to put your chart while you work on it.
3. Click 'Insert' in the top menu and select any one of the chart types you feel will best represent your data. For instance, click 'Line' then select '2D line.' A blank chart area appears in your spreadsheet.
4. Right click in the blank chart area and click 'Select data.' When the 'Select Data Source' window appears, use you mouse to click and drag over the cells that contain the data you want in the graph. When you release the mouse, you will see the chart automatically updated with your data.
5. Modify the data as required for your purposes. You can modify the text around the X and Y axes by double-clicking on them and modifying the text in the dialog boxes.
6. Save your file when your modifications are complete. If you wish to use this chart in a presentation, or include it in a document, you can copy it to that document: clicking on your chart and press 'Ctrl' plus the letter 'C' to copy it, then switch to the other document and press 'Ctrl' plus the letter 'V' to paste it into the document. You will need to make sure that the other application is capable of displaying the graph or chart, but Microsoft's other major applications, such as Microsoft Word and PowerPoint, can do this.

Read more ►

How to Make a Watermark in Excel

1. Open the Excel workbook in which you want to create a watermark.
2. Click View on the Excel toolbar, or use the keyboard shortcut 'Alt' plus 'V.'
3. Click the View menu and select Header and Footer.
4. Choose Custom Header in the Page Setup dialog box that appears. Click the 'Center Section' so your cursor is active in the corresponding field.
5. Click the 'Insert Picture' button on the right; this button has a picture of mountains and the sun. Navigate to the picture that you wish to use as a watermark and double-click the graphic to insert it.
6. Click the 'Format Picture' button on the far right of the Header dialog box. Resize the picture to fit your page using the options on the Size tab.
7. Click the Picture tab. Under Image Control in the drop-down menu next to 'Color' choose Washout. This will give your graphic a watermark appearance.
8. Click 'OK' on the Format Picture dialog, and again on the Header and Page Setup dialogs.

Read more ►

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 ►