Wednesday, September 18, 2013

How to Create a Clustered


1. Click 'Start,' 'All Programs,' 'Microsoft Office' and open 'Microsoft Excel.'
2. Enter your column headings as you would normally into the spreadsheet. Add your row headings, but leave one blank cell for each graph cluster column that you want.
3. Enter the data into the spreadsheet. When entering the data, use a separate row for each cluster that you want to create.
4. Click in the upper-right corner of the spreadsheet and drag the cursor so that all the cells containing data are selected. Click the 'Insert' menu at the top of the window.
5. Click 'Column' and the second option in the '2-D Column' section, which is 'Stacked Column.' Right click one of the columns in the chart, and select 'Format data series.'
6. Use the slider in the 'Gap Width' section and slide it all the way to the left to eliminate the gap between the clusters. Click 'Close.'
Read more ►

How to Use VBA to Delete Columns in Excel 2007


1. Click the 'Developer' tab, click 'Visual Basic' and click the 'Insert' menu. Click 'Module' to insert a new VBA code module.
2. Type the following to create a new sub procedure:Private Sub removeColumns()
3. Type the following to remove column B from the active sheet:Columns('B:B').SelectSelection.Delete Shift:=xlToLeft
4. Edit 'B:B' in the previous step, and type the column letter you want to remove. Type 'End Sub' to end the procedure, and press 'F5' to run the procedure and remove the column.
Read more ►

How to Create a Distribution Chart


1. Open Microsoft Excel.
2. Type the number 3.0 in the A1 cell and then type the number 2.75 in the A2 cell just below it. Highlight both cells.
3. Drag the bottom-right of the selected cells to the 25th row. Use the tiny black square as a handle. Each number will be decremented by .25, giving the successive number below it.
4. Select cell B1and enter the equation as: '=normdist (a1,0,1,0)' and hit the 'Enter' key. The Normal Distribution for the number (3) in cell A1 with a mean of zero and a Standard Deviation of 1 appears in the B1 cell.
5. Click the bottom-right of the B1 cell and drag the cursor to the 25th cell as you did in step 3. The Normal Distribution appears in each corresponding B cell for its 'A' cell number. Keep the cells from B1 to B 25 selected.
6. Click 'Insert' from the main menu and then select 'Line' and choose the first line graph from the 2D sub-selection. The chart for the Normal Distribution appears a bell-shaped line graph.
7. Select 'Column' from the main menu and choose the first column graph in the 2D sub-selection. The chart now is represented as a column graph. Experiment accordingly with each variation and ultimately decide which style best suits your presentation.
Read more ►

Tuesday, September 17, 2013

How to View an Excel 2007 Spreadsheet in Excel 2003


1. Learn how to exchange files between Excel 2007 and Excel 2003. The programs in the Microsoft Office suite have been upgraded to use an open XML file format in the 2007 version in order to make the files smaller, more secure and easier to use with other programs. Microsoft has created a compatibility pack which ensures that in spite of the new file format you can use newer releases with the older programs.
2. Learn to use the compatibility pack. If you have the Office 2003 version of Excel or older, you can use the compatibility pack to view, edit and save an Excel 2007 spreadsheet. You can also convert the 2007 Excel spreadsheet from the open XML format to the older binary file format.
3. Prepare to download the compatibility pack. Make sure you have one of the following operating systems on your computer: Windows 2000 Service Pack 4, Windows Server 2003, Windows Vista, Windows XP Service Pack 1 or Windows XP Service Pack 2. The compatibility pack file requires 25MB on your hard disk, so be sure you have enough space.
4. Install updates. If you have Microsoft Office 2000, Office 2003 and Office XP you will need to install high-priority updates from Microsoft.com before you download the compatibility pack (see Resources below).
5. Download and install the compatibility pack. Visit Microsoft.com to download the compatibility pack (see Resources below). You can save the executable file to your hard disk and run it at a later time or you can run it directly from Microsoft's website.
6. Open your Excel program. Click the 'File' button on the toolbar, select 'Open' and search for your Excel 2007 spreadsheet using the dialog box that pops up. Click the file to select it and then click the 'Open' button to view it in your Excel 2003 program.
Read more ►

How to Monitor Stock Prices in Microsoft Excel


