How to Create a Normal Distribution Graph in Excel

1. Enter -4 in cell A1. Enter -3.75 in cell A2. Highlight both cells and grab the fill handle (the tiny box in the bottom right hand corner) with your mouse. Drag the fill handle to cell A33 and release the mouse.
2. Enter =NORMDIST(a1,0,1,0) into cell B1. This tells Excel to calculate the standard normal distribution from the value you entered in cell A1 with a mean of 0 and a standard deviation of 1. Press enter.
3. Using the same motion you used in Step 1, drag the fill handle from the corner of cell B1 down to cell B33.
4. Highlight cells A1 through A33 by holding the the left mouse button down and dragging the cursor.
5. Select 'Insert' from the toolbar, then 'Scatter,' and 'Smooth Line Chart.'
6. From Chart Tools at the right hand side of the toolbar, select 'Layout,' 'Axes,' 'Primary Vertical Axis,' then 'None'. This step will make the y-axis disappear.
7. Select 'Axes' from the center toolbar, then 'Primary Horizontal Axis'. Select the bottom option ('More Options'). Change the minimum x-value to -4 and the maximum x-value to 4 by pressing the appropriate radio button and filling in the values.

Read more ►

How to Create a Drop

1. Open Excel 2007 and click a blank cell in the workbook. Select the 'Data' tab and select 'Data Validation.' A drop-down list appears. Click 'Data Validation.' On the 'Setting' tab, change the Allow field to 'List.' In the source field, type 'yes,no,maybe.'
2. Click the 'Input Message' tab. Click 'Show input message when the cell is selected.' Add a title for your input message in the 'Title' field. Add a custom input message in the 'Input Message' notes field.
3. Click the 'Error Message' tab. Click 'Show error message when invalid data is entered.' Add a title for your error message in the 'Title' field. Add a custom input message in the 'Error Message' notes field. Select a style for your error message in the 'Style' drop-down list. Click 'OK.'
4. Click the cell where you started the data validation process. Notice the drop-down list that appears. You also will see the input message.

Read more ►

How to Calculate Correlation Coefficient Between Two Data Sets

1. Open Excel 2007 and sum in one column the numbers for the first set of data. For example, you would add the numbers 10, 20, 30, 40, 50 and 60 in the A2, A3, A4, A5, A6 and A7 cells of your Excel worksheet. In a second column, sum the numbers for the second set of data. For example, you would add the numbers 5, 2, 6, 6, 7 and 4 in the B2, B3, B4, B5, B6 and B7 cells of your Excel worksheet. Your goal is to find the correlation coefficient for these two sets of data.
2. Click on the 'A9' cell. This is the cell where you will calculate the correlation coefficient.
3. Click on the 'Formulas' tab and choose 'Insert Function' (this is found on the top left hand side of Excel spreadsheet). The 'Insert Function' window will open. Click on the drop-down menu of 'Or select a category' and choose 'Statistical.' Scroll down the 'Select a function' window. Choose 'CORREL.'
4. Click 'OK.' The 'Function Arguments' window will open, and you will see two cells: 'Array1' and 'Array2.' For Array1, enter A2:A7 for first set of data and for Array2, enter B2:B7 for the second set of data. Click 'OK.'
5. Read your result. In this example, the calculated value of the correlation coefficient is 0.298807.

Read more ►

How to Freeze a Row in Microsoft Excel

1. Open the Excel worksheet.
2. Click the top row heading. The row heading displays a number just left of the first column of cells. The selected row appears shaded.
3. Click the 'View' tab on the command ribbon.
4. Click the 'Freeze Panes' button in the 'Window' group. A list of options appears.
5. Click the 'Freeze Top Row' option. A black horizontal line appears on the worksheet. This line indicates the locked row that stays on the screen as you scroll down the worksheet.

Read more ►

How to Add a Title to an Excel Chart

