How to Use Goal Seek in Visual Basic

1. Open the Excel document in which you'd like to run your Goal Seek macro.
2. Find the two cells you would like to use for Goal Seek. The first should be the cell for which you have a fixed goal. The second should be a cell you allow Excel to change in order to fulfill that goal. The first cell's value must be a formula that is based on the second cell--otherwise, no matter what changes Excel makes to the second cell, no changes will appear in the first.
3. Open the Macro window. In Excel 2007 or later, do this by clicking on the 'View' tab of the ribbon, then clicking 'Macros.' In earlier versions of Excel, click the 'Tools' menu, choose 'Macro,' then choose 'Macros.'
4. Type a name for your Goal Seek macro in Macro window, and click 'Create.' This will launch the Visual Basic for Applications editor and show you the text of the new Macro, which will start out as an empty subroutine like this:Sub MyMacroName()
End Sub
5. Create a Range object representing the cell for which you have a known goal (the first cell from Step 2). For example, if you'd like to Goal Seek cell A1, write:Dim goalCell As Range
goalCell = ThisWorkbook.Worksheet(1).Range('A1')
6. Create a Range object representing the cell you'd like Excel to change to cause the first cell to reach the known goal. For example, if you'd like Excel to manipulate cell A2, write:Dim variableCell As Range
variableCell = ThisWorkbook.Worksheet(1).Range('A2')
7. Call the GoalSeek method on the goal cell's Range. As the first argument, provide the value you'd like it to Goal Seek to; as the second argument, provide the variable cell's Range. For example, if you'd like to Goal Seek to 0, write:goalCell.GoalSeek(0, variableCell)

Read more ►

How to Make a Chart With Standard Errors in Microsoft Graph

1. Type your data into Excel using a column format. For example, if you are creating a chart for children's test scores, type the names of the children in the first column, then type the test scores in the second column.
2. Highlight the data in the document. Click at the top left, then drag the cursor to the bottom right.
3. Click 'Insert,' then click 'Chart.' Follow the instructions on the Chart Wizard to create a graph of your choice.
4. Double click a series on the chart, then press 'Ctrl' and '1' to open the Format Series dialog box.
5. Click either the 'X Error Bars,' or the 'Y Error Bars' tab, depending on what type of error bar you want to add.
6. Click an icon that represents the specific error bar you want to add. The choices are 'Both,' 'Plus,' 'Minus,' or 'None.'
7. Click a radio button for the error amount, and type an amount into the error text box. For example, click the radio button for 'Fixed value,' then type '2' into the text box.
8. Click 'Ok.'

Read more ►

How to Delete a Custom Toolbar in Excel 2003

1. Open Excel 2003 and locate your customize toolbar. Click 'Tools' on the menu bar and select 'Customize.' The Customize dialog box appears.
2. Click the 'Toolbars' tab in the Customize dialog box. Search the menu for your custom toolbar's name. Use the scroll bar to drag up and down to find your custom toolbar's name. Remove the check from custom toolbar's name.
3. Click the toolbar name. The toolbar name is highlighted in blue. Click the 'Delete' button. The custom toolbar is deleted.

Read more ►

How to Import an MS Outlook Calendar Into MS Excel

1. Launch Microsoft Outlook.
2. Click the 'File' tab at the top of the window.
3. Click 'Open,' and then click 'Import.'
4. Click 'Export to a file,' and then click 'Next.'
5. Click 'Microsoft Excel 97-2003,' and then click 'Next.' This is the .XLS file type, which is compatible with all versions of Microsoft Excel from 97 to 2010.
6. Click 'Calendar,' and then click 'Next.'
7. Click 'Browse,' and then select a location on your computer for your Excel calendar file. Click 'Next' to continue.
8. Click 'Finish.'
9. Browse to the file location you selected earlier, and then double-click the file to open it in Excel.

Read more ►

How to Remove Excess Tab Symbols From an Excel Spreadsheet