1. Open a blank Microsoft Excel spreadsheet.
2. Click on a cell where you want to show a stock price.
3. Click on 'Data' in the top menu bar.
4. Scroll down to 'Import External Data,' then over to 'New Web Query.'
5. In the window that pops up, type the URL http://finance.yahoo.com in the address.
6. Enter the stock symbol you wish to track. Be sure to double check that you entered the correct stock symbol by checking the company name that shows.
7. Scroll down to 'Last trade:' and click on the arrow to the left. The arrow will change to a check mark. The data highlighted will be shown on your spreadsheet.
8. Choose the data you wish to be in your spreadsheet, then click on the 'Import' button at the bottom of the window. You may choose to add any data with an arrow next to it into your spreadsheet by clicking on the arrow to the left of the data.
9. Verify the cell where you want the data to appear when prompted. You can click on any cell in the spreadsheet if you wish to change the location. Click on 'OK' after choosing the cell.
10. Save the spreadsheet. You can update the stock price(s) any time by clicking on 'Data' in the top menu bar. Then scroll down to 'Refresh Data' and click on it.
11. Know that you can also update the stock prices in the 'External Data' toolbar. Just click on the red exclamation point in that toolbar.
Read more ►

How to Copy Excel Formulas Through Multiple Rows


1. Click the cell that contains the formula you wish to copy with your mouse. Check the cell address bar at the top of your worksheet to ensure that the formula is correct.
2. Click 'Edit,' 'Copy' from the edit menu to select the formula. You may also right-click within the cell, and click 'Copy.'
3. Select the destination cells with your mouse by clicking the first cell and dragging through any contiguous rows to be included. Click 'Paste' from the edit menu to copy the formula and any formatting associated with the formula. You may also 'right-click' your mouse within the destination cell range and click 'Paste.'
Read more ►

How to Make a Pie Chart With Microsoft Excel


1. Open a blank worksheet in Microsoft Excel.
2. Enter all of the data into one column starting with cell 'A1.' Using monthly expenses as an example, type 'groceries' in cell 'A1.' Navigate to the cell below ('A2') and type 'gas.' Continue moving down column 'A' typing 'utilities,' 'daycare,' 'car payment' and 'mortgage' in each cell.
3. Insert the total dollar amount you spend on each item. Move the cursor to cell 'B1.' Type the total monthly cost of gas in the cell. Continue down the 'B' column, inputting the monthly costs for each of your other expenses.
4. Put your cursor in the cell directly under the last dollar amount -- cell 'B7' in our example. Holding down the left mouse button, select the cells in this column above 'B7' all the way up to 'B1.' All of the dollar amounts should now be highlighted.
5. With the column still highlighted, click on the 'AutoSum' button along the top of your Excel worksheet. This will total the dollar amount column, displaying your total monthly expenses.
6. Put your cursor in cell 'A1' and highlight down to 'A6,' and then from 'B1' to 'B6.'
7. While the 'A' and 'B' columns are simultaneously highlighted, select the 'Charts' tab at the top of your worksheet. Then choose 'Pie' from the list of chart types.
8. You can select several types of pie charts. For Microsoft Excel's most basic pie chart, select the first option. Your chart will be instantly created in your worksheet.
9. To add percentages or other labels automatically, double-click on the pie chart. A dialog appears.
10. Click 'labels,' then select the desired type of labels, such as percentages or dollar values. Microsoft Excel will place percentages and names on the pie chart.
Read more ►

How to Create a Line Chart in Excel 2007


1. Enter in headers for all of your data columns. Just type in the name for each data field in the cell directly above the data. If your data doesn't have an available row of cells above it, right-click on the row number that corresponds to the top row of data, and choose 'Insert.' These headers will be the labels for your data on the line chart.
2. Type in the row labels in the column directly to the left of your data. If you don't have any empty cells to enter in information, right-click on the letter at the top of the leftmost data column and select 'Insert.' These labels will appear at the bottom of the line chart to show the progression of your data. If you don't enter these labels, the chart will default to numerical labels, staring at one and counting upward.
3. Select the cell above the row labels and to the left of the column headers. Hold the shift key and select the bottom-right cell in your data field.
4. Click on the 'Insert' tab at the top of the Excel ribbon. Locate the 'Charts' area, and select 'Line.' Choose between the seven different types of line charts. You can choose between a standard line chart, stacked line chart and 100 percent stacked line chart. A stacked line will add all the data fields together, instead of comparing them like a regular line chart. A 100 percent stacked line will add all the items together, and then display each item's percentage of that total. Each of these options is also available with markers along the data lines. You can also choose to create a 3D lines chart. Once you select the line chart type, your line chart appears on the screen.
Read more ►

How to Copy Paste Macro for Excel


