December 6, 2017 |

Get the most from Excel’s AutoComplete

The AutoComplete feature anticipates the value you might be entering in a list by comparing it with previous entries in a column.  Let’s say I am typing a list and it looks something like this:

apples
oranges
pears
lemons
oranges

Because I had previously typed the word ‘oranges’, as if I started typing ‘oranges’ again (in fact as soon as I type the ‘o’), it will prompt me with the entire word. This is courtesy of the AutoComplete feature which recognizes input based on what  was typed previously.  If you wish to accept the entry simply press the Enter key.  If you don’t want the AutoComplete value being offered, just keep typing the new value. This feature’s only requirement is that the values be contiguous (e.g. one after another in the list, there cannot be any breaks/empty cells).

AutoComplete Tip 2.

Instead of allowing AutoComplete to finish a value, you can choose to be offered a pick list which is based on the previous values in a single column. Using the same example as above of fruit, you would simply go to the next empty cell and then hold down the ALT key and press the down cursor arrow.  This will display a pick list of values (items) based on what you have previously typed.

Autocomplete

Simply click on the entry you would like to use and then press either the Tab or Enter key to move on and keep working.

Tags: ,
| Posted in Excel, Microsoft Office | Comments Off on Get the most from Excel’s AutoComplete
November 26, 2012 |

Taking Control of Repetitive Text in Excel

This would suit the situation where you find yourself keying in lots of data and then want to re-use text without retyping it; perhaps it didn’t appear as an AutoComplete, or you do not want to begin typing the item to activate AutoComplete.

These instructions also assume you have a list of text above your insert point and no empty cell above you (or it doesn’t work).

1.  Hold down the Alt key and then press the down cursor arrow.
2.  You’ll find that a drop down list of the text already entered into the column will appear.

3.  Let go of the Alt key and use the cursor down arrow key to move through the list to stop on the one you want, or just mouse click on it.

 

November 7, 2012 |

Changing Default Font/Size in MS Excel

Here’s how to to change the default font in Microsoft Excel.  The first explanation is for 2007 users and the second for 2010 users.

V2007 Users

1.  Click the Office Button and then click  Excel Options.

2.  In the Popular category of options locate the “When creating new workbooks” section.

3.  Make your choices for  default font and size and when finished click OK.

V2010 Users

1.  Click the File Menu and choose Excel Options.

2.  In the General category of options locate the “When creating new workbooks” section.

3.  Make your choices for  default font and size and when finished click OK.

Tags: , ,
| Posted in Excel | No Comments »
October 4, 2011 |

Merging Excel Data with Word

Barb recently came across the problem with a user who wanted to merge an Excel file with a Word document in order to do a mail merge. The problem they found was that if you merge direct from Excel, date and currency formatting that you have applied in Excel is completely ignored by Word, e.g.

  • If you have formatted your date to show as 3 Oct 2011 in Excel, Word converts it show as 10/3/11.
  • If you have formatted currency to show as $3.50, Word converts is to show as 3.5

The only way to keep your formatting is to copy the data into a Word document and when pasted your date and currency formatting is retained but as they notes are quite comprehensive, we offer them here as a pdf download: Merging Excel with Word.

Tags: , ,
| Posted in Excel, Word | No Comments »
September 26, 2011 |

Course Reviews: Site City of Boroondara

The City of Boroondara are a local council for whom I have been doing the in-house IT training since 2000.  Each time we run a class the attendees have to fill in an online evaluation.  One of the joys of this job is you are always being evaluated on each day’s work (in writing and by strangers); and there would not be too many jobs where that is a daily occurrence.

Here’s a couple of feedback forms from the other month, sent to me by Sangita Parsot, the Training Coordinator at Boroondara.

Course name: Introduction to Excel

Course date: Tuesday, 30 August 2011

Course pace: Just right

Course contents: Just right

Presentation of course: Excellent

Quality of venue: Very good

Knowledgeable trainer: Yes, Angela was extremely knowledgeable and friendly.

Training style suitable: Yes, relaxed and inclusive, so making it easy to learn.

Anticipated knowledge: Heaps

Suggested improvements: None, I loved it, thanks

Course name: Introduction to Excel

Course date: Tuesday, 30 August 2011

Course pace: Just right

Course contents: Just right

Presentation of course: Excellent

Quality of venue: Very good

Knowledgeable trainer: Angela was extremely knowledgeable and was able to answer all queries.

Training style suitable: Yes, her style was very relaxed, making everyone feel comfortable and making the training aims achievable.

Anticipated knowledge: Yes, and more.

Suggested improvements: It was great, thank you.

Course name: Introduction to Excel

Course date: Tuesday, 30 August 2011

Course pace: Just right

Course contents: Just right

Presentation of course: Excellent

Quality of venue: Excellent

Knowledgeable trainer: Articulate and knowleable

Training style suitable: Yes

Anticipated knowledge: Yes

Suggested improvements: Nil

September 27, 2010 |

Quick Guide to Excel Formulas

Barb Stapleton who works with me wrote up this a great little guide on using Excel Formulas.  Help yourself Structuring Excel Formulas (2)!

Tags: ,
| Posted in Excel | 2 Comments »