<<Convert Excel Spreadsheets to Web Pages \| Trading Software That Operates Within Excel \| Convert Excel, Access & Other Databases \| Merge Excel Files>>

Development

Quick & Dirty Tips For Microsoft Excel

| | Information Helpful? Why Not Donate.

TRY OUT: Smart-VBA | Code-VBA | Analyzer-XL | Downloader-XL | Trader-XL| More Free Downloads.. Best Value: Finance Templates Bundle

Current Special! Complete ExcelExcel Training Course for Excel 97 - Excel 2003, only ~~$145.00~~. $59.95 InstantBuy/Download, 30 Day Money Back Guarantee & Free Excel Help for LIFE!

Got any Excel Questions? Excel Help

Formula Quick Tips

Rather than typing those mega long formulas in the result file, that reference outside Workbooks, type it in the "outside Workbook" then Cut and paste to the "result file"

Copy formulas from one Workbook to another. Open both Workbooks and select the formula range. Now, CUT, not copy, the formulas and paste into the other. IMPORTANT: close the formula Workbook you CUT from WITHOUT SAVING.

Toggle throughRelative and Absolute Range reference. Select the cell and then, in the formula bar, click the range reference and push F4 until the reference type you need is seen.

Create Custom Lists

The most flexible way to create a custom list is to enter the list contents into a range of cells. For example, say you have a list of 10 employee names. Enter each name, starting with cell A1 and ending with cell A10, and sort the list, if needed. Now go to Tools>Options>Custom Lists (*Excel 2007) and click the collapse tool to the left of the Import button. Using the mouse pointer, left-click in cell A1 and drag all the way down to A10. Click the Collapse tool again, then click the Import button, then OK. From this point on, the custom list will be available to all workbooks on the same computer.

*Click the Microsoft Office Button, and then click Excel Options. Click the Popular category, and then under Top options for working with Excel, click Edit Custom Lists.

Custom Lists Quick Tips\

Once you create a custom list, you can turn the list upside down. To do this, return to the column next to the custom list and place the last entry from the list in the top cell. In the cell beneath it, place the second-to-last entry. Select both cells and double-click thefill handle. The list you produced should be reversed

Create a custom list of the Alphabet. Enter =CHAR(98-ROW()) in A1 andFill Down. Copy the Alphabet list and Paste Special - Values. For upper case letters, use =CHAR(ROW()+64)

Special Characters

Very similar to the above with the use of the CHAR Function, but you use =CHAR(ROW()) in A1 and thenFill Down until you get the #VALUE! Error. Now copy the list of special characters and Paste Special - Values.

Remove Special Characters

Often, when you copy or import data into Excel, you end up with some special characters that are not wanted. Sometimes, you cannot even see them, but they cause grief when they are referenced by formulae. For the ones you can see, select the cell and then from the Formula Bar, highlighted the special character and copy (Ctrl+C). Now show Find & Replace (Ctrl+H) and in the Find What box paste the special character (Ctrl+V), and leave the Replace With box blank. Now click Replace All. The same applies to the special characters you cannot see, but as you cannot see them you highlight, what appears to be a space, from the Formula Bar.

Transpose That Formula Range Without Altering The Range References

Select the formula range and show Find & Replace (Ctrl+H). In the Find What box type = and in the Replace With box type # or any non common character that does NOT appear in any of your formulas. Now copy, what were formulas, and Paste Special - Transpose. Now simply select the transposed range and reverse the Find & Replace you used above.

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALLpurchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Try out:Analyzer XL |Downloader XL |Smart VBA |Trader XL Pro (best value) |ConsoXL | MergeXL | O2OLAP for Excel | MORE>>

Excel Trader PackageTechnical Analysis in Excel With $139.00 of FREE software!

Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft