How to Use the CORREL 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. Before we work with data, it's important to understand an important fact about the correlation coefficient, the value that represents the strength of the relationship between two random variables. The correlation coefficient ranges from -1 to 1, with -1 indicating a perfect negative correlation and 1 indicating a perfect positive correlation.
3. Enter the data. I'll use cells A1 to A25 and B1 to B25 for this purpose. The A column represents each student's homework average and the B column refers to each student's exam average.
4. Enter the following values, starting with cell A1 and ending with cell A25: 89, 92, 88, 76, 90, 68, 100, 87, 93, 77, 81, 80, 94, 90, 83, 79, 73, 83, 91, 84, 88, 90, 93, 80, 91.
5. Now enter the following values, starting with cell B1 and ending with cell B25: 76, 83, 80, 84, 81, 90, 92, 78, 70, 93, 82, 90, 98, 75, 67, 72, 90, 82, 77, 81, 87, 63, 92, 71, 82.
6. Now we can calculate the correlation coefficient. In cell A27, type =CORREL(A1:A25, B1:B25). A1 to A25 and B1 to B25 is where the data appears. Hit ENTER.
7. The result is -.07965, indicating a very slight weak relationship between the two variables.

Read more ►

How to Multiply Using Excel 2007

1. Determine the cell references that will be multiplied together. The cell reference refers to the labels on the columns and rows where the cell is located and will include a letter and a number.
2. Choose the cell where the multiplication results will be returned by clicking within that cell.
3. Click in the Formula Bar (denoted by 'Fx' at the top of the screen). Type = , then the cell reference of the first cell, * and finally the cell reference of the second cell. A correct formula will appear as: =G10*X10, according to Home Learn.
4. Press 'Enter' on the keyboard to multiply the cells together. The correct answer will now appear in the cell.

Read more ►

How to Center Numbers in a Cell in Excel

1. Select the cell with the numbers you want to center.
2. Click the 'Home' tab.
3. Click the 'Center' button, located in the Alignment section.

Read more ►

How to Remove Duplicate Rows From an Excel Spreadsheet

1.
Click on the 'File' menu and then the 'Open' option to open the spreadsheet with the duplicate rows. The example highlights the duplicate rows for easier viewing.
2. Select the entire spreadsheet by clicking on the top left cell of the column header and then scrolling to the last row. Press the 'Shift' key and click on the lowest right cell with information. The rows are highlighted.
3.
Click on the 'Data' menu, 'Filter' and then 'Advanced Filter.' The Advanced Filter dialog box appears.
4. Choose 'Filter the List, In Place' and 'Unique Records Only.' Then click the 'OK' button. Excel hides the duplicated records.
5. With the filtered records still selected, press the 'Ctrl' and 'C' keys to copy the rows. The list is highlighted with bounding outlines.
6.
Click on the 'View' menu, 'Toolbars' and then 'Clipboard.' The Clipboard window appears with your copied data in the first icon.
7. Click on the 'Data' menu, 'Filter' and then 'Show All' to display the original list.
8. With the original list still selected, press the 'Delete' key. The list is deleted.
9.
Click the first icon in the 'Clipboard' window. The list appears with no duplicates. The example highlights the filtered rows for easy viewing.
10. Click the 'Save' button to save the spreadsheet.

Read more ►

How to Install Speech in Office Excel

1. Click the Windows 'Start' menu. Click 'Control Panel' and select 'Add or Remove Programs.' The 'Add or Remove Programs' screen will appear.
2. Click on 'Microsoft Office' and click the 'Change' button. Select 'Add or Remove Features' and click 'Next.' Double-click 'Office Shared Features.'
3. Double-click 'Alternative User Input' and select 'Speech.' Click the down arrow next to 'Speech' and select 'Run from my computer.' Elect to update Microsoft Office. The installation of speech recognition is complete after the update finishes.
4. Open Microsoft Excel. Click the 'Tools' menu button. Click 'Speech' to begin speech recognition.

Read more ►

How to Calculate the Percentage Change in Two Numbers in Excel

1. Find the beginning number and the ending number. For example, assume a business has income of $700 in 2009 and $1,300 in 2010.
2. Type the beginning number in cell A1 of your Microsoft Excel page. In the example, type '700' in cell A1.
3. Type the ending number in cell A2. In the example, type '1,300' in cell A2.
4. Type '=((A2-A1)/A1)' in cell A3. This calculates the percent change. In the example, your results should be 0.857. You can convert this to a percentage by formatting the number as a percentage using the tool bar.

