Excel Basics: Build a Dynamic Gradebook with Formulas & Functions

welcome to excel Basics video number one in  this video we get to learn about formulas   functions formatting cell references and Page  setup and that's just video number one in this   new Excel Basics 10 video class I'm going to  cover all the topics to make you awesome in  

Excel now in video number one we're going to  build this amazing grade book and it's going   to be totally Dynamic so if I change extra points  from 10 to 15 before I H enter Watch What Happens   here enter everything updates here's the topics  I'm actually going to start off with hey what is  

Excel we'll talk about the amazing fill handle and  angry rabbit default alignment entering data old   school formulas and the amazing Microsoft 365 make  our life easy Dynamic spill array formulas we'll   talk about these functions including the amazing  new X lookup function we'll talk about the ever  

important number formatting xl's Golden Rule and  even page setup now for those of you who want to   download the Excel workbook and follow along with  everything in this video either below the YouTube   video or at my people website you find the link  and rightclick save link as that opens the save  

as dialogue box I'm going to select a particular  location and because there's lots of cool files   in this free class when I select desktop I'm going  to click the new folder button or use the keyboard   contrl shiftn I'm going to name it something like  my Excel Basics class and enter and then enter I  

see the folder path here here's the name of the  file that I'm going to save into this folder   when I click save on the taskbar you can click  this icon for the file explorer or it used to   be called Windows Explorer go to the desktop find  your particular folder you created select it on  

the left then on the right I'm eventually going  to have lots of files here but to open this I'm   not going to open Excel and then controll o to  open I'm just going to double click it in Windows   explore and now our file is open I can see up in  the title bar the name of the file now I have have  

some notes here this is the sheet where I'm going  to talk about what is Excel but if you contrl P   I've done page setup so here's a complete cheat  sheet with all sorts of notes over here on notes   control P these are all the awesome notes about  formatting formulas functions and Page setup now  

before we go over to the grade book sheet and  build everything from scratch I want to start   start off by going to wiie what is Excel now we  have an Excel file called a workbook file here   and actually let's jump down to nine ribbon and  10 quat up here Home tab insert tab page layout  

formulas all of these tabs together are called the  ribbon and they just contain commands where we use   a drop- down arrow to get to a particular command  or a button up here this is called the quick   access toolbar it has a couple items in a later  video I will show you how to completely customize  

this so all of your favorite items are right in  the quick access toolbar now if we look down to   number eight what does Excel actually do well it  can do anything period but broadly speaking it   does two things it can make calculations like we  have some sales numbers and we create a formula  

in this case using the sum function to add that's  an example of a calculation if I scroll over just   a little bit data analysis is the second thing  that Excel does and all that means is go from   raw data into some useful information in this  case we wanted to see which product had the  

largest sales so we created this report using  the pivot table feature and then clearly we   can see Carlota had the largest sales so making  calculations and data analysis going from raw   data into useful information to make a decisions  that is what Excel does now the structure of excel  

well at its Essence it's a two-way grid hey this  is called a column and columns are designated by   letters d c CB rows are designated by numbers so  the intersection of a column and a row notice only   two directions but the intersection that's called  a cell and the name of the cell d8 if you look up  

into the formula bar hey hover your cursor right  there that's called the name box so we can look   up there and see it says hey that's d8 right here  that's C4 so we have columns rows the intersection   is a cell all the cells together is called a work  sheet or sheet for short down here the name of  

this sheet this is called the worksheet tab wi e  if I take my that's a selection cursor but if I   point right over the sheet tab notes I see that  Arrow I can click and move to a different sheet   now each sheet tab has the name of the worksheet  if we go back to wiie we have columns rows cells  

all the cells together make up the worksheet  the name of the worksheet is down in the sheet   tab now we've been using our cursor to select a  sheet but there's a great keyboard control page   down moves to the right activating the next sheet  control page up moves to the left perhaps the best  

way to navigate to Sheets is these are called  scroll arrows if you rightclick it opens up the   activate dialogue box you can select whichever  sheet you want click okay and it jumps right to   that sheet right click activate I want wiie click  okay now columns rows cells all the cells make up  

the worksheet the worksheet name is in the sheet  Tab and all the sheets together make up the Excel   workbook the Excel workbook file name is right up  in the title bar there's the quick access toolbar   that's the name of this file so don't mix up  the name workbook that's all the worksheets  

and worksheet that's just a particular worksheet  with all the cells all right let's scroll down   we have two other really important Excel topics  hey default alignment when you enter text numbers   logical or you actually accidentally get an error  there's a default alignment that tells you what  

type of data it is when you enter text it's always  align to the left numbers which can be numbers   times and dates align to the right if you enter a  logical true or false it's automatically centered   and capitalized and if you accidentally create an  air which we'll see later when we create formulas  

