How to Find the Regression Equation in Excel 2007

From Graphing
1. Select all the x (independent variable) and y (dependent variable) data.
2. Click on 'Insert' in the top menu bar, then on 'Scatter.' Click on any of the five chart options.
3. Click once on the graphed line. Right-click the line and choose 'Add Trendline...' from the menu that appears.
4. Click the check-box for 'Display Equation on chart' at the bottom of the window that appears. Click 'Close.' The regression equation for the data appears on the chart. With example x-values (1,2,3,4,5) and y-values (10,12,14,16,20), the equation that appears is 'y = 2.4*x 7.2.'
Built-in Excel Functions
5. Find the slope of the regression equation, 'm,' by typing in any empty cell '=slope(known_ys, known_xs).' For example, if the known y-values are the cell range B1:B5 and the known x-values are in the cell range A1:A5, then type '=slope(B1:B5,A1:A5).' With example x-values (1,2,3,4,5) and y-values (10,12,14,16,20), the result is a slope of two and four tenths.
6. Find the y-intercept of the regression equation, 'b,' by typing in any empty cell '=intercept(known_ys, known_xs).' For example, if the known y-values are the range B1:B5 and the known x-values are in the range A1:A5, then type '=intercept(B1:B5,A1:A5).' With the same examples values as before, the result is a y-intercept of seven and two tenths.
7. Write out the regression equation now as 'y = m*x b.' In our example, the regression line equation is 'y = 2.4*x 7.2.'
Brute Force Calculations
8. Calculate the sum of all the x-values, denoted (x). Do this by typing in an empty cell '=sum(A1:A5),' where A1:A5 is the range of x-values. Perform the same operation to find the sum of all the y-values, denoted (y), which may for example be in the range B1:B5.
9. Calculate the sum of the product of each x and y pair, denoted (xy). Do this by summing together A1*B1, A2*B2, etc.. in the same fashion as step one. Also, denote the number of x-y pairs as 'n.'
10. Calculate the sum of the square of each x-value, denoted (x^2). Do this by summing together A1^2, A2^2, etc.. in the same fashion as step one.
11. Calculate the slope, 'm,' of the regression equation by performing the following calculation: n(xy) - (x)*(y). Then calculate n(x^2) - (x)^2. Lastly, divide the first result by the second result. With example x-values (1,2,3,4,5) and y-values (10,12,14,16,20), the result is a slope of two and four tenths.
12. Calculate the y-intercept, 'b,' of the regression equation by performing the following calculation: (y)-m*(x). Lastly, divide the result by 'n.' With the same example data as the previous step, the result is a y-intercept of seven and two tenths.

Read more ►

How to Hide Gridlines in Excel

1. Open Microsoft Excel.
2. Click on the 'Office' button. Select 'Open' to load your saved spreadsheet, or select 'New' to start a new project.
3. Click on the 'View' tab. Tabs are located at the top of the main Excel window, and clicking on a tab does not take you away from your spreadsheet.
4. Click on the 'Gridlines' check box to deselect it. The box is checked by default, and deselecting the box turns off gridlines for the spreadsheet. Changes appear immediately within your Excel spreadsheet.

Read more ►

How to Duplicate Sheets in Excel 2007

1. Open Excel 2007 and click the 'Office' button. Select the 'Open' option. Browse the files and locate the Excel workbook. Click the workbook and the 'Open' button. The workbook opens.
2. Right-click on the spreadsheet and click the 'Copy' option. The 'Move Or Copy' dialog box appears. Select the workbook to duplicate the worksheet to in the 'To Book' drop down list. Click the worksheet where this copied worksheet will appear before in the 'Before Sheet' drop down list.
3. Click the 'Create A Copy' check box. Click 'Ok' to complete the copy.

Read more ►

How to Convert a Number to Text in Excel 2007

1. Open the Excel spreadsheet that has the numbers you want to convert to text.
2. Use your mouse to highlight the cell(s) you want to convert to text.
3. Click the 'Home' tab located in the Excel ribbon at the top of the page and look to the 'Number' section of the ribbon.
4. Click the arrow next to 'Number' on the ribbon to open the 'Format Cells' dialog box.
5. Click the 'Number' tab when the 'Format Cells' box opens. A list of options you can convert the numbers to is located in the left pane of the dialog box. Some options you can convert the numbers you selected to include percentages, fractions and text.
6. Select 'Text' from the options and click 'OK.' The numbers you selected on your spreadsheet will now be formatted as text.

