How to Do a Calibration Curve on Excel

1. Run a set of known X variables through the equipment to produce a series of Y outputs.
2. Open Microsoft Excel. In cell A1 type 'concentration.' In cell B1 type 'output' or the appropriate unit of measure for your experiment.
3. Enter experimental concentrations for the X variable in column A, starting at A2.
4. Enter experimental outputs for the Y variable in column B, starting at B2
5. Highlight entire data set.
6. Click the 'Insert' ribbon.
7. Click the 'Scatter' button and select the 'Scatter plot with markers and straight lines.' The graph should now appear and the Excel ribbon should default to design view.
8. Click on the graph. In the Design ribbon under 'Chart layouts,' select 'Layout 9.' The calibration curve is complete. A y=mx b equation will appear in the graph along with the R2 value.

Read more ►

How to Make a Graph on Excel With X Y Coordinates

1. Open a new Microsoft Excel 2010 worksheet. Click on cell 'A1' and type in the header for your first series of data. This series will wind up on the horizontal, or 'X,' axis.
2. Click on cell 'B1' and enter the header for your second series of data. This series will be plotted on the vertical, or 'Y,' axis.
3. Select cell 'A2' and enter the first value you want to plot. Then select cell 'B2' and enter the corresponding coordinate. Continue entering your coordinates down the columns until you have entered all your data.
4. Click on any cell in the data field you have just created. Click 'Insert' at the top of the Excel window, and then click the 'Scatter' button in the Charts area on the ribbon.
5. Click on one of the five scatter chart types that appear in the pop-up menu. You can choose to plot just the markers for the data points, straight lines between the points, curved lines between the points, or either of the types of lines and the markers at the same time. Click on a scatter chart type and your graph will appear on the spreadsheet.

Read more ►

How to Create Criteria Ranges in Excel for Database Functions

1. Open the Excel application on your computer that contains the table of database functions that you want to create criteria for.
2. Highlight the range of cells containing numeric data using your mouse. Click on the “Data” tab for Excel 2007 or the “Data” option from the top toolbar menu for Excel 2003.
3. Click on the “Filter” option and the click on the drop-down arrow located in the column header.
4. Move your mouse over the “Number Filters” option and then click on the “Custom Filter” option. The Custom AutoFilter dialog box will appear.
5. Select the number criteria for your ranges by entering the numbers you want to only be included in your criteria range. An example is to enter “25” and “50” for the lowest and highest number.
6. Select the “And” option if you want to filter so that all criteria is true, or click on the “Or” option for the table column or section to be true together or separately. Your criteria will then be created.

Read more ►

How to Restore Gridlines in Excel 2003

1. Click 'Tools' in the menu bar.
2. Select 'Options.'
3. Click on the 'View' tab, then check the 'Gridlines' box.
4. Save your document.

Read more ►

How to Convert a Microsoft Word Table Into an Excel Workbook

1. Open Microsoft Word and Excel from the desktop menu. In Word, find the document containing the text to be converted. In Excel, open a blank worksheet.
2. Return to the Word document and confirm that the text appears in true table format. If there are gridlines or a compass symbol enclosed in a box at the top left corner of the first column, the text is a true table. If not, the text appears in columned format only and must be converted to a true table.
3. Convert columned text to a true table by inserting a tab, a comma or an asterisk in between each row of the columns. Highlight all the text. Click 'Table' on the 'File' menu and scroll down to 'Convert.'
4. Choose the text appearing to the right, 'Text to Table.' A dialog box opens entitled, 'Convert Text to Table.' Enter the number of columns and rows. Go down to 'Separate Text At' and type in the symbol used to divide the columns. Hit 'OK.'
5. Click on the box containing the compass symbol in the top left corner to select the entire table for exporting to Excel. Under 'Edit' on the 'File' menu, choose 'Copy.' Switch back to the Excel program and click inside the cell where the table should be placed. Go to 'Edit' and choose 'Paste.'
6. Format the Excel worksheet to accommodate the table with a new look. Adjust the width of columns and the height of rows so that the text fits neatly.
7. Keep the same appearance it had in the original Word document by locating the 'Paste Options' icon in the lower right corner of the pasted text. Click on the drop-down arrow and choose 'Keep Source Formatting.'

Read more ►

How to Unfreeze Panes

