Specialized Training – EXCEL 1

Prerequisites:
Should have at least Microsoft Office 2016 or Office 365
Willingness to learn and open to explore new tools and technologies

Dataset link: Module 1 - Lets Begin with Some Examples.xlsx

Mastering Excel
Essential Skills and Tools:
• In this session, you will master the essential skills and tools to use Excel
effectively. We will explore Why “EXCEL” is a powerful tool for managing
and analyzing data and walk you through the key features of its interface,
Including the Ribbon, Menu System, Quick Access Toolbar, and Navigation
tools. You will also get familiar with workbooks and worksheets, the formula
bar, status bar and shortcut menus.
• We will cover Data Entry and Editing Rules, how to use the Autofill feature,
and the importance of the Undo and Redo Functions. Additionally, You will
learn how to add notes, use date and time functions, and understand the
difference between Save and Save As. By the end of the Session, You will
have the essential skills to navigate and use Excel efficiently.

Microsoft Excel Overview

Microsoft Excel is a widely-used spreadsheet application developed by Microsoft,
part of the Office suite, first released in 1985. It organizes data into rows and
columns, making it ideal for recording, analyzing, and visualizing numerical and
statistical data.
Key Features:
• Data Management: Organize data in tables using rows, columns, and cells.
• Formulas and Functions: Perform calculations and automate tasks.
• Charts and Graphs: Visualize data for better insights.
• Pivot Tables: Summarize and analyze large datasets.
• Macros: Automate repetitive tasks with VBA.
• Compatibility: Works across Windows, macOS, Android, and iOS.
Excel is versatile for tasks like budgeting, data analysis, reporting, and project
management.

Components of the Ribbon in Excel

The Ribbon is a key feature in Microsoft Excel, designed to help users access tools
and commands efficiently. It consists of four main components:
1. Ribbon Tab
o Tabs like Home, Insert, Page Layout, etc., are positioned at the top of
the Ribbon.
o Each tab contains multiple commands logically grouped for ease of
use.
 2. Ribbon Group
o Commands within a tab are subdivided into groups based on their
function.
o Example: The Home tab includes groups like Clipboard, Font,
Alignment, and Number.
3. Dialog Launcher
o A small arrow in the lower-right corner of some groups.
o Clicking it opens a dialog box with additional related commands and
options.
4. Command Button
o These are the clickable icons within a group.
o Example: Buttons like Bold, Italic, and Underline in the Font group.
Overview of Excel Tabs

Tab Description Key Features/Groups

File/Workbook Access options for managing Open, Save, Print, Export,

files and settings. Account Settings.

Home Basic commands for Clipboard, Font, Alignment,

formatting, editing, and quick Number, Styles, Editing.
tasks.

Insert Add visual and structural Tables, Charts, Pictures,

elements to the worksheet. Shapes, Hyperlinks.

Page Layout Adjust page setup for printing Themes, Margins, Orientation,
and presentation. Print Settings.
Formulas Tools for creating and Functions Library, Name
managing formulas and Manager, Formula Auditing.
calculations.

Data Manage data import/export, Sorting, Filtering, Data

analysis, and organization. Validation, Data Analysis,
Queries.

Review Proofing tools and options for Spell Check, Comments,

collaboration and protection. Protect Workbook.

View Customize the workbook's Workbook Views, Zoom,

display and navigation Freeze Panes, Gridlines.
settings.

Ribbon Display Options in Excel

At the top-right corner of the Excel window, the Ribbon Display Options button
allows you to customize how the Ribbon is displayed. It offers three primary
options:
Option Description

Auto-hide Ribbon Hides the Ribbon and the tabs entirely, providing a clean
and distraction-free workspace.

Show Tabs Displays only the tabs (e.g., File, Home, Insert), hiding the
commands and groups under them.

Show Tabs and Shows the full Ribbon, including tabs, commands, and
Commands groups. This is the default view.

These options are designed to give users flexibility in managing their workspace
for optimal productivity.
Balloon Tips
When you move your cursor on one particular option, a mini size of information
will be displayed in a form of box. This is called Balloon Tips.

Quick Access Toolbar in Excel

The Quick Access Toolbar (QAT) is located at the top-left corner of the Excel
window and provides quick access to frequently used commands. It can be
customized to suit user preferences for enhanced productivity. There are several
ways to configure the QAT:
Option Description

Add Commands Users can add commonly used commands like Save, Undo,
or Print to the toolbar for quick access.

Remove Commands Unwanted commands can be removed from the QAT to

declutter the workspace.

Customize Toolbar The QAT can be moved either above or below the Ribbon
Location for better visibility and convenience.

You can add or remove any option just by right clicking on that option. This
toolbar allows users to streamline their workflow by providing faster access to
essential commands without navigating through menus.
Overview of the key elements in Excel:

• File/Workbook:
The File or Workbook refers to the entire Excel document. A workbook can
contain multiple sheets, which are individual tabs within the file.
• Sheet:
A sheet is a tab within a workbook that holds data. Each sheet is made up
of rows and columns, forming a grid.

• Rows:
Rows are horizontal lines in the sheet, numbered from 1 onwards. They run
across the worksheet. By default, Excel has 1,048,576 rows, allowing users
to work with large datasets.

• Columns:
Columns are vertical lines in the sheet, labeled with letters (A, B, C, etc.).
They run down the worksheet. By default, Excel has 16,384 columns, giving
users ample space to organize data.

• Cells:
Cells are the individual boxes where a row and column intersect. Each cell
is identified by a unique address based on its column and row (e.g., A1
refers to the cell in the first column and first row).

