How to Read XML Data in Excel 2007

Import an XML data file into Excel 2007
1. Open the Excel 2007 application.
2. Click on the Microsoft icon on the top-left corner of the application's screen and click 'Open.'
3. Locate the XML data file on your hard drive.
4. Click on 'Open' to launch the Open XML or the Import XML dialog box. The Open XML dialog box will be displayed if the XML file does not have a style sheet reference. If the file does have a style sheet reference, the Import XML dialog box will be displayed.
5. Click on one of the three options if the Open XML dialog box is displayed on the screen. For example, the 'As an XML table' option imports the contents of the file to the workbook and applies an inferred schema to the data if the file does not refer to one. The 'As a read-only workbook' option causes the XML data file to be converted into a table containing rows and columns. The root node of the file is displayed as a heading in the first cell. The data is displayed in a read-only file with the tags as column headings. Any other tags are sorted alphabetically and shown on the second row. No inferred schema is applied to your data in this case. The 'Use the XML Source task pane' option displays the schema of the XML file in a task pane to the right of your application screen. Drag elements from the pane to the Excel worksheet in order to map them.
6. Click on one of the two options if the Import XML dialog box is displayed on the screen. For example, the 'Open the file with the following style sheet applied (select one)' option allows you to choose one of the style sheets shown. The selected style sheet will be applied to the data. The 'Open the file without applying a style sheet' option displays the data in the worksheet in columns and rows with the XML tags as column headings. The root node is displayed in the first cell. The rest of the elements are sorted and placed in the second cell.

Read more ►

How to View Comments in Microsoft Excel 2003

1. Scroll to the “View” tab on the command bar to open the View menu.
2. Turn on the View Comments feature. Under the “View” tab, scroll to and left-click on “View Comments.” This will allow you to see all comment callout boxes and their text within the workbook.
3. Disable the View Comments feature by accessing the “View” tab and left-clicking on the “View Comments” selection, which is highlighted when active and appears as normal text when inactive.
4. View comments by right-clicking on cells that contain them. When you have the View Comments feature turned off, you can tell which cells contain comments: They will have a red comment arrow in the upper right corner. To view these comments, simply right-click on the cell and select “Show/Hide Comment.” You can undo this by right-clicking on the cell again and selecting “Hide Comment” to hide the comment callout box.

Read more ►

Microsoft Excel: How to Protect My Formulas in Excel Sheets

1. Open Excel and type a formula in cell A1. Right click on the cell and select 'Format Cells.' Select the 'Protection' tab. Check the option for 'Hidden.' Click 'OK.' The hidden option lets you hide the content of the cells but it requires that the worksheet be protected for this to be activated.
2. Select the 'Review' tab and locate the Changes group. Within the Changes group, select 'Protect Sheet.' Make sure the 'Protect Worksheet and contents of lock cells' option is checked. Type a password in the 'Password to Unprotect Sheet' field. Confirm the password at the 'Confirm Password' prompt. Write this password in a secure location so you don't forget it. Click 'OK' to confirm the password protection.
3. Verify the password protection by going to cell A1. Look at the formula bar and notice it is blank. If you send this workbook to other users, they will get the same result. Your formula is protected and not visible unless they have the password. Do this for each cell or range where you want to protect your formulas.

Read more ►

How to Manage Range Names in Excel

1. Start Microsoft Excel and open an existing spreadsheet that contains range names that you would like to manage, filter, edit, delete or create additional range names.
2. Choose the 'Formulas' tab to display the 'Formulas' ribbon. Click the 'Name Manager' button to open the 'Name Manager' dialog box.
3. Click the 'New' button in the 'Name Manager' dialog box to open the 'New Name' dialog box. In this dialog box, you can provide a name, scope, comment, and cell reference for the new range name that you create.
4. Select a range name you would like to edit from the 'Name Manager' list and click 'Edit' to open the 'Edit Name' dialog box. Here you can rename the range, change the scope, edit the comment or change the cell reference.
5. Identify a range name you would like to delete from the 'Name Manager' list and click the 'Delete' button. A dialog box will come up and ask you to confirm that you want to delete the name range. Click the 'OK' button to delete the name range from the 'Name Manager' dialog box.
6. Highlight and click the 'Filter' button on the top right of the 'Name Manager' dialog box to display the filter options available for the name manager. You can filter to find range names that are located in the current workbook, that are located in the current worksheet, that have errors, that don't have any errors, defined names and table names.
7. Complete the process by clicking on the 'Close' button. This will close the 'Name Manager' dialog box after you have finished managing the range names.

Read more ►

How to Create a Money Graph by Using Microsoft Excel 2003

