Microsoft Excel

Handbook
How to use this Hand Book

This Handbook on Microsoft Excel is primarily aimed for officials in

Government Departments who are required to work on spread sheet
applications, who need to analyse, calculate or present numbers in a
particular format.

This is a compendium of information on various spread sheet utility

features. This book will come handy when one has to work on Excel and
would like know the right way of working on particular feature to arrive at
the desired output. This book will helpful to the readers from both IT and
non -IT background as quick reference guide on various concepts, covered
during the training programme.

This Hand book is organised into sections. Each section presents

information to the readers with different levels of technical expertise and
different needs. At the start of each section is a brief introduction about
the topic and subsequently the methods of working on the specific
features and their usage with practical examples where ever possible.
Excel is a very popular and widely used tool. It helps in day to day
functioning and also helpful for core technical/professional calculation and
data analysis. Mastering this tool will help in playing with data to arrive at
analytical reports. We hope that this Handbook provides concise,
informative and easily used companion for those who work on spread
sheet as they can use the information from this book during training and
use it as reference guide throughout.

Contents
1. Microsoft Excel – An Introduction......................................................................................................7
What is Microsoft Excel?....................................................................................................................7
Why Microsoft Excel..........................................................................................................................8
2. Getting Started with Excel 2010........................................................................................................8
What’s New in Excel 2010.................................................................................................................8
New Features of Excel 2010..............................................................................................................8
The Excel Worksheet (Spreadsheet) and Workbook.......................................................................12
Moving From Cell to Cell.................................................................................................................12
Selecting Cells or Range..................................................................................................................13
Propagating Cell Contents...............................................................................................................13
3. Modifying Spreadsheets.................................................................................................................14
Cut, Copy, and Paste Data...............................................................................................................14
Move/Copy Cells.............................................................................................................................14
Moving and Copying Cells...............................................................................................................14
4. Adding and Deleting Rows and Columns........................................................................................15
Column Width and Row Height......................................................................................................15
Set Column/Row Width/Height......................................................................................................15
Automatically Fit Column/Row Contents........................................................................................16
Set Column/Row Width/Height with Mouse..................................................................................16
Merge or Split Cells.........................................................................................................................17
Merge and Center Cells...................................................................................................................17
5. Formatting Spreadsheets................................................................................................................18
Wrap Text........................................................................................................................................18
Format Numbers.............................................................................................................................18
 Cell Borders.....................................................................................................................................19
6. Locking Cells and Protecting a Worksheet......................................................................................20
Managing Workbooks and Worksheets..........................................................................................20
7. Viewing, Renaming, Inserting, and Deleting Worksheets...............................................................21
How to View a Worksheet..............................................................................................................21
How to Rename a Worksheet..........................................................................................................21
How to Insert a Worksheet..............................................................................................................21
How to Delete a Worksheet.............................................................................................................21
8. Moving Worksheets (Spreadsheets)................................................................................................21
How to Move a Worksheet in the Same Workbook.........................................................................22
How to Move a Worksheet to a NEW Workbook.............................................................................22
How to Move a Worksheet to a Different Workbook.......................................................................22
9. Copying Worksheets (Spreadsheets)...............................................................................................22
How to Copy a Worksheet in the Same Workbook.........................................................................22
How to Copy a Worksheet to a NEW Workbook.............................................................................22
How to Copy a Worksheet to Different Workbook..........................................................................23
10. Important Excel shortcuts.............................................................................................................23
Shortcut keys using Ctrl Keys..........................................................................................................24
Avoid common errors with formulas..............................................................................................24
11. Advanced Spreadsheet Modification............................................................................................25
Hide or Display Rows and Columns.................................................................................................25
12. Basic Math Calculations in Excel...................................................................................................27
Mathematical Order of Operations.................................................................................................28
Copying and Pasting Formulas........................................................................................................29
Examples of Excel Functions: The SUM Function............................................................................29
Examples of the SUM Function.......................................................................................................30
13. The AVERAGE Function.................................................................................................................30
What is the AVERAGE Function?.....................................................................................................30
Example of the AVERAGE Function.................................................................................................31
14. The MAX Function........................................................................................................................31
Examples of the MAX Function.......................................................................................................32
15. The MIN Function.........................................................................................................................32
Examples of the MIN Function........................................................................................................33
16. What is the COUNT Function?......................................................................................................34
Examples of the COUNT Function...................................................................................................34
17. The COUNTIF Function..................................................................................................................35
Other COUNTIF Function Operators...............................................................................................35
Advanced COUNTIF Spreadsheet Examples....................................................................................36
18. Logical Function............................................................................................................................39
If function........................................................................................................................................39
Description of the IF Function.........................................................................................................39
Examples of the IF Function.............................................................................................................40
Using AND and IF Functions Together..............................................................................................44
Using OR and IF Functions Together................................................................................................46
19. Conditional Formatting..................................................................................................................47
Highlight orders from Texas.............................................................................................................48
Highlight dates in the next 30 days..................................................................................................49
Highlight column differences..........................................................................................................49
20. Data Sorting..................................................................................................................................50
Sorting by Multiple Columns...........................................................................................................50
21. Filter..............................................................................................................................................51
What are filters?.............................................................................................................................51
Preparing to filter............................................................................................................................51
Advanced Filter...............................................................................................................................53
One condition in two or more columns..........................................................................................53
One condition in one column or another........................................................................................53
One of two sets of conditions for two columns..............................................................................53
To turn off the Advanced Filter.......................................................................................................54
22. Subtotal........................................................................................................................................55
Formatting and sorting your Excel data..........................................................................................55
Applying Subtotal to your table......................................................................................................56
23. References....................................................................................................................................59
24. Naming Cells and Ranges..............................................................................................................60
 How do you define a named range?...............................................................................................60
25. Lookup function............................................................................................................................62
Introduction....................................................................................................................................62
Vlookup syntax................................................................................................................................62
Vlookup at work..............................................................................................................................63
Working with exact matches...........................................................................................................66
Converting formulas to values........................................................................................................67
26. Data Validation..............................................................................................................................68
What is Data Validation?.................................................................................................................68
27. Pivot Table....................................................................................................................................69
What is a Pivot Table in Excel?.........................................................................................................69
Create an Excel Pivot Table..............................................................................................................71
Group A Pivot Table in Excel............................................................................................................74
Example 2: Group a Pivot Table by Range........................................................................................75
Common Pivot Table Grouping Error...............................................................................................76
Pivot Table Grouping Error: Cannot Group That Selection...............................................................76
28. Using Excel Auditing Tools.............................................................................................................77
Viewing formulas.............................................................................................................................77
Tracing cell relationships.................................................................................................................77
Identifying precedents....................................................................................................................78
Tracing error values........................................................................................................................78
Fixing circular reference errors.......................................................................................................78
Using the Excel Formula Evaluator..................................................................................................79
Excel Error Values...........................................................................................................................79
29. Comments in Excel........................................................................................................................79
What a comment can do in Excel?..................................................................................................79
How to Add Comments to Cell in Excel...........................................................................................80
How to Show/Hide Comments to Cell in Excel................................................................................81
30. Working with Tables in Excel 2010................................................................................................82
 Creating Tables In Excel 2010..........................................................................................................83
Properties of Excel Tables...............................................................................................................84
Deleting an Excel Table...................................................................................................................84
31. Excel 2010 Macros........................................................................................................................85
32. Previewing and Printing................................................................................................................85
Printing the Worksheet...................................................................................................................85
Printing of Worksheet in Multiple Pages.........................................................................................86
Repeating Rows and Columns for Multiple Pages...........................................................................86
Previewing Worksheet....................................................................................................................87
1. Microsoft Excel – An Introduction

What is Microsoft Excel?

Microsoft Excel is a spread sheet program that is used to record and analyse
numerical data.

Spreadsheet programme is a software application capable of organizing, storing

and analyzing data in tabular form. The application can provide digital simulation
of paper accounting worksheets. They can also have multiple interacting sheets
with data represented in text, numeric or in graphic form. With these capabilities,
spreadsheet software has replaced many paper-based systems, especially in the
business world. Originally developed as an aid for accounting and bookkeeping
tasks, spreadsheets are now widely used in other contexts where tabular lists can
be used, modified and collaborated.

Spreadsheet software provides a distinct advantage when working with numbers.

Calculation and functionalities are easier to represent in spreadsheets than in
word processors, and thus effective data handling is possible. Spreadsheet
software also provides flexible presentation of data. This software is capable of
interacting with databases, can populate fields and can also help in automation of
data creation and modification. Spreadsheet software can be shared both online
and offline and allows for easy collaboration. There are many spread sheet
applications available in the market or in open source (free), for the purpose of
this training Microsoft Excel is used.

Data in spread sheet is represented by cells, organized as rows and columns that
form a table and can be text or numeric. Alphabetical letters are assigned to
columns and numbers are assigned to rows. The point where a column and a row
meet is called a cell. The address of a cell is given by the letter representing the
column and the number representing a row.
Why Microsoft Excel

We all deal with numbers in one way or the other. Microsoft Excel comes in handy
when we want to record, analyze and store such numeric data and it becomes
easy to handle data.

2. Getting Started with Excel 2010

What’s New in Excel 2010

With the introduction of Excel 2010, Microsoft is putting the focus on analysing
data, visualising trends, and sharing the results. Along with enhancements to
existing functionality made across the board, Excel 2010 comes with a few
new features too.

New Features of Excel 2010

• Slicer – a filter enhancement for PivotTables that helps us to quickly and

intuitively drill down and analyse data in our spread sheet.

• Sparklines – tiny charts designed to fit in a cell that helps us to

visualise trends in the underlying data.

• Share - publish to a SharePoint compatible server. You and your

colleagues can then work on the same worksheet simultaneously.

• Conditional formatting – conditional formatting is much improved. It

gives you more control over styles and icons, there are improved data
bars, and you have the ability to highlight specific items in a few clicks.
You also can display data bars for negative values to more accurately
illustrate your data visuals.

• Protected View for downloaded items – Excel 2010 prevents you

from editing downloaded content without your consent.

• 64-bit support – in fact, all of the Office 2010 apps offer 64-bit support
giving you greater processing power.
 • Greater capacity – thanks to 64-bit support, the 2GB file size limit on
previous versions of Excel is blown out of the water with Excel 2010. You
can now work on files that have a staggering 4GB size.
The file you create and edit in MS Excel is called a workbook. You can create
an Excel workbook from a blank workbook or from an existing, customizable
Excel template. Each workbook file contains many worksheets, which are
comparable to individual pages in a Word document. A worksheet is also
referred to as a spreadsheet or a sheet, and you can use these terms
interchangeably. This book also uses the terms “workbook” and “file”
interchangeably.

Starting Excel with the Start Menu

1) Click on Start Button
2) Select All Programs
3) Select Microsoft Office
4) Click on Microsoft Excel 2010

Starting Excel by Using Run Option

1) Click on Start Button
2) Select Run (a dialog box will appear)
3) Type Excel
4) Enter
This will launch the Microsoft Excel 2010 application and you will see the following
excel window.
Quick Access Toolbar

Ribbon
Formula Bar

Spreadsheet Grid

Status Bar

There are 5 important areas in the screen

1. Quick Access Toolbar:

This is a place where all the important tools can be placed. When you start
Excel for the very first time, it has only 3 icons (Save, Undo, and Redo). But
you can add any feature of Excel to to Quick Access Toolbar so that you can
easily access it from anywhere (hence the name).

2. Ribbon:

Ribbon is like an expanded menu. It depicts all the features of Excel in easy
to understand form. Since Excel has 1000s of features, they are grouped in to
several ribbons. The most important ribbons are – Home, Insert, Formulas,
and Page Layout & Data.

3. Formula Bar:

This is where any calculations or formulas you write will appear. You will
understand the relevance of it once you start building formulas.

4. Spreadsheet Grid:

This is where all your numbers, data, charts & drawings will go. Each Excel
file can contain several sheets. But the spreadsheet grid shows few rows &
columns of active spreadsheet. To see more rows or columns you can use the
scroll bars to the left or at bottom. If you want to access other sheets, just
click on the sheet name (or use the shortcut CTRL+Page Up or CTRL+Page
Down).

5. Status bar:

This tells us what is going on with Excel at any time. You can tell if Excel is
busy calculating a formula, creating a pivot report or recording a macro by
just looking at the status bar. The status bar also shows quick summaries of
selected cells (count, sum, average, minimum or maximum values). You can
change this by right clicking on it and choosing which summaries to show.

The Ribbon

Understanding the Ribbon is a great way to help understand the changes

between Microsoft 2003 to Microsoft 2007/2010. The ribbon holds all of the
information in previous versions of Microsoft Office in a more visual stream
line manner through a series of tabs that include an immense variety of
program features.

Home Tab

This is the most used tab; it incorporates all text and cell formatting features
such as font and paragraph changes. The Home Tab also includes basic
spreadsheet formatting elements such as text wrap, merging cells and cell
style.
Insert Tab
This tab allows you to insert a variety of items into a document from
pictures, clip art, and headers and footers.

Page Layout Tab

This tab has commands to adjust page such as margins, orientation and
themes.

Formulas Tab

This tab has commands to use when creating Formulas. This tab
holds an immense function library which can assist when creating
any formula or function in your spreadsheet.

Data Tab
This tab allows you to modifying worksheets with large amounts of data
by sorting and filtering as well as analyzing and grouping data.

Review Tab

This tab allows you to correct spelling and grammar issues as well as
set up security protections. It also provides the track changes and
notes feature providing the ability to make notes and changes
someone’s document.
View Tab
This tab allows you to change the view of your document including
freezing or splitting panes, viewing gridlines and hide cells.

The Excel Worksheet (Spreadsheet) and Workbook

An Excel worksheet, or spreadsheet, is a two-dimensional grid with

columns and rows. Look at the spreadsheet below. The column names are
letters of the alphabet starting with A, and the rows are numbered
chronologically starting with the number one. The cells in the first roware
A1, B1, C1, and so on. And the cells in the first column are A1, A2, A3, and
so on. These are called cell names or cell references.

We use cell references when creating math formulas or functions. For

example, the formula to add the contents of cells B2 and B3 together is:
=B2+B3.

The Name Box is located in the area above Column A, and displays the cell
reference of the selected cell - the cell where the cursor is resting. In our
spreadsheet above, the selected cell is C2. Notice that the column letter
(C) and the row number (2) change color.
The beginning of the Formula Bar can be seen in the area above Column D
on our worksheet. The Formula Bar displays the contents of the selected
cell.

A workbook is a collection of worksheets or spreadsheets. When the Excel

program is opened, a workbook opens with three blank worksheets. The
names of the worksheets are displayed on tabs at the bottom of the Excel
window.

Moving From Cell to Cell

The arrow keys can be used to move left, right, up, and down from the
current cell. Press the Enter key to move to the cell immediately below the
current cell, and press the Tab key to move one cell to the right.

Selecting Cells or Range

In order to complete more advanced processes in Excel you need to be
able to highlight or select cells, rows and columns. There are a variety of
way to do this, see the table below to understand the options.
Propagating Cell Contents

There are multiple ways to propagate or fill data from one cell to adjacent
cells. Let's begin with two popular keyboard shortcuts that allow us to fill
down, or fill to the right:

• To fill adjacent cells with the contents of the cell above, select the cell
with the data and the cells to be filled and press Ctrl + D (the Ctrl key
and the D key) to fill down.
• To fill adjacent cells with the contents of the cell to the left, select the
cell with the data and cells to be filled and press Ctrl + R (the Ctrl key
and the R key) to fill to the right.
To propagate in any direction, use the Fill Handle. Click in a cell with data
to be copied, hover the cursor over the cell's lower right corner until the
cursor changes to a thin plus sign (+) or a dark square, and drag up,
down, left, or right to fill the cells. Excel's Fill Handle is powerful; see our
Fill Handle tutorial.

If the data to be copied is a date, number, time period, or a custom-made

series, the data will be incremented by one instead of just copied when
the Fill Handle is used. For example, to display the months of the year in
column A, type January in cell A1, drag the Fill Handle down to cell A12,
and the months will display, in order, in column A!

3. Modifying Spreadsheets

In order to create an understandable and professional document you

will need to make adjustments to the cells, rows, columns and text.
Use the following processes to assist when creating a spreadsheet.

Cut, Copy, and Paste Data

You can use the Cut, Copy, and Paste commands in Microsoft Office
Excel to move or copy entire cells or their contents. NOTE: Excel
displays an animated moving border around cells that have been cut or
copied. To cancel a moving border, press ESC.

Move/Copy Cells

When you move or copy a cell, Excel moves or

copies the entire cell, including formulas and
their resulting value s, cell formats, and
comments.
1. Select the cells that you want to move or copy.
2. On the Home tab, in the Clipboard group, do one of the following:
a. To move cells, click Cut .
b. To copy cells, click Copy .
3. Click in the center of the cell you would like to Paste the information
too.
4. On the Home tab, in the Clipboard group, click Paste .

NOTES: Excel replaces existing data in the paste area when you cut and
paste cells to move them. When you copy cells, cell references are
automatically adjusted. If the selected copy or paste area includes
hidden cells, Excel also copies the hidden cells. You may need to
temporarily unhide cells that you don't want to include when you copy
information.

Moving and Copying Cells

To move cell contents, right-click in the selected cell and click Cut; then
right-click in the new location and click Paste. Similarly, to copy cell
contents, right-click in the selected cell and select Copy, and paste in the
new cell.

You can copy the contents of a cell as described above, but paste and fill
multiple adjacent cells. Just highlight the block of cells you want to paste
in: click and hold the left mouse button in one corner of the cell range. Still
holding the mouse button down, swipe the cursor over to the opposite
corner until just the cells you want filled are highlighted. Then right-click
and click Paste.

To remove the animated border around the original cell, press the ESC
key, or start typing in a new cell.

4. Adding and Deleting Rows and Columns

To insert a new row in a spreadsheet, right-click on a row number, and

click Insert. Excel always inserts the row ABOVE the row that was clicked
on. If you want to continue inserting rows, press the F4 key to insert each
additional row.

To delete a row, right-click on the row number, and click Delete.

Contiguous rows can be deleted by highlighting them before clicking
Delete. And noncontiguous rows can be selected by pressing and holding
the CTRL key before clicking Delete. Don't press the Delete key on the
keyboard unless you just want to delete the cell contents and not the
actual row.
 To insert a new column, right-click on a column letter and click Insert.
Excel always inserts the column to the LEFT of the column that was
clicked on. As with rows, if you want to add additional columns after
inserting the first column, press the F4 key.

To delete a column, right-click on the column letter, and click Delete.

Contiguous columns can be deleted by highlighting them before clicking
Delete. And non-contiguous columns can be selected by pressing and
holding the CTRL key. Don't press the Delete key on the keyboard unless
you just want to delete the cell contents and not the actual column.

Column Width and Row Height

On a worksheet, you can specify a column width of 0 to 255 and a row
height of 0 to 409. This value represents the number of characters that
can be displayed in a cell that is formatted with the standard font. The
default column width is 8.43 characters and the default row height is
12.75 points. If a column/row has a width of 0, it is hidden.

Set Column/Row Width/Height

1. Select the column(s) or row(s) that you want to change.
2. On the Home tab, in the Cells group, click Format.
3. Under Cell Size, click Column Width or Row Height.
4. A Column Width or Row Height box will appear.

5. In the Column Width or Row Height box, type the value that you
want your column or row to be.
Automatically Fit Column/Row Contents
1. Click the Select All button

2. Double-click any boundary between two column/row

headings.

3. All Columns/Rows in the entire worksheet will be changed to

the new size
NOTE: At times, a cell might display #####. This can occur when the
cell contains a number or a date that exceeds the width of the cell so it
cannot display all the characters that its format requires. To see the
entire contents of the cell with its current format, you must increase the
width of the column.

Set Column/Row Width/Height with Mouse

To change the width of one column/row
1. Place you cursor on the line between two rows
or
columns.

2. A symbol that looks like a lower case t with arrows on the

horizontal line will appear

3. Drag the boundary on the right side of the

column/row heading until the column/row is
the width that you want.
To change the width of multiple columns/rows

1. Select the columns/rows that you want to change

2. Drag a boundary to the right of a selected column/row heading.
3. All selected columns/rows will become a different size.
To change the width of columns/rows to fit the contents in the cells
1. Select the column(s) or row(s) that you want to change
2. Double-click the boundary to the right of a selected column/row
heading.
3. The Column/Row will automatically be size to the length/height of the
longest/tallest text.
Merge or Split Cells
When you merge two or more adjacent horizontal or vertical cells the
cells become one larger cell that is displayed across multiple columns or
rows. When you merge multiple cells, the contents of only one cell appear
in the merged cell.

Merge and Center Cells

1. Select two or more adjacent cells that you
want to merge.
2. On the Home tab, in the Alignment group,
click Merge and Center.
3. The cells will be merged in a row or column, and the cell contents will
be centered in the merged cell.

Merge Cells
To merge cells only, click the arrow next to
Merge and Center , and then click
Merge Across or Merge Cells.

Split Cells
1. Select the merged cell you want to split
. The cells will split
2. To split the merged cell, click Merge and Center and the contents of
the merged cell will appear in the upper-left cell of the range of split
cells.

Automatically Fill Data

To quickly fill in several types of data series, you can select cells and drag
the fill handle . To use the fill handle, you select the cells that you
want to use as a basis for filling additional cells, and then drag the fill
handle across or down the cells that you want to fill.
1. Select the cell that contains the formula that you want to be brought
to
other cells.

2. Move your curser to the small black square in the lower-right corner
of a selected cell also known as the fill handle. Your pointer will
change to a small black cross.

3. Click and hold your mouse then drag the fill handle across the cells,
horizontally to the right or vertically down, that you want to fill.
4. The cells you want filled will have a gray looking border around them.
Once you fill all of the cells let go of your mouse and your cells will be
populated.

5. Formatting Spreadsheets

To further enhance your spreadsheet you can format a number of

elements such as text, numbers, coloring, and table styles. Spreadsheets
can become professional documents used for company meetings or can
even be published.

Wrap Text
You can display multiple lines of text inside a cell by wrapping the text.
Wrapping text in a cell does not affect other cells.
1. Click the cell in which you want to
wrap the text.
2. On the Home tab, in the Alignment
group, click Wrap Text.
3. The text in your cell will be wrapped.

NOTE: If the text is a long word, the characters won't wrap (the word
won't be split); instead, you can widen the column or decrease the font
size to see all the text. If all the text is not visible after you wrap the text,
you might have to adjust the height of the row. On the Home tab, in the
Cells group, click Format, and then under Cell Size click AutoFit Row

Format Numbers
In Excel, the format of a cell is separate from the data
that is stored in the cell. This display difference can
have a significant effect when the data is numeric. For
example, numbers in cells will default as rounded
numbers, date and time may not appear as
anticipated.
After you type numbers in a cell, you can change the format in which they
are displayed to ensure the numbers in your spreadsheet are displayed as
you intended.

1. Click the cell(s) that contains the numbers that you want to format.
2. On the Home tab, in the Number group, click the arrow next to the
Number Format box, and then click the format that you want.
 If you are unable to format numbers in the detail
you would like that you can click on the More
Number Formats at the bottom of the Number
Format drop down list.

1. In the Category list, click

the format that you want to
use, and then adjust
settings to the right of the
Format Cells dialog box. For
example, if you’re using the
Currency format, you can
select a different currency symbol, show more or fewer decimal places,
or change the way negative numbers are displayed.
Cell Borders
By using predefined border styles, you can quickly add a border around
cells or ranges of cells. If predefined cell borders do not meet your needs,
you can create a custom border.

NOTE: Cell borders that you apply appear on printed pages. If you do not
use cell borders but want worksheet gridline borders for all cells to be
visible on printed pages, you can display the gridlines.

Apply Cell Borders

1. On a worksheet, select the cell or range of cells that you want to
add a border to, change the border style on, or remove a border
from.
2. Go to the Home tab, in the Font group
3. Click the arrow next to Borders
4. Click on the border style you would like
5. The border will be applied to the cell or cell range
NOTE: To apply a custom border style, click More
Borders. In the

Format Cells dialog box, on the Border tab, under

Line and Color, click the line style and color that
you want.

Remove Cell Borders

1. Go to the Home tab, in the Font group
2. Click the arrow next to Borders

3. Click No Border .
NOTES: The Borders button displays the most recently used border
style. You can click the Borders button (not the arrow) to apply that style.

6. Locking Cells and Protecting a Worksheet

There are two steps to preventing important cell content from being
accidentally overwritten or deleted. First, the cell must be locked.
Second, the worksheet must be protected. If you have any valuable data
or complex formulas that you do not want to lose, and data DOES get
accidentally erased(!), then you owe it to yourself to learn which cells to
lock and which worksheet protection options to take.
Managing Workbooks and Worksheets

When the Excel program is first opened, the user is presented with a
workbook containing three empty worksheets (spreadsheets). The first
empty worksheet is displayed, and in the bottom left corner of Excel we
see three tabs - one for each worksheet - with the names Sheet1, Sheet2,
and Sheet3 as shown in the screenshot below. We also see arrows that are
used to scroll right and left to locate worksheet tabs when a workbook has
a large number of worksheets.

If you're only using one worksheet, you don't have to delete the two
unused worksheets - most folks don't bother. Excel workbooks are saved
with a file extension of xlsx in newer versions of Excel. Older versions
used the xls extension.

How many Excel worksheets can we have in one workbook? Microsoft says
the number is limited to your computer's memory! It's handy to group
together worksheets that are VERY closely related, and especially if you
are linking data from one worksheet to another. But hopping back and
forth using the worksheet tabs can become confusing.

7. Viewing, Renaming, Inserting, and Deleting

Worksheets
"Sheet1" isn't very descriptive. Here is how you view, rename, insert, and
delete worksheets in a workbook.

How to View a Worksheet

To view a worksheet, click on its tab. If the workbook window is not wide
enough to display all of the tabs because of long worksheet names and/or
many worksheet tabs, use the arrows to the left of the tabs to navigate
left or right, or right-click on any of the arrows and select the desired
worksheet from the list that displays.

How to Rename a Worksheet

To rename a spreadsheet, right-click on the spreadsheet tab, select

Rename from the context menu, and type a new name. Or, double-click on
the worksheet tab and type a new name.

How to Insert a Worksheet

The fastest way to insert a worksheet in a workbook is to simply click on

the small tab to the right of the last worksheet tab as shown in the image
below. Then you can move the worksheet to a different position if desired.
Alternatively, you can insert a new worksheet to the left of an existing
worksheet by right-clicking on the tab of the worksheet that is
immediately to the right of where you want the new worksheet to be
located and select Insert from the Insert window. Excel always inserts a
spreadsheet to the left of the selected worksheet.

How to Delete a Worksheet

To delete a worksheet, right-click on the worksheet tab and select Delete

from the context menu.

8. Moving Worksheets (Spreadsheets)

Sometimes we need our worksheets need to be in a different order or
even in a different workbook.
How to Move a Worksheet in the Same Workbook

There are two ways to move a worksheet in the same workbook. The easy
way is to click and hold the left mouse button on a worksheet's tab and
slide the tab to its desired position. Watch the little black arrow that
appears just above. When it is to the right of left of the adjacent
worksheet, release the mouse and the worksheet will be moved.

If you dislike dragging with the mouse, here is another method. Right-click
on the tab of the source worksheet and click "Move or Copy..." In the Move
or Copy window, click the name of the worksheet that you want the sheet
to be inserted before, and click OK.

How to Move a Worksheet to a NEW Workbook

To move a spreadsheet to a new workbook, right-click on the tab of the
source spreadsheet and click "Move or Copy." In the Move or Copy
window, click the drop-down arrow under “To Book:” and click (new book).
Excel removes the worksheet from the existing workbook and opens a
new workbook containing the moved worksheet. Save the workbook.

How to Move a Worksheet to a Different Workbook

Open both the source workbook and the target workbook. Right-click on
the tab of the source worksheet (the one to be moved) and click "Move or
Copy..." Then at the top under "To book," click the small down arrow to
open up the drop-down menu and click on the name of the target
workbook (where the worksheet is to be moved to). Verify that the
worksheet was successfully moved to the other workbook and save the
workbook.

9. Copying Worksheets (Spreadsheets)

Rather than start from scratch, it is often easier to copy, and then modify,
an existing worksheet - especially if you're going to be using a lot of the
same formatting, formulas, and so on.

How to Copy a Worksheet in the Same Workbook

To copy a worksheet in the same workbook, right-click on the tab of the
source worksheet and click "Move or Copy..." In the Move or Copy window,
check the “create a copy” box, click the name of the worksheet that you
want the sheet to be inserted before, and click OK.

How to Copy a Worksheet to a NEW Workbook

To copy a worksheet into a new workbook, right-click on the tab of the
source worksheet and click "Move or Copy..." In the Move or Copy window,
click the drop-down arrow under “To Book:” and click (new book). Excel
opens a new workbook containing the copied spreadsheet. Save the new
workbook.
How to Copy a Worksheet to Different Workbook
The best way to copy a worksheet to an another workbook is as follows:
Open both the source workbook and the target workbook. Right-click on
the tab of the source worksheet (the one to be copied) and click "Move or
Copy..." On the Move or Copy window, CHECK the box at the
bottom titled "Create a copy." Then at the top under "To book," click
the small down arrow to open up the drop-down menu and click on the
name of the target workbook (the other workbook). Verify that the
worksheet was successfully copied to the other workbook and save the
workbook.

As a rather messy alternative, you can copy and paste the contents as
follows. In the source worksheet, right-click in the top left corner cell to
select all the workbooks cells and select Copy.

Then, open the other Excel workbook, find an empty worksheet, right-click
in the top left corner cell to select all cells, and click Paste. Save the
workbook.

Return to the first (source) worksheet and press the ESC key to remove
the animated border and then click in an empty cell to deselect all of the
cells.
10. Important Excel shortcuts

Keystroke Where the Insertion Point Moves

Forward one box

Back one box

Up one box

Down one box

PageUp To the previous screen

PageDown To the next screen

Home To the beginning of the current screen

End To the end of the current screen

Shortcut keys using Ctrl Keys

Key Where the Insertion Point Moves
Combination

Ctrl + To the last box containing data of the current row.

Ctrl + To the first box containing data of the current row.

To the first box containing data of the current

Ctrl + column.

Ctrl + To the last box containing data of the current column.

Ctrl + PageUp To the sheet in the left of the current sheet.

Ctrl +
To the sheet in the right of the current sheet.
PageDown

Ctrl + Home To the beginning of the sheet.

Ctrl + End To the end of the sheet.

Avoid common errors with formulas
The following table summarizes some of the most common errors that you
can make when entering a formula and how to correct those errors:

MAKE SURE THAT YOU MORE INFORMATION

Make sure that all parentheses are
part of a matching pair. When you
Match all open and close
create a formula, Excel displays
parentheses
parentheses in color as they are
entered.
When you refer to a range of cells,
use a colon (:) to separate the
reference to the first cell in the range
Use a colon to indicate a range
and the reference to the last cell in
the range. For example, A1:A5.

Some functions have required

arguments. Also, make sure that
Enter all required
you have not entered too many
arguments arguments.

You can enter, or nest, no more than

