How to Use the Range.sort Method in VBA

1. Open the Excel 2010 spreadsheet that you want to sort using VBA. Press 'Alt' and 'F11' simultaneously to bring up the VBA editor.
2. Double-click on the module, which is located on the left side of the editor, where you are writing your VBA code. Your code will appear on the right side of the screen. Place your cursor into an empty line of code where you want to use the Sort method.
3. Enter the following code into the VBA editor, without quotes: 'Range('A:B').Sort key1:=Range('A1'), Order1:=xlAscending'. Change 'A:B' to the Excel range that you want to sort and change 'A1' to the first cell in the column you want to use to sort by. Change 'xlAscending' to 'xlDescending' if you want to sort from high to low, instead of low to high.
4. Place a comma and a space at the end of your code if you want to add more parameters to the sort method. Enter the name of the parameter, followed by ':=' and then the value for the parameter. Place another comma and a space after each parameter, except for the last one.
5. Use the 'Key 2,' 'Order 2,' 'Key 3' and 'Order 3' parameters to set up additional sorting rules for when the first key find identical values. Use the 'Type' parameter to determine whether to sort the range by labels or values, and use 'xlSortLabels' and 'xlSortValues,' respectively. Use the 'Header' parameter, set as either 'xlYes,' 'xlNo' or 'xlGuess' to let the sort function know if your data has headers or not.Use the OrderCustom parameter, using an integer as the value, to use a preset custom sort order that you created. Use the 'MatchCase' parameter, setting it to either 'True' or 'False' to determine whether to make the sort case sensitive. Use the 'Orientation' parameter, set as either 'xlSortRows' or 'xlSortColumns' to determine how your data table is orientated. Use the 'SortMethod' parameter, set as 'xlStroke' or 'xlPinYin' if you are sorting Chinese characters. Finally, use the 'DataOption1,' 'DataOption2,' or 'DataOption3' parameters, set as 'xlSortTextAsNumbers' or 'xlSortNormal' to determine whether the respective key will have its text be treated as numbers for the sort.

Read more ►

How to Reverse the Order of a Chart in Excel 2003

1. Click the axis for the categories, series or values that you want to change the order of.
2. Click the 'Format' tab, click 'Selected Axis' and then click the 'Scale' tab.
3. Check 'Categories in Reverse Order,' 'Values in Reverse Order' or 'Series in Reverse Order.'

Read more ►

How to Convert Quattro Pro Files to Word Documents

Converting Quattro Pro with Microsoft Excel
1. Go to “Start->Settings->Control Panel” (or “Start->Control Panel” in Windows XP).
2. Choose “Add/Remove Programs,” highlight “Microsoft Office” or “Microsoft Excel,” and click “Add/Remove.”
3. Click on “Add/Remove Features,” locate and run “Quattro Pro Converter 5.0” to install the converter, and click “Update Now” to complete.
4. Open Microsoft Excel. Go to “File->Open” and select the Quattro Pro file you wish to convert.
5. Go to “File->Save As,” select “Microsoft Excel (*.xls)” under “Save as type” and click “Save.”
Converting Quattro Pro with Media Convert
6. Open the Media Convert website.
7. Browse for and select the Quattro Pro (WB2) file you wish to convert.
8. Select “Microsoft Excel (.xls)” under “Output Format” and click “OK” to convert the file.
Pasting Excel into Microsoft Word
9. Open a new Microsoft Word document. Copy and paste the contents of your converted Excel file.
10. Click the “Paste Options” icon next to the data. Choose “Match Destination Table Style” or “Keep Source Formatting” to keep the data as tables, “Keep Text Only” to reformat the data into paragraphs of text or “Paste as Picture” (Microsoft Word 2007 only).
11. Save the document in Microsoft Word (DOC) format.
Converting Excel to Word
12. Download and install Total Excel Converter. Run the program.
13. Locate the file you wish to convert and highlight it.
14. Click “DOC” under “Convert to” to save a copy of the file in DOC format.

Read more ►

How to Put Small Caps in MS Excel Vista

