Hey this is puneet back again and in this video we will looking into three options that can save

your lot of time while working in excel and let me tell you

you I personally use these tips every single day while working in excel so let's get started

all right so the first option is aoubt using custom list

so custom list is basically a predefined list of values

that you can save in excel and than you can use those values just by entering

the first entry from the lsit so for example if I type

and than if I drag this cell upto

the row 16 I'll get rest of the enteries from this list

so this list basically a list of names and the first entry is the first name that I can use as a header

so rest of these fifteen entries are the first names so in this way

you can predefined a list that you want to use frequently in excel

so you no need to enter those values again and again and you not even need to copy paste values

from somewhere else simply need to enter the first entry and than you can drag and drop

that values upto the rows where you want to enter the values

now let's see how we can create these custom list so here I have this small data set and I want to

convert all of these columns into cutom list so the first thing is to go to the

fie tab and than more and click on options and from here I need to go to the

advanced and drag it down to the end and than click on edit

custom list and you can see here we have few custom lists that are already

you have by default in excel so when you enter jan and you drag it down

upto the 12 rows you will get the name of the months and in the same way

you can get the name of the days as well and here you have 2 different ways to create this

list first is to entering values manually into the list enteries

and the second is to import values from a range of cells so here I am gonna

use is button to import a range the first is the column

a and than click on import so here I have this

list of custom values here and again the 2nd list import and than 3rd

import and than 4th import

five and 6th

the last one alright so rightnow I have 6 custom list here and I am gonna enter

these list into worksheet simply by using the 1st entry from the lsit

even if you delete this data from here those entries will

be intact and you can use those entries anywhere in excel so for example if I want to insert

first name so I am gonna use the heading first name the first entry from

the list and than I need to drag it down yes

and if I want to enter gender I need to use the first

value from the list and than drag it down

and if I need email I am gonna enter email and than

than drag it down the custom list in excel is for the

data that you frequently use every day while working in excel

allright so the 2nd option is to use flashfill you might be aware of this option but you are

not using it so its a small reminder for you to use it more frequently while working with

data in excel so here I have these three columns of name first name middle name and the last

name so what I want here is to combine these values from three cells and want it

in a single cell so I am gonna enter the first entry

and than I am gonna use the shortcut

key contol+E so it gives me

the combination of all three cells for all the entries that I have in the

columns and in the same way if I want to extract names from these email ids so I am gonna do this for

first entry manually and than I press the shotcut key control+E to get it

for rest of the entries and now If I press

control+E gives me the names from rest of the entries

so you can use this flashfill option whenever you dealing with massy data and you dont want to use

heavy formulas to clean the data now this

3rd tip is quit useful for people who are into data analytics and people who actally use pivot table

alot so what happen is when you insert a pivot table you go to the insert

tab and than click on the pivot table button now here this is a small checkbox

that says add this data to the data model so when you click on this

and click ok and than you create a pivot table normally the way you create it

now

with this you get option in the Pivottableanalyze tab and than

refresh dropdown you get this option connection properties

now when you click on this properties so you get this option to refresh your pivot table

after a specific time so for example if I want to refresh my pivot table after every one minute

so I am gonna enter 1 here and than click ok

and now I am gonna go to the data so I am gonna delete some of the

entries to see if this option actually works so right now the total I have is

25173 and the total I have in pivot table is

25183 so there is a differce of 10 quantities

so here wait for a few seconds

so it refreshes itself after every one minute

so in this way you can set a timer for example 10 minutes 20 minutes the time

whatever you want and specially helpful to deal with lot of updation in your data

so friends that's it for this video I hope you found this video useful and make sure to subscribe

to this channel beacuse I'll be coming out of than lot of cool excel tips and tricks

and tutorials in coming weeks and don't forget to share which excel tip you most like

from this video and I'll take to you in the comment section