- 7 months ago
5 Hidden features in Excel _ Excel Time-Savers - 5 Hidden Features _ Excel Features _excel(720P_HD)
Category
📚
LearningTranscript
00:00class if you are also an excel user or preparing for excel interview
00:04then today's video is going to be very important for you
00:08because in today's video you are going to see five such hidden features of
00:12Excel which are going to be very helpful for your Excel job profile
00:17after watching today's video
00:19you will be able to boost your Excel work to a level
00:22so no doubt that this video is going to be very important for you
00:24of course
00:26the only request to you is that if you find this video useful and informative
00:30then please like and share this video
00:33so without any delay let's start this video
00:36so friends as you can see we have come here on the computer screen
00:40and now let's talk about the first feature
00:43so the first feature is formula auditing
00:45now as you can see i have created data here
00:49suppose you also have some length data
00:52a lot of data is created
00:53and what do you have to do in it
00:55wherever the formula has been applied
00:57those formulas have to be checked
00:59which formula has been applied in which cell
01:03now if you check one by one
01:05like here this salary sheet has been made
01:08so wherever the formula has been applied in it
01:11if i check it
01:12then first i will have to come here
01:13like here it is seen that formula is being applied in the form of full
01:17after this i come here
01:19so full is not being applied here
01:20then i click here
01:22so full is being applied here
01:23then gross salary
01:25so one by one i have to check which full form is applied in which place
01:33so if i check my full form in this way
01:36which full form has been applied where
01:38so a lot of my time will be wasted in this
01:41but with the help of the feature that i am going to tell you now
01:45you can view all your formulas together
01:47so what do you have to do
01:49you just have to hold the control button and along with that you have to press the
01:53tilt button
01:54after which you can see that wherever the formula has been applied
01:59it will be shown here in this way
02:01like it will come here in the salary of working days
02:04the formula has been applied here in the overtime salary
02:07so it is coming here
02:09it has been applied in the gross salary
02:10there is esi
02:12there is pf and in the last here is the in-hand salary
02:15so if you want to view all the formulas together on any of your data
02:21then you can use this feature and finish your work quickly
02:25what do you have to do to make it normal
02:29now the same thing will be done
02:31press the tilt button with the control
02:33after which it will become normal as before
02:36so it is simple
02:37if you want to check all the values together
02:39then press the tilt button along with the control
02:41after which
02:42wherever the formula has been applied
02:44that formula will be shown to you
02:46after this
02:47let's talk about the next feature
02:48so the next feature is advanced data entry setting
02:51now friends
02:53as you can see here
02:54I have mentioned some names of these persons here
02:56like Pankaj, Pushpendra, Vakas
02:59I have mentioned the names of people here in this way
03:02also, months are given here
03:04and I have mentioned here how much weight each person has in which month
03:08now suppose you are also doing any kind of data entry work
03:13and what happens in it
03:14whenever you mention any value or mention data
03:17and you have to go to the right side
03:19so what do you do
03:20many times you press enter by mistake
03:22but what happens by pressing enter is that instead of going to the right cell
03:27you come to the cell below
03:28now when you work in this manner
03:31your flow gets disturbed because you have to go to the right cell
03:34but when you press enter
03:36you come to the cell below it
03:38for example
03:39I will show you an example
03:41here I write the name
03:43like Keshav
03:44after this
03:45I give the weight of Keshav here
03:47like 74
03:48now what do I do here
03:50I have to press tab
03:51but when we work
03:52we become habitual of entering
03:54so if by mistake I press enter
03:56then we will come down here
03:58so what do I have to do here
04:00I have to press tab
04:01so that we come to the next cell
04:03instead of this
04:04you can also use the right arrow key here
04:06you are also moving forward here with the right arrow key
04:09but now I am going to tell you a setting
04:12so that when you press enter
04:14you can move your direction right
04:16left
04:16top up
04:17in whichever direction you want
04:19so what do you have to do for this
04:23you have to come here in file
04:26and here you have to come in options
04:27we click on this
04:30after this you have to come here in advance
04:34and after coming in advance
04:36you can see here
04:36it is written after pressing enter move selection
04:39so the direction is given here
04:43what is the direction by default here
04:44down
04:45that means when we press enter
04:47after that our selection will reach down
04:49that means we will come down
04:50but now I make it right from here
04:53after pressing right
04:54now I make it okay from here
04:56and now if I press enter
04:58then which side will it move to
04:59it will be right side
05:01like here now I press enter
05:03so you can see in this way
05:05my cursor or let's say cell active cell is going forward
05:08now I am going to put 72.5 here
05:12and then I am going to enter it on the right side
05:14now I am going to put 75 here
05:16and then enter it on the right side
05:17now let's talk about one more thing in this
05:19because many times when we mention the value here
05:22what do we have to do
05:23we have to write the value with points
05:25because weight has obviously not been accurate
05:28so for example
05:30suppose I have written
05:31Keshiv's weight in April is 74.5
05:34so I have to put a point here by default
05:37it is not that it has come automatically
05:39now we want that the point should be automatically applied after one digit
05:43that is
05:45I just add the value there
05:47and the point should automatically be applied to our value
05:50so what do we have to do for this
05:52for this we have to come back to the file
05:54and after coming to the file
05:56we have to come to the options here
05:58after this we have to come here
06:00you have to go to advance again
06:02and after coming to advance
06:04you have to click on the option to automatically insert a decimal point
06:08after this
06:10the place given here
06:11after how many places you want to add a point
06:14I can put one here
06:15that is
06:16after one digit
06:17what will be automatically applied there
06:20decimal point
06:21now I do OK here
06:22and now if I apply any value here
06:25like if I write 70.3 here
06:28then I do not need to put a point
06:30I am just writing 723 here
06:32and I am pressing enter
06:33so you can see that in this way
06:36you can use these features
06:37and complete your work in a very easy way
06:39now after this
06:43let's talk about the next feature
06:44which is find and replace formatting
06:46now friends
06:48suppose you have a lot of data
06:50and formatting has been applied to it
06:52like here is the students report
06:55and here as you can see
06:56here the role numbers are given
06:58this is the class
07:00this is the name
07:01and this is the total number
07:02now which class's data is this
07:045th class
07:05this is the 6th class below
07:06similarly
07:07the data of other classes is also created
07:09like 7th
07:118th
07:129th
07:1310th
07:1311th
07:14and lastly 12th
07:15as you can see
07:17now here I want that
07:19wherever this formatting is applied
07:20it should change
07:22that is
07:23I want to apply another formatting
07:25now one way is what we can do
07:27first of all
07:28we can come here and change its color
07:30like I did this and then coming here
07:33I applied it in this way
07:35now whatever we want to do here
07:37bold italic
07:38we can do that
07:39after doing this
07:41let's come to the format painter here
07:43I double tapped on the format painter
07:45on which it will remain applied
07:47after this I drag it from here
07:49so this formatting will be applied on it
07:51and then if I have to change the rest of them too
07:54then I will have to go to each one
07:56one by one in this manner and change the formatting
07:59so obviously it will be a waste of your time
08:02because there is a lot of data and going and changing the formatting in each one in this manner is a waste of time
08:08so what do we do for this
08:09we get a formatting feature in find and replace
08:13where we can go and change the formatting of all our data at once
08:17now how will we do this
08:19so what do I do first
08:20I remove the click from the format painter from here
08:26and whatever data we have changed
08:28I make it normal with ctrl z
08:30so so here I did ctrl z and it became as before
08:33now what do we have to do for this
08:38so we have to press ctrl h after pressing ctrl h
08:42an option of find replace will come here in this way
08:45now what do you have to do here in the find what option
08:48here the format option will come
08:51click on it
08:52after clicking
08:54choose format from the cell
08:55that is what do we have to do
08:58the format we want to choose
08:59for that we have to click here and after that click here
09:04so we have told that we are finding this format and want to replace it with what
09:11so for that we have to come here and click
09:13after this click on the format here
09:17and whatever formatting you want to apply
09:19like I want the color here first
09:22so let's keep the color
09:24like this one has become a little blue
09:26let's come to the font
09:28after coming to the font
09:29keep the color of the font white
09:31after this if you want to change the style of the font etc
09:34here then from here I did this style and whatever size etc you want to keep
09:39I made it 10
09:40after this if you want to give a border
09:42you can also give a border
09:44whatever you wish you want to apply here
09:47currently I have applied only fonts and fill here
09:50after this you just have to click on ok here
09:52and what do you have to click on here
09:54replace all
09:56as soon as you click on replace all
09:59whatever data is in this formatting will be converted to this formatting
10:02now here I just click on replace all
10:06I clicked and it is telling that all done we made 32 replacements
10:11that means we have done 32 replacements
10:14now let's click ok here and close it
10:18after this if you drag it and check below
10:22you can see that wherever we applied yellow formatting
10:25blue color formatting has been applied there
10:27so in this way if you want to change the formatting simultaneously
10:32which you have multiple formatting
10:34then you can do it easily with the help of this feature and save your time
10:38now let's talk about the fourth feature which is customize ribbon tab
10:43now how does this customize ribbon tab work
10:46so friends as you can see here at the top
10:50we have some tabs like home tab
10:52insert drop page layout
10:55formulas data review view developer and help
11:00but sometimes we do some limited work
11:03for which we have to go to different tabs and choose things or options
11:07due to which our time is wasted
11:09but what we can do is that we can create a particular new tab
11:13and all the options that we need
11:16we can mention all those options in that tab
11:18so that we can finish all our work through that tab only
11:22now how will we do this thing
11:25so here you have to come to file and after that you have to come to option here
11:30after doing this here comes the option of customize ribbon
11:34you have to click on it
11:36after clicking here you get an option of new tab below
11:40so we have to create a new tab
11:42so for this we have to click here
11:44after this here on the new tab you have to left click and right click and then rename it
11:49because we want to change the name of this tab
11:52so we clicked on rename
11:54after this whatever name you want to give to this tab
11:58i will make it favorite here
12:00i named this tab favorite and click ok
12:04as you can see here a tab named favorite has appeared
12:07and whatever things you want to add in it
12:10where will you add from
12:11so come to this place and choose command from
12:14so from where do we want to choose that command
12:17so i want to choose from the main tabs
12:20that means whatever our tab is main
12:22so first of all we click on this arrow here on the home tab
12:26all the commands will open here
12:28and what do i do
12:31first of all i click on fonts and add it
12:33as soon as i click on add
12:35you will see that the command will be added here
12:37i clicked on add
12:39so you can see that the command of font has been added here
12:42after this i want to add numbers
12:44i added numbers and the number will appear
12:47if you want to add alignment
12:49then click on alignment as well
12:51add it
12:52after this i want to take some commands from insert
12:55so at this place we will click on home
12:58which will close home
12:59let's come to insert and from insert
13:01i want a table
13:02so i clicked on table and added it
13:05after this it will appear here
13:06and now i want things from page layout
13:09so i clicked on insert here
13:11it closed
13:12now let's come to page layout here
13:15and after this we take page setup here
13:18clicked on it and here we clicked on add
13:21now you can see that in this way
13:23all our commands have appeared here
13:25fonts numbers alignment table
13:29all these commands of page setup have appeared
13:32now if you want to create a new group
13:35you can also create a new group in which all these things will come
13:39the same thing has to be clicked here and you have to rename it by right clicking
13:44so that the name of your group will change
13:46now what do we have to do
13:48if i want to move these commands up or down
13:50then i can do it from here
13:52like if i want the numbered command to go up
13:54then the numbered command will come up
13:56if you want to move it down
13:57then the numbered command will come down
13:59if you want further down
14:00then click down one more time
14:01this is how it will come
14:02so if you want to move any command up or down
14:05then you can do it from here
14:06for now i click on it here
14:08favorite has been closed and here we also close the home
14:12now i want to keep this favorite command before home
14:16so for this we clicked on the favorite tab and from here we move it up
14:22after which you can see that the favorite tab which is before home has come here
14:29after this we just have to click on ok and here you can see that earlier the
14:33favorite tab has come here in which we had added fonts added alignment numbers tables and page setup
14:41so we had added all these things in favorite
14:45now you do not need to go to different tabs and do things
14:49you can directly use the favorite tab and finish your work quickly
14:53now how to remove it
14:56so removing it is also simple
14:58come to file
14:59come back to option and click on customize ribbon
15:02after this you have to click on favorite here
15:05what do you have to do here after clicking
15:08remove
15:09as soon as you click on remove it will be removed from here
15:13and now what do you have to do
15:15ok
15:15as soon as you click ok
15:17it will disappear from here as well
15:19as you can see it disappeared from here as well
15:21so in this way you can create any tab of your own in whatever commands you use on a daily basis
15:28you can mention them in it and finish your work quickly
15:31now let's talk about the last feature which is focus cell
15:36now what do we do for focus cell let's come to the data
15:40now what is the use of focus cell
15:42so friends sometimes what happens is that if we explain data to any of our seniors or colleagues
15:47then we have to tell him that brother focus on the f column and the eight row
15:52that is focus on this cell which i am telling things
15:55now he has to pay attention that friend which is the f column and which is the eight row
15:59many times mistakes are made in these things due to which your work gets spoiled
16:03so with the help of focus cell you can focus on the rows and columns of your data
16:08what to do for this
16:10come to the view and here on the view you get the option of focus cell
16:14you have to click here and you just have to click on the focus cell
16:18after which wherever you place the cursor that area row and column will be focused
16:23like if i clicked on 42 here then this column and this row will be focused
16:30that means now you do not need to tell anyone on which cell you have to focus
16:34if you click directly that area will automatically be focused
16:39so that you can easily explain things to any person and do your work easily
16:44i hope friends you liked this tutorial of excel
16:47and all the terms we covered in this video must have become clear to you
16:51if you liked the video then please like and share this video
16:55also give your feedback by commenting see you in the next video till then
16:58thank you
17:06you
17:08you
17:10you
17:12you
17:14you
17:16you
Be the first to comment