How to Add a New Workbook in VBA

1. Press the 'Alt' and 'F11' keys together in your workbook to open the Visual Basic Editor (VBE).
2. Click 'Insert' and then click 'Module' to open a blank module window.
3. Type the following:Sub AddNew()Set NewBook = Workbooks.AddWith NewBook.Title = 'All Sales'.Subject = 'Sales'.SaveAs Filename:='Allsales.xls'End WithEnd Sub
4. Press 'F5' to run the program and open a new workbook.

Read more ►

How to Repair an Excel File

Attempt to Open the File
1. Repair the file manually. From the menu, click “File,” “Open.” Select the damaged file. Click the arrow next to the “Open” button. Click “Open and Repair.” At the prompt, click “Repair.” The file may open successfully.
2. If you have AutoRecover turned on, open the recovery file. Since recovery files are deleted when you quit Excel, try this before restarting. The default file location for AutoRecover files is “C:\\Documents and Settings\\
\\Application Data\\Microsoft\\Excel.”
3. Revert to the most recently saved version. On the menu, click “File” and “Open.” Select the name of the file you want to recover. A dialog box appears, asking “Revert to Saved Document?” Click “OK.”
4. Close Excel, then reopen it and see if you can open the file.
5. Restart the computer, then open Excel and try opening the file.
6. Clear temporary files. In Windows Explorer, navigate to \'C:\\Windows\\Temp.\' Delete the contents of the folder. Restart your computer, open Excel and see if you can open the file.
7. Start Excel in Safe Mode. Click “Start.” Under “Accessories,” click “Run.” Type the following command, including quotation marks (the “/s” switch specifies Safe Mode):
“c:\\program files\\Microsoft office\\office\\excel.exe” /s
Press “Enter.” When Excel opens, try to open the file.
8. If you have a more recent version of Excel installed, try opening the file in that version.
Save as SYLK or HTML
9. You may be able to filter out errors in the active sheet by saving in SYLK (Symbolic Link) or HTML (Hypertext Markup Language) format. To save as SYLK, select “File,” “Save As.” Click the arrow next to “Save as type,” and select “SYLK (Symbolic Link) (*.slk).” Click “Save.”
10. One or more warning messages will appear. Click “OK” or “Yes.”
11. Close the saved file.
12. Click “File,” Open.” Change the file type to “All Files.” Select the SLK file you just saved and click “Open.”
13. Click “File” and “Save As.” Give the file a different name, and save it as a Microsoft Excel workbook (.xls).
14. This method affects the active sheet only. For each additional worksheet in the corrupted file, reopen the workbook, activate the sheet and repeat the process.
15. If the problem persists, try saving the file in HTML format. After saving, close the file, reopen it and save it as an Excel file, using a different filename.
Modify the Registry
16. Back up the registry before making any changes. Changing the registry is risky, and the Registry Editor does not wait for a “Save” command before making your changes permanent. For information on backing up the registry, see the Microsoft Knowledge Base at support.Microsoft.com/kb/322756/en-us.
17. Open the Registry Editor. Click “Start,” then “Run.” Type “regedit” and press “Enter.”
18. In the left-hand pane, double-click on “HKEY_CURRENT_USER.” You will see a list of subkeys. Navigate to HKEY_CURRENT_USER\\Software\\Policies\\Microsoft\\Office\\10.0\\Excel\\Options by double-clicking each subkey.
19. In the right-hand pane, double-click on “ExtractDataMode.” In the “Value Data” box, change the “2” to “1.” Click “OK.” Close the Registry Editor and restart your computer to see if the change fixed the problem.
20. Follow the same procedure to change the value of “ExtractDataFormulas” from “2” to “1.” Restart your computer and see if the fix worked.
21. Repeat the process to change the value of “ExtractDataDisableUI” from “2” to “1,” and restart your computer.

Read more ►

How to Create Division Formulas in Cells in Microsoft Excel 2003

1. Choose the cells you wish to divide. Make sure the cells contain divisible dates.
2. Select a cell that will contain the formula. Pick an empty cell and left-click on it to activate and select it.
3. Create the division formula. In the blank cell, you will create a sum formula. The formula will have to include the cell you wish to divide and the cell that contains that division integer. A division formula for two cells would read like this: =Sum (D1/D2). This tells the spreadsheet to take the data contained in cell D1 and divide it by the data contained in cell D2. The program will place the results of the division operation into the blank cell in which you created the formula.
4. Implement the division formula. Once you are done entering the division formula, simply click on the 'Enter' key, and the formula will be implemented within your spreadsheet.

Read more ►

How to Insert a File Location in Excel 2007

