Tuesday, August 28, 2012

How to Convert Numbers to Text in Microsoft Excel


1. Open a new workbook in Microsoft Excel.
2. Type the list of numbers you would like to convert to text down a column.
3. Type =Text(cell,'units') in the next empty column or row, where 'cell' is the cell containing the number you want to convert and 'units' is the unit you want the number to be expressed in. For example, if you have 10 in cell A1 and want to express it in U.S. dollars, type =text(A1,'$0.00') in cell B1.
4. Click on the cell containing the formula and go the lower right-hand corner of the cell until your mouse pointer turns into sign. Drag the formula down the column to the last row containing data.
Read more ►

Monday, August 27, 2012

How to Create a Drop


1. Select the data you want the drop-down menu to contain. This can be any list of information. Highlight those cells and click the 'Data' menu. Select 'Sort' and 'A-Z.' This will alphabetize your list.
2. Place your cursor in the cell where you want your drop-down list located. Click the 'Data' menu and the 'Data Tools' menu. In the 'Data Tools' menu, select 'Data Validation.'
3. Choose 'List' in the 'Allow' menu of the 'Data Validation' dialog box. Click the arrow beside the 'Any Value' option to view the options where 'List' will be found.
4. Click the button at the very end of the 'Source' entry box. This will generate a 'Data Validation' dialog box. Select the worksheet where the data is located for your list. Highlight all of the cells you want included in the drop-down list. Click the button at the end of the 'Source' entry box to select the cell range. If you do not want the list to be left blank, you should clear the check mark in the the 'Ignore Blank' check box in this menu.
5. Select the 'Input Message' tab to set an input message that will appear when the list is selected. This will allow you to provide people with an understanding of what you would like them to select in this menu.
6. Select the 'Error Alert' tab to set an alert to notify users if they have improperly populated the list or failed to select an option.
7. Select 'OK' to populate and publish the drop-down list.
Read more ►

How to Use a Chart Wizard in MS Excel 2007


1. Open the Excel workbook with the data that you wish to chart.
2. Select all the cells containing the relevant data. Press 'CTRL' and 'A' to select them all.
3. Select 'Chart' from the Insert menu. This will open the Chart Wizard.
4. Select a chart type under 'Standard Types.' If you are unsure how a specific chart will represent your data, click it to view a preview. Click 'Next' when you are done.
5. Confirm the data range to include in your chart. The information is already filled in to show the cells you selected in Step 2, but you can switch around the order in which your spreadsheet's rows or columns appear on the final chart. Click 'Next' when you are done.
6. Choose from the wide range of chart options in the next window. They are divided into six tabs: Titles, Axes, Gridlines, Legend, Data Labels and Data Table. If you have the 'Preview' box checked on these pages, you can see what various options will look like. Note that users trying to create basic charts can leave these options at their default settings; they can be changed later if need be. Click 'Next' when you are done.
7. Choose whether to place your chart in the existing workbook or to create a new document. Click 'Finish' when you are done.
Read more ►

How to Add a Header and Footer in Excel


1. Create or open the file you will be working with. A header and footer can be changed in an existing file or added to a new or existing file.
2. Determine if you will use a header, footer or both. Also decide what you want each of these to look like and say. These steps show how to add both a header and footer so choose the steps suitable to what you need.
3. Go to 'File' in the menu bar and select 'Page Setup.' A new box with options will appear. If you do not see 'Page Setup' in your options from the drop-down menu, click the arrow at the bottom of the 'File' list to see more options.
4. Click the tab that says 'Header/Footer,' and then the 'Custom Header' button. Type the words you want displayed into the appropriate box (or boxes) for where on the top of page you want the header to display (left, center or right). Once you have entered the words, click the 'A' to change the font to whatever type, size and color you want these words to be displayed as and click 'OK' on both the font and header boxes. Each word or section of words (left, center or right) can have its own font selection.
5. Include a footer by clicking the 'Custom Footer' button of the 'Header/Footer' tab. Again enter and format the text where you wish it to be displayed. You can automatically add the page numbers, date, time, file path, file name or tab name by using the buttons. Note that a code automatically appears depending on what you choose. You should not edit that information, but you can include text before or after that code.
6. Select the 'OK' button once done formatting the footer. Click the 'Print Preview' button to verify that you header and footer look the way you want. Edit them as needed and click 'OK' to add them to your document. Save the file by selecting 'Save' or 'Save As' from the 'File' menu.
Read more ►

Sunday, August 26, 2012

How to Create a Timeline Using Word


