How to Update Link Workbooks in Excel 2007

1. Close out all of the workbooks in the Microsoft Excel 2007 application. Click on the “Microsoft Office” button and then click on the “Open” option.
2. Select the destination workbook that contains all of the links and then click on the “Open” button.
3. Click on the “Update” button from the pop-up dialog box that appears on the screen to automatically update all of the links for the source workbooks.
4. Click on the “Don’t Update” option if you want to only select links to other workbooks, and then click on the “Edit” option from the top toolbar menu.
5. Click on the “Links” option and then select the object you want to update from the “Source” list. Click on the “Update Values” option.

Read more ►

How to Detect Repair Excel 2007

Microsoft Excel 2007
1. Open Microsoft Excel 2007.
2. Select the 'Microsoft Office' button. Click the 'Excel Options' button.
3. Select 'Resources' located on the left side. Click the 'Diagnose' button and then click 'Continue.'
4. Click the 'Start Diagnostics' button. Office tries to identify your software problem and attempts to repair any problems. Click 'Close' when the diagnostics are complete. If Microsoft Diagnostics cannot fix the problem, repair the software through the Control Panel.
Control Panel
5. Open the 'Start' menu. Choose 'Control Panel' from the menu. Click the link to 'Uninstall a Program.'
6. Select your Microsoft Office Suite from the list of programs. Click the 'Change' button.
7. Choose the radio button to 'Repair.' Click the 'Continue' button. Microsoft Office then repairs the software.

Read more ►

How to Calculate a Date in Excel

1. Populate today's date with the Today function. Type '=TODAY()' in a blank cell to return today's date. The Now function returns the date and the time stamp: '=NOW()'.
2. Calculate a future date by entering a date in a cell. Click on an empty cell and type '=cell reference number of days' where cell reference refers to the date that was entered and the number of days should be substituted for the actual number of days you want to calculate. For example, if we enter 5/22/2009 in cell A1 and want to know what the date will be in 21 days, the formula would be '=A1 21', which returns 6/12/2009.
3. Calculate a past date following the same procedure as in Step 2, but replace the plus sign with a minus. For example, if we enter 5/22/2009 in cell A1 and want to know what the date was 13 days ago, the formula would be '=A1-13', which returns 5/9/2009.
4. Calculate the number of days between two dates with the DAYS360 formula. Enter a start date and end date in two cells. Click on an empty cell and type '=DAYS360(start date,end date)', where the start and end dates would be substituted with cell references. In our example in Step 3, if we had the original date of 5/22/2009 in cell A1 and the calculated end date in B1, the formula would be =DAYS360(B1,A1), which returns 13 days.

Read more ►

How to Learn Basic Microsoft Excel

1. Learn Excel online through the Microsoft Office website, which includes Excel tutorials for all levels. Click the 'support' tab at the top of your page and select 'Excel' from the drop-down list. Select the Excel version you want to learn from the right and click 'Getting started.' Pick a topic -- such as 'Basic tasks in Excel 2010' -- that interests you. (see References)
2. Learn how to complete basic tasks in Excel through video tutorials at Microsoft Office. Click the video that interests you, such as 'Video: Getting Started with Excel 2010,' 'Video: Create a workbook,' or 'Video: Print a worksheet.'
3. Learn basic Excel through books and CDs. You can purchase low-cost books at Amazon.com, Ebay, Craigslist, discount outlets, yard sales or borrow one from your local library. Try books like 'Microsoft Excel 2007 Step by Step,' which also includes a companion CD, 'Excel 2010 in Easy Steps,' 'MS Excel 2007 Training,' or 'Mastering Excel Made Easy Training v. 2010 through 97.'
4. Take a class at your local community college to learn the basics of Microsoft Excel, such as learning about cells, spreadsheets, using formulas and inserting rows/columns.

Read more ►

How to Embed a PDF File in Excel

1. Select the cell in your Excel spreadsheet where you wish to embed a PDF. The PDF file will take up more than just one cell; select the cell you want the top left-hand corner of the file to appear. Click on the 'Insert Object' button in the Text group under the Insert tab. Highlight 'Adobe Acrobat Document' under Object type in the Object pop-up window .
2. Decide how you want your PDF to appear in your Excel document. If you want the PDF to appear as the first page of the PDF, simply click the OK button. If you want the PDF to appear as an icon, click the check mark labeled 'Display as Icon' and choose an icon using the 'Change Icon...' button or click 'OK' to accept the default Adobe Acrobat Document icon.
3. Navigate to the PDF you wish to embed in your Excel workbook in the Windows Explorer pop-up window. Double-click on the PDF file or click the 'Open' button after highlighting the PDF file to embed it in your Excel workbook.
4. Double-click on your embedded PDF or representative icon in Excel to access your embedded file.

