September 2013 - Excel

Browse » Home » Archives for September 2013

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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'.

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.

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.

Tuesday, September 24, 2013

How to Extend the Microsoft Excel Record Limit

Open a New Worksheet
1. Open Excel and the worksheet you have hit your record limit on.
2. Click on the 'Insert Worksheet' button located at the bottom of the Excel workbook. The button looks like a folder with a yellow star and is usually located at the end of the worksheet tabs. You can also press the 'Shift' key plus 'F11.' If you do not have function keys, you can also right click on a worksheet tab and click 'Insert.' Select 'Worksheet' to add another worksheet to your book.
3. Save your workbook and begin doing computations in your new worksheet.
Upgrade to Excel 2007 or 2010
4. Navigate to the Microsoft Office website and download a newer version of Microsoft Office to increase the Excel record limit. You can also purchase a stand-alone version of Excel 2007 or 2010.
5. Install the newer version of Excel on your computer.
6. Open your older workbooks in the newer version of the program. Microsoft products are backwards compatible so you can simply open Excel worksheets from 97 to 2003 in a newer version to take advantage of the larger record limit.
Migrate to a Database Program
7. Download and install a database program such as OpenOffice's Base or Oracle's MySQL, or purchase Microsoft Access.
8. Open the database program and set up a blank database.
9. Import the Excel workbooks or worksheets into the database. Expand your database with the records you need.

How to Convert Read

1. Open the 'Read-Only' spreadsheet in Microsoft Excel 2007. You can open the file by double-clicking on it from it's current location or in Excel 2007 by clicking on the 'Office' button and then 'Open.' When the 'Open' dialog box open, click on the file and then click 'Open.'
2. Click on the 'Office' button and then 'Save As' to begin the process of saving the file in a earlier, editable version. The 'Save As' dialog box appears.
3. Click the drop-down arrow next to 'File type as' and choose 'Excel 97-2003' Workbook. Choose a file location for the file and then click 'Save.' Close the workbook.
4. Open the workbook in Excel 2003. Click on 'File > Open.' Locate the workbook and click 'Open.' You can now edit the document all you want. It is no longer locked for editing.

Monday, September 23, 2013

How to Make Three Dimensional Graphs in Excel

1. Open the Excel worksheet that contains the source data.
2. Click and drag to select the worksheet's categories and data to appear in the chart.
3. Click the “Insert” tab on the command ribbon.
4. Click the arrow under the preferred chart type in the “Charts” group -- for example, click “Pie,” “Bar” or “Area.” A gallery of thumbnail charts displays, including “3-D” for three-dimensional options.
5. Click a thumbnail chart in the “3-D” section -- for example, click “3-D Bar.” The source data converts to a three-dimensional chart.

How to Use Decimal Numbers in Excel

1. Launch Excel and open the worksheet where you want the number formatting changed to decimals.
2. Highlight the cells you want to change. If you want to use a whole column for decimals, click in the shaded box with the column's identification letter, or use the identification number box for rows to highlight the whole row.
3. Select multiple cells, rows or columns by holding down the 'Ctrl' key as you click on other cells, rows or columns after your first selection.
4. Click the 'Home' tab, and then click in the bottom right corner of the 'Number' section, where you will see a small box with an arrow. This launches a window with the full set of number options.
5. Click on 'Number' in the left menu column, and then select your preferred number of decimal places using the arrows in the field next to 'Decimal places' in the middle of the dialog box. You can also manually enter a number into the decimal places selector.
6. Choose any other options you want to modify, such as using a comma to separate digits at every 1,000 and how negative numbers are formatted, and then click 'OK.' Your numbers will appear as decimals.
7. Enter new decimal amounts as you would on any computer keypad, using the period on your keyboard to indicate a decimal point. If you have a numeric keypad on your keyboard, the decimal key there will also work.
8. Choose special decimal formatting, such as how many decimal places to show in dollar amounts, by highlighting the appropriate cells and accessing the same dialog box. Choose 'Currency' instead of 'Numbers,' and then refine your settings from there. Click 'OK' to reset the format.
9. Increase or decrease the number of decimal places that appear in your cells by highlighting your cells again, and then tapping the decimal increase or decrease buttons on the number's pane of the Home menu, just above the box and arrow icon.

How to Share Modify Excel Documents

Sharing
1. Open your workbook.
2. Click 'Review,' then 'Share Workbook.'
3. Check the ' Allow changes by more than one user at the same time' and 'This also allows workbook merging' check boxes.
4. Click 'Advanced,' then change the settings for tracking changes. Click 'OK.'
5. Save the document in a network directory.
Modifying
6. Open the workbook on the network drive by double-clicking on the workbook.
7. Click the 'Office' button, then 'Excel Options.'
8. Click 'Popular,' then 'Personalize your copy of Office.' Enter your name in the user name section.
9. Edit the data as you normally edit a workbook.
10. Save the file.

Sunday, September 22, 2013

How to Use Microsoft Excel for Payroll

1. Navigate to the Microsoft Office Payroll Calculator template page (see Resources) and click 'Download,' then 'Accept.' Excel will automatically open to the template when the download is complete.
2. Fill out the columns with the information according to the headings above each column. The date you enter will include employee names and IDs, hourly wages, state tax percentage, insurance deductions and other relevant tax deduction information. As you enter the data, Excel will automatically calculate the figures in the 'Total Taxes Withheld' and 'Total Regular Deductions' fields.
3. Click 'Payroll Calculator,' which is near the bottom of the spreadsheet. Enter the requested information about the employees' hours, including overtime, and the calculator will provide you with the total net pay for each employee.

How to Create a Dashboard in Excel 2007

1. Log on to your computer and open Microsoft Excel 2007. Open the spreadsheet you want to upload.
2. Click on the Office button and choose 'Publish' from the list. Choose 'Excel Services' from the list and choose 'Distribute the document to other people'.
3. Enter the URL of your website, followed by the name of the file, in the 'File name' box For instance, if you are saving a document called spreadsheet1 to the site xyzcorp.com/reports, you would type 'xyzcorp.com/reports/spreadsheet1.xlsx' in the 'File name' box.
4. Click 'OK' and choose 'Save'. In the 'Document type' box choose 'Reports'.
5. Go to the site where you want to add the spreadsheet and choose 'Site actions'. Choose 'Edit Page' and then 'Modify shared web part'.
6. Click 'Workbook' and then choose 'Select a Link'. Navigate to the workbook you want to add to the dashboard and click 'OK'.

