How to Create Button Without Macro in Excel (3 Easy Ways)

 

We will use two different datasets from different sheets. The first sheet contains Product Info.

create button in excel without macro

The second one contains Sales Info.

create button in excel without macro

In the first method, we will create a button in Excel to navigate from the Product Info sheet to the Sales Info sheet and vice versa. We will use only the Product Info dataset in Method 2 and Method 3.


Method 1 – Use Shapes to Create a Button for Opening a Particular Sheet via Hyperlink Without Excel Macro

Steps:

  • Go to the Insert tab and click on the Illustrations icon. A drop-down menu will appear.
  • Select Shapes. It will open a list of shapes.

Use Shapes to Create Button for Opening Particular Sheet Without Excel Macro

  • We selected the normal Rectangular shape.

Use Shapes to Create Button for Opening Particular Sheet Without Excel Macro

  • The cursor will turn into a small black plus (+) sign.
  • Click and drag the small black plus (+) symbol to draw a rectangular shape like the picture below.

Use Shapes to Create Button for Opening Particular Sheet Without Excel Macro

  • Type a name on it and change the font size if you want.

Use Shapes to Create Button for Opening Particular Sheet Without Excel Macro

  • Rightclick on the Sales Info button and select Link from the menu. The Insert Hyperlink window will appear.

Use Shapes to Create Button for Opening Particular Sheet Without Excel Macro

  • In the Insert Hyperlink box, select the Place In This Document option and choose the sheet name. We have selected Sales Info.
  • Click OK to proceed.

Use Shapes to Create Button for Opening Particular Sheet Without Excel Macro

Note: When you click on the button, it will take you to the Cell A1 of the Sales Info sheet. You can change the cell in the “Type the cell reference” field. If you want to go to Cell B5 of a sheet, type B5 in that field.

  • This creates a button on the desired sheet.

Use Shapes to Create Button for Opening Particular Sheet Without Excel Macro

  • If you click on the Sales Info button, it will take you to the Sales Info sheet.

  • You can also create the Product Info button to move to the Product Info sheet.

Read More: Excel Hyperlink with Shortcut Key


Method 2 – Create a Button Without Macro from the Quick Access Toolbar

In the dataset below, there are 3 misspelled words. We will add a button to the Quick Access Toolbar to perform a spell check with one click.

Create Button Without Macro from Quick Access Toolbar

Steps:

  • Click on the Customize Quick Access Toolbar icon. A drop-down menu will appear.

Create Button Without Macro from Quick Access Toolbar

  • Select Spelling from the drop-down menu.
  • You can choose other options to perform other tasks. To see the Print Preview with a single click, you can add the “Print Preview and Print” button.

Create Button Without Macro from Quick Access Toolbar

  • You will get the Spelling button on the toolbar.

Create Button Without Macro from Quick Access Toolbar

  • Select the range B4:D8.

Create Button Without Macro from Quick Access Toolbar

  • Click on the Spelling button.

Create Button Without Macro from Quick Access Toolbar

  • You will get the Spelling box.
  • Select the desired spelling from the Suggestions section and click on the Change option to apply the new spelling.

Create Button Without Macro from Quick Access Toolbar

  • You will get results like the screenshot below.

Note: To find more functions:

  • Click on the More Commands option.

  • Select All Commands from the “Choose commands from” section.
  • You can add other buttons from here.

Read More: How to Create Button to Link to Another Sheet in Excel


Method 3 – Add a Button from the Developer Tab Without Macro in Excel

We will add a Scroll Button in Column D to set the quantity of the products.

Add Button from Developer Tab Without Macro in Excel

Steps:

  • Go to the Developer tab and select Insert. A drop-down menu will appear.
  • Select Scroll Button from the “Form Controls” section.

Add Button from Developer Tab Without Macro in Excel

  • Draw the button in Cell D5.

Add Button from Developer Tab Without Macro in Excel

  • Rightclick on the button to open the menu.
  • Select Format Control from there.

Add Button from Developer Tab Without Macro in Excel

  • In the Format Control box, set the cell that you want to link in the Cell link field.

Add Button from Developer Tab Without Macro in Excel

  • Drag the Fill Handle down to copy the Scroll Button in the below cells.

  • Format each Scroll Button individually and link the desired cells.

  • Click on the Scroll Button to set the quantity of the products.

  • You will see the results like the picture below after.


How to Create a Button with Record Macro in Excel

We will use a dataset that contains the sum of the sales for the first four months. We will assign a macro in the button to calculate the sum of the range C5:C8 automatically.

How to Create Button with “Record Macro” in Excel

Steps:

  • Go to the Developer tab and click on the Insert icon. It will open a drop-down menu.
  • Select the Button icon from the Form Controls section.

How to Create Button with “Record Macro” in Excel

  • The cursor will change into a small black plus (+) symbol.

How to Create Button with “Record Macro” in Excel

  • Click and drag the small black plus (+) icon to draw the button. The Assign Macro box will appear.
  • Change the Macro name and select Record from there.

How to Create Button with “Record Macro” in Excel

  • Another box will appear with default values. Click OK to proceed.

How to Create Button with “Record Macro” in Excel

  • Rightclick on the button and set its name.

How to Create Button with “Record Macro” in Excel

  • Remove the contents of Cell C9 and insert the formula below:
=SUM(C5:C8)
  • Press Enter to see the result.

How to Create Button with “Record Macro” in Excel

We have used the SUM function to calculate the sum of the range C5:C8.

  • Navigate to the Developer tab and select Stop Recording from there.

  • If the Cell C9 is blank, click on the Sum Button.

  • You will see the sum with a single click on the button.

Read More: How to Convert Text to Hyperlink in Excel


Download the Practice Workbook


Related Articles

<< Go Back To Hyperlink in Excel | Linking in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Mursalin Ibne Salehin
Mursalin Ibne Salehin

Mursalin Ibne Salehin holds a BSc in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. Over the past 2 years, he has actively contributed to the ExcelDemy project, where he authored over 150 articles. He has also led a team with content development works. Currently, he is working as a Reviewer in the ExcelDemy Project. He likes using and learning about Microsoft Office, especially Excel. He is interested in data analysis with Excel, machine learning,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo