How to Use Command Line Parameters in Microsoft Excel

1. Open a command prompt by clicking 'Start,' choosing 'Run,' entering 'cmd' and clicking 'OK.'
2. Type 'excel' on the command prompt. Enter a space. Do not press Enter yet.
3. Type any command line switches you'd like to use, separating them with spaces.
'/r fileName.xls' will open fileName.xls in read-only mode.
'/t fileName.xls' will create a new document, using fileName.xls as a template.
'/s' will cause Excel to start in 'safe mode,' which prevents any start-up scripts from loading.You can see the full list of commands at the Microsoft Office page devoted to the topic.
4. Press enter. This will cause Excel to load. After you are done using Excel, you can close your command prompt window.

Read more ►

How to Upgrade Excel 2003 to 2007

1. Insert the Microsoft Office 2007 CD into your computer's disc drive. The automatic setup will begin.
2. Type the product key into the text box. The product key is a 25-character key and is usually located on the back of the CD jacket.
3. Click on the 'I accept the terms of this agreement' check box and then click on the 'Continue' button.
4. Click on 'Upgrade,' which will remove Excel 2003 and replace it with Excel 2007. Wait for the installation to complete; it will take 15 to 30 minutes.
5. Click on the 'Close' button to complete the installation of Excel 2007.

Read more ►

How to Change a Reference to an Absolute in Excel 2007

