Save Space With Compact Worksheet Buttons

Last week, AlexJ sent me a tip for making better worksheet buttons, and I’ll be using it from now on, instead of my old method. I’ll show you both button types, and maybe you’ll switch too.

Big Bold Buttons

If I’m making a workbook with several sheets, I usually add a menu sheet at the front, with buttons that link to the other sheets. In the screen shot below, you can see a typical menu, with buttons for two of the worksheets. Each button has a hyperlink to cell A1 on the named sheet.

  • The buttons are wide enough to fit the longest sheet name
  • The font is bold, and big enough to read on the dark background.

They work well, but look a bit big and clunky.

buttoncaptions02

Clear and Compact Buttons

Then, AlexJ sent me his tip for worksheet buttons. Make the buttons small, and let the text flow out to the right side.

  • With this method, all the buttons can be compact – just big enough to see, and click easily
  • The text doesn’t need to be big and bold, because we’ll use black font on the white worksheet.

The captions are clickable too, so that is an extra advantage.

buttoncaptions01

Set Up the Text

Here are the steps for creating a compact button and caption:

  • Add a small shape to the worksheet, and type a caption
  • Right-click on the shape, and click Size and Properties
  • Click the Text Box heading, to see the options
  • Add a check mark to Allow Text to Overflow Shape
  • Remove the check mark for Wrap Text in Shape
  • Add a few spaces at the start of the text, so it moves to the right of the button shape
  • Change the Font colour to black

buttoncaptions03

Download the Sample File

To see how the buttons work, you can download the sample file from AlexJ’s Sample Files page on my Contextures website.

In the Excel Tips section, look for ET0001 – Compact Buttons With Captions. The zipped file is in xlsx format, and does not contain macros.

Watch the Video

To see the steps for creating compact buttons with captions, please watch this video tutorial.

Or watch on YouTube: Save Space With Compact Excel Buttons And Captions

__________________

You may also like...

6 Responses

  1. Hi Debra,

    What I often do if the buttons are just taking you to other worksheet is put hyperlinks in cells and then format the cells:
    – use a fill color
    – use a lighter shade of the same color for the top and left (thick) border
    – use a darker shade of the same color for the bottom and right (thick) border
    – Format the text color and remove the underline so it looks nicer than a standard hyperlink format

  2. Heather says:

    My solution is similar to Jan’s – and I only use this on worksheets with more than a handful of tabs.
    I have a table of contents tab at the front of the document – and often this has hyperlinks to the other sheets as well as a short recap summary (or description if more appropriate) for each sheet.
    The hyperlinks are just formatted to standout for those using the file – usually in this area I make it obvious by leaving them as normal hyperlink formatting.

    But on each sheet in the document, I also have a link back to the table of contents in Cell A1 (with text of TOC or something similar showing), and that is always highlighted to stand out for the reader. To avoid printing that cell – I don’t have anything else in column A (or anything in that column is not for printing), and my print area is formatted to begin with column B.

    The only place I really worry about buttons that look like buttons is on a dashboard where i don’t want the user to have to flip to a TOC page each time. I’ll definitely have to look into incorporating your buttons into a few of these.

  3. John H says:

    So sensible and simple an idea AlexJ! I agree with Jan that for simple hyperlinks I like a formatted 3d cell range but for assigning macros to a shape this is a great idea. No more wasting time fiddling font sizes and aligning. If the empty button is a bit unsettling, you could add a text box with say the word “Select” (or what’s a better word?) in courier to your preferred button and group the text box and the button is ready to be copied and text added for each use.

  4. Khushnood Viccaji says:

    I usually use the text option for hyperlinks to various sheets in my workbooks.

    However, one irritant of this approach was if some of the sheet names were longer than the rest, and the column was too wide for the other, shorter sheet names.
    In such cases, the hyperlinks to shorter sheet names would work only if you click on the text in the cell, rather than the cell itself.
    That is, if you hover the mouse over the ‘white space’ on the right of such text entries, it would show the default Excel mouse pointer – a thick white cross.

    Recently I discovered by accident, that if you apply wrap text to such cells with shorter text (even though it’s not necessary), the mouse pointer would display as a hyperlink pointer (rather than the white cross), even if it is hovered in the blank area of the cell.

Leave a Reply to Heather Cancel reply

Your email address will not be published. Required fields are marked *