Excellent and interesting demo of how to use Excel much more effectively. I've never heard of most of this before, and have been referencing ranges and cells in my formulas. As I've been using big spreadsheets these tips will make the work I do far more efficient. Big thank you and thumbs up
Hi Alan.. great summary of the benefits of Excel tables. Another good feature is the ability to have auto totals of varying types (sum, average, min, max, count, etc.) at the bottom of each table column. Keyboard sequence is ALT-JT-T (pointer must be in table to start). I use that all the time. Thanks again and Thumbs up!
Love the very practical concepts you always discuss. Would be great if you can create such videos about possible problems with shared excel files and linking two workbooks together.
Hi, great video.....I'm trying to find out how to add data to a table without having to type it in? When I copy it in my table it doesn't appear to be part of the table with banded rows, etc.....is there a trick for that?
Excellent summary. Very helpful👍 I have used tables but didn't appreciate the full extent of the benefits. Naming I was advise to give the name a prefix e.g. "tOrders" so when typing the name you get a table not a "named range". Don't know if this is a good practice.
Are tables faster when traversing via VBA to do the normal CRUD (Create, Read, Update, Delete). I get the "use" advantage of tables. But Bottom line question is do tables allow better performance or not if ranges are converted to Tables
I believe they are a little slower but this will only be a factor if dealing with large data. And in those scenarios, depending on the instance, the table could be treated as a separate workbook (or maybe kept in external source) and imported as a query.
Very helpful video. One thing I found trying this out is that, in order for the table headings to replace the column letters in the excel sheet header, is that the header row of your table must have its first cell at A1.
Thank you, Al. The headers don't need to have cell A1 as their beginning. They can be anywhere on the sheet as long as the Table knows the first row are headers. When you scroll down, the active cell must be in the table for the headers to replace the column letters. That is probably what happened.
@@Computergaga update: it works as you have explained, thanks again. At first I thought I would have to select a cell in the table before scrolling. But then I discovered that, once the table header has scrolled out of view, you can toggle the excel header between the table header and column letters by clicking somewhere inside the table and somewhere outside.
I have a 7-column spreadsheet with a date in the first column, a number in each of the adjacent 6 columns. I am trying to figure out how I can set up a secondary area where I can enter 6 numbers (one in each column cell on a single row) and have Excel return the date that all 6 numbers that I enter came up as an exact match within my data or return a message that no date was found for the 6-number entry that I enter. I was wondering how I could do that and am requesting some help with the project. I have seen the VLOOKUP function and the INDEX and MATCH functions illustrated on TH-cam videos but am not proficient with these functions as I have mostly worked with very basic functions in Excel.
Yes INDEX and MATCH is required because the date is in the first column of the range. I would join all numbers together into one cell in a helper column and use that for purposes of the lookup. I have an example of VLOOKUP being used for this here - th-cam.com/video/ZqWTsmk2Jk8/w-d-xo.html Just adjust for an INDEX and MATCH version. It will be a nice challenge.
10th Reason: inserting a row in a table automatically copies formulas down into the new row. eg if have an "Extended Cost" column that has formula which multiplies Quantity column times Cost Each column, etc
I wonder why all these articles are titleed "X Number of Reasone You Should Use Tables", then they don't list off the reasons. SMH. Im going to keep googling until I find out if I need to use tables, but Im frustrated why no one can simply give the reasons.
Odinata, I'm confused. Did you watch the video? The video shows you how to create a table and style it, then lists the 9 reasons and demonstrates each one. It's exactly what you want. At a basic level the primary benefits of a table are that they are dynamic and meaningful references. I cannot encourage the use of tables more. They are fantastic! At a more advanced level, they are used for Power Query and the data model. This is all shown in the video.
@@Computergaga Oh, so th e first reason to to use a table is to "Control the formatting of a table" at 0:44? "It creates more meaning to excel"? Thats Reason one? More "meaning"?
It's the first mentioned in the video. Not necessarily the main reason. But yes more meaning is a huge reason and covers many bases. - Instead of classic Excel using the grid such as D3 and column F. Using a table means Excel understands what is has. - You can use column headers such as 'Country' instead of letters like E = more meaning. - You can reference the current row in calculations instead of a cell such as D432 and you have to know you're in 432. - You can refer to a table by name such as 'Germany' instead of something like 'Germany'!$A$1:$G$200 = more meaning. I hope you find it useful. It takes my time to produce free content on here to help. If you don't appreciate it, move on. Why moan that it is not up to a standard when you clearly didn't even watch it because the reasons all have a section slide to introduce them.
Excellent and interesting demo of how to use Excel much more effectively. I've never heard of most of this before, and have been referencing ranges and cells in my formulas. As I've been using big spreadsheets these tips will make the work I do far more efficient. Big thank you and thumbs up
Thank you David.
Really useful... keep posting the videos about Excel.... Your work is appreciated..
Thank you Velavan.
Fantastic Video done in an excellent manner.
Thank you so much 😀
Hi Alan.. great summary of the benefits of Excel tables. Another good feature is the ability to have auto totals of varying types (sum, average, min, max, count, etc.) at the bottom of each table column. Keyboard sequence is ALT-JT-T (pointer must be in table to start). I use that all the time. Thanks again and Thumbs up!
Good tip Wayne, thank you.
Thank You! Love your videos. Keep them coming. This one will save me about an hour a week of frustration!
You're welcome Mike. Thank you.
Very nicely done and "the butter to my bread" is phrase I'd never thought I'd hear in relation to Excel tables!
Thank you, Ross 👍
I like the brief high-level explanation without getting bogged down in details .. thanks
My pleasure. Thank you DubJedi.
Love the very practical concepts you always discuss. Would be great if you can create such videos about possible problems with shared excel files and linking two workbooks together.
Thank you, Ahror. For the comments and suggestions.
I am currently using Excel table feature very extensively. I appreciate your video very much.
You're welcome Keval. Thank you.
Thank you Sir, may I have the practice file please?
This was awesome. I always wondered what the advantages were and now I know. Thank you for a great video.
You're welcome Jim. Thanks.
Hi, great video.....I'm trying to find out how to add data to a table without having to type it in? When I copy it in my table it doesn't appear to be part of the table with banded rows, etc.....is there a trick for that?
Phenomenal explanations thanks
That's very nice. thank you Alex.
Excellent summary. Very helpful👍
I have used tables but didn't appreciate the full extent of the benefits.
Naming I was advise to give the name a prefix e.g. "tOrders" so when typing the name you get a table not a "named range". Don't know if this is a good practice.
Thanks Bob.
If it works for you. Then using a t prefix is good practise.
Thanks for telling me of the repeat.
Nice and good job. Please put all excel practiced files in the description.
Thank you, Muhammad.
Excellent dear sir, God bless you.
You're very welcome.
Thanks for explaining the DAX. Data Expression Language which works only if a table is formated as an Excel table.
Very good sir!
Are there any videos on how to use Power Query Editor?
I have a Power Query playlist you can find here - th-cam.com/play/PLxv7tqbNjU9XolH6MYNakpB_pAVVZqqYa.html
good job. Please put all excel practiced files in the description.
Thank you, Muhammad.
many thanks Alan, nice tutorial appreciate it very much
Cheers
Mohideen
You're welcome Mohideen.
Thank you so much! I can't believe I didn't even know about these.
You're welcome. Thank you.
Are tables faster when traversing via VBA to do the normal CRUD (Create, Read, Update, Delete). I get the "use" advantage of tables. But Bottom line question is do tables allow better performance or not if ranges are converted to Tables
I believe they are a little slower but this will only be a factor if dealing with large data. And in those scenarios, depending on the instance, the table could be treated as a separate workbook (or maybe kept in external source) and imported as a query.
Awesome, thanks. Never really used tables before - that's going to change. Also, I now have the song "Radio Gaga" going through my head.
Sorry about that David. Thank you for the comment.
Thank you so much! ..Your work is appreciated..
You're most welcome! Thank you ❤
Superb man !!!
Thank you so much Akif 😀
Do you have a video on how to draw this table please
You can format a range as a table by clicking Home > Format as Table and picking a style. Then name it on the far left of the Table Design tab.
one word : WAW !
Very helpful video. One thing I found trying this out is that, in order for the table headings to replace the column letters in the excel sheet header, is that the header row of your table must have its first cell at A1.
Thank you, Al. The headers don't need to have cell A1 as their beginning. They can be anywhere on the sheet as long as the Table knows the first row are headers. When you scroll down, the active cell must be in the table for the headers to replace the column letters. That is probably what happened.
@@Computergaga thanks, I'll check that out
@@Computergaga update: it works as you have explained, thanks again.
At first I thought I would have to select a cell in the table before scrolling. But then I discovered that, once the table header has scrolled out of view, you can toggle the excel header between the table header and column letters by clicking somewhere inside the table and somewhere outside.
Great Video !
Thank you Arvinder.
good stuff!
Thank you, John.
I like that I know how to leverage the power of tables now.
Excellent! Tables are awesome.
I have a 7-column spreadsheet with a date in the first column, a number in each of the adjacent 6 columns. I am trying to figure out how I can set up a secondary area where I can enter 6 numbers (one in each column cell on a single row) and have Excel return the date that all 6 numbers that I enter came up as an exact match within my data or return a message that no date was found for the 6-number entry that I enter. I was wondering how I could do that and am requesting some help with the project. I have seen the VLOOKUP function and the INDEX and MATCH functions illustrated on TH-cam videos but am not proficient with these functions as I have mostly worked with very basic functions in Excel.
Yes INDEX and MATCH is required because the date is in the first column of the range. I would join all numbers together into one cell in a helper column and use that for purposes of the lookup. I have an example of VLOOKUP being used for this here - th-cam.com/video/ZqWTsmk2Jk8/w-d-xo.html Just adjust for an INDEX and MATCH version. It will be a nice challenge.
Is there a way to use tables like this in Google sheets?
Not sure. I'm not a big Google Sheets user.
Is there a way to do this in Google Sheet?
Possibly. I don't use Google Sheets.
structured references dont work at our Excel at work. Have checked the Excel options, tab, it's checked, but it doesnt work
As long as the list is formatted as a table - you will get structured references. Format as table using the button on the Home tab.
Am I the only one that hears him say in ever beginning of his video say...
"Computer Gargoyle"?
Lol!
That sounds better. I should have called it that :)
Now if u do that then I'll probably "Computer Gaga". Lol!😂😂
😀😀😀😀
10th Reason: inserting a row in a table automatically copies formulas down into the new row. eg if have an "Extended Cost" column that has formula which multiplies Quantity column times Cost Each column, etc
Thank you. Another fine reason why tables are great.
جميل جدا
Watch in 1.25 time speed, you are welcome
I'm gonna naeem my table!
😊
why do I feel CAP LOCKS in the way you talk... Anyway, great tut!
Thanks 😊
l made it 100 likes
Thank you.
I wonder why all these articles are titleed "X Number of Reasone You Should Use Tables", then they don't list off the reasons.
SMH.
Im going to keep googling until I find out if I need to use tables, but Im frustrated why no one can simply give the reasons.
Odinata, I'm confused. Did you watch the video? The video shows you how to create a table and style it, then lists the 9 reasons and demonstrates each one. It's exactly what you want.
At a basic level the primary benefits of a table are that they are dynamic and meaningful references. I cannot encourage the use of tables more. They are fantastic!
At a more advanced level, they are used for Power Query and the data model.
This is all shown in the video.
@@Computergaga if you can name reason one for using tables and give the time stamp in this video where you stated it, then I'll give you another shot.
Take a lot at the description where the timings are all listed, or just watch it. I've given you 9.
@@Computergaga Oh, so th e first reason to to use a table is to "Control the formatting of a table" at 0:44?
"It creates more meaning to excel"? Thats Reason one? More "meaning"?
It's the first mentioned in the video. Not necessarily the main reason.
But yes more meaning is a huge reason and covers many bases.
- Instead of classic Excel using the grid such as D3 and column F. Using a table means Excel understands what is has.
- You can use column headers such as 'Country' instead of letters like E = more meaning.
- You can reference the current row in calculations instead of a cell such as D432 and you have to know you're in 432.
- You can refer to a table by name such as 'Germany' instead of something like 'Germany'!$A$1:$G$200 = more meaning.
I hope you find it useful. It takes my time to produce free content on here to help. If you don't appreciate it, move on. Why moan that it is not up to a standard when you clearly didn't even watch it because the reasons all have a section slide to introduce them.