Freezing Panes in Excel 97/2000/XP/2003
1. Open the spreadsheet you want to freeze.
2.
Select the row below the one you want to freeze by clicking on the row header.
3. Select 'Window' from your menu and choose the 'Freeze Panes' option.
Unfreezing Panes in Excel 97/2000/XP/2003
4. Open the spreadsheet with frozen panes.
5. Select 'Window' from the menu.
6. Choose the 'Unfreeze Panes' option.
Freezing Panes in Excel 2007
7. Select the row just below the one you want to freeze by clicking on the row header. This will make a specific row or rows visible as you scroll within Excel. Select the 'View' tab in the 'Windows' group, and choose 'Freeze Panes.' Select 'Freeze Panes' from the pop-up list to keep rows visible while your worksheet scrolls.
8.
Make a specific column or columns visible as you scroll within Excel by selecting the column to the right of the one you want to freeze and clicking on the column header. Select the 'View' tab from the 'Windows' group and choose 'Freeze Panes.' Select 'Freeze Panes' from the pop-up list to keep columns visible while your worksheet scrolls.
9. Make both the column(s) and rows(s) visible as you scroll within Excel by selecting the cell below and to the right of the columns and rows you want to freeze. Select the 'View' tab from the 'Windows' group and choose 'Freeze Panes'. Select 'Freeze Panes' from the pop-up list to keep rows and columns visible when you scroll.
10. Freeze or lock only the top row of an Excel worksheet by selecting the 'View' tab from the 'Windows' group. Choose 'Freeze Panes' and select 'Freeze Top Row' from the pop-up list.
11. Freeze only the first column of an Excel worksheet by selecting the 'View' tab from the 'Windows' group. Choose 'Freeze Panes' and select 'Freeze First Column' from the pop-up list.
Unfreezing Panes in Excel 2007
12. Selecting the 'View' tab from the 'Windows' group.
13. Choose 'Freeze Panes'.
14. Select 'Unfreeze Panes' from the pop-up. This will unfreeze any locks you've created in the worksheet.

Read more ►

How to Move Averages in Excel 2010

1. Enter the date, or other reference information, in column A, and enter its corresponding data in column B. Repeat for each data point. As an example, you might have 'Jan 1, 2011' in cell A1 and '$43.25' in cell B1. On the next row, you might have 'Jan 2, 2011' in cell A2 and '$44.50' in cell B2. This continues for however many data points you have, but you will need several data points to construct a moving average.
2. Click the cell in column C whose row number corresponds to the number of data points you want to include in the moving average. As an example, to include the last 20 data points, click cell C20.
3. Type '=AVERAGE(range)' and press 'Enter,' replacing 'range' with the actual range of data points. In the example, this would be 'B1:B20,' so you would enter '=AVERAGE(B1:B20)' in cell C20.
4. Click the cell you just modified, hold the 'Ctrl' key and press 'C' to copy the cell formula.
5. Hold the 'Shift' key and click the last cell in column C that corresponds to the last data point. This selects all the cells between those two cells. As an example, if your last data point was at B40, hold the 'Shift' button and click cell C40. This selects cells C20 through C40.
6. Hold the 'Ctrl' key and press 'V' to paste the formula in all the selected cells. The formulas are automatically altered to reflect their new positions and will include the same number of most recent data points. All these calculations are your moving averages for the corresponding data points.

Read more ►

How to Make Double Line Graphs on Excel

1. Open the Excel worksheet that contains the data values.
2. Click and drag on the categories and two data series you want to plot.
3. Click the “Insert” tab in the Command Ribbon.
4. Click the arrow for the “Line” button in the Charts group. A gallery of seven thumbnail charts appears.
5. Click the preferred chart sub-type.
6. Click “OK.” The two data series convert to a line graph. The Chart Tools Ribbon displays.
7. Customize the chart with the commands in the Chart Tools Ribbon. Chart Tools contains three tabs: “Design,” “Layout” and “Format.” Experiment with the commands in each tab to increase readability and add visual interest. For example, the Design tab contains 12 chart layouts and 48 chart styles.

Read more ►

How to Format Dates in Microsoft Excel 2003

