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 ►

How to Use Subtotals and Totals in an Excel Spreadsheet

Creating Labels for Your Spreadsheet
1. Start Microsoft Excel and open the file you want to change.
2. Enter label titles for your columns and rows.
3. Drag over the column that contain the label titles.
4. Click B (boldface icon) on the tool bar.
5. Drag over the row that contain the label titles.
6. Click B (boldface icon) on the tool bar.
Creating Subtotals and Grand Totals
7. Drag over the columns and rows for which you want to create subtotals and grand totals.
8. Open the Data menu and select Subtotals.
9. In the Subtotal dialog box, select the columns names you want subtotaled in the 'Add subtotal to' option.
10. Select OK.

Read more ►

How to Insert and Size Pictures in Microsoft Excel 2003

1. Open and insert the picture file. To open a picture file, scroll to the “Insert” tab and then select “Picture.” Under the submenu, select “From File” and browse to the desired image file. Click “OK” to insert it into the spreadsheet.
2. Drag the picture where desired. Once the picture is inserted, you will noticed circular drag points that border it. You can click on the middle of the picture and hold, then drag the picture around the spreadsheet.
3. Resize the picture using drag points. To resize the picture using these circular drag points, left-click and hold on a drag point and then drag the picture to resize it as desired.
4. Resize the picture using the “Format Picture.” To access this toolbar menu, right-click on the picture and select “Format Picture.”
5. Use the format picture menu to resize. Once in the format picture menu, left-click on the “Size” tab. Under “Size and Rotate” you can specify the height and width in inches by typing them into the corresponding boxes. Under the “Scale” section, you can specify the height and width by percentages by typing them into the corresponding boxes.
6. Implement size changes. To implement the size changes that you just made, click on the “OK” button.

Read more ►

How to Alphabetize an Excel Spreadsheet

1. Right-click on the Excel workbook you want to alphabetize. Click 'Open,' then click on the worksheet tab at the bottom of the workbook that contains the data that you want to organize.
2. Click the upper left corner of the spreadsheet, just above Row 1 and to the left of Column A, to select all of the cells in the sheet.
3. Click 'Sort and Filter' on the home tab (it is on the far right side of the window) and then select 'Custom Sort....'
4. Change the entry in the 'Sort by' drop-down menu to the column you want to alphabetize and click 'OK.' You can also arrange entries in reverse alphabetical order by changing the 'Order' setting to 'Z to A.'

Read more ►

How to Link Sheets in Excel 2007

1. Highlight the content of the first (original) sheet you want to link in Excel 2007.
2. Click on the worksheet tab (at the bottom of your spreadsheet) of the worksheet you want to link to.
3. Select the 'Home' tab. Choose 'Paste' and 'Paste Link' from the 'Clipboard' group to link to the sheet.

Read more ►

How to Make a Linear Vs. Logarithmic Chart

1. Click on the Excel logo on your desktop to open Excel.
2. Click on a cell in a new Excel document and type in the first of your data points. Press the enter key and enter the second data point in the cell below. Repeat this until you have entered all your data points.
3. Click on the first cell in your column of data and then drag down, keeping the left mouse button pressed, until you have highlighted your entire column of data.
4. Click on the 'Insert' tab in Excel and then click the 'Line' button in the charts section of the ribbon. A menu should appear.
5. Click on one of the 2-D chart styles in the menu. A chart will appear in Excel.
6. Click on the row of numbers running down the left-hand side of the chart. Right-click and select 'Format Axis.' The Format Axis menu will appear.
7. Click on the 'Axis Options' tab of the Format Axis menu. Click on the tick box next to 'Logarithmic scale'.

Read more ►

How to Hide an Excel Worksheet so Another User Can't Unhide It

1. Open the Excel worksheet.
2. Click the sheet tab you wish to hide. If the tab is not visible at the bottom of the screen, click the tab scrolling button until the tab comes into view, then click the tab.
3. Click the 'Home' tab on the command ribbon.
4. Click the down-arrow for the 'Format' button in the 'Cells' group. A list of options appears.
5. Click the 'Hide Unhide' option in the 'Visibility' section.
6. Click the 'Hide Sheet' option. The worksheet hides from view.
7. Customize the command ribbon so the 'Cells' group and its 'Format' button also hides from view by clicking the Excel 'File' tab and the 'Options' link. Click the 'Customize Ribbon' button. Click the 'Main Tabs' option in the 'Customize the Ribbon' text box on the right.
8. Click the 'Cells' group under the 'Home' tab section. Click the 'Remove' button between the left and right panes. Click 'OK.' The 'Cells' group and the 'Format' button that contains the 'Unhide Sheet' option disappears from the command ribbon.