it's centered now let's come off to the side here  and practice entering some data and see if default   alignment can help us I'm going to type word and  enter the default alignment is to the left so we   know that Excel thinks that's text if I type 43  enter there is the default alignment to the right  

so we know Excel thinks that that is a number if  I select the cell and type 43.1 to uhoh I made a   typing error when I enter that default alignment  to the left that means Excel thinks this is text   not a number that's our visual cue that we  had a typing error I'm going to doubleclick  

backspace and now I have only a single decimal  so when I hint enter Excel thinks it's a number   same holds for time and dates if I type 8 A.M I  was supposed to type a space between the number   and the am but when I hit enter there's my  visual cue that I mistyped d double click  

type a space hit Tab and now we have the default  alignment to the right we know Excel thinks it's   a proper time value let's enter 12322 24 well  Excel knows there's not 32 days in December   doubleclick backspace get rid of the two type of  one and enter the default alignment lets us know  

we didn't make a typ in error this time that's a  proper date value the other way besides tracking   down uh data entry errors the default alignment  is really helpful it's a lot of the data in the   world is stored in databases and when we export  it from a database it often comes in as text so  

if you get a column of values like this you don't  even have to build a formula you know that formula   is not going to work because the visual CU tells  you that's text if I do come over and let's say   we want to add so we use the sum function this  actually will be our first Formula we ever create  

home editing and the sigma button if you click  it it'll put in the sum function but although   there are 450 amazing built-in functions like Su  and average PMT only one of them is cool enough   to have a keyboard and there it is alt equals  all right with the cell selected let's use our  

keyboard alt equals now it's polite it puts the  equal sign as the first character in the cell   all formulas start with an equal sign and here's  the amazing built-in sum function this number one   argument just requires that we highlight and I'm  going to use my selection cursor highlight some  

cells notice the dancing ants if you accidentally  highlight the wrong cells no problem highlight   the correct cells the correct three cells and hit  enter nope it's not going to work the sum function   F2 is programmed to ignore text now later in the  class we'll learn the Amazing Power query tool  

built into Excel that allows us to import data  from anywhere and assign the correct data type   so we never have to deal with this problem again  all right another important topic besides default   alignment is number formatting home number group  there's a drop down we can apply number formatting  

to numbers now let's just see one of the world's  most famous Excel problems hey 3 + 4 + 5 if I   click in the cell and hit F2 well the sum function  looks like it's working I can do that one in my   head it should equal 12 but what is going on here  F2 to put it in edit mode how can you add three  

four and five and get 13 the reason is number  number formatting someone came and reduced the   decimals if I click in the top cell and select all  four cells come up to number and I'm just going   to increase the decimal what number formatting was  hiding those decimals let's reduce and then select  

the cell on the surface of the spreadsheet we C3  that's number formatting applied on the top of the   cell up in the formula are number formatting does  not change the underlying number that number there   that the sum function is using to get a total  is 3.4 4.4 5.4 now when I have a situation like  

this I usually highlight all the cells and we're  going to use our eraser that wipes away all number   formatting home number drop down and the one  right at the top General this is like an eraser   no matter which one of these number formatting  options you applied when I use the Eraser General  

bam it wipes it all away and now we can see the  culprit number formatting with just making it look   a certain way without changing the underlying  numbers now we will definitely learn how to   officially change the underlying number with a  round function but you can't do it with number  

formatting now we want to look at another common  mistake that people make with number formatting   that affects the formula now here's a tax rate  .375 that means for every dollar you have to pay   3.75 pennies if we formatted this as a percent it  would be 3.75% now the person in charge of this  

says no the tax rate is actually 4% 04 so they  come up and they decrease the decimals and Bam   on the surface it looks like 04 so the person  comes over and creates a formula thinking they   have 04 now this is our second formula and all  formulas start with an equal sign as the first  

character in the cell and instead of highlighting  a range of cells I'm just going to click the cell   P35 with that 100 I need to multiply so I need  an Asis shift 8 or asterisk on the number pad   and then I select 04 four well I can do that in my  head that should be $4 when I hit enter the person  

says uhoh the formula is making an error the  formula is not making an error F2 formulas never   seen number formatting if I escape click in this  cell I can look up in the formula bar the number   does not change when you apply number formatting  it just displays the number in a certain way so a  

lot of people get in trouble with this this number  formatting does not change the number and formulas   never see the number formatting if you actually  want 04 you have to select the cell and type   04 the number on the surface matches the number  underneath and I get exactly what I want $4 now  

if we want to change it back to 0.375 I select the  cell .37 5 now before I hit enter I see the number   there but when I hit enter it's 04 that's because  when I click back on this cell I still have a   number formatting applied if I look up here that's  the underlying number now I'm going to use our  

eraser drop down General you want to make sure and  however you format it it matches the underlying   number if you're going to use it somewhere else  in a formula now the same thing holds true if   we apply percentage number formatting because  0375 again means 3.75 pennies for every dollar  

