How to Recover an Unsaved Spreadsheet in Excel

1. Open Excel after your computer has crashed or froze. The Document Recovery box will appear on the right side of the application after you open it.
2. Scroll over the recovered file from that task pane and then click on the arrow that appears. A menu will then pop up.
3. Click on the “Open” option to open the recovered file. You can also click on the “Save As” option to save the file so that you don’t risk losing any data again.
4. Close out of the file if it seems to need to be repaired in any way still. Click the “File” option from the top toolbar menu and then click on the “Open” button.
5. Locate the recovered file that you just saved and click on it. Click on the drop-down menu option. Click on the “Open and Repair” option and any damage to the file should be fully repaired.

Read more ►

How to Convert Excel 2007 Files to Excel 2000

If You Have Excel 2007
1. Open Microsoft Excel 2007 on your computer. The program file is typically located under the 'Microsoft Office' folder under the 'All Programs' option of the 'Start' menu.
2. Open the Excel 2007 workbook you wish to convert. To open a workbook, select the 'Open' option from the Microsoft Office button in the top left-hand corner of the program. Select your file and click the 'Open' button to continue.
3. Select the 'Save As' option from the Microsoft Office button. Select the 'Excel 97-2003 Workbook' option under the 'Save a copy of the document' section.
4. Name your file and save it to your preferred destination. The workbook is now compatible with Excel 2000.
If You Have Excel 2000
5. Open the Microsoft Excel conversion tool website in your web browser (see Resources). Review the system requirements to ensure your system is eligible for the compatibility pack.
6. Click the 'Download' button toward the top of the page to begin downloading the file. If the download does not start automatically, click the 'Start Download' link on the page you are redirected to. Click the 'Save File' button when prompted.
7. Open the executable file once it finishes downloading. By default, this file is named 'FileFormatConverters.exe.' Click 'Run' when prompted to begin the installation process.
8. Accept the license terms by clicking on the box next to the 'Click here to accept' text at the bottom of the screen. Click 'Continue' to begin extracting the files. Click 'OK' when the extraction is complete.
9. Open Excel 2000 from your 'Start' menu. Open the Excel 2007 file by selecting the 'Open' option from the 'File' menu and locating your file. Excel will automatically begin converting the file for use in Excel 2000. Note that the file is converted for one-time use and will need to be reconverted each time it is opened.
10. Click the 'Save As' option from the 'File' menu to permanently save your Excel 2007 file as an Excel 2000 file. Select the 'Microsoft Excel Workbook' option from the 'Save as Type' section and name your file. This file will be permanently compatible with Excel 2000.

Read more ►

How to Make an Organizational Chart in Excel

1. Open Excel to a new workbook. Choose the 'Insert' tab, and in the Illustrations group, select 'SmartArt.'
2. Choose 'Hierarchy' in the Choose a SmartArt Graphic gallery. Then select the organization chart layout and click 'OK.'
3. Select a box and type in your text by clicking the [Text] label in the box and either pasting or typing the information to display in this box.
4. Add more boxes to complete your organization chart. Click the box closest to where you want to add the box and choose from the following: 'Add Shape Before' adds the box on the same hierarchy level, but just before the box you've clicked; 'Add Shape After' also stays on the same hierarchy, but adds the box after; 'Add Shape Below' adds a box one level down and 'Add Shape Above' adds a box one level up.
5. Add assistants, as appropriate, by clicking box on the hierarchy and selecting 'Add Assistant.'
6. Modify the relationships among the boxes by denoting them with the appropriate line. Click the 'Format Shape' found on the shortcut palette. Select 'Line Style,' and then select either a dashed or solid option.
7. Define the correct layout of the boxes on chart. Select 'Standard' to display the boxes as hierarchical levels. Choose 'Both' to display reporting relationships, with boxes on either side of a vertical line. Select 'Left Hanging' to display the reporting relationship with all boxes on the left side of the line, which is ideal if you are displaying multiple divisions on one chart.
8. Modify the colors of the boxes to further clarify your hierarchy. Select the SmartArt graphic, and then select 'SmartArt Tools' from the Design tab. From the SmartArt Style groups, select 'Change Colors.' Then select the colors to use for your organization chart.
9. Save the chart by choosing the 'Save' icon and naming the file.

