hi and welcome to up for excel live i'm
john i'm going to be talking you through
the settings in excel that are going to
save you
time from the get go every single time
you use
excel
okay
let's get straight into it then so
the main settings on excel
when you go into the file menu on the
ribbon and then click options you'll
you'll get this sort of options box and
this is where all the settings are
and there's you know absolutely hundreds
of them really uh maybe even thousands
and most of them are completely fine but
there's a few that as soon as i get a
copy of excel i'll tweak them because
they'll just save you that bit of time
and then there's a few other tips i'll
give you as we go through these
the general tab the first one i'd like
to sort of point out which is somewhere
near the top oh yeah here we go live
preview
and you might think well what's live
preview well all it is is
if i was to sort of i don't know say
highlight those cells and hover over
some of the menu commands here
you can see that it's changing live on
my screen now provided you've got a
decent sort processor and stuff this
shouldn't actually be using too much uh
cpu and it shouldn't slow things down
but it's massively time saving then like
clicking on it certainly you don't like
it and then going back and it's
particularly great if you say do format
as a table
and i don't let's just say pick that for
the moment and that's kind of ridiculous
but as i hover over the different
images here you can see what that's
going to look like
and and you can you can pick whatever
whatever you want so
just do control z to get rid of that so
live
preview
really handy little setting it's
actually on by default so you probably
won't
need to necessarily do anything but
jumping over me words here
but you know worth worth making sure
that it is ticked
so ones you might want to change
is um the first one's the calculation
options now
again it's going to depend on how big a
workbook you're using but if you're
using a massive spreadsheet and you're
finding it quite slow
whack it on to manual calculation
is a great way to go straight off all
you have to do is push f9 every time you
want it to calculate
and
it will just be the same as you know
just speed things up massively now as
default though i would leave it on
automatic because it's quite easy to
forget about a manual calculation
but it's well worth knowing that it's
there and that's something that you can
play with
data and proofing there's not really
much i would say about that
um
oh sorry yeah i didn't want to mention
another big thing in the formulas
section error checking
now by default you'll have this on error
enable background error checking and you
know most of these will be ticked if not
all of them
saying you know what it's going to check
for
and
if you're using the big
spreadsheets or spreadsheets
on servers or you know cloud-based stuff
this is a massive killer of time it just
slows everything down and if you kind of
know what you're doing in excel you you
don't need this anyway
i mean
you know i've just unticked everything
because
you know i use my own kind of versions
of error checks and things like that in
spreadsheets i don't need this sort of
stuff there's other ways of doing it
which i think are you know far better
and far less resource intensive i mean
if you're brand new to excel maybe you
might want some of these and i suggest
actually you go off and read those just
in case
you think well actually that is the kind
of thing i would imagine i might do by
mistake
take it by all means but just know
that with this going on in the
background all the time uh it's gonna
hugely slow your spreadsheets down and
the bigger the gap the more noticeable
it will be
when you save things
you hit save as it goes straight into
like some kind of location
and you navigate away from that to the
place where you save everything
every single time don't need to do that
just change your default local file
location to where you're going to
actually save everything
the vast majority of the time that will
just save you a bit of navigation each
time saved new spreadsheet is well worth
doing
on to the advanced stuff and not really
advanced just an advanced tab but
there's a couple of things in here i
always untick straight away
first one is alert before overwriting
cells
so i'm just going to re-tick that
so you can see what happens so if only
it's down decider i want that there so
i'll drag it over there i get a
message and there's already that ahead
do i want to replace it
okay
now
if you've got a lot of that to do that
can be a real pain you know you might
say well what if i do it by mistake it's
handy to have that warning it's kind of
not because if you do it by mistake you
just hit control z and undo it whereas
if you um
have that setting switched off
right
now
it just does it yeah and it just saves
like reaching for the mouse or anything
like that
and it just saves a bit of time i think
and and that sort of error message
especially sometimes when you're doing
like a whole series of of moving stuff
around on a spreadsheet can become a
real pain to have to keep going to the
mouse clicking okay or even if you hit
enter or shortcut k still a pain take
that setting off will be my advice
also on advanced
allow editing directly in cells now this
is my personal sort of top tip setting
yeah if you don't
this is the one that you should
definitely untick i think on all
versions of excel because i just cannot
see the benefit of it somebody please
say you know if somebody knows the
benefit of having this setting
then i'd like to know about it let me
know in the chat after you've heard what
i'm going to say about it
so i'm going to tick it on at the moment
so you can see kind of what it does and
it's on by default
so when you click on a cell if i double
click on that cell for example highlight
everything and i can do all the editing
in this cell puts the whole formula
spills it out
here and i can you know move around and
i can say yeah i want to add
say that cell something like that right
okay
why is that of no benefit because i
could have just done it up here you know
that and that will always be identical
so there's no advantage i can say of
doing
it here
and there's a major disadvantage because
if i want to add say g18 to this
i can't even click on it because it's
part of the same this the formula now is
covering it up so if i want to say
you know edit directly in cells normally
that if i want to say it was that plus
um
that and then i decide oh actually i
don't want that cell selling it anymore
it's it's really hard to click on it so
anyway
i think i've explained myself as to why
there's no advantage
now there is an advantage to switching
it off because when you switch it off
so this setting here
now
let's zoom in on this a bit
when i hit f2
same thing
but instead of editing it here i edit it
here so now i can easily access all the
cells around it no problem nothing's
covered up so that's first slight
advantage and then the other advantage
is when i double click on it it
highlights everything it's linked to
and which is something you feature that
would not be available
if
i didn't um have this setting unticked
and if it's just linked to one cell
double clicking on it we'll jump
straight to that cell so this one i
think you know if i link it to this
other sheet for example here
now
when i double click on that you'll run
straight through to the other sheet so
it's actually quite a useful
spreadsheet auditing feature now
so definitely untick that little setting
there
right what else have we got
so customize the ribbon
so strongly suggest there's one thing
you should do on here and that's come in
and tick everything why not i mean
by default the developers normally
hidden
why
i guess maybe they think you could do
some damage with it but
ticket you know
this is not like these are using up any
space or real estate on your screen you
know you'd have to have incredibly small
screen for you that you know or tons and
tons of different ribbon bars before you
start encroaching on here or anything so
just just tick that it's fine
and it just saves you a bit of time
in the long run
you won't need to kind of add it when
you need to get to things like any other
commands on here and i think drawing is
also kind of hidden by default
the other thing is the quick access
toolbar now
this is probably one of the most useful
things in the settings that you could
ever get to actually
you have
you can put as many different things on
the quick access toolbar as you want
and the quick access toolbar is this bar
right at the top here
along here and you can make it show
lower down below the ribbon if you want
but
the main point i'm trying to make is not
that you know it's quite handy to be
able to go up and click on things you
use a lot yes that is handy but what's
far more useful about it is when you hit
the alt key
it numbers everything
in the quick access toolbar if i have
more it would be like zero one zero two
etcetera and the numbers one to nine
when you hit alt and then that number so
alt one for example there would give me
that
and zoom out is open two
and now i've got like effectively a
shortcut key to zoom in and zoom out you
know
really really fast and you could do that
for whatever it is that you're going to
be using i put them on there because i
want to do live broadcasting it's quite
handy well zoom in and out of the screen
a click of a button
and you know we've got file open on four
for example 0.4
um you know there's various things in
there so it's really really useful set
up you've got nine spaces
before you start having to do like
multiple key sequences pick wisely you
know you tap your top nine features get
them on that quicksand quick access
toolbar and you're gonna save a whole
lot of time
that's it for my settings that's how i
set up excel i hope you got something
from that i really like to hear about
the settings that you like as well
because i know everyone has their
different preferences of how they like
to set things up and i'd like to know
why because maybe i don't know of the
reasons
so give us a shout
um if you're watching this on recorded
put something in the comments i can
reply to you if you're watching live now
put it in the chat and i'll answer it in
a second when i go and have a look