Excel

Browse » Home » All posts

Wednesday, August 15, 2012

How to Sort in Excel 2003

1. Open Excel 2003 and select a workbook containing data that needs to be sorted. Click 'File' on the menu bar and select 'Open.' Browse your computer for the workbook. Click the workbook and select 'Open.' The workbook opens.
2. Highlight the range of data that needs to be sorted. Click the first cell in the range you want to sort by. Click the 'Ascending' button on the Standard toolbar. The data is sorted in ascending order by the values in the column.
3. Highlight the range of data you want to sort. Click 'Data' on the menu bar. Select 'Sort.' The 'Sort' dialog box opens. Select a column header in the 'Sort By' field to sort your data by. Select another field in the 'Then By' field to perform a secondary sort. Click 'OK.' Your data is now sorted by multiple criteria.

How to Teach Yourself Microsoft Excel Virtually

1. Browse through Excel tutorials on the official Microsoft Excel website. Choose 'Getting Started With Excel 2010' if you are a total beginning, or 'Excel Skills Builder' to augment the skills you already have.
2. Compare private databases of Excel tutorials. Examples include 'Excel-2010.com,' 'Excel 2010 Tutorials' and 'Lynda.com.' Although the accuracy of non-Microsoft materials isn't guaranteed, private websites often provide other users' personal experiences, which may help you work through difficulties better than straight instruction.
3. Enroll in a formal online Excel 2010 training course via a local community college or university. Enrolling in an online course allows you the freedom to teach yourself Excel but the resource of having a tutor or professor on hand to clear up any ambiguities for you.

How to Control Multiple Users on One Excel Spreadsheet

1. Open the Excel spreadsheet you wish to share among multiple users.
2. Click 'Tools' and select the 'Share Workbook' option. A pop-up window will appear.
3. Place a check mark in the check box at the top of the 'Editing' tab on the Share Workbook pop-up window.
4. Click the 'Advanced' tab at the top of the 'Share Workbook' pop-up window. The window will change to present a variety of customizations for controlling multiple users on the Excel spreadsheet.
5. Choose desired options on the 'Advanced' tab. You may change the history log time frame and control how updates are handled.
6. Click the 'OK' button. Excel will immediately save the workbook and provide a pop-up message to indicate this. The Excel spreadsheet is now set up to handle multiple users.
7. Place the spreadsheet on a shared network drive where multiple computers can access it.
8. Save the spreadsheet on any computer to immediately see updated changes by other users currently working on the same file.

How to Create Mailing Labels in Microsoft Word

1. Open Microsoft Word 2010 and create a new document if one hasn't already been created. You can create a new document by clicking 'File' then 'New.'
2. Click the 'Mailings' tab, then 'Labels.' The 'Envelopes and Labels' window will open.
3. Type the address you wish to use on your labels in the address box. Choose if you want a whole sheet of the same label or just a single label printed in the 'Print' section.
4. Click the 'Options...' button near the middle bottom section of the window to choose the labels to which you want to print. Under 'Label Information' select the brand of labels you are using in the drop-down box. Under 'Product Number' select the product number of the labels you are using and then click 'OK.'
5. Click 'New Document' to create a new page of labels. If you are only printing a single label, the new document option will not be available. To print just a single label, click the 'Print' button in the lower left-hand side of the window.
6. Insert the label sheet in your printer. Print the newly created label document by clicking 'File,' then 'Print.' Choose your printer and click 'Print.'

How to Make the Top Row Scroll in Excel

1. Open up to the worksheet in the Microsoft Excel workbook.
2. Click the 'View' tab.
3. Click the 'Freeze Panes' icon. Select 'Freeze Top Row' from the menu.
4. Click the 'Windows Office' orb logo in the top left corner. Use the save options on the menu, if you want to make the top-row scroll permanent.

How to Figure Percentage Using Excel

