How to Interpret the Linear Regression Summary in Microsoft Excel 2003

1. Right-click on the regression line in your chart, and choose Properties. Check 'Display equation on chart' and 'Display R-squared value on chart'. Click OK.
2. Look at the R-squared value displayed next to the regression line. The R-squared value represents the amount of variability in the data that is explained by the linear regression analysis. If all the data lies exactly on the regression line, the R-squared value will be 1. If the R-squared value is 0, that means there is no correlation between the two datasets.
3. Turn your attention to the equation listed above the R-squared value. It will be of the form 'y = m x b', where m and b have been replaced by numbers. This equation describes the linear regression line. The 'm' value is the slope of the line, and the 'b' value is the location where the line crosses the vertical axis. You can use this equation to predict values in the dataset based on their value on the horizontal axis; just multiply their horizontal location by the 'm' value and then add the 'b' value to the result; this will give you the best estimate of the location of that point based on the linear regression analysis.
4. Look at the slope of the line. If it slopes downwards to the right, the data is 'negatively correlated,' if it slopes upward, the data is 'positively correlated.' Positive correlation means that the datasets tend to agree with or reinforce each other; negative correlation means that they tend to be at odds or mutually exclusive.

Read more ►

How to Create an Excel Spreadsheet to Figure Out Sick Leave

1. Type 'Vacation and Sick Leave Record' in cell E1. In cell E2, write the period that the sick leave worksheet is applicable to. For example, write '1/1/2010 to 12/30/2010.'
2. Type the employee's name in cell A4. Optionally, type their maximum sick leave accumulation on the next line in days or hours.
3. Type the word 'Month' in cell A6. Type the months January through December directly underneath, in the same column, with one month per row.
4. Type the word 'Amount' in cell B6, the word 'Used' in cell C6 and the word 'Balance' in the cell D6.
5. Enter the total number of the employee's available sick leave hours in cell B7. For example, if the employee has 120 hours, write '120.'
6. Type the following formula into cell D7:=B7-C7.Copy the formula to cells D8 to D17 by dragging the fill handle (the little black square in the bottom right corner of the cell) to cell D17.
7. Click on cell B8, type '=', then click on cell D7. This transfers the balance from the previous month to the 'amount' column for the beginning of the second month. Drag the fill handle of cell B8 to cell B17.

Read more ►

How to Password Protect Excel 2003

1.
In order to protect your Microsoft excel worksheet go to TOOLs menu select PROTECTION. From there you should see a menu like the picture on your left.
2.
From there you will be given a series of options that will allow you to protect a Microsoft excel worksheet, workbook or a range on your specific file. As you can see from the image on your left you can get very granular with this.
3. Make your appropriate adjustments and then click OK. You should be prompted to retype the password. Once that is done make sure you save your changes and close the document and reopen it to see if your password took.

Read more ►

How to Create a Box Plot in Microsoft Excel 2007

