How to VLookup Two Values in Excel 2007

1. Open the Excel 2007 file that contains the table you want to work with.
2. Right-click on the column letter above the leftmost column on your table. Choose 'Insert' to create a new column.
3. Select the top cell in your new column. Type '=XXYY' into the cell, where 'XX' equals the cell reference to the first cell in the first column you want to search through with VLookup, and 'YY' is the second column. Press 'Enter' and you will see the entries in those columns compressed together into the new column. For example, if 'here' was in the first column, and 'there' was in the second, your new column would read 'herethere.'
4. Move your mouse over the bottom-right corner of the cell with your formula. Click the mouse and hold down the button,and then drag the mouse down until your reach the last row in your table. Release the mouse button and Excel will copy your formula down the entire length of the table.
5. Click on the top cell in your new column. Press 'Ctrl' and 'A' to select the entire table. Click the 'Home' tab at the top of the screen. Click the 'Sort and Filter' button, which you can find in the 'Editing' area of the ribbon. Choose 'Sort A to Z.' VLookup will not return good values if the first column is not ordered from lowest to highest.
6. Select any cell outside of the data table. Enter the following formula into the cell: =VLOOKUP('XX',YY:ZZ,2) where 'XX' is equal to the two values that you want to search for, 'YY' is the top-left cell in the data table, 'ZZ' is the bottom-right cell in the data table and '2' is the column number where you want to search for your result. When entering your search values, remember to place them together without spaces, so if you were searching for the number '1' in one column and the word 'trucks' in a second column, you would enter '1trucks.' Press 'Enter' to complete the formula and get your result.

Read more ►

How to Remove the Blanks from a Pivot Table Add

Remove Blank Cells
1. Open Excel and open 'Pivot Table Options' in the Excel ribbon by clicking anywhere on the Pivot Table.
2. Click 'Pivot Table Options' to open the options box so you can make changes to the Pivot Table settings.
3. Click the 'Layout Format' tab and look to the 'Format' section.
4. Uncheck the box next to 'For empty cells show:' and then click 'OK.' Blank cells will no longer show up in the Pivot Table add-in.
Remove Blanks from Rows
5. Click on the Pivot Table report to open 'Pivot Table Options' in the Excel ribbon.
6. Look to the 'Active Field' section of the ribbon and click 'Field Settings.'
7. Click the 'Layout Print' tab when the 'Field Settings' box opens.
8. Uncheck the box next to 'Insert blank line after each item label' and click 'OK.' Blank lines will no longer appear after rows.
Remove Blanks from Item Labels
9. Select the item that has a blank from the Pivot Table report.
10. Click the 'Design' tab located in the Excel ribbon and then click 'Blank Rows.'
11. Select 'Remove blank line after each item label' and blank lines will no longer appear after item labels in the Pivot Table.

Read more ►

How to Use the Same Color for Multiple Line Charts

1. Turn on your computer and open Excel. Microsoft Excel is a spreadsheet program that helps you maintain data and compile charts, tables and graphs in a few easy steps. This program is available for PC and Mac computers and we will look at creating a graph in Excel.
2. Enter the data you want to include in your chart in the Excel spreadsheet. This is easy to do. You will place your 'Y-axis' data on the first vertical column and the 'X-axis' data across the first horizontal row. Start The vertical column on the second row so that you can place the information in each data box. For example, if you want to review 'Department Revenues' by each 'Quarter' you would place the 'Department Name' going down the first column with each 'Quarter' labeled across the top row: Q1, Q2, Q3, Q4.
3. Go to the menu to 'Insert > Chart.' Click this and follow the onscreen menus to select 'Line Chart.' Highlight the areas in the data sheet you want to use in the chart. You will be given the option to either include the chart on your existing data page or open a new page with just the chart on it.
4. Look at the chart that pops up and review it to make sure it is correct. This will be a color chart with the data represented by different colored lines. Now you need to change the colors into patterns.
5. Right-click on the first data line, which may be red, green, yellow or blue. Select 'Format Data Series.' A box will appear with several tabs over your chart. Make sure you have selected the 'Colors and Lines' tab. Confirm that you are on the correct line by the color that is in the color box. Choose the pattern under the 'Dashed' tab. Click 'OK' when you are done customizing the line. You can maintain the color of the line or change it to black.
6. Repeat Step 5 for all data lines, making sure to choose a different pattern for each information line.
7. Save the file and print.

Read more ►

How to Create the Input Form on Excel 2007

1. Plan your worksheet. Think about the type of information you will be keeping in the spreadsheet and set up your columns.The column headers will be used as the form labels next to the input boxes.
2. Set up your spreadsheet. Type in the column headers. Set the column lengths. Do not merge any cells in the column headers. Excel will not be able to generate an input form from merged cells. You can always make the columns bigger by changing the column width property.
3. Add the Form button to the Quick Access toolbar. Click the drop-down arrow on the Quick Access toolbar and then click 'More Commands.' A dialog box appears. Click the drop-down under 'Choose commands from:' and choose 'All Commands.' Stroll through the list and find 'Form.' Highlight 'Form' and click 'Add,' then click 'OK.' The Form button is now in your Quick Access toolbar.
4. Highlight the column headers. Click on the 'Form' button in the toolbar. Excel will generate an input form that will allow you to enter in new information into the columns. Notice your column headers on the left-hand side of the input form next to the text boxes.