Create a Timeline in Word 2003
1. Open the Word document on which you want to create a timeline. Go to the 'File' menu, click on 'Page Setup' and go to the 'Margins' tab. Select the 'Landscape' orientation option and click 'OK.'
2. Go to the 'View' menu, point to 'Toolbars' and select 'Drawing' if it is not already selected. Click the rectangle auto shape in the drawing toolbar and draw a long, narrow rectangle horizontally onto the Word document, leaving room above and below it for text.
3. Right-click the shape and select 'Format AutoShape.' The 'Format Shape' dialog box will open. Go to the 'Colors and Lines' tab of the dialog box. Select a color from the 'Fill Color' drop-down menu.
4. Click 'AutoShapes' on the 'Drawing' toolbar. Point to 'Lines' and select a straight line. Draw the line coming up from the rectangle in the first position you want at the beginning of the timeline. Repeat, drawing the next line downward from the rectangle. Alternate each mark on the timeline.
5. Go to the 'Insert' menu and select 'Text Box.' Draw a text box above the first line coming out of the rectangle. Type a date or time and other pertinent information in the box. Repeat for the remaining points on the timeline.
6. Go to the 'Insert' menu, point to 'Picture' and select 'From File' or 'Clip Art' to add graphics to points on the timeline. Save and print it as desired.
Create a Timeline in Word 2007 or 2010
7. Open the Word document on which you want to create a timeline. Go to the 'Insert' menu and select 'SmartArt.' The 'SmartArt Gallery' will open.
8. Click 'Process' in the list of 'SmartArt Types.' Hover your mouse over the gallery options or click on an option to see its name and description. Select 'Basic Timeline' and click 'OK.' A timeline graphic will appear along with a corresponding text pane.
9. Click on the first text entry in the text pane. Enter the date or time and other pertinent information in the box. Repeat for the remaining points on the timeline.
10. Add an entry to the timeline by selecting the entry before or after the desired new entry. Right-click that entry, point to 'Add Shape' and select 'Add Shape After' or 'Add Shape Before.'
11. Click on the timeline and go to the 'Design' tab in 'SmartArt Tools.' Click 'Change Colors' to select a different color scheme. Select an option in the 'SmartArt Styles' gallery to change the appearance of the timeline.
12. Go to the 'Insert' tab and select 'Picture' or 'Clip Art' to add graphics to points on the timeline. Save and print it as desired.
Read more ►

How to Print Bar Codes in Microsoft Excel


1. Launch Microsoft Excel from the Microsoft Office folder in your Start menu.
2. Click the 'Tools' drop-down menu. Select the 'Macro' tab, and choose 'Security.' Set it to 'Medium.' If you use Office 2007, click on the Microsoft Office button at the top left corner and select 'Excel Options.' Click on 'Trust Center Settings' under the Trust Center category. Click on 'Macro Settings.' Put a check mark next to 'Enable all macros.'
3. Click the 'File' drop-down menu and select 'New.' Select 'Enable Macros' when you get a prompt. For Excel 2007, click on the Office button and select 'New.'
4. Enter the formula for your specific bar code in Column A, which is the first column. Make sure to use only numbers and/or letters while developing your bar code formula. Also, do not add any spaces between the characters.
5. Increase the width of Column B, the column next to the first column from last step. Click on the line that divides Column B and Column C and drag it to your right until you have enough space for the bar code.
6. Double-click on the formula you developed and type it in Column A from Step 4. Right-click on it and select 'Copy.' Right-click on the next cell in Column B and select 'Paste.'
7. Double-click to select all the formula in Column B. Select the 'Font' drop-down menu. Choose the font that says 'Bar code.' This will transform the formula into a bar code.
8. Select the cell with the bar code. Click the 'File' drop-down menu and select 'Print...' For Excel 2007, click the Office button and select 'Print.' Click 'Print...' again.
9. Choose 'Selection' under the Print What sub-category. Click 'Print' to end the task.
Read more ►

How to Print Mail Labels From Excel


1. Open Excel 2010 and select the 'Mailing' tab on the ribbon. Select 'Start Mail Merge.' Click 'Labels.' Select the label vendor and product number. Click 'OK.' A grid of the label appears in your Word document.
2. Click 'Select Recipients.' Select 'Use Existing List' from the drop-down list. Browse your files and locate the Excel workbook containing the mail labels. Click the workbook and select the worksheet containing the mail label information. Click 'OK.'
3. Format the labels by clicking the 'Insert Mail Merge' field. From the drop-down list, select a field name. It appears in the first label in the document. Add a space or additional formatting as necessary. Press the 'Enter' key to advance to the next line in the label.
4. Click the 'Preview Results' button to see the labels. Click 'Update labels' to apply the new label format to all of the new labels. Select 'Finish and Merge' to print the labels. Select 'Print Records' and 'All' to finalize the print process. Click 'OK' and review your printed mail labels from your printer.
Read more ►