Read more ►

How to Unlock a Chart in Excel

1. Open Excel 2010 and the chart that is locked.
2. Click 'Unprotect Sheet,' which is located in the 'Changes' grouping on the 'Review' tab along the toolbar.
3. Enter the password to the chart if you are asked to provide one and click 'OK.' If you didn't create the chart or set the password, speak to the person who did.

Read more ►

How to Do an OHLC Chart in Excel

1. Open the Excel worksheet.
2. Type the data with four series of values in this order: open, high, low and close. For example, row 1 can include the five column headers: “Date,” Open,” “High,” “Low” and “Close.”
3. Click and drag the cursor to select the categories and data values you wish to convert to a chart.
4. Click the “Insert” tab on the command ribbon.
5. Click the arrow for the “Other Charts” button in the “Charts” group. A gallery of thumbnail images for charts and subtypes displays.
6. Click the “Open-High-Low-Close” image link in the “Stock” section. The “Open-High-Low-Close” button displays vertical lines and rectangles. The data converts to an embedded chart on the Excel worksheet. The “Chart Tools” ribbon displays three tabs: “Design,” “Layout” and “Format.”
7. Customize the chart with the commands on the “Chart Tools.” For example, the “Design” tab contains options for “Chart Layouts” and “Chart Styles.” Adjust the fonts with the “Font” commands on the “Home” tab for better readability.

Read more ►

How to Import XML to XL

Import XML Data as an External File
1. Open Microsoft Excel. Click on the 'Data' tab and locate the 'Get External Data' group. Click on the icon for 'From Other Sources' and select 'From XML Data' from the drop-down menu.
2. Locate the XML data file. Select the file name and then click on 'Open' in the lower right hand corner of the current dialog box. The 'Import Data' dialog box will appear.
3. In the 'Import Data' dialog box, specify whether to import the data as an XML table in a new worksheet, XML table in an existing worksheet, or whether to flatten the data into a two-dimensional table with the XML tags serving as column headings in the worksheet.
4. Save the spreadsheet when the import has completed.
Import XML Data Into Mapped Worksheet Cells
5. Highlight the 'Developer' tab in Excel. If the 'Developer' tab does not appear on the menu, click on the Microsoft Office button in the upper left corner of the application window and then click on 'Excel Options' in the lower right hand corner of the dialog box.
6. Highlight 'Popular' in the menu on the left and click on the check box next to 'Show Developer tab in the Ribbon' under 'Top Options for Working with Excel.' Then click on 'OK' to exit the dialog box.
7. Create a workbook that links to an existing XML schema. If there are no mapped elements, use the 'XML Source Task Pane' under 'XML Source Task Pane' to develop a mapping in the current workbook.
8. Import individual data items by selecting one of the mapped cells in the active worksheet and then clicking on the 'Import' icon in the 'XML group' under the 'Developer' tab, which will open the 'Import XML' dialog window. Select the location of the XML data file and click on 'Import.'
9. Import the entire XML data table by clicking on the 'Import' icon in the 'XML group' under the 'Developer' tab, which will open the 'Import XML' dialog window. Select the location of the XML data file and click on 'Import.' Specify whether to import the file into a new or existing worksheet.

Read more ►

How to Change Default Colors in Excel

Adjust the Color in a Worksheet
1. Change the font color. Click on one cell to select it or click on the first cell in a group and drag the mouse to highlight all the cells. You can also click on the gray box at the top left corner of the worksheet to select all the cells on the page.
2. Click on the 'Font Color' button on the tool bar. Click to select one of the standard colors displayed on the palette. The font color button has an 'A' on it, with a bar of the current font color underneath the A.
3. Change the background color of a cell. Highlight the cell or cells you would like to adjust and then click on the arrow on the right side of the 'Fill Color' button on the toolbar. Click on a color to apply it to the cell.
4. Change the color in a chart. Open the chart you want to format and double-click on the element you want to change. You can change the color of the background, labels and font of the chart.
Adjust a Color in the Color Palette
5. Open the workbook containing the color palette you want to change. You can also make the changes for a new workbook.
6. Open the 'Options' dialog box. Select 'Tools' from the tool bar and click on 'Options.' This dialog box contains various selections that you can use to change the default attributes of a workbook.
7. Click on the 'Color' tab. Select the color you want to change and click on 'Modify.' A 'Colors' pop up box should appear.
8. Replace the current color. To switch the color you have selected with another, select a different color from the standard tab of the colors dialog box.
9. Customize a color. Click on the 'Customize' tab of the 'Colors' box to adjust the intensity of the color you have selected.

Read more ►

How to Unsync Split Pane in Excel

