How to Fill an Excel Cell With Two Colors

1. Right-click the Excel cell you wish to format and select 'Format Cells.'
2. Click the 'Fill' tab.
3. Click 'Fill Effects' under the color picker section.
4. Click 'Two colors' at the top-left, and choose the two colors from the right-hand drop-down menus.
5. Select the 'Shading styles,' and view the preview to get the effect you want.
6. Click 'OK' twice to exit both windows.

Read more ►

How to Calculate CPK With Excel

1. Launch Microsoft Excel and type “Data” in A1, “Upper Limit” in B1, “Average” in C1, “StDev” in D1, and “Cpk” in E1.
2. Type “1” in A2, “2” in A3, “3” in A4, “4” in A5, “5” in A6, “6” in A7, “7” in A8, “8” in A9, “9” in A10, and “10” in A11. Type “15” in B2.
3. Add the following formula in C2 to calculate the average for the data:=AVERAGE(A2:A11)
4. Add the following formula in D2 to calculate the standard deviation for the data:=STDEV(A2:A11)
5. Calculate CPK using the upper limit, average, and standard deviation values by adding the following formula in E2:=((B2-C2)/(3*D2))

Read more ►

How to Do Name Badges With Microsoft Excel

1. Launch Excel. A blank spreadsheet will appear.
2. Populate a column with names. If you want to include titles on the badge, populate a second column with this information. Click 'File' and select 'Save.' Give your list a name, and click 'OK.'
3. Launch Microsoft Word.
4. Click 'Tools.' Point to 'Letters and Mailings,' and click on 'Mail Merge Wizard.'
5. Click 'Labels.' Select 'Label options.' Select your name badge size.
6. Click 'Use an existing list' under 'Select recipients.' Click 'Browse.' Select the Excel file with your name badge information. Click 'Open.'
7. Click your Excel list in the 'Select Table' dialog box. Click 'OK.' Click 'File' and select 'Print.'
8. Peel off the printed labels from the label paper and stick them onto the name tag holders.

Read more ►

How to Use VBA Userforms

1. Display VBA by choosing the 'Tools' menu, then 'Macro' and finally, 'Visual Basic Editor.' The VBA window appears.
2. Choose the 'Insert' menu and 'Userform.' A blank userform with handles appears on the right and a 'Toolbox' window appears below that. A userform icon appears in the 'Project' window in the upper left. The 'Properties' window in the lower, left changes to display the userform properties. If these windows are missing, choose them from the 'View' menu.
3. Resize the userform by clicking on a handle, holding down the left mouse button and dragging the handle.
4. Rename the userform to something more relevant by clicking the name in the 'Properties' windows. Type in a new using the form 'frm
,' which follows recommended programming style. Example names include 'frmStartup' or 'frmMain.'
5. Change the caption by clicking on the 'Caption' property and entering a new name. Because this name appear at the top of the userform, enter one that is meaningful to the user such as 'Sign-in' or 'Main.'
6. Adjust additional properties as needed by clicking on their values. You can then set new values by typing them in or selecting them from a dropdown. For example, to change the background color, click the 'BackColor' property, choose the 'Palette' tab and choose a new color.
7.
Add controls from the Toolbox. If this window is not visible, choose the 'View' menu and select 'Toolbox.' Click on the Toolbox control you want to add and drop it into the userform. Note that the information in the 'Properties' window changes to relate to the current control. To define the properties of other controls or the userform, choose a new control in the dropdown at the top of the window.
8. Change a control property by clicking on the property and typing a new value or choosing one from the dropdown. Resize controls by their handles, or click on their centers to move them around the form.
9.
Enter code for the userform itself by clicking the first 'Select Objects' button in the Toolbox and then by double-clicking on any blank part of the form. The code window appears on the right with a blank 'Click' subroutine. Enter code as needed or enter a new subroutine by choosing a name from the 'Subroutine' dropdown in the upper right. Note that you can enter code for different controls by clicking the 'Control' dropdown in the upper left.
10. Return to the userform as needed by double-clicking its icon in the 'Project' window. Don't forget to save frequently as you build the form.
11. Test your creation by clicking the 'Play' button of the 'Standard' toolbar in the top toolbar. If this bar is missing, choose the 'View' menu, then 'Toolbars' and finally, 'Standard.'
12. Close the window and return to the Word document by choosing the 'File' menu and then the 'Close' option.