Set Up the Plot Data
1. Create a table with a column for each data set. In the table rows, add formulas for the calculations of (in order) the minimum, first quartile, median, third quartile and maximum for each data set using the Excel functions MIN, MAX, MEDIAN and QUARTILE (or PERCENTILE). This is the 'summary table.'
2. Create a second table with the same rows and columns as above. This table will contain the values used for the plot. This is the 'plot data table.'
3. Add a formula for each data set's maximum value in the plot data table that is the maximum minus the third quartile values from the summary table.
4. Add a formula for each data set's third quartile value in the plot data table that is the third quartile minus the median values from the summary table.
5. Add a formula for each data set's median value in the plot data table that is the median minus the first quartile values from the summary table.
6. Add a formula for each data set's first quartile value in the plot data table copying the first quartile value from the summary table.
7. Add a formula for each data set's minimum value in the data table table that is the first quartile minus the minimum values from the summary table.
Create the Plot
8. Select the range containing the third quartile, median and first quartile of all the data sets in the plot data table.
9. Open the Insert ribbon. Click 'Column' on the charts. Select 'Stacked Column' from the '2-D Column' type charts.
10. Click 'Select Data' under 'Design' on the 'Chart Tools' section of the ribbon. Modify the 'Series' order so 'Series 3' is the bottom segment on the chart and 'Series 1' is the top segment on the chart.
11. Click on the bottom segment of one of the columns in the chart. Select 'Layout' under 'Chart Tools' on the ribbon. Click 'Error Bars' and select 'More Error Bars Options.' The 'Format Error Bars' window appears.
12. Select 'Minus' for the 'Direction.' Select 'Custom' for the 'Error Amount.' Click 'Specify Value' and select the range for all the minimum values in the plot data table for the 'Negative Error Value' in the 'Custom Error Bars' window. Click 'OK' to exit this window and 'Close' on the next window to return to the chart.
13. Click on the top segment of one of the columns in the chart. Go to the 'Format Error Bars' window as above. Choose 'Plus' for the 'Direction' and use the maximum values in the plot data table for the 'Positive Error Value' range for the 'Custom Error Amount.'
14. Right-click on the bottom segment of one of the columns on the chart and select 'Format Data Series.' Set the 'Fill' to 'No fill.' Set the 'Border Color' to 'No line.' Close the window.
15. Delete the chart legend. Add polish by formatting the chart colors, adding a title and other finishing touches.
16. Select the rows containing the plot data table. Under 'Home' on the ribbon, click 'Format' and select 'Hide Rows' under the 'Hide Unhide' sub-menu.

Read more ►

How to Do Percentages With Excel 2003

1. Open Excel 2003, and open a workbook that contains a column with amounts and another column with totals. Click 'File' on the menu bar, and click 'Open.' Browse your files, and locate the workbook. Click the workbook and select the 'Open' button. The workbook opens.
2. Click in the next available column in your workbook. Type '=.' Click in the first cell that contains the first amount. Type '/.' Click in the first cell that contains the first total, and press the 'Enter' key. A value is generated.
3. Highlight the column containing this new formula. Click the '%' symbol on the standard toolbar. Your value is transformed into a percentage.

Read more ►

How to Maximize a Sheet in Excel 2007

1. Open an Excel worksheet by clicking on the round 'Office' button in the top left-hand corner of Excel 2007. Select 'Open' from the drop-down menu on the left. Choose the file name of the Excel document that you wish to open.
2. Click on the 'maximize' button in your Excel work window. You'll find it just to the left of the 'close' button in the top right corner. An 'X' identifies the close button, and a square, the maximize button. The maximize function changes your worksheet to maximum size within your open Excel document.
3. Click on the 'restore down' button to make your worksheet smaller. The restore down function changes your worksheet to smaller size within your open Excel document. You'll find the restore down button in the same location as the maximize button. The two buttons function as a toggle: When you have maximized the sheet, the button represents restore down.

Read more ►

How to Sort and Filter in Microsoft Excel

1. Open Microsoft Excel and go to 'File' and 'Open' to open the document containing data you want to sort and filter. Highlight the document and click 'Open.' The document will open in a new window.
2. Highlight the data you want to sort. You may select an entire worksheet, a column, multiple columns or specific data within a column.
3. Complete a simple sort by selecting the 'Home' tab from the ribbon and from the 'Editing' group selecting 'Sort and Filter.'
4. Click the appropriate option that matches your preferred sort. For example, if your data is a group of dates, click 'Sort Oldest to Newest' or 'Sort Newest to Oldest.' If your data is text, select either 'Sort A to Z' or 'Sort Z to A.' If your data consists of numbers, select 'Sort Smallest to Largest' or 'Sort Largest to Smallest.' The sort will occur upon clicking this command.
5. Complete an advanced custom sort by selecting 'Editing', 'Sort and Filter' and 'Custom Sort.' Select the appropriate options for your sort from options that include columns, value or order---with or without the column headers. You may also sort by multiple levels. For example, your data may be sorted alphabetically at the first level and by date at the second level.
6. Filter your data by selecting 'Home,' the 'Editing' group and 'Filter.' A drop-down arrow appears next to the first cell of data. Click the drop-down and uncheck any data that you want to filter out. You may perform a sort based on the data that remains.

Read more ►

How to Use Absolute References in Microsoft Excel