but we can add a formatting it won't change the  underlying number because that underlying number   is necessary to make calculations but we don't  have to multiply by 100 add a percent symbol we   can just simply select the cell I'm going to use  the drop down percentage I can see right there  

the preview this will give me percent with  two decimals showing and sure enough that's   just a way to format the number 0375 we still get  exactly the same thing if I decrease the decimals   oh that's 4% we don't leave it like that cuz it  misleads whoever is looking at this increase the  

decimals another thing about percentage number  formatting if I click in this cell and decrease   the decimal the surface shows me three I can look  up in the formula bar but actual numbers like this   changing the decimals is one of the only times you  can actually look up into the formula bar and see  

what's actually there contrl Z watch this I'm  going to select 3.75 oh it's not showing me the 0.0375 it's showing me the actual percentage the  only way if you get a spreadsheet like this to   check is if you come and apply your eraser General  then you can see the actual decimal that decimal  

is there it has to be there if our formulas are  going to be correct contrl Z one other thing about   percentage if I come up and decrease the decimals  4% look at that it's sort of polite it shows me at   least that 3.75 is the real percentage I'm using  again we don't leave it like that increase the  

decim decimals so whether you apply percentage  number format make sure to show enough decimals   so we can see what that number is if we leave  it as a decimal same thing all right so what   is Excel number formatting is so important it just  displays the number a certain way without changing  

the underlying numbers default alignment helps  us with entering data and gives us a visual cue   when we get text numbers we talk about the ribbon  the quat and the structure of excel now we can go   over to grade book we want to create our grade  book here but let's go click on this sheet here  

because this is what we're going to try and create  now notice I have a bunch of stuff off to the side   cuz I'm going to show you some cool tricks but if  I were to print this out I don't want that stuff   so I'll also show you how to do page setup so when  you contrl P it looks beautiful like that escape  

let's go over to Gradebook all right in our first  project here you're going to be amazed how many   different cool Excel tricks we learn in fact a  lot of the basics that you need are all in this   project so the goal is to create a grade book with  student name test one test two test three four  

some quizzes total scores percentage grade and  decimal grade so in cell B7 I'm going to type name   and instead of hitting enter which puts the thing  in the cell and moves the cursor down since the   next task is to move to the right and type test  one I'm using the Tab Key test one and since the  

next task is to copy this cell instead of hitting  enter or tab I'm using control enter that puts the   thing in the cell and keeps the cell selected now  why does it matter why do we have to use tab and   control enter instead of enter Because it saves  a lot of clicks if you know what you're going to  

do next use the right keystroke that saves a lot  of clicks over your whole lifetime that's like a   whole free vacation now we got to talk about that  little green box in the corner of the selected   cell that's a magic box called the fill handle  and we have to talk about cursors that is the  

selection cursor this is the move cursor we do not  want that one we want to hover over that fill hand   and when we see that one right there that's the  Crosshair that's at least what Bill Gates calls   it I like to call it the angry rabbit so I hover  over the fill handle I click with my Angry rabbit  

and click and drag and you got to be kidding me  it incremented tests anytime you have a word and a   number it will do that for example if we type quiz  one contrl enter fill hand handle with our angry   rabbit there are four quizzes now in just a moment  I'll show you a bunch of other amazing things that  

that Phil handle does now let's select cell K7  total tab percent grade tab decimal grade and   enter now notice the text is kind of hanging over  into the end column if we point between column M   and N there's the cursor that allows us to click  and drag and change the column WID now we want  

to enter names in the class we have Sue and the  goal now is to do something in the cell below so   this is where we use enter GG enter Shantel enter  Dean enter mcki enter and mo enter now just as we   have a total and grade columns over here I want  a row that'll calculate the average for each test  

and quiz so I'm going to type the word average  and since the next step requires that this cell   is selected I'm going to use controll enter now  the next thing is I want to highlight all this   and I could do it this way and then add borders  but instead there's an awesome keyboard either  

control asterisk on the number pad or control  shift 8 on the regular number pad now I know a   lot of of you will try control 8 and this brings  up the outlining grouping dialogue box you got to   hit shift to get to the little asteris on the  number eight so control shift 8 or I always  

use the asteris on the number pad control asteris  that is amazing it highlighted all of the current   region now we can add borders home font here's the  Border dropdown I'm going to select all borders   and when we add borders fill color font color  that's called style formatting that's different  

than number formatting number formatting is for  numbers now to the headers let's highlight and   add some fill and font color if we come to the  dropdown and add red if we printed this out it   would be hard to read you always want to make  sure that the value difference between the font  

and the fill color is big enough so it's easy to  read and the way you can tell is you squint your   eyes and if it's hard to see the difference you  need to increase the value difference in this case   if I was going to use red I would come over use  the drop down and use white then when you squint  