Read more ►

How to Plot Points on a Grid

1. Open Excel or another spreadsheet. Enter headers in cells A1 and B1 that correspond to your variables. For example, if you want to plot age against income, put 'Age' in cell A1 and 'Income' in cell B1. The A column will correspond to the X axis and the B column to the Y axis. (You can enter the data in any cells; just be consistent.)
2. Enter the appropriate data. In our example, enter the data for age and income in the appropriate columns, starting in row 2 and continuing with one row per person.
3. Plot the data. In a new cell, click 'Insert,' 'Scatter' and then click on the graph in the upper left of the panel of graphs.
4. Modify the plot. In the Charts Layout section, scroll down with the arrows until you get to layout 10, and click on it.
5. Modify the axes. Inside the graph, click on 'Axis title' on the horizontal axis and enter 'Age.' Then click on 'Axis title' on the vertical axis and enter 'Income.'

Read more ►

How to Extract the First Word of a String in Microsoft Excel

1. Click in the cell to the right of the one you want to edit. For example, click in B2 if you want to extract the first word from cell A2.
2. Enter the following formula and then press the 'Enter' key:=LEFT(A2,FIND(' ',A2)-1)This tells Excel to find the first word in the word string in cell A2 and to extract it to cell B2 for our example.
3. Copy the formula and apply it to all cells in the preceding column. To do that, hover your mouse over the black square in the lower right corner of the cell. When your mouse pointer changes to a plus symbol, click your mouse and drag down.

Read more ►

How to Make Column A the X

1. Select the data you want to use for both the x- and y-axes by clicking and holding the mouse button while selecting the cells.
2. Select the 'Line' button from the chart options on the 'Insert' tab. Select the style of the line that you want to use. In the resulting graph, Excel will have drawn columns A and B as lines.
3. Right-click the graph. After a menu appears, click the 'Select Data...' option to open the 'Select Data Source' dialog. Both 'Series 1' and 'Series 2' are listed under the 'Legend Entries' list.
4. Click 'Edit' under the 'Horizontal Axis Labels' list to open the 'Axis Labels' dialog. Click the icon that displays a red arrow, and then highlight the column on the spreadsheet that you want to denote as the x-axis. In this example, this is all the numbers in column A. Click 'OK' when finished.
5. Select 'Series 1' in the 'Legend Entries' list. Click the 'Remove' button, and then click 'OK.' The x-axis now denotes the quantities in column A.

Read more ►

How to Restrict Data Entered in Excel to a Specific Length of Text

1. Open the spreadsheet document you'd like to apply the text restriction to.
2. Click the 'Data' tab, then click 'Data Validation.'
3. Click 'Settings.'
4. Select 'Text Length' in the 'Allow' field.
5. Select 'Less than or equal to' as the rule type.
6. Enter the maximum number of characters you'd like to allow in the text field.
7. Select 'Ignore blank' to not count spaces and other blank characters toward the text limit. If spaces should count toward the limit, leave this unchecked.
8. Type an optional message that will be displayed to the user if she attempts to enter too much information in the 'Input Message' field.
9. Select 'Show error alert after invalid data is entered' to make sure the user receives a notification that his text was not entered.
10. Click 'Save' to save your document.

Read more ►

How to Use Excel's Hyperlink Function

1. Know what the hyperlink function does. Hyperlink creates a shortcut or jump that opens a document stored on your computer's hard drive, the Internet or on a network server.
2. Follow the syntax of the Hyperlink function, which is =HYPERLINK (link_location, friendly_name). The parameter link_location refers to the area where the document to which you wish to link is stored. Make sure you include the entire address including http:// when linking to the Internet, or the full path when linking to a document on a drive. The friendly_name parameter is optional. It tells Excel what text to display for the hyperlink. If friendly_name is blank, Excel displays the link itself. Enclose both the link_location and friendly_name parameters in quotation marks.
3. Use the Hyperlink function. If you want to link to a picture on your C: drive and make a hyperlink in Excel that displays, 'Click here to see a picture of an elephant,' then use the following formula. =HYPERLINK('c:\My Pictures\elephant.jpg', 'Click here to see a picture of an elephant'). Don't put a period at the end of the equation. If you want to link to a website such as eHow, and you want the user to see the link address on which he's clicking, omit the friendly_name parameter like this: =HYPERLINK('http://www.ehow.com'). Again, don't include a period at the end of the equation.

