Excal - Data Sort and Filter

Sort data in a list

• Excel makes it easy to sort a list in ascending or descending order based on any field(s)
in the list.
• The fields selected on which to sort are called the sort fields or the sort keys.
• You may choose to sort the data on a single field or on a collection of fields.

• go to cell in range you want to sort

• Data menu  Sort

Sort using multiple keys

(sort by field 1, then by field 2, then by field 3)

• specify first field to sort by and ascending/descending

1
 • specify second field (if any) to sort by and ascending/descending
• specify third field (if any) to sort by and ascending/descending

Multiple Sort Levels

1. Note that the header row (titles) are not part of the sort
2. Note that the data within a record is kept together (does not sort just one column
independent of other fields in that record)

2
 3. Can indicate if no header row (no titles)

Click Options button on Sort dialog box to access sort options

• Case sensitivity

• Sorting top to bottom or left to right

FILTER

• Filtering data in Excel hides the rows of a list that DO NOT meet a specified
criteria
• Up to two separate criterion can be specified for each field (column)

Data Filter

• AutoFilter
– simple criteria

3
 • Advanced Filter
– complex criteria

 Excel will examine data in fields and will create a filter to allow you to show only
specified data

 Tells you it has done this by putting little arrows in field names

Arrows bring list boxes for you to specify filter criterion

AutoFilter selects and shows you only the data you want to see in Microsoft Excel

AutoFilter Choices

Will ALWAYS see at least 6 choices

• All--shows all records

• Top 10--filter specified amount of numerical data by percent or number of
items
• Custom--specify multiple selection conditions in an AND or OR relationship
• Exact values--will show the various unique value(s) your field contains
4
 • Blanks--shows all records without an entry
• NonBlanks--shows all records with an entry
•
Types of Filters

• Exact Matches
• Comparative
– Less Than, Greater Than or Equal To, etc
• Compound
– matches this OR matches that
– matches this AND matches that
• Wildcards
– The ?
– The *

When choose certain criterion from this list, only those records matching that
criterion are shown (others are hidden, NOT deleted)

To display all the items in the column removing any

previously specified filtering for the column

To display the
top or bottom
n items

To specify
more complex
criteria

To display one
type of crop

5
AUTO FILTER

Filtering selects just the data we need and hides all the rest. Filtering doesn't change our
data in any way. As soon as we remove the filter, all our data reappears, exactly the same
as it was before.
 To remove filter, select All from the filter list
 To display all records, Data  Filter  Show All

Filtering Filters
Can successively use filters to filter filtered data

• i.e. can filter by one field then filter that result by another, different field

Advanced Filter

 Extends AutoFilter in two important ways:

 Create more complex criteria, such as the ability to set OR conditions
 Copy the rows to another section on the worksheet, leaving the original list
intact
 Requires the use of a criteria range
 An area of the worksheet containing the criteria that must be met
 Must contain at least two rows
6
  First row contains field names
 One or more rows containing values
 Text entries treated as though they were followed by a wildcard (*)
 Relational operators can be used to find a designated range
 Requires the use of a copy to range
 A row contains field names
Criteria Operators
 > greater than
 < less than
 >= greater than or equal to
 <= less than or equal to
 <> not equal to

7
The Subtotal dialog box

 Subtotals command in the Data menu computes subtotals based on data groups
 List needs to be sorted on the field(s) you want to base subtotals on
 Uses a summary function like SUM or AVERAGE
 Outline format allows for several views of data

8
 Make sure only
the items we want
to summarize are
checked

Option for printing each

subtotalled group in a separate
page

An Excel Datasheet with totals added

9
 Level outline
buttons

Click to hide
or display
individual
records with
subtotals

Sub totals
appear after
each district

Use the subtotals outline view

• Sometimes, it might be more beneficial to view the summary information only.

• We can do this by displaying the data in Subtotals Outline View.
• We may choose from Level 1, Level 2, and Level 3 outline view.

There are three types of controls in the outline section:

 Hide detail buttons When the rows in a group are visible, a hide detail
button appears next to the group.
 Show detail buttons When you hide a group of rows, the button next to the group
changes to a show detail button . Clicking a show detail button restores the rows in that
group to the worksheet.
 Level buttons Each of the numbered level buttons represents a level of
organization in a worksheet; clicking a level button hides all levels of detail below that of
the button you clicked.

The following table identifies the three levels of organization in the previous graphic.
LEVEL DESCRIPTION
1 The grand total
2 Subtotals for each group [as sheet shown below]

10
3 Individual rows in the worksheet [as sheet shown above]

What is Data Validation?

Data validation is a tool that helps you control the kind of information that is entered in your
worksheet. With data validation, you can:

--provide users with a list of choices

--restrict entries to a specific type or size
--create custom settings

Note: Data validation is not foolproof. It can be circumvented by pasting data into the cell, or by
choosing Edit|Clear|ClearAll

Provide a Drop-down List of Options

Use Data Validation to create a dropdown list of options in a cell. List items can be typed in a
row or column on a worksheet, or typed directly into the Data Validation dialog box.

Create a List of Items

If the list of options is more than a couple of items, it will be easier to maintain if you type the
list on a worksheet. The list can be entered on the sheet that will contain the dropdown lists, or on a
different sheet. In this example, the list will be stored on a sheet named Lists

11
Name the List Range

If you type the items on a worksheet, and name the range you can refer to the list from any
worksheet in the same workbook.

1. Select the cells in the list.

2. Click in the Name box, to the left of the formula bar
3. Type a one-word name for the list, e.g. District
4. Press the Enter key.

Name Box

Data validation list

In single row or column, type the entries you want to see in the drop-down list. (Note: The list
must be in a single block of cells -- e.g. you can use A2:A6, but not A2, A4, A6, A8.)

Apply the Data Validation

1. Select the cells in which you want to apply data validation

2. From the Data menu, choose Validation.
3. From the Allow drop-down list, choose List
4. In the Source box, type an equal sign and the list name, for example: =District
5. Click OK.

12
To select a range name, instead of typing it:

1. In the Data Validation dialog box, under Allow, select List

2. Click in the Source box, and on the keyboard, press the F3 key
3. From the Paste Name list, select a named range, and click OK.
4. Click OK, to close the Data Validation dialog box.

13
Using a Delimited List

Instead of referring to a list of items on the worksheet, you can type the list in the Source
box, separated by commas. For example:

Kancheepuram,Villupuram,Cuddalure

Note: This method of Data Validation is case sensitive -- if a user types KANCHEEPURAM,
an error alert will be displayed.

Allow Entries that are not in the List

To allow users to type items that are not in the list., turn off the Error Alert.

14
Protect the List

To protect the list from accidental damage, if you have entered it on a different worksheet, you
can hide that sheet.

1. Select the sheet that contains the list

2. Choose Format | Sheet | Hide

Input Message
1. Click on the Input Message tab to activate it
2. Add a check mark to the Show input message when cell is selected box.

3. Type your message heading text in the Title box. This text will appear in bold print at
the top of the message.
4. Type your message in the Input message box.
5. Click OK or follow the steps below to add an Error Alert.

Error Alert
1. Click on the Error Alert tab to activate it
2. Add a check mark to the Show error alert after invalid data is entered box.

15
 3. Choose an Error Alert Style from the dropdown list

1. Stop: This prevents the entry of invalid data.

1. If the Retry button is clicked, the invalid entry is highlighted, and can be overtyped.

2. If the Cancel button is clicked, the invalid entry is deleted, and the cell's original
content is restored.

The user cannot leave the invalid entry in the cell

2. Warning: This discourages the entry of invalid data.

16
 1. If the Yes button is clicked, the invalid entry is accepted, and the next cell is
selected.
2. If the No button is clicked, the invalid entry is highlighted, and can be overtyped.
3. If the Cancel button is clicked, the invalid entry is deleted, and the cell's original
content is restored.
The user can choose to leave the invalid entry in the cell.

3. Information: This announces the entry of invalid data.

1. If the OK button is clicked, the invalid entry is accepted, and the next cell is selected.
2. If the Cancel button is clicked, the invalid entry is deleted,
3. and the cell's original content is restored.
The user can choose to leave the invalid entry in the cell.

4. Type your message heading text in the Title box. This text will appear in bold print
at the top of the message.
5. Type your message in the Error message box.
6. Click OK

Note: If the Office Assistant is visible, the Input Messages and Error Messages will be
displayed as balloons

17
Functions
Function is a small programme or codes written for an action / calculation through
arguments and abbreviated to a word
Functions in Excel are placed in the ribbon under the tab FORMULAS. Five groups of
commands are available under this tab. They are
 Function Library
 Defined Names
 Formula auditing
 Calculations

The often used group in the ribbon is Function Library. The other groups are used for
advanced calculations .Functions are grouped as follows

 Math &Trig
 Logical
 Statistical
 Text
 Financial
 Date and time
 Engineering
 Lookup and references
 Database
 Information
 User Defined
 Cube

The common format of a function is

Function name (argument1, argument2)
Note arguments are separated by comas. .Usually arguments are cell addresses. It
differs according to the calculations involved.
How to call function in excel sheet.
Step1: Place the cursor in the cell where the result is to be entered.
Step2: Enter = sign in that cell
Step3: Enter the name of the function – sum,sqrt etc.
Step4: Enter open bracket (
Step5: Enter the cell addresses or cell address as required for the function to
caculate
Step6:Enter close bracket )
Step7: Press Enter key

Another way of calling a function in MS Excell sheet

Step1: Place the cursor in the cell where the result is to be entered.
Step2: Left click formulas tab
18
 Step3:From function library group left click insert function which will show a
