Level 300 – Advanced

Is your inbox overflowing like mine?  Want to know how you can you quickly focus on what you need to action?  Well here is a nifty way to do it.

In Outlook 2007, you can organise your emails using views.  What is a view you say?  Basically think of it of a different way to look at all the emails you have.

In Outlook 2007 there are a number of views you can “view” your emails with:

• Messages
• Messages with Auto Preview
• Messages from the Last Seven Days
• Unread Messages in a particular folder
• Messages sent to me
• Messages in a timeline (very cool!)
• and some more complicated views like Outlook Data Files, and Documents

So how do you change the view of your inbox?

Simple. 

1. Click on the folder you want to organise (ie your Inbox).
2. Click on the tools menu
3. Select “Organize”
4. In the window that appears, click “Using Views”
5. Select your favourite view

If you want to get really tricky you can create your own custom view by clicking the “Customize Current View” button in the top right hand corner of that box.  You can customise your view with different fields, group by’s, sorts. filters and other settings.

How cool is that!

‘till next time!
TNP

Say you have a nice long list of names.  But they are just all over the place.  Wouldn’t it be good to be able to sort them into alphabetical order?

Well in Word 2007 it is a piece of cake!  Assuming you have each item in your list on a separate line…

1. Select the text you want to sort alphabetically
2. Make sure you are on the “Home” tab in the Ribbon
3. Look for the “Paragraph” group
4. Click on the sort button (that is the one with an "A on top of a Z”
5. A box will appear – if you want straight alphabetical order on your paragraphs just hit ok.
6. If you want reverse alphabetical order, make sure you select “Descending”

Using the sort button you can also sort numerically, or by date!  Simply change the “Type” of sort you want to do in the “Sort Text” box.

‘till next time!
TNP

I noticed one of my colleagues the other day struggling with her Excel Spreadsheet (yep that’s right, even TNP needs a day job to pay the mortgage!). 

She had a sheet with a few hundred rows in it – all containing information about people who had registered to attend an event.  I noticed that she was trying to count how many people from each state had registered.

I popped over and suggested that she use a Pivot Table.

What is a Pivot Table?

A Pivot Table in Excel 2007 is a pretty powerful, yet simple way to slice and dice the data in your spreadsheet.  It can help you summarise hundreds, thousands, even millions of rows (in Excel 2007 at least) into information that you can take action on!  And because you can quickly change how you view your data, it can be a really useful tool to use in the business world.

So how do you create a simple Pivot Table in Excel 2007? 

Well first, you need some data in your spreadsheet that you want to slice and dice.  Once you have the data – maybe it is a list of customers, a list of products, or a list of transactions including customers AND products… you can then start Pivoting!

1. Make sure there are no blank rows or blank columns where all you data is in your spreadsheet (extra rows and columns after all your data is ok though)
2. Click on the “Insert” tab in the ribbon
3. Look for the “Tables” group. 
4. The very first button in that group should be “Pivot Table”.  Click on that
5. Select “Pivot Table”
6. A little box will appear, and Excel 2007 will take an educated guess at what data you want to include in your pivot table.  Excel is generally pretty good and picking the data, so don’t worry about that part – but make sure you look at the bottom half of that box.  Excel is asking you where you want your pivot table to appear.  I suggest you choose a “New Worksheet” and then click “OK”
7. You will now notice what looks like a “Task Pane” appear in the right hand side of your Excel window.
8. In the top half of that task pane, select the fields that you want to include in your pivot table – basically you need to pick the information you want to slice and dice!
9. In the bottom half of that task pane, play around with where those fields sit in the Pivot Table.  You can filter based on certain fields, you can add fields as row or column labels, or you can get summary values for the contents of cells – you take your pick.  It is best to play around and experiment at this stage to get the exact outcome you want, simply because there are so many ways you might want to slice and dice that data.
10. When you are done, just close that task pan by clicking the “x” at the top of it (make sure you click the one at the top of the Task Pane, and not the one on the top of the window! that will close Excel!)
11. Now play around with your pivot table, and impress your boss!

Whilst it might look a little complicated to begin with, the best advice I can give is to give Pivot Tables a go and simply experiment with some data.  Then you will see the true potential of how much time this great feature of Excel could save you!

’till next time!
TNP

Ever wanted the users of your spreadsheets to fill in cells using only a certain number of options.  Say you had a list of States… maybe numbers from your General Ledger… or Product Categories.  Wouldn’t it be fantastic, instead of making people remember the correct names (and spelling) of each… that we could just give them a nice little list of options to pick from to populate the cell?

Well in Excel 2007, that is really easy!  Here is how to go about it.

Using Data Validation to create look up lists in Excel 2007

There are two ways to go about this… the simple way, if you only have a few options… or the less simple way

The Simple Way 

1) Click on the cell you want the look up / drop down list to appear in