your eyes you can see the difference I'm going  to end up using the drop- down and this dark   blue right here now I want to add the same font  and fill to average but here's a great trick I'm   going to select this cell up in home clipboard  that's the format painter you can also get  

the format painter by rightclick and there's the  mini toolbar if I click you can see the cursor is   loaded up with the paintbrush that means wherever  I click it's going to just apply the formatting   when I click it applies to formatting it didn't  copy the content now we're going to have a bunch  

of formulas here and here and I would like to add  a particular color to indicate that formulas are   in these cells now what I want to do is I really  want to highlight just these cells and apply a   particular color now how did I do that well I  have to teach you the difference between the shift  

selection trick and the control selection trick if  I click in this cell right here and before I click   at the end if I hold shift it's like highlighting  everything in between the bookand however if I   click in this cell hold control and click here  it highlights only the cells we selected now  

these two tricks work in every application word  PowerPoint email I love to check my emails like   this click click click and then delete all right  so I'm going to highlight these cells right here   and now hold the control key and click now I can  come up and select a color and I've been using  

the same color for over 25 years to indicate that  this cell has a formula and it's that color right   there you probably don't have it so you have to  come to more colors and it's that green right   there almost directly in the center that's white  and then that green now the reason I chose this  

green many years ago if I click okay click off  to the side is because that was the same color as   accounting paper all right now we have a clear  visual indicator formulas and all of the raw data   will be here now before we enter all of our scores  I want to come up and select B5 I want a row with  

the total possible points for each assignment in  this row so I'm going to type Max points tab 100   contrl enter and now we're we're going to see  that the fill handle does lots of different   things if it's just a single number when I get  my Angry rabbit and click and drag well it just  

copies down here it was text and a number so it  knew to increment in just a moment I'll show you   some other cool tricks with dates and months and  things like that now another way to enter data   or the same piece of data into multiple cells is  if I highlight these four cells the light color  

cell is called the active cell if I type the max  score of 30 into quiz one area if I use control   enter that says Hey I want to put that thing  from the active cell into all the highlighted   cells now I love this trick because in our world  where there seems to be so much trouble I think  

the most important thing is love so I like to  highlight a bunch of cells when things are bad   type love control enter and it's like putting  love everywhere now I want to undo that instead   of up here I'm going to use the keyboard contrl  Z up here I'm going to say total or hopefully  

I'll spell it right and enter now I'm going to  use my shift key and click I've highlighted that   but I need that because I want to add borders so  now I'm going to hold control I add some borders   right here looks like the column isn't wide enough  so between B and C I'm going to click and drag  

oh hey wait a second watch this right click and  instead of clicking it one time that loads the   cursor paintbrush for just one click if I double  click it now everywhere I click until I click   Escape I can paste that formatting click click  and then to turn that off Escape here I want  

some green and now we have our grade table now  before we enter our form formulas and scores I   got to show you some great fill handle and angry  rabbit tricks all right b77 is selected and I   want to show you a great date and fill handle an  angry rabbit trick now many of us have to enter  

transactions into Excel and we need to always type  today's date well luckily there's a keyboard for   today's date control semicolon and sure enough  Wednesday October 23rd 2024 controll enter point   to the fill handle there's our angry rabbit and  I'm going to click and drag what there's fences  

that they put up no no those are train tracks no  that means that the column is not wide enough to   show the full content now I want to change all  of the column widths at the same time so I hover   over B click drag to K and now I pick anyone  and click and drag and that will fit all of the  

columns to the same width now here's another  great fill handle angry rabbit trick January   controll enter fill handle angry rabbit click  and drag and you got to be kidding me Excel   knows months I'm definitely not going to create my  calendar over in word I'm doing it in Excel here's  

another great date trick let's put one SL 1/2024  controll enter click and drag and when we let go   there's a smart tag hover click the drop down and  you got to be kidding me I can fill months when I   click that I get the first of each month now here  we did months what about days Wednesday controll  

enter click and drag and the fill handle the angry  rabbit they know days too and look at this when it   got to Sunday it knew to start again at Monday  now we don't have to abbreviate the days if I   spell Wednesday right it'll work control enter and  I want to click and drag just to Friday and then  

we'll start at Monday control enter click and  drag to Friday now we have weekdays let's put   in today's date control semicolon control enter  and what I really want are weekdays only when I   click and drag Monday is not the 26th it should be  the 28th but here's our wonderful smart tag hover  

click and there we have Phil weekdays and just  like that now Monday is the 28th Friday is the   25th and sure enough that is correct another thing  we can do do with the fill handle and angry rabbit   is if I type a two and a five highlight when I  click and drag Excel will recognize the pattern  

please always add three click drag and there  it is add three add three add three now there's   lots of other amazing things with that little fill  handle and the Crosshair or angry rabbit we'll get   to see lots of uses throughout this class now our  next task is to enter scores and we want to learn  