How to Import Data With Excel 2007

1. Log on to your computer and open the Microsoft Excel spreadsheet into which you want to export the data. Click the 'Microsoft Office' button.
2. Click 'Open' and choose the type of file you wish to export. Double-click the file you wish to import.
3. Choose whether the file to be imported is a delimited or fixed-width file. With a delimited file, each field is separated by a particular character, generally a comma, a semicolon or a colon. This type of file is most appropriate for files where each record contains data of varying lengths. A fixed-width file is most appropriate for files where each record is the same size.
4. Review the field names Excel assigns to each field. Type a new name if you wish, then click 'Next.' Click 'Finish' to complete the import process.

How to Import an Excel Macro From 2003

Export from Excel 2003
1. Press 'Alt' 'F11' together from within Excel 2003 to open the Visual Basic Editor (VBE).
2. Click on a macro name in the left sidebar to open the module window.
3. Click 'File' > 'Export File.'
4. Click the 'Save In' drop-down folder to choose a location to save the file, then click 'Save.'
Import the Excel 2003 File
5. Open Excel 2007 or Excel 2010.
6. Press 'Alt 'F11' together to open the VBE.
7. Click 'File' and then click 'Import.'
8. Locate the file on your computer and then click 'Open.'

Saturday, September 21, 2013

How to Find the Z

1. Open an Excel worksheet and enter your data in one column. For the purposes of this article, enter sample data into cells A1 through A10, typing a different number in each cell.
2. Click on an empty cell below your data and use the “AVERAGE” function to calculate the mean of the numbers you entered. For the example above, you would type “=AVERAGE(A1:A10)” into the formula bar and press 'Enter.' The “A1:A10” part of the formula indicates the range of cells containing the data you want to analyze. The result of the calculation will appear in the cell you selected.
3. Click on a different empty cell below your data and calculate your standard deviation by typing a formula using the function “STDEV” along with your cell range. Continuing the example above, the formula would be '=STDEV(A1:A10)'.
4. Click on an empty cell beside the cell containing the number you want to find the Z-score for. For instance, click on cell B3 if you want to find the Z-score for the number in cell A3. Click the “fx” button on the formula bar to open the “Function” window. Select 'Statistical' from the category dropdown menu, then choose 'STANDARDIZE' to bring up the Function Arguments window.
5. Enter the number you want to calculate the Z-score for in the “X” box. Enter the number itself or a cell reference where the number is located, such as “A3.”
6. Enter the mean you calculated in Step 2 in the “Mean” box. Again, you can type in the number itself, or the cell where you entered the formula in Step 2.
7. Enter your standard deviation in the “Standard_dev” box. Type the number, or the cell where you entered the formula in Step 3.
8. Press “OK” to display the Z-score for the cell you chose in Step 5.

How to Add up Multiplications in Excel

1. Launch Excel 2010 and open the spreadsheet that contains the products that you want to add together.
2. Click once in the cell where you want the sum of the products to appear. Click the 'Insert Function' button on the Formula bar, which opens a list of available functions. Click the 'SUMPRODUCT' option, which opens a separate window.
3. Click the 'Array1' field in the Function Arguments box. Select the range of numbers for which you want to find the sum of the products, for example 'A1:B4.' Repeat this step for each range of numbers, using a different Array field for each group. For example, select 'C1:D4' for the Array2 field. Click the 'OK' button to accept the formula.

How to Use Microsoft Excel 2010

1. Start Microsoft Excel 2010.
2. Activate Excel over the Internet if prompted.
3. Click the top-left cell on the new blank spreadsheet, and type the number '25.'
4. Press 'Enter' to save the cell contents, and move to the next cell down the column.
5. Type the number '5' and press 'Enter.'
6. Press the 'Equal' sign ('='), and immediately click once on the cell that contains the number '25.'
7. Press the 'Plus' sign (' ') on the keyboard, and immediately click on the cell that contains the number '5' and press 'Enter.' Notice that the third cell now displays the sum of 25 5, or 30. The cell also contains a formula that is shown in the formula bar, which should look like '=A1 A2.'
8. Click in a blank cell.
9. Press the 'Equal' sign ('='), and immediately click once on the cell that contains the number '25.'
10. Press the 'Asterisk' symbol ('*') on the keyboard, and click on the cell that contains the number '5' and then press 'Enter.' Notice that the third cell now displays the answer of 25 x 5, or 125. The cell also contains a formula that is shown in the formula bar, which should look like '=A1*A2.'
11. Click on the cell that contains the number '25.'
12. Type the number '50,' and press 'Enter.' Notice that the two calculated cells automatically updated to reflect the new number.
13. Click on the first cell, and hold down the mouse button.
14. Drag the mouse across the remaining cells until all four cells are highlighted, and release the mouse button.
15. Click the 'Insert' tab on the toolbar, click 'Column' in the 'Charts' group, and to create a column chart of the data, click on the first chart in the context menu that pops up.
16. Click either the cell that contains the '50' or the cell that contains the '5,' and change it to another number and then press 'Enter.' Notice that the cells containing the totals and the chart all update to reflect the new values.

How to Allow the Selection of Multiple Items in Page Area of a Pivot Table

1. Open Excel 2007 and select a workbook. Click the 'Office' button and click 'Open.' Browse your computer and locate the workbook. Double-click the workbook. The workbook opens.
2. Highlight the data you want displayed in the pivot table. Select the 'Insert' tab. Click 'Pivot Table' twice. Click 'OK.' The blank pivot table appears in a new worksheet.
3. Add fields to the pivot table by checking the fields in the right 'Pivot Table Field List.' The fields appear in the column by default. Move some of the fields to the row by dragging the field name to the 'Row Labels' in the right 'Pivot Table Field List.' Add a field to the report filter by dragging the field name into the 'Report Filter' in the right 'Pivot Table Field List.'
4. Look above your pivot table to find the report filter. Click the drop-down list and select the option for 'Select Multiple Items.' This options allows for the selection of multiple items in your report filter page area.

