Excel

Browse » Home » All posts

Monday, March 12, 2012

How to Use a Subroutine in Excel Visual Basic

1. Open the Visual Basic Editor. In Excel 2003, from the menu, select 'View,' 'Toolbars,' 'Visual Basic.' From the new toolbar, click the Visual Basic Editor icon. In Excel 2007, on the Developer tab, in the Code group, click 'Visual Basic.'
2. Insert a new module. From the menu, click 'Insert,' 'Module.' You will see the new module listed in the Project Explorer with the name 'Module1.'
3. Insert a new procedure. From the menu, click 'Insert,' 'Procedure.'
4. In the 'Add Procedure' pop-up box, type a name for your new subroutine. Use underscores, rather than spaces, to separate words.
5. Under 'Type,' select 'Sub.'
6. Under 'Scope,' choose 'Public' or 'Private.' A public subroutine is accessible to other procedures; a private subroutine only works within the same procedure.
7. If desired, check the 'All Local variables as Static' checkbox. Static variables retain their values when you exit a procedure. The default behavior clears all variables.
8. Click 'OK.' In the code window, you will see the Sub and End Sub statements.
9. Add code. Between 'Sub' and 'End Sub,' insert the VBA instructions for your subroutine. For instance, use the following code to display a message box with a greeting:Sub Test()
MsgBox 'Hello World'
End Sub
10. Run the subroutine. From the menu, select 'Run.' In Excel 2003, the subroutine will execute. In Excel 2007, select the subroutine in the pop-up box, and click 'Run' to start execution.

How to Use the Excel Monthly Household Budget Template

1.
Open Excel. If you are using Excel 2007, click the 'Office Button' and then click 'New.' In Excel 2003, click 'File' and then 'New.' Type 'Family Monthly Budget' in the 'Search Online For' box and click 'Go.'
2.
Click 'Family Monthly Budget Planner' to select that template. Click the 'Download' button. The template will open as a new Excel worksheet.
3.
Start in the 'Projected Cost' column. Type in what you expect the costs to be for each item listed that month. You can round off your costs to whole numbers, or put in exact numbers and the template will round them off for you. If you are uncertain how much you might spend on an item, look at past bills or receipts and make an estimate.
4.
Enter the information into the 'Total Actual Cost' column as the month goes along. Every time you spend money or pay a bill, enter the amount into the correct cell. If you already have a number in that cell, add the 2 numbers together and enter the new number into the cell. The template will automatically calculate the difference between your projected and actual spending.
5.
Enter your projected monthly income at the beginning of the month. As you earn money throughout the month, enter this data into the cells in 'Actual Monthly Income.' Your household budget template will help you see what you actually have coming in and where it is going.

How to Darken the Selection in Microsoft Excel

1. Select the column or columns of data with your mouse. If you want to select all of the columns, position your mouse on top of the first column letter (usually column A), click, and drag it across to the last column. This will select everything in your spreadsheet.
2. Click the 'Home' tab and select the 'Cell Styles' option. You will see a listing of various cell themes.
3. Choose one of the cell themes that are listed at 20 percent opacity from the list of cell style options. From now on, when you select the cells in your worksheet, the selection box will be darker and easier to distinguish. Leave this formatting on your worksheet until you are finished working with the file and ready to save the document.
4. Use the scroll bar at the bottom of your worksheet to scroll over until you find a column that has the default Excel background and text formatting. Select the column (as you did in step one) and double-click the 'Format Painter' button on the 'Home' tab. Then click and drag your mouse across the column letters in your spreadsheet--this will return your worksheet to the default formatting.

How to Merge Excel Spreadsheets to Find Duplicates

Complete the Merge
1. Identify the duplicate values you are searching for. Identify the data type and make sure it is the same in both spreadsheets. For example, if you would like to identify duplicate dates, make sure the dates are recorded in the same format (9/12/2034) across spreadsheets.
2. Confirm the spreadsheets have the same number of columns. To merge spreadsheets, make sure they have the same number of columns. If not, make sure the column headers match until it no longer matters. For example, if spreadsheet A has four columns and spreadsheet B has six columns, make sure the first four columns of both spreadsheets are First Name, Last Name, Phone and Email. Make sure there are no empty columns or rows.
3. Copy and paste or consolidate. Copy the information from spreadsheet A and paste the information into spreadsheet B below the last record on spreadsheet B. Each column in spreadsheet B should line up with the appropriate column in spreadsheet A.
Identify Duplicate Records
4. Insert a new column. Insert the new column next to the column with the duplicate value. For example, if you are looking for duplicate phone numbers in column C, insert a new column to the left of column C. Column C should be come before column D. Type the title 'Duplicate?' into the column's first row to name the column.
5. Enter the formula for identifying one column duplicates. Select the second row of your 'Duplicate?' column and enter the duplicate formula: =IF(D2=D3, 'Duplicate',''). This formula will identify duplicate records in column D only. For duplicates in multiple columns, enter =IF(D2E2=D3E3, 'Duplicate',''). This formula will identify duplicate records in columns D and E.
6. Copy and paste the formula. Copy the formula down the length of the appropriate column. Right-click the cell with the formula and select 'Copy' from the short-cut menu. Select each cell down the length of the appropriate column and right-click and select 'Paste Special...', then click 'Values' and 'OK.'
7. Sort the 'Duplicates?' column. Sort your spreadsheet based on the duplicate column. Sort the duplicate column in descending order to move each duplicate to the top of the list.

