How to Add a Legend to an Excel Chart

1. Start Microsoft Excel 2007 and open a workbook you have already created from your files to which you want to add a legend.
2. Point and click on the chart so it is selected. You can tell the chart is selected because it will be surrounded by a light blue border.
3. Select the 'Layout' tab at the top of the Excel screen to display the 'Layout' ribbon. Locate the 'Labels' group in the 'Layout' ribbon.
4. Click the 'Legend' button in the 'Labels' group of the 'Layout' ribbon. The options for adding and displaying a legend to the chart will be displayed.
5. Choose 'Show Legend at Right,' 'Show Legend at Top,' 'Show Legend at Left' or 'Show Legend at Bottom' to place the legend in the specified location while shrinking the chart to make room for the legend. You can choose 'Overlay Legend at Right' or 'Overlay Legend at Left' to place the chart to the right or the left of the chart overlaying the chart so the chart does not shrink.
6. Watch as the legend is immediately placed inside the chart. You can move the chart manually at this time using the mouse to click and drag it to a new location.

Read more ►

How to Make a Calibration Curve Graph in Excel 2007

1. Highlight both columns of your data. Left-click on the top left of your data, then drag the mouse to the bottom-right of your data.
2. Click the 'Insert' ribbon.
3. Click the 'Scatter' button in the Charts section of the Insert ribbon. Click the bottom-right icon in the Scatter Plot drop-down list. The icon has squares with lines between them. The ribbon has now automatically switched to “Design,” and your plot has appeared on screen.
4. Click “Select Data,” in the Data section of the Design ribbon.
5. Click the list item titled “Series1,” then click 'Edit' right above that.
6. Type “Calibration Curve” in the “Series Name” text box. Press 'OK.'
7. Press 'OK' again, and you are back on your spreadsheet with the Calibration Curve plot.

Read more ►

How to Transfer Data From UserForm to a Worksheet

How to Transfer Data From a UserForm to a Worksheet
1. Open Microsoft Excel and create a new blank workbook by clicking on the File menu and selecting 'New.' Save the Excel file as 'update_worksheet.xls.'
2. Keep the workbook open and open the Visual Basic Editor by hitting Alt-F11. Create a new UserForm by clicking on the Insert menu and selecting 'UserForm.' Name the UserForm 'transferForm' by changing the Name attribute in the Properties menu. Change the Caption attribute to 'transferForm.'
3. Add a textbox to the form by selecting a textbox from the toolbox and dragging it into place on the form. Name the textbox 'transferInput' in the properties window. If the toolbox is not open, select the View menu and Toolbox.
4. Add an update button to the form by selecting a CommandButton from the toolbox and dragging it into place on the form. Name the CommandButton 'transferButton' in the properties window and change the caption attribute to 'Update Worksheet.'
5. Add a close button to the form by selecting a second CommandButton from the toolbox and dragging it into place on the form. Name the CommandButton 'closeButton' in the properties window and change the caption attribute to 'Close Form.'
6. Open the Visual Basic code editor by double-clicking on the transferButton (update button). Add code to the transferButton _click() function that transfers any data entered into the textbox to the update_worksheet.xls worksheet when the user clicks the button. Close the Visual Basic code editor.Private Sub transferButton_Click()Dim transferWorksheet as WorksheetSet transferWorksheet = Worksheets('Sheet1')transferWorksheet.Cells(1,1).Value = Me.transferInput.ValueEnd Sub
7. Reopen the Visual Basic code editor by double-clicking on the closeButton (close button). Add code to the closeButton_Click() function that closes the form when the user clicks the button. Close the Visual Basic code editor.Private Sub closeButton_Click()Unload MeEnd Sub
8. Run the form's code by clicking on the Run menu and selecting 'Run Sub/UserForm.' Enter data into the input field and click the 'transferButton.' Ensure that the data is successfully transferred to the first cell value in the Excel worksheet, switching to Excel if necessary. Save and close the running UserForm.
9. Use an Excel event procedure to open the UserForm when a user opens the workbook in Excel. Open the Project Explorer and double-click the ThisWorkbook code window. Enter a macro that opens the form when the worksheet opens and save the macro. Save and close Visual Basic and update_worksheet.xls.Private Sub Workbook_Open()transferForm.ShowEnd Sub
10. Reopen 'update_worksheet.xls.' The UserForm transferForm will open. Type some text into the 'transferInput' field and click the 'transferButton.' The text entered into the transferForm UserForm will be transferred to the first cell in Sheet1 in update_worksheet.xls.

