How to Use Vlookup in Excel 2007

1. Open MS Excel 2007. Click on the Windows icon on the top left of the screen. Scroll to and click 'Open.' Search your computer for the file you need to find values on. Click on it to select it and click on the 'Open' button to open the file.
2. Make sure there are definite options of things to search for. For example, if you want to list the city of people who contacted your organization, but you only have the cities listed by code, you will use a VLOOKUP to look up which city corresponds to that code.
3. Build a table array which lists your options somewhere else on the spreadsheet. In the option above, list all of your city codes in one column and the corresponding cities in the column next to it.
4. Add a column in your main spreadsheet to list the values you are looking up by right-clicking on the existing column to the right and clicking 'Insert.' In this city example, your column could be titled 'City Name.'
5. Type '=VLOOKUP(' in the first cell where the values will be located. Click on the cell with the value you are looking up (The city code in this example). Type ',' after you click in the cell.
6. Highlight the array you created in the other area of your spreadsheet with what you are looking up and their values. Type ','.
7. Type the number of the column in the array the information you want to display is in. In this example, you will have two columns of data in your array, the city code on the left and the city name on the right. For this array, you would type '2' after the comma in Step 6 to tell Excel that the information to display is in column 2 of the array. Type another comma.
8. Type 'false' after the comma in Step 7. This will tell Excel that you only want to return the data if it has an exact match. Placing 'true' in this spot will return the closest value to what you are looking up and you don't want that. Type a ')' and press 'Enter.' The cell will now contain the city name for that city code.
9. Click in the cell with the VLOOKUP equation. Click into the formula bar and place the cursor before the first cell in the array portion. Type '$' in front of all the column letters and row numbers in the array. (For example, if the array is G1:H125 you would type '$' before G, 1, H, and 125 to look like $G$1:$H$125). Press 'Enter.'
10. Right click in the VLOOKUP cell and click on 'Copy.' Highlight all the other cells in the column, right-click, and click on 'Paste' to paste the equation into all the other cells.

Read more ►

How to Find the Percentage of Two Cells in Microsoft Excel

1. Open Microsoft Excel.
2. Create your first data point. For the purpose of this example, assume you want to find out what percentage of your total business income was earned in the months of March and April. Enter 'March' (without quotation marks) in cell A1, then press the 'Tab' key and enter '8,500' (again without quotation marks) in cell B1.
3. Create your second data point. Press the 'Enter' key to move your cursor to cell A2. Enter 'April,' press the 'Tab' key, then press '8,750' in cell B2.
4. Add your data points. Move your cursor to cell B3, directly below your two number values. Enter the following formula into this cell to sum the two values:=SUM(B1:B2)
5. Calculate the percentage of the total. Double-click cell B3 to edit its contents, then add a '/' character and the total value by which the preceding cells should be divided. For this example, assume your annual business revenue is $115,000. Your formula should now look like this:=SUM(B1:B2)/115000The formula tells Excel to take the sum of the values in cells B1 and B2 and divide them by the total yearly revenue value of $115,000. Press 'Enter' after inputting the revised formula to return your final percentage (0.15, or 15 percent, in this example).

Read more ►

How to Create a Custom Sort in MS Excel

1. Open the Excel worksheet.
2. Click and drag to select the range of cells you wish to custom sort.
3. Click the 'Home' tab on the command Ribbon.
4. Click the 'Sort Filter' button in the 'Editing' group. A list of options appears.
5. Click the 'Custom Sort' option. A dialog window opens. The categories include 'Column,' 'Sort On' and 'Order'
6. Select or deselect the check box for 'My data has headers.'
7. Click the arrow next to the 'Sort by' text box in the 'Column' category. Select the 'Column.'
8. Click the arrow in the text box under 'Sort On.' Select the preferred option, such as values or cell color.
9. Click the arrow in the text box under 'Order.' For example, 'Smallest to Largest' for values, or 'A to Z' for text.
10. Click the 'Options' button. A small dialog window opens. Select the orientation for sorting, such as 'Sort left to right' or 'Sort top to bottom.' Click 'OK.' This window closes.
11. Click 'OK.' The 'Sort' window closes. The range of cell contents display the new order.

Read more ►

How to Calculate Fractions in Excel 2007

