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.

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.

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.



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


Switch to the next pane (clockwise)


Switch to the previous pane (counterclockwise)


Make a duplicate of the current slide


Start a slide show


Promote a paragraph


Demote a paragraph


Apply subscript formatting


Apply superscript formatting


Open the Font dialog box


Repeat your last action

F4 or CTRL+Y



View guides


Delete a word








Insert a hyperlink


Select all













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

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.

To repeat an operation push F4

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]~.

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.