How to Sort by Cell Color in Excel 2003

1. Press 'Alt and 'F11' together from the Excel worksheet you want to sort. This opens the Visual Basic Editor (VBE).
2. Click 'Insert' and then click 'Module.'
3. Cut and paste the following code into the blank window:Sub SortByColor()On Error GoTo SortByColor_ErrDim sRangeAddress As StringDim sStartCell As StringDim sEndCell As StringDim rngSort As RangeDim rng As RangeApplication.ScreenUpdating = FalsesStartCell = InputBox('Enter the cell address of the ' _'top cell in the range to be sorted by color' _Chr(13) 'i.e. 'A1'', 'Enter Cell Address')If sStartCell > '' ThensEndCell = Range(sStartCell).End(xlDown).AddressRange(sStartCell).EntireColumn.InsertSet rngSort = Range(sStartCell, sEndCell)For Each rng In rngSortrng.Value = rng.Offset(0, 1).Interior.ColorIndexNextRange(sStartCell).Sort Key1:=Range(sStartCell), _Order1:=xlAscending, Header:=xlNo, _Orientation:=xlTopToBottomRange(sStartCell).EntireColumn.DeleteEnd IfSortByColor_Exit:Application.ScreenUpdating = TrueSet rngSort = NothingExit SubSortByColor_Err:MsgBox Err.Number ': ' Err.Description, _vbOKOnly, 'SortByColor'Resume SortByColor_ExitEnd Sub
4. Press 'F5' to run the macro. The macro will ask you the beginning of the range you want to sort by color: enter the top-left cell in the range.

Read more ►

How to Insert Hyperlinks into Excel 2007 Without Changing Text Format

1. Open the saved spreadsheet.
2. Click to highlight the text where you wish to insert the hyperlink.
3. Click the Insert tab on the ribbon.
4. Click on the 'Hyperlink' button in the Links group. A dialog box entitled Insert Hyperlink will appear.
5. Click a file from the Look In list. If the hyperlink leads to a website, click the Address box and enter the URL.
6. Click the 'OK' button. The colored hyperlink appears on the selected text.
7. Change the text that contains the hyperlink, if you wish. For example, to change the hyperlink color to match the original font color of the text, click the Home tab's font commands.

Read more ►

How to Find Row Number of Active Cell For VBA

1. Press 'Alt' and 'F11' together from inside the worksheet where you want to find the active cell's row number. This action opens the Visual Basic Editor (VBE).
2. Click 'Insert' and then click 'Module' to open a blank module window.
3. Cut and paste the following code into your worksheet:Sub RowNum()MsgBox 'The active cell row is ' ActiveCell.RowEnd Sub
4. Press 'F5' to run the routine. The macro will return the active cell's row in a message box.

Read more ►

How to Make a Graph in MS Excel

Instructions
1. Input the graph data. For the purposes of this example, type 'Month' in cell A1, and type the months January through December in cells A2 through A13. In cell B1, type 'Applications.' For each month, increase the 'Applications' number by 200. In cell B2, type '200,' in cell B3 type '400' and so on. The last figure in the 'Applications' column -- '2,400' -- is in cell B13.
2. Place the cursor on a cell within the data table. If the cursor is not on an item in the table, the chart will not know where to access the data. Go to the 'Insert' tab. Click on the down arrow under the 'Column' option in the 'Charts' panel.
3. Select the first chart option under the '3-D Column' heading. A graph titled 'Applications' will appear in your work area, showing 12 columns that represent the 12 numbers you entered in Step 1.
4. Click on the border of the chart to drag it to the desired location. Change the size of the graph by grabbing one of its corners and dragging it.

Read more ►

How to Create Quality Control Charts in Excel

1. Distribute your data to revolve around a mean (average). Ensure your measurements are independent of each other. Create a subgroup for each data point and measurement number.
2. Add all your measurements in the subgroup and divide by your number of measurements. Calculate the mean of all the means; this will determine your over all mean. Determine the standard deviation of your data points by using this command: 'Standard deviation: =STDEV(data points).'
3. Tally the upper and lower control limits (UCL, LCL). Enter this formula: 'UCL = CL 3*S', 'LCL = CL -- 3*S.' This conveys 3 standard deviations above and below your mean. This will process your 1, 2 and 3 sigma lines. Draw a line at each deviation you've calculated. Diagram the subgroup means 'x-axis' counter to the 'y-axis.' Confirm that your points do not fall off any of your sigma lines. This can help you determine whether you data is 'in-control' or 'out-of-control.'

Read more ►

How to Convert XML to an Excel 2003 Spreadsheet

