(The New) Office Coach

Making gmail the default email client (Firefox Windows)

2009-11-14T14:43:00.001+11:00

This is the most comprehensive article for gmail and Firefox (Windows)

in reference to: http://email.about.com/od/gmailtips/qt/et_default_prog.htm (view on Google Sidewiki)

When you need quick shortcuts to SendTo

2009-11-11T12:17:00.002+11:00

Sometimes, you just need a quick shortcut to send files and folders to other destination folders or programs (like an editor). Add shortcuts to the SendTo Folder

Comprehensive list of fonts installed with Office 2007

2009-11-05T09:46:00.001+11:00

Note tthat there is Cambria, Calibri, Corbel, Candara

in reference to: http://support.microsoft.com/kb/924623 (view on Google Sidewiki)

Google Sidewiki for bloggers with Blogger.com accounts

2009-10-30T17:41:00.001+11:00

This is really useful and simple. If you come across something to add to your blog, just write a note on the Sidewiki and in the same side panel, add the remark and the url to your blog.

in reference to: http://www.labnol.org/internet/sidewiki-comments-bookmarklet/10847/ (view on Google Sidewiki)

Be careful

2009-10-30T17:38:00.001+11:00

Don't click on the "OK" button unless you have made a backup copy of your .pst file. The Ok button runs the process without further prompting. Always nice to have a backup just in case.

in reference to: http://www.kopf.com.br/outlook/ (view on Google Sidewiki)

Classnotes of the 28th October 2009 Access session

2009-10-28T19:56:00.001+11:00

A welcome to all from the Monash University SDU class of the 28th October 2009. As I was teaching, I made some screenshots and wrote some notes. I’ve put them here.

I spoke of the free Microsoft online book – Microsoft Access 2000: Building Applications with Forms and Reports

I also mentioned the McFedries New Book: Microsoft Office Access 2007 Forms, Reports, and Queries

Enjoy!

VBA Editor utilities

2009-10-21T22:12:00.002+11:00

Working on an Excel VBA project for a client at the moment. Again, really relying on MZ-Tools for VBA to help write code. And Jim Rech's VBE Bookmarks utility sure makes bookmarks more useful than the standard VB IDE's Next Bookmark method.

Interesting Spreadsheet Reading

2009-10-20T22:08:00.004+11:00

Just came across the EUropean SPreadsheet Risks Interest Group. They offer free discussion and research papers on their website. There is also a brief list of VBA / Excel Best Practices as well as Testing Approaches at Codematic

Was this helpful?

2009-10-08T23:43:00.001+11:00

Microsoft Office Help has got incrementally better, but over the generations, it can be two steps forward, one step back. The new Office 2007 look and feel pastel and themed colours of Help are pretty, the writing may be more lucid, the different font sizes / styles may be more appealing. But still, the Bookmark This feature in winhelp classic never returned (although Visual Studio Help has this) and now, you have multiple entries in the search results with no differentiation of which one is the interesting entry.

Quick Tips for Office 2007

2009-10-01T11:33:00.001+10:00

Heading Numbering appears to be through a different user interface in Word 2007. This seems the easiest and direct:

Write some text
Apply Heading 1, 2 or 3 using Styles
Click on one of the Headings e.g. pick a Heading 1 paragraph with the cursor
Click on the Outline Numbering button in the toolbar and specifically choose Heading Numbering icon

Ending your Powerpoint Show without a Black Slide – it’s in the Office Button > Powerpoint Options

del.icio.us Tags: Office2007,Word2007,Powerpoint2007

Think you had a hard time at the Office?

2009-09-16T16:52:00.001+10:00

Windows had worse:

see more Fail Blog

Working smarter with email software

2009-09-11T16:53:00.001+10:00

Image via Wikipedia

Yesterday, I was getting quite frustrated in Lotus Notes, trying to chase down one email item in the new threading feature. I was mouthing profanities until I realised that I had no hope of finding that email item. I had routed a duplicate of Lotus incoming items to my Yahoo inbox and was subsequently retrieving such items via the POP3 interface into Outlook. Why didn’t I use connect Outlook directly to my Lotus Inbox via Lotus IMAP4 emulation? Well, because the Lotus IMAP4 interface gave up on me some months ago. Never found out why from our sysadmin team.