1. Make a copy of the file you want to remove tab symbols from. Work on the copy of the file.
2. Go to the 'Home' tab.
3. Click on the 'Find Replace' icon (it looks like a pair of binoculars). This will pull up the Find/Replace dialog box. Alternatively, hit 'Ctrl'-'H' to pull up the Find/Replace dialog box.
4. For the item to be found, enter '^t'. Leave the 'Replace' field blank.
5. Click on the 'Options' button and select 'Workbook' from the first drop-down menu.
6. Click on the 'Find Next' button; make sure it's highlighted what you want replaced, and click 'Replace.'
7. Repeat the previous step until all the tab symbols you want replaced are removed. Alternatively, if you want to eliminate all tab symbols, select 'Replace All.'

Read more ►

How to Remove Add

Delete Toolbars
1. In Excel 2007, point to the toolbar you want to remove with your mouse.
2. Right-click with your mouse.
3. Select 'Delete.'
Hide Toolbars
4. Click on the 'View' tab in Excel 2007.
5. Find the name of the toolbar you want to hide in the 'Show/Hide' section.
6. Uncheck the box next to the toolbar's name.

Read more ►

How to Restore Office File Associations

1. Click on the Start button and choose Run. Type the following in the run command box and press the Enter key: 'winword /regserver'. Doing so will start the Microsoft Word application and automatically re-associate all word processing file types with the Word program.
2. Click on the Start button and choose Run. Type the following in the run command box and press the Enter key: 'excel /regserver'. This will start the Microsoft Excel application and will associate all spreadsheet data file types with the Excel program.
3. Click on the Start button and choose Run. Type the following in the run command box and press the Enter key: 'powerpnt /regserver'. This will start the Microsoft Powerpoint application and will associate all Powerpoint presentation files with the Powerpoint program.
4. Click on the Start button and choose Run. Type the following in the run command box and press the Enter key: 'msaccess /regserver'. The Microsoft Access application will start and it will associate all appropriate database file types with the Access program.

Read more ►

How to Create a Sum Formula in Microsoft Excel 2003

1. Create a new file and name it for easy saving by left-clicking on the “File” tab on the command bar and selecting “Save As.”
2. Create a group of numbers in cells. Select a desired column and then create at least two or more groups of numbers in a row, from top to bottom. For example, if you were to use column “A”, then you would create a group of two or more numbers in cells “A1” and “A2”.
3. Create a sum formula for the cells. To create a sum formula, select a vacant cell and activate it by left-clicking on the desired cell. Then enter your sum formula. To create a SUM formula in Excel you use this format: SUM=(A1 A2). Before each sum formula, type in the word “SUM=” followed by the cells that will be summed in parenthesis.
4. Save your spreadsheet. Make sure to save your new spreadsheet by clicking on the “File” tab and selecting “Save.”

Read more ►

How to Replace All in Excel VBA

1. Open the Excel workbook where you want to enter your VBA code. Press 'Alt' and 'F11' to open up the VBA console.
2. Double-click on the module where you want to enter your code. If there is no module you can right-click on a worksheet, move your mouse over 'Insert' and choose 'Module.' Then double-click on the module to bring it up.
3. Copy the following formula into the module:Sub AReplace()Dim sb As WorksheetFor Each sb In Worksheetssb.Cells.Replace What:='XXX', Replacement:='YYY', LookAt:=xlPart, _SearchOrder:=xlByRows, MatchCase:=FalseNextEnd SubChange 'XXX' to the value you are searching for and 'YYY' to the value you want to replace it with. You can also change the 'MatchCase' value to 'True' if capitalization is important. This macro will find and replace all desired values on every worksheet in your workbook.
4. Use the following code if you only want to replace all the values in a given selection:Selection.Replace What:='XXX', Replacement:='YYY', LookAt:=xlPart, _SearchOrder:=xlByRows, MatchCase:=FalseYou can use this code within any other VBA macro as long as you enter this code after you have defined a selection area in your macro.
5. Click on the 'X' in the top-right corner of the VBA window to close it. All your changes are automatically saved.
6. Click the 'Developer' tab and press the 'Macro' button. Choose your macro from the list and click 'Run' to replace all the targeted values in your worksheet or workbook.

