Data Analytics Modelling
Lab Notes (2022-23)(Q21-Q25)
IV. Table for Question No. 20-25
Q21: From the above data in Excel, Concatenate the
Headers.
Step1: Select the two rows in the header and copy
the cells in a new Sheet ( in cells 2nd and 3rd rows)
Step2: Select Cell A6, right click and select paste
special,
Step3: Select Transpose.
Step4: Select the cell C6, write the syntax
= CONCATENATE( A6, “ – “, B6) and Enter.
Step5: Select C6 and drag down.
Step6: The content in the columns will be
concatenated.
Step7:Copy the rows C6-C12
Step8: Select the cell D6, right click select paste
special, select Paste123 option.
Step9: All the values will be pasted.
Step10: Copy these values. In cell D6, right click and select paste
special, and select transpose.
Step11: The concatenated headers appear in a row.
Step12:Copy these cells, and go back to the table,
and paste them in the header row. Select
and delete the other row.
Q22.
From the above data in Excel,
convert the numerals in Year column to text format.
Step1:
Select the cells to be formatted
Step2:
Go to format and select Format Cells.
Step3:
In the dialogue box select TEXT option and click OK.
Step4:
If the given data are in numerals , it will be on the right side of the cell,
where as if it is in the text format then it will appear in the left side of the
cell.
Or
Step1:
Select the cells to be formatted.
Step2:
Copy them in a new column using Paste Special.
Step3:
In the tool bar, click on the down arrow next to General. Select Text in the drop down menu.
Step4:
The numerals will get converted to text format.
23.
From the above table in Excel, highlight the blank spaces
and fill it with "Not
Available".
Step1:
Copy the data in a new sheet.
Step2:
Select the entire data and go to Find and Select.
Step3:
In the drop down appeared, select Go To Special.
Step4:
A dialogue box will appear. Select Blank
and Click Ok.
Step5:
The blank spaces are highlighted. Press
shift and in one of the blank cells fill colour and enter “NOT AVAILABLE” and
click Control+Enter. All the blank
spaces are fill with Highlighted with colour and with NOT AVAILABLE
information.
Q24:
From the above table in Excel, select
only the first name
from the Employee name and paste in another
column.
Step1:
Insert a column next to the Employee Name.
Step2:
In Cell B3 write the syntax, =LEFT(A3,FIND(" ",A3)) and enter.
Step3:
Select B3 and drag down.
Step4:
We get the required first names.
Q25:Create
Email Addresses using lower case and underscore in between the names and end
with “@gmail.com”. (For example priya_dixit@gmail.com)
Step1:
Select the contents of Employee name in a new sheet.
Step
2: Copy the contents once again in a new column. Select the contents, go to
Data Tab and click on Text to Columns.
Step3:
A Dialogue box opens. Select Space and click on Next and Finish.
Step4:
A dialogue box opens asking for replacing the contents. Click ok.
Step5:
Give the headers of the two new columns as First Name and Last Name.
Step6:
In cell D4 write the syntax
=CONCATENATE(LOWER(B4),"_",LOWER(C4),"@gmail.com")
And
click enter.
Step7:
Select D4 and drag down.
Step8:
Mail Id’s are created as required.
Comments
Post a Comment