How to Hide Macros in an Excel Workbook

1. Open Microsoft Excel and navigate to the 'Menus' tab at the top, left corner of the screen. Click the 'Files' drop-down menu and select 'Open.' Double-click the name of the Excel workbook that has the macro you want to hide.
2. Navigate to the 'Developer' tab, which is located at the top, right corner of the window. Click the 'Macros' button, which is positioned at the left side of the navigation ribbon and to the right of the 'Visual Basic' button.
3. Scroll through the list of macros and locate the name of the macro you want to hide. Click 'Cancel' to close the window and click the 'Visual Basic' button to open the VBA editor window.
4. Click the 'Modules' link at the left side of the window to bring up a list of VBA modules saved on the workbook. Scroll through the code until you find the opening 'Sub' command at the top of the Macro. For instance, if the Macro is titled 'Macro1,' the line of code should read 'Sub Macro1().'
5. Click behind the opening 'Sub' command and type the word 'Private.' Add a space between the 'Private' and 'Sub' commands so your line of code reads 'Private Sub Macro1().'
6. Click 'File' and choose 'Save' to save the changes and hide your macro.

Read more ►

How to Create a P Chart Using Samples

1. Click 'Start' then 'All Programs' and select 'Microsoft Excel' to open the program.
2. Type 'Class' in cell A1 of the Excel worksheet and type the numbers 1 to 10 in chronological order in cells A2 to A11.
3. Type 'Absent' in the cell B1 and then type the following values (according to the introductory example), one below the other as shown below (B2 to B11):4131263230Value '4' should be entered in cell B2, '1' in B3 and so on.
4. Type 'Proportion' in the cell C1, type '=B2/50' in cell C2 and then press 'Enter.' Proportion is calculated using the value in cell B2 (i.e., 4) and dividing it by 50 (number of students). These instructions cause Excel to automatically perform the calculation.
5. Point the mouse cursor to the bottom-right corner of cell C2. The cursor will turn to sign. Then click and drag the mouse cursor vertically down until you reach cell C11. Excel automatically calculates all the values using the formula used in the previous step while replacing B2 with B3, B4 and so on and then fills the corresponding cells.
6. Type 'Total Absent' in cell A13, type '=SUM(B2:B11)' in cell B13, and then press 'Enter.' Type 'Total Possible' in cell A14, type '=10*50' in cell B14, and then press 'Enter.' SUM is an built-in Excel function that, as instructed here, automatically calculates the sum of numbers in cells B2 to B11 and then displays the sum in cell B13. In this case, 10 is multiplied with 50 using the multiplication operator * and then the result is displayed in cell B14. Here, 10 is the number of classes and 50 is the number of students.
7. Click in cell 'A1,' click 'Insert' menu and then 'Chart.' Click 'Standard Types' tab on the Chart Wizard.
8. Click 'Line' below 'Chart type,' select the chart type that is described as 'Line with markers displayed at each data value' on the right pane and then click 'Next.' Click 'Columns' radio button on the Data Range tab and then click 'Next.'
9. Click 'Titles' tab. Type 'Samples' in the Category (X) axis box and 'p' in Value (Y) axis box. Then click 'Next.' Click the 'Finish' button. P chart will be displayed on the Excel spreadsheet.

Read more ►

How to Sort a Name List by Same Names in MS Excel

1. Select the column of names that you would like to sort.
2. Open the 'Data' tab.
3. Under the 'Sort Filter' group, click the 'Sort A to Z' button. This button features the letter 'A' on top of the letter 'Z' on the left side, and an arrow pointing down on the right side. All of the names are now sorted alphanumerically, and cells containing the same names are right next to each other.

Read more ►

How to Show Numbered Columns

Microsoft Excel 2003
1. Click on the 'Tools' menu, and select 'Options.'
2. At the top of the window that appears, click the 'General' tab.
3. Under 'Settings,' click the 'R1C1 reference style' check box.
4. In the lower right-hand corner of the window, click the 'OK' button.
Microsoft Excel 2007
5. At the top left of the screen, click the round button that features the Microsoft Office logo.
6. Toward the bottom right of the information box that appears, click 'Excel Options.'
7. At the top of the window that appears, click the 'Formulas' tab.
8. In the 'Working with formulas' section, click the 'R1C1 reference style' check box.
9. In the lower right-hand corner of the window, click the 'OK' button.

