1
00:00:00,00 --> 00:00:05,600
Hey this is puneet back again and in this 
video we will looking into three options 
that can save

2
00:00:05,700 --> 00:00:10,500
your lot of time while working in excel and 
let me tell you

3
00:00:10,600 --> 00:00:15,400
you I personally use these tips every single 
day while working in excel so let's get started

4
00:00:15,500 --> 00:00:20,300
all right so the first option is aoubt using 
custom list

5
00:00:20,400 --> 00:00:25,200
so custom list is basically a predefined 
list of values

6
00:00:25,300 --> 00:00:30,100
that you can save in excel and than you can 
use those values just by entering

7
00:00:30,200 --> 00:00:35,000
the first entry from the lsit so for example 
if I type

8
00:00:35,100 --> 00:00:39,900
and than if I drag this cell upto

9
00:00:40,000 --> 00:00:44,800
the row 16 I'll get rest of the enteries 
from this list

10
00:00:44,900 --> 00:00:49,700
so this list basically a list of names and 
the first entry is the first name that I 
can use as a header

11
00:00:49,800 --> 00:00:54,600
so rest of these fifteen entries are the 
first names so in this way

12
00:00:54,700 --> 00:00:59,500
you can predefined a list that you want to 
use frequently in excel

13
00:00:59,600 --> 00:01:04,400
so you no need to enter those values again 
and again and you not even need to copy paste 
values

14
00:01:04,500 --> 00:01:09,300
from somewhere else simply need to enter 
the first entry and than you can drag and 
drop

15
00:01:09,400 --> 00:01:14,200
that values upto the rows where you want 
to enter the values

16
00:01:14,300 --> 00:01:19,100
now let's see how we can create these custom 
list so here I have this small data set and 
I want to

17
00:01:19,200 --> 00:01:24,000
convert all of these columns into cutom list 
so the first thing is to go to the

18
00:01:24,100 --> 00:01:28,900
fie tab and than more and click on options 
and from here I need to go to the

19
00:01:29,000 --> 00:01:33,800
advanced and drag it down to the end and 
than click on edit

20
00:01:33,900 --> 00:01:38,700
custom list and you can see here we have 
few custom lists that are already

21
00:01:38,800 --> 00:01:43,600
you have by default in excel so when you 
enter jan and you drag it down

22
00:01:43,700 --> 00:01:48,500
upto the 12 rows you will get the name of 
the months and in the same way

23
00:01:48,600 --> 00:01:53,400
you can get the name of the days as well 
and here you have 2 different ways to create 
this

24
00:01:53,500 --> 00:01:58,300
list first is to entering values manually 
into the list enteries

25
00:01:58,400 --> 00:02:03,200
and the second is to import values from a 
range of cells so here I am gonna

26
00:02:03,300 --> 00:02:08,100
use is button to import a range the first 
is the column

27
00:02:08,200 --> 00:02:13,000
a and than click on import so here I have 
this

28
00:02:13,100 --> 00:02:17,900
list of custom values here and again the 
2nd list import and than 3rd

29
00:02:18,000 --> 00:02:22,800
import and than 4th import

30
00:02:22,900 --> 00:02:27,700
five and 6th

31
00:02:27,800 --> 00:02:32,600
the last one alright so rightnow I have 6 
custom list here and I am gonna enter

32
00:02:32,700 --> 00:02:37,500
these list into worksheet simply by using 
the 1st entry from the lsit

33
00:02:37,600 --> 00:02:42,400
even if you delete this data from here those 
entries will

34
00:02:42,500 --> 00:02:47,300
be intact and you can use those entries anywhere 
in excel so for example if I want to insert

35
00:02:47,400 --> 00:02:52,200
first name so I am gonna use the heading 
first name the first entry from

36
00:02:52,300 --> 00:02:57,100
the list and than I need to drag it down 
yes

37
00:02:57,200 --> 00:03:02,000
and if I want to enter gender I need to use 
the first

38
00:03:02,100 --> 00:03:06,900
value from the list and than drag it down

39
00:03:07,000 --> 00:03:11,800


40
00:03:11,900 --> 00:03:16,700
and if I need email I am gonna enter email 
and than

41
00:03:16,800 --> 00:03:21,600
than drag it down the custom list in excel 
is for the