Saturday, August 25, 2012

How to Transform a Spreadsheet into a Web Page in Excel 2003


1. Fill out your Excel 2003 worksheet. You will create the Web page from this version of the worksheet.
2. Format your worksheet. Make sure that your worksheet is formatted so it looks good enough to appear on the Internet. Save the file without exiting.
3. Under 'File' from the toolbar at the top of the Excel window, click 'Web Page Preview' on the dropdown menu. This will preview how the page will appear online. You may have to make formatting changes; if so, preview until you're finished.
4. Once the preview satisfies you, click 'Save as Web Page' under 'File.' This will save your spreadsheet as an 'htm' file so people can view it on the Internet.
Read more ►

How to Evaluate Multiple Columns in Excel 2003


1. Choose a formula or function that you want to use. For example, use the 'Sum' function to add columns or the 'Average' function to calculate the average of all the numbers.
2. Type the function into a blank cell. For example, type 'Sum(' without quotes to start the Sum function.
3. Type the column references, separated by a colon, then close the function with a closing parenthesis. For example, type 'a:b)' without quotes. Your function should look like '=Sum(a:b)'.
4. Press the 'Enter' key or click out of the cell.
Read more ►

How to Make an Invoice Using Excel


1. Navigate to the Excel templates website (see References below for link).
2. Select a template option and examine it for relevance to your needs. For example, click on 'Basic Invoice,' 'Sales Invoice,' or 'Service Invoice.' This brings up a window with a 'Download' button. Click on the button to download the template to your computer.
3. Locate the downloaded file on the computer by using Windows Explorer. To run Explorer, click on the 'Windows' button in Windows 7 or Vista or the 'Start' button in Windows XP, then click on 'Run.' Type 'Explorer' into the text box, then click 'OK.'
4. Double-click on the template file. The template will open up in Excel automatically, and you can then tailor the invoice to fit your needs by changing the business name, amount and other categories. When finished customizing the template, save it to the location of your choice on the computer for future use.
Read more ►

Friday, August 24, 2012

How to Find Standard Deviation on Excel 2007


1. Open Excel 2007 and select the 'Formulas' tab. Click the 'Statistical' button. A drop-down list appears.
2. Click 'Stdev.' The Standard Deviation function arguments window appears.
3. Type the first value in your standard deviation function in the 'Number 1' field. Type the second value in your standard deviation function in the 'Number 2' field. Click 'OK.' The standard deviation function is displayed in the cell.
Read more ►

How to Make a Stem Leaf Plot in Excel 2007


1. Rename a blank Excel worksheet “Data” by clicking the bottom tab for the worksheet and typing the new name.
2. Rename another blank Excel worksheet in the workbook “Stem” by clicking the bottom tab for that worksheet and typing the new name.
3. Enter your list of numbers in column A of the “Data” worksheet.
4. Press “Alt” and “F11” at the same time to open the visual basic editor.
5. Double click “This Workbook” under Microsoft Excel Objects in the left navigation pane to open a blank code window.
6. Paste the following VBA code into the blank window:
Sub StemAndLeaf()
dataColumn = 1'Clean everything out of the Stem worksheet.
Worksheets('Stem').Cells.Clear'Look at the Data worksheet.
Worksheets('Data').Activate'Find the maximum value.
rowPointer = 2
Do Until Cells(rowPointer, 1).Value = ''
rowPointer = rowPointer 1
Loop
Maximum = Cells(rowPointer - 1, dataColumn).Value'Set the divisor to strip off leaves.
divisor = 1
Do Until Maximum / divisor
divisor = divisor * 10
Loop'If the first digit of the largest value is less than 5, then
'use a smaller divisor.
'Otherwise you could end up with four or fewer rows in the plot.
If Fix(Maximum / divisor)
topStem = Fix(Maximum / divisor)'Set up the Stem worksheet.
Worksheets('Stem').Activate
Cells(1, 1).Value = 'Count'
Cells(1, 2).Value = 'Stem'
Cells(1, 3).Value = 'Leaves'
For rowPointer = 2 To topStem 2
Cells(rowPointer, 2).Value = rowPointer - 2
Cells(rowPointer, 3).Value = '|'
Next rowPointer'Calculate the counts.
'The following code is slower than it needs to be,
'but a faster code would be harder to read and understand.
Worksheets('Data').Activate
rowPointer = 2
Do Until Cells(rowPointer, dataColumn).Value = ''
measurement = Cells(rowPointer, dataColumn).Value
Stem = Fix(measurement / divisor)
Worksheets('Stem').Cells(Stem 2, 1).Value = Worksheets('Stem').Cells(Stem 2, 1).Value 1
rowPointer = rowPointer 1
Loop'Calculate the shrink factor.
Worksheets('Stem').Activate
maximumCount = 0
For rowPointer = 2 To topStem 2
If Cells(rowPointer, 1).Value > maximumCount Then
maximumCount = Cells(rowPointer, 1).Value
End If
Next rowPointershrinkFactor = Fix(maximumCount / 50)
If shrinkFactor
Cells(1, 4).Value = 'Each digit represents' Str(shrinkFactor) ' cases.''Return to the data, and fill the leaves in light of the values in the data.
Worksheets('Data').Activate
rowPointer = 2
Do Until Cells(rowPointer, dataColumn).Value = ''
measurement = Cells(rowPointer, dataColumn).Value
Stem = Fix(measurement / divisor)
leaf = measurement - Stem * divisor
leaf = Fix(leaf * 10 / divisor)Worksheets('Stem').Cells(Stem 2, 3).Value = Worksheets('Stem').Cells(Stem 2, 3).Value Trim(Str(leaf))
rowPointer = rowPointer shrinkFactor
Loop'Get to the Stem worksheet.
Worksheets('Stem').Activate
End Sub
7. Press “F5” to run the code. Your Stem and Leaf plot will appear in the “Stem” worksheet.
Read more ►

