How to Create Macro Buttons in Excel 2007

1. Open an Excel workbook that contains a macro.
2. Click the arrow to the right of the Quick Access Toolbar to reveal a drop-down menu; from that menu choose 'More Commands....'
3. Click the left-hand drop-down menu in the resulting window. Choose 'Macros.'
4. Double-click a macro in the resulting list to move it into the 'Customize Quick Access Toolbar' list.
5. Click the 'Modify' button below the list of Quick Access Toolbar buttons.
6. Choose a button image in the resulting window, and type a new display name for the button, if you want. The button name appears in a tooltip when you hover the mouse cursor over the button.
7. Click 'OK' to close the Modify Button window, then 'OK' to close the Excel Options window and return to the main Excel window, where your macro button appears on the Quick Access Toolbar.

Read more ►

How to Make a Chart on Excel With Coordinates

1. Open a new Microsoft Excel 2010 spreadsheet.
2. Click on cell 'A1' and type in the header for the first half of your set of coordinates. This header will not appear on the spreadsheet, but may be of use for referencing purposes when you are entering your data. Click on cell 'B1' and type in the header for the second half of your set of coordinates. This header will wind up as the title for your chart, and will be displayed in the chart's legend as well.
3. Select cell 'A2' and enter the first part of your first set of coordinates. This part of your coordinates will be plotted on the horizontal axis. Select cell 'B2' and end the second part of that set of coordinates. This part of the coordinates will be plotted on the vertical axis. Continue to enter in sets of coordinates in the first two columns until you have added all your data to the spreadsheet.
4. Click on cell 'B1' and select the 'Insert' tab at the top of the screen. Click the 'Scatter' button, located in the Charts area of the ribbon. Choose any of the five different XY Scatter charts that appear in the pop-up menu. The different charts will all plot the same data, but will allow you to choose to add curvy lines, straight lines and data markers. Once you click on your desired chart type, the chart will appear on your spreadsheet.

Read more ►

How to Spell Check on Excel 2007

1. Launch Excel 2007 and open the file you want to spell-check.
2. Click the 'Review' tab at the top of the screen.
3. Click the 'Spelling' button in the 'Proofing' section on the ribbon at the top of the screen. The 'Spelling' button is represented by the letters 'abc.'
4. Click one of the options in the 'Spelling' window when Excel encounters a potential spelling mistake. You can choose 'Ignore' if the word is spelled correctly or you can select the correctly spelled word under 'Suggestions,' then click 'Change' to fix the misspelled word.
5. Click 'OK' after all spelling mistakes have been corrected.

Read more ►

How to Add Gridlines to an Excel Chart

1. Start Microsoft Excel 2007 and open a workbook from your files that contains a chart to which you would like to add gridlines.
2. Click the chart you want to add gridlines to so it is selected. You can tell the chart is selected because it will have a light blue border surrounding it.
3. Select the 'Layout' tab at the top of the Excel 2007 screen to display the settings in the 'Layout' ribbon. Find the 'Axes' group in the 'Layout' ribbon.
4. Choose the 'Gridlines' button in the 'Axes' group of the 'Layout' ribbon and point to 'Primary Horizontal Gridlines' to display the horizontal gridline options.
5. Use 'Major Gridlines' to display gridlines only at major units, 'Minor Gridlines' to display them at minor units or 'Major Minor Gridlines' to display the gridlines at both the major and minor unit markings.
6. Opt for 'Primary Vertical Gridlines' from the 'Gridlines' button to choose to display either 'Major Gridlines,' 'Minor Gridlines' or 'Major Minor Gridlines' at the vertical units in the chart.
7. Watch as the gridlines are displayed on the selected chart as you have indicated.

Read more ►

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 ►