How to Add Labels in Excel 2007

1. Open Microsoft Excel 2007 and the worksheet that contains the data you want to chart.
2. Select the data that will be charted and create a chart for it by clicking on the 'Insert' tab. Choose the type of chart you want to create from the 'Charts' group---column, line, pie, bar, area, scatter or other. Your chart will be placed onto the worksheet.
3. Make sure graph is selected, then click on the 'Layout' tab in the Ribbon, so you can add your data labels. Go to the 'Labels' group and click on the arrow by 'Data Labels.' Choose 'More Data Label Options' from the list of choices.
4. Select 'Label Options' in the 'Format Data Labels' dialog box.
5. Place a check mark by the options you want the data label to contain---'Series name,' 'Category Name' and/or 'Value' and remove any check marks you don't want.
6. Pick where you'd like the labels to appear---'Center,' 'Inside End' or 'Inside Base,' then click on 'Close.' The labels will be placed onto your chart.
7. Drag the labels manually on your chart if you'd prefer to place them in a different location.

Read more ►

How to Create a Formula in Excel 2003

1. Open Excel by double-clicking the Excel icon on your desktop. If you already have a spreadsheet made, you can create a formula using your existing data. If you do not have any data, just plug some numbers into the cells so you can try out these formulas.
2. Decide what your formula needs to calculate. Do you need to add up a column or row of figures? Do you need to divide one cell's value by another cell's value?
3. Start with an '=' (equal sign). Every formula will start with an equal sign. If you don't begin with this symbol, your formula will not work.
4. Put the first cell's name next. Each cell has a name assigned to it. 'A1' is the first cell in the worksheet. Find the cell name by looking at the row and column that it is in.
5. Use the correct function key: '*' for multiplication, '/' for division, ' ' is for addition and '-' for subtraction.
6. Insert the last cell name. This is just like a math problem. Use the same rules as a simple math problem and your formula will work for you.
7. Hit 'Enter.' Does your formula work? Double-check to make sure the components of the formula are correct.

Read more ►

How to Use Excel's VLOOKUP Function

1. Learn the syntax for VLOOKUP. It is VLOOKUP(search_value,table_array,column_index_number,range_lookup).
2. Specify search_value as the value to search for in table_array. Search_value may be a reference or a value. VLOOKUP will return the #N/A error value if search_value is smaller than any value in the first column of table_array.
3. Use table_array to provide the data to search. It must represent at least two columns and a reference to a range or range name may be used. The values in the first column may be logical values, numbers or text. The search is not case sensitive.
4. Provide the column_index_number. This is the column number of the value to be returned. For example, a 2 in this field will return the value in the second column. If column_index_number is less than one, VLOOKUP will return #VALUE!. If it is greater than the number of columns in the table being searched, VLOOKUP will return #REF!.
5. Enter a value for range_lookup if needed. This is a logical value that instructs VLOOKUP to perform an approximate or exact search. If range_lookup is TRUE or omitted, an exact match or the largest value less than search_value will be found. If range_lookup if FALSE, only an exact match will be found.

Read more ►

How to Select Visible Cells Only in Excel 2007

Using the 'Select Visible Cells Only' Button
1. Launch Excel and open the file that you'd like to work on.
2. Right-click on the menu bar and then click on 'Customize Quick Access Toolbar.'
3. Click the drop-down menu under 'Choose commands from' and then choose 'Commands Not in the Ribbon.'
4. Choose 'Select Visible Cells' from the list and then click the 'Add' button. Click 'OK.' The 'Select Visible Cells Only' button should now be visible on your Quick Access Toolbar.
5. Select a range of cells that contains hidden rows or columns. Click the 'Select Visible Cells Only' button. This removes all hidden cells from your selection.
Using the 'Go to Special' Window
6. Launch Excel and open the file that you'd like to work on.
7. Select a range of cells that contains hidden rows or columns.
8. Press 'F5.' Click on the 'Special' button. You may also do this by holding down the 'Alt' key and then pressing 'S.' This opens the 'Go to Special' window.
9. Press 'Y' to select 'Visible cells only.' Click 'OK,' or press 'Enter' to close the window. This removes all hidden cells from your selection.

Read more ►

How to Use Random Function in Microsoft Excel