1. Open the workbook that has the macro you want to copy.
2. Press the 'Alt' and 'F11' keys together to access the VBE.
3. Click on the name of the macro you want to copy in the left-hand column. This opens up a window with the Visual Basic for Applications (VBA) code.
4. Click on the 'Edit' tab and then click on 'Select All.'
5. Click on 'Ctrl' and 'C' to copy the macro to the Office Clipboard.
6. Open the workbook where you want to copy the macro to.
7. Press the 'Alt' and 'F11' keys together to open the VBE.
8. Click on 'Insert' and then click on 'Module' to open a blank window.
9. Click inside the blank window, then press the 'Ctrl' and 'V' keys to paste the code into the open window. Your macro is now copied and ready for use.
10. Press the 'Alt' and 'F11' keys together to exit the VBE and return to your workbook.
Read more ►

Monday, September 16, 2013

How to Export File Search Results to Excel


1. Click on the 'Windows' button in the lower left-hand corner of your computer screen. Type in the file name or phrase you want to find. The results will appear in a separate window.
2. Press 'Control' and 'A' at the same time. This selects the results. Next, press 'Shift' and right-click your mouse at the same time. This will open a dialog box.
3. Select 'Copy as Path' from the dialog box. Open Microsoft Excel. Press 'Control' and 'V' at the same time to paste your results in the spreadsheet.
4. Try one of several dozen software options, such as Power File Search, Agent Ransack or SysExporter. Many of these have limited, trial versions that will let you test the software before buying it.
Read more ►

How to Remove the Date From Excel When Printing


1. Click the Windows 'Start' button and select 'All Programs.' Click 'Microsoft Office,' then click 'Microsoft Excel' to open the file.
2. Press the 'Ctrl' and 'O' keys to open the 'Open' dialog window. Double-click the Excel file you want to open. The file opens in the spreadsheet software.
3. Click the 'Page Layout' ribbon. In the right side of the ribbon in the 'Headings' section, remove the check mark next to the 'Print' option.
4. Press the 'Ctrl' and 'S' keys to save the settings to the document. Print the document to your printer to create a hard copy without headers.
Read more ►

How to Synchronize Scrolling in Excel


1. Open Microsoft Excel.
2. Open your two worksheets, or split your workbook into multi-view.
3. Click 'View' on the toolbar.
4. Click 'Synchronized Scrolling' on the right.
Read more ►

How to Convert CSV Files to Excel 2007


1. Open Excel 2007.
2. Click the circular Microsoft Office button in the upper left corner. Select Open.
3. Click the 'All files (*.*)' drop-down box and select Text Files.
4. Locate your CSV file and click 'Open.'
5. Click the Delimited radio button if the CSV file's data is separated by commas, semicolons or similar characters. Click the 'Fixed width' radio button if the CSV data is arranged in columns. Click 'Next.'
6. Check the check box indicating the delimiter that separates the values, such as commas, semicolons or tabs. Click 'Next.'
7. Click a column to select it. Click the General, Text or Date radio buttons to select a text type for the column. Repeat for the remaining columns and click 'Finish.'
8. Click the circular Microsoft Office button and select 'Save as.'
9. Click the 'Save as type' drop-down box and select 'Excel workbook (*.xlsx).' Type in a file name and click 'Save.'  
Read more ►

How to Move Information Inside an Excel Spreadsheet


Move Cell Content Inside an Excel Spreadsheet
1. Start Microsoft Excel, and open a spreadsheet that contains data that you would like to move to a different area within the spreadsheet.
2. Select a cell or group of cells that you would like to cut and paste to a new location by clicking and dragging with your mouse over the cell(s).
3. Right-click on top of the selected cell(s) and choose 'Cut' from the shortcut menu to delete the content from the cell(s) and add them to the Excel clipboard.
4. Click in the cell(s) where you would like the cell content that you just copied to the clipboard to be moved.
5. Use your mouse to right-click and select 'Paste' from the shortcut menu to finish moving the information to it's new location within your spreadsheet.
Move a Worksheet Inside an Excel Spreadsheet
6. Locate the 'Sheet' tabs at the bottom of your Excel spreadsheet.
7. Click on the sheet tab that refers to the worksheet you would like to move inside the Excel spreadsheet.
8. Drag the sheet tab either to the right or left and release the mouse button when the sheet tab appears in the desired location.
Read more ►

Sunday, September 15, 2013

How to Disable Automatic Hyperlink in Excel 2007


1. Click the Office button after opening Excel.
2. Select 'Excel Options' from the Office menu. An 'Excel Options' window will open.
3. Click 'Proofing' from the list of options. You can access AutoCorrect here to disable automatic hyperlinks.
4. Click 'AutoCorrect Options' near the top of the window. The 'AutoCorrect' window will open.
5. Click the 'AutoFormat As You Type' tab and clear the checked box next to 'Replace as you type Internet and network paths with hyperlinks.'
6. Click 'OK' to disable automatic hyperlinks and click 'OK' again to close the window. When you type a Web address in Excel 2007, it will no longer change to a hyperlink.
Read more ►

Blogger news