1. Select a cell and activate it. You will need to first left-click on a cell that you wish to enter a date into.
2. Create a date inside of a cell. Enter in any date that you like in the cell and then press enter; this will store the information into that cell.
3. Access the cell formatting menu. To access this menu, right-click on the cell that you just entered the date into and select “Format Cells.”
4. Format the date as desired. Under the cell formatting properties menu, left-click on the “Number” tab and then scroll to and highlight the “Date” field. Under the “Type” box you can scroll to the desired date format. When finished, click on the “OK” button to implement the changes.

Read more ►

How to Turn Off Page Lines in Excel

1. Click the 'File' tab in the Excel window, and then click 'New' if you want to make a new Excel document. Click on a template that you want to use, and then click 'Create.' A new Excel document will appear. Otherwise, click the 'File' tab, then click 'Open,' and then click on a folder where your file is stored. Click on the file to select it, and then click 'Open.'
2. Click the 'File' tab in the Excel window.
3. Click on 'Normal' in the 'Workbook Views' group. Excel will now display your spreadsheet without the page break lines. Click 'Page Layout View' if you want to see the lines again. If you still see page lines when working with a document in Normal view, that means Excel is set to turn them on automatically. To hide page breaks while in Normal view, you will need to adjust the display options for the document.
4. Click the 'File' tab, and then click 'Options' under 'Help.' Click the 'Show Page Breaks' check box to clear it under 'Display Options for This Worksheet' in the 'Advanced' category. When this option is unchecked, the page lines will not be displayed when you view a spreadsheet with Normal view.

Read more ►

How to Create a Workbook in Excel 2007

1. Decide what your needs are. You need a purpose for using Excel. Whether it is for a budget or to keep track of work, you need to know what you are planning to build.
2. Open Excel and you will see the option to create a workbook or use a template. You may want to check what templates are available and if you feel one of them meets your needs you could try using it. However, since most work is unique and you may have your own thoughts on how you want to present the data, you may be better off creating your own workbook.
3. Use column headings that will make your life easier when it comes time to manipulate the data. If you are using names, you may want to make columns with first names, middle names and last names. This will give you the choice to sort on any of these columns later on. Addresses should be broken up into street names, cities, states and zip codes for the same reason.
4. Enter the data. If you are creating an address book for yourself, you could make multiple sheets in your workbook for friends, families, co-workers, and businesses.
5. Use the tabs at the bottom of the page. The tabs signify the different pages or worksheets in your Excel workbook. You can rename them and make them more significant for your needs. For example, sheet 1 could be friends, sheet 2 family and sheet 3 co-workers.
6. Personalize the Excel workbook. The more you make the workbook your own, the easier it will be to work with the data. The examples in the above steps all involve work that you would do for your personal needs, but you can carry it over into your job also.
7. Add to the workbook as needed. The beauty of using Excel workbooks is that you only have to enter data once. After that, you can add or delete new entries or worksheets as you need. Editing data is also simple to accomplish.

Read more ►

How to Format Data Labels in Excel

1. Launch 'Microsoft Excel' by double clicking the program's shortcut on the desktop, or by selecting 'Microsoft Excel' from the 'Programs' or 'All Programs' menu.
2. Click 'File' or the 'Office Button' in the upper left corner of the program window and select 'Open.' Click on the downward pointing arrow to the right of the 'Look in' text box, and navigate to your Excel file. Select the file and click 'Open' or double click the file to open it.
3. Go to step 4 if you have Excel 2007; otherwise select the chart with the data labels you want to format. Click 'Chart' on the menu bar and select 'Chart Options' to open up the 'Chart Options' dialog box. Click the 'Data Labels' tab at the top of the window.
4. Do this step if you have Excel 2007. Click the right mouse button on the data labels and select 'Format Data Labels.' Click the 'Label Options' tab at the top of the window.
5. Select 'Series name,' 'Category name' or 'Value,' depending on what label you want displayed. Select more than one label if appropriate.
6. Select a 'Separator' of your choice if more than one data label was selected. Click 'OK' if you have Excel 2003 or an earlier version. Click 'Close' if you have Excel 2007.
7. Click the right mouse button on the data labels, and Select 'Format Data Labels.' Select a background color, border style, border color and border weight.
8. Click the 'Number' tab if your data labels are displayed as values. Select the appropriate 'Category' type for the values.
9. Click the '3-D Format' tab for Excel 2007. Select a '3-D Format.'
10. Click the 'Alignment' tab. Select a 'Horizontal' or 'Vertical' alignment for your text.
11. Click in the 'Degrees' or 'Custom angle' text box. Type a number to angle the text at.
12. Click 'Ok' for Excel 2003 and earlier, or click 'Close' for Excel 2007. Your chart should be displayed with your newly formatted data labels.
13. Click the data labels to select them. Select a 'Font', 'Font Color' of your choice, using the buttons on the 'Formatting Toolbar' or ribbon, if you are using Excel 2007.
14. Click 'File' on the menu bar, or the 'Office Button' and select 'Save' to save the spreadsheet. The spreadsheet can also be saved by clicking the save button on the toolbar.