1. Start Microsoft Excel 2007 and open a spreadsheet that contains a chart to which you would like to add a title.
2. Click in the white area of the chart to select the entire chart. There should be a light blue outline surrounding the chart indicating that you have selected the entire chart.
3. Select the 'Layout' tab at the top of the Excel screen to display the layout options for the selected chart.
4. Click the 'Chart Title' button in the 'Labels' section of the 'Layout' ribbon. A drop-down menu will appear that will display the different locations that you can add a title for the selected chart.
5. Choose 'Centered Overlay Title' to add a centered title that lies on top of the existing chart so the chart does not have to be resized. Choose 'Above Chart' to add a centered title that goes above the chart and resizes the rest of the chart so it can fit.
6. Click the chart title you have just added to the chart and move the chart by clicking and dragging it to its new location. You must click on the outline of the chart with a 4-headed arrow before you can successfully move the entire title.

Read more ►

How to Use a Filter in Excel

1. Isolate column headings to one cell. If a column heading spills over into another cell, use text wrapping to place the heading in one cell. Highlight a multi-cell column header, press 'Format' on the menu bar, click 'Row', select 'Autofit and then Format', select 'Cells' and 'Wrap Text' to place headers in one cell.
2. Format the row that contains the column heading differently than the rows that contain data, so Excel recognizes it is a row heading. Embolden characters, change the font color or place a border around the column heading to differentiate from data cells.
3. Ensure each column contains one type of data. For example, a spreadsheet with student data should have a column for test grades, one column for averages, one column for student names and so on.
4. Click any cell inside the spreadsheet you want to filter. If you select an entire column as opposed to a single cell, Excel will present the option to filter that particular column, not all of the columns in the data set.
5. Press 'Data' on the menu bar, scroll down to 'Filter' on the drop-down list and click 'Auto-Filter.' The 'Auto-Filter' drop-down arrows will appear to the right of every column heading of the single column you selected for filtering.
6. Click the drop-down arrow near the column heading for a column of data to display the filter options for the particular column. Observe that Excel displays only the data that applies to your filter selection. Excel hides any rows that do not contain the selected filter.
7. Filter the top or bottom numerical records in a column of data with Excel's 'Top 10' Filter. Click on a data cell in the column and click 'Top 10' at the top of the Auto-Filter drop-down menu in Excel 2003. For Excel 2007, click 'Numbered Filters' and choose 'Top 10.' Select 'Top' or 'Bottom' in the Top 10 Auto-Filter dialog box, choose a number of records from 1 to 500, select 'Items' or 'Percent,' then click 'Apply.'
8. Set custom filters to show records that meet two criteria instead of one. Click the Auto-Filter drop-down menu of the column heading you want to set a filter for and press 'Custom.' Enter two filtering conditions for the column of data. For example, you can see which students scored from 90 to 100 and 60 to 70. Check either the 'And' or 'Or' button or else the results will not display. Click 'OK' to set the custom filter.
9. Click 'Data' on the menu bar, point to Filter and press 'Show All' to turn off the filter and display the hidden data.

Read more ►

How to Put Roman Numerals in Microsoft Office 2003

1. Open Microsoft Office Excel 2003.
2. In the Excel spreadsheet, click on a cell where you wish to put a Roman numeral.
3. Type '=Roman(58)' and press 'Enter.' The Roman numeral 'LVIII' that represents '58' will appear in the cell. Note that you can enter any number from 1 to 3,999 in parentheses.
4. Repeat Step 3 for other cells and/or numbers.
5. Click on a cell with the Roman numeral created in steps 3 or 4, and press 'Ctrl-C' on the keyboard to copy it.
6. Launch Microsoft Office Word 2003. Click the menu 'File' and select 'New' to create a new document, or 'File' and 'Open' to open an existing one.
7. Place the mouse pointer where you wish to insert the Roman numeral and press 'Ctrl-V' on the keyboard.

Read more ►

How to Use a Saved Template for Pivot Charts