small Step4: window. Note = sign is automatically entered in the cell.
Step5: Under the heading select a categoryselect group of function required for
the calculation.
Step6: This will display the list of functions available under this group.
Step7: Choose the required function which will open an interactive window
Step8: Fill up the interactive window. It will show the result
1. Press OK button the result will be transferred to the cell.
Mathematical functions

SUM(number1,number2….)
Sum is the function name. Number1,Number2 mentioned within bracket is arguments
for this function, This arguments can be actual numbers as 8,10,14 or cell address as
B3,B4,B5,B6,B7. Or range of cells as B3:B7.
This function adds all the values mentioned within the brackets.
MAX(number1,number2,,,)
This function finds the highest value from the given list of values and places in the cell
where this function is entered.
Max(12,30,15,20,5) – indivual numbers are entered separated by coma(,). The result is
30
Max(B2,B5,B10,B15) – cell address is entered separated by coma(,). Here the
content of the cell which has uppermost value among the given cell address is
placed in the cell.
Note:- The cell address is not entered
MAX(B2:B15) – Range of cell address is used separated by colon (:). The content of
the cell address which highest value is placed in the cell.

List of
functions for
each category

Select one of
Excel’s 10
function
categories

MIN(number1,number)
This function finds the least value from the given list of values and places in the cell
where this function is entered
Min(12,30,15,20,5) – indivual numbers are entered separated by coma(,). The result is
5

19
Min(C2,C4,C5,C9,C10) -- cell address is entered separated by coma(,). The content of
the cell which has lowest value among the given cell address is placed in the cell
Min(C10:C20)-- – Range of cell address is used separated by colon (:). The content of
the cell address which minimum value is placed in the cell.

COUNT()
This function counts the numeric inputs
COUNT(12,3,5,68) -- indivual numbers are entered separated by coma(,). The result is
4.
Note:-the values are not summed. It counts the numeric values.
COUNT(C12,C3,C5,C68)-- cell address is entered separated by coma(,). The result is
4 if only the cells content are numeric else the result is 0
COUNT(D1:D7)-- Range of cell address is used separated by colon (:). The result is 7
if only the cells content are numeric else it excludes those cells that are not numeric.

COUNTA()
This function counts non-numeric inputs. Excludes blank inputs. It means the inputs
can be text, numeric, and date.
COUNTA( A2:A20) – the result will be 19, provided there is no blank cell.

SUMIF( range, criteria,[sum_range])

This function has three arguments.
Range: range of cells (Column) that is to be considered for selecting criteria
Criteria : Enter the criteria as cell address – A2- or the content of the cell address –
“THANJAVUR” within quotes --
Sum_range: The range of cells to be summed

Function sum(E2: E10) adds the cell content starting from E2 to E10 and the result
223800 is placed in cell E11.
 Function sumif(A2:A10,A2,E2:E10) adds the cell values corresponding to
THANJAVUR. A2:A10 is the range from which the criteria is to be selected.
 A2 is the criteria, meaning that the content the cell A2 is the criteria. The content
of the cell A2 is thanjavur.
 E2:E10 the actual cell values to be added.
 In the range A2:A10 thanjavur appears twice. In Cell A2 and in cell A10. The
corresponding values in column E is 24800 and 25000. The result 49800 is
placed in cell E12.

20
Round(number,num_digits)
This function has two arguments.
 Number : the number with decimals
 Num_digits : specify the number of places to be rounded such as 2 or 3 or 1
Roundup(number,num_digits)
This function has two arguments.
 Number : the number with decimals
 Num_digits : specify the number of places to be rounded such as 2 or 3 or 1.
 This function round to next higher value even though the adjacent value is less
than 5.
Rounddown(number,num_digits)
This function has two arguments.
 Number : the number with decimals
 Num_digits : specify the number of places to be rounded such as 2 or 3 or 1.
 This function do not round to next higher value even though the adjacent value
is greater than 5.

Rank(number, ref, order)

This function has three arguments.
Number : the cell value for which rank is to be calculated
Ref : range of cells or list of numbers from which the rank is to be calculated
Order : two options are given - descending / ascending. The range of cells are
arranged in descending or ascending order at the background and based on
that order rank is assigned to the cell value we have selected.

21
Logical Function
If (logical_test,value_if_true,value_if_false)
This function has three argument
Logical test: a condition that is evaluated as TRUE or FALSE
Value_if_true : What to do if the evaluation is TRUE. It can be text or numeric or result
of a calculation
Value_if_false : What to do if the evaluation is FALSE. It can be text or numeric or
result of a calculation

If( AND(logical1,logical2),Value-if_true,value_if_false)
 In this function two logical tests are carried out.
 If both the logical test are true then – value-if-true is carried out
 If any one of the logical test is true – value-if-false is carried out

This function evaluates the logical arguments. If evaluations of all the logical
arguments are true then the result will be TRUE. If any of the evaluation of the logical
argument is false then the result will be FALSE.

If( OR(Logical 1, logical 2),value_if_true,value_if_false)

 In this function two logical tests are carried out.
 If anyone the logical test are true then – value-if-true is carried out
 If both the logical test is false – value-if-false is carried out