Read more ►

How to Remove a Cell Border

1. Select the cell or cells containing the border that you would like to remove. To select a single cell click directly on it, and to select multiple cells, click the left-mouse button and drag until all of the cells are selected.
2. Click the 'Borders' drop-down menu. The 'Borders' drop-down menu is located on Home tab's Font menu on the toolbar at the top of the window. It has an image of a four squares with dotted or solid lines, indicating cells and borders.
3. Click 'No Border' from the 'Borders' drop-down menu to remove the boarder from the cell or cells selected.

Read more ►

How to Use Multiple Consolidation Ranges in Excel 2007

1. Launch Microsoft Excel from the 'Programs' or 'All Programs' menu. Click the 'Office Button' in the upper left corner of the program window and select 'Open.' Navigate to the file you wish to consolidate the ranges of, and select it. Click 'Open.'
2. Click the first worksheet of the range that will be consolidated to select it. Click the 'Home' tab. Select 'Insert' and then 'Insert Sheet' to insert a new sheet for your consolidated range. Double click the sheet name tab at the bottom of the program window to select it. Type a name of your choice. Press the 'Enter' key on the keyboard.
3. Click in the upper left cell of the area where you want the consolidated data to appear. Click the 'Data' tab in the 'Data Tools' group. Click 'Consolidate.' Click the downward pointing arrow to the right of the 'Function' drop-down box, and select the function you want to use to consolidate your data.
4. Click the select range button under the 'Reference' heading. Click the tab on the bottom of the worksheet with the first range to be consolidated. Select the range of cells to be consolidated. Notice that the range appears in the 'Consolidate -- Reference' box. Click the select range button to collapse it. Click 'Add' to add the range in the 'All references' list box. Repeat this step from the beginning for all worksheets.
5. Select 'Top row' under the 'Use labels in' heading, to have the column headings appear. Click 'Create links to source data' to have the data on the consolidated worksheet automatically update when the data on the other worksheets changes. Click 'Ok' to display the consolidated range.
6. Click the 'Office Button' in the upper left corner of the program window, and select 'Save' to save the file.

Read more ►

How to Make a Check Box in Excel

Display Developer Tab
1. Click the 'File' tab on the Ribbon and select 'Options' from the left panel.
2. Click 'Customize Ribbon' to display two panes.
3. Click the box next to 'Developer' to select it in the right pane. Click 'OK.'
Make Check Box
4. Click the 'Developer' tab on the ribbon.
5. Click 'Insert' and select the check box. A cross will appear on the screen.
6. Click and drag to insert a box with a check box and text. Select the text next to the check box, delete it and insert your own text.
7. Right-click the check box you created and select 'Format Control' to format it. You can modify colors and lines, change its size or add protection.

Read more ►

How to Create Links in Excel 2007

1. Click the cell (text box) where you want to insert the hyperlink.
2. Click the 'Insert' tab.
3. Click 'Hyperlink' in the 'Links' group.
4. Click 'Existing File or Web Page' under 'Link to.'
5. Enter the full website in the address box. Include 'http://' in the address.
6. Type the text that you want to display as the hyperlink name in the 'Text to display' box. This is so the person viewing the document sees the text that you chose, but it appears as a hyperlink. The web address will be hidden.
7. Click 'OK' when done.

Read more ►

How to Calculate Tax Gratuity in Excel

