Sunday, April 15, 2012

How to Merge Cells in a Shared Workbook


1. Navigate to the location of the shared workbook on your computer.
2. Double-click on the workbook to open it.
3. Click the top left cell that is going to be part of your cell merge, then drag the mouse pointer until all of the desired cells have been selected.
4. Right-click anywhere in the highlighted area of cells, then select the 'Format Cells' option.
5. Click the 'Alignment' tab, then check the box next to 'Merge cells.'
6. Click the gray 'OK' button to close the window.
Read more ►

How to Write Macros in Excel 2007


1. Make the 'Developer' tab of the ribbon visible. Click the 'Office Button' and select 'Excel Options.' Select 'Show Developer Tab in the Ribbon' in the 'Popular' category. Click 'OK.'
2. Enable macros. Go to the 'Developer' tab. Click 'Macro Security' in the 'Code' group. Select 'Enable All Macros' and click 'OK.'
3. Click 'Visual Basic' in the 'Code' group. The 'Microsoft Visual Basic for Applications' window opens. Go to the 'Insert' menu and select 'Module' to open a new module.
4. Type or paste the macro that you want to use in Excel 2007. Press the 'F5' key to run the macro and ensure that it works correctly.
5. Go to the 'File' menu and select 'Close and Return to Microsoft Excel' when you are finished.
Read more ►

How to Unlock an Excel Password


1. Download an Excel macro designed to unlock passwords.
2. Double-click 'allinternalpasswords.xls' to open the macro. The workbook itself is hidden and the macro displays a toolbar.
3. Click the 'File' menu and click 'Open ...' to open the workbook that you wish to unlock.
4. Click the button on the toolbar to unlock the password. The macro provides you with a password that will work in the locked workbook.
5. Enter the provided password in order to unlock the workbook.
Read more ►

Saturday, April 14, 2012

How to Calculate Correlations Between Three Sets of Data Using Excel


1. Open your Excel file, click the 'File' tab, then click 'Options.'
2. Click 'Add-Ins' at the left side of the window.
3. Click the drop-down menu to the right of 'Manage,' click 'Excel Add-Ins,' then click 'Go.'
4. Check the box to the left of 'Analysis Toolpak,' then click 'OK.'
5. Click the 'Data' tab at the top of the window, then click 'Data Analysis' in the Data Analysis section of the ribbon at the top of the window.
6. Click 'Correlation' and 'OK.' This opens a new Correlation window.
7. Check the 'Columns' option if your data is sorted by columns, or check the 'Rows' option if your data is sorted by rows.
8. Hold down the 'Ctrl' key and use your mouse to highlight each set of data for which you want to calculate correlations.
9. Click the 'OK' button to calculate your correlation and have it displayed on a new worksheet in your workbook.
Read more ►

Friday, April 13, 2012

How to Remove Dashes From Social Security Numbers in Excel


1. Open the Excel worksheet containing the Social Security numbers or other data from which you want to remove the dashes. Select the cells containing the numbers. If you want to remove the dashes from the entire worksheet, click the 'Select All' button, which is the rectangle between 'A' and '1' in the upper left of the worksheet. To select an entire row or column, click on the number or letter pertaining to that row or column. To select several cells in the worksheet, press and hold the 'Ctrl' key while you click on each cell.
2. Click the 'Format' drop-down in the 'Cells' group of the 'Home' tab in Excel 2007 and select 'Format Cells.' In Excel 2003 or earlier, go to the 'Format' menu and click 'Cells.'
3. Go to the 'Number' tab of the 'Format Cells' dialog box. Select 'Number' in the 'Category' box. Change 'Decimal Places' to '0' and click 'OK.'
4. Open the 'Find and Replace' dialog while the cells are still selected. Go to the 'Find Select' drop-down on the 'Home' tab in Excel 2007 and select 'Replace.' In Excel 2003 or earlier, go to the 'Edit' menu and click on 'Replace.' You can also use the keyboard shortcut 'Ctrl H' to open 'Find and Replace.'
5. Type a dash into the 'Find What' box. Leave the 'Replace With' box empty. Click the 'Replace All' button. Excel will find all of the dashes in the selected cells and remove them.
Read more ►