2) Go to the “Data” tab in the Excel 2007 ribbon

3) Look for the “Data Tools” group

4) Click on “Data Validation”

5) In the box that appears, on the “Settings” tab, set the Validation criteria to allow “List”

6) In the “Source” text box, type in the options you want to make available (and remember to separate them with a comma!)

7) Click ok

Check out how unreal your drop down box in Excel 2007 looks!  And go tell your friends how easy it was!!!!

The Less Simple Way

I call this less simple simply because it is still pretty simple.  This method is best if you will have a long list of options that might already exist in your spreadsheet, or a list that you would want to regularly update.

Basically all you need to do is follow “The Simple Way”, and when you get to step 6, instead of typing in the options you want to make available, simply select the cells in your spreadsheet that contain the values.

So there you have it – drop down boxes… look up lists… whatever you call them… made easy in Excel 2007.

’till next time!
TNP

Are you going to be somewhere near Sydney, Australia in mid November?

If you are, and are interested in all things Office, make sure you get to OfficeDevCon08!

From the blurb… “OfficeDevCon is the first Australian community-driven event aimed squarely at Microsoft Office developers and power-users.  It allows people with interests across the Microsoft Office platform come together in one place to network and hear expert presenters speak on a wide range of advanced Office-related topics”

Well guess what… one of those expert presenters will be your’s truly… The New Paperclip.

In fact, in reality it will be my alter-ego… the guy behind The New Paperclip… Paul Woods

Paul will be presenting TWO sessions that should not be missed – especially if you work in Corporate IT.  Here is a quick outline of each presentation…

“The Office Suite Smackdown”
Saturday 15 November – 3:00-3:50PM

Office might seem like the obvious choice… but recently the competition has heated up for desktop productivity suites.  Your manager might be asking about alternatives like Google Docs, Open Office, Corel Wordperfect or Zoho Office.  In this session we compare the alternatives to Office 2007 from a users point of view – to help you pick the right tool for your organisation.

“The 10 most common Office 2007 user frustrations… and how to avoid them”
Sunday 16 November – 9:00-9:50AM

Don’t want your whole organisation angry with you because you forced them to use this horrible steaming pile of Office 2007 software?  It is a lot easier than you think to ensure a smooth and pain free transition to Office 2007 – it isn’t about buying 400 page dummies books, or just pointing people to the help button.  Simply by making sure you anticipate the most common questions up front you can win most of your potential battles.  In this session we will look at the most searched for answers by hundreds of thousands of frustrated Office 2007 users at http://www.thenewpaperclip.com… and see how you can ensure your people don’t end up asking YOU those questions.

So what do I need to do to attend these presentations?

Simple.  Go to http://www.officedevcon.com.au/ and register your spot.  Tell them that TNP sent you!  And make sure you catch up with me at the bar on Saturday afternoon!

’till next time!
TNP

So… you are sneaky.  You have some precious data you want to keep hidden.  Or your not so sneaky and just have some word documents that no one else should really see – for privacy or security or other reasons.  Well it might be a good idea to use the encryption built into Office 2007.

The encryption in Word, Excel, and PowerPoint 2007 basically turns your files into a mess that no one can read… unless they have the password.  In fact unless you get the password right, there is no chance you can even open the file.

Before I let you know how to do it, I will quickly let you know about how strong this encryption really is.  In Office 2007 they use something called AES (Advanced Encryption Standard) with a 128-bit key and SHA-1 hashing.  That probably sounds like a mouthful for most people, but let me put it into perspective.  This level of protection can be used to encrypt classified information up to the SECRET level in the US Government.  If it is that good, chances are your data will be safe (that is if you remember the password!)

To encrypt your document in Word 2007, Excel 2007, or PowerPoint 2007, it is really easy!

1) Click on the Office Orb
2) Go to the “Prepare” section
3) Click on “Encrypt Document”
4) Type in your secret password and click OK
5) Retype your secret password and click OK

I can not stress enough that you need to remember what that password is… because if you forget it, there is no chance you will ever open that document again – it is strong encryption!

