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
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.
If you have a summary sheet, set it up as closely as possible to your
other data sheets.
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
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.
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
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
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()
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()
Now, on your Home worksheet, go to ToolsàOptions,
View tab. Uncheck Gridlines and uncheck Row and Column headings. If you want,
you can even uncheck Sheet tabs.
You can download the workbook used for the
here. It does contain macros.
MrExcel.com provides examples of Formulas, Functions and Visual Basic procedures
for illustration only, without warranty either expressed or implied, including
but not limited to the implied warranties of merchantability and/or fitness for
a particular purpose. The Formulas, Functions and Visual Basic procedures on
this web site are provided "as is" and we do not guarantee that they can be used
in all situations.
Access®, Excel®, FrontPage®, Outlook®, PowerPoint®, Word® are registered
trademarks of the Microsoft Corporation.
MrExcel® TM is a registered trademark of Tickling Keys, Inc.