Tutorial on Microsoft Excel 2003

1. Click 'Start,' 'All Programs,' 'Microsoft Office,' and then Microsoft Office Excel 2003. Excel 2003 will launch.
2. Click on any cell in the worksheet to select it.
3. Type numerical data or text in a selected cell and then press 'Enter' to enter information into the cell.
4. Click on the 'Sheet' tabs at the bottom of the window to work in different worksheets. Each sheet acts as its own separate workspace within the same Excel workbook.
5. Click on a cell, hold down the mouse button and then drag the mouse across the spreadsheet to select a block or range of cells. Selecting ranges of cells allows you to perform actions on all the cells at once. For instance, you can select a group of cells and then press 'Delete' to delete the data they contain.
6. Select a range of cells containing data, click 'Insert' and then 'Chart' to insert a graph. A chart creation wizard will appear. Select the chart type you want from the list, click 'Next' several times and then 'Finish.'
7. Select a cell by pressing the '=' button, type in a formula and then press 'Enter' to have the cell display the result of the formula. You can reference the values in other cells while entering a formula by clicking on the desired cell while entering the formula.
8. Hold down the 'Control' key and then press 'S' to save the current workbook. You will have to enter a name for the workbook and then click the 'Save' button when saving the project for the fist time.

Read more ►

How to Send a Mass Email From an Excel Spreadsheet

Send a Mass Email from an Excel 2003 Spreadsheet
1. Make an Excel spreadsheet containing all of the information you will need for your mass email, such as your contacts' names and email addresses. Enter a heading into the first cell in each column. Save and close the Excel spreadsheet.
2. Open Outlook and minimize it. Open Word and type your email, leaving blanks where you want the recipients' names and other personal information to go. This data will come from the Excel spreadsheet you created.
3. Go to the 'Tools' menu. Point to 'Letters and Mailings' and select 'Mail Merge.' Under 'Select Document Type,' click 'Email Messages.' Click 'Next.'
4. Select 'Use Current Document' and click 'Next.' Click 'Browse,' select the Excel spreadsheet you created, click 'Open' and click 'OK.' Sort the recipients list if desired and click 'OK.' Click 'Next.'
5. Click 'More Items' to enter the fields from your Excel spreadsheet. Insert the fields in the appropriate places. The email address field should go at the top of the document and the 'First Name' or similar field should go into the salutation.
6. Click 'Next' to preview your email message. Click 'Next' again to complete the merge. Click 'Electronic Mail' under merge. Enter a subject for your mass email in the 'Subject Line' field and click 'OK.'
Send a Mass Email from an Excel 2007 Spreadsheet
7. Make an Excel spreadsheet containing all of the information you will need for your mass email, such as your contacts' names and email addresses. Enter a heading into the first cell in each column. Save and close the Excel spreadsheet.
8. Open Outlook and minimize it. Open Word and type your email as desired.
9. Go to the 'Mailings' tab of the ribbon and click the 'Start Mail Merge' button. Select 'Email Messages' in the drop-down menu.
10. Click on 'Select Recipients' in the 'Start Mail Merge' group. Find the Excel spreadsheet you created, click 'Open' and click 'OK.' Select fields from the 'Write Insert Fields' group on the 'Mailings' tab of the ribbon. Click 'Greeting Line' to enter a salutation. Click the 'Match Fields' button to match a heading from the Excel spreadsheet to each field you insert.
11. Go back to the 'Mailings' tab and click the 'Finish Merge' button in the 'Finish' group. Click 'Send Email Messages' to send your mass email.

Read more ►

How to Make a Ledger

1. Open Excel by double-clicking the Excel icon on your desktop. If you don't have an Excel icon on your desktop, you can click 'Start' and then 'All Programs,' followed by 'Microsoft Office.' Then select 'Microsoft Excel.'
2. Enter 'Entry Date' in the A1 field. Enter 'Account Name' in the B1 field. Enter 'Debit/Credit' in the C1 field. Finally, enter 'Amount' in the D1 field.
3. Enter your financial transactions into these four fields to create the general ledger. For example, you purchased office supplies on July 1, 2010, in the amount of $50. For this transaction, you'd enter '7/1/10' in A2 for the entry date, 'office supplies' in B2 for the account name, 'D' in C2 because the transaction is a debit and '$50' in D2 for the amount. Continue entering all your transactions in chronological order.
4. Click the 'Insert' tab, click 'Pivot Table' and then select 'Pivot Table' again if you're using Excel 2010. If you're using an older version of Excel, you'll need to click 'Data,' then 'Pivot Table Pivot Chart Report' and click 'Next.'
5. Left-click on B1 and continue holding down the button to highlight all cells through to the end of the list. For example, if you had 100 transactions added to the general journal, you'd highlight B1 through D101. Click 'Next.'
6. Click 'New Worksheet' and then click 'Finish.'
7. Left-click the 'Name of Account' field and drag and drop it into the 'Row' field area.
8. Left-click the 'Debit/Credit' field and drag and drop it into the 'Column' field area.
9. Left-click the 'Amount' field and drag and drop it into the 'Data' field area.
10. Click the 'Pivot Table' button located in the toolbar and select 'Table Options.'
11. Remove the check mark from the 'Grand totals for rows' check box. Click OK.

