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

QuickBooks 30-day free trial + 30% off for 12 months:
quickbooks.intuit.com/partner...
Check out all my Advanced Courses:
hectorgarcia.teachable.com
Timestamps:
00:00 Introduction
00:42 Create a TABLE for transactions data entry
10:15 Create a report with a Pivot Table
13:30 Using Vlookup for pulling account name / account number (or vice versa)
18:10 Using Data Validation Lists to pull chart of accounts
25:20 Pulling it all together to create a Pivot Table for a Financial Report
Check out all my Advanced Courses:
hectorgarcia.teachable.com
Call our office 954-414-1524 to setup a private training/consultation about QuickBooks, Excel, Accounting/Bookkeeping, Taxes, and/or General Business Consulting (rates vary from $275 to $650) for first consultation depending on length and consultant required. E-mail Hector your questions [e-mail]

[@Notepad123]

Finally someone who explains excel easily 🙏🏻 thank you

[@WitchyGirl]

Thank you so much for sharing this information. I really appreciate your videos. They are so helpful.

[@jayda7342]

Very impressive! Thanks for useful tips and tricks

[@MagaWatcher]

Thank you for doing such a good job explaining the spreadsheet..This is the first one out of many that i viewed.. and it tops them all.. i like the way you start with a blank page and repeat instructions and give simple examples... I saved this video...

[@HectorGarciaCPA]

Great!

[@ehtinetravel7992]

Thanks Hector you're video help me as accounting student.

[@anychannel5805]

Good training CPA in accounts and finance deals with a lot of data.

[@djones8527]

Thank you, this was very helpful :)

[@brettgoldstein1515]

thanks. this is the best video on a simple way to do accounting on excel.

[@HectorGarciaCPA]

Glad you liked it

[@DreamFirms]

Thanks for this!

[@eyanaeeipa9541]

The best Excell class ever

[@HectorGarciaCPA]

Awesome thanks!

[@janineduropan7372]

Thank you for your video, this helps me a lot!

[@HectorGarciaCPA]

Sure, my pleasure, happy to help!

[@leoalaskachannel6611]

Wow, i was simply amazed by your video. Thank you for sharing. 🥰

[@HectorGarciaCPA]

Thanks, my pleasure

[@sathyanarayanansatagopan9069]

Thank you Sir! Please explain how to convert into XML data to export into tally.

[@mharryanjb.gaspar4573]

Damn, that's pretty cool. I learned a lot, thank you!

[@HectorGarciaCPA]

Great!

[@ahsanali50]

Thanks hector. i really enjoy watching and learning from your videos !!

[@HectorGarciaCPA]

You welcome

[@antonmursid3505]

Antonmursid🇮🇩🙏🙏🙏

[@AccountingandQuickbooks]

Thanks for sharing stay blessed

[@HectorGarciaCPA]

You welcome

[@mikeclaudiajessen7854]

I know this isn't the subject of your video, but I'm curious about how to amend a pivot table, using a source table of eg. 500 transaction involving potential 1099 vendors. Often the vendors will have more than one line of the source table (or transaction or payment made to it) throughout the year. Therefore, please tell me how I can sort the pivot table to show vendors with a total of $600 or more in total expenses for the year. Thanks!

[@umbnmbu4679]