Read more ►

How to Embed a SWF Into Excel 2007

1. Open the Microsoft Excel 2007 file on your computer that you want to insert a SWF file into.
2. Click the 'View' option from the top toolbar menu and then click on the 'Toolbars' option.
3. Click the 'Control Toolbox' option and then the Control Toolbox toolbar will appear at the top of the page.
4. Click the 'More Controls' option from the Control Toolbox toolbar and then click the 'Shockwave Flash Object' option. The SWF object will then appear in your spreadsheet.
5. Right-click the SWF object and then click the 'Properties' option. Enter the URL into the 'Movie URL' field.
6. Click the box next to the 'Embed Movie' field so it's selected, and then click the 'OK' button.

Read more ►

How to Change the Case in an Excel Spreadsheet

1. Type the following formula into the cell directly to the right of the cell you want to change the case in:=LOWER(A1)
2. Change 'A1' to the cell number you want to change. For example, if your text is in cell B3, then change 'A1' to 'B3' so that the formula reads:=LOWER(B3).
3. Change 'LOWER' to 'UPPER' or 'PROPER,' if desired. (Leave the function intact if you want to change to lowercase).
4. Press 'Enter.' Excel converts the text to the new case.
5. Copy the new data and paste it over the original cell to replace it.
6. Delete the cell with the function in it.

Read more ►

How to Compare Sheets in Excel 2007

Compare Worksheets in the Same Workbook
1. Click the 'View' tab and click 'New Window' from the 'Window' group.
2. Click the 'View Side by Side' button from the 'Window' group.
3. Click the tabs at the bottom of each worksheet to display the worksheets you want to compare.
4. Click the 'Synchronous Scrolling' button from the 'Window' group to scroll both worksheets at the same time.
Compare Worksheets from Different Workbooks
5. Open both workbooks that contain the worksheets that you want to compare.
6. Click the 'View' tab and click 'New Window' from the 'Window' group.
7. Click the 'View Side by Side' button from the 'Window' group.
8. Click the tabs at the bottom of each worksheet to display the worksheets you want to compare.
9. Click the 'Synchronous Scrolling' button from the 'Window' group to scroll both worksheets at the same time.

Read more ►

How to Insert Hyperlinks and Email Addresses in Microsoft Excel 2003

1. Select the cell that you wish to insert an email address or a hyperlink. You will first need to select a cell where you would like to insert the hyperlink or the email address. You can do this by hovering the mouse cursor over the desired cell and left-clicking to activate that cell.
2. Access the insert hyperlink/email address menu. Scroll to the “Insert” tab on the command bar and select “Insert Hyperlink.” An insert hyperlink properties menu will open. If you are wanting to insert an email address instead of a hyperlink, left-click on the “Email Address” icon in the lower left side of the box.
3. Enter the desired email address or hyperlink. Enter the desired email address or hyperlink into the address field. Make sure that you properly code the hyperlink with the “http://” format or the link will be invalid.
4. Insert the email address or hyperlink into the cell. To insert the email address or the hyperlink into the spreadsheet all you have to do is click on the “Okay” button.

Read more ►

How to Put White Space in Cells in Excel 2003

1. Click a cell that you wish to add white space.
2. Click the formula bar at the top of the screen.
3. Type the following in the formula bar: =' 'Substitute the exact number of spaces that you wish to add to the cell between the quotation marks.
4. Right-click the cell and click 'Copy' in the menu.
5. Highlight all cells that you wish to add white space.
6. Right-click the selection and click 'Paste' to add the blank spaces to all selected cells.

Read more ►

How to Make Standard Deviation Charts in Excel 2003