Read more ►

How to Learn Descriptive Statistics Using MS Excel 2007

1. Define variability in data using the range, variance and standard deviation functions. In MS Excel 2007, use the Data Analysis Tool under the Data Tab in the top navigation bar. If the option is not available, install the Excel Analysis Toolpack using the MS Office Button. First select Excel Options, then Add-ins, then select Analysis Took Pac, click Go, then OK. Once installed, select the Data tab, then Data Analysis, then Descriptive Statistics; click on the input field and highlight the data. Then check the summary statistics checkbox and click OK.
2. Construct a histogram for analysis of frequency. Open a spreadsheet and enter a set of data values in a column. Select the charting function under the Insert tab and select histogram charts, then click the input field and highlight the data in your column using your mouse. Click OK.
3. Test kurtosis, skewness and distribution using the charting feature. Enter the data in rows or columns, then select Bar or Scatter Plots under the Insert Chart tab to reveal the graphical representation of data dispersion. For kurtosis, select the Formulas tab, then More Functions, then scroll down until you see KURT. Click the input field, highlight your data and select OK.
4. Utilize the stem-leaf and box-plot functions to perform projections (explanatory data analysis). The stem-leaf and box plots are available under charting using the histogram option.
5. Invest in other statistical analysis software to perform more advanced analysis. Several personal and commercial brands exist in the market. MINITAB is a popular personal tool that can be utilized with Excel for point analysis, and SPSS is available from IBM for a more comprehensive package of tools.

Read more ►

How to View the Contents of a Cell in Excel 2003

1. Double-click the Excel file that you want to work with to open it in Excel 2003.
2. Click on the cell that you want to see the contents of. If the cell is hidden behind a window, you can also navigate to it by clicking any cell and then using the arrow keys to move the selection to your desired cell. Finally, you can also click on the box just above the 'A' column and type in the column and row of the desired cell.
3. Look at the formula bar, which starts just above the 'C' column, to see the contents of the cell.

Read more ►

How to Make a Number Line in Excel

1. Open the Excel spreadsheet that needs to have a number line. Click the 'Insert' tab at the top of the window.
2. Click the 'Shapes' button inside the 'Illustrations' heading. Select the image of a line with an arrow at either end inside the 'Lines' heading.
3. Click the bottom line of the cell that needs to be the far left end of the number line. Drag across to the last cell that needs to the far right end of the number line. For example, click the bottom line of 'D6' and drag across to the end of the 'N6' cell to make a number line that spans from the 'D' column to the 'N' column of row 6.
4. Click the second cell in the range, such as 'E6.' Select the 'Home' tab and click the 'Borders' icon inside the 'Font' heading. Click the 'Left Border' button to add the first vertical line on the number line.
5. Repeat the process with each other cell in the range that needs to have a vertical line to designate an entry on the number line.
6. Click one of the cells below the horizontal number line that needs to have a number inserted. For example, click 'E7' if you want the first number to appear in row 7.
7. Navigate back to the 'Insert' tab and click the 'Text Box' button in the 'Text' heading. Click the cell again and drag until the text box is the size you want for the first number. Type the number in the box and then select the font size in the 'Font' drop-down menu.
8. Click the left edge of the text box and drag the number to a different location if it doesn't appear exactly where you want it on the number line. Repeat the process of creating text boxes for any other numbers that need to appear on the line.

Read more ►

How to Make Excel Accept 20 Digit Numbers

1. Right-click on the cell in which you want the 20-digit number to appear. Use shift or control to select multiple cells first, if multiple cells will be accepting 20-digit numbers.
2. Click on 'Format Cells...'
3. Choose 'Text' and click 'OK' to exit.
4. Paste or type the 20-digit number into the cell. Every digit will remain unchanged.

Read more ►

How to Copy Formulas Without Changing Cell References in Excel

1. Click the cell containing the formula that you would like to copy, then use the mouse pointer to highlight all of the text in the formula bar (the bar with 'fx' on the left side) at the top of the screen. Alternatively, press the 'F2' key to display the formula in the cell itself, then highlight it with the mouse pointer.
2. Press the 'Ctrl' and 'C' keys simultaneously to highlight the copied text.
3. Press the 'Escape' key to take Excel out of the formula editing mode.
4. Click the cell that you would like to paste the formula into.
5. Press the 'Ctrl' and 'V' keys simultaneously to paste the formula into the selected cell. Repeat steps 4 and 5 for each cell that you would like to paste the formula into.