Read more ►

How to Find Blank Cells in a Column in MS Excel

Sort Method
1. Open the Excel file that you want to work with. Select the correct spreadsheet from the list of sheets at the bottom of the window. Click on any cell in the column where you are looking for blank cells.
2. Click the 'Data' tab at the top of the window. Locate the 'Sort Filter' area of the ribbon.
3. Select either the 'ZA' button, to sort the information from largest to smallest, or the 'AZ' button, which will do the opposite.
4. Click on any cell in the column you just sorted. Move your mouse to the very bottom of the selected cell, so that the pointer changes into a set of four arrows. Double-click the mouse button to immediately go to the last entry in that column. The blank cells are all located below this entry.
Filter Method
5. Open the Excel spreadsheet that you want to work with and choose the correct sheet from the bottom of the window. Click on any cell in the column where you are looking for blank cells.
6. Click the 'Data' tab and find the 'Sort Filter' area. Click the 'Filter' button. You will see drop-down arrows appear at the top of each column.
7. Select the drop-down arrow for your desired column to open up a small window. Click in the box next to 'Select All' to remove all of the check marks. Scroll to the bottom of the list and place a check mark next to 'Blanks.'
8. Click 'OK' and you will see the report get filtered to only show rows where your selected column has a blank cell.

Read more ►

How to Unlock a File in Excel 2007

1. Open the Excel file.
2. Click the 'Review' tab on the command ribbon.
3. Click the 'Unprotect Sheet,' 'Unprotect Workbook' or 'Unprotect Shared Workbook' button in the 'Changes' group.
4. Type the password if a dialog box opens. Click 'OK.'
5. Edit the unprotected file.
6. Press 'Ctrl S' to save this file.

Read more ►

How to Convert Military Time on an Excel Spreadsheet

1. Select the cell with military time. Place your mouse over the cell. Click your left mouse button once to select the cell. The cell is active when outlined with a black border. In addition, the status bar will indicate 'Ready' in the lower-left corner of the program window.
2. Right-click the cell. Open the short-cut menu by right-clicking the cell. Click 'Format cells...' from the short-cut menu. The 'Format Cells' dialogue box will open.
3. Choose a format. Make sure the current tab is 'Number.' Select 'Time' from the 'Category' section of the 'Number' tab. In the 'Type' section, select the time format you would like to use. Time formats marked with an asterisk adjust based on the regional settings of your operating system.
4. Accept the changes. Click 'OK' to change the military time to your selected format.

Read more ►

How to Make a Button for a Macro in Excel

1. Open the Excel 2010 spreadsheet where you want to add a macro button. Click the 'File' tab at the top of the screen and choose 'Options' from the list items on the far-left side of the screen.
2. Select 'Customize Ribbon' on the 'Options' window. Locate the 'Developer' entry in the rightmost column under the 'Customize the Ribbon' heading. Click the empty box next to 'Developer' to add that tab to the Excel ribbon. Click 'OK' to go back to the spreadsheet.
3. Click the 'Developer' tab at the top of the Excel spreadsheet. Locate the 'Controls' area of the ribbon and click the 'Insert' button. Under the 'Form Controls' heading, select 'Button.' The cursor will change to a ' ' sign.
4. Click and hold the mouse button anywhere on the spreadsheet. Drag the mouse to the left and you will see a box appear under the cursor. When the box is the size that you want the button to be, release the mouse button.
5. Select the macro you want the button to run when the user clicks it and click 'OK.' If you haven't created the code for the macro yet, you can type in a name for the macro and click 'New.' The VBA console will open up and you can enter in the code for the macro between the 'Sub' and 'End Sub' lines in the console.
6. Click the 'Design Mode' button on the ribbon and right-click the button. The mouse pointer will change to a ' ' sign and you can click and drag the button to another location. Click the 'Design Mode' button again to lock the button in place.
7. Right-click the button and choose 'Edit text' to change the text that appears on the button. Enter in whatever text you want and press 'Enter.'

Read more ►

How to Use the Multiple Rows Function in Excel

1.
In Excel, place your cursor in an empty cell located below the group of numbers that you would like to reference.
2.
Click on the 'Function' key located to the left of the Formula task bar.
3. Select 'Rows' from the Function list.
4. Click 'OK'.
5.
Highlight the section of rows containing the set of numbers you would like to add or enter the row and column reference points in the Formula bar.
6.
Click 'OK' to exit.
7.
Test the formula's accuracy by adding a couple of new rows. Highlight two rows within the middle of the range. To do this, left-click your mouse and hold down the 'Down-Arrow Key.' Release. The rows should still be highlighted.
8.
Right-click your mouse, then select 'Insert.'
9.
Verify that the cell containing the formula references all of the rows including the new row additions you just made.

