Excel

Browse » Home » All posts

Sunday, December 16, 2012

How to Remove Borders From Cells in Microsoft Excel 2003

1. Open the file that you wish to work on. Scroll to the “File” tab on the command bar and select “Open.” Then browse to the desired Excel file and click “Okay” to open it.
2. Access the cell with the borders that you wish to remove. You can activate the cell by scrolling to the cell of choice and left-clicking on it.
3. Remove the border from the cell. Scroll to the “Format” tab on the command bar and select “Cells.” Under the cells properties box, left-click on the “Border” tab.
4. Set the border presets. There will be box under this tab labeled “Border Presets.” You will need to left-click on the “None” box to remove a border from the cell. Then simply click “Okay” to implement the changes.

How to Create a Spreadsheet Template in Excel 2003

1. Open a new Excel document.
2. Make your spreadsheet. Include all the formatting you would like for your template. Add the fonts, cell colors, macros and anything else you would like.
3. Include only the information that needs to be on every spreadsheet. For example, if you are making a template for a budget, add the headings but not the entries for each transaction.
4. Go to File > Save As. Below the box where you name the file, there is a drop-down menu with file types. Save your file as a '.xlt' Template file. Name the file and click Save.
5. Use the template. Go to File > New. This will bring up a few options, including one for the template you made.

How to Create a Form Using Mircosoft 2007 Excel

1. Launch the Microsoft Excel 2007 program and open a blank spreadsheet. Click the 'Start' button and then 'Excel.' If you do not have Excel 2007, a free trial of Office may be downloaded from the Microsoft site (see Resources section). If a blank spreadsheet does not open automatically, click the 'Microsoft Office' button and then 'New.' Double-click the 'Blank Spreadsheet' icon to open a blank document.
2. Make certain the 'Developer' tab is available in the 'Ribbon.' Click the 'Microsoft Office' button and select 'Excel Options' to launch a separate dialogue window. Click the 'Show Developer tab' check box in the 'Top Options' section of the 'Popular' category. Click the 'OK' button.
3. Add form fields to the Excel 2007 spreadsheet. Go to the 'Developer' tab and locate the 'Controls' section. Click 'Insert' and select the desired form tool from under the 'Form Controls' section, such as 'List Box.' Click in an area of the spreadsheet that you want to add the form tool. The form tool will appear.
4. Adjust the form tool properties. Right-click the form tool and select the 'Format Control' option to launch the 'Properties' dialogue box. Depending on the type of form tool you selected, use the options in the 'Properties' window to change the details. Change the size of a form tool by clicking on it and using the small circles on the border to drag it to a desired size. To change the location of a form tool, click once on the border and drag it to a new area while holding down the mouse button.
5. Repeat steps 3 and 4 for each form tool you want to add to the spreadsheet. Save the form using the 'Save As' option under the 'Microsoft Office' button. Select a folder in which to save the Excel 2007 form using the arrows in the address bar of the 'Save As' dialogue box. Type a name for the form in the 'File name' field and click the 'Save' button to complete the process of creating a form using Microsoft 2007 Excel.

How to Add Data Labels to a Pie Chart

Adding Data Labels to a Pie Chart in Excel 2007 and Excel 2010
1. Start the Microsoft Excel program and open the worksheet containing the pie chart to which you wish to add data labels.
2. Select the chart by clicking on it. A translucent ribbon will appear at the edges of the chart, indicating its selection.
3. Click on the 'Layout' tab in the toolbar above the chart to display the 'Layout' ribbon.
4. Locate the 'Data Labels' button and click on the downward arrow to reveal a drop-down list of options.
5. Select the desired location for the labels. This will add data labels on your pie chart.
Adding Data Labels to a Pie Chart in Versions Prior to Excel 2007
6. Select the pie chart by clicking on it.
7. Click on 'Chart' from the toolbar above and select 'Chart Options.' A dialog box will pop up.
8. Select the 'Data Labels' tab from this box.
9. Select what you wish to depict on the chart from the five options: 'Series name,' 'Category name,' 'Value,' 'Percentage' and 'Bubble size.'
10. Click 'OK.' This will add the desired data labels to your pie chart.

