Home Coding Learn MS Excel for Data analysis

Learn MS Excel for Data analysis

0

Hello everyone,Learn MS Excel for Data analysis

learn ms Excel

today the topic is about ms excel or Microsoft excel. so in this post, you will be able to know how important will be the ms Excel in your life and career.

for data analysis and data analytics. apart from the basic and advanced coding, it is necessary to learn ms excel so that you can easily visualize the data.

In this post of educated info several formulas and functions are given below. one thing that needs to be considered is that only viewing these formulas won’t solve your problem but practicing them in excel and getting used to them is the key to remembering the formulas.

so welcome to educatedinfo and here is the list of formulas or functions that is important to learn in your career. remember, in every big college and university ms excel and office must be prerequisite because they are the basic components that need to know for every person, student, employee, analyst, and scientists.Learn MS Excel for Data analysis

excel, ms, ms excel, microsoft
Ms excel

some important formulas

  • To remove uncertainty or more spaces in the keyword in excel use trim command for ex.  I       amrahul    singh   ——-   I am rahul singh   ex =TRIM()
  • Flash fill– it is used to find or extract symmetrical thing in nearly columns for ex. If u have rs45755@gmail.com in 1 column then go to nearby column type rs45755 and apply falsh fill then all the email id in column 2 first name will appear (we can extract)anything.
  • Concatenate function is used to add two values or names in one column for ex in column 1 rahul is written and column 2 singh is written than in column 3 apply concatenate ‘rahul singh’ will appear (concatenate(value1,” “,value2)
  • Vlookup function – it is used to assign certain value adjacent to the values. It take 4 arguments vlookup(what u r looking for or assigning, area of main table or data, what u want to take from table, true/false).Learn MS Excel for Data analysis

some other important formulas

For example, we have product id in column 1 randomly arranged from 1 to 900 and we have to write the respective product name in column 2 and our main data is on sheet 2 in which product id and name of products are written in c1 and c2 where c1 are arranged serial wise so to write the particular product in s1c2 we use Vlookup function like,

=vlookup(c1, area of table of sheet 2, c2 from sheet 2, false[1 or 2])

  • Hlookup function is same but works horizontally
  • Index and match function

The index and match functions are different but the combination of both gives us amazing results like vlookup function (it is better than vlookup)

  • Consolidate command– in data tab there is consolidate option

Other formulas of ms excel

It is used to add, count, the sum of the particular table like pivot table for example-  if we have a certain table in sheet 1 of some rows and column and values are given, and the same table is in sheet2, sheet 3 and so on, so if we want to find the sum, avg or any other operation and make one table of these tables in sheet n then we use consolidate command.Learn MS Excel for Data analysis

ALSO CAN READ –Gate Agricultural engineering Cracking Guide

  • If you have given date in one column like 1-jan-2021 so to extract date, month and years we can use function like =DAY(), =month(), =year()
  • To find out the name of the date (Monday, Tuesday etc) use =TEXT(select date, “dddd”) you will get Monday or Tuesday (if u use dd instead of dddd then initial letter will appear like mon, tue, wed etc)
  • To find out the name of the month use =TEXT(value or date, “mmmm”)
  • suppose you have to find out day of the week in form of number use =weekday(date, 2) (if you use 1 instead of 2 then week start with Sunday)
  • Now, To find the week of the year use =WEEKNUM(date, 2)
  • To find out day of the year use =DATE(YEAR(date), 1,0) but then to fin d the day subtract this by column in which actual date is there so the subtraction value will give you the day of year.
  • =ROUNDUP (value, 0) is used to round up the number
  • To change date automatically on daily basis use =TODAY(no arguments)

If u want yesterday or tomorrow use -1 or +1 for ex. =today()-1 for yesterday.Learn MS Excel for Data analysis

More formulas of ms excel

  • To find your age use =YEARFRAC (date, today(), 1) you can use integer function to find date in integer ex.  Integer(yearfrac(date, today(), 1))
  • To use If function it will take 3 arguments first one is the condition, second is the value that you want if condition is true and then third if condition is false. FULL DETAILS 

For example if we can label people age as young (less than 5 year age) or old so we can use =IF(agecolumn<=35, “young”, “old”)

  • NESTEDIF, suppose instead of just young and old I want to categorize ages in 3 category viz. young, mid age, old then we use nested if or if more than 2 times. For example

=IF(agecolumn<=35, “young”, IF(agecolumn<=60,”midage”, “old”))

  • If or function, so if you have to categorize more than these values easily and want that young and mid age gets qualifies label and old will get not qualifies than you can use you can use if or function for example =IF(OR(agecolumn=”young”, agecolummn=”mid age”), qualifies,”not qualified”))
  • The if or function can also be applied to the multiple columns (in “or function”) to meet certain conditions as per requirements
  • If AND also works in the same way
  • Countif function is used to count specific given parameter

For ex =COUNTIF(SLECTED COLUM, “value or name”)

  • Counif, countis, sumif, and sumifs are useful functions which act same
  • Pivot table, timeline, and slider are important for dashboards.Learn MS Excel for Data analysis

to know more about these stuff go here

LEAVE A REPLY

Please enter your comment!
Please enter your name here