Compute your entire Excel worksheet with a click of the mouse

Most users know how to take advantage of Excel’s AutoSum feature to quickly total spreadsheet data across a row or a column. For example, in the spreadsheet below, most users would use AutoSum to calculate the totals for each state by clicking in B7, double-clicking the AutoSum button, and then copying the formula across the range C7:E7. Likewise, to obtain the total sales for each software category, they would click in cell F4, double-click the AutoSum button, and then copy the formula down the range F5:F7.

Even though using AutoSum this way can save users quite a bit of time over entering each formula separately, they can save even more time by following these steps:

1. Select the range A2:F7.
2. Click the AutoSum button.

AutoSum lets you enter all the formulas with just one click! What’s more, you can do this for all the auto functions. For example, if we change the labels in F3 and A7 to Average, we can then complete the worksheet by following these steps:

1. Select the range A2:F7.
2. Click the AutoSum button drop-down arrow and select Average.

Miss an Excel tip?

Check out the Microsoft Excel archive, and catch up on other Excel tips.

Help users increase productivity by automatically signing up for TechRepublic’s free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

Colorize your Outlook messages to identify e-mail from specific senders

A simple but highly useful way to stay on top of important e-mail is to have Outlook display messages from different people in different colors. Here’s a quick rundown of how to set this up.

---

Expecting important mail? Identify it as soon as it comes in by displaying it in a distinctive color. Start by select an existing message from the sender in question, if you have one. If you don’t, that’s okay; you can enter the sender’s name manually in a minute. Now follow these steps:

1. In Mail, choose Organize from the Tools menu.
2. In the Ways To Organize Inbox pane, click Using Colors on the left side (Figure A).

Figure A

1. In the first condition statement (we won’t use the second), choose From in the first drop-down list (Figure B).

Figure B

1. If you chose a message before starting, the sender’s name will appear in the text box to the right. If it’s the wrong name, enter the right name or the person’s e-mail address.
2. Choose a color from the second drop-down list (Figure C).

Figure C

1. Click Apply Color and close the pane.

Afterward, Outlook will display all messages, existing and new, from the person you specified in the color you selected.

Quick copy trick in Excel

There are a number of ways to copy data in Excel, but if you’re like me, you’re always looking for shortcuts that eliminate a few clicks. Here’s one that’s handy when copying a single column of values to an adjacent column:

1. Select the column and the adjacent column. If the column of values is already selected, you can press [Ctrl]+[Shift]+[Right Arrow] to extend the selection.

1. With both columns highlighted, press [Ctrl]+[Shift]+. (that’s the period character).

It’s quick and easy, and pressing [Ctrl]+Z will cancel the copy action. If the target cells contain values, Excel will overwrite them. If you press [Ctrl]+Z to cancel the copy, Excel will reverse the action and reset the cells to their original values.

This trick also works with rows: Press [Ctrl]+[Shift]+, (the comma character).

Hide the Ribbon programmatically in Access and Excel 2007

All of the Office 2007 applications sport the infamous Ribbon. Unfortunately for developers, the Ribbon object isn’t as easy to control as its earlier counterpart, the command bar object. Access developers get a small bonus, as they can hide and show the Ribbon programmatically using the following commands, respectively:

DoCmd.ShowToolbar “Ribbon”, acToolbarNo

DoCmd.ShowToolbar “Ribbon”, acToolbarYes

Most likely, you’ll hide the Ribbon when you launch the database using an AutoExec macro or startup property. You could add a custom button that shows the Ribbon or simply hide and show the Ribbon as required.

Excel users can use a similarly easy set of VBA statements to hide and display the Ribbon, respectively:

Application.ExecuteExcel4Macro “SHOW.TOOLBAR(”"Ribbon”",False)”

Application.ExecuteExcel4Macro “SHOW.TOOLBAR(””Ribbon””,True)”

How you execute the code is up to you. You can attach the hide code to ThisWorksheet’s Open event to hide the Ribbon when a user opens the file. Or you might execute it when you launch a user form.

Office Challenge: How do you move Word table rows up and down?

I use a lot of tables and seldom do I complete a table without rearranging the data, at least a bit. The good news is that you don’t have to overwrite existing data, which is tedious and inefficient. How do you can adjust table rows up and down?

Last week we asked…

“How do you hide and unhide system and custom objects in Access?” The answer follows:

• To hide an object, right-click it in the Database window or Access 2007’s Navigation Pane, choose Properties from the resulting submenu, and check the Hidden option in the Attributes section on the General tab.
• To unhide hidden objects, choose Options from the Tools menu and check the Hidden Objects option in the Show section on the View tab. In Access 2007, right-click the Navigation pane’s menu bar, select Navigation Options, select Show Hidden Objects, and click OK. If hiding the object doesn’t work (it’s still visible in the Database window or Navigation Pane) most likely, the Hidden Objects option is currently checked. Uncheck it, and the hidden objects should disappear.
• Access hides System objects by default. To unhide them, choose Options from the Tools menu. Then, check the System Objects option in the Show section on the View tab. In Access 2007, right-click the Navigation pane’s menu bar, select Navigation Options, select Show System Objects, and click OK.

For additional insight into hiding objects, read tip 6, Hide objects — a subtle form of protection in 10 tips for security a Microsoft Access database.

Two easy ways to create Outlook search folders

Your Outlook users can define and save search criteria to organize messages in a way that makes sense to them. They don’t have to physically rearrange their e-mail; they can simply consolidate what they need in a virtual folder for quick access.

---

Outlook allows users to set up predefined search folders (for example, Mail Flagged For Follow Up or Important Mail) or to define and save their own search folder criteria so they can easily locate specific messages. But because the search folder isn’t an obvious feature, many users haven’t learned to take advantage of it. Here are two methods to share with them so they can start using search folders to quickly find the mail items they need.