1. Open the Excel spreadsheet you want to use to calculate fractions.
2. Click an empty cell and enter a number in decimal format, for example, '1.875.' Press 'Enter.' The number should show up exactly as you entered it.
3. Click the cell again and then click the 'Home' tab on the menu ribbon. Click the down arrow in the text formatting window in the 'Number' group. The text formatting window might display 'General' initially or something else. Click 'Fraction' from the drop-down list. The number displays as a fraction now.
4. Add, subtract, multiply, divide or otherwise create any equation using fractions in the formula bar. For example, to add two fractions, enter '=1/2 1/4.' Excel will display '3/4' in the cell if the cell is formatted as a fraction.

Read more ►

How to Lock Column Headers on Microsoft Excel

1. Open a worksheet in Microsoft Excel. Add row and column header text as desired.
2. Click on the column letter on the right side of the column you want locked. This highlights the entire column. Highlight a group of columns by clicking on the first column and dragging the mouse, with the left button depressed, across the desired columns to highlight.
3. Click on the 'View' tab if using Microsoft Excel 2007. Under the Window group item in the menu, click on the arrow for 'Freeze Panes.' Select the option to 'Freeze First Column' to lock only the one column highlighted. Select the option to 'Freeze Panes' if you want to freeze a group of panes at one time.
4. Click on the 'Window' menu if using Microsoft Excel 2003 and select 'Freeze Panes' to lock one or a group of columns.
5. Repeat the process listed in steps 1 through 4 to unlock the column by selecting 'Unfreeze Panes.'

Read more ►

How to Make a Histogram From a List of Data in Excel

1. Open a new Microsoft Excel 2010 spreadsheet. Select cell 'A1' and type in 'Input Range.' Select cell 'B1' and type in 'Bin Range.'
2. Click on cell 'A2' and type in the first value in your data set. Enter the rest of the data set into the cells down the first column.
3. Select cell 'B2' and enter your bins down the second column. Each actual bin number will stand for the top value in that bin's range. So, if you enter bins of '20,' '40' and '60,' the ranges will be anything below 20, 21 to 40, and 41 to 60, respectively. Excel will automatically add an additional bin called 'More,' which will collect every value higher than your highest bin number. The More bin will appear even if your highest bin is equal to or greater than your highest value. In addition, you have the option of not entering any bins and allowing Excel to automatically determine your bins.
4. Click the 'File' tab at the top of the screen and click 'Options' from the menu that appears on the left side of the Excel window. Click on 'Add-Ins' on the left edge of the Options window, then click the 'Go' button. Click the check box next to 'Analysis ToolPak' and click 'OK.' This will load the add-on that is required to create a histogram. You will only need to load this add-on once, as Excel will now load it automatically.
5. Select the 'Data' tab and click the 'Data Analysis' button. Select 'Histogram' and click 'OK.' The Histogram window will appear.
6. Click the radio button next to 'Output Range' and then click on cell 'C1.' Click the check box next to 'Chart Output.' This process will begin the histogram table at cell 'C1' and create the actual histogram to the right of the table on the spreadsheet.
7. Place your cursor into the 'Input Range' field and press 'Backspace' to erase whatever was in the box. Click and hold the mouse button over cell 'A1' on your worksheet, then drag the mouse down until you reach the last value in your data set and release the mouse button. If you entered your own bins on the worksheet, repeat this process with your bins in the 'Bin Range' field.
8. Click the check box next to 'Labels' as your data has labels in the first row, then click 'OK.' Excel will display your histogram.

Read more ►

How to Unlock an Excel Workbook

1. Open the Excel workbook you want to unlock.
2. Select 'Tools,' 'Protection' and 'Unlock Workbook' from the toolbar. If you added a password initially (when you locked the file), you will be prompted to type it in. Click on 'OK' in the 'Unprotect Workbook' box. The workbook will be unlocked.
3. Select 'Tools,' 'Protection' and 'Unprotect Shared Workbook' from the toolbar to unlock a shared workbook. Enter the password into the 'Unprotect Sharing' box if you inserted one initially. Then click on 'OK.' Click on 'Yes' to remove the workbook's shared settings if you are prompted to.
4. Select 'File' and 'Save As' from the toolbar to remove the password from a password-protected workbook file. Click on 'Tools' and 'General Options' in the 'Save As' dialog box. Remove the password from the 'Password to open' field (to allow users to open it) and/or 'Password to modify' field (to allow users to modify it) and click 'OK.' Click on 'Save' to save the workbook without a password.

Read more ►

Excel 2003 Menu Bar Tutorial