Read more ►

How to Create a Cumulative Frequency Distribution Chart in Excel 2003

1. Click cell A1 and type 'Class Limits'.
2. Click cell B1 and type 'Frequency'.
3. Click cell C1 and type 'cumulative frequency'.
4. Type the class limits (categories) for your data in column A, starting in cell A2 and continuing down the column. Enter one set of class limits per cell. For example, if your study is about IQ scores you might have class limits of 0-50, 51-100, and 101-150. Type '0-50' in cell A2, '51-100' in cell A3 and '101-150' in cell A4.
5. Type the frequencies in column B, starting in cell B2 and working down the column. A frequency is how often a particular item was found. For example, if you are studying the IQ scores for a group of 20 children, you might have frequencies of 5 children with IQ score between 0 and 50, 7 with IQ scores of 51 to 100 and 8 with IQ scores between 101 to 150. Type '5' in cell B2, '7' in cell B3 and '8' in cell B4.
6. Click cell C2, type an equal (=) sign and type the cell of the first frequency listed in column B. In this example, the first frequency is listed in cell B2, so type '=B2'.
7. Click cell C3, type an equal (=) sign followed by 'C2', a plus ( ) sign and the location of the second frequency in column B. In this example, you would type '=C2 B3'.
8. Click the fill handle at the bottom-right of cell C3. The fill handle looks like a little black square. Drag the fill handle down the column until it is equal with the last filled-in cell row in column A.

Read more ►

How to Use Cells in Countif on Excel 2003

1. Run the Microsoft Excel application and open the worksheet that you wish to edit.
2. Determine the cell addresses of the range that you want to count. For example, to count all instances of an object in the first 50 rows of column A you refer to cells A1:A50.
3. Click a cell that you want to use to perform the COUNTIF calculation and display the observed value.
4. Type the following in the cell:=COUNTIF(A1:A50, 7)Substitute the cell addresses of your range in place of 'A1:A50', and substitute the value that you wish to count in place of '7'. You can search for either a number value or a character string. In order to search for a character string you must surround the value with quotation marks. For example, this formula will count all cells in the range that contain the word 'Trouble':=COUNTIF (A1:A50, 'Trouble')Alternately, you can use a cell reference as your criterion. For example, the following search counts all items that match the value in cell B3:=COUNTIF (A1:A50, B3)
5. Press Enter. Excel will calculate the frequency of your search criterion in the data range and will display the observed value in the formula cell.

Read more ►

How to Balance Your Checkbook Using Microsoft Excel 2010

Create a Register Template
1. Open a new spreadsheet in Excel 2010. Leave cell A1 empty, and label the various columns you will need beginning in cell B1. Common examples of column headers to add to your spreadsheet include date, check number and transaction information. The last three column headers should be debit, credit and balance.
2. Apply any desired formatting to your register. This includes changing font size and adding a colored background to your column headers so they stand out more. You should also adjust the size of the columns to compensate for the information that you will enter into them. Those that will be used for transaction or memo information should be considerably wider to allow you to enter more detailed notes.
3. Enter data into your spreadsheet to test its functionality. Do not use real data, but instead create generic information so you can make sure that the columns you created in Step 2 are adequate for the information that you need to enter.
4. Create a formula in your spreadsheet so Excel can keep track of your account balance automatically. Make note of the first balance cell on your spreadsheet. If Column G is marked as your balance, then your first balance cell would be G2. This cell will remain untouched, as it will be where you enter your beginning balance. To create the formula with a balance column at G, credit column at F and a debit column at E, enter the formula =SUM(G3-E3 F3) into cell G2. This instructs Excel to add any credit and subtract any debits on that line from your total balance.
5. Move your cursor to cell G3 and enter the following formula: =IF(AND((ISBLANK(E3)),(ISBLANK(F3))),'',SUM(G3-E3 F7)). Ensure that cell G3 is still active and press the auto fill button with your mouse. Drag your curser down the balance columns in your spreadsheet until you reach the 100th line. You can now enter debit and credit information and Excel will add or subtract it form your balance automatically on all lines between 2 and 100.
Enter Account Information
6. Reconcile your checkbook by subtracting any transactions that you have completed since your last statement date. If you have access to online banking, you can also use that to help you determine your current balance by comparing those transactions that have cleared to those that are still pending.
7. Enter your beginning balance on the first cell of your spreadsheet. In the above example you would enter your beginning balance into cell G2.
8. Enter all credit and debit transactions that you complete into the appropriate column so your balance is accurate at all times.