’till next time!
TNP

PS – if you want to read more about the level of encryption, check out this article on Wikipedia – http://en.wikipedia.org/wiki/Advanced_Encryption_Standard.  And if you really want to get into the nitty-gritty of Cryptography in Office 2007… visit David LeBlanc’s weblog, and in particular this article – http://blogs.msdn.com/david_leblanc/archive/2008/07/03/office-crypto-follies.aspx

Are you an Office freak from down-under?

On the weekend of the 15th and 16th of November, a group of Aussie Office fanatics are putting together a conference called Office DevCon2008.  From what is on the agenda it looks like a great event for anyone involved in managing Office products in their workplace, or for those interested in getting a little more intimate with the Office 2007 suite, SharePoint and more.

Registration is free… and there might be sneak previews of Office 14 as well!

If you are in Sydney in mid November, make sure you check it out.  You never know, you might bump into yours truly there as well

You can find out more by visiting the Office DevCon 2008 website

’till next time!
TNP

Did you think you have great Word 2007 skills?  Does everyone in the office come to you for help with Excel 2007 or PowerPoint?  Are you the Access 2007 database guru at work?

Well – instead of helping everyone else all the time, how about you take some time to invest in yourself – and get a credential that proves your worth to your organisation!

You want to become a Microsoft Certified Application Specialist (or MCAS).

What is a Microsoft Certified Application Specialist?  Someone who knows their stuff – that’s what!  Basically it means that possess the right skills and expertise to shine when using one of the Microsoft Office 2007 programs.

There is a different exam for each program (Word, Excel, PowerPoint, Outlook and Access).. and you don’t have to do all of them! – Say you are just the best at PowerPoint and want to show that on your resume… then just do the PowerPoint 2007 exam.  Simple!

If you are interested, here is a quick list of the exams that you could take:

• 77-601: MCAS: Using Microsoft Office Word 2007
• 77-602: MCAS: Using Microsoft Office Excel 2007
• 77-603: MCAS: Using Microsoft Office PowerPoint 2007
• 77-604: MCAS: Using Microsoft Office Outlook 2007
• 77-605: MCAS: Using Microsoft Office Access 2007

What kind of things do you need to know?

Here is a quick look at the topics covered in the Word 2007 exam:

• Creating and customising documents
• Formatting content
• Working with visual content
• Organising content
• Reviewing documents
• Sharing and securing content

To learn more about the Microsoft Certified Application Specialist, and what could be on the other Office 2007 exams – visit http://www.microsoft.com/learning/mcp/msbc/requirements/default.mspx

And if you are studying for any of the exams – make sure you take a look through the rest of this site (www.TheNewPaperclip.com – just in case you didn’t know)… as you might learn a thing or two that will be handy in your exam!

’till next time!
TNP

Did you know that Microsoft have released an update to Office 2007?  They call it Service Pack 1 (or SP1 for the nerds out there!) and basically it is a big file that you can download that contains lots of updates and bug fixes.

So what you say?  Well it is a good idea to install the Service Pack for a couple of reasons – but the main one is security.

Microsoft Office 2007 Service Pack 1 fixes some holes in Word, Excel and the rest of the Office suite – holes that could make your computer vulnerable to attack.

What is this PaperClip’s advice?  Simple.  If you haven’t installed it yet – download or get a CD containing the Service Pack, and make sure you install it!

For more information on the service pack, visit http://support.microsoft.com/kb/936982, or if you want to download it already (note it is 218 MB!) go directly to http://www.microsoft.com/downloads/details.aspx?FamilyId=9EC51594-992C-4165-A997-25DA01F388F5&displaylang=en

’till next time!
TNP

Is there a feature of Office that you KNOW exists… but can’t for the life of you find it?

Maybe it was an old feature you remember from Word 2003, or Excel, or PowerPoint.  They didn’t cover it in the course that work sent you to, you can’t find it in the help file, and Google hasn’t returned any results!

Panic?  Not quite yet!

Here is a neat little trick that I learnt from Tim Anderson over at ITWriting.

1. Go to the Quick Access Toolbar (you know the one beside the big Office button (the old file menu!))
2. You will see a little arrow that points down – click on it and then “Customize Quick Access Toolbar”
3. Click on “More Commands”
4. In the “Choose commands from” dropdown box, select “All Commands”
5. Scroll through the list to find your long lost friend!

Simple as that! Kudos to Tim @ IT Writing for a fantastic find!

’till next time!
TNP