1. Open Microsoft Excel. Choose 'View' from the menu bar and click 'Toolbars' from the drop-down menu. Select 'Formatting' to view the formatting toolbar. There should be a check mark on its left side to indicate that the toolbar is on, and it should appear below the standard toolbar, which you will recognize by 'Print' and 'Save' icons among many others.
2. Activate the cell or cells that you would like to apply the small caps effect. Write all of the text that you would like to have in small caps in uppercase, or capitalized, letters.
3. Highlight the letters that you would like to apply the small caps effect to (the letters following the first capitalized letter in each word or words). Decrease the font size for those letters by accessing the font formatting tools from the formatting toolbar. Repeat to apply this effect to all of the text that you would like to see appear as small caps.

Read more ►

How to Use Variables in Excel

1. Select the cell with the result that you want to declare as a variable.
2. Click on the Formulas menu item, and choose Names (Excel 2003 and earlier) or Name Manager (Excel 2007 and Excel 2010).
3. Enter the name of the variable in the dialog that comes up. From this point on, in this workbook, you'll be able to refer to the contents of this cell by entering its name.

Read more ►

How to Flow a List Across Multiple Columns

1. Choose the two columns you want to merge. You can also merge several columns on the spreadsheet. Once selected, click the first column and drag the mouse across the others. This will highlight multiple columns at once.
2. Click the 'Merge and Center' button in Microsoft Excel 2007. In Excel 2003, click the 'Format' menu item and select 'Merge and Center' from the list of options.
3. Select 'Merge Across' from the menu list in Excel 2007. In Excel 2003, select 'Merge.' You will notice that the cell lines across the columns disappear. This indicates that the columns have been merged.
4. Choose the alignment of the text in the columns. Click the 'Center' button to center the list within the selected merged columns. You can also left align or right align the text in the merge cells.
5. Click the 'Ctrl' and the 'S' button on the keyboard. This saves the Excel spreadsheet in both 2003 and 2007 versions.

Read more ►

How to Create Excel 2007 Custom Properties

1. Start Excel by clicking on the icon labeled Microsoft Office Excel 2007. It may be located on your start menu, quick launch bar or desktop.
2. Click on the menu item labeled 'File' with your left mouse button, then select the line labeled 'Properties' to bring up the file properties screen. Select the Custom tab to display the Custom panel. Enter a name for the custom property or select one from the drop-down box labeled 'Name.'
3. Look at the 2 buttons on the right. They are grayed out, which means that they are not functional at this point because you have not entered a value for the property. These buttons will be labeled 'Add' or 'Delete' if the selected property does not have a value. Otherwise, they will be labeled 'Modify' and 'Delete.'
4. Select a data type from the drop-down list labeled 'Type.' Your data may be text, a date, number or logical (yes or no). Enter a value in the box labeled 'Value.' Your data must match the data type you selected. For example, if you selected a Type of Number, then Value must be numeric.
5. Notice that the buttons labeled Add and Delete or Modify and Delete are now active. You may now Add, Modify or Delete the specified value. For a new value, you would click the 'Add' button.

Read more ►

How to Add Borders to Cells in Microsoft Excel 2003

1. Select the cell that you want to add borders to. You will first need to select and activate the cell that you wish to add a border to. Do this by left-clicking on the desired cell.
2. Select the border type. Adding a border is very easy to do. Once you have selected the cell that you wish to add a border to, right-click on it and select “Format Cell.”
3. Add the border. A cell formatting properties box will open. Left-click on the “Border” tab. There are several different types of borders that you can add, which are displayed as icons in the “Borders” tab. You can outline the cell, create side borders or create top and bottom borders. Select the desired border by left-clicking the corresponding border icon.
4. Save your spreadsheet file. Once you have made the desired changes, make sure to save your updated spreadsheet. You can do this by clicking on the “File” tab on the command bar and selecting “Save.”

Read more ►

How to Remove a Password Protected Cell

1. Open Microsoft Excel. Open the Excel spreadsheet file from within Excel.
2. Find the security options in Excel. It will most likely be in the 'Tool' menu under 'Options.' Look under the 'General' tab and the 'Security' tab if there is one. Look for password settings.
3. In the password protect field select the asterisks and then the delete button. There may be a second password field for modifications, if so delete that password as well. You may be required to enter the password before you can delete the old password.
4. Exit out of the menu choices. Select the cell that was password protected and then delete the cell.

