Excel

Browse » Home » All posts

Sunday, December 23, 2012

How to Remove a Check Box in Excel

1. Open the Excel file that contains the check box you want to remove.
2. Click the 'Design Mode' option on the 'Controls' group of the 'Developer' tab.
3. Click once on the check box that you want to delete. Press the 'Delete' key on your keyboard. Repeat this step for each check box that you want to remove.

How to Use Excel's TAN Function

1. Enter your input values into a row or column in a blank spreadsheet. The 'input value' refers to the angle, represented by the 'x.' Let's assume you enter an input value of 5 and you enter it in cell A1.
2. Click your cursor in a different cell and type the formula '=Tan(A1).' This gives you the result of -0.14255. This number represents the tangent of your angle.
3. Change the number of decimal points in your answer if necessary. Go to 'Format' and 'Cells.' Select the 'Number' tab, then 'Number' and enter the number of decimal points you would like.
4. Compare your answers with the most popular tan values: 'Tan(0 degrees) = 0.0000;' 'Tan(30 degrees) = 0.5773;' 'Tan(45 degrees) =1.000;' 'Tan(60 degrees) = 1.7320;' 'Tan(90 degrees) = infinity.'

How to Set Row Height in Excel 2007

1. Open the Excel spreadsheet. Select the rows for which you want to set the row heights. Click on a row in the left column to select the entire row. Press and hold 'Ctrl' and click more rows to select multiple rows, or click the diagonal arrow in the top-left corner of the spreadsheet, above the first row, to select every row.
2. Click the 'Home' tab from the Ribbon if it's not already selected. Click on 'Format' from the Cells section and select 'Row Height.' This opens a dialog box.
3. Type a number into the text box and click 'OK.' This sets the row height for your selected cells to the number you entered.

How to Use Autofill With Numbers in Excel 2007

Autofill the Same Number
1. Type the number in the first cell that you would like to contain that number.
2. Click on the cell to highlight it.
3. Click the small square in the lower-right corner of the cell and hold the mouse button down.
4. Drag the square in whichever direction you would like to AutoFill. Release the mouse button when all desired cells are highlighted. The value is copied into the cells.
Autofill a Sequence
5. Type the first two numbers of your sequence in the first two cells in your list. For instance, to start an integer sequence in the 'A' column you would type '1' in cell A1 and type '2' in cell A2.
6. Highlight the cells that contain the first two numbers in your sequence.
7. Click the small square in the lower-right corner of your selection and hold the mouse button down.
8. Drag the square in the direction that you would like to autofill. Release the mouse button when all desired cells are highlighted. Sequential values are copied into the highlighted cells.

How to Enable Extra Rows in Excel 2007

1. Select the row above which you want to place the new row. You can do this by left- or right-clicking the row number on the left-hand side. For example, if you want to insert a single row between rows three and four, select row four. If you want to insert three rows between rows three and four, select rows four, five and six by clicking on row four, holding down the left mouse button and dragging the cursor down over rows five and six to highlight them as well. To select rows that are not adjacent to each other hold down the 'Ctrl' key and click on the rows you wish to highlight.
2. Click the 'Insert' option at the top of the screen. Navigate down to 'Rows' and click the left mouse button. If done correctly, the new row or rows will appear above the row you've selected. In the case of multiple or non-adjacent rows, they will all appear at once. Alternately, you can right-click the row below where you want to insert a new one. Choose 'Insert' from the box that pops up and select 'Rows.' This will perform the same function.
3. Hold 'Ctrl' and press 'Y' on your keyboard to repeat the row insert. The insertion operation that is repeated will be the same one you just performed. This means if you inserted three rows, then you hold down 'Ctrl' and press 'Y,' three new rows will be inserted.

Saturday, December 22, 2012

How to Use Spin Buttons in Excel

1. Use Excel to create your chart or document. Note: Be sure to save your work every few minutes.
2. Open the 'Developer' tab, select 'Controls,' and then 'Form.'
3. Select the 'Insert' option on that tab. You will see this with a little folderlike icon and some tools.
4. Click on your Excel document where you want the spin button to appear. The place where you click is where the upper-left hand corner of the button will appear.
5. Select the 'Properties' option from the 'Developer' tab and 'Controls.'
6. Start setting your options for the spin button. Let's say that you decided on the age of a person. Select a standard age, such as 25. Then select a starting (minimum) age, such as 21. Next, select an ending (maximum) age, such as 100. Then select the increments in which the numbers go up, such as one year at a time, five years, etc. Finally, fill in the spot that asks for cell information.
7. Click to save your changes and then once again click to save your Excel document.