Read more ►

How to Graph a Normal Distribution Curve in Excel 2007

1. Enter the number -4 into cell A2.
2. Select the cell with your mouse. Click on the down arrow under 'Fill' in the Editing group to the far right on the ribbon (the toolbar). Choose 'Series.' A pop-up will appear.
3. Click on the radio buttons to choose 'Columns and Linear.' Type .25 into the 'Step Value' box and 4 into the 'Stop' box. Press 'OK.' This should fill column A with the x-values for the normal distribution.
4. Type =NORMDIST(A2,AVERAGE($A$2:$A$34),STDEV($A$2:$A$34),FALSE) into cell B2. This calculates the mean and standard deviation from your x-values and also creates your first y-value in cell B2.
5. Copy the formula that you typed in Step 4 by highlighting the cell and dragging the fill tool (the tiny square at the bottom right) down to cell B34. This creates all of your y-values.
6. Select the 'Insert' tab from the ribbon. Click on the down arrow next to 'Scatter' and choose 'Scatter With Smooth Lines and Markers.'

Read more ►

How to Compare Data in Excel with VLOOKUP

1. Open the Excel 2010 spreadsheet that contains that data you want to compare. Click on the top cell of the first empty column in your spreadsheet.
2. Enter the following formula into the cell:=vlookup(A1,B:B,1,False)Change 'A1' to the top cell in the first column of data. Change the 'B's in 'B:B' to the letter of the column where the second set of data is located. Keep the '1,' as this tells VLOOKUP that you want to check the first column in the 'B:B' range. 'False' specifies that you want to search for an exact match, instead of an approximate one.
3. Press 'Enter' to complete the formula and have Excel run it. If the first entry in the first column appears in your second column, its name appears in the cell where you wrote the formula. If not, '#N/A' appears in the cell.
4. Select the cell where you entered your formula. Move your mouse pointer to the bottom-right corner of the cell. Click and hold the mouse button, then drag the mouse down until you reach the last cell where you want to use the formula. Release the mouse button. When you copy formulas like this, Excel will use relative references, so the 'A1' in the formula will change to, for example, 'A2,' 'A3' or 'A4' depending on what row the formula is in. The column where you entered the formulas will now display all the items from the first column that also appear in the second.

Read more ►

How to Create and Name an Excel 2007 File

Create New File Instructions
1. Open the 'Start' menu and type 'Excel' into the search box.
2. Click the 'Windows Office' icon in the top left corner of the Excel 2007 menu bar.
3. Click the 'Blank Workbook' in the 'New Workbook' window, then click the 'Create' button.
4. Click on the 'Microsoft Office' icon and select 'Save.' Create a name for your spreadsheet and ensure that the 'Save as type:' menu is set as 'Excel Workbook (*.xlsx).'
5. Click 'Save' in the 'Save As' window to save the new Excel 2007 file.
Existing Data Instructions
6. Open the 'Start' menu and type 'Excel' into the search box.
7. Click the 'Windows Office' icon in the top left corner of the Excel 2007 menu bar.
8. Click 'Blank Workbook' in the 'New Workbook' window, then click the 'Create' button.
9. Copy the information to be transferred into the new spreadsheet. Select cell 'A1' in the newly created spreadsheet. Paste the data by pressing both 'Ctrl' and 'V' on your keyboard.
10. Click on the 'Microsoft Office' icon and select 'Save' once the information has been copied. Create a name for your spreadsheet and ensure that the 'Save as type:' menu is set at 'Excel Workbook (*.xlsx).' Click 'Save' in the 'Save As' window to save the new file.

Read more ►