MS EXCEL

What is MS Excel?
MS Excel is a Microsoft Office application developed by Microsoft. The Excel Spreadsheet Software
supports multiple operating systems such as Windows, Mac OS, Android, Ios, and iPad OS. It is used to
perform calculations and computations using various functions and formulas present in Excel. Users can
store and analyze the data whenever they needed.

How to get a start with MS Excel?

To start MS Excel on Computer, the steps to be followed are:
Step 1: Click on Start Button
Step 2: Choose All Programs

Step 3: Click MS Office

Step 4: Choose MS Excel Application. The MS Excel application will open.

Another option is to Type "MS Excel" in the search box after clicking the Start Button.
What is a Cell?
A cell in Excel is defined as the intersection of rows and columns. It is present in the form of a rectangular
shape. The combination of rows and columns forms a table.

Cell Address in Excel

As the name suggests, cell address is the name by which the Cell is addressed. For example, A1 indicates
the Cell in the first Row and column.

Features of MS Excel
The various features of MS Excel are as follows:
The several features in MS Excel format and edit the data based on the requirement.

Home: The Home option consists of tabs such as Clipboard, Font, Alignment, Number, Styles, Cells, and
Editing.
Insert: The Insert tab consists of Tables, Illustrations, Charts, Links, and Text.
Page Layout: Themes, Page Setup, Page Background, Paragraph, and Arrange.
Formulas: The Formulas consist of Function Library, Defined Names, Formula Auditing and Calculation
Data: The Data consists of Get External Data, Connections, Sort &Filter, Data Tools, and Outline.
Review: The review tab consists of Proofing, Comments, and Changes.
View: The View tab consists of Workbook Views, Show/Hide, Zoom, Window, and Macros.
WPS PDF consists of Create PDF, WPS PDF Tools, and Settings.
Advantages of Using MS Excel
The advantages of using MS Excel are as follows:

Data Storage: MS Excel is widely used in many organizations as a large amount of data is stored in Excel.
The user can edit and retrieve the data whenever needed. Hence filtering the data is a quick and easy
process.

Data Recovery: Data Recovery is done quickly in Excel. If data is written on paper, it isn't easy to recover
and filter it. But it is easy to find and recover the data in Excel Spreadsheet.

Mathematical Calculations: Excel consists of a variety of formulas that are used to perform mathematical
calculations. Based on the data and requirements, the formula is modified.
Data Privacy: Data security and privacy is ensured in Excel. The Personal Computer and laptop are secured
with a password; hence data is protected.
All data in one place: In earlier days, the records and data were maintained in separate files, which were
difficult to take and time-consuming process. In Excel
the data are stored in one Excel file, which is used to analyze the data whenever needed.
Organized and clear View: The data is stored as a table in MS Excel. Hence the data looks organized and
clear to understand.

Usage of MS Excel
As MS Excel is widely used for various purposes, some of the MS Excel usage is defined as follows:
o Data Entry
o Time Management
o Accounting
o Financial analysis
o Charting and Graphing
o Financial Modeling
o Programming
o Task Management and Data Management
o Customer Relationship Management(CRM)
o Mathematical Calculations
o Human Resource Management

Terminologies and Components Present in Excel

The terminologies and Components Present in Excel are defined as follows:
Cell: The intersection of Row and column is called Cell
Cell reference: The cells are referenced by the cell name. The respective Cell's horizontal Row and vertical
column numbers assign the cell name.
Active Cell: The current selected Cell is called Active Cell
Workbook: The workbook comprises one or more worksheets.
Worksheet: Multiple worksheets are nested within a single workbook.
Worksheet tab: The worksheet tab is presented at the bottom left of the spreadsheet.
Column and Row Heading: The column letter and row number are outside the rows and columns.
Selecting the Header will select the entire Row and column.
Formula: Formulas in Excel generate the accurate result for the given problem. It is entered in a particular
cell preceded by a "=" sign.
Formula bar: The formula bar or input bar is at the top of the worksheet where the formulas or values are
entered.
Filter: The Filter option is in the Sort & Filter tab in the top right of the Home bar. It displays the user-
specified data in the selected rows and columns.
Auto-fill: The auto-fill option fills the required data in multiple cells. The data containing the respective
Cell is selected, and drag the bottom right corner to the specified amount of Cell to auto-fills the data.
Auto Sum: The Auto Sum feature is used to add the data present in the selected Cell. The Auto Sum option
is present in the top right of the home page in the "Sort and Filter" tab.
Pivot Table: The Pivot table automatically sorts and calculates the data for the selected data. It is presently
left to the Insert tab.
Pivot Chart: The Pivot Chart represents the data in graph format. It is located in the middle of the Insert
Page next to Maps.
Source Data: The source data is used to create Pivot Table.

Excel Version
"Excel Version" refers to the specific release or edition of Microsoft Excel, a popular spreadsheet software
program for data analysis and visualization. Microsoft regularly releases new versions of Excel, each with
new features, improvements, and bug fixes. The version number is usually indicated in the product name
or the "About" section of the software, such as Excel 2010, Excel 2013, Excel 2016, Excel 2019, and Excel
365. Knowing the Excel version is essential because some features and functionalities may differ between
different versions, and some add-ins or macros may not be compatible with all versions of Excel.

Types of MS Excel Version

There have been many versions of Microsoft Excel released over the years, including:
o Excel 1.0 (1985)
o Excel 2.0 (1987)
o Excel 3.0 (1990)
o Excel 4.0 (1992)
o Excel 5.0 (1993)
o Excel 95 (7.0) (1995)
o Excel 97 (8.0) (1997)
o Excel 2000 (9.0) (2000)
o Excel 2002 (10.0) (2001)
o Excel 2003 (11.0) (2003)
o Excel 2007 (12.0) (2007)
o Excel 2010 (14.0) (2010)
o Excel 2013 (15.0) (2013)
o Excel 2016 (16.0) (2015)
o Excel 2019 (16.0) (2018)
o Excel for Microsoft 365 (16.0) (2019)
Each version of Excel has introduced new features, capabilities, and improvements over the previous
version. Excel for Microsoft 365 is the most recent version and is available through a subscription service
that includes automatic updates and new features.

Advanced Tools
The advanced tools present in Excel are as follows:
TREND function: This function is used in a chart to calculate future trends and analyze X and Y values.
VLOOKUP: VLOOKUP is called vertical lookup and is used to find the specified value in the extensive data
set to return. The Approximate or Exact Match is specified by True or False.
Table Array: The data present in two or more tables are linked.
Col_index_num: While creating a table, the table array specifies where the data is being pulled in the
column.
Range_lookup: The range lookup is a value in VLOOKUP that specifies what to find the closest value when
no value is matched. False is returned if no exact match is found, and true is returned if the approximate
value is found.
MAX and MIN Functions: The MAX and MIN function finds the maximum and minimum values in the given
data range.
AND function: The AND function syntax contains more than one criterion. If the criteria match, the result
is TRUE, or the result is FALSE.

Excel Shortcut Keys

The shortcut keys present in Excel are as follows:
Keys Functions

Ctrl+O Open a Workbook

Ctrl+W Close a Workbook

Alt+H Go to the Home tab

Ctrl+S Save a Workbook

Ctrl+C Copy Selection

Ctrl+V Paste Selection

Ctrl+Z Undo recent action

Delete Remove Cell Contents

Alt+H,H Choose a fill color

Ctrl+X Cut Selection

Alt+N Go to Insert tab

Ctrl+B Apply bold formatting

 Alt+H, A, C Center align cell contents

Alt+P Go to the Page Layout tab

Alt+A Go to Data Tab

Alt+W Go to View Tab

Shift+F10 or Windows Menu Key Open the context menu

Add borders Alt+H,B

Delete Column Alt+H, D, C

Go to the Formula tab Alt+M

Hide the selected rows Ctrl+9

Hide the selected columns Ctrl+0

Features of MS Excel
Some of the salient features of MS Excel are listed as follows

Programming Language
Excel consists of a programming language called VBA. VBA is called Visual Basic Applications. The purpose
of the programming language is to automate a repetitive task, saving the user time and creating custom
solutions. VBA is used to create macros, a small program that performs repeated everyday tasks such as
formatting the data and generating the report.

Macros
Macros in Excel are used to increase the efficiency and productivity of the data. Macros perform automatic
tasks such as data cleaning, entry, modeling, analysis, etc. It is used to create formulas, which are reused
in the data when needed.

The basic knowledge of VBA should be known to understand macros. Excel contains an in-built macro
recorder that records the user's action and converts the required action into VBA code. One can create
complex macros by modifying the existing code or writing the own code from scratch.

If the user wants to share the macro code with others, it can be saved in a separate file or Excel
spreadsheet. Before sharing, it is necessary to check whether it contains malicious code which attacks the
program. Macros provide several benefits, such as saving user time, improving workflow, and increasing
efficiency and productivity. To ensure privacy, Macros should be used from trusted sources.

Integration
MS Excel provides compatibility with other applications such as PowerPoint and Word. The data present
in Excel can be copied and pasted into these applications if needed. Copying the data to the respective
MS Office applications, the data looks professional, attractive, and organized.
The file format supported by Excel is as follows:
1. TXT
2. XML
3. HTML
4. CSV
These file format helps to share the required data with other applications, or it can import the necessary
data from external sources.
The various external sources connect with MS Excel are as follows:
o SharePoint lists
o SQL Database
o O Data feeds
Security Features
Excel provides security features such as Password Protection and file encryption to maintain data security
for sensitive and confidential data. Third-party access and unauthorized access are denied by creating the
password for the required files. The password must be created robustly, not breached easily, and cannot
be stored in an insecure location.

Another way to protect the file is by restricting access to the files. The file permission option in Excel
determines who can access the file and the changes done in the file. For example, if the read-only option
is enabled, the third-party user can only read the data, whereas modifying or editing the data is not done.
While sharing the files with other users, the files should be encrypted. Sharing files via email or cloud
services should be protected using an encryption method.

Data Sorting
The Sorting feature in Excel is used to sort the data based on logical order, such as ascending or descending
order.

Formulas and Functions

Excel provides various in-built formulas and functions used to perform calculations and finds a solution
for complex data.

Header and Footer

The Header is at the top of the spreadsheet, where the document's title is entered. The footer is at the
bottom of the spreadsheet, where the page numbers are inserted.

Real-time application
The various formulas and functions are used to generate the result for the required problem. If the existing
data is modified and replaced with new data, the formula and function automatically update the result
regarding the new data.

Insertion of Charts
Excel provides various charts, such as Pie charts, Bar graph charts, and Line charts. For data analysis, the
charts are inserted for the required data, which helps to understand the data quickly and easily.
Summary
Excel consists of various in-built features used to generate organized and precise data. The usage of Excel
is increasing day by day as it contains many salient features.
What is Workbook Window?
In Excel, when we create a new document, we always select a blank workbook, as you already have done
in the previous chapter. When this blank workbook opens up, an interface you see is called a workbook
window.

This workbook window contains several essential components. Most importantly, rows and columns that
together create cells. In other words, an interface that you see when opening a new or existing document
in Excel is called workbook window and that Excel file is called workbook.

What is Workbook?
An Excel workbook is a collection of worksheets that stores the data inside rows and columns. A new Excel
workbook is default named as Book1 (see at the top of the Excel). You can provide and save the workbook
according to the data saved in it. A single Excel worksheet consists of around 1,048,576 and 16,384
columns.

Note: A worksheet is also called a spreadsheet.

A workbook has several worksheets, named as Sheet1, Sheet2, Sheet3,… SheetN. You can add these
worksheets to your workbook by clicking on this + symbol near to sheets. One worksheet can be active at
a time. It means - in an Excel workbook, only one worksheet can be active. However, you can more than
one worksheet in a workbook.

Worksheet
Excel files are known as workbooks. Each workbook holds one or more worksheets (also called a
"spreadsheetsx").

Whenever we create a new Excel workbook, it will include one worksheet named Sheet1. A worksheet is
a framework of columns and rows where columns are identified by letters running across the top of
worksheet, and rows are identified by numbers running down the left side of the worksheet.

When working with a high amount of data, we can create multiple worksheets to help organize our
workbook and make it easier to find content. We can also group worksheets to quickly add data to
multiple worksheets at the same time.
Operations on worksheet
Excel enables the users to perform several types of operations on Excel worksheet and its
data. For example, rename a worksheet, insert a new worksheet, delete a worksheet, and
many more. See them how could they performed –

To rename a worksheet
As we have told you that whenever we create a new Excel workbook, a
new worksheet named Sheet1 gets added to the workbook by default. Excel allows its users to rename
the worksheet name.
We can rename a worksheet to reflect its content better. In this example, we will develop a training log
organized by month. Now, follow the given steps below:

1. Right-click the worksheet we wish to rename, then select Rename from the worksheet menu.

2. Type the desired name for the worksheet.

3. Click anywhere outside of the worksheets, or press the Enter on our keyboard. The worksheet will
be renamed.

To insert a new worksheet

Excel provides a new sheet button (+ symbol) near the worksheet menu that allows the users to add any
number of worksheets to their currently active workbook. See the steps for it -
1. Locate the new sheet button (+) and click on it to add a new sheet.
2. A new, blank worksheet will occur named as Sheet2.

Similarly, you can add more sheets to your workbook from here.
Note: To change the default number of worksheets, navigate to backstage view, click Options, and then
choose the desired amount of worksheets to contain in each new workbook.

To delete a worksheet
1. Right-click the worksheet you wish to remove, then select Delete from the worksheet menu.
 2. You will see that the targeted worksheet has been deleted from our workbook. Only one
worksheet has remained.

Alternatively, you can also go to Home Tab in the Cells Group, click on Delete and select Delete Sheet.

Note: The Undo button will not undo the deletion of the worksheet.

To copy a worksheet
If we need to duplicate the content of one worksheet to another, Excel allows us to copy an existing
worksheet. Using the few simple steps, you can copy the content of a sheet to the same workbook.
1. Right-click the worksheet you want t

copy, then select Move or Copy from the worksheet menu.

 2. A dialogue box named Move or Copy will occur. Select the sheet in the Before Sheet field whose
content you want to copy. We have selected Sheet1.

3. Mark the Create a copy checkbox, then click OK.

You can also select move to end option that will also allow to place a copy of the sheet on the
right of the current sheet.
4. The worksheet (Sheet1) has been copied and its content is placed in a new sheet present to the
left of the first sheet. It will have a similar title as the original worksheet, as well as a version
number.

Note: We can also copy a worksheet to a fully different workbook. We can choose any workbook that is
now open from the to book dropdown menu.

Copy a worksheet to another workbook

Instead of copying the content of a sheet to another sheet within the same workbook, you can also copy
the content to another workbook. Excel allows the users to do it. You just have to choose another
workbook in the to book dropdown menu.
Here, we have steps for it:
1. Choose a new workbook in which you want to move the content.

2. Mark the Create a copy checkbox and click OK.

3. The same content will be copied to the new workbook.

To move a worksheet
Sometimes we may want to move a worksheet to rearrange our workbook.
1. Select the worksheet we wish to move. The cursor will develop into a small worksheet icon
2. Hold and drag the mouse until the small black arrow emergesabove the desired place.

3. Release the mouse. The worksheet will be changed.

To change the worksheet color

We can change a worksheet's color to help organize our worksheets and make our workbook easier to
navigate.
1. Right-click the desired worksheet and take the mouse over Tab Color. The Color menu will emerge.

2. Choose the desired color. A live preview of a new worksheet color will develop as the mouse over
different options. In this example, we'll choose Red.
3. The worksheet color will be modified.

The worksheet color is significantly less noticeable when the worksheet is selected. Select another
worksheet to view how the color will emerge when the worksheet is not selected.

The Scrolling Buttons

These buttons scroll the show of sheet tabs one at a time or to show the first and last grouping of sheet
tabs and are placed to the left of the sheet tabs.

The Scroll Bars

Our spreadsheet may frequently have more data than we can view on-screen at once. Click, hold, and
drag the vertical or horizontal scroll bar depending on what part of the page we want to view.

How to Create and Open Workbooks?

Excel files are known as workbooks. Whenever we start a new project in Excel, we'll need to create a new
workbook. There are different ways to start working with a workbook in Excel 2016. We can choose to
create the new workbook, either with a blank workbook or a predesigned template or open an existing
workbook.
Create a new blank workbook
To create a new blank workbook in MS Excel, follow the given steps –
1. Select the File tab from where the Excel Backstage view will appear.

2. Select New, and then click the Blank workbook.

3. A new blank workbook will occur like this.

Open an existing workbook

In addition to creating new workbooks, we'll often need to open a workbook that was previously stored.
An existing workbook may contain data inside it. We can open it directly from the location where it is
stored or through MS Excel. Follow the given steps below:
1. Navigate to Backstage view from the File menu, then click Open.
2. Now, MS Excel will allow us to open an existing file either from your local computer, or cloud storage
(OneDrive), recent files.

Open existing files from local storage

To open an existing file from the local computer, click the Browse button here.

3. The Open dialog box will emerge, and you will navigate to the local storage. Locate and select a
workbook, then click Open.

We have opened an existing workbook named Employee from the Desktop.

Open existing files from cloud storage

If your Excel file is stored on cloud storage like OneDrive, you can open it by following these simple steps:
1. From the Excel backstage view, click on the Open and select OneDrive cloud storage.
2. Now, log in to your OneDrive account from here if you have not logged in.

Now, open the existing file stored on your cloud storage.

Note: If we've opened the desired workbook recently, we can browse our Recent Workbooks rather than
searching for the file.

Open existing file from Recent files

Excel enables a recent folder on backstage view. It temporarily keeps recently opened files. If it has your
desired Excel file, you can open it by following these simple steps:
1. From the Excel backstage view, click on the Open and look for the existing file you want to open under
the Recent

Pin a workbook
If we frequently work with the same workbook, you can pin it to the backstage view for quick access.
1. Navigate to the backstage view and then click Open. Our recently edited workbooks will occur.
2. Take the mouse over the workbook we wish to pin. A pushpin icon will emerge next to the workbook.
Click that pushpin icon.
3. The workbook will stay in the Recent Workbook, or you can also find it inside the Pinned tab. To unpin
the workbook, simply click the pushpin icon again.

Note: We can also pin folders to the Backstage view for quick access. From the Backstage view, click Open,
then locate the folder we wish to pin and click the pushpin icon.

Compatibility mode
We may have existing Excel files created in an older version of Microsoft Excel, such as Excel 2003 or Excel
2000. Sometimes, we need to work with those workbooks to make some changes in them. When we open
these kinds of workbooks, they will occur in Compatibility mode.

Compatibility mode disables certain features. For example, if we open a workbook created in Excel 2003,
we can only use those tabs and commands that were available in Excel 2003.Hence, we can only access
commands found in the program that was used to create the workbook. In order to exit from Compatibility
mode, we'll need to convert the workbook to the current version type. If we are collaborating with others
who just created an earlier version of Excel, it's best to leave the workbook in the Compatibility mode so
the format would not change.

To convert a workbook
If you want to access all the features of the current version of Excel, you can change the workbook to that
version of the file format. For example, convert an older version workbook to a 2016 file format.
Note that converting the data may cause some changes in the original layout of the workbook.
Follow the steps to make an older file compatible with Excel version 2016:
1. On the Excel Backstage view, click the Info tab on the left panel.
2. Click on the Check for Issue and then Check Compatibility option.

3. If the current file has any compatibility issue, it will display here. Select a preferred Excel version from
here and click OK.

3. The Save As dialog box will emerge. Select the location where we want to save the workbook, enter the
file name for presentation, and then click Save.

4. The workbook will be changed to the newest file type.

Saving and Sharing Workbooks
Whenever we create a new workbook in Excel, we'll need to know how to store it to access and
edit it later. As with previous versions of Excel, we can save files locally to our computer. But
unlike older versions, Excel 2016 also lets us save a workbook to the cloud using OneDrive.

Additionally, we can also export and share workbooks with others directly from Excel.

Save and Save As

Excel offers two methods to save a file: Save and Save As. These options work in a similar way, with a few
crucial differences:
o Save: When we create or edit a workbook, we'll use the Save command to save our changes. We'll
use this command most of the time.
When we save a file for the first time, only that time we will need to select a file name and location.
After that, we can just click the Save command to save it with a similar name and location. You can
also use the Ctrl+S shortcut key for it.
o Save As: We'll use this command to create a copy of a workbook while keeping the original. When
we use Save As, we will need to provide a different name and/or location for the copied version.

Steps to save a workbook

It's essential to save our workbook whenever we start a new project or make changes to an existing one.
Saving early and often can prevent our work from being lost.
1. Locate and select the Save command on the Quick Access Toolbar.

2. If we're saving the file for the first time, the Save As pane will occur in the Backstage view. Where
choose a location to save your file with a new file name.

3. To save the workbook to our computer, click Browse button here. Alternatively, we can click OneDrive
to save the file to your OneDrive.
4. The Save As dialog box will emerge. Select the location in local storage where we want to save the
workbook. Enter the file name for the workbook, then click Save.

5. The workbook will be saved. We can click the Save command (Ctrl+S) again to save our changes as we
modify the workbook.

Using Save As to make a copy

If we want to save a different version of a workbook while keeping the original, we can create a copy. For
example, if we have a file name "Sales Data" we could save it as "Sales Data 2" so we'll be able to edit the
new file and still refer back to the original version. On the currently opened file, select the Save As
command in the Backstage view. Then follow the same steps like when storing a file for the first time,
we'll need to select where to save the data and give it a new file name.

AutoRecover
Excel automatically saves our workbooks to a temporary folder while we are working on them. If
we forget to save our changes, or if Excel crashes, we can restore the file using the Excel
AutoRecover feature.

To use AutoRecover
1. Open Excel 2016. If auto-saved versions of the file are found, the Document Recovery pane will emerge.

2. Click to open an available data. The workbook will be recovered.

Note: By default, Excel AutoSaves every 10 minutes. If we are editing a workbook for less than 10 minutes,
Excel may not develop an AutoSaved version.

If we don't see the file we need, we can browse all AutoSaved files from the Backstage view. Just go to File
> Info > Manage Workbooks > Recover Unsaved Workbooks. From here, you can set up the required
autorecover option.

Exporting Workbooks
By default, Excel workbooks are stored in the .xlsx file type. There may be a time when we want to use
another file type, such as a PDF or Excel 97-2003 workbook. It's easy to export our workbook from Excel
in a variety of file types.
To export the workbook as a PDF file
Exporting our workbook as an Adobe Acrobat document, usually called a PDF file, can be especially helpful
if sharing the workbook with someone who does not have Excel. A PDF will make it feasible for recipients
to see, but not to edit the content of our workbook.

1. Click the File tab to create the Backstage view.

2. Click Export, then select Create PDF/XPS.
3. The Save As dialog box will emerge. Select the location where we wish to export the workbook, enter
the file name and then click Publish.

Note: By default, Excel will only export an active worksheet. If we have multiple worksheets and want to store all of
them in a similar PDF file, click Option in the Save as dialog box. The Options dialog box will emerge. Select the Entire
workbook, then click OK.

To export the workbook in other file types

We may also find it helpful to export our workbook in other file types, such as an Excel 97-2003 Workbook
if we need to share with people using an older version of Excel, or a .CSV file if we need a plain-text version
of our workbook.
1. Click the File tab to create the Backstage view.
2. Click Export, then select Change File Type.
3. Select the common file type, then click Save As.
4. The Save As dialog box will emerge. Select the location where we want to export the workbook,
enter a file name, and then click Save.
MS Excel Online
When it comes to organizing vast amounts of financial data, MS Excel is one of the first spreadsheet
software used by most people worldwide. Although MS Excel is a powerful spreadsheet program, it has
some limitations, which mainly include:
o It must be installed on the device.
o It works offline without cloud support.
o It does not allow collaboration.
o It must be purchased, etc.

Apart from this, there is another spreadsheet program named Google sheets. Google sheets does not
have any of the above limitations. Similarly, Microsoft developed an online version of the MS Excel
program to overcome certain limitations and stay updated in the competitive atmosphere. Since it is
developed by Microsoft, MS Excel Online has become popular quickly and is used in many organizations.
Therefore, it is necessary to understand the basic concepts of MS Excel Online and be confident to work
on it.

What is MS Excel Online?

MS Excel online or Microsoft Excel online is an upgraded version of MS Excel and comes
included with the Office 365 package. As the name suggests, it works online and allows users to save
the data on the cloud (namely OneDrive). Although the online version is not as powerful as the MS Excel
desktop version, it has all the basic features required for the spreadsheet. MS Excel Online is much
similar to Google Sheets, rather than the desktop version of MS Excel.

With MS Excel Online, Excel is not only a desktop program. Instead, it is a web-based spreadsheet tool.
Additionally, the tool allows users to work on spreadsheets without dependency on any particular
platform. Since it is entirely web-based, users can easily access it using the web-browsers on different
platforms, such as Windows, Mac, Android, etc. MS Excel online is also referred to as the 'Excel for the
web'. The following image displays the initial interface of the blank worksheet, which typically comes
when we create a new spreadsheet or worksheet:

Features of MS Excel Online

The following are some essential features of MS Excel online:
Free to Use: MS Excel online is completely free for personal use with all the features, allowing users to
save their files up to 5GB on the cloud. However, to remove the storage limit or use it for business
purposes, it must purchase a Microsoft 365 subscription.
Surveys and polls: MS Excel online allows users to create surveys and polls using the forms. Since it is a
web-based version, others can share their views, feedback, or answers in respective surveys/ polls.
Despite this, the results will be displayed to the creator for further analysis. It can be accessed via
OneDrive.

Collaboration: MS Excel online enables multiple users to collaborate if desired. Users can use the 'Share'
feature to link other people to an existing spreadsheet and either let them view or edit the data within
the sheet. The host person or the creator only manages the permissions (edit and view).

Search: MS Excel Online allows users to search for any specific function, shortcut, action, or more by using
the search bar from the top. Users can also find the tutorial for the desired action or feature.
Link to Workbook: MS Excel online also supports the 'link to workbook' feature. It mainly helps users to
attach refreshable connections to other workbooks saved on the OneDrive.

Add-ins Support: There are plenty of add-ins on the web for MS Excel online. The add-ins can be added
to Excel online to get additional features and use the tool more effectively.

Comment: The comments can be inserted with MS Excel online in a specific cell. It mainly helps one person
to instruct or inform other team members to take certain actions for any particular cell. For example,
suppose that the team leader wants to get the summarized analysis report for any result. Thus, the leader
can comment on a specific result cell to tell others to create the analyzed report using charts.
Templates: MS Excel online has several built-in ready-to-use templates, and users can easily import the
desired template and work on it accordingly. The templates help reduce the time of the work process to
some extent.

Basics of MS Excel Online: Understanding the User Interface

Before discussing some basic work processes for Excel online, we must first understand the basic terms
to become familiar with the Excel online user interface. However, they are similar to the terms used in
other spreadsheet programs, including the MS Excel desktop and Google sheets.
Let us discuss them briefly:
Cell: A cell is defined as a single data point in any worksheet. The cell is typically represented by the
rectangle box, formed with the intersection point of a row and the column. Since the spreadsheet contains
several rows and columns, therefore, it also contains several cells. Each cell in a spreadsheet is
represented by the unique address constructed by the letter of a particular column and the row number.

For example, the following picture has a cell in Excel online. Here, the corresponding cell is formed by the
intersection of row 1 and column A. Thus, it is identified by Cell A1.
Row: A row is defined as a group of cells that lie horizontally (from left to right) in a worksheet. Each row
has its unique address and represented by a specific number, starting from '1'.
In the following picture, row 1 is selected in MS Excel online.

Column: A column is defined as the group of cells that lie vertically (from top to bottom) in a worksheet.
Each column has its unique address and represented by a specific letter, starting from the 'A'.
In the following picture, column B is selected in MS Excel online:

Range: The range is defined as the selection of certain cells in the worksheet.
In the following picture, some cells (from B1 to B6) are selected in column B. Therefore, the range is
represented as B1:B6, as displayed in the cell address box.

Spreadsheet Title: The title is nothing but the specified name of any worksheet. When we create a new
worksheet in Excel online, it typically uses the default name 'Book' associated with random numbers, such
as Book 1, Book 2, Book 3…and so on.

In the following picture, the spreadsheet title is located on the top of the spreadsheet interface:

Excel Online automatically saves the current changes to an existing file on OneDrive. Furthermore, it also
displays the notification text next to the spreadsheet title, which notifies users when the changes were
made to the worksheet.
Apart from this, users can click on the notification text to see the detailed version history of the changes
made in the worksheet, along with the person's name who made the corresponding changes.

Toolbar Menu: A toolbar menu is defined as the horizontal area below the spreadsheet title and contains
multiple tabs with the Excel commands. The commands can be accessed by clicking on the particular tab.
In the following picture, a toolbar menu is represented in Excel online:

Shortcut Toolbar: A shortcut toolbar is another horizontal area on the top that contains common quick
options. Typically, it includes the commands for font style, font size, font color, text alignments, etc. A
shortcut toolbar is situated below the toolbar menu, as displayed in the following picture:
Formula Bar: The formula bar is where users can apply, enter, or alter the data, functions, and formulae
of the selected cell in the worksheet. Users can also see the applied function on any particular cell in the
formula bar by selecting the corresponding cell.

A formula bar is located below the shortcut toolbar and represented by the sign 'fx', as displayed below:

Sheets Toolbar: A sheets toolbar typically allows users to manage sheets related options in the workbook.
Using this, users can insert, rename, and delete the sheets as desired. It is situated on the bottom of the
window, as displayed below:

The sheets options can be accessed by right-clicking on the sheet name.

Functions: MS Excel online has an extensive range of built-in functions that helps perform
different operations on the data in a cell(s). Using the functions, users can easily calculate values,
manipulate data, get quick results, and more. The functions can be accessed by navigating to
the Insert > Function.

In addition to this, the formula is defined as the combination of functions, cells, ranges, and operators
used to achieve results by calculating certain values.

Worksheet/ Sheet: A worksheet or sheet is defined as the single page of the workbook that includes rows
and columns. It typically represents the main working area within the workbook.
Workbook/ Spreadsheet: A workbook/spreadsheet is defined as a document or file that contains one or
more worksheets.

Working with MS Excel Online

After we become familiar with the basics terms and areas of MS Excel online, we must understand how
to perform basic tasks.

Let us discuss some basic things:

Starting MS Excel Online

There are various methods to launch MS Excel online; however, the simplest and most used method is
to use the official website of Excel for Web. This method includes the following steps:
o First, we need to launch a browser like Google Chrome, Firefox, etc. Then, we must navigate to the
following link:
Link: https://www.office.com/launch/excel
 o Next, we need to log in using the Microsoft account. If we don't have a Microsoft account, we
can easily create one for free. Besides, we have already logged in previously, and login
information is still saved, we will be automatically redirected to the home page of Excel online.
This looks like the following image:

The home page also displays some built-in Excel templates.

Creating a New Workbook

Creating a new workbook or a spreadsheet is a one-click process from the Excel online homepage. We
only need to click on the plus sign (+) or a tile with the text 'New Blank Workbook' to create a fresh and
empty workbook. This option is mainly used to start from scratch with the desired formatting and data.

We can also select the desired inbuilt template to start working with some saved formatting and sample
data. Excel online contains numerous ready-to-use templates that we can select from the template gallery
by clicking on the 'More Templates' button.
Naming/ Renaming the Workbook
Once we create a new worksheet, Excel automatically assigns a default name to it. If we want to change
it to the desired name, we must click on the default name from the top of the window, as displayed below:

After clicking on the default name, we will get the option to enter the desired name for the workbook.

We can also change the spreadsheet name or title by navigating to File > Save As > Rename.

Opening an Existing Workbook

Opening an existing workbook/ spreadsheet is also possible in MS Excel online. For this, we must navigate
to File > Open. However, this option does not allow us to open files from our devices. Instead, it only
enables us to open any specific spreadsheet from the OneDrive.
Therefore, if we want to open any file from our local devices, we must first upload it on OneDrive. Then,
we can open it in Excel online and work as desired.

Adding Data to the Worksheet

After the workbook has been created, the worksheet is displayed on the screen. Here we can enter the
desired data. First, we need to select a specific cell and then type the contents into it. Once we are done
typing the desired contents, we must press the 'Enter' button to register the data and move to the next
cell.

The contents of the selected cell can also be viewed in the formula bar.

Note: Excel online also allows us to cut, copy, paste, drag-drop the data directly into the sheet. However,
it does not support the 'Import' feature, restricting us from importing the data directly from other
spreadsheets.

Deleting Data in Worksheet

We can easily delete the contents from the desired cell(s) like the other spreadsheet programs. For this,
we are first required to select the corresponding cell or multiple cells and then press the 'Delete' button.
Doing this will delete the contents from the selected cell or the range.
Alternately, we can use the shortcut 'Ctrl + Z' to delete the recently added data or undo the last
step(s) we performed.

Formatting the Data in Worksheet

After inserting the contents into the worksheet, we might need to format the data to make it attractive
or more informative. Most of the basic formatting options are located under the 'Home' tab.

Applying Functions in Worksheet Data

Like the other spreadsheets programs, Excel online also supports functions. We can directly insert the
desired function with the equal sign (=) in an empty cell and get the corresponding results.

Let us understand with an example using the SUM function:

Suppose we type the values 5 and 8 in cells A1 and B1, respectively. After that, we apply the sum function,
such as =SUM(A1,B1), in the C1 cell. After applying the sum function, we need to press the 'Enter' key. By
doing this, Excel displays the sum of values from A1 and B1 cells in cell C1, i.e., 13.
That is how we can insert the desired function in Excel online. However, if we don't remember the
functions we need, we can find them by going to Insert > Functions.

Printing a Worksheet
Excel online also enables users to print a sheet directly from the web browsers without saving it to the
devices. For this, we need to give the print command to Excel online. More specifically, we must navigate
to the File > Print.
After selecting the print option, we need to configure the printing preferences as desired. This usually
includes the page setup, orientation, layout, paper size, printer, etc. Once we have configured all the
settings, we need to click on the 'Print' button to finalize the print process.

Saving a Worksheet
MS Excel online has an AutoSave feature, and therefore, all the changes are automatically saved on
OneDrive. Additionally, Excel online also allows us to save the desired spreadsheet locally on our devices.
For this, we need to perform the following steps:

o First, we need to open a specific spreadsheet from OneDrive. After the spreadsheet has opened, we
must navigate to the File > Save As.

o On the next screen, we will see few options, including the download buttons. It must be noted that
Excel online does not allow us to save our spreadsheets into several formats. Instead, it only allows
us to download a copy of spreadsheets as XLSX and ODS.
Note: By default, any worksheet we create or work on using MS Excel Online is automatically saved on
the Microsoft OneDrive. Therefore, we can also open or launch a particular file from there at any time.

MS Excel Online Shortcut Keys

Like MS Excel desktop, MS Excel online also supports a wide range of shortcut keys that allow users to
perform corresponding tasks in no time. Using the shortcut keys, users usually eliminate two or more
steps of the respective action or the work process.

Although MS Excel supports numerous shortcuts, the following table displays the most commonly used
Excel online shortcut keys, including the corresponding actions:

Shortcut Key(s) Action

Alt + Shift + A To launch a shortcuts dialog window

Shift + F10 To display the context menu

Alt + Windows, O To open an existing file in Excel app

Alt + Windows, W To open the View tab

Alt + Windows, R To open the Review tab

Alt + Windows, A To open the Data tab

Alt + Windows, N To open the Insert tab

Alt + Windows, H To open the Home tab

Alt + Windows, F To open the File tab

Enter To complete the cell entry and move to the cell below

Esc To cancel the entry

Delete To clear the selected cell

 Alt + Enter To start a line in the selected cell

Ctrl + Z To undo the changes

Ctrl + Y To redo the changes

Ctrl + C To copy the selection

Ctrl + X To cut the selection

Ctrl + V To paste the copied contents from the clipboard

Ctrl + Alt + V To launch 'Paste Special' dialog

Shift + F11 To insert a new worksheet

Ctrl + K To insert the hyperlink

Ctrl + L To insert the table

Alt + F1 To insert the chart

Ctrl + O To open a worksheet

Ctrl + P To print the worksheet

Alt + F2 To launch the 'Save As' dialog

Ctrl + F To launch the 'Find and Replace' dialog with the Find tab selected

Ctrl + H To launch the 'Find and Replace' dialog with the Replace tab selected

Ctrl + F7 To SpellCheck

Shift + F2 To insert the comment

Ctrl + Space To select the entire column

Shift + Space To select the entire row

Ctrl + A To select all cells in the sheet

Ctrl + Shift + L To turn on the filtering option for the selected cell

MS Excel Online File Extensions

When it comes to opening files in MS Excel online, it supports all the popular spreadsheet file extensions
like the MS Excel desktop versions. However, some of the advanced features of Excel desktop might not
always work in Excel online. Apart from this, Excel online does not allow users to open existing Excel files
directly from the devices. Instead, users must first upload the corresponding Excel files to the OneDrive
and then open them using Excel online. The following are some most popular files extensions that can be
opened in MS Excel online:
 File Extension File Format

XLS Microsoft Excel's workbook file (97-2003)

XLSX Excel workbook

XLSM Excel macro-enabled workbook

XLT Excel template file (97-2003)

XLTX Excel template

XLTM Excel macro-enabled template

ODS OpenDocument Spreadsheet

HTML Hypertext Markup Language File

TAB Tab-delimited text or data files

TSV Tab-separated values

CSV Comma-separated values

TXT Text file

On the other hand, MS Excel online only allows users to save/ download spreadsheet files in
the XLSX extension and ODS extension. To save in other extensions, users must convert the downloaded
files using the Excel desktop version or online converter tools like Zamzar, Online2pdf, Convertio, etc.
The Excel desktop version allows saving files in different file types with the help of the 'Save As' command,
while the online converters automatically convert the selected files to the desired formats.

Excel Vs. Google Sheets

Organizing the data or keeping track of data and lists is one of the most common tasks in many businesses,
companies, and organizations. Although numerous software claims to organize the information in neat
and clean spreadsheets, MS Excel and Google Sheets are the two most popular programs/ tools.

Excel and Google sheets are both powerful spreadsheet tools and have distinct features. Even though
many features are similar in these spreadsheet programs, there are significant differences between Excel
and Google Sheets. This article discusses essential differences between Excel and Google sheets, which
will help us know the values of each software. Furthermore, this will help us choose the right spreadsheet
software for our purposes between Excel and Spreadsheets.

What is MS Excel?
MS Excel or Microsoft Excel is a powerful spreadsheet program that is installed with the MS Office
package. Excel mainly stores the data in tables, formed by several rows and columns. Additionally, users
can also apply built-in formulae or functions on the stored data to get the desired output/ result. The
applied formulae or functions are displayed on the formula bar.
MS Excel is a handy program for various data-related tasks and reports, such as making payslips or salary
slips, calculating weekly expenses, accounting, and many more. Despite this, finding any information is
easy with the interactive support of data sorting and filtering, allowing users to apply rules on data set by
various criteria. The following image represents the primary interface of an MS Excel worksheet, which is
the typical screen we see for the first time when creating a new Excel sheet:

Advantages
o Advanced functionality
o Responsive/ Fast
o Extensive range of options for data visualization
o No need for internet (Excel 2016 and prior versions)
o Unlimited storage
o Extensive range of formulae and functions
o Highly customizable

Disadvantages
o Different versions have version compatibility difficulties
o Expensive Full version support
o No clean revision history

What is Google Sheets?

Google sheets is another popular spreadsheet program developed by Google. However, this particular
program is slightly different from MS Excel. The Google sheets works with the web browser like a typical
web page and saves the data on the cloud, such as Google drive. Google Sheets also helps users store data
in tables like MS Excel and perform various data sets related tasks. Google Sheets is currently not
considered a suitable program/tool for performing complex tasks or working with large amounts of data.
It is comparatively newcomer as compared to MS Excel and also lacks many advanced features. Instead,
the tool has some excellent features, such as it is free, helps with team collaboration, cloud support,
scripting, and more. This somehow makes Google Sheets a true competitor to MS Excel.

The following image represents the primary interface of Google sheets, which is the typical screen we see
for the first time when creating a new sheet:
Advantages
o Easy to use
o Free to use
o Inbuilt real-time revision history with chat support
o Excellent visibility and rigid integration with Google
o Better sharing support with no version compatibility issues
o Supports an extensive range of add-ons and Google other products
Disadvantages
o Becomes slow with the vast amounts of data
o Supports limited formulae and functions
o Limited options for data visualization and customizations

Key Differences between Excel and Google Sheets

The following are some key differences between the MS Excel and Google sheets:

o MS Excel is included with the MS Office suite, whereas Google sheets is a part of the G Suite
(Google suite).
o MS Excel is expensive, and its latest version (Office 365 Excel Online) charges a monthly or yearly
fee, depending on the user's selection. Besides, Google Sheets is entirely free for personal use
and only requires a Google Account.
o MS Excel supports over 400 functions/ formulae, while Google sheets has limited options.
o MS Excel deals with complex tasks and large data sets efficiently. On the other hand, Google
sheets is more suited for basic operations and small data sets.
o MS Excel (except Office 365) does not have a collaboration feature, and it asks users to save their
files first to send/share them with others. On the other hand, Google Sheets is an entirely web-
based tool, and hence, it allows users to share sheets with others and work together in real-time.
o Data analysis is more straightforward and effective in MS Excel as it can handle vast amounts of
data. Besides, Google sheets is not usually made for data analysis. Instead, it is the data entry tool
developed for performing basic spreadsheet tasks.
o MS Excel is best suited for large-scale companies or businesses. In contrast, Google sheets is best
suited for freelancers, students, and small companies.
Major Differences between Excel and Google Sheets (Comparison Chart)
The major differences between Excel and Google Sheets can be explained more clearly with the following
comparison chart:
Parameters MS Excel Google Sheets

Cost Factor MS excel is comparatively expensive, and users Google sheets is currently free for personal
must purchase the whole Office suite to use MS use, and the cost is almost negligible for
Excel. business users.

Installation MS Excel must be installed on the device along Google sheets does not require any
with the Office suite. However, Office 365 comes installation and works entirely with web-
with online support and allows users to use Excel browsers.
with web-browsers.

Developed by MS Excel is developed by Microsoft Corporation. Google sheets is developed by Google LLC.

Launched on MS Excel was initially launched in the year of Google sheets (as part of Google suite) was
1987. initially launched in the year of 2006.

Cloud-support MS Excel works offline. Office 365 is the only Google sheets uses Drive (also called 'Google
version that has support for Microsoft Excel Drive') to store data.
online. It uses Dropbox (also called 'Microsoft
Dropbox') to store data.

Collaboration Before Office 365, no other Office suite version Google sheets works online and supports
allows users to share MS Excel data to collaboration. It allows multiple people to
collaborate with other people. work on the same file in real-time.

Statistical Analysis & Excel is superior to Google sheets and other Although Google sheets has many built-in
Visualization spreadsheet software in statistical analysis and formulae, this particular tool is not as
visualization because it has an extensive range of powerful as Excel. It also does not allow users
built-in formulae and functions. to create charts such as flow charts or Gantt
charts automatically.

Macro support MS Excel allows users to record macros or use Google Sheets also allows users to record
VBE. macros; however, it does not use VBE.
Instead, it uses Google application scripting.

Complexity MS Excel can be a bit complicated for some users Google Sheets has a simple user interface
as it has a wide range of functions and advanced with essential spreadsheet functions and
functionalities. functionalities.

Huge data compatibility MS Excel is highly effective against vast amounts Google sheets is best compatible with small
of data or large data sets. Performing operations data sets. However, it may become slow or
with functions or manipulating the data can be even stuck with vast amounts of data unless
easily performed. the device is assembled with high-end
hardware.

Chat support MS Excel does not offer chat support since it Google Sheets offers chat support to help
works offline. However, Office 365 is an option users connect efficiently while collaborating
that comes with chat support, helping users to at work.
connect while working on Microsoft Excel online.
 Functions availability MS Excel supports more than 400 functions of Google sheets has relatively strictly limited
different categories. function availability.

Advancement Excel has advanced functionalities, allowing Google sheets do not have advanced
users to perform all the desired tasks related to functionalities like MS Excel. However, it is an
spreadsheets. efficient tool for performing almost all the
basic-level tasks.

Output formats MS Excel allows users to save their files into Although Google sheets can open various file
more than 20 formats, including popular types types, users can save them into six file
like XLSX, CSV, PDF, ODS, HTML, etc. formats only. This typically includes XLSX,
ODS, PDF, HTML, CSV, and TSV.

Versions compatibility MS Excel is available in different versions. Google Sheets does not face any
Sometimes, a file created with one Excel version compatibility issues as there is only one
does not work correctly in another version of MS version available for all the users. Likewise,
Excel. This somehow creates version the updates are given to all users
incompatibility. simultaneously.

Add-on There are many add-ons or extensions available Google Sheets currently has strictly limited
on the web that can be installed and linked with support for add-ons.
Excel to include/extend more features.

Cell Quantum MS Excel has about 17 billion cells in a Google sheets contain around 2 million cells
worksheet. maximum.

Shortcuts availability MS Excel has an extensive range of supported Google Sheets supports keyboard shortcuts,
keyboard shortcuts, allowing users to perform but it has limited shortcut keys. In another
most of the tasks efficiently. way, Google sheets supports fewer keyboard
shortcuts as compared to MS Excel.

Customizations MS Excel is highly customizable. All the shortcuts Google Sheets has limited customization
on the ribbon can be managed as per the options, which is almost negligible compared
requirements. to Excel.

Automation Automation of most tasks is possible through Automation is possible through scripts in
VBA in Excel. Google sheets.

Supported Languages MS Excel is officially available in nearly 91 Google sheets is officially available in nearly
different languages. 83 languages.

Which one is the best, MS Excel or Google sheets?

Finding the best spreadsheet tool between MS Excel and Google sheets mainly depends on the
requirements. MS Excel may be required for one user, and for another user, Google Sheets may be
sufficient. Both the tools have their advantages and limitations.

In particular, if the user needs to do basic spreadsheet work, Google Sheets is a good choice. Additionally,
it is free and does not oblige users to install any software or add-ons. Besides, MS Excel is a good choice
for advanced users who deal with large amounts of data and apply complex functions.
Office Button in Excel

MS Excel is one of the most popular spreadsheet software programs included with the Microsoft Office
suite. However, Office suite also has some other popular products, such as Word, PowerPoint, etc. To
make all these software user-friendly and effectively similar in the interface, Microsoft tries to make the
UI quite similar for all its products, allowing users to find commands and tools easily. One such common
option in all Office 2007 product interfaces, including Excel, is the Office button.

This article discusses the Office button in Excel (also called the Microsoft Office button). The article also
elaborates all the options found inside the office button along with their functions.

What is an Office Button in Excel?

The Office Button was an experimental feature of the Microsoft Office suite 2007. All Office 2007 products
(such as Word, Excel, PowerPoint, etc.) have a common button inside the active window, named Office
Button. Since it is also present in Excel thus, we call it Office Button in Excel while working with Excel or
referring to the button concerning MS Excel.

The Office button found in MS Excel 2007 and other Office 2007 products looks like this:

Note: It is very important to note that the Office button is only available in Office 2007 products. This
means that the Office button is only present in Excel 2007. Later, the Office button was deprecated in
Office 2010 and has since been no longer available in Excel 2010 and later. However, the options present
in the Office button have been provided under the File tab.

Where is Office Button found in Excel?

When we open Excel 2007 program, we see an Office Button on the top-left corner of the Excel window.
The button is attached to a ribbon and is identified by a circle containing an Office logo inside it.The office
button situated on the top-left corner in MS Excel 2007 looks like this:

What is the primary function of the MS Excel Office Button?

The primary function of the Office button in Excel and other Office 2007 products was to help users
perform frequently used actions immediately using the shortcuts. The office button provides many useful
options and helps us open, save, print any workbook with ease. Furthermore, we can easily access the
Excel options and configure them instantly as per our choice directly from the Office button.

Excel Office Button Menu Options

When we click the Office Button in Excel, we get the following menu options:

New
The 'New' option in the Office button menu primarily displays the 'New Workbook' dialog box where we
can select to either create a blank workbook/spreadsheet or use any of the existing templates by
Microsoft.

Open
The 'Open' option in the Office button menu displays the 'Open' dialog box using which we can choose
and open any specific excel file from our computer.

Save
The 'Save' option in the Office button menu helps us save the changes to an active workbook. This
option typically saves temporary data to the current file for future use, even after the corresponding file
is closed. Besides, if the 'Save' option is used the first time in a newly created spreadsheet, it will launch
the 'Save As' dialogue box, which is the next option in the menu list.
Save As
The 'Save As' option in the Office menu displays the 'Save As' dialog box, which helps us to save the copy
of an active file in the desired file format and with the desired file name at the desired location.

We further get some options when moving the mouse cursor onto the 'Save As' option or click the option.
They are:

o Excel Workbook: If we click on this option, we get a 'Save As' dialog box, with the default file type
selected as 'Excel Workbook (.xlsx)'. This file type can contain all the typical spreadsheet objects
except macro and is suitable to import to other relevant spreadsheet software.
o Excel Macro-Enabled Workbook: If we click on this option, we get a 'Save As' dialog box, with the
default file type selected as 'Excel Macro-Enabled Workbook (.xlsm)'. This file type can contain
macro.
o Excel Binary Workbook: If we click on this option, we get a 'Save As' dialog box, with the default
file type selected as 'Excel Binary Workbook (.xlsb)'. This file type can handle vast amounts of data
with macros but is not suitable for importing other relevant spreadsheet software.
o Excel 97-2003 Workbook: If we click on this option, we get a 'Save As' dialog box, with the default
file type selected as 'Excel 97-2003 Workbook (.xls)'. This file type is similar to the Excel workbook
(.xlsx), but it is suitable for old versions of Excel, such as Excel 2003 and earlier.
o Find add-ins for other file formats: If we click on this option, we get some extensions to include
support for other file formats that are not provided within Excel.
It mainly involves the most common spreadsheet file formats to help us save an active workbook instantly.
However, we can select the 'Other Formats' to display the 'Save As' dialogue box or get a list of all the
supported formats.

Print
The 'Print' option in the Office menu helps us take a hard copy of the current spreadsheet on paper with
the help of a connected printer. Additionally, we can also save a copy of an active document to a portable
document format (PDF).

We further get some options when moving the mouse cursor onto the 'Print' option or click the option.
They are:
o Print: If we click on this option, we get a 'Print' dialog box to adjust some common parameters for
printing. For example, the number of print copies, default printer, page orientations, etc.
o Quick Print: If we click on this option, Excel directly commands the system to print an active sheet
using the most recently used settings or the default settings.
o Print Preview: If we click on this option, Excel displays the print preview window where we can
see a digital view of the document that will be printed on paper.
Prepare
The 'Prepare' option in the Office menu helps us to prepare the current spreadsheet for distribution. That
means we can view or modify the properties of the spreadsheet as per our choice. Additionally, we can
inspect the hidden metadata and other personal information of an active workbook using this option.

We further get some options when moving the mouse cursor onto the 'Prepare' option or click the option.
They are:

o Properties: If we click on this option, Excel displays us properties window to view or configure
parameters (such as Workbook title, Author name, Keywords, etc.) for the spreadsheet. This
option is strictly related only to the properties of the workbook, not to Excel.
o Inspect Document: If we click on this option, Excel displays us 'Document Inspector' dialogue box.
This option mainly helps us inspect (find) and remove hidden data or any personal information
from the current workbook.
o Encrypt Document: If we click on this option, Excel displays us 'Encrypt Document' dialogue box
to configure a password to make our worksheet secure from unauthorized or unwanted people.
o Add a Digital Signature: If we click on this option, Excel shows us a 'Sign' dialog box. This option
mainly helps us to insert a sign to claim ownership of the spreadsheet digitally.
 o Mark as Final: If we click on this option, Excel marks the active workbook as final and saves it
accordingly. This option is mainly used when there are no further changes or modifications
required in a spreadsheet. In particular, the option makes the spreadsheet read-only and non-
editable.
o Run Compatibility Checker: If we click on this option, Excel shows us a 'Compatibility Checker'
dialog box. Using this option, we can use the older version of the Excel workbook in newer
versions or vice versa to prevent permanent data loss or incorrect functionalities.

Send
The 'Send' option in the Office menu helps us to share the current spreadsheet document with others
directly through the Excel program. We don't need to save it and then send it to others. Excel provides a
variety of sharing options under the 'Send' command. The option was later renamed as 'Save and Send' in
Office 2010.

We further get some options when moving the mouse cursor onto the 'Send' option or click the option.
They are:
o Email: If we click on this option, Excel shows us an 'Email' dialogue box where we can choose to
send a copy of the current spreadsheet as an email attachment over the web. Excel typically
provides two options to attach an active workbook into an email. We can either attach the current
document as PDF or XPS.
o Internet Fax: If we click on this option, Excel enables us to use an Internet fax service and fax the
spreadsheet as desired. However, this feature is not useful anymore.
Publish
The 'Publish' option in the Office menu enables us to distribute the current spreadsheet with people on
the network. Additionally, we can also create a blog article using the spreadsheet's content and share it
accordingly.

We further get some options when moving the mouse cursor onto the 'Publish' option or click the option.
They are:
o Document Management Server: If we click on this option, we get a 'Save As' dialog box, with the
default location of 'My Network Places' to save a corresponding spreadsheet.
o Create Document Workspace: If we click on this option, Excel launches the Document
Management task pane to create a site for the spreadsheet and synchronize a copy of it across
the system.

Close
The 'Close' option in the Office button menu helps us close the current spreadsheet or workbook opened
in Excel.
Excel Options
The 'Excel Options' button in the Office button menu helps us configure or modify the settings/
preferences of the MS Excel program. By using this, we can customize the entire Excel accordingly.

Exit Excel
The 'Exit Excel' button in the Office Button menu helps us to exit the Excel program. This means that we
can close all open spreadsheets/ workbooks, including Excel itself, at once.

Ribbon and Tabs in Excel

Excel ribbon is the row of tabs and icons at the top of the Excel window that allows you to find, understand
and use commands for completing a certain task. Excel ribbon is the primary interface that contains every
command and feature. The Ribbon has multiple display options according to your preferences.
In Excel Ribbon, the buttons and icons are grouped into different tabs based on the category of their
functionalities. It contains seven tabs: Home, Insert, Page Layout, Formulas, Data, Review, and View.
Each tab has its specific groups of related commands. These groups have several additional commands
that can view by clicking the arrow at the right bottom corner of any group.

The Ribbon first appeared in Excel 2007, replacing the traditional toolbars and pull-down menus found in
previous versions. In Excel 2010, Microsoft added the ability to personalize the Ribbon.

In Excel, the Ribbon is made up of these four basic components, such as:
1. Ribbon tab: It contains multiple commands logically subdivided into groups.
2. Ribbon group: A set of closely related commands normally performed as part of a larger task.
3. Dialog launcher: A small arrow in the lower-right corner of a group brings up more related
commands. Dialog launchers appear in groups that contain more commands than available space.
4. Command button: It is the button you click to perform a particular action.

Excel Ribbon Tabs

The standard Excel ribbon contains the following tabs, from left to right:
o File: It allows you to jump into the backstage view that contains the essential file-related commands
and Excel options. This tab was introduced in Excel 2010 as replacing the Office button in Excel 2007
and the File menu in earlier versions.
o Home: It contains the most frequently used commands such as copying and pasting, sorting and
filtering, formatting, etc.
o Insert: It is used for adding different objects in a worksheet such as images, charts, PivotTables,
hyperlinks, special symbols, equations, headers, and footers.
o Draw: It depends on the device type you're using. It lets you draw with a digital pen, mouse, or finger.
This tab is available in Excel 2013 and later, but the Developer tab is not visible by default.
o Page Layout: It provides tools to manage the worksheet appearance, both onscreen and printed.
These tools control theme settings, gridlines, page margins, object aligning, and print area.
o Formulas: It contains tools for inserting functions, defining names, and controlling the calculation
options.
o Data: It holds the commands for managing the worksheet data as well as connecting to external data.
o Review: It allows you to check spelling, track changes, add comments and notes, protect worksheets
and workbooks.
o View: It provides commands for switching between worksheet views, freezing panes, viewing, and
arranging multiple windows.
 o Help: It only appears in Excel 2019 and Office 365. This tab provides quick access to the Help Task
Pane and allows you to contact Microsoft support, send feedback, suggest a feature, and get quick
access to training videos.
o Developer: It provides access to advanced features such as VBA macros, ActiveX and Form controls,
and XML commands. This tab is hidden by default, and you have to enable it first.
o Add-ins: It appears when you open an older workbook or load an add-in that customizes the toolbars
or menu.

How to Hide Ribbon in Excel

If you want to use as much space as possible for your worksheet data, you can minimize the Ribbon by
pressing the Ctrl + F1 shortcut key. It mostly happens when you are using a laptop with a small screen.
Or you can hide the Ribbon completely by clicking the Ribbon Display Options button at the upper-right
corner of the Excel window and then select Minimize the Ribbon.

How to Unhide Ribbon in Excel

If all commands disappeared from your Excel ribbon and only tab names are visible, then you need to
press Ctrl + F1 to get everything back.
If the entire Ribbon is missing, then click the Ribbon Display Options button and unselect Minimize the
Ribbon.

How to Customize Excel Ribbon

If you want to personalize the Ribbon according to your requirements to know exactly where everything
is located, you can easily access them. You can enter the most customizations through the Customize
Ribbon window under Excel Options. And the shortest path is:
o Right-click on the Ribbon,
o And select Customize the Ribbon button from the context menu,
o Then it shows you customize the excel options window.
From there, you can add your tabs with any commands you choose, change the order of tabs and groups,
show, hide, rename tabs, and a lot more.

Contextual Ribbon Tabs

In addition to the constant tabs described above, the Excel ribbon also has context-sensitive tabs, aka Tool
Tabs, which show up only when you select a certain item such as a table, chart, shape, or picture. For
example, if you select a chart, the Design and Format tabs will appear under Chart Tools.

NOTE: If you are starting in Excel, Ribbon Hero might come in handy. It's a game created by Office Labs to help people
explore the most useful features of the Office ribbon. Although this project is not actively developed or supported
further, it is still available for download on the Microsoft website.

How to Show Developer Tab

The Developer tab is a very useful addition to the Excel ribbon that allows you to access advanced features
such as VBA macros, ActiveX and Form controls, XML commands, and more.
The problem is that the Developer tab is hidden by default. But it's very easy to enable it. Follow the
following steps to enable this tab, such as:
o Right-click on the Ribbon,
o Click on the Customize the Ribbon tab,
o Select the Developer under Main Tabs, and click on the OK
Similarly, you can activate other tabs available in Excel but not visible on the Ribbon, e.g., the Draw tab

Features of Tabs in Excel

The ribbon is designed to assist in quickly finding Excel commands to complete a task. The commands
are grouped together in logical groups, which are collected together under tabs. Each tab relates to a
particular activity, such as page layout or view. Some tabs only appear when needed to reduce the
clutter on the screen. These tabs are known as Contextual Tabs.

Commands related to working with Excel workbook content are represented as buttons on the tabs that
make up the groups. The Home tab is activated by default when Excel is opened. Within each tab,
except the File Tab, the buttons are organized into groups. In some groups, the button that might be
used most often is larger than the other buttons.

Less common commands can be accessed by clicking the Dialog Box or Task Pane Launcher button
located in the lower right corner of the group about the command. Many tabs are available on the
Ribbon in Microsoft Excel, such as Home, Insert, Page Layout, Formula, Data, Review, and View, and
each tab has different features.

Home Tab
The excel Home Tab is used to execute regular instructions like bold, underline, copy, and paste.
It is also used to apply formats to cells in a worksheet. It contains Clipboard, Font, Alignment,
Number, Cells, and Editing.

1. Clipboard: This Clipboard Group is primarily used for Cut copy and paste. It means, if you want to
transfer data from one place to another, then you have two choices, either COPY (preserves the data in
the original location) or CUT (deletes the data from the original location).
o There are options of Paste Special, which implies copy in the desired format.
o And there is also Format Painter Excel, which is used to copy the format from the original cell location
to the destination cell location.

2. Fonts: This font group within the Home tab is used for choosing the desired Font and size. There are
hundreds of fonts available in the dropdown, which we can use for. In addition, you can change the font
size from small to large, depending on your requirements. Also helpful is the feature of Bold (B), Italics (I),
and Underline (U) of the fonts.
3. Alignment: This group is used to align tabs, such as Top, Middle, or Bottom alignment of text within the
cell. And there are other standard alignment options like Left, middle, and right alignment. There is also
an orientation option that can be used to place the text vertically or diagonally.
o Merge and Center is used to combine more than one cell and place its content in the middle. It is a
great feature to use for table formatting etc.
o Wrap text can be used when there is a lot of content in the cell, making all the text visible.

4. Number: This group provides options for displaying number format. There are various formats
available, such as general, accounting, percentage, comma style in excel, etc. You can also increase and
decrease the decimals using this group.

5. Styles: You can have various styles for cells like Good, Bad, and Neutral. Other sets of styles are available
for Data and Models like Calculation, Check, Warning, etc.
In addition, you can make use of different Titles and Heading options available within Styles.
o The format Table allows you to convert mundane data into an aesthetically pleasing data table
quickly.
o Conditional formatting is used to format cells based on certain predefined conditions. These are very
helpful in spotting the patterns across an excel sheet.

6. Cells: This group is used to modify the cell according to its height and width etc. Also, you can hide and
protect the cell using Format Feature. You can also insert and delete new cells and rows from this group.

7. Editing: This group within the Home Tab is useful for Editing the data on an excel sheet. The most
prominent of the commands here is the Find and Replace in Excel Also, you can use the sort feature to
analyze your data (sort from A to Z or Z to A), or you can do a custom sort here.

Insert Tab
The Insert Tab is used to insert distinct features like tables, pictures, clip art, shapes, charts, page numbers,
word art, headers, and footers into a document. It contains Tables, Illustrations, Add-Ins, Charts, Tours,
Sparklines, Filters, Links, Text, and Symbols groups. The below table describes each of the teams and
buttons present on this tab.

1. Tables: This group provides an excellent way to organize the data. You can use a table to sort, filter,
and format the data within the sheet. You can also use Pivot Tables to analyze complex data very easily.
2. Illustrations: This group provides a way to insert pictures, shapes, or artwork into excel. You can insert
the pictures directly from the computer or use Online Picture Option to search for relevant pictures.
o The shapes provide additional ready-made square, circle, arrow kinds of shapes that can use in excel.
o SmartArt provides an awesome graphical representation to visually communicate data in List,
organizational charts, Venn diagrams, and process diagrams.

3. Charts: It helps you visualize the data in a graphical format. You can make graphs on your own, and
excel provides various options like Pie-chart, Line Chart, Column Chart in Excel, Bubble Chart k in Excel,
combo chart in excel, Radar Chart in Excel, and Pivot Charts in Excel. Recommended charts allow Excel to
come up with the best possible graphical combination.

4. Hyperlink: This is a great tool to provide hyperlinks from the excel sheet to an external URL or files.
Hyperlinks can also use to create a navigation structure with the excel sheet that is easy to use.

5. Text: This group is used to text in the desired format, such as add header and footer.
o WordArt allows you to use different styling for text.
o You can also create your signature using the Signature line
o Symbols are special characters that we may want to insert in the excel sheet for better
representation.
o The equation allows you to write mathematical equations that we cannot ordinarily write in an
Excel sheet.
Page Layout Tab
The Page Layout Tab holds all the choices that permit you to rearrange your document pages
simply in the manner you wish them. You can set margins, apply themes, manage page
orientation and size, add sections and line breaks, show line numbers, and set paragraph
indentation and lines. It contains Themes, Page Setup, Scale to fit, Sheet Options, Arrange.

1. Themes: Themes allow you to change the style and visual look of excel. You can choose various
styles available from the menu. You can also customize the colors, fonts, and effects in the excel
workbook.

2. Page Setup: This is an important group primarily used along with printing an excel sheet.
o You can choose margins for the print.
o You can choose your printing orientation from Portrait to Landscape.
o You can choose the size of paper like A3, A4, Letterhead, etc.
 o The print area allows you to see the print area within the excel sheet and helps make the
necessary adjustments.
o We can also add a break where we want the next page to begin in the printed copy.
o Also, you can add a background to the worksheet to create a style.
o Print Titles is like a header and footer in excel that we want them to be repeated on each printed
copy of the excel sheet.

3. Scale to Fit: This option is used to stretch or shrink the printout of the page to a percentage of the
original size. You can also shrink the width as well as height to fit in a certain number of pages.

4. Sheet Options: It is another useful feature for printing. If we want to print the grid, then we can check
the print gridlines option. If we want to print the Row and column numbers in the excel sheet, we can also
do the same using this feature.

5. Arrange: Here, we have different options for objects inserted in Excel like Bringforward, Send Backward,
Selection Pane, Align, Group Objects, and Rotate.

Formula Tab
The formula tab is used to insert functions, outline the name, produce the name, review the formula, etc.
In the ribbon, the Formulas tab has vital and most helpful functions to form dynamic reports. It contains
Function Library, Defined Names, Formula Auditing, and Calculation.

1. Function Library: This is a very useful group that contains all the formulas that one uses in excel. This
group is subdivided into important functions like Financial Functions, Logical Functions, Date & Timing,
Lookup & References, Maths and Trignometry, and other functions. One can also make use of Insert
Function capabilities to insert the function in a cell.

2. Defined Names: This feature can be used to name the cell, and these named cells can be called from
any part of the worksheet without working about its exact locations.
3. Formula Auditing: This is used for auditing the flow of formulas and their linkages.
o It can trace the precedents (origin of data set) and show which dataset depends on this.
o Show formula can also use to debug errors in the formula.
o The Watch window in excel is also useful for keeping a tab on their values as you update other
formulas and datasets in the excel sheet.
4. Calculations: The option selected for calculation is automatic. However, one can also change this option
to manual.

Data Tab
The data tab contains options mainly for filtering, sorting, and manipulating data. It has options for
importing external data.

1. Get External Data: This option is used to import external data from various sources like Access, Web,
Text, SQL Server, XML, etc.
2. Power Query: This is an advanced feature used to combine data from multiple sources and present it
in the desired format.
3. Connections: This feature is used to refresh the excel sheet when the data in the current excel sheet
comes from outside sources. You can also display the external links as well as edit those links from
this feature.
4. Sort & Filter: This feature can be used to sort the data from A to Z or Z to A, and also you can filter
the data using the dropdown menus. Also, one can choose advanced features to filter using complex
criteria.
5. Data Tools: This is very useful for advanced excel users. One can create various scenario analyses
using Whatif analysis - Data Tables, Goal Seek in Excel, and Scenario Manager. Also, one can convert
Text to Column, remove duplicates and consolidate from this group.
6. Forecast: This Forecast function can be used to predict the values based on historical values.
7. Outline: One can easily present the data in an intuitive format using the Group and Ungroup options.

Review Tab
The review tab contains tools mainly for spell checking, thesaurus, sharing, protecting, and tracking
changes in Excel Worksheets.
1. Proofing: Proofing allows you to run spell checks in excel. In addition to spell checks, one can also use
a thesaurus if you find the right word. There is also a research button that helps you navigate the
encyclopedia, dictionaries, etc., to perform tasks better.
2. Language: You can translate the excelsheet from English to any other language by using this feature.
3. Comments: This feature is used to write an additional note for important cells. It helps the user
understand clearly the reasons behind your calculations etc.
4. Changes: If you want to keep track of the changes made, one can use the Track Changes option. Also,
you can protect the worksheet or the workbook using a password from this option.

View Tab
View tab contains mainly commands to view the Excel worksheet, like change views, freeze panes, arrange
multiple windows, etc.

1. Workbook Views: You can choose the viewing option of the excel sheet from this group. You can view
the excel sheet in the default normal view, or you can choose Page Break view, Page Layout view, or
any other custom view of your choice.
2. Show: This feature can be used to show or not show Formula bars, grid lines, or Heading in the excel
sheet.
3. Zoom: Sometimes, an excel sheet may contain a lot of data, and you may want to change zoom in or
zoom out desired areas of the excel sheet.
4. Window: The new window is a helpful feature that allows the user to open the second window and
work on both simultaneously. Also, freeze panesare another useful feature that allows freezing of
particular rows and columns such that they are always visible even when one scrolls to the extreme
positions. You can also split the worksheet into two parts for separate navigation.
5. Macros: This is again a fairly advanced feature, and you can use this feature to automate certain tasks
in Excel Sheet. Macros are nothing but a recorder of actions taken in excel, and they can execute the
same actions again if required.

Quick Access Toolbar in Excel

The Quick Access Toolbar (QAT) is a small, customizable toolbar at the top of the Office application
window that contains a set of frequently used commands. These commands can be accessed from almost
any part of the application, independent of the ribbon tab that is currently opened.
The Quick Access Toolbar has a drop-down menu containing a predefined set of the default commands,
which may be displayed or hidden. Additionally, it includes an option to add your own commands.
There is no limit to a maximum number of commands on the QAT, although not all the commands may
be visible depending on the size of your screen.
Where is the Quick Access Toolbar Located?
A customizable toolbar is located in the upper left corner of the Excel window, above the ribbon. If you
want QAT to be closer to the worksheet area, you can move it below the ribbon.
When you click the down arrow at the end of the quick access toolbar, it displays more commands. You
can add any of these commands to the quick access toolbar by a left click on the command.

Customized Quick Access Toolbar

By default, the Excel Quick Access Toolbar contains only three buttons: Save, Undo, and Redo. If you use
a few other commands frequently, you can add them to the Quick Access Toolbar.
Microsoft provides many customization options for the QAT, but still, certain things cannot be done.
You can customize or free to personalize the Quick Access Toolbar with the following things, such as:

o Add your own commands.

o Change the order of commands, both default, and custom.
o Display the QAT in one of the two possible locations.
o Add macros to the Quick Access Toolbar.
o Export and import your customizations.
But here is a list of the following things that cannot be changed or cannot be customized, such as:
o You can only add commands to the Quick Access Toolbar. Individual list items (e.g., spacing values)
and individual styles cannot be added. However, you can add the whole list or entire style gallery.
o Only command icons can be displayed, not text labels.
o You cannot resize the Quick Access Toolbar buttons. The only way to change the size of the buttons
is to change your screen resolution.
o The Quick Access Toolbar cannot be displayed on multiple lines. If you've added more commands
than the space available, some commands won't be visible. To view them, click the More
Controls button.
How to customize Quick Access Toolbar Window
Most customizations to the QAT are done in the Customize Quick Access Toolbar window, which is part
of the Excel Options dialog box. You can open this window by following these steps:
Step 1: Click on the File tab and go to the Excel Options.
Step 2: Click on the customize button.

Step 3: The Customize Quick Access Toolbar dialog window will open, where you can add, remove, and
reorder the QAT commands.

Step 4: And then click on the OK button.

How to Add Command Button to Quick Access Toolbar

Depending on what kind of command you want to add, this can be done in three different ways.

1. Enable a command from the predefined list

To enable a currently hidden command from the predefined list, this is what you need to do:
o Click the Customize Quick Access Toolbar button (the down arrow).
o In the list of the displayed commands, click the one you want to enable.
For example, to be able to create a new worksheet with a mouse click, select the New command in the
list, and the corresponding button will immediately appear in the Quick Access Toolbar:
2. Add a ribbon button to Quick Access Toolbar
The fastest way to add to the QAT a command that appears on the ribbon is:
o Right-click the desired command on the ribbon.
o And select the Add to Quick Access Toolbar in the context menu.

3. Add a command that isn't on the ribbon to Quick Access Toolbar

To add a button that is not available on the ribbon, follow these steps:
Step 1: Right-click on the ribbon and click Customize Quick Access Toolbar.
Step 2: In the Choose commands from the drop-down list on the left, select Commands Not in the
Ribbon.
Step 3: In the list of commands on the left, click the command you want to add.
Step 4: Click on the Add button.
Step 5: Click on the OK to save the changes.

For example, to have New open Excel windows with a single mouse click, you can add the New button
to the Quick Access Toolbar.
How to Remove a Command from Quick Access Toolbar
To remove either a default or custom command from the Quick Access Toolbar, right-click it and
pick Remove from Quick Access Toolbar from the pop-up menu.

Or select the command in the Customize the Quick Access Toolbar window, and then click
the Remove button.s

Rearrange Commands on Quick Access Toolbar

To change the order of the quick access toolbar commands, follow the following steps:

Step 1: Open the Customize the Quick Access Toolbar window.

Step 2: Under Customize Quick Access Toolbar on the right, select the command you want to move, and click
the Move Up or Move Down arrow.
For example, to move the New File button to the far-right end of the quick access toolbar, select it and
click the Move Down arrow.

Group Commands on Quick Access Toolbar

If your quick access toolbar contains quite a lot of commands, you may want to sub-divide them into
logical groups. For example, separate the default and custom commands.
Though the Quick Access Toolbar does not allow creating groups like on the Excel ribbon, you can group
commands by adding a separator. Follow the following steps, such as:

Step 1: Open the Customize the Quick Access Toolbar dialog window.
Step 2: In the Choose commands from the drop-down list on the left, pick Popular Commands.
Step 3: In the list of commands on the left, select and click Add.
Step 4: Click the Move Up or Move Down arrow to position the separator where needed.
Step 5: Click OK to save the changes.
As a result, it separates the command, and the quick access toolbar appears to have two sections, as
shown in the below image:

Add Macros to Quick Access Toolbar

To have your favorite macros at your fingertips, you can add them to the QAT too. To have it done, then
follow these steps:

Step 1: Open the Customize the Quick Access Toolbar window.

Step 2: In the Choose commands from the drop-down list on the left, select Macros.
Step 3: In the list of macros, select the one you wish to add to the Quick Access Toolbar.
Step 4: Click on the Add button.
Step 5: Click on the OK to save the changes and close the dialog box.

As an example, we are adding a custom macro RANDOM in the current workbook:

Optionally, you can put a separator before the macro like shown in the screenshot below:

Export and Import a Custom Quick Access Toolbar

Microsoft Excel allows saving your Quick Access Toolbar and ribbon customizations into a file that can be
imported later. This can help you keep your Excel interface looking the same on all the computers you
use and share your customizations with your colleagues.
1. Export a customized quick access toolbar:
In the Customize the Quick Access Toolbar window, click Import/Export, then click Export all
customizations, and save the customizations file to some folder.
2. Import a customized quick access toolbar:
In the Customize the Quick Access Toolbar window, click Import/Export, select Import
customization file, and browse for the customizations file you saved earlier.

The file that you export and import also includes the ribbon customizations. Unfortunately, there is no
easy way to export or import, only the Quick Access Toolbar.

When you import the customizations file to a given PC, all prior ribbon and QAT customizations on that
PC are permanently lost. To restore your current customizations in the future, be sure to export them
and save them as a backup copy before importing any new customizations.
Mini Toolbar in Excel

The Mini Toolbar is a floating toolbar that appears above the shortcut menu when you right-click a
cell or other objects like a shape or chart. It is a small version of the full toolbar that displays commonly
used formatting commands like Bold, Italics, Font Size and Font Color. The mini toolbar was introduced
as a quick-access version so the Excel users can easily access the Font group, border, and few extra
buttons on the Home tab.

The Mini Toolbar is also available in other office applications such as Word and PowerPoint. When you
select any text or right-click on it, a faded, partially transparent pop window emerges on the top of your
selected text. If you hover your mouse pointer over it, it immediately converts to its solid and usable
form. Sometimes the Excel users may find it annoying as it keeps getting in the way. In that case, they
can turn it off (we will learn the steps to turn off Mini Toolbar later in this tutorial).

FACT: Do you know that the Mini Toolbar feature is only available in the 2007 and 2010 versions
of Word, Excel, PowerPoint, and Outlook. The user can even turn off this feature if it is not required.
However, the toolbar commands are pre-defined and cannot be customized to include different other
functions.

Mini Toolbar Mouse Pointer Rules:

o If you bring your mouse pointer towards the Mini Toolbar, it freezes and immediately becomes
active.
o If you take your mouse pointer away from the Mini Toolbar, it disappears.
o You will notice that the Mini Toolbar disappears if you press any key from your keyboard or scroll
the wheel of your mouse.

Mini Toolbar Features:

The mini toolbar contains the most commonly used tools of the Home Tab. The list of the tools are as
follows:

1. Font Type(Drop-Down) - The Font feature shows the list of fonts available in Excel. The user can
quickly select or change the font of the cell as per their choices.
2. Font Size(Drop-Down). With the help of Font Size, you can quickly adjust the size of the text.
3. Increase Font Size- This feature allows Excel users to quickly increase the font size of the selected cell
to the next larger font size.
4. Decrease Font Size- This feature allows the Excel users to quickly decrease the font size of the
selected cell to the previous smaller font size.
5. Percent Style- With the help of the Percent Style feature, you can incorporate the percent number
format into the selected cells.
NOTE: The shortcut key to perform percent style in Excel is Ctrl + Shift + %.
6. Comma Style- With the help of the Percent Style feature, you can incorporate the comma style
number format into the selected cells.
7. Accounting Number Format(Drop-Down)- The Accounting Number feature incorporates the 'default'
number format to the selected cells. The drop-down consists of the following commands:
o English (UK)
o English (US)
o Euro
o and More Accounting Formats.
8. Merge & Center(Drop-Down). The Merger & Centre feature concatenates the selected cells and puts
the contents in a new cell in the centre position. The drop-down consists of the following commands:
o Merge & Center
o Merge Across
o Merge Cells and Unmerge Cells
The Merge Across is similar to the Merge and Center feature with a single difference that merges and
centres the selected cells in the same row with no change in the alignment.
9. Bold- This feature toggles bold on the selected cell.
NOTE: The shortcut key to apply Bold in Excel is Ctrl + B.
10. Italic- This feature toggles italics on the selected cell.
NOTE: The shortcut key to apply Bold in Excel is Ctrl + I.
11. Center- This feature aligns the content of the selected cell in the center position.
12. Fill Color(Drop-Down)- The Fill Color feature is useful whenever the user want to quickly apply a color
to the background of the selected cell. The drop-down consists of the following commands:
o Theme Colors
o Standard Colors
o No Fill and More Colors.
13. Font Color (Drop-Down). As the name suggests, the Font colour features allow the Excel user to quickly
change the colour of the font of the selected cell. The Font Color drop-down incorporates the below give
commands:
o Automatic
o Theme Colors
o Standard Colors and More Colors.
14. Borders(Drop-Down). The Borders enables the users to quickly apply a border of any length and
thickness to the selected cells. The Borders drop-down incorporates the below give commands:
o Bottom
o Top
o Left
o Right
o No
o All
o Outside
o Thick Box
o Top and Bottom
o Draw Border
o Thick Bottom
o Top and Thick Bottom
o Erase Border
o Bottom Double
o Top and Double Bottom
o Draw Border Grid
o Line Color
o Line Style and More Borders.
15. Increase Decimal- As the name suggest, this features adds one decimal place from the selected cells.
16. Decrease Decimal- As the name suggests, Increase Decimal adds one decimal place from the selected
cells.
17. Format Painter- This feature copies the formatting from any cell (or group of cells or range) enabling
the user to paste the same formatting elsewhere.

Removing the Mini Toolbar

Many times, while working on excel, the Mini Toolbar pops up very often, and therefore many users may
find it annoying as it keeps getting in the way. Thus, Excel provides the option to switch off Mini Toolbar.
Following are the steps to turn off the Mini Toolbar from Excel:

1. In your Excel application, click on File > Options > General.

2. You will see the check box in front of the Show Mini Toolbar. Clear the selection. (Refer to the
below screenshot)

3. Click OK.

Title Bar, Help Button, Zoom Control and View Buttons

Title Bar
It lies next to the quick access toolbar or on top of the excel window. It displays the name of the open
document.
Help Button
It lies on top right corner of the excel window beside the option to minimize the window. It is in the form
of an encircled question mark. It provides excel related help.

Zoom Control
Zoom control is a slider that lies next to view buttons at the right end of the status bar. It helps zoom in
and zoom out the document. Move the slider to right or click on the plus sign to zoom in and move it to
left or click on the minus sign to zoom out.

View Buttons
There are three view buttons on the right end of the status bar, to the left of the zoom control. These
buttons provide different options to view the document.
Worksheets in Excel
While working with Excel, often you come across terms worksheet, Rows, Columns and Cells. In this
tutorial we will briefly understand these terms along with their fundamentals and the operation (create,
copy, paste, delete, etc) performed in them.

Worksheet
A Worksheet is a single page containing a collection of cells where the user can store, update and
manipulate the data. Worksheet is also called a spreadsheet. It is made up of rows, columns and cells.
NOTE: An Excel workbook can contain several worksheets.

In Excel, individual data cells in a worksheet are defined and organized with the help of the
vertical column and horizontal row that are combined to form a cell reference, unlike A1, B25, or
R867.

The specifications for Excel Worksheet current versions include:

o Number of Rows per Worksheet: 1,048,576
o Number of Rows per Worksheet: 16,384
o Total cells per Worksheet: 17,179,869,184
Select a Worksheet
Whenever you open an Excel workbook, by default, Excel will take you to the Sheet1 worksheet. As you
can see in the below image, the name of the worksheet 'Sheet1' is displayed on its sheet tab at the bottom
of the windowpane. Though you can rename it anytime, we will cover the steps down in this tutorial.
Insert a Worksheet
Excel enables the user to insert multiple worksheets as per their requirement. To insert a new
worksheet in excel, click on the plus (+) symbol located at the bottom of the windowpane. By default,
the new worksheet will be named 'SheetN' where N represents an integer number 1,2,3,4,5,6,7,...., n.

In the below example, we have added a new worksheet named Sheet2.

Output

Rename a Worksheet
To rename your worksheet with a customized and appropriate name following the below steps.
1. Right-click on the sheet tab for which you wish to change the name.
2. The toolbar window will appear. Choose the Rename option.
3. Type your preferred name. For instance, in the below screenshot, we have renamed 'Sheet1'
to Sales 2021.

Move a Worksheet

Excel allows the user to move and locate their worksheet in any order. Click on your worksheet sheet
tab and drag it to your preferred location.

For instance, in the below example, we have dragged Sales 2021 before the 'Sheet 2' sheet tab.

Output

Delete a Worksheet
Deleting a worksheet in excel is very simple, follow the below steps:
1. Scroll down to the bottom and right click on a sheet tab.

2. The dialog box will appear. Click on delete

3. The sheet will be immediately deleted.

Copy a Worksheet
While working many times, the Excel users require copying and pasting the data of one worksheet to
another and making some changes. Excel provides easy steps to copy the data of one worksheet to
another.
1. Right-click on the sheet tab that you want to copy.
2. The toolbar window will appear. Click on the 'Move or Copy' option.

3. The 'Move or Copy' dialog box will be displayed. In the Before Sheet section, select the 'move to
end' option and make sure to check in the 'create a copy'.

4. Click OK. Your data will be copied to the new worksheet.

ROWS

Rows run horizontally across the worksheet ranging from 1 to 1048576. A row is identified by the number
on the left side of the row, from where the row originates.
Insert a Row in Excel
Many Excel users often need to insert a row in the worksheet to organize or manipulate their data.
1. Select and right-click on the cell within the row where you want to insert a new row.

2. The dialog box will be displayed. Choose Insert.

3. The insert dialog box will appear. Choose the 'Entire row'

4. It will insert a new row at the top of your selected cell.

Delete a Row in Excel
While working with Excel users need to delete a row in the worksheet to organize or manipulate their
data. It's a lot easier to delete a row in excel.
1. Select any cell within the row that you need to delete. Right-click on the cell.

2. The dialog box will be displayed. Choose Delete.

3. The Delete dialog box will appear. Choose the 'entire row' option.
4. The entire row will be immediately deleted.

COLUMNS

Columns run vertically downward across the worksheet ranging from A to XFD (in total 16384 columns).
A column is identified by a column header on the top of the column, from where the column originates.

Insert a Column in Excel

Many Excel users often need to insert a column in the worksheet to organize or manipulate their data.

1. Select and right-click on the cell within the column where you want to insert a new column.

2. The dialog box will be displayed. Choose Insert.

3. The insert dialog box will appear. Choose the 'Entire column'

4. It will insert a new column at the left of your selected cell

CELLS

Cells are small rectangular boxes in the worksheet where we enter data. A cell is the intersection of a
row and column. It is identified by row number and column header. In Excel, each cell is identified using
a set of coordinates or positions, such as A1 (where A represents a column and 1 represents the row), B2,
or M16. You can perform several operations with cells in Excel, unlike changing the font style, font size,
background color, text alignment, format painter, wrap text, and conditional formatting.

Excel provides the feature to select multiple cells at one time. A group of selected cells is collectively
known as cell range. With cell range instead of a single cell address, the user refers to a cell range utilizing
the cell addresses of the selected first and last cells, separated by a colon. For instance, a cell range
covering B2, B3, B4, B5, B6, B7, B8, B9, and B10 would be addressed as B1:B10.

Cell Range: B1: B10

Now, as we have covered the basics, let's move towards the operation that could be applied to a
cell.

Select a cell
To perform any operation, i.e., to write, edit, delete, format, the user must first select a cell. Below given
are the steps to select a cell in Excel:
1. With the help of your cursor, click on any cell.
2. Your cell is now selected, and you will notice a rectangular borderaround the same cell, and the
column and the row heading will be highlighted.

3. The cell will remain selected unless and until the user points the cursor to another cell.

Select cell range

1. Click on the first cell from which to start the selection. Hold and drag your cursor to the last
adjoining cells where you want to end the cell range.
2. Once done, release the cursor, and the desired cell range would be selected.

3. The cell will remain selectedunless and until the user points the cursor to another cell.

Cell content
Any data you enter in your Excel worksheet is stored in a cell. Each cell can hold various types of
information, including string, numbers, formatting, formulas, and functions.

1. String (Text)
Cells can hold string values, unlike alphabets, numbers, and dates.
2. Formatting properties
Cells also include formatting properties that improve the appearance of your cell content. For
instance, percentages can be displayed as 0.55 or 55%. You can make the cell content in Bold,
Italics, change its font color, font size, or change its background color.

3. Formulas and functions

Cells can include predefined or customized formulas and functions that are helpful in
calculations. For example, SUM(A2:A5) will add the number stored in each cell and give you the
output.

Insert content to a cell

Click and select the cellwhere you want to insert the content.

1. Type in the desired data into the selected cell, then click the enter button or move your cursor to
another cell. The content will be shown in the cell and the formula bar.

Delete cell content

1. Click and select the cell.

2. To delete the cell content, press the Delete or Backspace key from your keyboard.
3. If you want to delete multiple cells, select the cell range and press the Delete key.

Note: Delete Key can delete the content of multiple cells at once, whereas the Backspace key
only deletes the data of a single cell at a time.

Delete cell(s)
People often get confused between deleting the cell content and deleting the cell itself, but there is a
significant difference. If you delete the content, the cell will remain, and only the content gets omitted,
whereas if you delete the cell, the cells below shift up or are shifted left and replace the deleted cell.
1. Click and select the cell(s).

2. Right-click on the cell. The dialog box will be displayed. Choose Delete.
3. The given below Delete dialog box will occur. Choose the 'entire row' option.

4. The cell will be immediately deleted and the cells lying below will shift up.

Drag and drop cells

Instead of copying and pasting the data, you can use the drag and drop option to move cell content from
one cell to another.
1. Click and select the cell(s).
2. Point your cursor over the border of the selected cell(s) till it changes to a black cross with
four arrows.

3. Drag your mouse cursor to the cell you prefer to paste the content. At this point the mouse pointer
will change to a dotted rectangle.

4. Release the cursor, and the content will be cut from the original cell and pasted in the selected
location.
Moving across the worksheet in Excel
There are multiple options to move across the worksheet using mouse and keyboard.

You can scroll the work sheet up or down by rolling mouse wheel or using vertical scroll bar. In a similar
way, you can use horizontal scroll bar to move the work sheet horizontally towards left or right side.

How to enter data in Excel

Select a cell with a single click where you want to enter data; cell B3 is selected in the image given below.
Then double click in the cell to enter data. You can enter text, numbers and formulas in the cell.

After entering data, you can press Tab key to move to next column and can press Enter key to move to
next row. You can press arrow keys for more options to move to other cells.
How to select data in Excel
Selecting data is one of the most common operation Excel user practices in their day-to-day life.

There are various methods to select a cell or multiple cells in Excel, such as through a mouse or by
keyboard shortcut keys or a combination of both. As Microsoft Excel users, it is essential to quickly select
cells, rows, columns, table cells present in an Excel worksheet. Selection assists the users in various tasks
such as insertion, deletion, and formatting of multiple rows and columns in an Excel worksheet. Whenever
you select any cells, row, column, or cell range, you will notice that the color of the selected cell(s) will
change and get highlighted compared to the rest of the cells.

This tutorial will discover the various ways for selecting cells, row columns, tables, and the range of an
Excel worksheet.

Select a Single Cell

Selecting a cell in Excel is pretty easy. Just put your mouse cursor on the respective cell that
you want to select, and that's it.

For example:
We want to select the B 4 cell. So we will keep our cursor to that particular cell address to select it.

Select Multiple Continuous Cells

Selecting multiple continuous cells is easy and simple. Select the first cell from where you want to start
the selection and drag the cursor to the end of the selected cell. Suppose you want to select multiple
cells B1: E10.
Follow the below steps:
Put your mouse cursor on the first cell from where you want to start your selection, i.e., B1.

1. Press your mouse button and drag the mouse cursor till cell E10. It will include all the in-between
cells.
2. Leave the mouse cursor, and the cells B1: E10 will get selected.

Select Rows/Columns

While working with Excel many times, the user requires selecting an entire row or column for various
operations such as hiding or deleting the row or column, applying some conditional formatting,
highlighting the data, etc.

Unlike you select a cell in Excel by placing the mouse cursor on the respective cells, you can also select
any row or a column by moving your mouse on the row or column.

1. Select a Single Row

Below given are the steps to select an entire row in Excel.
o Place your mouse cursor on the top of your row number. For example, if you can second the select
row, so bring your cursor to Row 2.

Double click on the row number, and it will select the entire row. In the below image, you can
see the entire Row 2 is selected (Excel highlights the cells in blue to represent selection).
o

2. Select a Single Column

As in the above steps, we have seen how to select an entire row in Excel. Similarly, you can select a
column as well. Follow the below steps to select an entire Column in Excel:
o Place your mouse cursor on the top of your Column alphabet. For example, if you can second the
select column, so bring your cursor to Column B.

o Double click on the column alphabet, and it will select the entire column. In the below image, you
can see the entire Column B is selected.

3. Select Multiple Contiguous Rows/Columns

We work on Excel and manipulate the data. Sometimes we want to select multiple rows/
columns together to apply the same operation on adjacent rows/columns at once.
For example, suppose that you want to select row numbers 5, 6, and 7 together.
Follow the below steps.
o Bring your mouse cursor over row number 5 in the Excel worksheet.
 o Double click the left cursor to select Row 5, but keep the mouse left button pressed as we have
to select multiple rows.

o While pressing the mouse drag your cursor down till you reach Row 4. At this point leave the
mouse pointer and the three adjacent rows will be selected.

Following the above steps, you can select multiple columns as well. Make sure to click on the column
alphabet instead of row number and drag till the last column selection.

4. Select Multiple Non-Adjacent Rows/Columns

Not always, we work with contiguous Rows/Columns. We may require it to work with non-adjacent
rows/columns as well. How do we select them?

Selecting non-adjacent rows/columns is simple. However, the above select and drag technique won't
work as restricted to adjacent cells, rows, or columns. To select random cells, rows, or columns in Excel,
we have to use a keyboard and mouse combination. Let's say we want to select random rows such as
row numbers 5, 7, 9, 10. Below given are the steps.

1. Bring your mouse cursor on the top of your first-row number, i.e., row 5 in Excel. Click on the row
number to select it.

2. Next, press the Control key on your keyboard.

3. While pressing the Control Key, drag your mouse cursor to the desired row numbers to select them.
First, we dragged it to Row 7 and clicked on row number. As you can see, it got selected.

4. Leave the mouse button, move your mouse cursor to Row 9, click on the row number,
and select it.

4. Similarly, select row 11.

5. Once you have selected the required rows, leave the mouse and the CTRL-key.
Unlike when you have selected the adjacent rows using a keyboard and mouse combination,
you can apply the same method to select multiple non-adjacent columns.

Select the Cells in the Excel Table

Most of the time, you work with Excel Table, and you are required to select all the cells in the table to
apply specific operations, formatting, or filters.
You manually select the table cell using the click and drag option. But it could be time-consuming, and
with large tables, it becomes damn hard. However, with the help of Excel keyboard shortcuts, selecting
the table cells of the worksheet can be done in a blink of time.
Below given are the steps.
 1. Go to your table and select any cell within your Excel table data set

2. Press the Ctrl key and alphabet A key from your keyboard.

2. It will quickly select all your cells from the table.

Note: CTRL + A would select all the cells within the table data set unless and until Excel runs into a blank row or
column as it would consider the blank row or blank column as the end of the data set and won't stop the selecting
operation.
Select Cells Using Name Box
So far, we have covered various selection methods such as selecting the cells manually or with the help
of keyboard shortcuts or using a combination of a mouse and keyboard. But the cells we selected were
close by and easy to locate.

In some cases, you may require to select multiple continuous or non-continuous cells, rows, or columns
located at a distance in your Excel worksheet. Of course, those cells can be selected manually, but to
your surprise, it will be time-consuming, and if it is something you have to do regularly, then it is a great
concern. Therefore, to save your time and select the far-off cells quickly, you can use the Name Box.

"In Excel, Name Box is the small rectangular field placed on the top left corner of your worksheet." In
general, whenever you select any cell in your Excel worksheet, the cell reference (of the active cell) is
shown in the name box. Though the vice versa is also applicable, i.e., whenever you type any cell
address in the name box, it selects all the mentioned cells or cell range."

For example: Let's suppose we have to select multiple non-contiguous cells, unlike B12, N43, and Z50. If
you notice the cells mentioned earlier are far off each other, it would be cumbersome and time-
consuming if we select it manually.
So let's try selecting the cells using the name box instead. Follow the below steps.
1. With the help cursor, click on the name box mounted at the top left corner of your Excel worksheet.

2. Types in the cell address that you want to select. Separate the cell references with a comma in case
you want to select multiple cells. For example, in our case, we will type in B12, N43, Z50.

3. Once done, press the Enter button. It will instantly select the entered cells.
Note: In the Name Box bar, only the cell references of the active cells are visible. It does not represent the
total cells you selected; it only displays the active cell, so don't get confused.

Selection using Keyboard Shortcut Keys

Selection helps you to operate easily. So far, we have seen many different ways, but Excel also
provides some useful shortcut keys using which you can select the combination of cells. Below
given are some useful keyboard shortcuts that will help you to automate the selection process
easily.
1. Ctrl+End This keyboard combination selects the bottom-right cell in your Excel worksheet.

2. Ctrl+Home This keyboard combination selects the top- left (A1) cell in your Excel worksheet.

3. Home This keyboard combination selects the left-most cell of the active row in your Excel
worksheet.

4. End, Enter This keyboard combination selects the right-most cell in the used range in the
active row of your Excel worksheet.

5. Ctrl, A This keyboard combination selects the current region in your current worksheet.

6. Ctrl, \ This keyboard combination selects the cells in selected rows that do not match the
formula of your current Excel worksheet.

7. Ctrl, Space This keyboard combination selects the entire column(s) for the selected range for your
active spreadsheet.

How to delete data, rows and columns in Excel

How to Delete Data
Select the data you want to delete, right click on it then select delete option from the menu. You can
also delete it by pressing Delete key on the keyboard. See the image:
How to Delete a Row
Select the row by a left click on the row number then right click and select the Delete option. If
you want to delete more rows drag the mouse downward to select more rows. See the image:

How to Delete a Column

Select the column by a left click on the column header then right click and select Delete option.
To delete more columns drag the mouse horizontally left or right to select more columns. See
the image:

Move data in Excel

Moving your cells, rows, or columns is a common operation performed by every Excel user in
their day-to-day life.
Let's understand this using an example. Suppose I have a dataset (given below), and I want
to move the highlighted row to the last position of the Excel table.
The common method that most of you will think of is by copying the content of the row, pasting the copied
row to the desired location, and later deleting the content from its original location.

But don't you think it's a bit of a lengthy process. So why not shift to a faster way to move rows and
columns in Excel. While working with Excel, you may regularly need to insert, move or delete cells.
Whenever cells are inserted, moved, or deleted, the existing cells shift, along with their data, to
accommodate for the modifications to adjacent cells. In this tutorial, we will discover some methods that
will quickly help you to move your rows and columns from one place to another.

Move Data using Cut and Insert

1. Select the data you want to move from one place to another.

2. Place your cursor on any of the selected cells and right-click on it. The following window
will be displayed. Select the cut option.

3. Next, choose the cell where you want to shift the data. For instance in the below example I have
chosen B9 cell.
 4. Again right-click on the selected cell, and from the window pane, select the Insert option-
> Insert Copied Cells.

5. As you can see in the following output, the data will be moved to the desired location.

Move Data using Shift key

Moving data using the shift key is basically the same as cutting and pasting data, and it delivers the same
output. Both the Excel operations enable the users to move large chunks of Excel data from one place to
another. But move data using the shift key is essentially used to move the data over a short
distance where the mouse cursor could be easily dragged.
Let's see how we can easily move Excel rows and columns using the Shift key.

Move Rows in Excel

Let's suppose you have the following data entries in your Excel worksheet. You want to move
the highlighted row to cell B9.
Follow the below-given steps to move rows in Excel quickly:
1. Place your cursor on the respective row to select it.

2. Press the Shift Key on your keyboard.

3. Move your mouse pointer to the end of the selected row. You would notice that the cursor will
change to the move icon (a four-directional arrow).

4. You still require pressing the shift key and moving the cursor to the new location where you want
to place the information.
5. The entire row will be cut from its original place and moved to the new allotted location.

6. At this point, leave the mouse cursor and the Shift key.

Note: Though I referred to row, in the above example, I have selected some cells from the row. But by
following the above steps, you can move an entire row as well, and it will quickly move the entire row to
a new location.

Here are some key points one should know about the Excel 'Move' method:

o With the help of this method, one can only move contiguous cells, rows, or columns. This method is
not applicable for non-contiguous or adjacent cells, rows, or columns. For instance, you can't move
rows 2 and 7 together, and you have to move them one by one. However, you can move rows 2 and
3 together.
o Whenever you move or shift cells in a row or column using the move shift key method, it will not
affect any other data (above or below / to the right or left of the cell) in your Excel spreadsheet.

Move Columns in Excel

So far we learned how to move cells of a row from one place to another. Using the same method can
also be applied for moving columns in Excel.
Follow the below-given steps to move rows in Excel quickly:
1. Place your cursor on the respective column (or adjacent columns) to select it.
2. Press the Shift Key on your keyboard.

3. Move your mouse pointer to the end of the selected row. You would notice that the cursor will
change to the move icon (a four-directional arrow).

4. You still require pressing the shift key and moving the cursor to the new location where you want to
place the selected column. For instance, in the below example we want to move to column H

5. The entire column will be cut from its original place and shifted to the new allotted location.

6. At this point, leave the mouse cursor and the Shift key.
How to copy paste data in Excel
When we work with Excel worksheets, we may require the same data at multiple places. Each time writing
the same data takes a lot of time. So, it is good to use the copy and paste option in Excel. It will save your
time and effort to do the same task again and again.

Not only Excel but almost all text and image editors allow copying data and pasting it to another place.
For this, they all have a common copy and paste shortcut command. Ctrl+C to copy the data and Ctrl+V to
paste the data. In this chapter, we will take examples to brief the copy-paste options in Excel and how
they are used in Excel.

Excel copy paste options

Excel enables the user to copy the data from one or more cell and paste to another cells, worksheet, or
wherever you want. Besides the simple copy-paste operation, it also offers an option of special paste.

Note: The simplest trick to copy and paste data is - Select targeted data + press Ctrl+C + go where you
want to paste the data + press Ctrl+V.

Excel provides different types of paste options to paste the data differently like simple paste and special
paste. We will show you all these methods in this chapter with their detailed example.

The one biggest advantage of copy and paste option is that - it does not remove the data from where you
have copied. Whereas, if you cut and paste the data from one place to another, it removes the data from
the previous place.

Step to simple copy-paste in Excel

These steps are containing an example of the simple copy-paste of data. This data does not contain any
formula, formatting, or validation. It needs only a few simple steps, same as other editors and you can
easily copy the data to another place.
Following are the steps to copy the data and paste it to another place by using a simple copy-paste
operation.

Step 1: We have this data stored in an Excel worksheet. Select the data which you want to copy or use
the Ctrl+A to select all data.

Step 2: We have selected the first four rows including header, to copy from this Excel sheet. You can
also select a particular cell, row, or column data.
Step 3: Now, use the Ctrl+C shortcut command to copy the selected data. (You can also copy the
data by right-clicking on selected data and then choose the Copy command.)

Step 4: Go to the place where you want to paste the data and take the cursor there, then use
the Ctrl+V shortcut command to paste the copied data.

We have added a new sheet in the same Excel file to place the copied data.
Step 5: You can see that the data is successfully pasted to another place without any interruption.
Similarly, you can paste the data wherever you want.

Increase the size of the column width to make the data completely visible. Now, save the file to keep the
changes done here.

Note: When you copy and paste the data to another place, the original data does not remove from the
place you copied. It still remains at its location.

Step 6: You can also go back to the sheet from where you copied the data to see that it is still there.

It was an example of simple copy-paste.

Paste special method

The above steps we have described are only for simple data to copy-paste. A simple copy-paste operation
will not work if your data contains any formatting, formula, or validation applied to it. It would help if you
used Paste Special option to copy-paste the values with formatting.

We will define the steps for it. This method will take more steps than the above example. Following are
the steps to copy the data and paste it with or without formatting applied to it using Paste Special method
to another place by using simple copy-paste operation.
When to use this method?
For example, we have a dataset with formula, formatting, or conditional formatting applied on this data.
If you simply copy-paste this column value, cell reference will be changed. This will cause that all values
become 0 and formatting will also not keep as it is. Hence, we will suggest you use the Paste Special
method. We will use Paste special option to keep the formatting even after pasting the data.
Examples
In this chapter, we will show you three different examples to illustrate the usage of the Paste Special
method. With the help of them, you will better learn that when and how to use Paste Special method.
1. Copy only values without color formatting
2. Copy with column width
3. Copy only values not formula
Learn these examples in detail one by one.

Copy only value without color formatting

Sometimes, we apply color formatting on Excel data to analyze the data based upon certain conditions.
We use conditional formatting on Excel data for this. In case If we need this data at another place but
without color formatting, the simple copy-paste method does not work. It copies the data along with color
formatting.

We can use Paste Special option to keep the only data without color formatting. Copy the targeted data
and paste it using Paste Special method. You have to choose a format while pasting the data without
formatting. Let's see with an example to copy data without color formatting -

Step to copy and paste special

Step 1: We have the following data with color formatting in an Excel worksheet. Select this data and
copy it using the Ctrl+C shortcut command.

Step 2: We want to paste this data into a new worksheet. So, press CTRL+N command to create a new
worksheet.
Step 3: Right-click here and choose Paste Special option from this list.

Step 4: A Paste Special panel will open where select Value radio button under Paste option and click OK.
Step 5: Now, you will see the copied data is pasted along column width and width of the column has not
destroyed as a simple paste.

Copy with column width

You have seen that when you copy the data and paste it simply, the width of the column is changed. We
have to increase and decrease the width of the column manually. It looks something like this -

We have a solution for it, i.e., Paste Special. You can copy and paste the data with column width using
it. Paste Special method enables several advance features to keep the formatting and formulas as it is. It
keeps the cell formatting same.
Let's see with an example to copy data with column width -
Example
Step 1: Copy the Product column data simply using the Ctrl+C shortcut command.
Step 2: Go to the cell or worksheet where you want to paste the data.

Step 3: Right-click here and choose Paste Special option from this list.

Step 4: A Paste Special panel will open where select column width radio button under Paste
option.
Leave other options as default and click OK.

Step 5: Now, you will see that it has been automatically adjusted the column width according to the width
of the copied data.
Step 6: Now, use the CTRL+V shorted key to paste the copied data here in the selected cells/column
(Column F).

You can note that the width of the column has not been destroyed as simple paste. Similarly, you can
keep the formatting with the help of Paste Special option.

Copy only value not formula

Sometimes, users only want to copy the value, not the formula applied to it. If we simply copy and paste
the values from one place to another, formula applied to those values will also come with it. It might be
possible that the cell reference changes, and you got an incorrect value (0 or anything else).
For example - total price = Price*Quantity, i.e., D= B*C. It means column D value comes with the
multiplication of price and quantity. See how formula come with it when we paste the data simply -
We have a solution for it, i.e., Paste Special. Use the Paste Special method to paste only value not the
formula, along with it.
Let's see for its solution using Paste Special method –

Example
Step 1: Copy the Product column data simply using the Ctrl+C shortcut command.

Step 2: Go to the cell or worksheet where you want to paste the data.

Step 3: Right-click here and choose Paste Special option from this list.
Step 4: A Paste Special panel will open where select Values radio button under Paste option. Leave other
options as default and click OK.

Step 5: Now, you will see that copied data is pasted to column F successfully.

Step 6: You can click any of the cells of column F (pasted data) and see that there is no formula is
applied on the pasted value now.

Now, these values are simple values with no formula.

Spell Check in Excel

MS Excel is one of the most powerful programs that help us record data within the rows and columns in
spreadsheets. It allows us to store vast amounts amount of data and perform different data-centric
operations on it. Since we typically enter the data with the help of a keyboard, there are chances that
we mistype some spellings. For this, Excel provides us a built-in tool to find and correct the wrong or
mistyped spellings with ease. The tool is usually known as the 'Spell Check' and 'Spell Checker'. However,
the Spell Check tool

Excel is not as powerful as Word Spell Check as it does not check grammatical errors or check spellings
as soon as we start typing. In excel, we must check and correct spellings manually.
In this article, we are discussing the detailed introduction of Spell Check in Excel. The article also discusses
the steps to help us understand how we can spell check within the Excel worksheet and increase the
overall productivity, making our work effective.

What is a Spell Check?

Spell check is a software tool that helps us find the wrong spelling of words and correct them. The tool is
typically helpful for various word processing programs, emails, data entries, cell phone messaging, blog
posting, and more. A good spell-check tool automatically finds the misspelled words within the entire
document and gives us relevant suggestions to correct them. Some tools even automatically correct the
misspelled words.
The spell-check tools can be built-in with the specific programs as well as come in as standalone
software. However, the most common thing is that they all usually underline the errors with red color.
Furthermore, a menu with correctly spelled suggestions is also displayed, enabling us to choose the
correct spelling or ignore it.

Where do we find Spell Check in Excel?

In Excel, the spell-check tool is located under the 'Review' tab in the ribbon. It is identified by
the text 'Spelling' associated with the tile that has 'ABC' written on it. It looks like the following
image:

Every time we click on the spell-check tool from under the Review tab, it launches the 'Spell Check
Dialogue Box', as shown below:

Note: Although we can perform a spell check in Excel in different ways, the easiest one is to use a
shortcut key. Excel allows to immediately perform spell check within the entire sheet by pressing the
function key 'F7' on the keyboard. However, it must be noted that we may first need to activate the
function keys by pressing the 'Fn' button on the keyboard.

How does Spell Check work?

We must understand the process of how the spell-check tool works in Excel. Suppose we select cell A1
and run the spell check within the entire sheet. The tool will analyze cell A1 and then the others cells in
the first row. After that, it will move onto the second row and check all the cells from left to right, then
the third row, and so on. If the tool finds any spelling error in any of the cells, it displays the Spell Check
dialogue box with various options. Apart from this, if we select the cell somewhere else within the sheet
and run the spell-check tool, the tool starts checking for errors from that particular point. For example, if
we select cell B2 and run spell check, it will go through all the cells in the second row from the B2 cell
and then the other rows. Every time we start from a cell somewhere else in the sheet, it asks us whether
we want to continue checking for spelling mistakes at the beginning of the sheet after checking the rest
of the cells after selection.

Understanding the Spell Check Dialogue Box Interface

When we run spell check in Excel, it displays a Spell Check dialogue window with some options. The spell
check dialogue window typically looks like this:

In the above image, we have the following options:

o Ignore Once: Suppose Excel finds and flags a particular word as an error, and the word is repeated
multiple times within the sheet. However, we want to keep it as it is for the selected cell and move
the cursor to the next cell of the corresponding word; we must click the 'Ignore Once' button. This
way, we can go through all the occurrences of the mistyped word and decide to change it or not
using the suggestions.
o Ignore All: If Excel finds and flags a particular word as an error and the word is repeated multiple
times within the sheet, but we want to keep all these occurrences the same (as they are), we must
click on the 'Ignore All' button.
o Add to Dictionary: When Excel displays some words as errors and flags them under incorrect words
even though they seem correct to us, we can use the 'Add to Dictionary' button to save that
particular word as a correct one. That way, Excel does not flag those words again and accepts them
 like other correct words. Generally, we use the 'Add to Dictionary' button for certain names or
abbreviations that we create and use on our own.
o Suggestions: Whenever Excel finds incorrect or wrong spellings, it lists some relevant words as
alternatives under the suggestions. We can pick any of desired words from the list.
o Change: When we pick up any suggestion from the list, we need to decide if we want to change it
only for the selected cell or within the entire sheet. If we want to use the suggestion only for the
selected or highlighted cell, we must click the 'Change' button.
o Change All: When we have occurrences of any wrong/ misspelled word within the sheet, and we
want to change all of them at once, we must click the 'Change All' button after selecting the correct
word from under the suggestions. This will apply the corresponding suggestion within the entire
worksheet.
o AutoCorrect: Excel AutoCorrect option helps correct the spellings automatically after we save a
specific word once. If we pick up the appropriate suggestion from the suggestions list and then click
the 'AutoCorrect' button, Excel will add the corresponding word in the AutoCorrect list. That means,
whenever we mistype the same word again, Excel will automatically correct it with the suggestion
we selected earlier.
o Dictionary Language: We can select the dictionary language from the drop-down list next to the
dictionary language.

Adjusting Spell Check Settings

Excel spell-check tool has some default configurations or options that help control how Excel connects
and formats our data. We can change these default settings by clicking on the 'Options' button in the
Spell Check Dialogue box. It will open the Options dialogue box to help us make the necessary changes
in spell check by navigating to the Proofing menu.

The following are some default presents for spell check in Excel:
o Excel spell check ignores all such words that are written in upper case. That means, if we have
misspelled the word in upper case, it will not be checked. For example, the word 'HELLOOO' will not
be flagged as a misspelled word.
o Excel spell check ignores checking words that include numbers. For example, if we type the word
'Helloo123', it will not be flagged as a misspelled word.
o Excel spell check ignores internet URLs and file addresses.
o Excel spell check does not ignore repeated words. For example- Hello Hello, how are you? Since the
word 'Hello' is repeated here, so it (the extra word) will be flagged as an error.

Steps to Spell Check Individual Cells and Ranges in Excel

Sometimes, we may have some names or acronyms within the cells that may not be needed to check for
spelling errors. However, we may need to spell check for the remaining individual cell or the multiple
cells in a range.

For Individual Cells

If we have doubts regarding spelling mistakes in a single cell, we can perform a spell check only for a
corresponding cell. Suppose we have the following data set where we have some doubts regarding the
spelling written in cell A2.

We need to follow the steps discussed below to spell check in any cell within the sheet:
o First, we need to select cell

o After that, we must navigate to the Review tab and select the Spelling

o Since we have the misspelled spelling in cell A2, Excel will display some relevant suggestions within
the Spell Check dialogue box.
 Besides, if Excel does not find spelling mistakes, it will not launch the dialogue box. Instead, it will
only inform that there are no spelling errors.
o We need to choose an appropriate word from the list of suggestions and then click on
the Change However, we can also use the other options from the dialogue box accordingly.

o After completing the previous step, Excel will update the respective word and display a pop-
up window with a message saying the 'Spell Check Complete'.

We will get the following result after we are done with the spell check process:

For Ranges
If we have doubts regarding spelling mistakes in multiple cells, we can perform a spell check for multiple
cells. Suppose we have the following data set where we have some doubts regarding the spellings in
columns C and D.
We need to follow the steps discussed below to spell check multiple cells in a range within the sheet:
o First, we need to select the desired cells. We can select two or more cells, rows, columns, custom
range, or the entire sheet. In our case, we select the entre columns C and D.

o After selecting the desired cells, we need to go to the Review tab and then select Spellings.

o After that, Excel will display all the misspelled words within the selected range. Furthermore, it will
launch a spell check dialogue box for each misspelled word with the relevant suggestions.
Since we have two wrong spellings, we get the following two windows:

We must select the right suggestion for each misspelled word and click on the Change button until
the Excel displays the 'Spell Check Complete' pop-up.
 We will get the following result after we are done with the spell check process:

Steps to Spell Check Selected Sheets in Excel

If we have data in multiple sheets within the workbook and we want to perform a spell check in some
individual sheets only, we need to follow the below steps:
o First, we need to navigate the sheet tab and select the sheets we want to perform spell check. To
select the sheets, we must press & hold the Ctrl button and click the desired sheets using the mouse
button.

o After the desired sheets are selected, we need to either go to the Review > Spellings or press the
shortcut key F7. This way, Excel will spell check in all the selected worksheets and display us
misspelled words with suggestions.
Once the spell check is completed, we need to right-click on the sheet tab and select the 'Ungroup
Sheets' option to ungroup the sheets.
o
Steps to Spell Check Entire Workbook in Excel
Excel also allows us to check spelling mistakes within the entire workbook/ spreadsheet at once. This
will cover all the sheets of the active workbook. For this, we need to follow the below steps:
o First, we need to navigate the current workbook's sheet tab and right-click using the mouse. Next,
we must click the option 'Select All Sheets'. This way, we select all the sheets of an active
workbook.

o After all the sheets are selected, we must either navigate to the Review > Spellings or press the
shortcut key F7 on the keyboard. By doing this, Excel will spell check in an entire workbook and
display us misspelled words with suggestions.
We can perform the desired action using the options given in the spell check dialogue box.

That is how we can use the spell-check tool in Excel.

Insert Symbols and Special Characters in Excel

Most of the time, while working with Excel, you would be using either numbers or text in a cell. Using a
computer keyboard, you can directly insert approximately 94 different characters, such as letters,
numbers, and other symbols like punctuation marks. However, there are many other symbols available.
You can insert symbols like tick marks, cross marks, arrows, and thousands of such symbols in excel.
Excel also allows you to insert special characters like a trademark, copyright, registered symbols, and
many more.

The most important thing to understand when inserting symbols, fractions, special characters, or
international characters is the font you will use. Not all fonts have the same characters in them. It is
important to use the right font to find the symbol or character you want. For example, the Elephant font
has no fraction characters in it, while Verdana does. In a typical font such as Arial, approx 200 different
symbols are available, such as the British Pound sign £, the Euro Currency symbol €, and the copyright
mark ©.

These symbols cannot directly enter with a single key on a keyboard. Excel 2010 makes it easy to enter
symbols into cells. These symbols are available in the Symbol dialog box.

All characters are assigned a standard code number. This is how characters are stored internally by the
computer. You should know the code number to use special symbols in the files.

How to Insert Symbols in Excel?

In Excel, to add a symbol into a cell by using the symbol dialog box, you need to follow the following
steps, such as:

Step 1: Place your cursor in the file where you want to insert the symbol.

Step 2: Go to the Insert tab and click on the Symbol button.

Step 3: Pick a symbol, or choose More Symbols.
ADVERTISEMENT
ADVERTISEMENT
Step 4: Scroll up or down to find the symbol you want to insert.
Different font sets often have different symbols in them, and the most commonly used symbols are in
the Segoe UI Symbol font set. Use the Font selector above the symbol list to pick the font you want to
browse through.

Step 5: When you find the symbol you want, click on it.
Step 6: Then click on the Insert button, and this symbol will insert into your file.
How to Insert symbol using ASCII or Unicode Character Codes
You can also use the character code of the symbol as a keyboard shortcut. Symbols and special
characters are either inserted using ASCII or Unicode codes. You can tell which one you look up the code
for the character

Step 1: Go to the Insert tab and click on the Symbol button.

Step 2: To display more symbols click on the More Symbols option.
Step 3: Scroll up or down the list to find the symbol you want, or you might have to change the font or
the subset to locate it.

Step 4: Towards the bottom right, you'll see boxes for Character code and a from.
 o The character code is what you'll enter to insert this symbol from the keyboard.
o And the fromtells you what kind of character it is. If from says "Unicode (hex)" it's a Unicode
character. Or if from says "Symbol (Decimal)", then it's an ASCII character.

Common Symbols and Codes

If you know the code number, you can enter that character any time by holding down the ALT key and
typing the 4-digit code number on the numeric keypad. Even though character code numbers are only
three digits (between 0 and 255), you must type in the leading zeros to ensure that you get the proper
character.
For example, to enter the British Pound sign, whose code is 163, hold down the ALT key and type 0163
on the numeric keypad. Here are some common symbols and their codes, such as:
Symbols Codes

Euro € 0128

Ellipsis … 0133

Dagger † 0134

Double Dagger ‡ 0135

Bullet • 0149

Trademark ™ 0153

Cents ¢ 0162

Yen ¥ 0165

Pounds £ 0163

Copyright © 0169

Registered ® 0174

Plus/Minus ± 0177

Paragraph ¶ 0182

Degree ° 0176
Create a List of Symbols using Formulas
If you don't know the code number of symbols, then you can quickly create a simple list of symbols on
an Excel worksheet by entering the following formula in cell A1 and then copying the formula down
through row 255:

This will display in each cell the character whose code number is that cell's row number. For example,
the Euro Currency symbol will appear in cell A128, which indicates that its code number is 128.
You can change the font of the column to see what characters are supplied by various fonts. Fonts with
lots of useful symbols include Symbol, WebDings, and the 3 WingDings fonts, Ref
Specialty, ENCICON, Marlett, MonoType Sorts, and MS Reference.

Using Symbols in Formulas

If you need to use a special symbol in a worksheet formula, you can use the function to return the
character from its code number. For example, suppose you want to add a copyright notice to your
worksheet. You could use the following formula:

=CHAR(169) & " Copyright, 2000."

The CHAR(169) function returns the actual copyright symbol to appear properly in the cell. The "inverse"
of the CHAR function is the CODE function. While CHAR takes a number and returns the actual
character, the CODE function takes a character and returns the code number. For example, the following
formula returns 65, the character code of a capital letter "A".

=CODE("A")

The VBA programming equivalents of the CHAR and CODE functions are Chr() and Asc(). For example, to
display a message box with a copyright message, use the following code:

MsgBox Chr(169) & " Copyright, 2000"

How to Insert Fractions Values in Excel?

Some fractions (1/4, 1/2, and 3/4) automatically switch to a fraction character (¼, ½, ¾) when you type
them. But other fractions do not (1/3, 2/3, 1/5, etc.), so you'll need to use the insert symbol process if
you want to insert those as symbols.
Step 1: Click or tap where you want to insert the fraction.
Step 2: Go to the Insert tab and click on the More Symbols button.
Step 3: In the Subset dropdown, choose Number Forms.
Step 4: Double-click the fraction that you want to insert.
Step 5: And then click on the Close button.
Only a handful of common fractions have a symbol that you can substitute. For more unusual fractions,
such as 8/9, you can approximate the symbol by setting the numerator as superscript and the
denominator as a subscript.

NOTE: Not all fonts have a number forms subset. If you don't see the number forms subset available for
your font, you will have to use a different font, such as Calibri, to insert the fraction character.

How to Insert Special Characters in Excel?

You can easily insert special characters in your spreadsheet by using the special characters Excel
feature. The font we use is vital when entering symbols, special characters, international characters, or
fractions. Now follow the following steps to insert special characters in Excel spreadsheets:
Step 1: Place the cursor at the exact location where you want to insert a special character.
Step 2: Go to the Insert tab and click on the Symbol button.
Step 3: Now, click on the Special Characters button.

Step 4: Click on the special character that you want to insert.

Step 5: And click on the Insert button to insert that button in the sheet.
NOTE: Many of the special characters have shortcut keys associated with them. For example,
ALT+CTRL+C inserts the Copyright symbol (©) and many more. If you want to insert that special
character quickly, press the shortcut keys.
International Characters in Excel
If you want to type in other languages or consider switching your keyboard layout to that language.
For one-off characters, there are almost always keyboard shortcuts in Office to do that. For example:

Shortcuts Keys To insert Characters

CTRL+` (ACCENT GRAVE), the letter à, è, ì, ò, ù,

À, È, Ì, Ò, Ù

CTRL+SHIFT+^ (CARET), the letter â, ê, î, ô, û

Â, Ê, Î, Ô, Û

CTRL+SHIFT+: (COLON), the letter ä, ë, ï, ö, ü, ÿ,

Ä, Ë, Ï, Ö, Ü, Ÿ

CTRL+SHIFT+~ (TILDE), the letter ã, ñ, õ

Ã, Ñ, Õ

CTRL+' (APOSTROPHE), the letter á, é, í, ó, ú, ý

Á, É, Í, Ó, Ú, Ý

CTRL+SHIFT+&, a or A æ, Æ

CTRL+, (COMMA), c or C ç, Ç

CTRL+/, o or O ø, Ø

ALT+CTRL+SHIFT+! ¡

CTRL+SHIFT+&, s ß

ALT+CTRL+SHIFT+? ¿

CTRL+' (APOSTROPHE), d or D ð, Ð

CTRL+SHIFT+&, o or O œ, Œ

CTRL+SHIFT+@, a or A å, Å

How to do addition in Excel

One of the typical day-to-day operations performed in Excel is addition. Whether you have to find the
sum of the price list, expense sheet, and calculate the total sales or any other action. You can even
create your customized SUM formulas by combining the SUM function with other Excel functions.
Therefore, in this tutorial, we will cover the various ways to quickly calculate the sum of numbers in
excel.
1. Sum a column in Excel with one click

This is one of the fastest options that is quickly used to find the sum of numbers. To apply this method,
follow the below steps.
1. Click on the column alphabet for which you want to find out the sum.
2. At the Excel Status bar, you will notice the total of the cells.

3. Instead of selecting the entire column, you can select a few cells to calculate the sum. It will quickly
display the sum of the selected cells in the Excel status bar.

NOTE: Though this method is quick, the drawback of this method is that it neither allows copying nor
shows numeric digits.

2. Calculate the sum in Excel with AutoSum

The second method down in your list is AutoSum. This method allows the user to quickly sum up a
column and hold the output in the table. The advantage of using the AutoSum method is that it
automatically adds up the numbers and will present the result in the specified cell. To apply this
method, follow the below steps.

1. Click on the empty cell below the column where you want to sum up the numbers.

NOTE: To bypass any additional operations like range selection, always select the first empty cell below
the column you require to sum.

2. Go to the Home tab -> Editing group -> AutoSum option.

3. Excel will automatically pick the range and will put the same in the SUM function. Though if needed,
you can alter the range as well.

4. Click on the Enter button on your keyboard, and you will notice Excel will immediately give you
the sum of the numbers present in the selected range.

This method is fast and lets you automatically get and keep the summing result in your table.

3. Addition using the SUM function

One of the easy methods commonly used to find the total in Excel is the SUM function. The SUM
function is used to select specific cells or even a large range in an Excel worksheet and calculate their
total. This method is beneficial if you have a large excel column and want to calculate the sum without
highlighting the range. Though, you still need to enter the SUM function manually.
ADVERTISEMENT

NOTE: The SUM function calculates the sum of all the values selected in the range even if the cells are
hidden or filtered.

Using the SUM function in Excel, you can find a range of cells, an entire column/row, contiguous cells, or
even non-contiguous cells. Excel users often create customized advance functions by combining the
SUM function with other Excel functions.
To apply this method, follow the below steps.
1. Click on the empty cell below the column where you want to sum up the numbers.

ADVERTISEMENT
2. Start the formula with equal to (=) followed by SUM (). In the arguments select the range of the cells
by pointing your mouse to one cell and dragging the cursor to the end. If you want you can directly enter
the number of cell references separated by comma.
NOTE: You can also manually type the SUM range address. It is helpful to calculate large range data
easily. For example =SUM(D2:D3000).

3. Press the Enter button and Excel will immediately calculate the sum of numbers.

4. Find Sum of only visible cells

In the above section, we have covered that SUM functions add all the values, including the hidden and
filtered fields. Therefore, with this method, we learn how to calculate the sum only for the visible cells.
Therefore, omitting the filtered or hidden cells. To apply this method, follow the below steps.
1. Enter your data in Excel spreadsheet.

2. Now, we will filter the above dataset and fetch only the values that are greater than 40. Click on any
cell and go to Data-> Sort & Filter-> Filter icon.
3. As shown below the filter arrows will appear in the header. Click on the Marks filter arrow.

4. The following window will appear. Click on Number Filters-> Greater than.

5. It will display the Custom Filter window. In the textbox, type 40 and press ok.

6. It will show you the following filtered output.

7. To quickly find the sum of the filtered column, select the range and click on AutoSum under
the Home tab. You will have your filtered sum in the selected cell.

8. Another method to quickly find the sum is by selected the filtered cells and view the sum of the
cells on the Excel Status bar. Though with this method, you won't be able to paste the data into
your worksheet.

5. Convert data into Excel table and get the sum for your column
Creating tables and calculating the sum of the columns is also one of the common methods to find the
sum. Therefore if you have a large dataset and want to find the sum, convert your data to Excel Table. It
will be helpful to quickly total the sum of the columns and rows of your table. To apply this method,
follow the below steps.
1. Put your cursor on any cell and press the shortcut key' Ctrl + T' on your keyboard to create a table
out of your Excel data. You will have the given Create Table window and it will automatically select
the data for you. Click on OK.

2. Excel will create a table for you. Next, in the ribbon toolbar, you will see the Design tab. Navigate to
this tab and look for the Total Row. As shown below, tick the checkbox given in front of the Total
Row.

3. Excel will immediately add a new row (named with Total) at the end of your table. Though it already
calculates the sum, still make sure to click on the small arrow icon next to your number.

4. Select the SUM option from the appeared options. That's it you will get the sum of your data.

5. Using the method you can easily find the sum of every column. You can also try the other options as
well such as Average, Min and Max.

NOTE: This method also adds up only the visible (filtered) cells.

Using Sigma Button to do addition in Excel

Select the cell to display the result then in Home tab click on the Sigma button, on the right end of the
ribbon. You will see the image given below:
Now select the list of data you want to add. You will see the image shown below, and then press the
Enter key to get the result.

How to do subtraction in Excel

It is similar to addition just replace the plus "+" sign with minus sign "-".
Choose the cell where you want the result to appear as cell C6 in this example. Then type = sign in the
cell, now select first cell C3 then type "-" sign then select second cell C4. Now press the enter key, you
will get the subtraction of these two numbers.

How to calculate average in Excel

MS Excel, or Microsoft Excel, is the world's most popular spreadsheet software that enables users to
record massive amounts of data and perform various operations. It makes it easier to perform analytical
tasks and mathematical calculations on the recorded data in cells across worksheets. Finding an average
(also called arithmetic mean) is one commonly used mathematical task we often perform in Excel.
In this tutorial, we discuss various step-by-step methods on how to calculate averages in Excel. Before
discussing the different methods of calculating average in Excel, let us first briefly introduce the
definition of average and its usage in Excel.
What is Average in Excel?

In Excel, the average is categorized as one of the statistical functions that help us retrieve the arithmetic
mean value for a given series of many numeric figures. In particular, the average value is calculated by
adding all the given numer8ic values and then dividing the sum results by the total number count of values
we added in the sum calculation. Although it seems easy to calculate the average for a small group of
numbers, it becomes difficult and prone to errors when dealing with larger data sets. This is where some
programs like Excel come into play.

We can easily calculate the average for massively given numeric values and minimize human errors using
an Excel program. There can be many cases when we might need to calculate the average. For instance,
we can calculate the average sales of any specific item for the last 12 months in a business.

How to calculate the average in Excel

Since different averages are based on the given data, Excel offers various methods to calculate the
average. Some of the commonly used methods include the following:

Calculating Average in Excel: General Method

Since the average for numbers can be calculated by adding all numbers and dividing their results, we can
use the SUM and COUNT functions in Excel to apply this concept.
For example, suppose we have given the following numbers: 15, 10, 30, 20, 25. To calculate the average,
we first add all numbers like this: 15+10+30+20+25. Next, we divide the sum by 5 as the total Count of
numbers is 5.
Thus, the average, in a mathematical way, is calculated like this:

15+10+30+20+25/ 5
The result for the above calculation is 20, so the average.
If we want to calculate this in Excel, we can use the formula with SUM and COUNT functions like this:

= SUM(15,10,30,20,25)/COUNT(15,10,30,20,25)

Additionally, if the five numbers are recorded in a range A1:A5, we calculate the average and
record the result in cell A6 using the below formula:

= SUM(A1:A5)/COUNT(A1:A5)
The above image in Excel also shows the same average result as we obtained above mathematically.

Calculating Average in Excel: AVERAGE function

The AVERAGE function is an existing Excel statistical function that is most widely used to calculate the
averages (arithmetic mean) in an Excel sheet. The function is mainly helpful when there is no specific
limitation when calculating averages. The AVERAGE function can accept up to 255 individual arguments,
including numbers, cell references, ranges, arrays, or constants.

Syntax

The syntax of the AVERAGE function in Excel is defined as below:

=AVERAGE(number1, [number2], ...)

Where number1 is the required argument and number2, number3, and more are additional/ optional
arguments for which we need to calculate the average. Although only one argument is mandatory, we are
most likely to use at least two arguments to calculate the average.

Steps to use the AVERAGE function

Let us now understand the application of the AVERAGE function with an example. Suppose we have given
an Excel sheet with some customers' names in column A (A2:A8) and their respective ages in column B
(B2:B8). We need to calculate the average age of all the customers in cell B9.

We can perform the below steps to calculate the average age in our example Excel sheet:

o First, we need to select the resultant cell to record the average result. In our example, we select
cell B9.
o Next, we need to apply the AVERAGE formula and supply the necessary arguments to it. For this,
we first enter the equal (=) sign and then type the function name (i.e., =AVERAGE).
o After typing the function name, we must enter the opening parenthesis and pass the
individual cell references or a range in the formula. Since we have adjacent cells, we can
select or enter the range B2:B8 directly in our formula.

o Once the arguments have been given in the formula, we must enter the closing
parenthesis and press the ENTER key on the keyboard. This will immediately calculate
the average for the given data set. The below image shows the average age of the
customers in our example:
 Although we used the adjacent cells (or a range) in this example to calculate the average, we
can also use the non-contiguous cells as the arguments in the formula. We need to select each
desired non-contiguous cell by holding the Ctrl key on the keyboard. After that, we must
separate them using the Commas.
Suppose we want to calculate the average age for three customers, Anoop, Deepak, and Lallan.
We can use the AVERAGE function in the following way:
=AVERAGE(B2,B5,B6)

We can use the AVERAGE function with mixed arguments, empty cells, texts, logical values, and
zeros. However, the function ignores the empty cells and values like text and logical values while
calculating the average. But, the cells containing zeros are included.

Calculating Average in Excel: AVERAGEA function

The AVERAGEA function in Excel also helps us calculate the average or arithmetic mean of the desired
arguments within the worksheet. The function has been available in all the versions of Excel since Excel
2007. The AVERAGEA function is mainly used to eliminate the discrepancy of the AVERAGE function and
can include or accept all data types (or values) within a distribution.

Unlike the AVERAGE function, the AVERAGEA function evaluates the text values as zeros. Furthermore,
the logical values are also treated differently by the AVERAGEA function. It evaluates the logical value
TRUE as 1, while the logical value FALSE is evaluated as 1.

Syntax

The syntax of the AVERAGEA function in Excel is defined as below:

=AVERAGEA(value1, [value2], ...)

Like the AVERAGE function, the first argument in the AVERAGEA function is mandatory, while other
subsequent values are optional. Also, the AVERAGEA function can accept a maximum of up to 255
arguments, which can be numbers, cell references, ranges, arrays, or constants. Although the syntax and
application of both functions, AVERAGE, and AVERAGEA, seem similar, they are different as the
AVERAGEA function can calculate the average of given cells with any data like the numbers, text values,
and Booleans. In contrast, the AVERAGE function only calculates the given data set's average with
numbers.

Steps to use the AVERAGEA function

The application of the AVERAGEA function is precisely the same as the AVERAGE function. Let us now take
an example of the following Excel sheet where we have some students' names (range A2:A8) and their
marks (range B2:B8). We need to calculate the average of marks using the AVERAGEA function in cell B9.

We can apply the AVERAGEA formula by performing the below steps:

o First, we must select the resultant cell B9 to record the average result.
o Next, we must enter the AVERAGEA function followed by the equal sign. We must start
the parenthesis, enter/ select the effective range B2:B8, and close the parenthesis.

o After clicking the ENTER key, we get the average marks of the students in cell B9, as
shown below:
 We see a difference between both results if we now calculate the average marks using the
AVERAGE function in cell B10. It occurs as the division Count number is different for both
functions.

For the AVERAGEA function, the Count is 7 as the text field is also evaluated as a valid value. But,
the division count for the AVERAGE function is 6 as it ignores the text data. So, the difference in
results by the functions AVERAGE and AVERAGEA occurs in Excel.
However, by changing the text value (absent) in cell B7 to zero (0), we get the same result from
both functions because both include zero values when calculating the average.

Calculating Average in Excel: AVERAGEIF function

The AVERAGEIF function is another existing function that enables users to calculate the average for the
given data set. Unlike the AVERAGE and AVERAGEA functions, the AVERAGEIF function also enables
users to apply any specific criteria, meaning the average will be calculated for numbers following the
applied criteria. With the AVERAGEIF function, Excel first looks in the given data set for a stated
criteria/condition. It then calculates the arithmetic mean of the effective resultant cells that follow or
satisfy those particular criteria.

Syntax
The syntax of the AVERAGEIF function in Excel is defined as below:

=AVERAGEAIF(range, criteria, [average_range])

Where the range, criteria, and average range are the arguments of the AVERAGEIF functions.
o Range: It is a mandatory argument used to represent the area/ location on the sheet that
contains cells with data required to be tested against the given criteria or condition.
o Criteria: It is another mandatory argument used to specify a rule/ condition to include only the
specific cells or data while computing the averages. The criteria we need to specify here may include
numbers, text values, logical expressions, cell references, or a range.
o Average_range: It is an optional argument used to specify the range of cells where we want to
calculate the average. If average_range is not specified, the function uses the range by default.

Steps to use the AVERAGEIF function

Let us take an example of the following Excel sheet where we have the names of some fruits (A2:A7)
with their prices (B2:B7). Some fruits are also repeated with different prices.

Suppose we only want to calculate the average price for any specific fruit (i.e., Mango) in cell B8. We can
use the AVERAGEIF function and calculate the average price for Mango in the following way:

o First, we need to select cell B8 to record the average price for the Mango.
o Next, we enter the AVERAGEIF formula in the following way:
=AVERAGEIF(A2:B7, "Mango", B2:B7)
Where A2:B7 represents the range, Mango represents the desired criteria, and B2:B7 represents the
average_range.

Although the average_range is optional, we had to specify this to include only the cells with
numbers (prices). In our example, if we don't specify the average_range, the function will take the
entire range A2:B7 where there are also text values. This will result in #DIV! error.
o

o After entering the formula, we must press the Enter key on the keyboard to retrieve the formula
result.

Calculating Average in Excel: AVERAGEIFS function

The working concept of the AVERAGEIFS function in Excel is similar to the AVERAGEIF function, although
the AVERAGEIFS function allows multiple criteria to be tested while calculating the average. However,
the AVERAGEIF function only allows one condition at a time. Unlike the AVERAGEIF function, the
AVERAGEIFS function also includes the cells containing TRUE and FALSE, which is somehow required in
some cases to test multiple criteria.

Syntax

The syntax of the AVERAGEIFS function in Excel is defined as below:

=AVERAGEAIFS(average_range, criteria1_range1, criteria1, [criteria2_range2, criteria2], ...)

Where the average_range, criteria1_range2, and criteria1 are the mandatory arguments of the
AVERAGEIFS functions. All other arguments are optional and used according to the given data set.
Additionally, criteria1 is the rule/condition to be used on the specified range1, while criteria2 is the
condition for the range2 and more.
Steps to use the AVERAGEIFS function

Let us take the same example datasheet that we used for the AVERAGEIF function. In that sheet, we have
the names of some fruits (A2:A7) with their prices (B2:B7). Some fruits are also repeated with different
prices.

In the previous example, we calculated the average price for the Mango based only on one criteria/
condition. Now, we need to calculate the average price for the Mango that has prices of more than 20.
So, we have to use the two criteria:

First: For the range A2:A7, the only condition (criteria) is "Mango", and three rows satisfy this condition

Second: For the range B2:B7, the condition (criteria) is ">20", and only two out of three rows with the
Mango satisfy this condition So, the average prices will be calculated using the following mathematical
operation:

(22+25)/2 = 23.5

However, we need to use the OR logical operator to test multiple conditions in Excel. It returns either
TRUE or FALSE based on the condition of satisfaction. For instance, if we want to test if the cell A1 contains
"Mango" or "Apple", we can use this:

=OR(A1="Mango", A1="Apple")
In our case, we check the condition only for the "Mango" in the entire range A2:A7 in the following way:
We only apply the OR condition =OR(A2="Mango") in cell C2 and copy it to the remaining cells below.

As displayed in the above image, the OR query result is added in the new column C
as TRUE and FALSE. Next, we must use this result and calculate the average by matching only the TRUE
values for the range B2:B7.

Since we need to apply multiple criteria while calculating the average, we use the AVERAGEIFS function
using the following steps:

o Like applying the other Excel functions, we first need to select the resultant cell to record a result.
So, we select cell B8.
o Next, we enter the equal sign, type the function name AVERAGEIFS and enclose the required
arguments within the parenthesis signs in the following way:
=AVERAGEIFS(B2:B7,C2:C7,"TRUE",B2:B7,">20")

Here, we only considered the fruit name "Mango" by following the TRUE results and only those that
have prices above 20.
o Lastly, we press the ENTER key on the keyboard, and the average price is returned immediately,
i.e., 23.5. It is the same as our mathematically calculated average result.

Special Cases
There are many scenarios we may face while computing average results in Excel. However, the following
are the two most common scenarios, and we should be cautious while computing the average in such
cases.
Calculating Average in Excel Including Zeros

To calculate the average with zero values, we can use the AVERAGE function as usual and pass the cells
with zero values in them. When calculating the average using the Excel AVERAGE function, the formula
ignores the text or logical values and the empty cells. However, zero values are included as they are
considered valid by the formula. So, the total number count increases, and the overall average of the
distribution decreases.

For example, the following sheet has average marks of two different students where student A is absent
in one subject while student B has zero (0) marks in one subject.

Although marks in total subjects are the same, the average results vary for both students. For student A,
the sum of marks is divided by 4. However, for student B, the sum of marks is divided by 5 because a zero
value is included here. Therefore, we must explicitly exclude zeros if we don't want to include zero values
in our average results.

Calculating Average in Excel Excluding Zeros

We can use the AVERAGEIF function with a little trick to calculate the average, excluding the zero values.
Since the AVERAGEIF function enables us to set certain criteria, we can use the below criteria to calculate
the average without including the zeros:

=AVERAGEIF(cell or a range,">0")

The above formula will ignore the cells with zero values and the text and logical values.
If we now apply the AVERAGEIF formula in our example data, we get the same average marks for students
A and B.

Excel has ignored the zero value cell this time, and so the Count number has decreased from 5 to 4 for
student B.

We can also ignore zeros using the AVERAGE function. It is useful when we have a small data set. However,
we must carefully use the cell reference or the direct numbers in the formula to eliminate errors. In the
same example, we can calculate the average for the student B using the AVERAGE function without
involving zeros in the following way:
=AVERAGE(B3,D3,E3,F3)

However, it is not usually convenient as we cannot copy-paste the corresponding formula for
other resultant cells in this way.

Important Points to Remember

o When using the AVERAGE function to calculate the average in Excel, we must be very cautious. If the
supplied arguments are error values or texts that cannot be translated into numbers, the function will
lead to unexpected results or errors.
o We can only specify one condition when calculating the average using the AVERAGEIF function.
o The AVERAGEIF function in Excel ignores the empty cells in its argument average_range. If the given
range is blank or contains only the text values, the AVERAGEIF function returns the #DIV! error.
o If any cell in the given criteria within the AVERAGEIF function is empty, the function evaluates it as a
zero (0) value cell.
o Instead of typing the function name in a resultant cell, we can select the desired function from the
Excel Function List. For the average based functions, we can go to Formulas tab > More Functions >
Statistical Functions > AVERAGE/ AVERAGEA/ AVERAGEIF/ AVERAGEIFS. This eliminates the human
errors of misspelled function names.
o Using Sigma Button to calculate average in Excel
o Select the cell to display the result. In the Home tab click on the small arrow next to the Sigma button
and left click on the Average option. You will see the image shown below. Then press the Enter key to
get the result.

o
Excel Fill Handle

The Fill handle is an Excel feature that is used to fill the data automatically with a specific pattern. You
can extend a series of numbers, dates and text combinations to the desired number of cells.

Fill handle is a versatile tool in the excel suite that autocompletes various entries, transforms the data,
and several other applications. It is an inbuilt excel feature that is enabled by default and therefore does
not involve any manual procedure to implement it.

To use the fill handle, move your mouse pointer to the right bottom corner of the cell; it will change into
a tiny + (cross) sign. Drag the cross sign, and if Excel recognizes the pattern, it will autofill the cells with
the updated data. Else it will copy the same data in all the dragged cells.

For example: Let's enter 2 in cell B2 and 4 in cell B3. Next, we will implement a fill handle by selecting
both the cells and dragging the cross (+) sign to the following cells. You will notice that Excel will
automatically update the cells by incrementing 2 in each value.

How to Use Fill Handles in Excel?

Moving on, let's understand the steps to use the fill handle feature to insert the first 25 numbers quickly
in your Excel spreadsheet.
o Enter the first two entries of your data. Unlike we have entered 1 and 2 in the cell C1 and C2.

o Select both the cells and hover the mouse over the bottom-right edge of the rectangle
window. You will notice the cursor will immediately change to a plus (+) icon.
o

o Click on the mouse and drag the cross (+) icon down to the below cells until it reaches the 25th
value. As you can see in the below example, excel will automatically identify the pattern that will fill
the first 25 entries for you (increment by 1).
o If you want to fill data in the adjacent row, fill the first entry and hover the pointer over the right
edge of the selection window. It will change to a plus icon (+); double click on it. The fill handle will
automatically fill all the values to the last cell (based on the entries in the adjacent column). As you
see in the below example we have copied the values of Column C to Column D.

Examples of Using Fill Handle in Excel

In the above examples, we covered the steps to use the fill handle feature in excel to autofill the list of
the first 25 numbers. However, it is a basic operation that we have performed. There occurs many
situations where the fill handle is an advantage as it automatically recognizes the pattern and fills the cells
correctly. Let's understand the Fill handle feature more briefly using the day-to-day operations.

1. Autofill Weekday Names

Entering the list of days in excel is one of the common operations performed by every user. You can
quickly fill the week names using the Fill Handle feature. Follow the below-given steps:Open your excel
spreadsheet and enter the full week name, i.e., Monday. You can also enter the three
alphabetnomenclature, i.e., Mon.
1. Select the cell and bring your mouse cursor over the bottom-right edge of the rectangle window.
You will note the cursor will change to plus (+) sign.

2. Autocomplete the cells with the weekday names by dragging the cursor down.

Autofill Dates

Dates are also commonly used in our day-to-day excel life. Therefore using the Fill handle, you can easily
autofill dates in your spreadsheet.

Note: You can use the Fill Handle to autofill any date format that Excel recognizes.

Enter the Date in the first cell and drag the cross (+) sign to the following cells. The date values will be
auto-filled in the dragged cells. You will notice Excel will automatically increment the day field by 1.

Similarly, you can autofill Months, years, days, etc.

Autofill Options
After using the Fill Handle and dragging the cross (+) icon to autofill the cells, you will notice the Auto Fill
Options icon at the bottom right corner of the selection window (refer to the below image).
Click on the Fill Handle icon. You will have several options. These options are different for different data.
For example you will have the following option if you autofill a list of numbers using Fill Handle.

1. Copy Cell: Selecting this option enables the copy-paste of selected data to the dragged cells.
2. Fill Series: It is the default option. It auto-fills the cells as soon as it identifies any pattern.
3. Fill Formatting Only: Selecting this option only replicates the formatting of the selected cells to the
dragged cells. It does not fill the values.
4. Fill Without Formatting: Selecting this option auto-fills the cells by identifying the pattern, but it does
not copy the cell's formatting.

Similarly when you are working with dates you will have the following fill handle options:

1. Copy Cell: Selecting this option enables the copy-paste of selected dates to the dragged cells.
2. Fill Series: It is the default option. It auto-fills the dates based on their pattern.
3. Fill Formatting Only: Selecting this option only replicates the formatting of the selected cells to the
dragged cells. It does not fill the date values.
4. Fill Without Formatting: Selecting this option auto-fills the cells by identifying the pattern, but it does
not copy the cell's formatting.
5. Fill Days: This option auto-fills the cells with days.
6. Fill Weekdays: This option auto-fills the cells with weekdays only and eliminates the weekends.
7. Fill Months: Selecting this option fills the cells with incrementing months. Therefore the day number
will be the same for all the cells, whereas only the month numbers change.
8. Fill Years: Selecting this option fills the cells with incrementing years. Therefore the day number and
month number will be the same for all the cells, whereas only the year numbers change.
What if your Excel fill handle feature is not working?
Many times while working with Excel spreadsheets the Fill handle feature does not work. Don't worry if
it happens as Excel provides an option to enable it manually. Follow the below steps to enable the fill
handle feature manually in Excel:
1. Click on File -> Options.

2. The Excel Options dialog box will be prompted. From the left pane of the window, select the
"Advanced" option.
3. Look for the Editing Options section, and make sure to tick the checkbox that states "Enable fill handle
and cell drag-and-drop".
4. Click OK.
5. Excel Fill Handle with Text
6. Fill handle works in a similar way with texts. It helps repeat the text to desired range of cells. See the
images shown below:

7.
8. The text is selected and then copied to other cells using fill handle.

9.
Excel Fill Handle with Text and Numbers
Fill handle also works with cells containing both text and number. In this case, the text does not change
but the number increases by one for every cell you select. See the images:
Excel Fill Handle with Dates
Fill handle can be used to fill dates in multiple cells. If you need to enter a complete month in worksheet,
you don't need to type each date separately. Just choose a cell, type the date and using fill handle carry it
over other cells, Excel will enter the subsequent dates in the selected cells. See the images:
Excel Creating Formulas

MS Excel, short for Microsoft Excel, is a widely used powerful spreadsheet program that provides access
to different worksheets in a single workbook. Users can store large amounts of data in these worksheets
using existing cells. One of the significant advantages of using Excel is that it allows us to perform various
calculations, operations, and analyses. Excel has a wide range of pre-defined functions, and we can
perform almost every operation in Excel by creating formulas through existing functions.

In this tutorial, we discuss the various step-by-step methods of creating formulas in Excel properly. The
steps for creating or using a formula differ for each Excel function as each function requires different
arguments. However, the basic concept remains the same. Before we discuss the methods of creating a
formula, let us briefly understand the definition of an Excel formula.

What is the Formula in Excel?

An Excel formula is nothing but an introductory statement consisting of one or more 'operands' and
'operators'. The formulas in Excel help us specify the relationship between the values recorded within the
cells in the worksheet, perform mathematical calculations on the recorded values, and retrieve the
desired results in a resultant cell.

One essential point to note while using the formulas in Excel is that we must always start them with an
equal sign (=). If we don't start the formulas with an equal sign, they are not treated as formulas but only
as a text string.

The Difference between a Formula and Function

An Excel formula is a statement or an equation structured manually by a user to perform any calculation.
At the same time, the Excel function is the pre-defined calculation in the spreadsheet program. We can
use more than one function in a formula.

Example of Formula: =A1+A2+A3

Example of Function: =SUM(A1:A3)

How to create a formula in Excel?

There are several methods we can use to create formulas in Excel. We discuss the most common methods
below, and each method has its advantages. All of the following methods for creating formulas work in all
versions of Excel.

Creating Formulas using Constants and Operators

When creating formulas in Excel, constants are numbers, dates, or text values involved within the formula.
In addition, the operators are any sign, symbol, or character that refers to an action or operation to be
performed. The most common arithmetic operations include addition, subtraction, multiplication,
division, exponentiation, and modulus operations. To create a basic Excel formula in an Excel cell using
the constants and operators, we must perform the steps below:
o First, we need to select a cell to record the output or result.
o Next, we must type an equal symbol (=) and enter the formula equation to perform the calculation.
o Lastly, we must press the Enter key on the keyboard to obtain the results.

Creating an Excel formula using constants and operators is different from writing an equation in
maths. The fundamental difference is that we start the formula with an equal sign in Excel,
while in mathematics, we usually insert an equal sign at the end.

For example, if we want to add two numbers (3 and 5), an Excel formula will look like =3+5.
However, in maths, we use 3 + 5 =. When we insert an Excel formula in an Excel cell and press
the Enter key, the result of the formula appears like the following image:

Creating Formulas using Cell References

Instead of typing the numbers directly into the Formula equation, we can also refer to the corresponding
cells that contain the required numbers (or values) in the sheet. It is more convenient and easier to use
cell references in formulas when working with large data sets. Furthermore, one advantage of using a cell
reference is that the formula results are automatically updated in real time whenever we change the value
in the corresponding cell/range.

To create a formula using the cell references, we can perform the below steps:

o First, we need to select a cell to record the output.

o Next, we enter the equal sign (=). After that, we need to type the first cell that contains the
number. It is not mandatory to always type the cell reference. Instead, we can also click on the
specific cell, and Excel will automatically fetch the cell reference of that cell in the applied formula.
This eliminates human error.
o In the next step, we must type the operator sign we want to apply for the formula. For example,
if we want to sum up the first and second cell values, we must use the addition sign (+) between
them.
o After entering the operator sign, we need to type the cell reference of another number that we
want to include in our formula.
o Lastly, we must press the Enter key to obtain the formula result.
Suppose our example sheet contains two numbers, 3 and 5, in the cells A1 and B1. We need to calculate
the sum of these values in cell C1 using the formula containing cell references. So, we have to apply the
following formula in a cell C1:

=A1+B1

After we press the Enter key, the result will appear in a cell C1, as shown below:

If the values are recorded in more than two cells, we can use the cell reference of each cell similarly. For
example, if we have values in cells A1, B1, and C1, we can add their corresponding values using the formula
=A1+B1+C1. However, if several cells are required in a formula, it is better to try using existing functions
and ranges of cells.

Note: By default, Excel uses relative references and changes them accordingly when copied from one cell
to another. If we need to prevent them from automatically changing by Excel, we must use absolute
references. To switch between references, we must press the F4 function key on the keyboard.

Creating Formulas using the Pre-defined Functions

When there are multiple cells to use in the formula, and if they are contiguous, it is even better to use the
entire range than to use each cell reference separately. Using the range in Excel helps us save time and
get the desired results more accurately. Additionally, we can also use the cell references in formulas when
combining the equation through functions. No matter the function name, the formula still begins with an
equal sign followed by the function name and the required arguments enclosed within the parenthesis.
However, each function has a different syntax structure and requires specific arguments. We must know
the syntax, necessary arguments, and the order of the function we want to use.

When creating a formula by combining the existing Excel functions, we usually have two common ways.
We can either use the function wizard to select the desired function (s) in our formula or directly type/
write the entire formula with the functions into a cell or formula bar. Let us discuss both the ways of
creating formulas in Excel:

Using the Function Wizard

Using the Function Wizard to create a formula in Excel is the most trusted and effective way. If we find
creating formulas complex or are beginners in Excel, the Function Wizard comes real handy. To create a
formula in Excel using the Function Wizard, we must go through the following fundamental steps:
o First, we need to select a resultant cell by clicking on it and running the Excel function wizard.
We need to navigate the Formulas tab and click on the 'Insert Function' button under the
'Function Library' section, which will launch the 'Insert Function' window.
 Alternatively, we can also use the 'fx' button from the left side of the formula bar to insert the
desired function in a cell.

Another quick way to insert a function in Excel involves using the function drop-down list. After
selecting the cell, we must type an equal sign (=) and choose the function from the drop-down
list present to the left of the formula bar.

o In the Insert Function window, we typically get the three options using which we have to find
and select the desired function. We have to type the function name in the first option box and
click on the Go button. Additionally, we can type the desired function's description or operation
and click 'Go' to find relevant function names. However, it is not such a convenient option if we
don't know the appropriate name of the function.
 When we are not sure of the function's name, we must manually select the category to which
our function may belong. The list has about 14-16 different categories, and all the existing Excel
functions are divided into them accordingly.

o After selecting the category of the desired function, we will see a list of various functions under
the section 'Select a function'. We need to find the desired function in the list, and we can select
the function in the list to get more information about it. Excel will display the information or
description with the syntax of that particular function we selected.

o Once the function name is found, we must click the OK button. This will launch the 'Function
Arguments' window, where we need to pass the desired or required arguments/ parameters for
the function. When entering the arguments for any function, Excel shows all the required
information in the window. We can type the cell (s) or a range manually in the respective boxes
or use the selection box icon from the end of the box to select the cells in the sheet using mouse
clicks.
 In the above image, we can see that the result also appears at the bottom of the window
after we pass/ select the desired arguments for the function.
o Lastly, we must click the OK button or press the Enter key on the keyboard to complete the
formula and retrieve the corresponding formula result in a selected resultant cell.

Typing/ Writing a formula into a Cell or formula bar

It seems a lengthy process, although it is easy to create Excel formulas using the Function Wizard. If we
have knowledge about the Excel function we want to use, a faster way would be to type it directly into
the cell or formula bar and pass the corresponding arguments according to the formula syntax. As usual,
we first have to select a cell to record an output value. Next, we must enter the equal sign (=) followed by
the desired function name. When we start typing the function name into an Excel cell, a list with the
relevant function names appears in front of us. We can also select the desired function from the list by
pressing the TAB key on that particular function. When we write the function name manually, we must
manually type the starting parenthesis sign. However, a starting parenthesis is automatically inserted
when selecting a function from the list by pressing the TAB key.
As soon as we write/ select the function name, Excel automatically shows the syntax for that function,
highlighting the arguments required for the function. We can type the cell reference or a range manually
or click on the specific cells/range to include them in our formula. For example, the following image shows
the formula to calculate the average price for the recorded values in cells from B2 to B5.

In the above image, we used each cell reference separately. However, we can also use the range
B2:B5 instead of repeatedly typing/selecting each cell. We can easily cover large data sets in our
Excel formula by supplying an entire range. Lastly, we must type the closing parenthesis and press
the Enter key to get the final results.

When we have data in different worksheets that we want to include in the formula, we must also specify
the sheet name with the range of cells when writing the cell references manually. For example, suppose
we have numbers in a range B2:B5 in two different sheets, Sheet1 and Sheet2. In that case, we can use
the following formula and calculate the average:

=AVERAGE('Sheet1'!B2:B5,'Sheet2'!B2:B5)

In contrast, if we select the cell reference by clicking on the corresponding cell, the sheet name is
automatically inserted in the formula by Excel.

Note: It is recommended to use the Function wizard instead of directly writing the function into a cell. By
selecting a function from the function wizard, we can reduce the chances of Excel errors.

Creating Formulas using Defined Names

Instead of using the cell references/ ranges in the formula directly, we can also specify a desired name for
the specific cells/ range and use it later in our formula. However, it will not be easy for others to
understand the formula. For example, suppose we name the two cells A2 and B2 as Revenue and
Expenses, respectively. We can use the below formula in another cell to record the 'Net Income' by
subtracting the expenses from the revenue:

=Revenue-Expenses

Here, we defined the names of cells. However, we can also define a name for the entire range, usually
called Named Range.

To manage (define, view, edit, or delete) any name for the cell(s) or a range, we must go to Formulas >
Name Manager. However, the fastest way to define a name in Excel is to select the specific cell (s)/ range
and type the desired name directly in the Name Box, where we usually see the cell reference.

Important Points to Remember

o In Excel, we can copy formulas from one cell to another cell in the same worksheet, another sheet,
and even in another workbook. However, we must be careful while copying the formula as the
cell references may change unexpectedly.
o If we delete any row/column or cell containing the formula's value, the formula result may change
to #REF! error. In that case, the function treats the deleted cell/row/column as an invalid
reference and gives an error.

Excel Fill Handle in Formulas

In the image given below, we want to multiply the numbers in column B with 5, so we can type
the formula such as =B3*5 for cell B3 and other cells of the column B, but this is not possible with long
lists.
In such cases, we use Fill handle to perform the calculations. So, type the formula =B3*5 in cell C3 then
left click on the right bottom corner of cell C3, fill handle will appear. Pull the fill handle downward till cell
C10 then leave the mouse. The whole list of numbers in column B will be multiplied by 5.
The pull handle also works horizontally. See the image given below. The value in cell B10 is multiplied with
5 in cell B12 and as you move the pull handle horizontally towards cell C12, the value in cell C10 also gets
multiplied by 5.

Excel Relative Referencing

Microsoft Excel, abbreviated as Excel, is a widely used spreadsheet software that helps users record vast
amounts of data types in cells across multiple worksheets in a workbook. It enables the users to perform
various simple to complex calculations on the recorded data within the cells and get the results concluded
using the inbuilt functions or formulas. In particular, most Excel formulas can be used with the desired
values as direct arguments. However, we often supply corresponding cell references in Excel formulas
that hold effective values needed to be used in calculations or financial analysis. The main advantage of
using cell references instead of direct values in a formula is that we get real-time results or outputs by
applied formulas when we change the values in the corresponding cells accordingly. There are mainly
three types of cell references in Excel: relative, absolute, and mixed references.
In this tutorial, we discuss a brief introduction to Excel Relative Referencing. The tutorial also discusses
step-by-step procedures for creating or using relative references within the Excel worksheets with the
help of relevant examples. Before we look at Relative References in Excel, let us first understand Cell
Reference in Excel with its definition.

What is a Cell Reference?

In Excel, a cell reference is defined as the name or address of a specific cell or range. It accordingly remains
unique for each respective Excel cell. An Excel cell reference is formed by combining a particular column
name and the corresponding row number. For example, A1 is the cell reference to the first cell in the
sheet where the letter 'A' represents the first column and the numeric '1' represents the first row of the
sheet.

The primary purpose of a cell reference in Excel is to tell an Excel formula where to look for the desired
value/data to be used in the formula to produce the corresponding output. In Excel, we can use cell
references to refer to the same sheet, another sheet, another workbook, and many other similar
programs.

What is the Relative Reference in Excel?

Relative reference is the default approach used by Excel. It makes the Excel cells reference-free, giving the
fill function freedom to continue its order without any restriction. In particular, a relative reference in
Excel refers to a pointer to any specific cell or range of multiple cells within the same sheet, another sheet,
or workbook. For example, the relative reference for the cell A1 can be given as below:
=A1

As shown above, the relative reference in Excel is nothing but only the combination of a respective
column name and row number. If we copy-paste the relative references in different cells, their
references automatically change depending on the relative position of rows and columns. Relative
references typically change when copied to other locations because they describe the "offset" to other
cells accordingly, rather than fixed addresses.

For example, if you copy the formula (=A1*B1) from the first row to the second row of the sheet, the
formula will become (=A2*B2). The relative references are mainly useful if there is a need for using the
same formula or calculation in multiple cells within the workbook (s).
When do the relative references in Excel change?

As discussed above, the relative references are the default reference type used by Excel. When we use
relative references, they automatically change after copying them to another location in the sheet or
workbook. Each referred cell with relative reference changes with left, right, top or bottom movement.
For example, suppose we give relative reference to cell D9 and perform the movement in the following
ways within the sheet:

o Leftward: The given reference (D9) automatically changes to C9.

o Rightward: The given reference (D9) automatically changes to E9.
o Upward (Top): The given reference (D9) automatically changes to D8.
o Downward (Bottom): The given reference (D9) automatically changes to D10.

How to create/ make relative references in Excel?

To create or make a relative reference, we should include the equal sign before the cell reference to refer
to any specific cell in the sheet. Depending on the requirement for our formula, we can either refer to a
cell or a range. Below are the steps explaining how to create a basic relative reference in Excel:

o First, we need to select a cell where we want to use relative reference for any specific cell. For
example purpose, we select the cell D3 in our example sheet, as shown below:

o In the next step, we must start entering the equal sign (=) and then select or type the
desired point of reference (cell or range). We type the equal sign "=" in cell D3 and select cell B3
as a reference point in our example.

o After pressing the Enter key, we see that the same value is displayed in cell D3 as cell
B3. Moreover, if we change the value in cell B3, the value in cell D3 will change
accordingly. That's because we have created a relative reference of cell B3 in cell D3.
How to use relative references in Excel?

Let us now understand the concept of Excel relative referencing better with the help of the below
examples:

Relative Referencing in Excel: Example 1

Consider the following sheet as an example data set where numbers in four different cells, A1, A2, B1, and
B2, are recorded.

Suppose we need to SUM values in A1 and A2 in the below cell A3. Likewise, we also need to
SUM values in B1 and B2 in cell B3.

o First, we calculate the sum of two numbers from A1 and A2 in cell A3. We select cell A3,
type the enter sign (=), and then select cell A1. After that, we enter the addition operator,
select cell A2, and press the Enter key on the keyboard. So, the formula (=A1+A2) gives the
output SUM as 90.

o Next, we calculate the sum of two numbers or numerics from B1 and B2 in cell B3. Since we have a
similar scenario in the next column B, we can again use the addition operator to SUM values and
obtain results.
However, we can get the result in another way which will be comparatively easier and more useful
when there are more similar scenarios in the next columns.
o Instead of entering the entire formula again, we can usually copy-paste (or drag using the Fill
Handle) the formula from cell A3 into cell B3. When we copy-paste the contents from cell A3 to cell
B3, only the applied formula is copied from one cell to another (not the
result).
 The above image shows that the formula has automatically adopted the respective cells
accordingly. After being copied, the applied formula (=A1+A2) has automatically changed
into formula (=B1+B2). The cell references automatically changed based on the relative
position of the row and column. However, we get the appropriate results. That's because
the destination cell B3 only contains the formula, not the source value. This is how Excel's
relative referencing works.

Relative Referencing in Excel: Example 2

Consider the following Excel sheet as an example data set where we list different items with their prices
and sold quantities.

We need to calculate the Sale Price/Value for each item separately in column D. To do this, we need to
multiply the sold quantities by their respective price.

o First, we calculate the Sale Price for the first item (i.e., Mouse). So, we select the corresponding
resultant cell D2 and insert the multiplication formula. We first type equal sign (=), then we
select a cell with Price (B2), insert the multiplication operator (*), select the cell with quantity
sold (C2), and press the Enter key. This gives the Sale Price for the first item.
 o Next, we need to calculate the Sale Price for other items. We can apply the multiplication
formula to other resultant cells similarly. However, it will take a lot of time. Therefore, we
leverage the Relative Reference feature of Excel and usually copy-paste (or drag using Fill
Handle) the formula from D2 to other remaining cells. This immediately calculates the Sale Price
for other items in the column.

Suppose we check the formula in any other specific resultant cell in column D. We notice that
the cell references have changed relatively (based on relative positions of row and column)
while keeping the same formula as the source cell.

Important Points to Remember

o The relative reference does not contain a dollar ($) sign.
o Neither a row nor a column is fixed in Excel relative referencing. If a row or column is fixed, the
reference will be referred to as a mixed reference. If both row and column are fixed, it becomes an
absolute reference.
o We can change between different cell references by pressing the 'F4' function key after placing the
cursor between the specific cell in the formula bar.

Excel Absolute Referencing

Microsoft Excel, also known as Excel, is an extremely powerful spreadsheet program developed by the
Microsoft Company. Using Excel, users access multiple worksheets to record large amounts of data within
one or more cells. Additionally, Excel has a different range of built-in functions to help us apply various
operations or calculations on recorded data to achieve financial analysis or desired output. Although
most formulas can work with numerical values directly assigned to them, we often use cell
references to access desired values that we want to supply in specific formulas. Using cell references is
useful because the formula automatically updates the corresponding output in the resulting cells
accordingly if we change the values recorded. There are mainly three types of cell references,
namely relative, absolute and mixed.
In this tutorial, we discuss a brief introduction to Excel Absolute Referencing. We also discuss the step-by-
step procedures to create or use an absolute reference in our Excel worksheet with the help of relevant
examples. Before we look at Absolute Reference in Excel, let us first introduce Excel Cell Reference.

What is a Cell Reference?

An Excel cell reference refers to the address or name of the corresponding cell or a range of multiple cells.
It usually consists of the corresponding column name followed by the specific row number. A cell
reference tells Excel formulas where to look for the data/value used in the formulas. In Excel, we can refer
to cells in the same sheet, another sheet, another workbook, and other supported programs.

What is an Absolute Reference in Excel?

By default, Excel uses relative references, which means that the cell reference changes based on the
relative position of the row and column when it is copied to another cell or range. However, there may
be cases when we may need to fix the addresses of some cells to get specific outputs. After fixing the
cell references, we can ensure that they do not change even after copying them to another location.
This is where Excel Absolute Referencing comes into play.

By definition, "Excel Absolute Reference refers to a' locked' reference so that the address of its
corresponding row and column does not change when copied".

An absolute reference in Excel represents an actual fixed location or address on the worksheet. So,
when we want to fix the position of the desired cell in an Excel formula so that its position remains
constant while copying it to another location, we make the corresponding cell in the formula absolute.

When do we need to use Absolute Cell References in Excel?

When working in Excel, we come across a wide range of use-cases where we have to use absolute
references to get the desired results, regardless of the field or industry we are working on. Some of the
common cases when we commonly use absolute references for efficient calculation and reporting are as
follows:
o When calculating the sales taxes based on the certain fixed rate for invoices of different items
o When using a fixed price per unit
o When implementing a single percentage for individual years to calculate annual profit targets in
projects
o When changing prices or units for multiple cells values at certain fixed measurements
o When referring to fixed-availability rates for each resource while managing projects
o When using the relative references for a column and absolute reference for a row to match the
column calculation in our referenced cells to use its value in other desired data tables or a range

How to create or make an Absolute Cell Reference?

We should include the dollar sign ($) before the column name and row number for desired cell
reference to make it absolute. Depending upon the use-cases, we may need either to fix an individual
cell or an entire range. Let us discuss both the cases:

Creating/ Making a cell Absolute

When we need to make a single cell an absolute cell, we must insert a dollar sign before the column
name and row number. This will change the relative reference into an absolute reference.

For example, an absolute reference to cell A1 will be as below:

=$A$1

If we copy the cell to the other cell below, the absolute reference remains the same and gives the same
output.
Creating/ Making a range Absolute

When we need to make an entire range absolute, we must add a dollar sign to each cell reference in the
range. For example, if we have a range A1:B7 to be used in the form of an absolute reference. We need
to include the dollar sign in the following way:

=$A$1:$B$7

That way, we fix the column letters and row numbers for the entire range.

Note: Instead of manually adding the dollar sign ($), we can also press the F4 function key once after
placing the cursor in the specific cell reference in the formula bar. This will immediately add a dollar sign
to a particular cell reference, making it absolute. Pressing the F4 function key multiple times will toggle
between other types of cell references.

How to use Absolute Referencing in Excel?

We need to perform the below steps to use the absolute referencing in Excel properly:
o First, we must select a resultant cell to insert a formula and type the equal sign (=) to start the formula.
o Next, we need to start typing the function name and select it from the list by pressing the TAB key on
the keyboard. After that, we must supply the respective arguments as per syntax.
o In the next step, we need to click between the cell in the formula bar that we want to make an
absolute. Also, we must press the F4 function key once that converts the relative cell reference into
an absolute reference. We can do this for all the desired cells or a range in the formula bar.
o After making the desired cells/ range absolute, we must press the Enter key on the keyboard. Now,
we can copy the formula to any other location on the sheet without automatically converting it by
Excel.

Let us now understand the concept of absolute reference better with the help of the following examples:
Absolute Referencing in Excel: Example 1
Consider the following sheet as an example data set where we have prices of different items in USD ($).
We need to convert these prices from USD to INR in a column (C) next to it. Suppose 1 USD equals
approximately 75 INR, recorded in cell B2 in the sheet. In that case, we need to multiply the recorded
prices of different items with cell B2. So, we need to fix cell B2 in the multiplication formula while
keeping all other cells relative or default.

The steps to convert or change the recorded prices from USD to INR by using the multiplication formula
are listed below:
o We first need to select cell C5 and type an equal sign (=) to start the formula. Next, we select the
respective cell B5, type the multiplication operator (*), and select cell B2. After that, we press the
Enter key to get the corresponding result for the first item.

In the above image, we can see the price of the first item in INR. The result is correct for the first
item. However, if we copy the formula into other cells below cell C5 to get the INR prices for the
other items, we get the incorrect prices. When we copy the formula from C5 to C6, the cell
references automatically change for given cells B5 and B2 (as shown in the following image). But, we
have to fix cell B2 to get the appropriate results, so we make it absolute first.
o

Before copying the formula into other cells, we again select the cell where we have initially
inserted the formula, i.e., cell C5. Then, we go to the formula bar and click or place the
cursor in between cell B2.
o In the next step, we press the F4 function key once. This immediately changes the reference from B2
to $B$2 in the cell C5, and the entire formula changes from (=B5*B2) to (=B5*$B$2), as shown
below:

o Once the cell reference has been converted into the absolute reference, we must press the Enter
key. Lastly, we can drag or copy-paste the formula into other cells below C5, and the prices will be
converted from USD to INR correctly.
Absolute Referencing in Excel: Example 2
Consider the following sheet as an example data set where we list some grocery items with their prices
and purchased quantities.

Our example sheet shows that we have the fixed GST (Goods and Service Tax Rate) rate of 5% for each
item. For calculating the Net Prices (Total Price including the GST), we first need to calculate the GST
price for each item and then include this in the respective total prices.

The steps to calculate the total prices, including the GST for each item, are listed below:
o First, we need to calculate the total price for each item without the GST in column E. So, we select
cell E4 to record the total for the first item, excluding the GST. We do this by multiplying the number
of quantities purchased with the respective price, as shown below:

After applying the multiplication formula, we press the Enter key and drag the formula to other
column cells to get the respective total prices for each item.
 Since there is no fixed value in the multiplication formula, we don't need to use the absolute
reference.
o In the next step, we need to calculate the GST price for each value. Since the GST rate is fixed (cell
G1), we must use the absolute reference here. So, we calculate the GST for the first item by
multiplying the total price with the GST rate. Also, we make the cell with GST rate an absolute
reference in the following way:

After pressing the Enter key on the keyboard, we get the GST price for the first item. Since we have
the absolute reference for the desired fixed cell ($G$1), we can copy or drag the formula to other
cells below in column F to get the GST prices for all items. Thus, we keep the fixed GST rate constant
in cell G1, while the relative reference for column E changes accordingly.

o After calculating the GST prices for items, we can calculate their respective total price, including the
GST. For this, we must add the total price with the GST price in the following way:

Since both the cells in the formula are relative (changing values cells), we can drag or copy-paste the
formula into other cells to get the total prices for the other items, including the GST.
An alternate to Excel Absolute Referencing: Named Range

In Excel, a named range is an alternative to an absolute reference. We can name any desired cell or a
range accordingly and use it in our formulas. This way, we can make our formulas easier to understand.
Depending on our requirements, we can create multiple named ranges in a single sheet. For this, we can
use the Name Manager and create, edit or delete the desired named range accordingly.

For example, suppose we have salary data of some employees, and we want to include the additional
bonus of 1500 INR with each respective salary. The data is structured in an Excel sheet in the following
way:

We need to fix cell C1 in the formulas in the above sheet. So, we name this cell as a BONUS using the
Name Manager (Formulas > Name Manager).

After that, we select the resultant cell C4 and insert the formula (Salary + Bonus) to calculate the total
salary for the first employee.
After pressing the Enter key, we get the first employee's desired result (total salary).

Now, we can drag or copy-paste the formula in other cells below C4 to get the total salary of other
employees. Since we have named the cell C1 as BONUS, it does not change automatically in other resultant
cells (C5, C6, C7, and C8).

Similarly, we can also assign a name for the desired range and use it in the corresponding formulas. This
concept works similarly to the Excel Absolute Referencing.

Important Points to Remember

o We fix both the respective column and the row when using the absolute reference. If only a row or
column is fixed by placing a dollar sign before it, the respective cell reference will be called the 'mixed
reference'.
o Excel absolute reference does not automatically change irrespective of the movement of the
respective formula in upward, downward, leftward or rightward direction.
o When pressing the 'F4' shortcut key to change the cell reference, we may first need to activate the
function keys by pressing the 'Fn' function key for specific keyboards.
Instruction while typing data in excel
Type the values and text in separate cells else the formula will not work. See the example given below:

In case you want to add currency symbol to values then use mini toolbar as shown in the image given
below.

Select the range of cells then right click. Mini toolbar appears above the shortcut menu. Click on the
currency symbol. It will be added to the given values. To find more currency symbols click on the arrow
next to the currency symbol. See the images given below.
Quick Excel Functions

Microsoft Excel, commonly called Excel, is powerful spreadsheet software that allows users to record large
amounts of data sets and perform various arithmetic calculations. It is a versatile spreadsheet program
for solving math problems, analyzing or comparing data, or making graphical representations of data.
However, all such things in Excel are done with the help of existing functions and formulas created by us.

This tutorial discusses the list of the most used essential quick excel functions, including the relevant
examples. With this tutorial, we'll learn when to use the various functions and perform basic tasks quickly
without any calculators or extra work.

What is an Excel function?

An Excel function refers to a predefined formula that helps us perform a specific operation on the
recorded data within the worksheet. The functions contain familiar names and eliminate the need to enter
complex formulas manually. In Excel, the formulas may contain one or more functions in the equation.
Since the functions are typically another form of formulas in Excel, they also start with an equal sign (=).

Basic Excel Functions that we can use quickly in Excel

Excel has over 400 predefined functions, and this number steadily increases from version to version with
every upgrade. We usually don't need to memorize them all. Instead, we can access the desired function
using the function wizard. However, it is beneficial to know some quick Excel functions that we often need
to use. Below we discuss some basic but valuable quick Excel functions that we often need to use as a
beginner:

SUM
The SUM function in Excel helps us add two or more values directly in an Excel cell. Additionally, we can
also pass the cell references, ranges, or the mix of arguments. The SUM function is the most basic built-in
Excel function and requires at least one argument. The syntax of the SUM function is defined as below:

=SUM(number1, [number2], ...)

Where the number1 is the mandatory argument and may contain a number, cell reference, range, or a
mix of all three. Additionally, we can add more than one argument as per our requirements.

Example: Suppose we have a list of month-wise sales data for any item, and we need to calculate the total
sales for the entire year. We can use the SUM function and quickly add sales for each month. If our sales
data is recorded in cells from B2 to B13, we can use the SUM function in the following way to record the
yearly sales in any resultant cell:

=SUM(B2:B13)

When the above equation is entered in cell B14, we get the addition of values in the corresponding cells,
as shown below:

Excel also provides another quick way to record the addition of column values or rows of numbers.
When we have values in an adjacent (contiguous) range, we can go to the very next cell after the data
and click on the AutoSum (Shortcut: press 'Alt' and '=' keys simultaneously) button from the Home tab.
This will immediately insert the SUM function and calculate the SUM of all the previous cells with
numbers.

AVERAGE

The AVERAGE function in Excel is another basic function that is often used by beginners, especially when
learning the concepts of Excel functions and applying them within the worksheet. As the name suggests,
the function helps us calculate the average value or arithmetic mean for the given numbers.
 Like other essential functions, the AVERAGE function also eliminates the need to create a formula to
calculate the average manually. Instead, we only need to pass the cells or a range, and results will be
calculated accordingly in the resultant cell. The function performs all the calculations behind the scenes.
It first adds all the given numbers and then divides the results by the total count of given numbers.

The syntax of the AVERAGE function is defined as below:

1. =AVERAGE(number1, [number2], ...)

The syntax is almost similar to the SUM function, and it is mandatory to pass at least one argument in the function.
We can pass arguments as numbers, cell references, range, or a mix of all three. We can pass more than two
arguments or numbers in the AVERAGE function as per our requirements.

Example: The application of the AVERAGE function is precisely similar to the SUM function. If we take the same
example data of month-wise sales again, the average in cell B13 can be calculated in the following way:

=AVERAGE(B2:B13)

For the quick implementation of the AVERAGE function, we can select a cell next to our
contiguous data set and go to Home > AutoSum drop-down (under Editing section) > Average.

COUNT

The COUNT function in Excel is another essential function used to count the total number of cells in a
given range that contains numeric values. Using the COUNT function, we quickly know about the number/
count of numeric values (numbers) entries. The COUNT function usually ignores data sets other than
numbers. The syntax of the COUNT function is defined as below:

1. =COUNT(value1, [value2], ...)

The first argument is mandatory, while we can add more arguments if required.
 Example: In the previous example sheet, we have month-wise sales data. So, the total number of cells
that contain numbers is 12 because we have the month from January to December. Let us now check the
results using the COUNT function by giving the range in the function as follows:

=COUNT(B2:B13)

In the above image, we can see that the result is 12, the same as expected. Suppose we enter any text
value (i.e., "No Sales") in any of the cells of the range B2:B13. So, the results change to 11 because the
COUNT function ignores the cells with text values.

We can apply the COUNTA function to measure all the cells in a range irrespective of the numbers or text
values. For the quick implementation of the AVERAGE function, we can select a cell next to our contiguous
data set and go to Home > AutoSum drop-down (under Editing section) > Count Numbers. However, it
only fetches the range of the adjacent cells with numbers. So, we must double-check the range within the
function.
MAX & MIN
MAX and MIN are the two different basic functions in Excel and are quick to use within the desired cell.
Both functions have almost the opposite operations. The MAX function helps us get the maximum value
in a given set of data, cells, or ranges. In contrast, the MIN function helps us get the minimum value in a
specific range. However, the syntax and the application process of MAX and MIN functions are similar.
The syntax of the MAX function is defined as below:
1. =MAX(number1, [number2], ...)
 The first argument is mandatory, while we can add more arguments if required. Example: When we apply
the MAX function in our month-wise sales data, we get the highest number of sales. We can use the MAX
function for the range B2:B13 in the following way:

=MAX(B2:B13)

This returns 1800, the highest/ maximum number in our range.

The syntax of the MIN function is defined as below:

1. =MIN(number1, [number2], ...)

Like the MAX function, the first argument in the MIN function is mandatory, while we can add more
arguments as per our requirements.

Example: On the same example sheet, we can use the MIN function for the range B2:B13 in the following
way:

=MIN(B2:B13)

This returns 921, the highest/ maximum number in our range.

RANDBETWEEN
 The RANDBETWEEN function in Excel lets us pick or record any random number from the given set of
numbers, and it only returns a random integer number. The function is categorized under the Math and
Trigonometry functions section. We need to specify a range of numbers by passing a minimum and
maximum number in a formula.

The syntax of the RANDBETWEEN function is defined as below:

1. =RANDBETWEEN(bottom, top)

Here, we must specify the lowest number as the bottom argument and the highest number as the top
argument in the formula.

Example: Suppose we have a list of 10 people, and we want to pick any random person amongst them for
any specific purpose (for example- choosing a winner randomly). We can use the RANDBETWEEN function
and pass respective arguments in the following way:

=RANDBETWEEN(1, 10)

This will return a random number between 1 and 10. Each time we apply it to the cell, the value returned
can be the same or different.

TRIM
When creating formulas in an Excel cell, we often encounter unexpected error codes instead of
the expected results. The formula returns errors due to several reasons. One of the most common causes
of formula errors may be the presence of unnecessary spaces in a cell. Therefore, we may need to remove
such unwanted spaces in our worksheets. However, deleting the extra spaces within the large data set
might be somewhat tricky if we try to check and delete them manually. This is where the TRIM function
is helpful.

Although there are multiple ways to delete extra unwanted spaces from our worksheet, the TRIM function
makes it more accessible. The function eliminates all extra spaces in given cells but retains a single space
character between two or more words. The syntax of the TRIM function in Excel is defined as below:
1. =TRIM(text)

We must pass the desired cell reference where we want to remove extra spaces.

Example: Suppose we have a list of data in column A where we see extra spaces in values. We apply the
TRIM function in the following way:

=TRIM(A2)

After applying the formula in the first resultant cell B2, we copy the formula in the other cells below.

The above image shows how the TRIM function has removed the extra spaces from the data in column A
and recorded the resultant values in the following column B.

LEN

The LEN function in Excel helps us know the total number of characters present in the given cell. It is
essential to note that the function counts all the characters, whether numbers, text or symbols. Also, the
space characters are included in the count results of the LEN function. The syntax of the LEN function is
defined as below:

1. =LEN(text)

We must pass the desired cell reference where we want to count the total characters.

Example: In the previous example data, we apply the LEN function in the following way:

=LEN(A2)

The formula is copied to other cells below in column B, where cell references change accordingly.
 In the above image, the function calculates the total characters in values recorded in column A
and records them in the subsequent column B.

CONCATENATE

The CONCATENATE function in Excel helps us quickly combine values from two or more cells into a new
cell. The syntax of the CONCATENATE function is defined as below:

1. =CONCATENATE(text1, [text2], ?)

Where the 'text1' is the mandatory argument; however, we can pass optional arguments if required.

Example: Suppose we want to combine values from cells A1 and B1. We can apply the CONCATENATE
function in the following way:

=CONCATENATE(A1,B1)

If we want to separate the combined values using the space, we can pass the space character as an
argument in the CONCATENATE function in the following way:

=CONCATENATE(A1," ",B1)

TODAY & NOW

In Excel, the TODAY and NOW are two different functions where the TODAY function helps us return the
current date while the NOW function returns the current time and time both. Both functions are helpful
if we don't remember the date or time but need to use them within the Excel cells. The beauty of these
two functions is that we don't even need to pass any arguments in them. We can use the syntax of TODAY
and NOW functions in the following ways, respectively:

=TODAY( )

=NOW( )

Example: The following image shows how the TODAY and NOW functions are applied to record the
current date and time in cells:

Quick Functions accessible from the Status Bar

Some of the functions discussed above can also be added to the status bar located at the bottom of the
Excel window. Adding such functions to the status bar helps us see those functions' results without even
applying them in our excel sheet. Instead, as soon as we select cells or ranges in an Excel sheet, the
results of the added functions automatically appear on the status bar. The following image shows the
AVERAGE, COUNT, MINIMUM, MAXIMUM, and SUM for the selected range of cells.

We can choose between the available functions to add to our status bar. We have to press the right-click
button on the status bar and select the function to add. A tick mark/symbol appears when we select a
function to add to our status bar. It looks like this:
Important Points to Remember
o When using Excel functions, we must ensure not to enclose numbers within double quotes. If we
use double quotation marks around the numbers, Excel will treat them as text values, which can
lead to unexpected formula results.
o It is recommended never to format the numbers used in the Excel functions.
o We must ensure that each opening parenthesis has the ending parenthesis, especially when there
is more than one set of parenthesis.
o Although we can copy-paste functions from one cell to another, we must ensure that the cell
references are correct. It is better to use absolute references when there is a need to copy the
functions/ formulas.

Excel IF Function
MS Excel, or Microsoft Excel, is a powerful spreadsheet program that enables users to record large
amounts of data in cells within multiple worksheets. Additionally, it allows users to perform various
mathematical calculations and analytical operations on recorded data using a wide range of existing
functions and formulas. The IF function is one such built-in widely used, most popular Excel function. In
this tutorial, we discuss the brief introduction of the Excel IF Function. The tutorial also discusses the step-
by-step process of using the Excel IF function, including the relevant examples.

What is the IF Function in Excel?

The Excel IF function performs a logical comparison between two values (or cells containing values). The
function evaluates if the supplied condition satisfies and then returns an output value depending on
 whether the result of the condition is TRUE or FALSE. In particular, the IF function is an inbuilt conditional
function that returns a value based on the fulfillment or non-fulfillment of the supplied condition. For
example, we can use the IF function to compare two values, whether the value in cell A1 is greater than
that in cell B1. If the conditions satisfy, it results as the TRUE; otherwise, FALSE.

The working of the Excel IF function is almost similar to the appropriately structured Flow Chart.
The function is mainly useful when making logical interpretations for decision-making. We can
also extend the logical test functionality of the Excel IF function by combining it with other logical
functions, such as AND, OR, etc.

Syntax of IF Function
The syntax of the Excel IF function is defined as below:
1. =IF(logical_test, value_if_true, value_if_false)

Where, the 'logical_test', 'value_if_true', and 'value_if_false' are the three parts or arguments in the IF
function. Based on the above syntax, the general format of the Excel IF function is defined as below:

=IF(A1>B2, "TRUE", "FALSE")

We separated the different arguments (or parts) in the IF formula by a Comma (,). However, we can also
use the Semicolon (;) based on the language settings of the machine/ device.

Arguments of IF Function
The IF Formula in Excel accepts the following three arguments:
o Logical_test: It is the first and the mandatory argument. It helps us specify the condition to be
tested or evaluated as either TRUE or FALSE. The specified condition can be a value or a logical
expression.
o Value_if_true: It is an optional argument accepted by the IF function. It helps us specify the value
that we need to return as output when the given condition (logical_test) is 'TRUE'.
o Value_if_false: It is another optional argument accepted by the IF function. It helps us specify the
value that we need to return as output when the given condition (logical_test) is 'FALSE'.

General Flow Chart Structure of Excel IF Function

As discussed above, the IF function works on the concept of a flow chart. Depending on the condition and
usage of several logical functions, the flow chart of the IF function may appear to range from simple to
complex accordingly. We can draw a flow chart of generic IF function like below:
The above flow chart shows that there is only one condition or criteria, while the two outcomes are based
on condition satisfaction. If the condition is satisfied (evaluated as TRUE), the function returns the value
from the left box. If the condition is not satisfied (evaluated as false), the function returns the output from
the other side.

Logical Operators used in IF function

The Excel IF formula typically uses logical operators to compare the values based on the given condition.
When evaluating a test using the IF function, we can use any of the below logical operators:

Operator Description Example

= Equal to A1=B1

< Less than A1<b1< td=""></b1<>

<= Less than or equal to A1<=B1

> Greater than A1>B1

>= Greater than or equal to A1>=B1

<> Not equal to A1<>B1

How to use the IF Function in Excel?

To use the IF function in our Excel sheet, we must perform the following steps:
o First, we must enter or type the equal sign (=) to start the function name.
o Next, we must type the function name, followed by the starting bracket, i.e., =IF(.
o In the next step, we need to give the function the first argument (logical_test). We must
specify the condition using operators' signs for logical comparison.
o After that, we need to give optional arguments, returning a value as an output based on
the TRUE or FALSE.

Excel IF Function Examples

Let us understand the concept and working of the Excel IF Function better with the help of the following
examples:

Example 1: Basic IF Function application for empty/ non-empty cell

Everyone knows that life is not possible without oxygen. Suppose we have the following excel sheet as an
example data set where column A contains the list of some planets and column B contains data about the
availability of oxygen for these planets.
We only need to use the Excel IF function to find the planets where life is possible. We can use the oxygen
availability criteria in the IF function to get the desired result. Let us now put the IF formula in our resulting
column C (cells C2 to C5) and find the names of the planets with the potential for life among the planets
listed in our example sheet:

o First, we find the possibility of life on the first planet (i.e., Jupiter). So, we select the respective
resultant cell C2 and start the formula by entering an equal sign. After that, we enter the IF function
name and the starting bracket, as shown below:

o Next, we apply the condition for the first planet. Since we are checking for oxygen availability as the
life factor, we apply the condition (B2="YES") as B2 has data for oxygen availability. It will look like
this:

o Lastly, we give values to be returned based on the satisfaction and dissatisfaction of the condition. If
the condition matches, we want the IF function to return: 'Life is possible here'. If the condition does
not match, we want the IF function to return: 'Life is not possible here'. We use the Comma (,) to
separate the arguments. So, the entire formula looks like this:
 After that, we type the closing bracket and press the Enter key to get the corresponding result. This
provides the result that life is not possible on Jupiter.

o After finding the result for the first planet, we can apply the IF formula in the remaining
resultant cells accordingly. However, it will be easier to copy-paste the formula from C2 to
other cells below. Also, we can drag the formula using the Fill Handle. This will help us
perform the task quickly.

IF function based on equal to

The IF function based on equal to condition checks whether the given number is equal to the specified
value.
Example: Check whether the specified data is equal to some value.
The steps to be followed are:
Step 1: Enter the data in the worksheet, namely A1:C7
Step 2: The items and categories of fruits and vegetables are given in the data. In cell C2, enter the
Formula as =IF (B2="Vegetables," "Nutrients," "Vitamins"). Suppose the data in cell range B2:B7 equal
the Vegetable category. In that case, the result is displayed as Nutrients, or if the data present in the cell
belongs to the Fruits Category, the result is displayed as "Vitamins."

Step 3: The Fill Handle option fills the Formula for the remaining cells, which displays the required result.

IF function based on Greater than

The IF function based on the greater than condition checks whether the given number exceeds
the specified value.
Example: Check whether the specified data is greater than some value.
The steps to be followed are:
Step 1: Enter the data in the worksheet, namely A1:C7

Step 2: To check whether the cost of each product is higher than 1000, enter the Formula in the cell D2 as
=IF (C2>1000, "Yes," "No")
Step 3: The Fill Handle option fills the Formula for the remaining cells, which displays the required result.

IF function based on Lesser than

The IF function based on the lesser than condition checks whether the given number is lesser than the
specified value.
Example: Check whether the specified data is lesser than some value.
The steps to be followed are:
Step 1: Enter the data in the worksheet, namely A1:C7

Step 2: To check whether the cost of each product is lesser than 1000, enter the Formula in the cell D2 as
=IF (C2<1000, "Yes," "No")

The Formula returns the result as "True" or "False" based on the conditions.

IF Function using NOT function(<>)

The IF function and the function on(<>) are used to check various criteria in the selected cell.
The steps to be followed are:
1. Enter the data in the cell range A1:A5
2. To check whether the cell range is not blank, enter the Formula in cell B1 as =IF(A1<>",1,0). Press
Enter.
The Function returns the value one if the cell is not blank and returns the value zero if the 3. cell is blank.
4. Use the fill handle to display the result for the remaining cells.
The IFS Function

The IFS function is an inbuilt function in Excel and is used to check two or more conditions for the given
data.
The syntax for the IFS Function is as follows:
=IFS (logical_test1, value_if_true1, [logical_test 2, value_if_true2], [logical_test3 ;...)
The IFS function has three logical conditions:
If the given number is more significant than (>), another number
If the given number is equal to (=), another number
If the given number is lesser than (<), another number
Example: Check multiple conditions for the given data using IFS Function
The steps to be followed are:
Step 1: Enter the data in the worksheet range A1:D7

Step 2: The car driving speeds of various people are entered in the data. Multiple conditions are entered
into the Formula to find the Normal, Fast, and Slow car speeds. Enter the formula in the cell D2 as =IFS
(C2<50,"Slow", C2<100,"Normal", C2>100,"Fast")

Step 3: The Fill Handle option fills the Formula for the remaining cells, which displays the required result.
Notes:
o While creating the Formula for the Nested IF function, the order in which the function is nested
with each other is essential.
o Nesting multiple functions in one Formula is challenging to maintain and sometimes hard to
create.
o An alternative for the Nested if Function is the VLOOLUP formula, INDEX Formula, MATCH, etc.

IF ERROR

The IF ERROR is one of the standard functions in Excel, where it handles Errors in the Formula. If the
Formula contains an error, it returns an expression or a value of the expression.

The syntax of the IFERROR is:

=IFERROR (value, value_if_error)

Value- The Formula or value which needs to be evaluated for errors

Value_if_error- This value is returned if the Formula returns an error value.

The value_if_error is optional. If this option is not included in the Formula, it returns a default error
message. If it is included, it displays a message about what the user has entered in the Formula.

Example 2: Using IF Function with AND, OR, and MOD functions

Since the IF function can be combined with many other Excel functions, we discuss using the IF function
with AND, OR, and MOD functions in this example. Suppose we have the following list of many years
(1990-2022).
We need to determine whether a respective year is a leap year or not using the IF function. As we know
that the leap year consists of 366 days, whereas February has 29 days. We find the leap year using the
following concepts:

o A year exactly divisible by 4 and not exactly divisible by 100, or

o A year exactly divisible by 400

We can use any of the above two conditions and combine the IF function with AND, OR, and MOD. In
the formula, the AND function typically evaluates the conditions of finding the leap years to become the
respective value 'TRUE'. The OR function evaluates one of these two conditions to become the
respective output as 'TRUE'. Furthermore, the MOD function will mainly help us find a remainder after a
dividend is divided by a divisor.

Based on the given conditions of the leap year, we can use the MOD function in two following ways:

MOD(year,4)=0 and MOD(year,100)<>0

Or

MOD(year,400)=0

If any of the above MOD criteria are satisfied, the corresponding year is a leap year. Now let's combine
these criteria into the IF function, our formula will be:

=IF(OR(AND((MOD(year,4)=0),(MOD(year,100)<>0)),(MOD(year,400)=0)),"It is a Leap Year", "It is not a

Leap Year")

Where the term 'year' is used to represent the desired year or use its corresponding cell reference from
the sheet. So, when we apply the entire formula in our resultant first cell (B2), we replace the term 'year'
with cell A2. It will look like this:

=IF(OR(AND((MOD(A2,4)=0),(MOD(A2,100)<>0)),(MOD(A2,400)=0)),"It is Leap Year", "It is not a Leap

Year")
Similarly, we can apply the IF formula in other resultant cells in column B to find whether the respective
years are the leap years. Once we apply the formula in all the resultant cells, our example sheet looks like
this:

If we hide the formula in the resultant cells and check their results, we get to know which years in our
list are the leap years. The following image displays that the following years 1992, 1996, 2000, 2004,
2008, 2012, 2016, and 2020 are the leap years, while others are no-leap years.
IF with NOT function
The IF function combines NOT to display the required result.

NOT Function
Example: Use the NOT Function with the IF function
1. Enter the data in the required worksheet, namely A1:B5
2. To check whether the data present in cell A1:A5 is NOT Pink, the value present in cell B1 is
added with the result 10, or else the value in B1 is returned. Enter the formula in the cell C1 as
=IF(NOT(A1="Pink"),B1+10,B1)

3. Use the fill handle to display the remaining cells' results.

Example 3: Using IF Function in VLOOKUP

In this example, we combine the IF function with the VLOOKUP function to make it more effective.
Suppose we have the following example data sheet with a list of a few items in column A and their
availability in column B.

We need to use the IF function to determine whether the specific item is available in our stock (inventory).
We use cell D2 to enter the item's name to be searched, while the availability of the related item will be
recorded in cell E2. So, cell D2 will serve as the lookup value for the VLOOKUP function, and cell E2 will be
our resulting cell to insert the entire formula.

When combining the IF function with VLOOKUP, the VLOOKUP function mainly looks up the values
referring to the availability of the respective item, while the IF function checks whether the number of
availability is greater than zero. If the number of items is above zero, the corresponding item is in our
inventory.

Based on our example data, we can apply the VLOOKUP function in the following way:

=VLOOKUP(D2,A2:B8,2,0)

Where A2:B8 is the table array, and '2' is the column number used to return a value.

Now, we apply the condition for the item availability by combining the above VLOOKUP formula with the
IF function in the following way:

=IF(VLOOKUP(D2,A2:B8,2,0)>0, "Item Available","Item Not Available")

 The formula returns 'Item Not Available' for the item 'SSD' in the above image. Likewise, our data table
shows zero items for SSD in our inventory. So, the formula works correctly. We can change the item name
in cell D2, and the item's availability will be automatically reflected in cell E2. However, the formula
remains the same.

Nested IF Function
The IF function in Excel can be nested. The Nested IF Function is a combination of multiple IF functions. A
'nested IF' contains at least one IF function inside another to evaluate more than one condition at once
and return the output accordingly. In Excel, up to 64 IF functions can be nested in a formula. But, we must
double-check to ensure that each IF condition is correctly nested inside the other.

The following example shows the use of nested IF where IF functions are used (nested) inside another.

1. =IF(C5<60,"F",IF(C5<71,"D",IF(C5<85,"C",IF(C5<95,"B","A"))))

The condition in the IF function is used to find the corresponding grade based on the scores. The reasoning
for assigning grades is in the table below:
 Score Grade

0-600 F

61-700 D

71-840 C

85-940 B

95-100 A

We usually move in a nested IF formula in one direction, whether from high to low or low to high. In our
example, we go from low to high. We start by testing to find grades for scores below 60, then move on to
the next IF function and test grades for scores below 70, and so on. This way, we allow the function to
return output (grade) only if the condition is TRUE, and the function checks the next IF condition only if
the previous function has already returned FALSE.

Note: Instead of using the multiple nested IF functions, we can use an IFS function in Excel. However, the
IFS function is only available in Office 2019 and Microsoft 365. In older versions, we can consider using
VLOOKUP or HLOOKUP because they can handle many conditions properly, even in complex situations.

IF function with specific text

The IF function does not support wildcards. Hence the combination of ISNUMBER and SEARCH Function
is used to find the specific text in the given data.

Example: Find the specific text using ISNUMBER and SEARCH Function.

Step 1: Enter the data in the worksheet, namely A1:A5

Step 2: To determine whether the particular string is present in cell A1, enter the Formula in cell B1 as
=IF(ISNUMBER(SEARCH("ABC," A1)), "Yes," "No")

Step 3: If the specified data is present in the cell, the Function returns the result as Yes, or it returns the
result as No.

Step 4: Use the fill handle to display the remaining cells' results.
IF Function for Case-sensitive Data

To differentiate the upper case and lower case characters, the IF function, along with the EXACT
function, is used.

The steps to be followed are:

1. Enter the data in the worksheet, namely A1:B5

2. Enter the Formula in the cell C1 as =IF(EXACT(B1, "flower"), "No," "Yes"). Press Enter. The result
will be displayed in cell C1 based on case-sensitive data.

3. Use the fill handle to display the result for the remaining cells.

IF Formula executing another formula

The IF formula is used to execute another formula using specific criteria. The steps to be followed are:
1. Enter the data in the cell range A1:A5
2. In cell B1 enter the formula as =IF(A1>70,A1*5%,A1*7%). Press Enter. ThFunctionon multiplies
the data with 5% if the value is greater than 70, or else it multiplies the data with 7%.

3. The fill handle displays the result for the remaining cells.
IF Function for Date
Along with text and numeric values, the IF function calculates dates. The IF function and the DATEVALUE
function are used to compare the dates.
To compare the dates in the given data, the steps to be followed are :
1. Enter the dates in the required worksheet, namely A1:A5

2. Enter the Formula in the cell B2 as = IF(A1>DATEVALUE("5/5/2023"), "Coming Soon," "Completed")

3. Use the fill handle to display the remaining cells' results.

IFunctionon for Date using Absolute Reference. An alternative option to compare dates is using Absolute
Reference for the specified Date.

The steps to be followed are:

1. Enter the range of data in the worksheet, A1:A5, and enter the specified Date in cell C1.

To compare the Date with the specified Date present in cell C1, enter the Formula in cell B1 as
=IF(A1>$C$1, "Coming Soon," "Completed"). Press Enter, and the result will be displayed in cell B1.

3. Use Fill Handle to display the result for the remaining cells.
IF function for Date using TODAY() Function
An alternative option to compare the dates is using the Today(Function) for the specified Date.
The steps to be followed are:
1. Enter the range of data in the worksheet, namely A1:A5.
2. To compare the Date with today's Date, enter the Formula in cell B1 as =IF(A1>TODAY(), "Coming
Soon","Completed"). Press Enter, and the result will be displayed in cell B1.

3. Use Fill Handle to display the result for the remaining cells.

IF function using ISBLANK() Function

The ISBLANK() function with the IF function is used to check whether the cell is blank.

The steps to be followed are:

1. Enter the data in the worksheet, namely A1:A5
2. To check whether the cell range A1:A5 contains blank, enter the Formula in the cell B1 as
=IF(ISBLANK(A1),0,1). The Function returns the value zero; if the cell is blank or if the cell contains
data, it returns the value one

3. Use Fill Handle to display the result for the remaining cells.

Important Points to Remember

o The IF function does not support wildcards. However, we can use basic wildcard functionality by
combining the IF function with the COUNTIF function.
o If any argument in the IF function is supplied as the array, the function evaluates each array
element accordingly.
o The #NAME! error occurs for the IF formula when we type the wrong function name. For example,
IFF, IEF, etc.
o The #VALUE! error for the IF formula occurs when the argument 'logical_test' is not evaluated as
TRUE or FALSE.
 Excel IF Function

MS Excel, or Microsoft Excel, is a powerful spreadsheet program that enables users to record large
amounts of data in cells within multiple worksheets. Additionally, it allows users to perform various
mathematical calculations and analytical operations on recorded data using a wide range of existing
functions and formulas. The IF function is one such built-in widely used, most popular Excel function.

In this tutorial, we discuss the brief introduction of the Excel IF Function. The tutorial also discusses the
step-by-step process of using the Excel IF function, including the relevant examples.

What is the IF Function in Excel?

The Excel IF function performs a logical comparison between two values (or cells containing values). The
function evaluates if the supplied condition satisfies and then returns an output value depending on
whether the result of the condition is TRUE or FALSE. In particular, the IF function is an inbuilt conditional
function that returns a value based on the fulfillment or non-fulfillment of the supplied condition.

For example, we can use the IF function to compare two values, whether the value in cell A1 is greater
than that in cell B1. If the conditions satisfy, it results as the TRUE; otherwise, FALSE. The working of the
Excel IF function is almost similar to the appropriately structured Flow Chart. The function is mainly useful
when making logical interpretations for decision-making. We can also extend the logical test functionality
of the Excel IF function by combining it with other logical functions, such as AND, OR, etc.

Syntax of IF Function
The syntax of the Excel IF function is defined as below:
1. =IF(logical_test, value_if_true, value_if_false)

Where, the 'logical_test', 'value_if_true', and 'value_if_false' are the three parts or arguments in the IF
function.

Based on the above syntax, the general format of the Excel IF function is defined as below:

=IF(A1>B2, "TRUE", "FALSE")

We separated the different arguments (or parts) in the IF formula by a Comma (,). However, we can also
use the Semicolon (;) based on the language settings of the machine/ device.

Arguments of IF Function
The IF Formula in Excel accepts the following three arguments:
 o Logical_test: It is the first and the mandatory argument. It helps us specify the condition
to be tested or evaluated as either TRUE or FALSE. The specified condition can be a value
or a logical expression.
o Value_if_true: It is an optional argument accepted by the IF function. It helps us specify
the value that we need to return as output when the given condition (logical_test) is
'TRUE'.
o Value_if_false: It is another optional argument accepted by the IF function. It helps us
specify the value that we need to return as output when the given condition (logical_test)
is 'FALSE'.

General Flow Chart Structure of Excel IF Function

As discussed above, the IF function works on the concept of a flow chart. Depending on the condition and
usage of several logical functions, the flow chart of the IF function may appear to range from simple to
complex accordingly. We can draw a flow chart of generic IF function like below:

The above flow chart shows that there is only one condition or criteria, while the two outcomes are based
on condition satisfaction. If the condition is satisfied (evaluated as TRUE), the function returns the value
from the left box. If the condition is not satisfied (evaluated as false), the function returns the output from
the other side.

Logical Operators used in IF function

The Excel IF formula typically uses logical operators to compare the values based on the given condition.
When evaluating a test using the IF function, we can use any of the below logical operators:

Operator Description Example

= Equal to A1=B1

< Less than A1<b1< td=""></b1<>

<= Less than or equal to A1<=B1

 > Greater than A1>B1

>= Greater than or equal to A1>=B1

<> Not equal to A1<>B1

How to use the IF Function in Excel?

To use the IF function in our Excel sheet, we must perform the following steps:
o First, we must enter or type the equal sign (=) to start the function name.
o Next, we must type the function name, followed by the starting bracket, i.e., =IF(.
o In the next step, we need to give the function the first argument (logical_test). We must specify
the condition using operators' signs for logical comparison.
o After that, we need to give optional arguments, returning a value as an output based on the TRUE
or FALSE.

Excel IF Function Examples

Let us understand the concept and working of the Excel IF Function better with the help of the following
examples:

Example 1: Basic IF Function application for empty/ non-empty cell

Everyone knows that life is not possible without oxygen. Suppose we have the following excel sheet as an
example data set where column A contains the list of some planets and column B contains data about the
availability of oxygen for these planets.

We only need to use the Excel IF function to find the planets where life is possible. We can use the
oxygen availability criteria in the IF function to get the desired result. Let us now put the IF formula in
our resulting column C (cells C2 to C5) and find the names of the planets with the potential for life
among the planets listed in our example sheet:

o First, we find the possibility of life on the first planet (i.e., Jupiter). So, we select the respective
resultant cell C2 and start the formula by entering an equal sign. After that, we enter the
IFfunction name and the starting bracket, as shown below:
o

o Next, we apply the condition for the first planet. Since we are checking for oxygen availability as
the life factor, we apply the condition (B2="YES") as B2 has data for oxygen availability. It will
look like this:

o Lastly, we give values to be returned based on the satisfaction and dissatisfaction of the
condition. If the condition matches, we want the IF function to return: 'Life is possible here'. If
the condition does not match, we want the IF function to return: 'Life is not possible here'. We
use the Comma (,) to separate the arguments. So, the entire formula looks like this:

After that, we type the closing bracket and press the Enter key to get the corresponding result.
This provides the result that life is not possible on Jupiter.

o After finding the result for the first planet, we can apply the IF formula in the remaining
resultant cells accordingly. However, it will be easier to copy-paste the formula from C2 to other
cells below. Also, we can drag the formula using the Fill Handle. This will help us perform thetask
quickly.
 o

IF function based on equal to

The IF function based on equal to condition checks whether the given number is equal to the specified
value.
Example: Check whether the specified data is equal to some value.
The steps to be followed are:
Step 1: Enter the data in the worksheet, namely A1:C7

Step 2: The items and categories of fruits and vegetables are given in the data. In cell C2, enter the
Formula as =IF (B2="Vegetables," "Nutrients," "Vitamins"). Suppose the data in cell range B2:B7 equal
the Vegetable category. In that case, the result is displayed as Nutrients, or if the data present in the cell
belongs to the Fruits Category, the result is displayed as "Vitamins."

Step 3: The Fill Handle option fills the Formula for the remaining cells, which displays the required result.

IF function based on Greater than

The IF function based on the greater than condition checks whether the given number exceeds
the specified value.
Example: Check whether the specified data is greater than some value.
The steps to be followed are:
Step 1: Enter the data in the worksheet, namely A1:C7

Step 2: To check whether the cost of each product is higher than 1000, enter the Formula in the cell D2
as =IF (C2>1000, "Yes," "No")

Step 3: The Fill Handle option fills the Formula for the remaining cells, which displays the required result.

IF function based on Lesser than

The IF function based on the lesser than condition checks whether the given number is lesser than the
specified value.

Example: Check whether the specified data is lesser than some value.
The steps to be followed are:
Step 1: Enter the data in the worksheet, namely A1:C7
Step 2: To check whether the cost of each product is lesser than 1000, enter the Formula in the cell D2 as
=IF (C2<1000, "Yes," "No")

The Formula returns the result as "True" or "False" based on the conditions.

IF Function using NOT function(<>)

The IF function and the function on(<>) are used to check various criteria in the selected cell.
The steps to be followed are:
1. Enter the data in the cell range A1:A5
2. To check whether the cell range is not blank, enter the Formula in cell B1 as =IF(A1<>",1,0). Press Enter.
The Function returns the value one if the cell is not blank and returns the value zero if the 3. cell is blank.
4. Use the fill handle to display the result for the remaining cells.

The IFS Function

The IFS function is an inbuilt function in Excel and is used to check two or more conditions for the
given data.
The syntax for the IFS Function is as follows:
=IFS (logical_test1, value_if_true1, [logical_test 2, value_if_true2], [logical_test3 ;...)
The IFS function has three logical conditions:
If the given number is more significant than (>), another number
If the given number is equal to (=), another number
If the given number is lesser than (<), another number
Example: Check multiple conditions for the given data using IFS Function
The steps to be followed are:
Step 1: Enter the data in the worksheet range A1:D7
Step 2: The car driving speeds of various people are entered in the data. Multiple conditions are entered
into the Formula to find the Normal, Fast, and Slow car speeds. Enter the formula in the cell D2 as =IFS
(C2<50,"Slow", C2<100,"Normal", C2>100,"Fast")

Step 3: The Fill Handle option fills the Formula for the remaining cells, which displays the required result.

Notes:

o While creating the Formula for the Nested IF function, the order in which the function is nested
with each other is essential.
o Nesting multiple functions in one Formula is challenging to maintain and sometimes hard to
create.
o An alternative for the Nested if Function is the VLOOLUP formula, INDEX Formula, MATCH, etc.

IF ERROR
The IF ERROR is one of the standard functions in Excel, where it handles Errors in the Formula. If the
Formula contains an error, it returns an expression or a value of the expression.

The syntax of the IFERROR is:

=IFERROR (value, value_if_error)

Value- The Formula or value which needs to be evaluated for errors

Value_if_error- This value is returned if the Formula returns an error value.

The value_if_error is optional. If this option is not included in the Formula, it returns a default error
message. If it is included, it displays a message about what the user has entered in the Formula.

Example 2: Using IF Function with AND, OR, and MOD functions

Since the IF function can be combined with many other Excel functions, we discuss using the IF function
with AND, OR, and MOD functions in this example. Suppose we have the following list of many years
(1990-2022).

We need to determine whether a respective year is a leap year or not using the IF function. As we know
that the leap year consists of 366 days, whereas February has 29 days. We find the leap year using the
following concepts:

o A year exactly divisible by 4 and not exactly divisible by 100, or

o A year exactly divisible by 400

We can use any of the above two conditions and combine the IF function with AND, OR, and MOD. In the
formula, the AND function typically evaluates the conditions of finding the leap years to become the
respective value 'TRUE'. The OR function evaluates one of these two conditions to become the respective
output as 'TRUE'. Furthermore, the MOD function will mainly help us find a remainder after a dividend is
divided by a divisor.

Based on the given conditions of the leap year, we can use the MOD function in two following ways:
MOD(year,4)=0 and MOD(year,100)<>0
Or
MOD(year,400)=0
If any of the above MOD criteria are satisfied, the corresponding year is a leap year.
Now let's combine these criteria into the IF function, our formula will be:

=IF(OR(AND((MOD(year,4)=0),(MOD(year,100)<>0)),(MOD(year,400)=0)),"It is a Leap Year", "It is not a

Leap Year")

Where the term 'year' is used to represent the desired year or use its corresponding cell reference from
the sheet. So, when we apply the entire formula in our resultant first cell (B2), we replace the term 'year'
with cell A2. It will look like this:

=IF(OR(AND((MOD(A2,4)=0),(MOD(A2,100)<>0)),(MOD(A2,400)=0)),"It is Leap Year", "It is not a Leap

Year")

Similarly, we can apply the IF formula in other resultant cells in column B to find whether the respective
years are the leap years. Once we apply the formula in all the resultant cells, our example sheet looks like
this:
If we hide the formula in the resultant cells and check their results, we get to know which years in our list
are the leap years. The following image displays that the following years 1992, 1996, 2000, 2004, 2008,
2012, 2016, and 2020 are the leap years, while others are no-leap years.

IF with NOT function

The IF function combines NOT to display the required result.

NOT Function
Example: Use the NOT Function with the IF function
1. Enter the data in the required worksheet, namely A1:B5
 2. To check whether the data present in cell A1:A5 is NOT Pink, the value present in cell B1 is
added with the result 10, or else the value in B1 is returned. Enter the formula in the cell C1 as
=IF(NOT(A1="Pink"),B1+10,B1)
3. Use the fill handle to display the remaining cells' results.
Example 3: Using IF Function in VLOOKUP

In this example, we combine the IF function with the VLOOKUP function to make it more effective.
Suppose we have the following example data sheet with a list of a few items in column A and their
availability in column B.

We need to use the IF function to determine whether the specific item is available in our stock (inventory).
We use cell D2 to enter the item's name to be searched, while the availability of the related item will be
recorded in cell E2. So, cell D2 will serve as the lookup value for the VLOOKUP function, and cell E2 will be
our resulting cell to insert the entire formula.

When combining the IF function with VLOOKUP, the VLOOKUP function mainly looks up the values
referring to the availability of the respective item, while the IF function checks whether the number of
availability is greater than zero. If the number of items is above zero, the corresponding item is in our
inventory.

Based on our example data, we can apply the VLOOKUP function in the following way:

=VLOOKUP(D2,A2:B8,2,0)

Where A2:B8 is the table array, and '2' is the column number used to return a value.

Now, we apply the condition for the item availability by combining the above VLOOKUP formula with the
IF function in the following way:

=IF(VLOOKUP(D2,A2:B8,2,0)>0, "Item Available","Item Not Available")

 The formula returns 'Item Not Available' for the item 'SSD' in the above image. Likewise, our data table
shows zero items for SSD in our inventory. So, the formula works correctly. We can change the item name
in cell D2, and the item's availability will be automatically reflected in cell E2. However, the formula
remains the same.

Nested IF Function
The IF function in Excel can be nested. The Nested IF Function is a combination of multiple IF functions. A
'nested IF' contains at least one IF function inside another to evaluate more than one condition at once
and return the output accordingly. In Excel, up to 64 IF functions can be nested in a formula. But, we must
double-check to ensure that each IF condition is correctly nested inside the other. The following example
shows the use of nested IF where IF functions are used (nested) inside another.

1. =IF(C5<60,"F",IF(C5<71,"D",IF(C5<85,"C",IF(C5<95,"B","A"))))

The condition in the IF function is used to find the corresponding grade based on the scores. The reasoning
for assigning grades is in the table below:
Score Grade

0-600 F

61-700 D

71-840 C

85-940 B

95-100 A
We usually move in a nested IF formula in one direction, whether from high to low or low to high. In our
example, we go from low to high. We start by testing to find grades for scores below 60, then move on to
the next IF function and test grades for scores below 70, and so on. This way, we allow the function to
return output (grade) only if the condition is TRUE, and the function checks the next IF condition only if
the previous function has already returned FALSE.

Note: Instead of using the multiple nested IF functions, we can use an IFS function in Excel. However, the
IFS function is only available in Office 2019 and Microsoft 365. In older versions, we can consider using
VLOOKUP or HLOOKUP because they can handle many conditions properly, even in complex situations.

IF function with specific text

The IF function does not support wildcards. Hence the combination of ISNUMBER and SEARCH Function
is used to find the specific text in the given data.

Example: Find the specific text using ISNUMBER and SEARCH Function.

Step 1: Enter the data in the worksheet, namely A1:A5

Step 2: To determine whether the particular string is present in cell A1, enter the Formula in cell B1 as
=IF(ISNUMBER(SEARCH("ABC," A1)), "Yes," "No")

Step 3: If the specified data is present in the cell, the Function returns the result as Yes, or it returns the
result as No.

Step 4: Use the fill handle to display the remaining cells' results.

IF Function for Case-sensitive Data

To differentiate the upper case and lower case characters, the IF function, along with the EXACT function,
is used.
The steps to be followed are:
1. Enter the data in the worksheet, namely A1:B5
 2. Enter the Formula in the cell C1 as =IF(EXACT(B1, "flower"), "No," "Yes"). Press Enter. The result
will be displayed in cell C1 based on case-sensitive data.

3. Use the fill handle to display the result for the remaining cells.

IF Formula executing another formula

The IF formula is used to execute another formula using specific criteria. The steps to be followed are:
1. Enter the data in the cell range A1:A5
2. In cell B1 enter the formula as =IF(A1>70,A1*5%,A1*7%). Press Enter. ThFunctionon multiplies
the data with 5% if the value is greater than 70, or else it multiplies the data with 7%.

3. The fill handle displays the result for the remaining cells.

IF Function for Date

Along with text and numeric values, the IF function calculates dates. The IF function and the DATEVALUE
function are used to compare the dates.
To compare the dates in the given data, the steps to be followed are :
1. Enter the dates in the required worksheet, namely A1:A5
 2. Enter the Formula in the cell B2 as = IF(A1>DATEVALUE("5/5/2023"), "Coming Soon,"
"Completed")

3. Use the fill handle to display the remaining cells' results.

IFunctionon for Date using Absolute Reference

An alternative option to compare dates is using Absolute Reference for the specified Date.

The steps to be followed are:

1. Enter the range of data in the worksheet, A1:A5, and enter the specified Date in cell C1.

To compare the Date with the specified Date present in cell C1, enter the Formula in cell B1 as
=IF(A1>$C$1, "Coming Soon," "Completed"). Press Enter, and the result will be displayed in cell B1.

3. Use Fill Handle to display the result for the remaining cells.
IF function for Date using TODAY() Function
An alternative option to compare the dates is using the Today(Function) for the specified Date.
The steps to be followed are:
1. Enter the range of data in the worksheet, namely A1:A5.
2. To compare the Date with today's Date, enter the Formula in cell B1 as =IF(A1>TODAY(),
"Coming Soon","Completed"). Press Ent er, and the result will be displayed in cell B1.
 3. Use Fill Handle to display the result for the remaining cells.

IF function using ISBLANK() Function

The ISBLANK() function with the IF function is used to check whether the cell is blank.
The steps to be followed are:
1. Enter the data in the worksheet, namely A1:A5
2. To check whether the cell range A1:A5 contains blank, enter the Formula in the cell B1 as
=IF(ISBLANK(A1),0,1). The Function returns the value zero; if the cell is blank or if the cell
contains data, it returns the value one

3. Use Fill Handle to display the result for the remaining cells.

Important Points to Remember

o The IF function does not support wildcards. However, we can use basic wildcard functionality by
combining the IF function with the COUNTIF function.
o If any argument in the IF function is supplied as the array, the function evaluates each array
element accordingly.
o The #NAME! error occurs for the IF formula when we type the wrong function name. For example,
IFF, IEF, etc.
o The #VALUE! error for the IF formula occurs when the argument 'logical_test' is not evaluated as
TRUE or FALSE.
How to use COUNT, COUNTIF, and COUNTIFS Function?

Excel COUNT

The COUNT function returns the absolute number of cells that include numbers. This count contains
both Numbers and Dates.

Syntax

1. COUNT (value1, [value2]...)

Where the parameters, value1, [value2], etc. can be any values or authority to cell ranges.

Example 1: The following illustration returns the number of integer values in a given range.
 Example 2: The following illustration count integer Values in the range A1: A4 and B1:B2.

ADVERTISEMENT

EXCEL COUNTIF
Excel COUNTIF function is utilized for counting cells inside a selected range that meet a specific rule, or
condition. For example, we can write a COUNTIF equation to discover out how many cells in our
worksheet include a number higher than or less than the number we consider. Another common use of
COUNTIF in Excel is for counting cells with a particular word or beginning with a specific letter(s).

Syntax

The syntax for a COUNTIF function in Microsoft Excel is:

1. COUNTIF (range, criteria)
Where
range: The range of cells that is examined against the given criteria.
Criteria: The condition which needs to be examined against each cell in the range.

Note: If our criteria is a text string or a statement, then it must be enclosed in the double-
quotes.
 Example: The COUNTIF function following counts the number of cells that are higher than or equivalent
to 5.

Excel COUNTIFS
The Excel COUNTIFS function takes in at least one cell range and returns the total counts if criteria are
fulfilled.

Syntax
1. COUNTIFS (criteria_range1, criteria1, [criteria_range2, criteria2],...)

Where criteria_range1: Arrays of values (or ranges of cells including values) to be examined against the
particular criteria1, criteria2 ... (The provided criteria_range arrays must all have the similar length).

criteria1: The conditions to be examined against the values in the criteria_range1, [criteria_range2],....
Example 1: The following example displays the use of the COUNTIFS function.
In this example, we want to count the number of persons who fulfilled these two criteria.
1. A person should be female.
2. A grade should be <=D, which is between A to C.

Example 2: Count the total number of candidates who have failed in the exam."*male" is utilized to
discover the cells which end with a male.
Excel Advanced If Functions
=IF (AND(B3>50,C3>50),B3+C3,B3*C3)

The IF function says if values of both cells B3 and C3 are greater than 50 then add these cells else
multiply these cells. See the image given below

=IF(B4>AVERAGE(C4:C9),"cell B4>average","")

The IF function says if value in cell B4 is greater than the average of the cells from C4 to C9 then display
"cellB4>average" else leave the cell blank.