Read more ►

How to Filter Duplicates in Excel

1. Click 'Start' then 'All Programs.' Open Excel 2010 by clicking 'Microsoft Office Excel 2010.'
2. Click 'File' then 'Open.' Select the Excel spreadsheet that contains the duplicate data you wish to filter.
3. Click the 'Data' tab then click the 'Remove Duplicate' button. A dialog box opens which lets you select the columns and rows that you want Excel to scan for duplicate data.
4. Click 'Select All' then click 'OK.'
5. Click 'OK' once more to confirm the success of the operation. The spreadsheet is now filtered of duplicate data.

Read more ►

How to Add (or Subtract) in a Spreadsheet like Excel

1. Open the Excel worksheet.
2. Enter the values in a row or column.
3. Click a cell to the right of the row of values or click a cell below the column of values. The selected cell displays a black outline.
4. Click the 'Home' tab.
5. Click the 'AutoSum' button in the 'Editing' group. This summation button displays the uppercase Sigma. A formula appears with the range of cells.
6. Press the 'Enter' key. The total appears in the selected cell.

Read more ►

How to Convert the Date to Text in Excel

1. Open in Excel the workbook that has the dates you want converted to text. If that file isn't available, populate date data into a single column (A2) in a new Excel workbook.
2. Label your date column (presumed to be column A for purposes of this instruction) as 'Date' in cell A1. Label the column where you want the converted date to populate as 'Date-Text.'
3. Enter the following formula in the second row of the 'Date-Text' column to convert the date to dd-mmm-yyyy format:=TEXT(A2,'dd-mmm-yyyy')Example:
Date: 1/10/2010
Date-Text: 10-Jan-2010Copy this formula down the column as necessary to convert all of the date data to text data.
4. Enter the following formula in the second row of the 'Date-Text' column to convert the date to dd-mm-yyyy format:=TEXT(A2,'dd-mm-yyyy')Example:
Date: 1/10/2010
Date-Text: 10-01-2010Copy this formula down the column as necessary to convert all of the date data to text data.
5. Enter the following formula in the second row of the 'Date-Text' column to convert the date to dd-mmm-yy format:=TEXT(A2,'dd-mmm-yy')Example:
Date: 1/10/2010
Date-Text: 10-Jan-10Copy this formula down the column as necessary to convert all of the date data to text data.
6. Enter the following formula in the second row of the 'Date-Text' column to convert the date to mmm-dd-yyyy format:=TEXT(A2,'mmm-dd-yyyy')Example:
Date: 1/10/2010
Date-Text: Jan-10-2010Copy this formula down the column as necessary to convert all of the date data to text data.
7. Enter the following formula in the second row of the 'Date-Text' column to convert the date to mmm-dd-yy format:=TEXT(A2,'mmm-dd-yy')Example:
Date: 1/10/2010
Date-Text: Jan-10-10Copy this formula down the column as necessary to convert all of the date data to text data.

Read more ►

How to Calculate Empty Cells Using Excel

1. Add the following numbers and blank cells in the first column of your Excel 2007 document. For example, add the numbers 2,3,leave blank,5,6,7,leave blank,9,0,2,3,4,leave blank,6,7, and 8 in A2, A3, A4, A5, A6, A7, A8, A9, A10, A11, A12, A13, A14 and A15. Here 'leave blank' means don't enter any value in A3, A7 and A13.
2. Click on the 'A17' cell for this example. This is the cell where you will calculate how many total blank cells are in this column. You can choose any cell to calculate total blank cells.
3. Click 'Insert Function' on the top left-hand side of the Excel spreadsheet. The 'Insert Function' window will pop open.
4. Click on the drop-down menu of 'Or Select a Category.'
5. Scroll down to the 'Select a Function' window. Choose 'Countblank.'
6. Click 'OK.' The 'Functions Arguments' window will open. Ensure that on 'Number 1' cell, A2:A15 is populated. If A2:A15 is not populated, enter A2:A15 manually. Click 'OK.'
7. The total blank cells for the row has been successfully calculated in the 'A17' cell. In this example, the calculated value of blank count is '3'. In other words, only three cells are blank on this column. You can use this method to accurately identify blank cells regardless of the size of the data.