Read more ►

How to Calculate Compound Interest in Excel

1. Create a new blank Excel document by clicking on the 'File' menu and selecting 'New.'
2. Enter a column heading corresponding to the following values in the first row of your document starting in column A.Column - Heading
A - Amount Invested
B - Annual Percentage Rate
C - # Times Compounded Annually
D - # of Years
F - Future Value
3. Enter the desired values for calculation in row 2 starting in column A. Example values are listed below for each column. This represents $1,000 compounded quarterly at a rate of 2.25% for 10 years.A - 1000
B - .0225
C - 4
D - 10
4. Enter the following function in row 2 of column F.=A2*((1 B2/C2)^(C2*D2))The result of this formula should be approximately 1251.53.
5. Apply this formula to more rows by clicking on it and dragging your mouse down the desired number of rows. When you have highlighted enough rows in one column hit the Control (Ctrl) and D keys at the same time to 'Fill Down' the formula.

Read more ►

How to Enter Formulas in Excel to Show Not Less Than Zero

Hide Negative Values With Conditional Formatting
1. Select the cell(s) in your Excel spreadsheet you wish to format.
2. Click “Conditional Formatting” under the “Format” menu.
3. Select “Cell value is” from the first drop-down menu for Condition 1.
4. Select “less than or equal to” from the second drop-down menu.
5. Enter 0 in the next box.
6. Click on the Format button and select white in the color drop-down menu. If the background color of your cells is something other than white, select a color that will blend in and make it appear as if the cell is empty.
7. Click OK. Any cells you have applied this conditional formatting to will appear to be blank if the value in the cell is equal to or less than zero. If you place your cursor in the cell you will be able to see the cell’s actual value in the formula bar at the top of the worksheet.
Display Negative Values As Blank Cells or Dashes
8. Use the IF function to specify the format of a result that is equal to or less than zero. The syntax of the IF function is IF(logical_test,value_if_true,value_if_false)
9. Enter 1 in cell A1 and enter 2 in cell A2 of your worksheet.
10. Create an IF formula in cell A3 by typing: =IF(A1-A2>0,A1-A2,“-”). In this example the logical test is whether cell A1 minus cell A2 is greater than zero. If it is the “value_if_true” it will display; if it isn’t, the “value_if_false” will display. In our formula we defined the value_if_true as the difference between cells A1 and A2 and the value_if_false as a dash. Therefore, if A1-A2 is zero or less you’ll see a dash in cell A3, if the result is positive, you will see a number.
11. Change the values in cells A1 and A2 to see how it affects what displays in cell A3.

Read more ►

How to Create a One Way ANOVA in Excel

1. Open the Excel 2010 file where you want to create your one way ANOVA. Enter your data sets into the Excel cells. Separate your data sets by columns with the top cell in each column describing the set.
2. Click the 'File' tab at the top of the screen. Choose 'Options' from the list that appears on the left side of the screen. Select 'Add-Ins' from the left side of the window that appears. Click 'Go' at the bottom of the window.
3. Place a check mark next to 'Analysis ToolPak' and click 'OK.'
4. Click the 'Data' tab at the top of the Excel window. Find the 'Analysis' area and click the 'Data Analysis' button found there. Click on 'Anova: Single Factor' in the pop-up window and click 'OK.'
5. Place your cursor into the 'Input Range' box. Click and hold on the top-left cell in your data sets. Drag your mouse to the bottom-right cell and release the button. If you included the headers in your selected range, click the 'Labels in First Row' box in the ANOVA window.
6. Click the radio button next to 'Output Range' and then place your cursor into the text box next to the radio button. Select a cell on your worksheet where you want the ANOVA information to go. Click 'OK' to create the one-way ANOVA.

Read more ►

How to Remove Extra Rows in Excel 2003

1. Open Excel 2003. Click the 'File' menu and select 'Open'. Locate the spreadsheet with the extra rows and double-click its file name.
2. Press and hold down the 'Ctrl' key on the keyboard. Click once on each row number, in the leftmost column, to delete.
3. Right-click any of the highlighted rows. Select 'Delete Row'. The rows are deleted and the rest of the spreadsheet bumps up to replace those rows.

Read more ►