Practical Excel for Accounting: Pivot Tables, Drop-down Lists, and VLOOKUP

this session will be split into three parts excel as a standalone tool excel to prepare it to import into quickbooks or accounting system and excel to clean up data that's been exported from an accounting system so those are going to be the three parts so let's dive right in and let's talk about excel as a standalone tool so the first thing we're going to talk about is tables and list validation so let me open up excel here and i'm going to open up a brand new page from xero and again we're gonna make the assumption that you decided that you're gonna organize some accounting data in excel there's no accounting system we're gonna and everything's gonna be entered from scratch right not gonna be imported from bank or anything else so typically what you do is you set up an excel spreadsheet and i'm going to zoom this in a little bit may hopefully make it a little bit easier to read via the webinar and then i'm going to start with my headers so let's say my first one is going to be date my second one's going to be let's call it payee my third one is going to be amount so i'm just typing it in there right i'm typing amount and then the last one is going to be memo so that's simple i'm going to start by just building my my my headers and the next thing i want to do is i want to select the data from the headers and then one more row right under it and then i'm going to hit ctrl t on the keyboard and i'm going to create a table you want excel to be working with an active table because what the table does it it brings down the properties of the cells on top of it so you don't have to be formatting and reformatting all the data as you enter more lines of data we're going to click on this option that says my table has headers we're going to hit that checkbox and then hit ok so this is going to give you a table and it will look exactly like this and you can change uh by clicking on table design whoops let me close that one by clicking on on table design here in the top you can change the formatting of that table so if you want it to be orange green whatever is easier more comfortable for you you can use it right so that would be up to you which format you would like to use so then we're going to start typing right now the first thing i want to do is i want to make sure that this column has the date format so i'm going to change that into short date then i want it on the amount column i want to change that to uh let's say accounting right so then essentially what i do is i just change the formatting so now i can start typing data up so let's say on january 1st 2020 then i hit tab we went to office depot okay hit tab and then i put the amount let's say it's 500 tab and then type printer and then what happens is if i press enter on the very last line of of printer i'm going to hit undo for a second so i'm in the very last uh column or field and i press enter um let me just enter uh well it didn't do it this time because i undid it so let me do a printer and then press enter it should create a new line for me if it doesn't do it just type on the next line notice this i don't have to deal with formatting let's see i do i'm going to do 0 1 0 2 20 20. let's say we went to chipotle and then it was 27 dollars and 80 cents and i put here uh with robert whatev whatever it is with uh robert press enter and then it will go into the next line so what's cool about a table is excel knows that the data ends there and enter will send me by default to the first field of that table so that allows me just to just quickly enter data as i go uh the other things that you can do is let's say for example i'm going to enter a bunch of restaurants first so let's say we're going to do uh chili's okay this is 50 and then we're gonna do on the fourth let's do panera and it's 12.89 right so i can just uh enter all my data and then i can classify a bunch of things in one shot now one thing i missed out of this table is a field for the account category so i'm just going to insert it in here i'm going to insert it right there and excel knows that that table will continue to get expanded notice that automatically it adds a header for column one just because every single uh data field in a table needs to have a header so we're going to put here account or category whatever you want to call it and then here this is where we type office supplies right let me spell that so let's spell that correctly so office supplies and then down here we put meals meals meals right of course some of the things that you can do if they are all the same category let's say that all of these are supposed to be shareholder distributions i can select them all in one shot here and then come into the edit field so again i selected a bunch of common ones and then i went into the edit field and i'm going to put this let's say this is meals and entertainment okay i know that's no longer a thing enter payment okay and i can hit control enter on the keyboard so combination control enter control enter will pre-fill all those cells in one shot okay so again i'm trying to show you sort of the practical things around excel uh that could you know make your life easier what's really cool about the table as well is that it's an automatic filtering mechanism so let's say for example i'm going to come in here and put let's say february 1st 2020 and we went to chipotle again and it was 78 dollars and 90 cents and i'm gonna type meals and entertainment okay and basically uh what's really cool about this is i can click on any of the drop down filters here for the table and i click on the drop down filter and i can maybe say you know what i only want to see february so notice that you get you get an instant filtering mechanism with uh with your table so again just to make sure that we are kind of 100 aware how this works just in case you missed the beginning let's say you were starting from scratch here and you created your um your headers then you're going to select all your headers and at least one row under it so all the headers one row and hit control t control t right and then hit checkbox and then hit ok so what some people do is they will do one tab let's say for each bank account so let's we call it chase one one five two and then we have uh boa one seven three five something like that and then basically they'll do the data entry in multiple uh tabs i personally don't recommend that i prefer to have one big table for everything so if you ever wanted to separate let's say the source account the bank account the credit card whatever i'm gonna delete that and i'm gonna show you the way i would do it the way i would do it is i i'll insert a column right at the beginning and actually not the beginning let me just do it right here i'm gonna do it right here and then move uh i'm gonna move date over to the second one so i'm going to move date over to the second one and i'll delete the first date okay and we'll call this date and the first one will be called source okay so then this would be like the source account that you're making the expenditure from so in here i'll put uh you know checking checking 1179 and then i'll do all my data entry for checking 1179 so i'm just going to do a couple of copy and pasting here just to kind of speed up the process get get a little bit more data in here just so you can see that so as i enter all my data i can just select the first field hold the shift key on my keyboard go all the way down to the last cell that will select the entire group edit right so i want to edit the first field while they're all selected in there hit ctrl enter and that will load them all in there okay and you know you just have to be aware that any anything that's an expense in this case that would be a positive number and anything that's uh that's income it should be a negative number now you can do a debit and a credit column but i find it much uh easier and better to just kind of follow that sequence the debit is a positive and the credit is a negative so let's say for example we're gonna go ahead and enter a a a deposit so let's say on the third we have customer abc deposited at 5 000 and we put that under sales okay so that's it that's that's pretty pretty pretty simple stuff and then the way you would uh then do let's say a profit and loss with this is you take this table and as long as you have it set up as a table you go into the insert tab insert tab and then we're going to go to pivot table okay now one thing i noticed i didn't put the negative sign on the deposit so i got to make sure i stay consistent with what i'm saying so we do a negative sign there for the deposit so let's go back into uh insert and then a pivot table so we're going to go ahead and do a pivot table and then hit ok and then basically this will allow me to create a report for me so i usually will do something like account on the rows and then amount under values okay and that will give me sort of a quick uh profit and loss report now i do need to be aware that positives are debits and uh and credits are negatives if i'm going to do it the other way i just want to be conscious of which ones are positive and which is a negative could you do that in credits yes of course you can do debits and credits right we'll insert a new one and we'll put here uh we'll do one for expense and then income right if you want the debits credits however you want to do it and then you would just enter uh that data on each of the columns i find that the negative and for an avid user negative and positive is a better choice the other thing i would strongly recommend if you plan to build let's say like a p l type of report with a pivot table is to organize your accounts with either account numbers or just with simple ordering like for example let's say i want to make all my sales once and then i will make all my expenses threes and we'll make we'll make cost of goods sold twos right so you could do that so i'm just gonna put a three at the beginning of that uh category there okay so i'll just modify one and then hit ctrl enter and then we'll do this and i'm gonna show you multiple approaches to this um so because if you don't want that number in there and i don't understand why you wouldn't want that number in there and i'm going to go ahead and go to a pivot table analyze up here okay and then i'm going to click on refresh so my table gets refreshed and then i'm going to go into my pivot table and bring in my income as a column and actually as a value and then i'll bring my expense as a value as well and then we have them organized in this in this fashion right and this will happen automatically because we have the number in in front of each of these accounts but i'm going to show you a building a chart of accounts prior and doing the data entry with a prior chart of accounts so let me delete that uh that pivot table completely and i'm just gonna clear out these accounts here completely i'm going to clear this out let's do clear and then for this we'll just do debit and credit make it a little bit more kosher and then i'm going to do is i'm going to bring in a chart of accounts that i already had in excel so we have i have that here somewhere let me pull that up okay so let's say for example i got a chart of accounts already right so i already had let's just say i had a chart of accounts already somewhere set up for this and what i'm going to do is i'm going to bring in that chart of accounts over to my blank data entry sheet so i'll just bring that over here click and drag that okay so now i have essentially a chart of accounts right so i can work a couple of ways if i want to do account number based data entry i can set that up or i can do account based data entry so i'm going to do account number based data entry right so what i'm going to do is i'm going to create a new column here and we're going to call it account number and then account name so what i'm going to do is i'm going to do a vlookup where whatever i put here under account number resolves on their account name so let's say for example for let's do job expenses job materials is 543c so i'll type 54300 right and then i want let me change the formatting so this is just a regular number format and no decimals definitely no decimals okay and i want this number to resolve the account name that i already have in a chart of accounts type of sheet already i'm going to zoom this in so it's a little bit easier to read okay so that's what i want all right so what i'm gonna do is i'm gonna do a vlookup okay so the vlookup and this is one of the most common things that people want to learn in excel is a vlookup first thing i want to do is i'm gonna uh hit ctrl t and make my chart of accounts into a table you will see the common theme here is that we're going to be working with tables hit okay right and then this table okay this table is going to have um is going to be a basically a fixed a fixed table when we when we select and the way you know the table name is when you select the entire table you will notice that this thing is called table 3. so now i will use table 3 as the array for me to do my vlookup and for vlookup to work you have to make sure that the first column is the column that we're going to do the search on so i'm going to come back in here and then i'm going to type a formula which is equals vlookup okay and i'm going to look up this value and notice that when you see the formulation you see this weird characters here it's because it's using the table reference not using the specific um the the specific cell right because this is gonna this is gonna basically copy across the board so we're gonna look up that in in table three okay in table three because that's the name of the table and then i'm gonna recall the second column because i wanted to return the second column and then we always put false on vlookup i'm going to go ahead and zoom this in so you can see it this is the formula here and the only thing that might be different from what you're used to is this thing right here right where normally this would say something like e2 right that's what we're used to seeing e2 because it's this field but the way tables work is when you do that formula and press enter the formula gets copied automatically down and it's every single time we create a um a uh a line so for example my account for my sales in this case let's say we have construction income which is four zero one zero zero i just type here four zero one zero zero press enter and it will automatically figure out what that is for me let's say my office applies and again this is probably more useful for people that memorize the chart of accounts so 6300 press enter and that will recall that that account for me i'm going to show you a different technique i'm gonna go ahead and insert a new column and at least delete these two and we're gonna i'm gonna call this one account okay and what i'm gonna do is i'm gonna do what's called a drop down list and that's called a list validation so i'm going to select the cell that i want to work with i'm going to click on data so data and then i'm going to click on where it says list validation okay it's a little icon that looks like this it's got a sort of a check mark and then a no it's next to text to columns if i hover over it you see it's called data validation so i'm going to go into data validation this is inside the data tab and then i'm going to tell it look the only thing that you can enter here is going to be a list right so it needs to be a drop down list and then i have the option to ignore blank okay and do an insult drop down which is what we want all right so that's going to be the default setting then under source i'm going to click on that button right there so i'm going to click on this button to go find the source click on source and then i switch over to this tab and i select this column so all i have to do is select uh the entire column b and then basically i'm telling it that this is going to be a um a a drop down menu that only contains the account uh the account names as they're listed here so then i'm going to press enter and then hit ok and now i got an instant drop down menu right here i got an instant drop down menu of my uh accounts from my chart of accounts so i can pick uh the right category here and and do my categorization uh from here right so i just want to make sure that that cell it's all the way down and i can pick each of the categories so let's say this is office supplies okay there are some pros and cons to this and i'm going to explain them in a second so office applies so in the pro is you get the drop down menu and you get all the options okay uh the con here is that you don't you can't search it so i can't um come in here and type meals and hope that i'll magically find it from the list like i literally have to find it in the list i can't search so i think that's where you know maybe we're used to the way quickbooks does the data entry or most accounting software does that entry where you start typing a couple letters and it shows you uh something unfortunately that's not a built-in function you can do in in in in in excel okay i will show you an alternative which is kind of an interesting one so i'm gonna go ahead and duplicate this um i'm gonna click on move or copy so i can duplicate this sheet and i'm gonna show you a different technique and hit ok so let's say this is going to be a different account okay and i'm going to go ahead and just delete that column completely and what i'm going to do is i'm going to clear all the data and clear all the data here and then what i'm going to do is i'm actually going to um create a like a bunch of bogus transactions so i'm going to do 12 31 2019 and then i'm going to take my chart of accounts and copy the entire chart of accounts here okay i'm going to copy the entire chart of accounts copy it and i'm going to paste it in let me insert a new um a new column here okay hold on let me insert a column first and then come in here and paste them not this one sorry this one okay and i'm going to call this one account okay and i'm just gonna put zeros across the board for this just bring this all the way down with a bunch of zeros and i'm gonna bring in the states okay so i'm basically creating let's call it one transaction per account all for 12 31 2019 if i don't want to see them i can just simply just filter them out so i can filter them out if i don't want to see them right so i want to see everything about that i can filter them out that's always a possibility but what's cool about starting like this is i can scroll all the way down to my next line so let's say i want to start doing my data entry here and i'll do uh let's say this is from checking account 1179 and i'll start typing 0 1 0 2 20 20 i'll put here chipotle you're gonna see this is pretty neat so 25.89 and now if i come down here and i type uh meals i type m e a notice that this did like sort of an autocomplete okay and this only works when the adjacent uh cell on top of it the whole data set has a list the other thing let's say for example i wanted to put let's say insurance okay so i just come in here and type ins and sometimes we'll it will find it quickly depending on on the on on the on excel is kind of tricky when it comes to that but you have an alternative which is you can right click on it and click on where it says pick from drop down list okay oops i clicked on the wrong one let me try this again right click and click pick from drop down list and then without me doing the data validation it will create a temporary drop down list of all the uh options that are right on top of it so you're not always stuck having to do a list validation you can simply right click click on pick from drop down list and then you get a quick drop down list based on the stuff that's on top of it and again if you don't want to see it all you have to do is hide it so we can hide that one so that way we we enter something what looks like a clean thing from scratch although the the the transactions are there uh i mean they are there they're hidden uh for a minute um but you can still click do the drop down list and have access uh to them okay uh that's because again we didn't do a full data validation option for this we basically just entered the all the options in the top all right so any questions go ahead and and put them in the in the chat box anything that i've may have missed if i went too fast if something didn't make sense maybe they're why i was doing it a certain way go ahead and ask that okay so we covered the vlookup and we also covered the that one so let's talk a little bit about a pivot table for a second and i wanted to do a couple of more things when it comes to a pivot table so i'm going to go back into my excel spreadsheet and then what i'll do is i'm going to do a lookup let me undo this for a second on height and i'll remove that filter okay so we're going to create a new one for account number so i'm going to call this account number and then i'm going to go into my chart of accounts and i'm going to create a new column called report this is pretty cool you'll see how this works so i'm going to do one called report so i added a new column called report and then i'm going to type balance sheet for one balance sheet and i'm going to copy and drag that down and then where my accounts start becoming p l accounts i'll put here uh profit and loss so basically i'm creating a vlookup chart of accounts table so when i'm doing my data entry i'm going to type the account number i mean the account name but i'm going to have it do a vlookup on this the only thing with vlookup the way the way it works is you have to have the first column as the one that you're searching so all i have to do is cut the account number and paste it at the end right and then i can just delete delete that actually let me do it again just make sure it's not doing it wrong and then copy and paste that in there it needs to be within the table otherwise it will get confused so that's within the table all right so i had i got the account name then i got the report then i got the the number let me change this to a number here so then i'm going to go back into my data sheet where i'm going to type i'm going to type the the account name but i want the table to resolve the account number and the report that it belongs to so i'll also create a new one and we'll call this one report so i'll just do two vlookups so with the vlookup again so vlookup and then the value will be the account name and then comma the table array would be the third column again because it's based on one two three how many columns are in my table so i want the third column and at the um that's gonna be under table three and then that would be third column and then false so i'm gonna zoom this in and this is the syntax this is what i'm searching with the which is the account name this is where i'm searching it which is the table array this is the no the column of the table and false means don't give me approximate give me exact and then i'll press enter okay and i want to make sure that this is on the right format and then i'll enter that again perfect there's my vlookup so notice that all of my account names are being resolved with an account number i'll do the same thing with report i can just take that entire vlookup formula copy it and then i can paste it here but then what will change is the column number so i want it to return column number column number two which is the one that contains the report name and you're going to see this going to make a lot of sense when we do our um our pivot table report okay because generally speaking we are doing data entry and we're just going to pick our account name and then we want the account number to be used for reporting purposes so you're going to see exactly how how this works okay um so i'm just gonna put a number here around them just to have something in here and i'm just gonna drag this down so we got some numbers in there okay just for the purposes of having something okay so we got something in here and then i'm gonna run a pivot table so i'm gonna go to insert pivot table and i'll create a new pivot table and then i can now choose uh what account i want so for example i can go into a report here and i'll click and click and drag that into filters and i can say look what i really want is a profit and loss and then i hit okay and then i can take my account number drag it into rows okay and then i can take my uh my amount my say the debit amount drag it into values and then i can take my credit amount drag it into values and there we go let's just make sure that we have all these things are correct we got balance sheet and we have okay all these should be profit and loss let me just make sure that my chart of accounts is correct okay so all these should be profit and loss okay perfect so i'm gonna go back here okay that's all correct then i go into my uh my pivot table then i'm gonna click on refresh okay and then i got my my profit and loss report based on a account number and and it's going to be in a profit and loss format the other thing i can do is i can concatenate the two and i'm gonna so i'm gonna create one more new column and then what i'll do is i'm gonna do a concatenate where i do equals concat okay i can cut is a new one it used to be called concatenate and then i'm going to pick account number as the first one and then comma i'll do quote hyphen space quote okay let me just zoom in so you can see it basically i'm going to insert a space a hyphen and a space between the account number and the account name comma and then account name close the parentheses press enter this is not going to show me a combined account number and account name so we'll call this number slash name and then with something like this i can go back into my pivot table i'm going to go ahead and refresh my pivot table one more time and we're gonna uh uncheck account number and bring in the number name as the rows and then look check it out so now i got what looks a lot more like a sort of pnl type of format on a on a pivot table you

Channel: Hector Garcia CPA

Views: 159,501

Keywords: quickbooks, tutorial, desktop, online, pro, premier, enterprise, accountant, bookkeeping, accounting, 2020, quickbooks online, quickbooks tips and tricks

Id: hX9uic6L6y4

Length: 32min 17sec (1937 seconds)

Published: Wed Nov 18 2020