How to Use Ticks in Excel

1. Open the Excel file.
2. Click 'Developer Tools' on the command ribbon.
3. Click the down arrow on the 'Insert' button in the 'Controls' group. A list of controls appears.
4. Click the 'Check Box' button. This button displays a check mark inside of a square. The pointer changes to a ' ' symbol.
5. Click on the cell to place the check box. A square and the text 'Check Box 1' display on the worksheet.
6. Edit the text if desired. For example, delete 'Check Box 1' and type an item or a step to compile a list.
7. Click the text to insert a check or tick in the check box. To remove the check, click again on the text.
8. Press 'Ctrl' and 'S' simultaneously to save the file.

Read more ►

How to Plot a Secondary Axis on Microsoft Excel

Secondary Vertical Axis
1. Open the spreadsheet in Excel that contains the chart to which you want to add a secondary axis. Click the 'Microsoft Office' button, then click 'Open' and then locate the file on your computer. Click 'Open' to open the file. Your existing chart should pop up on the screen when you open the spreadsheet. Depending on the type of graph you created, it could be a bar chart, line graph or other graph type.
2. Click the data series in the chart you want to plot on a secondary axis.
3. Click the 'Format' tab, then click 'Format Selection' in the 'Current Selection' group.
4. Click the 'Series Options' tab, then click 'Secondary Axis' below 'Plot Series On.' The secondary axis will be display in the chart.
Secondary Horizontal Axis
5. Click anywhere on the chart to display Chart Tools.
6. Click the 'Layout' tab, then click on 'Axes' from the 'Axes' group.
7. Click 'Secondary Horizontal Axis,' then click the type of display you want. For example, if you want an unlabeled axis, click 'Show Axis Without Labeling.'

Read more ►

How to Open Excel 2007 in an Earlier Version of Excel

1. Go to the Microsoft Download Center online.
2. Click on the Microsoft Compatibility Pack. Click 'Download.'
3. Click 'Run' in the box that appears on the screen.
4. Click 'Run' in the next box that appears on the screen.
5. Check the box 'Accept' to accept the terms.
6. Click 'OK' in the next box to appear on the screen. Wait for the loading to finish.
7. Open Excel and double-click on the Excel 2007 file that needs to be opened. Wait for the file conversion to complete.
8. Makes changes and work in the file, saving it when done.

Read more ►

How to Insert a Tab Name in a Cell

1. Open Microsoft Excel. Choose the spreadsheet you want to work with.
2. Click on the cell where you want to insert the tab name.
3. Enter the following formula into the cell:=MID(CELL('filename',A1),FIND(']',CELL('filename',A1)) 1,256)

Read more ►

How to Convert Excel to DAT

1. Double click to open your Microsoft Excel 2010 file.
2. Click the 'File' tab in the upper-left corner, then select 'Save As.'
3. Click the drop-down menu to the right of 'Save As Type' and select 'CSV (Comma Delimited).'
4. Click the 'Save' button, click 'OK' to save the active sheet, then click 'Yes' to keep the worksheet in CSV format.
5. Right click the CSV file you just created, click 'Open With' and select 'Notepad.'
6. Click 'File' at the top of the window, then click 'Save As.'
7. Click the drop-down menu to the right of 'Save As Type' and choose 'All Files.'
8. Click inside the 'File Name' field and add '.dat' to the end. For example, if your filename is 'xxxxxx,' modify it so it reads, 'xxxxxx.dat.'
9. Click the 'Save' button.

Read more ►

How to Change the Macro Security Levels on Excel 2003

1. Open the Microsoft Excel 2003 application on your computer, and then click the 'File' option from the top toolbar menu.
2. Click the 'Open' option, and then select the Excel 2003 document you want to work with. Click the 'Open' button.
3. Click the 'Tools' option from the top Excel toolbar menu, and then click the 'Options' button.
4. Click the 'Security' tab in the new dialog box that appears on the screen. Click the 'Macro Security' option.
5. Select the 'Security Level' tab and then select the 'Very High', 'High', 'Medium' or 'Low' option. Click the 'OK' button and close out of the dialog box.

Read more ►

How to Delete the First Five Characters in Excel 2007

1. Open your spreadsheet in Microsoft Excel 2007.
2. Locate the cell that contains the text you need to truncate.
3. Enter the following text in a new cell, replacing 'old_text' with the cell reference of the text you need to truncate.=REPLACE(old_text,1,5,'')As an example, to delete the first five characters from cell A1, you would enter:=REPLACE(A1,1,5,'')Alternatively, you can replace 'old_text' with the actual text encased in quotes. As an example, to delete the first five characters from the text 'truncate me!,' you would enter:=REPLACE('truncate me!',1,5,'')