How to Reduce the Size of a Large Excel 2007 File


1. Press 'Ctrl' and 'End' at the same time. This will bring you to the bottom right cell of the worksheet.
2. Select all the rows between your real last used row, and the row that Ctrl End brought you to. To select a row, click on the row identifier (for example, 'A.'). To select multiple rows, hold down the 'Shift' key and click the first and last row. Press 'Delete.'
3. Select all the columns between your real last used column, and the column that Ctrl End brought you to. Press 'Delete.'
Read more ►

How to Use Scatter Plots in Excel


1. Open the Excel 2010 file containing the data you want to make into a scatter chart.
2. Arrange the data so the first row for each column has a header in the top row. The header is just a word that describes the data. If you need to add a row to accommodate the headers, right-click the number on the left side of the top row and select 'Insert' from the pop-up menu.
3. Arrange the information so that the leftmost column holds the values you want to use on the X-Axis. You can move a column by right-clicking on the letter at the top of the column and choosing 'Cut' from the pop-up menu. Then, right-click on the letter at the top of the first column and choose 'Insert Cut Cells.'
4. Select any cell within your data table. Click 'Insert' at the top of the screen. Find the 'Charts' area of the ribbon and click the 'Scatter' button. Choose one of the five different scatter chart types, which include 'Only Markers,' showing just the intersection of the data fields; 'Smooth Lines,' with a curved line running from one data point to another; and 'Straight Lines,' with a jagged line running between the data points. Smooth- and straight-line scatter charts are available with and without markers. Click the chart type and Excel will create the scatter chart.
5. Click the 'Quick Layout' button on the 'Design' tab to get a list of pre-set layouts for your chart. These layouts will control how the chart appears by adding axis titles, showing a number grid and displaying the marker data as numbers, as well as a few additional options. You can fine-tune the chart's appearance even more by selecting the 'Layout' or 'Format' tab and modifying the options from the ribbon.
Read more ►

How to Generate a Random Number in a Range in Excel


1. Start Microsoft Excel 2007, and open an existing spreadsheet from your files or create a new blank spreadsheet into which you want to generate a random number within a range that you designate.
2. Select the cell or multiple cells that you want to generate the random number into. You can use the SHIFT or CTRL keys on your keyboard to select multiple cells at the same time. The cells that random numbers will be generated into will be outlined by a black heavy line.
3. Type '=RANDBETWEEN' (no quotes) into the 'Formula' textbox near the top of the Excel screen.
4. Continue to type the rest of the function that specifies the range you want the random numbers to fall into. '([Bottom],[Top])' (no quotes) should be inserted after '=RANDBETWEEN' (no quotes) with numbers replacing the word 'Bottom' and 'Top.' The 'Bottom' number should be the lowest random number you want to be generated ant the 'Top' number should be the highest number you want to be generated.
5. Press the 'Enter' key on your keyboard if you selected one cell or the 'CTRL ENTER' if you have selected multiple cells. You will now see random numbers have been generated in the range that you have specified.
Read more ►

Blogger news