Converting the XML File Directly Using Excel 2003
1. Click on the Microsoft Excel 2003 icon on your computer's desktop to open the application. Or click 'Start,' 'All Programs,' 'Microsoft Excel 2003,' if you are using Windows. On a Mac, click on the Excel icon in the Applications folder of your Mac's hard drive.
2. Click 'File,' then 'Open.'
3. Click on the down arrow next to the box labeled 'Files of Type.' Select 'XML files (*.xml).'
4. Click on the down arrow next to the box labeled 'Look In.' Navigate to the directory containing the XML file you would like to convert to a spreadsheet.
5. Double-click on the XML file's name to open it. The 'Open XML' dialog box will open.
6. Click on 'As an XML list' to open the XML file to view the raw data in a structured list format. Click 'OK.' This option lets you edit the XML data.
7. Click on 'As a read-only workbook' to open the XML file to view the data in an uneditable file. Click 'OK.' The XML data will be displayed in a grid instead of a list.
Converting XML File Via The Excel 'Data' Menu
8. Open Microsoft Excel 2003.
9. Click 'Data' and select 'XML.'
10. Click on 'XML Source.' The 'XML Source' task pane will open.
11. Click on 'XML Maps.' Click on 'Add.' Use the 'Look in list' function to navigate to the XML file you want to open.
12. Double-click on the XML file to open it. Click 'OK' to close the open dialog box confirming that you want to open the XML file.
13. Click 'OK' to display the XML file in the XML Source task pane.
14. Click and drag the items you want to view from the Source task pane to the blank cells of the open Excel 2003 worksheet.
15. Click on the 'A1' cell of the spreadsheet to highlight it.
16. Click on 'Data,' 'XML,' 'Import.' The 'Import XML' dialog box will open.
17. Click on 'Look in list' to locate the XML file you used in Step 5.
18. Click on the file's name. Click 'Import.' The XML file will open in Excel 2003.

Read more ►

How to Make a Large Organizational Chart on Microsoft

1. Open the Excel, Outlook, PowerPoint or Word program to display a new document.
2. Click the “Insert” tab on the command ribbon.
3. Click the “SmartArt” button in the “Illustrations” group. A gallery of thumbnail images appears.
4. Click the “Hierarchy” option in the left pane to display “Hierarchy” charts in the middle pane.
5. Click a thumbnail image to preview an enlarged view and chart description.
6. Click “OK.” The chart template copies to the new document. The “SmartArt Tools” ribbon displays two tabs: “Design” and “Format.”
7. Click the “Design” tab on the “SmartArt Tools” ribbon.
8. Click the “Text Pane” button in the “Create Graphic” group. The “Type your text here” pane appears.
9. Type the text such as a name and title, in the the text pane. The text copies to the organization chart.
10. Insert more chart shapes by clicking the “Add Shape” button in the “Create Graphic” group on the “Design” tab.
11. Update the layout by clicking the “More” arrow beside the “Layouts” group. A gallery of layout thumbnails appears. Click the preferred layout. For example, the “Picture Organization Chart” can insert image files.
12. Apply formatting options by clicking the “Format” tab in the “SmartArt Tools” ribbon. Experiment with the “Shape Styles” or “WordArt Styles” to customize the shapes in your chart. For example, select a color border for colleagues working on a specific project.
13. Save this document. Select a file location you can easily access.

Read more ►

How to Create a Family Budget Using Microsoft Excel

1. Open Microsoft Excel.
2. Click cell 'A1,' then type 'Item.'
3. Click cell 'A2,' then type the first item in your budget. For example, 'Grocery.'
4. Click cell 'A3,' then type the second item in your budget. For example, 'Rent.' Continue entering your budget items in column A, moving down the column.
5. Click cell 'B1' and then type 'Amount.'
6. Click cell 'B2,' then type the amount that corresponds with the item in cell A2. In this example, cell A2 contained the item 'Grocery.' If you spend $600 per month on groceries, type 600.
7. Click cell 'B3,' then enter the amount that corresponds with the item in A2. Continue entering amounts for items, moving down column B, until you have entered an amount for each item in column A.
8. Highlight column B; click the 'B' at the top of the column.
9. Click the 'Home' tab and then click the '$' symbol in the Number group. This tells Excel that all the numbers you entered are in dollars.
10. Click on the first blank cell in column B, then click the 'Σ' button on the Home tab. This sums up the items in your budget column and gives you a total.

Read more ►

How to Add Dates in Microsoft Excel

1. Right-click on the cell you want to format.
2. Choose 'Format Cells' from the list.
3. Click on the 'Number' tab.
4. Choose 'Date.'
5. Click on the date option from the list box. For example, click on '5-Jul' to format all dates in that particular cell that way.
6. Press 'OK.'

Read more ►

How to Make a Rubric in Excel

1. List the achievement levels across the first row, one level per cell. Reserve column A for the category titles and begin the achievement headings in column B. The achievement levels may be academic grades, place rankings or any other scoring system.
2. Click on the row header to highlight the entire row. Then select the 'Home' tab from the ribbon and click the 'Bold' button in the 'Font' group. Making the text bold allows the user to more easily distinguish the headings from the criteria.
3. Type the category headings down the first column, one category per cell. For instance, a rubric for a research paper may include categories for punctuality, research quality, mechanics and formatting. Double click on the boundary between column A and B to adapt the column width to the length of the longest word.
4. Click inside the cell at the intersection of the first category row and the first achievement level column. Type a short description of the criteria necessary to achieve this level in this category.
5. Repeat this process until there is a description for every category at every achievement level.
6. Click inside the first criteria cell and drag the handle on the black selection box that appears to enlarge it until it encompasses all the cells.
7. Click on the 'Home' tab in the ribbon, then click the 'Wrap Text' button in the 'Alignment' group. This causes your text to automatically wrap to fit the width of the column.
8. Click and drag the boundary between two column or row headers to widen the cells.
9. Apply a border to each column and row, if desired. This helps clearly separate the criteria. To do this, click the 'Home' tab in the ribbon, then click the 'Borders' button in the 'Font' group and select a border type.

