How to Delete Macros in Excel 2003

1. Open the Microsoft Excel 2003 application on your computer and the click on the workbook that contains the macro that you want to delete.
2. Click on the 'Tools' option from the top toolbar menu and then scroll over the 'Macro' option. Click on the 'Macros' option.
3. Locate the 'Macros in' list and then click on the 'This Workbook' option. A small dialog box will then appear and show the macro you have in the workbook.
4. Click on the name of the macro you want to delete in the 'Macro name' box. Hold down the 'CTRL' key on your keyboard to select more than one macro.
5. Click on the 'Delete' and your macro will be deleted from your workbook. Close out of the dialog box to return to your workbook.

Read more ►

How to Create Progress Bar in VBA

1. Open Microsoft Office Excel, then press 'Alt' and 'F11' to open the Visual Basic Editor. Right-click 'VBAProject,' then click 'UserForm' to insert a new Form. From the 'Toolbox' menu, click 'Label.' While holding your mouse button, drag on the Form to create a new label control.
2. Right-click the label and select 'Properties,' then change 'Height' to 42 and 'Width' to 12. Change the 'Top' to 12. Create nine more labels using the same process.
3. Click 'CommandButton' on the 'Toolbox' pane and add a new button to your Form. Right-click 'CommandButton' and click 'View Code' to create a new procedure.
4. Type the following to create a new variable and define its value:Dim cnt As Integercnt = 0
5. Type the following to hide all the labels when the Form starts:Me.Label1.Visible = FalseMe.Label2.Visible = FalseMe.Label3.Visible = FalseMe.Label4.Visible = FalseMe.Label5.Visible = FalseMe.Label6.Visible = FalseMe.Label7.Visible = FalseMe.Label8.Visible = FalseMe.Label9.Visible = FalseMe.Label10.Visible = False
6. Type the following to create a while loop and iterate 10 times while pausing the code for 5 seconds:Do While cnt
10Select Case cntCase Is = 1Me.Label1.Visible = TrueMe.Label1.BackColor = vbBlueCase Is = 2Me.Label2.Visible = TrueMe.Label2.BackColor = vbBlueCase Is = 3Me.Label3.Visible = TrueMe.Label3.BackColor = vbBlueCase Is = 4Me.Label4.Visible = TrueMe.Label4.BackColor = vbBlueCase Is = 5Me.Label5.Visible = TrueMe.Label5.BackColor = vbBlueCase Is = 6Me.Label6.Visible = TrueMe.Label6.BackColor = vbBlueCase Is = 7Me.Label7.Visible = TrueMe.Label7.BackColor = vbBlueCase Is = 8Me.Label8.Visible = TrueMe.Label8.BackColor = vbBlueCase Is = 9Me.Label9.Visible = TrueMe.Label9.BackColor = vbBlueCase Is = 10Me.Label10.Visible = TrueMe.Label10.BackColor = vbBlueEnd SelectMe.RepaintApplication.Wait Now TimeValue('00:00:05')cnt = cnt 1Loop
7. Click the 'Insert' menu then click 'Module' to add a new module. Copy and paste the code below to start your 'UserForm1.':Sub showProgressBar()UserForm1.ShowEnd SubPress 'F5' to run your program.

Read more ►

How to Make a Linear Log Graph in Excel

1. Open Excel 2010 and open your spreadsheet containing the logarithmic data set.
2. Locate an empty space in your spreadsheet to work on your chart. Click somewhere in this area. Click on the 'Insert' tab in the top menu. Click on 'Scatter' in the 'Charts' section and select 'Scatter with Straight Lines and Markers.' Putting your mouse over each icon will give you the title.
3. Right click in the blank chart area that appears in your spreadsheet and click 'Select Data.'
4. Click on the icon at the far right of the 'Chart data range' text box use your mouse to click-drag select the rows and columns containing your data for this chart. Click on the icon again at the far right of 'Chart data range' so that the 'OK' button is displayed. Click 'OK.'
5. Double click on whatever axis (x or y) is your logarithmic data range. For instance if it is the x axis, Double click on the numbers on the x-axis scale. Click on the 'Logarithmic scale' check box which is in the 'Axis Options' section. Click the 'Close' button. You will now see your selected axis changed in scale, for instance increments in base 10 are 1, 10, 100, 1000, etc.
6. Click the 'File' tab in the top menu and select 'Save As' to save this file to keep your work.

