13
Nov

# ðŸ“ˆMicrosoft Excel 2016 Spreadsheet Layout and DesignðŸ¤“

in the last video we saw how easy it is
to create useful worksheets simply by formatting cell for data and
calculations in this video we’ll continue to work with the same example
but we’re trying to simplify it a bit and make it easy for anyone to use
firstly let’s simplify it whenever you use a constant in an Excel worksheet
it’s a good idea to specify it externally to your calculations that way
you only need to enter the constant once and if it changes you only have to make
a single change to your worksheet that’s a huge bonus if you have hundreds of
rows using the same constant we have two constants in our worksheet the hourly
rate and the bonus amount so let’s specify these externally to the
calculation block the hourly rate is eight dollars bonus is \$25 now let’s change our calculation formula
to refer to these constants the basic pay is the weekly hours worked times the
hourly rate constant you can use the dollar sign to prevent Excel from
changing the referenced row and column address of the constant so you could do
this but there’s an even better way click on the cell with the constant in
this case eight dollars and in the name box type rate you’ve now specified that
constant with the name rate and you can use the name rate whenever you want to
refer to that constant like this now to the same with bonus not only does
that make the calculation easier to do but also easier to read having done that
you can now delete the two columns hourly rate and bonus amount simplifying
the calculation sheet considerably suppose you decide to increase the
hourly rate to ten dollars all you need to do is to change that one constant and
the worksheet will recalculate everyone’s final pay and it’s the same
with the bonus for Christmas you double the bonus as well easy now let’s add
some instructions and some better layout first the instructions even if you’re
the only person who uses the worksheet it’s good practice to give yourself some
basic instructions coming back to a worksheet after some time can be
confusing next some layout let’s add some
conditional formatting highlighting those workers who have earned a bonus and color and outlines to make the data
entry cells easier to identify and finally let’s protect all those
calculation cells that you don’t want anyone to change and remove the grid
lines in excel or cells are locked by default but this has no effect until you
protect the sheet so if you want to protect only a few cells you first have
to unlock everything go to the arrow here and select the entire sheet
right-click and select format cells unlock all the cell’s and select those
you want to protect right click on them and select format
cells select locked and if you don’t want users to see the formula in the top
bar also select hidden when you’ve selected and locked all the cells you
need to go to the sheet tab here right click and click protect sheet you can