1. Open Microsoft Excel.
2. Create two values between which you want a random number generated. For this example, enter '1' in cell A1 and '10' in cell A2, omitting the quotation marks in both cases.
3. Type the implementation of the random number function show below into cell A3:=RAND()*(A2-A1) A1The random function is designed to return a random value greater than or equal to 0 and less than 1. By taking the difference between the upper limit of the range (in cell A2) and the lower limit of the range (in cell A1) and then adding the lower range value, you generate a random number between those values.
4. Press the 'Enter' key to confirm the function entered in the previous step and return a random number within the specified range. To return only whole numbers, right-click the cell containing the function and select the 'Format Cells' option. Select the first tab on the resulting screen, choose 'Number' from the category list, specify a decimal place value of 0 and then click 'OK.'

Read more ►

How to Convert Word Files to Excel Files

Copy and Paste
1. Open the Word file in Microsoft Word.
2. Click and drag to highlight the information you wish to bring into Excel, press 'Ctrl-C' to copy.
3. Open Microsoft Excel and click into a blank cell.
4. Press 'Ctrl-V' to paste' the Word content into Excel. Table data from Word transfers the best. Paragraph text is pasted into the first cell on each row, with one paragraph per row.
Save the Word file as HTML First
5. Open the Word file in Microsoft Word.
6. Select Click the 'File' menu and select 'Save As.' Choose 'HTML' or 'Web page' in the Save As Type drop-down menu.
7. Click 'Save' and you will have a file with the .htm extension.
8. Open Excel to a blank spreadsheet, select the 'File' menu and choose 'Open.'
9. Select the HTML file you just saved from Word and click 'Open.' Excel will read this file, placing any table data in separate cells and individual paragraphs on new rows, but the text is formatted a little better than with the copy-and-paste method.
10. Click in cells containing numerical table data and you'll see that they are completely editable in Excel. Excel maintains the format from the Word HTML document very well.
Insert Word File as an Object
11. Open Excel to a blank spreadsheet.
12. Select the 'Insert' menu, choose 'Object...' and choose Microsoft Word document as the type.
13. Click the 'From File' button, navigate to the Word file you want to bring into Excel and click 'OK.'
14. Notice that this object is not editable in Excel. It appears as an image on the spreadsheet. If you double click the object, however, Microsoft Word will open and allow you to edit the file. Any changes made to the file in Word are automatically transferred to the object in Excel when you save and exit Word.

Read more ►

How to Recover a Lost Document in Microsoft Excel

Closed Without Saving
1. Click 'File' and then click 'Recent.'
2. Click 'Recover Unsaved Workbooks.' A folder of drafts will open in another window.
3. Double-click the file to open it.
4. Click 'File' and 'Save As' to save the file so you don't lose it again.
Previously Saved
5. Open the file on which you were working before you lost your changes.
6. Click 'File' and 'Info.'
7. Click the version that says '(when I closed without saving)' under Versions.
8. Click 'Restore' at the top of the window to save the version you lost.

Read more ►

How to Create Microsoft Excel Forms

Add Data Forms Command to Quick Access Toolbar
1. Open Excel to a default, blank worksheet. Click the 'Microsoft Office' button at the top left of the screen to open the 'File' menu and then select the “Excel Options” to open a new “Options” window.
2. Select “Customization” from the list on the left side of the window to view commands you can add to the Quick Access toolbar. To make locating the 'Data' Forms command easier, click the drop-down list next to “Choose Commands From” and select “Commands Not on the Ribbon.”
3. Select the “Forms” command from the drop-down list and press the “Add” button to add the command. Click “OK” to commit and return to the blank worksheet. The “Forms” command icon will be on the far right of the Quick Access toolbar.
Prepare the Worksheet
4. Enter column titles. Type “January” in cell A1, and “February” in cell B1, and use AutoFill to fill in the remaining months by selecting cells A1 and B1, then placing the mouse cursor in the lower-right corner of cell B1 until it changes to a “cross” shape. Hold the mouse button and drag across the spreadsheet to cell L1.
5. Set column width by clicking on cell A1 and dragging across to cell L1 to select and highlight cells. Locate the “Cells” section on the “Home” tab of the main menu and click the “Format” drop-down box. Select “Column Width” and set the width to “20” (or whatever width you want the columns to be) and then click “OK” to return to the spreadsheet.
6. Type the number “12” in cell A2 and “4” in cell B2. This step is necessary so Excel can distinguish column headings from the data.
Create the Data Entry Form
7. Place your cursor in the “A” column heading, click to select and drag across the spreadsheet to select all columns.
8. Click the “Form” icon you added to the Quick Access toolbar to create a data entry form that displays as a new window. The data entry form displays the months you entered as column titles.
9. Enter information as necessary to complete the worksheet. To start a new row, click the “New” button and continue entering information. Click “Close” when data entry is complete to return to the completed worksheet.

