Use a Front Row plug-in to shut down a Mac

I bought a Mac Mini to use as a media center. Great, except I found an important feature lacking: you need a keyboard to shut it down (unless you buy Remote Buddy, which for me, was no solution for several reasons). I adapted the frontpython code a bit and created an icon. Et voila: a Shut Down plug-in for Front Row.

You can download it freely from dinkytunes.com; if you like it, please leave a message at the website.

[robg adds: I've seen a number of posts on various sites about how to shut down from Front Row without a keyboard attached. I've mirrored the current version of the file [6.2MB download] here on macosxhints for easier downloading. If you’re reading this hint sometime after its posting date, however, you may want to use the linked site, as there may be updates there that won’t be r…

Use Excel’s Text To Columns command to separate first and last names

If you need to split a list of names into two columns, you don’t have to do it manually. See how this Excel wizard can quickly knock out the task for you.

---

When importing data into a worksheet, it is often necessary to separate the cell contents into two columns. For example, say you’ve imported the list of names shown in Figure A.

Figure A

For your mailing program, you need to separate the first and last names into two columns. Follow these steps:

1. Select A2:A10.
2. Go to Data | Text To Columns. In Excel 2007, click the Data tab and then click the Text To Columns command in the Data Tools group.
3. In Step 1 of the Convert Text To Columns Wizard, click Delimited (Figure B).

Figure B

1. Click Next to advance to Step 2 of the wizard, then select the Space check box and clear the Tab check box in the Delimiters section (Figure C).

Figure C

1. Click Next to advance to Step 3, then click Text under Column Data Format (Figure D).

Figure D

1. Click on the second column in the Data Preview window and then click Text under Column Data Format (Figure E).
2. Enter D2 in the Destination text box and click Finish.

Figure E

Figure F shows the results.

Figure F

---

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.

How to convert imported text into numbers

Reformatting data you’ve imported into Excel can be tricky if it comes in as text. Here are two tricks for working around this problem.

---

Data imported from other sources into Excel often requires reformatting. However, text that is preceded or followed by spaces cannot be reformatted in the usual way. If you try to convert such text to numbers using Excel’s Format Cells command, the errors remain. You need to trim the spaces before you can convert the data.

For example, say you’ve imported the numerical data in column A, shown in Figure A. It is formatted as text, so you need to change it to number format before you can work with it. One method is to click on the Smart Tag, as shown, and select Convert To Number. Excel converts the contents in the cells to numbers.

Figure A

Another method is to use Excel’s Value and Trim functions. Follow these steps:

1. Select C1:C10.
2. Enter the following formula (Figure B):
   =VALUE(TRIM(A1))

Figure B

1. Press Ctrl + Enter.

Excel displays the numerical values for each cell. You can now copy the results to another part of the workbook using the Paste Special option to copy the values without the formulas (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.

Enhance your training with online interactive slide shows

Let your users watch instructional presentations at their own pace. Using PowerPoint’s slide show setup options, you can easily make a presentation viewable online.

---

Instead of just giving your students a copy of the slides in your training presentation, why not give them a copy of the slide show? That way, they can navigate from slide to slide at their own pace. To prepare an interactive slide show for viewing online, follow these steps:

1. Go to Slide Show | Set Up Show. (In Office 2007, click the Slide Show tab and then click Set Up Slide Show in the Set Up group.)
2. Under Show Type, click the Browsed By An Individual (Window), as shown in Figure A.
3. Click OK.

Figure A

Now when your students view the show, they can move through the presentation using the scroll bar or the up-and-down navigation buttons in the right-hand corner of each slide (Figure B).

Figure B

---

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.

How to delete space above a column to even up the text

Word’s spacing options can throw your text out of alignment if you have multiple columns. Here’s a quick fix that will even things up.

---

Word often adds paragraph spacing where you least expect it. For example, say you’ve typed a page of text and used the Columns command to format it into two columns. When you do, you notice that the first column doesn’t quite line up evenly with the top margin (Figure A).

Figure A

This happens when the paragraph is formatted with space above it. Clicking to the left of the first word in the first line of the column and pressing the backspace key won’t eliminate the space. Instead, you will need to turn off the paragraph line spacing for the first paragraph. Follow these steps:

1. Click anywhere in the first paragraph.
2. Go to Format | Paragraph. In Word 2007, click the dialog box launcher in the Paragraph group to open the Paragraph dialog box (Figure B).

Figure B

3. Click in the Spacing Before text box, enter 0 and then click OK.
4. With the extra line spacing turned off, the first line of the first column lines up evenly with the text in the first line of the second column (Figure C).

Figure C

---

Miss a Word tip?

Check out the Microsoft Word archive and catch up on other Word 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.

Clean up your Inbox

Are you still suffering from the New Year itch? If your Inbox is anything like mine, I can’t think of a better spot to scratch than e-mail!

Now, if you don’t think you’re an e-mail packrat, choose Mailbox Cleanup from the Tools menu. Then, click the View Mailbox Size button and thumb through your folders. Surprise! You’ve probably got more (a lot more) e-mail than you thought.

Is your itch worse? Mine sure is. Fortunately, relief is just a few clicks away.

First, use the Find option to find old items — just how old is up to you. If you haven’t responded to an item in the last several months, do you really think you ever will? This feature lets you limit your search in a number of ways, so take a few minutes to look around. Then, run a search and delete your oldest items. (This feature searches the Inbox and personal folders.)

After you delete all the old messages you’re willing to part with, archive old messages that you intend to hang on to for a while longer by clicking AutoArchive. This step isn’t necessary, but it will improve performance if you have a lot of them.

When you’re done, empty the Deleted folder. You cleaned up your Inbox, but you sent all that clutter to the Deleted folder, so it’s still hanging around. Click Empty to toss it out forever. (Warning: Deleting items from the Deleted folder deletes them for good!)

Ahhhhhhhhhhh…

I told you that you’d feel better!

Quickly save the current record in Access using code

There are a number of ways to save data via an Access form. The easiest way is to let a bound form take care of it. However, sometimes even a bound form loses data, unexpectedly. For instance, if a user forgets to press Enter after changing or entering a value and code takes over, VBA won’t consider that unsaved value in any processing. In addition, some developers like to bypass the bound behaviors and control the save process using code. Either way, the Dirty property is usually the most efficient way to save the current record.

The Dirty property applies to both the Form and Report objects. When a user changes a record but hasn’t saved it, the Dirty property returns True – the object is dirty.  When the record hasn’t been changed, Dirty returns False. This Boolean structure makes it easy to save a record quickly — or not — as needed. Simply drop the following statement into code:

If Me.Dirty Then Me.Dirty = False

Setting the object’s Dirty property to False forces Access to save the current record. If the object isn’t dirty, the statement does nothing.

Determine how Outlook displays unread messages

By default, Outlook displays unread messages in your Inbox using a bold font. It works, but you might prefer another way of making unread messages stand out. If that’s the case, read on:

1. In Outlook select the Inbox.
2. Choose Arrange By from the View menu.
3. Select Current View from the resulting submenu.
4. Select Customize Current View.
5. Click Automatic Formatting.
6. At this point, several options are (probably) checked. Whatever options you leave checked will reflect the changes you make in subsequent steps. If you want to change the format only for unread messages, uncheck everything but the Unread Messages option.
7. Click Font.
8. Specify the formats you want to apply.
9. Click OK three times.

It couldn’t be easier — it’s just a matter of knowing where to look for the right option. Don’t settle for defaults unless they’re really what you want.

Save time by changing multiple Access form/report controls at once

When you want to apply the same format to more than one control, it’s far more efficient to update them at the same time. Here’s a look at how to do it.

---

If you need to make the same change to a number of controls, there no need to do it more than once. Access lets you update a property common to each of the selected controls simultaneously. For example, say you would like the label controls on this form shown in Figure A to stand out by changing their Fore and Back Colors.

Figure A

First, select the controls by clicking one control and then holding down [Shift] while selecting the other controls. (You can also select them by dragging the mouse to draw a rectangle around them.) After selecting the controls, follow these steps:

1. Click the Properties tool in the Forms Design toolbar. (In Access 2007, click the Properties Sheet command in the in the Tools Group of the Forms Design Tools tab.
2. Click the Builder Button in the Back Color property and select Black.
3. Click the Builder Button in the Fore Color property and select White (Figure B).

Figure B

When you run the form, all selected controls will display with a white font on a black background, as shown in Figure C.

Figure C

---

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.

Create a timesheet in Excel to track billable hours for your customers

Excel lets you vary the calculations you perform on specified cells using a single formula. Here’s a look at how the technique works and one good example of when you might want to use it.

---

You can use an Excel spreadsheet to keep track of your billable hours: Just list the start time in one column, the end time in a second column and then subtract the first from the second. But what if you don’t charge the same for each block of time? For example, suppose you’re working on three projects for one customer. You want to charge the hours directly spent on the projects in full, but you want to charge the customer for only half the time spent administering the project. Follow these steps:

1. Enter the labels for the worksheet as shown in Figure A.

Figure A

2. Select F2:G9. Right-click the selection and then select Format Cells.
3. Under Category, select Time. Under Type, select 1:30 PM (Figure B). Click OK.

Figure B

4. Select H2:H9. Right-click the selection and then select Format Cells.
5. Under Category, select Custom. Under Type, select h:mm  (Figure C). Click OK.

Figure C

6. Right-click H10 and then select Format Cells.
7. Under Category, select Number (Figure D) and click OK.

Figure D

8. Select H2:H7 and then enter the following function (Figure E):

=IF(OR(E2=”Project 1″,E2=”Project 2″,E2=”Project 3″),G2-F2,(G2-F2)*0.5)

Figure E

9. Press Ctrl + Enter.
10. Enter the data as shown in cells D2:G7 (Figure F).

Figure F

Excel automatically calculates half the time for Admin and full time for the projects. The total number of hours is calculated in H10.

---

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.

Next Page »