• Name Box:
The Name Box is located at the top-left corner of the Excel window. It
displays the address of the currently selected cell, helping you easily
identify which cell you're working on.
• Formula Bar:
The Formula Bar is located above the sheet and displays the content or
formula of the selected cell. If you click on a cell that contains a formula,
the formula will appear in the Formula Bar.

• Status Bar:
The Status Bar is located at the bottom of the window. It provides
information about the current state of the worksheet, such as the sum,
average, or count of selected cells. It can also display other information like
page number or zoom level.

• Active Cell:
The Active Cell is the currently selected cell in the worksheet, where any
data you enter will be placed. It is highlighted with a thick border, and its
address is shown in the Name Box. You can move the active cell by clicking
on another cell or using the arrow keys on the keyboard.

• Scroll Bars:
Excel has two types of scroll bars:

o Vertical Scroll Bar: Located on the right side of the window, it allows
you to scroll up or down through the rows of your worksheet.
o Horizontal Scroll Bar: Located at the bottom of the window, it allows
you to scroll left or right through the columns of your worksheet.
 These scroll bars help navigate large spreadsheets when dealing with
datasets that exceed the visible screen area.
• Zoom:
The Zoom Control is located at the bottom-right corner of the Excel
window. It allows you to adjust the zoom level of the worksheet to make
the data appear larger or smaller. You can drag the slider or use the plus (+)
and minus (−) buttons for precise control.

• View Buttons:
The View Buttons are located next to the Zoom Control. They allow you to
switch between different worksheet views:

o Normal View: The default view for working on worksheets.

o Page Layout View: Displays how the worksheet will look when
printed, including margins and headers.
o Page Break Preview: Shows where page breaks will occur when
printing, allowing you to adjust them as needed.
These elements together provide the tools needed to navigate, analyze, and
customize the appearance of data in Excel effectively.
9 different cursor types:
1. Select Cursor (White Plus Sign):
Appears when the mouse is over a cell. It is used to select cells, ranges, or
entire rows and columns by clicking or dragging.

2. Fill Handle Cursor (Black Plus Sign):

Appears when you hover over the small square at the bottom-right corner
 of a selected cell or range (the fill handle). It is used to copy data, fill series,
or extend formulas across rows or columns by dragging.

3. Move Cursor (Four-Headed Arrow):

Appears when you hover over the border of a selected cell or range. It
allows you to move the cell's contents by dragging and dropping to a new
location.

4. Resize Column/Row Cursor (Double Arrow):

Appears when you hover over the boundary between column headers or
row numbers. It is used to resize rows or columns by dragging the cursor.

5. Cross Cursor (Thin Cross):

Used for drawing or editing shapes, charts, or other graphical elements in
the worksheet. It often appears in drawing mode.

6. I-Beam Cursor (Text Selector):

Appears when you hover over text in the formula bar or a cell. It is used to
place the insertion point for editing text.

7. Hand Cursor:
Appears when hovering over hyperlinks or interactive objects like buttons.
It is used to activate the hyperlink or interact with the object.
 8. Diagonal Resize Cursor (Diagonal Double Arrow):
Appears when you resize a shape, chart, or image diagonally. It allows you
to adjust the height and width proportionally.

9. Rotate Cursor (Circular Arrow):

Appears when you hover near the corner of a shape or object that allows
rotation. It is used to rotate the object in any direction.
These cursor types are context-sensitive and change automatically based on the
task you're performing in Excel, making it easier to navigate and interact with the
application.
Insert
The Insert feature in Excel is used to add new elements like rows, columns, cells,
or other objects into the worksheet. Here's an overview of the various ways to use
the Insert function:
1. Insert Rows
• Adds new rows above the selected row.
• How to Insert:
o Right-click on the row number and select Insert.
o Use the Insert button in the Cells group on the Home tab.
o Press Ctrl + Shift + "+" on the keyboard and choose Entire Row.
2. Insert Columns
• Adds new columns to the left of the selected column.
• How to Insert:
o Right-click on the column header and select Insert.
o Use the Insert button in the Cells group on the Home tab.
 o Press Ctrl + Shift + "+" on the keyboard and choose the Entire
Column.
Insert Cells
• Adds new cells and shifts existing cells down or to the right.
• How to Insert:
o Right-click within the cell or range and select Insert, then choose the
desired shift direction.
o Use the Insert button on the Home tab and select Insert Cells.
o Press Ctrl + Shift + "+" and choose Shift Cells Down or Shift Cells
Right.
4. Insert a Sheet
• Adds a new worksheet to the workbook.
• How to Insert:
o Click the + button next to the sheet tabs.
o Right-click on any sheet tab and choose Insert, then select
Worksheet.
o Use the Insert Worksheet option from the Cells group on the Home
tab.
Mini Toolbar in Excel
The Mini Toolbar is a small, context-sensitive toolbar that appears when you right-
click on a cell, text, or object. It provides quick access to commonly used
formatting options like font style, size, alignment, and cell color. To activate it
Right-click on a cell, text, or object in Excel, the mini toolbar will appear on the
top.
Creating/Opening a new workbook:
You can create a new workbook in several ways: by going to File → New → Blank
Workbook, pressing Ctrl + N for an instant new file, or selecting Blank Workbook
from the Start Screen when launching Excel.
Excel also offers templates when opening a new workbook, including a standard
Blank Workbook for general use and pre-designed templates like budgets,
calendars, and invoices to save time and provide structure for specific tasks. These
options make it easy to create and customize workbooks to suit various needs.