1. Click 'Start' and 'All Programs.'
2. Navigate to the 'Microsoft Office' folder, click it once to display the folder contents and then click 'Microsoft Excel' to launch the program.
3. Click the 'Office' button in the upper left corner, and then select 'New' to create a new document. Or click 'Open' to locate and open an existing Excel spreadsheet.
4. Click and hold your mouse on the uppermost cell containing your target data, and then drag the mouse until all of your target data has been selected.
5. Click the 'Insert' tab at the top of the screen. Then click on the 'PivotTable' icon, and select the 'PivotChart' option.
6. Select whether you would like the PivotChart to be inserted into the existing worksheet or into a new worksheet. Click 'OK.'
7. Locate the 'PivotChart Tools' section at the top of the screen, and click the 'Design' tab in this section.
8. Click the 'Change Chart Type' icon at the top of the screen.
9. Select the 'Templates' option in the left column, select your saved template and click the 'OK' button.

Read more ►

How to Create a Calendar in a Pull

Create a Calendar Using Excel 2007
1. Enable the 'Developer' tab. The Excel 'Developer' tab is inactive by default, so you may need to activate it to create a pull-down menu. To activate the 'Developer' tab, click the 'Office' button in the top-left corner of Excel. Locate and click the 'Excel Options' button along the bottom right of the menu. Select and click the 'Show Developer in Ribbon' check-box from the pop-up menu, then click 'OK' to exit.
2. Insert a pull-down calendar. Click the 'Developer' tab from the Excel main menu ribbon. Click 'Insert' to display a menu of options and then click the 'More Controls' icon from the ActiveX controls section. The 'More Controls' icon is the last icon in the second row. It appears as a hammer and wrench crisscrossing in an 'X' pattern. A long list of additional controls will appear, so scroll down until the 'Microsoft Date and Time Picker 6.0' option appears. Click the option and then click 'OK' to return to your spreadsheet.
3. Draw, position and size the pull-down calendar. Draw a rectangle the size you desire for the calendar. Use the resize handles on the drop-down box to adjust the size if necessary. To move the calendar to another location on the Excel spreadsheet, click inside the calendar, hold the mouse button down and drag it to a new location. Resize the cell to fit the calendar by double-clicking the top margin lines.
4. Test the calendar. Deselect the 'Design Mode' tab in the main menu ribbon by clicking it once. Click the black triangle to the right of the calendar date, and your calendar will appear. If you need to make additional adjustments, go back to 'Design Mode.'
Link Calendar to Another Cell
5. Select the link cell. Select the cell you want to update when the date on the calendar changes, such as H9. In the formula bar, type in '=B5' (or whatever cell the calendar is in). Then click anywhere outside the cell to deselect it.
6. Create a link between the calendar and the update cell. Click 'Design Mode' and then click on the drop-down calendar to select it. Select 'Properties' option from the options box next to the 'Design Mode' button and locate the 'Linked Cell' option, and in the 'Linked Cell' information box, type in 'H9' (or whichever cell you select for the date update). Close the 'Properties' box and click on 'Design Mode' to deselect.
7. Activate the update. Change the date on the pull-down calendar. The update will appear in the linked cell.

Read more ►

How to Make a Dashboard in Excel

