- 5/28/2025
Top 20 Excel Tips and Tricks _ Excel Tricks 2025 _Exceltips _Exceltricks
Category
📚
LearningTranscript
00:00वो अख्सल का क्योंको, इस अख्सल तरहां अबना निया वेटियो है।
00:04बर सम्भाषने के लिए हम आप पास ट्रिक्स 20 चार के लिए इन्देंट करेंगा, जो आपकी जाब प्रोफाईल में एक नवां करेंगा।
00:12देखिए, अधिक्षा की बारे में आने पर अधिक्षा के लिए ज़िन्दा के लिए ज़िन्दा हमें चीज़ा करें, तो वो बहुत अगर्बार है।
00:19क्योंकि अन्धेमाने तरने के लिए आप एक्सेल ने तरह गयरी जिन्दा करते हैं.
00:25बहुफ़ना, EXCEL आपके भ्यार ranष्मत्मी पर हाण से लाबतर तुम्हारे सिधिया अफाल neat observing तगल्या, नी तुम्हारे सिद्या मन्ध कत pigel ४ प्रस्तृ राक तुम्हारे प्रस्तु भगाड़ा पनाल क�od
00:55Please like and share this video
00:57Without any delay, Let's start this video
01:01So Dear Friends,
01:02As you can see,
01:03We are here on the computer screen
01:05And first of all
01:06Let's talk about the first trick
01:07Which is custom list
01:09What is this custom list and how does it work?
01:12Let's first understand it
01:15Normally, I will enter January here
01:18I entered January here
01:21And if I come to the corner
01:23And drag it
01:25तो आप भी वो देख सकते है कि वाड़ सा हमारा है जब यह मनस में प्रीक्षे है .
01:31गरमारा, और अगर मैं सुन्डे मंदे का लिस्त रखा रहा हूं के लिए,
01:35Like I wrote Sunday here and now I am dragging it here in this way.
01:41So it's list is being created here.
01:44But I want that
01:44I have any data of mine
01:46from my side and I want to create my list in it.
01:50As you can see here, there are so many stock items here.
01:54And I want that if I write CPU and if
01:56I drag it, then the rest of the items
01:58should be automatically shown on dragging.
02:01So we can do this
02:02with the help of custom list.
02:04वस्ते कारीश से की माज है और वस्ते का छोड़ने के लिए जो कोड़ने के लिए क्योंने करें
02:07मुझे अक्रेल बार कहुए, वदिया है कि इसी लिस्ट को प्रशोक करने के लिए, आप ने तरह गप नहीं गपना है
02:16Here I had typed mouse and now I am dragging it so mouse is coming everything here.
02:21But now I want that if I mention anything from this if I mentioned any
02:26Stock item then the list of the items ahead of it should be created.
02:29So for this thing, we use edit custom list now, how will we apply it?
02:34So first of all here, I delete the mouse which is written now, what do we have to do?
02:40We have to come here in the file and after coming to the file
02:43We have to come here in the options.
02:45जकौने में हमें अबार्जही रहा है .
02:48आप सही कहीं क Paige हमें बाच़ Pearson Tarot रहा है.
02:51यह तरह गोले फ्रफोरमच चाहिए हमें वरोड निंट कहा वरहा है.
02:54तरह अगर वरह मनस्वाड पर जानना पारतीता है,
02:58आपने लिए कलक़ना होगी.
03:01अगर कलक्छा हो रहे हो,
03:02यूट्यूब में यहाँ वही वाद स्वार्थ करें जीवने के लिए वरोगी है.
03:07आप वाद नदी देखा देशें,
03:08वाद नदी नागनी चली के लिए,
03:10अपने शाट्ट का जानुएरी पैबुयोरी यह अनूल है. अपने मकर पर देखने के लिए हैं,
03:17एक मस्ट एक आप प्राधान ड्राग करने के लिए दिसलिए इस लिस्स ेपिरे
03:20अपर अपर सक्षन एपर जन्युरि तो गुर्जोर जपिया तो लेकिन परीडियों के बारे में अनुप्प अपर जड़ें
03:27हैं, आरे वो स्थारा पार करते हैं. क्या है अनुप्प के बारे में, हम वाला आगेंग करना है,
03:34We have to keep the cursor here and I will delete it first.
03:38Because this list, the selection was of this blank area of ours.
03:41what do I do?
03:43I will select this list from here.
03:46I selected it.
03:48After this we have to import here.
03:50Now as soon as I click on import,
03:52You will see that The list of our stock items
03:54will come here at this place.
03:56So I import here.
03:58After importing,
04:00you can see that The list of our stock items
04:02Epic Drafts items has come here and now I click Ok here.
04:05After this, I do Ok from here also.
04:08And now if I type anything.
04:11Like I typed keyboard here.
04:14I typed keyboard.
04:16And now if I drag it.
04:18So coming here, I am dragging it in this way.
04:22So you can see that after the keyboard, here is CPU, mouse, RAM, SSD, SD, speaker, optic drag.
04:30ధరగ నాని ఆపకలల అనందలలలలల నా ఇపడి ఆమ్దలలలలల దానా ఇపి నందలలలలలల పతనా నందలలలల నంటలలల �
05:00अपना आप जानेगा बलाया बारा है।
05:02नहीं कुछ आप प्रभाव करना हैत
05:03तो से एक एक वीडियो पर समय थी
05:05आप से पनाना करना है और पर आप से बाद चामने करना आईxus
05:08after coming to options you have to come here to advanced after coming to advanced you have to come
05:16to the end and click on edit custom list here after doing this click on the list which you want
05:23to remove delete and here the option of delete comes now if you delete here then it is asking
05:30you here that your list will be permanently deleted do you want to delete it permanently
05:35we said absolutely we will click on ok so that your list will be permanently deleted from here
05:41now i click ok from here and i click ok from here too now if i mention any stock item in it
05:49like i wrote mouse here and now if i drag it then mouse will appear in all you see what is
05:55appearing in all mouse because we have removed the list of stock items from there
06:00now let's talk about our second trick which is proofing now what happens in proofing like you
06:08are typing cpu then what is the full form of cpu central processing unit but if i write cpu here
06:15and the full form of cpu appears so we can do this with the help of proofing option that is
06:21you wrote its short name and it gets replaced by its full name so we can do that with the
06:26proofing option now how will we do this thing so for this we will come back to the file and
06:33here we have to come to the options click here and here we get the option of proofing
06:40click on it after clicking we get the option of auto correct options here click on it now
06:48whatever you want to replace write that thing here at this place so i write cpu here and with
06:56what do you want to replace cpu central processing unit so let's write it here
07:03i wrote this now what will happen here whenever i type cpu what will automatically come in its
07:08place central processing unit so what do i do here okay that means first click on add and then
07:15press ok here and then press ok here once again now see whenever i type cpu here i wrote cpu and
07:25i press enter here so what will automatically come here central processing unit similarly if
07:32you want to give someone else's short name and make it full name then you can do that too what
07:37to do come back to the file come to options and here you get the option of proofing you have to
07:44come to the auto correct options this time i write gpuie graphical processing unit so here i write
07:54graphical processing unit in with i wrote this that means now whenever i type gpu what will
08:03appear automatically graphical processing unit from here i added it and then click ok here
08:10then after this i click ok again now i am typing gpu here and i am pressing enter
08:16so you can see that graphical processing unit has appeared here
08:20this saves your time many times you have to write it in full form but while typing in full form
08:26many times you make mistakes and it also takes more time but if you use the proofing option then
08:33with its help you just have to write in short and the full form will appear automatically in front
08:37of you now let's move towards the next trick which is customize quick access toolbar now what
08:43do we have to do in customize quick access toolbar normally if you look here at this place
08:49on the top the customize quick access toolbar is shown here now the advantage of this is that
08:54whatever options we have tools we can add them here in the shortcut how to add it has to be done
09:00here we have to come to the customize quick access toolbar and whatever we want to add
09:04we have to apply the right sign here that is if we just click it will be shown here now some
09:10limited commands are given here but if we have to add any commands other than these then we will
09:16come here in more commands and whatever command we want to add we will click on it here and edit
09:21here after which that command will be shown in the customize quick access toolbar but there is
09:27no need to do such a long process i will tell you a shortcut for this like if you want to add a
09:33picture in the customize quick access toolbar so what do you have to do you have to come to insert
09:38and come to picture so here we come to illustrations and here is the option of pictures
09:45here you have to right click after which you will see that the first option here is add to quick
09:50access toolbar that is what will happen as soon as you click on it the option of picture will be
09:56added here in the customize quick access toolbar so i click here after which you can see that the
10:03option of picture has been added here in the customize quick access toolbar directly you can
10:10apply the picture from here whichever picture you want to pick and if you want to remove it
10:15from here then simply right click here and the option remove from quick access toolbar comes
10:20here as soon as you click here the option of picture will be removed from the customize quick
10:25access toolbar so in this way you can add any of your tools to the customize quick access toolbar
10:33now let's talk about the next trick
10:36this trick is for those who like to work more with the keyboard
10:40because what happens when we work with the keyboard is that we are able to do the work
10:44faster and in a more professional way so what will we learn here using shortcuts with the help
10:50of alt now how will we do this as you can see at present we have some data of cells
10:58i want to select this data first i selected it now i want that whatever format is applied in it
11:05all the formats should be removed at once now what do we normally do to remove the format
11:10come to home after this come to clear and clear the format from here so a lot of our time is
11:17wasted in this so now you do not need to do this simply whatever data you want to remove or remove
11:24the format from you have to press alt for that
11:31after that press h after this you have to press e and then press f
11:34after which you will see that the format will be completely removed from whatever data you had
11:38selected as you can see whatever format was applied on it whether i have given a border
11:46here or given color to the text or given color to the cell all of it has been removed from here
11:52now i will explain to you how it works for now i press ctrl z after pressing ctrl z you can see
11:59that this data has come as before now i press alt here what will happen by pressing alt in the
12:05first step here you can see that the shortcut key of all our tabs is showing here that is the home
12:13key is h the insert key is n the draw key is ji so in this way the shortcut key of all our tabs
12:21is showing above now where do we get the option of clear in the home tab so what do we have to do
12:27to go to the home tab we have to press h by pressing h we have come to the home tab after
12:32this we have to come here to clear so which shortcut is it showing for clear e so here we
12:39will press e and what do we have to do we have to clear only the format if we have to clear everything
12:45then we will press a and if we have to clear only the format then we will press f as you can see
12:50the entire format has been cleared from here right now you might be feeling that there are
12:55too many steps in this how will we remember but when you practice this do it four times
13:02then you will get used to it and you will be able to do any of your work very easily and very fast
13:08okay now we have learned how to remove its format
13:14now let us talk about how to set its column width as you can see this date is written here
13:22but the width of the cell or the column is less here same as it is with the other columns here
13:28as well so we want to manage them so that its column width gets set automatically
13:33so there is a shortcut for this also what do we have to do for this
13:38we have to press alt h o and i what will happen
13:44all our data will automatically fit in with like earlier it was getting congested and compressed
13:51but now it is showing here completely correctly it has been fitted what has happened in this
13:58i will tell you this also by pressing ctrl z so here i did ctrl z now after pressing ctrl z
14:04the first thing i pressed was alt i pressed alt after pressing alt where do we have to come h means
14:11home so i pressed h after this i pressed o o is the shortcut for what format as you can see here
14:20so i pressed o what do we have to do after this we had set the column width automatically
14:26so here as you can see auto fit column width what is its shortcut i as soon as we press i
14:32whatever data we had it has been automatically set in column width so in this way you can use
14:39alt and whatever your work is you can do it quickly you can do it for multiple things
14:45i have told you the method how to use it after this let's talk about the next trick
14:51now friends if you have to copy any file then first of all you open that file and then you go
14:56and create a copy of it but now you do not need to do this you can copy the file without opening
15:03it what do you have to do for this so for this we come to the file here and after coming to the
15:09file as you can see i have many files here so what i do is here on this file it is written 25 formulas
15:18right click here and after right clicking it is written here open a file that is whatever our
15:27file of 25 formulas is a file of it will be copied and opened so here as soon as i click on open a
15:35file you can see that a copy of it has been generated here you can see here in the header
15:41the name of the file what was the name of the original file 25 formulas but here it is a
15:47generated copy of it so what is written here 25 formulas 1 1 means this is its copy
15:56so in this way you can create a copy of any file without opening it
16:02so for now i close this file and come back to my data
16:08now let's move on to the next trick now friends if you have to go to any cell in your excel sheet
16:14what do you do in this way you drag from here hold it from here or what do you do
16:20press the down arrow key and then go but now you do not need to do this you can directly
16:26jump to that cell by writing the address of that cell how will you do it for this we have to come
16:32here in the name box and the cell to which we want to jump we have to mention its address here
16:38like i want to go to j28 so i wrote here what is j here column and now i write 28 here
16:46now as soon as i press enter our cursor will automatically reach j28 as you can see what is
16:53this j column and which row is here this is 28 so in this way you can jump to any cell
17:01what do you just have to do you have to mention the address of that cell in the name box
17:07like i do it here now i wrote b1 and pressed enter so where will we come here b column and
17:1512th row apart from this you can also set the name of your data on the basis of name box
17:23now how will you do it so like this data is created with me here
17:27what do i do i selected this much data and i name this data
17:32like i placed the cell and now i press enter
17:38so now this range of my data is fixed in it with the cell name
17:43like now i have to do its total etc so here i do equal to sum now i do not need to select it
17:52what name did i set it with cell so here i write cell and after writing cell i just have to enter
17:59here when i press enter you can see that the sum of all these has come here
18:06so if you want to set the name of any of your data in this way and later want to use it with
18:10a formula then you can do it like this it is basically used when you have a lot of data
18:17very big data so you do not need to select the data again and again select your data once give
18:25it a name and later wherever you have to apply the formula then you can use this method now let's
18:32move on to the next trick which is the format painter now what is this format painter
18:39normally friends when you are given some work and are told that whatever format we have in this
18:43data you have to apply the same format in the new data that you are creating
18:48so brother if you know these things that what things have been picked from where
18:51then you will do it easily but those who don't know how will they do it so format painter is used
18:58for this with this you can copy the formatting of any data at once and you can easily apply it
19:05on any new data you want to apply how will you do it so like here in this way it just
19:12how will you do it so like here in this way a data is given here
19:20i want to copy the format of this heading given so i placed the cursor here whatever formatting
19:27you want to copy you just have to place the cursor on it and after placing the cursor here
19:32you have to come to the format painter so i clicked on the format painter after clicking
19:38i am dragging here in this way and i am releasing the mouse after which you can see that whatever
19:44formatting i had applied here has been applied here after this i want to apply the formatting
19:50of these borders etc here so for this i clicked here because now i want to copy only this formatting
19:59and now now i come here to the format painter i clicked on it after clicking i drag it from here
20:05in this way and now i am releasing the mouse so you can see that whatever formatting was applied
20:12here will be applied here in this way clear now i normalize it with ctrl z why did i normalize it
20:20first i will tell you that now so this was about the format painter and how you can copy and apply
20:26the format on any of your data but the format painter is used only once once means you chose
20:34any format clicked on the format painter and applied it after that the format painter does
20:40not work i will show you by doing it so here the date is selected clicked on the format painter here
20:47and as you can see this brush is coming this time i just keep the cursor on the student name and
20:53release it so i did this here you can see that the format has been applied on the student name
20:58but now the painter is not coming look at the cursor no brush is coming when the format painter
21:04is applied then the brush appears on the cursor but right now there is nothing like that but we
21:10want that whatever format we have copied from the format painter we can apply it multiple times
21:16so there is a trick for this what is that trick first i do ctrl z i made it normal
21:23now what do we have to do so now i have placed the cursor on the format here
21:28now you don't have to tap once here here you have to double tap i.e. double left click with
21:33the mouse when we double left click here with the mouse then the format painter gets applied
21:38to multiple things here that is first i clicked here after which you can see that the brush is
21:43coming here as well and if i click here now then the format painter will be applied here as well
21:49and in the cursor you can see that the symbol of the brush is coming here that is if i want to
21:54apply the format painter on any other data as well like i did here then it will be applied on
21:59these as well now if i want to remove this format painter then what do i have to do for this for
22:04this i have to come to the simple format painter and left click from here after which your format
22:10painter will move from here and after that you can do whatever work you want so in this way you
22:16can use the format painter and copy the format on your data now let's move on to the next trick
22:23which is flash fill now what is this flash fill and how does it work as friends sometimes what
22:30happens is that you get mixed data like here i have mixed data in which the name is written
22:36here rahul choudhary age is written and here his designation is written now i have to mention
22:42these three in different columns like the name has to be written here age here and designation
22:49here so if i copy paste them separately one by one and write them here then it is going to take
22:54a lot of time so now we do not need to do this with the help of flash fill we can do it very
23:01easily so what to do for this here we have to select rahul choudhary which is written here
23:07and copy it with control c after copying just go to the next column
23:15like here there is column a so after we have to come to column b place the cursor here and paste
23:20it here with control v after this you have to do control e as soon as you do control e
23:25you can see that all the names that were here have been mentioned here in this way or have come
23:30we come here and auto fit them at this place for this we will double tap here after which all
23:36the names have been fitted in this column in the same way if we want to take the edge here
23:41then we will select the edge and copy it here after copying place the cursor here just in the
23:48next column here we paste with control v and control e after which all the edges will come
23:54here and what is left for us in the end designation so select the designation from
24:01here as well and copy it after this come here and paste with control v and control e after which you
24:09can see that the names have come separately in a separate column the h has come in a separate
24:15column and the designation at the end has come in a separate column so in this way we have to
24:23so in this way with the help of flash fill if you have any mixed data then you can separate it
24:30whether it is name age or designation you can separate all of them with the help of flash fill
24:38after this let's talk about the next trick which is copy using clipboard option now how does this
24:44option work normally if you copy something like you have these multiple names i will tell you
24:51this option on this sheet like you have these multiple names but out of this you have rahul
24:58chowdhury and jeet diwakar and this prem diwakar apart from this these four names of kapal sharma
25:04have to be taken to a separate sheet that is you have to copy and paste now one thing you can do
25:10is to first copy rahul chowdhury and paste it on another sheet then do the same with jeet diwakar
25:16then do the same with prem diwakar and then do the same with kapal sharma so this is going to
25:21take time here these are just four names but suppose you have a lot of data suppose you have
25:28data of 500 people and out of that you have to paste the data of 10 or 15 or 20 people separately
25:34on a sheet so now how will you do it by doing this you will copy it and then go to a new sheet
25:40and paste it then if you come back and copy it and then go to a new sheet then time will be wasted
25:45so for this we are going to use the clipboard option now how will this clipboard work so for
25:51this you have to come here the clipboard option is given we will click here after clicking the
25:57clipboard option will open here in this way so whatever we copy here will keep showing here at
26:04this place for now like rahul chowdhury is here i copied it as soon as i copied it you can see that
26:11the copied name is showing here apart from this it comes on jeep diwakar i copied it here so it is
26:17showing here as well after this place the cursor on prem diwakar and copy it and now we place the
26:24cursor here on kapal sharma and copy it as well whatever we have copied is showing here as you
26:31can see here this manager i had copied it earlier because of which it is also showing here in the
26:38clipboard so i have copied this name here and wherever i have to paste it suppose i take a new
26:45excel file i did ctrl n here ctrl n after doing ctrl n a new excel window will come to us and as
26:55soon as i come here and click on paste all after which you can see that i double tapped from here
27:01it got fixed now you can see that whatever i had copied here all of them have come here
27:07so in this way you can use the clipboard option and whatever multiple data you have you can paste
27:13it together on one sheet i close it from here and come back to our data now if you want to clear it
27:23then the option of clear all comes here you can clear it from here after which if you copy and
27:30paste anything here then it will work like normal copy paste i close it from here and move on to
27:37the next trick the next trick is pin your important file now what is this thing so it means that we
27:46have multiple files one of them is our important file so what do we have to do whatever our
27:53important file is we have to search it find it and sometimes in haste we also make mistakes
28:01but with the help of pin you can keep your important files in a separate place and whenever
28:05you need you can easily open it and work on it for this you have to come to the file here and
28:13after coming to the file you can see here that the pin option is visible whatever file you pin
28:19it will be shown here inside it currently i have not pinned any file here due to which it is not
28:25showing here i will pin a file and show it to you so let's come to recent after coming to recent
28:31like here there is a file of january 2025 i have to pin it so to pin it this pin option comes here
28:39you can see that this pin is made so as soon as we click here after this this file of ours
28:45has been pinned if we come up and check in pin here then you can see that the file which we
28:52had pinned will be shown here separately in this way now whenever we have to do any work quickly
28:59we can open this file here and do that work now let's move on to the next trick now friends
29:06many times it happens that we work in the office and while working we spend a lot of time
29:12due to which we bring our laptop etc home and work at home but the theme in our laptop is a
29:18light theme and when we work at night it hurts our eyes so what can you do in such a situation
29:25you can change your theme how will you change so for this you have to come to the account here and
29:31after coming to the account you get the option of office theme here here colorful is given click on
29:37it whichever theme you want like here if i click on black then in this way you will get a black
29:44theme which is very suitable for if you are working at night at your home or in the office
29:50and if you want to take another theme like dark gray then you can choose this
29:56normally what i do is i keep the colorful theme here which we already had
30:01after this we come back from here and then go to the next trick
30:05now let's talk about the next trick which is remove duplicates
30:09now how does this remove duplicates work
30:14so for example suppose you have an attendance sheet salary sheet or any
30:19other kind of data and in that you have repeated the name of any candidate or any person
30:26now you will not check it one by one that where have i mentioned the name of that person twice
30:31because if the data is small then you will easily find it like here we have small data but if the
30:37lengthy data is of 1000 people 500 people or 2000 people then in that case you are going to have a
30:44problem so for that case we will use the remove duplicates option here with this any duplicate
30:50data will be removed in just one click now how will this work so for this we have to select this
30:57data because from this data we have to remove any duplicate value for now let me first show you
31:03what is duplicate here so here pintu is written and then here pintu is written that is the name
31:11of pintu is repeated twice here now what do i do here i come to the data tab and here the option
31:19of remove duplicates is given we have to click on it after clicking the option of select all and
31:26unselect all is given here all our columns are already selected you can see that the right sign
31:32is in front of all what do we have to do unselect all with this all our columns will be unselected
31:39and we have to select only the student name because on what basis do we need the duplicate
31:44value on the basis of the student name that means any duplicate student should be removed
31:50now as soon as we press ok here you will see that any duplicate name will be removed from here
31:58so i did ok here and here you can see that it is written that one duplicate value found and
32:02removed nine unique values remain same
32:07this is telling that one duplicate value has been removed and the nine unique values remain
32:12same here as it is so in this way you can remove any duplicate value from your data and correct
32:19your data now let's talk about the next trick which is filter top and bottom data now you must
32:27have heard and used this filter but how does this top and bottom data work so we have so much
32:36sales data what i want from this is that whatever sales data we have only the top five sales should
32:42be shown or the top five bottom sales should be shown you can customize the numbers according to
32:49your needs i currently have a small amount of data if you have data of 100 then you can find
32:55the top 10 from it you can find the bottom 10 you can find top 5 top 4 bottom 4 bottom 5
33:03now how to do this so for this we have to click on the filter button here i clicked where will we
33:09find the filter button inside the data tab after this we have to come to the sales here because we
33:14are checking on behalf of the sales right we have to click here and here you get the option of number
33:19filters as soon as you come here you get an option of top 10 here you have to click on this
33:26after clicking on this you just have to mention here how many you want to find from the top
33:31want to see so i will remove 10 here and write 5 and i just click ok here after which you can
33:40see that whatever top 5 data or sale is here it will be shown here in this way now if you want to
33:46make it normal then clear it here it will become normal similarly if you want to find the bottom
33:53then you have to come here click on the number filter if you do not click then these options
33:58will appear and you have to click on top 10 after this if you want to see the bottom then come here
34:04click on the bottom and this time i want to check the bottom four here so i wrote four here and
34:11clicked ok so that the four items or four sales of the bottom will be shown here in this way
34:20so in this way you can show your boss manager the top 10 or bottom 10 from the total sales of your
34:26company we clear it from here and to remove the filter click on it here after this comes another
34:35great trick which is called custom shot now how does custom shot work so first let me explain it
34:43to you a little bit that what we are doing like here we have the date of sales in the city here
34:49i want this city to come alphabetically that is first a then b then c then d in this way like
34:56first there is bengaluru here then agra then delhi then agra then bengaluru etc it should not be in
35:03this way it should be in an ascending order abcd so for this we have to select this data and very
35:09simple coming to the next step here we have to short a to z click and here we have to short by
35:15clicking on continue with the current selection after which you can see that our city has been
35:21set here in an ascending order now i want that the city and the sales are given in front of them
35:29so those sales should be shown in descending order that is the highest sales then less than
35:35that less than that in this way and on what basis should this happen on the basis of the city okay
35:41that is if it is agra then first agra has the highest sales then less than that then less than
35:45that now bengaluru comes so bengaluru has the highest then less than that less than that in
35:50this way same with chennai and in this way same with delhi so what do you have to do for this you
35:57have to use the custom short now where will you get the custom shot so first of all what do we
36:03have to do we have to select this data selected it what do we have to do for custom short we have
36:11to come to home and here in short and filter we get the option of custom short we clicked on it
36:18after clicking let's drag it here in this manner it has moved a little to the side now look here
36:24the filter that we applied on the basis of city is showing here but we have to apply one more
36:29filter on the basis of sales so for this we have to come to the ad level here we have to click on
36:34this after clicking what do we have to choose here sales so we take sales here and what do
36:40we want to keep in sales largest to small so after clicking here we click on largest to small and now
36:46as soon as i do this click ok here so you can see that whatever sales are there they are showing
36:51here in descending order that is first agra has the highest sales then less than that then comes
36:58the turn of bangalore highest sales less than that then less than that same as it is with chennai
37:03and delhi also that is if you want to show city wise sales to your manager that too in ascending
37:09order or ascending order then you can show it in this way in this way you can use custom shots and
37:17impress your manager after this let's talk about another great trick which is hide any data using
37:25format cell that is with the help of this option you can hide any of your dates when can you use
37:32it like if you have any data and out of that you want to show only a particular thing that is some
37:38selected thing to the person in front you do not want to show the rest of the things so in that
37:44case you can use this option or use a trick like here this is the data i select it and i want that
37:53whatever my cell is no one can show it no one can see it so for this here i have to press one with
38:00control due to which the format cell will open here if you are not able to open it like this
38:05then simply there is an option given here you click on it due to which the format cell will open
38:11now you have to come to custom here and remove the general written here and type semicolon here
38:15three times type one two three three times and as soon as i do this click on ok
38:23after clicking ok you can see that all your data here will be hidden
38:30what to do to bring it back remove the semicolon now keep one thing in mind that if you click here
38:38whatever data is mentioned inside it will definitely show here in the formula bar
38:45so it is not that it will be hidden completely permanently whatever you have worked inside it
38:49will definitely show in the formula bar but from here it will be hidden in this way
38:55now if you want to unhide it then select it with control
39:01press one and delete it from here and then click ok after which whatever data you have will be
39:06shown again after this let's talk about the next trick which is remove blanks now friends
39:15many times it happens that when we work we have to add and remove many persons
39:21so we add but when we remove the rows there remain blank
39:26as you can see here one row is blank here this is done this is done and this is done
39:32so in this way many rows remain blank and if we delete them one by one it will take time
39:40so to select all the rows together we have an option with the help of which we can select
39:45all of them together and if we want to remove them then we can remove it so for now let's learn
39:52to select them to select we have to select it from here which we have data so i select it
39:59so i select it and now we have to do control g after doing control g an option comes here
40:06whose name is special special option is found under go to so here we click on special what do
40:13we have to do after clicking we have to select the blank area so here we clicked on blank and
40:19here we click ok after pressing ok you can see that whatever blank area was there will be selected
40:25in this way now whatever thing or whatever work you want to apply on it you can do it easily
40:31what do we have to do we have to remove it so currently minus with control is the shortcut
40:37to delete a row or column so from here i pressed minus with control which will bring the delete
40:43option here and here i clicked on the entire row what will happen all the rows that are selected
40:49will be removed as soon as we press ok they will be deleted so i click ok here
40:57after which you can see that all the rows that were blank here have been removed
41:03so in this way you can use remove blank
41:07and whatever blank rows or columns you have you can select them and remove them
41:13after this let's talk about another great trick now you have to write a big name in it
41:20like i write salary of overtime i have to write here so i type here i wrote salary of overtime
41:29now salary of overtime is coming in only one cell here i want that the overtime written here
41:36should expand this cell and shift below it so normally you can do this work here with wrap text
41:43like you will click here what will the extra word do it will fit in this cell below but now
41:49you do not need to go to wrap text you can do this work in shortcut so here like i write again
41:56salary of overtime so i wrote salary off here after this whatever i write is becoming extra
42:04from our cell so now i have to hold alt as soon as i do it after holding alt i enter here so what
42:09will it do the shortcut will do the wrap text and now i type overtime here and i press enter
42:16so you can see that in this way whatever extra word will be there it will be managed below in
42:21this cell so now you do not need to go to wrap text from here you can apply wrap text by entering
42:28with alt in the shortcut after this let's talk about the next trick which is update full
42:34now how does this update full work so like here i have this data student names total marks
42:42i sum this data here i wrote sum and selected it from here and pressed enter
42:49after entering the sum of all these is coming out to be this much
42:54but if suppose i write here like i wrote raj and gave the marks it's 220 is okay
43:01so are the numbers that i have added here separately showing here no right so what do
43:07we have to do for this for this we will have to update our formula how will we do that so here you
43:13can see this sign the green one right you have to click on this cell on which the formula was
43:18on which the formula was applied you have to come here and click on this on this point okay
43:24so i come here and click on this point after this it is written here update formula to include
43:31cells that means you want to update your formula by including the cell i said i definitely want
43:37to do it so now as soon as i click on this here the 220 that i have added here separately will
43:42also be included here so i clicked here clicked after which you can see that whatever data i had
43:50updated here in this way has come in full the first way is this i make it as before by pressing
43:58ctrl z what is the second way what to do for the second way here you have to double left click
44:04after which the formula will open in edit mode and from here you can see this arrow cursor coming in
44:10this way you have to hold it from here and drag it down to the bottom okay whatever your update
44:15cell is you just have to come here and press enter after which your formula will be updated
44:21so in this way you can update any of your formulas what many people do is that they delete
44:27the formula and then apply it again but there is no need to do this if you are adding some other
44:32data then in this way you can update your formula after this let's talk about the next trick now
44:39friends many times it happens that we do not remember the date and we have to mention the
44:43current date so for this we apply the date formula but now you do not need to apply the
44:49date formula simply you can do it in shortcut what do you just have to do for this hold ctrl
44:56and press the semi-colon with it what will happen after that whatever will be the current date it
45:02will come in front of you in this way after this is the next and last trick if you want to mention
45:09the time after the date then you do not need to apply any separate formula for that for that you
45:15just have to press ctrl shift and semi-colon after which whatever will be the current time
45:20it will be shown here in this way and if you want to add an emoji then for that you have to
45:26press the semi-colon along with the window after this whatever emoji you want to add like here the
45:35cricket bat is done and here the book is done here we do this for the hand and from here we
45:39close it so in this way you can also apply emoji in it i hope that friends you must have liked
45:48this tutorial and all the tricks that we covered in this tutorial you must have understood them all
45:54if you liked this video then please like this video share it also do give your feedback by
45:59commenting see you in the next video till then thank you
Recommended
50:17
1:17:04
25:30
46:39
43:53
44:38
0:46
2:11