Read more ►

How to Find and Replace Text in Microsoft Excel 2003

1. Access the 'Find and Replace' menu. Scroll to the “Edit” tab on the command bar and select “Find.” A “Find and Replace” properties menu will open.
2. Type in the text you want to find. Under the “Find” tab, you can type in the text that you wish to find.
3. Type in the text that you wish to replace. Under the “Replace” tab, you can type in the text that you wish to replace.
4. Find and replace desired text. You can do this in two different ways. One way is by pressing the “Find All” button, which will find all occurences of the text you have typed, and another method is by using the “Find Next” button, which will find each separate occurrence of the text. Choose the desired option and, when you find text to replace, click on the “Replace” button to replace it. You can also click on “Replace All” to replace all occurrences of the text inside of the spreadsheet.

Read more ►

How to Override an Excel Password

1. Make a new spreadsheet. Leave it entirely blank. If it is a protected workbook, instead of just one sheet, make a new workbook.
2. Click on the top left corner of the protected spreadsheet's header bars (above the 1, left of the A) to highlight the whole sheet.
3. Copy the spreadsheet, either by pushing 'Ctrl' 'C' or by selecting 'Edit' from the menu bar and clicking on 'Copy.'
4. Click on your new blank spreadsheet. Click the top left corner to highlight the whole sheet, as in Step 2.
5. Paste the copied spreadsheet by pushing 'Ctrl' 'V' or by selecting 'Edit' from the menu bar and clicking on 'Paste.' You now have an identical copy of the protected spreadsheet---only it's not protected.
6. Rename the new spreadsheet to match the name of the old one (you may have to first rename or delete the old one to do this, if they are in the same workbook).
7. Manually copy all of the page setup settings, if you need to print this spreadsheet just like the old one. With the old, protected spreadsheet pulled up, select 'File' from the menu bar and click on 'Page Setup.' Go through the tabs and write down all the settings. Return to the new spreadsheet. Open the page setup box and enter the settings.
8. Repeat Steps 1 through 7 for each spreadsheet, if it is a whole workbook being copied. Rename the new workbook to match the old one when you are finished.

Read more ►

How to Make Pie Graphs in Excel

1.
Enter the data that you want your pie graph to represent into a spreadsheet in Microsoft Excel. Make sure to include column headings that explain what the data signifies. Try to be as descriptive with your data entry as possible because these elements will automatically feed into the pie graph. To help explain, we will construct an example in which we create a pie graph that breaks down a favorite color list by percentage.
2.
Click on the cell that contains your first column heading. Then, from the “Insert” menu, click on the arrow underneath the word “Pie” and the picture of a sample pie graph. Another menu will open.
3.
Choose the type of pie graph that you want to create with your data. For this example, we will choose the simplest of the two-dimensional options. Once you have made this selection, the pie graph will appear in your spreadsheet.
4.
Right-click on the pie graph and another menu will appear. Use the options in this menu to make any modifications that you wish to the format. For our example, we will change the wording in the title and apply labels to the pie graph.
5.
Save your Excel file so that you do not lose your work.

Read more ►

How to Make Address Labels From Excel 2007

1. Open Excel by clicking 'Start,' then 'All Programs,' then 'Microsoft Office,' and finally 'Microsoft Office Excel 2007.'
2. Click the 'Microsoft Office Button,' then select and click 'New,' and under the Template > Microsoft Office Online section click 'Labels.'
3. Click 'Mail and shipping,' then select and click 'Business.'
4. Select and double click the icon 'Avery 8160 Template' and the label template will begin downloading onto your computer system. Once it has completely downloaded, 30 labels will appear on a letter-size, 8.5-inch by 11-inch, Excel worksheet window.
5. Type the address information onto the address labels.
6. Print out the typed labels using the Avery 8160 labels.

Read more ►