1. Enter a value into the cell you want to keep constant.
2. Select another cell in the Excel spreadsheet for the formula that will use the absolute reference.
3. Enter the formula. Use the dollar sign ($) in front of the row reference to keep to keep the row absolute or in front column reference to keep it absolute. For instance, '$C$21' is an absolute reference for cell 'C21,' while 'C$21' an absolute reference to row '21' but but a relative reference column 'C.'

Read more ►

How to Convert Word to Excel 2007

1. Launch Microsoft Word and open the document that you want to convert to Excel. Click once on the Microsoft Office button and select the 'Open' option. Locate the folder in which the document is saved in the 'Look in' section of the 'Navigation Pane.' Double click on the file name to open it. If you do not have either program, a free trial may be downloaded from the Microsoft site.
2. Save the Word document as a text (TXT) file. Click once on the Microsoft Office button and select the 'Save As' option. Select a folder in which to save the text file using the 'Save in' menu. Use the 'Save as type' menu to select the 'Text (TXT)' option. Type a name for the text file in the 'File name' field and click once on the 'Save' button.
3. Launch Microsoft Excel 2007 and import the text file. Click once on the 'Data' tab and locate the 'Get External Data' section. Click once on the 'From Text' option. Use the 'Look in' menu to locate the folder in which the text file is saved. Double click on the file name to open it.
4. Use the 'Text Import Wizard' to convert the information from the Word document to Excel. Select the 'Delimited text files (.txt)' option. Click once on the 'Next' button. Depending on the type of data that was in the Word document, select the appropriate separator, for example commas or tabs, to separate the text into Excel fields. Click once on the 'Finish' button to complete the process of converting the Word document to Excel 2007. Remember to save the Excel 2007 file by clicking once on the Microsoft Office button and once on the 'Save As' option.

Read more ►

How to Unlock Grayed Out Menus in Excel 2007

1. Position your cursor so that it is on one of the sheet tabs at the bottom of the screen. Make sure the sheet tab is highlighted.
2. Right-click the sheet tab. Choose 'Ungroup Sheets' from the drop-down menu.
3. Click one of the menus. All of the options should now be visible. If the options are still grayed out, right-click the sheet tab again and choose 'Ungroup Sheets.'

Read more ►

How to Remove a Contribute Toolbar From Excel 2003

1. Open Excel. Customizations to the toolbar interface are always accomplished within the program. However, it does not matter what file is opened within Excel, or if the program window is open without any files loaded.
2. Locate the 'Contribute' toolbar. It is important to see the toolbar when it is active so you can easily verify if the removal process was successful. As there are many toolbars, removing a single toolbar is not always obvious unless you are aware of its precise location in the program window.
3. Click the 'View' menu. Select the 'Toolbars' submenu. If the 'Toolbars' submenu is not listed, the menu is set to automatically collapse and show only the most frequently used features. Click the double arrow at the bottom of the 'View' menu to fully expand the list of items and select the 'Toolbars' submenu.
4. Locate the 'Contribute' item in the 'Toolbars' submenu. If the 'Contribute' toolbar is turned on, it will show a check mark next to its listing. Click on the 'Contribute' item in the list and the check mark will disappear. The toolbar is no longer active and it is removed from the Excel 2003 screen.
5. Right-click on any toolbar area or button in the Excel 2003 window to display the toolbars context menu as an alternative to using the 'View' menu. Remove the 'Contribute' toolbar in the same fashion using this pop-up menu.

Read more ►

How to Open WB3 Files in Excel 2003

1. Launch 'Excel.'
2. Go to 'File.' Select 'Open,' choose 'Quattro Pro/DOS' from the 'Files of Type' drop-down menu. Navigate to the folder where your WB3 file is located, and double-click to select the file.
3. Go to 'File,' and select 'Save As.' Choose 'Excel 97-2003 Workbook (*.xls)' from the 'Save as Type' options. Click 'Save.'

Read more ►

How to Create Mailing Labels From an Excel Database