Read more ►

How to Find the Print Area on Excel in Vista

1. Log in to your user account in Windows Vista and launch Microsoft Excel.
2. Press 'Ctrl-O' to browse your computer and open an Excel file.
3. Click the 'Office' button that is located in the top left corner of the Excel window.
4. Select the menu 'Print' and 'Print Preview' to display the printable area on the first page. Use the 'Next page' button to show the rest of the document pages.
5. Click 'Close Print Preview' to quit the preview mode.
6. Select the Excel menu 'Page Layout' to change the print area.
7. Select the desired worksheet cells your left mouse button. Click the menu 'Print Area' and then 'Set Print Area.'

Read more ►

How to Link an Outlook Calendar to Excel

Exporting Outlook Calendar to Excel
1. Open Microsoft Outlook and wait for its main screen to fully load. Click on 'File' from the menu at the top of your page and select 'Import and Export.' Click on 'Export to a file' on the following page before selecting the 'Next' tab at the window's bottom.
2. Select 'Microsoft Excel' from the programs list and click on 'Next.' Click on the 'Calendar' folder from your options list on the following page before clicking on 'Next' at the bottom of your screen.
3. Type your chosen name for the calendar file into the text box. This will be the filename for your calendar in Excel. Click on 'Finish' at the bottom of the screen to start linking your calendar with Excel.
Importing Excel Files to Outlook Calendar
4. Customize Excel by examining your calendar section in Outlook. Customize the fields in Excel to match with all the information on Microsoft Outlook. For instance, when importing appointments, Outlook provides information on the name of the appointment, location, subject and time. Title the columns of your Excel spreadsheet to match the Outlook information.
5. Save the data after you complete your spreadsheet. Choose the rows and columns that contain the exact information you wish to import and include column headings. Type the name referring to your selection at the left side of your address bar, and press 'Enter.' Go to the process of saving the data. Exit Excel after saving.
6. Open Microsoft Outlook by double-clicking on its desktop icon. Import the information by going to the 'File' menu and selecting 'Import and Export.' Click on 'Import from Another Program or File' and select 'Next' before clicking on 'Microsoft Excel 97-2003.' Click 'Next' to start locating your file.
7. Click the 'Browse' button and locate the Excel worksheet that you saved in step 2. Select the spreadsheet and click 'Next.' This will show the available folders that you can import into Outlook.
8. Highlight your Calendar option and choose 'Next.' You will find a summary of what you selected reading 'Import [filename] into folder: calendar.' The [filename] represents the name of the spreadsheet that you made, such as 'meetings' or 'addresses.'

Read more ►

How to Make an Age