How to Construct a Histogram in Microsoft Excel

1. Click on the 'Data' tab in Excel 2007 or the 'Tools' tab in Excel 2003.
2. Click on 'Data Analysis.'
3. Choose 'Histogram' from the list box, then press 'OK.'
4. Tell Excel where your data is by entering a range in the 'Input range' box. For example, if your data is in column A2 to A11, type 'A2:A11' into the box. If you have entered your own bin values (a range of data for the columns), enter the location of the data in the 'Bin values' box in the same format. For example, if your bins are located in B2 to B5, enter 'B2:B5' in the bin values box.
5. Check the 'Chart Output' box, then press 'OK.' Excel will insert a histogram into the spreadsheet.

How to Get Stock Quotes in Excel

1. Open Microsoft Excel. First, select 'Start' from the main operating system menu. Next, choose 'Programs.' Then, click on 'Microsoft Office' in the programs menu. Finally, select 'Microsoft Excel' from the Microsoft Office menu.
2. Click on the 'Data' menu from the Microsoft Excel main menu screen. Then, choose 'Get External Data' from the data menu. A dialog box will appear with a list of established data sources. Finally, choose the data source labeled 'Investor Stock Quotes.'
3. Select the cell in the spreadsheet for the stock quote information input or choose the 'Create New Worksheet' option to place the stock quote in a new worksheet. After selecting either option, select 'OK' from the dialog box.
4. Type the stock ticker symbol into the next Microsoft Excel dialog box. If the user wants to update the stock quote in the future, choose 'Use this value/reference for future refreshes.' Also check the second check box if you would like the information to refresh on its own.
5. Save the Microsoft Excel file for future use. Select 'Save' from the main file menu, name the file and choose the appropriate place on the computer hard drive to save it.

Saturday, December 15, 2012

How to Do a Pamphlet in Excel

1. Click on the 'File' tab and select 'Print.' Set the layout of the spreadsheet to 'Landscape' and set the margins to '0.25' on all four sides of the page. This will give you the maximum printable area on your pamphlet.
2. Click on the “View” tab and select 'Page Layout' on the left side of the Ribbon. This will change the view of your spreadsheet to one with page borders set. In this view you will also be able to set the width of columns in inches or fractions of an inch which is essential to using Excel as a page layout program.
3. Click on the box at the upper left hand corner of the worksheet area between the two rulers. It has a small triangle pointing down and to the right; this will select all cells in the work sheet.
4. Define your layout grid by clicking on the gray column headers at the top of the worksheet area and set your column widths to a quarter of an inch. Continue the process by clicking on the gray row borders at the left edge of the worksheet area and set your row heights to one fifth of an inch.
5. Switch back to the 'Home' tab. Select cells and use the 'Merge Center' button in the 'Alignment' section of the tab to merge cells to suit your design. For example, if you wanted to center a title on the left side of your brochure you'd select cells A1 through U2 and click 'Merge Center' to make a large box to enter your title.
6. Override the centering of text in a merged group of cells by using the text alignment tools next to the 'Merge Center' button and set the fonts to reflect what you want; larger fonts for titles and smaller fonts for text.

How to Convert the First Letter to an Uppercase in Excel

1. Decide if you want the first letter of every word to be capitalized ('Pete Is Great'), or just the first letter in the cell ('Pete is great'). In this example, assume that the original text ('pete is great' - note no capitals) is in cell A1, and you want the text capitalized in cell B1.
2. Enter the following Excel code into cell B1 if you want every word to be capitalized:=PROPER(A1)This will give the output 'Pete Is Great'.
3. Type the following Excel code into cell B1 if you only want the first letter of the cell capitalized:=UPPER(LEFT(A1,1))LOWER(RIGHT(A1,LEN(A1)-1))This will give the output 'Pete is great'.The 'UPPER(LEFT(A1,1))' part of the function tells Excel to return the left-most character in cell A1 in upper case. The 'LOWER(RIGHT(A1,LEN(A1)-1))' tells Excel to return all but the left-most character as lower case.