a highlighting and entering data trick so we have  the active cell at the top and I want to enter 88   enter 78 enter 99 enter 100 enter 92 enter and  for Mo 95 and now when I hit enter it jumps up   to the top of the column now for test two 87 78 92  991 enter and up to the top of the next column now  

I'm going to hit pause and enter this data when I  unpause you can hit pause and then enter the same   numbers and when I enter the last score and hit  enter it jumps back to the top of the First Column   now hit pause enter the numbers all right for our  first Formula we're going to add the Max points  

that'll become the denominator that we'll use  for each one of the students total to calculate   percentage grade and of course you remember from  the beginning of the video if you want the sum   function alt equals and unlike the beginning of  the video where we had text values this time it  

guesses right cuz if there are numbers right next  to it when you alt equals it will grab them enter   enter enter so it looks like 500 120 will be the  denominator when we calculate percentage grade now   alt equals and before I control enter and copy  this down we got to talk about what happens to  

cell references when you copy a formula right  now it looks like it's C8 to j8 but it's really   not when we copy it down because the formula moves  from Row 8 to row 9 this will become C9 to j9 this   is called a relative cell reference that means  from the point of view of the formula where is  

the formula always going to look it's always going  to look to the left eight cells so controll enter   grab that fill handle and the angry rabit and now  we can click and drag it can copy a formula to   click in the last cell and hit the F2 key we just  want to verify that sure enough it is is looking  

at the eight cells to the left Escape if I click  in the top cell F2 Row 8 enter F2 sure enough that   is moving row 9 enter 10 11 12 13 now with our  total we can calculate percentage grade now we're   going to type in equal sign remember all formulas  start with an equal sign and instead of clicking  

Escape I'm going to do it the fast way use my  arrow keys equals left Arrow divided by up up   up left so that's a relative cell reference right  now when I controll enter it'll work for this cell   but F2 what happens if we copy this well you can  sort of already guess right K5 well if we copy a  

formula that five is going to move to six now just  to see that if we actually did this it would not   work controll enter copy it down one cell that's  a divide by zero error because F2 that's 424   divided by empty cell which is zero and that's not  allowed now I want to contrl Z F2 the way that we  

convert a relative cell reference to a locked cell  reference called called absolute cell reference is   you want to make sure that your cursor is touching  somewhere next to or in the middle of the cell   reference if you do it over here it won't work  but I'm going to have my cursor at the end and  

when you hit the F4 key it puts the dollar signs  in to lock the column reference K and the row   reference 5 that means no matter where we copy  this formula K5 is locked on K5 controll enter   click and drag go to the last cell and hit F2 we  want to verify that sure enough that's a relative  

cell reference that's absolute or locked and it's  working perfect now enter I want to go to the top   cell and hit F2 and I want to look at how this  formula is constructed and how we created it first   off this is called a single input single output  formula because on either side of the operator  

there 's a single item and a single item when  you have a formula like that it can only deliver   a single answer and when we created this type of  formula we had to consider relative and absolute   cell references then we had to control enter and  manually copy it now we want to see not this F2  

old school formula this is the type of formula  we've been creating for 40 years but we want to   see the new Microsoft 365 Excel Dynamic spilled  array formulas now I'm going to click Escape now   I want to insert a column create the percentage  grade formula but this time we're going to use a  

dynamic spill array formula to insert a column  rightclick the column header M and point to   insert there is our new column now I want to copy  using our fill handle and our angry rabbit click   and drag now we can create our Dynamic spilled  array formula equal sign and instead of a single  

relative cell reference I'm going to highlight all  of the cells that would have been relative cell   references type our operator Division and then  the denominator 520 now there's the operator and   there's not just a single item on one of the sides  that makes this an array formula what will happen  

is each item will be used in the numerator and 520  in the denominator and check this out I don't have   to lock the cell reference and when I controll  enter I didn't have to manually copy that formula   down the column those are two reasons why creating  formulas with Dynamic spilled array formulas are  

faster than F2 the old school formulas now let's  learn some important things of about Dynamics   build array formulas click in the top cell I can  see the formula up in the formula bar but when   I hit enter there's a ghost formula enter another  ghost and watch this when I hit F2 there's nothing  

there well there's a spilled value there and a  ghost formula and if you want to go grab that   value right there in a formula equals I want that  value from The Spill Dynamic array no problem and   the reason that there are ghosts and nothing in  the cell is because guess what F2 the formula  

only lives in the top cell all those values below  are just spilled and you got to be careful don't   put anything in the way of the spill so if I type  word the formula is polite it gives you a spill   error which says hey you put something in the way  so I can't fully spill delete and now it can spill  

now there's one other advantage to Dynamics build  array formulas let's edit the old school formula   and then compare and contrast F2 what I'd like  to do is round this value now remember if we use   number formatting it only displays the number  a certain way if I want to change the number  

