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
April 13, 2014 |

Changing the Size of the Excel Formula Bar

Changing the height of  the Microsoft Excel Formula Bar can make it a little easier to read your formulas and is very easy to do:

  1. Place your mouse pointer over white divider strip below formula bar.
  2. When your pointer changes to an open up/ down arrow,  hold down the left mouse button and simply drag to your required height.
  3. The Formula Bar will remain this height until you repeat the steps and change it back.

Tags:
| Posted in Excel | Comments Off on Changing the Size of the Excel Formula Bar
March 17, 2013 |

New client, new week

This week we begin work with Jesuit Social Services http://www.jss.org.au/, delivering in house Excel classes.

Barb and I look forward to getting to you know you all and hope you find lots of useful tips and hints here!

 

 

| Posted in Excel, Uncategorized | Comments Off on New client, new week
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

December 6, 2010 |

How to Validate Data in Microsoft Excel

Barb was out at Eastern Health on Friday doing an Intermediate Excel course.  The attendees wanted to do some work on data validation and while the manual contained the basics of validation, they needed more.  So Barbie very graciously wrote this 9 page handout over the weekend, which I share with you all now. Data Validation in ExcelAh, but what is data validation you ask? Well, Data Validation allows you to restrict the data entered into a range of cells in an Excel worksheet. For example, if you have created a worksheet that others use, you need to ensure that only data that matches your requirements is entered into those cells, perhaps a range of numbers (e.g. between 10 and 20), or only specific text (e.g., Mr., Mrs, or Miss).

Tags:
| Posted in Excel | No Comments »
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 »
May 20, 2010 |

Spellcheck in Excel

While Excel is primarily for numeric data , it may also contain a fair amount of text, so there will be times that you need to spell check.  First you need to make a choice on what exactly needs checking:

  • If you have the formula bar selected Excel will only check the contents of the formula bar, leaving the rest of the worksheet untouched.
  • If the spell check is started with a range of cells selected, only the cells in the selected range will be checked.
  • To check an entire worksheet, select any cell on the sheet and start the spell check.
  • Finally, to check multiple worksheets, first select the sheet tabs for all of the worksheets. (Use the Shift key or Ctrl key with the mouse to select all the sheets).

Once if you have made that decision, it is basically the same as in Word, you either press the F7 key, click the spellcheck button on the Standard Toolbar in versions of word lower than 2007 or click the Spelling button on the Review tab of 2007.Happy checking!

| Posted in Excel | No Comments »