hey there Tech Enthusiast welcome back to Prime coding so I am adita and if you have been struggling for getting a single resource which will help you to prepare MS Office questions I know many of you are keep on struggling watching many types of video you not getting all stuffs at a single place so you are at the right place so today I am sharing a cheat shet with you all which contains all type of MS office type based questions now in MS Office what all topics being included like um Excel Excel formulas shortcut word formulas shortcut as well as the um Outlook features everything is covered in this video I will let you know all types of shortcuts all type of features all type of everything so that this video will be the OneStop solution of everything so if if you are gearing up your skills or sharping your knowledge this video will be very helpful because these resources will help you to succeed in your essential OA exam is it so without wasting time let's Deep dive into the cheat sheet first starting with msword questions where shortcuts control C very basic copy selected text or item control V paste copied item or uh item text or item see guys these particular PDF pbd I will let you know where you can find in few minutes okay so stay till the end so contr X is cut selected text or item I hope you have watched a recent uh podcast which I have done with a where he told that you need to know about the basic commands or shortcuts given in the word Ms word okay so these are the basic and we'll cover Advance also so contrl Zed undo then control Y which is redo control B is bold the selected text and control I is a Talis which happens is it will convert your sentence like in this manner slant manner okay so these are the some basic shortcuts and control U for underlying the selected text control s save the document contrl P open the print dialogue box contrl F find in that document control h replace text in the document control enter insert a page break so this question is also repeated control Shi shift plus L apply bullet point to selected text so if you have text if you select this and press on control shift L it will be the bullet will come here and your line will be converted as a bullet point okay now the thing is text okay F2 is same as dialog box now Ms word Ms word now styish pain will be applying consistent formatting to text using predefined Styles now what do you mean by this so in the exam it might be you have given the explanation of stylus pain and they will ask you which options is suitable for the particular definition so all these are also covered in this sheet okay so styish pain definition is given track changes review and ending documents by tracking all modification let's say they will give you the question like this okay which option is more suitable with the given explanation review and edit documents by tracking all modification you have four options with you now now if you have studied from this sheet you will Mark track changes and bingo you have marked your portion right so table of content we automatically generate a table of content based on your document headline okay now mail merge so these questions were much repeated in the OA that's why I have added this here so create personalized letters labels and email is the use of mail merge now smart art insert visual element to represent information okay it is a definition of smart art references tab insert citation footnotes end notes and manage your biblography okay if you are creating your final year report references step may help you to create your final report in better manner now review pain access spelling and grammar check Treasures and word count okay so these is a definition of review plane now you have studied two types of plane which is um sty stylish pain and a review pain so please make a note of it now Ms World mail merch again combine a template with a data source to produce personalized documents which is letters labels and emails this is a very concise definition of mail merge track changes review track changes to see who made what changes to the document okay this is also a must needed thing in the documents so multiple member can contribute in a single word and everyone can see who made what changes in the document okay now Styles and formatting using styles to ensure consistent formatting across your document Bingo now uh references and citations references go in insert citation for academic papers and report so this is all types of questions as covered of many types of features is also covered of word in this SE sheet table of content is references table of content and automatically generate a table of content okay so if you don't know word I hope by going one by one definitions of it you are being very familiar with all type of feature usually provided by the world okay now margins if you put the margins that how you can do you need to go on layout then click on margins to set the margin sizes orientation layout go to the orientation to switch between portrait and landscape okay it might be you need to do your landscape your A4 should convert it into landscape or vertical or horizontal any way so you can do using orientation columns would be layout columns to split text into multiple colums fair enough now msxl is shortcuts control n let's move to the MX Excel stuffs now control and create a new workbook okay how you can create a new workbook in Ms Exel Microsoft Excel is control n control open an existing workbook okay if you want to open some existing thing then how you can do it now control W close the current workbook and uh close the current workbook using control W if you want to close you can just head on to the control W now save the workbook using control S command Okay control P open the print dialogue box great control F find text or numbers in the worksheet you can use using contrl F control h replace text or numbers in the worksheet control Z same as control Ms word which is undo the Last Action control y for redo the action okay now as I already spoke control y redo the last undo of action so control Arrow Kee move the edge of the data region create shift space would create a select an entire row control space select an entire column Alt Enter would be start a new line within a cell okay if you want to create or go to the new line within a cell you need to use alt plus enter now Control Plus semicolon insert the current date if you want to insert a current date so see how small or less important thing it is but it can be asked to you in your essential exam how you can insert a current date in the word or Excel cell so you need to do is Control Plus semicolon now control+ shift plus dollar sign apply the currency format whatever currency you are writing it you can put your own whatever currency you need so F2 command will help you to edit the active cell fair enough now Ms XEL is nothing if you don't use some aggregation function present in it so let's talk about aggregation Auto sum quickly sum of sum a range of cells how you can do it is sum of A1 to A5 it will do the Su okay now V look up H look up look up values in a table and return the corresponding data it will look into the table the current cell that you are specifying and return you the correspond Bing data belongs to that great now we look up A2 B2 E10 plus false so these things we'll discuss briefly as we are going forward okay don't miss any of the stuffs this sheet is created by lot of hard work so just we need nothing from you was not a single penny we just need need a like And subscribe to the channel if you have not because we can see most of the people are just coming in watching their uh code questions answers going away and acing their interview but they are not giving a something in return okay so please it's a free okay just need to hit on the Subscribe button I be very happy now then share with your friends so that you should do a great task of helping them in their career okay so please as much knowledge you will share you will get in double in return so do that okay now P tables summarize analyze and ex explore the large data set in keyword tables so see these bold R you need to focus it more because they can ask definitions of these types of bold letters in your assessment okay highest cells that meet certain criteria now data validation restrict the type of data entered into a cell charts visualize data using various chart types sort or and filter is organize and extract the data based on the specific criteria I hope we are equipped with autosome V lookup P tables conditional formatting data validation charts and short and filter fair enough now msxl some function now for getting these type of sheets such as now we are covering MS Office fully okay then I have a plan to do the cloud and networking also okay so for that simple goal very easy goal just need to hit 200 likes on this video and 100 comments or let it be more than 70 comments I will upload the cloud cheat sheet then the network cheat sheet now where you can find all these cheat sheet firstly you need to uh go through whole video then come to the description you have a telegram link okay go to the telegram link then if you join the telegram GRE in the top in the top of the group you can see a pinned message just click on it there would be a Drive Link or when you join it a bot which is name as Rose will give you a Drive Link just head over to that you will find all resources of TCS essential and whatever thing that we are continuously talking about in that particular Drive Link okay then you just need to study find your uh things and just prepare for your online assessment now sum function talk about now okay formula is sum of A1 col A10 what do you mean by it explanation see how nicely we have written that so that this video would be one stop solution the Su function is one of the most basic yet powerful function in the Excel as we know that without sum we can't calculate the sum of the particular cell so it is used to add up the range of the numbers pretty simple in this example of this example will calculate the sum of values in the cell A1 through A10 so from A1 to A10 you will get your result of sum okay so if the cell contains numerical value please make sure that you are not applying this function in this uh string uh columns okay so make sure it is a numerical values the function will add them together and return the total for instance if A1 contains five and A2 contains 10 and so on up to A10 the sum function will add this values and return their total fair enough very easy explanation so that if you revise this it will fit inside your mind and why I have written the explanation see in your exam it might be they give you this uh command Okay A1 to A10 now select the appropriate explanation of this shortcut then you might start because you just have study shortcuts and the uh shortcuts and definitions but you don't study these types of function how they usually work so yes explanation is just for you to revise before setting in OA okay now we look up function talking it very briefly formula is we look up first stands for Value second stand for table then column index then range of lookup so how much you need to cover okay explanation would be V lookup stands for vertical lookup it use like vertical lookup itself mean that you either you have rows or either you have columns so vertical lookup I know I don't want to explain it you know it isn't so now to search for Value in the first column in the First Column specify table and return a value in the same row so if it process First Column it will return the answer in the very first row okay now here is a breakdown of parameters value the value give you want what you want to search for table range is off cells that contain the data including the column you want to return the value from fair enough column index the column number of tables the number of column number in the tables from which you want to return a value Let It Be A10 to a15 or anything as for your choice dange look up this optional it specifies whether you want to exact match false or appropriate match is true Fair now if function let's talk about IF function so if function consists of condition as we are from programming background we know that what do you mean by conditions now value if true and value if false so let's say if this name is uh like ID is 100 yes if it exist you will replace the row value by 10 or you will replace the row value by null so you can use using the if formulation explanation the IF function is a logical function that returns one value condition is true another value it's false it's commonly used to make decision within your spreadsheet which is in need when you are creating an high level of excel okay you need to apply a number of formulas so that you can perform the given task condition a logical test you want to perform it would be a comparison like a 1 is greater than 10 let's say the A1 column values if it is greater than 10 so it will be true or false the two GES would be there now value if true the value to return in the condition is true and false the value to return in the condition is false example see how precisely we have created this cheat for you we have a great example along with the explanation suppose you want to check if a student passed an exam you could use if B2 whatever the point point that we denoting which is greater than equivalent to 15 then pass or fail then what happen is the new column row that you have created column it will filled by pass or fail as compared to their marks if you compared with the marks now if the value in B2 is 50 or greater formulas return pass otherwise it return fails very easy stuff concatenate function formula would be concatenate A1 B1 explanation the concatenate function in Excel is used to combine join text from different cell into one cell from different cell from different cell like from different cell into one cell concatenate will help you to do that now let's see the example to make or convert our knowledge into some examplary so that it will fix into our mind now if a cell A1 contains hello let's say let's say one cell contains hello and another contains um contains world fair enough another W contains world the formula which is concatenate concatenate A1 cell and D1 cell then what happen is you will get Hello World okay pretty easy is it now will result hello world if you want to want a space between them so it is a miscellaneous thing will also added into it space into it you can modify the formula to concatenate A1 which is parenthesis and B1 then you will get a space in between now which will give you hello world fair enough now Ms Excel would be count if function let's talk about coun IF function with explanation and example okay count if range and criteria explanation the count a function counts a number of cells okay in a specified range that meets the Criterion that you have specified in this formulation okay now it usually counts the roles of column that you want to calculate now the function is useful for the task like counting the number of times a specific value appears in a data set let's say someone give you the task okay or in McQ they can ask you in this way is let me know an aggregation function which is or a formula how you can count Aditya name present in this uh spreadsheet then what you need to there is you need to count use count if okay now understand this count if how you can use as an example in spreadsheet now suppose you have a list of scores in the range of B2 and you want to count how many times the score 90 appears you would use the formula which is count if which is B2 to B10 you need to go for B2 to B10 and you need to count 90 so if 90 appears three times in the range then the formula will return return three fair enough it is a very easy explanation of count of function now average function now formula is average which is A1 to A10 fair enough now explanation is the average function calculates the mean of the number in a specified range it sums all the values in the range and divides by the number of values okay so if you want to find the average see how precisely we have written the explanation and the given example okay now if you got shortlisted for enture inter you then I would highly appreciate you won't miss this opportunity because it is very hard you are preparing like I know and you know how many videos that you have watched mine like of an hour of an 3 hour continuously for weeks we are uploading the video and you are watching it so you don't want to lose this interview opportunity so what you can do is you just need to head over to Prim coding. in because we have did 100 plus successful guidance and they have CED many types of sunas based company Let It Be NWI let it be as most of the people CCT the TCS because we have lost our mation in the hype of TCS okay and the hard M we and my me and my team handed and many of the people got shortlisted in their prescribed role okay and none of them got rejected so this is the USP of my mock session is if you spend a little I will give you in return is lacks of rupees package mind my words you will get to know how quality content we are providing in the mock session if you become a part of it and mind my words if you're able to crack enture see the face of your parents how they are smiling and how they can think about their daughter or son that he cracked or he basically settled in his career or her career so without wasting time go to the prime coding doin and have a mo because having Mo will do nothing will do nothing and it will create a great impact in your career okay now you can see n number of success story present in our website just head over to it click on um next this tab and go in multiple types of cards read the multiple type of cards where the these explanation or Su story is written okay and what you need to do is if you are not believing words you can you can just like adesh ful we have took his podcast as well okay and adash Bor as well you can see the podcast whatever we have took they cracked from our mock sessions only fair enough so you can just head over to it now MS PowerPoint let's begin with the MS PowerPoint topic shortcuts contrl L create a new presentation contrl M insert a new slide contrl D duplicate the selected slide contrl s save the presentation contrl P open the print dialog box and controls and undo the action it is somehow same as compared to word then office then power is it no redo the last undone action control shift plus greater than symol will do increase font size let's decrease the font size if I start the slideshow okay so this question I saw one two times in the mock series that I'm creating where you which command basically use or shortcut key used to present or do the slideshow of a PP great so shift plus FY start the slideshot from the current slide now control K insert a hyperlink Control G group selected uh selected groups okay now MS Office power MS PowerPoint slide Master what it do select the right option of it how you can do it customize the layout and design of slides throughout your presentation fair enough transitions add visual effects when moving from one slide to another okay this is the mean of transition what do you mean by animation anime text and objects on a slide fair enough speaker notes add notes for reference during the presentation it means when you're presenting or projecting a single slide you will get your notes here so that you can read it if you forget few of the lines okay now MS PowerPoint which is rephrase timings practice the timing of your presentation okay so timer will be there and you can practice according to their uh slide that you are speaking about now design idea will give you the layout suggestions how you can beautify your slides smart art in insert organizational charts and process diagrams and other visual elements Master slide view slide Master to edit the design of all slides in the presentation fair enough now mams PowerPoint in slide layouts you can go in slide layout like this and predefined slide layouts are present okay so now themes design themes to apply a consistent look at all slides transitions transition to add transition effects between slide animations animations to add animations to an object on slides Fair now Ms Outlook so PowerPoint has very less points we have only covered only covered the main thing that you want to know about Now shortcuts of Outlook control and create a new email appointment or task Fair control R reply to the selected emails crl F forward the selected email crol shift M create create a new email message control shift a create a new appointment see why I'm continuously speaking so that if you listen these terms okay once and after that you have revised it once so at least out of 100% 50% of the things will fit inside your mind okay because the thing you listen it will stay longer in your mind until then you have only speak about all the TS by yourself okay I think you got my point now shift control uh control shift Q creates a new meeting request control shift K creates a new task control enter send a mail contr s save draft of the email control 1 control 2 control 3 is main view calendar view and contract view great now Ms outlooks rules okay automate email organization and management based on criteria fair enough quick steps quick steps is apply multiple actions to email with a single click with a single click okay calendar sharing is share your calendar with others and view theirs okay what if if you have shared your calendar with your teammates so that they can see your availability okay in Prime coding organization we also have a calendar where my teammates can see my availability and I can see their availability because of calendar sharing okay now out of office assistant is set automatic replies for you for when you are away so this question I have covered a number of times in my Mo test okay if you have not given the mock test I've have uploaded two mock tests where in second mock test you can exp the coding question as well which contains the recent asked coding questions firstly asked in 9th of August 2024 and another was asked in 2023 okay so please watch that out and let me know how much marks you have scored after watching the full video fair enough now folders organize emails task calendar events in uh custom folders fair enough search quickly find emails attachments contacts and calendar events okay people pain is uh view social network updates and recent communication with contacts okay fair enough folders organize email folder in better management now rules you need to go in file then manage rules and alerts to automate email organization search is control e is a shortcut used in Outlook for searching something or searching within your emails Flags Mark important emails with flags for follow up create appointment is Control Plus shift plus a okay now create meeting request okay how you can do it is the shortcut of it is Control Plus shift plus Q now see control appointment we know the shortcut control shift a now please revise each day when you are waking up you need to revite all this so that when the question came in front of you in the exam you directly hit on to the right answer is it how which shortcut is used to create a task which is control shift K view calendar which is control+ two switch between males and calendar which is control One Main and control 2 which is calendar fair enough so thanks for watching this video till now here if you're watching please like this video subscribe to the channel we have did a lot a hard work for you so in return we just need to subscribe from you and share with your friends your bats your loved ones so that they can also learn from a great content and do something good in their career and remember they will remember you because of your one share they they have created or did something good in their career okay so we'll meet you with a cloud and networking series or a cheat sheet for that you just need to uh comment down below and like this video and hit 200 likes and about 100 comments we'll definitely upload the next Point okay so we'll meet you with a new video soon