then I have to use the round function after the  equal sign but before that K8 rou notice in the   drop-down which offers up a bunch of functions  whatever function is in blue if you hit tab   it'll enter it into the formula not only that but  this is a description of what the function does if  

I arrow down there's another description so you  literally can teach yourself about the different   round function functions the one we want is round  I see it highlighted in blue so I hit tab now when   you use a function there's a screen tip and it  tries to help you complete the function now it  

needs a number to Round that's our formula notice  the number argument is bold I want to click at the   end very carefully with our ibeam cursor I see  that vertical flashing line so I type a comma   Watch What Happens right here here comma Now it's  polite the next argument that you have to enter  

is in bold now number of digits just means hey  what position do you want to round to for us I'm   going to count from the decimal to the right one  2 3 4 so I want to round to that position now if   you're rounding to the penny it would be one two  You' put a two right there to the dollar position  

like for taxes you put a zero we want want 1 2 3 4  so I type a four and this when I close parentheses   and control enter will officially change the  number now I can click and drag now notice we   edited the formula and then we had to recopy it  down F2 here's how we do it with Dynamic spilled  

array formulas rou tab we have our number click  at the end comma I see number of digits in bold   I want to round using four close parentheses and  when I controll enter I do not have to recopy the   formula down so that means Dynamic spilled array  formulas as opposed to the old school we don't  

have to lock we don't have to enter and manually  copy and we don't have to edit and manually copy   again with the dynamic spilled array formulas you  don't have to lock you don't have to man manually   copy the formula and editing is much faster and  easier now let's review the three different types  

of formulas we just created Dynamic spill array  formula on one of the sides of the operator there   are multiple items so this formula spills multiple  answers F2 single input single output formula   there's only single input so the operator on the  round function can only deliver a single answer  

but what about this one F2 notice we have multiple  items an array of items and the sum function can   only deliver a single value this is called an  aggregate calculation and all aggregate means   is take multiple items and deliver one answer some  is taking multiple answers and giving you one sum  

average taking multiple answers but always giving  you a single answer similarly a calculation like   Max which means please find the biggest value it  goes through multiple items and gives you a single   answer so aggregate calculation single input  single output and dynamic spill array formula now  

the next task is to calculate the decimal grade  based on percent grade but wait a second we don't   have a lookup table we're going to create that off  to the side actually we're going to use the same   two headers I'm going to highlight in controll C  to copy come over here and contrl V Let's point to  

the smart tag and I say hey keep column widths  now what we're going to do here is we're going   to create a column so that we can match these in  a percent grade column once we find a match then   in this column we'll have the decimal grades a  match means we'll jump over get it and the lookup  

function that we use will bring the grade back  automatically now at the top a percentage grade of   Z is zero tab the decimal grade of course is zero  now I'm entering with a tab when I hit enter it's   polite it goes back one column the lowest possible  percentage at Highline college to get the lowest  

possible decimal grade is. 61 and I want you to  notice something I didn't type the leading zero   when I hit tab it's polite it puts it in for me  the lowest grade is 7 I'm not putting in that   leading zero and enter 62 tab8 enter now just like  over here we use the fill handle with our numbers  

right here I've established a pattern always add  01 always add 0.1 and I can highlight both columns   point to the fill handle I see my Angry rabbit and  I'm reading the screen tip when I get to 4.0 I Let   Go Now I want to control asteris because I want  to add borders and there's our lookup table now  

here's how it works to look up a particular  decimal or later we can add a percentage   formatting how we look it up find a match jump  over to the course corresonding position in the   second column and bring that decimal back now  we're going to take 8154 and we race through and  

we try to find an exact match or the next smaller  well there's no exact match that's the next   smaller so we take 2.7 and we bring it back 4.95  we eras through there's no exact match we take the   next smaller so that grade is 4.0 and if someone  has okay 5135 race through well we can't find an  

exact match the next smaller is zero so we return  a zero now this column right here it's important   that it contains all the possible values to get a  match for the percentage grade and sure enough it   does how does it have all possible values well  a formula like this can only deliver a number  

between Z and 1 and 0 and .94 using exact match or  next smaller which is the type of lookup we'll do   will work perfectly now let's try our amazing  well wait a second V lookup is the function   we've been using for 35 years but we don't use it  anymore there were some problems with it backspace  

backspace we use x lookup not only does X lookup  solve those problems but it can do oh so much   more now we need a look up value remember we're  taking this and we're trying to find a match in   this column then return something from this column  I type a comma to get to the next argument look up  

array that's where we're going to get a match  now I'm going to click in the top cell and I'm   going to use a keyboard to jump down to the bottom  control shift down arrow it knew to stop because   it saw an empty cell now I want to just back to  the active cell control backspace now that's a  

