What is button in excel
In Microsoft excel buttons are added in a worksheet by assigning the macro. The work of the button is to perform the task that is recorded in the macro.
You can create button in excel using two methods. First one is using the filter and the second one is the advanced filter. So, I’ll teach you How to create button in excel with filter and with advanced filter. Read the Whole Article Carefully about how to create button in excel, Because after Reading this article you actually learn that how to create button in excel.
How to create Macro in Excel
A macro in excel is used to record each command and action you perform in your spreadsheet or document. It is a set of instruction used to replace the repetitive command and actions of the keyboard and mouse. Macro can be run to record commands in both Microsoft excel and Microsoft word. The file extension used to save a macro file is .mac
Creating Button in Excel
In excel filter and macro features are used to create buttons. To create buttons, you need to follow the simple steps:
- Create the table for which you want to create the buttons.
- Go to the view tab and select macros and then select record macros, name your macro and press ok.
- Select your table and then go to data tab and select filter.
- Then select the filter of that column and select the data for which you are creating that button.
- Again go to the view tab and select macros and select stop recording.
- Go to Insert tab and select a shape for the button. Insert the shape in your worksheet and then right click on the shape and select assign macro.
- Click on the button and see the result.
How to create button in excel using filter:-
Let us take an example of some customer’s eligibility Test.
- Create the table of the customer’s record like the customer’s id, name, age, and eligibility.
- Go to the view tab and select macros and then record the macro, name the macro as eligible and press ok.
- Then select your table and apply filter on it.
- After applying the filter on the table, select the eligibility column’s filter and then select the eligible option.
- Then go to view tab and select stop recording in macros.
- Then insert a shape for the button and name the button as eligible.
- Right click on the button and select assign macro. After that select eligible.
- Click on your button and then see your result. You will get the data of all eligible customer in the table.
- Perform the same steps for creating the button of not eligible customers.
Create Button in excel using advanced filter
There are two features by which you can create buttons using the advanced filter. First one is advanced filter in data tab and second is macro in view tab.
Advanced filter in excel
Advanced Filter in excel helps us to create a unique item list in excel, or to extract and specific item for your data. You can also use a specific criteria in Advanced Filter in excel, so it is useful when a simple AutoFilter can’t do what you need.
Steps of creating buttons using advanced filter:-
1 – Create the table for filter your data by the help of button.
2 – Create a criteria on which basis you want to create buttons.
3 – go to view tab and select macros.
4 – In macros, select record macro. In macro name, type the name of your macro.
5 – Go to data tab and select advanced in sort and filter.
6 – In advanced filter:
- You have to first select the action between filter the list, in place and copy to another location.
- If you select to filter the list, in place, it means that the data will be filtered at the place where the listed range is selected.
- In list range, you have to select your table.
- In the criteria range, you have to select the criteria.
- If you select copy to another location, it means that your data will be filtered at that place where you select the range.
- In copy to, you have to select the range where you want to place your data (remember that when you are selecting the range in the copy to, the number of columns should be equal to the number of columns in your table and number of rows may be different).
7 – Again go to view tab, select macros and then select stop recording.
8 – Go to insert tab and select shape to create a button.
9 – After inserting the shape in your worksheet, right click on the shape and select assign macro. After that select your macro name and click on the button and then check your result.
How to create button in excel using the advanced filter:-
Let us take an example of 10 students in a class. Here you have to create a button for the remarks of the students like, pass or fail.
- Firstly you have to create the table of the student’s record like their roll no., name, marks in each subject, total marks, average, and remarks.
- Next, you have to create the criteria of remarks. You can also create the criteria with the help of data validation. Go to the data tab and select data validation. In settings, allow list and then select the source (pass and fail from the table) and click on ok.
- Now record the macro. Go to the view tab and select macros and then select the record macro. Name the macro as remarks and click on ok.
- Go to the data tab and select advanced and then select the action, list range (A5: J15), criteria range (N1: N2) and copy to (A18: J33).
- Then go to view tab and select macros and then select stop recording.
- Now go to insert tab and select any one shape for the button.
- After inserting the shape, right click on it and select assign macro.
- After that select remarks and press ok.
- Now select pass in the criteria and press on the button. You will get the record of all Passed students.
- similarly, select fail in the criteria and press on the button. You will get the record of fail students.
So, Finally we get learned that how to create button in excel, this is a little complicated process but you can learn it easily after reading this article carefully, then you really get that how to create button in excel.