Read more ►

How to Define Cell Ranges in Excel 2003

1. Open Excel from the Start menu.
2. Choose your cell range. For example, highlight 'F5' through 'F28.' (Letters represent columns while numbers represent rows.) To highlight cells, just click and drag. To select a group of cells that are not touching, click each cell separately and hold down the 'Ctrl' key.
3. Click on the name box, which is directly above the 'A1' cell.
4. Type the new cell range name. For example, on a schedule, you might name all the time that you are commuting to work 'Commuting.' Hit the 'Enter' key.
5. Use the cell range names in your next formula. These names can make your formulas much easier to use.
6. Visit the Help section of the Microsoft Web site for more information on how to use cell range names in formulas.

Read more ►

How to Use the Freeze Pane Command in Excel to Create Static Headings

1. Open a spreadsheet file.
2. To freeze the top horizontal pane, select the row below where you want the split to appear.
3. To freeze the left vertical pane, select the column to the right of where you want the split to appear.
4. To freeze both the upper and left panes, click the cell below and to the right of where you want the split to appear.
5. Open the Window menu and select Freeze Panes. When you scroll through the worksheet, the area you selected remains on the screen.

Read more ►

How to Format the Values of Vertical Axis Currency in Excel

1. Click and drag to select the cells for the vertical axis. To select the entire row or column, click the cell header.
2. Click the 'Home' tab on the command ribbon.
3. Click the down-arrow for the 'Accounting Number Format' button in the 'Number' group. This button displays a currency symbol. A list of currencies and symbols appears.
4. Click the currency, such as U.S. dollar or the Euro.
5. Click the arrow in the bottom-right corner of the 'Number' group. A window entitled 'Format Cells' opens.
6. Click the 'Number' tab.
7. Click the 'Currency' option. The selected currency appears in the 'Symbol' and 'Negative Numbers' text box.
8. Select the number of decimal places in the 'Decimal places' text box. For example, '2' for U.S. dollars.
9. Click an option in the 'Negative numbers' text box. For example, a negative number can appear in red text or inside parenthesis.
10. Click 'OK.'
11. Type the values for the vertical axis. The values automatically format to the selected currency.

Read more ►

How to Change the Orientation of Text in Excel Cells

1. Start Microsoft Excel and open the file you want to change.
2. Select the cells in which you want to format the text.
3. Open the Format menu and select Cells.
4. In the Format Cells dialog box, select the Alignment tab.
5. In the Orientation box, increase or decrease the number of degrees, or drag the indicator to the angle you want.
6. Select OK to accept the changes.

Read more ►

How to Create a Drop Down Menu in Excel 2007

1. Switch to a different sheet in the workbook that is being used by clicking a tab on the bottom of the window. This helps prevent the list from being confused with a data-entry area.
2. Type a list of items on the sheet using cells that are on top of one another (e.g., A1, A2, A3 and A4). Leave no spaces between the items and type them in the order that they should appear on the drop-down menu.
3. Use the mouse to draw a box around the cells you used for the list.
4. Click inside the text-entry box at the top of the screen, to the left of the formula area. To confirm that you have the correct box, hover the mouse cursor over it. A message saying 'Name Box' will appear. In the box, type a name for the list. Remember the name.
5. Return to the original sheet and click the cell where the drop-down menu should be created.
6. Click the 'Data' tab at the top of the window. In the 'Data Tools' portion of the tab, click 'Data Validation.'
7. Click the drop-down menu next to 'Allow' and select 'List.'
8. Click inside the 'Source' box. Type the equal sign followed by the name of the list. For example, if you named the list 'MyList,' type '=MyList' in the box. Click 'OK.' The drop-down menu will appear in the cell.

Read more ►

How to Use the Excel Chart Wizard