Read more ►

How to Delete Every Other Row Automatically in Excel 2003

1. Open a new workbook in Excel and type the number '1' in cell A1. Skip cell A2 and type '2' in cell A3. Repeat this process until you have the numbers 1 through 5 entered in cells A1 through A9. The following cells should have the following numbers: '1' for A1, '2' for A3, '3' for A5, '4' for A7 and '5' for A9.
2. Click 'Tools' and select 'Macro.' Click on 'Visual Basic Editor.'
3. Select the 'Insert' tab and click 'Module.'
4. Copy the following macro and paste it in the Visual Basic sheet:Sub Delete_Every_Other_Row()' Dimension variables.Y = False ' Change this to True if you want to' delete rows 1, 3, 5, and so on.I = 1Set xRng = Selection' Loop once for every row in the selection.For xCounter = 1 To xRng.Rows.Count' If Y is True, then...If Y = True Then' ...delete an entire row of cells.xRng.Cells(I).EntireRow.Delete' Otherwise...Else' ...increment I by one so we can cycle through range.I = I 1End If' If Y is True, make it False; if Y is False, make it True.Y = Not YNext xCounterEnd Sub
5. Click 'Close and Return to Microsoft Excel' to exit, then test the macro by highlighting rows A1 through A9 on the Excel spreadsheet you made earlier. Click 'Tools,' then 'Macro' and then click 'Macros.'
6. Select 'Delete_Every_Other_Row' from the Macro menu and click 'Run.' The alternate rows will be deleted and numbers 1 through 5 will now be displayed in cells A1 through A5.

Read more ►

How to Delete Multiple Overlapped Checkboxes in Excel

1. Start Microsoft Excel. Press 'CTRL O,' select your workbook and then click on 'OK.'
2. Click 'Find Select' under the Home tab on the ribbon toolbar. Choose 'Selection Pane' from the menu. This opens a list panel at the right side of the Excel window.
3. Click 'Show All' near the bottom of the Selection Pane.
4. Hold down 'CTRL' and then click on the items on the list that correspond to the overlapped checkboxes. To de-select an item, click on its name again.
5. Press 'DELETE' to remove the checkboxes from your sheet.

Read more ►

How to Insert Information in an Excel Spreadsheet

Direct Data Entry
1. Launch Microsoft Excel 2010.
2. Click in one of the cells of the spreadsheet and type a number or word, and then press 'Enter.'
3. Type another number or word and press 'Enter' again to continue to fill in the current column. Alternately move to a different cell by clicking the mouse on that cell or by using the 'Tab' key or one of the arrows on the keyboard to navigate to a different cell.
Link to Existing Data
4. Launch Microsoft Excel 2010. Open the spreadsheet that contains the cells where data from other spreadsheets will be displayed.
5. Open the Excel spreadsheet that contains data that will be used in a new spreadsheet.
6. Switch back to the spreadsheet that will display the data and click on the cell that will display the data. Press the 'Equal' sign ('=') on the keyboard. Switch to the spreadsheet that contains the data and click once on the cell that contains the data and press 'Enter.' Notice that the display spreadsheet now displays the data from the other spreadsheet.
Import Data
7. Launch Microsoft Excel and open the spreadsheet that will contain the imported data.
8. Click on the cell where the imported data will be displayed.
9. Click the 'Data' tab on the toolbar and click the appropriate button in the 'Get External Data' group to launch the 'Open File' or 'Data Connection' dialog.
10. Locate the source data file or location and click 'OK' to connect to the new data source. Specify additional options depending upon the type of connection made and the amount of data being used.

Read more ►

How to Use Forms for Data Update in Excel

Use Forms in Excel 2003
1. Click on a cell in the data table you want to update.
2. Go to the 'Data' menu and select 'Form.' A data form opens.
3. Scroll through the records to find the data you want to update. You can also search for a specific entry by typing in specific criteria such as a name or record number. Click 'Find Next' to go to that record.
4. Press the 'Tab' key to move through the fields in the form. Update or edit any fields that you want to change.
5. Press 'Enter' to apply the changes and move to the next record. Close the form window when you are done updating the data and save the changes to the worksheet.
Use Forms in Excel 2007
6. Add the 'Form' button to the 'Quick Access Toolbar' the first time you want to use a form, as it is not included in the Excel 2007 ribbon by default. Click the arrow at the right end of the 'Quick Access Toolbar' and select 'More Commands.' Select 'All Commands' under 'Choose Commands From.' Click on 'Form Button' in the list and click 'Add.'
7. Click on a cell within the data table that you want to update. Click the 'Form' button on the 'Quick Access Toolbar.' A data form opens.
8. Scroll through the records to find the data you want to update. You can also search for a specific entry by typing in specific criteria such as a name or record number. Click 'Find Next' to go to that record.
9. Press the 'Tab' key to move through the fields in the form. Update or edit any fields that you want to change.
10. Press 'Enter' to apply the changes and move to the next record. Close the form window when you are done updating the data and save the changes to the worksheet.

