Friday, April 19, 2013

How to Create a Family Tree on Microsoft


1. Open PowerPoint 2010 and click the 'File' tab. Click 'New.' Type 'Family Tree' in the search box. Review the available family tree templates that appear.
2. Click the family tree template to see a preview of it in the right task pane. Click the 'Download' icon to download the template.
3. Click in the text boxes and highlight the default text. Type in your family information. Save your changes by clicking the 'Save' icon on the Quick Access Toolbar.
Read more ►

How to Use Excel's GETPIVOTDATA Function


1. Understand the syntax of the GETPIVOTDATA function. The formula is =GETPIVOTDATA(pivot_table, name). Pivot_table references a cell in the PivotTable or a range of cells in the PivotTable, a label of a cell above the PivotTable or the name of the range that contains the PivotTable. 'Name' is text enclosed in quotations marks that describes the data.
2. Create a new workbook to practice using pivot tables. Label columns A, B and C with the headers 'Name,' 'Month' and 'Sales.' Under the header row, fill in several of the rows with data. Include the same name and month more than once.
3. Hold down your left mouse key and drag it across and down so that all the cells are highlighted. Click 'Data' and select 'PivotTable and PivotChart Report.' Follow the wizard to create a PivotTable.
4. Click 'Layout' in Step 3 of 3. Depress the left mouse key and drag the 'Name' button to the Row field, the 'Sales' button to the Data field, and the 'Month' button to the Column field. Click 'OK.'
5. Choose an empty cell in column A and so that it gets entered in the box on Step 3. Choose 'Existing Worksheet.' Click 'Finish.'
6. Select the first cell of the pivot table. Click 'Insert' on the menu. Click 'Name,' 'Define' and enter a name for the table. Click 'OK.'
7. Pick an empty cell. 'Type =GETPIVOTDATA.' Insert a '(' followed by the name of the table, then the calculations to be shown in the cell. Type a single name and month to get the sales total for that month, or a single name or month to get the total sales for that name or month.
Read more ►

Thursday, April 18, 2013

How to Use the Find the Method in Excel 2003 Using Visual Basic


1. Launch Excel 2003.
2. Open the 'File' menu at the top of the screen and click 'Open.' Double-click the spreadsheet that needs to use the 'Find' method.
3. Click the 'Tools' menu at the top of the window and scroll down to the 'Macro' entry. Click the 'Visual Basic Editor' entry in the sub-menu.
4. Click the name of the worksheet you need to use in the list of worksheets at the left side of the screen. Click inside the code editor screen at the left side of the window.
5. Type the phrase 'Sub Finding()' at the top of the screen to let the Visual Basic editor know where the code should begin. The word 'Finding' can be replaced with anything you'd like, such as 'Sub MyFindCode()' or something similar. Tap 'Enter' twice and type 'End Sub' to let the editor know where your code ends.
6. Move the cursor in between the 'Sub' and 'End Sub' headings. Type 'Cells.Find().Activate' to tell the editor to use the 'Find' method. Move the cursor inside the '()' portion of the 'Find' method and use the 'What' argument to indicate what you are looking for. For example, the code should read 'Cell.Find(What:='555').Activate' if you want to find a cell containing the numbers '555.'
7. Fill in the remaining arguments for the 'Find' method to let the editor know to search the entire worksheet for the specified numbers or letters. For example, if you want to search for '555,' your entire line of code should read 'Cells.Find(What:='555', After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate.'
8. Click the green arrow icon labeled 'Run' at the top of the screen to use the 'Find' method and locate the cell containing the numbers or letters.
Read more ►

How to Find the Weighted Mean in Excel 2007


1. Input your data points into column A in your Excel 2007 worksheet, one number per cell. For example, you might input the numbers 90, 86, 45, 67, 78, 76, 79, 82.
2. Input the weight of each of the numbers in column B. The weight of each number should sit directly to the right of the number. The weights you choose do not matter, they are only meaningful relative to each other. For example, if a data point has a weight of 1 and another point has a weight of 2, the second point will be twice as important to the mean, but if both data points have a weight of 2, they will be identically important. The weights might be 1, 1.5, 2, 1.25, 3.6, 4, 2, 1.
3. Write '=A1*B1' (without quotes) in cell C1.
4. Left-click cell C1 to highlight it, then press 'Ctrl C' to copy it.
5. Highlight column C by clicking the letter 'C' above the column, then paste the formula by pressing 'Ctrl V.'
6. Type '=SUM(C:C:) / SUM(B:B)' (without quotes) in cell D1 to find the weighted average. In the example, the weighted average is 74.47 (rounded).
Read more ►