Read more ►

How to Write Macros for Graphs in Excel

1. Map out the process. You can create a graph from a macro as long as the process is always the same; that is, you will need to pick the same graph type every time. The best way to ensure you don't miss any steps is to map the process out first on a piece of paper. You can write out steps or use blocks and arrows, whichever is most comfortable and easy to read. This process has the potential to save a lot of time in terms of reducing trials and errors.
2. Open the report you want to create a graph for. The challenge with creating a macro for graphs is that the information must be pulled from the same section each time in order for the macro to work. That is, the best way to run a graphing macro is to use it on a report where the formatting stays the same, but the numbers change. This means the numbers which are being graphed are always in the same place.
3. Go to the Tools menu. Click on 'Macros' and 'Record New Macro'.
4. Create the macro name and keyboard short-cut. You can choose anything you want, but let's go with 'Graph' and 'ctrl g' for this example.
5. Begin creating the graph. Once you create the name the macro will begin to record your commands. There are many ways to create a graph, but the easiest is to click on the graph icon in the standard toolbar. This icon looks like a chart. Walk through the wizard, step by step. Each choice will also be chosen in your macro command. When finished click 'OK' and 'Stop Recording'.
6. Run your macro. You can access your graphing calculator in two ways: 1) go to the Tools menu and then click on 'Macros' to see a list of the macros created by name, choose 'Graph' and the select 'Run'; or, 2) hold down the 'ctrl' key and press 'G'.

Read more ►

How to Modify the Data Source in Excel 2007

1. Launch Excel 2007.
2. Click the 'Windows' button in the top-left corner. Scroll down an click 'Open' and then find the spreadsheet that you want to modify. Highlight the field or fields that you want to modify.
3. Click the 'Options' tab. Click the 'Change Data Source' button in the 'Data' group.
4. Click the radio button either next to 'Select a table or range' or 'use an external data source.' Type in the Table/Range' in the field or hit the 'Choose Connection' button and find the source that you want to link with your Excel file.
5. Click the 'OK' button.

Read more ►

Rotate Text in Microsoft Excel

1.
Open Microsoft Excel. Then open an existing worksheet that contains text you would like to rotate-orr create a new worksheet.
2.
Select the cell or cells that contain text you would like to rotate by clicking on a single cell, dragging your mouse across them, or holding down the 'Ctrl' key and clicking on each cell.
3.
Right-click the selected cells and choose 'Format Cells.' Click on the 'Alignment' tab.
4.
Click the 'Horizontal' drop-down button in the 'Text Alignment' section and choose 'Center.' Click the 'Vertical' drop-down button as well, and choose 'Center' there, too.
5.
Move your mouse to the 'Orientation' section of the dialog box. Click the red diamond to the right of the word 'Text' and drag it to rotate the text to the angle you desire. Click 'OK.'

Read more ►

How to Create an Excel 2007 Chart With Text

1. Click the 'Insert' tab of the ribbon at the top of the page. In the 'Charts' area, select the type of chart you want to create. Excel 2007 provides many chart options including bar graphs, scatterplots and pie charts.
2. Click on the 'Select Data' button in the 'Design' section of the ribbon. In the 'Chart Data Range,' select the entire area that contains your chart data. In the 'Legend Entries (Series)' section, click 'Add' to select each segment of information.
3. Define the 'x' values that are displayed on the horizontal axis and the 'y' values that are displayed on the vertical axis. Name the series to display a description on the legend or select the cell that contains the series title to automatically update the chart when you update the cell. Add all of your series and click 'OK' on each screen.
4. Select the 'Layout' tab of the ribbon and click the 'Chart Title' option box. Choose whether you want to display the main title above or on the first lines of the chart. Type in the chart title in the text box.
5. Click the 'Axis Title' drop-down box and select both a horizontal axis label and vertical axis label. Type the descriptions of your data into the new axis text boxes. Save your Excel spreadsheet by pressing 'Ctrl-S' or the save 'Save' icon at the top of the screen.

Read more ►