Turn an Excel sheet into graph paper

This past week, I started mapping out new flowerbeds on graph paper. Now, graph paper’s cheap, but making changes takes time and sometimes you just have to start over. Unfortunately, I don’t have specialized software aimed at garden design, so I thought about what I do have–I have Excel! With just a little work, I turned an Excel sheet into a modifiable piece of graph paper.

The trick is to square up the cells. The gauge is less important—like graph paper, a cell can equal anything you want. The hard part is getting the width and height settings to produce a square because there’s no easy way to match a cell’s height and width settings. You can’t just set the row height and column width to the same value because:

• Excel measures row height in points.
• Excel considers the current font when calculating column width; a column width of 5 means that the column will display 5 digits, using Normal style.

You could spend a lot of time tweaking the height and width and you could even try holding a ruler up to your screen, but there’s an easier way: Format an AutoShape as a square and use it as a guide. First, you need to insert and format an AutoShape as follows:

1. From the Drawing toolbar, choose Basic Shapes from the AutoShapes dropdown list.

   
   

2. Double-click the rectangle (the first shape in the first row) and Excel will insert a rectangle into the current sheet.
3. Right-click the rectangle and choose Format AutoShape.
4. Click the Size tab.
5. In the Size and Rotate section, enter .25″ for both the Height and Width to create a square. If you want a larger square, enter a larger value for both measurements.

   
   

6. Click the Properties tab, select the Don’t Move Or Size With Cells option, and click OK.
7. Back in the sheet, move the rectangle (it’s a rectangle object, shaped as a square), to the top-left corner, just over cell A1.

   

Now, use any method you like to resize both the height and width. Perhaps the easiest way is to drag the header and row cells to the appropriate position as follows:

1. Select the entire worksheet (or the area you want to resemble graph paper). To select the entire sheet, click the sheet selector to select the entire sheet—that’s the cell that intersects the row and column headers (in the top-left corner).
2. Hover the mouse over the right border of column A’s header cell.
3. When the cursor turns into a double-arrow, drag the border until it’s flush with the rectangle’s right border. Excel will resize the width of all the selected columns, not just column A.
4. To resize the height, adjust the bottom border of row 1 until it’s flush with the bottom of the rectangle. Again, Excel will adjust all the selected rows.

   

At this point, you have a sheet full of .25 inch cells. Move the AutoShape rectangle at cell A1 or delete it–you’re done with it. If the cells are still a bit too large, set the Zoom property to 50%. Then, start adding the appropriate components by formatting cells and adding AutoShapes. Be sure to add a legend to identify all those components.

As you change your mind, it’s easy to reformat cells and delete objects. No more erasing, no more starting over!

How to quickly remove all hidden text in Word

It’s easy to hide text in Word. You just assign the Hidden format. You can hide anything, but most likely, you’ll hide confidential or otherwise sensitive data. Now, you might be wondering why you wouldn’t just delete the text rather than hide it. Sometimes, it’s more efficient to hide it from others, when you know you’ll need to refer to it later. Unfortunately, it’s easy to forget that the hidden text is there — it’s hidden, after all! If you’re in the habit of hiding text, you might want to enhance that habit a bit by adding a final step: Remove that hidden text before you distribute the document.

Before you can find hidden text, you have to have hidden text to remove. Fortunately, that’s the easy part. To hide text, do the following:

1. Select the text you want to hide.
2. From the Format menu, choose Font.
3. Click the Font tab.
4. Check the Hidden option in the Effects section.

Uncheck the Hidden option to display hidden text. Or click Show/Hide on the Standard toolbar — t’s faster. Show/Hide is a toggle, so a second click rehides all the hidden text when you’re ready to put it out of sight.

Viewing all the hidden text can be helpful, but it won’t remove it. To remove all hidden text in a document, do the following:

1. From the Edit menu, click Replace. Or press [Ctrl]+H.
2. Click the Find What control.
3. Click More.
4. Click the Format button and then choose Font from the resulting menu.
5. Check the Hidden option in the Effects section.
6. Click OK.
7. Click Replace All and Word will delete any text to which you’ve applied the Hidden format.

If you change your mind, just press [Ctrl]+Z to retrieve the deleted text. One word of warning: Removing hidden text could displace other text, so be sure to look the document over well before distributing.

Show week numbers in Outlook’s Calendar

Knowing the week number—its position from the first week of the year—is vital in many operations. By default, Outlook doesn’t display week numbers, but that doesn’t mean it isn’t possible, and you don’t need code or an add-in. You just need to know which setting to check.

You can display week numbers in the Date Navigator (that’s the small monthly calendar in the top-left corner), by completing the following steps:

1. From the Tools menu, choose Options.
2. Click the Calendar Options button.
3. In the Calendar Options section, check the Show Week Numbers In the Date Navigator option.

   

Click OK twice and Outlook will display week numbers in the Date Navigator.

The numbers to the left of each week represent that week’s position with the 52-week year. For instance, the week of March seventh through the thirteenth is the eleventh week of the year. Outlook 2007 will also display week numbers in Month View.

What simple Outlook setting really makes a difference for you?

Office challenge: How can you keep others from changing a PowerPoint presentation?

Recently, a colleague was embarrassed when half-way through a presentation, he found a few changes he hadn’t made! He had shipped the presentation to his company contact who took care of setting up the room and equipment. He had checked the equipment and everything worked fine. However, it never occurred to him that someone might have actually changed the presentation.

He was fortunate, the changes were subtle, and he actually liked them, but the surprise in the middle of the presentation unnerved him. He stumbled for an awkward second or two before he recovered. Nobody in the audience noticed, but it was unpleasant for him.

What would you do to protect your presentation? 

Last week we asked:

What’s the quickest way to enable the Macro Recorder in Word? If you know this one, you know how easy it is; if you don’t know it, you might be surprised. The quickest way to launch the Macro Recorder is to double-click the REC indicator on the Status bar (at the bottom of the screen). Recording your keystrokes is just a double-click away! The problem is that a lot of people ignore the Status bar. That’s unfortunate, because it hosts several good shortcuts. Along with REC, which enables the Macro Recorder, there are three toggle indicators:

• TRK toggles the Track Changes feature.
• EXT toggles the extended selection mode.
• OVRtoggles overtype mode.

In addition, if you double-click any of the indicators on the left, from Page to Col, Word will display the Go To tab in the Find and Replace dialog box. This isn’t any more efficient than pressing [F5], but some systems disable or usurp the function keys. If you’re supporting or working with one of these systems, double-clicking the indicators on the Status bar is a welcome alternative. Double-click the Language indicator to display the Language dialog box.

Ultimitloozer was the first to respond with the REC indicator on the Status bar. Happymedia_dz also had the right answer and included instructions for displaying the indicator in Word 2007. Thank you both!

What’s challenging you this week? Leave a comment and perhaps someone will have the right solution for you!

Four ways to insert an em dash in a Word document

• You’ve inserted space characters between the words and the hyphen characters. When you enter spaces between the hyphens, Word formats the hyphens as an en dash ( – ), which is shorter than an em dash.
• Someone has disabled the AutoCorrect option that formats hyphens as an em dash.

Now, this default won’t work for everyone every single time. If you occasionally need two hyphens instead of an em dash, you can press [Ctrl]+Z and Word will undo the em dash character and restore the hyphens. If you find yourself doing this a lot, it might be more efficient to disable the AutoCorrect option and enter an em dash, when you require it, manually. You can disable this option as follows:

1. From the Tools menu, choose AutoCorrect Options.
2. Click the AutoFormat As You Type tab.
3. Uncheck the Hyphens ( — ) With ( — ) option.
4. Click OK.

After disabling the AutoCorrect option, you’ll have to enter an em dash manually. Fortunately, there are three easy methods:

• Press [Ctrl]+[Alt]+-. You must use the minus sign (-) on the numeric keypad; if you use the hyphen character on the alphanumeric keypad, Word will change the cursor.
• Hold down the [Alt] key and type 0151 on the numeric keypad.
• Choose Symbol from the Insert menu, click the Special Characters tab, highlight the em dash, and click Insert.

How to acquire, position, and hide a Calendar control in an Excel sheet

My recent post on embedding a Calendar control in an Excel sheet generated a lot of interesting questions. In this post, I’ll answer three of them:

• How to get a copy of the control
• How to control the position of the control
• How to hide the control

How to get a copy of the Calendar control

The easiest and safest way for Office 2003 users to get this control is to download and install Office 2003 Service Pack 3. Be sure to install the file in the right folder: C:\Windows\System32. (Depending on your version of Windows, you might have a System folder instead of a System32 folder.)

If you’re using Excel 2007 but haven’t installed Access, you might not see the control in the list of ActiveX objects. Simply install Access. It’s not the best solution, but it’s probably the safest. There are a number of sites that host the control, and they’re most likely safe, but downloading is always a risk.

If you still can’t see the control, register it. You can find more information on registering an ActiveX control in the following articles at Microsoft.com:

• Excel 2007: Add or register an ActiveX control
• Excel 2003: Add or register an ActiveX control

How to control the position of the Calendar control

