Tips to a Cool Workbook in Microsoft Excel


Excel

Also see Ken's article for proper design of your workbook/worksheet.

In order to use these tips by following my directions, you have to create your workbook properly. What does that mean? It means Column Headings in Row 1 (if any) and Row Headings in Column A (if any).

For our example, we're going to keep sales data for our employees (I wish).

Want to make your workbook really easy to navigate?

  1. Make sure all your worksheets have freeze panes turned on so when the user scrolls, the headings remain in place.
  2. Be nice to yourself and don't put spaces in your sheet names.
  3. Make sure all un-like data is on different worksheets. Placing unlike data on the same worksheet makes easy sorting and grouping and subtotaling nearly impossible.
  4. If you think it's impossible to get the report you want in the layout you want, think again. If you lay out your data like a database, you have much more flexibility in working with your data. You can use the AutoFilter and Subtotal functions much more easily, and you can always do a mail merge to Word if you need a special layout.
  5. If you have a summary sheet, set it up as closely as possible to your other data sheets.
  6. Use textboxes if you must have your worksheet "title" on the worksheet.

How to Freeze Panes

When you select the Freeze Panes option, the cells above and to the left of the cursor position become "frozen". So if you want just the first row to be frozen, you place your cursor on A2 first. If you want just the first column to be frozen, place your cursor on B1 first. If you want both the first row and first column to remain frozen, place your cursor on cell B2 first. Then hit Window Freeze Panes.

Spaces in Sheet Names

While you may rarely manually type formulas, when you do, it's a lot easier if you don't have spaces in your sheet names. Look at the first formula compared to the second. The second was in a workbook with spaces in the sheet names.

=SUM(Sheet1!C2:C65536)

=SUM('Sheet 1'!C2:C65536)

So, if you must put spaces in your sheet names, do it after your workbook is built, so you don't have to worry about all those extra characters-Excel will convert them for you if you change the sheet name. However, if you use the methods described in this article, you don't have to worry about using the sheet names for reference anyway.

Using Textboxes for Titles

You can have your title and still maintain a "database" layout. Just make Row 1 about an inch high. Then, using the Drawing toolbar, choose the textbox and create a textbox at the top of your worksheet. You can fill the textbox with any color you like, change the color and size of your font, and the color and thickness of the border. If you don't want it to print when you print your report, then double-click the textbox's border and uncheck Print Object. You can also tell it not to move or size with the cells, so it'll stay put.

You end up with a decent looking header for your worksheet.

Using Textboxes for Navigation

In the workbook shown in graphic above, I created a textbox "navigation" button. Just create the textbox as described previously, and then select it. Hit Insert Hyperlink. Choose Bookmark. Then you can choose any sheet or named range you like.

You can even add a screentip, as shown above, which looks like this.

Go To Buttons

Thanks to my friend Steve Lucas for providing the code I needed to show you how to make Back and Next buttons. These are created with textboxes for the buttons. The code is applied as Steve provides in his VBA Express knowledgebase entry.

Keeping it Clean

The following tips make it so that no matter where the last user was in the workbook when they closed the workbook, it always opens up on the home page, and whenever any worksheet is activated, we're at the top of the data and not down on row 7,638 or wherever the last user left it.

We do this with worksheet events. Right-click each worksheet tab and paste the following code into the code window that appears at right:

Private Sub Worksheet_Activate()
    Range("A2").Activate
End Sub

While you're in the Visual Basic Editor, double-click ThisWorkbook at the left of the screen.

Paste the following code into the code window at right.

Private Sub Workbook_Open()
    Range("Sheet1!A1").Activate
End Sub

Now, on your Home worksheet, go to ToolsOptions, View tab. Uncheck Gridlines and uncheck Row and Column headings. If you want, you can even uncheck Sheet tabs.