How to Add a Yes or No Box to an Excel Spreadsheet

Display the Developer Tab
1. Click the 'File' tab and click 'Options.'
2. Click 'Customize Ribbon' and click 'Main Tabs.'
3. Check the box labeled 'Developer' and click 'OK.' The Developer tab appears in the Excel ribbon.
Yes or No Option Button
4. Open the Excel spreadsheet that you want to add an options button.
5. Click the 'Developer' tab and click 'Insert' from the 'Controls' group.
6. Click 'Option Button' under the 'Form Controls' heading.
7. Click the cell in which you want to insert the options button.
8. Highlight the words 'Options Button 1' on the options box. Type 'Yes' and click on an empty cell in your worksheet.
9. Click 'Option Button' under the 'Form Controls' heading.
10. Click the cell in which you want to insert the second options button.
11. Highlight the words 'Options Button 2' on the options box. Type 'No' and click on an empty cell in your worksheet.
12. Right-click an options button and click 'Format Control' to edit the button's color, format, size or any other properties. Click 'OK.'
Yes or No Check Box
13. Open the Excel spreadsheet that you want to add a check box.
14. Click the 'Developer' tab and click 'Insert' from the 'Controls' group.
15. Click 'Check Box' under the 'ActiveX Controls' heading.
16. Click the cell that you want to insert the check box. This cell will include the upper left corner of the check box.
17. Highlight the words 'Check Box 1' on the check box. Type 'Yes' and click on an empty cell in your worksheet.
18. Click 'Check Box' under the 'ActiveX Controls' heading.
19. Click the cell that you want to insert the second check box.
20. Highlight the words 'Check Box 2' on the check box. Type 'No' and click on an empty cell in your worksheet.
21. Right-click a checkbox and click 'Format Control' to edit the check box's color, format, size or any other properties. Click 'OK.'

How to Specify the Templates Folder in Excel

1. Open Microsoft Excel, then click 'Tools,' followed by 'Options.'
2. Type the folder path into the box labeled 'Alternate startup file location.' To find the folder path, right-click on the folder, then click 'Properties.' The path is listed beside the label 'Location.'
3. Click 'OK' to save your settings, then restart Excel. The new settings will take effect.

How to Determine Percentages Using Division

1. Start by determining what needs to be divided by what. For example, if you want to know what percentage score you achieved on a test, you will be dividing the number of questions you got right by the number of total questions.
2. Use a calculator or a pencil and paper to do your division. Let's say you got 35 questions correct out of 50. You will enter 35 divided by 50 into your calculator. The answer is .7.
3. Multiply the number by 100 to get it in a percentage. In this case .7 multiplied by 100 equals 70, or 70 percent.

Friday, September 20, 2013

How to Make a Work Vs. Time Graph on Excel

1. Click and drag to select all of the cells containing your work and time data, open the 'Insert' tab, and then click the 'Create Chart' button and double-click on the desired type of chart from the list. The graph will now appear onscreen if you are using one of the latest versions of Excel (2007 or 2010). If you are using an earlier version of Excel, follow the onscreen prompts to finish formatting the graph.
2. Right-click on the graph, choose 'Select Data' from the context menu, click on one of the 'Legend Entries,' such as 'Series1' or 'Series2,' click 'Edit,' and then type the desired name (for example, 'Work') into the 'Series name' box and click 'OK.'
3. Change the graph type by right-clicking on the graph and selecting 'Change Chart Type.' To move the graph to a new Excel sheet, right-click on the graph, select 'Move Chart,' and then click 'New sheet.'

How to Create a Receipt in Excel

1. Open Excel. Click 'File,' 'New.' Type 'Receipt' in the search box. Click a template image to see a preview in the right task pane. Choose the template you want. Click 'Download.'
2. Highlight the default contact information on the receipt. Type your information on the receipt.
3. Type the details of the transaction on the receipt lines. Save the receipt by clicking the 'Save' icon on the 'Quick Access' toolbar.

How to Make a Graph in Microsoft Excel 2003

1. Create a spreadsheet with at least two data sets. One set will be the X-axis (independent axis) of the graph, and the other set will be the Y-axis (dependent axis) of the graph.
2. From the 'Insert' menu, click 'Chart.' The Chart Wizard will appear.
3. Select the type of chart you wish to create. A list of sub-types will appear. Select the sub-type and click 'Next.'
4. In the Chart Source dialogue box, click 'Data Range.' Select the data you wish to include for the independent and dependent axes, select 'Rows' or 'Columns' in the dialogue box, and click 'Next.'
5. From the Chart Options dialogue box, use the 'Titles' and 'Legends' tabs to define the graph axes and title. Click 'Next.'
6. Finally, use the 'Chart Location' dialogue box to determine where to place the graph in the Excel worksheet. The graph can be placed as an item in the current worksheet, or on a separate page. Click 'Finish' when you are done.
7. If, after viewing the completed graph, you wish to make changes, right-click on the graph. This will allow you to modify the various parts of the graph, including colors and fonts for the legends.

How to Use the Pivot Table Wizard in Excel 2003

1. Open your Excel worksheet. Under 'Data' on the toolbar, select 'PivotTable' in the dropdown menu.
2. In the 'PivotTable Wizard' box, select the data you want to analyze by clicking on the corresponding radio buttons. Click 'Next.'
3. In the next screen, select the 'range' you want to analyze. To define the range, drag your left mouse over the cells you want sorted. Click 'Next.'
4. In the final screen, select whether you want the pivot table to appear in a new worksheet or an existing one.
5. Click 'Finish' to insert the pivot table. Save your work.

Thursday, September 19, 2013

How to Make Cells Print in Excel 2007

1. Go to Microsoft Excel 2007.
2. Open or create the Excel worksheet you want to print. To open your file, select the Microsoft Office and click on 'Open' (shortcut: Ctrl O). To create a worksheet, click on a blank cell, type in a value and press the 'Enter' or 'Tab' key. Add additional entries you need.
3. Select the 'Page Layout' tab from the Ribbon and go to 'Gridlines' under the 'Sheet Options' group.
4. Place a check mark by 'Print' in the 'Gridlines' group to make the gridlines for your cells print. To make your gridlines visible on your page, place a check mark by 'View' as well.
5. Click on the Microsoft Office button, choose 'Print,' then select 'Print' once again. Pick the printer you'd like to use from the 'Print' dialog box, then click on 'Print.'
6. Save your work by clicking on the 'Save' button in the 'Quick Access Toolbar' (shortcut: Ctrl S). Then, name your file and click on 'Save.'