When you embed a control in a sheet, it stays where you put it. If your sheet is larger than a single screen — and most are — you’ll quickly lose sight of, and easy access to, the control. The easiest way to handle this problem is to freeze the pane. Just be sure to embed the control above the part of the sheet you’ll be working with. That way, it will always be visible. However, this solution is a bit awkward.

First, if the sheet already exists, adding the necessary rows to accommodate the Calendar might have repercussions. Be sure to save a copy of the workbook before you start. Second, you still have to move from the active cell to the top of the sheet to click the Calendar. That’s not a big deal, but it could get tiresome if you’re entering a lot of dates this way.

Using VBA, you can attach the control to the active cell. This way, the calendar’s always right where you need it. But having the calendar move a little every time you select a cell can become an annoyance too. In the end, you’ll just have to choose what works best for you.

To anchor the control to the active cell, do the following:

1. Display the Control Toolbox toolbar and click Design Mode.
2. Double-click the control to launch the sheet’s module.
3. Enter the following code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
‘ Anchor control to the active cell.

Calendar1.Left = ActiveCell.Left + ActiveCell.Width + 30

Calendar1.Top = ActiveCell.Top – 30

End Sub


1. Return to Excel.
2. Click Exit Design Mode on the Control Toolbox toolbar.

Select any cell to trigger the sheet’s Selection Change event and see how the control reacts. The control will always be just a quick click away from the active cell. The example code adds a combination of values to position the control. In this case, I added 30 to the control’s top and left borders. Subtracting a value from the Top value helps to center the calendar vertically with the active cell. If you add a value, you’ll push the control down. You can adjust these value to position the control just where you want it. You might even choose not to offset the control at all and omit the additional value.

How to hide the Calendar control

The Calendar control has a Visible property. By setting this property to False, you can hide the control. But choosing the right method for this can be tricky. For instance, you could set the Visible property to False via the control’s Double Click event –  but then, how would you unhide it? You’d have to remember exactly where the control was when you hid it. That’s too hard. You could add a new command to a toolbar that hides and unhides the control. Perhaps the easiest way is to hide the control conditionally. For example, you might want to hide the control when the active cell doesn’t contain a date — that makes the most sense. Then, you don’t have to hide and unhide it at all; Excel does it all for you.

Use the following subprocedure to hide the control, conditionally:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

‘ Anchor control to the active cell.

‘ Display control only when active cell contains a date.

If IsDate(ActiveCell.Value) Then

With Calendar1

.Visible = True

.Left = ActiveCell.Left + ActiveCell.Width + 30

.Top = ActiveCell.Top - 30

End With

Else

Calendar1.Visible = False

End If

End Sub

Office poll: What’s your most common support call on Excel?

Many of us support Excel users, and those users generate a lot of our calls! What problems or questions do you get the most calls for? If you don’t see it on the poll list, please add a comment below. If you don’t support Excel users, what problem or issue do you run into the most when using Excel yourself?

Note: There is a poll embedded within this post, please visit the site to participate in this post’s poll.

Inhibit Excel’s #DIV/0! error

If an equation or formula attempts to divide a value by 0, Excel displays the #DIV/0! error, but that doesn’t mean the formula you’re using is wrong. Most likely, the logic is sound enough. In mathematical terms, Excel returns this error when the divisor is blank or 0. As you can see in the spreadsheet below, the formula in questions works fine four out of five times. The formula =D4/B4 in cell E4 returns this error value because cell B4 is 0.

If you’re the only one using the spreadsheet, you probably don’t need to do a thing; you know what the error means. However, if you share the spreadsheet, you’ll probably want to inhibit this error value to avoid confusion or worse — someone might think you’re sloppy or even incompetent.

To inhibit the display of this error, you can use the IF() function as follows: 

1. Select cell E2 because you’ll want to inhibit all of the formulas in column E, not just the one that’s currently displaying an error value.
2. Replace the simple equation with the following function: =IF(B2=0,””,D2/B2).
3. Using the Fill handle, copy the new function to cells E3:E6.

When the value in column B is 0 or the cell is blank, the IF() function returns an empty string — that’s the double quotation marks component — instead of #DIV/0! error. You can replace the empty string (””) with a more descriptive string, such as “Not applicable,” if that makes more sense within the context of your spreadsheet’s purpose.

Don’t let the #DIV/0! error value spoil an otherwise sound spreadsheet. Use the IF() function to suppress the error.

Office challenge: What’s the quickest way to enable the Macro Recorder in Word?

If you use VBA with Word, you know that turning on the Macro Recorder is often the quickest way to automate a task. You could try to beat out the code yourself, but why work so hard when you don’t have to? Of course, you won’t always get exactly the code you need, but with a little tweaking, you can usually get the job done quicker by starting with the recorded code. So, what’s the quickest way to enable the Macro Recorder in Word? (Note, I didn’t say the most traditional method. I asked for the quickest method.)

