Wednesday, May 15, 2013

How to Remove Hidden Macros in an Excel Spreadsheet


1. Double-click the Microsoft Excel document you want to edit. This opens the document and loads the Excel application.
2. Click the 'View' menu option and select 'Unhide.' In the section labeled 'Personal,' select 'Unhide workbooks' from the list of options.
3. Click the 'Developer' tab at the top of the Excel window. If you don't have the Developer tab, click the Office button and select 'Excel Options.' Click the 'Popular' tab and select 'Show Developer tab in the Ribbon.' Press the 'OK' button.
4. Click the 'Macros' button in the 'Developer' tab. A pop-up window opens with a list of macros programmed for the spreadsheet.
5. Click the name of the macro you want to delete. Click the 'Delete' button. Click 'Yes' to confirm that you want to delete the macro. It's now deleted.
Read more ►

How to Create Sequences in Excel


1. Click the first cell in the row or column that you want to contain the sequence and type the first item. For example, to create the sequence of 'Red,' 'Green' and 'Blue,' type the word 'Red.' Press 'Enter' to move to the next row or press the right arrow key to move to the next column.
2. Type the next item in the sequence, such as 'Green.' Press 'Enter' or the right arrow key.
3. Type the next item in the sequence, such as 'Blue.' Repeat this process until you have typed every item in the sequence.
4. Click and drag with the mouse pointer to highlight the cells containing the complete sequence plus the additional first item. You should see a large black square in the lower-right corner of the box outlining the highlighted cells.
5. Click and drag the black square to auto-populate the blank cells below or to the right with the sequence that you have entered. As you drag the square, Excel displays a small pop-up message showing the data that it will put in each cell.
6. Release the mouse button to stop auto-populating the sequence.
Read more ►

How to Open Large Files in Excel 2003


1. Launch Excel, and navigate to the 'tools' menu.
2. Choose the 'macro' submenu, and select the Visual Basic editor.
3. Choose 'module' from the insert menu.
4. Copy and paste the following text:Sub LargeFileImport()'Dimension VariablesDim ResultStr As StringDim FileName As StringDim FileNum As IntegerDim Counter As Double'Ask User for File's NameFileName = InputBox('Please enter the Text File's name, e.g. test.txt')'Check for no entryIf FileName = '' Then End'Get Next Available File Handle NumberFileNum = FreeFile()'Open Text File For InputOpen FileName For Input As #FileNum'Turn Screen Updating OffApplication.ScreenUpdating = False'Create A New WorkBook With One Worksheet In ItWorkbooks.Add template:=xlWorksheet'Set The Counter to 1Counter = 1'Loop Until the End Of File Is ReachedDo While Seek(FileNum)
5. Press the F5 key to run the macro.
Read more ►

How to Use the Conditional Sum Wizard in Excel


1. Open the file that contains that data that you need to run the conditional sum wizard on.
2. Add a header row to your data, if it lacks one, by right-clicking on the number next to the first row and choosing 'Insert.' Type in names for the columns in the cells directly above the data.
3. Click on the top-left cell of the data range. Move to the bottom-right cell in the data range. Press and hold the 'Shift' button and click on the cell to select the entire data range.
4. Click the 'Formulas' tab at the top of the screen, then click on the 'Conditional Sum' button located on the far right side of the ribbon, under the 'Solutions' group.
5. Click 'Next' on the first screen, as you have already defined the data area.
6. Select the column that you want to sum from the drop-down menu at the top of the window, identified as 'Column to sum.' Then select the column that you want to evaluate for a condition in the drop down menu in the middle of the window, identified as 'Column.' Next to that column you can choose an operator, such as 'equals,' 'greater than' or 'less than.' Finally, you can type in the value that you want to check the original column for in the 'This value' box. Press the 'Add Condition' button when you are done, and then press 'Next.'
7. Choose between having Excel export your result as just a formula in a cell, or as the formula in a cell as well as the conditional data next to that cell. Choosing the latter lets you change the conditions of the formula without having to go through the wizard again. Press 'Next' when you have made your selection.
8. Select a cell where you want the condition to be placed, if you chose that option, and the cell for the conditional sum formula. Press 'Finish' to complete the process.
Read more ►

Tuesday, May 14, 2013

How to Create a Macro to Run an Access Query Paste the Result Into Excel