Read more ►

How to Make Tables in Excel 2007

1. Launch Excel 2007 from your desktop. Type the information you would like to use as each column's 'header' in side-by-side cells within the same row.
2. Enter the data that you would like to be displayed in the rows directly under each of the 'headers' that you have typed out. Verify that you have entered the information correctly into the cells and that data is sorted as you would like it to be displayed in the table.
3. Click and drag your mouse over the cells that make up the table you are creating to highlight them. Click the 'Insert' tab towards the top of the Excel 2007 window. Click the icon labeled 'Table' that appears directly underneath the 'Insert' tab.
4. Click the empty box next to the text that reads 'My table has headers' in the window that opens and click 'OK' to continue. Excel 2007 will take the information that you have entered and create a table in the spreadsheet.

Read more ►

How to Use Excel As a Database

How to Set Up an Excel Spreadsheet as a Database
1. Identify the data you want to include in the database. Before you can set up a spreadsheet as a database, you need to know what information you will be tracking.
2. Use Row 1 as headings for each item that you plan to track. For example, if you plan to use Excel as a database for contact information, you would include name, address, phone number and other headings across Row 1.
3. Format Row 1. Change the formatting of Row 1 to separate the headings from the data in your database. To do this, click the number one next to Row 1, which highlights the entire row. Add your formatting, such as bold, center and a gray background.
4. Freeze the heading row. Use the freeze pane feature so that your headings will always appear at the top of the database.
In Excel 2007, click on the number two next to Row 2 to highlight the first row under the headings. Click the View tab. Click Freeze Panes and then Freeze Top Row.
In Excel 2003, click on the number two next to Row 2 to highlight the first row under the headings. From the menu bar, click Window>Freeze Panes.
5. Type in the data. Use one row for each database entry, keying in the information to match the heading information.
6. Sort the data in the database. After you enter the information into the database, you can view the data in a variety of ways. See the next section for instructions on how to sort the data in an Excel database.
How to Sort the Data in Excel 2007
7. Decide how you want to sort the information. For example, you might want the data listed alphabetically by last name, or you might want to sort the data by zip code.
8. Highlight the data in the database. Click on the upper left cell that is left of A and above 1. This highlights the entire spreadsheet.
9. Open the Sort window. Click the Data tab. Then, click the Sort button in the Sort Filter section. This opens the Sort window.
10. Select the data you want to filter. Under Column, select the data you want to sort beside Sort By. If you want to include a secondary sort (such as first name in case you have more than one person with the same last name), then select the heading for a secondary sort next to 'Then by.'
11. Choose sort order. The default (A to Z) is to sort alphabetically or lowest to highest number. To do the opposite, select Z to A under Order.
12. Click OK. The data in the database sorts in the way you instructed.
How to Sort the Data in Excel 2003
13. Decide how you want to sort the information. For example, you might want the data listed alphabetically by last name, or you might want to sort the data by zip code.
14. Highlight the data in the database. Click on the number 2 next to Row 2 to highlight the entire row. While pressing the Shift key, click on the number next to the last row with data to highlight all data without including the heading.
15. Open the Sort window. From the menu bar, click Data>Sort.
16. Select the data you want to filter. Under Sort By, select the data want to sort. If you want to include a secondary sort (such as first name in case you have more than one person with the same last name), then select the heading under 'Then by' for a secondary sort.
17. Choose sort order. The default (Ascending) is to sort alphabetically or lowest to highest number. To do the opposite, click the radio button to select Descending.
18. Click OK. The data in the database sorts in the way you instructed.

Read more ►

How to Count Multiple Criteria in Excel 2003