How to Create a Text Box in Excel


1. Open Microsoft Excel 2007 and or start a new blank workbook or open an existing workbook that you want to create a text box in.
2. Select the 'Insert' tab from the top of the Excel 2007 screen to display the Insert ribbon. This ribbon includes all of the objects you can insert into an Excel workbook.
3. Locate the 'Text' section of the Insert ribbon. It is the section at the end or the right of the Insert ribbon.
4. Choose the 'Text Box' button from the Text section of the Insert ribbon. The button will be highlighted once it's clicked.
5. Click in the area in the worksheet where you want to begin drawing the text box. Hold down your left mouse button and drag outwards from that point to create the text box.
6. Release the mouse button once you are happy with the size and shape of the text box. The text box looks like white empty space with a border and sizing handles surrounding it.
7. Click inside the text box to insert the cursor, then type text or add objects inside it.
Read more ►

Thursday, April 12, 2012

Access Denied When Saving From the Excel 2007 Format to Excel 2003


1. Click the Windows 'Start' button and enter the folder location of the Excel file in the search text box. For instance, if the file is located in 'C:\myfolder,' enter this value into the text box and press 'Enter.' This opens an Explorer window that displays the folder contents.
2. Right-click the folder that contains your Excel spreadsheet and select 'Properties.' A window opens that displays several options for your folder.
3. Remove the check mark in the box labeled 'Read-Only.' This allows you to save your converted files to the folder and stops errors such as 'Access Denied.'
Read more ►

How to Change X Axis Values in Excel 2007


1. Click on the x-axis to select it. Click somewhere beneath the bottom horizontal line of the chart to ensure that you select the axis rather than the chart area.
2. Right-click within the selected area and choose 'Format Axis' from the drop-down menu that appears.
3. Click the circle for 'Fixed' next to 'Minimum:' under 'Axis Options' and type the smallest number you want displayed on the x-axis in the box to the right. Do the same for 'Maximum:' but type the largest number you want displayed on the x-axis.
4. Select the 'Fixed' circle next to 'Major unit:' and enter a number in the box to the right if you want to change what numbers are displayed on the x-axis. For example, type '0.1' into the box if you want to display every tenth.
5. Click the 'Close' button at the bottom of the 'Format Axis' box to accept your changes.
Read more ►

How to Make a Graph With a Z


1. Open Microsoft Excel and enter your data into the vertical columns. Highlight your data. Click the 'Insert' tab, then click the 'Chart' button under it.
2. Choose the graph you prefer, making sure that it is in 3-D. Click 'Press and Hold to View Sample' in order to preview the chart you selected before applying it. Click 'Next' once you have found the chart that works best for your purposes.
3. Enter your data range by highlighting the appropriate columns of data and then clicking the button beside the field your selected information appears in. Click the 'Series' tab to edit or modify your data series and their names.
4. Click 'Next,' then click the 'Titles' tab of the next window. Type in a title for your graph and titles for the x, y, and z axes.
5. Click the 'Axes' tab for options to make your axes' information visible or invisible. Click the 'Gridlines' tab to make gridlines visible or invisible. Click the 'Legend' tab to make your legends visible or invisible. You can also use this tab to determine legend placement.
6. Click 'Data Labels' to choose which pieces of series information and titles you want to show out of all you have entered.
7. Click 'Data Table' to view all of the series data you have entered in your graph in table form.
8. Click 'Next.' Select your chart location and placement in the final window that pops up. Click 'Finish.'
Read more ►

How to Use Excel 2010