How to Use Excel's Range Finder

1. View the Excel spreadsheet for which you wish to use range finder.
2. Double-click a cell. Range finder activates.
3. View the other cells associated with your selected cell's formula range. Notice that the cells in the equation and the actual cells are color-coded.

Wednesday, September 18, 2013

How to Use Microsoft Excel 3

1. Enter two columns of data in an Excel spreadsheet. The first column contains data labels for the pie chart; the second column contains the proportions, which can be expressed as percentages or real numbers. In the latter case, Excel will calculate the percentages to build the pie chart.
2. Highlight the cells, excluding the total row, if you have one.
3. Click the 'Insert' tab.
4. Choose 'Pie' and select one of the 3-D pie charts from the drop-down menu to insert a chart based on that model into your spreadsheet.
5. Adjust the size of the pie chart and use the 'Chart Tools Design' tab options to change its look.
6. Click the drop-down menu labeled 'Chart Area' in the 'Chart Tools Layout' tab, and use the 'Chart Title' and 'Legend' items to add or edit a title or legend for the 3-D pie chart.
7. Right-click on the pie chart and choose '3D Rotation' to adjust the depth, perspective and other visual elements having to do with the 3-D look of the chart.

How to Use the AutoFill Function in Excel

Creating a Custom Fill List
1. Enter the list in a spreadsheet.
2. Open the Tools menu and choose Options.
3. Click the Custom List tab.
4. Click Import.
Using the Basic AutoFill
5. Open an Excel document.
6. Enter the first value in the cell you want to begin the series (such as 'January').
7. If your series is numerical, click the next cell you want in the series and type the next value. The difference between the two initial cells determines how the series is incremented. For example, if you entered 1 in the first cell and 3 in the next cell, the increment would be 2.
8. Select the two cells you just entered (or the first one, for a non-numerical series, such as days of the week).
9. Locate the 'fill handle,' or the dark square in the lower right corner of the cell. The pointer should change to a small dark square when it's over the fill handle.
10. Drag the pointer to cover all the cells you want in the series.

How to Replace Duplicates With Blanks in Excel

1. Launch Microsoft Excel and open your workbook.
2. Right-click the header of the column that contains the duplicates you wish to erase. Choose 'Insert' from the context menu to create a blank column.
3. Double-click the first cell in the blank column. Enter the following formula:=IF(A1='', '', IF(COUNTIF($A1:A1,A1)>1,'',A1))
4. Replace all instances of 'A' in the formula with the letter label of the column that contains duplicate cells. Highlight this cell again, and then double-click its fill handle -- the small black square at the bottom right corner of the cell -- to copy the formula to the blank cells below it.
5. Highlight all of the cells that contain the 'COUNTIF' formula. Hover your mouse cursor over the right border of the selection until it turns into a cross. Hold down your right mouse button, and then drag the arrow one column to the right. Choose 'Copy Here as Values Only' from the context menu that appears.
6. Delete the column that contains the 'COUNTIF' formula by right-clicking on its header and selecting 'Delete.'

How to Create a Clustered

1. Click 'Start,' 'All Programs,' 'Microsoft Office' and open 'Microsoft Excel.'
2. Enter your column headings as you would normally into the spreadsheet. Add your row headings, but leave one blank cell for each graph cluster column that you want.
3. Enter the data into the spreadsheet. When entering the data, use a separate row for each cluster that you want to create.
4. Click in the upper-right corner of the spreadsheet and drag the cursor so that all the cells containing data are selected. Click the 'Insert' menu at the top of the window.
5. Click 'Column' and the second option in the '2-D Column' section, which is 'Stacked Column.' Right click one of the columns in the chart, and select 'Format data series.'
6. Use the slider in the 'Gap Width' section and slide it all the way to the left to eliminate the gap between the clusters. Click 'Close.'

How to Use VBA to Delete Columns in Excel 2007

1. Click the 'Developer' tab, click 'Visual Basic' and click the 'Insert' menu. Click 'Module' to insert a new VBA code module.
2. Type the following to create a new sub procedure:Private Sub removeColumns()
3. Type the following to remove column B from the active sheet:Columns('B:B').SelectSelection.Delete Shift:=xlToLeft
4. Edit 'B:B' in the previous step, and type the column letter you want to remove. Type 'End Sub' to end the procedure, and press 'F5' to run the procedure and remove the column.

How to Create a Distribution Chart

1. Open Microsoft Excel.
2. Type the number 3.0 in the A1 cell and then type the number 2.75 in the A2 cell just below it. Highlight both cells.
3. Drag the bottom-right of the selected cells to the 25th row. Use the tiny black square as a handle. Each number will be decremented by .25, giving the successive number below it.
4. Select cell B1and enter the equation as: '=normdist (a1,0,1,0)' and hit the 'Enter' key. The Normal Distribution for the number (3) in cell A1 with a mean of zero and a Standard Deviation of 1 appears in the B1 cell.
5. Click the bottom-right of the B1 cell and drag the cursor to the 25th cell as you did in step 3. The Normal Distribution appears in each corresponding B cell for its 'A' cell number. Keep the cells from B1 to B 25 selected.
6. Click 'Insert' from the main menu and then select 'Line' and choose the first line graph from the 2D sub-selection. The chart for the Normal Distribution appears a bell-shaped line graph.
7. Select 'Column' from the main menu and choose the first column graph in the 2D sub-selection. The chart now is represented as a column graph. Experiment accordingly with each variation and ultimately decide which style best suits your presentation.

Tuesday, September 17, 2013

How to View an Excel 2007 Spreadsheet in Excel 2003