1. Open your Excel spreadsheet.
2. Locate the formula bar at the top of the spreadsheet. It will have an 'fx' symbol on the left.
3. Type the following syntax in the formula bar (without the quotes):'=countif(range,criteria)'Replace 'range' with the range of cells that you want the Excel to include (e.g. 'A1:A20') and replace 'criteria' with the criteria you wish to use for the formula (e.g. '>20' where ALL values greater than 20 will be taken into account).
4. Press 'Ctrl Shift Enter' to enter the array.

Read more ►

How to Create a Worksheet Form in Excel 2003

1. Launch Microsoft Excel 2003. Start a new worksheet, or open the worksheet that you wish to add forms to.
2. Click 'Tools,' and select 'Customize.' The 'Customize' menu will appear.
3. Click the 'Toolbars' tab, and scroll down to 'Forms.' Place a checkmark next to 'Forms,' and click 'Close.' The 'Forms' toolbar will appear on your Excel spreadsheet.
4. Click on the type of form that you want to add to your Excel 2003 worksheet from the 'Forms' toolbar. You can add labels, group boxes, buttons, checkboxes, option buttons, list boxes, combo boxes, scroll bars and spinners.
5. Drag your cursor over the area in the worksheet where you want the form to appear, to create the form. You can edit and move the form after creation.
6. Right-click the form, and select 'Format Control.' The 'Format Control' dialog box will appear.
7. Edit the form using the options in the 'Format Control' dialog box. You can change the size, color, text and protection settings for the form.
8. Add macros to the forms by right-clicking the form and selecting 'Assign Macro.' The 'Assign Macro' dialog box will appear. Macros control the functions of the form.
9. Select a pre-created macro from the list of macros, and click 'OK' to use the macro or 'Edit' to open the Microsoft Visual Basic Editor so that you can write the code for the macro.

Read more ►

How to Select a Cell Without Deselecting

1. Add one cell at a time to previously selected cells, without deselecting any of these previously selected cells, by pressing and holding 'Ctrl' while you use the left mouse button to add new cells.
2. Add a range of cells to previously selected cells without deselecting any of these previously selected cells by pressing and holding 'Shift' while you drag the left mouse button to add a new range of cells.
3. Add more cells to an existing selection of cells by turning on the 'Add to Selection' feature by pressing 'Shift' 'F8,' and then using your mouse to select any nonadjacent cells.
4. Turn off this 'Add to Selection' feature by pressing 'Shift' 'F8' a second time, and Excel will revert to the default cell selection technique.

Read more ►

How to Use Excel 2003 With Map Coordinates

1. Start Excel and enter the following text into the following cells:'LatA' in cell A1'LonA' in cell C1'LatB' in cell A2'LonB' in cell C2These will be the labels that appear to the left of the cells in which you'll be entering the latitude and longitude pairs of your map coordinates.
2. Now you will name the contents of cells B1, D1, B2 and D2 with names corresponding to the labels you entered in Step 1. These names will be used in two complex formulas rather than direct cell references, to make them easier to understand. Name cell B1 as 'LatA' by clicking B1 and entering 'LatA' into the cell name drop-down, located to the left of the formula bar in Excel. Repeat this process for cell D1, naming the cell 'LonA,' for cell B2, naming it 'LatB' and cell D2, naming it 'LonB.'
3. Enter 'Bearing' into cell A3, and copy and paste the following formula into cell B3:=MOD(ATAN2(SIN(LonB-LonA)*COS(LatB),COS(LatA*SIN(LatB)-SIN(LatA)*COS(LonB-LonA))),6.30318)This formula returns the angle to the east of North between the coordinates in row 1 and the coordinates in row 2, in degrees. The last term, '6.30318,' is two times pi.
4. Enter 'Distance' in cell A4, and copy and paste the following formula into cell B4:=ACOS(COS(RADIANS(90-LatA)) *COS(RADIANS(90-LatB)) SIN(RADIANS(90-LatA)) *SIN(RADIANS(90-LatB)) *COS(RADIANS(LonA-LonB))) *3959This formula returns the distance between the coordinates in row 1 and the coordinates in row 2, in miles. The last term, '3949,' is the radius of the Earth, in miles. To convert the distance to kilometers, change the last term to '6371.'
5. Enter the latitude and longitude pair of your first map coordinate into cells B1 and D1 respectively. Enter the latitude and longitude pair of your second map coordinate into cells B2 and D2. The bearing between the two points will appear in cell B3, in degrees, and the distance between the two coordinate pairs will appear in cell B4.

