How to Do a VLOOKUP With Two Spreadsheets in Excel

1. Select the cell in which you want the returned value.
2. Click on the formula bar, the long empty box at the top of the screen.
3. Type =VLOOKUP(“text you want to match”,Be sure to include the double quotes.
4. Click the tab of the other spreadsheet, the one holding the searchable table.
5. Highlight the table.
6. Click back to the original spreadsheet. Now the location will be in the VLOOKUP function.
7. Click the formula bar again, add a comma after the table location and type in which column you want the value returned from. For example, if you want to return two columns to the right of the search column, type 3.
8. Type a closing parenthesis and press “Enter.”

Read more ►

How to Add Radio Buttons to Microsoft Excel for Data Collection

1. Define the mutually exclusive data to be collected. For example, if the spreadsheet is used to track clothing by size, the option buttons might be for small, medium and large sizes. This prevents mistyping information.Select the 'Developer' tab; from the 'Controls group,' press 'Insert > Option Button.' Place the cursor into the position where the upper left corner of the button and label are desired, and then drag a square, creating the frame. Do the same for the remaining option buttons to be created. Excel will name these 'Option Button 1,' '2,' and '3.'
2. Select 'Option Button 1,' and in the 'Developer' tab, 'Controls' group, select 'Properties.' A new window opens allowing the option button to be customized. From this pane, the font, color, status, control, caption and important functions can be controlled. Define the caption, select fonts, colors and size, and then create the functions for the button. Do the same for the remaining option buttons to be grouped.
3. Select the option buttons. Using the 'Drawing Tools>Format' tab in the 'Align' group, use the 'Align' button to position the option buttons as desired, then use the 'Group' button to create the Option Button Group.
4. Add the appropriate commands and cell links as needed for the option buttons, and then protect the cells over which the buttons are located. When the spreadsheet is completed, protect the Worksheet in the 'Review' tab, 'Changes' group, and the option buttons will be functional.

Read more ►

How to Insert Auto Numbering in a Header for Excel 2003

1. Run the Microsoft Excel 2003 application, and open the workbook that you want to edit.
2. Click 'View' in the menu bar, and then click 'Header and Footer' in the drop-down list.
3. Click the 'Custom Header' button.
4. Click the section of the header that you would like to add automatic page numbering to.
5. Click the '#' icon to add automatic numbering to the specified section.
6. Type any additional text that you want to display in the header, and then click 'OK' twice to save your header changes.

Read more ►

How to Use Multiple Regression in Excel

Excel for Multiple Regression
1. Enter the data you will use to conduct your regression analysis into an Excel spreadsheet. You can enter the data by hand or import a data file from another source, such as an ASCII file or another spreadsheet, into Excel.
2. Unlock the Data Analysis tool from the add-ins menu and install it. Open Excel, click “Tools” and select “add-ins” from the drop-down menu that appears. A smaller window opens that displays a set of options. Check the box next to “Analysis ToolPak” and click “OK.” The Data Analysis option appears in your Tools menu, ready for use. If you are using Excel 2007, you can access the Data Analysis add-in by clicking the Microsoft Office button in the top left corner of an open Excel workbook. Click the button and then click “Excel Options.” A new window opens, displaying a set of options on the left side. Choose “Add-ins,” select “Analysis ToolPak” and click “OK.”
3. Click the Tools menu in Excel and select Data Analysis (in Excel 2007, click the “Data” tab and click the Data Analysis button). A window opens that displays a menu of analysis tools. Scroll to “Regression” and click “OK.”
4. Enter the values for dependent variable (Y) and independent variables (X) by clicking on the applicable cells and columns in your Excel data sheet. After selecting the ranges of data to be entered for analysis, click 'OK.' Excel runs the procedure and display your results on a new worksheet.
5. Examine your summary output, starting with the regression statistics at the top of your output. Note the value of R-square, which tells you what percentage of the variability in the dependent variable (for example, average salaries) is explained by your regression model. Then note the values of the coefficients and corresponding t-statistics and significance levels. A t-statistic of 2 or greater indicates statistical significance, meaning that the relationship between that independent variable and the dependent variable is likely not due to random chance.

Read more ►

How to Compare Two Columns in Excel 2007

1. Activate Excel and open a new worksheet. Set up three different columns entitled 'A,' 'B' and 'C'. Allow for five data entries within each column so that you are working with a 3x5 grid.
2. Enter the data for the two known columns in 'A' and 'C'. Leave column 'B' blank for the time being, and make sure not to enter zeros in the 'B' column. Column 'B' needs to remain blank because this is where the formula will be entered and function.
3. Select the first cell in column 'B' and enter the following formula therein; =IF(ISERROR(MATCH(A1,$C$1:$C$5,0)),'',A1)
4. Navigate to 'Fill' from the 'Edit' menu and click 'Down'. The duplicate data will appear in column 'B.' Rinse and repeat as needed with new data sets.

Read more ►

How to Merge Columns From Two Tables

1. Open the Excel file that contains the two tables with the data you want to merge.
2. Select the cell where you want the merged column to start and type in a header for the column. Select the cell directly beneath the header. If you want the merged call to be part of one of the tables, right-click on the top of the column where it lists the column letter, directly to the right of where you want the new column to go. Choose 'Insert.'
3. Enter in the following: =CONCATENATE(XX,' ',YY) Make 'XX' equal to the first cell that you want to merge, and make 'YY' equal to the cell in the second column you want to merge with the first. Both 'XX' and 'YY' need to follow Excel's standard naming rules, which list the column letter followed by the row number, like 'A1' or 'C23.' Press 'Enter' when you are done typing in the formula. The two cells will merge in your new column.
4. Click on the cell that you just entered the formula into and move your mouse over the lower-left corner of the cell. When the mouse cursor turns into a plus sign, click and hold the mouse button. Drag the mouse down until you have reached the last row of the column and release the mouse button. The entire column will fill with data merged from the two original columns.

Read more ►

How to Make a Bar Line Graph in Excel

1. Launch Excel. Click the 'File' tab and select the 'New' option from the File menu to create a new Excel spreadsheet.
2. Enter the data for the graph into the cells. Enter the data you want to appear in the bar graph in one row and the data you want to appear in the line graph in an entirely separate row. Each piece of data must be entered into its own cell. For the bar graph, each cell will become its own bar. For the line graph, each cell will represent a specific point on the line.
3. Click on the first data-containing cell (upper-left corner) and drag the mouse cursor to the last data-containing cell (bottom-right corner) to highlight all of the cells that contain data.
4. Click the 'Insert' tab, followed by the 'Bar' option beneath the 'Charts' heading. Select the 'Stacked Bar' option from the Bar menu to transform the data into a bar chart. By default, the data entered in the first row appears as a blue bar, while the data entered in the second row appears as a red bar.
5. Click on the data series that you want to change to the line portion of the graph (the red bar, for example). Click the 'Design' tab, followed by the 'Change Chart Type' option beneath the 'Type' heading. Click the 'Line' option and select the type of line chart you want to use from the Line menu. The data series you selected will now be transformed into a line chart, giving you a bar and line graph.

Read more ►

How to Copy Column Values to Rows in Excel 2007

1. Copy all of the blocks in the column that you want to convert to a row. You can copy multiple blocks at once by holding down the 'CTRL' button as you click on each block in the column.
2. Right click anywhere within the highlighted blocks and select 'Copy.' Another method for copying the information in the columns is to press the 'CTRL' and 'C' buttons at the same time.
3. Choose the row where you want to paste the information, right click on the first block and select 'Paste Special.' A menu with several options will pop up.
4. Place a mark next to 'Transpose' in the bottom right section of the menu and click 'OK.' The information in the column will paste into the row you chose.

Read more ►

How to Use the LINEST Function in Vista and Excel

1. Highlight a set empty cells in your spreadsheet which composes 5 rows and 3 columns. Click on the 'Formulas' tab, and then select the 'More Functions' menu. Select 'Statistical' sub-menu, and then select the 'LINEST' function. The LINEST 'Function Arguments' dialog will open.
2. Click on the red arrow next to the 'Known_y's' argument, and highlight the column or row of data on your spreadsheet that represent the y-axis of your argument. Click on the downward pointing red arrow in the 'Function Arguments' dialog when done.
3. Click on the red arrow next to the 'Known_x's' argument, and highlight the column or row of data on your spreadsheet that represent the x-axis of your argument. Click on the downward pointing red arrow in the 'Function Arguments' dialog when done.
4. Type 'TRUE' without the quote marks in both the 'Const' and 'Stats' boxes. Click 'OK' to close the 'Function Arguments' dialog.
5. Highlight the formula in the formula bar, and hold down Control, Shift, and Press Enter. Starting with the top left cell and going from left to right, the following information will be calculated: slope, intercept, the standard error of all values, the standard error of all values except the last, the R-squared value, the standard error for the y estimate, the F statistic, degrees of freedom, the regression sum of squares, and the residual sum of squares.

Read more ►

How to Print the Gridlines of an Excel Worksheet

1. Start Microsoft Excel, and open a spreadsheet that you would like to print, having the gridlines appear on the printed copy of the spreadsheet.
2. Choose the 'File' menu and click on 'Page Setup...' to open the 'Page Setup' dialog box. Within the 'Page Setup' dialog box you can choose settings that apply to your whole Excel spreadsheet.
3. Click on the 'Sheet' tab at the top of the 'Page Setup' dialog box to display the sheet settings for the Excel spreadsheet you have open.
4. Find the 'Print' section of the 'Sheet' tab in the 'Page Setup' dialog box, located in the middle of the dialog box.
5. Use your mouse to click the check box in front of the option 'Gridlines' to add a check mark to the box. This will make sure that the gridlines will print when you print your current spreadsheet.
6. Press the 'OK' button with your mouse to close the 'Page Setup' dialog box and set your current spreadsheet to print with gridlines.
7. Print your spreadsheet by choosing the 'File' menu, clicking on 'Print' and then selecting the settings you wish to apply to your printed spreadsheet. When your spreadsheet prints, you will notice that the gridlines have printed, as well.

Read more ►

How to Chart Cells From Two Different Worksheets in Microsoft Excel

Create the Chart
1. Enter data in rows by using row headers with data under them. You can also choose to enter data in columns by using column headers with data under them.
2. Select the cells containing the data you will use for the chart.
3. Select the 'Insert' tab on the top menu, and click the arrow in the bottom-right corner of the Charts group to open the Insert Chart window.
4. Choose the type of chart in the Templates column of the Insert Chart window.
5. Select the chart using the images in the right box in the Insert Chart window, and click the 'OK' button.
6. Move your embedded chart by clicking anywhere in the chart to activate the Chart Tools option on the top menu.
7. Click 'Chart Tools' on the top menu, select the 'Design' tab, and click the 'Move Chart' button in the Location section of the menu.
8. Embed the chart in any sheet in the workbook using the pull-down menu in the Object In option in the Move Chart window, if you choose to. Click the 'OK' button when you are done.
9. Move the chart to a new sheet that you can name using the box next to the New Sheet option in the Move Chart window, if you want to. Click the 'OK' button when you are done.
Add Data from a Second Worksheet to the Chart
10. Click anywhere in the chart to activate it.
11. Click 'Chart Tools' on the top menu, and select the 'Design' tab.
12. Click the 'Select Data' button in the Data section of the Design tab to open the Select Data Source window.
13. Click the 'Add' button in the Legend Entries (Series) box of the Select Data Source window to open the Edit Series window.
14. Name the series in the Series Name box. You can choose to type in a name or link to a cell that contains the name.
15. Select the square box next to the Series Name box in the Edit Series window to link to the name of the series. If it is on another worksheet, click the tab of that worksheet, select the cell containing the name, and click the square box in the Edit Series window.
16. Select the square box next to the Series Values box in the Edit Series window to select the data range you want to include in your chart.
17. Click the tab for the sheet containing the data, and select the range of data you want to include on your chart, and then click the square in the Edit Series window. Click the 'OK' button to include the new data from a second worksheet in your chart.

Read more ►

How to Rotate a Pie Chart in Excel

1. Open the Excel worksheet containing the pie chart you wish to rotate.
2. Click on the pie chart. Doing so will display the 'Chart Tools' menu at the top of the Excel window. Within this menu you will see tabs labeled 'Design,' 'Layout' and 'Format.'
3. Select the 'Format' tab. Navigate to the 'Current Selection' group. Locate the 'Chart Elements' box and click on the arrow next to it. Click on the desired data point or series.
4. Return to the 'Format' tab. Select 'Format Selection' from the 'Current Selection' group.
5. Locate the 'Angle of First Slice' box. Move the slider to the position corresponding to the desired degree of rotation. Alternatively, indicate the angle at which you wish the first slice to appear by entering a value between zero and 360.

Read more ►

How to Change to R1C1 Cell Reference in Excel 2007

1. Open Microsoft Excel 2007.
2. Click on the 'Office' button in Excel.
3. Click on 'Excel Options.'
4. Click on 'Formulas,' in the left-hand pane of the 'Excel Options' box.
5. Check the 'R1C1 reference style' check box under 'Working with formulas' to enable 'R1C1' style referencing. Clear this check box to use 'A1' style referencing.

Read more ►

How to Insert a Check Box on an Excel Spreadsheet

1. Click the 'Developer' tab.
2. Click 'Insert.'
3. Select 'Check Box' under the Active X controls.
4. Click where you want the check boxes to appear on the spreadsheet.
5. Go back to the 'Developer' tab and select 'Design Mode.' This allows you to design the check boxes.
6. Go back to the 'Developer' tab and select 'Properties' to change any properties for the check boxes.

Read more ►

How to Enable Excel Data Analysis in Office 2007

1. Open the Microsoft Excel 2007 application on your computer and then click on the 'Microsoft Office' button.
2. Click on the 'Excel Options' button from the bottom of the application and then click on the 'Add-ins' button.
3. Select the 'Excel Add-ins' option from the 'Manage' box and then click on the 'Go' button.
4. Click on the box next to the 'Analysis ToolPak' field so that it's selected and then click on the 'OK' button.
5. Click on the 'Yes' button if you are prompted to install the add-in for your computer. Once the add-in is loaded, click the 'Data Analysis' button from the 'Data' tab.

Read more ►