1. Open the spreadsheet that has the split screens.
2. Click the 'Synchronous Scrolling' button in the 'Window' group on the 'View' tab.
3. Close the split panes by clicking on the 'Close' button in the upper-right corner of the pane. Repeat this step until only one pane remains open.
4. Click the 'Maximize' button in the upper-right corner of the pane.
5. Click the 'Save' button on the 'Quick Launch' toolbar to save the changes.

Read more ►

How to Replace Blank Cells With Value in Excel 2003

1. Select the range of cells that contain the blank cells you want to replace. Do this by clicking on the uppermost cell on the left side, then drag the cursor down to the right until the last cell of the range is highlighted. Not all of the cells in the range have to be blank.
2. Go to 'Edit' on the menu bar and select 'Replace.' A pop-up menu will appear.
3. Leave the 'Find what' box blank to search for blank cells.
4. Enter the value you want to replace the blanks with in the 'Replace with' box. For example, if you want '4' to replace all the blank cells, type '4' (no quotation marks).
5. Click the 'Replace All' button if you want all the blank cells replaced with your value. If you want only certain cells replaced with the value, click 'Find Next' and then click 'Replace' whenever you want the blank cell replaced. If you do not want a certain blank cell replaced, click 'Find Next' to go to the next blank cell.

Read more ►

How to Create a Pivot Table in Excel 2003

1. Create a spreadsheet in Excel with all of the data you want to place in your PivotTable.
2. Open a new a workbook for your PivotTable report.
3. Click on 'Data' in the menu bar, and then 'PivotTable and PivotChart Report.' This launches the PivotTable and PivotChart Wizard.
4. Select the option to use data from one Excel list or database. Click 'PivotTable.'
5. Select your data range. This is the actual data for your PivotTable. Click on the 'Browse' button and select the file and datasheet for your PivotTable. Click 'Finish.'
6. Drag your fields from the PivotTable Field List into the 'Column' and 'Row' sections of the PivotTable. Put the field that you want totaled into the 'Data Items' section.
7. Right-click anywhere on the table and click on 'Table Options' to change or add options for totals. Right-click on a field and click on 'Field Settings' to rename a field and change its count settings.
8. Drag the fields in the columns and rows to rearrange the data within the table. Switching the columns will change how the table reports the totals.

Read more ►

How to Use the Auto Filter in Microsoft Excel 2003

1. Highlight the cells and columns you wish to apply the auto filter to. Click and hold the mouse button in as you scroll the cursor over the desired groups of cells and columns, release the mouse button and they will remain highlighted.
2. Apply the auto filter. Scroll to “Data” and select “Auto Filter” from the submenu. Downward arrows will appear over all the columns you highlighted, from the top portion of the highlighted columns.
3. Filter the data as desired. Click on any down arrow to filter data in that column. When you click on the arrow a submenu will open. You can filter data by selecting the following options: 'Ascending' (which will sort the data as it ascends the workbook), 'Descending' (which will filter the data as it descends the workbook), 'Top Ten' (which will filter data by the top results of equations) and 'Custom' (which will open a custom filter menu where you can select custom filter algorithms from the drop-down menus).
4. Turn the auto filter off. To turn off the auto filter scroll to “Data” and click on “Auto Filter” again.

Read more ►

How to Move the Excel 2007 Pivot Table Field List

1. Open the Microsoft Excel 2007 application on your computer. Click on the 'Microsoft Office' button and the select the 'Open' option.
2. Locate the Excel 2007 file that contains the PivotTable that you want work with and then click on the 'Open' button to load the file onto your spreadsheet.
3. Click on the 'Options' tab if you don't see the PivotTable Field List after you've clicked on your PivotTable. Click on the 'Field List' option from the 'Show/Hide' group.
4. Locate the layout section at the bottom of the Field List and then click on the 'Move Up' option to move any select field in the list up one position. Click on the 'Move Down' option to move a field down one position.
5. Click on the 'Move to Beginning' option to move a field to the top of the list. Click on the 'Move to End' option to move the field to the bottom of the list.
6. Click one of the options to move a field to the 'Report Filter,' 'Row Labels,' 'Column Labels' or 'Values' area of your PivotTable.

Read more ►

How to Set Excel Macro Security to Medium

1. Open Microsoft Excel. Go to Start–Programs–Microsoft Office–Microsoft Office Excel. Please note that in some versions of Microsoft Office, you will Microsoft Office Excel straight from Programs.
2. Go to the Tools menu at the top of your Excel window.
3. Open the Tools menu and go to Macros.
4. Choose Security from the Macros menu.
5. Choose Medium from the Security Level tab. You also have the options of Very High, High, and Low. For most users High or Medium is best.
6. Click the OK button and restart Excel by closing the program and re-opening it. Your macro security level is now set to medium.

Read more ►