1. Open Excel and click 'File' and 'Open,' browsing to the file of raw data. Highlight the file and click 'Open.'2. Create a blank column next to the first column of raw data you would like to parse. For example, if column 'A' is the first column of raw data, then right-click on the 'B' and select 'Insert.' This will provide you with a new, blank column.3. Type '=RIGHT(A1,LEN(A1)-FIND('*',SUBSTITUTE(A1,' ','*',LEN(A1)-LEN(SUBSTITUTE(A1,' ','')))))' into cell B2 and click 'Enter.' This formula breaks down the line of text in cell A1 in the following manner:'LEN(A1)-LEN(SUBSTITUTE(A1,' ',''))'...
Browse » Home » All posts
Saturday, March 16, 2013
How to Create a Stock Chart in Excel 2007 With Multiple Stocks
1. Open Excel. Create a new document or open a spreadsheet that already contains stock data.2. Enter or arrange the stock price data into columns. Each column should represent the changes in a stock's price. For example, if you are creating a chart of daily activity in the stock market for four specific stocks, use the first four columns, A through D. Label the stock name or ticker symbol at the top of each column in row 1, then place the daily price data in the subsequent rows. Do not skip rows or leave any cells blank.3. Select the entire data set by dragging the mouse from cell A1 to...
How to Add an XML Map to an Excel Spreadsheet
1. Launch Microsoft Excel and open the spreadsheet to which you want to add the XML map.2. In Excel 2003, click the 'Data' tab and put the cursor over 'XML.' On the drop-down, select 'XML Source.' In Excel 2007, click 'Data,' then click 'From Other Sources' in the drop-down, then click 'From XML Data Import.'3. Click on the 'XML Maps' button and then click 'Add.'4. Locate the XML schema you want to map to the spreadsheet and then click 'Open.'5. Click 'OK' to map the XML schema to the spreadshe...
Friday, March 15, 2013
How to Learn MS Excel Macros
Learn How to Record a Macro1. Access the 'Macro' menu from the 'Tools' menu and click on 'Record New Macro.' This will open a dialog box in which you will type a name for the macro. When you click the 'OK' button, the macro will automatically begin to record and a small window with a 'Stop' button will appear.2. Perform the set of functions that you would like to have in your Macro. If you would like to average a set of numbers, add a dollar sign and put a black border around the cell, carry out all of those tasks. The functions and commands you perform will record in the order in which you...
How to Insert a Comment Box in Microsoft Excel
Microsoft Excel 20071. Open your spreadsheet in Microsoft Excel.2. Click on the cell where you want the comment to appear. Click on the 'Review' menu, and then 'New Comment' in the 'Comments' section. You can also right-click on the cell and click 'Insert Comment.' A text box will appear with your user name in it.3. Type your comment. When you are finished, click anywhere in the spreadsheet outside of the text box. The comment box will disappear, but will reappear when you roll the mouse pointer over the cell.4. Click on the cell, and then the 'Edit Comment' button to edit the comment...
How to Embed Flash Game in Excel Spreadsheet
1. Download free flash game online or use one you already have2. Open Excel and go to the top left option button. Choose excel options in the bottom right hand corner.3. Click show developer tab in the ribbon.4. Go to the developers tab and click insert. Choose insert shockwave object.5. Create an area on the spreadsheet as large as the window you are working on.6. Right click the area you just created and choose properties.1. Make the embed movies option 'True'2. Find the file you want on your computer and paste the path into the movie option. (exit properties)7. Click design...
How to Use the Freeze Pane Command to Create Static Headings in Excel 2003
1. Open your Excel worksheet once you have logged 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. Highlight the column to the right of the column to be frozen. Go to 'Window'. Select 'Freeze Panes'. A bold line will appear to the right of the frozen column indicating that those panes are frozen.3. Freeze rows by highlighting the row below the row to be frozen. Repeat Step 3 as needed.4. Freeze both by highlighting the cell to the right of the column and below the row and repeating...
How to Use ActiveCell in Excel Using a VBA
1. Launch Microsoft Office Excel, click the 'Developer' tab, and click 'Visual Basic.'2. Click the 'Insert' menu and click 'Module' to insert a new code module. Type the following code to start a new sub procedure:Private Sub usingActiveCell()3. Copy and paste the following to activate 'Sheet1:'Worksheets('Sheet1').Activate4. Copy and paste the following to add a value to 'A1,' 'A2,' and 'A3:'Range('A1').SelectActiveCell.Value = 3.5Range('A2').SelectActiveCell.Value = 10Range('A3').SelectActiveCell.Value = 205. Copy and paste the following to highlight the cells with data:With ActiveCellRange(Cells(.Row,...
Thursday, March 14, 2013
How to Create a Two X Axis Chart in Excel
1. Create a chart that measures two data series against a set of variables using the Chart Wizard. Click 'Finish' and the chart will appear in your worksheet.2. Click anywhere on the chart. In the top navigational menu, click the 'Format' tab. Click the arrow in the 'Chart Elements' box, and then select the data series you wish to be the secondary plot.3. Click on 'Format Selection' in the 'Current Selection' section of the 'Format' tab.4. Click on the 'Series Options' tab in the pop-up window, and then select 'Secondary Axis.' Click 'Close'. You will see a secondary vertical axis appear...
How to Insert an Excel Drop Down Menu Box Into a Word Document
1. Select the Excel cell with the drop-down box by left-clicking it with your mouse.2. Click 'Ctrl' and 'C' to copy the contents of the box to the clipboard.3. In Microsoft Word, click on the 'Home' tab.4. Click the down arrow beneath 'Paste' at the far left of the ribbon (the toolbar). Select 'Paste Special' from the options list.5. Choose 'Microsoft Office Excel Worksheet Object.' This inserts the object into Wo...
Wednesday, March 13, 2013
How to Insert a Calendar Into an Excel Spreadsheet
1. Download a calendar template from Microsoft Office Online. Search through the available templates and, after you find one you like, click the calendar link and select 'Download.'2. Click 'Accept' to agree to the service agreement and click 'Save' to save the template to your computer.3. Open the Excel workbook that you want to insert a calendar into. Click the 'Insert' button at the top of the page and select 'Object' from the menu that drops down.4. Select 'Create from file' in the Object box that opens and click 'Browse' to search for the calendar template. When you find it, select...
How to Convert XLXS to XLS in Microsoft
Convert With Excel1. Launch Microsoft Excel 2007 or Excel 2010. Open the '.xlxs' file that you want to convert to '.xls'.2. Click 'File' from the main program menu. Select 'Save As' from the drop-down menu.3. Select 'Excel 97-2003 Workbook' from the 'Save As' context menu. This converts and saves the '.xlxs' file to an '.xls' file.Convert With Office Compatibility Pack4. Launch your Web browser. Navigate to the Microsoft Office Compatibility Pack download page.5. Click the 'Download' link toward the top of the page. Select 'Save' from the on-screen download prompt to save the 'FileFormatConverters'...
How to Add Hours in Microsoft Excel
1. Create or open an Excel workbook that has hours that need to be totaled. The hours are formatted as h:mm.2. In the example at the left, the sum of the column results in an inaccurate number since Excel ignores hours that exceed 24. By reformatting the cell that holds the sum formula, we can correct this situation.3. Right click in the cell that holds the sum formula, and then click on Format Cells.4. On the Format Cells dialog box, click on the Number tab if it's not already displaying.5. Edit the custom setting, placing brackets around the 'h' as: [h]:mm .6. Click OK and you...
How to Hide Password Protect a Sheet Within Excel
1. Open the Excel worksheet you want to password protect and hide.2. Place your cursor on the worksheet tab at the bottom of your spreadsheet.3. Right click over the tab and choose 'Protect Sheet' from the pop-up menu. The “Protect Sheet” dialog box will open.4. Add a check mark to 'Protect worksheet and contents of locked cells.' Insert the password you want to use into the 'Password to unprotect sheet' text box.5. Choose the options you want to allow and press 'OK.' Re-enter your password in the “Confirm Password” dialog box and press “OK.”6. Right click on the worksheet tab again...
How to Use Excel to Find Duplicates in a Long List
1. Open Microsoft Excel and the file that contains your list with duplicates.2. Highlight the entire column (or columns) that contain the list(s) you want to examine for duplicates by clicking on the column letter(s) ('A,' 'B,' etc.) at the top of your data.3. Click the 'Conditional Formatting' command in the 'Styles' group under the 'Home' tab, choose 'Highlight Cells Rules' and then choose 'Duplicate Values.'4. Choose the color scheme you want the duplicate values to be highlighted with using the drop-down menu on the right of the 'Duplicate Values' dialog box that opens, or just click...
Subscribe to:
Posts (Atom)