42
00:03:21,700 --> 00:03:26,500
data that you frequently use every day while 
working in excel

43
00:03:26,600 --> 00:03:31,400
allright so the 2nd option is to use flashfill 
you might be aware of this option but you 
are

44
00:03:31,500 --> 00:03:36,300
not using it so its a small reminder for 
you to use it more frequently while working 
with

45
00:03:36,400 --> 00:03:41,200
data in excel so here I have these three 
columns of name first name middle name and 
the last

46
00:03:41,300 --> 00:03:46,100
name so what I want here is to combine these 
values from three cells and want it

47
00:03:46,200 --> 00:03:51,000
in a single cell so I am gonna enter the 
first entry

48
00:03:51,100 --> 00:03:55,900
and than I am gonna use the shortcut

49
00:03:56,000 --> 00:04:00,800
key contol+E so it gives me

50
00:04:00,900 --> 00:04:05,700
the combination of all three cells for all 
the entries that I have in the

51
00:04:05,800 --> 00:04:10,600
columns and in the same way if I want to 
extract names from these email ids so I am 
gonna do this for

52
00:04:10,700 --> 00:04:15,500
first entry manually and than I press the 
shotcut key control+E to get it

53
00:04:15,600 --> 00:04:20,400
for rest of the entries and now If I press

54
00:04:20,500 --> 00:04:25,300
control+E gives me the names from rest of 
the entries

55
00:04:25,400 --> 00:04:30,200
so you can use this flashfill option whenever 
you dealing with massy data and you dont 
want to use

56
00:04:30,300 --> 00:04:35,100
heavy formulas to clean the data now this

57
00:04:35,200 --> 00:04:40,000
3rd tip is quit useful for people who are 
into data analytics and people who actally 
use pivot table

58
00:04:40,100 --> 00:04:44,900
alot so what happen is when you insert a 
pivot table you go to the insert

59
00:04:45,000 --> 00:04:49,800
tab and than click on the pivot table button 
now here this is a small checkbox

60
00:04:49,900 --> 00:04:54,700
that says add this data to the data model 
so when you click on this

61
00:04:54,800 --> 00:04:59,600
and click ok and than you create a pivot 
table normally the way you create it

62
00:04:59,700 --> 00:05:04,500
now

63
00:05:04,600 --> 00:05:09,400
with this you get option in the Pivottableanalyze 
tab and than

64
00:05:09,500 --> 00:05:14,300
refresh dropdown you get this option connection 
properties

65
00:05:14,400 --> 00:05:19,200
now when you click on this properties so 
you get this option to refresh your pivot 
table

66
00:05:19,300 --> 00:05:24,100
after a specific time so for example if I 
want to refresh my pivot table after every 
one minute

67
00:05:24,200 --> 00:05:29,000
so I am gonna enter 1 here and than click 
ok

68
00:05:29,100 --> 00:05:33,900
and now I am gonna go to the data so I am 
gonna delete some of the

69
00:05:34,000 --> 00:05:38,800
entries to see if this option actually works 
so right now the total I have is

70
00:05:38,900 --> 00:05:43,700
25173 and the total I have in pivot table 
is

71
00:05:43,800 --> 00:05:48,600
25183 so there is a differce of 10 quantities

72
00:05:48,700 --> 00:05:53,500
so here wait for a few seconds

73
00:05:53,600 --> 00:05:58,400
so it refreshes itself after every one minute

74
00:05:58,500 --> 00:06:03,300
so in this way you can set a timer for example 
10 minutes 20 minutes the time

75
00:06:03,400 --> 00:06:08,200
whatever you want and specially helpful to 
deal with lot of updation in your data

76
00:06:08,300 --> 00:06:13,100
so friends that's it for this video I hope 
you found this video useful and make sure 
to subscribe

77
00:06:13,200 --> 00:06:18,000
to this channel beacuse I'll be coming out 
of than lot of cool excel tips and tricks

78
00:06:18,100 --> 00:06:22,900
and tutorials in coming weeks and don't forget 
to share which excel tip you most like

79
00:06:23,000 --> 00:06:27,800
from this video and I'll take to you in the 
comment section

80
00:06:27,900 --> 00:06:30,900