1. Open an Excel workbook containing data you want to manipulate. Create a new worksheet, inserted at the front of the workbook, and call it 'Dashboard.'
2. Click 'File' and then 'Options.' Click 'Customize Ribbon.' Tick the checkbox next to 'Developer' and click 'OK.' This enables your form control. Forms can be a vital component in an Excel dashboard.
3. Insert form items by clicking 'Developer,' 'Insert' and selecting a form option. With forms, such as a combo box, you can 'Format Control' and choose an output cell. That cell, in the case of a combo box, will show a number representing your selected option. For a combo box displaying the days of the week, for example, the output cell (hidden) will contain a value between '1' and '7.' This number can then be used by a graph or macro to display data only for that day.
4. Create a linked cell to link to other locations. This is useful if, for example, you have a totaled set of figures on the second worksheet and want to create a graph on page one but also show those totals. Type out 'Total' and 'Subtotal,' for example, on the first page, and then click the cell to the right of 'Total.' Type an equal sign ('='), then click the second worksheet, select the 'Total' cell figure and press 'Enter' to create a link to that cell. You can change the data on that page and it will still update on the first page.
5. Use color formatting to improve the look of your dashboard. This is useful not just for block color but also for conditional formatting. For instance, select a table of data and click 'Home,' 'Conditional Formatting,' 'Color Scales' and then 'More Rules.' You can select a different color scale and edit the figures so that high figures, for example, are displayed as red and low figures as green. Use these basic techniques to start creating your dashboard.

Read more ►

How to Make a Graph in MS Excel That You Change Every Day

1. Open the Microsoft Excel 2010 spreadsheet that contains the data you want to make into a dynamic graph.
2. Click anywhere in your data, then click the 'Insert' tab at the top of the screen. Click the chart button that corresponds to the type of graph you wan to use, then select your desired graph from the popup menu to make it appear on the spreadsheet.
3. Click the 'Formulas' tab at the top of the screen, then click the 'Define Name' button in the middle of the ribbon. Type a name for the first data series in your chart. This is normally the same text as appears in the header above the data series on your spreadsheet, but can be anything you desire.
4. Place your cursor into the 'Refers to:' box. Delete whatever is in the box, then type in '=OFFSET(' and click on the first cell in the column or row where the data for this series is located. Type in a comma, the number one, another comma, then number zero, then another comma. Then type in 'COUNTA($A:$A)-1)' but change each 'A' after the dollar signs to the column letter or row number where your data is located.Your final formula should look similar to this:=OFFSET(Sheet1!$A$1,1,0,COUNTA($A:$A)-1)Click 'OK' to close the Define Name window. Click the 'Define Name' button again and repeat the entire process for each data series in your graph.
5. Right-click on your graph and choose 'Select Data' from the popup menu. Click on your first data series, listed on the left side of the window that appears, then click 'Edit.'
6. Place your cursor into the 'Series values' text box and press backspace several times to delete everything after the exclamation point. Type in the name of the range you created for this set of data.
7. Click 'OK' to go back to the Select Data Source window. Click on a different data series and repeat the process. Continue until you have entered your names for each data series, then click 'OK' to close the Select Data Source window. Your chart now changes as you add new data each day.

Read more ►

How to Make an Address Book on a Computer

1. Open up Microsoft Excel on your computer. You will see that a spreadsheet opens up with predetermined rows and columns. You will use these rows and columns to set up your address book.
2. Add a title to the top of the spreadsheet. This will help you remember what information is in a particular address book. You may need to set up different address books for various purposes--for example, one for members of a book club and one for family members. Having a clear title will help you keep everything organized. Make the title bold and increase the size of the font, using the tools on the top toolbar.
3. Skip a couple of lines after the title and enter headings for the columns in the address book. The following column headings will be very helpful: Name, Address, City, State, ZIP Code, Phone Number, Fax Number and E-mail Address. Type in these headings into columns A to H, respectively. It is very important that each item is entered into a separate column.
4. Center and bold the column headings. To do this, click on the row number to the left of the row of headings. This will highlight, or select, the entire row. And, then, use the bold and center tools on the top toolbar.
5. Enter the contact information in each column. Information for each contact should be entered on a separate row. Depending on how many contacts you have, this could take some time. But, remember, you will only need to do this one time. In the future, you will just have to add new contacts and update information. If you do not have certain items of information for certain contacts, leave the cell blank. Remember to use the two-letter state abbreviations that the post office requires.
6. Format the text in the ZIP Code column to make all of the ZIP codes look the same. Highlight all of the text in the column, except for the column heading. Right-click on the highlighted cells and select 'Format Cells.' Click on the 'Number' tab, and then click on 'Special' in the 'Category' box. Click on 'Zip Code' or 'Zip Code 4' in the 'Type' box. Click 'OK.' This will make all of the ZIP codes look the same.
7. Format the text in the Phone Number column so that all of the phone numbers in the column look the same. Highlight the text in the Phone Number column, except for the column heading. Right-click on it and select 'Format Cells.' In the 'Number' tab, select 'Special' from the 'Category' box. Click on 'Phone Number' in the 'Type' box. Click 'OK.'

