and now we will learn how to use the batch enter transaction window only the accountant Edition
has the batch enter transaction window here are some things you need to know about batch entering
transactions number one it's the most simple way to import transactions over 90 percent of
the transactions that a company typically has will import this way and you just copy and paste from
Excel so you need to know a little bit about Excel but very little because all you're really going to
do is copy and paste now let's open the window and see how it works well from the main menu
in the accountants edition we click accountant batch enter transactions and we get the batch
enter transactions window notice there are one two three four five six seven eight different kinds of
transactions that you can import but these are the eight most common and popular transactions
that companies usually have notice when I choose a different transaction type the columns become
different that's because different transactions have different fields of information now first
you have to choose a transaction type for example deposits then you have to decide which columns of
information you're going to copy and paste from Excel now not every transaction requires every
field so if we click this customize columns by the way we're clicking this customize columns
at the moment the deposit type of transaction is chosen so when we choose the transaction type we
get columns that QuickBooks suggests but then when we click customize columns we have choices to add
additional information if we click if we want to add you know a column for cleared and that
happens to be information that's in our excel file that we're copying and pasting from we can click
on it and click Add and it will add the cleared column then when I click OK you'll see there's an
additional column to tell us whether the deposit has cleared or not if I want to remove it I again
click customize columns I can click on the column I want to remove and then I click remove notice
you can also change the order of the columns if I click on date and I click move down move down
move down and then I click OK notice date has been moved over here so this customized column button
is the most important thing because it not only gives you the ability to add or remove whatever
fields of information you have when you copy and paste but it also gives you the ability to change
the order of the columns so that it matches the Excel spreadsheet that you're copying and pasting
from please note some columns cannot be removed it depends on the transaction type and each
transaction type is different for example when it comes to checks if you choose checks and you
click customize columns you can see that the three columns that I have remaining here do not have the
removed choice available I can click here to add a cleared column add a billable column I can pretty
much add any column that's in the write a checks window and when I click OK and click ok here I
click OK yeah we also have to choose customer if we're going to make it billable so we'll add
customer ok so now I have all these columns and if I had an Excel spreadsheet that had all of
this information you know a piece of information for each check for each of these columns I could
copy and paste from Excel and of course I could also change the order of these columns move down
quantity move up you know whatever I need until the columns are arranged in the order in which
I need them to be but notice what happens when I click account the remove choice is not available
it's not available for a mount for cleared I'm allowed to remove that but date I'm not allowed
to remove billable I am allowed to remove cost I am allowed item I can remove customer I can remove
quantity I can remove but these three I can't and the columns that you cannot remove will all depend
on the transaction type that you choose in the top right now before we actually copy and paste
transactions from Excel let's setup the journal report and the reason I want you to set up the
journal report and the way that I'm showing is because it's the best way to clearly see
if transactions have been entered and if they've been entered properly so step one let's open the
journal report from the main menu we click reports accountant and taxes journal now what's next well
let's select all days that'll help us find what we're looking for so all dates I hope you're
following step by step now let's sort the journal by transaction number and put it in descending
order what that will do is that will bring the most recently entered transactions to the
top of the journal sort by transaction number in descending order so we're going to click customize
report from the top left and we're going to click sort by and we'll choose transaction number and
sort by will sort in descending order now when I click OK you can see transaction 246 was the last
one - 45 - 44 - 43 etc now the journal looks the way we need it to look so let's save it up in the
icon bar so we can access it very easily view add journal - the icon are now even if you were not
doing batch enter transactions I recommend to all my clients that you set up the journal this way
because if you're entering many different kinds of transactions you need to know where you left
off and which ones were entered and which ones were not by doing it this way the most recent
ones are right at the top and you know if they were entered and how they were enter now here is
the Excel spreadsheet that we're going to copy and paste from in this case they're invoices and these
are the column names which I'll put in red just so that you know you should not copy and paste
the column headings only the data and you should know that I set this up as invoices and right now
these column headings match the invoice choice in QuickBooks in the batch to enter batch transaction
window so if we choose invoice you can see date number customer amount item quantity rate and
total date number customer amounts item quantity rate and of course I didn't bother putting total
because QuickBooks will calculate that so I match the columns before I started this video but you
will know how to match the columns by doing what I just showed now before we record before we copy
and paste all of those invoices I just have to quickly mention what the rules are for entering
batch transactions number one the columns must be aligned that's also called mapping but I showed
how you're able to do that in in QuickBooks and you could also do that by going to your Excel
spreadsheet and using your Excel knowledge to change the order of the columns the date format
must be here to did two spaces for the month two characters for the day and four characters for the
year separated by the forward slash so my Excel spreadsheet already has the date in the proper
format if you put the date in a diff date format where you put in a different number format you
will not be able to record the transactions and you will get an error there are different types
of date formats if I click here and you can see it's the date if I choose the date to this type
of format to open it up a little it will not work when you copy and paste from Excel now the second
rule is there's only one item per transaction unfortunately you cannot import on an excel you
cannot cannot import an invoice or a check or a credit or any transaction that has items in the
body of the transaction where there's more than one per transaction so I if you notice invoice
number 661 only has five of the education service nothing more the next invoice has six of the web
web services and so on now you can try just for illustration purposes I'm going to make these two
invoice numbers the same because some people think that if you make the tree in the document number
the same QuickBooks will put the two different items on the same invoice it will not they will
be recorded as two separate invoices that happen to have the same invoice number so unfortunately
if it's a check credit or invoice you cannot put more than one item in a transaction now all names
in the field must be spelled exactly and they are spelled in the QuickBooks file if there's even one
character difference the transaction will not be entered and it will just stay there in the batch
transaction field in the batch transaction window until you fix it and make it the correct spelling
so now let's copy and paste from Excel and the example we're doing is voices so here we are
back at the invoices and here we are back at the enter batch transaction window for QuickBooks and
once you have the columns aligned it's very very simple you simply highlight with click and drag
the data part let's go all the way down here oh by the way I want to bring your attention to invoice
number 669 instead of instead of putting quantity and rate i blank and put amount because when you
make an invoice you are allowed to put only an amount instead of quantity and rate and that also
holds true for copying and pasting from Excel so we copy only the data not the column headings we
come to QuickBooks we click in the top left corner now I suggest we remove the number suggestion and
the date suggestion that X that QuickBooks gives us and now that it's blank and the cursor is in
the top left field very simple right click paste and all all of the information gets
immediately copied here now notice what happened here I purposely misspelled the name of the item
and it's highlighted in red that means when I click Save transactions this will not be saved
so watch Oh notice there's a guy named Dave who I also didn't put on the customer list you can fix
this very easily by clicking before you click save transaction but watch let's only fix days
click here click up here and then click add new Dave that's all you have to do click OK and now
the red you know in note and no longer highlights in red because Dave is on the list but education
instead of education is not on the list okay now we could add an item education but let's
not let's click stay transactions click yes and it says eight out of the nine have been saved and we
click okay now this one has not been saved then it remains on the list because of the misspelling but
before we correct it let's make sure the other ones were really entered let's open the journal
report and you can see 669 was entered here okay ah 667 666 well why why was 668 skip because 668
was not entered 668 have the misspelling which will fix instantaneously in a moment but look
here's 666 665 664 and they're all invoices and if you double click on any of them you get exactly
what you saw when you copied and pasted from Excel so you can see anything from 669 going all
the way down anything from 669 down to I guess 661 we're just recently entered from Excel now
let's fix this you can click here if you if you agree with the quick add would you like to add
it now yes you come to the IDA window and click service but unfortunately you have to choose which
income account goes with that service so let's cancel it and let's simply spell it correctly
okay now that it's spelled correctly we were allowed to save it okay or we can simply remove it
and do you see we can just choose the proper item spelled properly and then it will let us record it
and now when we come back to the journal you see 666 was recorded the most reason because I just
recorded it right now and as you can see it's at the very top of the journal now we record credits
in exactly the same place that we record invoices and you may well ask how does QuickBooks know it's
a credit well if you're using the quantity and the rate column the rate column must be negative now
I know that sounds strange but I already tried on the phone with a QuickBooks tech support Pro
advisor person to make only the quantity column negative and when I try to save it it it popped
it back to positive but if you make the rate column negative it will record it as a credit
instead of an invoice and if you're using only the amount column instead of quantity and rate as I
mentioned you were allowed to then the amount must be negative so we come back here to our our excel
file these last two on the bottom I put the amount as negative and I put the amount of negative so
that QuickBooks will know that this is these are credits and I didn't bother with the rate
or with the quantity but I'll do another example with rate and quantity just to show you which one
has to be negative so we highlight this click copy come back to QuickBooks now I'm going to
open the enter batch transactions choose invoice and credits and again go to the top left remove
the number suggestion that QuickBooks suggests and remove the date click paste now notice the
amount stay a negative and QuickBooks filled in the rate but don't worry if the amount is negative
QuickBooks knows that this is a credit save the transaction click yes to have been saved now to
check that they've been recorded properly open the journal and they're right on top 671 and 670 are
both credit memos and if I double click you can see it's a credit memo because we put the amount
as negative from the Excel spreadsheet now to illustrate that last point I made two additional
credits where one of them has the negative number in the rate column that's this one the one to Joe
and the one to Sally has the negative number in the quantity column now let's highlight both of
them and see which one QuickBooks accepts and which one QuickBooks will not accept open the
batch transaction window for invoices and credits ok remove the number suggestion and
the date suggestion paste now notice ok this one the rate was negative but it flipped it over to
make the quantity negative don't touch it let it do what it needs to do but with this one as
you remember the 7 the quantity was negative and QuickBooks the crazy thing removed the negative
and just kept it this way so you have to type in the negative yourself and then click save
transaction yes and then they both went in as credits and you can prove that by coming to the
top of the journal for Sally and Joe they both went in as credits so that's somewhat unusual
behavior but you'll get used to it and you'll work around it as you do batch enter transactions