Read more ►

How to Duplicate Words in Excel

1. Double-click the icon on the desktop or click 'Start,' 'All Programs,' 'Microsoft Office' and Microsoft Excel 2010' to launch Microsoft Excel.
2. Type a word into the first cell and press 'Enter' to accept the entry and move to the next cell down.
3. Type a different word that starts with a different letter into the second cell down the first column and press enter.
4. Type the first letter of either word previously entered into the column and Excel will offer to automatically complete the entry to match the previously entered cell. Press 'Enter' to accept the entry or keep typing to make a new entry.
5. Press the 'Delete' key and then press 'Enter' if typing a shorter version of the previously entered word to accept the information as typed instead of the automatic completion suggestion.
6. Click 'File,' 'Options' and click on the 'Advanced' menu choice on the left to review the settings for automatic completion. Turn automatic completion on by checking the 'Enable AutoComplete for Cell Values' box or turn it off by clearing the check box. Click 'OK' to save the setting.

Read more ►

How to Merge Cells in Excel

1. Start Microsoft Excel, and open the spreadsheet that contains the multiple cells you would like to merge into one cell. If you like, you can just start a new spreadsheet to perform the task of merging cells.
2. Select the cells that you would like to merge by clicking on the first cell with your mouse, holding down your left mouse button and dragging until all of the cells you want to merge are highlighted.
3. Right-click to bring up the Excel menu and click on 'Format Cells...' to open the 'Format Cells' dialog box. In the 'Format Cells' dialog box you can change all of the formatting options related to the cells and text you have within the cells, including the option of merging cells together.
4. Click the 'Alignment' tab at the top of the 'Format Cells' dialog box to show the alignment options for the currently selected cells.
5. Use your mouse to click the check box before the option 'Merge Cells' under the 'Text control' section of the 'Format Cells' dialog box.
6. Press the 'OK' button with your mouse to close the 'Format Cells' dialog box and merge the selected cells together in your Excel spreadsheet.

Read more ►

How to Write a Protected Excel Spreadsheet

1. Launch Excel and create or modify the data in an existing worksheet to suit your particular needs. Click on 'File,' and 'Save' to save your work.
2. Click on the 'Tools' menu. Select 'Protection' from the drop-down menu that appears, and then select 'Protect Sheet.'
3. Type in a password of your choosing when prompted, and click 'OK.' Remember this password as it will be required when making changes to the worksheet.
4. Protect the entire workbook of worksheets you have created by clicking on the 'File' menu, and then selecting 'Save As.' The Save As dialog box opens.
5. Click on the button labeled 'Tools.' Select 'General Options,' and the Save Options dialog box opens.
6. Type in the password of your choosing in the text box, and click 'OK.' This password will now be required to open the workbook file.

Read more ►

How to: PMT Function in Excel 2003

1. Click on an empty cell in the Excel 2003 worksheet and then type PMT( interest_rate, number_payments, PV).
2. Replace 'interest_rate' with the actual interest rate. For monthly payments, divide the interest rate by 12 and for weekly payments, divide by 52. For example if you have a 6 percent interest rate and will be making monthly payments, type '6%/12.'
3. Replace 'number_payments' with the number of payments to be made. For example, if you have four years of monthly loan payments, then type '4*12.'
4. Replace 'PV' with the loan's principal value. For example, if you have a $6,500 loan then type '$6500.' In this example, the final function should read 'PMT(6%/12, 4*52, 6500).'
5. Press the 'Enter' key to get the monthly payment.

Read more ►

How to Change MS Excel From English to German

1. Purchase the German language pack and download it if you haven't already done so. Install the language pack by double clicking its icon and following the on-screen prompts.
2. Open Microsoft Excel and click the 'File' tab at the top of the page.
3. Click the 'Options' link in the left pane and select 'Language.'
4. Click the drop-down menu under Editing Languages and select the German version you want to use for spell checking, according to your country preference. Click the 'Add' button next to the selection. Select the entry in the list and click the 'Set as Default' button to the right of the list.
5. Move down the window to the Choose Display and Help Languages section. Click the German entry in the Display Language list and click the 'Set as Default' button below the list. Repeat the process for the Help Language list.
6. Click 'OK' to close the window and apply the changes.

Read more ►

How to Tell Which Version of Excel Created a Spreadsheet

1. Locate the Excel file on your computer. For example, run Windows Explorer to find the file.
2. Look at the file extension for the file. The file extension is the set of characters that come after the dot. For example, you might see .xls.
3. Decide which version of Excel created the spreadsheet. If the file extension is .xlsx, Excel 2007 created the file. If the file extension is .xls, Excel 2003 (or an earlier version of the software) most likely created the file. There is an exception: Excel 2007 has the capability of being able to make backwards compatible (.xls) files.

Read more ►