Read more ►

How to Recover a Password for VBA Projects in Excel

1. Download and install Rixler's VBA Password Recovery Master. The software can unlock any length VBA password automatically. Point the software to the location of your password protected VBA file and the software will work to recover the password. When your password has been recovered, a single mouse click will copy the password to the Office Clipboard.
2. Order the Pro Edition of Dombajsoft's Remove VBA Password Program. The software instantly removes any VBA password, unlocking the file so you can get into your project. The software doesn't recover the password, but it does gain you access to your file. Once you have access you can add a new password of your choice. The software supports VBA projects up to and including Office 2010.
3. Purchase a copy of Softpedia's Remove VBA Password. Remove VBA Password instantly unlocks your VBA project in any Office application, including Excel, Word, PowerPoint, Publisher, Outlook, FrontPage and Project. Passwords of any length or character set can be removed in seconds, allowing you to gain access to your file.

Read more ►

How to Add Headers and Footers to Documents in Excel 2003

1. Open your Excel worksheet after logging on to your computer. You should be able to locate the program by using your 'Start' button. When you cannot find it there, use your 'Search' or 'Find' function.
2. Go to 'View'. From the displayed menu, select 'Header and Footer'. Choose your desired header or footer from the appearing options. Drag your mouse across the icons to view their descriptions. Click on 'Insert Auto Text' for more options.
3. Create a custom header or footer by selecting 'Custom Header' or 'Custom Footer'.
4. Choose either 'Left Section', 'Center Section' or 'Right Section'.
5. Select the buttons to insert the desired header or footer for that section.
6. Enter additional information if desired in the respective box (left, center, right), start a new line in respective section by pressing 'Enter' or delete a section of the header or footer by highlighting information in the section box and pressing backspace.

Read more ►

How to Calculate Median in Excel

1. Enter a list of numbers down a column that you would like to calculate the median for. Make sure the numbers are in ascending order.
2. Click on a cell where you would like the median to appear. This can be the cell after the last number or any other area in the spreadsheet where you want the median to appear.
3. Type =MEDIAN(range) in the cell, with the range specified as the range of cells containing the data. For example, if you have data in cells A1 through A6, the range would be A1:A6. Alternatively, in Microsoft Excel 2007, you can click on the 'Formulas' tab in the toolbar. Click on 'Insert Function,' type 'Median' and press 'OK.'

Read more ►

How to Open an Excel 2007 File in Excel 2003

1. Go to the download page for the Microsoft Office Compatibility pack using the link in the References section of this article.
2. Click the 'Download' button, and save the file to the location of your choice. The name of the download is 'FileFormatConverters.exe.'
3. Launch the program. You may have to click a 'Run' button to confirm that you want to run it. Read the license agreement for the software. If you agree, put a check in the box and click 'Continue.' The installation will proceed automatically. Click 'OK' when it is finished.
4. Restart your computer.
5. Double click the Excel 2007 file with an XLSX extension. You will see a window saying 'File conversion in progress.' After a few seconds, the file will open.

Read more ►

How to Merge Multiple Workbooks in Excel 2003

1. Open Excel on your computer. Make sure that each workbook to be merged has a unique name and is located in the same folder on your hard drive.
2. Open the workbook that you want to merge all changes into.
3. Go to the 'Tools' menu and select 'Compare and Merge Workbooks.'
4. Save the workbook, if prompted by Excel to do so.
5. Select the workbooks to be merged into the currently open workbook, by clicking on the name(s) in the 'Select Files to Merge into Current Workbook' dialog box. To select more than one file name, hold down the 'Control' key while clicking on multiple file names. Click 'OK.'

Read more ►