1. Open the Microsoft Excel 2003 application on your computer. Click on the “File” option from the top toolbar menu and then click on the “Open” option.
2. Locate the Excel 2003 file that contains the chart you want to work with from your desktop or folder where you store your Excel files. Select the file so it’s highlighted, and click on the “Open” button.
3. Click on the line, bar or dots of your graph, which represent your data. Click on the “Format” option from the top toolbar menu and then click on the “Selected Data Series” option.
4. Select the shape or color of error bar you want from the “Display” list, including black dots and transparent squares. Select the 'Standard Deviation' option from the list below the “Error amount” field.
5. Click on the “Custom” option if you want to insert specific amounts for the standard deviation equation. Click on the “OK” button and the changes will be made to your chart.

Read more ►

How to Hide Macros in an Excel Workbook

1. Open Microsoft Excel and navigate to the 'Menus' tab at the top, left corner of the screen. Click the 'Files' drop-down menu and select 'Open.' Double-click the name of the Excel workbook that has the macro you want to hide.
2. Navigate to the 'Developer' tab, which is located at the top, right corner of the window. Click the 'Macros' button, which is positioned at the left side of the navigation ribbon and to the right of the 'Visual Basic' button.
3. Scroll through the list of macros and locate the name of the macro you want to hide. Click 'Cancel' to close the window and click the 'Visual Basic' button to open the VBA editor window.
4. Click the 'Modules' link at the left side of the window to bring up a list of VBA modules saved on the workbook. Scroll through the code until you find the opening 'Sub' command at the top of the Macro. For instance, if the Macro is titled 'Macro1,' the line of code should read 'Sub Macro1().'
5. Click behind the opening 'Sub' command and type the word 'Private.' Add a space between the 'Private' and 'Sub' commands so your line of code reads 'Private Sub Macro1().'
6. Click 'File' and choose 'Save' to save the changes and hide your macro.

Read more ►

How to Create a P Chart Using Samples

1. Click 'Start' then 'All Programs' and select 'Microsoft Excel' to open the program.
2. Type 'Class' in cell A1 of the Excel worksheet and type the numbers 1 to 10 in chronological order in cells A2 to A11.
3. Type 'Absent' in the cell B1 and then type the following values (according to the introductory example), one below the other as shown below (B2 to B11):4131263230Value '4' should be entered in cell B2, '1' in B3 and so on.
4. Type 'Proportion' in the cell C1, type '=B2/50' in cell C2 and then press 'Enter.' Proportion is calculated using the value in cell B2 (i.e., 4) and dividing it by 50 (number of students). These instructions cause Excel to automatically perform the calculation.
5. Point the mouse cursor to the bottom-right corner of cell C2. The cursor will turn to sign. Then click and drag the mouse cursor vertically down until you reach cell C11. Excel automatically calculates all the values using the formula used in the previous step while replacing B2 with B3, B4 and so on and then fills the corresponding cells.
6. Type 'Total Absent' in cell A13, type '=SUM(B2:B11)' in cell B13, and then press 'Enter.' Type 'Total Possible' in cell A14, type '=10*50' in cell B14, and then press 'Enter.' SUM is an built-in Excel function that, as instructed here, automatically calculates the sum of numbers in cells B2 to B11 and then displays the sum in cell B13. In this case, 10 is multiplied with 50 using the multiplication operator * and then the result is displayed in cell B14. Here, 10 is the number of classes and 50 is the number of students.
7. Click in cell 'A1,' click 'Insert' menu and then 'Chart.' Click 'Standard Types' tab on the Chart Wizard.
8. Click 'Line' below 'Chart type,' select the chart type that is described as 'Line with markers displayed at each data value' on the right pane and then click 'Next.' Click 'Columns' radio button on the Data Range tab and then click 'Next.'
9. Click 'Titles' tab. Type 'Samples' in the Category (X) axis box and 'p' in Value (Y) axis box. Then click 'Next.' Click the 'Finish' button. P chart will be displayed on the Excel spreadsheet.

Read more ►

How to Sort a Name List by Same Names in MS Excel

1. Select the column of names that you would like to sort.
2. Open the 'Data' tab.
3. Under the 'Sort Filter' group, click the 'Sort A to Z' button. This button features the letter 'A' on top of the letter 'Z' on the left side, and an arrow pointing down on the right side. All of the names are now sorted alphanumerically, and cells containing the same names are right next to each other.

Read more ►

How to Show Numbered Columns