How to Build Drop

1. Scroll to the area of the spreadsheet where you want to create the drop-down list's contents. You build a drop-down list menu in Excel from data typed into any set of cells. If you want this to be inconspicuous to the Excel user, do not use the beginning of the spreadsheet. Instead, you can scroll to the right several columns or scroll down many rows to a less obvious part of the spreadsheet.
2. Type the list you want in your drop-down list menu. You can either type the list as a series of vertical cells, all in the same column, or as a horizontal list, all in the same row. For example, if you selected cell P100 as the starting point for your list, you can type each list item into P100, P101 and P102 (or into P100, Q100 and R100) and continue the list for as long as necessary.
3. Click in the cell or cells where you want your drop-down list menu to appear, based on the typed list elsewhere in the spreadsheet. If you want multiple cells to have the menu, drag your mouse over all of them so they are all selected.
4. Click on the 'Data' menu and the 'Data Tools' section. Then, click 'Data Validation' and select 'Data Validation.' In versions of Excel prior to 2007, click the 'Data' menu and select the 'Validation' option. A pop-up box will appear.
5. Click the drop-down menu on the 'Settings' tab of the pop-up window. Select the 'List' option.
6. Click in the 'Source' field.
7. Drag your mouse over the cells in your spreadsheet that contain the list of items that you typed earlier.
8. Type the 'OK' button to complete the drop-down menu setup.

How to Use Excel Charts

1. Launch Microsoft Excel 2010.
2. Open a file with some data already present in the cells or populate a blank sheet with some data that will be used for the chart. Include data labels for the rows and columns by typing descriptive names for the data in the next cell above the first entry in each column and in the next cell to the left of each row of entries.
3. Click in the cell above the first row label and to the left of the first column label and hold the mouse button down. Drag the mouse across the data diagonally until it is pointed at the lower right entry in the last row and last column and release the mouse button. This should highlight all the data that will be included in the chart.
4. Click the 'Insert' tab and click the small arrow at the bottom of any of the chart types listed in the 'Charts' group. Click the 'All Chart Types' button at the bottom of the list that appears.
5. Click on the desired chart type and click 'OK' to create a chart.
6. Click the 'Design,' 'Layout' or 'Format' tabs under the new 'Chart Tools' menu tab that appeared once the chart was created to access additional features and options.

How to Copy Vertically Paste Horizontally in Excel

1. Open the document in Microsoft Excel and highlight the cells you want to copy.
2. Click the 'Home' tab on the Microsoft Office ribbon and click 'Copy.'
3. Click on the cell where you want to paste the data.
4. Click the pull-down menu next to 'Paste' on the 'Home' tab to view a list of paste options, then click 'Transpose.' The copied data is pasted into the highlighted cell and its adjacent cells with the rows and columns reversed.

Friday, December 14, 2012

How to Calculate Descriptive Statistics Using Analysis ToolPak

1. Open Excel 2007 and add the numbers for which you want to calculate descriptive statistics in the first column. For example, add the numbers 210, 110, 50, 50, 70 and 80 in A2, A3, A4, A5, A6 and A7 cells of Excel.
2. Click on the 'A9' cell. This is the cell where you will calculate the descriptive statistics using Analysis ToolPak. Please note, you don't have to select 'A9' cell for the descriptive statistics calculation; any cell under your chosen values can be selected.
3. Click on the 'Data' tab and then 'Data Analysis' found on the top right-hand side of the Excel spreadsheet. A window titled 'Data Analysis' will pop open.
4. Click on the 'Descriptive Statistics.' A window titled 'Descriptive Statistics' will pop open. In the Input Range of window, select and drag from A2 to A7 cells. In the Output Range of Window, select A9 cell.
5. Click on the 'Summary Statistics' of the window. A check mark will appear next to Summary Statistics. Click 'OK.'
6. Thirteen descriptive statistics have been successfully calculated starting from 'A9' cells. In this example, the following will appear as your calculated values.Mean 96.66666667
Standard Error24.17528582
Median 75
Mode 50
Standard Deviation59.21711464
Sample Variance3506.666667
Kurtosis 3.549458572
Skewness 1.854360629
Range 160
Minimum 50
Maximum 210
Sum 580
Count 6

