How to use Ribbon in Excel 2022

Excel selects the ribbon’s Home tab when you open it. Learn how to use the ribbon.

Tabs

The tabs on the ribbon are: File, Home, Insert, Page layout, Formulas, Data, Review, View and Help. The Home tab contains the most frequently used commands in Excel.

Tabs

Groups

Each tab contains groups of related commands. For example, the Page Layout tab contains the Themes group, the Page Setup group, etc.

Groups

Use the Ribbon

Let’s use the ribbon to insert a table. Tables allow you to analyze your data in Excel quickly and easily.

1. Click any single cell inside a data set.

2. On the Insert tab, in the Tables group, click Table.

Use the Ribbon

3. Excel automatically selects the data for you. Check ‘My table has headers’ and click on OK.

Create Table

Result. Excel creates a nicely formatted table for you.Excel Table

Note: use the drop-down arrows to quickly sort and filter. Visit our chapter about tables to learn more about this topic.

Collapse the Ribbon

You can collapse the ribbon to get extra space on the screen. Right click anywhere on the ribbon, and then click Collapse the Ribbon (or press CTRL + F1).

Collapse the Ribbon

Result.

Hidden Ribbon

Quick Access Toolbar in Excel

If you use an Excel command frequently, you can add it to the Quick Access Toolbar. By default, the Quick Access Toolbar contains four commands: AutoSave, Save, Undo and Redo.

Quick Access Toolbar 101

To add a command to the Quick Access Toolbar, execute the following steps.

1. Right click the command, and then click Add to Quick Access Toolbar.

Add to Quick Access Toolbar

2. You can now find this command on the Quick Access Toolbar.

Quick Access Toolbar in Excel

3. To remove a command from the Quick Access Toolbar, right click the command, and then click Remove from Quick Access Toolbar.

Remove From Quick Access Toolbar

Commands Not in the Ribbon

To add a command to the Quick Access Toolbar that isn’t on the ribbon, execute the following steps.

1. Click the down arrow.

2. Click More Commands.

More Commands

3. Under Choose commands from, select Commands Not in the Ribbon.

4. Select a command and click Add.

Add Command

Note: by default, Excel customizes the Quick Access Toolbar for all documents (see orange arrow). Select the current saved workbook to only customize the Quick Access Toolbar for this workbook.

5. Click OK.

6. You can now find this command on the Quick Access Toolbar.

Click Command on Quick Access Toolbar

Customize the Ribbon in Excel

The ribbon in Excel can be customized. You can easily create your own tab and add commands to it.

See also  How to Enable Macros in Excel 2022

1. Right click anywhere on the ribbon, and then click Customize the Ribbon.

Customize the Ribbon in Excel

2. Click New Tab.

Click New Tab

3. Add the commands you like.

Add Commands to the Ribbon

4. Rename the tab and group.

Rename Tab and Group

Note: you can also add new groups to existing tabs. To hide a tab, uncheck the corresponding check box. Click Reset, Reset all customizations, to delete all Ribbon customizations.

5. Click OK.

Result.

Custom Tab

Developer Tab in Excel

Turn on the Developer tab in Excel if you want to create a macro, export and import XML files or insert controls.

To add the Developer tab to the ribbon, execute the following steps.

1. Right click anywhere on the ribbon, and then click Customize the Ribbon.

Customize the Ribbon in Excel

2. Under Customize the Ribbon, on the right side of the dialog box, select Main tabs (if necessary).

3. Check the Developer check box.

Turn on the Developer Tab

4. Click OK.

5. You can find the Developer tab next to the View tab.

Developer Tab

Status Bar in Excel

The status bar in Excel can be quite useful. By default, the status bar at the bottom of the window displays the average, count and sum of selected cells.

Status Bar 101

The status bar in Excel can do the math for you.

1. Select the range A1:A3.

Select Range

2. Look at the status bar to see the average, count and sum of these cells.

Average, Count and Sum

3. To quickly change the workbook view, use the 3 view shortcuts on the status bar.

View Shortcuts

Note: visit our page about workbook views to learn more about this topic.

4. Use the zoom slider on the status bar to quickly zoom in or out to a preset percentage.

Zoom Slider

Note: use the ribbon to zoom to a specific percentage or to zoom to a selection.

Customize Status Bar

Many status bar options are selected by default. Right click the status bar to activate even more options.

1. Right click the status bar.

2. For example, click Caps Lock.

Customize Status Bar

Note: this doesn’t turn on Caps Lock (see image above, Caps Lock is still turned off). The status bar displays the Caps Lock status now.

3. Press the Caps Lock key on your keyboard.

4. Excel displays the text Caps Lock in the status bar.

Status Bar in Excel

5. Right click the status bar.

See also  How to use Relative References in Excel 2022

6. For example, click Minimum.

Click Status Bar Option

7. Select the range A1:A3.

Select Range