1. Open a new Excel 2010 spreadsheet by double-clicking on the Excel icon on your desktop. Look at the spreadsheet and its layout. New files have three worksheets. Move between the three spreadsheets by clicking 'Sheet 1,' 'Sheet 2' or 'Sheet 3' in the lower left part of your screen.
2. Examine the tabs along the top -- 'File', 'Home,' 'Insert,' 'Page Layout,' 'Formulas,' 'Data,' 'Review' and 'View.' Click on each one to see its options. Most basic Excel 2010 commands are found on the 'Home' tab.
3. Click the cell in the upper left part of the spreadsheet marked A1, found in column A, row 1. Type the name of your first variable in cell A1 and additional variable names in B1, C1 and so forth.
4. Create your data table by entering data beneath each variable name in the cells A2, A3 and so forth. Use as many cells as necessary to fit your data set. Scroll down using the arrows on the right hand of your screen.
5. Change the formatting of the cells using the commands found on the 'Home' tab. Click on a cell or group of cells and change their font, color, size or appearance.
6. Move cells by highlighting them and hovering over the thick black border until you see a four-headed arrow. Click and drag the cells to your desired location. Copy cells by highlighting them and right-clicking on them. Click 'Copy,' click to where you want to move them, right-click again and click 'Paste.'
7. Perform basic functions by going to the 'Formulas' tab and clicking 'Insert Function.' Search for the name of the function you want to perform, such as adding, averaging or finding the maximum. Input a range of cells to tell the spreadsheet what calculation to perform. Press the 'Enter' key to view the result of the formula.
8. Save your worksheet by clicking the green 'File' tab. Select 'Save As' to designate a destination folder and unique name for your Excel 2010 spreadsheet. Click 'Print' to print a copy of your spreadsheet.
9. Access the Excel 2010 help files by clicking the blue question button in the top right corner of the screen. Type your question into the search box to find an answer from Microsoft.
Read more ►

How to Remove Hyperlinks From Excel 2007


1. Double-click your Excel file to open the file in Microsoft Excel 2007.
2. Select a blank cell in your spreadsheet, then type the number '1' into the cell.
3. Right-click on the cell and select 'Copy.'
4. Hold down the 'Ctrl' key on your keyboard as you select each cell containing a hyperlink that you want to remove.
5. Click the 'Home' tab at the top of the Excel 2007 window.
6. Click the arrow below 'Paste' in the 'Clipboard' section, then click 'Paste Special.'
7. Click 'Multiply' in the 'Operation' section, then click 'OK.' This removes the actual hyperlink from the cell, but it leaves your data underlined.
8. Click 'Cell Styles' in the 'Styles' section at the top of the window.
9. Click 'Normal' under the 'Good, Bad and Neutral' section to remove the underlines.
Read more ►

How to Construct a Histogram Using Excel 2007


1.
Capture the stock data. Go to Yahoo Finance and type 'SBUX' in the symbol box. Click on the link that says 'historical prices'. Go to the bottom of the page and click 'download to spreadsheet'. The info will download automatically and then open in an Excel spreadsheet.
2.
Get the Max and Min values of the 'Adj Close' column. Go to the editing option of the Home menu. In the drop-down box of mathematical functions select 'Max.' Type 'G2:G501' and press 'Enter.' Repeat the same with 'Min.' The Max value should be 28.29 and the Min value should be 7.17.
3.
Create the bin values. Round 28.29 to 30 and 7.17 down to 5. Start at 5 and add increments of 1 until you get to 30. Type those numbers in the column next to 'Adj Close' and call it 'Bin values.'
4.
Open the histogram dialog box. Under the 'Data' tab select 'Data Analysis'. In the dialog box select 'Histogram' and press 'OK.' The Histogram dialog box will then open.
5.
Input the histogram setting. In the 'Input Range' field type '$G$2:$G$501' and in the 'Bin Range' field type '$H$2:$H$25'. These are the cell ranges of the data and bin values respectively. In 'Output Options' select 'New Worksheet Ply' and name the worksheet 'Histogram.' Then select chart output and press 'OK.'
6.
Survey the Histogram. Observe the distributions of the lines. If they bundled in the middle the histogram is said to be 'evenly distributed' or 'bi modal.' This is usually the case with test or survey data. As is sometimes expected with erratic stock data the histogram in this article turned out to be 'saw-toothed.' This kind of distribution might indicate that the stock under inspection has been quite volatile over the past 500 days.
Read more ►