How to Compare Two Excel Spreadsheets for Duplicate Rows

1. Open your spreadsheet and make sure you know what columns the data you want compared are in. This example will assume the data is in column A, and that the two lists to be compared are in two spreadsheet tabs, labeled Sheet1 and Sheet2 of the same workbook.
2. Enter the following formula in cell B1 of Sheet1. =COUNTIF(Sheet1A:A,Sheet2A:A). Hit Enter. If the record is unique (it doesn't appear on both lists), this will evaluate to 0.
3. Copy the formula from cell B1 through as many rows as you need on Sheet1.
4. Select columns A and B, and click on the Home tab (in Excel 2007) and select Sort and Filter. Choose to filter the results by Column B in ascending order. This will move all the results with a '0' in column B to the top, putting all your duplicate records at the bottom of the list.

Read more ►

How to Draw in Excel 2007

1. Open Microsoft Excel 2007.
2. Click on the 'Insert' tab. This is located near the top right-hand corner of the screen.
3. Click on the 'Line' drawing tool. The 'Line' tool is a good place to start, because you can use basic line shapes to draw a plethora of other custom-drawn shapes.
4. Click 'Lock Drawing Mode,' located underneath the main 'Line' heading. This option lets you connect several lines to predetermined nodes.
5. Click on the document where you want the line to start.
6. Drag your cursor across the spreadsheet to form a line and double-click when the line is long enough. Repeat this process as needed, and click on existing lines to create connection nodes. If you want to move a line or a node, click and drag the entire line or one of the circular nodes.
7. Press the 'Esc' key when you are finished connecting lines. This takes you out of the line editing mode.
8. Click on the 'Shapes' tool if you need to make a simple, pre-drawn shape.
9. Select the desired shape from the list of icons. The 'Shapes' tool gives you access to squares, circles, triangles and oblong shapes, each indicated by different icons underneath the 'Shapes' tool heading.
10. Click and drag the selected shape across your spreadsheet.
11. Click on the 'Home' tab when you are finished. This returns you to the main Excel window, letting you type text, adjust fonts and use Excel macros.

Read more ►

How to Make a Checkable Survey in Excel

1. Open a new Microsoft Excel 2010 worksheet.
2. Enter your desired text into the cells on the spreadsheet. You can click and drag the sides of the letters at the top of each column or the numbers at the left side of each row to resize the cells as you need to. This text is what the survey taker will see when they are using the survey. Leave spaces next to the text so that you can add your checkboxes.
3. Click the 'File' tab at the top of the screen and choose 'Options' from the list that appears on the left side of the window. Select 'Customize Ribbon' from the list on the Excel Options window. Look at the column that appears on the right side of the window and place a check mark next to 'Developer.' This will allow you to use the Developer tools with your spreadsheet. Click 'OK' to go back to your spreadsheet.
4. Select the 'Developer' tab at the top of the spreadsheet. Click the 'Insert' button on the ribbon and choose the small checkbox under 'ActiveX Controls.' Click and hold the mouse button anywhere on the spreadsheet, then drag the mouse down and to the right to create a box that will become your checkbox. Release the mouse button and the checkbox will appear.
5. Click on the checkbox to select it. Press 'Ctrl' and 'C' to copy it to your clipboard, then press 'Ctrl' and 'V' to paste a second copy of the checkbox. Press 'Ctrl' and 'V' repeatedly until you have created all the checkboxes that your survey needs.
6. Move your mouse over a checkbox until the pointer turns into a set of four arrows. Click and hold the mouse button down, then drag the checkbox to wherever you want it located on the spreadsheet. ActiveX objects exist above the spreadsheet level, so you do not have to place the checkbox within any particular cell or set of cells. Move each checkbox to your desired location.
7. Right-click a checkbox, move your mouse over 'Checkbox Object' and choose 'Edit.' You can now change the default text in the checkbox to whatever you desire. Repeat this process for every checkbox. If you don't want to use any text, click and hold the mouse button over either of the corners on the right side of the box. Drag the mouse to make the checkbox smaller until just the actual checkbox is visible, essentially hiding the text. Repeat this process for every checkbox.
8. Click the 'Design Mode' button in the ribbon to exit design mode. Your checkboxes will now be locked in place, and clicking on them will only add or remove a check. You will need to click 'Design Mode' again if you want to edit the boxes any further.

Read more ►