Microsoft Excel 2003
1. Click on the 'Tools' menu, and select 'Options.'
2. At the top of the window that appears, click the 'General' tab.
3. Under 'Settings,' click the 'R1C1 reference style' check box.
4. In the lower right-hand corner of the window, click the 'OK' button.
Microsoft Excel 2007
5. At the top left of the screen, click the round button that features the Microsoft Office logo.
6. Toward the bottom right of the information box that appears, click 'Excel Options.'
7. At the top of the window that appears, click the 'Formulas' tab.
8. In the 'Working with formulas' section, click the 'R1C1 reference style' check box.
9. In the lower right-hand corner of the window, click the 'OK' button.

Read more ►

How to Make Tables in Excel 2007

1. Launch Excel 2007 from your desktop. Type the information you would like to use as each column's 'header' in side-by-side cells within the same row.
2. Enter the data that you would like to be displayed in the rows directly under each of the 'headers' that you have typed out. Verify that you have entered the information correctly into the cells and that data is sorted as you would like it to be displayed in the table.
3. Click and drag your mouse over the cells that make up the table you are creating to highlight them. Click the 'Insert' tab towards the top of the Excel 2007 window. Click the icon labeled 'Table' that appears directly underneath the 'Insert' tab.
4. Click the empty box next to the text that reads 'My table has headers' in the window that opens and click 'OK' to continue. Excel 2007 will take the information that you have entered and create a table in the spreadsheet.

Read more ►

How to Use Excel As a Database

How to Set Up an Excel Spreadsheet as a Database
1. Identify the data you want to include in the database. Before you can set up a spreadsheet as a database, you need to know what information you will be tracking.
2. Use Row 1 as headings for each item that you plan to track. For example, if you plan to use Excel as a database for contact information, you would include name, address, phone number and other headings across Row 1.
3. Format Row 1. Change the formatting of Row 1 to separate the headings from the data in your database. To do this, click the number one next to Row 1, which highlights the entire row. Add your formatting, such as bold, center and a gray background.
4. Freeze the heading row. Use the freeze pane feature so that your headings will always appear at the top of the database.
In Excel 2007, click on the number two next to Row 2 to highlight the first row under the headings. Click the View tab. Click Freeze Panes and then Freeze Top Row.
In Excel 2003, click on the number two next to Row 2 to highlight the first row under the headings. From the menu bar, click Window>Freeze Panes.
5. Type in the data. Use one row for each database entry, keying in the information to match the heading information.
6. Sort the data in the database. After you enter the information into the database, you can view the data in a variety of ways. See the next section for instructions on how to sort the data in an Excel database.
How to Sort the Data in Excel 2007
7. Decide how you want to sort the information. For example, you might want the data listed alphabetically by last name, or you might want to sort the data by zip code.
8. Highlight the data in the database. Click on the upper left cell that is left of A and above 1. This highlights the entire spreadsheet.
9. Open the Sort window. Click the Data tab. Then, click the Sort button in the Sort Filter section. This opens the Sort window.
10. Select the data you want to filter. Under Column, select the data you want to sort beside Sort By. If you want to include a secondary sort (such as first name in case you have more than one person with the same last name), then select the heading for a secondary sort next to 'Then by.'
11. Choose sort order. The default (A to Z) is to sort alphabetically or lowest to highest number. To do the opposite, select Z to A under Order.
12. Click OK. The data in the database sorts in the way you instructed.
How to Sort the Data in Excel 2003
13. Decide how you want to sort the information. For example, you might want the data listed alphabetically by last name, or you might want to sort the data by zip code.
14. Highlight the data in the database. Click on the number 2 next to Row 2 to highlight the entire row. While pressing the Shift key, click on the number next to the last row with data to highlight all data without including the heading.
15. Open the Sort window. From the menu bar, click Data>Sort.
16. Select the data you want to filter. Under Sort By, select the data want to sort. If you want to include a secondary sort (such as first name in case you have more than one person with the same last name), then select the heading under 'Then by' for a secondary sort.
17. Choose sort order. The default (Ascending) is to sort alphabetically or lowest to highest number. To do the opposite, click the radio button to select Descending.
18. Click OK. The data in the database sorts in the way you instructed.

Read more ►