Method one will enable them to build a new folder from scratch, either using standard options or by specifying custom criteria. Method two will let them perform a search and then save the results as a search folder.

Note: This article is also available as a one-page PDF handout to distribute to your users.

Method one: From scratch

1. Click File | New and select Search Folder to open the New Search Folder dialog box (Figure A).
2. Select a predefined search folder from the list (Figure B). You can also choose Create A Custom Search Folder and specify your own criteria.
3. Click OK, and the new search folder will appear in your Outlook folder list. In our example, we created an Unread Mail folder.

Figure A

Figure B

Method two: On the fly

1. Press [Ctrl] + E and enter a search term in the Look For text box.
2. Pull down the Search In drop-down list and choose a search location (Figure C).
3. Click Find now.
4. Once the search results appear, click on Options and choose Save Search As Search Folder (Figure D).

Figure C

Figure D

---

Get a steady supply of Office tips

Help users increase productivity by automatically signing up for TechRepublic’s free Microsoft Office Suite newsletter, featuring Word, Excel, Access, PowerPoint, and Outlook tips, delivered each Wednesday.

Generate random AutoNumbers in Access tables

Sequential numbers are fine for some purposes, but sometimes random numbers are better. Mary Ann Richardson explains how you can populate the AutoNumber field with random numbers when necessary.

---

There may be times when you would rather have Access generate random, rather than sequential, numbers in an AutoNumber field. For example, if you print a list of members whose membership numbers are generated sequentially, it may appear as if some of the records are missing when, in fact, those members have simply left the organization and their records archived. With random numbers, none of the “missing” members would be noticed.

To have Access randomly generate numbers that are unique to a table, follow these steps:

1. Open the table in Design view.
2. Click in the AutoNumber field row.
3. In the Properties window, click in New Values property text box.
4. Click Random (Figure A).

Figure A

The AutoNumber field will now be populated with random numbers as shown in Figure B.

Figure B

---

Miss an Access tip?

Check out the Microsoft Access archive and catch up on other Access tips.

Help users increase productivity by automatically signing up for TechRepublic’s free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

Use SUM in an array function to count values that match multiple criteria

When you need to count values based on more than one criterion, the CountIF function won’t do the job. The solution is to build an array formula with SUM.

---

If you want to count the number of days that orders totaled 30,000 or more, you can use the CountIF function. But what if you want to know the number of days that orders totaled more than 30,000 but less than 50,000? To count items that meet two or more criteria, you can use an array formula with the SUM function.

First, use conditional formatting to highlight the cells we want to count.

1. Go to Format | Conditional Formatting. In Excel 2007, on the Home tab, click Conditional Formatting.
2. Select Formula IS. In Excel 2007, click New Rule and then click Use A Formula To Determine Which Cells To Format.
3. Enter the following formula: =AND($D2>=30000,$D2<50000)
4. Click the Format button.
5. Click yellow on the Patterns. In Excel 2007, on the Fill tab, select yellow under Background Color (Figure A) and click OK.
6. Click OK again.

Figure A

To calculate the number of days sales totaled more than 30,000 but less than 50,000, follow these steps:

1. Click in G2.
2. Enter the following formula: =SUM((D2:D15>=30000)*(D2:D15<50000))
3. Press [Ctrl] + [Shift] + [Enter] (Figure B).

Figure B

To calculate the total sales for those two days, follow these steps:

1. Click in L2.
2. Enter the following formula: =(SUM(IF(D2:D15>=30000,IF(D2:D15<50000,D2:D11))))
3. Press [Ctrl] + [Shift] + [Enter] (Figure C).

Figure C

---

Miss an Excel tip?

Check out the Microsoft Excel archive and catch up on other Excel tips.

Help users increase productivity by automatically signing up for TechRepublic’s free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

Turn off (or customize) Word’s Smart Cut and Paste

Have you ever carefully selected a block of text to copy somewhere else and been disappointed with the results? Perhaps you lost leading or trailing spaces or paragraph spacing. If so, you’re a victim of Word’s Smart Cut and Paste feature. This feature enables Word to automatically adjust formatting when you paste text. The problem is, you might not want Word to adjust a thing.

This feature is enabled by default, and it works well for most people, most of the time. However, if you’re very specific with your editing, you might find this feature annoying. If that’s the case, simply turn it off –  or better yet, customize it. To disable this feature, do the following:

1. Choose Options from the Tools menu.
2. Click the Edit tab.
3. Uncheck Smart Cut And Paste in the Cut And Paste section.

By disabling this feature, you’ll lose its good points as well as its bad. That’s why customizing it might be more efficient. Instead of unchecking the Smart Cut And Paste option in step 3, click the Settings button to display the options you can control. Then, uncheck the behaviors you want to lose and check those you want to keep. For instance, if you don’t want to lose leading or trailing spaces, just uncheck the Adjust Sentence And Word Spacing Automatically option.

Office Challenge: How do you hide and unhide Access objects?

Everyone knows there’s usually more than one way to do something in any Office app. The key to efficiency is to know which method to use when. Sometimes, it doesn’t matter. But more often than not, one method is more efficient than the others.

Each Friday, we’ll publish a weekly Office Challenge. We hope you’ll share your solutions, all of them: The common, the unique, the insightful, and even the “You’ll think I’m nuts, but…” methods.  We want to hear them all!

This week, tell us how to hide and unhide objects in Access 2003 and Access 2007. There’s more than meets the eye on this one, and Access 2007 has a neat trick that’s easy to miss.

---

Tune in next Friday for our solution, along with a brand new Office Challenge.

« Previous Page — Next Page »