How to Add Formulas and Tables in Keynote

Intro going over table charts and formulas in keynote  and we're going to go over how to use formulas   in keynote when you have tables and so on and  we're going to go to through a few examples and   if you want to know how to format your table so it  looks nice just click the video in the corner and   um that that video will show you how to make it  look nice like such if you're pasting it from   google sheets or excel so now we're just going to  go over the formulas and um i want to show you a   list and i'll put the link in the notes basically  this is a support guide information on keynote   on apple website and it has a lot of functions  that it supports about 250 and it has the basic   ones that you have in google sheets and um  excel and so on and if you click each of them   you can see like the plus icon you can see more  detail what kind of formulas are listed and so   most of the ones you will use in keynotes are  the basic ones some of the more complicated   calculations you'll probably do in  google sheets or excel and anyway   keynote is just to show like a summary of your  data so maybe some summary analysis and so so   you were probably going to use like the basic  function which are like average sum round minimum   maximum and so on and just simple calculation  adding subtracting calculating percentages   whenever you paste information from like google  sheets or excel like it pastes as value so i   actually like to use formulas in keynote when  i have any kind of tables because i just want   to double check that while i was formatting or  changing things that i didn't make a mistake and   then you're surprised that actually presenting  something and the data is incorrect there's a   missing digit or what have you so i'm going to  start with this example let's calculate average   total and sum of totals so let me actually  remove these i'm going to select these cells and   AVERAGE, SUM start from the beginning so when i pasted the  data here it's a value data and let's say i want   to calculate an average of this quarterly data  for car sales in 2021 for like six different car   makers so you can just click on a cell start  typing equal and that's where the formula bar   will show up and the formula bar is very small  and sometimes hard to tell what's going on so   once if you hover over it the bottom line then  you have this like kind of arrow icon or this hand   icon sometimes it doesn't want to show up right  away but i'll show you in a second or sometimes oh   right there it's like a hand icon you can drag it  and expand this formula bar you can also move this   away from your place because i don't like that i  don't see what's going on underneath so i like to   actually position a little bit lower and now you  can start typing the formula so i will say average   and now you can see google keynote is showing you  all the formulas that are available for you you   can select one of them and i'm going to select  this one now it's asking you for your value   and you can actually drag ranges just  as you drag in google sheets or excel   i can just select these four columns and then  click enter now another way to see the sum if you   click this check mark you can just click the check  mark and it will show you the average the result   of your formula and once you have this formula you  don't have to redo it you can also copy it up you   can do select the cell command c and select these  cells and command v you can do it in such a way   Copy & Paste Formulas or you can also take this and you will have this  like little yellow dot or circle in the middle   of your cell you can actually drag this to copy  over the formula this way and you can double check   how if the formula is showing the correct  information if you just double click on it   it will show you what it's calculating now there's  two also other ways to see the formula like let   me actually um try to move this away from here  you can click on this arrow next to the function   icon and you can say show formula as text so you  see the way it's presented right now it's like   this in these boxes uh formula and then the range  i can if i do show formulas text it's actually   presenting it more like it does usually in excel  or google sheets and then you can also do convert   formula to text in a cell so that would do is  actually show you the calculation like this but   it's not the calculation itself so um that's  the difference so i'm going to change this   to have an actual formula here so once i calculate  the average i see the numbers here and i worry   about you know changing the format because when i  copy it over i must have let me just see my table   table i didn't use the alternating row because if  i do that i actually can make this color copy over   across the row so i'll just do this so so then  even if i copy the formula let me show you um if i copy this formula now it will not change  modify from the white cell it won't modify my   column and my rows that are like the light blue  because it's alternating row color that's the   way to do it um to you know avoid if you  have tables with formulas i suggest to use   the alternating row color because if you're  copying it over you will come across this   issue a lot now let's do the sum so sum is equal  and then i will just use sum and you also have   this option sum if you can do some products some  other formulas here but i'm just going to do some   and i'm okay let me do shove in function browser  oh you can also see right now here it's like the   form from function help menu so you can have  some help on your formulas right here as well   and you can also do insert function here as  well but okay let me do one more time equal sum   parenthesis and i'm going to change  it to as text i just like it better   and i'm going to add these  four columns close parentheses   enter and then again i'm going to copy  it over like so now i also want to enter   a formula that adds up total for all the six car  makers so i'll just do at the bottom equal sum and then just select the range and so so this is a  quick way to do some of these and i can also maybe   create a round like i can add a round function  so as if in excel like that's why i kind of like   ROUND & Nested Formula using the show formulas text because i can just  it's easier for me to type other formulas here   so i'm going to do round select  this comma and then how many digits   maybe let me do two two four digits i can  also add nested function within the formula   in keynote let's do another example so this one  is an interesting one let's say you have some kind   of you know financial presentation of stocks and  pricing and maybe you know you're working on this   presentation over time like you don't want to go  back and have to pull this data for these stocks   STOCK Price Formula uh maybe the presentation you started two  months ago so you want to keep updating   it so this is actually a good option which is  the formula that you can use in keynote stock   then you you put the symbol and then it's  optional value and now let me show you   let's go to the formulas like here it would tell  me more information on the syntax and what kind of   information i can ask for in the formula and you  can see the formula right now it's stack which   is you have to provide a symbol and then um price  this price actually has multiple options you can   say 0 1 2 3 4 and so on there's a list of how many  25 different things you can pull for this stock   and each of these numbers represent like either  price name change and etc okay now you see this   you must specify a value so this will be an error  like whenever you see something like this it's an   error and that means the formula is something is  wrong with the formula so i'm going to change this   okay so now let's start typing our formula  here so let's say i'm going to take this   so if you want to delete the formula just select  and delete basically you can do this no i don't   want to do this but i can just do right click  on the cell and clear all so i can delete the   formula sometimes it didn't work doesn't work  if i just try to oh it did work this one but   sometimes it doesn't want to like delete for some  reason just goes to the formula even though i'm   the x just makes basically saying cancel changes  and close so it's not really deleting the formula   if let's say if i do this and click check mark  then i'm deleting the formula okay equals stock   parentheses and now i'm going to reference this  and now i'm going to also select which option so   name i have to i have to check what's name name  what number do i have to put for name for name   it's one i have to put one close parenthesis and  enter and now i have the name now if i want to   copy this over like we were talking about and  then do you see here i put the the names of the   options that i have to select just to reference  quickly so i don't have to look for it in the   um guide so let's say if i have this and  now i once i have this formula and i want   to copy it over like so okay i have all kinds  of errors why because actually here if i double   Relative & Absolute References check this formula i can see what is happening  it's actually referencing b three so it's not   referencing the symbol but it's referencing  the name so i actually have to select b2 in my formula and make it an absolute reference  so i'm going to go back to my original formula   and here i'm going to say preserve row and column  i just want to preserve the row because column   actually i'm going to be later on copying this  formula across so i want to preserve rows so   these are the options for absolute reference and  now if i click enter and now if i copy it over   it is working but now i want to change what  i'm referencing so i want to not do the name   but you see you can actually click on here  and it shows you what options do you have   for this formula price and then i'm going to do  the same thing here i want a volume which was   see let me just actually move this value  volume was eight then high 52 week high it's 11 and 52 low that's 12. annual dividend that's number 20. so i  already kind of went through it so i know okay so now in here it's not available i'll show   you what to do in case you have  an error and earning per share 21 IFERROR Formula and finally p e ratio 20 3. so whenever you have this  and you might have an error   you can also add an if error nested formula  beforehand so i'm going to say if error   what to do maybe put not available and that's  what happens and now i can actually copy this   and you have this like yellow dot in the middle  you can drag this across and it will copy for all   the other uh tickers and symbols that you have for  each of the companies so you know it's a quick way   of referencing and and using the stock formula as  well as if an error formula if you have errors in   your calculations here's another example that we  have we have average home prices and i want to   do an average and we already did the average  calculation before let me see if okay you   see this is not a formula because i paste  it over so i can just use average formula   for each of the regions and then i can copy the  formula you see it's very sensitive like when   you click anything it's very sensitive i can just  click the x not to change anything in my formula   and copy the formula over now um percentage change  so that's another one that you might um be using   Calculate Percentages a lot so let me delete this and show you how to  calculate that so let's actually make this box   a little bit bigger if i can select this okay  so what we want to do uh percentage change is   basically taking the new value subtracting the  old value and divided by old value so i'm going to   use parentheses because i want to make sure  that my arithmetic is correct saying the   new value for 2022 subtract the old  value close parentheses and divide   okay it's the division yes when i click the slash  it does the uh division sign like mathematical   division sign and then divided by 2021 the old  value click press and then you have the collect   formula and then we just copy it across whenever  you click on a cell you can see it shows you what   is it using which cells it's referencing another  one quick one here this one is just basically   using a sum of these total car cells by region  across so that's that's something we've ever done   finally this is a final example in our video  that we're going to go over it's kind of like   Calculating Tax Example using a table and i have a table here and  um it is using a table but i removed all   the formatting so you can't tell but it's a it's  like a clean presentation of the calculations if   you ever want to lock it like you know because  whenever you adding some other formatting you   want to make sure you lock it so you don't  make some inadvertent changes to your table   but right now because i'm going to put  formulas i want to show you how to do that so   i have basically tax calculation for 2022 and  i have a single taxpayer with taxable income of   hundred thousand and the brackets they're they're  i'm just showing some of the brackets that are   applying because there are also more brackets for  each taxpayer but right now i'm just showing at 10   12 22 24 and it's saying you know the  income between 0 and 10 000 it's taxed at 10   between ten thousand and forty one and twelve  percent and so on so it's like a progressive   um tax system that basically the tax rate  increases as your income increases and   just wanna show you that you're not finding your  highest tax bracket so if this person is in a 24   bracket you're not going to take um 100 000 times  the 24 as uh you know and the tax rate will be 24   24 000 you're actually going to have to calculate  at each tax rate how much you pay in taxes so   we're going to do that so amount of income  at each so what i want to do is basically   say how much of income at this tax bracket would  be in total so i'm taking the maximum value   subtracting the minimum so you're using  subtraction now later on i can take this   yellow dot and copy over the formula now for the  last item i have to change the formula because now   it's taking the maximum 170 000 subtracting  89 but i actually have 100 000 not 170 so i   can just actually click on the here and remove  this item select the cell that i want and click   and check to enter it and now i have this  is the income level at each of the brackets   there will be tax at each of the brackets so the  corresponding bracket so now i want to multiply   by tax rate so i can say equal select now i  can just move this may be somewhere else a   little bit because i can't tell what's happening  i like to be able to tell what's happening take   this amount at each each of the bracket rate  multiply by tax rate and press enter or check   and now i'm going to drag this formula down and  now this is total for each of the tax brackets   that i will be paying so i'll be paying two  thousand six hundred and twenty two at twenty   four percent and then uh twenty two at twenty two  percent i'm paying ten thousand and each and a   ten percent i'm paying 1000 and so on and each of  them i want to add them up so i'm going to do some   and i'm going to select the cells and enter so  right now it shows me okay so i'm actually going   to be paying taxes 17 800 versus if you just did  a plane calculation if this was not a progressive   increasing tax rates tax system so this is like a  quick way to basically show an example and i can   also select the table and some of these formatting  that i've done and lock it so even if i change   anything or move anything i'm not affecting  my calculations so you want to make sure you   when you do the calculation lock your table so  nothing can be changed by an accident as you   can see it's very sensitive so sometimes that  can happen so thanks so much for watching you