Read more ►

How to Calculate Business Quarters in Excel Not Based on Calendar Year

1. Open a new workbook in Excel and enter a list of random dates that can be used for testing.You can copy these example dates to your sheet beginning in cell A1:1/4/2010
2/11/2010
3/21/2010
4/28/2010
6/5/2010
7/13/2010
8/20/2010
9/27/2010
11/4/2010
12/12/2010
2. Determine the number value of the month in which your 'year' starts (e.g. January is 1 and November is 11). For this example, the year will start in October (month 8).
3. Subtract one from your month value. In this example, the result would equal seven (7).
4. Substitute your new value into the following equation:=MOD(CEILING(22 MONTH(
) -
- 1,3)/3,4) 1For this example that would be:
=MOD(CEILING(22 MONTH(A1)-7-1,3)/3,4) 1
5. Copy and paste the equation into cell B1 in your Excel worksheet.
6. Click and hold the mouse button down on cell B1 and drag your mouse cursor down to highlight all the cells from there to B10.
7. Hit the keys 'Control' ('Ctrl') and D at the same time to copy the formula down to all the cells.Your worksheet should now look like the following data:01/04/20102
02/11/20103
03/21/20103
04/28/20103
06/05/20104
07/13/20104
08/20/20101
09/27/20101
11/04/20102
12/12/20102

Read more ►

How to Copy Formulas Without Changing Them in Excel

1. Open your Excel document.
2. Click on the cell containing the formula you wish to work on.
3. Locate your cell references in the formula shown in the Formula Bar. These will be a letter followed by a number, such as A3 or D14.
4. Place a dollar sign ($) in front of the letter and number of the cell reference you don't want to change. Examples would be $A$3 or $D$14.
5. Copy your formula to the other parts of your spreadsheet--you'll see that Excel does not change your cell references.

Read more ►

How to Center Horizontally and Vertically in Excel

Center Horizontally
1. Click the cell or cells in which you want to center data horizontally.
2. Click the 'Home' tab.
3. Click the 'Center' button located in the 'Alignment' group. The Center button centers the text horizontally in the selected cell.
Center Vertically
4. Click the cell or cells in which you want to center data vertically.
5. Click the 'Home' tab.
6. Click the 'Middle Align' button located in the 'Alignment' group. The Middle Align button centers the text vertically, between the top and bottom of the cell.

Read more ►

How to Lock a Formula in Excel 2007

1. Select the cell containing the formula you wish to lock by highlighting it.
2. Click on the 'Home' tab, which is located on the top of the Excel window.
3. Navigate to the 'Cells' module, which is on the right half of the Excel window. Select 'Format.' A menu displaying cell formatting options will appear.
4. Navigate to the 'Protection' section of the format menu. Click on 'Lock Cell,' highlighting the lock icon.
5. Protect the sheet; until you do so, locking the formula cell will not take effect. Repeat steps 2 and 3. Select 'Protect Sheet' from the cell formatting menu. A dialog box titled 'Protect Sheet' will appear.
6. Select the 'Protect worksheet and contents of locked cells' option. Type in a password that users must enter to unprotect the sheet into the password field; this is not a required step. In the 'Allow all users of this worksheet to' section, choose privileges that you wish users to have when working with the sheet. Finish by clicking 'OK.'

Read more ►

How to Do Descriptive Statistics in MS Excel 2007