1. Type or paste in a spreadsheet the list of ages for males, with one row per age. For example, you might type '10,' '15,' '50' and '24' for the ages of four different people. In practice, however, your list should include at least 30 ages.
2. Write on paper a list of between five and 20 age ranges. For example, write '0 to 5' for the first age range that includes ages zero to five years old.
3. Type in the first cell to the right of the topmost age from Step 1, the maximum age of the first age range on your paper. For the example in the previous step, this age is '5.'
4. Use the previous step's instructions to enter the maximum age of the remaining ranges, below the previous step's cell.
5. Click and drag on the cell immediately to the right of those of the previous step. This action selects those cells.
6. Click the 'fx' symbol to the left of the large text box above the spreadsheet grid, then double-click the 'Frequency' item from the dialog box that appears.
7. Click the small square button for selecting cell ranges, located to the right of the upper text box on the following screen. Drag on the list of raw ages from Step 1.
8. Click the square button to the right of the lower text box, then use the previous step's instructions to select the Step 3 and 4 cells holding the age ranges.
9. Click 'OK' to insert the 'Frequency' function, then click in the formula bar above the spreadsheet grid. Press 'Control,' 'Shift' and 'Enter' simultaneously to fill the cells next to the age range cells with the 'Frequency' function. This action tells you how many males are in each age range.
10. Use Steps 1 through 9 to compute the frequency distribution for females. Enter the raw female data anywhere on the same spreadsheet as the raw male data. However, make sure that the column containing the frequency distribution for females is directly to the right of the male frequency distribution.
11. Drag across both male and female frequency distributions to select them, then click the 'Insert' menu heading. Click the 'Column' button in the 'Chart' panel, then click the top left chart type from the bar chart gallery that appears. Excel now creates the histogram based on your frequency distributions for males and females.

Read more ►

How to Change Minutes to Decimals in Excel

1. Double-click the Microsoft Excel file containing the time value you want to convert.
2. Find the cell location containing the time value that you want to convert to decimal format. For example, if your target cell were in column A, row 4, then the cell location would be 'A4.'
3. Click inside the cell in which you want to display the converted time.
4. Type '=(XX-INT(XX))*24' into the cell, replacing 'XX' with the location of the cell that you want to convert.
5. Press 'Enter' on your keyboard to execute the formula. Note that Excel will take 'AM' and 'PM' values into account, so '10:30 AM' and '10:30 PM' would have decimal values of '10.5' and '22.5,' respectively.

Read more ►

How to Avoid Unauthorized Deletion of Microsoft Excel Files

1. Open your spreadsheet and click on the 'Tools' menu if you are using Microsoft Excel 2003. Choose 'Protection' and then choose 'Protect Sheet' from the menu. This will protect the entire sheet and prevent any changes from being made.
2. Click on the Review tab and then 'Changes' if you are using Office 2007. Click on 'Protect Sheet.' This will protect your Office 2007 spreadsheet from any unauthorized changes.
3. Click on 'Tools' and choose 'Protection' from your Excel 2003 spreadsheet. Choose 'Allow Users to Edit Ranges' from the menu if you want your users to be able to edit specific ranges. Specify the ranges that users will be allowed to change. This is a useful feature if you need to solicit additional information from your users but do not want to risk them accidentally changing any other numbers in your worksheet.
4. Click on the 'Review' tab and choose 'Changes' from the menu if you are working with an Excel 2007' worksheet. Choose 'Allow Users to Edit Ranges' and choose the ranges that users will have permission to change.

Read more ►

How to Create a Password for an Excel File

1. Open Excel and search for a file that you want to protect. Open the file by clicking the 'Open' icon on the Quick Access Toolbar. Browse your computer and locate the file. Click 'Open.' Select a cell to lock. This cell will not be accessible by people who use this spreadsheet. Right click on the cell and select 'Format Cells.' Select the 'Protection' tab and make sure 'locked' is selected. Click 'OK.'
2. Select the 'Review' tab on the ribbon. Select 'Protect Sheet.' Verify that the 'Protect worksheet and contents of locked cells' is selected. Remove the check from the 'Select locked cells' option. This will prevent any users from accessing the locked cells. Add a password. You will be prompted to confirm this password. Write it down in a secure location. Click 'OK.'
3. Add an additional level of security by password-protecting the file from any access without the password. Save the file by clicking the 'Office' button and selecting 'Save As.' Locate the 'Tools' button in the lower left hand corner of the Save As dialog box. Click the 'Tools' button and select 'General Options.'
4. Add your password to allow you to open the file. You will be prompted to confirm this password. The file is password-protected from entry now. Users cannot open this workbook without knowing the password.

Read more ►

How to Round a Number Using VBA