1. Label your Excel spreadsheet columns, if desired. For example, you can type 'Sale' into cell A1, 'Tax' into cell B1 and 'Gratuity' into cell C1. You can choose your font type, size and style from the formatting toolbar. You can also choose to center your typing or justify it to the right or left. If the formatting toolbar is not showing, select the 'View' tab from the top then 'Toolbars' from the drop-down menu and select 'Formatting.'
2. Format the Excel worksheet to display how you prefer. For example, to format the second row to display a dollar sign and two decimal places, click on the '2' at the beginning of row 2. Select the 'Format' tab from the top, and in the drop-down box choose 'Cells.' From the 'Category' box, choose 'Currency,' and in the 'Decimal places' box, select '2.' In the 'Negative numbers' box, choose how you prefer negative numbers to display, then click 'OK.'
3. Enter the taxable sale amount into a cell in your Excel worksheet. For example, if your taxable sale amount is $30, enter '30' into cell A1. With the suggested formatting above, Excel will display '$30.00' in the cell.
4. Create a formula to calculate the correct amount of sales tax. For example, if your sales tax rate is 5 percent, enter '=A2*.05' into cell B2. This formula tells Excel to multiply — denoted by the asterisk symbol — the value in cell A2 by .05, which is the decimal value of 5 percent. Cell B2 should now display '$1.50.' This is the amount of sales tax you owe on a $30 sale at a 5 percent tax rate.
5. Create a formula to calculate gratuities at your desired percentage. For example, if you want to pay a gratuity of 20 percent, enter '=A2*.2' into cell C2. This formula instructs Excel to multiply the value in cell A2 by .2, which is the decimal value of 20 percent. Cell C2 should now show '$6.00' as your desired gratuity amount on a $30 sale at a 20 percent tip rate.

Read more ►

How to Count How Many Times a Word Appears in Excel

1. Open the Excel document containing the text that you want to analyze. Select the first cell that contains the text. Hold down the 'Shift' key and select the last cell that hold your text. This will create a selection box around all of your text. Press 'Ctrl' 'C' to copy this information to the clipboard.
2. Click on a new worksheet at the bottom of the spreadsheet. If all the worksheets are currently being used, press the new worksheet button, which is located directly to the right of the last worksheet.
3. Select cell 'B1' on the new worksheet. Press 'Ctrl' 'V' to paste the information to this sheet. Select the letter 'B' above the pasted information to select the entire column.
4. Click on the 'Data' tab and then select the 'Text to Columns' button. This will bring up a small wizard window. Select 'Delimited' on the first page of the wizard and click 'Next.' Click on 'Space' from the given options and click 'Finish.' Each word of the text will now have its own cell.
5. Select cell 'A1.' Enter in the following formula: =COUNTIF(B:Z, 'x') where 'Z' is the last column that contains text and 'x' is the word you are searching for. Press enter and the number of times that word appears in the text will be shown in cell 'A1.' Select cell 'A2' and enter in the following formula: =COUNTIF(B:Z, 'x?') to also count instances of the word that are followed by a piece of punctuation.
6. Select cell 'A3' and enter in the following formula: =sum(A1, A2) to get the final count of how many times the word appears in the text.

Read more ►

How to Create Checkbook Registers in Excel

1. Launch Excel 2007. Select the Insert tab.
2. On the Insert tab, click 'Table.' In the create table dialog box enter =$A$1:$G101 in the 'Where is your data?' field. Check the 'My table has headers' box then click 'OK.' Excel will create a table with seven columns and 101 rows.
3. Create labels for your checkbook register on row 1. Beginning with cell A1, replace each generic label with the following labels: Check No., Date, Transaction Description, Statement, Payments, Deposits, Balance. The balance label should be in cell G1.
4. Format the check register rows by first turning off the filters, which are automatically turned on when you create tables in Excel. To remove the filters, click the Data tab then click 'Filter.' Click the Design tab to begin color banding your rows and the balance column. In the table style options, check Last Column. Header Row and Banded Row should have a check. If they do not, place a check in the header and banded row boxes.
5. Format the check register columns by first clicking the Home tab then select column A and B. Click 'Center' in the alignment grouping. Select column C then click 'Format.' Select Column width and enter 35---or any any width you prefer. Select column E, F and G. In the number groupings, select Accounting Number format. With E, F, and G still selected, click 'Format' and increase the width of the selected columns.
6. Enter beginning balance and balance formula. Type 'Beginning Balance' in cell C2 and enter your beginning balance amount in cell G2. In cell G3 enter the following formula: =IF(AND(ISBLANK(E3),ISBLANK(F3)),'',G2-E3 F3). Copy this formula to cells G4 to G101.

Read more ►