1. Enter your data into an Excel spreadsheet.
2. Start the chart wizard. Click on the 'Insert' button on the toolbar and then select 'Chart.'
3. Select the type of chart you want from the 'Chart type' list in the chart wizard dialog box. Different versions of the chart you have selected will appear in the 'Chart sub-type' window on the right. Select one and click the 'Next' button.
4. Examine the preview of your chart. The 'Data range' tab shows you the range of data from your spreadsheet that is being displayed in your chart. If you want to change the range of data or the labels, click in the 'Data range' box to make the changes without closing the wizard.
5. Plot your chart. Click on the 'Rows' or 'Columns' radio buttons to plot your chart by rows or by columns.
6. Add or remove data from the selected range. Click on the 'Series' tab to make adjustments if there is information you would like to add or remove. Use the 'Series,' 'Name,' 'Values' and 'Category (X) axis labels' to make the changes.
7. Enter chart options. Use this step of the wizard to enter labels for your chart, select the type of gridlines to show and choose whether to hide or display a legend and the data table. Click 'Next.'
8. Choose the location of your chart. Select the 'As new sheet' radio button if you want to place your chart on its own sheet. The 'As object in' radio button will embed it in an existing worksheet which you can select from the drop down menu.
9. Click 'Finish.' If you selected a new sheet for your chart, it will be displayed in a separate window. Otherwise, your chart will be displayed on the worksheet you selected with handles you can use to move it where you want it.

Read more ►

How to Calculate Time Cards in Excel

1.
Go to 'Start,' 'Programs,' 'Microsoft Office' and 'Microsoft Excel.' If you have used Excel recently, go to 'Start' and 'Microsoft Excel.' Click 'New' to open a new document.
2.
Go to 'Timesheets,' click on 'Weekly timesheets with breaks,' then click on 'Download' at the lower right-hand corner of the screen. The time card will have a place for the Company name, time in and time out.
3. Enter '9:00 AM' in the 'Time In' cell (E11) for Sunday. Enter '12:00 PM' in the 'Time Out' cell (E12) for Sunday. The total hours calculated before the break is 3.00.
4.
Enter '1:00 PM' in the second 'Time In' cell (E14) for Sunday. Enter '6:00 PM' in the second 'Time Out' cell (E15) for Sunday. The total hours calculated after the break is 5.00.
5.
Enter '9:00 PM,' '2:00 AM,' '2:30 AM' and '5:00 AM' in the respective 'Time In' and 'Time Out' cells for Tuesday. The time before and after the 'Meal Break' is 5.00 and 2.50 hours, respectively.
6. Change the formula for the day's total hours. Enter '=J12 J15' to add the total hours for the day. Press 'Enter.'
7. Copy and paste the formula in each cell, except the 'Total Hours Scheduled' cell, to remove the error message icon.

Read more ►

How to Make a Fitness Chart

Excel 2010
1. Click the 'File' tab and select 'New.' Type 'Fitness chart' in the search box. View the available fitness charts. Download the chart by clicking the chart image and the 'Download' icon.
2. Replace the default measurements in the spreadsheet with your measurements. Review the available tabs on the bottom of the spreadsheet and click the 'Chart' tab. Depending on the template, the tab may be called 'Measurements' or something similar.
3. Save your changes by clicking the 'Save' icon on the Quick Access Toolbar.
Google Documents
4. Access the Google Documents website. Type 'Fitness Chart' in the search box. View the available fitness charts. Download the chart by clicking the 'Use This Template' icon.
5. Replace the default measurements in the spreadsheet with your actual measurements. Review the available tabs on the bottom of the spreadsheet and click the 'Graphs' tab. Depending on the template, this tab will be called 'Measurements' or something similar.
6. Save your changes by clicking the 'Save' icon on the Quick Access Toolbar.
OpenOffice
7. Access the OpenOffice website. Type 'Fitness' in the search box. View the available fitness spreadsheets. Download the spreadsheet by clicking the 'Use This Template' icon.
8. Replace the default measurements in the spreadsheet with your own measurements. Review the available tabs on the bottom of the spreadsheet and click the 'Graphs' tab. Depending on the template, it may be called 'Measurements' or something similar.
9. Save your changes by clicking the 'Save' icon on the Quick Access Toolbar.

Read more ►