Excel

Browse » Home » All posts

Sunday, November 11, 2012

How to Change the Name Order From First to Last in Excel

1. Open your list of names in Excel. The list of names should be in column A.
2. Enter the following formula in column B1:=RIGHT(A1,LEN(A1)-FIND(' ',A1))Note that there is a space between the quotation marks in the find function.
3. Enter the following formula in column C1:=LEFT(A1,LEN(A1)-LEN(B1)-1)
4. Enter the following formula in column D1:=CONCATENATE(B1,', ',C1)
5. Copy the contents of cells B1, C1 and D1 down for as many rows as you have names. For example, if your name list goes to row 200, make sure you copy the contents of cells B1, C1 and D1 down through B200, C200 and D200.

How to Do Fractions in Microsoft Word

1. Place the cursor in the document where you would like to insert a fraction.
2. Click the 'Insert' tab above the Ribbon. Notice that the options in the Ribbon change.
3. Click the 'Equation' button in the Ribbon. New options will appear.
4. Select 'Fraction' from the Structures section.
5. Choose the desired fraction type from the resulting drop-down menu. An equation box will appear where your cursor is.
6. Type the fraction's denominator and numerator into the equation box. Click outside of the box when finished.

How to Split a Cell in Excel 2007

1. Open the Excel document that has the cell you need to change.
2. Click the 'Home' tab in the Office ribbon at the top of the screen.
3. Click on the merged cell you want to split. The button labeled 'Merge Center' in the alignment group is highlighted in orange by default, indicating that you've selected a merged cell.
4. Click the 'Merge Center' button to split the cell into separate cells.

How to Unprotect an Excel Spreadsheet

1. Open the Excel workbook with the worksheet you want to unprotect.
2. Right-click on the worksheet's tab at the bottom of the window.
3. Select 'Unprotect sheet.'
4. Enter the password, if applicable, and press 'Enter.'

Sunday, October 28, 2012

How to Insert a PDF File Into Excel 2003

1. Open the Excel file that you want to place the PDF into.
2. Click on the cell you want to insert the PDF on. The upper left corner of the PDF will align against the upper-left corner of the cell you have selected, although you can move it later if you want.
3. Click the 'Insert' menu from the top the Excel window.
4. Select 'Object.'
5. Click on the 'Adobe Acrobat Document' in the 'Object type' section and then click the 'OK' button.
6. Navigate to the PDF that you want to insert.
7. Click the PDF and then click the 'Open' button. Your computer will open the PDF in Adobe Reader and insert it into your spreadsheet.

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.

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.'

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.

Saturday, October 27, 2012

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.

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.

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.

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.

Friday, October 26, 2012

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.”

Thursday, October 25, 2012

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.

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.

Subscribe to: Posts (Atom)