How to Make a Log with Microsoft Excel 2003

How to Make a Log with Microsoft Excel 2003
1. Choose the column headings you wish to use and determine the number of columns you will need. If your log requires the headings 'Date,' 'Time,' 'Comment' and 'Initials,' you will need 4 columns.
2. Open Microsoft Excel 2003 and type your column headers in the first row. For this example, type 'Date' in Cell A1, 'Time' in Cell B1, 'Comment' in Cell C1 and 'Initials' in Cell D1. Highlight those 4 cells and choose the format and font you wish to use for the text.
3. Highlight columns A through D and right click somewhere on the highlighted cells. Select 'Format Cells' from the menu that pops up and activate the 'Borders' tab. Click the 'Outline' and 'Inside' buttons under the 'Presets' subhead. Click 'OK.'
4. Adjust the row height to meet your needs by clicking 'Format' on the menu bar, than 'Row' and then 'Height...' The default height is '12.75' which yields approximately 50 lines per page. This may be too small for some people's handwriting, so adjust this number to meet your needs and click 'OK.' It may take some experimenting to get it just how you want it. You may use this same method to re-adjust later.
5. Change to the page break view by clicking 'View' on the menu bar and then 'Page Break Preview.' Once there, only the first 1 or 2 rows will be active and there will be a blue border around them. Click and drag the bottom blue border downward until a page divider appears and 'Page 2' appears in the highlighted area below the divider. You may have to release the border and drag again to get this to appear. Click and drag the bottom blue border upward until it aligns with the divider.
6. Adjust the column widths to meet your needs by clicking and dragging the dividers between the column headers. For example, you can change Column A's width by clicking the divider between 'A' and 'B' and dragging it in one direction or the other. Adjust the columns as wide as necessary, but do not exceed one page in width for the whole table. You will know if you have exceeded 1 page if a dotted page divider appears with 'Page 2' on the right side of it.
7. Save the file. Print as many copies as you need. When you run out of copies, you can simply open the file and print out more.

How to Calculate Probability Using Excel

1.
Go to Start>Programs>Microsoft Office>Microsoft Excel. If Excel has been used recently, simply go to Start>Microsoft Excel.
2.
Create two columns, one entitled 'Numeric grades' and the other 'Probability of getting each grade.'
3.
List the grades from 50, 60, 70, 80, 90 and 100 in cells A2 to A7.
4.
List the probabilities associated with each grade from cells B2 to B7. List the numbers as follows: 0.05, 0.1, 0.4, 0.3, 0.1 and 0.05.
5.
Enter '=Prob(A2:A7,B2:B7,70,100)'. This formula for probability isolates the numeric range of numbers (A2:A7), the probability of getting each grade (B2:B7), the lower range for which the probability is needed (70) and the upper range for which the probability is needed (100). In short, the formula answers the question: What are the chances of someone getting a grade between 70 and 100?
6.
Click on the '%' icon to convert the answer (0.85) to a percentage. The resulting answer is 85%. There is an 85% chance that the grade will be between 70 and 100.

How to Link an Excel Cell to a Word Document

1. Open the Excel document within Excel and right-click on the cell to be linked to the Word document.
2.
Select 'Hyperlink' from the cell menu and locate the Word document to link it to and then click 'OK.'
3.
Save the Excel spreadsheet and then click on the cell to open up the linked Word document.

Subscribe to: Posts (Atom)