how to add button in excel

How to create buttons to open/go to certain sheets in Excel?

Sometimes, you may need to create a button to open a certain worksheet in current workbook, so how to deal with it? Actually there are several methods that can help you solve it.

Create button to open certain sheet with shape and hyperlink
Create button to open certain sheet with Form control button
Create buttons to open all sheets with Kutools for Excel

Use Kutools for Excel's Navigation Pane to list all opening workbooks and their sheets, click to jump to sheets!

Create button to open certain sheet with shape and hyperlink

This method will guide you to insert a rounded rectangle, and then add hyperlink for this rounded rectangle to the certain worksheet. You can do as follows:

1. Click Insert > Shapes > Rounded Rectangle. See screen shot:

2. Draw a rounded rectangle on the worksheet, and you can format it and type text to it as you need. In our case, we type Go To Sheet1 as below screen shot shown:

3. Right click the rounded rectangle, and select Hyperlink from the right-clicking menu.

4. In the opening Insert Hyperlink dialog box,
(1) Select Place in This Document in the Link to section;
(2) Click to select the certain sheet name in the Or Select a place in this document section;
(3) Type the destination cell address into the Type the cell reference box, and at last click the OK button.

Now when you click the rounded rectangle, it will skip to the specified cell of specified sheet at once.

Easily insert multiple macro buttons to go to (each) other worksheets in Excel

For inserting a macro button to go to another worksheet, normally you have to finish all above 5 steps, and it will be quite tedious to insert many macro buttons to go to each worksheet in the current workbook. But, Kutools for Excel's Create List of Sheet Names utility enables you to quickly insert multiple macro buttons to go to each worksheet. Full Feature Free Trial 30-day!
ad create list of sheet names 1

This Create List of Sheet Names utility also supports to batch insert multiple hyperlinks to go to each worksheet (or other worksheets) in current workbook.

Create button to open certain sheet with Form control button

This method will walk you through creating a form control button to open certain worksheet. You can do as follows:

1. Click the Developer > Insert > Button in Form Controls section. See screen shot:

Note: By default the Developer tab does not display in Ribbon, click to know how to show/display developer tab in Excel Ribbon.

2. Draw a button, then an Assign Macro dialog box comes out. In the dialog box, type a name for the macro into the Macro name box, and then click the New button.

3. Now the Microsoft Visual Basic for Applications dialog box opens, please paste following code between sub and End sub. See screen shot:

Code: Go to certain worksheet in Excel

ThisWorkbook.Sheets("Sheet1").Activate

Note: In the code, please change the Sheet1 to required sheet name based on your needs.

4. Save the code, and close the Microsoft Visual Basic for Application window. Now you will get a form control button as below screen shot shown. Click the button, it will skip to the specified worksheet immediately.

Create buttons to open all sheets with Kutools for Excel

Both above methods can only create one button to open one sheet. If you want to batch create multiple buttons to open all worksheets, you should try Kutools for Excel's Create List of Sheet Names utility.

Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now

1. Click the Kutools Plus > Worksheet > Create List of Sheet Names.

2. In the Create List of Sheet Names dialog box,
(1) Check the Contains buttons and macros option in the Sheet Index Styles section;
(2) In the Specify sheet name for Sheet Index box, type a name for the new created sheet;
(3) Specify the position of index sheet in the Insert the Sheet index in drop down list;

3. Click the OK button. Then it will create an index sheet with multiple buttons. Each button is named with its worksheet name and linked to this worksheet. See below screen shot:

Note: This method requires enabling the Trust to the VBA project object model option in the Trust Center.

Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now

Demo: create buttons to open/go to all sheets in Excel

Kutools for Excel includes more than 300 handy tools for Excel, free to try without limitation in 30 days. Download and Free Trial Now!

Navigation Pane: List all opening workbooks and vertical sheet tab for easily switching to any sheet

Kutools for Excel's Navigation Pane lists all opening workbooks and corresponding worksheets vertically as below screenshots. (1) Clicking a workbook in the Workbook section will switch to this workbook window; (2) while clicking a worksheet in the Worksheet section will skip to open this worksheet. Full Feature Free Trial 30-day!
ad navigation pane book sheet 1

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.

kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

Enable tabbed editing and reading in Word, Excel, PowerPoint , Publisher, Access, Visio and Project.
Open and create multiple documents in new tabs of the same window, rather than in new windows.
Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

officetab bottom