How to Open Excel 2007 Files With Office 2003

1. Visit Microsoft's online Download Center. Search for 'Microsoft Office Compatibility Pack for Word.'
2. Click on the 'Microsoft Office Compatibility Pack for Word, Excel and PowerPoint File Formats' link.
3. Click on the 'Download' link. Click on 'Save' to save the file to your computer.
4. Click on the downloaded FileFormatConverters.exe file. It should be located in the downloads file on your computer.
5. Click on 'Run' to start the installation process. Once the installation is complete, you will be able to open and edit files made in Excel 2007 in your Office 2003 suite of applications.

How to Enter a Series of Dates in an Excel 2007 Spreadsheet

1. Open the worksheet.
2. Click on a cell that will contain the first date. Enter this date. For example, 5/12/11.
3. Point on this selected cell's lower right corner. A black ' ' symbol will appear over the tiny square in this corner. This symbol is the fill handle.
4. Drag the fill handle across the adjacent cells to fill them with the sequence of dates.

Sunday, March 11, 2012

How to Use Excel to Subtract From the Total

1. Open the document you would like to make the changes to in Microsoft Excel.
2. Locate the cell that contains the total and make note of the cell number --- for example, 'E2' or 'F15.'
3. Select the empty cell in which you would like to have the subtracted result shown.
4. Type '=X - Y' where X is the number of the cell containing the total and Y is the number or cell you would to subtract from the total.
5. Press 'Enter' and the cell will now show the subtracted total.

How to Make the Cursor Stop Highlighting

1. Launch the Microsoft Excel project in which you wish to stop the cursor highlighting.
2. Select a single cell with your cursor. The cursor highlights a range of cells only if extended-selection mode is turned on.
3. Turn extended-selection mode off by pressing the 'F8' key.
4. Select a single cell with your cursor. The cursor will highlight only the individual cell. Extended-selection mode has been turned off.
5. Restart your system if the problem persists.

How to Combine Worksheets Inside an Excel Spreadsheet

1. In Excel 2007, click on the 'Data' tab and choose 'Consolidate' from the Data Tools group. For earlier versions of Excel (97-2003), click on 'Data->Consolidate.'
2. Define the reference ranges using the Reference box. Specify the first range you want to consolidate.
3. Click 'Add.' The reference you defined in Step 2 will appear in All References.
4. Repeat Steps 2 and 3 until you have defined everything you want to consolidate. Then click 'OK.'

Tuesday, February 28, 2012

How to Make a Map Using Excel

1. Create the information you want to appear on the map, such as statistics for a region. Enter the name of the place (such as city, state, or country) in column A and the number or statistic in column B.
2. Once you have your data for your map, click File and Save. Highlight and select all your data rows and columns by holding the left button down and dragging it until all your data is selected.
3. Go to the top of the screen and use your mouse to click Insert, followed by Map. Move your cursor (which will no longer flash now) and move it the part of the Excel page where you want your map to be.
4. Holding down the left mouse button, drag your cursor across the Excel page to show how large you want the map. You will need to start from the top left corner of your desired map area and drag it down to the bottom right.
5. Wait for Microsoft Excel to display a pop-up menu, listing the maps available. Read through the list, select the one you want, and click OK.
6. Excel will then find boundary names to match the data you entered (such as the region, city or state). If it cannot automatically find the information you are looking for, a pop-up menu labeled Resolve Unknown Geographic Data will appear with other name options; choose one and click Change or select Discard if you cannot find a match.
7. Microsoft Excel will then generate a map based on statistics and data you entered. Look it over to make sure all the statistics and names are correct; if not, go back to your saved data, fix it and re-save.

How to Remove Scroll Bars in Pages

1. Open the document or spreadsheet from which you would like to remove the scroll bars.
2.
Select 'Tools' from the toolbar menu. Next, choose 'Options,' then click on the 'View' tab.
3. Uncheck the boxes next to 'Horizontal Scroll Bar' and 'Vertical Scroll Bar.' Then click 'OK.' (These options are in the 'Show' area under the 'View' tab in Microsoft Word; they are located in the 'Window Options' area under the 'View' tab in Microsoft Excel.)
4. To show the scroll bars again, repeat steps 1 and 2. Next, place checks in the boxes next to 'Horizontal Scroll Bar' and 'Vertical Scroll Bar.' Click 'OK' when finished.

