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...
Browse » Home » All posts
Saturday, April 28, 2012
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 da...
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 ce...
Friday, April 27, 2012
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/2010Date-Text: 10-Jan-2010Copy this formula down the column as necessary to...
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...
Thursday, April 26, 2012
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...
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...
How to Calculate the Time Difference Between Two Times in Excel 2003
1. Open Excel 2003, and select a workbook that contains columns with time information. Click 'File' on the menu bar, and select 'Open.' Browse your files, and select the workbook. Click the 'Open' button. The workbook opens.2. Highlight the first column that contains time data. Right-click the highlighted data, and select 'Format Cells.' The Format Cells dialog box appears. Click the 'Number' tab. Select 'Time' from the 'Category' list. Select the first time format displayed. Click 'OK.'3. Highlight the second column that will appear in the timed calculation. Press 'F4.' This repeats...
Wednesday, April 25, 2012
How to Use MegaStat in Excel 2007
1. Create a new spreadsheet in Excel 2007.2. Open MegaStat.3. Click the 'Add-Ins' menu option at the top-right of the screen, and you will see a new available option for 'MegaStat.' Click it to gain access to all MegaStat's functio...
Tuesday, April 24, 2012
How Do I Create a Color Drop Down Box in Excel?
Create Lookup List With Background Color1. Go to a section of unused cells in the spreadsheet.2. Type the choices that will appear in the drop-down list. Type one choice per cell and compose a list of choices with each selection under the previous one.3. Highlight all cells in the list and then right-click on the highlighted block of cells.4. Click 'Format Cells' from the context menu.5. Click the 'Fill' tab and then click on the desired color for the background of the lookup list (the selected cells).6. Click 'OK.' The background color of the cells is set.Add the Lookup Cells As a...
How to Create Conditional Formats in Excel 2007
1. Open an existing Microsoft Excel document in which to create these conditional formats. Double click on the 'My computer' option on the main operating system desktop. Choose the location of the Excel file and double click on the file icon to open the document.2. Ascertain which cells or worksheet the conditional formatting will apply to. Prior to opening the conditional formatting manager, choose particular cells, the entire worksheet or workbook to apply the conditional format.3. Choose the 'Conditional Formatting' button from the home menu ribbon. Then select the 'Manage Rules' option...
How to Make Mailing Labels in Excel 2007
1. Open the Excel 2007 worksheet containing the list you want to use to make mailing labels. If you do not yet have a list, open a blank worksheet. Enter a name for each column in the first row of the worksheet. Choose names that will be easy to understand in the merge, such as 'First Name,' 'Last Name' and 'Street Address.' Save and close the worksheet.2. Start Word 2007. Go to the 'Mailings' tab and click 'Start Mail Merge.' Select 'Labels' from the drop-down list. The 'Label Options' dialog box opens.3. Select the label settings for the label pages you are using. Pick the label brand...
How to Put Excel Data Into a Pie Chart
1. Open a new worksheet in Microsoft Excel. Add headings in the first column down the left side of the page. For instance, if you are making a pie chart of my favorite things, you would write, 'Football,' in cell A1, 'Chocolate,' in cell A2, and 'Television,' in cell A3. Then, put the numbers in the 'B' column. So, using the above example, this would mean you'd put 50% in cell B1 next to 'Football,' a 30% in cell B2, next to 'Chocolate,' and a 20% in cell B3 next to 'Television.'2. Highlight all of the cells in both columns, from A1 to B3. In Microsoft Excel 2007, simply click the 'Pie'...
Monday, April 23, 2012
How to Merge Excel Worksheets Into a Workbook
1. Open Microsoft Excel. Press and hold the 'Ctrl' key on your computer's keyboard and press the 'O' key to start the 'Open' window. Click on the first workbook you would like to open. Press and hold the 'Ctrl' key and click on the second workbook. Press the 'Open' button at the bottom right of the 'Open' window to open both workbooks.2. Click the workbook on the bottom Windows toolbar that contains the sheet that you would like to merge. Right-click the sheet tab that you would like to move or copy. For example, right-click 'Sheet 1' tab at the bottom left of the Excel workspace to select...
How to Delete Cells in MS Office 2003
1. Open an existing worksheet in Microsoft Excel.2. Select the cell (or cells) you want to delete from your worksheet. To select an individual cell, simply click the cell. For a range of cells, click the first cell and drag your cursor down to the last cell. If it's a large range, click the first cell in the range, and press 'Shift' while you click the last cell. Press 'CTRL' while clicking individual cells that aren't adjacent.3. Open the 'Edit' menu.4. Click 'Delete.' As a shortcut, right-click the selection and click 'Delete.'5. Make a selection in the 'Delete' dialog box. Select...
Subscribe to:
Posts (Atom)