How to Create a Pivot Table to Consolidate Multiple Data Ranges

1. Open the Microsoft Excel application that contains that data you want to create a PivotTable for on your computer. Click on any blank cell within your worksheet.
2. Click on the 'Data' option and then click the 'PivotTable and PivotChart Report' option for Excel 2003. Press the 'ALT', 'D' and 'P' keys at the same time for Excel 2007. The PivotTable and PivotChart Wizard will then appear.
3. Click on the 'Multiple consolidation ranges' option and then click on the 'Next' button. Click on the 'I will create the page fields' option and then click on the 'Next' button.
4. Click on the 'Collapse Dialog' option and then select the first data range you want to add from your worksheet. Click on the 'Add' button to add the data range.
5. Click on the 'Collapse Dialog' option again to select another data range that you want to add. Click on the 'Add' button again to select your second data range. You can then continue to add as many data ranges as you want.
6. Click on the '0' option below the 'How many page fields do you want?' field and then click on the 'Next' button. Select a location for your PivotTable to be placed in your worksheet and click on the 'Finish' button.

Read more ►

How to Label the Columns of Microsoft Excel 2007 with Letters

Windows version of Excel 2007 or higher
1. Open Excel. You do not need to bring up a specific file. Just click to open the program, which will automatically open a blank spreadsheet.
2. Select the Office symbol button in the top left corner of Excel to open the menu. At the bottom right, click on the 'Excel Options' button. A new window will appear.
3. Click on 'Formulas' from the menu on the left and look mid-page under the heading 'Working with Formulas.' Uncheck the first item ('R1C1 Reference Style'), and you will return to columns labeled with letters. To change back to numbers, simply check the box once again. Click on 'OK' to close the window and save your selection.
Mac version of Excel
4. Open Excel and click on the 'Excel' menu option in the furthest top left corner. Scroll down and select 'Preferences' from the menu selections. A new window will pop up.
5. Click 'General' under the 'Authoring' heading to bring up the selections in the next window.
6. Remove the check from the box beside 'Use R1C1 reference style' to return column labels to letters, instead of numbers. Select 'OK' at the bottom right to save your options and close the window.

Read more ►

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 ►