Read more ►

How to Perform Linear Regression in an Excel Spreadsheet

Excel 2003
1. Highlight your chart data by clicking at the top left of the data and then dragging the mouse to the bottom right.
2. Graph a scatter plot from your data. Click on the 'Insert' menu, then click on 'Chart' to start the Chart Wizard. Click on 'XY (Scatter)' in the Chart Type box.
3. Add a trend line to the graph. Click on 'Chart' and then click on 'Add Trendline.' A pop-up window will appear.
4. Click on the 'Linear' Trend/Regression Type icon.
5. Click on the 'Options' tab, then check the 'Display equation on chart' box.
6. Click on 'OK' to display the regression line on your chart.
Excel 2007 or Excel 2010
7. Highlight your chart data by clicking at the top left of the data and then dragging the mouse to the bottom right.
8. Click on 'Insert,' then click on 'Scatter.' Click on the first scatter graph icon on the left.
9. Click on 'More Trendline Options.'
10. Click the 'Linear' radio button, then click the 'Display Equation on Chart' check box.
11. Click on 'Close.' Excel will insert the regression line into your chart.

Read more ►

How to Create Free Dashboards

Excel Dashboards
1. Upload data into an Excel spreadsheet. The first row should contain content headings, and the following rows are for data. Be sure to name the spreadsheet without using spaces or other characters; instead replace spaces with a '_' symbol.
2. Create an outline of how you want the dashboard to look. This will depend on what data you are tracking and who will be looking at the dashboard. You will use this later to place the charts and graphs in the final product.
3. Write formulas in Excel to turn your data into charts. The formula you need will depend on what you are charting and how you need to arrange it; You will find a link to a collection of Excel formulas in the Resources section.
4. Use Excel tools such as formatting, form control, objects and graphs to create the dashboard. You can find all these tools in Excel by selecting Menu > View > Tool Bars. It is possible to create a dashboard using only the tools available in Excel, but if you have an understanding of other programming software or languages, those may be helpful for creating more visually appealing or interactive dashboards.
5. Update your data to update the dashboard. You can choose how frequently you need to do this.
Dashboard Software
6. Upload data into Excel spreadsheet or other database. Sometimes you can simple copy and paste, but it is easier to connect Excel to the database, or enter it manually. Some software may only be compatible with certain data storage software, so check to see what you need.
7. Install the dashboard software of your choice.
8. Import the data from the database into the software. You will have to choose the specific file name and file path. Again, be sure that your document title does not contain any spaces or unusual characters.
9. Open the data file in the dashboard software. From here, you can use the various tools in the software to create the dashboard you need.

Read more ►

How to Make Excel 2003 Not 2007 Your Default Program

1. Open the Windows registry by clicking 'Start,' selecting 'Run,' typing 'regedit' into the 'Open' box and clicking 'OK.'
2. Navigate to 'HKEY_CURRENT_USER>Software>Microsoft>Office>12.0>Excel' in the registry by clicking the arrow to the right of each of these folders.
3. Right-click the 'Options' key inside the 'Excel' folder, point to 'New' and select 'DWORD Value.' Type 'NoReReg' over 'New Value' to rename it.
4. Right-click 'NoReReg' and select 'Modify.' Type '1' into the 'Data Value' box. Click 'OK.' Close the Windows registry.
5. Open Microsoft Excel 2003. Click 'Help' in the toolbar. Select 'Detect and Repair' and click 'Start.' After 'Detect and Repair' is complete, click 'OK.' Close Excel 2003.

Read more ►

How to Format in Microsoft Excel 2007