1. Learn how to exchange files between Excel 2007 and Excel 2003. The programs in the Microsoft Office suite have been upgraded to use an open XML file format in the 2007 version in order to make the files smaller, more secure and easier to use with other programs. Microsoft has created a compatibility pack which ensures that in spite of the new file format you can use newer releases with the older programs.
2. Learn to use the compatibility pack. If you have the Office 2003 version of Excel or older, you can use the compatibility pack to view, edit and save an Excel 2007 spreadsheet. You can also convert the 2007 Excel spreadsheet from the open XML format to the older binary file format.
3. Prepare to download the compatibility pack. Make sure you have one of the following operating systems on your computer: Windows 2000 Service Pack 4, Windows Server 2003, Windows Vista, Windows XP Service Pack 1 or Windows XP Service Pack 2. The compatibility pack file requires 25MB on your hard disk, so be sure you have enough space.
4. Install updates. If you have Microsoft Office 2000, Office 2003 and Office XP you will need to install high-priority updates from Microsoft.com before you download the compatibility pack (see Resources below).
5. Download and install the compatibility pack. Visit Microsoft.com to download the compatibility pack (see Resources below). You can save the executable file to your hard disk and run it at a later time or you can run it directly from Microsoft's website.
6. Open your Excel program. Click the 'File' button on the toolbar, select 'Open' and search for your Excel 2007 spreadsheet using the dialog box that pops up. Click the file to select it and then click the 'Open' button to view it in your Excel 2003 program.

How to Monitor Stock Prices in Microsoft Excel

1. Open a blank Microsoft Excel spreadsheet.
2. Click on a cell where you want to show a stock price.
3. Click on 'Data' in the top menu bar.
4. Scroll down to 'Import External Data,' then over to 'New Web Query.'
5. In the window that pops up, type the URL http://finance.yahoo.com in the address.
6. Enter the stock symbol you wish to track. Be sure to double check that you entered the correct stock symbol by checking the company name that shows.
7. Scroll down to 'Last trade:' and click on the arrow to the left. The arrow will change to a check mark. The data highlighted will be shown on your spreadsheet.
8. Choose the data you wish to be in your spreadsheet, then click on the 'Import' button at the bottom of the window. You may choose to add any data with an arrow next to it into your spreadsheet by clicking on the arrow to the left of the data.
9. Verify the cell where you want the data to appear when prompted. You can click on any cell in the spreadsheet if you wish to change the location. Click on 'OK' after choosing the cell.
10. Save the spreadsheet. You can update the stock price(s) any time by clicking on 'Data' in the top menu bar. Then scroll down to 'Refresh Data' and click on it.
11. Know that you can also update the stock prices in the 'External Data' toolbar. Just click on the red exclamation point in that toolbar.

How to Copy Excel Formulas Through Multiple Rows

1. Click the cell that contains the formula you wish to copy with your mouse. Check the cell address bar at the top of your worksheet to ensure that the formula is correct.
2. Click 'Edit,' 'Copy' from the edit menu to select the formula. You may also right-click within the cell, and click 'Copy.'
3. Select the destination cells with your mouse by clicking the first cell and dragging through any contiguous rows to be included. Click 'Paste' from the edit menu to copy the formula and any formatting associated with the formula. You may also 'right-click' your mouse within the destination cell range and click 'Paste.'

How to Make a Pie Chart With Microsoft Excel

1. Open a blank worksheet in Microsoft Excel.
2. Enter all of the data into one column starting with cell 'A1.' Using monthly expenses as an example, type 'groceries' in cell 'A1.' Navigate to the cell below ('A2') and type 'gas.' Continue moving down column 'A' typing 'utilities,' 'daycare,' 'car payment' and 'mortgage' in each cell.
3. Insert the total dollar amount you spend on each item. Move the cursor to cell 'B1.' Type the total monthly cost of gas in the cell. Continue down the 'B' column, inputting the monthly costs for each of your other expenses.
4. Put your cursor in the cell directly under the last dollar amount -- cell 'B7' in our example. Holding down the left mouse button, select the cells in this column above 'B7' all the way up to 'B1.' All of the dollar amounts should now be highlighted.
5. With the column still highlighted, click on the 'AutoSum' button along the top of your Excel worksheet. This will total the dollar amount column, displaying your total monthly expenses.
6. Put your cursor in cell 'A1' and highlight down to 'A6,' and then from 'B1' to 'B6.'
7. While the 'A' and 'B' columns are simultaneously highlighted, select the 'Charts' tab at the top of your worksheet. Then choose 'Pie' from the list of chart types.
8. You can select several types of pie charts. For Microsoft Excel's most basic pie chart, select the first option. Your chart will be instantly created in your worksheet.
9. To add percentages or other labels automatically, double-click on the pie chart. A dialog appears.
10. Click 'labels,' then select the desired type of labels, such as percentages or dollar values. Microsoft Excel will place percentages and names on the pie chart.

How to Create a Line Chart in Excel 2007

1. Enter in headers for all of your data columns. Just type in the name for each data field in the cell directly above the data. If your data doesn't have an available row of cells above it, right-click on the row number that corresponds to the top row of data, and choose 'Insert.' These headers will be the labels for your data on the line chart.
2. Type in the row labels in the column directly to the left of your data. If you don't have any empty cells to enter in information, right-click on the letter at the top of the leftmost data column and select 'Insert.' These labels will appear at the bottom of the line chart to show the progression of your data. If you don't enter these labels, the chart will default to numerical labels, staring at one and counting upward.
3. Select the cell above the row labels and to the left of the column headers. Hold the shift key and select the bottom-right cell in your data field.
4. Click on the 'Insert' tab at the top of the Excel ribbon. Locate the 'Charts' area, and select 'Line.' Choose between the seven different types of line charts. You can choose between a standard line chart, stacked line chart and 100 percent stacked line chart. A stacked line will add all the data fields together, instead of comparing them like a regular line chart. A 100 percent stacked line will add all the items together, and then display each item's percentage of that total. Each of these options is also available with markers along the data lines. You can also choose to create a 3D lines chart. Once you select the line chart type, your line chart appears on the screen.

How to Copy Paste Macro for Excel

1. Open the workbook that has the macro you want to copy.
2. Press the 'Alt' and 'F11' keys together to access the VBE.
3. Click on the name of the macro you want to copy in the left-hand column. This opens up a window with the Visual Basic for Applications (VBA) code.
4. Click on the 'Edit' tab and then click on 'Select All.'
5. Click on 'Ctrl' and 'C' to copy the macro to the Office Clipboard.
6. Open the workbook where you want to copy the macro to.
7. Press the 'Alt' and 'F11' keys together to open the VBE.
8. Click on 'Insert' and then click on 'Module' to open a blank window.
9. Click inside the blank window, then press the 'Ctrl' and 'V' keys to paste the code into the open window. Your macro is now copied and ready for use.
10. Press the 'Alt' and 'F11' keys together to exit the VBE and return to your workbook.