Anyway, so here I am in Outlook 2007 hunting for another email item. Blast! Really hard to find. Even though I have just installed Microsoft Desktop Search 4.0 (which even searches non POP3 local stores, even IMAP4 transitory things.) Even though I had Categorised items (but obviously forgotten to categorise this one).

Soo, I switch the Inbox View to Arrange By: From. That looks half promising. But I can’t see the Preview and I have to scroll like crazy to get to another From: sender.

Lightbulb blinks on. View > Expand / Collapse Groups > Collapse All Groups

Aaah!

del.icio.us Tags: Outlook 2007,Outlook,email

The Excel Data Picker

2009-09-10T21:17:00.001+10:00

I was leading a training session the other day, demonstrating in-cell validation using lists or number limits and Deepa asked whether she could drop down a date list, like we do in a custom list. I blinked and said, “you mean a Date Picker?”. Then I realised, it was a good request. I immediately pointed to the date picker living in Access 2007 cells (even though they annoy Pepe) and the new Word 2007 Date Picker Content Controls. But Excel cells? I muttered something about the richness and maturity of Excel and said I would have a look on the internet.

There it is, an effort by ISamRad – it’s offered plain, unwrapped, an .xla in all its glory (download it here). I just tested it with Excel 2003 and Excel 2007, both work fine. Sam is actually Sam Radakovitz, a program manager on the Excel team – he contributes to the Excel Blog.

Enjoy!

del.icio.us Tags: Excel2007,Excel,Dates,DatePicker

The Office 2007 Theme Builder

2009-09-01T09:29:00.001+10:00

Just spotted the Office 2007 Theme Builder at Microsoft Connect. Check it out.

Microsoft - the love / hate thing

2009-08-22T01:09:00.004+10:00

To be fair, it's any software and any software company. Microsoft just gets more flak because more of us use more of their products - statistically, Microsoft products dominate the marketplace and this causes anguish when what we hope is designed, wasn't designed that way.

Lately I have enthused over Outlook 2007 Categories - not just colour names, you can assign new Categories and rename existing Category tags so that they are more meaningful - like VIP, Must Do, The Boss, The Wife - you get what I mean.

One of the nice things about Outlook in most aspects is that if your machine dies, you can rescue or keep safe your stuff by copying the .pst file before hand. The .pst file contains your Email items, stored in whatever folder hierarchy structure you designed, your Calendar, your To Dos and so on. Except Category customisations. It appears they may be stored somewhere in registry like HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Outlook\Categories

It's enough to make your cry.

Laying out the carpet for to recipients for your Excel files

2009-07-20T13:42:00.001+10:00

I was looking at the MSKB 942385 and then downloaded the Office 2007 Service Pack 1 Changes_all.xls package. Irritating, that file is. Excel is now a great grid and table tool – it’s natural formatting, data handling (filtering, sorting) abilities exceeded Word’s model a long time ago. So whenever you need to deliver a dense grid of data, Excel is the way to go. Of course, Access would be as good but not everyone has Access on their machine. But, people still have a Word mindset – if you want to deliver for example 400 over rows of info to your recipient, leave that word processor approach of laying it out nice so that people can print (do you help them print?) but help them more pro-actively. Facilitate their hunting for gems in that 400 row grid, ensure that they can hit “print” and it prints well.

Download that file and play along with me. See what you can do to help the recipient along.

Suggested Best Practices

Forget that danged banner proclaiming that the file is what it is – you can put a brief title in the Excel header / footer. Additionally, you can repeat the banner in the worksheet tab
Provide Folder Path / Filename info as well as Page x of y in the Excel Header/Footer
Ensure that the grid has a column of category names. Remove arbitrarily placed Category Headers from the grid.
Put the data in an Excel Table. So that the recipient can sort/filter easily
Freeze the column title / row titles. Dang! Frozen worksheets can’t go into the new Page Layout View.
Set relevant Print Titles so that the paper follows your frozen worksheet titles.

Now, was it that hard to extend some courtesy to the recipient? What other tips for any Office program can you think of that will facilitate recipients?

The Excel 2007 .XLSB file format

2009-07-17T20:45:00.001+10:00

del.icio.us Tags: Excel2007

The Excel Team Blog has this to say about the curious .XLSB file format.

Even though we've done a lot of work to make sure that our XML formats open quickly and efficiently, this binary format is still more efficient for Excel to open and save, and can lead to some performance improvements for workbooks that contain a lot of data, or that would require a lot of XML parsing during the Open process.