1. Click 'File' to view options that are related to file operations. You can create new files, open files, save files, print files and change file properties from this menu.
2. Click 'Edit' to view common options for performing Excel edits. This menu contains options that allow you to copy and paste data, find and replace text strings and delete cells or cell contents.
3. Click 'View' to change the Excel 2003 display. You can use this menu to change the Excel toolbars, document headers and footers and change the screen view.
4. Click 'Insert' to add something new to your document. You can use this menu to add blank cells, columns or rows. You can also add formula functions and graphics to a cell via this menu.
5. Click 'Format' to change the appearance of elements in your document. You can change the format of individual cells, rows, columns and entire worksheets using the functions under this menu heading.
6. Click 'Tools' to access a menu of tools that you can use to augment the contents of your workbook. The Tools menu includes entries for spell-checking, sharing your document and protecting cells with a password. The menu also contains entries for changing Excel options and editing the Excel AutoCorrect feature.
7. Click 'Data' to perform categorical edits on your worksheet's data. You can sort data, create forms and filters, insert pivot tables and import external data to your spreadsheet.
8. Click 'Window' to change the active window, freeze panes in your spreadsheet or split your view of the spreadsheet into multiple parts.
9. Click 'Help' to view Excel 2003 help documentation.
10. Click the 'Double Arrow' icon at the bottom of any menu to view all menu items. When you first click on the menu, the menu only displays items that you have used recently.

Read more ►

How to Compare Two Lists in Excel 2007