VBA Round Function
1. Open the Excel 2010 worksheet that contains the VBA code you want to work with. Press 'Alt' and 'F11' to launch the VBA console.
2. Click the module on the left side of the VBA console that holds the Excel macro you want to edit. Place your cursor on the code on the right side of the window, where you want to round a number.
3. Enter the following code into the VBA console:x = round(y)Where 'x' is a variable or function name in your code and 'y' is a numeric variable or an actual number. You can also place a comma after 'y' and input the number of decimal places you want to round to, so 'round(223.446, 2)' will return a value of '223.45.'
Excel Worksheet Function
4. Open the Excel 2010 worksheet that you want to work with. Press 'Alt' and 'F11' to open the VBA console.
5. Click the module that you want to work with from the left side of the VBA console. Place your cursor into the code on the right side, wherever you want to round your number.
6. Enter the following code into your VBA console:x = Application.WorksheetFunction.RoundUp(y, 0)In this code, 'x' is equal to a variable or function name and 'y' is a numeric variable or actual number. Unlike the VBA 'Round' function, the number after the 'y,' which signals the number of decimal places to round to, is not optional. Finally, you can replace 'RoundUp' with 'RoundDown' if you need to round the number down.

Read more ►

How to Create Online Forms in Excel

1. Download a utility to convert Microsoft Excel spreadsheets into online HTML forms. Excel does not offer native support for interactive forms. Spreadsheets may easily be saved as HTML files, but the resulting web pages are static and do not allow data entry. A good utility program is not free, but free trials are available. Spreadsheet Converter is an excellent option for making online forms out of Excel. The program may be tried for 30 days with no charge.
2. Install the spreadsheet utility from the downloaded file. Spreadsheet Converter downloads as an '.exe' file. Double-click the file and follow the onscreen instructions until installation is complete.
3. Open Excel. Note the new menu at the top of the program window called 'SpreadsheetConverter.' All of the features of this utility are available through this menu.
4. Build the online form in Excel as you would like it to appear on the web page. For example, type a label of an input field, and leave an empty adjacent cell where the user will enter the data. Cell A1 could be labeled as 'First Name' and cell A2 as 'Last Name.' Cells B1 and B2 would be kept empty for the user response.
5. Code the user data entry cells in Spreadsheet Converter. This is necessary so the utility knows how the resulting online form should function. The program must be told which cells are for data entry. Click on a data entry cell and select the SpreadsheetConverter menu. Choose the appropriate option. The 'Mark Input Cell' command will identify the selected cell as a form field for data input.
6. Create a dropdown list menu for the online form, if desired. Many online forms offer pre-set choices for users to select. Pick a cell for this menu and choose the 'Dropdown List' item from the SpreadsheetConverter menu. A pop-up window will appear. Type in the name of each menu item and press 'OK.'
7. Convert the Excel spreadsheet to an online form. Choose the 'Convert' command from the SpreadsheetConverter menu. Press the 'Next' button on each screen until conversion is complete.
8. View the online form. SpreadsheetConverter automatically opens the HTML version of the Excel spreadsheet in Internet Explorer when the conversion is complete.

Read more ►

How to Create Summary Workbooks in Excel 2003

1. Launch Excel, and open all of the workbooks you want to summarize.
2. Click the 'New' icon to start your summary workbook. Format the cells as you would like them to appear before importing your data.
3. Go to the first workbook you want to include in the summary, and click the cells you want. Click 'Ctrl C' to copy them.
4. Return to your summary workbook, and click the empty cell where you want to include the data. Click the 'Home' button and select 'Paste' and then 'Paste Link.' Repeat Steps 3 and 4 with the data in all of your workbooks until your summary is complete.

Read more ►

How to Track History on Microsoft Excel