1. Enter the data for your graph in a new or existing spreadsheet in Microsoft Excel 2003. Include a relevant title for each monetary amount as this is the information that will appear on your graph and help you identify what each number is. For example, on a graph comparing your various household expenses, you may list 'groceries, rent and utilities' with the amount for each in the cell beside the title.
2. Format the numbers in your graph as currency. Highlight the numbers and click 'Format' in the menu bar, then select 'Cells.' In the number tab, set the category to 'Currency.' You can then set the number of decimal points you want behind the numbers and the currency symbol that you prefer.
3. Highlight the numbers and titles for the numbers that you want to include in your money graph. Click on the 'Chart Wizard' icon in the toolbar.
4. Select the type of chart that you want to create from the window that will pop up for the chart wizard. You can create all the standard types of graphs, such as column graphs, bar graphs, line graphs and pie charts. Other options include an XY scatter, doughnut graph, bubble graph or stock graph. If you are graphing your expenses over a period of months, a line graph will quickly show you if they've gone up or down. A pie chart is an effective way to see how big of a chunk each individual expense is taking out of the total.
5. Choose the chart sub-type for your selected chart. This determines exactly how the finished chart will look. Most chart types are available flat or with various 3-D effects.
6. Click 'Next' to continue to the next step of creating your graph. Check the data range to make sure you are using the correct information for your chart. Identify whether the series in the selected data runs in the row or column. If the titles for each amount run horizontally along the top of the page with the numbers beneath, your series is in rows. If the labels are in a vertical list with the numbers set next to each label, you have a series in columns.
7. Select 'Next' and enter a title for your chart. You can also label the X and Y axis and determine where the legend for your graph will appear on the finished page.
8. Go to the next page of the set up and determine the page where your graph will appear. Click on the graph to open the chart menu. You can make additional changes to the graph here if you are unsatisfied with your results.

Read more ►

How to Link Sheets Using Visual Basic in Excel

1. Press 'Alt' and 'F11' at the same time to open the Visual Basic editor from your Excel worksheet.
2. Click 'Insert > Module' to open a blank code window.
3. Insert the following code (up to 'End Sub') into the blank window:Sub GetValue()
Range('A1').Value = '=[workbook_name.xls]Sheet1!B1'
End SubThis code fetches the value of A1 from the sheet Sheet1 in the workbook workbook_name.xls, then places that value into cell A1 of the currently active sheet.
4. Press 'F5' to display the Macros dialog. The macro name should already be filled in as 'GetValue.'
5. Click 'Run' to execute the VBA code linking the two workbooks together. You should now see the value from A1 in workbook_name.xls in cell A1 of your currently open worksheet.

Read more ►

How to Add a Dial Chart to Excel 2010

1. Go to the Microsoft Office Templates site at office.microsoft.com, type “dial chart” into the search field, click the drop-down arrow next to the search button and select “Excel.” Click the “Search” button.
2. Click one of the dial chart thumbnails and click the “Download” button. The dial chart opens in Excel.
3. Edit the data that the dial chart represents, as necessary. Generally, the “Max Value” field displays the maximum number listed on the gauge, and the “Indicator Line” filed dictates value, which is where the dial line displays.

Read more ►

How to Embed Excel Files into Word

1. Open Microsoft Word. You can either open a blank document or an existing document.
2. Click on the location in your document where you wish to embed your spreadsheet.
3. Click the 'Insert' tab and select 'Object.'
4. Click the 'Create from File' to browse for an existing Excel file. Navigate to and select the Excel spreadsheet. Choose to display the spreadsheet as an icon or link as well as the full object.
5. Click 'OK' to embed your object. If you didn’t choose to display the spreadsheet as an icon or link, you will see the first sheet of the workbook in your Word document. Click the spreadsheet object to view any other sheets and also to change data.

Read more ►

How to Disable Hyperlink Warning Messages in Office 2007

1. Creating a system restore point is recommended (See Resources).
2. Click the 'Start' button.
3. Type 'regedit' in the search box. Press 'Enter.'
4. Click 'Continue' if you get a 'User Account Control' window.
5. Press 'F3' on the keyboard to open the 'Find' window.
6. Type 'HKEY_CURRENT_USER\Software\Policies\Microsoft\Office\12.0\Common' and click 'Find Next.' If this text is not found, press 'ok,' then F3 again. Type 'HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Common' and click 'Find Next.'
7. Look for a sub-key named 'Security'. If this exists, click on it. If not, create it by clicking 'Edit', 'New' and then 'Key.' Type 'Security' and press enter.
8. Click 'Edit,' 'New,' and then 'DWORD' value. Type 'DisableHyperlinkWarning' and click 'Modify'.
9. Select 'Decimal' and type '1' for the value. Click 'OK.'
10. Close the registry editor and reboot the computer.

Read more ►

How to View Excel 2007 Files on Excel 2003