This function evaluates the logical arguments. If any one of the evaluations is true the
result will be TRUE. If all the evaluation of the logical argument is false then the result
will be FALSE.

22
 FINANCIAL FUNCTIONS
 Function – a predefined computational task
 Requires arguments
 Values the function uses to calculate answers
 Returns a value

USAGE OF FINANCIAL FUNCTIONS

PMT()

Calculates the payment for loan based on

 Amount financed
 Interest rate
 Number of PAYMENT periods

PMT(Rate, NPeriods, PresentValue, FutureValue, PaymentType)

 Rate is the interest rate for the loan.

 Nper is the total number of payments for the loan.
 Pv is the present value, or the total amount that a series of future payments is worth
now; also known as the principal.
 Fv is the future value, or a cash balance you want to attain after the last payment is
made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.
 Type is the number 0 (zero) or 1 and indicates when payments are due.

Using the PMT function

Amount financed
expressed as a
Interest rate negative number
divided by 12

23
 Comparison of periodic payments(EMI) during different payment periods
PMT PMT PMT PMT

Dream Flat 4,000,000.00 4,000,000.00 4,000,000.00 4,000,000.00

Annual
Interest 9.50% 9.50% 9.50% 9.50%
Term 12.00 120 180 240
Periodic
Payment 350,734.05 51759.02 41768.99 37285.25
Total
Payment 4,208,808.57 6,211,082.76 7,518,417.72 8,948,459.40
Total Interest
Paid 208,808.57 2,211,082.76 3,518,417.72 4,948,459.40

Amartisation table by using formulae

24
Inserting Functions:

On the top row of icons on the spreadsheet toolbar, click on the downward
tab next to the summation symbol (Σ) or the function symbol (fx), after
moving to a cell on the worksheet where you want to enter a function.

“down triangle”

25
 Click to search
for a function

Select one of
Excel’s 10 function
categories

328

80

58

53

List of functions for each category

26
The FV function

– Returns the future value of a series of payments

– For example, contributions to your EMI
– Based on:
– Installment Number
– Rate of Interest
– EMI or PMT
– Present Value
– Type of payment

Returns the future value of an investment based on periodic and constant payments and a
constant interest rate.

FV(Rate, Periods, Payment, PresentValue, PaymentType)

INVESTMENT FOR A PERIOD OF 20 YEARS

Monthly Deposit 1000

[Link] 8%
Term 240
Present Value 0
Type 1
(Rs. 592,947.22)

INVESTMENT FOR A PERIOD OF 20 YEARS

Monthly Deposit 1000

[Link] 0.08
Term 240
27
Present Value 0
Type 1
=FV(B3/12,B4,B2,0,1)

 Rate is the interest rate per period.

 Type is the number 0 or 1 and indicates when payments are due. If type is omitted, it is
assumed to be 0.
IPMT()

The IPMT() function can help you to calculate the amount of interest that the lending
institution would earn during a certain period.

IPMT(Rate, Period, NPeriods, PresentValue, FutureValue, PaymentType)

To compute Interest Amount during the 10th Instalment:

Rs. 8,199.76

PPMT()

The PPMT() function calculates the actual amount that applies to the balance of the loan.
This is referred to as the principal

PPMT(Rate, Period, NPeriods, PresentValue, FutureValue, PaymentType)

To compute Principal Amount during the 10th Instalment:

=PPMT(0.095/12,10,12,-4000000)

Rs. 342,534.28

28
 PIVOT TABLE
Pivot table: Developing Tables from Excel Data base.
Excel Data Base – First the let us under stand what is data base and then excel data base.
Data base – Data base is a structure in which data are stored. We can retrieve data, edit data,
update data, or delete data from this data base. In any data base data are stored in Fields. The
type data that can be stored in a field is Alpha, numeric, Alpha numeric, date and images. A
data stored in field / fields relating an incident or an item or about a person is called a record. A
set of records storing similar data in field / fields is called a Data File. Many data base soft
wares are available in the market. For example Dbase, My SQL, FoxPro, Oracle, DB2, MS
Access.
Data can be stored in Excel in a similar method used in data base soft wares. In Excel
data base Columns are considered as fields and data are stored in it. The type of data stored in
each column defines the data type of the column. Each row is considered as record
How to store data in Excel data base: The first row of a Excel sheet is allotted for entering
field name. The first row means A1,B1,C1,D1….etc.

29
Field names are entered in the first row. In the subsequent rows data are entered. Each row is
called a record.

Now let us understand how to develop tables from Excel Data base using Pivot table.
This the sample Excel data base

30
Let us assume that we want to develop the following table using the above Excel database
1. District wise total expenditure on basic
2. District wise gender wise number of staff
3. District wise designation wise number of staff

Step 1: Go to insert tab

31
Step2
Press pivotTable and select PivotTable

When PivotTable is selected “create Pivot Table window appears. In the Table/Range select the
data range to be used for creating pivot [Link] A1 to A31.