How to Calculate a Standard Error Regression


1. Open Microsoft Excel 2007. Input your data into the spreadsheet into two columns. You should have known values for y (your dependent variable) and each independent variable.
2. Go to the 'Data Analysis' command under the 'Data' tab on the user interface. If you do not have this function, you must add it in. To add the analysis to Excel, go to the 'Microsoft Excel' button in the right-hand corner of your screen. Go to 'Excel Options and Add-ins.' Select the 'Data Analysis' toolpak. Download to your Excel program.
3. Select 'Data Analysis.' A list of statistical choices will appear. Choose 'Regression.'
4. Input the data in the correct ranges. A box will prompt with an input for Y-range and X-range. In addition, select where you want the results to appear, on a separate worksheet or the same worksheet. If you want the results to appear on the same worksheet, select 'Output Range' and highlight the cells you want the results to appear on.
5. Click OK and look at the summary output. There will be a list of the regression results, including the standard error.
Read more ►

How to Make a XY Graph on Excel


Create XY Graph In Excel 2003
1. Open Microsoft Excel 2003.
2. Place your data such that all the X-values are in the same row or column. Place your other set of data in an adjacent row or column. For example, if you have six X-values and six Y-values, place all the X-values in column A and all the Y-values in column B.
3. Select the range of values to be included in the XY chart. To select the range of the six XY-value example, click the first cell included, which is A1, then drag your mouse to the last cell to be included, which is B6.
4. Go to the 'Insert' menu, and click 'Chart.'
5. Select 'XY (Scatter)' under the 'Chart Type' box. Choose the chart sub-type you want to use under the 'Chart sub-type' box.
6. Click 'Next' to show you the data range and a sample view of the chart.
7. Click 'Next' again to go to the 'Chart Options.' Enter the information for 'Chart Title,' 'Value (X) axis,' and 'Value (Y) axis.' These are text information you can use to make your chart descriptive.
8. Click 'Next' to go to the 'Chart Location' box. You can either place the chart as a separate worksheet or as an object in the same worksheet as your data points.
9. Click 'Finish' to display your XY chart.
Create XY Graph In Excel 2007 or 2010
10. Open Microsoft Excel.
11. Place your data such that all the X-values are in the same row or column. Place your other set of data in an adjacent row or column. For example, if you have six X-values and six Y-values, place all the X-values in column A and all the Y-values in column B.
12. Select the range of values to be included in the XY chart. To select the range of the six XY-value example, click the first cell included, which is A1. Drag your mouse to the last cell to be included, which is B6.
13. Go to the 'Insert' tab and click 'Scatter' in the 'Charts' group menu.
14. Click the 'Chart Area' of the XY chart. This shows the 'Chart Tools,' 'Design,' 'Layout,' and 'Format' tabs specific to the XY chart.
15. Click the chart style you want to use under the 'Design' tab.
16. Click 'Chart Title,' and type the title you want for the chart.
17. Click 'Axis Titles' on the 'Layout' tab. Click the 'Primary Horizontal Axis Title' to place a title in the horizontal axis. Click the 'Primary Vertical Axis Title' to place a title in the vertical axis.
18. Press 'Enter' to show the chart.
Read more ►

Wednesday, April 17, 2013

How to Disable Links in Excel 2007


1. Click the Excel button on the top toolbar, and then select the 'Excel Options' button at the bottom. Choose 'Proofing' from the left menu.
2. Click the 'AutoCorrect Options...' button under the 'Autocorrect options' section. Select the 'Autoformat as you type' tab, then uncheck 'Internet and network paths as you type' under the 'Replace as you type' section. Click 'Ok' to save the changes.
3. Click 'Ok' in the 'Excel Options' window to close it and return to the spreadsheet. Type or paste a URL into a blank cell then hit the space bar or enter key to verify the changes.
Read more ►

How to Turn on Sounds in Excel 2007


1. Click the 'Microsoft Office' button. A list of commands opens.
2. Click 'Excel Options.' The 'Excel Options' window opens.
3. Click the 'Advanced' tab.
4. Scroll down to the 'General' section.
5. Check the box for 'Provide Feedback With Sound.'
6. Click 'OK.'
Read more ►

How to Reset the Excel 2003 Application to Default Settings


