cpa formula This is a topic that many people are looking for. star-trek-voyager.net is a channel providing useful information about learning, life, digital marketing and online courses …. it will help you have an overview and solid multi-faceted knowledge . Today, star-trek-voyager.net would like to introduce to you EXCEL FOR BEGINNERS, Auditors & Accountants . Following along are instructions in the video below:
“Y all. It s stuffy and welcome back to my channel. So today. I am am going to be sharing with y all.
Some excel formulas that you need to as an accountant. So if you like this kind of content and want to see a series maybe i ll call the series like on the job with stephie or something. Like that if y all. Like this type of content.
Then please be sure to give it a thumbs up and let me know in the comments. So that i know to do more videos like this alrighty. So without further adieu. Let s just go ahead and jump right into the video alright y all so i apologize in advance if you hear juicy barking it is raining outside and she really does not like the rain so let s go ahead and just jump right into it let s just pretend that this is a report of all of the transactions in a specific bank account first of all whenever i get a new reports that are in two separate columns like this you see here s a debit here s a credit.
I like to put everything into one column so it s much easier for my formulas. As you can see we have debits so these are all positive numbers credits are subtractions we re gonna insert a column here and i m just gonna make this here amount so what you can easily do is you press. The equals sign. You always have to start with some type of equal or a plus sign.
Or a minus sign otherwise excel will just think that you re typing in regular characters. So equals this. If you don t put a minus sign in front of it. It s just automatically going to think.
It s a positive number and then i m gonna say minus this okay and so as you can see it gives back a positive number well then i m gonna double click here as you can see it s like a plus sign if you double click this it is going to make the same formula all the way down. I m pressing the control key and the down arrow button. If you do that it takes you to the very bottom keep in mind that to contain something in the cell. Otherwise.
If you go all the way. If you press ctrl down. It takes you to the very bottom. As you can see there s like nothing here anyways.
That s just a kind of a quick way to get around in excel. Same thing goes like if you press ctrl to the left button takes you all the way to the left. If you go control right button. Takes you all the way to the right so.
That s an easy way to navigate in excel. So anyways just another tidbit there so we re at the very bottom here. I m going to just press. Ctrl arrow up again.
So i can get to the very top. And as you can see this credit is now showing up in this column. But it has a minus sign in front of it because of the formula that we used here. So another thing that i do in excel.
A lot is pasting values. So for example. If you start messing around with this data. It might screw up because this formula is grabbing this specific cell.
So i m gonna going to copy all you could either do it one of two ways so you could either just click the top of this column d. And do it or you could go from this first formula control shift down it ll select everything in that column as you see here i m gonna press ctrl c. On my keyboard so this is an easy shortcut instead of having to go around up in this area. I m just gonna go alt e f.
V. What it s gonna do is just paste. The numbers. And it s going to take away the formulas you could do all ts t.
It will do the format formulas comments. But anyways that is a a very efficient way to get around in excel is instead of having to click around you re using stuff on your keyboard. So you can either press ok or just press. The enter button.
And as you can see instead of having the formula. There it now has the number also another trick to get around in here is pressing ctrl z. Let s say you didn t mean to make it all values press. Ctrl z.
It undos undoes undo this if this is the only data you need in excel. And you no longer need these two columns. It s good to paste the values because look here if you end up deleting this because these are formulas here and they re pulling from these two columns. If you end up deleting this you re gonna get this error.
Just because you no longer have anything in those two columns so press ctrl z to undo that copying alt e s. And then b okay so now when you delete these it goes away and no problem here so another thing. That s really a nice handy thing is something called freeze panes and let s say you want to be able to see your headers at all times. Even if you re scrolling down.
So see i m scrolling down right now i lose. My headers right control arrow up to get all the way to the top again. So. Let s say you just want to be able to see the top row that header so.
What you re gonna do is go to freeze. Panes and then press freeze top row. So as you scroll down you now see the header at all times. I m gonna undo that so i m gonna press unfreeze panes let s say that you had a lot more information over here to the right let s say you go like this oh.
Then you just lose the dates. And you want to be able to see the header you want to see the date and you want to see the description at all times. When you want to freeze. It this way you have to select the cell and then everything to the left of it and above.
It is going to be frozen. So i ll show you what that means go to freeze. Panes press freeze panes. So as you can see the top row is frozen.
So you always see the header then if you scroll to the right you ll always see the description and the date. So that is how you use freeze. Panes and also i will suggest always keeping a original copy before you even start messing with it somewhere on your computer. And then work on a copy of it in a separate file that way you always have the original to go back to in case.
You screw something up for whatever reason you want to always double check that you re doing things accurately. So what i m going to do go all the way to the to the bottom. So. Control arrow down.
You can either use a formula up here and press auto. Sum. Which makes it super. Easy okay.
So that s the amount or you can do a formula. Which is what i usually do just because it s easier for me. So i always do equals sum and as you go around in your job and you use excel more often you ll remember these formulas. But if you re new and you re unsure of how to use specific formulas you can always press this insert function button.
And you can see most recently used or you go to all and then you can just look in that way. And it always gives you some kind of description as well equals. Some and then you always put parentheses. Because whatever s in the parentheses.
Is the actual like equation that you re using so i have the very bottom. I m gonna press control shift up button it s going to take everything up there now notice that is stopping at row 141 and that is because there s an empty cell here so whenever you use this control shift up or control up it s going to stop at the next blank cell. So anyways. I m gonna go ahead and just continue on so i m gonna press control shift up again up again until it goes all the way to the top.
Okay just like that and i want to make sure i m gonna double check to make sure i m getting everything in there so my first number is going to e2. I want to make sure i have that so it is i m gonna press enter so it gives you that amount i m gonna copy this formula over to the right side to the credit as you can see so this is the debit. This is the credit amount and because in these two rows. It all shows is a positive i m going to do another equation so equals debit amount minus this credit amount doesn t match do these two much yes do all right so now.
We re gonna do a pivot table and a pivot table basically summarizes. A list of transactions into a summarized table like you see here on the screen so i m gonna show you all how i make this pivot table. I advise that you always have some type of description in your header here instead of having this blank for example label this like location for example you re going to select all of the information you want to include in a pivot table. So i m going to press shift to the right key.
So that takes all of my headers. And i m going to push continue pressing shift. Ctrl and then the down button and that is going to include everything. Here.
You don t necessarily need to put that total in there that was just my check. So now that you have your entire information source selected go to insert go to pivot table. And i m going to say. See i ve already selected the range and then i m gonna say new worksheet.
If you want to do existing worksheet that means. It s going to put the pivot table in this specific tab that you re in. But i put it on a new worksheet or a new tab my preference personally so i press ok and so as you can see here it created its own tab. So here is the original detail or listing of transactions and here is the pivot table.
I like to label that tab pivot so i know pivot pivot. Little formula table is no longer on the right side that s because i m not selecting here well as soon as you touch this pivot table. It ll pop up with these different fields again let s say i want to analyze how much money. I spent on a specific date.
So i m going to take this date. I m gonna put it down here two rows so i m gonna drag amount over to this some values spot sometimes you ll find this when you drag the header over into this little bucket. Sometimes it ll say count for whatever reason. It will just say count and what that s doing is counting.
How many transactions you have that can also be a helpful feature as well. But let s say for the purpose of this example. I want to know what the sun is what you re gonna do is go to this down button here go to value field settings and then you can change what that field setting is so you could do the average you could do the sum you could do the count whichever. I typically will use sum or count.
I don t really use anything else. But you know you could use it so i m gonna press some press ok. I m going to format this so i can see it a little bit easier that amount three six two zero nine five matches. Three six two zero nine five.
So the next formula that i wanted to share with y all is some if let s say that i wanted to see how much money i spent in boston what you re going to do is you re gonna press your equals sign and you re gonna press your in type in sum if you could walk through it using this function here but i m gonna show you guys how to type it out yourself i m gonna press the parentheses. And as you can see the first thing they re gonna ask for is the range. So what am i looking for exactly so i m going to say i m gonna look here in location to the range is here through here the criteria here s the quote marks. I m gonna say dallas.
So it s looking in those columns for dallas comma to move on to the next piece of it and then this is where you re going to actually be summing. So what am i going to be adding up this column here so starting here control shift down so i would do this and then closing. So that is the end of my formula it says dallas and we re gonna double check this using our pivot table here in a second another way you could do this instead of having to type out dallas. Let s say i wanted to look at all of these locations.
We have dallas. We have canada we have payment. We have boston and it doesn t matter if you have uppercase or lowercase. What matters is the actual text that s inside of there so instead of typing in dallas yourself you could do this we re going to delete this out of there.
And when you were going to the second one you can just press that column and as you can see is the same amount let s say that you wanted to do the same thing for all locations like i said here to make it easier instead of having to type this and i copy this formula over down to these you re gonna see that we started here at c2. It s shifting downwards to c3 everything down here instead of pulling from this first cell and same thing goes here c3 c4 c5. And that s not what we want necessarily so to fix that if i want to fix the specific range that i selected originally and i don t want it to move down. I want it to look specifically here even if i move the formula into a different cell.
I m just gonna start over just so you guys can see from start to finish. I m gonna select the location all the way down and then i m going to press the f4 button and then you see that these dollar signs come up that means. It is fixing it to this column this row. This column this row.
If you press f4 again it s only fixing it to the specific row. It doesn t matter what column. It is if you press f4 again it s going to keep it on the specific column. But it ll move based on what row.
You re on then if you press f4 again it goes away so i m gonna press this so that it fixes it to the column and the rows that i m already looking at i m gonna select dallas which you can t see there but it s selecting that dallas word and then again you can see it is fixing it to the specific range. And i m not having to fix anything again so to double check this we re gonna use our vlookup. So let s go back to our pivot table. So i m going to go to this pivot.
I m gonna change it i want to do location for some reason pivot tables don t really work all that if it s on the actual pivot table itself so i m gonna copy it and paste values right below so as you can see it s not a pivot table that table here is not showing up again. So that s why i pasted it over here. So i m going to say equals vlookup. You can use this function over here.
Too and eventually you can just type. It all yourself. Which is what i typically do equals vlookup. I m looking up dallas comma.
So i can move to the next one table array means in what data set am. I looking. I m looking at this data set right here. I m going to press f4.
So that whenever i copy that formula it doesn t move comma to move to the next one now. It s asking if i find dallas. What information do i want to see i want to see the sun so i m gonna say column two because it s column b. Then i type in false because i want an exact match.
I don t want an approximate match. I wanted to say is there a dallas in there if so what is the amount if not i want to see an error. So as you can see is pulling that now i m going to double click again now is pulling canada from here. And there it is same thing with payment again right there same thing with boston one six oh five nine five boston.
One thing to know about vlookups vlookups are going to be looking in this range in the leftmost column. So as you can see here s dallas. You want to make sure dallas or whatever you re looking for is going to be in the leftmost column. Okay so that is how you do a vlookup all right all.
So those are some of the important excel formulas that you need to know as an accountant and trust me. It gets a lot easier as you do it more and more often just keep practicing it and eventually you won t even have to have a little cheat sheet or anything. Like that so i hope this video was helpful. If it was please be sure to give it a thumbs up.
I would greatly appreciate it don t forget to be a part of our sub e fan. Because we would love to have you. And i will talk to y all. In my next video hey y all.
It s stuffy and welcome back to to my channel. Oh. My gosh you can see my shorts look. And yes.
So that s pretty much i don t know what else is saying this in trip. There s not a lot more to say. This is the realness filming a rare sophie shorts y all. I m happy shortened.
I was in college. Yes. I can still fit in them they re a bit cider and they used to be when i was in college. Okay bye.
Thank you for watching all the articles on the topic EXCEL FOR BEGINNERS, Auditors & Accountants . All shares of star-trek-voyager.net are very good. We hope you are satisfied with the article. For any questions, please leave a comment below. Hopefully you guys support our website even more.
“Open for all the info!! n.n.n.n.n.nHi y all and Happy Sunday! Today I m sharing some basic Microsoft Excel formulas, functions and shortcuts for accountants and anyone else who wants to learn to efficiently use Excel! Below are time stamps of topics:nn01:00 Basics for using Exceln02:56 Paste special values n04:08 Keyboard shortcut to undo (ctrl + z)n05:00 Freeze panesn06:35 Basic formulas in Excel like Sumn07:38 Keyboard shortcut to move around efficientlyn08:50 Pivot tablen11:50 SumIf formulan14:00 What a $ dollar sign in Excel meansn15:17 vlookupnnThings in this video:nEyelash extensions Lashed by Kasia https://www.instagram.com/lashed.by.kasia/nnCamera: https://amzn.to/2NF68uqnn—————————-MUSIC————————————–nSong: Ukulele by www.bensound.comnn GET IN TOUCH nnEmail: email@example.comBlog: www.stephienese.comnInstagram: http://instagram.com/stephiemillsnJuicy s IG: https://www.instagram.com/juicythepugweeniennLet s be pen pals!nPO Box 180122nArlington, TX 76096nn—————————-DISCLAIMER——————————nAll opinions are my own and are from my personal experiences/ observations. They are not on behalf of any company. I m just sharing to provide a different perspective. Base your opinions on what is true for yourself.nn#OnTheJobWithStephie #FriendlySisterlyAdvice”,
vlog, cpa, stephielaclac, stephienese, public accounting, what it s like to be a cpa, audit, external audit, KPMG, EY, Deloitte, PWC, BDO, Grant Thornton, ac…