File Path in a Header or Footer
1. Open the saved worksheet.
2. Click on the 'Insert' tab on the command ribbon.
3. Click on the 'Header and Footer' button in the 'Text' group. The 'Design' tab will appear.
4. Click on the header or footer text box.
5. Click on the 'File Path' button in the 'Header Footer Elements' group. A code will appear in the text box.
6. Click away from this text box. The workbook's file path will display in the text box.
File Path in a Hyperlink
7. Open the saved worksheet.
8. Click on one cell to insert this file location. You can opt to select the cell's text for an embedded link.
9. Click on the 'Insert' tab on the command ribbon.
10. Click on the 'Hyperlink' button in the 'Links' group. A dialog box for 'Edit Hyperlink' will appear.
11. Click on the down arrow of the 'Look In' box for the location of the file. For example, the file may be saved on the desktop.
12. Click on the file in the scrollable window.
13. Click the 'OK' button. The hyperlink will appear in the cell.

Read more ►

How to Convert a PDF File Into an Excel File

1. Download and install PDF-to-text conversion software, or convert your file online. There are several programs that will do this; some are free and some cost money but have free trial periods. See the Resources section for information.
2. Process the PDF-to-text conversion, and save the text file.
3. Open a blank Excel spreadsheet and import the text file. In Excel 2003, go to Data>Import External Data>Import Data. In Excel 2007, go to Data>Get External Data>From Text. The 'Text Import Wizard' will pop up.
4. Choose the type of file that best describes your data. You will see a preview of the text below, to help you choose. If commas or tabs separate the data you wish to import, choose 'Delineated.' If the fields are already in columns, with spaces in between, choose 'Fixed Width.'
5. Follow the wizard through the importing steps. In each screen you will be able to see a preview of how the data will be imported, so you can always go back and change a step if it does not look correct. For example, if you chose 'Delineated' and the preview screen does not show separate columns for your data, go back and change your selection to 'Fixed Width.'
6. Select 'Finished' and then 'Existing Worksheet' as the location to open your data. Your PDF document is now in an Excel file. It will likely not be perfect and you will need to do a little tweaking to have all the data where you want it, but most of the work has been done.

Read more ►

How to Create Headers in Microsoft Excel 2003

1. Scroll to the “View” tab on the command bar to access the View menu.
2. Under the “View” submenu, select “Header and Footer,” which will open a header-and-footer properties box. Click on the “Custom Header” button to access the Header Properties box.
3. Enter the desired header text into the left, right and center sections. Using the icons inside the box, you can insert a picture, date stamp, pages to be included and files to be included, and you can even change the font type. Make sure you have selected a section, then click on the corresponding icon to add the desired features.
4. Click on the 'OK' button to add your header to your spreadsheet.

Read more ►

How to Delineate Commas in Excel

1. Open Microsoft Excel and open the comma-separated list in another program such as Notepad.
2. Click and drag with the mouse pointer to highlight each item in the comma-separated list. Right-click the list and then click 'Copy' to copy the list to the Windows clipboard.
3. Click cell A1 in the upper left corner of the Excel spreadsheet. Right-click the cell and then click 'Paste' to paste the list from the Windows clipboard into Excel.
4. Click the 'Data' tab at the top of the Excel window.
5. Click to highlight column 'A' of the spreadsheet and then click the 'Text to Columns' button in the 'Data Tools' section of the toolbar.
6. Click the 'Delimited' radio button at the top of the window and then click 'Next.'
7. Click the 'Comma' check box under 'Delimiters' and remove any other checks.
8. Click the 'Finish' button.

Read more ►

How to Make a Simple X

1. Open a blank worksheet in Microsoft Excel 2007.
2. In the first cell, enter “X.” Press “Tab,” and enter “Y” in the next cell over. These will be the column headings for your X and Y data.
3. Enter a series of X data points in the column under “X.” It’s a good idea to simply enter regular intervals of X to make up the horizontal axis. For instance, enter all the values ranging from “-5” up to “5” to display the line across a negative and positive axis.
4. Calculate the Y value that corresponds to each X value. The equation format for a line graph is Y = MX B, where M is the slope of the line and B is the Y-intercept value. If you have a linear equation for the line, plug the X values you’ve entered into the equation to calculate the Y values. Enter each value in the Y column.
5. Highlight the cells you want to graph on a chart.
6. Click the “Insert” tab. In the “Charts” group, select the “Scatter” icon.
7. Select “Scatter with Smooth Lines and Markers.” This will display X versus Y in a line graph that connects the data markers. The chart will display on your worksheet.
8. Click the “Design” tab, which appears under “Chart Tools” when you select the chart. If you want to change the format of the chart, make a selection in “Chart Layouts” and a line style under “Chart Styles.” To format individual elements of the chart, click the “Format” tab and select the element you want to change, such as “Chart Title” or “Axes.”
9. Click the “Microsoft Office Button,” and select “Save” to save the data and graph you’ve made.

Read more ►

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 ►