1. Save and rename your workbook and worksheet settings templates if you want to use them again. Save them in the Templates folder, which is located here: C:\Documents and Settings\user_name\Application Data\Microsoft\Templates.
2. Click on your computer's start menu and search for the following in the search dialog box at the bottom of the menu: C:\Program Files\Microsoft Office\OFFICE11\XLSTART. Delete any template files in this folder. If you'd only like to delete workbook settings templates, delete templates with 'Book.xlt.' If you'd only like to delete worksheet settings templates, delete templates with 'Sheet.xlt.'
3. Open Microsoft Excel 2003 on your computer if there are no files located in the folder specified in Step 2. Click on 'Tools,' then 'Options' and then on the 'General' tab. Delete the Book.xlt and/or Sheet.xlt files specified beside 'At startup, open all files in.'
Read more ►

How to Make an Excel Chart With a Negative and Positive Axis


Numerical (Value) Axis
1. Open the Microsoft Excel 2010 file that contains the chart you want to alter.
2. Click anywhere on the chart. Select the 'Format' tab at the far-right end of the ribbon. Click the drop-down box that appears on the far-left end of the ribbon. Select your desired axis from the list. Then, choose the 'Format Selection' button located right below the drop-down box.
3. Click the radio button next to 'Fixed' under the 'Minimum' heading. Place your cursor into the text box next to this button and enter the lowest number you want displayed on your axis. Place a dash before you enter the number to signify that you are entering a negative number.
4. Press 'Enter' to close the Format Axis window. Your chart will automatically update to show the negative axis.
Text-Based (Category) Axis
5. Open the Excel 2010 spreadsheet that holds the graph you want to manipulate.
6. Click on the top cell in any empty column on your spreadsheet. Enter the lowest value that you want your chart to display on the axis in this cell. Be sure to place a dash in front of a negative number. Press 'Enter' to move down to the next cell and then enter the next value you want displayed on the axis. Since you will be manipulating a text-based axis, you will have to specify each value here, as Excel will not treat these values as numbers. Continue to add values until you have added enough to fill your entire desired axis. If you are counting up from negative numbers into positive numbers, skip zero.
7. Select anywhere on the chart that you want to change. Click the 'Design' tab at the top of the screen. Find the 'Select Data' button, which is located in the Data area of the ribbon, and click it. The Select Data Source window will appear on your screen.
8. Click the 'Edit' button under the axis you want to change. The window will minimize. Select on the letter at the top of the column where you entered your desired axis labels. Choose 'OK' twice to use these labels instead of the default ones.
9. Click the 'Layout' tab at the top of the screen. Select the drop-down box that appears in the ribbon and select the axis that you just altered. Click the 'Format Selection' button.
10. Click the radio button next to 'At Category Number' under the Vertical Axis Crosses heading. Enter a number into the text box equal to the absolute value of the lowest number on your axis, plus one. So if your axis goes down to negative five, you would enter six into this box. Click 'Close' and your vertical axis will now cross your horizontal axis at the right spot.
Read more ►

Tuesday, April 16, 2013

How to Import an Excel Spreadsheet into Another Sheet


Linking
1. Launch Microsoft Excel and open the source file that contains the data to be linked.
2. Open or create the destination file in which the data will be displayed.
3. Navigate to the source spreadsheet and highlight (or 'Select') the cell or range of cells to be imported by clicking the cell, or clicking the top left corner of the range of cells and holding the mouse button down while dragging the cursor to the lower right corner of the range of cells and then releasing the mouse button. Alternately, click the gray button that contains the row number or column letter to select an entire row or column, respectively, or click the gray button in the upper left corner of the spreadsheet between the row header '1' and the column header 'A' to select the entire sheet.
4. Right-click any cell in the selected range of cells and click 'Copy.'
5. Navigate to the destination spreadsheet where the data will reside and click once on a cell that you want to make the upper right corner of the copied data from the source spreadsheet. If an entire row or column was selected, click the first cell in the row or column or select the row or column by clicking the gray box that contains the number or letter of the row or column. If you selected an entire spreadsheet click the cell 'A1' or the gray button in the upper left corner of the spreadsheet between the row header '1' and the column header 'A.'
6. Right-click in the selected cell or header.
7. Select 'Paste' to copy the contents of the original cells exactly as they are to the new location, with no links back to the original data. This will allow a duplicate spreadsheet to be created including data and formulas, but subsequent changes to the original spreadsheet data will not be reflected in the new location. Formatting will be copied with this method.
8. Select 'Values' to copy the current values stored in each cell to the new location. This will not copy any formulas, but will copy the current results of any calculations performed by any formulas. Subsequent changes to the original spreadsheet data will not be reflected in the new location. Formatting will not be copied with this method.
9. Select 'Formulas' to copy all the existing formulas and values to the new location. Subsequent changes to the original spreadsheet data will not be reflected in the new location. Formatting will not be copied with this method.
10. Select 'Transpose' to copy the data and formulas along with the formatting to the new location in a transposed orientation, so that rows become columns and columns become rows. Subsequent changes to the original spreadsheet data will not be reflected in the new location.
11. Select 'Formatting' to copy the formatting of the selected cells but leave the cells empty. No values or formulas will be copied, but format attributes such as cell color and currency formatting will be reproduced.
12. Select 'Paste Link' to create a link to the source spreadsheet that will be updated when the original spreadsheet data is updated. This will allow the data from the original spreadsheet to be shown in the destination spreadsheet without allowing any changes to be made to the original spreadsheet from within the destination spreadsheet.
Read more ►