How to Split a Merged Cell

1. Right click on the merged cell. Click 'Format Cells.'
2. Click on the 'Alignment' tab.
3. Uncheck the 'Merge Cells' check box by clicking on it.
4. Click 'OK'. The merged cells are now split.
5. Click on the icon for merged cells on the menu bar if available to enable or disable merged cells when needing a quick shortcut.

How to Password Protect an Excel Worksheet Using a Macro

1. Open the Microsoft Excel spreadsheet you wish to protect.
2. Press 'ALT F11' to open Visual Basic.
3. Click on the 'Insert' menu and select 'UserForm.'
4. Click on the 'TextBox' tool and drag it onto the 'UserForm' work area. Adjust the size of the text box as desired.
5. Press 'F4' with the 'TextBox' still selected to enter the 'Properties' pane and scroll down to 'PasswordChar.' Insert an asterisk (*) in the value column.
6. Click on the 'UserForm' to bring the toolbox back and drag a 'CommandButton' onto the 'UserForm.' For aesthetic purposes, position the button to the right of the 'TextBox.'
7. Press 'F4' with the 'CommandButton' still selected and change the caption to 'OK.'
8. Click on the 'UserForm,' press 'F4' and change its caption to 'Protect/Unprotect All Sheets.'
9. Press 'F7' and paste the following code as it appears below:Private Sub CommandButton1_Click()Dim WSheet As WorksheetFor Each WSheet In WorksheetsIf WSheet.ProtectContents = True ThenWSheet.Unprotect Password:=TextBox1.TextElseWSheet.Protect Password:=TextBox1.TextEnd IfNext WSheetUnload meEnd Sub
10. Select 'Module' from the 'Insert' menu and paste the following:Sub ShowPass()UserForm1.ShowEnd Sub
11. Press 'ALT Q' to return to Excel.
12. Press 'ALT F8,' then press 'Options' after selecting 'ShowPass' from the list.
13. Assign a shortcut key to the macro and press 'OK.'
14. Press the shortcut key combination to launch the macro, enter a password, then press the 'OK' button to password-protect the sheets in the workbook.

How Do I Repeat Headers on Every Page In Excel?

1. Open the Excel spreadsheet that you want to print with repeating headings.
2. Click the 'Page Layout' tab on the ribbon.
3. Click the 'Print Titles' button in the Page Setup group. This will open a window with several tabs. Select the 'Sheet' tab if it is not already open.
4. Click the little blue icon with a red arrow at the far right of the box called 'Rows to repeat at the top.' This will bring up a narrow window with a blank line. Click the row in your spreadsheet that you want repeated. Usually this is the first row with column headings. To select several rows, click and drag down until you see all the rows you want, highlighted. This will insert the row numbers into the narrow box.
5. Click the little icon on the far right of the box to insert the row numbers into the printing window.
6. Click 'Print Preview' to see how the headings will look on multiple pages. Click 'Print' if you are satisfied.

How to Change Cell Font Alignment in Microsoft Excel 2003

1. Select all of the cells. To change the alignment in all of the cells, make sure that you have your spreadsheet file open, then use the hotkeys “CTRL-A” to select all of the cells in the spreadsheet.
2. Open the cell properties box. Once you have selected all of the cells, right-click on the highlighted cells; a cell submenu will open. Select “Format Cells” and a cell properties box will open.
3. Select the desired font alignment. Scroll to the “Alignment” tab in the cell properties box to change the alignment. You can set the vertical and horizontal attributes as well as specifying whether the text will wrap, shrink to fit, or merge inside of the formatted cells. Make the desired alignment selections and then click “Okay” to implement the changes.
4. Make sure to save the updated file. After you have made the desired changes, save your file by scrolling to the “File” tab on the command bar and left-clicking on “Save.”

How to Format Worksheet Groups in Excel

1. Start Microsoft Excel 2007 and open an existing workbook that contains worksheets that are grouped together to form a group.
2. Look at the sheet tabs in the lower left corner of the Excel screen. The sheet tabs that are white represent the worksheets that are part of the group.
3. Click on a white sheet tab so you bring up one of the worksheets that are in the worksheet group. It does not matter which one you choose, the formatting will be applied to all the worksheets in the group.
4. Select the cells in the worksheet that you want to apply formatting to in the selected cell on this worksheet and the other worksheets in the group.
5. Apply the formatting to the cells such as a border, shading, formula, function or column width.
6. Highlight and click on another white sheet tab to bring up another worksheet in the same group. Notice that the same cells in that worksheet have also had the same formatting applied.
7. Ungroup the worksheet group once you have finished formatting all the cells you want to format in the worksheets.