Monday, September 16, 2013

How to Export File Search Results to Excel

1. Click on the 'Windows' button in the lower left-hand corner of your computer screen. Type in the file name or phrase you want to find. The results will appear in a separate window.
2. Press 'Control' and 'A' at the same time. This selects the results. Next, press 'Shift' and right-click your mouse at the same time. This will open a dialog box.
3. Select 'Copy as Path' from the dialog box. Open Microsoft Excel. Press 'Control' and 'V' at the same time to paste your results in the spreadsheet.
4. Try one of several dozen software options, such as Power File Search, Agent Ransack or SysExporter. Many of these have limited, trial versions that will let you test the software before buying it.

How to Remove the Date From Excel When Printing

1. Click the Windows 'Start' button and select 'All Programs.' Click 'Microsoft Office,' then click 'Microsoft Excel' to open the file.
2. Press the 'Ctrl' and 'O' keys to open the 'Open' dialog window. Double-click the Excel file you want to open. The file opens in the spreadsheet software.
3. Click the 'Page Layout' ribbon. In the right side of the ribbon in the 'Headings' section, remove the check mark next to the 'Print' option.
4. Press the 'Ctrl' and 'S' keys to save the settings to the document. Print the document to your printer to create a hard copy without headers.

How to Synchronize Scrolling in Excel

1. Open Microsoft Excel.
2. Open your two worksheets, or split your workbook into multi-view.
3. Click 'View' on the toolbar.
4. Click 'Synchronized Scrolling' on the right.

How to Convert CSV Files to Excel 2007

1. Open Excel 2007.
2. Click the circular Microsoft Office button in the upper left corner. Select Open.
3. Click the 'All files (*.*)' drop-down box and select Text Files.
4. Locate your CSV file and click 'Open.'
5. Click the Delimited radio button if the CSV file's data is separated by commas, semicolons or similar characters. Click the 'Fixed width' radio button if the CSV data is arranged in columns. Click 'Next.'
6. Check the check box indicating the delimiter that separates the values, such as commas, semicolons or tabs. Click 'Next.'
7. Click a column to select it. Click the General, Text or Date radio buttons to select a text type for the column. Repeat for the remaining columns and click 'Finish.'
8. Click the circular Microsoft Office button and select 'Save as.'
9. Click the 'Save as type' drop-down box and select 'Excel workbook (*.xlsx).' Type in a file name and click 'Save.'

How to Move Information Inside an Excel Spreadsheet

Move Cell Content Inside an Excel Spreadsheet
1. Start Microsoft Excel, and open a spreadsheet that contains data that you would like to move to a different area within the spreadsheet.
2. Select a cell or group of cells that you would like to cut and paste to a new location by clicking and dragging with your mouse over the cell(s).
3. Right-click on top of the selected cell(s) and choose 'Cut' from the shortcut menu to delete the content from the cell(s) and add them to the Excel clipboard.
4. Click in the cell(s) where you would like the cell content that you just copied to the clipboard to be moved.
5. Use your mouse to right-click and select 'Paste' from the shortcut menu to finish moving the information to it's new location within your spreadsheet.
Move a Worksheet Inside an Excel Spreadsheet
6. Locate the 'Sheet' tabs at the bottom of your Excel spreadsheet.
7. Click on the sheet tab that refers to the worksheet you would like to move inside the Excel spreadsheet.
8. Drag the sheet tab either to the right or left and release the mouse button when the sheet tab appears in the desired location.

Sunday, September 15, 2013

How to Disable Automatic Hyperlink in Excel 2007

1. Click the Office button after opening Excel.
2. Select 'Excel Options' from the Office menu. An 'Excel Options' window will open.
3. Click 'Proofing' from the list of options. You can access AutoCorrect here to disable automatic hyperlinks.
4. Click 'AutoCorrect Options' near the top of the window. The 'AutoCorrect' window will open.
5. Click the 'AutoFormat As You Type' tab and clear the checked box next to 'Replace as you type Internet and network paths with hyperlinks.'
6. Click 'OK' to disable automatic hyperlinks and click 'OK' again to close the window. When you type a Web address in Excel 2007, it will no longer change to a hyperlink.

How to Insert a Calendar in Excel 2007

1. Display the 'Developer' tab in Excel, if it is not already displayed. Click the 'Microsoft Office Button' and then click 'Excel Options.' In the category labeled 'Popular,' under 'Top options for working with Excel,' check the box labeled 'Show Developer Tab in the Ribbon' and click 'OK.'
2. Click 'Insert' in the 'Controls' group on the 'Developer' tab. Under 'ActiveX Controls' click the 'More Controls' button. This button will look like a hammer and a wrench. The 'More Controls' dialog box will appear.
3. Select 'Calendar Control 12.0' and click 'OK'. Right-click the area into which you wish to insert the calendar. The calendar will then be displayed on your workbook. The current month will be displayed.
4. Edit the calendar by right-clicking on it and selecting 'Calendar Object' and then 'Properties.' This will allow you to change the dates in the calendar. If you wish to edit the font, color or any other properties of the calendar, right-click the calendar and choose 'Properties.'

How to Add Autofill to an Excel Spreadsheet

1. Open Excel 2010 and click the 'File' tab. Select the 'Open' option. Browse the files and locate the workbook. Click the workbook and the 'Open' button. The workbook opens.
2. Click the worksheet where the autofill will be added. Insert a column adjacent to the worksheet data. For example, if your data resides in cells A1 to D20, insert a column before column A. Do this by clicking the A column header. Right-click the column header A and select 'Insert.' A blank column appears to the left of the data.
3. Insert the number '1' in the first cell, A1, adjacent to the data. Insert the number '2' in cell A2. Highlight cells A1 and A2. Notice the black square in the right corner of highlighted cells. Move your cursor over this square and notice it becomes a black ' .' Click and drag the plus down in the spreadsheet. Autofill fills in the remaining numbers of the sequence. When you have reached the desired end result, stop dragging.