How to Make Sums in Excel 2010


1. Open the Microsoft Excel 2010 spreadsheet that contains the numbers you want to add together.
2. Click on an empty cell in the spreadsheet, where you want the summation to appear. Type the equals sign into the cell to start a formula.
3. Enter 'SUM(' if you want to add together a number of cells. Type in the column letter and row number of the first cell that you want to include. Place a colon after this cell reference if you are adding together a continuous range of cells, then type in the last cell in the range. Place a comma after the first reference if the selected cells are not continuous on the spreadsheet, and enter in the second cell reference. Continue adding commas and cell references until you have entered all of the cells you want to add together. Enter a close parenthesis and press 'Enter.' For example, if you want to add the cells from 'A1' to 'B10,' enter: '=SUM(A1:B10).' If you want to add just 'A1' and 'B10,' enter '=SUM(A1,B10).' If you want to add 'A1' through 'A10,' but include 'B10' as well, enter '=SUM(A1:A10,B10).'
4. Enter 'SUMIF(' if you only want to add together some of the cells, based on the values of adjacent cells. Type in the first cell in the range that you want to check for a given criteria, then place a colon followed by the last cell in the range of cells. Type in a comma, then enter the value that you want to check for in the range of cells. This can be a simple number, a piece of text enclosed in quotes, or a cell reference. Type in another comma, then type in the range of cells that you want to add together if the cells qualify given your condition. So if you entered '2001' as your criteria value, Excel will look through the first range of cells and whenever it finds '2001,' it would add together the corresponding cell in the second range of cells. Type in a close parenthesis and press 'Enter' to complete the formula. For example, if you want to check cells 'A1' through 'A10' for the value '23,' and then add together the corresponding cells in the range of 'B1' through 'B10,' enter: '=SUMIF(A1:A10,23,B1:B10).'
5. Type 'SUMIFS(' into the cell if you want to sum together a range of cells based on multiple criteria. SUMIFS is similar to SUMIF, except the arguments are arranged in a slightly different order. Type the range of cells that you want to sum together into the formula, then enter a comma. Type the range of cells to check for a criteria, followed by a comma and the criteria itself. Enter another comma and continue to add criteria ranges and criteria until you have entered all data. Place a close parenthesis at the end of the formula and press 'Enter.' To add together cells 'A1' through 'A10' whenever '23' appears in column 'B,' and '25' appears in column 'C,' enter: '=SUMIFS(A1:A10,B1:B10,23,C1:C10,25).'
Read more ►

Wednesday, April 11, 2012

How to Link Data in Other Excel 2007 Workbooks


1. Open the Excel 2007 workbook that contains the data for which you want to link to other workbooks.
2. Double-click in the cell on the Excel workbook the contents of which you want to link to other workbooks. This will select all the contents in that cell.
3. Right-click in the selected cell and then click 'Copy.'
4. Open the other Excel 2007 workbook to which you want to link the data you selected from the workbook that you opened in Step 1.
5. Right-click in an empty cell on the workbook, click the 'Home' tab, click the downward pointing arrow below 'Paste' and then click 'Paste Special.'
6. Click the 'Paste Link' button on the Paste Special dialog box that opens and then click 'OK.' Repeat steps 3 to 6 in other workbooks to which you want to link the selected data.
Read more ►

How to Use the Cursor to Highlight Rows in Excel 2003


1. Move the cursor to the row number on the left side of your spreadsheet and click the number to highlight the row.
2. Move the cursor to another row number, hold down the 'Shift' key, and click the row number to highlight all the rows between the first highlighted row and the new row. For example, highlight row 12, move the cursor to row 20 and shift-click the row number to highlight all rows between 12 and 20.
3. Move the cursor to another row, hold down the 'Ctrl' key and click the row number to highlight only individual rows. For example. highlight row 12, move the cursor to row 20 and ctrl-click the row number to highlight rows 12 and 20.
Read more ►

Blogger news