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

Рет қаралды 161,335

Күн бұрын

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 hector@garciacpa.com

Пікірлер: 71

@Notepad123 3 жыл бұрын

Finally someone who explains excel easily 🙏🏻 thank you

@jayda7342 3 жыл бұрын

Very impressive! Thanks for useful tips and tricks

@eaglegemstonepropertiesllc9415 Жыл бұрын

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 3 жыл бұрын

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

@WitchyGirl 2 жыл бұрын

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

@mikeclaudiajessen7854 3 жыл бұрын

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!

@anychannel5805 3 жыл бұрын

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

@brettgoldstein1515 Жыл бұрын

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

@HectorGarciaCPA Жыл бұрын

Glad you liked it

@sathyanarayanansatagopan9069 2 жыл бұрын

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

@djones8527 2 жыл бұрын

Thank you, this was very helpful :)

@DreamFirms 3 жыл бұрын

Thanks for this!

@leoalaskachannel6611 Жыл бұрын

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

@HectorGarciaCPA 4 ай бұрын

Thanks, my pleasure

@janineduropan7372 Жыл бұрын

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

@HectorGarciaCPA 4 ай бұрын

Sure, my pleasure, happy to help!

@eyanaeeipa9541 Жыл бұрын

The best Excell class ever

@HectorGarciaCPA 4 ай бұрын

Awesome thanks!

@ahsanali50 3 жыл бұрын

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

@HectorGarciaCPA 3 жыл бұрын

You welcome

@antonmursid3505 2 жыл бұрын

Antonmursid🇮🇩🙏🙏🙏

@umbnmbu4679 3 жыл бұрын

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!

@mharryanjb.gaspar4573 2 жыл бұрын

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

@HectorGarciaCPA 2 жыл бұрын

Great!

@AccountingandQuickbooks 3 жыл бұрын

Thanks for sharing stay blessed

@HectorGarciaCPA 3 жыл бұрын

You welcome

@StefanoVerugi 9 ай бұрын

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 4 ай бұрын

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 Жыл бұрын

@ronmakhan7780 3 жыл бұрын

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

@hkpeaks 3 жыл бұрын

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

@sandynorwood1551 3 жыл бұрын

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 3 жыл бұрын

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?

@leonardhandoko5624 Жыл бұрын

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

@HectorGarciaCPA Жыл бұрын

Qb is more powerful

@jorgeleyva3939 Жыл бұрын

Excelente

@HectorGarciaCPA 4 ай бұрын

Muchas gracias!

@WormholeVenue 2 жыл бұрын

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

@HectorGarciaCPA 2 жыл бұрын

Yes in the field settings

@gitaaria 4 ай бұрын

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 4 ай бұрын

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

@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

@maismajdali2388 2 жыл бұрын

THX

@marthacressy7543 2 жыл бұрын

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

@HectorGarciaCPA 2 жыл бұрын

hectorgarcia.teachable.com/p/dataimport

@bhavneshparikh8283 Жыл бұрын

How to download this file

@jennifergreen474 4 ай бұрын

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

@HectorGarciaCPA 4 ай бұрын

No.. they would need to be in two places

@antonmursid3505 2 жыл бұрын

Antonmursid🇮🇩🙏🙏🙏

@jajasatorashi136 3 жыл бұрын

Hi what excel version is this? Thx

@HectorGarciaCPA 3 жыл бұрын

2020

@jajasatorashi136 3 жыл бұрын

@@HectorGarciaCPA thx

@antonmursid3505 2 жыл бұрын

Anton🇮🇩🙏🙏🙏

@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

@kigulalujjioffical7927 2 ай бұрын

Can you make me a banking system

@HectorGarciaCPA 2 ай бұрын

@Asaptaxes 3 ай бұрын

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

@HectorGarciaCPA 3 ай бұрын

I asume you are selling it?

@Asaptaxes 3 ай бұрын

@@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?