Read more ►

How to Write an Excel VBA Program

1. Find the Excel file that you want to augment by including a VBA program. Double-click on the file to open the spreadsheet.
2. Hold down Alt and press F11 to open the VBA editor. The editor will open up in a new window.
3. Right-click on any of your worksheets, which will be listed in the thin column on the left side of the VBA editor. Move your mouse over 'Insert' and select 'Module.' This will add a new module, which is just something to hold your VBA program, to the list. Double-click on the module that appears, and the right half of the VBA editor will turn white.
4. Click on the right side of the VBA editor. A blinking cursor appears. Enter the following code into the editor:Sub Name()Change 'Name' to whatever you want to name your subroutine. Press Enter to complete the line and the VBA editor will automatically place the 'End Sub' command on a line beneath the cursor. This is how you will start all macros that you create in VBA.
5. Create your program's variables on the next few lines. Start each line with the word 'Dim,' which signifies that you are creating a variable. Type the name of the variable followed by the word 'as,' then the type of variable. For example, both 'Dim x as Integer' and 'Dim y as Variant' are acceptable. If you have multiple variables of the same type, place them on the same line separated by a comma, such as 'Dim x, y as Integer.'
6. Enter the actual code for your program beneath the variables. Enter your code in lowercase, as VBA will automatically capitalize commands that it recognizes, which can help you write your code. VBA will help you complete certain commands. For example, when you enter 'Range(' to begin a line of code that will select a range of cells on the worksheet, VBA will display a small box next to your cursor telling the correct format to complete the argument. When you get to a point in your code where it is obvious that you will need to call certain functions, VBA will automatically display a list of all the functions, letting you select from the list instead of typing it in.

Read more ►

How to Hide Gridlines in Microsoft Excel 2003

1. Open Excel 2003 and select a workbook to print. Click 'File' on the menu bar and select 'Open.' Browse your files for the workbook. Click the workbook and select the 'Open' button.
2. Click 'Tools' on the menu bar once the Excel 2003 workbook opens and select 'Options.'
3. Click the 'View' tab once the Options dialog box opens.. Locate the 'Windows Options' section and uncheck 'Gridlines.' Click 'OK' to confirm your changes. The gridlines on your spreadsheet are now hidden from view.

Read more ►

How to Add Cells in Excel

Add Blank Cells to an Excel Spreadsheet
1. Highlight the location for the new cells.
2. Click on a cell and drag the mouse until all the cells in the location have been highlighted. The number of cells you select should be equal to the number of cells you want to add.
3. Click on 'Insert' on the toolbar and select 'cells' from the menu.
4. Select 'Shift cells right' or 'Shift cells down.' 'Shift cells right' will move the data in the cells you have selected to the right and put new cells where the old data used to be, while 'Shift cells down' will move the data down, making space for the new cells above.
Create Totals on a Worksheet
5. Enter data in your worksheet. Type in the figures you want to calculate in a column or a row.
6. Click on the cell where you want the sum total to appear. This is usually the blank cell beneath, or on the side of, the last cell containing data.
7. Click the 'Autosum' button on the toolbar to add the numbers. This will display a formula for the calculation that corresponds to the cells in the range. You can manually adjust the formula if you want to remove some of the cells or include others.
8. Press the 'Enter' key to accept the formula and proceed with the calculation. The sum of the figures will appear in the cell.
9. Find more help on how to add cells on Excel at Microsoft.com. The 'Help and How-to' home page lists the different versions of Microsoft Office products (see Resources below). You can search for information that is specific to your version of Excel.

Read more ►

How to Draw Separating Lines in Excel

Draw a Border Line
1. Open the Excel worksheet.
2. Click the “Home” tab on the command ribbon.
3. Click the arrow next to the “Border” button in the 'Font' group to display a list of border styles. To create a custom line, click the “Draw Border” option in the “Draw Borders” section. The pointer converts to a pencil symbol.
4. Click and drag the cursor on the worksheet to start the line.
5. Release the mouse to end the line. Press any key to convert the pencil symbol back to a pointer.
Insert a Pre-Defined Border
6. Open the Excel worksheet.
7. Click and drag on the range of worksheet cells that will contain the new border or line.
8. Click the “Home” tab on the command ribbon.
9. Click the arrow next to the “Border” button in the “Font” group to display a list of border styles.
10. Click the preferred pre-designed border style, such as “Left Border,” “Thick Box Border” or “Top and Double Bottom Border.” The selected cells display the lines.

