Create drop down lookup lists in Excel 2007

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

8) 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 πŸ˜‰

The New Paperclip LIVE! at OfficeDevCon 08

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… 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 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 πŸ˜‰

How to encrypt your Word, Excel, and PowerPoint 2007 files

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 –Β  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 –

Office DevCon 2008 – Sydney, Australia

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 πŸ˜‰

Become Certified on Office 2007 – impress your boss!

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

And if you are studying for any of the exams – make sure you take a look through the rest of this site ( – 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 πŸ˜‰

Microsoft Office 2007 Service Pack 1

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, or if you want to download it already (note it is 218 MB!) go directly to

’till next time!
TNP πŸ˜‰

How to find ANY Office 2007 command you want!

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 πŸ˜‰

Remove recent documents from Word 2007

Are you a little worried by some of the names of the documents you have recently opened in Word 2007?  Scared your boss might catch you out updating your resume, working on your next novel or simply working on things that you shouldn’t?  Well this little trick will help you out!  Basically, how to get rid of all those recent documents that Word 2007 lists for you.

  1. Open "Word Options" by clicking on the Office Button, and then looking for the "Word Options" button
  2. Click on the Advanced Section
  3. Scroll down to the "Display" Section
  4. The first option in the "Display" Section is "Show This Number of Recent Documents" – set that to "0"
  5. Click "Ok"

There you have it – no more recent documents means no more strife from your boss! 

But just in case you have a change of heart in the future, you can turn on recent documents again simply by changing the "0" to something like "5" or "17", depending on how many you want to see.

’till next time!
TNP πŸ˜‰


Technorati Tags:

Center Across Selection – the Merge and Center alternative in Excel 2007

Yesterday I told you all about how you can use Merge and Center to get some text to run across a number of columns, rows, or both.

Well Jon Peltier quickly got onto the comments box and let us know about an alternative to Merge and Center in Excel 2007 called "Center Across Selection".  It’s just like merge and centre, but without all the hassles that merging cells cause.

Center Across Selection is a little harder to find than Merge and Center, but in true The New Paperclip tradition… I am going to show you how!

  1. Select your text and the cells you want to centre your text over.
  2. On the ribbon, make sure you are on the "Home" tab, and then look for the "Alignment" group.
  3. Click on the little square with the arrow in the bottom right hand corner of the "Alignment" group.
  4. In the "Format Cells" box that appears, look for the "Horizontal" drop down box
  5. Select "Center Across Selection"
  6. Click "ok"
  7. Look in amazement at your perfectly centered text πŸ™‚

How easy is that!  Thanks again Jon for the heads up!

’till next time!

TNP πŸ˜‰


Technorati Tags: ,,

Turn off the mini toolbar in Excel 2007

Have you ever noticed if you select some text in Microsoft Excel 2007 you get a little pop-up menu.Β  You know the one, it lets you change the font, the font size, the font colour and a lot more.

Some people think it is great.Β  Some people think it is annoying.

I think it is great, but as a service to you, I will let you know how to turn it off, and stop it from popping up!

  1. Click on the menu – the “Office Orb” (that circle in the top left hand corner which used to be the file menu!)
  2. Click on “Excel Options”
  3. Look in the “Popular section”, under “Top options for working with Excel
  4. Untick the “Show Mini Toolbar on selection” checkbox
  5. Hit ok

There you go – no more annoying (not) popup mini menu in Excel 2007

’till next time!

TNP πŸ˜‰


Technorati Tags: ,