Does your boss have you add outside dates to his or her calendar? Say for example they have a subscription to the Metropolitan Opera or season's tickets to all Chicago Bears games. It's kind of a pain to manually enter all those dates, isn't it?

Well if you have Outlook 2007 or later (and some other calendar programs too), you may be able to take advantage of functionality that allows you to sync up with those calendars by using internet calendars, if they are available (and a LOT of them are!).

Internet calendars come in two flavors. One version is static (just like printing out the page - if the game gets cancelled, the calendar won't reflect it). The other version is dynamic - game time gets changed from 3:00 to 4:00; the calendar automatically updates to reflect it.

Guess what else? You can actually publish your own calendar so that other people can subscribe to it! How about publishing your family calendar with birthdays and anniversaries to your family around the country?

Here's a page that gives an overview of this nifty new feature in Outlook 2007:
http://office.microsoft.com/en-us/outlook/HA101673251033.aspx

Here's a page that gives you step by step instructions for how to do it:
http://www.ehow.com/how_2058421_add-internet-calendar-outlook-2007.html

OK, you're sold and dying to try it out for yourself?

Start here!

http://icalshare.com/index.php - This website has over 1500 shared calendars - everything from theater performances to professional sports to concert venues. Check it out! Plus, it doesn't just work with Outlook 2007. It actually works with a number of other programs as long as they support the iCal format.

In case anyone is interested in pursuing Microsoft Office 2010 certification, I thought you might like the background information.

What it is:

The Microsoft Office Specialist (MOS) program is a certification program for Microsoft Office 2010. Certification exams are available in Word (core and expert), Excel (core and expert), PowerPoint, Outlook, Access, Sharepoint and Windows. Microsoft created it and utilizes Certiport (www.certiport.com) as their official partner to oversee the testing process.

Office 2007 was initially administered under Microsoft Certified Administrative Specialist (MCAS), but later the name was changed back to Microsoft Office Specialist (MOS). Office 2003 was administered under the name Microsoft Office Specialist (MOS) program. Office 97/2000 was administered under the name Microsoft Office User Specialist (MOUS) program.

How it works / Cost:

There are testing centers located throughout the US with different days/hours, etc. Cost generally runs about $75-100 with the testing center allowed to set the cost. Some charge a proctoring fee. Vouchers for the tests are available through www.certiport.com (the official site) and through various resellers (where you can often find the vouchers cheaper, including a free retake option if you don't pass). 

You call the testing center, make an appointment and show up. You'll sit at a computer that has a simulated version of Office 2010 running on it. You are allowed one hour and you cannot use Help. Most exams have roughly 30 questions.  Each question may (and usually does) have several parts. You will immediately receive a score to know if you passed or not and a (not very helpful) summary will print out to tell you your score and areas missed. If you pass, a nice frameable certificate will also be mailed to you. When you pass one test, you are a Microsoft Office Specialist. When you pass four tests (Word expert, Excel expert, Powerpoint, and either Outlook or Access), you are a Master Level Microsoft Office Specialist. The certifications never expire and don't need to be renewed, but they are only valid on the version of Office that you tested.

Preparing For the Test / Cost of Study Materials:

If you know the programs generally well and just want to specifically study for the purpose of passing the exam, get the study guides (roughly $25-30 each on amazon):

MOS 2010 Study Guide for Microsoft Word (core), Excel (core), PowerPoint, and Outlook
http://tinyurl.com/cykdt5x

MOS 2010 Study Guide for Microsoft Word Expert, Excel Expert, Access, and SharePoint
http://tinyurl.com/cs5levy

They give you very succinct instructions on how to pass the tests based on the published guidelines of what is on each test (basically in the format of: To do this: 1) Click Here. 2) Choose that. 3) Click OK.)

If you don't know the programs well, I would buy books that are individual by program. The Microsoft Press Step by Step Series has usually been a good set, though I haven't looked at the 2010 versions specifically. 

IAAP offers an Options Technology program which is computer software training, with (I believe) separate modules for each test. This program includes a voucher with purchase. I have not reviewed this program, so I have no opinion on how effective it is. I also don't know if it focuses on teaching you the software or preparing you for the test (those can be two very different focuses, you know).

More information is available at:
http://www.microsoft.com/learning/en/us/training/office.aspx

The official program information is available at:
http://www.certiport.com

Hope this helps people that are considering pursuing the certification. If you have any other questions, post them in the comments and I will try to answer them there as well.

I have been doing some self study recently to prepare to take the Microsoft Office 2010 certification exams. As part of that, I am exploring new features that are offered in this version and I have found some AWESOME things to share with you!

1) Recover Unsaved Documents - I can't imagine there is anyone out there who hasn't done this at least once. You spend a long time typing up a nice brand new document, only to accidentally click NO when you are prompted to save it at the end. In prior versions, you were completely out of luck and had no recourse for any kind of recovery. Enter Office 2010! When you reopen the program, on the File tab under Recent, look in the bottom right corner. You will see a folder icon that says Recover unsaved document/worksheet/etc (different in different Office programs). Click that and it will show you the unsaved files you had for the last four days. Waaa hoo! Microsoft claims in its help file that this can work on previously saved files too. Basically you have to make sure that AutoSave is turned on with the option to Save Last Autosaved Version If I Don't Save Changes checked (find these options in File-Options-Save). In order for that to work, you need to have had the file open long enough for an Autosave to kick in and even then, you'll only get your changes back to the last time it AutoSaved. Still it's worth a shot, so take a moment to select that option right now.

2) Photo Album - PowerPoint 2010 has added a very nifty new feature called Photo Album. You can find it on the Insert tab. It allows you to enter multiple pictures and it will arrange them on the slides for you. Decide you want 2 pictures per page, no problem, PowerPoint reformats them all for you. Change your mind - want 4 pictures per page? Zip, it's done by changing the drop down on the photo album window. Want to have a text box next to each image? You can add them easily from the photo album page. While you are in the photo album window, note that you can - on the fly - rotate images, crop them, change the order, etc. You can also access the photo album (for editing after you have inserted it) by right mouse clicking your slides on the left hand navigation pane. Really cool new feature!

3) Remove Backgrounds in Photographs - Office 2010 has added the ability to remove background images in pictures. You just click the part of the picture you want removed, and voila! Instant white space.

4) Insert Screen Shots - Previously if you wanted to add a screen shot, you would press Alt-Prt Scrn and then paste the screen shot where you wanted it. Office 2010 has automated that step. Click on Insert - Screen Shot and you will get a drop down pane with thumbnails of all the windows you have open. Click the one you want and a screen capture picture is pasted into your document. Bam!