How to Calculate Percentages in Excel

1. Type the amount in a cell. For example, a student received a score of 48 out of 60 on a test and wants to find his percentage. The student would type 48 in cell A1.
2. Type the total in an adjacent cell. In the example, the student would type 60 in cell B1.
3. Type '=amount cell/total cell' in an adjacent cell to the total cell. Replace amount cell and total cell with the cell addresses of the amount and total. In our example, the student would type, '=A1/B1' in cell C1. The total will display 0.8 in cell C1.
4. Highlight the cell with the decimal, and click '%' button under the 'Home' tab to convert the decimal to a percentage.

How to Create Macros in Excel 2003

1. Open the Microsoft Excel 2003 application on your computer. Click on the 'Tools' option from the top toolbar menu and then click on the 'Options' button.
2. Click on the 'Security' tab and then click on the 'Marco Security' option. Click on the 'Security Level' tab and choose either the 'Medium' or 'Low' option.
3. Click on the 'Tools' option and then scroll over the 'Marco' option. Click on the 'Record New Macro' button.
4. Enter a name for your macro in the 'Macro name' box. The first character in the name must be a letter and not a number.
5. Choose where you want to store the macro on your computer from the 'Store macro in' box. Enter a description for your macro in the 'Description' box. Click on the 'OK' button.
6. Perform the task that you want for your macro within the Excel spreadsheet, and then click on the 'Stop Recording' button when you're finished. For example, you can select a cell, press the 'Enter' key and then select the 'yellow' highlight color from the toolbar. Now, every time you press the 'Enter' key in a cell it will be highlighted in yellow.

How to Merge Sheets in Excel 2007

1. Click the Windows 'Start' button. Type 'excel' in the search text box. Press 'Enter' to open the Excel software.
2. Press 'Ctrl' and 'O.' In the dialog window that opens, double-click the XLSX file that contains multiple sheets you want to combine.
3. Click the 'DigDB' menu item at the top, then click the 'Table' sub-menu item, then click 'Append Tables in this File.' A configuration window opens.
4. Click each sheet you want to combine, and click the 'Add' button. If you want to combine all sheets together, select 'All Sheets,' and then click 'Add.'
5. Click 'OK' to combine the spreadsheets. Notice the original, first spreadsheet has all the combined data. Press 'Ctrl' and 'S' to save the changes.

How to Convert Date to Day of Week in Excel

Convert Replace Date with Day of Week
1. Open the Excel workbook in which you want to convert and replace the date data with day-of-week data. If this workbook is not available, open up a new file for practice. If using a practice workbook, enter sample date data in mm/dd/yyyy format.
2. Left click with the mouse to select the column or row that contains the range of date data.
3. Select 'Format' from the menu.
4. Select 'Cells' from the drop-down list.
5. Select the 'Number' tab on the 'Format Cells' pop-up window.
6. Select 'Custom' in the 'Category' list.
7. Enter 'dddd' in the 'Type' field to display the date as day-of-week, with the day entirely spelled out (Monday, for instance).Enter 'ddd' in the 'Type' field to display the date as day-of-week, with the day abbreviated to standard three-letter abbreviation (Mon, for instance).
Convert Date to Day of Week, Without Replacing Initial Data
8. Open the Excel workbook in which you want to convert and replace the date data with day-of-the-week data. If this workbook is not available, open up a new file for practice. If using a practice workbook, enter sample date data in mm/dd/yyyy format. This article will presume that the date data is located in column 'A,' with the header 'Date' in cell 'A1.'
9. Determine where you want the day-of-week data located. Label that column header with DOW (cell 'B1,' for example).
10. In 'B2,' enter the formula '=TEXT(A2,'dddd')' to display the date as day-of-week, with the day entirely spelled out (Monday, for instance).
Or, you can enter the formula '=TEXT(A2,'ddd')' to display the date as day-of-week, with the day abbreviated to standard three-letter abbreviation (Mon, for instance).
Press 'Enter' to move the cell cursor out of cell 'B2'.
This is with the understanding that cell 'A1' contains the label 'Date' and cell 'B1' contains the label 'Day of Week' and that the first date is located in cell 'A2' and the first cell to be populated with the day of the week is cell 'B2'. If your workbook is set up differently, adjust the location of the formula to the desired location of the day-of-week data and adjust the formula accordingly to refer to the correct cell.
11. Return the cell cursor to cell 'B2'. Select 'Edit' from the menu.
Select 'Copy' from the drop-down list.
12. With the cell cursor, select the entire range where you want the day-of-week data. Select 'Edit' from the menu, and select 'Paste' from the drop-down list; this will paste the formula that you entered in cell 'B2' through the entire range of cells.

Subscribe to: Posts (Atom)