32
Step 3 : select Existing Worksheet in the Create Pivot Table window. It will open the location
box

Step 4:
in the
location
box enter
the cell
address
where
the Pivot
Table is
to be
placed
(ie) A40.

It will
display
the
followin
g
window.

33
Pivot Table table area
Field list along with
Field to be specified for row
Field to be specified for coloumn
Field to be specified for Value

34
 Here the order of designation appears as Asst, JA and Sup because pivoted table uses ansi
codes in ascending order. Hence it has to changed as Sup, Asst, and JA . To solve this
Step 1. keep the cursor on cell ‘Sup’
Step 2. right click. A list of sub menus appears.
Step 3. In that select the submenu ‘Move’. It displays other submenus.
Step 4. Here select the Move ‘Sup’ to beginning ’. The ‘Sup’ column moves to the beginning
along with all the details and appears as shown below.

Step5: to create a table “District wise, designation wise number

of staff”. Drag the field “District Name” to the Row Labels
square, drag the field “designation” to Column Labels square
and drag the field “name” or “designation” to Value square.
Simultaneously the table is generated in the Pivot Table area

35
Here the order of Designation appears as Asst, JA, Sup because pivoted table uses ansi
codes in ascending order. Hence it has to changed as Sup, Asst, JA To solve this
Step 1. keep the cursor on cell “Sup”
Step 2. right click. A list of sub menus appears.
Step 3. In that select the submenu ‘Move’ ’. It displays other submenus.
Step 4. Here select the submenu ‘Move ‘Sup’ to beginning. The mSup column moves to
the beginning along with all the details and appears as shown below.

36
 DATA ANALYSES - POWER QUERY

A Database is required for Data analyses. Database can be developed using database
software like Dbase, FoxPro, Oracle, DB2, MS Access, MySql, PostgreSqL, SQLServer.
Though MS Excel is not a database software, can be used to create a Database. In Excel,
Columns are used to store data, whereas in database software Fields are used. Any FACTS in
the form of Quantities and Figures are DATA. Storing such facts in fields is called
TRADITIONAL Database In the current situation facts are in audio, video formats in addition
to structured and unstructured text format. These types of Data are called BIGDATA
Data analyses means retrieving information in a given format for the queries raised,
from the Database already constructed. Data analytics (DA) is the process of examining data
sets in order to draw conclusions about the information they contain, increasingly with the aid
of specialized systems and software. In Excel Data analyses is done using filtering, advance
filtering, sorting, Data validation, statistical and mathematical functions, Charts, Data Analysis,
Power Query and PowerPivot tools.
Power Query is available in Excel and Power BI Desktop. Power Query is one of the
data analyses tools available in MSExcel. It is a free add-in for Excel 2010, 2013. It is an inbuilt
37
feature in MSEXCEL2016 onwards. Power query is a data connection technology. It helps us to
get data from Web, Excel File, Databases like SQLserver, MSAccess, Azure, SharePoint etc.
Enables us to clean the data and transform the data as required.
Power query can be installed from, if it is not available in Excel Add-in,
[Link] . Before installing find out the
Excel version you are using and if 32bit or 64 bit.
Use the following steps to know the version and bits of Excel used
Step 1: Keep open Excel file
Step 2: Click the File button.
Step 3: Click the Account option on the left side [Link] Excel version will be listed under
the Subscription Product section.
Step 4: Click the About Excel button. The bit version (32-bit or 64-bit) will be listed in the first
row of the pop-up window.

Power Query installation

The following are the installation steps for Excel 2010 and [Link] Excel2007 version Power
Query cannot be installed.
Step 1: Keep Excel completely closed (exit)
Step 2:Click the following link to download the page.
[Link]
Step 3:Click the Download button.
Step 4:Click the checkbox for the bit version you are using. Most likely you will be using 32-
bit.
Step 5: Click Next. The add-in installation file will download.
Step 6: Click the file to run the installation.
Step 7: The Setup Wizard window will open. Follow the steps to install Power Query.
Once the installation is complete, open Excel. We can now see the Power Query tab in the
Ribbon.
Let us assume that Power Query is installed but not available in the ribbon. Then use the
following steps
Step 1: Click File
Step 2: Select OPTIONS
Step 3: Excel options window opens.
Step 4: Press Add-in
Step 5: in MANAGE input window select COM Add-ins and press GO
Step 6: COM add-ins window opens. Check Microsoft Office Power Pivot for excel 2013. And
Check Microsoft Power Query for excel.
Now Power Query and Power Pivot Tabs appear in the ribbon.

USE OF POWER QUERY IN DATA ANALYSIS

Power Query is a data connection technology

38
Data can be imported from the above listed sources. They can be combined, and refined to meet
our analysis needs.

Using the above commands the data are opened in power query editor and cleaning – required
data – can be carried out. Then Append or Merge can be applied as required.

POWER QUERY APPEND

