Level 300 – Advanced

Are you building a spreadsheet and would like to know how many black cells you have in a given range?

There is a great function in Excel that you can use to do exactly that – count the number of BLANK cells in a range.

Simply type…

=COUNTBLANK(range)

(replace range with the range of cells you want to limit your count to). 

Note that there is one particular thing that might slip you up with this function.  When using =COUNTBLANK(), Excel is only searching for blank, empty cells.  If you have a space in a cell for example – it might look empty to you, but Excel can see that there is a space – which means it will not think it is blank, and not count it.

‘till next time!
TNP

One of the interesting statistics I looked into when preparing for my presentation at Microsoft TechEd Australia 2009 was how many people make use of the shortcut keys in Office.

One of my favourite resources for fun facts like that is Jensen Harris.  Those of you who have dug a little deeper into Office 2007 would recognise that name – he is the guy responsible for the new Ribbon user interface.

In his presentation at MIX 08, he brings up a few statistics which really shocked me (and I am paraphrasing here):

• Only around 50% of users use the Ctrl+C shortcut to copy (which means 50% of folk use the menu!)
• Only around 27% of users use the Ctrl+S shortcut to save (which means 73% use the menu!)
• Only around 2% of users use the Ctrl+O shortcut to open documents  (which means around 97% of users don’t!)

Anyone thinking what I am thinking?  There is a huge opportunity here to increase productivity of Office users across the board, simply by working with users to identify what shortcut keys have the biggest impact, and helping them to master them.

Now there are two ways to go about that.  The first is probably the most pervasive already – lists of shortcut keys.  You can find them anywhere, simply by Googling (or Bing’ing) “Word Shortcut Keys”.  People download them, print them out, and put them on their desk beside their computer.

But what happens when you have a list of shortcut keys? 

1) You don’t learn them, you just refer to them

2) You don’t actually work through the shortcut keys to understand what they are capable of

3) You are probably less productive when you take into account the time it takes to look up the shortcut key every time you use one!

The second way is to work through a learning program which helps you understand the impact of, remember, and give you confidence to use key shortcut key combinations when you need to.

How many learning programs like that exist?  None.

Well until now…

I have put together a audio course called “Five days to Word 2007 shortcut mastery”.  Now this isn’t for everyone.  If you are comfortable with your list of shortcuts that you refer to all the time – then great!  If it works for you then there is no reason to even think about enrolling in this course.

On the other hand, if you still struggle with remembering, or understanding what shortcut keys are available in Word 2007, then this course is exactly what you are looking for.

Before you ask… no, it isn’t free.  But this is the kind of specialist training that you can’t really find anywhere else at the moment, and it has the potential to help you save hundreds or even thousands of dollars in lost productivity yourself.

Want to learn more – visit www.shortcutcourse.com.

‘till next time!
TNP

So you have a heap of headings in your document.  One way to make communicating with others about your document a whole lot easier is to add numbers to each of your headings.

You know what I mean… “John, take a look at section 4.2 and let me know what you think!”

Well it is really easy to add numbers to your headings in Word 2007 – as long as you have used your heading styles properly!

All you need to do is:

1)  Make sure you are on the “Home” tab in the Ribbon

2) Look for the “Paragraph” group

3) Look for the “Multi-Level List” button.  You will find it beside the “Numbering” button – chances are it is the third from the left in the top row.  Click on that button

4) Select your preferred heading numbering style from the list library.

There you have it, some awesome looking numbered headings, and a lot less headaches!

‘till next time!
TNP

Do you want to add a classy touch to the publication that you are working on in Publisher 2007? 

A drop cap is a great way to do that.  For those that don’t know what a Drop Cap is… it is one of those big letters you see at the start of a paragraph.

In Publisher 2007 it is easy to add a drop cap.

1) Click on the paragraph of text you want to add the drop cap to

2) Click on the “Format” menu

3) Click on “Drop Cap”

4) Select the drop cap style you want to use.  If you want to make something really special, click on “Custom Drop Cap” and select the letter position, size, font and colour

5) Click “OK”

Too easy!

‘till next time!
TNP

Do you use OneNote 2007 so much that you start to run out of space on the pages of your OneNote notebooks?

Well there is a neat little feature in OneNote 2007 which enables you to create more whitespace by pushing the content on your page out of the way.

To find it:

1) Click on “Insert”

2) At the bottom of the menu, click on “Extra Writing Space”

3) Click on your page where you want the whitespace to start, then drag to where you want it to end.

Simple as that!

‘till next time!
TNP

If you regularly collaborate with others creating Word documents, or Excel spreadsheets – I am sure that you are very familiar with the commenting feature.

Basically it allows you to add comments throughout the document or spreadsheet for others to see when they are reviewing the work.

