The Productivity Hub

I keep a lookout for useful Outlook rules (and anything else that keeps my Inbox manageable) and recently stumbled across a great, practical one.

If you receive mails from customers or other sources that require a response or action and have team members who are subject matter experts to help you deal with them, you can simplify the task by forwarding mails based on the keywords they contain. For instance, say I wanted all PowerPoint comments and questions to go to a particular team or teammate.

I'd go to Tools, Rules and Alerts and click New Rule. Under Start from a blank rule, click Check messages when they arrive and click Next.

If you only want to filter messages from a particular source, click the checkbox for from people or distribution list and then enter the source(s). Or, if you're filtering mails sent to a particular e-mail address (say, a customer alias you're signed up to receive), choose sent to people or distribution list. For my example, I'll use a group mailing alias that I'm on for Productivity Hub feedback:

Note: In both of these cases, any mail sources need to be in your address book. If they are not in the global address book, add then to your personal contacts before creating your rule.

Now check the box for with specific words in the subject or body and enter any keywords including alternate spellings and common misspellings. You don't have to be exhaustive since you can always go back and refine the rule later as new variants appear.

Click Next. Now check forward it to people or distribution list and enter the recipient(s) who should review and/or respond to mails on this topic. If you like, you also can create an auto-reply to the original sender using have server reply using a specific message or reply using a specific template (see Autoreplies done two ways for more detail on how these work).

I would also suggest moving the messages that you filtered to a specific folder so you have a record or what was sent, just in case they aren't received on the other end (e.g., someone's mailbox was full). Though if you keep copies of your sent items, you may be fine selecting delete it.

When you're done, click Next and include exceptions to the rule, if any.

Click Next again to name your rule, turn it on and, if you like, run it on mails already in your Inbox. This can be a good way to test your rule to make sure it works as intended, but be sure to warn the recipient(s) first! They may get a lot of messages all at once depending on how many match the rule you have pending.

Suzanne

Ever include dates in your Office document filenames? If you're archiving files and want them to sort properly, there's a format you should follow.