1. Click on the 'Review' tab in Excel 2007 or the 'Tools' tab in Excel 2003.
2. Click on 'Track Changes.'
3. Click on the 'Track changes while editing. This also shares your workbook' check box.
4. Click on any feature's check box that you want to track. For example, if you want to track who made changes, click on the 'Who' check box.
5. Click on the drop-down box to the right of the feature you checked in Step 4 to select what kind of changes you want to track. For example, click on 'All' changes.
6. Make sure the default option 'Highlight changes on screen' box is checked, then click on 'OK.' Any changes made to the document will be highlighted on your Excel spreadsheet.

Read more ►

How to Remove All White Spaces to the Right of a Word in Excel

1. Launch Microsoft Excel 2010. Open your spreadsheet by choosing 'Open...' from the 'File' menu or pressing and holding 'Control' ('Command' for Mac) and then pressing 'O.' Navigate to your Excel spreadsheet file and either double-click its name or click on it once and press the 'Open' button.
2. Click on the header of the first column of cells that contains text you need to edit. Go to the 'Home' tab and choose the 'Cells' group. Click on the arrow alongside 'Insert.' Click on 'Insert Sheet Columns' to create a new column to the left of the column you selected.
3. Click in the first cell of the column you just inserted. Type an equal sign and then the letter 'T.' Scroll down the list of functions that comes up till you reach 'Trim' and click on it. Click on the first cell in the column you need to edit. Your cell contents should say '=TRIM([cellnumber]),' where [cellnumber] is the column/row reference to the first cell you need to edit. Press 'Enter' to confirm your formula.
4. Click in the cell in which you just added the 'TRIM' function and place your pointing device cursor at the bottom right corner of the cell. Your cursor will change from an outlined plus sign to a black plus sign. Pull down on the lower right corner of the cell until you drag down to the bottom of the column of data from which you need to remove extra spaces. Let go of the pointing device when you have dragged all the way down. The data column you inserted should be filled with the same information that's in the original column to its right, but without the extra spaces.

Read more ►

How to Remove All Carriage Returns

Removing carriage returns manually - documents
1. Open your computer's word processing or text editor.
2. Open the document you want to remove carriage returns from by navigating to the directory containing the document and double clicking on the file's name.
3. Place your cursor at the end of the line of characters.
4. Press the 'Delete' key on your computer's keyboard until the line below the one you are on has moved up next to your cursor.
5. Repeat Steps 3 and 4 for each carriage return code you want to remove.
Removing carriage returns using automation - documents
6. Open your computer's word processing or text editor.
7. Open the document you want to remove carriage returns from by navigating to the directory containing the document and double clicking on the file's name.
8. Place your cursor at the end of the line of characters. Highlight the area with your mouse to the beginning of the next line of characters.
9. Press and hold the 'CTRL' key on your computer's keyboard. Press the 'C' key to copy the contents highlighted, if using Windows. If using a Mac, press and hold the 'Apple' key and press 'C.'
10. Click 'Edit,' 'Find Replace' to open the Find Replace utility.
11. Click in the box under the heading 'Find What' or 'Search For.' Press and hold the 'CTRL' key. Press the 'C' key to paste the contents highlighted if using Windows. If using a Mac, press and hold the 'Apple' key and press 'V.'If using Microsoft Word or OpenOffice, place '^p' in the 'Find What' or 'Search For.' box.
12. Leave the 'Replace With' box blank. Click on 'Replace All.' All carriage return codes are now removed from your document.
Removing carriage returns using automation -- spreadsheets
13. Open the spreadsheet you want to remove carriage returns from by navigating to the directory containing the spreadsheet and double clicking on the file's name.
14. Enter '=SUBSTITUTE(XX,CHAR(13),'')' in any blank cell of your spreadsheet. Replace the 'XX' with the 'Column' letter and 'Row' number of the cell containing the carriage return you want to remove. Note, the '13' is the ASCII code for a carriage return on both Windows and Mac systems.
15. Press the 'Enter' or 'Return' key on your computer's keyboard to execute the formula.
16. Change the 'Column' and 'Row' values for each cell you want to remove carriage returns from. Press the 'Enter' or 'Return' key to execute the formula.

Read more ►