tag:blogger.com,1999:blog-51807100315749223972024-02-20T03:59:28.646-08:00MS Office Tricksஇரவிசங்கரின்http://www.blogger.com/profile/03030207807628484685noreply@blogger.comBlogger17125tag:blogger.com,1999:blog-5180710031574922397.post-23400057291780037462010-06-08T13:49:00.002-07:002010-06-08T13:50:15.831-07:00Go to current date upon opening Worksheet<span style="font-weight:bold;">Go to current date upon opening Worksheet</span><br />Macro written by Ron deBruin.......with slight modifications.<br /><br />Copy/paste to a General Module.<br /><br />Sub Find_Todays_Date()<br />Dim FindString As Date<br />Dim rng As Range<br />FindString = Date<br />With ActiveSheet.Range("A:A")<br />Set rng = .Find(What:=FindString, _<br />After:=.Cells(.Cells.Count), _<br />LookIn:=xlFormulas, _<br />LookAt:=xlWhole, _<br />SearchOrder:=xlByRows, _<br />SearchDirection:=xlNext, _<br />MatchCase:=False)<br />If Not rng Is Nothing Then<br />Application.Goto rng, True<br />Else<br />MsgBox "Nothing found"<br />End If<br />End With<br />End Sub<br /><br />Place the following in the sheet module.<br /><br />Private Sub Worksheet_Activate()<br />Call Find_Todays_Date<br />End Sub<br /><br />BTW..........A is not a row. A is a column.இரவிசங்கரின்http://www.blogger.com/profile/03030207807628484685noreply@blogger.com1tag:blogger.com,1999:blog-5180710031574922397.post-13530893312595799172010-06-08T13:49:00.001-07:002010-06-08T13:49:51.868-07:00Go to current date upon opening Worksheet<span style="font-weight:bold;">Go to current date upon opening Worksheet</span><br />Macro written by Ron deBruin.......with slight modifications.<br /><br />Copy/paste to a General Module.<br /><br />Sub Find_Todays_Date()<br />Dim FindString As Date<br />Dim rng As Range<br />FindString = Date<br />With ActiveSheet.Range("A:A")<br />Set rng = .Find(What:=FindString, _<br />After:=.Cells(.Cells.Count), _<br />LookIn:=xlFormulas, _<br />LookAt:=xlWhole, _<br />SearchOrder:=xlByRows, _<br />SearchDirection:=xlNext, _<br />MatchCase:=False)<br />If Not rng Is Nothing Then<br />Application.Goto rng, True<br />Else<br />MsgBox "Nothing found"<br />End If<br />End With<br />End Sub<br /><br />Place the following in the sheet module.<br /><br />Private Sub Worksheet_Activate()<br />Call Find_Todays_Date<br />End Sub<br /><br />BTW..........A is not a row. A is a column.இரவிசங்கரின்http://www.blogger.com/profile/03030207807628484685noreply@blogger.com0tag:blogger.com,1999:blog-5180710031574922397.post-17095682144466033912010-06-08T13:47:00.001-07:002010-06-08T13:47:55.001-07:00Protect and Hide Your Formulas<span style="font-weight:bold;">Protect and Hide Your Formulas</span><br /><br />When a cell contains a formula, the formula is visible for all to see. Just activate the cell and glance up at the Formula bar. In some cases, you many want to hide your formulas to give your worksheet a cleaner look--or to keep others from seeing how your calculations are done.<br /><br />You can hide the formulas by setting the Hide option for the formula cells and protecting the sheet. But before you protect the sheet, you need to "unlock" all non-formula cells (by default, all cells are locked).<br /><br />Here's how to hide the formula cells:<br /><br /> 1. Select Edit, Go To (or press Ctrl-G or F5) to bring up the Go To dialog box.<br /><br /> 2. Click the Special button to show the Go To Special dialog box.<br /><br /> 3. Choose the Formulas option, and make sure the four check boxes are all checked.<br /><br /> 4. Click OK, and Excel selects all cells that contain a formula.<br /><br /> 5. Choose Format, Cells, and click the Protection tab.<br /><br /> 6. Place a check mark next to the Hidden option, and make sure that there is a check mark next to the Locked option. Click OK.<br /><br />Here's how to unlock the non-formula cells:<br /><br /> 1. Repeat Steps 1 and 2 above to show the Go To Special dialog box.<br /><br /> 2. Choose the Constants option, and make sure the four check boxes are all checked.<br /><br /> 3. Click OK, and Excel selects all non-empty cells that don't contain a formula.<br /><br /> 4. Choose Format, Cells, and click the Protection tab.<br /><br /> 5. Remove the check mark next to the Locked option. Click OK.<br /><br />At this point, the formula cells are set to Hide, and the non-formula cells are set to Unlock. But these settings have no effect unless the sheet is protected. Choose Tools, Protection, Protect Sheet to protect the worksheet. You can enter a password if you like.<br /><br />After performing these steps, you can select any formula cell, and the formula will not be displayed in the Formula bar. Furthermore, the formula cannot be changed. But the cells that contain other information can be changed.<br /><br />It's important to understand that protecting a worksheet--even if you use a password--is a very weak form of security. Password-cracking utilities are widespread on the Internet.இரவிசங்கரின்http://www.blogger.com/profile/03030207807628484685noreply@blogger.com0tag:blogger.com,1999:blog-5180710031574922397.post-53374684811840349062010-06-08T13:46:00.000-07:002010-06-08T13:47:15.923-07:00Zap the Numbers, Keep the Formulas<span style="font-weight:bold;">Zap the Numbers, Keep the Formulas</span><br /><br />One common type of spreadsheet is a fill-in-the-blanks template. Typically, such a sheet will have a number of input cells, and a number of formula cells. You enter your data in the input cells, and the formula cells kick into action and use those values to display some type of (usually) useful result.<br /><br />For example, you may have a worksheet set up to calculate a loan amortization table. Input cells would include items such as loan amount, interest rate, loan term, and so on. Formulas in other cells use this information to display the calculated results.<br /><br />At some point, you may want to clear the slate and delete all the values in the input cells. Rather than scroll around and look for the non-formula cells, you can take advantage of an often-overlooked Excel feature that lets you select cells in a "special" way:<br /><br /> 1. Select Edit, Go To (or press Ctrl-G or F5) to bring up the Go To dialog box.<br /><br /> 2. Click the Special button to show the Go To Special dialog box.<br /><br /> 3. Choose the Constants option, then clear all of the check boxes except Numbers (if some of the input cells in the spreadsheet accept text, leave the check mark next to Text).<br /><br /> 4. Click OK, and Excel will select all of the non-formula cells that contain a value.<br /><br /> 5. Press the Delete key, and those numbers are history--but the formulas remain intact.இரவிசங்கரின்http://www.blogger.com/profile/03030207807628484685noreply@blogger.com0tag:blogger.com,1999:blog-5180710031574922397.post-50277733902449658462010-06-08T13:45:00.001-07:002010-06-08T13:46:08.615-07:00What's the Current Date or Time?<span style="font-weight:bold;">What's the Current Date or Time?</span><br /><br />You may be familiar with Excel's NOW function, which plugs the current date and time into your spreadsheet. Just enter =NOW() into any cell, and Excel displays the date and time formatted according to the regional options you've set for Windows. If you just want to see the date, use the TODAY function, =TODAY(), instead.<br /><br />But if you want to use either of these functions as a type of "timestamp" you'll find that they don't do the job, because they're automatically updated whenever your worksheet is calculated or re-opened. The cell that displays today's date will display tomorrow's date when you open the spreadsheet tomorrow.<br /><br />To insert a fixed date into a cell--a date that you don't want to change--hold down Ctrl while you press the semicolon ( ;) key. To insert the current time (without a date), press Ctrl-Shift-Semicolon.<br /><br />These commands are useful if you like to document your work. For example, you might want to keep track of when you added new data to your worksheet.இரவிசங்கரின்http://www.blogger.com/profile/03030207807628484685noreply@blogger.com0tag:blogger.com,1999:blog-5180710031574922397.post-50120789123624998412010-06-08T13:44:00.000-07:002010-06-08T13:45:23.572-07:00Quick Math Without Formulas<span style="font-weight:bold;">Quick Math Without Formulas</span><br /><br />If you want to do math with Excel, your only choice is to write formulas, right? Wrong! Get familiar with Excel's Paste Function dialog box, and learn how to perform a variety of basic math operations without a single formula. Consider this scenario: Your company's product price list stored in a worksheet. Your boss informs you that, effective immediately, all prices must be increased by 5 percent.<br /><br />You could create some formulas to do the math, and then convert the formulas to values, and then copy and paste the new prices over the old prices. Or, you could use this more efficient method:<br /><br /> 1. Enter 1.05 into any blank cell. This is the "multiplier" that will increase the prices by 5 percent.<br /><br /> 2. Select the cell you used in Step 1, and choose Edit, Copy or press Ctrl-C.<br /><br /> 3. Select the range of values to be changed, and choose Edit, Paste Special to display the Paste Special dialog box.<br /><br /> 4. Choose the Multiply option and click OK.<br /><br />Voilà! The values are changed in an instant. You can then delete the cell that contains the 1.05 multiplier.<br /><br />Note that the Paste Special dialog box lets you carry out other mathematical operations--like add and subtract, for instance--so you can use this technique for a variety of other calculations.இரவிசங்கரின்http://www.blogger.com/profile/03030207807628484685noreply@blogger.com0tag:blogger.com,1999:blog-5180710031574922397.post-77986536016634135342010-06-08T13:43:00.000-07:002010-06-08T13:44:08.455-07:00Shade Alternate Rows<span style="font-weight:bold;">Shade Alternate Rows<br /></span><br />Looking for a way to simulate that nerdy green-banded computer paper on screen? Look no further. Excel's Conditional Formatting feature has many uses, and here's one that's particularly handy. Conditional Formatting makes it simple to apply cell shading (green or otherwise) to every other row in a worksheet range. For a lengthy list, shading alternate rows can improve legibility.<br /><br />Here's how to do it:<br /><br /> 1. Highlight the range of cells or rows or columns that you want to format.<br /><br /> 2. Choose Format, Conditional Formatting to display the Conditional Formatting dialog box.<br /><br /> 3. Select Formula Is from the first drop-down list box, and enter =MOD(ROW(),2)=0 in the second box.<br /><br /> 4. Click the Format button to bring up the Format Cells dialog box.<br /><br /> 5. Select the Patterns tab and specify a color for the shaded rows. You'll probably want to choose a light color, so that the default black text will still be legible. Or, you can go all out and change the text color as well (do this in the Font tab of the Format Cells dialog box). For example, you might select a dark blue background, accompanied by a mellow yellow text color.<br /><br /> 6. Click OK twice to return to your worksheet.<br /><br />The best part is that the row shading is dynamic: The alternate row shading persists even if you insert or delete rows within the original range. By the way, if get tired of this new look and want to get things back to normal, just select the range, choose Format, Conditional Formatting, and click the Delete button in the Conditional Formatting dialog box.இரவிசங்கரின்http://www.blogger.com/profile/03030207807628484685noreply@blogger.com0tag:blogger.com,1999:blog-5180710031574922397.post-25510306574910801002010-06-08T13:42:00.000-07:002010-06-08T13:43:24.916-07:00Add a Drop-Down List<span style="font-weight:bold;">Add a Drop-Down List</span><br /><br />Inserting a drop-down list to a cell is a slick trick that can add a touch of professionalism to your worksheets--and also ensure that erroneous data is not entered into the cell. And best of all: Macros are not required!<br /><br />Assume that you have an input cell in which the user is supposed to enter a month name: January, February, and so on. Here's how to add a drop-down list to that cell to make data entry a breeze--and save some keystrokes.<br /><br /> 1. Enter the items for your drop-down list into a list on the worksheet, one item per cell. In this example, I'll assume that the month names start in cell E1 and extend down to E12, but they can be in any out-of-the-way location on the worksheet. In Excel terminology, a rectangular group of cells (such as E1 to E12) is called a range.<br /><br /> 2. Select the cell that will contain the drop-down list. If you'd like more than one cell to display the same list, just select them all now rather than setting them up one at a time. (Click and drag to select a range; hold down Ctrl while you click to select non-adjacent cells.)<br /><br /> 3. Choose Data, Validation to display the Data Validation dialog box.<br /><br /> 4. Click the Settings tab.<br /><br /> 5. In the Allow field, select List.<br /><br /> 6. In the Source field, specify the range that contains the list items. In this example, the items are in cells E1 to E12, so type =E1:E12 into the field.<br /><br /> 7. Click OK.<br /><br />After performing these steps, you'll see a drop-down arrow whenever any of the drop-down cells you just defined is "active" (that is, selected and awaiting input). Click the arrow and choose a month from the list. If you try to type something else into the cell, you'll get scolded in the form of a pop-up message.<br /><br />If you'd like to provide your own wording for invalid entries, use the Error Alert tab in the Data Validation dialog box, and enter your own text in the 'Error message' field.<br /><br />If your list of items is relatively small, you can bypass Step 1 and enter the list items directly into the Source field in Step 6. Just separate each list item with a comma.இரவிசங்கரின்http://www.blogger.com/profile/03030207807628484685noreply@blogger.com0tag:blogger.com,1999:blog-5180710031574922397.post-4718925344288182112010-06-08T13:37:00.000-07:002010-06-08T13:38:42.398-07:00Auto start Macro to go to today's date in worksheetTry this macroIf you have date's in column A then this example will select the<br />Ron de Bruin posted on Monday, January 14, 2008 2:19 PM<br /><br />Try this macro<br /><br />If you have date's in column A then this example will select the cell with today's date.<br /><br />Sub Find_Todays_Date()<br />Dim FindString As Date<br />Dim Rng As Range<br />FindString = CLng(Date)<br />With Sheets("Sheet1").Range("A:A")<br />Set Rng = .Find(What:=FindString, _<br />After:=.Cells(.Cells.Count), _<br />LookIn:=xlFormulas, _<br />LookAt:=xlWhole, _<br />SearchOrder:=xlByRows, _<br />SearchDirection:=xlNext, _<br />MatchCase:=False)<br />If Not Rng Is Nothing Then<br />Application.Goto Rng, True<br />Else<br />MsgBox "Nothing found"<br />End If<br />End With<br />End Subஇரவிசங்கரின்http://www.blogger.com/profile/03030207807628484685noreply@blogger.com0tag:blogger.com,1999:blog-5180710031574922397.post-29744592397817191222010-05-30T19:41:00.000-07:002010-05-30T19:42:04.974-07:00Removing Extra Breaks in Word Documents<span style="font-weight:bold;">Removing Extra Breaks in Word Documents</span><br /><br /><br />Follow these steps:<br /><br />1. Press Ctrl + H to open the Find and Replace dialog box<br /><br />2. In the first box, enter ^p^p (the "p" must be lower case)<br /><br />3. In the second box, enter ^p<br /><br />4. Click Replace All<br /><br />Note: This will replace two paragraph breaks with one. You can specify other options, depending on the number of paragraph breaks you'd like between paragraphs. You can also replace a paragraph break with another character, if you choose.<br /><br />If you copied the text from the Internet, this might not work for you. That's because there are different types of breaks in HTML files. Not to worry, there is a solution:<br /><br />5. Press Ctrl + H to open the Find and Replace dialog box<br /><br />6. In the first box, enter ^l (the "l" must be lower case)<br /><br />7. In the second box, enter ^p<br /><br />8. Click Replace All<br /><br />You can then replace double breaks as necessary.இரவிசங்கரின்http://www.blogger.com/profile/03030207807628484685noreply@blogger.com0tag:blogger.com,1999:blog-5180710031574922397.post-20748592168735181622010-05-30T19:40:00.000-07:002010-05-30T19:41:09.708-07:00Removing Extra Breaks in Word Documents<span style="font-weight:bold;">Removing Extra Breaks in Word Documents<br /></span><br /><br />Follow these steps:<br /><br />1. Press Ctrl + H to open the Find and Replace dialog box<br /><br />2. In the first box, enter ^p^p (the "p" must be lower case)<br /><br />3. In the second box, enter ^p<br /><br />4. Click Replace All<br /><br />Note: This will replace two paragraph breaks with one. You can specify other options, depending on the number of paragraph breaks you'd like between paragraphs. You can also replace a paragraph break with another character, if you choose.<br /><br />If you copied the text from the Internet, this might not work for you. That's because there are different types of breaks in HTML files. Not to worry, there is a solution:<br /><br />5. Press Ctrl + H to open the Find and Replace dialog box<br /><br />6. In the first box, enter ^l (the "l" must be lower case)<br /><br />7. In the second box, enter ^p<br /><br />8. Click Replace All<br /><br />You can then replace double breaks as necessary.இரவிசங்கரின்http://www.blogger.com/profile/03030207807628484685noreply@blogger.com0tag:blogger.com,1999:blog-5180710031574922397.post-6670547593927932212010-05-27T10:10:00.001-07:002010-05-27T10:10:46.241-07:00* Selecting Synonyms<br /> A fast way is to locate a synonym for a word is to select the word and right click it. In the menu, select synonyms and click your desired replacement.<br /> <br /> * Remove All Formatting<br /> To remove all of the formatting from a Word document, press CTRL+A (or select the portion of the document to be changed) then press CTRL+SHIFT+N.<br /> <br /> * Double-Click to Format Bullets and Numbering<br /> To open the Bullets and Numbering dialog box and adjust the numbering scheme, just double-click one of the<br /> numbers or bullets in the list.<br /> <br /> * Sharing Documents with Non-Word Users<br /> Click File...Save As. In the Save as type box, select the necessary file type. Rich Text Format (*.rtf) or WordPerfect 5.1 for DOS (*.doc) will work with almost any word processor program.<br /> <br /> * Customizing Toolbars<br /> Right-click on any toolbars and click the customize the toolbar. Click the Command tab, select the desired category, and click and drag new features from the right command box to the toolbar. It is especially helpful to add the "insert picture... from file" button to the toolbar.<br /> <br /> * Screen Capture<br /> How did I create that insert picture button above? I usedthe screen capture feature of Windows. Press the Prnt Scrn (Print Screen) key on the keyboard and open Word. Click Edit...Paste (Ctrl + V). The image can then be cropped using the Picture Toolbar (only available with later versions of Word).<br /> <br /> *<br /><br /> Viewing the Document <br /><br /> There are 4 views for every Word document. These are found in the lower left corner of the screen. The most common is the print layout view.<br /><br /> Hold the mouse over the other buttons to find the Normal, Web Layout, Print Layout, and Outline Views.<br /><br /> *<br /><br /> Learn to Use Undo <br /><br /> Make a mistake? Press [Ctrl]Z or choose Undo from the Edit menu right away. Keep pressing [Ctrl]Z to backtrack through and undo the most recent editing changes you've made.<br /><br /> *<br /><br /> Templates <br /><br /> Creating professional documents in a limited amount of time is a challenge to everyone. Word makes this process easier than ever with the Template function.<br /><br /> Normally, when you create a new document, you click on the New Document tool on the toolbar. This simply creates a new blank document based on your Normal template. To use a formatted template, you must click on File, New. This opens the template dialog box. Double click on the template or Wizard you want to use, and Word will set everything up.<br /><br /> There is a huge assortment of templates available for you to use. You can create different styles of letters, memos, resumes, or other types of documents. There are even Wizards available for some of these document types that lead you step by step through the creation of a stylish, professional newsletter, resume, letter or fax.<br /><br /> *<br /><br /> Document Zoom <br /><br /> Click View…Zoom… to choose the document zoom percentage. There is also a zoom tool on the standard toolbar. <br /><br /> *<br /><br /> Selecting Text <br /><br />ü To select a word, double-click the left mouse button on the word<br /><br />ü To select a sentence, hold down CTRL and single-click the left mouse button on the sentence.<br /><br />ü To select a line of text on the screen, single-click with the left mouse button in the left screen margin at the line you want to select.<br /><br />ü To select a paragraph, triple-click the left mouse button in the paragraphs<br /><br />ü To select the whole document, hold down CTRL and A, or triple-click the left mouse button in the left margin of the screen.<br /><br /> *<br /><br /> Using AutoCorrect <br /><br /> Right-click on a word that's flagged as misspelled. If a correct suggestion appears on the shortcut menu, choose it from the AutoCorrect submenu to create an AutoCorrect entry.<br /><br /> *<br /><br /> Find and Replace <br /><br /> Click Edit…Find to type in text you desire to replace. You have the option to replace the text one time or replace it every time it appears in the document.<br /><br /> *<br /><br /> Selective Word Count <br /><br /> Need to know how many words, characters, paragraphs, or lines appear in a portion of a document? Just select the text you want to run the count on prior to choosing Word Count from the Tools menu.<br /><br /> *<br /><br /> Shrinking a Document to Fit <br /><br /> If you've created a document and one or two lines spill over unto a new page, you can use the "Shrink to Fit" feature to fit everything on the page. Choose File, Print Preview and click on the Shrink to Fit button.<br /><br /> *<br /><br /> Create A Desktop Shortcut To A Document <br /><br /> Click File…Save As…Click the drop down arrow and choose Desktop.<br /><br /> *<br /><br /> Removing Formatting for a Portion of Text <br /><br /> To quickly remove character formatting, such as bold, italic, or underline, and reset text back to the normal style, select the text and press Ctrl+Spacebar (Ctrl+Shift+Z also works). To reset paragraph formatting, such as tabs and indents, back to the normal style press Ctrl+Q.<br /><br /> *<br /><br /> Toggle through CASE Changes<br /> You can quickly change the case of text from Title to UPPER CASE to lower case by selecting the text and pressing Shift-F3. Each time you press Shift-F3, the case will change.<br /> <br /> *<br /><br /> Standardized Fonts<br /> Have you ever found the perfect font for a document and you want the viewer of your spreadsheet to see the same font, but you're not sure they have the same font? When you save your document select Tool / Options and on the Save tab select Embed TrueType Fonts and select OK. Now everybody will get it.<br /> <br /> *<br /><br /> Date Code <br /><br /> Automatically keeps dates current in your letters, memos, etc. Choose Insert, Date and Time, and select the date format you want in your document. Then check the small box at the bottom that says “update automatically.”<br /><br /> *<br /><br /> Insert Pictures and Clipart <br /><br /> Click Insert to add pictures. Pictures and clipart can also be adding using the copy and paste feature from the Internet if using Internet Explorer Browser. Go to http://jc-schools.net and right click any picture. Select copy. Then click the Word button in the Task bar and click paste to instantly paste that image into Word. If using Netscape you must choose Save Image As instead of the copy command. <br /><br /> You can also insert Screen Shots by clicking the Print Screen button on your keyboard. This will send the image to the computer clipboard. By choosing Paste in Word that image can be inserted into the document. <br /><br /> There is a Picture Toolbar (click View…Toolbars) which permits image editing. The image can be wrapped, cropped, lightened, darkened, or made into a watermark. <br /><br /> *<br /><br /> Other Text Effects <br /><br /> To add text to a document you can use the textbox button in the Drawing Toolbar at the bottom of the screen. The textbox can be formatted with fill or no fill and have an outline or have no line. There are a variety of line shapes, textures, and sizes. <br /><br /> Text Box: Yet another way to add text.<br /><br /> <br /><br /> <br /><br /> <br /><br /> *<br /><br /> Animated Text <br /><br /> Select a section of text with your mouse. Right-click the text and choose "Font" from the popup menu that appears. On the multi-tabbed dialog box that follows, select the "Animation" tab. Now, select an animation that you would like to see. You can see examples of each animation in the preview window. When you are done, press "OK" to close the dialog box.<br /><br /> *<br /><br /> Adding Borders<br /> Make sure you are in the Print Layout View. To add a border to page Click Format…Borders and Shading…From the Page Border tab choose the desired style, color, width, or if desired, choose an Art border.<br /> <br /> *<br /><br /> Getting Rid of Nuisances! <br /><br /> How do I get rid of these ¶¶¶¶? Click Tools … Options…Click the "Views" tab sheet. Under "Nonprinting Characters" unselect "Paragraph Marks".<br /><br /> *<br /><br /> How do I get rid of overtype? Choose Tools…Options…Edit tab…uncheck Overtype Mode.<br /> <br /> * Shortcut Keys in Word<br /> CTRL + A Selects all in the current document.<br /> CTRL + B Bold text.<br /> CTRL + C Copies the item or text to the Clipboard + can be pasted using CTRL + V.<br /> CTRL + D Displays the Font dialogue box.<br /> CTRL + E Centre Alignment.<br /> CTRL + F Displays the Find dialog box, to search the current document.<br /> CTRL + G Displays the Go to dialog box, to go to a specific location in the current document.<br /> CTRL + H Displays the Replace dialogue box.<br /> CTRL + I Italic text.<br /> CTRL + J Full Justification.<br /> CTRL + K Create Hyperlink<br /> CTRL + L Left Alignment<br /> CTRL + M Tab<br /> CTRL + N Creates a new document.<br /> CTRL + O Displays the Open File dialogue box.<br /> CTRL + P Displays the Print dialog box.<br /> CTRL + R Right Alignment.<br /> CTRL + S Displays the Save dialog box.<br /> CTRL + U Underline text<br /> CTRL + V Pastes the copied item or text from the Clipboard into the current position in the document.<br /> CTRL + X Cuts the item or text selected to the Clipboard.<br /> CTRL + Y Redo the last undone action.<br /> CTRL + Z Undoes the last action.<br /> CTRL + ENTER Insert Page Break.<br /> CTRL + F2 Show Print preview.<br /> CTRL + F4 Closes the active document window.<br /> CTRL + F6 Opens the next document window.<br /> CTRL + 1 Single spaces text<br /> CTRL + 2 Double spaces text<br /> CTRL + 3 Triple spaces textஇரவிசங்கரின்http://www.blogger.com/profile/03030207807628484685noreply@blogger.com0tag:blogger.com,1999:blog-5180710031574922397.post-4332275860615041242010-05-27T10:09:00.000-07:002010-05-27T10:10:33.763-07:00#<br /><br />Setting the Default Text Style<br />To change the style of the text that appears when you type things that aren't the title or the slide body, do the following:<br />Choose the first layout option and click in the title box. From the Format menu, select Font. Make all the changes that you want there, and then check default for new objects. Next click OK. From that point on, new text will be created in that font style and size.<br /> <br />#<br /><br />Changing A Design Template<br />To change the design template for the title or slide body objects, on the menu select View…Title or Slide Master. Click the body of the slide then right click the slide and choose Group…Ungroup. Delete any part of the design you desire. Then right click and choose Group…Regroup. When completed choose the slide sorter view mode from the lower left-corner of the screen.<br /> <br />#<br /><br />Using More than One Guide<br />If you like using guides, but wish there were more, you can create additional Guides by simply holding down the CTRL key while dragging on an existing Guide. This will create a new guide. To get rid of guides, just drag them off the edge of the slide.<br /> <br />#<br /><br />Creating Pages with Slides and Descriptive Text<br />If you want to create printable pages that have notes or descriptive text associated each slide, PowerPoint has a feature designed to do just this called Notes Pages, or Speaker's Notes (depending on which version you're using). To view the Notes page for any slide, go to the View menu and select Notes Pages. You will see an image of your slide there, and a placeholder for adding your script, notes, or any other text you wish. You can cut-and-paste text from Word here if you like. To print these pages, bring up the Print dialog, and at the bottom of the dialog where it says "Print What:", select Notes Pages. These pages were originally designed to be used as audience hand outs. To return to the normal view click the Slide View button in the lower right corner. <br /> <br />#<br /><br />Building Presentations for Distribution to Others<br /><br />When making a PowerPoint presentation that will be distributed to other people, there are some important things to watch out for that will cause problems:<br /><br />1. Stick with the fonts that come installed with Windows; Fancy fonts that appear on your machine will cause problems if everyone else doesn't have them.<br /><br />2. Avoid embedding sounds and videos: these will not go from Mac to Windows gracefully, and you have to be very careful about how you insert the files in order to get them to "travel" properly. See the FAQ section for more information on this.<br /><br />3. Design the presentation on the lowest version that you think might be in use. For example, if you want the presentation to be able to be viewed by Mac users (who may not have upgraded to the latest version), you will want to design your presentation in PowerPoint 4.0. If you don't have PowerPoint 4, then you'll want to save your presentation in the lowest format you think people will have. For cross-platform distribution, 4.0 is still your safest bet; for Windows-only distribution, save to PowerPoint 95. When you down-rev save, be prepared for some visual changes in your file--the previous version may not support some of the features you've put in, so be sure to sanity check your file on several different machines and versions BEFORE you distribute it! <br /> <br />#<br /><br />Easily Changing from Caps to Lower Case (or Vice Versa)<br />If you have text that is in the wrong case, select the text, and then click Shift+F3 until it changes to the case style that you like. Clicking Shift+F3 toggles the text case between ALL CAPS, lower case, and Initial Capital styles. This useful trick works with Word too!<br /> <br />#<br /><br />Nudging Objects<br />You can use the arrow keys to move objects very small distances. This is a big win for those laptop users who no longer have mice. Select the object, then use your arrow keys. Each press of the key will move the object on "grid unit" (1/12th of an inch, don't ask why); if you hold down the Ctrl key while nudging, or if you have the grid turned off, you can move the objects one pixel at a time.<br /> <br />#<br /><br />Saving Across Multiple Diskettes<br />Since PowerPoint 7.0 (the one in Office 95, also known as PowerPoint '95) you have the option to save large files over multiple diskettes. From the File menu, select Pack and Go. This wizard will compress your PowerPoint presentation and copy the file onto as many floppies as are necessary.<br /> <br />#<br /><br />Draw A Line Perfectly Horizontal or Vertical<br />Depress the Shift key while dragging to create your line.<br /> <br />#<br /><br />Draw A Perfect Square<br />Depress the Shift key while dragging to create your square.<br /> <br />#<br /><br />Draw A Perfect Circle<br />Depress the Shift key while dragging to create your circle.<br /> <br />#<br /><br />Quickly Access the Slide Master<br />Click on the Slide View icon (at the top-left of the screen), while depressing the Shift key.<br /> <br />#<br /><br />Create A New Design Template<br />Click on the File drop down menu, select New and then select the Design Template tab. Select an existing design template that most closely matches what you wish to achieve. Make changes to the slide master as required. Then save the file as a template using the SaveAs command to save the file as a dot file.<br /> <br />#<br /><br />Preview A Presentation in Black and White <br />Click on View…Black and White.<br /> <br />#<br /><br />Send to the Back or Bring Forward an Object<br />Right click on the object, select order, and choose the desired command.<br /> <br />#<br /><br />Insert the Copyright Symbol <br />To insert the copyright © symbol, enter (c) <br />To insert the Trademark ™ symbol enter (tm) <br />To insert the registered ® symbol enter (r)<br /> <br />#<br /><br />Saving Shows<br />Save your presentation as a ‘PowerPoint Show’ (.pps) and your presentation will open straight into screenshow mode. To change a .pps back to a presentation for editing, locate the file name, right click the file name, choose rename, and change the .pps extension to .ppt. A warning about instability will appear, but no harm will be done to the presentation.<br /> <br />#<br /><br />Jumping to Screens <br />In show mode type a number then hit ‘enter’ to go to straight to that screen i.e. ’1’ to go back to the 1st screen. This is particularly useful if you have a large show for multiple speakers - just make a note of the slide number where each one starts - and during rehearsal, simply keying the number and pressing enter, jumps you straight to the right place. Quick and efficient.<br /> <br />#<br /><br />Hiding Screens<br />In the show mode you can hide your first (or any other) screen until you are ready to start by pressing ‘B’’ to blackout the screen or (‘W’ to ‘whiteout’ the screen) then press the ‘B’ or ‘W’ again to reveal the screen when you are ready.<br /> <br />#<br /><br />To Go to the First Slide or Last Slide<br />Ctrl+Home will take you to the first slide in a presentation, Ctrl+End will take you to the last slide.<br /> <br />#<br /><br />Toolbar Tips<br />You can customize your toolbar to contain buttons for tasks that you routinely conduct. Click View…Toolbars…Customize. Click the Commands Tab and choose the desired task. Click and drag the accompanying icon to your preferred location in the toolbar. Close the customize window.<br /> <br />#<br /><br />Setting up the Show<br />To set up the presentation to run continuously, click Slide Show…Set Up Show. Check ‘loop continuously until ESC’ and click OK.<br /><br />To set up the presentation with timed settings on each slide, click Slide Show…Rehearse Timings. As each slide appears a rehearsal clock will appear in the upper left corner. When the desired time has expired click the mouse to progress to the next slide. After viewing the entire show you will be asked if you wish to save the rehearsed time. Click Yes.<br /><br />To record your own narration for the presentation click Slide Show…Record Narration.<br /> <br />#<br />Printing the Presentation<br />You have many options for printing your presentation. Click File…Print. You can choose to print it as slides or handouts and choose how many will fit on a page, print it as a Notes Page, or print it in the outline view. Other options include to print it in Grayscale, Pure Black and White, or with Animations. You can also choose to print only specific slide numbers or print all slides.<br /> <br /># Scrolling Credits<br />Scrolling text can be an effective means of ending or beginning a presentation. Click the desired text. When the Custom Animation text box opens, click the Effects tab. Click the arrow at the right side of the Entry Animation list box to expand the list. Locate Crawl From Bottom and select it. Check the list box labeled Introduce Text. If it isn't set to All at Once, click the arrow at the right side of the list box and select All at Once. Click the Timing tab and select the radio buttons labeled Animate and Automatically. Click OK to close the dialog box and save your changes.<br /> <br />#<br /><br />Keyboard Shortcuts<br /><br />Insert a new slide<br /> <br /><br />CTRL+M<br /><br />Switch to the next pane (clockwise)<br /> <br /><br />F6<br /><br />Switch to the previous pane (counterclockwise)<br /> <br /><br />SHIFT+F6<br /><br />Make a duplicate of the current slide<br /> <br /><br />CTRL+D<br /><br />Start a slide show<br /> <br /><br />F5<br /><br />Promote a paragraph<br /> <br /><br />ALT+SHIFT+LEFT ARROW<br /><br />Demote a paragraph<br /> <br /><br />ALT+SHIFT+RIGHT ARROW<br /><br />Apply subscript formatting<br /> <br /><br />CTRL+EQUAL SIGN (=)<br /><br />Apply superscript formatting<br /> <br /><br />CTRL+PLUS SIGN (+)<br /><br />Open the Font dialog box<br /> <br /><br />CTRL+T<br /><br />Repeat your last action<br /> <br /><br />F4 or CTRL+Y<br /><br />Find<br /> <br /><br />CTRL+F<br /><br />View guides<br /> <br /><br />CTRL+G<br /><br />Delete a word<br /> <br /><br />CTRL+BACKSPACE<br /><br />Capitalize<br /> <br /><br />SHIFT+F3<br /><br />Bold<br /> <br /><br />CTRL+B<br /><br />Italicize<br /> <br /><br />CTRL+I<br /><br />Insert a hyperlink<br /> <br /><br />CTRL+K<br /><br />Select all<br /> <br /><br />CTRL+A<br /><br />Copy<br /> <br /><br />CTRL+C<br /><br />Paste<br /> <br /><br />CTRL+V<br /><br />Undo<br /> <br /><br />CTRL+Z<br /><br />Save<br /> <br /><br />CTRL+S<br /><br />Print<br /> <br /><br />CTRL+P<br /><br />Open<br /> <br /><br />CTRL+Oஇரவிசங்கரின்http://www.blogger.com/profile/03030207807628484685noreply@blogger.com0tag:blogger.com,1999:blog-5180710031574922397.post-17237253615011769332010-05-27T10:07:00.000-07:002010-05-27T10:08:46.059-07:00<span style="font-weight:bold;"> * Generate Random Numbers</span><br /> Need to create random numbers? You can do it in Excel. <br /> To generate a number between 0 and 1, type =RAND() in a cell.<br /> To generate a number between 1 and 100, type =RAND()*100.<br /> After entering, use the fill handle to quickly populate as many cells with random numbers as needed. To use the fill handle, click the cell, move your pointer over the lower-right corner of the cell until it turns into a black plus sign, and drag it horizontally or vertically across the cells you wish to populate. The cells can then be formatted as desired.<br /> <br /><span style="font-weight:bold;"> * No Formula, Please</span><br /> When copying and pasting a cell that contains a formula, use the Paste Special feature. First, copy the cell (Edit...Copy). Next click in the desired location and click Edit...Paste Special. Choose Values to copy the number only and not the formula. <br /> <br /><span style="font-weight:bold;"> * Insert Time/Date in Excel</span><br /> Try these keyboard shortcuts to insert the time/date in an Excel spreadsheet:<br /> Current date: Press CTRL+SEMICOLON<br /> Current time: Press CTRL+SHIFT+ SEMICOLON<br /> Current date and time: Press CTRL+ SEMICOLON then SPACE then CTRL+SHIFT+ <span style="font-weight:bold;">SEMICOLON</span><br /><span style="font-weight:bold;"><br /> * Hide Worksheets in Excel 2002</span><br /> To hide Excel worksheets to prevent unwanted changes, Select the worksheet, click Format...Sheet...Hide.<br /> <br /><span style="font-weight:bold;"> * Color-Coding Excel Sheet Tabs in Excel 2002</span><br /> In Excel 2002, color-code sheet tabs for easier identification or grouping. <br /> Select the sheet(s) by holding down the CTRL key and clicking the tabs.<br /> Click Format...Sheet...Tab Color. You can also right-click the sheet tab and choose<br /> click Tab Color.<br /> <br /><span style="font-weight:bold;"> * Pasting an Excel Table and Its Formatting into Word</span><br /> In Excel, select the table and click Edit..Copy. Switch to Word, and click where the table will be located. Click Edit...Paste. Using the Paste Options smart tag, select one of the following options: To keep the formatting, select Keep Source Formatting. To automatically keep data updated as it is updated in Excel, select Keep Source Formatting and Link to Excel. To match the style of another table in the Word document, select Match Destination Table Style. To link the table instead of copying it, select Match Destination Table Style and Link to Excel.<br /> <br /><span style="font-weight:bold;"> * Go To</span><br /> To search for specific cells, such as ones that have formulas or ones that just contain values, use the Go To feature. Click Edit...Go to...and choose the desired feature.<br /> <br /><span style="font-weight:bold;"> * Shortcut Keys</span><br /> To see a complete list of shortcut keys in Excel, press F1 on the keyboard and type shortcut keys in the search box. <br /> <br /><span style="font-weight:bold;"> * Customizing Toolbars</span><br /> Right-click on any toolbars and click the customize the toolbar. Click the Command tab, select the desired category, and click and drag new features from the right command box to the toolbar.<br /> <br /><span style="font-weight:bold;"> * Quick Graphs</span><br /> Want to create a quick graph? Click anyway in the Excel data on the spreadsheet, press F11 key and presto! Right-click in the graph border to change the type, location, or data.<br /> <br /><span style="font-weight:bold;"> * Basic Keyboard Shortcuts</span><br /> F1 Help<br /> F2 Edit current Cell<br /> F5 Goto<br /> F7 Spell Check<br /> F12 Save file as<br /> CTRL + A Select entire worksheet.<br /> CTRL + B Toggle Bold Text.<br /> CTRL + C Copies the item or items selected to the Clipboard and can be pasted using CTRL + V.<br /> CTRL + F Displays the Find dialog box.<br /> CTRL + H Displays the Replace dialog box.இரவிசங்கரின்http://www.blogger.com/profile/03030207807628484685noreply@blogger.com0tag:blogger.com,1999:blog-5180710031574922397.post-30833256106395018562010-05-27T10:00:00.000-07:002010-05-27T10:06:44.706-07:00Excel Tips, Tricks and Shortcuts for Microsoft Excel<span style="font-weight:bold;">Excel Tips, Tricks and Shortcuts for Microsoft Excel</span>
<br />
<br /><span style="font-weight:bold;">Shortcut Keys</span>
<br />On most of Excels menu items you will see the shortcut key associated with it. To see a complete list push F1 and type "Shortcut Keys".
<br />
<br /><span style="font-weight:bold;">Quick Help</span>
<br />To get quick help on any menu item push Shift+F1 and click the menu item
<br /><span style="font-weight:bold;">
<br />Insert Today's Date</span>
<br />To insert Today's date push Ctrl+; (semicolon)
<br />
<br /><span style="font-weight:bold;">Insert Current Time</span>
<br />To insert the current time push Ctrl+Shift+: (Colon)
<br /><span style="font-weight:bold;">
<br />Show the Paste Function (Function Wizard)</span>
<br />Push Ctrl+F3
<br />
<br /><span style="font-weight:bold;">Show the GoTo dialog</span>
<br />Push F5
<br />
<br /><span style="font-weight:bold;">Show the Paste Names dialog</span>
<br />Push F3. This will only work if you have named ranges.
<br /><span style="font-weight:bold;">
<br />Name a Range</span>
<br />To name a selected range, click in the "Name box" (far left on the formula bar) and type a one word name.
<br />
<br /><span style="font-weight:bold;">Go To a Named Range</span>
<br />To go to a named range select it from the "Name box" (far left of the formula bar). Or push F5.
<br />
<br /><span style="font-weight:bold;">Edit a Named Range</span>
<br />To delete or edit a named range go to Insert>Name>Define or Push Ctrl+F3.
<br /><span style="font-weight:bold;">
<br />Headings as Range Names</span>
<br />Highlight your range including the headings and go to Insert>Name>Create or push Ctrl+Shift+F3.
<br /><span style="font-weight:bold;">
<br />Named Formula</span>
<br />To make a Name refer to a constant formula e.g. "TaxRate", go to Insert>Name>Define and type TaxRate in the "Names in Workbook" box and 36% in the "Refers To". Now enter =(10*TaxRate) anywhere on the Worksheet.
<br />
<br /><span style="font-weight:bold;">Named Range List</span>
<br />To obtain a list of all Named Ranges and where they refer, select any blank cell (make sure you have no data underneath or 1 column over) and go to Insert>Name>Paste then Paste List.
<br />
<br /><span style="font-weight:bold;">Nested Formulas</span>
<br />To help write nested formulas (more than 1 formula in a single cell) use the "Paste Function" i.e. Insert>Function or Shift+F3. Select the function that you need, enter the reference, number or text then select the drop arrow to the left of the formula bar to add more Formulas. Doing it this way ensures all your parentheses are in the correct places.
<br />
<br /><span style="font-weight:bold;">Debugging Formulas</span>
<br />To troubleshoot complex formulas select the cell containing it and then click the = (Equal sign) to the left of the formula bar, this will activate the "Paste Function". To step through your formula simply click in the part of the formula you want to debug.
<br />
<br /><span style="font-weight:bold;">Personal Help</span>
<br />To add your own text to any of the Office Assistants help files, push F1, enter your question then open the file. Go to Options>Annotate and type in your own text then click OK. You will now notice a paperclip symbol next to the heading, this will let you know that you have added your own Help in a way you will understand.
<br />
<br /><span style="font-weight:bold;">Different Help</span>
<br />Sometimes the Office Assistant is not very helpful to your needs, so try the "Context and Index" help by either clicking Help>Context and Index or selecting "Help Topics" from any "Help" file.
<br />
<br /><span style="font-weight:bold;">Customizing Toolbars</span>
<br />Right click on any Toolbar and select "Customize" or push Ctrl+Shift+F10 twice then "Customize". Now click the "Commands Tab" and drag menu items both on and off the Toolbars. If things get a bit messy simply click the "Toolbars" tab and click "Reset". This will return all menu items to their default.
<br />
<br /><span style="font-weight:bold;">Quick Charts</span>
<br />To create quick charts, click anywhere within your data and push F11.
<br />
<br /><span style="font-weight:bold;">Worksheet Template</span>
<br />Set up your Worksheet how you want it e.g. formatting, formulas etc then delete all other sheets in the Workbook. Now go to File>Save or Alt+F2 and select "Template (*.xlt)" from the "Save as Type". Type a name and click "Save" Now right click on the sheet tab and select Insert you should see your Template sheet.
<br />
<br /><span style="font-weight:bold;">Secret Menu</span>
<br />Click in any cell, then move your mouse pointer over any border of the cell until the mouse pointer changes to an arrow, right click and drag to it's destination and then release.
<br />
<br /><span style="font-weight:bold;">Secret Menu 2</span>
<br />Place a date in any cell, then move your mouse pointer over the bottom right corner of the cell (Fill handle) until the mouse pointer changes to a small black cross. Now right click and drag to any cell and release.
<br />
<br /><span style="font-weight:bold;">Quick Cell Move</span>
<br />Click in the cell(s) then move your mouse pointer over any border until the mouse pointer changes to an arrow, left click and drag to it's destination and then release.
<br />
<br /><span style="font-weight:bold;">Quick Cell Copy</span>
<br />Click in the cell(s) then move your mouse pointer over any border until the mouse pointer changes to an arrow, left click and hold down the Ctrl key and drag to it's destination and then release.
<br />
<br /><span style="font-weight:bold;">Change Formulas to Values</span>
<br />Click in the cell(s) with the formula(s) then move your mouse pointer over any border until the mouse pointer changes to an arrow, right click and drag to the next cell, now still holding down the right mouse button drag back to where you Start ed and release. Now select Copy here as values only.
<br />
<br /><span style="font-weight:bold;">Quick List</span>
<br />To quickly copy down the contents of a cell that has a list in the column to the left or right of it, simply click in the cell you want to copy and then Double click the Fill handle (little black square on the bottom right of the cell).
<br />
<br /><span style="font-weight:bold;">Fill Blank Cells Within a List</span>
<br />Let's say you have a list of entries in column A and within the list you have many blank cells. Here is a quick way to fill those blanks with the value of the cell above. Highlight column A, then push Ctrl+G and click Special then check the Blanks option and click OK. Now push Equals (=) then the Up arrow and finally holding down the Ctrl key push Enter.
<br />
<br /><span style="font-weight:bold;">Auto Fill</span>
<br />To fill a series across columns or down rows type January or Jan in any cell and place your mouse pointer over the bottom right corner of the cell (Fill handle) until the mouser pointer changes to a small black cross. Left click and drag down or across. This can also be done with Numbers, Weekdays, Quarters or any text that ends in a number e.g. Day1.
<br />
<br /><span style="font-weight:bold;">Custom Auto Fill</span>
<br />Type your list across columns or down rows. Now go to Tools>Option and select the "Custom Lists" tab. Click the collapse dialog box to the right of the "Import list from cells" box, highlight your range, click the expand dialog and then click "Import". Or type your entries in the "list Entries" box.
<br />
<br /><span style="font-weight:bold;">Adding Text to Formulas</span>
<br />To show a formula result and text or number(s) in the same cell type a & (Ampersand) after the formula then your text/number(s).
<br />
<br /><span style="font-weight:bold;">Adding Hidden Text to Formulas</span>
<br />Imagine you have a formula like: =$2018+$1056-4*$120. When you initially wrote it you knew what each number represented, but you come back later and can't remember. Add a hidden note to your formula by using the N() formula i.e. =$2018+$1056-4*$120+N("My Wage+Bonus-4 weekly loan repayments"). The N() function will convert text to zero.
<br /><span style="font-weight:bold;">
<br />Custom Format</span>
<br />You can format a cell to show any number or text without changing it's real value using "Custom Format". To see this type the number 20 in any cell then go to Format>Cells or push Ctrl+1. Select the "Number" tab and then select "Custom." Using any one of the pre-defined formats type "Twenty" (without quotations) or any text and then click "OK". To test it use the cell in any formula.
<br />
<br /><span style="font-weight:bold;">No More Chart Gaps</span>
<br />If you have a chart that is plotting empty text ("") or 0 (zero) from a formula then instead of using "" or 0 if the formula is False try using "#N/A" (without the quotations) or the formula =NA(). Or you can hide the Row(s) or Column(s). Either way Excel won't plot #N/A or hidden Rows or Columns.
<br />
<br /><span style="font-weight:bold;">My List</span>
<br />If you have a long list of Text with no blank cells between and you want to see a preview of what is in your list. Click in any cell within your list then right click and select "Pick from list", If you select one of the entries, Excel will insert it in the cell for you.
<br />
<br /><span style="font-weight:bold;">Remove Blank Rows</span>
<br />Highlight your range and go to Edit>Go to>Special and select "Blanks" then "Ok" now go to Edit>Delete or Ctrl+Shift+= (equal) and then select "Entire row" from the "Delete" dialog and click "Ok".
<br />
<br /><span style="font-weight:bold;">Sort Out Blank Rows</span>
<br />The quickest way to remove all blank rows is to select you range then go to Data>Sort.
<br />
<br /><span style="font-weight:bold;">See Formula cells</span>
<br />If you have a sheet full of formulas and you want to identify these cells at a glance go to Edit>Go to>Special and select "Formulas" then click "OK". Now go to Format>Cells or Ctrl+1 and select the "Patterns" tab and choose a color.
<br />
<br /><span style="font-weight:bold;">En Masse Changes</span>
<br />To make changes to more than one worksheet at the same time select one of the sheets, hold down your Ctrl key and click on each sheet name tab. Now any data entered one sheet will also be entered on the other(s). When you have finished right click on any of the sheet name tabs and select "Ungroup sheets".
<br />
<br /><span style="font-weight:bold;">En Masse Changes 2</span>
<br />Another way to have changes on one worksheet reflected on other sheets is to make all the changes you want on one sheet then hold down your Ctrl key and select the other sheet tabs. Go to Edit>Fill>Across Worksheets and Excel will give you 3 choices of what to copy to the other sheets i.e. "All", "Contents" or "Formats".
<br />
<br /><span style="font-weight:bold;">Worksheet Copy</span>
<br />Select the sheet name tab then hold down your Ctrl key and simply drag it to the position you want it.
<br />
<br /><span style="font-weight:bold;">Paste Reference</span>
<br />An easy way to reference another cell is to select the cell you wish to reference then right click and select Copy or Ctrl+C then select the cell you want the reference in, right click again and select "Paste Special" then click "Paste Link"
<br />
<br /><span style="font-weight:bold;">Absolute/Relative Toggle</span>
<br />If you have a formula you want to make absolute or relative then double click in the cell or F2 then place the insertion point anywhere in the cell address and push F4 1, 2 or 3 times.
<br />
<br /><span style="font-weight:bold;">Repeat</span>
<br />To repeat an operation push F4
<br />
<br /><span style="font-weight:bold;">Undo</span>
<br />To undo an operation push Ctrl+Z
<br />
<br /><span style="font-weight:bold;">Linked Picture</span>
<br />A good alternative to a textbox or any shape is a linked picture that reflects any changes made to its reference. To make one, copy your cell(s), select the destination cell and holding down your Shift key go to Edit<Paste Picture Link.
<br />
<br /><span style="font-weight:bold;">Run a Macro by Clicking a Cell</span>
<br />This is possible with use of VBA but let's face it most people don't know VBA so here is an easy way. Select the cell you want to run the macro and hold down your Shift key and go to Edit>Copy Picture then select "As shown on screen" from the "Copy Picture" dialog then hold down your Shift key again and go Edit and click "Paste Picture". Now right click on the cell picture and "Assign Macro".
<br />
<br /><span style="font-weight:bold;">Non Formula Result</span>
<br />Sometimes you just want the result from the Sum, Average, Min, Max etc from a group of cells without typing a formula in a cell. Excel allows you to do this very easily, first highlight the cells you want to evaluate then right click on the "Status Bar" and select the function you want and your result will be displayed in the "Status Bar".
<br />
<br /><span style="font-weight:bold;">Reduce File Size</span>
<br />When you have a workbook that is very large in size you can reduce this dramatically by saving the file as "Microsoft Excel Workbook (*.xls)" as apposed to "Microsoft Excel 5/95 Workbook (*.xls)". In other words avoid saving as multiple versions whenever possible. Also click here for much more details and other methods.
<br />
<br /><span style="font-weight:bold;">Cell Navigation</span>
<br />To move through a group of cells that you are working with without going outside the range highlight the group of cells and then use the "Enter" key to move through them.
<br />
<br /><span style="font-weight:bold;">Quick Formula Syntax</span>
<br />When writing formulas for Excel sometimes you just need a quick reminder of the formula syntax. In this is the case then type an equal sign followed by the function name and push Ctrl+Shift+A. For Example typing =Vlookup and then pushing Ctrl+Shift+A will give you: =vlookup(lookup_value,table_array,col_index_num,range_lookup). The non-bolded arguments are optional.
<br />
<br /><span style="font-weight:bold;">How to copy formulas without the reference changing</span>
<br />This can be achieved by either pressing F2 and then highlighting the formula, Copy, Enter then paste to destination. Or doing the same in the Formular bar. However, this is not much good for large amounts of data, so try this: Select the range of cells with Formulae, use the Ctrl key for non-contiguous ranges. Now go to Edit>Replace and Replace = with #. Copy and paste to your location and then simply use Edit>Replace # with =
<br />
<br /><span style="font-weight:bold;">How to copy and transpose formulas without the reference changing</span>
<br />In cell A1 of sheet 2 put: =Sheet1!A1 now copy this down a max of 255 rows. Now with the formulas selected go to Edit>Replace and Replace = with #. Now copy, select cell B1, go to Edit>Paste special and choose Transpose. Delete Column "A" and with Row 1 selected go to Edit>Replace and Replace # with =
<br />
<br /><span style="font-weight:bold;">Turn a List Upside-Down</span>
<br />1. Copy the list to another location using Copy, Edit>Paste Special>Value.
<br />2. Now select all data in the list, go to Tools>Options>Custom Lists.
<br />3. Ensure the list address is in the "Import list from cells:" and click "Import".
<br />4. Now go back to the column next to your list and in the top cell place the LAST entry from your list.
<br />5. Now in the cell below, place the second last entry.
<br />6. Select both cells and double click on the Fill Handle (small black square bottom right).
<br />
<br />The list should now be reversed. You could now also sort you original list using Data>Sort>Options, nominate your list then sort!
<br />
<br /><span style="font-weight:bold;">Formula Errors</span>
<br />Whenever typing one of Excels functions (especially nested ones) into a cell always use lower case. This way when you push Enter Excel will capitalize only the names of the functions you have entered correctly.
<br /><span style="font-weight:bold;">
<br />Entering Named Ranges Into Formulas</span>
<br />When you write a formula, sometimes you want to use a Named Range as one of the arguments for the formula, but you cannot remember the name. In these times simply push F3 when you reach the argument that you want the Named Range in and Excel will display the Paste Name dialog. Click the name you want then OK.
<br />
<br /><span style="font-weight:bold;">Optional Function Arguments</span>
<br />Sometimes you may not be sure what arguments in a function are optional and which are not. If your using the Paste Function (Function Wizard) then the non-bolded arguments are optional.
<br /><span style="font-weight:bold;">
<br />Sort by more than 3 Columns</span>
<br />Excels sort feature only allows to nominate up to 3 columns to sort by, here is how to get around this. The key to this is sorting by the last key first and working back to the first key. Say you data is in Column A:E and you want to sort by A, B, C , D then E
<br />
<br />1. Select all of Columns A:E
<br />2. Go to Data>Sort> sort by C then by D then by E
<br />3. Click Sort
<br />4. Now again with Columns A:E selected
<br />5. Go to Data>Sort> sort by A then by B
<br />6. Click Sort
<br />
<br /><span style="font-weight:bold;">Printing Workbooks</span>
<br />If you have quite a few Workbooks to print, go to File>Open from within Excel, select the Workbook(s) using the Ctrl key, then right click and choose Print.இரவிசங்கரின்http://www.blogger.com/profile/03030207807628484685noreply@blogger.com0tag:blogger.com,1999:blog-5180710031574922397.post-83902179666456031882010-05-27T09:18:00.001-07:002010-05-27T09:18:54.365-07:00display formulas so you can troubleshoot or debug them<span style="font-weight:bold;">Press [Ctrl]~ to display formulas so you can troubleshoot or debug them</span><br /><br />you can display your formulas by pressing [Ctrl]~. If you want to view the dependent cells for a particular cell, select it before you press [Ctrl]~.இரவிசங்கரின்http://www.blogger.com/profile/03030207807628484685noreply@blogger.com0tag:blogger.com,1999:blog-5180710031574922397.post-61831059246014550242010-05-27T09:12:00.000-07:002010-05-27T09:13:58.624-07:00Quickly Delete Blank Rows From A Long List (Excel 2003, Excel 2007)<span style="font-weight:bold;">Quickly Delete Blank Rows From A Long List (Excel 2003, Excel 2007)</span><br /><br />If you have a long list of data that contains blank rows that you want to delete, you have several options.<br /><br /> * You could select each row, one by one, and delete them individually. If you have dozens or hundreds of rows, this could be time consuming.<br /> * You could sort the rows alphabetically, which would put all the blank rows together, and then delete that group of blank rows. However, there will be times when you may want to maintain the order of your list, so this would not be an option.<br /> * You could filter the list for blanks, select these rows (i.e. visible cells) and delete them.<br /> * You could use a little-known shortcut to quickly select the blanks in your list and delete them.<br /><br />This week I will cover option number 4 and next week I will cover option number 3. The first two are self-explanitory and usually are not your best choice, so I won't discuss those.<br /><br />Suppose you receive a list of data that contains blank rows that you want to remove without affecting the order of the list. This technique will allow you to delete all rows that have blank cells in a particular column. Here's the fastest way to do it.<br /><br /> 1. First, before you make any drastic changes to any workbook always make sure you have a backup copy or a recently saved copy in case you need to restore the original data .<br /> 2. Now, select the cells in one column from the top of your list to the bottom.<br /> 3. Make sure that all the blank cells in this selected range are the rows you want to delete.<br /> 4. Press the F5 key on your keyboard (or select Edit, Goto).<br /> 5. Click the Special button.<br /> 6. Click the Blanks option and click OK. This will select all blank cells in the range you had previously selected.<br /> 7. Now choose Edit, Delete, select the Entire Row option and click OK.இரவிசங்கரின்http://www.blogger.com/profile/03030207807628484685noreply@blogger.com0