5) Customize the Ribbon - One of the biggest frustrations I had with Office 2007 was the inability to customize the ribbons. Office 2010 has fixed that little problem, thank goodness. Just right mouse click on your ribbon (note it can be context sensitive, so it won't show if you right mouse click on top of your styles for instance), choose Customize The Ribbon and get to customizing (including creating your own tabs on the ribbon)! Easy peasy!

Lot of NICE things have been added in Microsoft Office 2010. If anyone is thinking of upgrading from 2003, do yourself a favor and go straight to 2010!!!

Excel, of course, has gazillions of mathematical formulas. Some you probably use regularly (like sum, count, etc.), but others you may not use quite as much. Some, of course you'll never use unless you are an engineer, mathematician, or scientist. One of the formulas that is fairly useful is payment formulas. This allows you to fill in various blanks and come up with a formula for payments. Say you want to buy a car for instance. If you know how much the interest rate is and the value of the car, you can plug in different term lengths (48 months, 60 months) to compare monthly payment options.

To set up a payment formula, you create cells for each of the formula parts, as needed.

Here are the parts of this formula explained:
 
Rate: This is the annual interest rate, divided by 12. (see note below)

Nper: Number of payments

NOTE: The number of payments should match the payment period of the interest rate. So for instance, I broke the interest rate down into a monthly component (divided by 12), and therefore, I want my number of payments to be the number of months, so I will put in 60 months instead of 5 years. If I were going to make one payment a year for the next five years, I would have the left the rate as an annual rate and put 5 in the number of payments. Make sense how those are linked?

PV: Present Value - This is the amount of money you are borrowing.

FV: Future Value - This is any expected value you might have left at the end of the loan. This would come into play with a lease or a mortgage balloon payment. If it is blank, Excel will assume it is zero.

Type of Payment: (Optional) This field is optional, but it uses a 1 to reflect if the payment is at the beginning of the period or a 0 (or blank) if the payment is at the end of the period. I never use it myself.

Payment: This is where the formula goes and it will show the payment (monthly in my example) that will result from that combination of interest rate, number of payments, and amount borrowed.

A2=rate
B2=number of payments
C2=present value
D2=future value
E2 would be type of payment (I left this out)
 

The formula in cell E2 or F2 would be:
=PMT(A2,B2,C2,D2)

Excel has additional features that greatly expand formulas to allow you to play with the numbers to see how they affect the outcome. Those include Goal Seeker and What If Analyses (Scenarios). I may write some future blog posts on how to use these functions, as they are pretty cool and useful. In the meantime, I encourage you to look them up if you are not familiar with them. You might be surprised at how helpful they are.

Smart Art is a feature that was introduced to Microsoft Office in the 2002/2003 version, but it was expanded and beefed up (hmmm momentary distraction - is the phrase "beefed up" offensive to vegetarians?) in the Office 2007 version (yes Office - it's in Word, Excel and Powerpoint). Let me just tell you right now that this is not only one of THE *coolest* features ever in Microsoft Office, it's also one of the most fun to play with. Just take my word for it and take three minutes RIGHT NOW to check it out, if you haven't played with it before. You'll be glad you did. :)

Think back... to the dark ages... and for some of you, it may have been just yesterday... when your boss asked you to put together a simple diagram - perhaps a flow chart or whatever, where you have four or five boxes, each with a word in each box, maybe arrows pointing from one box to the next. Easy peasy, right? Then remember how your boss always came back and either asked for one more box to be added in the middle or one box removed. Remember how you had to manually resize all the text when you resized the boxes? Remember how you had to manually resize and move the boxes to accommodate changes? Remember having to flip all the arrows by hand when your boss decided to reverse the flow? Remember how excited you were when you learned how to select more than one box at a time and move things as a group cause every little tip helped save you at least a little bit of time for this project that sometimes took hours?

Welcome to the Age of Enlightenment, otherwise known as Smart Art. In Office 2003, the button is located on your drawing toolbar - you'll just have to poke around and play with it and know that 2003 can do most of what I am about to say. The rest of these instructions are based on Office 2007.

Click the Insert Ribbon, then the Smart Art button.  A window pops up offering you all kinds of graphics (like organization chart style, relationships, pyramids, cycles, etc.). Each one of those options on the left shows different graphics on the right hand side. Pick one you like. For purposes of illustration, scroll down and pick the first graphic under Cycles (five circles with arrows connecting them). Like magic, a graphic pops up in your window with five windows and arrows connecting them. On the left hand side is a window with a bulleted list. If you don't see that bulleted list, just left mouse click on the two triangles in the middle of the left hand side of the graphic or click Text Pane on the Design Ribbon. 

1. Type Apple as the first bullet. See how Apple gets added to the first circle.
2. Type Watermelon as the second circle. Note how the text automatically resizes itself in the circles to match each other with the longer word.
3. Delete the third bullet. Note how one of the circles gets deleted, so you now have four circles instead of five, and notice how the text resized itself slightly larger to allow for this.
4. Delete the third bullet again. Note how another circle gets deleted, so you now have three circles instead of four and notice how the text resized itself slightly larger to allow for this.
5. Hit enter, and see how a new blank bullet is added and a new circle is added back to bring us back to four circles. Type Lemon for this bullet and Lime in the last bullet.
6. Look up at your ribbon bars and you will see that new ribbon bars were added for Design and Format (as long as you are clicked on your Smart Art diagram). Note that you have buttons on the Design ribbon that allow you to add a shape before after your current circle (you can also do this by just pressing enter in the bulleted list).

Boss decided to reverse the order of the boxes? Woo hoo! The right to left button acts as a toggle to reverse or restore the order.

You'll see a group of buttons in the Layout palette. Click on the double triangle button at the bottom and all the cycles options become available. You can move your mouse over each graphic to get a preview in your document of how it will look (note that sometimes it doesn't look the way you would expect, sometimes you have to play with them a bit).

But wait, your boss comes back and says let's do this as a table format instead. You'll remember the dark ages briefly and cringe in pain. Oh yes, I well remember, selecting those boxes, deleting them, drawing new circles or triangles or whatever and basically starting over. Now? Ha! I laugh in the face of starting over. Instead I just click More Layouts at the bottom of the Layouts group of graphics. It brings up that original list of Smart Art diagrams and I can choose the first graphic under List. Voila, my round circles are converted to text box tables going across the page instead.

Bossie comes back and raves about the new layout, except that he really doesn't care for the color scheme. Oh ye of little faith... See the button called color scheme? Just click and you can see the Live Preview of colors as you move your mouse over the various options. Want it to look even a little bit fancier but still subtle? Move your mouse over the Smart Art Styles buttons and you'll see changes in shading, 3D effects and more. And again, click on the double triangle button and you'll see all the options at once.

The Format ribbon will give you more control over things like the font effects and borders, as well as things like wrapping document text around your diagram.

One last tip. Don't like the final size of the diagram? It's a picture! You can click and drag along the dots on the edges just like any other object in Office.

Play around. I think you will agree that Smart Art is one of the..

Coolest. Things. EVER!