Excel Basics #21: Data Analysis Filter &Dynamic Ranges

[tomasek262]

Hey! Where did you learn all that stuff? Amazing :) Thanks

[QQQQQman]

Hi I am having problems with creating table (2003). ctrl t ctrl l does not work. I go to Data, then Table then am asked for Row input cell and Column input cell. For the former I tried A1:A350 etc etc but "INPUT CELL REF IS NOT VALID" message keeps appearing for all I try:(.....

[ExcelIsFun]

In 2003, it is not called a table, it is called a LIST. Ctrl + L does work in 2003. Look for "List" instead of "table" in the menu in 2003.

[yasslimz]

I wanted to make a 2011 calendar split into headings including 4 quarters for the year, 12 months, 4 weeks in each month and the days having daily responsibilities. Is this possible? how?, please as the company i work for would really appreciate this!!

[ExcelIsFun]

I do not know. Try THE best Excel question site:

mrexcel[dot]com/forum

[yasslimz]

@ExcelIsFun Thank you for you help finding an alternative site.

[santakgrg]

thank you soo much for keeping tutorial....it's helping me soo much ...thank you and god bless you...

[ExcelIsFun]

You are welcome!

[deshioccer1]

how do you create table on mac excel???

[ExcelIsFun]

I am sorry. I do not know how to use a Mac.

[NinotchkaofSweden]

Thanks for great tutorials! I have come to a really hard case where I want to sort data depending on several parameters. I'm totally stuck on this. Is there any way that I can get some input/help? It could probably be a good example to show here...

[ExcelIsFun]

Did you try this video title:

Highline Excel Class 16: Sort, Sorting 10 Examples

Otherwise, it is sometimes hard to ask questions at YouTube. The best place to ask Excel questions is this site:

mrexcel[dot]com/forum

If you post, then send me the link and i will help (although the 1000s of other Excelers at that site will help also)!

[fantabulous008]

Dude ur AAWESOME!!! I can't believe how many tips are revealed in ur vids. These tips are help when studying for the MOS Exams!!! Thanks for posting these vids...

[ExcelIsFun]

You are welcome!

[MyToymaker]

This is my favorite channel. You are awesome!

[ExcelIsFun]

Not so much as awesome, as just a guy having fun with Excel!!

I am glad that you like the channel!

[unbalancedrown]

Thank you!

[ExcelIsFun]

You are welcome!

[tempsfuit]

Just wanna comment that your videos are so resourceful, great for excel rookies such as myself - thanks heaps!

[ExcelIsFun]

You are welcome! If you watch all 23 Basic Videos and then move on to the 'Highline Excel Class' series, you can become really good with Excel!

[TheMrBenjamin]

Hey,

Do you have video which covers using the exponential smoothing feature in the Excel Data Analysis toolpak?

Thanks

[ExcelIsFun]

Sorry, but I don't have a video on that topic.

[GeneralApathyNonFan]

How could I filter in duplicates? I have about a thousand names and I want keep those that appear more than once.

[ExcelIsFun]

If the names are in the range A2:A1001, then I would add a new column that has the formulas:

=COUNTIF($A$2:$A$1001,A2)>1

in A2, then copy the formula down.

When the formula result is TRUE, then you keep that dup. record.

I'll make a video soon.

[bail2g2]

I have a question regarding filters. At the beginning of the video you put in a sum formula which is nice, but how do you get it to change (without disapperaring)depending on your filters. For example if you selected the sales rep Rhonda how to you get that sum of sales to change to reflect only her sales.

[ExcelIsFun]

To have the SUM I showed show up when the Filter is used, put the SUM function in a row that is not a row in the table.

To add just Rhonda when the table is filtered, right-click the table, point to Table, then to Total Row. Then when you filter and just show Rhonda, the total will show just Rhonda's.

The SUMIF or SUMIFS functions will work also. Search my chnannel for SUMIF.

[MrAlbatros1]

Hi Mr ExcelIsFun,

I use Excel 2008 for mac. I can't seem to create that table you create in the beginning of the video using shortcut keys or even the Table Under Data in Menu. Help please. Thanks

[ExcelIsFun]

Dear MrAlbatros1,

I am sorry, but I do not know how to use a Mac. Someone did tell me that there is an option to use PC keyboard shortcuts on a Mac. If you can find it, post and let me know so that I can tell others!

--excelisfun

[MrAlbatros1]

These are some of the shortcuts I already figured out:

F2=Control+U on mac.

F4=Command+T on mac.

Control + 1= Command +1. mostly the control in pc can be substituted with Command on Mac.

Thats all I have so far.

I still can't figure out anything for the table in this vid though.

[ExcelIsFun]

Dear MrAlbatros1,

Oh man, I wish that I could help! None of the instructors I know on campus use Mac / Excel so I have no one to ask.

So you are saying Command + T will add dollar signs to a cell reference in a formula?

Did you try a Google or YouTube search for Excel Mac videos?

--WishIKnewMacExcel

--excelisfun

[d4greatmaster]

Hi Mr ExcelIsFun..! How could i find the Excel_Basics_#3? I could hardly find it by searching.. thx for the help.. Anyway.. This is very nice effort on explaining Excel.. good Work...!!

[ExcelIsFun]

Dear d4greatmaster,

Search for and watch this video:

Search For Excel Videos Download Excel Files excelisfun

It will show you how to get the whole playlist.

--excelisfun

[d4greatmaster]

Thx ExcelIsFun..! u r really helpful..

[ExcelIsFun]

Dear d4greatmaster,

You are welcome!

--excelisfun

[mykstrung]

thanks for your lessons. really help. by the way can you pls give some more example for pivot table. find it hard. sorry, I just want to master it for presentation

[ExcelIsFun]

Dear mykstrung,

I am glad that you like it. I have at least 30 other videos on Pivot Tables. Search for and watch this video to learn how to use the excelisfun channel and search for any video you want:

Search For Excel Videos Download Excel Files excelisfun

Let me know how your studied of Pivot Tables go after watch a few videos!

--excelisfun

[ExcelIsFun]

Dear KaroBarca7 ,

I do not. I'll put it on my list of things to do.

--excelisfun

[KaroBarca7]

haha thanks

[KaroBarca7]

hey do you happen to have a list of all your amazing keyboard shortcuts that we can print out?