Read more ►

How to Draw a Histogram With MS Excel

1. Type your data into column A of your worksheet. For example, click on cell 'A1' and type your first data item, click on cell 'A2' and type your second data item. Continue down the column, clicking on cells and typing your data in until all of your values are on the worksheet.
2. Place your bin widths into column 'B.' Type the top value of each bin, starting in cell B1. For example, if your bin widths were 0 to 4 feet, 5 to 6 feet and 6 feet to 8 feet, type '4' into cell B1, '6' into cell B2 and '8' into cell B3.
3. Click on the 'Data' tab, then click 'Data Analysis.'
4. Click on 'Histogram,' then click on 'OK.'
5. Type the range of your inputs ito the 'Input Range' box. Your inputs are the values you entered into column A. For example, if your inputs are in cells A1 to A10, type A1:A10.
6. Click on the 'Bin Range' box and type the location of the bin ranges. For example, type B1:B3 to indicate the bin ranges are in cells B1 to B3.
7. Click 'New Workbook' under 'Output Options' then click on the 'Chart Output' check box.
8. Click on 'OK' to create the histogram.

Read more ►

How to Insert an Excel Spreadsheet to a VB Form

1. Open Microsoft Excel and type 'A' in 'A1,' 'B' in 'B1,' 'Column A' in 'A2,' and 'Column B' in 'B2.' Save your spreadsheet to 'C:\' as 'ExcelFile.xlsx.'
2. Open Microsoft Visual Basic 2010 Express, click the 'File' menu and select 'New Project.' Click 'Installed Templates,' select 'Windows Forms Application' and click 'OK.'
3. Press 'Ctrl' 'Alt' 'X' to open the 'Toolbox' window. Double-click 'DataGridView' to add a new Data Grid View control to 'Form1.' Double-click 'Button' in 'Toolbox' to add a new button to 'Form1.'
4. Double-click 'Button1' to open the 'Form1.vb' module. Type the following above 'Public Class Form1':Imports System.Data.OleDb
5. Type the following inside 'Private Sub Button1_Click' to declare a 'DataSet' and define the Excel connection:Dim ds As New DataSet()Dim connectionString As String = 'Provider=Microsoft.ACE.OLEDB.12.0;' _'Data Source=C:\ExcelFile.xlsx ;' _'Extended Properties=Excel 12.0;'
6. Type the following to connect to the 'ExceFile.xlsx' file and fill the 'DataSet':Dim excelData As New OleDbDataAdapter('SELECT * FROM [Sheet1$]', connectionString)excelData.TableMappings.Add('Table', 'ExcelSheet')excelData.Fill(ds)
7. Type the following to display the spreadsheet in your Data Grid View:Me.DataGridView1.DataSource = ds.Tables(0)Me.Refresh()Press 'F5' to run your program and press 'Button1' to import the Excel spreadsheet.

Read more ►

How to Tell If a Worksheet Is Protected in Microsoft Excel 2003

Single Worksheet
1. Click on the 'Tools' menu.
2. Select 'Protection->Protect Sheet.'
3. Read the dialog box. If 'Protect worksheet and contents of locked cells' is checked, the sheet is protected.
Workbook (All Worksheets)
4. Open the workbook. If you cannot open the workbook, it is protected at the file level.
5. Add another worksheet to the workbook. If you can't add another sheet, the workbook is protected.
6. Resize the Excel window. If you can't, the workbook is protected.

Read more ►