Read more ►

How to Make a Timeline Using Microsoft Excel

1. Open Excel 2010 and select the 'File' tab on the ribbon. Click 'New.' Type 'Timeline' in the search box. Review the timeline templates that appear. Click 'Timeline.' Click the 'Download' button. The template downloads to your computer.
2. Review the timeline and update the default timeline. Click on the existing time in the timeline and add your day, month or year sequence to the timeline.
3. Update the text boxes with your timeline data. Remove the timeline guide by clicking the outer edge of the box and selecting 'Delete' on your keyboard. Save your change by clicking the 'Save' icon on the Quick Access Toolbar.

Read more ►

How to Insert a Radio Button Into Excel 2003

1. Open Excel 2003. Right click in the gray area of your standard toolbar. You will see a list of available toolbars. Click the 'Form' toolbar.
2. Click the button that has the radio dial button. If you point to it, it will display 'Option Button.' The cursor will become a thin ' .' This is your indicator to draw the radio button control field. Click and drag until you get a small box. The box will display the name 'OptionButton1.'
3. Rename the radio button by right clicking on the newly drawn button. Select 'Edit Text.' Type a new name over the default name.

Read more ►

How to Create a Border in Excel

1. Open Microsoft Excel.
2. Select the cell or range of cells you want to create a border. For adjacent cells, click the first cell you want to include in the border then drag your mouse until the last cell. This highlights the cells to be included in the border. For nonadjacent cells, click the first cell then press the 'CTRL' key and continue clicking the other cells you want to include. For all cells within the worksheet, click the small box on the corner between the first row (row 1) and the first column (column A.) This selects all the cells.
3. Right-click on the cells included in the border and click 'Format Cells...'
4. Go to the 'Border' tab and under 'Style' choose the border you'd like to use. Choose the color under the 'Color' drop down menu.
5. Click the small boxes around the big box under 'Border.' These small boxes have darkened lines that emphasize the part of the border to be created like the left and right outer borders, upper and lower borders, and the inner borders.
6. Click 'OK' to finish creating the border.

Read more ►

How to Convert Birth Date to Age in Excel

1. Open Excel and bring up the document with birth dates you want to convert.
2. Enter birth dates for each person in one column if there are no birth dates in your spreadsheet yet.
3. Click on an empty cell where you want the age to appear. To make it easy, create a column next to the birth date column and label it 'Age.' For example, if birth dates are in column B, age might be in column C.
4. Enter the following formula in the cell and press 'Enter':=DATEDIF(C2,TODAY(),'Y')Where it says 'C2,' replace this with the cell number that contains the first birth date. Using 'TODAY' will calculate the person's age as of today. If you want to calculate age based on a specific date, enter that date in another cell and use that cell number in the formula instead of 'TODAY().' For example, if you want to know a person's age on July 1, 2010, you would enter 7/1/2010 in cell K2 for example, and enter =DATEDIF(C2,$K$2,'Y') in your age column cells. The '$' sign fixes the reference to a single cell so that when you copy the formula, the cell reference will not change.
5. Copy this cell down the column to calculate age for all your cases.

Read more ►

How to Create an Excel Invoice Number Counter

1. Create an invoice in Excel, and save the file as 'Invoice.xls'
2. Enter your initial invoice number in cell A1. For example, if your initial invoice number is 300, you'd type 300 in cell A1.
3. Press the 'Alt' and 'F11' keys at the same time. This will open the Visual Basic editor.
4. Within the Visual Basic editor, press 'Ctrl' 'R' to open the Project Explorer window.
5. Double click the line that says VBA(Invoice.xls). When the menu opens up, double-click 'ThisWorkbook,' which is a special function in Visual Basic detailing how operations on this workbook will operate. A pane will show up on the right.
6. Enter the following text, exactly as shown here, in the pane on the right, without the quotes. The line breaks are important -- this should show up on three lines in the window.'Private Sub Workbook_Open()Range('a1').Value = Range('a1').Value 1End Sub'
7. Save and re-open the file. Every time the Invoice.xls file is opened, the number in cell A1 will have 1 added to it.

Read more ►

How to Make Frequency Tables

