What is Ribbon in Excel?
The ribbon is basically an element of the UI (User Interface) at the top of Excel. In simple words, the ribbon can be called as a strip that consists of buttons or tabs which is seen at top of the excel sheet. The ribbon was first introduced in Microsoft Excel 2007.
In an earlier version of Excel, there were menu and toolbar which was replaced by a ribbon in 2007. The basic tabs under Ribbon are – Home, Insert, Page Layout, Formulas, Data, review, and view. It can be customized according to the requirements. See the image below. The highlighted strip is called ribbon which consists of tabs like Home, Insert, etc.
How to Customize Ribbon in Excel?
Below are the steps to customize the ribbon.
Step 1 – Right-click anywhere on the ribbon and it will open a pop up with some options including “Customize the Ribbon”.
Step 2 – This will open the Excel Options box for you.
Step 3 – You can see two options on the screen. You can find customize the ribbon on the right side and choose the command from the option on the left side.
In the drop-down below, there are 3 options to customize the ribbon. By default, Main Tabs is selected. The other two are Tool Tabs and All Tabs.
Step 4 – You can click on the (+) sign to expand the list.
You will see some more tabs under the Main Tabs.
You can shrink the list by clicking on (-) sign.
Step 5 – Select or Deselect required tab to customize your ribbon and it will appear on your sheet accordingly.
You can also add additional tabs to your sheet by following the below steps.
- Click New Tab or New group and rename it with some name (not necessary) by clicking on rename option.
- Go to choose a command from option and select the desired option from the dropdown.
- Add command to the tab or group you have created.
Click on File Menu —> Options
This will open excel options for you where you will see the option to customize the ribbon.
How to Collapse (Minimize) Ribbon in Excel?
You can collapse the ribbon by doing right-click anywhere on the ribbon and then select collapse the ribbon option.
How to Use a Ribbon in Excel with Examples
Below are some examples where you required customization of the ribbon.
Someone asks you to record a macro or write a code in VBA. How will you do that?
We can use excel shortcuts Alt + F8 to record Macro and Alt + F11 to open VBA screen. But remembering shortcuts is not easy every time so here is another option.
Shortcut key to Record Macro:
Shortcut key to Open VBA Screen:
Add developer ribbon in excel by using the following steps. Go to customize the ribbon and this will open the excel options box.
Check on developer option shown in the list under main tabs. See the image below. Click Ok.
You will see the developer tab under your ribbon. See the image below.
You can see the macros or basic visual screens option.
Someone asks you to create an interactive dashboard using Power View in Excel 2016 version.
We know that Power View Option is hidden in Excel 2016. So, we need to follow the below steps to add power view command in our excel. Go to customize the ribbon.
Under customize the ribbon, extend the insert option than click on the new group.
Now go to choose the command from shown in the left and select command not in the ribbon from the dropdown now select insert a power view report. Click on add and this will add power view under your insert tab. (When you click on add make a sure new group (custom) is selected else an error will pop up). Select Ok. See below image:
Now you can see the power view option under insert tab in new group section:
Let’s take another scenario.
Suppose we are working on a report where we require the sum of the values in subsequent rows or column very frequently.
To sum up the values, we need to write a sum function whenever the total value is required. Here we can simplify our work by adding Autosum command to our ribbon. Go to customize the ribbon.
Under customize the ribbon, Extend home than click on the new group.
Now go to choose the command from shown in the left and select “All commands” from the dropdown, now select ∑ Autosum option. Click on add and this will add ∑ Autosum under your home tab. (when you click on add make a sure new group (custom) is selected else an error will pop up). Select Ok. See Below Image:
Now you can see the Autosum option under Home Tab in New Group section:
Now let us see the use of it.
We have some numbers in cell A1 to A7 and we need to get sum in A8. Select cell A8 and click on Autosum and it will automatically apply SUM formula with for active range and give you the SUM.
We get the following result.
Things to Remember
- Slow the performance by adding tabs to the ribbon. So, add and keep only those tabs whichever is required frequently.
- Add command to a group or tab which is logical so that it will be easy for you to find that command.
- When adding commands from the command, not the ribbon, make sure you have created a new custom group, otherwise, the error will be displayed.
This has been a guide to ribbon in excel. Here we discuss how to customize, collapse, and use ribbon in excel along with examples and explanations. You can learn more about excel from the following articles –