1. Click the 'Start' button on the taskbar, point to 'All Programs' and then click 'Microsoft Excel.'
2. Click in an empty cell on the spreadsheet document in which you want the percentage value to be displayed (for example, B12).
3. Type '=(total_value/number_of_values)*100' in cell B12 to get the percentage. Here, replace total_value with the total sum of the numbers whose percentage you want to calculate and number_of_values with the number of individual values. For example, to calculate the percentage of 12, 22, and 32, type '=(66/3)*100'. Here, 12 22 32=66 and there are 3 individual values (12,22,32).
4. Type '=((second_number-first_number)/ABS(first_number))*100' in an empty cell on the spreadsheet were you want to calculate the difference between two numbers as a percentage. Here, replace first_number with the first of the two numbers whose difference you want as percentage and second_number with the second number. For example, to calculate the difference between 32 and 42 as percentage, type '=((42-32)/ABS(32))*100' in the cell.

Tuesday, August 14, 2012

How to Adjust Page Breaks in Excel 2007

1. Open the worksheet you want to print.
2. Click 'View' then 'Page Break Preview.'
3. Click on the page break and drag the break to the place you want the break to be.

Monday, August 13, 2012

How to Set Two Print Areas Using an Excel 2007 Worksheet

Printer Method
1. Select your first desired print range by highlighting the cells within your worksheet; for example A1:H15.
2. Hold down the 'Ctrl' key while highlighting the second set of cells to print--for example N5:V15--resulting in two separate highlighted areas.
3. Select the 'Page Layout' tab, click on 'Print Area' and select 'Set Print Area.'
4. Press 'Ctrl P,' a shortcut that brings up the Print Dialog Box.
5. Verify the output printer displayed is the correct printer and click the 'Properties' button.
6. Look through your different tabs and options for a setting asking you to specify how many pages per sheet you want to print and change it from '1 page per sheet' to '2 pages per sheet.' These options vary by printer based on the make and model and may not be available on your printer.
7. Click the 'OK' button to close the properties box and return to the Print Dialog Box. If your printer does not have this capability, use the camera method.
Camera Method
8. Right-click on the tab of your current sheet ('Sheet1' by default) located at the bottom of the page and select 'Insert' from the pop-up menu. Click on 'Worksheet' and then click 'OK' to insert a new blank sheet into your document. Click the Office button in the upper left-hand corner of Excel and select 'Excel Options.'
9. Choose 'Customize' from the menu on the left and click the drop-down menu under 'Choose commands from' to change the selection from 'Popular Commands' to 'All Commands.' Scroll through the options until you find 'Camera;' click on it and choose 'Add' to add it to the quick-access ribbon at the top of the page. Click 'OK' to save your changes.
10. Return to the sheet where the data you need to print is located. Select the first range of cells to be printed and then click the Camera tool in the quick access ribbon at the top of the page. Switch to your blank worksheet and click to select where you want the upper left-hand corner of your image placed. The new sheet will contain the selected range as a graphic.

How to Insert the Same Drop

1. Enter the list items in a column on the spreadsheet where you will not need to enter other data, such as on the far right or bottom of the page. Enter the items in the order you want them to appear in the list, one item per cell. Make sure there are no blank cells between the items.
2. Select all of the cells into which you want to insert the drop-down list. If the cells are contiguous in one row or column, you can select them by clicking the first cell and dragging your mouse down or across the row or column. If they are in noncontiguous cells, press and hold the 'Ctrl' key while you click on each cell into which you want to insert the drop-down list.
3. Go to the 'Data' menu in Excel 2003 and select 'Validation.' In Excel 2007 or 2010, go to the 'Data' tab, click the 'Data Validation' button. The 'Data Validation' dialog box will open.
4. Make sure you are on the 'Settings' tab. Select 'List' in the 'Allow' box. Click inside the 'Source' box and then select the range of cells containing the list you created in the first step.
5. Make sure 'In-cell Dropdown' is checked. Clear the 'Ignore Blank' check box if you want to require users to enter data into the cell. Click 'OK' to insert the drop-down list into all of the selected cells.

How to Center in a Row on Excel for Microsoft 2003

1. Select the row of text that you want to center by left clicking on the first cell and then dragging the cursor to the right.
2. Right-click and then select 'Format Cells' from the pop-up window.
3. Select the 'Alignment' tab.
4. Click on the arrow next to the 'Horizontal' or 'Vertical' text alignment tab. If you want the text centered both horizontally and vertically, then choose both options.
5. Click on 'Center Across Selection.'
6. Click on 'OK.'

How to Create Free Receipts

1. Open a Microsoft Excel spreadsheet. Select 'New'. Select a free sales receipt template from the list of available spreadsheet templates.
2. Fill in or delete the specified fields for company information, such as company name, address, company logo, and receipt number.
3. Enter in the sales information for each item, which includes quantity, item number, item description, and the unit price.
4. Enter a value in the discount cell if a discount applies and the discount is a flat number. If the discount is in the form or a percentage off, then enter '=xy*z' in the cell where x = the column letter of the item, y = the row number of the item, and z = the percentage off in the form of a decimal (e.g. 20 percent is 0.20).
5. Enter the sales tax in decimal form (e.g. 8 percent is 0.08) into the sales tax cell on the sales receipt.

Saturday, August 11, 2012

How to Convert Numbers Into Letters in MS Excel

1. Open your spreadsheet in Microsoft Excel.
2. Click 'Tools' in the top menu bar, select 'Macro,' and choose the 'Visual Basic Editor' option. If you use Excel 2007 or later, click the 'Developer' tab and click the 'Visual Basic' button instead.
3. Click 'Insert' in the top menu bar of the Visual Basic Editor and select 'Module.'
4. Copy and paste all of the code shown below:Option ExplicitFunction SpellNumber(ByVal MyNumber)Dim Dollars, Cents, TempDim DecimalPlace, CountReDim Place(9) As StringPlace(2) = ' Thousand 'Place(3) = ' Million 'Place(4) = ' Billion 'Place(5) = ' Trillion 'MyNumber = Trim(Str(MyNumber))DecimalPlace = InStr(MyNumber, '.')If DecimalPlace > 0 ThenCents = GetTens(Left(Mid(MyNumber, DecimalPlace 1) _'00', 2))MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))End IfCount = 1Do While MyNumber
''Temp = GetHundreds(Right(MyNumber, 3))If Temp
'' Then Dollars = Temp Place(Count) DollarsIf Len(MyNumber) > 3 ThenMyNumber = Left(MyNumber, Len(MyNumber) - 3)ElseMyNumber = ''End IfCount = Count 1LoopSelect Case DollarsCase ''Dollars = 'No Dollars'Case 'One'Dollars = 'One Dollar'Case ElseDollars = Dollars ' Dollars'End SelectSelect Case CentsCase ''Cents = ' and No Cents'Case 'One'Cents = ' and One Cent'Case ElseCents = ' and ' Cents ' Cents'End SelectSpellNumber = Dollars CentsEnd FunctionFunction GetHundreds(ByVal MyNumber)Dim Result As StringIf Val(MyNumber) = 0 Then Exit FunctionMyNumber = Right('000' MyNumber, 3)If Mid(MyNumber, 1, 1)
'0' ThenResult = GetDigit(Mid(MyNumber, 1, 1)) ' Hundred 'End IfIf Mid(MyNumber, 2, 1)
'0' ThenResult = Result GetTens(Mid(MyNumber, 2))ElseResult = Result GetDigit(Mid(MyNumber, 3))End IfGetHundreds = ResultEnd FunctionFunction GetTens(TensText)Dim Result As StringResult = ''If Val(Left(TensText, 1)) = 1 ThenSelect Case Val(TensText)Case 10: Result = 'Ten'Case 11: Result = 'Eleven'Case 12: Result = 'Twelve'Case 13: Result = 'Thirteen'Case 14: Result = 'Fourteen'Case 15: Result = 'Fifteen'Case 16: Result = 'Sixteen'Case 17: Result = 'Seventeen'Case 18: Result = 'Eighteen'Case 19: Result = 'Nineteen'Case ElseEnd SelectElseSelect Case Val(Left(TensText, 1))Case 2: Result = 'Twenty 'Case 3: Result = 'Thirty 'Case 4: Result = 'Forty 'Case 5: Result = 'Fifty 'Case 6: Result = 'Sixty 'Case 7: Result = 'Seventy 'Case 8: Result = 'Eighty 'Case 9: Result = 'Ninety 'Case ElseEnd SelectResult = Result GetDigit _(Right(TensText, 1))End IfGetTens = ResultEnd FunctionFunction GetDigit(Digit)Select Case Val(Digit)Case 1: GetDigit = 'One'Case 2: GetDigit = 'Two'Case 3: GetDigit = 'Three'Case 4: GetDigit = 'Four'Case 5: GetDigit = 'Five'Case 6: GetDigit = 'Six'Case 7: GetDigit = 'Seven'Case 8: GetDigit = 'Eight'Case 9: GetDigit = 'Nine'Case Else: GetDigit = ''End SelectEnd Function
5. Return to your spreadsheet and single-click the cell containing a number you want converted into text.
6. Type '=SpellNumber(X)' (without quotation marks) into the cell, replacing 'X' with the number originally contained in the cell. You can also refer to the value of another cell, instead of a specific number (e.g., '=SpellNumber(B5)' to use the value in cell B5).
7. Press the 'Enter' key to convert the number value into letters. If you entered '7' in the previous step, for example, the value in the cell now displays as 'Seven.'