Read more ►

How to Build a Savings Interest Calculator in Excel

1. Open a new spreadsheet in Excel.
2. Label row 1 as follows: A1 is 'Date,' B1 is 'Balance,' C1 is 'Additional deposits' and D1 is 'Interest.' Format columns B, C and D as currency by selecting the columns and clicking on the dollar sign button in the 'Home' tab. (Users of Excel 2003, click the dollar symbol on the Formatting toolbar.)
3. Call your bank or look at your account online to find out when the bank adds interest payments to your account.
4. Fill in column A with interest payment dates, starting with the most recent date your account received an interest payment. If your bank adds interest payments monthly, use monthly dates (October 1, November 1, etc.); if it pays annually, add dates by year. For example, if your last interest payment was October 1, 2009, the next date will be October 1, 2010.
5. Type the current balance of your savings account into cell B2.
6. Fill in column C with additional deposits. If you deposit $500 into the account in the interval between interest payments, enter '500' in cells C2, C3, and down as far as you'd like. If you deposit irregularly, leave those cells blank but enter figures each time you make a deposit. You can combine these approaches, entering a minimum figure and adding to it if you make more deposits.
7. Enter the following calculation in cell D2, without the quotation marks: '=B2 C2 x*(B2 C2)' where x is the interest rate. The interest rate type--daily, monthly, annually or other--must match the intervals between the dates in column A (step 4). Remember to multiply the interest rate expressed as a percentage by .01. So a 1.5 percent interest rate calculated once a year gives you a multiplier of 0.015. In this case your formula would be '=B2 C2 .015*(B2 C2)'. If the same interest rate was applied monthly instead of annually, your formula would be '=B2 C2 (0.015/12)*(B2 C2)'.
8. Copy cell D2 and paste in cells D3, D4 and on down as far as you'd like.
9. Navigate to cell B3 and type (without the quotation marks) '=D2'. This will cause Excel to display the formula result--your account balance after the interest payment--in cell B3.
10. Copy cell B3 and paste in cells B4, B5 and on down as far as you'd like.
11. Save the spreadsheet and close it.

Read more ►

How to Make a Relative Frequency Histogram on Excel 2007

1. Create a column for your independent variables (your x-values). For example, if you are comparing the performance of salespersons, write 'Jim' in cell A2, 'John' in cell A3, 'Sue' in cell A4, 'Pat' in cell A5 and Joe in cell 'A6.'
2. Create a column with your dependent variables (your y-values). For the example given in Step 1, write 12 in cell B2,11 in cell B3,10 in cell B4, 9 in cell B5 and 4 in cell B6.
3. Sum the dependent variable column by clicking an empty cell at the bottom of the data and entering the summation formula. For the Step 1 example, you have information in cells B2 to B6, so the formula is =SUM(B2:B6).
4. Create a second set of y-values by calculating the percentage of sales for each item. Use a formula to calculate this for you; for the example given in the steps above, enter =B2/B7 in cell C2, =B3/B7 in cell C3, =B4/B7 in cell C4, and =B5/B7 in cell C5.
5. Click on the down arrow below 'Column' in the 'Insert' tab. Choose '2-D Column' from the menu.
6. Click on the column representing the total number of sales. Press the 'Delete' key. This action leaves you with the relative frequency distribution graph only.

Read more ►