Read more ►

How to Create a Balance Sheet in Excel

Find Template Through Excel
1. Open Excel and click 'New' from the 'File' tab at the top of the screen, then scroll through the 'Templates' list at the left of the screen to view templates that came pre-loaded with your program and available on the Office website.
2. Click 'I Accept' on the right hand side of the page to accept the terms and conditions, if necessary. Browse under the 'Budget,' 'Expense Reports' and 'forms' categories to locate different kinds of balance sheet templates.
3. Click 'Download' to download and open a desired template in Excel.
4. Fill out your balance sheet.
Find Template Online
5. Visit the Microsoft Office Templates site, and enter 'balance sheet' in the text box.
6. Choose 'Excel' from the product drop-down and then click 'Search.'
7. Browse templates until you find one that fits your requirements for a balance sheet, and click 'Download.'
8. Validate your version of Excel, if prompted to do so, then indicate where to save the template (your desktop, hard drive or a memory device), then click 'Save.'
9. Open Excel, click 'Open' under the Office button, navigate to where you saved the template, and click 'Open.' Fill out your balance sheet.

Read more ►

How to Delete a Page in MS Excel 2007

1. Click the 'Worksheet' tab at the bottom of the Excel window that you want to delete. For example, click on 'Sheet 2.'
2. Click on the 'Home' tab in the 'Office' ribbon.
3. Click on the down arrow next to the 'Delete' icon in the 'Cells' group.
4. Scroll down in the drop-down menu and click on 'Delete Sheet.'

Read more ►

How to Create Graphs Embed in Spreadsheets

1. Open the saved Excel worksheet.
2. Click and drag to select the range of data for the chart. For example, select the categories and the values to appear on the chart.
3. Click the 'Insert' tab on the command ribbon.
4. Click the preferred 'Chart' button on the 'Charts' group. Click the window launcher arrow in the group's bottom right corner to open a more extensive chart list. A chart appears over the worksheet. The 'Chart Tools' ribbon appears with additional format options.
5. Click the preferred formats on the various ribbon tabs, such as 'Design,' 'Layout' or 'Format.' Create a custom look by changing the color and font for the plot area and the chart area, for example.
6. Save this Excel file.

Read more ►

How to Prevent Julian Dates in Excel 2003

1. Click on the cell with the Julian date.
2. Type '=RIGHT(YEAR(A1' into the 'Find what?' text box.
3. Search for the Excel formula that converts standard dates to Julian dates. The full formula is '=RIGHT(YEAR(A1),2)TEXT(A1-DATE(YEAR(A1),1,0),'000')' where 'A1' is the cell location of the Julian date.
4. Click on the cell with the formula, then press the 'Delete' key to remove the formula.

Read more ►

How to Change the Orientation of a Worksheet to Landscape in Microsoft Excel

1. Connect your computer to a printer if you have not set up a printer yet for your computer. You do not need an active printer connection to change the page orientation, but you do need a printer setting entered into the computer. Otherwise, Excel will gray out the page orientation option because it only applies to printing. Connecting a printer via USB will automatically launch the setup wizard. Follow the onscreen instructions to install the printer driver until the setup is complete.
2. Launch Excel and open your workbook to the worksheet you want in landscape mode.
3. Close out of any cells you are editing by pressing 'Enter' or clicking in another cell to save your changes, or press 'Esc' to exit the cell without change.
4. Click the 'Page layout' tab in the Office Ribbon, and then choose 'Orientation' from the 'Page setup' area.
5. Select 'Landscape' to change your page orientation. This will close the menu automatically and make the change.

Read more ►

How to Calculate Percentages in Excel 2007

1. Open Excel, and in cell A1 type the numerator. In cell B1 type the denominator. For example, for 75 percent, in A1 type '3' and in B1 type '4.'
2. Type '=a1/b1' in cell C1. In the example, C1 will result in 0.75.
3. Right-click cell C1, then click 'Format Cells.' Under the 'Number' tab, click on 'Percentage.' Choose how many decimal places you want to take the percentage to. Hit OK. The number is now formatted as a percentage.

Read more ►