M2_Lab_Manual_Basic_excel.pptx

Lab Manual

Formulas and Functions in Excel

Objectives of this Lab Manual This lab manual is designed to provide practical knowledge to learners to solve a real-time work related assignment You will be given a problem and it’s descriptions. You are supposed to write down the steps to resolve the problem and submit the answer on a Google Sheet. A model answer will be provided at the end.

Problem What will you do? Calculate the sum of given data

Model Answer WWhhaatt sshhoouulldd yyoouu ddoo?? • Select Row/Column from the worksheet which sum user wants to calculate • Click on Home and then select Autosum • By default it select as SUM • Another methods is to type =SUM and select range and click on enter

What have 01 Sum of any range data in excel we learned…

Problem What will you do? Compare the text for the exact content [content and case]:

Model Answer WWhhaatt sshhoouulldd yyoouu ddoo?? • We are going to compare the two text strings exactly for the content and the case. • We will make use of the function called EXACT (text 1, text 2). It compares the text to be exactly the same and returns true if the texts have the same content and same case. [EXACT TAKES ONLY TWO TEXTS AT A TIME]. • We can simply compare the cells by using the formula = EXACT (Cell 1 containing text, cell 2 containing text) • The formula will return ‘TRUE’ if both the text strings are the same otherwise ‘FALSE’.

What have 01 Compare the text for the exact content we learned…

Problem What will you do? Format the three column Year Month and Day in one column

Model Answer WWhhaatt sshhoouulldd yyoouu ddoo?? • Type =DATE and select the range • By default it is formatted as year, month and day • Click on enter

What have 01 Format the three column Year Month and Day in one we learned… column

Problem What will you do? Check age of person using conditional analysis if he/she adult or not

Model Answer WWhhaatt sshhoouulldd yyoouu ddoo?? • Type =IF(select range < 18,”Adult”,”Not Adult”) • Click on Enter • Drag it until range of worksheet

What have 01 Check age of person using conditional analysis if we learned… he/she adult or not

Problem What will you do? Use Lookup function to find corresponding data

Model Answer WWhhaatt sshhoouulldd yyoouu ddoo?? • Look for this piece of information, in the following area, and give me some corresponding data from another column • The first step to effectively using the VLOOKUP function is to make sure your data is well organized and suitable for using the function. • We start by typing the formula “=VLOOKUP(“ and then select the cell that contains the information we want to lookup. • we select the table where the data is located, and tell Excel to search in the leftmost column for the information we selected in the previous step. • In this step, we need to tell Excel which column contains the data that we want to have as an output from the VLOOKUP. To do this, Excel needs a number that corresponds to the column number in the table. • This final step is to tell Excel if you’re looking for an exact or approximate match by entering “True” or “False” in the formula.

What have 01 Use Lookup function to find corresponding data we learned…

Problem What will you do? Create a table from the data you have on the worksheet

Model Answer WWhhaatt sshhoouulldd yyoouu ddoo?? • Select the Range of Cells that you want to include in the Table. Cells can contain data or can be empty. The following Range has 290 rows of employee data. The top row of the data has headers. • Under the Insert tab, in the Tables group, click Tables. The Create Table dialog box appears. Check that the data range selected in the Where is the data for your table? Box is correct. • Check the My table has headers box if the top row of the selected Range contains data that you want to use as the Table Headers. • Note: If you do not check this box, your table will have Headers – Column1, Column2, … • Click OK. • You can also convert a range to a table by clicking anywhere on the range and pressing Ctrl+T.

What have 01 Creating a table from the data you have on the we learned… worksheet

Thank You!