Read more ►

How to Turn Off Auto Save

Instructions
1. Turn off AutoSave in Visio. Go to the 'Tools' menu and select 'Options.' When the new window comes up, click on the 'Save/Open' tab. Uncheck the box next to 'Save AutoRecover information.' Click 'OK' at the bottom of the window.
2. Turn off AutoSave in Word, Excel or PowerPoint. Click on the button at the top left of the program's window that looks like the Windows logo. Look for a button that says 'Word Options,' 'Excel Options' or 'PowerPoint Options' at the bottom right of the window that appears. Click on 'Save' in the list at the left. Uncheck the box next to 'Save AutoRecover information.' Click 'OK' at the bottom of the window.
3. Turn off AutoSave in Publisher. Go to the 'Tools' menu and select 'Options.' When the new window comes up, click on the tab that says 'Save.' Uncheck the box next to 'Save AutoRecover information.' Click 'OK' at the bottom of the window.

Read more ►

How to Create a Scatter Plot With a Microsoft Word Spreadsheet

1. Open the Word document.
2. Click on the 'Insert' tab on the command Ribbon.
3. Click on the 'Chart' button in the 'Illustrations' group. A dialog box will display the different charts.
4. Click on an 'XY (Scatter)' chart button. Click on the 'OK' button. An Excel worksheet and a chart template will appear.
5. Enter the values on the worksheet. Click the 'Enter' key. The data will convert on the scatter chart.
6. Format the scatter chart for a custom look. For example, the 'Design' tab includes chart styles. The 'Format' tab includes colored outlines.
7. Save this document.

Read more ►

How to Create a Form Using Microsoft Excel

1. Insert form. Open a new workbook in Microsoft Excel. Press the 'Alt' and 'F11' keys on your keyboard to open Microsoft Visual Basic. Double click on 'Sheet 1' (Sheet 1) under Microsoft Excel Objects in the left window pane under VBAProject. Go to the Insert menu and select 'UserForm' to insert a form.
2. Name form. Go to the Properties window in the left window pane and click on 'UserForm1' next to (Name). Type a new name for your form. Hit 'Enter.'
3. Add text box. Click on the 'Textbox' button in the Toolbox toolbar. Click on a place in the form where you would like to add a standard-sized text box. You can adjust the size of the box using the handles. Go to the Properties window in the left window pane and click on 'TextBox1' next to (Name). Type a new name for your text box. Hit 'Enter.' Repeat this step for additional text boxes you would like to add.
4. Add label. Click on the 'Label' button in the Toolbox toolbar. Click on a place in the form where you would like to add a standard-sized text label. Adjust the size of the box using the handles. Go to the Properties window in the left window pane and click on 'Label1' next to (Name). Type a new name for your label. Hit 'Enter.' Repeat this step for additional labels.
5. Add buttons. Click on the 'Command' button in the Toolbox toolbar. Click on a place in the form where you would like to add a standard-sized button. Go to the Properties window in the left window pane and click on 'CommandButton1' next to (Name). Type a new name for your button. Hit 'Enter.' Change the caption to the text you would like to appear on the button such as 'Login.' Repeat this step for additional buttons you would like to add.
6. Add code. Select the button, go to the View menu and select 'Code.' Enter code functionality for the button. Go to the View menu and select 'Object' to go back to the user form. See the link in Resources below for examples of button codes.
7. Test form. Go to the Run menu and select 'Run Sub/UserForm' to run the form.

Read more ►

How to Create a Pivot Table From External Data in an Excel File

1. Save your external data into a file. The most common type of file is an external database.
2. Pull up a blank spreadsheet within Excel 2003. Click 'Data' in the drop-down menu and scroll down to 'PivotTable.'
3. Click 'External Data Source' and select 'PivotChart Report with PivotTable Report.' Once you make these selections, another pop-up menu will appear.
4. Select 'Get Data,' which will bring up another window. Select the file or database you will be choosing from. These include Access, Excel, dBase, and ODBC.
5. Click OK and close. Use default value for all other prompts and click Finish. The pivot table will automatically appear.

Read more ►

How to Copy Formulas Down in Excel Lightning Fast

1. Open the Excel 2010 spreadsheet where you want to add your formula.
2. Select the first cell where you want the formula to appear and type in your formula. Press 'Enter' when you are done.
3. Move the mouse to the cell where you just entered your formula. Move the pointer over the bottom-right corner of that cell and it will change into a ' ' sign.
4. Click and hold the mouse button. Drag the mouse down the spreadsheet until you reach the last cell where you want the formula to appear. Release the mouse button, and the formula will instantly copy itself down into every cell.

Read more ►