How can I add together two cells containing a time each while using the formula concatenate? example: A1=01:10:00; B1=02:10:00; C1= concatenate("the total time is";" "; A1+B1"). I am doing this, but the formula does not see the time as 'time' and it is giving me weird number instead! any tips please!

[@StefanoVerugi]

thanks for the video
if you need to shift a column like at 8:00 select it and press shift and place it where needed, without making space before
Excel 365 now (Sep 23) allows search in drop-down list

[@HectorGarciaCPA]

Thanks, i had forgotten about shift drag.
The search in dropdown on my excel was not available when i recorded the video

[@ruisantos4520]

Again Hector. Congratulations for this video. Its the best easy to learn video I ever saw done by someone that clearly knoww what he is doing. Do you sell this Exel accounting "allready ready and the entries and reporting" ?? I am not beeing able to reproduce what you are doing in my Exel. I need this took though and instead of trying to fight how to do it where I will be clearly not good due to my lack of programming, I can buy the it from you. Tks

[@HectorGarciaCPA]

No

[@hkpeaks]

pivot table is crazy, it can achieve reading 70M data 1Million row, with only 1.2 second

[@ronmakhan7780]

can you please show me how to use an xlookup here instead of the complicated vlookup formula.i don't get it to work for me thank you

[@jorgeleyva3939]

Excelente

[@HectorGarciaCPA]

Muchas gracias!

[@leonardhandoko5624]

Hi Hector, thank you for this wonderful explanation. Does it means I don't need quickbook anymore?

[@HectorGarciaCPA]

Qb is more powerful

[@maismajdali2388]

THX

[@sandynorwood1551]

Hi Hector
I’m sort of new at accounting. Could you please explain why you posted 5000 sales income as a negative. Wouldn’t that increase money?

[@HectorGarciaCPA]

Credits are negative in accounting.... it’s “positive” from the perspective that cash goes up... either way it doesn’t matter, as long as deposits and expenses have opposite signs

[@Toosaucyy4u]

so my question is the accounts are those made up or are you suppose to get those from somewhere. I get it's to categorize the expense or income but the account number I don't see where that comes from?

[@bhavneshparikh8283]

How to download this file

[@WormholeVenue]

can i make a pivot table to total values sorted by the data in a drop down box

[@HectorGarciaCPA]

Yes in the field settings

[@gitaaria]

If the bank statements doesn't have a category and we would like to have a cost of each category (Meals for instance) how can we have that category automatically been created on one column. For instance once it find the word Panera, automatically shows "Meals" in another column on the same row? Or let's say if there is the word "Home Depot", how can we have the word "Construction Material" to be typed in the Category Column? Also Would it be possible to download chart of account for excel?

[@HectorGarciaCPA]

If you can add artificial intelligence maybe.. but excel doesn’t do that for you automatically. You can download a chart of accounts, but would still need to categorize it yourself

[@jennifergreen474]

Is there a way to split transactions, but keep them on 1 line, like you can in QuickBooks?

[@HectorGarciaCPA]

No.. they would need to be in two places

[@ruisantos4520]

Good morning Hector. What is the version of Exel you are using ? I try "Control T" in my Exel and it do not pop up the Table to create the funcionality you have just shown.... My Exel is probably old ... Do not know how to see the version but I remember that I bought it quick a while ago.

[@HectorGarciaCPA]

In Mac is different shortcuts

[@ruisantos4520]

@@HectorGarciaCPA Tks ... Will keep trying. My PC is not a Mac... Anyway. Tks

[@marthacressy7543]

I don't see this course on teachable. Can you confirm it Is no longer available?

[@HectorGarciaCPA]

hectorgarcia.teachable.com/p/dataimport

[@jajasatorashi136]

Hi what excel version is this? Thx

[@HectorGarciaCPA]

2020

[@jajasatorashi136]

@@HectorGarciaCPA thx

[@user-yt9sp3wc6f]

mi hijo hizo una compania y quiere que le lleve las finanza pero no se mucho ingles como le ago JMI Hola

[@HectorGarciaCPA]

Aprende quickbooks

[@antonmursid3505]

Antonmursid🇮🇩🙏🙏🙏

[@antonmursid3505]

Anton🇮🇩🙏🙏🙏

[@kigulalujjioffical7927]

Can you make me a banking system

[@HectorGarciaCPA]

No

[@Asaptaxes]

Hi Hector, I have a great excel bookkeeping template for new business owners, hoping we can collab on this project.

[@HectorGarciaCPA]

I asume you are selling it?

[@Asaptaxes]

@@HectorGarciaCPA Yes. If we collaborate, you would be an affiliate marketing partner and earn rev share. Is it okay if i send you an email with more information?

[@binthawwa3532]

2nd part is paid or free?🤕

[@HectorGarciaCPA]

It’s inside a paid program.

[@premlata7053]

Fb link

[@HectorGarciaCPA]

K

[@bhavneshparikh8283]

How to download this file

[@HectorGarciaCPA]

Not available