killer keyboard because if you're a th rows down  and you just want to jump back to the active cell   control backspace comma return array well that's  the column and it has to be the same size as look   up array column that's the column that has the  item we want to go and get and bring back to the  

cell control shift down arrow control backspace  comma if the function can't find a match it yields   an error you can put something here to replace  that error but guess what we already have every   possible value so we're never not going to get  a match we can simply skip it by typing a comma  

match mode the first one is exact match that's  like looking up a product ID or a student ID you   want to match something exactly we don't want that  I'm going to down arrow we want exact match or   next smaller you can type minus one or hit tab the  final argument comma if you use exact match well  

we almost always search from first to last but  what if you have multiple dates for a particular   product and the price and you want to search last  to first so you can get the most upto-date price   we don't need this so I'm going to backspace leave  it out close parentheses controll enter and there  

is the grade is that right 90 point something sure  enough it can't find an exact match bam so it's   going to bring 3.6 back now why did it not spill  F2 well over here we had an operator Division and   there were a bunch of items on one side well  we can spill when we have a function argument  

like lookup value and instead of putting a single  value in which tells X lookup to deliver a single   value I'm just going to highlight all the student  percentages and now when I controll enter bam it   spills down the column now wait a second F2 what  is M8 pound or hashtag well our formula knows that  

when pointing to a dynamic spilled array formula  that formula only lives in the top cell M8 that   hashtag or poundside is a spilled range operator  that means please get everything that spills   from M8 as a comparison if I were to highlight  these lookup values well we get the range L8 to  

l13 that's because the formula is in each one of  the individual cells not just the top cell click   the look up highlight Dynamics spild and it knows  what's going on here M8 holds the formula and that   spilled range operator says hey get everything  that spills from that top cell now I'm not happy  

with his grades and I want to add some extra  credit so rather than F2 adding and hardcoding   the number into each formula controll enter copy  it down it works but there's a huge problem when   you come back back later and someone adds they're  like why is it 481 that doesn't add up not only  

that but when you come back later you might not  even remember so contrl zf2 anytime you have a   formula input that can change you have to put it  in a Cell label it and refer to it in the formula   with a cell reference so we're going to come up  here extra credit 10 enter now I'm lazy I don't  

want to format this so I'm going to come down here  rightclick format painter from the mini toolbar   click and I have my formatting now we come down  to the top cell F2 plus 10 now we have to copy   this formula so we want the same cell reference  in each cell so we hit the F4 key controll enter  

copy it down and there are the new grades you  know no that's too much I'm going to back it   off I'm going to type five and just like magic  everything updates now we have one last thing or   one last formula we want an average for each test  and quiz now average is an aggregate formula just  

like some I'm going to type equals ever I see it  there later we'll learn about the amazing average   ifs I'm going to hit tab these are going to be  relative s references so when I copy it to the   side just as we did with some it'll know to get  each column and notice I'm not going to type a  

close parentheses because if I control enter it's  polite it put it in I can even see it up here   click and drag all the way to the end now we have  some messy decimals here and instead of coming up   and clicking this button a bunch of times I want  to show you the format cells dialogue box now I'm  

going to highlight all the way to total and this  amazing dialogue box has a keyboard control one   now we'll learn about these tabs later but the  number tab has thousands of number formatting   options much more than this group up here all I  want now is number zero decimal places click okay  

probably these two since it's only going to be  two clicks to get it down to four I'll highlight   one two now we have have our finished grade book  now I would like to print contrl P gives us print   preview uhoh we have a bunch of stuff we don't  want to print and it looks like down here we have  

half the table on another sheet and we don't want  that so I'm going to click escape and if we go up   to page layout right there this opens the page  setup dialogue box so we're going to click now   we definitely don't want portrait because this  is definitely wider than it is tall so we're  

going to go landscape margins I'm going to give  it a little extra room on the left and right so   I'll change it to3 tab that moves to the next  text box three and I would like since if it's   off to the side a little bit it looks off-kilter  I'm going to check horizontal header and footer  

this is the preview for the header this is the  preview for the footer we have some built-in ones   but the real power is either one of these buttons  we're going to have a footer so I'm going to click   custom footer and the real power comes from these  buttons when we click these buttons it actually  

puts code in for us so that it's Dynamic so this  and you can hover now it says insert page number   that's like for one of five two of five this would  be total pages that would be the five there's the   current time so when you print it out it'll give  that time current date this is the complete file  

path the workbook name sheet name even inserting  a picture you can also type whatever you want now   all we want is the sheet tab name so I'm going to  click this button and it puts the code in in the   middle I want page one of five page two of five  so we click this that code means whatever the  

current page is it'll print that number out then  I'm going to type a space of and a space and click   total pages so what we did here is we clicked to  get code we typed a little bit and we click to get   code over here let's put date oh no that's time  I'm going to delete date space- space and time  