Last week we asked:

What keyboard shortcuts let you change the size of a word or phrase in Word? Your response was overwhelming and it was a fun challenge!

The keyboard shortcut I had in mind was [Ctrl]+> to increase the font size of the current word or selected text and [Ctrl]+< to decrease the font size. The greater than and less than signs were the clue I mentioned. If you’re really focused, that connection just pops right out at you.

Len222 was the first to respond with a shortcut — [Ctrl]+] and [Ctrl]+[ -- although they weren't the pair I had in mind.  Len222's shortcuts are simpler, as you don't have to remember the [Shift] key (< and > are [Shift] characters).  But the  bracket keys aren’t as intuitive as the < and > signs. If you’ve got them memorized, they work just as well! But a word to the wise, they aren’t interchangeable: The < and > shortcuts default to the Font Size control to determine the percentage increased or decreased; the [ and ] shortcuts decrease and increase by one point.

Steve.McCurdy was the first to mention the < and > shortcuts. A related shortcut, definitely worth noting is [Ctrl]+[Shift]+P. This shortcut gives focus to the Font Size control. You can then enter the size you want and press [Enter] to change the size of the current word or selected text.

Thanks to everyone for taking part in this week’s challenge!

Think you can stump everyone with an interesting challenge? Feel free to post it in the Comments section.

Cut down on errors by automating an Excel calendar control

Anyone entering dates in Excel should receive hazard pay. Even the best designed spreadsheet can confuse a user and frankly, it’s difficult to enter dates manually. Using the Calendar control can help. Instead of entering dates, you just click a date on a calendar! It couldn’t be simpler.

You might have seen this control in a user form, but you might not realize that you can embed one right into a sheet and use it in the same way. Customizing this control can be a complex process, but getting started is simple. First, you embed the control into a sheet. Then, you add the VBA code necessary to run the control. You embed and automate the control as follows:

1. Display the Control Toolbox toolbar. To do so, right-click the background of any toolbar or menu and check Control Toolbox from the resulting list.
2. On the Control Toolbox toolbar, click the More button. That’s the hammer and wrench icon at the bottom.

3. From the resulting list of additional controls, choose Calendar Control. (Your version number may differ.)

4. Click in the sheet where you want the Calendar control to appear. A simple click is all that’s required. You don’t have to drag the mouse to size the control. However, you can resize the control later. Notice that the Formula bar identifies the Calendar control (when the control is selected). At this point, you can right-click the Calendar control and change a number of properties, but we won’t explore those possibilities right now. Don’t close the Control Toolbar yet — it needs to remain open while you’re designing the control.
   
5. At this point, you’re ready to add the VBA code that will run the calendar. Double-click the embedded control to launch the sheet’s module in the Visual Basic Editor (VBE). In the Properties window, you can see that Excel named the embedded control Calendar1.

6. Enter the following code in the sheet’s module:

   Private Sub Calendar1_Click()
‘Enter clicked date into selected cell.
ActiveCell.Value = Calendar1.Value
End Sub


7. Return to the Excel sheet.
8. Click Exit Design Mode on the Control Toolbox toolbar (that’s the first icon). Excel won’t execute anything in the Calendar control until you exit Design mode. Later, if you want to change the control, display the Control Toolbar and click Design Mode.

If you’re using Excel 2007, the process is similar, but accessing the control is different. You’ll need to do the following:

1. Click Insert in the Controls group on the Developer tab. Then, click More Controls.
2. Follow steps 3 through 7 above.
3. To exit Design mode in Excel 2007, click Design Mode on the Developer tab in the Controls group.

The Calendar control is ready to use. For example, to enter a date into the empty cell, I7 (using the spreadsheet shown above) you’d select I7. Then, click any date in the embedded Calendar control. Clicking a date will execute the control’s Click event, which will enter the clicked date into the selected cell. With the Calendar control and one line of code you have eliminated data entry typos!

Two things are worth noting:

• If the Calendar control isn’t available (in step 3), you may need to install or register it. This seldom happens, but if it happens to you, review the article Add or Register an ActiveX Control. The process is similar for Excel 2003, but you’ll access the commands via the Control Toolbox toolbar instead of the Developer tab.
• If the Developer tab in Excel 2007 isn’t visible, you can display it quickly enough: Click the Office button; click Excel Options; select Show Developer Tab In The Ribbon (in the Top Options For Working On The Ribbon section); and click OK.

Do you have an Excel workbook that uses a Calendar control? If so, tell us about it. Or have you tried to use this control and failed? If that’s the case, share your experience here. Maybe someone will have a solution for you.

Next Page »