Saturday, September 28, 2013

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 ►

Friday, September 27, 2013

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 ►

Thursday, September 26, 2013

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 ►

Wednesday, September 25, 2013

How to Restore MS Excel Toolbar


1. Access the toolbar setting in Microsoft Excel 2008 by going to the “View” menu and selecting “Customize Toolbars and Menus.”
2. Restore the toolbars by clicking the boxes next to the toolbar names. The most commonly used toolbars are the “Standard” and “Formatting” toolbars. If you want these toolbars to appear at the top of the document, click the “Dock” option for each one.
3. Press “OK” to complete to process and return to your document.
Read more ►

How to Copy an Excel Spreadsheet Into PowerPoint


Copy Entire Worksheet
1. Select the PowerPoint slide in which you want to insert the Excel worksheet. If the slide has multiple content boxes, click the content box where you want the Excel information to appear.
2. Click the 'Insert' tab at the top of the PowerPoint 2010 window. Locate the 'Text' field and select the 'Object' button to open the 'Insert Object' window.
3. Select the radio button next to 'Create from file' on the left side of the window. Then click the 'Browse' button and navigate to the Excel file that you want to add. Place a check next to 'Link' if you want the image of the Excel spreadsheet to update whenever the Excel file changes. Click 'OK' to add the image of the top worksheet in the Excel file to your PowerPoint slide.
Copy Range of Cells
4. Open the Excel 2010 file and select the worksheet that contains the information you want to add to a PowerPoint slide. Click the upper left cell in the range of information and hold down the mouse button. Move the mouse to the lower right cell and release the button. Press 'Ctrl' 'C' to copy the cell information to the clipboard.
5. Open the PowerPoint 2010 presentation and select the slide in which you want to add the Excel information. If multiple object boxes are present on the slide, select the one where you want the Excel information to go. Click the 'Home' tab at the top of the screen and select the 'Paste' button on the far left side of the ribbon. Five options will appear.
6. Select 'Use Destination Styles' to paste the cells as a PowerPoint table and use the same style as the current PowerPoint presentation. Select 'Keep Source Formatting' to paste the cells as a PowerPoint table but keep the exact same appearance as the cells had in the Excel worksheet, including conditional formatting. Select 'Embed' if you want to paste the cells as a small piece of the Excel program, which you can then edit like an Excel worksheet. Select 'Picture' to paste an image of the selected cells. Select 'Keep Text Only' to convert the Excel information into text and paste it into the object box. When you make your selection, the Excel information will appear on your PowerPoint slide.
Read more ►

How to Get a Percent of a Total on Microsoft Office Excel 2007


1. Use the SUM function to total the values of the cells you want to include in the total. For example, if you want to determine the total of your income for the year and your income for each month is listed in cells A1 through A12, you would use the function SUM(A1:A12).
2. If you want to take a percentage of the total, enter in a new cell '=SUM(Cells you want to include)*Percentage.' For example, if you wanted to use the income mentioned in step 1 and find 10 percent of that income, you would enter '=SUM(A1:A12)*0.1.'
3. If you want to use a percentage found in another cell, enter that cell instead of a number for percentage in the formula '=SUM(Cells you want to include)*Percentage.' For example, if you had the income tax percentage in cell C5, you would enter '=SUM(A1:A12)*C5'.
Read more ►

How to Make a Chart in Excel 2003


1. Open your workbook in Microsoft Excel 2003 and select the spreadsheet containing the data you want to chart.
2. Highlight the cells containing the data you want included in the chart.
3. Click 'Insert' in the top menu bar and select 'Chart' to launch the Chart Wizard tool. You can also click the 'Chart Wizard' button on the Standard toolbar.
4. Select the 'Standard Types' tab in the dialog box that opens.
5. Select a general chart type (such as 'Bar' or 'Pie') from the 'Chart type' menu and then select a specific style from the adjacent 'Chart sub-type' menu. You can view a live preview of the chart by clicking and holding on the 'Press and Hold to View Sample' button at the bottom-right corner of the dialog box. Click 'Next' after making your choices.
6. Confirm that you selected the correct data previously. If you did not, highlight the data you want included in the chart, or manually enter the cells you want to use in the 'Data ranges' field. In either case, click 'Next' to confirm your selection.
7. Adjust the appearance of the chart in the resulting 'Chart Options' window by selecting the corresponding tab (such as 'Titles' or 'Gridlines'). The available options will vary, depending on the type of chart selected. Click 'Next' after specifying your preferences.
8. Select the 'As object in' option, click the adjacent drop-down menu and then select the name of the spreadsheet into which you want the chart inserted. To add the chart to a new spreadsheet, select the 'As new sheet' option and give the spreadsheet a name in the adjacent field.
9. Click 'Finish' to confirm your choices and generate the chart.
Read more ►

Copy Pasting in Excel 2007 Freezes My Computer


Detect and Repair
1. Open a new document in Microsoft Excel 2007.
2. Click on the 'Help' tab on the program's task bar. In the drop-down menu, scroll down to the program called 'Detect and Repair.' Click the program to begin it.
3. Allow the 'Detect and Repair' program to run. Hopefully, the program will fix any errors you have with your Microsoft Excel 2007 program. If the error persists, move onto the next section.
Edit Your Registry
4. Click the 'Start' menu and scroll up to 'Run.' Type in 'regedit' in the box and press 'Enter.'
5. Scroll through the folders until you find the pathway that is 'hkey_current_user\software\microsoft\office\10.0\excel.' This is the name for Microsoft Excel 2007. Right-click the folder, click on the option to 'Rename' and change the name to something else. Hit 'Enter' to complete the name change. Exit the registry editor and return to your desktop.
6. Open Excel on your desktop. This may resolve the problem that occurs when you copy and paste cells in the program. However, if it does not, you will need to reinstall the program.
Uninstall and Reinstall
7. Click on 'Start' and scroll up to 'Control Panel.' Choose the option to 'Add/Remove Programs.' Scroll through the list to locate 'Microsoft Excel 2007' and click the option to 'Change/Remove' the program. Follow the instructions to delete the program and restart your computer.
8. Insert the Microsoft Excel 2007 installation disc into your computer. Your computer will read the disk and begin the Installation Wizard. Follow the instructions to install Microsoft Excel 2007 onto your computer. You may need to restart your computer for the installation to take effect.
9. Open Excel 2007. When you open the program, you should now be able to copy and paste cells without the program freezing up on you.
Read more ►

Blogger news