Say that you archive final copies of your company newsletter with the date it was sent to customers. You might be tempted to use Newsletter-010410.docx to represent January, 4, 2010. (Or, if you're in a country that puts the day first, you might choose 040110.)

At first glance, this looks fine. Later newsletters sorted by file name will fall under your January 4th edition - until next year (or next month, if you put the day first). Then they start to fall out of sequence. For example, you would not want your files to sort like this:

Newsletter-010410.docx
Newsletter-011011.docx (see, this is from 2011!)
Newsletter-011810.docx
Newsletter-020110.docx

The best practice here is keep naming consistent and then use YYMMDD format, so they sort by year, then month, and then day:

Newsletter-10-01-04.docx

Feel free to use hyphens to separate the date sections and make them more readable - as long as you're consistent, they will sort just fine.

This tip ought to hold you for the next 90 years, or you could include the full year (2010) if you believe your archive will persist and still need to be sorted properly in the year 2100.

Suzanne

When you have a lot of data and it needs to be organized, do you use Excel or Access?

I get this question a lot when talking to people and I wish I could give you a short answer.

Well, I guess I can: It depends.

I understand why people are confused. In Access, you can specify the types of information (fields) you want to store about items, customers, or whatever you’re tracking (records). But you can do the same thing in Excel. Just substitute columns for fields and rows for records.

Access and Excel are alike in many ways. They can store millions of records. They can pull in information from outside sources. Both also can run queries, a fancy word for an advanced search that allows you to see which records meet certain conditions.

They even look similar. In Access, you can view your data in a giant table, which looks a little like an Excel worksheet.

To decide which program is right for you, you need to understand their differences.

Numbers vs. text

Is your data primarily numbers? Is your goal to do a financial analysis or crunch numbers in some way? If you said, “yes,” Excel is probably your best bet.

While Access can handle some pretty complex math, it’s no match for Excel, which has hundreds of built-in formulas and financial analysis functions. Excel is the program to use if you need to do a cost-benefit analysis, calculate a return on investment, make charts, or do any sort of statistical analysis.

On the other hand, if you plan to store mainly large chunks of text, you should probably put it into an Access database.

Complex queries

Both programs are great at searching through your data to answer any question you might have, but if you plan to pose especially complex or highly varied queries, you may want to use Access.

Access can easily run complex queries that search through several databases. It can almost instantly generate a list of all your customers that have more than 500 employees or find clients with cumulative purchases that exceed $2 million who haven’t placed any orders within the past six months. Does this sound like the type of information you need? If so, build a database in Access.

Pictures

Do you want to store pictures with your records? This can be useful if you’re putting together an inventory database or a directory of employees. If you do, then you should definitely use Access. Access can store pictures, web links, and most other types of information as easily as someone’s name.

Size of the job

While Excel has a lot of database features, you may want to leave the really big jobs for Access. If any of these statements are true, Access is probably your best bet:

• Many people need to use or add information at the same time.
• You need to work closely with data in a SQL Server database.
• You have more than 20,000 rows or records.
• You need several databases to work with each other.

For smaller projects, especially where all the data can neatly fit in a single worksheet, Excel may be just fine.

Don’t stress too much about your choice, though. Both programs are part of the Microsoft Office system, so it’s easy to transfer your data back and forth. You may want to have your master data stored and maintained in an Access database and then pull out appropriate cross-sections to analyze in Excel.

Suzanne

Just the other day, I was updating a chart and became vexed when a range of values that I added did not appear.

After a little head scratching, I realized this happened because I had customized the scale of my chart to show only values from 1 to 50 and the new values exceeded this. Customizing your scale in Axis Options may improve the appearance of your chart with a specific data set, but it turns off auto scaling.

If new values are added later that don't fall within your custom axis range, they'll run off the top, bottom or side of your chart like so:

In this example, the vertical axis is set to a fixed maximum of 50 and a minimum of 20. If this happens, just adjust your chart scale by right clicking the problematic axis on the chart and selecting Format Axis:

Just restore the Minimum and Maximum to Auto, or select Fixed values that fit your new data ranges.

Suzanne

When you're working in complex Excel workbooks, you may feel like you're getting lost in the clutter. There is a way to hide what you don't need to see so you can focus on the data you're really working with.

To hide a column or row, right-click its letter or number and select Hide from the dropdown menu:

You can select multiples columns and rows at once; hold the Control key if you want to select any that are non-consecutive (Columns H and M plus Row 32, for instance). Restore hidden columns and rows by selecting the columns or rows around the missing ones, then right-click and choose Unhide.

Normally, hiding rows won't interfere with any formulas so your results will remain the same even if values are called from hidden cells. But say you WANT to exclude hidden cells from a sum. Use the subtotal function with function number 109 to add up only the visible cells, as in this example:

=SUBTOTAL(109,A1:A64)

If you check the function numbers for Subtotal (viewable using auto complete when you start typing the formula into a cell), all function numbers above 100 will limit the function to visible cells only:

It's a handy trick to pull out whenever you hide cells for a reason: not just to get them out of your way, but to exclude them.

Suzanne

If you're not using the Excel status bar, you're missing out on a number of time-saving tricks. The status bar runs along the bottom of the program, displaying contextual information and offering some handy shortcuts.

Want to create a macro like the hyperlink removal trick we recently shared? Just click the icon at left next to the Excel status message (it usually says Ready but may say Edit or Enter depending on whether you're interacting with a cell).

Excel mode status and macro record icon

If you want to count a range of cells or see their sum or average, simply select them. These values will be displayed right of center on the status bar unless you adjust your status bar settings (more on this in a minute). I use this one daily, often control-clicking to pick cells from disparate sections to quickly add them up. This area will be blank until you select two or more cells with numerical contents.

Average, count, and sum for all selected cells

If you'll be printing your spreadsheet, the next set of icons will help you switch quickly between the Normal view, a Page Layout that simulates how the sheet will look on a printed page, and Page Break Preview, which offers more of a high-level view and lets you quickly adjust page breaks.

View buttons

Finally, at the far right there is a zoom slider. Or, if you want to select a specific percentage, click the current percentage displayed to access a dialog box that lets you choose a preset or enter a specific number:

Current percentage button and zoom slider

These are just the defaults. You can add Numerical Count, Minimum, and Maximum to the calculation section of the status bar. And you can add indicators that let you know when Caps Lock, Num Lock, and Overtype Mode are on. (Overtype is toggled with the Insert key to change cell editing behavior so that you overwrite characters instead of pushing them over.)

Customize the Status Bar by right-clicking it:

Play around in here and see which status bar features work best for you. If there's some that you never use, you can always turn them off.

Suzanne

Once you get a chart designed the way you like it (say, the Gantt chart layout we explored last week), it's easy to re-use it with new data. Click to select the chart you want to use as a template and then go to the Design tab and click the Save As Template button:

.

By default, your template files will be saved in the Microsoft, Templates, Charts folder - which is good. Saving here will add them automatically to your Change Chart Type, Templates section for easy selection later:

Now, when you select Excel data you want to make into a chart and click to the Insert tab, pick ANY of the chart types and then click down to the All Chart Types button to find your template(s) in the My Templates section:

Once you've built a basic schedule, you can greatly improve its usefulness by adding a Gantt chart. This is basically just a bar chart that visually displays your project schedule tasks and the dependencies between them:

Excel 2007 doesn't have full Gantt chart capability out of the box, but there is an easy way to simulate a Gantt chart. Let's start with the schedule we built in yesterday’s post. The first thing we need to do is add a column to our schedule that we'll call Days In, which represents the number of days into the project that each task begins:

To calculate Days In, use this formula in B2: =(NETWORKDAYS(D2,D2,Holidays))-1 and then increment the second "D2" for each row (for example, B3 is =(NETWORKDAYS(D2,D3,Holidays))-1 and B4 is =(NETWORKDAYS(D2,D4,Holidays))-1). You can't simply fill down for this without having to correct the formulas to match the desired pattern. But once you get it set up, it will automatically adjust your chart if you need to modify the Work Days or Start date values anywhere in your schedule.

When you are done creating your Days In column, select the values in the first three columns including the header row but omitting the Project Summary row. On the Insert tab, look under Charts and click Bar and under 2-D Bar, select Stacked Bar:

Your chart initially should look something like this:

Believe it or not, it's actually very close to done. You just need to make a few adjustments.

First, make sure your chart is selected and go to the Format tab. At the upper left is a dropdown box that says Chart Area:

Click it, select Series "Days In" and then click the Format Selection button directly below the box. You'll open a popup box called Format Data Series. Click to the Fill section, select No fill and click Close. There, now it's looking like a Gantt chart:

Only one thing is wrong - it's backwards! No worries. Simply select the chart again, go back to the Chart Area box, and select Vertical (Category) Axis. Click the Format Selection button again and, under Axis Options, check the Categories in reverse order check box and click Close. Voila!

Now select and delete the Legend to the right of your chart and you're done!

Suzanne

Are you ready for an intermediate Excel project? This one is fun, very useful, and will teach you a number of handy formulas for manipulating dates in Excel.

I recently had to build a schedule and, in the process, developed a great little system that calculates your dates based on the number of working days needed for each step.

The finished schedule looks like this:

The first thing you need to do is outline your task list and the number of work days required for each step. Also, go ahead and enter all of your column headers.

Once you have this, enter the kickoff date in C2. For D2, enter =WORKDAY(C2,B2) - be sure to format the cells in the Start and Finish columns as Dates, or you may see some strange numeric results instead.

Note that I entered zero for the number of days required to kick off the project since this is mainly just where I enter my start date. If you have a kickoff process and want to count that time, feel free to enter whatever number of days this requires. The great thing about this schedule is how flexible it is!

For E2, enter =TEXT(WEEKDAY(D2), "ddd") to display the day of the week that the step finishes, which I find very handy - particularly if dates move around, as they frequently do, and my boss asks when we are launching.

Next, move down to C3 and enter =D2 to carry down the previous row's finish date, which is the key to making your schedule update dynamically. Finally, copy or fill the remainder of columns C, D, and E down to the final task row (we'll do the PROJECT SUMMARY row in a minute). If you don't have any steps that run at the same time, your schedule should be more or less complete.

In my example above, look at row 8. The Localization step happens in parallel with Production so they share the same Start date (=D6 in my example). That green mark in C8 indicates that the formula is inconsistent, which is correct here. Just make sure that the finish date for the longest of the parallel steps feeds the start date for the steps that follow.

Now that our schedule is complete, let's add a summary row.

First enter your dates: Start is =C2 and Finish is the launch date, in this case =D11. Then, to calculate the total number of Work Days for the entire project, use =(NETWORKDAYS(C12,D12)-1) - substituting whatever row number you're on for the 12s since your number of tasks may be different from mine. Note: Since the kickoff date and launch date are typically partial days, I subtracted one from the result to more accurately represent the total work time required.

If you want to exclude holidays as part of your working day calculations, there are a few extra steps required. To the right of your main schedule or on a separate tab of the same document, create a column called Holidays where you'll list any holiday dates where your offices are closed that fall within your project dates (to make your schedule re-usable, you may want to enter upcoming holidays for the next 12 months or so). I put mine on a separate tab and entered the last holiday date in my list as part of the tab name.

To quickly find upcoming holidays on your Outlook calendar, go to View, Current View and select Events. Items that are holidays on your calendar will have Holiday in the Categories column.

Now enter any dates that your offices are closed in your schedule document, and then select only the cells with dates. At the top left next to the formula bar is a box that, if you hover over it, says it is called the Name Bar. Type Holidays followed by Enter (NOT Tab!) to save the name for this range of cells:

If you need to edit the range of cells included in a Name (say, to add more holiday dates), click the Formulas tab and then Name Manager.

Now, to exclude these dates, you need to modify a few formulas to recognize your holidays. Go back to your schedule and change D2 to be =WORKDAY(C2,B2,Holidays) and then fill down this row (exclude the summary row; if you get a circular reference warning, you've gone one line too far!). Next, go to your Project Summary Work Days cell (B12 in my example) and change it to =(NETWORKDAYS(C12,D12,Holidays)-1) - again changing the 12s to whatever row you are on in your schedule.

There, your schedule is complete and can easily be maintained during the project by adjusting the number of work days required for a task or replacing the finish date formula with the actual date a task ended. For instance, if Review in my example required an extra day, I could change the Work Days to 3 or simply edit the Finish Date to 10/13/09. The rest of the dates below will be pushed out so that my new end date is 11/9/09.

Told you this was fun! At least as much fun as you can have maintaining a schedule. Just wait until we make our first Gantt chart together…

Suzanne

As you know if you read this blog regularly, OneNote is one of my favorite programs. It's great for notetaking and managing large amounts of information. You can create notebooks and tabs for separate projects and even search within handwritten text, images, and embedded audio.

If you paste an image into OneNote, you cannot crop it and resizing is a little different - though there are some unique features you may want to take advantage of. I'd suggest first cropping your images destined for OneNote in another program, such as Word (see Illustrate your Office documents with screen captures). Then cut and paste the cropped image into OneNote.

You can still resize images in OneNote, but you do so by dragging one of the square dots at the corners or sides of your image:

To ensure your image doesn't appear stretched (lose its aspect ratio), you should generally drag one of the corner dots either in or out to resize. The dots on the sides will only adjust the size in one dimension.

Once you get your image sized the way you like, right click it to see more image options:

If you like, you can Copy Text from Picture (if there is any). Depending on the size and clarity of the text in your image, you may not get a perfect translation every time (things like underlines may confound it) - but even correcting a few tiny typos can save you a lot of time over typing it all manually.

Copy Hyperlink to this Paragraph lets you set a bookmark in OneNote between places, so you can easily jump between related sections. Set Picture as Background lets you type over an image or paste other images on top of it, which can help if you are annotating or compositing your illustrations.

Finally, Make Text in Image Searchable lets you select the language of the text so it can be searched along with any other text. Mine defaults to English, but you can switch to another language if needed or disable it if you don't want a particular image searched.

Suzanne

Once your Outlook mailboxes are in order, it's time to turn to your Outlook calendar. When you checked your folder sizes, you might have found that the calendar is one of the bigger culprits in filling up your Exchange mailbox. This is where large attachments designated for a single, long-forgotten meeting often hide. You may not have even attended the meeting, but the meeting request lives on.

The quickest way to root out these and other unnecessary calendar detritus is to change your calendar view. Select your calendar, go to View, Current View and then select By Category. Make a note of your existing view before you change it so you can switch it back when you're done. The default is Day/Week/Month.

The category view is a sortable list, but it's missing an important field: Size. To add it, go back to View, Current View and then select Customize Current View. Click the Fields button and then change the Select available fields from dropdown to All Mail fields:

Scroll down to select Size, click the Add button and then OK.

There, now you can sort by size by clicking the column header. Open your calendar items and check for any important notes and open the attachments to determine whether you should save them before deleting. When you're done, click the Start Date column header twice to put the oldest entries at the top. Delete any old appointments unless you need a record of your schedule. Also look for any birthdays, anniversaries and other annual events and make sure they are set to recur annually, so you don't have to keep adding them every year.

If your holidays for the coming year aren't already in place, go to Tools, Options and then click the Calendar Options button. Click the Add Holidays button and then select your country (along with any countries you do business with!):

There, now you should be ready to face the new year!

Suzanne

It's that time of year when people make resolutions to do things better, and one promise that I hear often is to clean up their Outlook mail folders. Fortunately, Outlook 2007 makes this easy with the Mailbox Cleanup tasks found on the Tools menu:

First, check to see how you're doing in terms of usage by clicking the View Mailbox Size button. This will show you where your biggest mail folders are and, if you're using Exchange, how much available server space you have left.

Use the Find items section to search for older mails and really big ones. Set your date range (180 days will put you back six months) or size (1000KB = 1MB) and look for any messages that you can prune. You might want to check whether your company has a retention policy. It's possible management will want you archive or delete mails that exceed a certain number of months or years, in which case your task is simplified.

If you click the AutoArchive button, Outlook will instantly begin archiving items older than six months. (note the lack of "..." on the button? That means there is no options dialog here.) If you want to select which AutoArchive options to apply, close the Mailbox Cleanup window and go to Tools, Options, Other and click the AutoArchive button there instead:

The Mailbox Cleanup dialog also includes convenient buttons to empty your Deleted Items folder and remove any alternate versions of e-mail items. Odds are that you don't have any of the latter. If you do, you can check for a Conflicts folder that would have been created in the event of an Exchange Server sync issue. Mine showed a very small amount of usage even though I had no Conflicts folder, so I left it alone.

This once-a-year clean up is just the beginning. Keep your Outlook mailboxes in shape all year long with my Inbox Bootcamp series:

· Quickly clean out your Inbox

· Intro to rules

· Advanced rules

· Turn critical mails into actions

· Schedule time each week to manage your mails

Suzanne

If you're not using the Microsoft Office Picture Manager, you're leaving a powerful image editing tool on the table that's included free with Office 2007.

When you take screenshots to illustrate your Word and PowerPoint documents, sure, you can simply resize them directly in the document. But, in terms of overall file size, they might be many times larger than you need or want them to be. A better approach is to quickly crop and resize your images before pasting them into your final documents to help keep your final document file sizes down. You'll also save time by being able to more precisely edit your images.

Similarly, if you create or post images to a Web site, resizing them ahead of time will make your site load faster and generally result in cleaner looking pictures.

Picture Manager should be installed by default. But if for some reason it's not, you should go get your Office 2007 discs and add it to your toolkit. Really, it's worth the effort.

Cropping images can be done two ways. First select Crop from the Picture menu. You'll see thick black draggable crop marks around your image:

Simply drag them where you want your new picture edges to be and then, when you're happy with your placement, click OK. I typically use this as a first pass cropping tool, but leave a little extra space around the edges so that I don't crop too far (easy to do when cropping small details from very large images!).

I then fine-tune my crop using the second method. On the right side above the OK button are a series of fields labeled Crop handles:

Enter how many rows or columns of pixels on any side(s) you want to remove and then click OK; I often enter 1 or 2 a few times as I finalize my image to remove unnecessary edges. Repeat as necessary until your picture is perfect.

Now to resize. If your cropped image is too big or you need a thumbnail version, go to the Picture menu and select Resize. You can enter specific dimensions or, as I often do, play with the percentage until you match your target pixel width or height. If the image still isn't right, you can either Undo (Ctrl-Z) or keep resizing:

Finally, if you want your file to be a specific format such as JPEG or PNG for the Web, go to File, Export. Select your file format, enter a file name, and click OK:

If you're just putting the image into a Word or PowerPoint, you can skip this step and just cut and paste the image from Picture Manager to your document.

As a best practice, I suggest saving both the original and the cropped/resized image in case you ever need them again. Once you've saved your new version using either Export or File, Save As, you can right click the original image (marked with an asterisk in Picture Manager as a reminder that your original can be restored) and select Discard Changes to revert it to its last saved state.

There's a lot more you can do with Picture Manager, but this will get you started.

Before you publicly share a Microsoft Office document (e.g., a Word file offered for download on your public Web site), you should use the Document Inspector to ensure there are no hidden comments, invisible images, or personal information.

Your first step should be to make a copy of the file you want to share since in most cases removing data is permanent and you might want to recover some of that information for internal use (say, if you revise it in six months).

Open your copy of the file, go to the Microsoft Office Button at top left and select Prepare, Inspect Document:

Choose which areas you want to search for hidden and personal data:

· Comments, Revisions, Versions, and Annotations

· Document Properties and Personal Information

· Custom XML Data

· Headers, Footers, and Watermarks

· Hidden Text

If you're not sure which to check for, choose them all. You did save a backup of the original, right? Click Inspect. When the process is complete, you'll receive a report with the option to remove any data that is found:

Once you're done, check that your document still contains everything you wanted to share. If anything important is missing (e.g., your custom headers) you can reload the original, save a new copy, and run the inspection again - this time excluding anything you want to keep.

Suzanne

If you have a Word document that you want to import into Excel, first save your Word document as text. From the Microsoft Office Button at top left, select Save As, Other Formats and in the Save As Type box choose Plain Text (*.txt). Once you've saved your text file, close your original document and open your text document in Word or, if you prefer, a plain text editor such as Notepad.

Separate the contents for each field in a row with commas or, if you want to preserve commas in your content, use another character that's not in use such as the tilde (~). Use hard returns (press Enter) to designate the ends of rows. Save any changes to your text file and then open the workbook in Excel where you want to import your text file content.

In Excel, go to the Data tab and under Get External Data, click From Text. In the Text Import Wizard, select Delimited and then click Next:

Here you'll select which character you used as a delimiter to designate when to start a new cell. Select Comma or, if you used a different character, Other followed by the character you chose:

In the final step, choose whether you want the data formatted as General, Text or Date. You can also select any columns you'd like to skip:

Click Finish to import your document.

If you found this helpful, you might also want to learn how to easily import from a Web page into Excel.