(In fact, we've found that the new binary format is faster than the old XLS format in many cases.)

Also, there is no macro-free version of this file format – all XLSB files can contain macros (VBA and XLM).

Runtime performance - once loaded into memory, the file format has no effect on application/calculation speed

Interesting Business Intelligence Tool – Gemini

2009-07-16T09:53:00.001+10:00

Just saw the YouTube demo of the Gemini project (SQL Server and Excel) handle 20 million data rows and related tables (on a netbook PC) in an impromptu expression, interactively, in a few seconds.

del.icio.us Tags: Excel2007,SQLServer,BusinessIntelligence,DataAnalysis

Put it in a table

2009-07-15T21:06:00.001+10:00

Image via Wikipedia

Excel 2007 has enhanced the object formerly known as Lists (in 2003) – they’re now called Tables. Tables are now super cool, the greatest thing in Excel since, oh, AutoFiltering.

How do you make a Table?

I guess the obvious way is to Insert > Table.

But I much prefer placing your cell cursor on a cell in the midst of a dense rectangular shaped block of data cells and doing the Home > Format As Table action.

What can Tables do for you?

Let me count the ways

A Table can have banded rows and/or columns. This colouring is independent of Direct Formatting or Cell Styles
A Table auto-extends in columns if you type a new heading
A Table auto-extends if you insert a new column or row in the midst of the cells
An auto-extending table also extends any range names that you have assigned to column blocks or row blocks
A table has a TableName and has ColumnNames which can be used in new Structured Formulae.
If you type a formula in an empty cell in a column of the table, Excel 2007 tries to give you a Structured Formula (MyTable[ThisRow][Salary] * 9% instead of the classic A2*9%
Completing a formula in an empty cell in a column of the table will cause Excel 2007 to fill the rest of the cells in that column with relevant formulae.
A table does not mind you inserting a new row at the bottom of the table and it auto extends the banded shading as well as the Table’s cell addresses to that new row.
A table acts as a a suggestion to be the source for a Pivot Table.
A table overcomes the guessing glitch Excel 2007 has with regard column titles which look like numbers. For example if your column titles were CountryName, 2008, 2009, when you select the whole block to make an Excel chart, Excel 2007 thinks 2008 is a Datapoint and not a Data Series Name. So you then have to unconvince it by excluding the column titles of your block and manually editing the Data Series Name. Or maybe you have to apply Numeric Formatting as Text.
A table supports a Totals Row which can have the aggregate statistic, normally SUM, AVERAGE, COUNT, etc…
You can refer to totals of Table Columns by using the Expression fragment – SUM(TableName[ColumnName]) – the Table Name and Column Name appear in the autocomplete list when you progressively type the formula into the cell.
A Table has the Remove Duplicates Command
A Table has the AutoFilter drop down arrows in the column titles row – you can do all the AutoFilter activities

What you can’t do with a Table

You can’t carry out the Data > Subtotals command in a Table. It’s greyed out. You can convert the Table back to a Range and then apply AutoFilters.
You can’t use the TableName syntax in a Data Validation of a List. Is there a workaround?

Curious things about a Table

If you change the Accent1, 2, 3… colours in a Theme, that affects Table Colours in an unexpected manner.
If you want to use the Table smarts but don’t like the coloured banding and coloured borders in a Table, you can clear this colouring. There is one choice in the gallery of Table Formats called “None” but this choice is not visible on the Home Tab of the Ribbon. It is visible in the (Table Tools) Design Tab of the Ribbon – as well, there is a Clear All text item in that Gallery of Table Formats.

del.icio.us Tags: Excel2007,Tables

Quick Microsoft Office 2010 video demos

2009-07-13T16:49:00.001+10:00

Word has a beefed up Document Map, Outline manager.

See What's New in Microsoft Word 2010

Excel has a Slicer and in-cell trend lines.

See What's New in Microsoft Excel 2010

New! New! New! Office Webapps delivered by Sharepoint

She sounds like Bernie Fraser but give her some time….

See What's New in Microsoft Web Applications 2010

del.icio.us Tags: Office2010,Excel2010,Powerpoint2010,Word2010,Sharepoint2010,Webapps

Office 2010 - The Movie

2009-07-11T10:49:00.005+10:00

The website

Spotted Book: Business Analysis with Microsoft Excel

2009-07-10T14:44:00.001+10:00

You can get the paper or the Adobe Acrobat version of Conrad Carlberg’s book.

Instead of teaching how to push buttons in Excel, he delivers working with Excel in the context of accounting and finance.

del.icio.us Tags: Excel,Acconting,Business,Analysis

Spotted Book – slide:ology

2009-07-10T14:29:00.001+10:00

I tend to browse the bookshelves when I shop. There are some interesting books out there. Nancy Duarte has a really worthwhile presentation book called slide:ology. Her blog features some extended content freebies which are worth looking at and downloading themselves.

del.icio.us Tags: Powerpoint,Keynote,Presentation Techniques,Books

That pesky security alert message for external workbook references

2009-06-18T14:59:00.000+10:00

Microsoft gets flak when there are macro virus alerts. Each iteration of Microsoft Office has toughened up default security. Excel 2007 now displays a security alert when you open a workbook which has external workbook references. MSKB 826921 explains the issue and workaround.

Update:

No need to work so hard. There is a deep menu nested way of switching off the alert – thanks to Lilith for telling us.

Office Button > Excel Options > Trust Center > Trust Center Settings > External Content > Enable automatic update for all Workbook Links (not recommended).

Yeah, not recommended but do it anyway – of course you should have a good, up to date, anti-virus system in place and running real time.

Don’t confuse this with:

Data > (Connections Block) Edit Links > Startup Prompt > Don’t display the alert and update links

del.icio.us Tags: Excel,ExternalReferences,SecurityAlert,Security

Pardon the Range Name

2009-06-11T22:53:00.001+10:00

Range Names are a lot of fun. Lots of “A1” cell reference people don’t know about the benefit of using Range Names – but with Excel 2007’s new in-your-face Formula Tab with Name Manager, we hope more people will use this feature.

One wrinkle in an otherwise simple explanation of Range Names, is the scope of the Name. You see, there is a global-to-workbook scope and a local-to-worksheet scope. Here is Simon Hurst’s attempt at explaining it:

Names must be unique within their scope. So you cannot have two identical names in one sheet, but you can set the same name up in different sheets. This could be very

useful – a similar formula on several different sheets could refer to the range name defined for that sheet

You are also meant to be able use the scope setting to define the same name at local worksheet level and global workbook level. If you refer to the name on a sheet where it has been defined for that specific sheet, then the local reference will be used. If you refer to the name on a sheet that doesn't have that name defined locally then the global workbook reference will be used. If you want to refer to the global name from a sheet that has the same name used locally, then you have to prefix the name with the workbook name, for example: ='2007 range names.xlsx'!profit.

Training Matters has an interesting article on effective use of Range Names – and explains how to replace cell references in pre-existing formulae that didn’t use a Range Name previously

The Define Name option includes the Define Name and Apply Name options. Apply Name is unchanged from previous versions of Excel and allows names to be applied to existing formulae where they will replace any direct references to the named cells or ranges.

It is also possible to create dynamic range names. You can use a dynamic formula to define a range. As new items are added, the range will automatically expand.

del.icio.us Tags: MicrosoftExcel2007,Excel,RangeNames

(The New) Office Coach

Making gmail the default email client (Firefox Windows)

When you need quick shortcuts to SendTo

Comprehensive list of fonts installed with Office 2007

Google Sidewiki for bloggers with Blogger.com accounts

Be careful

Classnotes of the 28th October 2009 Access session

VBA Editor utilities

Interesting Spreadsheet Reading

Was this helpful?

Related articles by Zemanta

Quick Tips for Office 2007

Think you had a hard time at the Office?

Working smarter with email software

Related articles by Zemanta

The Excel Data Picker

Related articles by Zemanta

The Office 2007 Theme Builder

Microsoft - the love / hate thing

Laying out the carpet for to recipients for your Excel files

Suggested Best Practices

The Excel 2007 .XLSB file format

Related articles by Zemanta

Interesting Business Intelligence Tool – Gemini

Put it in a table

How do you make a Table?

What can Tables do for you?

What you can’t do with a Table

Curious things about a Table

Quick Microsoft Office 2010 video demos

Word has a beefed up Document Map, Outline manager.

Excel has a Slicer and in-cell trend lines.

New! New! New! Office Webapps delivered by Sharepoint

Office 2010 - The Movie

Spotted Book: Business Analysis with Microsoft Excel

Spotted Book – slide:ology

That pesky security alert message for external workbook references

Pardon the Range Name