1. Click to download the 'Microsoft Office Compatibility Pack' from the Resources section below. Choose a directory on your local hard drive to save the file and click the 'Save' button.
2. Double-click the downloaded executable to run it. Read the license terms, then click to place a check in the box next to 'Click Here To Accept The Microsoft Software License Terms.' Click the 'Continue' button.
3. Click the 'OK' button when the Compatibility Pack has completed the installation process.
4. Launch the Microsoft Excel 2003 program by clicking the 'Start' menu button. Click 'All Programs', then 'Microsoft Office', and click on 'Microsoft Office Excel 2003' program shortcut.
5. Click the 'File' menu and choose the 'Open' menu option. Click on the Excel 2007 spreadsheet file to select it in the 'Open' dialog box, then click the 'Open' button to open the file.

Read more ►

How to Protect a Single Cell in Excel 2007

1. Click any cell in Excel, hold the 'Ctrl' key and press 'A' to select all cells. Alternatively, click the triangle at the top left of the worksheet, located just above the row numbers and to the left of the column letters.
2. Right-click any selected cell, and click 'Format Cells.'
3. Click the 'Protection' tab, uncheck 'Locked' and click 'OK.'
4. Click any cell to deselect all the other cells.
5. Right-click the single cell you wish to protect, and click 'Format Cells.'
6. Click the 'Protection' tab, check 'Locked' and click 'OK.'
7. Click the top 'Review' tab, and click 'Protect Sheet' in the 'Changes' group.
8. Enter a password in the 'Password to Unprotect Sheet' field of the 'Protect Sheet' dialog window, and click 'OK.'
9. Re-enter the password in the confirmation box and click 'OK.' That single cell is now protected. To unprotect it, click 'Unprotect Sheet' in the 'Changes' group of the 'Review' tab, enter the password and click 'OK.'

Read more ►

How to Put a Signature on Microsoft Excel

Manual Signature
1. Open Microsoft Excel. Click the 'File' tab and select 'Open,' then browse to the spreadsheet and double click the file.
2. Click the 'Insert' tab. Click 'Shapes' in the 'Illustrations' section.
3. Click the squiggly line 'Scribble' tool in the 'Line' section. The cursor changes to a pencil.
4. Position the cursor on the spreadsheet, then click and hold down the left mouse button and draw a signature of your first name with the Scribble tool. Release the left mouse button when finished. Repeat the process of clicking the 'Shapes' button, selecting 'Scribble' and clicking the cursor to draw your last name next to the first name box.
5. Change the signature color -- depending on your settings Excel's default may be light blue -- by clicking the new orange 'Drawing Tools' tab at the top of the work area. Click the 'Shape Outline' button. Click a small colored square, such as black or dark blue, to instantly change the color. Repeat for the last name and any further designations.
Import Signature
6. Open Microsoft Excel. Click the 'File' tab, select 'Open,' browse to the spreadsheet and double click.
7. Click the 'Insert' tab at the top of the work area. Click the 'Picture' button in the 'Illustrations' section.
8. Browse to the previously-created graphic file containing your signature. Double click the file name to add the signature image to your Excel spreadsheet.

Read more ►

How to Copy Workbooks in VBA

1. Open Microsoft Excel. Create a workbook, and name it 'Book1.xls.' Populate the cells with information, and save it to your local disk C: drive.
2. Create another workbook, and name it 'Book2.xls.' Leave it blank, and save it to your local disk C: drive. Close Excel.
3. Open Microsoft Word. Go to 'Tools,' 'Macro' and then Click 'Visual Basic.' This will open the Visual Basic programming environment.
4. Go up to 'Insert' and choose 'Userform.' This will create a Userform.
5. Double-click on the Userform, and paste the following code: FileCopy Source:='C:\Book1.xls', Destination:='C:\Book2.xls'Here is an example of what your code should look like:Private Sub UserForm_Click()FileCopy Source:='C:\Book1.xls', Destination:='C:\Book2.xls'End Sub
6. Press the 'F5' key on your keyboard to run the program. The information in workbook 'Book1.xls' should now be copied to 'Book2.xls' on your local C: drive.

Read more ►

How to Add a Trendline With Excel 2007

1. Select the chart to which you want to add the trendline. Click once on the data series for which you want the trendline to appear.
2. Click the 'Add Trendline' option on the 'Chart' menu. This action will open a separate pop up window.
3. Click the 'Type' tab. Select the type of trendline that you want to add from the list of available options. Click the 'OK' button to add the trendline to the chart.

Read more ►

How to Center Worksheets Both Horizontally Vertically in Windows Excel

1. Open your worksheet in Microsoft Excel by clicking the 'Office' button in the upper left corner and selecting 'Open.' Navigate to where your workbook is located and click 'Open' to display the worksheet.
2. Click the 'Page Layout' tab and click 'Margins' in the 'Page Setup' group
3. Click 'Custom Margins' to display the 'Page Setup' settings.
4. Check the boxes located next to 'Horizontally' and 'Vertically' under 'Center on Page' to center your worksheet. Click 'OK' to close the 'Page Setup' box.

Read more ►