In this article, we will learn to create a button without a macro in Excel. Usually, users create a button in Excel with the help of a macro to perform a particular task. We can also create a button without a macro to perform some predefined tasks. Today, we will demonstrate 3 methods. Using these methods, you can easily create a button in Excel. The last section will also discuss creating a button with the “Record Macro” option.
You can download the practice workbook from here.
3 Easy Ways to Create Button Without Macro in Excel
To explain the methods, we will use two different datasets from different sheets. The first sheet contains Product Info.
On the other hand, the second one contains Sales Info.
In the first method, we will create a button 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. Let’s move to the sections below to learn more about the methods.
1. Use Shapes to Create Button for Opening Particular Sheet Without Excel Macro
In Microsoft Excel, we can use shapes to create a button for opening a particular sheet without a macro. Here, we will hyperlink the desired sheet in the button. Let’s follow the steps below to learn the whole method.
STEPS:
- First of all, go to the Insert tab and click on the Illustrations icon. A drop-down menu will appear.
- Select Shapes from there. It will open a list of shapes.
- From different shapes, we select the normal Rectangular shape.
- After selecting the shape, the cursor will turn into a small black plus (+) sign.
- Now, use the small black plus (+) symbol to draw a rectangular shape like the picture below.
- After that, type a name on it and change the font size according to your preferences.
- In the following step, right–click on the Sales Info button and select Link from the menu. The Insert Hyperlink window will appear.
- In the Insert Hyperlink box, select Place In This Document option and select the sheet name. Here, we have selected Sales Info.
- Click OK to proceed.
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 from the “Type the cell reference” field. If you want to go to Cell B5 of a sheet, then, you need to type B5 in that field.
- As a result, you will be able to create the button on the desired sheet.
- If you click on the Sales Info button, it will take you to the Sales Info sheet instantly.
- Similarly, you can create the Product Info button to move to the Product Info sheet.
Read More: How to Create Button to Link to Another Sheet in Excel (3 Ways)
2. Create Button Without Macro from Quick Access Toolbar
We use buttons to perform a specific task with a single click. If you think from this perspective, we can also add buttons without macro from the quick access toolbar. You can perform certain predefined tasks using this method. For example, you can look at the dataset below. You can 3 misspelled words there. We will add a button to the Quick Access Toolbar to perform the spelling check with one click. We are using the spelling check as an example. You can perform other functions also.
Let’s follow the steps below to see how we can add a button to the Quick Access Toolbar.
STEPS:
- Firstly, click on the Customize Quick Access Toolbar icon. A drop-down menu will appear.
- Select Spelling from the drop-down menu.
- You can choose other options to perform other tasks. For example, to see the Print Preview with a single click, you can add the “Print Preview and Print” button.
- As a result, you will find the Spelling button on the toolbar.
- To perform a spelling check, select the range B4:D8.
- Then, click on the Spelling button.
- Instantly, you will get the Spelling box.
- Select the desired spelling from the Suggestions section and click on the Change option to apply the correct spelling.
- Finally, you will get results like the screenshot below with the help of the Spelling button.
Note: To find more functions:
- Click on the More Commands option.
- Then, select All Commands from the “Choose commands from” section.
- You can use other buttons from here to perform a specific task.
Read More: How to Assign Macro to Button in Excel (2 Easy Methods)
Similar Readings
- How to Create & Apply Option Button Click Event in Excel VBA
- Use Option Button in Excel (2 Easy Ways)
- How to Change Color of Toggle Button When Pressed in Excel
- Group Radio Buttons in Excel (2 Simple Methods)
- How to Change Cell Value Using Toggle Button in Excel
3. Add Button from Developer Tab Without Macro in Excel
In Excel, we can add some buttons from the Developer tab to perform a specific task that will not require any macro. If you select the Button option, then you will need to record a macro. We have discussed this in the last section. But here, we will add a Scroll Button in Column D to set the quantity of the products.
Let’s observe the steps below to see how we can add buttons from the Developer tab.
STEPS:
- In the first place, go to the Developer tab and select Insert. A drop-down menu will appear.
- Select Scroll Button from the “Form Controls” section.
- Secondly, draw the button in Cell D5.
- In the third step, right–click on the button to open the menu.
- Select Format Control from there.
- In the Format Control box, set the cell that you want to link in the Cell link field.
- Now, drag the Fill Handle down to copy the Scroll Button in the below cells.
- At this moment, format each Scroll Button individually and link desired cells.
- Now, click on the Scroll Button to set the quantity of the products.
- Finally, you will see the results like the picture below after setting the quantities.
Read More: How to Add Option Button in Excel (With Easy Steps)
How to Create Button with "Record Macro" in Excel
In the previous sections, we showed methods to create a button without a macro in Excel. In this section, we will use the “Record Macro” option to create a button. You must save the in .xlsm format to run the method correctly. To explain the method, 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.
Let’s pay attention to the steps below to see how we can create a button with the “Record Macro” option.
STEPS:
- In the first place, 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.
- As a result, the cursor will change into a small black plus (+) symbol.
- Now, use the small black plus (+) icon to draw the button.
- Instantly, the Assign Macro box will appear.
- Change the Macro name and select Record from there.
- Another box will appear, click OK to proceed.
- Now, right–click on the button and set the name of the button.
- At this moment, remove contents from Cell C9 and type the formula below:
=SUM(C5:C8)- Press Enter to see the result.
Here, we have used the SUM function to calculate the sum of the range C5:C8.
- In the following step, navigate to the Developer tab and select Stop Recording from there.
- Now, if Cell C9 is blank, then click on the Sum Button.
- Finally, you will see the sum with a single click on the button.
Read More: How to Edit a Macro Button in Excel (5 Easy Methods)
Conclusion
In this article, we have 3 easy methods to Create a Button in Excel Without Macro. I hope this article will help you to perform your tasks efficiently. Moreover, we have also discussed the method to Create a Button with the “Record Macro” in Excel. Furthermore, we have also added the practice book at the beginning of the article. To test your skills, you can download it to exercise. Also, you can visit the ExcelDemy website for more articles like this. Lastly, if you have any suggestions or queries, feel free to ask in the comment section below.
Related Articles
- How to Make a Calculate Button in Excel (with Easy Steps)
- How to Use VBA Code for Submit Button in Excel (with Easy Steps)
- VBA Code for Save Button in Excel (4 Variants)
- How to Clear Cells in Excel with Button (with Detailed Steps)
- How to Hide Columns with Button in Excel (4 Suitable Methods)
- Print to PDF Using Macro Button in Excel (5 Macro Variants)