Nest no more than 64
64 levels of functions within a
functions
function.
Enclose other sheet names in If the formula refers to values or cells
single on other worksheets or
workbooks, and the name of the
other workbook or worksheet
contains a non-alphabetical
character, you must enclose its
name within single quotation marks
( ' ).
without Do not format numbers as you
enter them in formulas. For
Enter numbers example, even if the value that
formatting you want to enter is $1,000, enter
1000 in the formula.

11. Advanced Spreadsheet Modification

Once you have created a basic spreadsheet there are numerous things
you can do to make working with you data easier. Some of these elements
are hiding, freezing and splitting rows. You can also sort and filter data,
these
 features are quite helpful when working with a
large amount of data.

Hide or Display Rows and Columns

You can hide a row or column by using the
Hide command or when you change its row
height or column width to 0 (zero). You can
display either again by using the Unhide
command. You can either unhide specific rows
and columns, or you can unhide all hidden
rows and columns at the same time. The first
row or column of the worksheet is tricky to
unhide, but it can be done.

Hide Rows or Columns

1. Select the rows or columns that you want to hide.
2. On the Home tab, in the Cells group, click Format.

3. Under Visibility, point to Hide &

Unhide, and then click Hide Rows or Hide
Columns.
NOTE: You can also right-click a row or column (or a selection of

multiple rows or columns), and then click Hide.

Unhide Rows or Columns

1. Select the rows, columns or entire sheet to unhide.

2. On the Home tab, in the Cells group, click Format.
3. Under Visibility, point to Hide & Unhide, and then click Unhide Rows
or Unhide Columns

TIP: You can also right-click the selection of visible rows and columns
surrounding the hidden rows and columns, and then click Unhide

Freezing/Splitting Rows an d Columns

To keep an area of a worksheet visible while you scroll to another area of
the worksheet, you can either lock specific rows or columns in one area
by freezing panes.
Freezing vs. splitting
When you freeze panes, Microsoft Excel keeps
specific rows or columns visible when you scroll
in the worksheet. For example, if the first row
in your spreadsheet contains labels, you might
freeze that row to make sure that the column
labels remain visible as you scroll down in your
spreadsheet. A solid line indicates that the row is frozen to keep column
labels in place when you scroll.

When you split panes, Excel creates either two or four separate
worksheet areas that you can scroll within, while rows or columns in
the non-scrolled area remain visible. This
worksheet has been split into four areas.
Notice that each area contains a separate
view of the same data. Splitting panes is
useful when you want to see different
parts of a large spreadsheet at the same
time.
NOTE: You cannot split panes and freeze
panes at the same time.

Freeze Panes
1. On the worksheet, select the row or column that you want to keep
visible when you scroll.
2. On the View tab, in the Window group, click the arrow below Freeze
Panes.
3. Then do one of the following:

• To lock one row only, click Freeze Top Row.  To lock one
column only, click Freeze First Column.

• To lock more than one row or

column, or to lock both rows and
columns at the same time, click
Freeze Panes.
NOTE: You can freeze rows at the top and columns on the left side of the
worksheet only. You cannot freeze rows and columns in the middle of the
worksheet.

Unfreeze panes
1. On the View tab, in the Window group, click the arrow below
Freeze Panes.
2. Click Unfreeze Panes.
Split Panes
1. To split panes, point to the split box at the top of the
vertical scroll bar or at the right end of the horizontal
scroll bar.
2. When the pointer changes to a split pointer or , drag the split box
down or to the left to the position that you want.
3. To remove the split, double-click any part of the split bar that divides
the panes.

12. Basic Math Calculations in Excel

Whenever the contents of the cells referenced in a math formula

change, Excel will automatically recalculate the answer for you. That is
what makes this software program so powerful.

The 5 basic rules to remember as we discuss Excel formulas are:

1. All Excel formulas start with an equal (=) sign. This tells Excel that it
is a formula.
2. The answer to the formula displays in the cell into which the formula
is entered.
3. Cells are referenced in a formula by their column-row identifier, ie.
A1, B2.
4. The symbols for addition, subtraction, multiplication, and division
are: + - * /
5. You do not have to enter capital letters in your formula; Excel will
automatically capitalize them.

Example of simple math formulas:

 =A1+A6 this Excel formula adds the contents of cell A1 and A6

 =A1+A2+A3 this Excel formula adds the contents of the three
cells specified. (See the SUM function for adding multiple
numbers)
 =A3-A1 this Excel formula subtracts the contents of cell A1 from
the contents of cell A3
 =B2*B3 this Excel formula multiples the numbers in cells B2 and
B3
 =G5/A5 this Excel formula divides G5 by A5. (NOTE: If you see
the error message #DIV/O! in a cell, you are trying to divide by
zero or a null value - which is not allowed.)
 =G5^2 this formula tells Excel to square the value in cell G5.
The number after the caret is the exponent. Likewise, the formula
 H2^3 would cube the value in cell H2.

We can combine multiple operations in one formula. Make sure you use
parentheses where needed or you may not get the correct results (see
Order of Operations below). Here are some examples:

 =(C1+C3)/C4 This Excel formula adds the value in C1 to the

value in C3, and then divides the result by the value in C4
 =4*(A2+A5)+3 This Excel formula adds the contents of A2 and
A5, multiples this sum by 4, and then adds 3.
Mathematical Order of Operations

Remember the Order of Operations by remembering the phrase

Please Excuse My Dear Aunt Sally.

The letters stand for: Parentheses, Exponents, Multiplication, Division,

Addition, and Subtraction. And all operations are carried out from left to
right. Here is how the order is applied:

1. First, any math inside of parentheses is calculated.

2. On the second pass, all exponents are resolved.
3. Then any multiplication OR division is performed.
4. Lastly, any addition OR subtraction is performed.

Note: Even though the Aunt Sally phrase may imply that multiplication is
done before division, and addition is done before subtraction, that is not
true. They are performed during the same step, or pass, through the
formula.

Let's illustrate with a simple formula: 4+2*3

 Since the multiplication must be done first, our expression resolves
itself to 4+6=10.

Let's practice with a more complex formula: (2*4)+3^2-8/4

 Step 1 - Parentheses: 2*4 = 8. Now our expression reads: 8+3^2-
8/4
 Step 2 - Exponents: 3^2=9. Now our expression reads: 8+9-8/4
 Step 3 - Multiply and Divide: 8/4=2. Now our expression reads: 8+9-
2
 Step 4 - Add and Subtract: The answer is 15

Now test your skill on a complicated formula! 3^(6/3)+(3*3)-2*(6-3)

  Pass 1 - Parentheses: 6/3=2, 3*3=9, and 6-3=2. So now our formula
reads: 3^2+9-2*3

 Step 2 - Exponents: 3^2=9. So now our formula reads: 9+9-2*3

 Step 3 - Multiply and Divide: 2*3=6. So now our formula reads: 9+9-
6
 Step 4 - Add and Subtract: 12
Copying and Pasting Formulas

To copy the contents of a cell, click in the cell, right-click, and click Copy.
(Or use the keyboard shortcut Ctrl+C.) Then place the cursor in the
receiving cell, right-click, and click Paste. (Or use the keyboard shortcut
Ctrl+V.)

To remove the animated border on the original cell, press Enter, press the
Esc key, or click in another cell and begin typing.

When pasting the contents of a cell into multiple cells, the cell contents
need only be copied once. Use the arrows on the keyboard to move to the
other cells and paste.

Examples of Excel Functions: The SUM Function

The SUM function allows the user to easily add values from a range of
cells.
Description of the SUM Function
It's easy to add in Excel. To add the contents of cells G3 and G4, merely
type =G3+G4. But the SUM function in vital when adding lengthy columns
of cells.
The syntax for the SUM function is:

SUM (number1,number2,number3, ... )

Where:
► number1, number2, number3, ... are from 1 to 255 arguments,
for which you want the sum. The arguments can contain cell
references, formulas, and functions.
Examples of the SUM Function

=SUM(2,1,3) - This Excel function adds the numbers 2, 1, and 3 and

returns a value of 6.
=SUM(B6:M6,R6) - This Excel function adds the contents of B6 through
M6, plus R6.
=SUM(A2:A23)/150 - This Excel formula adds the contents in A2
through A23 via the SUM function, and then divides the result by 150.
=10*SUM(A1:D1) - This Excel formula adds the contents of cells A1
through D1 as directed by the SUM function, and then multiplies the result
by 10.
=SUM(A2:A14,C4:C29) - This Excel function adds together the sum of
the contents of A2 through A14, and the sum of the contents of C4
through
C29.
=SUM(6/3,4*2,33) - This Excel function adds the numbers 2 (6÷3), 8
(4x2), and 33 and returns a value of 43.

13. The AVERAGE Function

We often use the word "average" in our daily lives. But in the world of
mathematics, the average, also called the mean, of a group of numbers
has a strict definition and must be calculated.

What is the AVERAGE Function?

When you find the average of a group of numbers, you calculate their sum
and then divide the sum by the number of numbers in the group. Microsoft
Excel calculates averages with the AVERAGE function.

The syntax of the AVERAGE function is:

AVERAGE(number1, number2, number3, ... )

Where:
► number1, number2, etc. are from 1 to 255 arguments for which Excel
should find the average. The arguments can be numbers; or cell
references, cell ranges, formulas, or other functions that resolve to a
number.

Cells that contain the AVERAGE function should be formatted to a desired

number of decimal points as the decimal portion may be infinite when
averaging numbers.
Example of the AVERAGE Function

=AVERAGE(4,5,2) . . . This Excel function adds three numbers and

divides by 3: (4 + 5 + 2 = 11; 11 ÷ 3 = 3.66666..... If the cell is formatted
to display 2 decimal points, Excel returns a value of 3.67

Worksheet Examples of the AVERAGE Function

Example #1:

=AVERAGE(C1,C4) . . . This example shows that individual cell

references can be arguments. The average of C1 and C4 is 4: 3 + 5 = 8;
8÷2=4

Example #2:

=AVERAGE(C1:C3) . . . This example shows the function containing a cell

range. The average of the first 3 cells in Column C is 3: 3 + 4 + 2 = 9; 9
÷3=3

Example #3:

=AVERAGE(C1:C2,5) . . . This example shows that arguments can be

numbers: 3 + 4 + 5 = 12; 12 ÷ 3 = 4

Example #4:
=AVERAGE(C1:C2,C4) . . . This example mixes a cell range with a cell
reference. Since C4 contains the number 5, the answer is the same as
Example #3 above

14. The MAX Function

The MAX function determines the largest value from the range specified in
the arguments..

The syntax of the MAX function is:

MAX(number1, number2, ... )

Where:
► number1, number2, etc. are from 1 to 255 arguments, which
can contain cell references, formulas, or functions that resolve to
numeric values.
Now let's look at several spreadsheet examples of the MAX function.
Examples of the MAX Function

Example 1:

=MAX(A1:A5,500) — This Excel function determines the largest value

among the numbers in cells A1 through A5, and the number 500. Excel
returns a value of 755 (cell A3).
Example 2:

=MAX(A1:A5,900) — This Excel function determines the largest value

among the numbers in cells A1 through A5, and the number 900. Excel
returns a value of 900.
Example 3:

=MAX(A1:B5) — This Excel function determines the largest value among

the numbers in the cell range A1 through B5. Excel returns a value of 923
(cell B3).

Example 4:

=MAX(A1:B1,A5,B5) — This Excel function determines the largest value

among 4 specific cells. Excel returns a value of 502 (cell A1).

15. The MIN Function

The MIN function determines the smallest value from the range specified
in the arguments.

The syntax of the MIN function is:

MIN(number1, number2, ... )
Where:
► where number1, number2, etc. are from 1 to 255 arguments,
which can contain cell references, formulas, or functions that
resolve to numeric values.

Examples of the MIN Function

Look at the last two examples in the worksheet below that show the MIN
function:

Example 1:

=MIN(A1:B5) — This Excel function determines the smallest value among

the cell range of A1 to B5. Excel returns a value of 20 (cell A4).

Example 2:

=MIN(B1:B5,100) — This Excel function determines the smallest value

among the numbers in cells B1 through B5, and the number 100. Excel
returns a value of 65 (cell B4).

Example 3:

=MIN(A1:B5,10) — This Excel function determines the smallest value

among the numbers in the cell range A1:B5, and the number 10. Excel
returns a value of 10.
Example 4:
=MIN(A1,B1,A5,B5) — This Excel function determines the smallest value
among the 4 cells listed, and returns a value of 89 (cell B5).

16. What is the COUNT Function?

This excel function calculates the number of cells that contain numeric
values or the number of arguments in the function, if any, that are
numeric. The syntax of the COUNT function is:

COUNT(value1, [value2], . . . )
Where:
► value1 is required and is an item, cell reference, or range of
cells, and
► [value2] . . . are optional and are 1 to 255 additional items, cell
references, or ranges.

For example, the function =COUNT(5,10,15) would ask Excel to count

the number of arguments that are numeric (3). And the
function COUNT(C1:C22) would ask Excel to count the number of cells in
the range C1 through C22 that contained a numeric value.

Examples of the COUNT Function

Now let's look at three unique

examples of the COUNT function.
In the spreadsheet below,
columns A and B contain data.
Column C contains the actual
COUNT function, so what we
see is the function's result.
Column D shows the function.
Follow along as we discuss each of
the three examples below the worksheet image.

Example 1:

=COUNT(A1:A5) - This Excel function counts the number of cells in the

range A1 through A5 that contain a numeric value. Excel returns a value of
3 (cells A2, A3, and A5). Cells A1 and A4 are not counted because they do
not contain numbers.

Example 2:

=COUNT(A1:A5,10) - The number of cells with numeric values in cells

in A1 to A5 is 3, plus 1 for the number 10; so Excel displays 4 (cells A2, A3,
A5, and the number 10)

Example 3:
=COUNT(A1:A5,B1:B5) - This Excel function counts all cells containing
numeric values in cells A1 to A5, and cells B1 to B5. Excel returns a value
of 5 (cells A2, A3, A5, B2, and B4)

17. The COUNTIF Function

This Excel function allows you to display the number of cells in a single
range whose values meets specific criteria. The default action or operator
for the COUNTIF function is equals and is not included when writing
the function. The syntax of the COUNTIF function is:

COUNTIF(range,criteria) ... where range is a group of cells, and

criteria is the value a cell must have to be counted.

Example #1:

=COUNTIF(B1:B9,4) counts the number of cells in B1-B9 whose value

equals 4.

Example #2:

=COUNTIF(B1:B9,A1) counts the number of cells in the same range

whose value equals that of cell A1.

Example #3:

=COUNTIF(B1:B9,"yes") counts the number of cells in B1-B9 that contain

the word yes - text must be enclosed in quotation marks.

Other COUNTIF Function Operators

As discussed above, the default operator for the COUNTIF function is

equals. However, advanced users can specify the other operands listed
below, and this tutorial discusses, and provides examples of, the use of
these other operands.

> greater than

< less than
>= greater than or equal to
<= less than or equal to
<> not equal to
These operators must be enclosed in quotation marks!
Advanced COUNTIF Spreadsheet Examples

The image below shows our Excel spreadsheet containing seven advanced
examples of the COUNTIF function.

Columns A and B contain text and numerical data. Column C contains

the actual function, and we show what the function looks like in Column
D.

Read the explanation of each example, and then study the worksheet. We
repeat the worksheet image several times for your convenience.

Example 1: Count the cells in a range whose values do not equal a

string of text

=COUNTIF(A1:A7,"<>Smith") - The operand and criteria must be

wrapped in quotes as shown. This function returns a value of 5 as there
are five cells in the range that do not equal Smith. Excel excludes empty
cells, cells with numbers, and error messages.

Many tutorials instruct users to concatenate every value that follows an

operator. For instance, it is perfectly OK to write the function as
=COUNTIF(A1:A7,"<>"&"Smith"). It just isn't necessary.

Example 2: Count the cells in a range that are < the contents of a
particular cell

=COUNTIF(B1:B7,"<"&B1) - When using an operator AND

referencing a cell name in the criteria, two rules must be followed.

• The operator must be enclosed in quotation marks, and

• An ampersand (&) must precede the cell name

The ampersand (&), which is the concatenation operator, must precede
the cell name so that Excel knows you are referencing a cell name. If you
omit the ampersand, Excel will display the dreaded "The formula you
typed contains an error" popup window.

Referencing a cell name with an operator is one of the few instances that
concatenation must be specified.

Example 3: Count the number of cells in a range that do not

contain text

=COUNTIF(A1:A7,"<>*") - In this example, the criterion must be wrapped

in quotation marks since we are using an operator. We use the wildcard *
to refer to any text. Excel counts the number of cells that do not contain
text and returns a value of 1 (cell A4).

Excel will count all cells other than cells containing text: empty cells, cells
with numbers, and cells with error messages. Excel will not count,
however, cells that have been cleared of data by pressing the space bar.
Clearing cells this way places a blank space character in the cell. Use the
delete key to clear the contents of a cell instead.
Example 4: Count the number of cells in a range that are not
empty

=COUNTIF(A1:A7,"<>"&"") - In Excel, a blank or empty cell (one that

does not contain a blank space character) is referenced by a pair of
double quotation marks, "". This example counts the number of cells in
A1:A7 that are not empty. We wrap the operator in quotation marks "<>" -
and then place the concatenate operator & before the pair of double
quotation marks symbolizing 'empty.' Excel returns a value of 6.
Example 5: Count the number of cells in a range containing text,
and whose first initial is greater than or less than a specific letter.

=COUNTIF(A1:A7,"<N") - This application of the COUNTIF function is

useful for counting the number of cells whose values are in the first half of
the alphabet (A-M) or the last half of the alphabet (N-Z). However, when
working with criteria of this nature, strict rules must be followed or Excel
will not return the value you expect.

When counting the number of cells in the lower half of the alphabet, the
criterion should read "<N" (and not "<=M"). Always use < on the first
letter after the range's upper limit.

And when counting the number of cells in the upper half of the alphabet,
code ">=N" (not ">M"). Always use >= on the lowest letter of the range.
Example 6: Count the cells in a range that are greater than the
average of the cells in that range

=COUNTIF(B1:B7,">"&AVERAGE(B1:B7)) - This useful application of the

COUNTIF function counts the number of cells whose values are above a
calculated average. (This function can be modified, or course, to count
cells that are less than average or equal to an average.) The operator
must be wrapped in quotation marks and an & placed before the AVERAGE
function. The average of the 7 numbers in this example is 50, so Excel
returns a value of 3.
Example 7: Calculate the percentage of cells in a range that meet
certain criteria - excluding cells without text

=COUNTIF(A1:A7,"Smith")/(ROWS(A1:A7)-COUNTIF(A1:A7"<>*")) - This
example illustrates how to ask Excel to determine what percent as
particular text value is compared to all of the cells in the range with text
values. It can be modified with wildcards; or empty cells could also be
eliminated from the dividend before calculating the percentage. Do
remember that Excel does not consider a cell empty if the space is
pressed when the cell is active. Use the delete key to clear a cell of all
data.

We begin with a COUNTIF function that counts the cells containing Smith
(2). We then divide that number by the number of rows, as determined by
the ROWS function (7), less the number of cells without text values (1).

Notice that the last two functions must be enclosed in parentheses so

Excel will subtract the cells without text values before dividing. Without
these parentheses, Excel would perform the division first. (Mathematical
Order of Operations is important to understand. See our Math Basics
tutorial on the Excel Basics menu.). Our equation reads: 2 ÷ (7 - 1).
Format the cell as a percentage.

18. Logical Function

If function

The IF function of Excel is a very powerful function. It allows the user to

specify certain criteria, and then instruct Excel to perform one action if the
criteria is true, and perform a different action if the criteria is not true.

Description of the IF Function

One of the most useful functions in Excel is the IF function. The IF function
allows you to test a condition and have one value returned if the condition
is TRUE, and another value returned if the condition is FALSE.

The syntax of the IF function is:

IF(logical-test, value-if-true, value-if-false)

where:
► logical-test is a condition which must evaluate to either TRUE
or FALSE
 ► value-if-true is the value Excel returns if the logical test
evaluates to TRUE
► value-if-false is the value that Excel returns if the logical test
evaluates to FALSE
More Information on Excel IF Function
• You can use IF Function for nested formulas.

• The maximum number of nested condition you can perform is 64.

• You can use comparison operators to evaluate a condition in IF function.

Examples of the IF Function

=IF(D1>26,33,44) - This Excel function checks to see if the value in D1

is greater than 26. If so, Excel returns a value of 33. If not, a value of 44 is
returned.

=IF(A5<>"","Done","Open") - This Excel function, a value of Done will

be returned if cell A5 is not empty. Otherwise, a value of Open will be
returned.

=IF(D1<100,"OK","Over Budget") - This Excel formula checks to see

if the value in D1 is less than 100. If so, Excel displays OK. If not, Excel
displays Over Budget. Text must be wrapped in quotation marks.

=IF(SUM(A1:A3)=50,"Slow","Fast") - This Excel formula checks to

see if the sum of the contents of cells A1 through A3 equals 50. If so, Excel
returns a value of Slow. If not, Fast is displayed.

=IF(A2>1000,985,B2+245) - This Excel formula checks to see if the

value in A2 is greater than 1000. If so, Excel returns a value of 985. If not,
Excel displays the sum of B2 and 245.Nested IF Functions

The IF function allows the user to ask Excel to test certain criteria and to
perform different actions depending on if the criteria is met or not.
Because data analysis can be complex, often the logical-test, value-if-true,
and/or value-if-false portions of our IF function must, in themselves,
contain an IF function. When IF functions are used inside of IF functions,
we call this nested IFs.
Excel allows both the "value-if-true" parameter and the "value-if-false"
parameter of the IF statement to contain IF statements. These are
called Nested IFs.
Here is an example of an IF statement for the value-if-false parameter:

=IF(B9>=18,"Adult",IF(B9>12,"Teen","Child"))

This function evaluates the value in cell B9. If the value in B9 is ≥18, the
logical test is true, so Excel displays Adult and stops reading the function.

If the value is not ≥18, Excel executes the value-if-false argument. This
argument contains an IF statement testing to see if the value in B9 is >12.
If true, Excel returns a value of Teen. If not true, Excel displays Child in the
cell.

Nested IFs: Example 1

Review the worksheet below. Each team plays two rounds and their scores
are added. Scores are always integers; there are no "half" points for
example. If their total score is 12 or greater (> 11), they get a Gold medal.
If the total score is 8, 9, 10, or 11 (>7 but < 12), they are awarded a Silver
Medal. If 7 or less, they receive no prize.

The formula for Team A which we have entered into cell E2 can be seen in
the worksheet's formula bar. We also show the formula below:

=IF((C2+D2)>11,"Gold",IF((C2+D2)>7,"Silver","No Prize"))

Let's look at how Excel processes Team A's formula. First, it encounters an
IF statement asking if the sum of cells C2 and D2 is greater than 11. The
sum is 10, so Excel processes the value-if-false portion of the IF
statement which begins with another IF function.
In this second IF function, Excel now compares the sum of the contents of
C2 and D2, to see if the sum is greater than 7. Since the sum is 10, the
test is true, so Excel does what the value-if-true portion of the IF
statement instructs, which is to display Silver in the cell and quit
processing the formula.
=IF((C2+D2)>=12,"Gold",IF((C2+D2)>=8,"Silver","No Prize"))

Note: Because we are working with Integers in this example, instead of

writing >=12, we can just say >11. However, if we had to account for
decimals, or if it is easier for you think in terms of >=, then see the
equivalent formula above.

Nested IFs: Example 2

A common example of nested IF functions is when grades or awards are

based upon a numeric scale.

The spreadsheet below shows the first 6 students of a class. The cells in
Column C contain a formula with nested IF functions for Excel to
determine the letter grade based upon the numeric grade that the
instructor entered in column B.

The formula with nested IFs for the third student on the list, Mr. Cole, can
be seen in the worksheet's formula bar, and is shown below. We've color
coded the parentheses to make it easier to see the nested IF statements.

=IF(B4>89,"A",IF(B4>79,"B",IF(B4>69,"C",IF(B4>59,"D","F"))))

Now we'll take a step-by-step look at how Excel handles the nested IFs in
this long formula. If we had examined the formula for the second student
in the list who got a score of 95, Excel would process the first IF
statement, display an A, and quit! But fortunately for us, this student's
score is quite low. So let's begin.

STEP 1
Excel begins by testing if B4 is > 89. If the test is true, Excel will display
an A and quit processing. But the test is not true as the value in cell B4 is
55, so Excel proceeds to the value-if-false argument - which is another IF
statement which we have underlined in purple.

STEP 2

Now Excel looks to see if the score in cell B4 is > 79. If so, it would display
a B. But 55 in not %gt; 79, so Excel proceeds to the value-if-false
argument. This is another IF statement and is underlined in purple.

STEP 3

This IF statement asks Excel to see if the value in cell B4 is > 69. It is not,
so Excel does not display a C, but proceeds to the value-if-false
argument which, of course, another IF statement (underlined in purple).

STEP 4

We are finally at the end of the formula. The last IF statement asks Excel
to test to see if the value in cell B4 is > 59. If it is, Excel will display a D. If
not, Excel will display an F. Since the value in cell B4 is 55, Excel displays
an F, and is finished processing the formula.

When working with nested IF statements, it is often useful to write the

formula in a simple editor like Notepad (not a word processing program
like Word) in the format shown below in order to keep track of the
parentheses. We've color-coded ours to help you see which pairs belong
together. But remove the spaces before copying and pasting into a
spreadsheet cell.
=IF(B4>89,"A",
IF(B4>79,"B",
IF(B4>69,"C",
IF(B4>59,"D","F"))))

Using AND and IF Functions Together

To combine IF and AND functions you have to just replace logical_test
argument in IF function with AND function.

By using AND function you can specify more than one condition.

Now, AND function will test your all conditions here.

If all the conditions are true then AND function will return true and IF
function will return the value which you have specified for true.

And, if any of the conditions is false then AND function will return false
and IF function will return the value which you have specified for false.

Let’s show you a real life example.

Now let's look at our worksheet examples. Each month sales reps make a
bonus of 500 or 250. To make a 500 bonus, they must sell 11 or more
units AND sell more than 800 cost. So our IF function tests to see if BOTH
of these conditions are met via an AND function. If so, Excel returns a
value of 500; otherwise, a value of 250.
We've entered our functions in Column D, and the function for raju is:

=IF(AND(B2>10,C2>800),500,250)

Then we copied and pasted the function into column D for the other 3
persons. Notice that since we're dealing with whole numbers, we specified
B2>10 instead of B2>=11 because it's easier to write. The function for
Ian can also be seen in the formula bar of our spreadsheet below
Raju's function: =IF(AND(B2>10,C2>800),500,250)

For Raju, the value in B2 (11) is > 10 and the cost in cell C2 (810) is >
800, so the value of the AND function is true. Therefore, the result of the
IF function will be the value-if-true: 500. (We formatted column D to
display currency.)

Kiran's function: =IF(AND(B3>10,C3>800),500,250)

The value for Kiran in cell B3 (9) is NOT > 10. Even though the next
condition of the AND function is met, the result of the AND function is still
false. So the value produced by the IF function is 250 - the value-if-false.

Sam's function: =IF(AND(B4>10,C4>800),500,250)

The value for Sam in B4 (14) is > 10 and the cost in cell C4 (860) is > 800,
so the AND function is true, and Excel displays 500 in cell D4.

Uma's function: =IF(AND(B5>10,C5>800),500,250)

The value for Uma in cell B5 (14) is > 10. However, the value in cell C5
(770) is NOT > 800, so the AND function is false. Therefore, the result of
the IF function is 250 (value-if-false).

Wrap-up

• If Excel cannot arrive at a TRUE or FALSE conclusion, it will return the

#VALUE! error
• If an argument of the function contains text, it must be enclosed in
quotation marks
• To indicate "not equal" in an equation, use <>
• When a function is embedded inside of another function, it is enclosed
in parentheses, and the equal sign (=) that is normally placed in front of
a function is omitted for the embedded function.
Using OR and IF Functions Together
In the syntax of IF function, have a logical test argument in which we use
to specify a condition to test.

IF(logical_test,value_if_true,value_if_false)

And, then it return a value based on the result of that condition.

Now, if we use OR function for that argument and specify multiple

conditions into it.

If any of the conditions is true OR will return true and IF will return the
specific value.

And, if none of the conditions is true OR with return false and IF will return
another specific value.

In this way, we can test more than one value with IF function.

Let’s get into some real life examples.

In this example, we are testing whether any of the three Sales Teams
qualify for the weekly bonus. In order to qualify, the team must have sold
at least 4 computer systems OR at least 9,000 in sales. See the worksheet
below.

The teams are listed in column A, the number of systems sold in column
B, and the total price of the sales in column C. Column D contains the
functions and the function for Team 1 is shown in the formula bar and
follows: =IF(OR(B2>=4,C2>=9000),"Yes","No").
19. Conditional Formatting

Conditional formatting quickly highlights important information in a

spreadsheet. But sometimes the built-in formatting rules don’t go quite far
enough. Adding your own formula to a conditional formatting rule gives it
a power boost to help you do things the built-in rules can’t do.

With conditional formatting, you can do things like highlight dates in the
next 30 days, flag data entry problems, highlight rows that contain top
customers, show duplicates, and more.

Excel ships with a large number of "presets" that make it easy to create
new rules without formulas. However, you can also create rules with your
own custom formulas. By using your own formula, you take over the
condition that triggers a rule, and can apply exactly the logic you need.
Formulas give you maximum power and flexibility.

For example, using the "Equal to" preset, it's easy to highlight cells equal
to "apple".

But what if you want to highlight cells equal to "apple" or "kiwi" or "lime"?
Sure, you can create a rule for each value, but that's a lot of trouble.
Instead, you can simply use one rule based on a formula with the OR
function:
Here's the result of the rule applied to the range B4:F8 in this
spreadsheet:

Here's the exact formula used:

=OR(B4="apple",B4="kiwi",B4="lime")

Highlight orders from Texas

To highlight rows that represent orders from Texas (abbreviated TX), use a formula
that locks the reference to column F:
=$F5="TX"

Highlight dates in the next 30 days

To highlight dates occurring in the next 30 days, we need a formula that
(1) makes sure dates are in the future and (2) makes sure dates are 30
days or less from today. One way to do this is to use the AND function
together with the NOW function like this:

=AND(B4>NOW(),B4<=(NOW()+30))

With a current date of August 18, 2016, the conditional formatting highlights dates
as follows:
Highlight column differences

Given two columns that contain similar information, you can use
conditional formatting to spot subtle differences. The formula used to
trigger the formatting below is:

=$B4<>$C4

20. Data Sorting

After entering data in excel, we may want to arrange it in the alphabetical

order (A Z) or smallest to largest values. For this, we may use the sort
functionality in Excel.

In excel, we can re arrange data on values, cell color, font color, or cell
icon. When we sort on values it may be in the order A to Z, Z to A (Text),
Smallest to Largest, largest to Smallest (Numbers), oldest to newest,
newest to oldest (Date) or custom list.
 Sort

Data tab Sort & filter Group Sort

Sorting by Multiple Columns

In Excel, we can also arrange the data by multiple columns, by clicking on

Add level so that one more column will get added in the sorting list.
Second and subsequent sorting parameters help us sort one field on top
of another. Example, if we sort data first Designation wise and then
location wise, first the data is arranged designation wise in the sorting
order selected and then for each designation, data would be arranged
location wise. We can perform up to 64 levels of sorting in Excel.
 Multi-Level Sorting

21. Filter

What are filters?

Filtering is a quick and easy way to find and work with a subset of data in
an Excel range. Filters enable you to quickly find all the entries for a
particular item, e.g. an employee name or a product, in a large worksheet.

Filters allow you to select just the data you need, and to hide any data
that is not relevant to your search. For example you might want to find the
students with the highest grade, or the records for a specific department.
You can also create your own filters, such as figures within a specific range
or above a particular amount.

Unlike sorting, filtering does not rearrange your data, it simply hides the
rows you don’t want. This will allow you to then edit, format, chart and
print your filtered data as you wish.

Preparing to filter

Ensure the data in your worksheet is neat and tidy and you will find
filtering easy. Use the following guidelines to prepare your data:

Use headings The top row of each column should have a

heading.
Don’t mix the data The data in a column should all be the same type –
don’t mix text and numbers.
Don’t interrupt Don’t have blank rows or columns. Individual blank
cells are OK.
Keep separate The data to be filtered should be in its own
worksheet. If not, separate it from other data with
a blank row or column.
Filtered data displays only the rows that meet criteria that you specify and
hides rows that you do not want displayed. After you filter data, you can
copy, find, edit, format, chart, and print the subset of filtered data without
rearranging or moving it.

Data tab Sort & filter Group  Filter

When we click on auto filter option a drop arrow key will get display on
each heading on the
data so, that we can
filter the list as per
our condition. We
just need to
uncheck the value
which we do not want
to view.
To use Auto filter
effectively, our
database must have
a heading and
data must be
ideal. An ideal
database contains
no blank row or blank
column and no
merged cells.

Auto Filter can be

done in any type of
data i.e. text values, numeric values and date values where we have
various type of criteria which is already mention in the list or we have to
select the custom filter option which last option in the list of Text filter,
Number filter or Date Filter.
Advanced Filter

The AutoFilter command on the Home Tab is usually the quickest way to filter a list.
However, if you want to filter a list using multiple sets of criteria or criteria containing
formulas use the Advanced Filter command on the Data Tab.
The Advanced Filter enables you to filter data by using a criteria range to display only the
rows that meet all the criteria you specify.

One condition in two or more columns

To find data that meets one condition in two or more columns, enter all
the criteria in the same row of the criteria range.

One condition in one column or another

To find data that meets either a condition in one column or a condition in

another column, enter the criteria in different rows of the criteria range.
Deptt Name Location DOJ Designation
Finance
Mumbai
Assistant Manager
One of two sets of conditions for two columns

To find rows that meet one of two sets of conditions, where each set
includes conditions for more than one column, type the criteria in
separate rows.
Deptt Name Location Basic

Finance >=15000

Procurement >30000
As an example, we will extract from the list all Employees whose Basic Pay
is greater than 15,000 from the Finance Department and also employees
from the Procurement Department with a Basic Pay greater than 30,000

 Insert several blank rows at the very top of your worksheet. (Your
column headings should now be on row 10 of the worksheet)
 Select the column headings in Row 10 and copy. Paste the headings into
Row 1 of the worksheet.

 Enter the criteria for the advanced filter as follows;

Emp Code Name Dept Name Location DOJ Designation Basic
Finance >=15000
Procurement >=30000
 Click into any cell within the list and then select the Data tab. In the Sort
& Filter group choose Advanced Filter…

 Ensure that the List Range is $A$7:$G$43

 The Criteria Range - specifies the range
of cells on your worksheet that contains
your criteria. In this example it should
be $A$1:$G$3, to ensure this click into
the Criteria range box and then with the
mouse click into cell A1 of the
worksheet and highlight to cell G3.

 Under the heading Action there are 2

options: Filter the list, in-place - hides
the rows that do not meet the criteria
and the filtered list is displayed where
the existing
List range had been displayed. Copy to Another Location copies the
filtered data to another worksheet or another location on the same
worksheet. If this option is selected the Copy to: box will no longer be
greyed out and you click into this box and select where you wish the
filtered list to appear.

 Click OK. This will return you to the worksheet, and display the records
that match the criteria.(Three records meet the criteria)
Emp Code Name Deptt Name Location DOJ Designation Basic
E101 Rahul Jain Finance Delhi 21-Jan-01 Manager 56699
E164 Sahiba Tandon Finance Mumbai 30-Nov-08 Executive 43381
E172 Vimi Gupta Procurement Mumbai 22-Oct-06 Executive 42028
To turn off the Advanced Filter

From the Data tab in the Sort & Filter Group click on Clear.

22. Subtotal

Subtotals are an ideal way to get totals of several columns of data that
need to be broken down in several different categories. It, like the title
suggests, will subtotal each category for you making it a quick and easy
way to get your Excel data summarized.

• Formatting and sorting your Excel data

• Applying Subtotal to your table

Formatting and sorting your Excel data

As always make sure your data is set up in a format that makes sense -
like a database, headers across the top with all your information in the
columns below the headers, see my sample below:
Make sure that the category that we need to subtotal by is sorted. In this
case we will be using the Department Name, and all my Departments are
sorted. If you don't sort the data prior to running the Subtotals function,
Excel will run your subtotals but it does a subtotal at each change in your
chosen column. So if you don't sort the data, you may have multiple
subtotals for the same item and have a messy situation to clean up. This
will make more sense as we go through the steps and run our subtotals.
Applying Subtotal to your table

First go to the Data tab on the ribbon and select the Subtotal:

After selecting Subtotal, this window pops up on top of your data file:
Go to the At each change in: area.
This is where we tell Excel how we
want our data sorted. I want this data set to be sorted by Department
Name, that way when we are all done, I can see the Location,
Designation by Department. You must think this through because if you
choose a category that is moot, then your Subtotal will end up
being meaningless. For example if I choose Location as my category,
then at each change in location I will have a subtotal for the columns I
choose and this will be meaningless to me for what I am trying to do.

Moving on, I am choosing Department Name, and in this case I want the
sum, but there are other options as well that you might need for your data
analysis, (Sum, Count, Average, Max, Minimum, Product, Count Numbers,
Standard Deviation, Standard Deviation Population, Variance, Variance
Population). Again, I am choosing SUM as my function:

Then click on the OK button and here are my results:

As you can see Excel adds a row at each change in Shipment Number and
Sums the appropriate columns. This is why you have to sort your data
first.
However this format is somewhat cumbersome to use.

If you notice Excel has added an outline to the data on the left hand side.
There are 3 levels, and this view is the expanded, or level 3 view. If we
select the 2 in the upper left hand corner by the red arrow... Then our data
will look like this:
If I hit the 1 in the upper left hand corner, by the 2 we just hit, our data
looks like this:
This view gives us the Grand Total of all of our data ready to use for easy
copying to a summary elsewhere.

When you open the Subtotal window, the Summary below data button is
selected for you. This is the default selection and puts a Subtotal line at
the bottom of each change in your selected variable. By unselecting this
button, it puts the subtotal at the top, and your Grand Total at the top of
the page, see picture below:

It makes for an interesting view and can be more convenient in some

scenarios. It is just an option that you can utilize when and if you need to
do so.

Also, if you uncheck the Replace current subtotals checkbox, you can add
a few subtotals into your table and get some interesting results.

If you are done with your Subtotals, just open the Subtotals window and
click on the Remove All button, and your data will go back to the previous
format. The Subtotals will be removed and it is as though you have not
touched your data, sometimes a good way to start over if you have made
an error or if you realize you need to add or remove columns from your
Subtotals.

23. References

There are 3 different sorts of cell references in Excel. These are:

• Relative

• Absolute

• Mixed

A relative reference will change column and row numbers as it is copied to

other cells. Think of it as an original instruction being to go in a certain
direction e.g. 2 rows up and 2 columns across from your current position.
When this instruction is copied elsewhere, it will still refer to 2 rows up and
2 columns across from your new current position.

An absolute reference is one that does not change. If you refer to a cell in
a certain row and column and then copy that reference elsewhere, it will
still refer to exactly the same cell or range.

A mixed reference is one that is half relative and half absolute.

An absolute address is defined with the use of the “$” symbol. This can be
typed in at the time of creating the formula, or by editing the cell
afterwards.

Examples of relative and absolute addressing:

The “$” sign can be hand typed or inserted by pressing the keyboard
shortcut F4. As you press F4 the cell address will cycle between the four
variations of an address – for example A4 would cycle between:

A4 A$4

$A4 $A$4

24. Naming Cells and Ranges

Cells and ranges can be given a name so that when you need to refer to
that cell or range of cells, you can refer to its name rather than an
address.

If you have a range of cells that hold a specific type of data e.g. Sales, No.
of students etc. then you can give that range a meaningful name. Later if
you want to refer to the range you can do that by specifying its name
instead of using its cell reference.

In the example below we have two columns, Month (column A) and Sales
Value (column B).

If we want to sum the total sales for the 12 months then we will use this
formula:

=sum(B2:B13)

Alternatively we can give the range B2:B13 a name like this “SalesValue”
and then use the sum formula as follows:

=sum(SalesValue)

The names will make it easier for you to refer

to a range and to make your formulas more
meaningful.

How do you define a named range?

1. Select the range that you want to name.

2. In the Formulas tab, Defined Names group click Define Name. The New
Name window will open.
3. In the Name field enter the name you want to give to your range.

4. In the Scope combo box, if you want this name to be known to other
sheets in the workbook, select Workbook. If the name will only be used
in the current worksheet, then select the name of the worksheet.

5. In the Refers To field make sure that the required range is specified.

6. Click OK when you are done. Your defined name is now ready to be used
in any formula.

NOTES:

• Range names must be one word – if you require 2 words, join them with
an underscore as in the example below.

• The names can be a combination of numbers and letters, but must not
start with a number.

• The default is to show the address as being ABSOLUTE. By clicking in

the REFERS TO text box, this can be edited to what is required.

• As a default, names are workbook level i.e. the name you create is
available throughout the whole workbook.
• If the worksheet has been named with a name that includes spaces,
the worksheet name will have to be enclosed in single quotation marks.

25. Lookup function

Introduction

A lookup formula essentially returns a value from a table by looking up

another related value. A common telephone directory provides a good
analogy. If you want to find a person’s telephone number, you first locate
the name (look it up) and then retrieve the corresponding number.

There are multiple lookup functions in Microsoft Excel and these include
vlookup, the very similar hlookup, and lookup. You use vlookup to find data
in a table by searching for a value in the first column of the table and then
returning the corresponding value in another column.

Use hlookup when the data is organized differently to find a matching

value in the top row of a table and to return the corresponding value from
one of the rows below. The lookup function has two forms - a vector and
an array form and it returns a value from a one column or one row range,
or from an array. Of the three, the one you're most likely to use day to day
is vlookup and this is the one I'll focus on here. Basically if you can
understand and create a vlookup you can create an hlookup function too.

Vlookup syntax

To use vlookup to return a value from a table you will provide Excel with
the value to look for in the first column of the table, the table range and
the column which contains the value to return.

When you provide the table range, Excel looks for the value you specify in
the first column of that range - typically the first column will be the row
headings for your data. When describing the column you do so as the
number of the column in the table range you've provided. Column 1 is the
first column and 2 is the one immediately to its right and so on. If you
specify a column number that is out of the range of your table - such as
less than 1 or greater than the number of columns in your table range
you'll get an error.

There is one optional True/False argument for this function which lets you
force an exact match or an approximate one - the default is the latter. If
you specify an exact match (False) then the table need not be sorted. If
you do not include this argument or if you specify an approximate match
(True) you must sort the table so that the values in the first column are in
ascending order - if not - the function may not return the correct result.
Where you omit this argument or use True, if there is not an exact match,
then Excel uses the nearest value, in the first column, to the value that
you are searching for that is smaller than that value.

So, for example, in this table where we are showing pounds weight, a
handling and a shipping charge, we can use vlookup to search for a weight
and determine the handling and shipping charge for a parcel of that
weight. Of course most parcels won't weigh exact amounts so we will use
the True value (or omit it) so the formula will return a result even if an
exact match is not found. This also means we need to sort the table so the
data in the first column is in ascending order.

Vlookup at work

If we try to find a match for a 1.5 pounds weight parcel there is no entry
for 1.5 pounds. In this case, the lookup function will return the entry for
the largest value that is less than the value that we're looking up. So if
we're looking up 1.5 and there is no match for it, Excel will look for the
number that is nearest 1.51 but less than it - in other words 1.

To look up a value in cell B11 that is the pounds weight of a parcel (in this
case 1.5lbs) and to return the handling value, you would write this
formula:

=VLOOKUP(B11,D2:F7,2)

The formula returns the handling cost of $4 - the value in column 2 of the
table opposite the next nearest but smaller value to the one we are
looking up.
If you want to be able to copy the formula down a column, then you will
need to alter it so that the table reference is absolute, so it would look like
this:

=VLOOKUP(B11,$D$2:$F$7,2)

Of course, you can also name your

table by selecting the cells from D1
to F7 and choose Formulas >
Define Name, type a name for the
table and click Ok.

Now you can use the table name when you are creating the formula. Here
the named range appears in place of the range itself:
=VLOOKUP(B12,shipping_and_handling,2)
We can adapt the formula for the handling to deal with the shipping. In
this situation because the shipping is in column 3 of the table, the only
change will be to the last value in the formula:

=VLOOKUP(B12,shipping_and_handling,3)

Hlookup works similarly, it takes the same values, in other words the value
to look up, the range of the table, but in this case you'll be providing a row
number rather than a column number. The rows will be numbered 1, 2, 3
and so on with row 1 being the first row in the table.

In the example above, we want to be able to find weights such as 11, 12

and 25 in a table that simply doesn't have those values in it. The ability to
find the closest value that is smaller than the value that we're looking up
is extremely attractive. There are, however, some provisos when you're
using this process and that is that you'll have to have a starter value as
we do of 0. This will catch the situation where you have a shipping weight
of less than 1 pound.
Working with exact matches

In other circumstances you may want an exact match and there may be
no room for the next nearest values. The example below for rug sizes and
costs is exactly that. If you don't have an order for a green weave rug then
there is no next lowest value. In this situation, we want an exact match or
an error message.

For this, we'll look up the value in column A and return a value from
column 2 or 3 depending on whether the order is for a medium or a large
rug. In this circumstance we'll need to use an If function to determine
which column number to use. The lookup function will read as follows:

=VLOOKUP(A7,A2:C4,IF(B7="M",2,3),FALSE)

In this case, we're looking up the value in column A and looking for a
match in column B or C depending on whether it's a medium or a large
rug. If there is not an exact match, in other words if the rug description
being ordered is not a direct match for one of the rug descriptions in
column A then a #N/A error will be returned. If the rug size isn't one of the
two options it will default to the large size because of the way the IF
function is written.

Converting formulas to values

If you have a range of formulas that will always produce the same result
(that is, dead formulas), you may want to convert them to values. If, say,
range A1:A20 contains formulas that have calculated results that will
never change — or that you don’t want to change. For example, if you use
the RANDBETWEEN function to create a set of random numbers and you
don’t want Excel to recalculate those random numbers each time you
press Enter, you can convert the formulas to values.

Just follow these steps:

1. Select A1:A20.
2. Choose Home ➪ Clipboard ➪ Copy (or press Ctrl+C).
3. Choose Home ➪ Clipboard ➪ Paste Values (V).
4. Press Esc to cancel Copy mode.
 Usage notes

VLOOKUP searches for a value in the first column of a table. At the match
row, it retrieves a value from the specified column.

Use VLOOKUP when lookup values are located in the first column of a table
with information organized vertically. Use HLOOKUP when lookup values
are located in the first row of a table, and each "record" appears in a new
column.

• Range_lookup controls whether value needs to match exactly or not.

The default is TRUE = allow non-exact match.

• Set range_lookup to FALSE to require an exact match and TRUE to allow

a non-exact match.

• If range_lookup is TRUE (the default setting), a non-exact match will

cause the VLOOKUP function to match the nearest value in the table that
is still less than value.

• When range_lookup is omitted, the VLOOKUP function will allow a

nonexact match, but it will use an exact match if one exists.

• If range_lookup is TRUE (the default setting) make sure that lookup

values in the first row of the table are sorted in ascending order.
Otherwise, VLOOKUP may return an incorrect or unexpected value.

• If range_lookup is FALSE (require exact match), values in the first column

of table do not need to be sorted.

26. Data Validation

What is Data Validation?

In Excel, the data validation feature helps you control what can be entered
in your worksheet. For example, you can:

• Create a drop down list of items in a cell

• Restrict entries, such as a date range or whole numbers only
• Create custom rules for what can be entered
• Create a prompt message explaining the kind of data allowed in a
cell.
• Create messages that appear when incorrect data has been entered.
• Set a range of numeric values that can be entered in a cell.
• Determine if an entry is valid based on calculation in another cell.
In practical circumstances you may want to be assured that your user will
enter one of the
listed rug
descriptions and
sizes. You could do
this using a Data
Validation List. To do
this, select the cells
in which the user
can place
their answer
such as column
A or B, and choose
Data > Data
Validation >
Data Validation
and from the
Settings option
choose List. In the
Source area, click
and then drag over
cells A2 to A4 which are the list of rug descriptions. Click Ok.

You can also create data validation lists to enter the value M or L as the
rug size using the data from cells B1:C1 as the source data for the list.

Now when the user goes to enter a rug style they can select an item from
the dropdown list. This ensures that they will always spell the name
correctly so that they will always choose an item that is in the list. It also
means that if they change their selection and choose a different rug, the
vlookup function will recalculate automatically and produce the desired
cost.

27. Pivot Table

What is a Pivot Table in Excel?

A Pivot Table gathers all the data in an Excel spreadsheet (or range of a
spreadsheet) and presents a summary of this data in a table. This table
allows you to see, at a glance, information such as:
1. The number of items of each type;
2. The sum of a data column, broken down into data types;
3. The average of a data column, broken down into data types.
This is best explained by way of an example...
The spreadsheet below lists every sale that was made by a company
during the first quarter of 2016. The spreadsheet records the sale date,
the invoice reference, the invoice total, the name of the sales
representative, and the sales region.
A B C D E
1 Date Invoice Ref Amount Sales Rep. Region
2 01/01/2016 2016-0001 $819 Barnes North
3 01/01/2016 2016-0002 $456 Brown South
4 01/01/2016 2016-0003 $538 Jones South
5 01/01/2016 2016-0004 $1,009 Barnes North
6 01/02/2016 2016-0005 $486 Jones South
7 01/02/2016 2016-0006 $948 Smith North
8 01/02/2016 2016-0007 $740 Barnes North
9 01/03/2016 2016-0008 $543 Smith North
10 01/03/2016 2016-0009 $820 Brown South
. . . . .
. . . . .
11 . . . . .
An Excel pivot table can summarise the data
in the above spreadsheet for easy analysis.
For example, the pivot table on
the right shows the total sum of all sales,
for each of the four sales people.
A more complex pivot table is shown below. In this pivot table, the sales
totals are broken down by month for each sales person. The sales totals
for each area are also shown.

A further feature of Excel pivot tables is the ability to quickly extract the
data from any part of the pivot table.
For example, if you wanted to see a list of Brown's sales during January
2016, you would simply use the mouse to double click on the cell showing
this value (the value $28,741 in the above example Pivot Table).

Excel then creates the new worksheet (shown below), listing Brown's sales
during January 2016.

Create an Excel Pivot Table

For this pivot table example we use the spreadsheet below, which lists a
company's sales figures during the first quarter of 2016. The spreadsheet
records the sale date, the invoice reference, the invoice total, the name of
the sales representative, and the sales region.
A B C D E
1 Date Invoice Ref Amount Sales Rep. Region
2 01/01/2016 2016-0001 $819 Barnes North
3 01/01/2016 2016-0002 $456 Brown South
4 01/01/2016 2016-0003 $538 Jones South
5 01/01/2016 2016-0004 $1,009 Barnes North
6 01/02/2016 2016-0005 $486 Jones South
7 01/02/2016 2016-0006 $948 Smith North
8 01/02/2016 2016-0007 $740 Barnes North
9 01/03/2016 2016-0008 $543 Smith North
10 01/03/2016 2016-0009 $820 Brown South
. . . . .
. . . . .
11 . . . . .
We will first create a very simple pivot table, which shows the total sales
for each of the four sales reps in the above spreadsheet. To do this:
1. Select any cell within the data range or select the entire data range
to be used in your Pivot Table.
Note: If you select a single cell in the data range, Excel will automatically
identify, and select the whole data range for your Pivot Table.
2. Click on the Pivot Table
button, which is located
within the 'Tables' grouping,
on the 'Insert' tab of the
Excel ribbon.

3. You will be presented with the 'Create PivotTable' dialog box (shown
on the right).
Make sure that the selected range refers to the range of cells that you
want to use for your Pivot Table.

There is also an option asking where you want the Pivot Table to be
placed.
This allows you to place your pivot table in a specified worksheet.
If you are not sure, select the option 'New worksheet'.
Click OK.
 4. You will now be presented with an empty Pivot Table, and the 'Pivot
Table Field List' task pane, which
contains several data fields. Note that
these are the column headers from your
data spreadsheet.
Within the 'Pivot Table Field List' task pane:

• Drag the 'Sales Rep.' field into the area

marked 'Row Labels' (or 'Rows');
• Drag the 'Amount' field into the area
marked 'Σ Values';
• Check: Make sure that the value in the 'Σ
Values' section reads "Sum of
Amount" and not "Count of Amount".

5. Your Pivot Table will be populated with the total sales for each sales
rep, as shown on the right.
Pivot Table before Formatting: Pivot Table after Currency

If you want the sales totals to be displayed as currency values, this is

done by formatting the cells containing these values.

The easiest way to do this is to highlight the cells to be formatted and

then to click on the currency format button, which is found in the 'Number'
group on the 'Home' tab of the Excel ribbon (see below).

The resulting Pivot Table will be as shown on the right.

Note that the default currency format used will depend on the settings on
your computer.

Group A Pivot Table in Excel

When analysing data in an Excel Pivot Table, it is often useful to group the
Pivot Table data into categories.

For example, instead of displaying total sales values for each day, you
might prefer to group the days into months and display the total sales
values for each month.

Excel can automatically group numeric values (including dates & times) in
pivot tables. This is shown in the following examples.

Example 1: Group a Pivot Table by Date

Imagine you have created the pivot table on

the left, which shows sales figures for each
date of the first quarter of 2016.
If you want to group the sales figures by month, you can do this as
follows:

Right click on the left column of the pivot table (containing the dates)
and select the option Group...;
You will be presented with the 'Grouping' dialog box for dates;

Select the value Months and click OK.

This will group the figures by month, as shown in the pivot table below.

Example 2: Group a Pivot Table by Range

Imagine you have created the pivot table on the left, which groups 150
children according to age. The ages range from 5 to 16 years.

If you want to group the ages into the age ranges 5-8 years, 9-12 years
and 13-16 years, you can this as follows:
 • Right click on left column of the
pivot table (containing the ages)
and select the option Group...;
• You will be presented with the
'Grouping' dialog box for numbers;

• Excel will automatically enter the

minimum and maximum values from the data, (which in this
case are of 5 and 16);
• We want to group the ages into 4-year periods, so we change the
value in the lower box (labelled By:) to a 4;  Click OK.
This will group the ages into categories, beginning with age 5-8 and
increasing by 4 years each time. The resulting pivot table is shown below:

Ungrouping a Pivot Table

To ungroup the values in a pivot table, simply:

• Right click on left column of the pivot table (containing the grouped
values);
• Select the option Ungroup...

Common Pivot Table Grouping Error

Pivot Table Grouping Error: Cannot Group That Selection

If you attempt to group a pivot table, but find that the Group... option is
greyed out, or that a message box pops up, saying "Cannot Group That
Selection", this is usually because the data column in the original data
sheet contains one or more non-numeric values (or non-date values), or
errors.
In order to fix this, you will need to return to your original data sheet and
amend the non-numeric or non-date values.

Once you have done this, right click on the pivot table and select Refresh.
This will update the values in the pivot table, and you should now be able
to group the selected row or column values.

28. Using Excel Auditing Tools

Excel includes a number of tools that can help you track down formula
errors. This section describes the auditing tools built in to Excel.

Viewing formulas

You can become familiar with an unfamiliar workbook by displaying the

formulas rather than the results of the formulas. To toggle the display of
formulas, choose Formulas ➪ Formula Auditing ➪ Show Formulas. You
may want to create a second window for the workbook before issuing this
command. This way, you can see the formulas in one window and the
results of the formula in the other window. Choose View ➪ Window ➪ New
Window to open a new window.

Tip

You can also press Ctrl+` (the accent grave key, typically located above
the
Tab key) to toggle between Formula view and Normal view. N

Tracing cell relationships

To understand how to trace cell relationships, you need to familiarize

yourself with the following two concepts:

l Cell precedents: Applicable only to cells that contain a formula, a formula

cell’s precedents are all the cells that contribute to the formula’s result. A
direct precedent is a cell that you use directly in the formula. An indirect
precedent is a cell that isn’t used directly in the formula but is used by a
cell that you refer to in the formula.

l Cell dependents: These formula cells depend upon a particular cell. A

cell’s dependents consist of all formula cells that use the cell. Again, the
formula cell can be a direct dependent or an indirect dependent.

For example, consider this simple formula entered into cell A4:

=SUM(A1:A3)

Cell A4 has three precedent cells (A1, A2, and A3), which are all direct
precedents. Cells A1, A2, and A3 each have a dependent cell (cell A4), and
they’re all direct dependents.

Identifying cell precedents for a formula cell often sheds light on why the
formula isn’t working correctly. Conversely, knowing which formula cells
depend on a particular cell is also helpful. For example, if you’re about to
delete a formula, you may want to check whether it has any dependents.

Identifying precedents

You can identify cells used by a formula in the active cell in a number of
ways:

• Press F2. The cells that are used directly by the formula are outlined
in color, and the color corresponds to the cell reference in the
formula. This technique is limited to identifying cells on the same
sheet as the formula.
• Display the Go to Special dialog box. (Choose Home ➪ Editing ➪ Find
& Select ➪ Go to Special.) Select the Precedents option and then
select either Direct Only (for direct precedents only) or All Levels (for
direct and indirect precedents). Click OK, and Excel selects the
precedent cells for the formula. This technique is limited to
identifying cells on the same sheet as the formula.
• Press Ctrl+[. This selects all direct precedent cells on the active
sheet.
• Press Ctrl+Shift+{. This selects all precedent cells (direct and
indirect) on the active sheet.
• Choose Formulas ➪ Formula Auditing ➪ Trace Precedents. Excel will
draw arrows to indicate the cell’s precedents. Click this button
multiple times to see additional levels of precedents. Choose
Formulas ➪ Formula Auditing ➪ Remove Arrows to hide the arrows.
Tracing error values

If a formula displays an error value, Excel can help you identify the cell
that is causing that error value. An error in one cell is often the result of an
error in a precedent cell. Activate a cell that contains an error value and
then choose Formulas ➪ Formula Auditing ➪ Error Checking ➪ Trace Error.
Excel draws arrows to indicate the error source.

Fixing circular reference errors

If you accidentally create a circular reference formula, Excel displays a

warning message —Circular Reference — with the cell address, in the
status bar, and also draws arrows on the worksheet to help you identify
the problem. If you can’t figure out the source of the problem, choose
Formulas
➪ Formula Auditing ➪ Error Checking ➪ Circular References. This
command displays a list of all cells that are involved in the circular
references. Start by selecting the first cell listed and then work your way
down the list until you figure out the problem.

Using the Excel Formula Evaluator

Formula Evaluator lets you see the various parts of a nested formula
evaluated in the order that the formula is calculated. To use Formula
Evaluator, select the cell that contains the formula and then choose
Formula ➪ Formula Auditing ➪ Evaluate Formula to display the Evaluate
Formula dialog box
Excel Error Values

29. Comments in Excel

Creating comments in cells can be helpful while using Microsoft Excel.
Comments help users who are not the original creator to get additional
information about the cell, such as what its value represents.

What a comment can do in Excel?

By annotating a worksheet with comments, you can attack additional

information to a cell to describe the cell for convenient reading and
understanding. For example:

1. Ask questions about the data with comments

2. Leave your feedback in excel document from another person
3. Describe certain value in cells of Excel
4. Explain formulas to other users

How to Add Comments to Cell in Excel

Text and picture are two different kinds of content in comment. The way to
add them is also different. So now let’s see how to create the two kinds of
comments for cell in Excel (2010).

Text comment:
Step 1: Select the cell that you want to add comment.
Step 2: Add comment to cell with three ways.
1. Go to the Review tab, and click on New Comment icon in Comments
group.
2. Right click the cell and select Insert Comment option from the context
menu.
3. Press Shift + F2 to easily add comment to cell.

Now you can edit the comment, including changing default user name
and entering content in the comment textbox. Or later, you can select the
comment and click Edit Comment button to begin to change the
comment.

How to Show/Hide Comments to Cell in Excel

If there are lots of comments having been created in Excel, you have
several choices to display one or all of the comments in Excel.

Option 1: Show comments one by one in Excel (Previous,

Next)

With Previous and Next button in Review tab and Comments session,
you can view the comments one by one in the Excel.
Until all the comments are showed, it would give you a warning message
about that you have seen all of the comments.

Option 2: Show or Hide All Comments at once in Excel

If you decided to
show all the
comments
instantly in
Excel, Show
Comments
optio n in Excel
tab would help
you to do
that easily.
Navigate
to Show All
Comment locati
on in Excel, and click it. All comments would display for cells and
you can see all of them.
Option 3: Show or Hide Comment with command “Show/Hide
Comment”

1. Right-click the cell that has comment and select Show/Hide

Comments from context menu.
2. Instantly all the comments in Excel are displayed and hidden.

Surely, if you want to edit comment for cell, you can select the cell and
click “Edit Comment“. Or if you want to delete comment, just choose the
cell has the comment, and click “Delete“.

30. Working with Tables in Excel 2010

A table is a range of cells that hold data, with each row corresponding to a
single occurrence of an entity. When you create a table, you can manage
and analyze the data in that table independently of data outside the table.
You can format the table, filter the table columns and also publish a table
to a server that is running Windows SharePoint Services 3.0 or Windows
SharePoint Services “4″. Usually there is an initial row of text headings
that describe the columns of data. For example, you might have rows and
rows of employee data with the following column headings:

• First Name
• Last Name
• Age
 • Salary

Each row in the table would hold the above details for a particular
employee. But as Excel 2010 handles tabular data perfectly well by
default, why would you want to put your data into a table? Well, tables
allow Excel to respond more appropriately to your data if it is held in a a
table. For example, if you create a chart based on the data in a table, the
chart will expand as you add more rows to that table.

Creating Tables In Excel 2010

It’s quite easy to create a table in Excel 2010; you simply make a cell
active that is in the range of cells you want to convert into a table and
then click Insert > Tables > Table. If your data has headers, make sure to
check the My table has headers box in the create table window, and then
those headers will be used. The good thing is that you don’t need to select
the entire range of cells: Excel can identify the limits of the data and also
identify the headings.

The following is an example of tabular data before we convert it into a

table.

After we convert it to a table, it looks like this:

If you create a table this way, it is given default formatting that you can
change using the Table Styles on the Design tab that appears when the
table is selected. Alternatively, you can select a style when you create the
table. To do this, activate any cell in the range you want to convert and
click Home > Styles > Format as Table. Choose a style from the panel that
opens and your table will be created with that style.

Properties of Excel Tables

The main things to notice about tables are:

• When any cell in the table becomes active, the Table Tools
contextual tab is displayed.
• Each column heading contains a drop down list that you can use to
sort or filter the data in that column.
• If you scroll so far down the table that the column headings
disappear, the letters that mark the column headings in the
worksheet are replaced with the table’s column headings.
• The cells have background colours to enhance readability.
• Background colours are applied to the cells to help readability.

Deleting an Excel Table

If you later decide that you’d prefer your data in its original form (i.e. not
in a table), you can convert it back to a range of cells. Click anywhere in
the table and then on the Table Tools tab, click Design > Tools > Convert
To Range. Once the data is converted back to a range, the table features
are no longer available.

Note that the formatting that was applied to the table is still present.
If you want to delete the table and all its data, select the table and press
the delete key.

31. Excel 2010 Macros

Excel macros are useful if you find yourself repeating the same actions
time after time. When you create a macro, you record all the actions and
commands you perform to complete a task. Then instead of repeating
those actions you can simply run the macro. Doing this saves you time
and reduces the probability of making mistakes.

There are two ways you can create a macro:

• Use the Macro editor

• Use the Excel macro recorder

Using the macro recorder is by far the easiest method, though you can
create some quite complex macros with the macro editor. The problem
with the macro editor is that it requires some technical expertise
(otherwise known as programming!) to use it effectively.

32. Previewing and Printing

Printing the Worksheet

Page setup Margins

In excel, we can also set the

margins for printing the
page. To customize the
margin setting click on small
arrow icon, this is right corner
of page setup group in Page
Margins
Layout ribbon
Orientation
Next command in Page
setup group is Orientation
which allows us to change
the page orientation which
can be portrait or landscape.
Orientation

Size

Third command in Page

Setup group is Size which
allows us to change the size

Size
of the page. There is various size of page for e.g. we normal use Letter
size page for printing or A4 size so, to fix the size of the page this
command will use.

Printing of Worksheet in Multiple Pages

Breaks
This option allows us to
break our long worksheet
in multiple sheets. This is
inserting page break.
Breaks

Repeating Rows and Columns for Multiple Pages

Print Area

This Print Area allows us to print same row and column to print in multiple
page spreadsheets.

Print Titles
Previewing Worksheet

In excel, we just have worksheet not the pages so cannot make out how
the print will come and how much data will get printed on a page. So,
before giving print in excel we must see the preview of the sheet so that
before giving it to print we can do last min changes. Here, excel has gave
us PrintPreview options so that we can see how much data will print on a
page. File Print Print Preview