1. Place a dollar sign in front of the column delimiter if you want to fix it, and in front of the row delimiter if you want to fix it.For example, you can change =VLOOKUP(A1,K1:P20,4) to =VLOOKUP(A1, $K$1:$P$20, 4) so that the reference to the table's location in K1:P20 won't change if you copy and paste or autofill this formula into empty cells elsewhere.
2. Mix the dollar sign use in order to fill a table. For example, if column headings are in B1 through Y1 and row headings are in A2 through A80, and the cells of the table depend on the values in the headings, then you can enter one cell the right way using mixed absolute references and drag to fill the entire table without the cells incorrectly referencing headings below row 1 or right of column A. For example, enter in B2 the formula =B$1$A2. You'll be able to drag this to fill the entire table because the formulas will always be pointing to the heading row (row 1) and heading column (column A).
3. Use shortcut keys to toggle through the dollar-sign permutations. Specifically, highlight the cell reference that you want to change in the formula bar (the long thin field at the top of the screen). Press the F4 function key to toggle through the different possible placements of $. (This feature doesn't work in some versions of Excel, such as Excel 2004, though it works in 2007.)

Read more ►

How to Import Access Query Into Excel

1. Open Access and create the following table of sample data, which has field names in the top row. Click 'Create>Table' to create the table.game,saledate,totalsale
quake,12/1/2009,$6.01
quake,12/3/2009,$7.98
guitar hero,12/5/2009,$4.98
guitar hero,12/6/2009,$5.98Save the table (by pressing 'control-s') with the name 'games.'
2. Create a new query for the games table by clicking 'Create>Query Design,' right-clicking on the 'Query1' text in the query's tab, and clicking 'SQL View.'Enter the following statement in the SQL code window:SELECT games.* INTO myqueryres
FROM games
WHERE (((games.game) Like '*tar*'));Save the query (by pressnig 'control-s') as 'myquery.'
3. Run the query by double-clicking 'myquery' in the navigation pane, then close Access and open Microsoft Excel.
4. Click 'Data' and choose 'From Access.' In the 'Select Data Source' dialog box, open the Access database you created the games table in. Select the table 'myqueryres' from the 'Select table' dialog box, then press 'OK' on the 'Import Data' dialog box. Notice that Excel has imported the query.
5. Close Excel, remembering the filename when you save the file. Re-open Access. Revise 'myquery' in 'SQL View' to read as follows:SELECT games.* INTO myqueryres
FROM games
WHERE (((games.game) Like '*ua*'));
6. Save and re-run the query, then close Access and re-open the Excel file you created the table link in. Select 'Data' and choose 'Connections,' then press the 'Refresh' button in the 'Workbook Connections' dialog box. Close the dialog box and notice that the query results have changed.
7. (For users of Excel 2003) Complete steps 1 and 2. Run 'myquery,' then press 'alt' 'F11' to enter the Visual Basic integrated development environment (IDE). Press 'Insert' and select 'Module,' then paste the following program code into the new code window:Public Sub sendToExcel()'''''''''''''''''''''''''
Set curdb = CurrentDb
Set recs = curdb.OpenRecordset('myqueryres')
st = 'game' ', ' 'saledate' ', ' 'totalsale' vbCrSet xlapp = CreateObject('Excel.Application')
xlapp.Workbooks.Add
r = 1: c = 1
xlapp.ActiveSheet.Cells(r, c) = st
r = 2
st = ''
Do While Not recs.EOF
st = st recs![game] ', ' _
recs![saledate] ', ' recs![totalsale] vbCr
xlapp.ActiveSheet.Cells(r, c) = st
recs.MoveNext
r = r 1
st = ''
Loop
recs.Close: curdb.Close
xlapp.ActiveWorkbook.SaveAs ('c:\accessquery.xls')
xlapp.QuitEnd Sub
8. Click 'Tools' and choose 'References,' then check the check box labeled 'Microsoft Excel Objects' so your macro can recognize the functions that Excel makes available.
9. Run your macro by placing the cursor anywhere in the sendToExcel subroutine and pressing 'F5.'
10. Double-click the file 'c:\accessquery.xls' from Windows Explorer, and notice the query's results in Microsoft Excel when it opens.

Read more ►

How to Create a Simple Budget in Excel 2003

1. Gather your budget information. Have your monthly statements and expenditure amounts ready.
2. Double-click on the 'Excel' icon on your computer. If you can't find it on the desktop, open the program using the 'Start' menu on your toolbar.
3. Decide whether you want to make your budget from scratch or use a template. Using a template is the easiest method. If you're going to use a template, skip to Step 5.
4. Make your budget from scratch by setting up your worksheet any way you like. Start by naming the rows and columns and then add figures for various incomes and expenditures.
5. Click 'New' on the task bar on the top of the page to display a box with various different templates for projects in Excel. Select the simple budget template option. Follow the instructions and fill in the cells with your expenses and income.
6. Save your budget by pressing the 'Ctrl' and 'S' keys at the same time. You can also print it out by pressing 'Ctrl' and 'P.'
7. Update and change your budget as needed. Your Excel 2003 budget is fully customizable and can be changed based on your needs. You can even make graphs and charts from your data for visual assistance.

Read more ►

How to Match Destination Formatting in Word

1. Launch Microsoft Word and open your document. Highlight and copy or cut the source material that you want to paste. Click within your Word document where you want to paste the copied text. Right-click or depress the 'Control' button simultaneously with the letter 'v.' Recent versions of Microsoft Word will show a little clipboard icon titled 'Paste Options.' Click on the 'Paste Options' button and a drop-down box will appear. If this is the only material that you intend to copy and paste, select the radio-button to the left of 'Match Destination Formatting' and your content will paste, matching what is already in your document. Click 'Edit' then 'Paste Special' if you are using earlier versions of Word. Alternatively, you can select the 'Tool' menu then click 'Options,' 'Edit' and then 'Show Paste Options.'
2. Change the default so that the paste font, size and color will automatically match every time. Select the 'Set Default Paste' option to change the default setting in the drop-down menu. A new window called 'Word Options' will appear. Scroll down the page to 'Cut, Copy, and Paste'. Drop-down boxes appear to the right of the first five options. The option 'Match Destination Formatting' appears in the first four boxes. Select your desired preferences. Click 'OK' at the bottom right.
3. Click the Microsoft Office Button in the upper left corner of your Word document to set the default in recent versions of Word before you select any material for pasting. Click the 'Word Options' box in the lower right corner of the box. From the menu column at the left, select 'Advanced.' The same window as in Step 2 will appear. Scroll down the page to 'Cut, Copy, and Paste'. Drop-down boxes appear to the right of the first five options. The option 'Match Destination Formatting' appears in the first four boxes. Select your preferences. Click 'OK' at the bottom right of the window.

Read more ►

How to Unlock an Excel 2003 Spreadsheet

1. Open the Excel spreadsheet you want to unlock.
2. Select 'Tools,' 'Protection' and 'Unprotect Sheet' from the toolbar. If you used a password to lock the spreadsheet, you'll be prompted to enter it in the 'Unprotect Sheet' pop-up box.
3. Enter the password and press 'OK.' The spreadsheet will unlock.
4. Click on 'File' and 'Save' to save your changes.

Read more ►

How to Merge Cells in Microsoft Excel 2003

1. Select the cells that you want to merge. To select cells for merging, left-click on the first cell and hold the mouse button in as you scroll over the cells that you wish to merge it with. Once you have selected the desired number of cells you can release the mouse button and they will stay highlighted.
2. Use the cell merge tool. After you have selected the cells that you want to merge, click on the cell merge icon located on the command bar. The icon looks like a lowercase letter 'a' sitting on top of a small book that is open. Left-click on this icon to merge the cells.
3. Set the text alignment in the merged cells. Use the text alignment icons located in the command bar to select left, right or centered alignment of the text within the merged cells after you have merged them.
4. Save your updated spreadsheet. After you have made the desired changes, do not forget to save your spreadsheet. Simply click on the “File” tab and select “Save” to save your changes.

Read more ►

How to Use Data Tables in Excel

Excel 2003
1. Open a new workbook.
2. Select the 'Data' tab.
3. Click 'Table.' Excel will create a table on your worksheet.
4. Create the data for your table. For example, you may have a set of values in column A and a formula you want to use in column B1 (there's no need to retype the formula, the table will carry it down for you).
5. Open a workbook.
6. Click on the 'Insert' tab, and then click 'Table.'
7. Provide the source of the data for the table. For example, if your data is in cells A1 through A10, then enter 'A1:A10' in the source box.
8. Click 'OK.' Excel will enter a table onto the worksheet.

Read more ►

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 ►