How to Calculate the Number of Days between Two Dates in Microsoft Excel

1. Open the Excel workbook with dates you want to work with.
2. Enter the first or starting date in an empty cell. For example, if you want to enter the first day of the billing cycle, you can enter that in cell A1. Make sure you enter the month, day and year. If you do not enter a year, Excel will default to the current year.
3. Enter the second or ending date in another empty cell, for example, cell B1.
4. Highlight both cells and click the number format window in the 'Number' group on the ribbon. Select 'Short Date' or 'Long Date' from the menu, depending on how you want the dates displayed. The number format window typically displays the word 'General' initially, but it can display 'Custom' or any of the other formats. Excel generally recognizes dates when they are entered in date format; however, it is always useful to make sure your cells are formatted as dates.
5. Click an adjacent cell such as C1 to enter your formula. Type the following formula, substituting your own cell numbers for 'B1' and 'A1,' and press 'Enter':=B1-A1Excel will subtract one date from the other and display the number of days between the two dates.

Saturday, September 14, 2013

How to Use Conditional Formatting in Excel

1. Open a new or existing spreadsheet in Excel 2007. Enter any necessary data.
2. Select the cells that require conditional formatting. Select all cells if you don't need any cells to be exempt from the conditions.
3. On the Home tab of the Ribbon, click 'Conditional Formatting.' Choose 'Highlight Cells Rules,' then select the condition that matches your needs: For text, choose 'Text that contains...,' or for numbers, one of the other options. Let's assume you want to highlight cells containing numbers greater than 10: Choose 'Greater than...'
4. In the resulting dialog box, type '10' into the empty field, then to the right, click the drop-down arrow and choose either an existing format or 'Custom Format' to choose your own cell background and border, font color and more.

How to Jump to a Cell in Excel

1. Press the 'F5' key on your keyboard. The 'Go To' dialog box appears on your screen. Alternatively, press the 'Ctrl' and 'G' keys simultaneously to open the 'Go To' dialog box.
2. Enter the row of the cell you want to jump to in the 'Reference' box. If you want to jump to cell C15, for example, enter 'C.'
3. Enter the column of the cell you want to jump to in the 'Reference' box. If you want to jump to cell C15, you would enter '15.' Your reference should look something like 'C15.' Press the 'Enter' key on your keyboard to jump to the cell.

How to Export Pivot Table Data to Excel

1. Load the Excel pivot table spreadsheet.
2. View the table and decide which components of the pivot data you wish to export to a new Excel spreadsheet. The pivot table divides the spreadsheet data into multiple categories, based on the table's formatting. However, the table does not show the original data, only the summation information. You may wish to export all the original spreadsheet data rows that correspond to a particular number that appears on the pivot table.
3. Double-click on the chosen numerical result in the pivot table. A new spreadsheet is immediately created and all the corresponding data from the original data source is exported from the pivot table field into the new spreadsheet.
4. Click on the pivot table spreadsheet tab to return to the pivot table.
5. Double-click on any other table row's numerical result to create an additional, separate data export into another new Excel spreadsheet. Repeat this process as needed until all the separate pivot data you require is exported. Excel conveniently places each export into a new separate spreadsheet to keep the data sets independent of each other for further analysis.

Friday, September 13, 2013

How to Attach a PDF Document to an Excel Spreadsheet

1. Open or create an Excel spreadsheet that has the information you want to attach the PDF document to. Make any changes and save the spreadsheet.
2. Click the area of your Excel spreadsheet where you want the PDF document attached. Click the 'Insert' drop-down menu in Excel 2003 or earlier versions. In later versions of Excel, click 'Insert' tab.
3. Click 'Object.' Make sure you are on the 'Create New' tab.
4. Select 'Adobe Acrobat Document' from the Object Type list. If you want to attach the entire PDF document as a viewable object on your spreadsheet, click 'OK.' If you want to insert an icon that will be a clickable link to the attached PDF document, select the 'Display as Icon' check box and click 'OK.'
5. Navigate to and select the PDF document you want to attach to your Excel spreadsheet. Click 'Open.' The PDF document will open in a separate window. Close it and you will be returned to to the spreadsheet with the PDF document -- or a link to it, depending on which you chose -- attached.

How to Remove an Excel 2003 Add

1. Open Excel on your computer. Go to the 'Tools' menu.
2. Select 'Add-Ins' from the 'Tools' menu. Check the box next to the program you want to remove in the 'Add-Ins Available' text box. Click 'OK.'
3. Restart Excel to finalize the removal.

How to Use Excel's SUBSTITUTE Function

1. Choose the cell that you want to enter the formula into. Click it with your mouse. Click inside the function (fx) box, just below the Excel menu.
2. Type in the function in this format: '=SUBSTITUTE(text, 'old_text', 'new_text', instance_num).' 'Text' is the cell that contains the text you want to subsitute. 'Old_text' is the text to be replaced and 'new_text' is the text to replace it with. 'Instance_num' is the instance of the text you want to replace.
3. Create an example to learn how the substitute function works. Click on the top gray cell so that the whole spreadsheet is highlighted. Click 'format,' 'cells' from the menu. Select 'text.'
4. Enter 'November 2, 2008' in A1.
5. Click on cell B1. Go to the function box, and type '=SUBSTITUTE (A1, '2', '4', 1). Hit enter. The text in B1 will now read 'November 4, 2008.' Then edit the formula and take out the '1.' All instances of '2' will be changed to '4.'
6. Go to cell B2. In the function box, type '=SUBSTITUTE (A1, '8', '12'). Hit enter. The year now read '2012.' In this case you don't need the 'instance_num,' since there is only one '8' in the cell.
7. Get help and other examples of the substitute function by clicking 'help' on Excel's menu. In the 'keywords' box, type 'substitute worksheet function.'

How to Wire a Macro to Pull From One Spreadsheet to Another