Analysis ToolPak
1. Install the Microsoft Office Analysis ToolPak. Click the 'Microsoft Office Button' and then 'Excel Options.'
2. Click 'Add-ins' and then 'Excel Add-ins' located in the Manage box. Click 'Go.'
3. Select 'Analysis ToolPak' in the Add-ins Available box and click 'OK.' If you do not see the Analysis ToolPak option, click 'Browse' to locate it. Click 'Yes' to install it if a prompt signifies that it is not installed on your computer.
Descriptive Statistics
4. Collect the data you would like to analyze. The descriptive statistics tool will be used later to organize and interpret the data for you.
5. Open Excel to generate a new spreadsheet.
6. Type the label of your first column in cell 1A. Enter the data values that you are evaluating in the cells directly below your label. For example, a real estate professional wishing to analyze prices of homes might label column 1A Sale Price. Thereafter, the various sale prices will be keyed into cells 2A, 3A, 4A and 5A.
7. Save the file so that you do not lose your data. Click on 'File,' 'Save.' Type in the file name and click 'Save.'
8. Select 'Tools,' 'Data Analysis.' This can be found under the Data tab. Click 'OK' to open the dialog box.
9. Choose 'Descriptive Statistics' in the dialog box. This is the tool you want to use. Click 'OK.'
10. Click on the small chart box located to the right of the input range. Highlight the spreadsheet column containing the data that you want to summarize. Do this by holding down the left mouse button and highlighting all of the data in the column you want to select. This data will be placed in your input range. Click on the small chart box again to return to the Descriptive Statistics box. If you also highlight the label, click on 'Labels in First Row' on this screen.
11. Click 'Output Range' and indicate to which cell you want the results to go. For example, choose cell H1 by typing 'H1' in the output range.
12. Select 'Summary Statistics' and click 'OK.' The descriptive statistics are now generated. Double-check the count in the results to make sure it included the correct number of items from your list of data.

Read more ►

How To Calculate Mortgage Payments in Excel

1. Start a new, blank workbook in Excel. In Excel 2007, click the 'Office' button and click 'New'; then click 'Microsoft Online.' In Excel 2003, go to the 'File' menu and click 'New'; then go to the 'Templates' section of the task pane.
2. Type 'mortgage calculator' in the 'Search Microsoft Online' box and click 'Go.' A list of possible templates will appear.
3. Select the 'Mortgage Payment Calculator' template and click the 'Download' button. The Mortgage Payment Calculator template will open as a new Excel spreadsheet.
4. Enter the mortgage loan amount in cell C7. Enter the interest rate in C8. Type the number of years of the mortgage loan in cell C9. The worksheet will calculate the monthly payment amount, as well as other data, such as the number of payments and equity.
5. Enter up to five different mortgage-loan amounts into columns C through G. This will make it easier to compare options, such as a 15-year mortgage vs. a 30-year mortgage, or different interest rates or loan amounts.

Read more ►

How to Prepare an Excel PivotTable

Using Excel Data
1. Open the Excel file that has the information that you want to make into a PivotTable.
2. Edit the range of data that you will use to remove any blank rows. Also place a column name in the top row, for any column that doesn't have one.
3. Select any cell in the range of data that you want to use.
4. Click on the 'Insert' tab at the top of the screen, and then click the 'PivotTable' button that appears on the toolbar. A small window will appear, and Excel will automatically select the entire range of data. Press 'OK' to continue, and the PivotTable will be created on a new worksheet.
Using External Data
5. Open the Excel 2010 program by double-clicking the Excel icon.
6. Click the 'Insert' tab at the top of the screen and then click the 'PivotTable' button on the left side of the toolbar.
7. Select the 'Use an External Data Source' radio button and then press the 'Choose Connection' button just below the radio button. A list of available connections will show up.
8. Click the connection you want to use and then click 'Open.' If you do not see the connection that you need, click on 'Browse for more' and then navigate to the connection file and click 'Open.'
9. Select 'New Worksheet' to place the PivotTable on a new worksheet, or select 'Existing Worksheet' and then input the cell where you want the PivotTable to go. Click 'OK' when you are done.
Placing Information in the PivotTable
10. Select a cell anywhere in the PivotTable placeholder graphic to bring up the field list on the right side of the screen.
11. Place checkmarks on the field list to the right side of the window, next to the fields you want to add to the PivotTable. Each field that you see is equal to one column of data from your original data set. When you place a checkmark, you will see the field appear both on the PivotTable in the main window, and in one of four boxes to the right of the field list. Each box corresponds to a different area on the PivotTable.
12. Drag and drop fields between the boxes on the right of the screen to place them where you want them on the PivotTable. Fields in the 'Column Labels' box will appear at the top of the PivotTable, while fields in the 'Row Labels' box will appear along the left side. Any field in the 'Values' box will have its data make up the body of the PivotTable, and any field in the 'Report Filter' will appear in a small drop-down box above the PivotTable, where you can filter the entire table.

Read more ►

How to Turn Off Sharing in Excel 2007

1. Launch Microsoft Excel and locate the 'Review' tab on the ribbon located on the top of the screen.
2. Click the 'Share Workbook' tool located in the 'Changes' group to launch the 'Share Workbook' dialog box. You should see a check mark next to the 'Allow Changes' check box.
3. Click the 'Allow Changes' check box to clear the check mark. Click 'OK' to save your changes and disable sharing.

Read more ►