1. In Access, create a table of sample data: enter the following data in a new table:the accidental tourist,12/1/2009,$6.01
the accidental tourist,12/3/2009,$7.98
iron john,12/5/2009,$4.98
iron john,12/6/2009,$5.98
2. Double-click the column headers (e.g. 'Field1') and replace each with these headers, in this order:book,datesold,netsaleSave the table ('control-s') with the name 'books.'
3. Create a query from the table, and press the 'Esc' key in the 'show table' dialog box. Right-click on the query's tab and select 'SQL view.' Enter the following in the code window:SELECT books.* INTO queryresults
FROM books
WHERE (((books.book) Like '*acc*'));Save the query ('control-s') and name it 'vbaquery.'
4. Open Excel and press the toolbar's 'Data>From Access' icon. Select the 'queryresults' table in the 'Select Table' dialog box. Click 'OK' on the 'Import Data' dialog box and notice the query's results: only the 'iron john' books are shown. Save the Excel file with any name, and close it.
5. Reopen the 'books' database in Access. Open the 'vbaquery' and revise its 'Criteria:' field to read 'Like '*acc*'' (Don't type the double quotes. Do type the inner, single quotes.) Resave the query.
6. Create a new query. Type the following SQL statement in the 'SQL view' window, then save the query as 'dropqueryresults':DROP TABLE queryresults;
7. Enter the Visual Basic integrated development environment (IDE) by pressing 'alt-f11,' then select 'Insert>Module.' Paste the following code into the new module's blank code window:Public Sub runquery()
'delete the results table first
On Error GoTo DO_QUERY
RunQueryForExcel ('dropqueryresults')DO_QUERY:
RunQueryForExcel ('vbaquery')
End SubPublic Sub RunQueryForExcel(qName As String)
DoCmd.SetWarnings False
CurrentDb.Execute qName
DoCmd.SetWarnings True
End Sub
8. Position the cursor anywhere in the 'runquery' subroutine and press 'F5' to run the query. Reopen the Excel workbook you previously opened and notice the updated data: your macro has replaced the 'iron john' rows with the 'accidental tourist' rows. (Access 2007 or later users can stop here.)
9. (For users of Access 2003 and earlier). Use step 7 to paste the following code into a new module in the Visual Basic IDE:
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Sub pasteToExcel()Const qName = 'vbaquery'
Dim db As DAO.Database
Dim recset As DAO.Recordset
Dim s As String
Dim appXL As Excel.Application
Dim ro, co'''''''''''''''''''''''''
Set appXL = CreateObject('Excel.Application')
appXL.Workbooks.AddSet db = CurrentDb
Set recset = db.OpenRecordset(qName)
s = 'book' ', ' 'dateddsold' ', ' 'netsale' vbCr
appXL.ActiveSheet.Cells(1, 1) = s
ro = 2
co = 1
s = ''
Do While Not recset.EOF
s = s recset![book] ', ' recset![datesold] ', ' recset![netsale] vbCr
appXL.ActiveSheet.Cells(ro, co) = s
recset.MoveNext
ro = ro 1
s = ''
Loop
recset.Close
db.Close
appXL.ActiveWorkbook.SaveAs ('c:\dataFromAccess.xls')
appXL.QuitEnd Sub
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Select 'Tools>References' and check the 'Microsoft Excel Objects Library.'
10. Return to Access and do steps 1 to 3. However, for step 3, paste this SQL code into the SQL code window:SELECT books.*
FROM books
WHERE (((books.book) Like '*acc*'));
11. Return to the Visual Basic IDE. Place the cursor inside the 'pasteToExcel' function and press 'F5' to run the function. Open the Excel file 'c:\dataFromAccess.xls' to view the results.
Read more ►

How to Create a Newsletter in Microsoft Office


1. Open Microsoft Word, and select a newsletter template by clicking the drop-down menu File > New Works Template > Tasks. Additional templates may be downloaded from the Microsoft Office Online website.
2. Create a nameplate for your newsletter. Using Word's 'header' is the simplest way to do this. Select View > Header and Footer from the drop-down menu to edit text boxes within the header and footer. Center your newsletter's title and add clip art, if desired, selecting Insert > Picture > Clip Art from the drop-down menu. If the software is available, you may use Copy and Paste to import clip art from Publisher.
3. Add content to your newsletter. Write or solicit articles from family members or co-workers, create a list of upcoming events or feature one-line quotes from members of the team.
4. Create a calendar to import using Excel. Select the Tools drop-down menu, and select CalendarMaker. The calendar may be copied and pasted into your Word newsletter.
5. If the software is available, create a mailing list using InfoPath.
6. Print enough copies for distribution. The easiest and most cost-efficient format for your newsletter is one page, front and back, using black ink.
Read more ►

How to Use VLookup in Excel 2003


1. Open the Excel 2003 file that contains the data table that you want to work with.
2. Select the top-left cell of the data table and hold down the mouse button. Move the mouse to the bottom-right cell and select it. Click the 'Data' menu at the top of the screen and select 'Sort.' Select 'Ascending' to sort the information from smallest to largest. The first column has to be sequential in order for VLookup to work.
3. Select any cell outside of the data table. Press the '=' key to start your formula. Type 'vlookup('. The open parenthesis is required to begin entering arguments.
4. Type in the value that you want to search for, or a reference to a particular cell in the first column. If you are searching for a text string, wrap the text in double quotes. You may include '?' and '*' as wildcards. '?' will replace any one value, while '*' will replace any string of values. If you need to search for a question mark or an asterisk specifically, precede the character with a tilde. Remember that VLookup will only search in the first column of your data table. Press the comma key when you are done.
5. Click on the top-left cell of the table and hold down the mouse button. Drag your mouse to the bottom-right cell of the table and release the button. You will see those two cells appear in your formula bar. Press the comma key.
6. Press the number key that corresponds to the column where your desired result resides. Columns in the table are numbered starting from the left and counting up by one for each column. If you enter '3' here, VLookup will find your value in the first column, then give you whatever is in that same row in the third column. While you may enter '1' here, VLookup will return the value that you searched for. Press the comma key.
7. Enter the word 'false' if you want VLookup to find an exact match for your search value in the first column. If you only want to find an approximate match, erase the last comma that you entered, as this command is optional and searching for an approximate match is the default setting. If you search for an approximate match, VLookup will use the next largest value if it cannot find an exact match. Type in a close parenthesis and press the Enter key to complete the process.
Read more ►

How to Remove Checkboxes in Excel 2007


1. Open Microsoft Excel 2007.
2. Click the 'File' tab, click 'Open' and browse to the spreadsheet with the check boxes to delete. Double-click the name of the file to open it.
3. Click once on the first check box to remove. Press the 'Delete' key to remove it. Scroll throughout the spreadsheet or page tabs at the bottom of the work area and continue clicking and pressing the 'Delete' key to remove the check boxes.
Read more ►

Monday, May 13, 2013

How to Make a Pivot Table in Excel 2007


1. Open the Microsoft Excel spreadsheet program. Click 'Start' on the main operating system menu. Then choose 'Programs' and from the program menu select 'Microsoft Excel.'
2. Choose a cell in a Microsoft Excel spreadsheet. Make certain the call range has a column heading. From the main tab menu, select the 'Insert' tab, then in the table menu click on 'Pivot Table.'
3. Select the data range to be analyzed. A user can either select a range of data in the current spreadsheet or choose data from an external source. Also, determine if the pivot will appear in the current worksheet or in a new worksheet.
4. Create the pivot table in Microsoft Excel. With the correct data source selected and each of the columns has a heading, click 'OK.' The pivot table will appear either on a new or existing worksheet. Now check the data columns to display in the pivot table.
5. Make sure the data in the pivot table displays correctly. Sometimes the pivot table will have formatting issue if the original table had labeling or data problems. Check the drag field boxes if there are errors in appearance or formatting. Experiment with the pivot table data by dragging the data into different fields until the pivot table display is correct.
Read more ►

Sunday, May 12, 2013

How to extend the Microsoft Office 2010 trial


1. Install the Office 2010 trial of your choice. Wait until the end of the initial 30 days to complete the rest of the steps. Office will have a pop-up reminder when the trial is about to expire.
2. Run the command prompt as an administrator. To do this in Windows 7, go to the Windows orb logo (formerly the 'Start' menu). In the search field, type 'cmd' and then press the 'Ctrl' 'Shift' 'Enter' keys together. Click 'Yes' if prompted by the User Account Control.
3. Enter 'C:\Program Files\Common Files\Microsoft shared\OfficeSoftwareProtectionPlatform\ OSPPREARM.exe' into the command prompt window. To paste the address into the command prompt right-click and select 'Paste' in the command prompt window. Press 'Enter.' If your main hard drive is not 'C,' or if you have a different operating system, OSPPREARM.exe could be located somewhere else. In Windows 7, you can search your hard drive for the file by opening your main hard drive folder and typing 'OSPPREARM' in the search box in the upper right-hand corner. Once you have located OSPPREARM.exe, copy the location into the command prompt and press 'Enter.'
4. Close the command prompt if the message 'Microsoft Office rearm successful.' is displayed. Your trial period was extended 30 days. If you do not see the message, try repeating the steps above. When you have reached the maximum rearms, the command prompt will produce an error message.
Read more ►

How to Delete Duplicate Rows in Excel Based on One Column


1. Select the entire column of data by left clicking on the top cell and dragging the cursor to the bottom cell.
2. Click on the 'Data' tab and select 'Filter > Advanced.' Click the 'Filter the list, in-place' radio button and check the box next to 'Unique records only.' Click the 'OK' button.
3. Open the Office Clipboard by pressing the 'Home' tab and choosing 'Clipboard.'
4. Hold down the Crtl and C keys at the same time to copy the cells into the clipboard.
5. Click on the 'Filter' tab to restore the original cell data.
6. Paste the modified cell contents from the clipboard into the cells by clicking on the contents in the clipboard.
Read more ►

How to Make a Gantt Chart Using Microsoft


1.
Open a new Excel worksheet. Enter column headings into Row 1, such as 'Start Date,' 'Amount Completed' and 'Amount Remaining.' Enter your data into the columns for each heading.
2.
Select the cells that contain data. If you are using Excel 2003 or earlier, click the 'Chart Wizard' button on the 'Standard' toolbar. In Excel 2007, go to the 'Chart' section on the 'Insert' tab. Select 'Bar' as the chart type, and then select 'Stacked Bar' as the subtype. Click 'Finish.'
3.
Double click the first color in the chart bars to open the 'Format Data Series' dialog box. Go to the 'Patterns' tab. Select 'None' for both 'Area' and 'Border,' and then click 'OK.'
4.
Double click the 'Category Axis,' go to the 'Scale' tab and select 'Categories in Reverse Order.' Go to the 'Font' tab and change the font size to '8.' Click 'OK.'
5.
Double click the 'Value Axis' and go to the 'Alignment' tab. Enter '45' in the 'Degrees' box. Go to the 'Font' tab and select 'Bold' under 'Font Style' and '8' for the font size. Click 'OK.'
Read more ►

How to Change Columns From Numbers to Letters in Excel 2007


1. Open Microsoft Excel 2007 to open a blank worksheet. Locate the 'Office' button in the top left hand corner next to the exit button. Click on the 'Office' button.
2. Click on 'Excel Options.' A new menu window will appear in which you need to click on the 'Formulas' tab.
3. Deselect he 'R1C1 Reference Style' check box. Click 'OK'to close the window and save your selections. This will change the columns from numbered to lettered immediately. To change the R1C1 reference style again simply revisit the excel options again and check the R1C1 box.
Read more ►

Saturday, May 11, 2013

How to Reassign a Macro Button in Excel 2003


1. Open Excel 2003 and locate the macro button on your toolbar. Click 'Tools' on the menu bar and click 'Customize.' The Customize dialog box will appear. Click the 'Commands' tab. Click the macro on the toolbar and click the 'Modify Selection' button in the Customize dialog box.
2. Select 'Assign Macro.' The Assign Macro dialog box appears. Select a macro from the list and click 'OK.' If necessary, you can update the macro image by clicking the 'Modify' button in the Customize dialog box and select 'Change Button Image.'
3. Update the macro description by clicking the 'Modify' button and selecting 'Name.' Type a new name over the previous macro description. Click 'Close.' View the newly assigned macro on your toolbar.
Read more ►

How to Make Box Plots in Excel


1. Open the Excel file that contains the data you want to represent as a box plot.
2. Scroll to the bottom of the data set and type in five new row headers on the left-hand side of the screen. These headers, from top to bottom, are: “First Quartile,” “Minimum,” “Median,” “Maximum” and “Third Quartile.”
3. Select the cell to the right of “First Quartile.” Type in “=Percentile (XXX, 0.25),” where XXX is the field of data for that sample. Write out the field by typing in the column and row of the first cell in the field, then add a colon, and then type in the column and row of the last cell in the field.
4. Type in “=min (XXX)” next to the “Minimum” cell. Then type in “=median (XXX)” next to the median cell and “=max (XXX)” next to the maximum cell. Finally type in “=percentile (XXX, 0.75)” next to the “Third Quartile” cell. The “XXX” in all of these will be the same data field.
5. Copy and paste these formulas to the cells to their right, if you need to create a box plot for more than one data sample. If your second sample is located in the cells directly to the right of your first sample, Excel will automatically change the formulas to reflect this.
6. Select all of the cells in the chart you just made, including the row headers in the first column. Click “Insert” on the top of the window, then click the “Line” button in the “Charts” area. Choose the “Line with Markers” button, and your chart will appear.
7. Click the “Switch Row/Column” button at the top of the screen.
8. Right-click on any of the data points on the chart. Select “Format Data Series” from the menu that pops up. Then choose “Line color” on the left side of the new window, and click the radial button next to “no line” before clicking “Close.” Repeat this for the other four data lines.
9. Click the Layout tab at the top of the screen, then click the “Analysis” button. Choose “Lines” from the menu and finally click on “High-Low Lines.” Then click “Analysis” and “Lines” again, but this time select “Up/Down Bars” to reveal a menu, and select the “Up/Down Bars” button from this new menu.
Read more ►

How to Insert a Countdown Timer in an Excel Worksheet


1. Open the Excel 2010 worksheet where you want to add a countdown timer. Right-click the cell where you want the timer to go and choose 'Format Cells.' Click 'Custom' on the left side of the window that appears and then select 'h:mm:ss' from the list on the right. Click 'OK' to continue.
2. Hold down 'Alt' and press 'F11' to open up the VBA console. Select your current worksheet from the list on the left side of the console. Click the small arrow next to the 'Insert Userform' button at the top of the screen, which is the second button from the left side, and choose 'Module' from the list of options that appear. Double-click 'Module 1' when it appears in the list.
3. Click the white space on the right side of the screen and enter the following code:Sub Countup()Dim CountDown As DateCountDown = Now TimeValue('00:00:01')Application.OnTime CountDown, 'Realcount'End SubThis code will allow the rest of the code to process once each second.
4. Press enter to access a new line on the page. Enter to following code:Sub Realcount()Dim count As RangeSet count = [E1]count.Value = count.Value - TimeSerial(0, 0, 1)If count
5. Click the 'X' in the upper right corner of the console to close it.
6. Select the cell that you are using for your countdown and enter your desired countdown time. Be sure that you enter the hours, minutes and seconds, even if the hours and minutes are zero. '2:30' is two hours and thirty minutes; '0:2:30' is two minutes and thirty seconds.
7. Click the 'Developer' tab at the top of the screen, and then click the 'Macros' button on the left side of the ribbon. Select 'Countup' from the list and click 'Run.' Your countdown will start and it will continue until it gets to zero, when you will see a message box explaining that the countdown is complete.
Read more ►

How to Create a Standard Deviation Graph in Excel


1. Open Excel. Open a data file or type the data into a series of columns with one value per column.
2. Click 'Insert' and select 'Scatter.'
3. Select the type of scatter graph you wish to insert.
4. A blank area should be inserted and you should see new tabs as options on the menu bar. Click the 'Design' tab and choose 'Select Data.' Hold 'Shift' while selecting the data and click 'OK' in the pop-up window.
5. Click on the 'Layout' tab and select 'Error Bars.' Select 'Error Bars with Standard Deviation' from the drop-down window.
Read more ►

How to Calculate Mean in Excel 2007


1. Enter your the data in column A. For example, if you have four numbers to enter, you would enter them in cells A1 through A4.
2. Determine the range for your data. For example, if you entered four data points, your range would be A1:A4.
3. Enter the formula '=AVERAGE(Range)' into cell B1 to have Excel automatically calculate the average of your data. In this example, since your range equals A1:A4, you would enter '=AVERAGE (A1:A4)' into cell B1 and the average will appear.
Read more ►

How to Embed a PDF File in Microsoft Excel 2003


1. Open the Excel document you're editing if it's not open already.
2. Select the location where you want to embed the PDF. When you insert the PDF, the top-left corner of the document will be flush against the top-left corner of whatever cell you have selected.
3. Click the 'Insert' menu at the top of the screen and select 'Object' to open the 'Object' window.
4. Select 'Adobe Acrobat Document' from the 'Object type' section and then click 'OK.' Excel will display an 'Open' window.
5. Navigate to and select the PDF that you want to embed into Excel.
6. Click 'Open.' Excel will embed the PDF into your document and open the PDF in Adobe Reader.
Read more ►

Sunday, April 28, 2013

How to View Different Worksheets in the Same Workbook in Excel 2003


1. Open the workbook in Excel 2003.
2. Click 'Window,' then 'New Window.' Excel will now feature two windows for the document. You can select a different worksheet in the new window if you like. Repeat this until you have as many windows open as necessary.
3. Click 'Window,' then 'Arrange.' Choose whether to arrange the windows horizontally, vertically or one of the other ways. This will equally split the windows; you can drag their corners to resize them.
Read more ►

How to Add a Workbook to Microsoft Excel


Excel 2003
1. Log on to your computer and open Microsoft Excel. Open a new or existing spreadsheet.
2. Place your cursor to the right of where you want the new workbook to appear. Click on the 'Insert' menu.
3. Choose 'Workbook' from the menu and click 'OK.' You will now see a new tab at the bottom of the spreadsheet.
4. Click on the 'Format' menu and choose 'Sheet' from the menu. Choose 'Rename' and give the new workbook a descriptive name.
Excel 2007
5. Log on to your computer and open Microsoft Excel. Open a new or an existing spreadsheet.
6. Locate the bar at the bottom of the spreadsheet that lists the existing tabs. Click on the 'Insert' button to the right of the last tab. A new worksheet will be added to the right of the last existing one.
7. Select an existing tab to place a new workbook in front of that tab. Then click on the 'Home' tab in the 'Cells' group and choose 'Insert' from the list.
Read more ►

How to Open Excel Without Macros


Starting Excel from the Desktop or Start Menu
1. Press and hold the 'Shift' key on your computer's keyboard.
2. Click on the Excel icon on your computer's Desktop or click 'Start,' 'All Programs,' 'Excel.' Excel's splash screen will appear and the program will open.
3. Release the 'Shift' key once the default blank workbook appears on your computer's screen.
Starting Excel from the Office Shortcut Bar
4. Click on the Excel icon on the Office Shortcut bar on your computer's Desktop.
5. Press and hold the 'Shift' key on your computer's keyboard immediately. Excel's splash screen will appear and the program will open.
6. Release the 'Shift' key when a blank workbook appears on your computer's screen.
Read more ►

How to Print Continuing Row and Column Headings in Excel 2003


1. Open the Excel file that you want to work on. With Excel open, go to 'File > Open' and find your file.
2. Click on 'File > Page Setup.'
3. Click on the Sheet tab.
4. Click on the chart icon next to the box for 'Rows to Repeat at Top.' This will take you back to your Excel sheet. You can now click on the row that you would like to appear on subsequent pages. For example, if there is a row with headings you'd like to repeat, choose that one. After you click each row, your choice will appear in a box on the main screen. Hit 'Enter' to return to the dialog box.
5. Click on the chart icon next to the box for 'Columns to Repeat at Left.' Click on the column that you want to see repeated. In this case, choose the column that contains your headings. Hit 'Enter.'
6. Click 'OK' to save your settings.
Read more ►

How to Make Bold Letters in Excel


1. Open the Excel file in which you want to make bold letters. Enter text in a cell or multiple cells.
2. Click the cell or group of cells in which you want to add bold font. If you want an entire row or column to contain bold font, select the entire row or column by clicking the letter or number at the beginning of the cell or row.
3. Click the 'Home' tab.
4. Click the 'Bold' button. The Bold button is represented by a 'B' and is located in the Font group.
Read more ►

Saturday, April 27, 2013

Step


1. Click the Windows 'Start' button, and select 'All Programs.' Click 'Microsoft Office,' then click 'Excel 2003.' The software opens on the computer to the main window. Excel automatically starts a new spreadsheet for you, if you want to create a new worksheet.
2. Click the 'File' menu item, then click 'Open.' The 'Open' dialog window lets you open an existing spreadsheet. Double-click an XLS file to load it in the Excel software, if you want to edit an existing spreadsheet.
3. Create the data in your spreadsheet. The type of data you enter into the spreadsheet is determined by what you intend to create. You can type text and numbers into the Excel cells. To create formulas, you must enter only numbers in a cell. For instance, if you want to make a spreadsheet of revenue, type 'Revenue' in the 'A1' cell. Excel marks each row with a number and columns with a letter, so 'A1' is the first cell on the spreadsheet.
4. Type your revenue data under the A1 cell. For instance, type '30' in A2 and '40' in A3. The following is what the spreadsheet should look like:Revenue3040
5. Create a formula to add the two revenue values. You can add two or several values in a formula. You prefix a formula with the equals sign. For instance, to add the two values typed in step four, type the following in A4:=sum(a2:a3)The formula above tells Excel to add cells A2 through A3. After you press 'Enter,' notice the value in the cell is the total sum of the two cells.
6. Highlight any cells you want to format. Click the 'Format' menu item, then click 'Cells.' A configuration window opens. The configuration window lets you set up the font, colors, background color, borders and alignment. Make you changes in the window and click 'OK' to see the changes.
7. Click the 'Save' toolbar button at the top of the window after you complete all of the spreadsheet changes. The file extension used for Excel 2003 is XLS. To open the file in the future, click 'File,' then view the files listed in the 'Recent' list. You can open the file using this method, if you forget the file name.
Read more ►

How to Use MS Excel to Make an Age


1. Create six column headers in row one, starting in column A. From left to right, the column headers should be “Male,” “Female,” “Total,” “Age Range,” “% Male” and “% Female.”
2. Input the age ranges that you have the information for under the “Age Range” column. If you are using a hyphen to denote the age range, like “11-14,” right-click the cells and choose “Change Format.” Then change the format to “Text.” Otherwise, Excel may automatically change your ranges to dates.
3. Place the population numbers for males and females that correspond to the age range in the row, under the “Male” and “Female” columns.
4. Input this formula: “=SUM(A2:B2)” into the first cell under the “Total” column. Move your mouse over the bottom right corner of the cell; it should turn into a plus sign. Click and drag the mouse down the column to the last row. Release the mouse button to copy the formula to every cell in the “Total” column. Select cell G1 and type in this formula: “=SUM(C:C)”. This will give you the total population for the entire graph.
5. Highlight all of the cells in columns E and F. Click on the “Home” table at the top of the screen and find the “Number” section. There will be a drop-down box that should read “General.” Change “General to “Percentage.”
6. Input the following formula into cell E2: “=0-(A2/$G$1)”. This will give you a percentage of the overall population that are males in that age group. It will be represented by a negative number for the sake of the graph you are making. Input this formula into cell F2: “=B2/$G$1”. This formula will give you the percentage for females, only as a positive number. Highlight both E2 and F2, then click on the bottom right corner and drag the formula down to fill every row in the table.
7. Highlight rows D, E and F. Click on the “Insert” tab and find the “Charts” area. Click on “Bar” and, under the “2D Bar” section of the drop-down menu, choose “Clustered Bar.” It should be the first choice on the left. A crude graph will now display on the screen.
8. Move your mouse over the age ranges listed in the middle of the graph. The words “Vertical (Category) Axis” will appear under the mouse when you are in the right place. Right-click and choose “Format Axis.” Click on the second radial button in the new window, labeled “Specify Interval Unit” but leave this set to “1.” Click on the drop-down menu next to “Axis Labels” further down the window and choose “Low.” This will move the age ranges to the left side of the graph.
9. Right-click on any of the colored graph lines and choose “Format Data Series.” Move the “Series Overlap” slider all the way to the right so it reads “100 %.” Move the “Gap Width” slider all the way to the left so it reads “0 %.” Now click 'Close' and your age-sex graph will be complete. You can modify the style of the graph, as far as headers and labels go, just like any other Excel graph.
Read more ►

How to Format Cells to Make Negative Numbers Appear Like 0000.00 in Excel


1. Open the Excel 2010 spreadsheet where you want to adjust the number format.
2. Click on the top-left cell in the area where you want to adjust the number format. Hold the mouse button down and drag your mouse pointer to the bottom-right cell, then release the button to select the entire area. Alternatively, you can select the column letter, or row number, to select an entire column or row.
3. Right-click your mouse while your pointer is located anywhere within your selection. Choose “Format Cells” from the small menu that appears and the Format Cells window will appear.
4. Click on “Custom” from the list on the left side of the window. Then place your cursor into the “Type” field on the right side of the window and use “Backspace” to erase whatever format is currently in there.
5. Enter the following code into the “Type” box:#,##0;'0000.00'The characters before the semicolon will control how positive numbers appear, while the characters after the semicolon will replace any negative number with “0000.00.” Click “OK” to close the window and complete your format change.
Read more ►

Thursday, April 25, 2013

How to Graph a Line Graph in Excel 2007


1. Open the Microsoft Excel 2007 application on your computer and click on the 'Microsoft Office' button. Click on the 'Open' option.
2. Locate the Excel 2007 file on your computer that contains the data you want to turn into a line graph. Select the file and then click on the 'Open' button.
3. Click on the 'Insert' tab and then click on the 'Line' option from the 'Charts' group. Click on the 'Line with Markers' option.
4. Use your mouse to highlight the data within the spreadsheet that also contain labels for the rows and columns. The Chart Tools feature now becomes available.
5. Click on the 'Design' tab and the select a line graph style from the 'Chart Styles' group. Click on the 'Layout' tab and then click on the 'Chart Title' option. Click on the 'Above Chart' option.
6. Click in the chart title text box and enter a title for your chart. Right-click on the chart title and enter a size for the chart title that meets your preference in the 'Size' box.
7. Click on the 'Layout' tab and then select a legend style for your line graph from the 'Chart Elements' box. Click on the 'Legend' option from the 'Layout' tab and select the position for the legend, such as the 'Show Legend at Top' option.
8. Click on the 'Axis Titles' option from the 'Layout' tab and enter titles for the X axis and Y axis of your line graph. You make any other changes to the line graph, such as thickness or color of lines, by clicking on the 'Design' tab.
Read more ►

How to Lose the Zeroes in Excel


All Zero Values
1. Move your mouse pointer to the 'File' tab and left-click. Click 'Options.'
2. Find the 'Advanced' category at the top and click it. Locate 'Display options for this worksheet' and select your worksheet.
3. Remove the check beside 'Show a zero in cells that have zero value.' The cells with zero values now appear blank and contain no numbers.
Selected Cells
4. Move your mouse pointer to the cells with zero values that you want to lose. Hold down the left button and drag your mouse. This highlights the cell(s).
5. Locate the 'Cells' group in the 'Home' tab. Click 'Format.' followed by 'Format Cells.'
6. Find the 'Category' list. Click on 'Custom.'
7. Type '0;-0;;@', without the quotation marks, in the 'Type' box. This hides the zero values.
Read more ►

How to Do Addition in Excel


Using the Plus Sign
1. Open the Microsoft Excel 2010 spreadsheet that you want to work with.
2. Click on an empty cell on the spreadsheet, wherever you want the results of your addition to be listed.
3. Type '=' -- the 'equals' symbol -- which indicates the start of a formula, then type in the number or cell reference of the first thing you want to add. If entering a cell reference, place the column letter of the cell in front of the row number, so that the first cell on the spreadsheet is 'A1' and the one under that is 'A2.'
4. Enter a ' ' sign into your formula, then enter the second number or cell reference that you want to add. Continue alternating ' ' signs and numbers or cell references until you have entered all the information you want to add together. Press 'Enter' to complete your formula. The result of the addition appears in the cell.
Using the SUM Function
5. Open the Microsoft Excel 2010 spreadsheet that you want to work with. Click on any empty cell.
6. Type '=' -- the 'equals' symbol -- which signifies that you are starting a formula, then type the word 'sum' and place an open parenthesis at the end of the word. This tells Excel that you will be performing the 'SUM' function on the values in the parentheses.
7. Enter the first number that you want to add, or the first cell reference, followed by a comma, then enter the second value or reference, followed by another comma. Continue alternating values and references with commas until you have entered all the items you want to add together. Enter a close parenthesis and press 'Enter.' The result of your addition appears in the cell.
Read more ►

How to Make a Bar Graph in Excel 2010


1. Open a new Excel 2010 spreadsheet. Identify two or more variables you want to include in your bar graph. For example, to graph the number of lung cancer cases occurring over the past 100 years, use the variables 'Decade' and 'Lung Cancer Cases.'
2. Place the independent variable on the x-axis of a bar graph. Type the variable name, such as 'Decade,' into cell A1. Type the second variable name in cell B1. This variable goes on the y-axis of your bar graph. Add additional variables to the spreadsheet if necessary.
3. Type numerical data under each variable. Include as many cells as necessary to complete your entire data set.
4. Click the 'Insert' tab and select 'Column' in the 'Charts' section. Select the '2-D Column' chart. A blank box will appear in your Excel 2010 spreadsheet.
5. Click 'Select Data' in the 'Design' tab of the 'Chart Tools' area. Click the 'Add' button to select the data for the y-axis of the bar graph. Type the variable name in the 'Series Name' box. Select the range of data points in the 'Series Values' box. Click 'OK.' Add additional variables to the y-axis if applicable.
6. Click the 'Edit' button in the 'Horizontal (Category) Axis Labels' section. Click and drag your mouse to highlight the x-axis values from the spreadsheet. They will appear on the bottom of the bar graph.
7. Click the 'Layout' tab of the 'Chart Tools' area. Use the options in this tab to change the axis names, grid lines, legend or chart title. Click the 'Format' tab in the 'Chart Tools' section to modify the color, shape and appearance of the bar graph.
Read more ►

How to Create Line Graphs With Two Groups in Microsoft Excel


1. Open the Microsoft Excel file that contains the data sets you wish to graph.
2. Click the 'Chart' button on the Excel toolbar. A pop-up chart wizard will appear.
3. Select the 'XY Scatter' chart type and any chart sub-type you prefer. The options provide four types of line graphs to suit your style. Press the 'Next' button.
4. Delete anything that is displayed in the 'Data Range' field. Select the 'Columns' option in the 'Series in' field. Then click the 'Series' tab.
5. Create a new series for each line you wish to graph based on a group of data. If you have two groups of data, you will create two series. Click the 'Add' button twice to create these two series.
6. Click on the first series in the 'Series' list box.
7. Type the name of this series in the 'Name' field. Specify the ranges for the X values and Y values of this first line group separately in the two other fields.
8. Click the second line group series in the 'Series' list box and fill in the same fields. Press the 'Next' button to customize the visual details of the chart, if desired, or alternately press the 'Finish' button to display the graph.
9. Right-click on each line or XY scatter plot and select the 'Format Data Series' option if you wish to alter how the lines display. This is useful if the lines are close together so each is uniquely identified.
Read more ►

How to Use Absolute Cell Referencing


Creating an Absolute Reference
1. Determine if a formula should be absolute or relative. When designing a formula, look for hard-coded numbers. A sample formula for the discount could be 'discount = cost of merchandise * 20%'. The 20% is a hard-coded number and has the potential to change. Instead, create a cell titled Discount Amount. The new formula would be 'discount = cost of merchandise * discount amount'. That way, if the discount ever increased, you would need to change only the amount in the Discount Amount cell, and all the formulas would automatically update.
2. Create the formula. Once you know a formula will have an absolute reference, you need to create the formula in Excel. Create a cell that holds the data that has the potential to change; in this example it will be A4. This is the 20% discount in our example. Identify the first cell that will contain the cost of the merchandise; for example, cell D4 is the first cell on the purchase order that contains merchandise cost. To figure out the discount amount, in cell D5, type =D4*A4 and press the Enter key.
3. Make the cell absolute. To make the cell A4 absolute in this formula (meaning Excel will use cell A4 absolutely every time), simply highlight A4 in the formula and press your F4 key. This will place a $ before the cell row and column name. You could also enter a $ in front of the cell row and column name in the formula. The new formula be =D4*$A$4.
4. Copy and paste the formula to the other cells. When you paste the formula into other cells, the relative reference, D4, will automatically update to the corresponding row (or column) number, and the absolute reference will stay the same. For example, the next row would contain the formula =D5*$A$4.
Read more ►

Wednesday, April 24, 2013

How to Add the Word 'Draft' As a Watermark to Excel Documents


1. Open your Excel document and click 'Insert,' then 'Word Art.'
2. Select the style you want your watermark to look like.
3. Type 'Draft.'
4. Right-click your word art and select 'Format Text Effects.'
5. Check that 'Solid Fill' is selected and drag the transparency bar to a setting that allows you to see the data on the spreadsheet and the word 'Draft' on the spreadsheet.
Read more ►

How to Add Two Cells in Excel


1. Start Microsoft Excel, and open the file you want to use.
2. Click on the cell in which you want the total to appear.
3. Press the key on the keyboard. This character will appear in the cell and in the Formula Bar.
4. Click on the first cell you want to add. That cell's name, such as B6, appears in the Formula Bar after the sign.
5. Press the key again.
6. Click on the second cell you want to add. That cell's name appears in the Formula Bar.
7. Press Enter to complete the equation. The total will appear in the initial cell.
Read more ►

How to Create a Bar Code in Microsoft Office Publisher


1. Start up Microsoft Excel and prepare your data. This will include all the pertinent information you wish to associate with the bar code. You will also need to create a column for your bar code data. This is simply a code that will translate into a bar code and act as a reference point for that specific row of data.
2. Highlight all of the data in the bar code column and in the 'Custom Toolbar' of Excel; click on 'Settings,' and choose the appropriate bar code type. You will also need to click on the drop-down box below 'Convert To' and select 'Image File.' At this point, click 'Generate Now.'
3. Save your data and close Excel; then open Publisher.
4. Select the appropriate label size in Publisher once it starts. This selection will be based on the labels the bar codes will be printed on.
5. Click 'Tools,' then 'Mailing and Catalogs' and then 'Mail Merge,' at which point you will select 'Picture Field' to insert the bar code. You can also select any of the data from your Excel spreadsheet to accompany the bar code, such as other product or lot information.
Read more ►

How to Make Excel 2007 Behave Like 2003


1. Visit AddInTools.com and download 'Classic Menu for Office 2007.' This product costs $29.99, as of September 2010, and you will be asked to pay with a credit or debit card. You will not be able to proceed until you have successfully purchased and downloaded this product to your computer.
2. Open Excel 2007 on your computer. To do this, click the computer's Start menu. Click 'Microsoft Office 2007,' then click 'Microsoft Excel 2007'. Once you open Microsoft Excel 2007, you will notice a new task on your toolbar: 'Menu,' located right before 'Home.'
3. Click the new 'Menu' task on the toolbar at the top of Excel 2007.You should now see a toolbar that looks just like the toolbar of commands you are familiar with using on Excel 2003.
Read more ►

How to Find Lost Excel Files


Windows XP
1. Click 'Start' in the lower-left corner of your computer screen.
2. Place your mouse pointer over 'Search,' then select 'For files or folders.'
3. Click on the 'Documents' option, then select 'Use advanced search options,' then click 'More advanced search options.'
4. Click the appropriate box for when the file was last modified. If you don't know, leave this section blank.
5. Type '.xls' (without quotes) in the box that asks for all or part of the document name. If you are using Excel 2007 or later versions, type '.xlsx' instead of '.xls.' This will limit your search to Excel files.
6. Add keywords in the keyword field if you know any from the spreadsheet and change the 'Look in' field to 'My computer.'
7. Click to place check marks in the boxes labeled 'Search system folders,' 'Search hidden files and folders,' 'Search subfolders' and 'Search tape backup.'
8. Click 'Search' and allow Windows to find your document for you. If you have not entered keywords, the system will find all Excel files that meet the other criteria, so you may have a long list to scroll through to find your file.
Windows Vista
9. Click the 'Start' orb in the lower-left corner of your screen. A search box is included at the bottom of the Start menu.
10. Type '.xls' (without quotes) in the search box. If you are using Excel 2007 or later versions, type '.xlsx' instead of '.xls.' This will limit your search to Excel files. Add any keywords you remember from the document to this box as well, separated from the Excel file extension with a space.
11. Press the 'Enter' key.
12. Look through the search results for your file. If you do not see it listed, click the box at the top of the search results window labeled 'Include non-indexed, hidden and system files.'
13. Click on 'Document' near the top of the window to narrow your results to documents only (this includes spreadsheets) and click 'Search.' Scroll through the results to find your lost Excel file.
Windows 7
14. Click 'Start,' then type 'folder options' into the search box near the bottom of the Start menu.
15. Click 'Folder options' when it appears, then click 'Change search options for files and folders.'
16. Click the boxes to search hidden files and folders and system folders, then click 'OK.'
17. Click 'Start' again and type '.xls' or '.xlsx' plus any keywords from your Excel file into the search box. Click 'Show more results' on the results page and scroll down to find your Excel file.
Read more ►

Tuesday, April 23, 2013

How to Use Excel 2007 to Estimate Linear Functions


1. Open a new or existing Microsoft Excel spreadsheet. If you start with a blank spreadsheet, enter your x-values into column A and y-values into column B. Each pair of numbers creates a data point on a chart, so they need to correspond to each other.
2. Label your columns with descriptions of your data, such as 'Month' and 'Hours.' In this example, if you worked 160 hours in the month of June, those two values create one data point on your chart. Highlight your data set and select the 'Insert' tab on the Ribbon at the top of the page.
3. Click the 'Scatter' drop-down box in the 'Charts' area, then select the 'Scatter With Only Markers' option. This option creates a plot from your data points and allows you to estimate the linear function. Select the 'Layout' tab of the Ribbon and click the 'Trendline' button from the 'Analysis' area.
4. Click 'More Trendline Options' from the drop-down options. Select 'Linear' from the 'Trend/Regression Type' to estimate a linear function. Select the 'Display Equation on Chart' box to view the linear function equation on the chart.
Read more ►

Monday, April 22, 2013

How to Work Out Percentages in Excel


1. Open the Microsoft Excel program and type the numerator (given amount) into cell A1.
2. Highlight cell B1.
3. In the 'fx' box in the toolbar at the top of the screen, type '=A1/X' (without the quotes), replacing X with the denominator (the total amount) . For instance, if you are calculating a percentage score out of 60 points, you would type '=A1/60'.
4. Hit enter. This should calculate a decimal (eg, .75) in cell B1.
5. Hit the '%' button on the formatting toolbar near the top of the screen to convert to a percentage. Alternatively, you can simply multiply by 100.
Read more ►

How to Remove the Password Required to Open Excel


1. Open the Microsoft Excel application by clicking 'Start' and typing 'excel' into the search bar. Click on 'Excel' in the list that appears to open it. You can also click 'All Programs' and open Excel from there. You don't need a password to open Excel this way -- you only it when you double-click on a protected file.
2. Click 'File,' then 'Open.' Click on the name of the file that is password-protected. Type the password and hit 'Enter' to open the document.
3. Click 'File,' then 'Info' and 'Permissions.' Click 'Encrypt with Password.' The password entry box opens.
4. Delete the password in the box, leaving the box empty. Click 'OK' to remove the password from the document. Click 'Save' before exiting the document.
Read more ►

Sunday, April 21, 2013

How to Detect Repair Excel 2003


1. Open Excel 2003 by clicking on the 'Start' button, then 'All Programs,' 'Microsoft Office' and 'Microsoft Office Excel 2003.'
2. Click 'Help' on the main menu, then select Detect and Repair. The Detect and Repair dialog box is displayed on your screen. You may be prompted to select an installation source. If so, browse to the location of your Microsoft Office installation files or insert the installation disc.
3. Choose if you want to restore your shortcuts or discard any customized settings by selecting the appropriate check boxes.
4. Click 'Start' to begin the Detect and Repair process. A Windows Installer window will appear, indicating the process has begun.
5. Allow the process to run. The program will prompt you when Detect and Repair has completed. Click 'OK' to return to Excel 2003.
Read more ►

Saturday, April 20, 2013

How to Make a Cell in Excel 2007 Act Like a Checkbox


1. Open the Excel worksheet you want to modify by adding in a check box. Navigate to the 'Developer' tab in the ribbon at the top of the window.
2. Click the 'Insert' icon in the 'Control' heading to bring up a drop-down menu. Select the 'Check Box' icon in the 'Form Controls' grouping.
3. Locate the cell that needs to act as a check box. Click the cell to create the new check box. Click the outline of the box and drag it to any other cell if you need to move it.
4. Highlight the text that reads 'Check Box 1' and press the 'Backspace' key. Type in the text you want to appear next to the check box. Click any other cell to save the check box and the new text.
5. Right-click the check box if you need to make any changes. Choose 'Edit Text' to change the text displayed next to the box or choose 'Cut' to delete the check box entirely.
Read more ►

Friday, April 19, 2013

How to Create an XY Chart


1.
Open Excel and in a new workbook, create a data spreadsheet for the XY chart to reference. Enter the months, as shown in the graphic depiction in cells 'A1' through 'M4.' (NOTE: You can create a chart similar to the one shown instead.)
2.
Select 'Insert' from the menu bar. Next, choose 'Chart.' (NOTE: You can choose the chart icon on the menu bar if it is available.)
3.
Go to the 'Standard Types' tab and select the 'XY(Scatter)' chart from the Chart Type list box.
4.
Press the 'Press and Hold to View Sample' button to preview the chart before making your final selection. When ready, click 'Next.'
5.
Verify that the data are correct and then click 'Next.'
6.
Go to the 'Titles' tab and enter a name for the chart. Enter names for 'X and Y Axis Values' if desired.
7.
Select option 'As Object In' and choose 'Sheet1.' Press 'Finish.' (NOTE: Other options include placing the chart in a different worksheet or creating a new worksheet to house the chart.)
8.
Click on the chart to activate the 'Chart' menu to modify the chart. Select 'Chart' and choose 'Chart Options.'
Read more ►

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 ►

Blogger news