1. Open Microsoft Word. Click the 'Mailings' tab and click 'Start Mail Merge.' Select 'Labels.' The 'Label Options' dialog box will open, where you can set up the labels.
2. Select the type of printer you are going to use under 'Printer Information.' Click the 'Label Vendors' list and select the manufacturer of your label sheets. Select the product number listed on your label sheet packaging from the 'Product Number' list. Click 'OK.' The sheet of labels is set up as a table in your document.
3. Click the 'Mailings' tab, then 'Select Recipients' in the 'Start Mail Merge' group. Click 'Use Existing List.' In the dialog box, browse through your computer files to select the Excel database file containing your address list. Double click the file.
4. Select particular recipients if you don't want to use your whole Excel list. To do so, click 'Edit Recipient List' in the 'Start Mail Merge' group on the 'Mailings' tab. Choose individual records by checking the box next to each record you want and unchecking the ones you don't want to use.
5. Set up the mail-merge fields, which will match each address component from your list to a placeholder on your label document. Click 'Match Fields' in the 'Write Insert Fields' group on the 'Mailings' tab. The dialog box will open, showing a list of address elements on the left side and corresponding column headings from your address list on the right side. Click each drop-down menu and select the correct column heading you want to use for each address element. Only select the address elements you want to use in your labels.
6. Click the first label on your Word document. Add any content, such as text, picture or logo, that you want to appear on each label. To insert an image, click the 'Insert' tab, then 'Picture' in the 'Illustrations' group. Select an image file from your computer, then click 'Insert.'
7. Insert the mail-merge fields, which serve as placeholders until you merge the labels with your address list. Click where you want to insert the address on the first label. Click 'Address Block' in the 'Write Insert Fields' group on the 'Mailings' tab. Select the address elements you want to insert and how you want them formatted. Click 'OK' to insert the address block.
8. Click 'Update Labels' in the 'Write Insert Fields' group to duplicate the data from the first label onto all the other labels.
9. Preview the merge results before completing the labels. Click 'Preview Results' on the 'Mailings' tab. If you're satisfied with them and are ready to print, click 'Finish Merge' in the 'Finish' group on the 'Mailings' tab. Click 'Print Documents.' Specify whether you want to print the whole set of labels or just a portion of them.
10. Connect your printer to the computer and feed it with the labels sheets. Click 'Print' and wait while your labels print out.

Read more ►

How to Set Up Formulas in Excel 2007

1. Click the cell where you want to display the results.
2. Press '=' on your keyboard to start a formula.
3. Add a parenthesis and the name of the first cell you want to include in your formula. For example, your formula to this point should look something like this: =(A1
4. Add the operator -- ' ,' '-,' '*' or '/' -- you want this formula to perform. For example, =(A1
5. Type the location of the next cell for your formula and repeat Steps 3 and 4 -- minus the parenthesis -- until you have listed all the cells you want to include in your formula. For example, =(A1 A2 B1 B2 C1 C2
6. Add a closing parenthesis and press 'Enter.' Your results should appear.

Read more ►

How to Change Appearance in Excel 2007

1. Choose a document theme to unite all of your Office 2007 programs with the same look. A document theme shares the same colors, fonts, lines, fill effects and other style choices. Select a document theme from the available choices or create a new document theme by going to Page Layout and selecting 'Themes.' Click on a document theme from 'Built-in' or 'Custom.' When you create a custom theme, save it under a new name.
2. Click Page Layout View to adjust margins or add headers and footers. This feature is similar to the Print Layout view in Word.
3. Use the different style choices to change the look of tables, charts and diagrams. Start with the quick styles (pre-defined styles) and customize to your liking.
4. Change chart and table Layout options to introduce changes that include moving items around. Charts and tables also have different styles to select. Go to the Design Tab or Chart or Table Styles, and click 'More.'
5. Alter your color scheme with a click of the Microsoft Office button. Click 'Excel options,' and then 'Popular.' Choose from the large number of color schemes. Change tab color by right clicking the worksheet tab. Aim at Tab Color, and choose your color.
6. Add formatting to charts not only changes the appearance, but also emphasizes important data. Try borders, fonts, bubbles or 3-D effects. If you really want an eye-catching look, try the 'Exploding Pie' or 'Doughnut Slice.' Pull up the Format dialog box to make changes. You can also right click chart items to format.
7. Fill charts not only with color, but also texture and pictures with the 'Fill Effects' command.

Read more ►