Well you will be happy to know that you can also add comments to the slides in your PowerPoint 2007 presentations.

Here is how:

1) Go to the “Review” tab in PowerPoint 2007

2) Look for the “Comments” group

3) Click on “New Comment”

4) Write your comment

5) Click on the comment and drag it to where you want it to appear on your slide

… but how do I change the name associated with the comment?

Good question!  PowerPoint takes that information from the information that has been entered in the PowerPoint options menu.

1) Click on the “Office Orb” – that is the old file menu in the top left hand corner of the screen

2) Click on “PowerPoint Options”

3) Look for “Personalize your copy of Microsoft Office”.  You will find the User Name and Initials there.  Simply change them to the right details and your comments will now be attributed to the right person.

‘till next time!
TNP

Are you lazy like me and hate typing dates?  Well did you know you can add one of those cool calendar style date pickers to your Word 2007 document?

It is pretty easy:

1) Make sure you can see the “Developer” tab in the Ribbon.  If you can’t, click on the Office menu (you know… the old file menu) in the top left hand corner of Word 2007, then click on “Word Options”.  Then simply check the third checkbox “Show Developer tab in the Ribbon”

2) Click on the “Developer” tab in the Ribbon

3) Look for the “Controls Group”

4) Click on the icon that looks like a tiny calendar (should be the second icon on the second row)

5) Click on the control that now has been added to your document, and select your favourite date!

‘till next time!
TNP

Page up and page down are great tools to quickly move through your Word 2007 document.  But they are not that precise.  Wouldn’t it be good if you could browse by things that matter in your document – like all the headings, or the tables, or the graphics?

Well you can!

In fact you can browse by all of the following:

• Fields
• Endnotes
• Footnotes
• Edits
• Headings
• Comments
• Sections
• Graphics
• Tables
• and of course, the default – Pages

… simply by using CTRL + PgUP (Page Up), and CTRL + PgDn (Page Down) – or the up and down arrows in the bottom right hand corner of your Word 2007 window

So how do you set which thing you want to browse by?  There are two ways:

1) Click on the “dot” in the bottom right hand corner of your Word 2007 window (the dot in between the two up and down arrows) – and then select what you want to browse by

or…

2) Use the shortcut “ALT + CTRL + HOME” and then select your preference

So for example, say you select “Browse by Heading”, every time you press CTRL + PgDn, the cursor will move to the next heading.  Likewise if you selected “Browse by Table”, every time you press CTRL + PgUp, the cursor will move to the previous table.

This can be a very powerful tool to use, especially if you are editing or reviewing your document!

‘till next time!
TNP

Are the normal line spacing (or “Leading”) options in Word 2007 just not enough?  Not very many people know it, but you can have some very fine grained control over the space between your lines.

In fact, you can say how many “Pts” space there is – for example, 12pts, or you can get really precise and give a spacing in cm, mm, or inches.

Here is how to do it!

1) Select the text you want to change the leading/line spacing of

2) Make sure you are on the “Home” tab in the Ribbon

3) Look for the “Paragraph” group, and click on the little square with an arrow pointing out of it in the bottom right hand corner of that group

4) Look down the box that appears until you get to the “Spacing” options.  In the right hand column you will see a “Line Spacing:” drop down box.  Select “Exactly”

5) Beside that dropdown box, there is a dropdown box called “At:”.  Type in your desired line spacing or leading in pts, cm, mm, or "

6) Click “OK”

Now you have precise control over exactly how far apart your lines appear in Word 2007!  How good is that!

‘till next time!
TNP

Did you ever notice that when you show your PowerPoint 2007 deck that it always shows it as a full screen?

Have you ever wondered if it was possible to show a PowerPoint presentation in a window that you can resize to meet the needs of your presentation?  For example, you might want to demonstrate a product, and have your presentation on the screen at the same time!

Well there is a well hidden command in PowerPoint 2007 which means you can show your deck in a window!  Here is how to do it:

1) Beside the quick access toolbar (in the top left hand corner of the screen – it is the one what has the save, and undo buttons) you will see a little arrow that points down.  Click on that

2) From the menu that appears, click “More Commands…”

3) In the “Choose commands from:” dropdown box, select “All Commands”

4) You will see literally hundreds of commands listed.  Luckily they are all in alphabetical order!  Scroll down until you find one that says “Slide Show in a Window”.  Click on that.

5) Click on the “Add > >” button to add that command to your quick access toolbar

6) Click “OK”

You will notice that there is a brand new button in your quick access toolbar in PowerPoint 2007.  It looks like a presentation screen, with a window in front of it.

Simply click on that button, and your presentation is now delivered through a window instead of full screen!

How easy is that!

‘till next time!
TNP