How to Remove Duplicates From Access Query


1. Open an Access 2007 database on your computer. Make sure it has a table that contains duplicate data. Select the 'Office' button and select 'Open.' Search your computer and locate the database. Click on the database and select 'Open.'
2. View the tables in your database in the Navigation Pane. Open one of the tables and visually note the duplicates and note the total number of records. Select the 'Create' tab on the ribbon. Click on the 'Query Design' button. Add the table you have just viewed by clicking on the table and selecting 'Add.' Select 'Close.' Add some fields to the query by double clicking on the fields.
3. Select the 'Properties' option by right clicking in the gray area of your query design field. This will display the Properties Sheet dialog box. Locate the 'Unique Values' field. Change the field to 'Yes.'
4. Run the query by clicking on the 'Run' button. The query will return the distinct values from the table. This is due to the Unique Values field being changed to Yes. Save the results by clicking on the 'Save' icon on the Quick Access Toolbar.
Read more ►

How to Insert a Dynamic Date and Time in Excel


1. Start Microsoft Excel 2007, and open an existing spreadsheet from your files that you want to insert a dynamic date and time into. Or, start a new, blank spreadsheet.
2. Click to select the cell into which you want to insert the dynamic date and time. The cell will be outlined in a thick, black line indicating it is selected.
3. Use your keyboard to type '=now()' (without the quotation marks) into the selected cell. This is the formula that is used to instruct Excel 2007 to insert the dynamic date and time.
4. Press the 'Enter' key on your keyboard to enter the formula into the selected cell. You will now see the current date and time appear in the cell you typed the formula into. This date and time will update every time the 'Enter' key is pressed while working in Microsoft Excel.
5. Repeat Steps 2 through 4 above to insert the dynamic date and time into any other cells inside the open spreadsheet you would like to display the current date and time.
Read more ►

How to Convert a Word Document to a CSV File


1. Open the Microsoft Word document in Word and save it as a '.txt' file. To do this, select 'File' and then chose 'Save As.' Next, use the down arrow next to 'File as Type' and select '.txt.'
2. Open the Microsoft Word document in MS Excel by selecting 'File' and then 'Open.' Find the file in the 'Look in' directory from within the 'Open' dialog box.
3. Click option 'Delimited' and then click '1' beside 'Start Import at Row' to indicate the first row to import the data. Next, select the geographical location of the original file format beside 'File Origin.' When done, click 'Next.'
4. Click 'Tab' under Delimiter preference and then click 'Next.' Additional options include 'Semicolon,' 'Comma,' 'Space' and 'Other' for you to customize the separating character type.
5. Click 'General' as the format option for the 'Column Data 'and then click 'Finish.' Other column data formats include 'Date,' 'Date' and 'Do not import column (skip).'
6. Select 'File' and then choose 'Save As' from the toolbar menu.
7. Choose '.csv' as the 'Save as Type' and then click 'Save.'
Read more ►

Monday, April 15, 2013

How to Convert Word to Excel 2003


1. Open the Word document with the data you want to convert to Excel 2003.
2. Highlight the data with your mouse. Choose 'Convert Text to Table' from the 'Insert' menu and convert the text into a table if it is not already.
3. Hover your mouse over the table until you see a ' ' appear in the upper-left corner. Click on the ' ' sign to select the entire table.
4. Click 'Edit' and then 'Copy.'
5. Launch Excel 2003 and click into a blank cell.
6. Click 'Edit' and then 'Paste.' Your data will appear within Excel.
7. Click 'File' 'Save As' and choose 'Excel Workbook (.xls)' from the 'Save as Type' drop-down field. Click 'Save.'
Read more ►

Blogger news