1. Open a new workbook (click the 'Office' button, 'New' and 'Blank Workbook') as well as the two lists you intend to compare. In the new spreadsheet, make headers across the top: 'Original List,' 'Comparison List' and 'Missing?'
2. Copy and paste the original list into the new spreadsheet. Move over a column and paste the comparison list into the spreadsheet.
3. Move your cursor to cell C2, the first empty cell in the 'Missing?' column. Type the function into this cell. Type in '=isna(match(' then either click the first cell in the 'Comparison' list or type B2 (if that's the cell number). Type a comma, then either click and drag to select the whole 'Original List' or type the range in--this might be something like 'A2:A18.' Type in another comma, then the word 'FALSE.' Finally, type two closed parentheses. The whole function should look like =isna(match(B2,A2:A18,false)).
4. Press 'Enter.' The function should return a 'TRUE' if the item in cell B2 does not appear in the list under column A, and 'FALSE' if it does.
5. Click to select the cell with the function in it. Change the cell range of the 'Original List' so there are two dollar signs in it. It should now read A$2:A:$18. These dollar signs will prevent Excel from changing that range in the next step.
6. Press 'Enter' again, then click and select the function again. Click the small black fill handle on the bottom-right corner of the cell and drag that cell down to the last row in the 'Comparison List.''
7. Look at the list you just created. If the lists are similar, most of the items under 'Missing?' should say FALSE.' Any items marked 'TRUE' don't appear in the 'Original' list.

Read more ►

How to Copy Paste Filtered Data

1. Drag your mouse over the filtered data so it is highlighted. This identifies to the program which data you wish to copy.
2. Press the 'F5' button on your computer keyboard, if you are using Excel 2007 or later. A pop-up window will appear. Alternately, you can click the 'Go To' button in the 'Find Select' section of the Excel toolbar. This step is not necessary for versions of Excel prior to 2007.
3. Click the 'Special' button on the pop-up window.
4. Click the 'Visible cells only' option, and press 'OK'. This instructs Excel to only select the data included in the filtered results. Hidden data from the original unfiltered data set is ignored. You are now ready to copy.
5. Press the Control-C keyboard combination to copy the data. Alternately, select the 'Copy' button on the Excel toolbar.
6. Click on the spreadsheet, file, or other document where you wish to paste the filtered data.
7. Press the Control-V keyboard combination to paste. The filtered data is pasted into the new file.

Read more ►

How to Copy a Microsoft Excel Sheet

Excel 2007
1. Click on the tab of the worksheet you want to copy.
2. Right-click with your mouse on the tab, then choose 'Move or Copy.'
3. Select where you want the worksheet to go. Use the drop-down menu to select a workbook.
4. Click on the check box next to 'Create a Copy.'
5. Click on 'OK.'
Excel 2003
6. Open the worksheet you want to copy.
7. Click on 'Edit,' then click on 'Move or Copy worksheet.'
8. Click on 'Create a Copy' in the dialog option box.

Read more ►

How to Format Number Currency in Excel 2003

1. Click 'Start' in Windows, select 'Microsoft Office' and then click 'Microsoft Office cell 2003.'
2. Click the cell that contains the number you want to display in the currency format.
3. Click 'Cells' on the 'Format menu.'
4. Click 'Currency' in the 'Category' box on the 'Number' tab.
5. Click the currency symbol you choose in the 'Symbol' list.
6. Enter the number of decimal places that you want to display in the 'Decimal Places.'
7. Select the display style for negative numbers in the 'Negative Numbers'.
8. Click 'File' and then click 'Save' to apply the change to your Excel spreadsheet.

Read more ►

How to Draw a Histogram in Excel 2007

1. Type the word 'Bins' into cell A1.
2. Type the word 'Frequencies' into cell B1.
3. Enter the minimum values for each histogram bucket in column A, under the word 'Bins.' For example, in cell A2 begin with '0.2,' and continue down the column with increasingly large numbers.
4. Enter the number of data items that belong in each given bin in column B, under the word 'Frequencies.' For example, if the first two bins are 0.2 and 0.3, and there are 12 data items that fall between the values 0.2 and 0.3, then type '12' to the right of 0.2 in column B.
5. Select all the data (including the header title) in column B. To select it, left-click on the title, and drag the mouse to the bottom of the list.
6. Click the 'Insert' ribbon.
7. Click the 'Column' button in the Charts section of the Insert ribbon.
8. Click the first icon on the top left of the Column drop-down. It looks like a flat bar graph.

Read more ►

How to Design a Crossword Puzzle With Excel 2003

Designing the Crossword Puzzle
1. Open a new document in Microsoft Excel 2003.
2. Select the area of your spreadsheet where the crossword puzzle will go. (Make sure to keep this same area highlighted throughout all of the steps in this first section.) Then right-click over the selected area and choose 'Format Cells' from the pop-up list.
3. Select the 'Alignment' tab. Then choose 'Right Indent' for the horizontal text alignment and 'Top' for the vertical text alignment.
4. Select the 'Font' tab. Then pick 'Arial' for the font, '10' for the font size and 'Superscript' for the effects and click 'OK.'
5. Select 'Format,' 'Row' and 'Height' from the toolbar. Type '20' into the 'Row Height' dialog box and click 'OK.'
6. Select 'Format,' 'Column' and 'Width' from the toolbar. Type '4' into the 'Column Width' dialog box and click 'OK.' The selected cells should now be square.
7. Select the 'Border' button from the 'Formatting' toolbar. Choose 'All Borders' to create a border around each selected cell.
Adding Content
8. Place your cursor in the cell where you want to add a number. Type in the number you want. Then press 'Enter' or use your arrow keys to move to the another cell in the crossword.
9. Insert additional numbers into the desired cells until they are all in.
10. Select any cells within the crossword puzzle that you want to shade (areas where letters/words will not be entered when printed). To select multiple cells at the same time, hold down the 'ctrl' key on your keyboard as you select each cell.
11. Click on the arrow beside the 'Fill (Color)' button in the 'Formatting' toolbar and choose black. The selected areas will be shaded.
Inserting the Clues
12. Place your cursor outside the crossword puzzle (below it) to insert the clues for the crossword puzzle. Type in 'ACROSS' and bold the text by using the 'Bold' button in the 'Formatting' toolbar. Then press 'Enter.'
13. Type in the number '1' and your first clue for the 'ACROSS' category and press 'Enter.' Continue typing in all of the other clues until you are finished.
14. Next to the Across clues, enter the Down clues in a different column. Type in the word 'DOWN' and bold it. Then press 'Enter.'
15. Type in each number and clue for the 'DOWN' category, pressing 'Enter' after each one. Continue typing in other numbers and clues until they are complete.
16. Save your work by selecting 'File' and 'Save' from the toolbar. Name your crossword puzzle file and click 'Save.'

Read more ►

How to Use a Reference in a Different Worksheet in Excel

1. Open the Excel workbook with multiple worksheets that you want to use.
2. Click the tab at the bottom of the Excel window to select the worksheet in which you want to enter a formula.
3. Click an empty cell to insert a formula. Type an equal sign, an Excel function and an open parenthesis to begin building your formula. For example, if you want to add numbers from different worksheets, enter:=SUM(
4. Enter a cell number or another function from the current worksheet you want to work with. For example, if you are adding a number from cell A2 in the first worksheet to a number in cell A2 in the second worksheet, click or type cell 'A2.' Follow that with an operator. For example:=SUM(A2
5. Click a different worksheet tab at the bottom of the Excel application window. Click the cell you want to add to the formula next or type the cell address. Excel will insert the name of the worksheet you are referencing along with the cell number. For example:=SUM(A2 Sheet2!A2
6. Type a closing parenthesis when you are finished building the formula and press 'Enter.' Excel will return you to the original worksheet. Your complete formula might look like this:=SUM(A2 Sheet2!A2)

Read more ►