How to Make a Frequency Chart

1.
Create a dataset of student grades; include student names and the grades 50, 60, 70, 80, 90 and 100. Enter or sort the grades in ascending order.
2.
Create a bin range of 50, 60, 70, 80, 90 and 100 and enter or sort it in ascending order.
3.
Go to 'Data Analysis.' In older versions of Excel, go to 'Tools,' 'Data Analysis.'
4.
Click on 'Histogram,' then click 'OK.' A frequency chart is also considered a Histogram.
5.
Select the 'Input Range' of the histogram. Click on the box beside the field.
6.
Highlight the 'Input Range' which is the 'Grade' column. Click on the box again to return to the histogram dialog box.
7.
Select the 'Bin Range' of the histogram. Click on the box beside the field and highlight the 'Bin Range' column.
8.
Click on the box beside the field to return to the 'Histogram' dialog box.
9.
Click on the 'Labels' check-box and the 'Output Range' button. The Labels check-box tells Excel that there are labels in the dataset. The Output Range tells Excel where to place that information that will feed the frequency chart or histogram.
10. Select the 'Output Range' using the same steps as the selection of the Input and Bin Ranges. However, select only one cell. In this case, cell E14 was selected. Return to the 'Histogram' dialog box.
11. Click the 'Chart Output' box which will generate the frequency (histogram) chart.
12. Click 'OK' to generate the frequency chart.

How to Turn Off Calculations in Excel 2010

1. Open Microsoft Excel and click 'Formulas' in the Ribbon at the top of the Excel window.
2. Click 'Calculation Options' on the far right side.
3. Select 'Manual' to disable auto-calculation. If you want Excel to recalculate your spreadsheet while auto-calculation is disabled, you will need to click 'Calculate Now,' also located under 'Calculation Options.' When you are ready, you can re-enable auto-calculation.

How to Do a Scattergraph in Excel 2003

1. Click 'Start,' then 'All Programs,' then 'Microsoft Office,' then 'Microsoft Excel 2003.'
2. Click 'File,' then 'Open.' Locate the Excel document that contains the data in it which you wish to make a scatter chart from.
3. Highlight the cells that contain the data.
4. Click 'Insert,' then 'Chart' to launch the Insert Chart wizard.
5. Select 'XY (Scatter)' foam the list of options then click 'Next.' You can 'Press and Hold to View Sample' to see what the graph will look like. Click 'Next' then name the axes and the chart. Click 'Finish' when you have finished creating the graph.

Subscribe to: Posts (Atom)