Make Frequency Data Ranges
1. Load your data into Excel. It is easiest to have the data in columns per question, and the responses from the different participants in rows. For example, you might have 100 responses to a survey in your data set. Start numbering your first row with the question numbers, and the respondent responses in the first column in cell A2. Cell A1 would be blank, but cell A2 would have the first respondent's answers to the questions going across. Cell A2 would have the first question's results, cell A3 would be the second question, and so on, to the end of the questionnaire.
2. Look over your spreadsheet after all of the data have been entered, then determine the range of the data. If you have 100 respondents in your data set, you will have 100 rows of data, ending on row 101. (Remember, the first row is the question number.) So your first column's data range will be A2:A101. Your second question will be data range will be B2:B101.
3. Use the simple formula for counting. Say you have six possible responses to your first question. The formula would read as follows:=countif(a$2:a$101,1)This formula tells Excel to count the times that the number 1 occurs in the data range found in column A from row 2 to row 101.The formula to count all the 2's in column A would read as follows:=countif(a$2:a$101,2)The formula for the 3's would be countif(b$2:b$101,3), and so on through all your possible responses to the question.
4. Simplify the process by pasting the first counting formula--countif(a:$2:a$101,1)--into the cells for the number of possible responses you have. For example, if you have six possible responses, copy that formula into the first six cells in the area of your spreadsheet where you are doing your counting. Change the criteria manually from 1 in the second cell to 2, and the third to 3, and so on. Once you have made all the changes to 1 through 6, put in the formula for calculating the percent distributions.
5. Total the count of column results in the first cell below your count. For example, if you are using A105 through A110 to do your counting, you would either use the sum button on the formula toolbar in Excel to sum the column, or this formula: =sum(a105:a110). You would use cell A111 to put in the formula.
6. Use the following formula to calculate the frequency distributions of the results in A105 through A110, starting it in cell A112: =a105/a$111). This will give you a decimal response, which you can reformat into a percentage for easier viewing. Simply copy the formula in A112 and apply it to the five cells that fall below A112 to get the percentage distribution of all the responses.
Make a Frequency Table Using Data Ranges
7. Create or find the data that you want to summarize.
8. Determine the ranges you want. For example, if your data set goes from 1 to 100, you would probably want to break it into 10 segments, 1 to 10, 11 to 20, 21 to 30, and so on. Let's assume your data are in column A, and rows 1 to 100.
9. Type in the following numbers in B1 through B10, in a column next to the data series: 10, 20, 30, 40, 50, 60, and so on, with each number in a separate cell.
10. Select 10 cells with the mouse in the column C next to the data range (column B).
11. Position the mouse in the function bar above the spread sheet (where it says 'fx'), then type in your formula. The formula for counting frequencies is pretty easy: =frequency(b1:b100b1:b10). Since this is an array function, you have to hold down control shift while you hit enter. Otherwise you will get an error like '=NAME?' or something like that. If you have entered your formula correctly, the results will be shown in Column C1 through C10.
12. Total the results of C1through C10 as discussed in Section 1, Step 5, but using the cells in C1 through C10.
13. Use the following formula to calculate the frequency distributions of the results in C1 through C10, starting it in cell C11: =c1/b$b11). This will give you a decimal response, which you can reformat into a percentage for easier viewing. Simply copy the formula in C1 and apply it to the nine cells that fall below C1 to get the percentage distribution of all the ranges.

Read more ►

How Do I Calculate CAGR in Excel?

1. Lay out the list of your data on a line either vertically or horizontally. Do not skip any spaces because the spreadsheet will interpret them as zero values.
2. Arrange the numbers in exactly the chronological order that they occurred. If the data list is short, you can even identify each year next to the figure. For example,$5,000 2007$6,250 2008$7,000 2009$7,900 2010
3. Use the formula to calculate CAGR using the numbers in you data set.(Last number/first number)^(1/n)-1n is the difference of the years. In this case 2010 - 2007 = 3.
4. Plug in the numbers to find the solution.CAGR = (7,900/5000)^(1/3)-1CAGR = .164 = 16.4 percent annually

Read more ►

How to Set the Advanced Filter in Microsoft Excel 2003

1. Open the advanced filter properties box. Scroll to “Data” and click on “Filter.” Select “Advanced Filter” from the submenu.
2. Set the list range. Click on the “List Range” field to activate it and then scroll with cursor as you depress the mouse button and highlight the range of cells and columns that you wish to filter; the information will automatically be added to the field; or you can enter the range of cells in Excel format on your own.
3. Set the criteria range. Click in the “Criteria Range” filter and then click on a single cell in the workbook which contains the criteria; it will automatically be entered into the field.
4. Implement the advanced filter. Check the box labeled “Unique Records Only” if you do not wish to see duplicate records. Click on the “OK” button to implement the advanced filter.

Read more ►