STEPS TO APPEND TABLES IN THE SAME WORKBOOK

Handloom sari sales. The number of saris sold by each sales person is given in tabular
format. Sari variety wise, sales persons wise number of saris sold is given in the table. This
table is constructed for each region - chennai, coimbatore, salem, and madurai. The
corresponding sheet names are che, cbe, slm, mdu. Price of each variety of sari is given in table
saritbl
Each column contains code number- 1 to 12 of the saris sold and region code.
Each row displays the names of the sales persons in each region. Each region comprises 25 sales
persons
Each cell has number of saris sold under each variety. A model data is presented below.

Our task is to append all the four region for analysis

Step 1: Convert all these data into TABLES.

Step 1.1: Select the range of data and Press ctrl+t

39
The above window opens. Check my table has header. Press OK.
Step 1.2 : DESIGN tab opens. Displays as shown below. Change the table name as desired.

Step 2 : Keep the cursor in A1 of ‘che’ sheet. Press Power Query tab.

Step 3 : Press From table/ Range.

The data table is opened in Power Query editor

Step 4: Press Close & Load. It opens two options. [Link] &Load, [Link] & Load to
Step 5 : Select the second option – Close &load to. It will open the following window. In that
window select the option only create connection. Check Add this data to the Data model.(
Data model means - a formal description of how data may be structured and accessed. It is very
useful when multiple data sets are to be appended or creating relational links )

40
Step 6 : repeat steps 1 to 5 for other three regions – cbe, slm, mdu. After the completion of
loading four regions, The following window appears.

Step 7 : Press Append. It will open a window with two option. Since there is more than two
tables to be appended, select the second option-- three or more tables. It will open the
following window.