1. Open your Microsoft Excel file.
2. Select the cells you want to format. Click and drag to select multiple cells. Click the top letter to select an entire column. Select the side number to select an entire row.
3. Choose the 'Home' tab on the top menu. Click the drop-down box under the 'Number' box. Click the option for 'More Number Formats.'
4. Click the tabs to change the number, alignment, font, border, fill and protection. The 'Number' tab is where you change the type of number used in the cell such as currency, date or time. The 'Alignment' tab is where you change you text direction and merge cells. The 'Font' tab changes your font type, size and color. The 'Border' tab changes the border surrounding your cells. The 'Fill' tab adds color to the background of the cells. The 'Protection' tab locks cells.
5. Click 'OK.'

Read more ►

How to Create a Survey in Excel

Enable Developer Tab
1. Open Microsoft Excel and click 'File' tab.
2. Click 'Options' and click 'Customize Ribbon.'
3. Click 'Main Tabs' and check the 'Developer' box. Click 'OK' to close the dialog box. The Developer tab appears in your Excel ribbon.
Insert Check Box
4. Open the Excel spreadsheet that you want to create a survey.
5. Click the 'Developer' tab and click 'Insert' from the 'Controls' group.
6. Click 'Insert' and a drop-down list appears on your screen. Click 'Check Box' under the 'ActiveX Controls' heading.
7. Click the cell where you want the upper-left corner of the check box to appear in your spreadsheet.
8. Highlight the words 'Check Box' and press 'Delete.' Type a property for your check box and press 'Enter.' This property might include 'Yes' or 'No,' 'Rent' or 'Own' or 'Full-Time' or 'Part-Time,' depending on the type of survey you are creating.
9. Right-click the check box and click 'Format Control.' Edit the check box's color, size, format and value. Click 'OK' to close the Format Control dialog box.
Insert Option Button
10. Click the 'Developer' tab and click 'Insert' from the 'Controls' group.
11. Click 'Insert' and a drop-down list appears on your screen. Click 'Option Button' under the 'ActiveX Controls' heading.
12. Click the cell where you want the upper-left corner of the option button to appear in your spreadsheet.
13. Highlight the words 'Option Button' and press 'Delete.' Type a property for your option button and press 'Enter.' This property might include 'Yes' or 'No,' 'Rent' or 'Own' or 'Full-Time' or 'Part-Time,' depending on the type of survey you are creating.
14. Right-click the option button and click 'Format Control.' Edit the option button's color, size, format and value. Click 'OK' to close the Format Control dialog box.
Insert List Box
15. Create a list of entries for your drop-down list, which must not contain blank cells and must be in a single column or row. This list can include data on the same or another worksheet within your workbook.
16. Click the 'Developer' tab and click 'Insert' from the 'Controls' group.
17. Click 'Insert' and a drop-down list appears on your screen. Click 'List Box' under the 'Form Controls' heading.
18. Click the cell where you want the upper-left corner of the list box to appear in your spreadsheet.
19. Right-click the option button and click 'Format Control.' Click the 'Control' tab.
20. Click the 'Input Range' button select the list of entries for your list box. Press 'Enter.'
21. Edit the list box's size, format and value. Click 'OK' to close the Format Control dialog box.
Insert Text Box
22. Click the 'Insert' tab and click 'Text Box' from the 'Text' group.
23. Click the cell where you want the text box to appear in your worksheet.
24. Hover your mouse over a corner of the text box and your mouse changes to an arrow. This indicates that you can change the size of the box.
25. Click your mouse on the corner of the text box and drag it diagonally to the desired size.
26. Click inside the text box and type the information you want the text box to include. Highlight the text in the text box and click the 'Home' tab. Use the settings in the 'Font' group to format the information in the text box.

Read more ►

How to Enable Autosave in Excel 2007

1. Click the 'Office Button' on the top left side of your spreadsheet. Click 'Excel Options.'
2. Click 'Save' on the left side of the 'Excel Options' dialog box.
3. Check the box labeled 'Save AutoRecover information every x minutes.'
4. Select the time increment you wish to have Excel autosave your worksheets. This number ranges from one to 120 minutes.
5. Click 'OK' at the bottom of the 'Excel Options' box. This procedure enables autosave on this worksheet and every worksheet you create in the future.

Read more ►

How to Sort by Date on Excel 2010

1. Open the Excel 2010 workbook to the worksheet that contains the data you want to sort.
2. Click the column letter (A, B, C, ...) that holds date values, to highlight that range of cells.
3. Select the 'Data' tab in the Ribbon at the top of Excel. Look for the Sort Filter group.
4. Click the 'AZ' icon to sort dates from earliest to latest (January, February, March). Otherwise, click the 'ZA' icon to sort dates from latest to earliest (March, February, January).

Read more ►