8. Look at the status bar to see the average, count, minimum and sum of these cells.

Added Status Bar Option

Status Bar Secrets

Here’s a little secret: Excel uses the status bar in many other situations. If you don’t like this, hide the status bar.

1. For example, filter a table.

Filtered Table

2. Excel uses the status bar to display the number of visible records.

Status Bar Message

3. Hover over a cell with one or more comments.

Comments

4. Excel uses the status bar to display the name of the author.

New Status Bar Message

5. If you have Excel 2016, use the shortcut CTRL + SHIFT + F1 to hide the ribbon and the status bar.

6. To only hide the status bar, add the following code line to the Workbook Open Event:

Application.DisplayStatusBar = False

7. Use the StatusBar property in Excel VBA to display a message on the status bar.

StatusBar Property in Excel VBA

Note: if you’re new to Excel, you can skip step 6 and step 7.

Checkbox in Excel

Inserting a checkbox in Excel is easy. For example, use checkboxes to create a checklist or a dynamic chart. You can also insert a check mark symbol.

Insert a Checkbox

To insert a checkbox, execute the following steps.

1. On the Developer tab, in the Controls group, click Insert.

Click Insert

2. Click Check Box in the Form Controls section.

Insert a Checkbox

3. For example, draw a checkbox in cell B2.

Draw a Checkbox

4. To remove “Check Box 1”, right click the checkbox, click the text and delete it.

Checkbox in Excel

Link a Checkbox

To link a checkbox to a cell, execute the following steps.

1. Right click the checkbox and click Format Control.

Right Click, Format Control

2. Link the checkbox to cell C2.

Link Checkbox

3. Test the checkbox.

Test Checkbox

4. Hide column C.

5. For example, enter a simple IF function.

Checkbox True

6. Uncheck the checkbox.

Checkbox False

Note: read on for some cool examples.

Create a Checklist

To create a checklist, execute the following steps.

1. Draw a checkbox in cell B2.

2. Click on the lower right corner of cell B2 and drag it down to cell B11.

Copy Checkbox

3. Right click the first checkbox and click Format Control.

Format Control

4. Link the checkbox to the cell next to it (cell C2).

5. Repeat step 4 for the other checkboxes.

6. Insert a COUNTIF function to count the number of items packed.

COUNTIF function

7. Hide column C.

See also  How to use MsgBox in Excel 2022

8. Insert an IF function that determines if you’re good to go.

IF function

9. Click all the checkboxes.

Checklist in Excel

Note: we created a conditional formatting rule to automatically change the background color of cell B16. Try it yourself. Download the Excel file and test the checklist (second sheet).

Dynamic Chart

Let’s take a look at one more cool example that uses checkboxes. A dynamic chart.

1. For example, create a combination chart with two data series (Rainy Days and Profit).

2. Add two checkboxes.

Chart with Checkboxes

3. Right click the first checkbox and click Format Control. Link this checkbox to cell B15.

4. Right click the second checkbox and click Format Control. Link this checkbox to cell C15.

Link Checkboxes

5. Uncheck the second checkbox. Cell C15 below changes to FALSE.

We’re now going to create two new data series.

6. Insert the IF function shown below. Use the fill handle to copy this formula down to cell F13.

7. Repeat this step for the new Profit data series.

New Data Series

Explanation: if the checkbox is checked, the old and new data series are the same. If the checkbox is unchecked, the new data series changes to a range with #N/A errors.

8. Use the new data series to create the combination chart. To achieve this, select the chart, right click, and then click Select Data.

Dynamic Chart

9. Uncheck the first checkbox and check the second checkbox.

Uncheck First Checkbox and Check Second Checkbox

Note: try it yourself. Download the Excel file and test the dynamic chart (third sheet).

Delete Checkboxes

To delete multiple checkboxes, execute the following steps.

1. Hold down CTRL and use the left mouse button to select multiple checkboxes.

Select Multiple Checkboxes

2. Press Delete.

Delete Checkboxes

Powerful Checkboxes

Finally, you can use VBA to create powerful checkboxes in Excel. Instead of inserting a Form control, simply insert an ActiveX control.

1. Insert a checkbox (ActiveX control).

2. At step 6, you can add your own code lines to automate all kinds of tasks. For example, add the following code lines to hide and unhide column F.

If CheckBox1.Value = True Then Columns(“F”).Hidden = True
If CheckBox1.Value = False Then Columns(“F”).Hidden = False

Use Checkbox to Hide Column
Use Checkbox to Unhide Column

Note: maybe coding is one step too far for you at this stage, but it shows you one of the many other powerful features Excel has to offer.

5/5 - (1 vote)
SAKHRI Mohamed
SAKHRI Mohamed

The blog of a computer enthusiast who shares news, tutorials, tips, online tools and software for Windows, macOS, Linux, Web designer and Video games.

Articles: 3764

Leave a Reply

Your email address will not be published.