41
Step 8 : add tables to Tables to append box in the order it is to be appended. Here region wise
appending is required. Press OK
`

Step 9 : The resultant table appear as shown above. The default name is APPEND1. This name
can be changed as is suitable. Com_sales is the new name given. Now Press Close &
Load. It opens two options. [Link] &Load, [Link] & Load to. Select option 2. It
specifies that it will load in TABLE format and it will be loaded in a new worksheet.

42
The appended table ‘Com_sales’ will be displayed in a new sheet – sheet5 as shown above. This
table has 100 rows. This table is ready for further analyses.

POWER QUERY – MERGE

AN ALTERNATE TO VLOOPUP UNCTION

We have to find out the sales performance of 24 sales persons selected from 4 region. The sale
of 3 varieties of the sari – Khadi Silk, Madurai Cotton, Meghalaya Cotton taken to study the
sales persons sales ability.
Usually VLookup function is applied. This function pickups the number of saris sold by the
selected sales persons from the appended region wise data table. As an alternative Power Query
is applied.
The data.

43
Step 1 : Convert the range of sales persons into table using ctrl+t. Name the table as sptbl –
sales performance table.
Step 2: keep the cursor in A1 of sptbl.
Step 3: Press From table/ Range. Power Query editor opens with the table sptbl.
Step 4: Press load to and select the second option. Load & Close to. In the displayed window
choose the option connection only

Step 5 : Press Merge in the Power Query tab. Opens Merge window with two portion appears.
It has two input box. One in upper portion and the other in lower portion. Click the
drop down. List of tables available is listed. Select the table

to be filled – sptbl – from the upper input box. In the lower input box select the table from
which the data is to be picked – Com_sales.
Step 6 : Select the Name column of sptbl. It will be darkened. Select the Name column of
Com_sales. It appears dark. Check whether the Join Kind is Left Outer. Press OK

44
The power query editor appears as follows

Step 7 : click the double arrow at the right most corner of the column Com_sales. The list of
columns available in Com_sales table appears. From the list select our required
colums- 4,7,11- which represents, Khadi Silk, Madurai Cotton, Meghalaya Cotton
saris. Press OK.

45
Step 8 : Click Load &Close and select Load & Close to . The opened window informs that the
result in table format is loaded in a new sheet. The default name of the table is
Merge1. This can be changed as required The final result is as displayed below.
Note : If the name in one appears in other regions, Then data for that name from that region will
be added in the list. Thus this table has more than 24 rows. It means that the name of
the selected sales person in region region1 appears in region 2 and region 3.

46
Creating Charts

Step1 : Create a Data table as shown below

Keep this cell
Beneficiaries BLANK
tvl 20400
kpm 22900
vel 24600
vip 32000
tvm 18300
cud 30500
try 25300

Step2 : Select the Data

Step3: Press INSERT tab
Step4: Under CHART group press column chart

Step5 : the following droup down appears when Column chart is clicked

47
step6 : Select first chart under 3-D column and left click the following chart appears.

Step7 : Click the + sign at the right side of the chart. It will display a list of chart
Element. The following will appear

Step8 : Axes, Chart Title, Gridlines are CHECKed. It means these actions are already
available in the chart. Now CHECK AXIS TITLES. It will insert two text boxes. One at
the bottom – X axis. Another at Y axis.

Step9 : select X-axis text box. It is highlighted. Now Keep the cursor at the formula bar
and type DISTRICT and press ENTER KEY. The data typed is automatically
transferred from formula bar to the X-asis text box.
48
Step10 : select Y-axis text box. It is highlighted. Now Keep the cursor at the formula
bar and type BENEFICIARIES’ IN NUMBERS and press ENTER KEY. The data
typed is automatically transferred from formula bar to the Y-asis text box.

The 2-D column, 3-D Column chart options are displayed. Further, More Column
Charts… option is also displayed.

Move through the Column Chart options to see the previews.

Click Clustered Column. The chart will be displayed in your worksheet.

49
Give a meaningful title to the chart by editing Chart Title.

Printing the Worksheet Content

Step1 : Select the entire data ( range of cells) to be printer.

Step2 : Press PAGE LAYOUT ---- PRINTAREA ---- SET PRINT AREA.
Step3 : In the same page setup group under PAGE LAYOUT, click MARGINS ---
CUSTOM MARGIN. It will open the following window. In that window select
PAGE tab and SELECT PAPER SIZE as A4
Step4 : Press PRINT or PRINTPRIVIEW.
In the print preview we can check whether the print will be perfect or some portion of
the print exceed the page size specified. Then printing in the second or subsequent
pages may not be meaning full. Let us analyse the following print. The first page has
District name and name of the person and other details up to HRA. The second page
starts with CCA and other columns. The second page print looks meaning less. We are
50
not able to fix the name or district for which these CCA and other details are related. If
we print the district and Name before CCA then that page will be meaning full. What
we have to do is repeat the printing of first two columns--- District and Name—alone at
the beginning of each page.

To add District and Name at each page do the following.

51
Come out of Print Preview
PageLayout—Margin—custom margin—SHEET tab
In the COLUMNS TO REPEAT AT LEFT in put window enter $A:$B.
Note:- these are the two columns to be repeated at each page. It will not disturb the
data selected. Only at the time of printing this will perform

Now look at the print preview of page 2.

52
Scaling
If you want to fit more data on one page, you can fit the sheet on one page. To achieve
this, execute the following steps.
1. Select 'Fit Sheet on One Page' from the Scaling drop-down list.

Note: you can also shrink the printout to one page wide or one page high. Click
Custom Scaling Options to manually enter a scaling percentage or to fit the printout to
a specific number of pages wide and tall. Be careful, Excel doesn't warn you when your
printout becomes unreadable.

Data Consolidation
You might have come across different situations wherein you have to present
consolidated data. The source of the data could be from one place, or several places.
Another challenge could be that the data might be updated by other people from time
to time.
You need to know how you can set up a summary worksheet that consolidates the
data from the sources that you set up, whenever you want. In Excel, you can easily
perform this task in a few steps with the Data Tool – Consolidate.

Preparing Data for Consolidation

Before you begin consolidating the data, make sure that there is consistency across
the data sources. This means that the data is arranged as follows −

53
  Each range of data is on a separate worksheet.
 Each range of data is in list format, with labels in the first row.
 Additionally, you can have labels for the categories, if applicable, in the first
column.
 All the ranges of data have the same layout.
 All the ranges of data contain similar facts.
 There are no blank rows or columns within each range.
In case the data sources are external, ensure usage of a predefined layout in the form
of an Excel template.
Suppose you have the sales data of various commodities from each of the regions –
East, North, South, and West. You might need to consolidate this data and present a
product wise summary of sales from time to time. Preparation includes the following −
 One worksheet per region – i.e. four worksheets with names East, North, South,
and West. These could be in the same workbook or different workbooks.
 Each worksheet has same layout, representing the details of product, number of
units, and amount.
 You need to consolidate the data product wise. Hence, ensure that the column
with the label Product is the first column and it contains the Product labels.

Consolidating Data in the Same Workbook

If you have all the data, that you have to consolidate, in the same workbook, proceed
as follows −
Step 1 − Ensure that data of each region is on a separate worksheet.

54
Step 2 − Add a new worksheet and name it Summary.
Step 3 − Click the Summary worksheet.
Step 4 − Click the cell where you want to place the summary results.
Step 5 − Click the DATA tab on the Ribbon.
Step 6 − Click the Consolidate button in the Data Tools group.
The Consolidate dialog box appears.

Step 7 − Select Sum from the dropdown list under Function.

Step 8 − Select the data from each worksheet as follows.

 Click the icon in the box under Reference.

 Select the worksheet – East.
55
  Select the data range.
 Again, click the icon in the box under Reference.
The selected range appears in the Reference box −

Step 9 − Click the Add button to the right of the box. The selected data range appears
in the box under All References.

Step 10 − Repeat Steps 1-5 for the rest of the data worksheets – North, South, and
West. The Consolidate dialog box looks as follows.

56
You can see that the data ranges appear worksheet wise in alphabetical order, in the
box under All references.
Step 11 − Check the boxes Top row and Left column under Use labels in. Click OK.

Your data is summarized product wise for the regions – East, North, South and West.

57
You can repeat the steps given above to refresh your summary results manually,
whenever you need them.

Consolidating Data Automatically

Suppose you want your summary sheet to be updated automatically, whenever there
are changes in the data. To accomplish this, you need to have links to the source
data.
Step 1 − Check the box - Create links to source data in the Consolidate dialog box
and click OK.

Your summary results appear with an outline as follows −

58
You will observe that a new column is inserted to the right of the column named
Product.
Step 2 − Click the + sign on the outline in the row containing the Product value named
Soap. You can see that the new column contains the consolidated value for each set
of product values, region wise.

59
Consolidating Data from Different Workbooks
In the previous example, all the data that you need to summarize is in the same
workbook. However, it is likely that the data is maintained separately for each region
and is updated region wise. In such a case, you can consolidate the data as follows −
Step 1 − Open the workbooks containing the data, say, workbooks – East-Sales,
North-Sales, South-Sales and West-Sales.
Step 2 − Open a new workbook.
Step 3 − On a new worksheet, click a cell where you want the summary to appear.
Step 4 − Click the DATA tab on the Ribbon.
Step 5 − Click Consolidate in the Data Tools box.
A Consolidate dialog box appears. In the Consolidate dialog box −

 Select Sum from the dropdown list in the box under Function.
 Click the icon in the box under Reference.
 Select the workbook – [Link].
 Select the data range.
 Again, click the icon in the box under Reference.
 Click the Add button to the right.
The Consolidate dialog box looks as follows −

 Click the icon to the right of the box under References.

 Select the workbook – [Link].
 Select the data range.
 Again, click the icon to the right of the box under References.
 Click Add.
Step 6 − Repeat the steps 1–6 to add the data ranges from the workbooks – South-
[Link] and [Link].

60
Step 7 − Under Use labels in, check the following boxes.

 Top row.
 Left column.
Step 8 − Check the box Create links to source data.
Your Consolidate dialog box looks as follows −

Your data is summarized in your workbook.

Linking Sheets and Books

You can tell one cell in Excel to look at another cell to get some information. So if, in cell A1
you type =A2,

 Excel will go to cell A2,

 see what is there and
 put it in cell A1.

61
You can use the same technique to get Excel to link to other sheets within the workbook or even
totally different spreadsheets (workbooks).

If we want to link one cell to another we type = and then you can either click on the cell you
want to link to, or type in the cell name.

Linking to another sheet or spreadsheet is the same. You can either type in the name or click on
the cell. Typing in the name of the cell can be difficult though because the moment you want to
link outside the current sheet, Excel needs to know the sheet name and it needs to be typed in, in
a special way. So to avoid having to learn this, just click on the cell you want

Link to a cell in another sheet

To link to another cell within the same spreadsheet but on a different sheet:

 click into a cell and type =

 Click on the sheet you want to go to
 Click on the cell you want to link to
 Click enter (very important- use enter to finish your formula- don’t click on another cell
or use your arrow keys)

Note below that instead of just now having the cell name (B5) it has the full sheet name
surrounded by ‘ and with an ! before the cell. So you can type it, but you need to get it exactly
right. Rather just click on the cell and let Excel do the work for you.

62
Including the external link in a formula
Above we just linked to the cell, but you can have the linking done within a formula. Below,
instead of just pulling through the price in cell C5, in cell D5 we have created a formula which
firstly looks at B5 on this sheet and then multiplies it by cell B5 in the other sheet. To enter this
all you need to do is:

 In cell D5 type =
 Click on cell B5 in this sheet
 type multiply (*)
 Go to the other sheet and click on the correct cell
 Hit enter (important that you hit enter here).

Using $ signs on these external links

You can also use $ on these links in the same way as you used them on the normal calculations
(and the F4 shortcut works here as well). So if you know that you want to use the link again
63
somewhere else but you need it to be absolute, then just put the $ signs on the letter and number
as appropriate.

The steps would be:

 click into a cell and type =

 Click on the sheet you want to go to
 Click on the cell you want to link to
 Type in a $ in front of the letter and number (or use F4)
 Click enter (very important)

Linking to a different workbook

Now that you have linked to different sheets within the same workbook, lets link to cells on
another workbook (totally different spreadsheet). The process is exactly the same except you
now need to go to a different spreadsheet. So:

 click into a cell and type =

 Click on the other spreadsheet you want to go to (it needs to be open)
 Click on the cell you want to link to
 Click enter (very important)

As shown below, the formula now is slightly longer in that it records the spreadsheet name
(Book2 below), the sheet name (6.1.2 Linking2) and the cell reference ($B$5).

64
The major difference between sheet and
spreadsheet linking
Although the process is the same there is one difference between the 2 links. If you link
between sheets within a spreadsheet, the cell reference has NO $ signs initially. If you link
between 2 workbooks, by default $ signs are put onto the letter and number

***

65