Excel Ribbon Toolbar

Excel Ribbon/Toolbar

How does a toolbar look like?

The top section of an excel file is called a Ribbon or a toolbar. It has various commands which can be useful for us in our daily work. Thus, increasing our efficiency and making our work easier.

The Ribbon toolbar further has various sections, which are briefly described below:

Home

Different groups in an excel file and some of the most used commands -

1. ClipBoard

This is one of the groups which is used the most and has options to format, cut, copy, place filters. Let’s look at some of the most used commands

Cut and Paste options – when you want to remove values from one location and paste them to some other location, use Cut and then followed by Paste –

• Go to the cell(s) and select them, then press ‘Cut’ or a short-cut is Cntrl + X
• Now go to the location where you want to paste them and click on the ‘Paste’ or a short-cut is Cntrl + V

Copy and Paste options – when you want to copy values from one location and paste them to some other location without removing them from the first location, we use Copy and then followed by Paste –

• Go to the cell(s) and select them, then press ‘Cut’ or a short-cut is Cntrl + C
• Now go to the location where you want to paste them and click on the ‘Paste’ or a short-cut is Cntrl + V
• When you do a Copy, and Paste, following the paste option a small text box appears on the last cell which has ‘(Ctrl)’ written over it –

On clicking on this box, a menu appears which asks you what sort of paste operation you want to perform – some of them are

• ‘Paste’ – just normal paste,
• ‘Formulas’ – if you are copying formula from one cell and want to paste it on another cell, then select ‘Formulas’
• ‘Formulas & Number Formatting’ – it will copy the formula and along with the format of the cell
• ‘Keep source Formatting’ – if you want to copy the value and the formatting of the cell as well
• In ‘Paste Values’ – ‘Value’ option is what is most regularly used – it copies the values only – so if cell from which you want to copy has a formula, this option will only copy the value from the formula and not the formula itself

Format Painter

When you want to copy just the format of one cell to another cell, we use Format Painter. We will be discussing Formats in detail in the third module.

• Font

This section deals with Fonts. Supposedly, you have a lot of information on an excel file and want to highlight some of it, you can use Bold (B), Italic (I), or Underline (U)options.

Excel has a lot of in-built fonts – you can select your own by clicking on the drop-down and you can also change the font-size.

• Alignment

As the name suggests, these options are used to align the data in a cell – you can centre align the data, do a right align or a left align.

• Number

Number is used for number formatting. If you want to change the format to a number, or a number to a currency, if you want to remove the decimal points etc.

• Cells

Insert Cells option helps to insert rows, columns or specific cells whereas the Delete option is used to delete rows, columns and cells. You can try it by using the practice data!

The Format option is available to change the colour, style, size (and many other option).

• Editing

Sort is used to sort the data. Supposedly, I want to sort my class data based on males/females, I will –

• Click on sort and then ‘Custom Sort’
• Select the column ‘Sex’ and press okay

Filter: It is used to sort the data, and only fetching the required ones by applying some condition.

Find& Replace: It is used to find the any specified data and replace all the occurrence of that data in the excel sheet

.

Insert

This group is used to insert pivots, tables, charts, images in the file

1. Tables

These commands are used to insert pivot tables and normal tables. A table is in simple terms a group of data – for ex: it maybe a list of employees in a store and their details like age, date of joining, salary together.

• Illustrations

Pictures & Online Pictures

These commands can be used to import pictures in an excel file from your desktop or internet

Shapes

There are a lot of in-built shapes which you can use to make excel file work more visually appealing. You can draw a flow chart, use lines to link different boxes, add text to those boxes.

• Charts & Sparklines

These different commands are used to inset charts in an excel file – we will be studying charts in the Advanced Excel Module

• Text

The most important function of the ‘Text’ option is that it can help in inserting other excel files, word documents in the current file –

• Go to Text and click on ‘Objects’
• Click on ‘Create from File’
• Browse for the file or document you want to add to this file and click on ‘Insert’ and then ‘OK’

Page Layout

This group is usually used to change the orientation of a file or give prints

Formulas

This group has everything related to the formulas. It lists the different formulas and has quite a lot of options around auditing of a formula.

We will be discussing more on this in the Formula tutorials.

Data

The Data group is used for importing data from external sources like ‘Ms Access’ or a ‘Text file’. The ‘Sort & Filter’ options are the same as we came across on ‘Home’ group.

1. Data Tools

When importing data from a text file or a csv file, at times the column values are separated by a delimiter like a comma ‘,’ or a semi-colon ‘;’ – in such a scenario we use ‘Text to Columns’ options.

Supposedly, I have got my data in the following format – this has number, seat, age and weight all together separated by semi-colons and these are just 5 rows of data but it is difficult for me to read them – imagine if you have 1000’s of rows of similar data – it would be a nightmare!

Text to Columns: is used in such cases –

• Select the column in which data is present
• Click on ‘Text to Columns’ and click on Delimited and Next

• Click on semi-colon as that is the delimiter present here and press next and then Finish

• The data will be now separated in 4 different columns

This is how this option becomes very important when we are importing data from different sources.

Remove Duplicates

This is used to remove duplicates in your data. Select the entire data and just click on remove duplicates – it will remove all the duplicate records.

Review

This group is used to check spellings across the entire file or add comments. But the most important function of this group is to protect an excel file using a password.

1. Comment

A comment is an additional information which you want to give for a value in a cell. Unless you don’t move your mouse over the cell, you will not be able to see that information.

Below is just a broad example - let’s suppose I have 2 student and both have the same name Amy – we need to put their admission details in front of their names but because they have the same name, we don’t want to interchange the information – so we add comments which will tell me which Amy works where and this can help me distinguish

View

This group is used to change the way an excel file is viewed.

1. Gridlines

If you uncheck them, then the borders of the cells go away

• Zoom

You can change the zoom of the file – zoom-in or zoom-out depending upon your liking

File

If you have a version older than 2016, then you may not be seeing this group but you will be seeing a windows icon.

‘New’ – if you want to open a new excel file

‘Open’ – if you want to open another excel file on your system without closing this file

‘Save’ and ‘Save As’ – if you want to save your file at a location on your computer. A short-cut to this is Cntrl + S

‘Print’ – if you want to print the excel file

‘Close’ – if you want to close the excel file

‘Protect Workbook’ – this is used to protect the workbook by using a password so that only those people can access it who have that password