now when we click okay we get a preview down here  now the last tab is going to be helpful print area   we want to click and then select from B1 all the  way to n14 everything else will be excluded let's   click okay I can already tell from this dotted  line when I contrl P it looks like it's hanging  

out on the next page when I click next page it  sure is is now we probably since all of these   columns could be made a little bit thinner I'm  going to highlight all of them and resize all   of them at once click maybe right about there we  don't see that dash line so we know contrl P that  

is beautiful page setup now the last thing we want  to do is I want to be able to copy this now that   I have this beautiful template I want to rename  this to rename a sheet tab you doubleclick I'm   going to click at the end and I'm in the winter  quarter so I'm going to type a w and then open  

parentheses for Highline that's the first quarter  of the Year close parentheses and enter now I've   registered a new name by the way if you want  a new sheet you click that and if you want to   color the sheet rightclick the sheet and there's  tab color now the reason that I put a parentheses  

is because when I copy this it'll automatically  increment to two this is cool if you have a sheet   for each week week 1 2 3 or day one two 3 now to  copy this rightclick move or copy but I'm going   to show you the keyboard if you click and drag up  don't let go you see that little black downward  

pointing triangle that means I'm moving it but now  if you hold control notice look at that piece of   paper plus I'm holding control no I'm not holding  control so there's no plus when I hold control and   see the plus it means it's copying now I'm going  to let go of the mouse not control and when I let  

go of the mouse that is amazing now actually I  do have to change this to spring so I'm going   to type S and here's our template and here's  what we're going to do we're not only going to   delete all the scores and you could type other  names but I'm going to delete all of these also  

temporarily it gives us divide by zero and I want  to delete the extra credit I want to enter the   first test score so 100 enter and then 88 87 92  98 99 52 and enter and by entering just the max   points in this particular assignment as you go  along this will give you the current information  

including the current grade based on whatever  assignments have been completed now we can fix   this right here F2 after the equal sign before the  a type if there's a bunch of great if functions   I'll show you all of them later but the one we  want is if air down arrow tab there's the value  

come to the end we're gonna type a comma to read  the next argument comma value if error well you   get to put what you want I'm putting a zero close  parentheses controll enter and copy it all the way   to the side and sure enough what we get a bunch  of ones but I copied the formula here it's looking  

good but guess what when you copy a formula  it copies the formula and the formatting so   our smart tag comes to the rescue again hover  click and you got to be kidding fill without   formatting this means it will copy just the  formulas and just like that we copied the  

formula and retained our formatting now I have one  last cool thing to show you I want to come back   to grade book winter one and I want to copy this  sheet so I'm going to hold control click drag up   I see the plus I'm going to drop it right after  grade book spring 2 letting go of the mouse but  

not control now I can let go of control and I'm  going to rename this double click and I'm going   to call it BR for by row and BC for by column  we have Dynamic spilled array formulas and that   replaces the single input single output we're  allowed to use functions and spill but what if  

about aggregate formulas just as an example if I  delete all this and right here alt equals I'm just   going to see if I can spill but that's never  going to spill because guess what that's an   agregate formula no matter how many numbers you  put in it's always only going to deliver a single  

answer contrl ZZ now what I'm about to show you  is current in beta but I'm filming this on October   30th 2024 and within a few months what I'm about  to show you how to spill aggregate calculations   should be out of beta and into all of Microsoft  365 all right I want to delete all these there's  

a formula in each cell so I highlight and use the  delete key same here delete and now let's see what   Microsoft did to to spill aggregate calculations  equals by Row the array argument we just enter an   array where we want to make a calculation for  each row comma to get to the function argument  

and there's a drop-down for this one we want some  so I'm going to tab close parentheses and that's   it controll enter and aggregate total spill down  the column F2 and if I want my extra credit here   I select five and that will be added to each one  of these controll enter down here well we have the  

same array but we want to do something for each  column equals by column there's the array same   array comma the function down arrow tab now we're  going to calculating an average for each column   now I wanted to extend it all the way to the end  so F2 and I want to point to that's not a fill  

handle but it's a little square in the corner and  when I see my diagonal pointing Arrow there it is   I click and drag to change the number of columns  controll enter and now our average spills across   all the columns all right by call and buy row  amazing functions they may not be in Microsoft  

365 right now but soon wow that was an epic  video we learned about upand coming BYO and   buy call to spill aggregate totals we learned how  to copy sheets contrl P we learned all about page   setup we learned about three different types of  formula aggregate single input single output and  

Dynamics spild array formulas we learned about  the X lookup function how to build a lookup   table for that xlookup function we learned about  style formatting a lot about number formatting and   we learned about the amazing Crosshair or angry  rabbit to do all sorts of amazing things all right  

that was a lot of fun with XL Basics number one  we'll see you next video for XL Basics number two [Music]