Go to current date upon opening Worksheet
Macro written by Ron deBruin.......with slight modifications.
Copy/paste to a General Module.
Sub Find_Todays_Date()
Dim FindString As Date
Dim rng As Range
FindString = Date
With ActiveSheet.Range("A:A")
Set rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
Application.Goto rng, True
Else
MsgBox "Nothing found"
End If
End With
End Sub
Place the following in the sheet module.
Private Sub Worksheet_Activate()
Call Find_Todays_Date
End Sub
BTW..........A is not a row. A is a column.
Tuesday, June 8, 2010
Go to current date upon opening Worksheet
Go to current date upon opening Worksheet
Macro written by Ron deBruin.......with slight modifications.
Copy/paste to a General Module.
Sub Find_Todays_Date()
Dim FindString As Date
Dim rng As Range
FindString = Date
With ActiveSheet.Range("A:A")
Set rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
Application.Goto rng, True
Else
MsgBox "Nothing found"
End If
End With
End Sub
Place the following in the sheet module.
Private Sub Worksheet_Activate()
Call Find_Todays_Date
End Sub
BTW..........A is not a row. A is a column.
Macro written by Ron deBruin.......with slight modifications.
Copy/paste to a General Module.
Sub Find_Todays_Date()
Dim FindString As Date
Dim rng As Range
FindString = Date
With ActiveSheet.Range("A:A")
Set rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
Application.Goto rng, True
Else
MsgBox "Nothing found"
End If
End With
End Sub
Place the following in the sheet module.
Private Sub Worksheet_Activate()
Call Find_Todays_Date
End Sub
BTW..........A is not a row. A is a column.
Protect and Hide Your Formulas
Protect and Hide Your Formulas
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.
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).
Here's how to hide the formula cells:
1. Select Edit, Go To (or press Ctrl-G or F5) to bring up the Go To dialog box.
2. Click the Special button to show the Go To Special dialog box.
3. Choose the Formulas option, and make sure the four check boxes are all checked.
4. Click OK, and Excel selects all cells that contain a formula.
5. Choose Format, Cells, and click the Protection tab.
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.
Here's how to unlock the non-formula cells:
1. Repeat Steps 1 and 2 above to show the Go To Special dialog box.
2. Choose the Constants option, and make sure the four check boxes are all checked.
3. Click OK, and Excel selects all non-empty cells that don't contain a formula.
4. Choose Format, Cells, and click the Protection tab.
5. Remove the check mark next to the Locked option. Click OK.
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.
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.
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.
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.
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).
Here's how to hide the formula cells:
1. Select Edit, Go To (or press Ctrl-G or F5) to bring up the Go To dialog box.
2. Click the Special button to show the Go To Special dialog box.
3. Choose the Formulas option, and make sure the four check boxes are all checked.
4. Click OK, and Excel selects all cells that contain a formula.
5. Choose Format, Cells, and click the Protection tab.
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.
Here's how to unlock the non-formula cells:
1. Repeat Steps 1 and 2 above to show the Go To Special dialog box.
2. Choose the Constants option, and make sure the four check boxes are all checked.
3. Click OK, and Excel selects all non-empty cells that don't contain a formula.
4. Choose Format, Cells, and click the Protection tab.
5. Remove the check mark next to the Locked option. Click OK.
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.
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.
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.
Zap the Numbers, Keep the Formulas
Zap the Numbers, Keep the Formulas
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.
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.
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:
1. Select Edit, Go To (or press Ctrl-G or F5) to bring up the Go To dialog box.
2. Click the Special button to show the Go To Special dialog box.
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).
4. Click OK, and Excel will select all of the non-formula cells that contain a value.
5. Press the Delete key, and those numbers are history--but the formulas remain intact.
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.
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.
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:
1. Select Edit, Go To (or press Ctrl-G or F5) to bring up the Go To dialog box.
2. Click the Special button to show the Go To Special dialog box.
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).
4. Click OK, and Excel will select all of the non-formula cells that contain a value.
5. Press the Delete key, and those numbers are history--but the formulas remain intact.
What's the Current Date or Time?
What's the Current Date or Time?
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.
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.
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.
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.
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.
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.
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.
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.
Quick Math Without Formulas
Quick Math Without Formulas
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.
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:
1. Enter 1.05 into any blank cell. This is the "multiplier" that will increase the prices by 5 percent.
2. Select the cell you used in Step 1, and choose Edit, Copy or press Ctrl-C.
3. Select the range of values to be changed, and choose Edit, Paste Special to display the Paste Special dialog box.
4. Choose the Multiply option and click OK.
Voilà! The values are changed in an instant. You can then delete the cell that contains the 1.05 multiplier.
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.
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.
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:
1. Enter 1.05 into any blank cell. This is the "multiplier" that will increase the prices by 5 percent.
2. Select the cell you used in Step 1, and choose Edit, Copy or press Ctrl-C.
3. Select the range of values to be changed, and choose Edit, Paste Special to display the Paste Special dialog box.
4. Choose the Multiply option and click OK.
Voilà! The values are changed in an instant. You can then delete the cell that contains the 1.05 multiplier.
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.
Shade Alternate Rows
Shade Alternate Rows
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.
Here's how to do it:
1. Highlight the range of cells or rows or columns that you want to format.
2. Choose Format, Conditional Formatting to display the Conditional Formatting dialog box.
3. Select Formula Is from the first drop-down list box, and enter =MOD(ROW(),2)=0 in the second box.
4. Click the Format button to bring up the Format Cells dialog box.
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.
6. Click OK twice to return to your worksheet.
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.
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.
Here's how to do it:
1. Highlight the range of cells or rows or columns that you want to format.
2. Choose Format, Conditional Formatting to display the Conditional Formatting dialog box.
3. Select Formula Is from the first drop-down list box, and enter =MOD(ROW(),2)=0 in the second box.
4. Click the Format button to bring up the Format Cells dialog box.
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.
6. Click OK twice to return to your worksheet.
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.
Add a Drop-Down List
Add a Drop-Down List
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!
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.
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.
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.)
3. Choose Data, Validation to display the Data Validation dialog box.
4. Click the Settings tab.
5. In the Allow field, select List.
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.
7. Click OK.
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.
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.
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.
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!
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.
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.
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.)
3. Choose Data, Validation to display the Data Validation dialog box.
4. Click the Settings tab.
5. In the Allow field, select List.
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.
7. Click OK.
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.
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.
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.
Auto start Macro to go to today's date in worksheet
Try this macroIf you have date's in column A then this example will select the
Ron de Bruin posted on Monday, January 14, 2008 2:19 PM
Try this macro
If you have date's in column A then this example will select the cell with today's date.
Sub Find_Todays_Date()
Dim FindString As Date
Dim Rng As Range
FindString = CLng(Date)
With Sheets("Sheet1").Range("A:A")
Set Rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
Application.Goto Rng, True
Else
MsgBox "Nothing found"
End If
End With
End Sub
Ron de Bruin posted on Monday, January 14, 2008 2:19 PM
Try this macro
If you have date's in column A then this example will select the cell with today's date.
Sub Find_Todays_Date()
Dim FindString As Date
Dim Rng As Range
FindString = CLng(Date)
With Sheets("Sheet1").Range("A:A")
Set Rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
Application.Goto Rng, True
Else
MsgBox "Nothing found"
End If
End With
End Sub
Sunday, May 30, 2010
Removing Extra Breaks in Word Documents
Removing Extra Breaks in Word Documents
Follow these steps:
1. Press Ctrl + H to open the Find and Replace dialog box
2. In the first box, enter ^p^p (the "p" must be lower case)
3. In the second box, enter ^p
4. Click Replace All
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.
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:
5. Press Ctrl + H to open the Find and Replace dialog box
6. In the first box, enter ^l (the "l" must be lower case)
7. In the second box, enter ^p
8. Click Replace All
You can then replace double breaks as necessary.
Follow these steps:
1. Press Ctrl + H to open the Find and Replace dialog box
2. In the first box, enter ^p^p (the "p" must be lower case)
3. In the second box, enter ^p
4. Click Replace All
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.
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:
5. Press Ctrl + H to open the Find and Replace dialog box
6. In the first box, enter ^l (the "l" must be lower case)
7. In the second box, enter ^p
8. Click Replace All
You can then replace double breaks as necessary.
Removing Extra Breaks in Word Documents
Removing Extra Breaks in Word Documents
Follow these steps:
1. Press Ctrl + H to open the Find and Replace dialog box
2. In the first box, enter ^p^p (the "p" must be lower case)
3. In the second box, enter ^p
4. Click Replace All
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.
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:
5. Press Ctrl + H to open the Find and Replace dialog box
6. In the first box, enter ^l (the "l" must be lower case)
7. In the second box, enter ^p
8. Click Replace All
You can then replace double breaks as necessary.
Follow these steps:
1. Press Ctrl + H to open the Find and Replace dialog box
2. In the first box, enter ^p^p (the "p" must be lower case)
3. In the second box, enter ^p
4. Click Replace All
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.
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:
5. Press Ctrl + H to open the Find and Replace dialog box
6. In the first box, enter ^l (the "l" must be lower case)
7. In the second box, enter ^p
8. Click Replace All
You can then replace double breaks as necessary.
Thursday, May 27, 2010
* Selecting Synonyms
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.
* Remove All Formatting
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.
* Double-Click to Format Bullets and Numbering
To open the Bullets and Numbering dialog box and adjust the numbering scheme, just double-click one of the
numbers or bullets in the list.
* Sharing Documents with Non-Word Users
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.
* Customizing Toolbars
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.
* Screen Capture
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).
*
Viewing the Document
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.
Hold the mouse over the other buttons to find the Normal, Web Layout, Print Layout, and Outline Views.
*
Learn to Use Undo
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.
*
Templates
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.
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.
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.
*
Document Zoom
Click View…Zoom… to choose the document zoom percentage. There is also a zoom tool on the standard toolbar.
*
Selecting Text
ü To select a word, double-click the left mouse button on the word
ü To select a sentence, hold down CTRL and single-click the left mouse button on the sentence.
ü 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.
ü To select a paragraph, triple-click the left mouse button in the paragraphs
ü To select the whole document, hold down CTRL and A, or triple-click the left mouse button in the left margin of the screen.
*
Using AutoCorrect
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.
*
Find and Replace
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.
*
Selective Word Count
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.
*
Shrinking a Document to Fit
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.
*
Create A Desktop Shortcut To A Document
Click File…Save As…Click the drop down arrow and choose Desktop.
*
Removing Formatting for a Portion of Text
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.
*
Toggle through CASE Changes
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.
*
Standardized Fonts
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.
*
Date Code
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.”
*
Insert Pictures and Clipart
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.
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.
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.
*
Other Text Effects
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.
Text Box: Yet another way to add text.
*
Animated Text
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.
*
Adding Borders
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.
*
Getting Rid of Nuisances!
How do I get rid of these ¶¶¶¶? Click Tools … Options…Click the "Views" tab sheet. Under "Nonprinting Characters" unselect "Paragraph Marks".
*
How do I get rid of overtype? Choose Tools…Options…Edit tab…uncheck Overtype Mode.
* Shortcut Keys in Word
CTRL + A Selects all in the current document.
CTRL + B Bold text.
CTRL + C Copies the item or text to the Clipboard + can be pasted using CTRL + V.
CTRL + D Displays the Font dialogue box.
CTRL + E Centre Alignment.
CTRL + F Displays the Find dialog box, to search the current document.
CTRL + G Displays the Go to dialog box, to go to a specific location in the current document.
CTRL + H Displays the Replace dialogue box.
CTRL + I Italic text.
CTRL + J Full Justification.
CTRL + K Create Hyperlink
CTRL + L Left Alignment
CTRL + M Tab
CTRL + N Creates a new document.
CTRL + O Displays the Open File dialogue box.
CTRL + P Displays the Print dialog box.
CTRL + R Right Alignment.
CTRL + S Displays the Save dialog box.
CTRL + U Underline text
CTRL + V Pastes the copied item or text from the Clipboard into the current position in the document.
CTRL + X Cuts the item or text selected to the Clipboard.
CTRL + Y Redo the last undone action.
CTRL + Z Undoes the last action.
CTRL + ENTER Insert Page Break.
CTRL + F2 Show Print preview.
CTRL + F4 Closes the active document window.
CTRL + F6 Opens the next document window.
CTRL + 1 Single spaces text
CTRL + 2 Double spaces text
CTRL + 3 Triple spaces text
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.
* Remove All Formatting
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.
* Double-Click to Format Bullets and Numbering
To open the Bullets and Numbering dialog box and adjust the numbering scheme, just double-click one of the
numbers or bullets in the list.
* Sharing Documents with Non-Word Users
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.
* Customizing Toolbars
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.
* Screen Capture
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).
*
Viewing the Document
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.
Hold the mouse over the other buttons to find the Normal, Web Layout, Print Layout, and Outline Views.
*
Learn to Use Undo
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.
*
Templates
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.
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.
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.
*
Document Zoom
Click View…Zoom… to choose the document zoom percentage. There is also a zoom tool on the standard toolbar.
*
Selecting Text
ü To select a word, double-click the left mouse button on the word
ü To select a sentence, hold down CTRL and single-click the left mouse button on the sentence.
ü 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.
ü To select a paragraph, triple-click the left mouse button in the paragraphs
ü To select the whole document, hold down CTRL and A, or triple-click the left mouse button in the left margin of the screen.
*
Using AutoCorrect
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.
*
Find and Replace
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.
*
Selective Word Count
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.
*
Shrinking a Document to Fit
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.
*
Create A Desktop Shortcut To A Document
Click File…Save As…Click the drop down arrow and choose Desktop.
*
Removing Formatting for a Portion of Text
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.
*
Toggle through CASE Changes
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.
*
Standardized Fonts
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.
*
Date Code
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.”
*
Insert Pictures and Clipart
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.
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.
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.
*
Other Text Effects
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.
Text Box: Yet another way to add text.
*
Animated Text
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.
*
Adding Borders
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.
*
Getting Rid of Nuisances!
How do I get rid of these ¶¶¶¶? Click Tools … Options…Click the "Views" tab sheet. Under "Nonprinting Characters" unselect "Paragraph Marks".
*
How do I get rid of overtype? Choose Tools…Options…Edit tab…uncheck Overtype Mode.
* Shortcut Keys in Word
CTRL + A Selects all in the current document.
CTRL + B Bold text.
CTRL + C Copies the item or text to the Clipboard + can be pasted using CTRL + V.
CTRL + D Displays the Font dialogue box.
CTRL + E Centre Alignment.
CTRL + F Displays the Find dialog box, to search the current document.
CTRL + G Displays the Go to dialog box, to go to a specific location in the current document.
CTRL + H Displays the Replace dialogue box.
CTRL + I Italic text.
CTRL + J Full Justification.
CTRL + K Create Hyperlink
CTRL + L Left Alignment
CTRL + M Tab
CTRL + N Creates a new document.
CTRL + O Displays the Open File dialogue box.
CTRL + P Displays the Print dialog box.
CTRL + R Right Alignment.
CTRL + S Displays the Save dialog box.
CTRL + U Underline text
CTRL + V Pastes the copied item or text from the Clipboard into the current position in the document.
CTRL + X Cuts the item or text selected to the Clipboard.
CTRL + Y Redo the last undone action.
CTRL + Z Undoes the last action.
CTRL + ENTER Insert Page Break.
CTRL + F2 Show Print preview.
CTRL + F4 Closes the active document window.
CTRL + F6 Opens the next document window.
CTRL + 1 Single spaces text
CTRL + 2 Double spaces text
CTRL + 3 Triple spaces text
#
Setting the Default Text Style
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:
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.
#
Changing A Design Template
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.
#
Using More than One Guide
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.
#
Creating Pages with Slides and Descriptive Text
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.
#
Building Presentations for Distribution to Others
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:
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.
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.
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!
#
Easily Changing from Caps to Lower Case (or Vice Versa)
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!
#
Nudging Objects
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.
#
Saving Across Multiple Diskettes
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.
#
Draw A Line Perfectly Horizontal or Vertical
Depress the Shift key while dragging to create your line.
#
Draw A Perfect Square
Depress the Shift key while dragging to create your square.
#
Draw A Perfect Circle
Depress the Shift key while dragging to create your circle.
#
Quickly Access the Slide Master
Click on the Slide View icon (at the top-left of the screen), while depressing the Shift key.
#
Create A New Design Template
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.
#
Preview A Presentation in Black and White
Click on View…Black and White.
#
Send to the Back or Bring Forward an Object
Right click on the object, select order, and choose the desired command.
#
Insert the Copyright Symbol
To insert the copyright © symbol, enter (c)
To insert the Trademark ™ symbol enter (tm)
To insert the registered ® symbol enter (r)
#
Saving Shows
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.
#
Jumping to Screens
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.
#
Hiding Screens
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.
#
To Go to the First Slide or Last Slide
Ctrl+Home will take you to the first slide in a presentation, Ctrl+End will take you to the last slide.
#
Toolbar Tips
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.
#
Setting up the Show
To set up the presentation to run continuously, click Slide Show…Set Up Show. Check ‘loop continuously until ESC’ and click OK.
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.
To record your own narration for the presentation click Slide Show…Record Narration.
#
Printing the Presentation
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.
# Scrolling Credits
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.
#
Keyboard Shortcuts
Insert a new slide
CTRL+M
Switch to the next pane (clockwise)
F6
Switch to the previous pane (counterclockwise)
SHIFT+F6
Make a duplicate of the current slide
CTRL+D
Start a slide show
F5
Promote a paragraph
ALT+SHIFT+LEFT ARROW
Demote a paragraph
ALT+SHIFT+RIGHT ARROW
Apply subscript formatting
CTRL+EQUAL SIGN (=)
Apply superscript formatting
CTRL+PLUS SIGN (+)
Open the Font dialog box
CTRL+T
Repeat your last action
F4 or CTRL+Y
Find
CTRL+F
View guides
CTRL+G
Delete a word
CTRL+BACKSPACE
Capitalize
SHIFT+F3
Bold
CTRL+B
Italicize
CTRL+I
Insert a hyperlink
CTRL+K
Select all
CTRL+A
Copy
CTRL+C
Paste
CTRL+V
Undo
CTRL+Z
Save
CTRL+S
Print
CTRL+P
Open
CTRL+O
Setting the Default Text Style
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:
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.
#
Changing A Design Template
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.
#
Using More than One Guide
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.
#
Creating Pages with Slides and Descriptive Text
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.
#
Building Presentations for Distribution to Others
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:
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.
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.
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!
#
Easily Changing from Caps to Lower Case (or Vice Versa)
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!
#
Nudging Objects
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.
#
Saving Across Multiple Diskettes
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.
#
Draw A Line Perfectly Horizontal or Vertical
Depress the Shift key while dragging to create your line.
#
Draw A Perfect Square
Depress the Shift key while dragging to create your square.
#
Draw A Perfect Circle
Depress the Shift key while dragging to create your circle.
#
Quickly Access the Slide Master
Click on the Slide View icon (at the top-left of the screen), while depressing the Shift key.
#
Create A New Design Template
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.
#
Preview A Presentation in Black and White
Click on View…Black and White.
#
Send to the Back or Bring Forward an Object
Right click on the object, select order, and choose the desired command.
#
Insert the Copyright Symbol
To insert the copyright © symbol, enter (c)
To insert the Trademark ™ symbol enter (tm)
To insert the registered ® symbol enter (r)
#
Saving Shows
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.
#
Jumping to Screens
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.
#
Hiding Screens
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.
#
To Go to the First Slide or Last Slide
Ctrl+Home will take you to the first slide in a presentation, Ctrl+End will take you to the last slide.
#
Toolbar Tips
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.
#
Setting up the Show
To set up the presentation to run continuously, click Slide Show…Set Up Show. Check ‘loop continuously until ESC’ and click OK.
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.
To record your own narration for the presentation click Slide Show…Record Narration.
#
Printing the Presentation
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.
# Scrolling Credits
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.
#
Keyboard Shortcuts
Insert a new slide
CTRL+M
Switch to the next pane (clockwise)
F6
Switch to the previous pane (counterclockwise)
SHIFT+F6
Make a duplicate of the current slide
CTRL+D
Start a slide show
F5
Promote a paragraph
ALT+SHIFT+LEFT ARROW
Demote a paragraph
ALT+SHIFT+RIGHT ARROW
Apply subscript formatting
CTRL+EQUAL SIGN (=)
Apply superscript formatting
CTRL+PLUS SIGN (+)
Open the Font dialog box
CTRL+T
Repeat your last action
F4 or CTRL+Y
Find
CTRL+F
View guides
CTRL+G
Delete a word
CTRL+BACKSPACE
Capitalize
SHIFT+F3
Bold
CTRL+B
Italicize
CTRL+I
Insert a hyperlink
CTRL+K
Select all
CTRL+A
Copy
CTRL+C
Paste
CTRL+V
Undo
CTRL+Z
Save
CTRL+S
CTRL+P
Open
CTRL+O
* Generate Random Numbers
Need to create random numbers? You can do it in Excel.
To generate a number between 0 and 1, type =RAND() in a cell.
To generate a number between 1 and 100, type =RAND()*100.
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.
* No Formula, Please
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.
* Insert Time/Date in Excel
Try these keyboard shortcuts to insert the time/date in an Excel spreadsheet:
Current date: Press CTRL+SEMICOLON
Current time: Press CTRL+SHIFT+ SEMICOLON
Current date and time: Press CTRL+ SEMICOLON then SPACE then CTRL+SHIFT+ SEMICOLON
* Hide Worksheets in Excel 2002
To hide Excel worksheets to prevent unwanted changes, Select the worksheet, click Format...Sheet...Hide.
* Color-Coding Excel Sheet Tabs in Excel 2002
In Excel 2002, color-code sheet tabs for easier identification or grouping.
Select the sheet(s) by holding down the CTRL key and clicking the tabs.
Click Format...Sheet...Tab Color. You can also right-click the sheet tab and choose
click Tab Color.
* Pasting an Excel Table and Its Formatting into Word
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.
* Go To
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.
* Shortcut Keys
To see a complete list of shortcut keys in Excel, press F1 on the keyboard and type shortcut keys in the search box.
* Customizing Toolbars
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.
* Quick Graphs
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.
* Basic Keyboard Shortcuts
F1 Help
F2 Edit current Cell
F5 Goto
F7 Spell Check
F12 Save file as
CTRL + A Select entire worksheet.
CTRL + B Toggle Bold Text.
CTRL + C Copies the item or items selected to the Clipboard and can be pasted using CTRL + V.
CTRL + F Displays the Find dialog box.
CTRL + H Displays the Replace dialog box.
Need to create random numbers? You can do it in Excel.
To generate a number between 0 and 1, type =RAND() in a cell.
To generate a number between 1 and 100, type =RAND()*100.
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.
* No Formula, Please
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.
* Insert Time/Date in Excel
Try these keyboard shortcuts to insert the time/date in an Excel spreadsheet:
Current date: Press CTRL+SEMICOLON
Current time: Press CTRL+SHIFT+ SEMICOLON
Current date and time: Press CTRL+ SEMICOLON then SPACE then CTRL+SHIFT+ SEMICOLON
* Hide Worksheets in Excel 2002
To hide Excel worksheets to prevent unwanted changes, Select the worksheet, click Format...Sheet...Hide.
* Color-Coding Excel Sheet Tabs in Excel 2002
In Excel 2002, color-code sheet tabs for easier identification or grouping.
Select the sheet(s) by holding down the CTRL key and clicking the tabs.
Click Format...Sheet...Tab Color. You can also right-click the sheet tab and choose
click Tab Color.
* Pasting an Excel Table and Its Formatting into Word
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.
* Go To
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.
* Shortcut Keys
To see a complete list of shortcut keys in Excel, press F1 on the keyboard and type shortcut keys in the search box.
* Customizing Toolbars
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.
* Quick Graphs
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.
* Basic Keyboard Shortcuts
F1 Help
F2 Edit current Cell
F5 Goto
F7 Spell Check
F12 Save file as
CTRL + A Select entire worksheet.
CTRL + B Toggle Bold Text.
CTRL + C Copies the item or items selected to the Clipboard and can be pasted using CTRL + V.
CTRL + F Displays the Find dialog box.
CTRL + H Displays the Replace dialog box.
Excel Tips, Tricks and Shortcuts for Microsoft Excel
Excel Tips, Tricks and Shortcuts for Microsoft Excel
Shortcut Keys
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".
Quick Help
To get quick help on any menu item push Shift+F1 and click the menu item
Insert Today's Date
To insert Today's date push Ctrl+; (semicolon)
Insert Current Time
To insert the current time push Ctrl+Shift+: (Colon)
Show the Paste Function (Function Wizard)
Push Ctrl+F3
Show the GoTo dialog
Push F5
Show the Paste Names dialog
Push F3. This will only work if you have named ranges.
Name a Range
To name a selected range, click in the "Name box" (far left on the formula bar) and type a one word name.
Go To a Named Range
To go to a named range select it from the "Name box" (far left of the formula bar). Or push F5.
Edit a Named Range
To delete or edit a named range go to Insert>Name>Define or Push Ctrl+F3.
Headings as Range Names
Highlight your range including the headings and go to Insert>Name>Create or push Ctrl+Shift+F3.
Named Formula
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.
Named Range List
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.
Nested Formulas
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.
Debugging Formulas
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.
Personal Help
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.
Different Help
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.
Customizing Toolbars
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.
Quick Charts
To create quick charts, click anywhere within your data and push F11.
Worksheet Template
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.
Secret Menu
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.
Secret Menu 2
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.
Quick Cell Move
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.
Quick Cell Copy
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.
Change Formulas to Values
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.
Quick List
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).
Fill Blank Cells Within a List
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.
Auto Fill
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.
Custom Auto Fill
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.
Adding Text to Formulas
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).
Adding Hidden Text to Formulas
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.
Custom Format
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.
No More Chart Gaps
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.
My List
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.
Remove Blank Rows
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".
Sort Out Blank Rows
The quickest way to remove all blank rows is to select you range then go to Data>Sort.
See Formula cells
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.
En Masse Changes
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".
En Masse Changes 2
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".
Worksheet Copy
Select the sheet name tab then hold down your Ctrl key and simply drag it to the position you want it.
Paste Reference
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"
Absolute/Relative Toggle
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.
Repeat
To repeat an operation push F4
Undo
To undo an operation push Ctrl+Z
Linked Picture
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
Run a Macro by Clicking a Cell
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".
Non Formula Result
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".
Reduce File Size
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.
Cell Navigation
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.
Quick Formula Syntax
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.
How to copy formulas without the reference changing
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 =
How to copy and transpose formulas without the reference changing
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 =
Turn a List Upside-Down
1. Copy the list to another location using Copy, Edit>Paste Special>Value.
2. Now select all data in the list, go to Tools>Options>Custom Lists.
3. Ensure the list address is in the "Import list from cells:" and click "Import".
4. Now go back to the column next to your list and in the top cell place the LAST entry from your list.
5. Now in the cell below, place the second last entry.
6. Select both cells and double click on the Fill Handle (small black square bottom right).
The list should now be reversed. You could now also sort you original list using Data>Sort>Options, nominate your list then sort!
Formula Errors
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.
Entering Named Ranges Into Formulas
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.
Optional Function Arguments
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.
Sort by more than 3 Columns
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
1. Select all of Columns A:E
2. Go to Data>Sort> sort by C then by D then by E
3. Click Sort
4. Now again with Columns A:E selected
5. Go to Data>Sort> sort by A then by B
6. Click Sort
Printing Workbooks
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.
Shortcut Keys
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".
Quick Help
To get quick help on any menu item push Shift+F1 and click the menu item
Insert Today's Date
To insert Today's date push Ctrl+; (semicolon)
Insert Current Time
To insert the current time push Ctrl+Shift+: (Colon)
Show the Paste Function (Function Wizard)
Push Ctrl+F3
Show the GoTo dialog
Push F5
Show the Paste Names dialog
Push F3. This will only work if you have named ranges.
Name a Range
To name a selected range, click in the "Name box" (far left on the formula bar) and type a one word name.
Go To a Named Range
To go to a named range select it from the "Name box" (far left of the formula bar). Or push F5.
Edit a Named Range
To delete or edit a named range go to Insert>Name>Define or Push Ctrl+F3.
Headings as Range Names
Highlight your range including the headings and go to Insert>Name>Create or push Ctrl+Shift+F3.
Named Formula
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.
Named Range List
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.
Nested Formulas
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.
Debugging Formulas
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.
Personal Help
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.
Different Help
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.
Customizing Toolbars
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.
Quick Charts
To create quick charts, click anywhere within your data and push F11.
Worksheet Template
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.
Secret Menu
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.
Secret Menu 2
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.
Quick Cell Move
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.
Quick Cell Copy
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.
Change Formulas to Values
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.
Quick List
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).
Fill Blank Cells Within a List
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.
Auto Fill
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.
Custom Auto Fill
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.
Adding Text to Formulas
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).
Adding Hidden Text to Formulas
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.
Custom Format
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.
No More Chart Gaps
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.
My List
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.
Remove Blank Rows
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".
Sort Out Blank Rows
The quickest way to remove all blank rows is to select you range then go to Data>Sort.
See Formula cells
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.
En Masse Changes
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".
En Masse Changes 2
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".
Worksheet Copy
Select the sheet name tab then hold down your Ctrl key and simply drag it to the position you want it.
Paste Reference
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"
Absolute/Relative Toggle
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.
Repeat
To repeat an operation push F4
Undo
To undo an operation push Ctrl+Z
Linked Picture
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
Run a Macro by Clicking a Cell
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".
Non Formula Result
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".
Reduce File Size
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.
Cell Navigation
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.
Quick Formula Syntax
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.
How to copy formulas without the reference changing
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 =
How to copy and transpose formulas without the reference changing
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 =
Turn a List Upside-Down
1. Copy the list to another location using Copy, Edit>Paste Special>Value.
2. Now select all data in the list, go to Tools>Options>Custom Lists.
3. Ensure the list address is in the "Import list from cells:" and click "Import".
4. Now go back to the column next to your list and in the top cell place the LAST entry from your list.
5. Now in the cell below, place the second last entry.
6. Select both cells and double click on the Fill Handle (small black square bottom right).
The list should now be reversed. You could now also sort you original list using Data>Sort>Options, nominate your list then sort!
Formula Errors
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.
Entering Named Ranges Into Formulas
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.
Optional Function Arguments
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.
Sort by more than 3 Columns
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
1. Select all of Columns A:E
2. Go to Data>Sort> sort by C then by D then by E
3. Click Sort
4. Now again with Columns A:E selected
5. Go to Data>Sort> sort by A then by B
6. Click Sort
Printing Workbooks
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.
display formulas so you can troubleshoot or debug them
Press [Ctrl]~ to display formulas so you can troubleshoot or debug them
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]~.
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]~.
Quickly Delete Blank Rows From A Long List (Excel 2003, Excel 2007)
Quickly Delete Blank Rows From A Long List (Excel 2003, Excel 2007)
If you have a long list of data that contains blank rows that you want to delete, you have several options.
* 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.
* 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.
* You could filter the list for blanks, select these rows (i.e. visible cells) and delete them.
* You could use a little-known shortcut to quickly select the blanks in your list and delete them.
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.
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.
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 .
2. Now, select the cells in one column from the top of your list to the bottom.
3. Make sure that all the blank cells in this selected range are the rows you want to delete.
4. Press the F5 key on your keyboard (or select Edit, Goto).
5. Click the Special button.
6. Click the Blanks option and click OK. This will select all blank cells in the range you had previously selected.
7. Now choose Edit, Delete, select the Entire Row option and click OK.
If you have a long list of data that contains blank rows that you want to delete, you have several options.
* 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.
* 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.
* You could filter the list for blanks, select these rows (i.e. visible cells) and delete them.
* You could use a little-known shortcut to quickly select the blanks in your list and delete them.
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.
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.
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 .
2. Now, select the cells in one column from the top of your list to the bottom.
3. Make sure that all the blank cells in this selected range are the rows you want to delete.
4. Press the F5 key on your keyboard (or select Edit, Goto).
5. Click the Special button.
6. Click the Blanks option and click OK. This will select all blank cells in the range you had previously selected.
7. Now choose Edit, Delete, select the Entire Row option and click OK.