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.

Read more ►

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.

Read more ►

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.”

Read more ►

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.

Read more ►

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.

Read more ►

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.

Read more ►

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.

Read more ►

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.

Read more ►

How to Freeze a Row in Microsoft Excel

1. Open the Excel worksheet.
2. Click the top row heading. The row heading displays a number just left of the first column of cells. The selected row appears shaded.
3. Click the 'View' tab on the command ribbon.
4. Click the 'Freeze Panes' button in the 'Window' group. A list of options appears.
5. Click the 'Freeze Top Row' option. A black horizontal line appears on the worksheet. This line indicates the locked row that stays on the screen as you scroll down the worksheet.

Read more ►

How to Add a Title to an Excel Chart

1. Start Microsoft Excel 2007 and open a spreadsheet that contains a chart to which you would like to add a title.
2. Click in the white area of the chart to select the entire chart. There should be a light blue outline surrounding the chart indicating that you have selected the entire chart.
3. Select the 'Layout' tab at the top of the Excel screen to display the layout options for the selected chart.
4. Click the 'Chart Title' button in the 'Labels' section of the 'Layout' ribbon. A drop-down menu will appear that will display the different locations that you can add a title for the selected chart.
5. Choose 'Centered Overlay Title' to add a centered title that lies on top of the existing chart so the chart does not have to be resized. Choose 'Above Chart' to add a centered title that goes above the chart and resizes the rest of the chart so it can fit.
6. Click the chart title you have just added to the chart and move the chart by clicking and dragging it to its new location. You must click on the outline of the chart with a 4-headed arrow before you can successfully move the entire title.

Read more ►

How to Use a Filter in Excel

1. Isolate column headings to one cell. If a column heading spills over into another cell, use text wrapping to place the heading in one cell. Highlight a multi-cell column header, press 'Format' on the menu bar, click 'Row', select 'Autofit and then Format', select 'Cells' and 'Wrap Text' to place headers in one cell.
2. Format the row that contains the column heading differently than the rows that contain data, so Excel recognizes it is a row heading. Embolden characters, change the font color or place a border around the column heading to differentiate from data cells.
3. Ensure each column contains one type of data. For example, a spreadsheet with student data should have a column for test grades, one column for averages, one column for student names and so on.
4. Click any cell inside the spreadsheet you want to filter. If you select an entire column as opposed to a single cell, Excel will present the option to filter that particular column, not all of the columns in the data set.
5. Press 'Data' on the menu bar, scroll down to 'Filter' on the drop-down list and click 'Auto-Filter.' The 'Auto-Filter' drop-down arrows will appear to the right of every column heading of the single column you selected for filtering.
6. Click the drop-down arrow near the column heading for a column of data to display the filter options for the particular column. Observe that Excel displays only the data that applies to your filter selection. Excel hides any rows that do not contain the selected filter.
7. Filter the top or bottom numerical records in a column of data with Excel's 'Top 10' Filter. Click on a data cell in the column and click 'Top 10' at the top of the Auto-Filter drop-down menu in Excel 2003. For Excel 2007, click 'Numbered Filters' and choose 'Top 10.' Select 'Top' or 'Bottom' in the Top 10 Auto-Filter dialog box, choose a number of records from 1 to 500, select 'Items' or 'Percent,' then click 'Apply.'
8. Set custom filters to show records that meet two criteria instead of one. Click the Auto-Filter drop-down menu of the column heading you want to set a filter for and press 'Custom.' Enter two filtering conditions for the column of data. For example, you can see which students scored from 90 to 100 and 60 to 70. Check either the 'And' or 'Or' button or else the results will not display. Click 'OK' to set the custom filter.
9. Click 'Data' on the menu bar, point to Filter and press 'Show All' to turn off the filter and display the hidden data.

Read more ►

How to Put Roman Numerals in Microsoft Office 2003

1. Open Microsoft Office Excel 2003.
2. In the Excel spreadsheet, click on a cell where you wish to put a Roman numeral.
3. Type '=Roman(58)' and press 'Enter.' The Roman numeral 'LVIII' that represents '58' will appear in the cell. Note that you can enter any number from 1 to 3,999 in parentheses.
4. Repeat Step 3 for other cells and/or numbers.
5. Click on a cell with the Roman numeral created in steps 3 or 4, and press 'Ctrl-C' on the keyboard to copy it.
6. Launch Microsoft Office Word 2003. Click the menu 'File' and select 'New' to create a new document, or 'File' and 'Open' to open an existing one.
7. Place the mouse pointer where you wish to insert the Roman numeral and press 'Ctrl-V' on the keyboard.

Read more ►

How to Use a Saved Template for Pivot Charts

1. Click 'Start' and 'All Programs.'
2. Navigate to the 'Microsoft Office' folder, click it once to display the folder contents and then click 'Microsoft Excel' to launch the program.
3. Click the 'Office' button in the upper left corner, and then select 'New' to create a new document. Or click 'Open' to locate and open an existing Excel spreadsheet.
4. Click and hold your mouse on the uppermost cell containing your target data, and then drag the mouse until all of your target data has been selected.
5. Click the 'Insert' tab at the top of the screen. Then click on the 'PivotTable' icon, and select the 'PivotChart' option.
6. Select whether you would like the PivotChart to be inserted into the existing worksheet or into a new worksheet. Click 'OK.'
7. Locate the 'PivotChart Tools' section at the top of the screen, and click the 'Design' tab in this section.
8. Click the 'Change Chart Type' icon at the top of the screen.
9. Select the 'Templates' option in the left column, select your saved template and click the 'OK' button.

Read more ►

How to Create a Calendar in a Pull

Create a Calendar Using Excel 2007
1. Enable the 'Developer' tab. The Excel 'Developer' tab is inactive by default, so you may need to activate it to create a pull-down menu. To activate the 'Developer' tab, click the 'Office' button in the top-left corner of Excel. Locate and click the 'Excel Options' button along the bottom right of the menu. Select and click the 'Show Developer in Ribbon' check-box from the pop-up menu, then click 'OK' to exit.
2. Insert a pull-down calendar. Click the 'Developer' tab from the Excel main menu ribbon. Click 'Insert' to display a menu of options and then click the 'More Controls' icon from the ActiveX controls section. The 'More Controls' icon is the last icon in the second row. It appears as a hammer and wrench crisscrossing in an 'X' pattern. A long list of additional controls will appear, so scroll down until the 'Microsoft Date and Time Picker 6.0' option appears. Click the option and then click 'OK' to return to your spreadsheet.
3. Draw, position and size the pull-down calendar. Draw a rectangle the size you desire for the calendar. Use the resize handles on the drop-down box to adjust the size if necessary. To move the calendar to another location on the Excel spreadsheet, click inside the calendar, hold the mouse button down and drag it to a new location. Resize the cell to fit the calendar by double-clicking the top margin lines.
4. Test the calendar. Deselect the 'Design Mode' tab in the main menu ribbon by clicking it once. Click the black triangle to the right of the calendar date, and your calendar will appear. If you need to make additional adjustments, go back to 'Design Mode.'
Link Calendar to Another Cell
5. Select the link cell. Select the cell you want to update when the date on the calendar changes, such as H9. In the formula bar, type in '=B5' (or whatever cell the calendar is in). Then click anywhere outside the cell to deselect it.
6. Create a link between the calendar and the update cell. Click 'Design Mode' and then click on the drop-down calendar to select it. Select 'Properties' option from the options box next to the 'Design Mode' button and locate the 'Linked Cell' option, and in the 'Linked Cell' information box, type in 'H9' (or whichever cell you select for the date update). Close the 'Properties' box and click on 'Design Mode' to deselect.
7. Activate the update. Change the date on the pull-down calendar. The update will appear in the linked cell.

Read more ►

How to Make a Dashboard in Excel

1. Open an Excel workbook containing data you want to manipulate. Create a new worksheet, inserted at the front of the workbook, and call it 'Dashboard.'
2. Click 'File' and then 'Options.' Click 'Customize Ribbon.' Tick the checkbox next to 'Developer' and click 'OK.' This enables your form control. Forms can be a vital component in an Excel dashboard.
3. Insert form items by clicking 'Developer,' 'Insert' and selecting a form option. With forms, such as a combo box, you can 'Format Control' and choose an output cell. That cell, in the case of a combo box, will show a number representing your selected option. For a combo box displaying the days of the week, for example, the output cell (hidden) will contain a value between '1' and '7.' This number can then be used by a graph or macro to display data only for that day.
4. Create a linked cell to link to other locations. This is useful if, for example, you have a totaled set of figures on the second worksheet and want to create a graph on page one but also show those totals. Type out 'Total' and 'Subtotal,' for example, on the first page, and then click the cell to the right of 'Total.' Type an equal sign ('='), then click the second worksheet, select the 'Total' cell figure and press 'Enter' to create a link to that cell. You can change the data on that page and it will still update on the first page.
5. Use color formatting to improve the look of your dashboard. This is useful not just for block color but also for conditional formatting. For instance, select a table of data and click 'Home,' 'Conditional Formatting,' 'Color Scales' and then 'More Rules.' You can select a different color scale and edit the figures so that high figures, for example, are displayed as red and low figures as green. Use these basic techniques to start creating your dashboard.

Read more ►