How to Sort Microsoft Excel Rows Alphabetically

1. Select the data you would like to sort alphabetically. Maybe it's just a small selection in a large worksheet, so simply select only the cells and any corresponding data; everything else remains the same.
2. Click on 'Data' from the main toolbar at the top of the worksheet and in the sub-menu that drops down, click on 'Sort.' The Excel program automatically selects the entire set of data and opens a new window.
3. Check to see that the data you select matches what you want to sort. If the program indicates that there isn't enough data or the data selection is too limited in some way, a 'Sort Warning' box appears. You can change the data selected before you continue.
4. Look at the choices in the 'Sort' sub-window and let the program know if your data has column headers or not. If you select 'Header Row' option, Excel will not sort that row of data. If you select 'No Header Row,' then all rows of data, including any header rows like Name, Address, City, State and Zip, will sort alphabetically in the process.
5. Decide how you want the data sorted. You can choose to only have one column sorted and all corresponding data will match up with it or have subsequent columns sorted after. For example, you can sort by last name from A to Z (ascending order) then sort the data alphabetically by first name, then again have the list alphabetically sorted by city.

How to Create a Normal Distribution Graph in Excel

1. Enter -4 in cell A1. Enter -3.75 in cell A2. Highlight both cells and grab the fill handle (the tiny box in the bottom right hand corner) with your mouse. Drag the fill handle to cell A33 and release the mouse.
2. Enter =NORMDIST(a1,0,1,0) into cell B1. This tells Excel to calculate the standard normal distribution from the value you entered in cell A1 with a mean of 0 and a standard deviation of 1. Press enter.
3. Using the same motion you used in Step 1, drag the fill handle from the corner of cell B1 down to cell B33.
4. Highlight cells A1 through A33 by holding the the left mouse button down and dragging the cursor.
5. Select 'Insert' from the toolbar, then 'Scatter,' and 'Smooth Line Chart.'
6. From Chart Tools at the right hand side of the toolbar, select 'Layout,' 'Axes,' 'Primary Vertical Axis,' then 'None'. This step will make the y-axis disappear.
7. Select 'Axes' from the center toolbar, then 'Primary Horizontal Axis'. Select the bottom option ('More Options'). Change the minimum x-value to -4 and the maximum x-value to 4 by pressing the appropriate radio button and filling in the values.

Friday, December 21, 2012

How to Create a Drop

1. Open Excel 2007 and click a blank cell in the workbook. Select the 'Data' tab and select 'Data Validation.' A drop-down list appears. Click 'Data Validation.' On the 'Setting' tab, change the Allow field to 'List.' In the source field, type 'yes,no,maybe.'
2. Click the 'Input Message' tab. Click 'Show input message when the cell is selected.' Add a title for your input message in the 'Title' field. Add a custom input message in the 'Input Message' notes field.
3. Click the 'Error Message' tab. Click 'Show error message when invalid data is entered.' Add a title for your error message in the 'Title' field. Add a custom input message in the 'Error Message' notes field. Select a style for your error message in the 'Style' drop-down list. Click 'OK.'
4. Click the cell where you started the data validation process. Notice the drop-down list that appears. You also will see the input message.

How to Calculate Correlation Coefficient Between Two Data Sets

1. Open Excel 2007 and sum in one column the numbers for the first set of data. For example, you would add the numbers 10, 20, 30, 40, 50 and 60 in the A2, A3, A4, A5, A6 and A7 cells of your Excel worksheet. In a second column, sum the numbers for the second set of data. For example, you would add the numbers 5, 2, 6, 6, 7 and 4 in the B2, B3, B4, B5, B6 and B7 cells of your Excel worksheet. Your goal is to find the correlation coefficient for these two sets of data.
2. Click on the 'A9' cell. This is the cell where you will calculate the correlation coefficient.
3. Click on the 'Formulas' tab and choose 'Insert Function' (this is found on the top left hand side of Excel spreadsheet). The 'Insert Function' window will open. Click on the drop-down menu of 'Or select a category' and choose 'Statistical.' Scroll down the 'Select a function' window. Choose 'CORREL.'
4. Click 'OK.' The 'Function Arguments' window will open, and you will see two cells: 'Array1' and 'Array2.' For Array1, enter A2:A7 for first set of data and for Array2, enter B2:B7 for the second set of data. Click 'OK.'
5. Read your result. In this example, the calculated value of the correlation coefficient is 0.298807.

Subscribe to: Posts (Atom)