All language subtitles for 2. Excel Table Case Study - Solution

In this video we will analyze Sutton's batting record using Excel table let us select any cell in the

data range.

On the whole tab in this tight group click format as table

Excel displays different styles to choose from select a style of your choice in the format as table

dialog box.

Make sure my table has headers checkbox is selected.

Make sure that the range of cells is correct in the where is the data for your table.

Click okay.

Excel formats the cell range even to eat 330 as a table every table column has filtering enabled in

the header row so that you can filter all sort your table data quickly click any cell in the table Excel

displays table tools and the design contextual tab on the design contextual tab uncheck the banded rows

check box notice that the banded rows disappeared from the table.

Now let us select the banded columns checkbox excel for matchstick column in the table accordingly.

You can also highlight the event which is the first column by checking the first column checkbox Excel

gives a name to the table by default it names it like table 1 table 2 etc..

Let us rename the table to do so in the properties group click the table name text box and let us type

such in performance

and press into next.

Let us sort the batting record.

Select the Data tab in the sort and filter grow click site button.

Venus sorted data on the ground column then on opposition and then on the specific Excel opens these

sort of dialog box under column click sort by list and select ground since we want to sort the records

on grounds such in played click the ad level tab in the then buy list.

Now select opposition because we want to know the countries against whom such in played in a particular

drone.

Add another level of sorting in that then by list select dismissal click Okay access source the data

by ground then for each ground it sorts by opposition and then for each opposition it sorts by the specific

criteria.

Let us not calculate the total runs made by certain click the design contextual tab and select the total

raw checkbox Excel inserts a total raw below the table notice that every column has a total function

list inserted click the total dropdown list in the runs column and select some from the list Excel sums

up the total number of runs made by such an.

Note that Excel inserts a subtotal function when doing data entry data can be misstated in the dismissive

column.

We need to ensure that only Zeit dismissal criteria is entered to do so we will validate the entry in

column B I have created a list of valid entries in a new war cheat sheet 1 let us go back to SRT will

cheat select cell range B1 to be 330 on the data tab in the data goods group click the data validation

button Excel opens the data validation dialog box on the settings step under allow select list from

the dropdown list box to validate the data from a list select the source text box to provide the source

for the list of valid entries in column B to go to sheet 1 and select the cell range of valid entries

click the error alert tab and type the error message.

This is not the right dismissal.

If you is it and does wrong data for dismissal other than the valid list of entries he will get this

error message.

You can also give an input message for any data entry level cheap let this validate.

If Excel throws an error message what are wrong dismissal insert a new rule in the table.

We will enter wrong dismissal criteria X gives the error message we give cancel the entry in the same

cell click the arrow to display the list of valid dismissal entries and selected the specific criteria

let us delete this row.

Now let us find out how many events such as scored against each country.

Select the cell in the table and click the design tab in the tools group click insert slice of button

on the Insert slices dialog box select opposition tag box because we want to find out events such inmate

against each opposition click OK in the opposition dialog box let us select Bangladesh X in shows such

in regard only for Bangladesh you can see Sachin played nine innings against Bangladesh and made eight

hundred and twenty runs similarly you can select each country and find out how many runs such in scored

against each one of them to remove the filter in the opposition dialog box click clear filter Excel

displace all the records to remove the slicer right click and select remove opposition we need to find

out unique countries against whom such in plain let us add a new column for country first we need to

increase the size of the table on the design conjecture that in the properties group click this size

table button in the resize table dialog box we will change the data range two dollars a dollar one on

dollar f dollar 331 basically including a new column F click Okay Excel includes column F in the table

that is the name column F as country

we need to extract only country name from Column D in column F two we will type the formula now this

formula extracts the country name from column D2 it extracts the name from the third position and the

length of string extracted is the length of complete string in the opposition name minus two Excel extracts

the Opposition Country from column D and automatically applies the formula to all rows in the entire

column.

Now to find out the distinct countries select cell f f1 and click remove duplicates.

Button on the Remove duplicates dialog box click on select all button select the country checkbox because

we need to remove duplicates from the country list to get the distinct country set in played against.

You can see that he played against nine countries.