1. Open Excel 2007 and find a workbook. Click the 'Office' button and select the 'Open' icon. Browse your files for the workbook. Click the workbook and click 'Open.' The workbook opens.
2. Click the Developer tab and select 'Record Macro.' The Record Macro dialog box appears. Enter a name for your macro in the Macro Name field. Add a brief description in the Description field. Click 'OK.' The macro starts recording.
3. Link two spreadsheets together by clicking on one cell in your first worksheet. Type '=.' Click on the second worksheet and select a cell that you want to link. Click that cell. Press the 'Enter' key. These two spreadsheets are now linked and the first worksheet will be populated with data pulled from the second worksheet.
4. Click 'Stop Recording' on the Developer tab. You have completed the recording macro process.

How to Add a Legend to an Excel Chart

1. Start Microsoft Excel 2007 and open a workbook you have already created from your files to which you want to add a legend.
2. Point and click on the chart so it is selected. You can tell the chart is selected because it will be surrounded by a light blue border.
3. Select the 'Layout' tab at the top of the Excel screen to display the 'Layout' ribbon. Locate the 'Labels' group in the 'Layout' ribbon.
4. Click the 'Legend' button in the 'Labels' group of the 'Layout' ribbon. The options for adding and displaying a legend to the chart will be displayed.
5. Choose 'Show Legend at Right,' 'Show Legend at Top,' 'Show Legend at Left' or 'Show Legend at Bottom' to place the legend in the specified location while shrinking the chart to make room for the legend. You can choose 'Overlay Legend at Right' or 'Overlay Legend at Left' to place the chart to the right or the left of the chart overlaying the chart so the chart does not shrink.
6. Watch as the legend is immediately placed inside the chart. You can move the chart manually at this time using the mouse to click and drag it to a new location.

Thursday, September 12, 2013

How to Make a Calibration Curve Graph in Excel 2007

1. Highlight both columns of your data. Left-click on the top left of your data, then drag the mouse to the bottom-right of your data.
2. Click the 'Insert' ribbon.
3. Click the 'Scatter' button in the Charts section of the Insert ribbon. Click the bottom-right icon in the Scatter Plot drop-down list. The icon has squares with lines between them. The ribbon has now automatically switched to “Design,” and your plot has appeared on screen.
4. Click “Select Data,” in the Data section of the Design ribbon.
5. Click the list item titled “Series1,” then click 'Edit' right above that.
6. Type “Calibration Curve” in the “Series Name” text box. Press 'OK.'
7. Press 'OK' again, and you are back on your spreadsheet with the Calibration Curve plot.

Wednesday, September 11, 2013

How to Transfer Data From UserForm to a Worksheet

How to Transfer Data From a UserForm to a Worksheet
1. Open Microsoft Excel and create a new blank workbook by clicking on the File menu and selecting 'New.' Save the Excel file as 'update_worksheet.xls.'
2. Keep the workbook open and open the Visual Basic Editor by hitting Alt-F11. Create a new UserForm by clicking on the Insert menu and selecting 'UserForm.' Name the UserForm 'transferForm' by changing the Name attribute in the Properties menu. Change the Caption attribute to 'transferForm.'
3. Add a textbox to the form by selecting a textbox from the toolbox and dragging it into place on the form. Name the textbox 'transferInput' in the properties window. If the toolbox is not open, select the View menu and Toolbox.
4. Add an update button to the form by selecting a CommandButton from the toolbox and dragging it into place on the form. Name the CommandButton 'transferButton' in the properties window and change the caption attribute to 'Update Worksheet.'
5. Add a close button to the form by selecting a second CommandButton from the toolbox and dragging it into place on the form. Name the CommandButton 'closeButton' in the properties window and change the caption attribute to 'Close Form.'
6. Open the Visual Basic code editor by double-clicking on the transferButton (update button). Add code to the transferButton _click() function that transfers any data entered into the textbox to the update_worksheet.xls worksheet when the user clicks the button. Close the Visual Basic code editor.Private Sub transferButton_Click()Dim transferWorksheet as WorksheetSet transferWorksheet = Worksheets('Sheet1')transferWorksheet.Cells(1,1).Value = Me.transferInput.ValueEnd Sub
7. Reopen the Visual Basic code editor by double-clicking on the closeButton (close button). Add code to the closeButton_Click() function that closes the form when the user clicks the button. Close the Visual Basic code editor.Private Sub closeButton_Click()Unload MeEnd Sub
8. Run the form's code by clicking on the Run menu and selecting 'Run Sub/UserForm.' Enter data into the input field and click the 'transferButton.' Ensure that the data is successfully transferred to the first cell value in the Excel worksheet, switching to Excel if necessary. Save and close the running UserForm.
9. Use an Excel event procedure to open the UserForm when a user opens the workbook in Excel. Open the Project Explorer and double-click the ThisWorkbook code window. Enter a macro that opens the form when the worksheet opens and save the macro. Save and close Visual Basic and update_worksheet.xls.Private Sub Workbook_Open()transferForm.ShowEnd Sub
10. Reopen 'update_worksheet.xls.' The UserForm transferForm will open. Type some text into the 'transferInput' field and click the 'transferButton.' The text entered into the transferForm UserForm will be transferred to the first cell in Sheet1 in update_worksheet.xls.

How to Embed a SWF Into Excel 2007

1. Open the Microsoft Excel 2007 file on your computer that you want to insert a SWF file into.
2. Click the 'View' option from the top toolbar menu and then click on the 'Toolbars' option.
3. Click the 'Control Toolbox' option and then the Control Toolbox toolbar will appear at the top of the page.
4. Click the 'More Controls' option from the Control Toolbox toolbar and then click the 'Shockwave Flash Object' option. The SWF object will then appear in your spreadsheet.
5. Right-click the SWF object and then click the 'Properties' option. Enter the URL into the 'Movie URL' field.
6. Click the box next to the 'Embed Movie' field so it's selected, and then click the 'OK' button.

How to Change the Case in an Excel Spreadsheet

1. Type the following formula into the cell directly to the right of the cell you want to change the case in:=LOWER(A1)
2. Change 'A1' to the cell number you want to change. For example, if your text is in cell B3, then change 'A1' to 'B3' so that the formula reads:=LOWER(B3).
3. Change 'LOWER' to 'UPPER' or 'PROPER,' if desired. (Leave the function intact if you want to change to lowercase).
4. Press 'Enter.' Excel converts the text to the new case.
5. Copy the new data and paste it over the original cell to replace it.
6. Delete the cell with the function in it.

Subscribe to: Posts (Atom)