Excel for Beginners

[Music] okay so it should be accessible now good morning everyone i'm just uh there's some technical difficulties i'm just trying to sort things out just give me a couple of minutes before we uh get kick-started with the session yep i could see that streaming is live now let's just get a confirmation yeah since it's live let's get started good morning everyone uh welcome to the training session on excel for beginners uh my name is sinthil kumar and uh and in this session we're gonna learn more about microsoft excel and how to use microsoft excel uh in your day-to-day uh activities or in your college days or in in your work yeah so the agenda for this session this is gonna be a five day training program or or it's more of a five weeks training program which is done every monday and this is exclusively for uh uh nirmala women's college coimbatore uh in the day one you're gonna learn more about excel basics getting started how to install microsoft excel and understand quite a bit of basics of uh the excel program in day two you're gonna learn more about a data manipulation and how to work with the data within your excel spreadsheets on day three you're gonna learn more about formulas in excel and what are the various formula options that are available for you to use within your excel worksheet on day 4 you're going to learn more about lookups how to do searches uh using formulas in excel on day 5 uh it's a very interesting topic which we're going to look at on a day for a week 5 which is going to be more of charts and visualization which lets you uh learn more about microsoft uh excel and how to use charts like pie chart bar chart etc within within your excel sheet and the entire five weeks of training it's going to be more of practical a session with very minimal slides the slides that i have is just hardly two or three slides used for just the first day but most of the uh training program we're gonna to use the excel and then show how to work with excel more of a practical session rather than using uh the slides and the schedule wise the schedule is going to be every monday we're gonna have a session every monday between 11 30 a.m to uh 1 pm uh indian time for 5 weeks and you will be provided with two assignments every week until week five uh and which is to be submitted online i'll provide you more details of how to submit online uh the assignments when things are ready and you'll at the end of week five you'll be provided with one uh multiple choice uh assessment on microsoft excel based on the training whatever we have done until now so we're gonna use that as as an example and the details of the assignments and tests will be communicated via email or through the college department uh once the uh session on the particular day completes so that's how the schedule will look like for next uh five days five five weeks yep and yeah without wasting much time let's get started so anyone who wants to learn about microsoft excel or wants to get started with microsoft excel would like to know what exactly is microsoft excel and why why do we use it i mean what are the use cases that we use microsoft excel for right microsoft excel is one of the spreadsheet program from microsoft it is one of the popular software which is available and it is part of the microsoft office suite so microsoft office contains products like uh word uh it contains powerpoint it contains vco it contains project and it contains excel as well excel is one of the uh products within the entire office suite yeah so at a basic level microsoft excel is used for storing information analyzing and sorting data and mainly used for reporting purposes right it is extremely popular in business because spreadsheets are highly visual and it's very fairly easy to use as well and some of the use cases where uh microsoft excel is used as for example accounting purposes in a company or within your college college where it keeps track of all the uh debits and credits and you want to keep that accounting you're going to use use that and it can be used to store students data within an excel sheet for example i i personally used excel sheet to store all the users who have registered for this program in an excel sheet before i share it with your college so that's one of the use case as well and other use cases like uh com various companies can use for uh analysis purposes uh uh that can be used or market research purposes people can use microsoft excel there are plenty of use cases which you can uh think of where microsoft excel fits in onto our day-to-day work either it's used for your work or it can be used for your college college stuff yep excel is used everywhere the spreadsheet programs are used everywhere there yeah there are plenty of alternatives as well it's not that just there is excel you have quite a few uh products from google and few other companies as well but microsoft excel is widely used and it's very popular which has been used everywhere yeah so let's get started first so i'm gonna just share the uh browser now let me yeah so there are multiple ways in which you can use uh microsoft excel from your laptop or from your mobile devices so one of the easiest way for you to install is go and then buy microsoft office product which is like our office 365 subscription buy it download your uh app and then download your microsoft office you see there is an install office option here download install and then you could use it locally with from your laptop that's one of the options the other option to use microsoft office free is go to office.com and then uh login with your microsoft account if you don't have a microsoft uh account or outlook dot com account you could go and then register for one for free once you register and then uh continue using my office.com you'll be provided an option to say use the free version of microsoft office so when you when you start using a free version of microsoft office which is a microsoft office online or excel online so which means you open up any browser and you would be able to access and manipulate data on your excel sheet or any microsoft office product on your browser or on your laptop yeah so that is option number two the third option is if you have got an apple device or an android device you could also go ahead and then install the apps the office apps which is available and then give it a try as well so there are plenty of options for you to use microsoft office right and if you already got microsoft office installed locally on your laptops feel free to use that for all your uh learnings when uh during this five week if not you don't have to worry ab worry too much about it because the office online provides pretty much 90 percent of exactly the same features what you get out of the other office uh products so so once you log into office.com the first thing that you're going to see is click on plus button here on the left sidebar which is like a create button once you click that obviously it's going it's going to provide you multiple options here where it says use document use create a presentation create a spreadsheet create a form create a quiz or create a page so since this is more like often excel uh training so ideally we want to go and then learn how to create an excel spreadsheet so you click on the plus button so this will create this will ask you to verify or a login so i'm going to just log in with my office account so i'm just entering the password on another window so you might not be seeing that so once i enter yep the password is entered so i'm going to click on create and let me give it a try again let me see what is happening yeah it's creating a microsoft excel workbook and you should see it just takes time to load first time you should see that the microsoft excel is loading on the browser and you would see by default microsoft has created this workbook and it has stored on onedrive since it's we are using an online version it stores on onedrive and you will see that excel workbook has been created and then you could start playing around with microsoft excel online yeah that's more of using a free version and in case if you want to get access to microsoft excel right but for the uh ease of uh my training i'm gonna use the uh local version so it's easier for me to uh the demo so i'm gonna use this here so let me just drag and drop and you should see a microsoft excel being shown here yeah so now by default how do i create a new worksheet yeah i click on file and this shows a list of all the options for me where i can say new when i say new it gives me an option to say create a blank workbook but apart from creating a blank workbook it also provides me plenty of options or online templates that i can use to go and then create uh the workbook for so for now let me start creating a new blank workbook for now let's look at the templates uh in in some time yeah i click on the blank workbook now so a blank workbook is created locally for me and you will also notice on the bottom uh where i'll just zoom in so it's windows plus on on the bottom uh you will see something called sheet one that has been shown there so basically what sheet one means is a workbook is basically a collection of different sheets right so i can have multiple sheets within the same workbook for example i can create a workbook and name it as a student information and i could create and sheet one i could have the list of all the student names and registration number for example and i could create another sheet by clicking on that add button a sheet too and then i could start entering the uh students and their marks as well to keep track of the marks information the second sheet right i can create as many worksheets i need within my excellent sheet which lets me to organize my data into my multiple sheets so it's easier for me to go and then search for the data so that's what one of the core features of your worksheets are end of the day excel is a workbook that's a file end of the day and that file contains multiple worksheets which lets you to organize your your data right so uh to start with what uh what are we mainly looking at let me look at so how does the excel uh uh visual overview look like because if you look at excel are excel ui there like plenty of uh items on your excel and you might be easily confused on what exactly each one of these means so let's get started one by one on the excel ui and understand what each and uh at least most of the ones what uh what they are and how it is used yeah the first thing is you see that there is this file that we have created with names as book three right and if i click on file and if i click on save as and it gives me an option where i can save these files i click browse which is fine so now this provides me options where i could save this file i'm saving it in the documents folder and the save as type is excel workbook right which is excel sx format which is the latest format after excel 2007 there are uh other uh excel format that are available as well which is like xls format which is like a old version of microsoft excel we could store with that format as well so we select uh xlsx as a format for now and then click on the save button right so now once you've saved so what what you actually saved is the workbook right although it's a excel excel file excel file is a workbook for us a workbook is an entire file whatever you see now right excel excel stores spreadsheets in files called workbooks the contents of the workbooks are shown in the workbook window this is the entire workbook window and you're going to see everything in sheet 1 sheet 2 and sheet 3 yeah and the next component on the ui what you see is basically a name box you see something here which says a1 right the one that i highlighted let me try to zoom again let me zoom in you see something called d6 for example and i select a a1 here this would i can change if i select b1 the cell this value changes yeah so let me zoom out so what what is that uh the section that you see with with like a drop down list or like a text box there is called as name box name box displays the cell reference of the active cell so what are cells right every worksheet whatever you see here workbook contains multiple worksheets and each worksheet is basically rows and columns the data is organized in rows and columns yeah you see rows being numbered with one two three four five it it goes on and columns are labeled as a b c d e f g h and in multiple uh columns there yeah and now if i wanted to enter a data on uh say the first row first column right that is called a cell so the cell here is a1 a says it's a column and one says it's a row number so which a1 is the column one and when i enter the date i enter some test data here and it's gonna show yeah on the column a1 uh it contains a value called test and the name box exactly reflects what cell that i have selected in this case i have selected a1 that's the reason it shows us a1 if i go and then select some other cell it's going to show the other cell reference alone yeah it just displays the cell reference of the active cell right and next you see there is a set uh there's like an arrow mark here and let me zoom in again you'll see there is an arrow mark on uh the uh left top left which is like a triangle kind of thing yep uh it's like a triangle or an arrow uh that is nothing but and select all button so uh there are times when you have multiple data in within your excel sheet and you want to select everything one of the easiest option is set click on any of the cell use ctrl a as a key shortcut key by default it's going to select everything within this excel spreadsheet yeah so now you don't want to do that without using a shortcut key if you want to select everything the easiest option for you to use is that select all button yeah once you use that select all button it selects all the cell in the active uh worksheet so another easiest way for you to go and then access which you're gonna see on the screen immediately yeah like like i mentioned the numbers one two three four five whatever you see on the left hand side right and let me zoom in again so it's easier for everyone to see all the numbering that you see one two three four five six these are all nothing but the uh row headings which are like a row numbers row headings are the numbers along the left side of the worksheet window that identifies the different rows within your worksheet right you click a row heading to select the entire for example i want to select the entire row of six i click the row six you see the entire row six is selected by your microsoft excel yeah that's more of uh row headings similarly you also have the column headings what you see on the column is like a a b c d that's column headings like i mentioned let me zoom in so a b c d e f g these are all nothing but the column headings and in case if you want to select the entire column all that you do is select the column headings there it's going to select all the uh cell within that particular column for you to work on manipulate the data or you want to perform some uh updates to all the cells there yeah so that's more of the cell thing you you also see something called a formula bar yeah you see there is an uh something called a formula bar here on the top uh just above the uh column heading let me zoom in you see here there is a text box on the top right if i select the a1 it's it's going to show the exact value what the cell a1 contains but that's more like a formula bar for us for example i want i have a cell which contains for example three values one c one con let me zoom out c one contains one c two contains uh sorry c two contains one c three contains two and c four contains 8 for example and you want to calculate the sum of all these three values how do we do that one of the easiest option for you to use is press or enter equals to symbol equals to symbol indicates that you are about to use formula within uh the uh a particular cell as soon as you entered equals to you see the name box name box provides you options now to say hey you are about to use a formula what are the built-in formulas that are available you could pretty much go and then select any one of this and apply the formula there by applying a function or an a simple way of applying a formula is using equals to select all the cells what you want to do for example i selected c2 and then i press plus button i press c3 i use plus button and c4 and press enter key so now you would see that the value c5 contains the sum of the cell values c2 c3 and c4 yeah so that's more of its just applied an arithmetical operation on these three cells to show the values for me a simple way of applying formulas within your excel sheet so now not just the formula you also got the built-in functions as well for example i want to use a built-in function in this case sum is something that i want to use so i can enter some here and now i can select the range of cells that i need to add i select these three values three cells and let me press enter so now you see it has performed the same operation but instead of using an arithmetic operation of plus on the formula it has used uh the sum function within excel excel provides plenty of built-in functions which we will look at in the upcoming sessions but some function is used in this case to just define all the cells where you want to sum up and then show the result on the destination which is the cell file which has shown up as a result as 11 in this case yeah so that's more of the formula but the formula bar displays the value or formula entered in the active cell it just uses the active cell to display for example if i select on some other cell it doesn't show anything for example i selected d4 d4 contains empty data that's the reason the formula bar shows us empty but in case if i select c4 c4 contains a value or a text it has just displayed the text but if i enter c5 since c5 is a formula internally it uses a formula that's what the formula bar shows but when on the display you'll see that formula been applied and the result been shown to the end user so the display value might be different but the underlying formula might be different so that's what the formula bar takes care of displaying that to the user so let's look at next other options yeah so now you will also see something called uh the scroll uh thing on the bottom uh let me zoom in when i scroll through uh this particular sheet in the bottom bottom uh left corner you will see there are like two uh arrows here one is the left arrow and other one is a right arrow right and this ah this is nothing but a scroll tab right this is uh this allows you to uh scroll uh the list of all the sheets tab in the worksheet so if you have multiple sheets and you want to scroll and it just helps you to scroll uh in case if you have more number of sheets there yeah and you'll also see the sheet names here there's a sheet tab where you see sheet 1 sheet 2 and sheet 3 let me zoom in you'll see sheet 1 sheet 2 and sheet 3 on the bottom so that provides the sheet that is currently displayed in the workbook window which is obviously the active window it's a sheet tab is a white so whenever you see something in white especially on the sheet sheet tab that that means that the sheet is currently active so anything in white is sheet is active and anything in gray is sheet is inactive at the moment yeah and you'll also see the other sheets in the workbook which are not visible the sheet tabs for these inactive ones are generally gray that's what it means and apart from this there are there are a few other pieces of information that you're going to see uh within the uh excel window so on the on the bottom right corner you see a display settings let me zoom in again on the bottom right corner that means okay so you zoom settings have been just messed up yeah on the bottom uh right corner you will see a zoom settings here i can go ahead and then zoom in or i can go and then zoom out it's more of the zoom control that increases or decreases display of the content in the worksheet if i if i just put it to 100 percent that is more of a zoo zoom out you'll see uh the content has been shown in like a very uh much better view there in case you feel that you want to zoom out you could always go and then zoom out and explore the data and when i when i just use it uh zoom out or zoom in again you're gonna see the content size changes yeah by default i always prefer uh keeping it to more of 120 that's the default one which i tend to use but it depends on each individual individual user what is the zoom zoom in or zoom out level that the person would like to uh use you'll also see uh something called as an active cell so whenever i select any cell within microsoft excel right the cell currently selected in the active uh worksheet is actually the active cell which is this currently selected one in this case imagine that i select uh c5 c5 is the active cell for me yeah the active cell is always outlined with a thick border you'll see when i selected you see a big greenish border on the selected cell yeah which is nothing but the outline which is like the thick border and the corresponding row and column headings are highlighted so whenever i select anything that's an active cell which means that the row and the column is actually highlighted you see on the top where the d color is changed as well as the row heading on the five is also change now you'll see something in a black because of the theme that i'm using but the uh the row heading or column heading would get highlighted depending on what cell that you currently select i can select e i can i can select e6 i can select a1 you'll see the color gets the column editing or the row heading gets highlighted accordingly yeah and like i mentioned previously each element uh what you select although it's an active cell that represents a cell within your excel for example a cell in this case as multiple uh items right so cell at a very basic level is like a each intersection of a row and column is defined as a cell within microsoft excel yeah and a worksheet data is placed within separate cells for us and finally you will also see something called a status as well in the bottom and if i go in and then enter you'll see uh let me zoom in there is a status bar there on the bottom left and you would see that been shown as enter for because i have just entered a data which i have not saved so let me modify it and let me enter that so now it will be in the ready state for us yeah and if i go and then modify the data and it will be more of edit uh data formats the status i'm trying to edit this data and you'll see the status of this is in the edit state yeah your status bar basically tells you what is the status of the current operation that you are doing it on your excel cell whether are you in an enter mode that's when you exactly enter the cell or are you on the edit mode which is you are trying to modify an existing cell or on a ready mode which means that everything is complete so that's what the excel ui looks like so at a very high level this is what the excel book looks like which is like a collection of worksheet and each worksheet contains collections of rows uh and columns which is nothing but the cell and you could play around with the active uh cells by putting putting up your own data and then manipulating the records there yeah so so let's let's see how i can enter the data now i'll use sheet2 as an example here let me just go back yeah i'll use a sheet 2 as an example here and if i look at sheet 2 you would see it's completely empty now so let me start entering the data i'm assuming that the first row is used as a collimator for example i could go and then select uh the a1 and i would name that as register number register number and i would create the column a b as first name and the column c as a department yeah and college d as uh column d as college so now the excel sheet what i've done is i have got four columns and each represents different fields altogether and i'm also entering uh the data now of the students one so now for example i'll enter register number one uh i'll name this person as norton and for example i'll name him stanley department is uh mca and college is say test college sorry i think i've just typed in a wrong field department is mca and colleges test college yeah and the same thing register number two and uh i would say uh name is senthil uh a department is a pca for example and it's like college one and i can enter any number of rows here i'm just trying to interview dummy data so that it's easier for you to use i'll enter register number three and then i enter say a test user and i would enter bca again yeah and then i will go and then select test college again you can enter as many data that you want so at a very high level when and i'll click on this you see on the top bar you there is a save button there i'll click on the save or you could also use control to save this particular file right so now this particular worksheet uh just rename the worksheet let me rename it to something else we'll look at that one by one students list right so now the worksheet that's the second worksheet which is a student's list contains list of all the students with their register number first name department and college yeah this form sort of the core data of your uh excel sheet now so now you would also have notice that uh i i did something on the ui to realign or auto adjust the column right on the top so for example let's look at the cell b2 b2 contains norton stanley but somewhere the text is going beyond uh the selected cell right and it's not shown properly to the user so what i did previously was on the column header you see when i mouse over on the column heading b and just in between b and c if i uh see you'll see there is like a arrow mark kind of thing i'll zoom in you'll see a kind of arrow mark thing you just double click on that it just does out of it for you to say hey i found there is a name which is like a bigger one and then i'll do the out of it the same thing applies for columns d as well you see kissed college is going beyond the column bit that is being set by default right if you want to just increase that one option is click and drag and drop so which means that you can specify the column width manually by dragging and dropping or you could also just go ahead and then just double click whenever you see that and it should automatically show you uh it should automatically autofit your column for that so let me double click on that to show you just take some time you need to just yeah so now you see that minus so so what you saw here was basically an autofit because your rows and columns might be uh you you might be entering a pretty uh big data or a large amount of data and you want to uh large amount of data right so uh to do that uh you have to just go and then out of it your sale value here so now just a minute there is something there's a pop-up yep that's fine yeah so let me continue on so what you see is an uh excel uh data what we have entered now so now i could go and then format this data as well so within the excel uh worksheet uh you see there is on the top on the home window which is like a ribbon which is used in microsoft excel you see section called which lets you to play around with the font sizes or color for your active cell so in this case what i want to do is i want to select the column row or a column header and select them and make them bold so if i use the bold option here which says make your text bold i click on that and as soon as i click on the uh the bold icon it just takes some time and before you should see the column header is now in the bold color so apart from the bold i also wanted to make that look more like a column header so i'm gonna set the background color or a fill color for that particular uh header row so i'm gonna go and then select probably a grayish or a dark rash so you notice that i've applied the formatting now and the header which is like a row number one contains the entire uh formatting of bold and in a gray filled color right so that's one way of looking at formatting there are like plenty of formatting options there for example another formatting option which most of the time i tend to use is select all the uh rows and apply the borders so you see there is a border there and i want to apply a very slim border across all the edges i click on all the borders and you will see that entire sheet contains the border they have to be shown for the selected cells for us yeah so i'm gonna click ctrl s to actually go ahead and then save this data so yeah it's gonna save the data which is all right let me just continue on yep so let's start looking at something else here so now that's more of uh adding a data in the cells or within your excel sheet and which you would start seeing there so another trick or most of the time when you want to apply the filter we're going to look at this in depth later on but there are times when you wanted to apply the filtering or some kind of sorting on all these uh columns like for example you want to sort by department or filter by department for example so to do that filtering just select all the cells here and go to the data tab i think it's in the data tab yeah and you see there is a filter option here i'll say windows plus you should see it here under the data tab sort and filter you will see a filter you click on the filter and immediately you will see by default excel would add a kind of drop down uh control or a combo box control on the row header so when you select that particular drop down you will see the list of all the options by which you can sort or you could filter as well in this case for example if i want to filter by date sorry filter by the uh department i can go and then select mca and i click on ok and i'd see that excel would go and then filter the entire data in in the uh worksheet based on the criteria that i have selected and if i want to clear the filter i select that again and i click on clear filter from the department so this will ensure that the entire data is added again so imagine that you wanted to sort this particular data i click on the same thing and i say sort largest to smallest you'll see that entire selected data whatever the excel sheet contains is been sorted by the ascending order of the register number previously it used to be like one two three and the same thing has been sorted into a descending order to three to one so that's uh that's a filter and a sorting at a very high level but we're gonna look into them in the uh day three or day four uh where we have uh exclusively some sessions on how to do data manipulation and apply filtering and so on right so now we saw excel workbook excel worksheet cells how to add data how to start with the basic formatting for your uh excel right so the next thing is like yeah so you're gonna have multiple worksheets uh within your excel and you want to save it uh you you want to add or manipulate the worksheet names for example or manage the worksheet names right so you can do that by right clicking on the sheet tab on the bottom right on the bottom you see sheet1 let me zoom in again you see sheet1 select any sheet which you would like to modify for example in this case i want to rename sheet1 or for example i will delete sheet1 i right click on the sheet1 and you will see an context menu that shows a list of options that you can perform on that particular sheet in this case if i go and then select delete it would delete sheet one from it it will just show you an option or a confirmation window to say microsoft excel will permanently delete this sheet do you want to continue if you say cancel it's going to stop it's not going to delete let me click on cancel which means nothing happens which means you're safe if you want to do if you're sure that you want to delete you click on delete yes it's going to delete the record so this has deleted the sheet1 that you have added yeah same thing for sheet3 as well you right click on sheet3 and you want to delete again right and you click on delete it gets uh deleted automatically right there there are other operations that uh the that you can apply on a worksheet for example a simplest way of adding and worksheet is using the plus button so plus button will always add a new sheet at the end i keep on adding sheet click i click plus it by default adds the sheet 3 sheet for sheet 5 and sheet 6 but you will also notice that when i right click on the sheet name let me zoom in you will notice that there is something called insert insert option yeah insert option also creates a new worksheet but insert in instead of creating it at the end the insert would uh insert a new worksheet at the same place where you have selected for example i click on worksheet insert here you'll see uh what what do you want to insert it provides me plenty of options in this case i set a new worksheet for example i click on ok you will see a worksheet 7 was inserted before sheet 3 rather than adding it at the end of sheet 6 yeah the ins inside option exactly does that if if you click on the add new worksheet it's always gonna add the worksheet at the end of uh the list and if you use the insert insert would be just prior to the sheet where you're currently selected and then try to insert but but again when you use the insert option it provides you multiple uh things within the templates for you to insert one of the options is a worksheet but apart from that you've got plenty of other steps like the charts or macro and so on but yeah you use worksheet in this case because we are using the worship worksheet manipulation to rename a worksheet again right click on the sheet name that you want to rename you see a rename button option here i'll zoom in again you'll see a rename option here you click on the rename which means it's going to provide you an option to rename the sheet name so in this case let me rename the uh sheet name from sheet 7 to students marks for example so this has renamed your sheet from student sheet 7 to student marks and then you could go and then add your own data within this particular excel sheet and then manipulate the data yeah so that's more of uh adding a worksheet or renaming the worksheet yeah so uh the other option is like you you can set the tab colors for uh the worksheet for example i could go in and select something like a green uh tab for the uh particular selected tab for example in this case you see that the tab there are ways sometimes where you feel that a worksheet is very important for you and when you share the work workbook with or the excel file with someone else you want to highlight and tell that person this is a very important worksheet or there is like a pending work to be done you could always go and then color them so that that kind of provides an indication saying that something is weird or something has to be taken care of those worksheets for us a easy way for us to uh just notify the other user is to just go and then add tab color and then provide the uh default colors there by default you select all the theme colors or standard colors but this is not just the list of colors that you have if you want more colors to be selected for tabs not just for tabs but anywhere within your excel sheet for example a fill color or a background font color you want multiple options then what is defined by the theme colors here you click more colors option so once you select the more color option you could go and then define an rgb value and then say this is what i want rgb values are nothing but any values that contains 0 to 255 which is like an rgb value that defines each and every color i can go and then select for example hundred i can say hundred hundred so hundred under hundred is nothing but somewhere it's a blackish or a greyish kind of thing and i could go and then select one for example it's different and i'll select one here as well you'll see it's more towards greenish in this case yeah i'll select unread and say 200 and i click on ok now you will see the color of this particular worksheet is bluish color but in this case i have not selected the predefined set of colors there i've used an rgb value to provide a very clear or a very a detailed color definition for my uh tabs there and the other operations that are available as a part of the worksheet one is i can go and then hide the worksheets as well so i click on the height you'll see that the sheet is now hidden uh where it's not shown to the end user so whenever you hide a worksheet by default the worksheet is there within your excel workbook or excel file contains that worksheet but just that the worksheet is not shown by default to the user in case if the worksheet is needed for the user all that they can do is right click and say unhide and then it's gonna show the list of all the worksheets that are hidden and for example i see uh students marks here as a worksheet i click on ok and then you'll see that worksheet is brought back yeah i'll again repeat again let me zoom in uh to hide a worksheet all that i did was right click on a worksheet on the bottom uh worksheet tab you see there is an hide button i click on height on the context menu and the sheet is hidden now the sheet is not shown anywhere so by default even when you send the file to someone else the sheet would be hidden in case if they want to unhide or show the sheet back all that they have to do is right click on the sheet click on unhide and click on minus and you will see the united option here and it provides a list of worksheets which is already hidden and i select them if there are multiple worksheets you will be able to select one or more worksheet that what that were previously hidden and you could uh unhide them from your worksheet so in this case it's a student's marks i click on ok and you'll see the student's marks sheet is back now into the thing so i so other options that we have got is uh for the students mark for example there are times when you want to protect the sheet you can use a product sheet option here which will ask you to lock the sheet for editing or uh for for other stuff so what i can do is i can provide test so what it gonna do is it's gonna protect the worksheet and the contents of the locked cells so in this case what i've done is i would say i've selected the column i uh 35 and i'm trying to log right let's say test again it just asks me to confirm the same password so once i have done this and i go and try to modify the data you will see there is a prompt message which says the cell or chart you are trying to change is on a protected sheet to make a change unproduct the sheet you might be requested to enter a password so in case if you want to do any modification uh within this particular excel sheet you have to give this password unprotected and then modified so there is an unprotect option again where previously we protected the same worksheet let me zoom in you see the unprotect option i can select and protect and it will ask me for the password to enter i say test and yeah so now since the password is entered i can go and then enter the sale data here that's more of protect and unproduct the product and end product plays a very uh important role whenever you want you don't want other users to modify the data others just to view the data and if they want to modify the data you want them to enter a password before the data can be uh modified so in those cases you use a protect and unproduct as an option yeah so that's more of uh protect and unpredict so these are some of the common operations that you're going to see uh that you usually perform within the worksheets there so the the other option is like more copy so that's more of uh if you want to move the selected sheet into a different sequence for example or into a different workbook altogether or uh something like that so you will be able to use uh the move option so in this case i imagine that i wanted to move students marks from the second from the list to the end of uh the list after sheet six i can select move to end and create okay so now you see uh the sequence of the uh students marks uh worksheet is in the end now rather than at the sequence number two but in case if you want to change that you you will be able to drag and drop as well rather than right clicking and selecting i can drag and drop that in before this so like multiple ways again for you to go and then perform the move or copy one way is to right click select more copy and then specify where exactly you wanted the worksheet to be moved the other option is just select drag and drop on the position where you want this worksheet to be shown that makes things easier for you yep there are multiple ways and what we saw was a couple of ways which you could move the worksheet so that covers the uh worksheet uh options here what we saw was how to add a worksheet delete a worksheet rename a worksheet and few other operations that you could perform on a worksheet there right so the next thing what you're going to look at is uh how do i save the worksheet or the excel uh in a different format by default when i click on file and i click on save as it shows me a browse option right and when i click on the browse option you will see that i can save this particular excel sheet into multiple uh file type so what we saw previously was excel workbook which is a default uh format which is based on open xml documentation uh or open xml document format right the other format that you see is also the excel uh uh 97 to 2003 which is like an uh sorry not a template you'll see something called xls format yeah which is like excel 97 to 2003 which is like a dot xls format this is also another format of excel which is uh used by quite a few people who are using the old version for example imagine someone is using microsoft office 2003 for example and or older version before office 2007 they tend to use the xls format right and if those files are provided to you and if you're running on a latest version of microsoft office you can still be able to open that in the new format but the other way around if you store a file in an xlsx format which has a different format altogether if someone has got a very old version of microsoft office or microsoft excel like before microsoft 2007 office 2007 they won't be able to open these files and it's going to just provide an error message or a warning message saying that unable to open this file right so that is one of the file form these are two default file formats that you're going to see being used more frequently excel workbook uh xlsx format and xls format the other default formats uh or the other formats which the users could save is the xls m format which is like a macro enabled workbook so in case if you have macros uh or if you're using some kind of programming within your uh excel sheet which will look at how to do a basic programming probably in a week five uh those kind of formats could be saved as xlm mx format but apart from that if you want to store the entire file as a csv file you could use a csv csv is basically everything in a comma delimited format you could save that as well for example let me click on csv for example yeah i would say book three dot csv in this case and i click on save yeah i've saved this file in a csv file yeah that's fine that's fine it's all right so now let me uh open that file which is in a csv format just a minute i'm opening it on another window let me yeah book three i'm opening this file and right click i open the same file which i've saved as uh the csv file which is like a book three dot csv if you see that the csv file are nothing but every cell has been represented uh with a value and each cell is delimited by a comma by default right in this case register number is the first column what you see in the excel worksheet but since we have saved that in the csv format each row is in a multiple lines and each columns are separated by comma it's it's the same data but represented in a csv format but a csv file would lose all the formatting there are quite a lot of features which you might not uh get when you have a csv file and when you use an uh the normal file which is like a dot xlsx format then you'll get most of the built-in functionalities like formatting and other stuff so let let me open the file back so if you want to know whether your what file that you have really opened you will see that on the title bar here if i zoom in again on the title bar you'll see that i've opened book three dot csv uh so that's a csv file so in case if i want to open the old file which is like a book three dot x xlsx file what i need to do is click on file and click on open and you'll see there is a recently opened document which i can select book three dot xls6 format i select that and you'll see that the file is now open it's it's opening now within few seconds yeah it's open now and on the top uh title bar you should be able to see that this is more of book three dot xlsx file yeah that's what the title says perfect so now let me go back students thing so similarly your excel sheet allows you to save in multiple formats as well so what we saw was just csv as a one of the examples but in case if you want to save the entire file as uh for example a different format like a text format you will be able to do that and uh you have options where you want to store the entire worksheet as a pdf you just select the pdf and then i display them for example let me select pdf in this case and let me click ok save it's opening you'll see that the file is now named as pdf here just taking time and you will see the selected worksheet is actually saved as a pdf you see there is just a text somewhere and that is nothing but the data what you see here if i had selected students list for example and if i select file save as browse and if i go and select and in this case i select pdf and i click on save and i said okay [Music] it's trying to convert this excel sheet into pdf you just give few seconds it should be fairly quick and you should see them here and let me zoom in yeah you should see uh the same worksheet been converted into the pdf format like the way you see now on the screen so that's more of uh saving the excel sheet from the uh excel format to a pdf or any uh different formats what you wanted to uh save it as so there are plenty of formats which is available and you could use any formats that you want from the sheet so that covers the uh worksheet here so finally uh before we uh sign up for the day uh one final feature which i want to show for the day one is adding a comment within your excel sheet sometimes when you are working on a sheet and you want to indicate uh to uh in the the sheet some kind of information one of the easiest way for you to indicate to the other user who's opening up this file is to add a comment so one of the easiest way to add a comment within uh an excel sheet is select a cell which you want to highlight and mark the comment and i'm selecting same thing in this case row number two register number two i right click and i am provided with an option to say insert comment right when i select this i will see a small window being shown to me which is like hey this is the user uh that is trying to comment this is picked up directly by my excel i'll show you where exactly this is picked from and i could go and then put my comment as well to say uh let me just say let me select and i'll put in a comment this student is a bright student and is thick glass stopper just give an example so i save this and when i save this file and when someone else opens the same file and they will notice that as soon as they see this sheet they will see that the cell b2 which is uh the name central and you will see there is something within like a red uh colored on the top right corner of this particular cell let me zoom in you'll see here the cell top right corner contains something like a red colored one and when i mouse over on that particular uh cell you'll see that it's a comment which indicates that this student is a bright student and is a class stopper so there is another way for you to add a comment within your sheet uh what is that so if i select a cell and if i go to the review you see the new comment option which does exactly the same as the other one when i zoom in let me zoom in you'll see in the review tab or a review ribbon the you see there is an option called a new comment when you click on that you're going to see the same same option here comment this is a test user let me zoom out this is test user yeah so and if you want to delete a comment uh what i've added i can right click on the cell that i've selected you will have an option been provided to say either show or hide a comment or delete a delete comment yeah these are the two options that you're gonna get yeah so let me go ahead and then delete one of the cells here so in this case what i'm gonna do is i'm gonna go ahead and right click and i'm gonna say but delete so this will delete the comment for me and i'll delete the other comments as well so it's easier to read the other comment yep and let me save the file so what we saw now was basically how to add a comment and how to delete a comment uh either using the context menu by right clicking on a cell and then adding the comment or using the uh review tab the comment section on the review tab so these are multiple ways which we could go and then add the uh tabs there so this sums up the uh day one uh training what we uh uh agreed upon so as a part of the next step what i'll be doing is i'll be sending out few uh learning materials uh via email through your department or through your direct directly through your email by providing information on what you need to learn until uh we for a week's time until we start our next session on monday and i'll also be providing couple of assignments so every week you're gonna get two assignments going forward for next five weeks i'm gonna provide the assignments on microsoft excel and where you need to be submitting and how you you should be submitting these assignments on microsoft excel and the deadline by which you you should be submitting it so i'll provide all the information to all the registered participants via email so if you have any questions please please uh feel free to uh ping me uh on the uh uh the chat here or uh you guys know my email address feel free to drop out an email to me if you have any questions on uh the microsoft excel so otherwise uh signing off for the day have a great day uh people and then see you on uh next monday for the day 2 of the training thank you

Channel: iSenthil

Views: 5,322

Rating: 4.9768786 out of 5

Keywords: Microsoft Excel, Nirmala Womens College

Id: 1_gXqpwPc-c

Length: 67min 50sec (4070 seconds)

Published: Mon Sep 27 2021