Excel Basics #18: Data Analysis Sort

[ishaq79]

I love ... sound you make while teaching ... zzzzzp... drrrrrooop

[ExcelIsFun]

@ishaq79 , cool! It does make it more fun!

[SuperNancyCampbell]

Great job on the video.

I still use Excel and another database called Mybusybiz for my business.

[superbeast326]

How can I lock cells based on certain conditions?

[ExcelIsFun]

@superbeast326 , maybe if you wanted to lock F2 if column A had "Flat" in it, otherwise Relative Cell Reference, a formula of this type might work:

=IF(A2="Flat",$F$2,A2)

[superbeast326]

@ExcelIsFun lets say I have locked B3:B50 and kept B2 unlocked, I can use the above formula to unlock B3 the moment B2 is filled with data and so on and so forth for all cells until B50?

[ExcelIsFun]

@superbeast326 , I have no idea. Try posting to THE best Excel question site:

mrexcel [dot] com/forum

[superbeast326]

Is it possible to use mySQL queries with Excel?

[ExcelIsFun]

@superbeast326 , I do not know SQL, so I can't answer your Q. Sorry.

[Gamer4Life223]

Wow man, you really love Excel. =P

[MrFartsalot1]

Mike Girvin,

If I want to look at all my formulas without F2 how do I do that???

[ExcelIsFun]

@MrFartsalot1 , double click cell, or, look in formula bar, or, use Formula evaluator (Formula Ribbon Tab)

[anomalytrade]

I appreciate your expertise on Excel as well as your organizational skills in delivering this skill. Thanks man. You're awesome.

[ExcelIsFun]

@anomalytrade , I am glad that the videos help!

[twnether]

Your voice reminds me of former UFC WW champ Matt Serra.

[anomalytrade]

@twnether heheh. I can see that.

[sov19871987]

Mike Girvin,

Should I study Excel data analysis or Access? In the future, I might need to use data analysis for customers and vendors, which one would work better for me?

[ExcelIsFun]

It depends on your job. However, more jobs require Excel. Why not just learn both, since the techniques are different, but the theories are exactly the same.

[AlexVolker]

Just to add to my previous post I am able to get the number to read like a cel number by converting it to text and manually adding the 0 and the spaces, but I need to autmate the process by formatting an entire data range in the same manner

[ExcelIsFun]

Google should be deeply ashamed of themselves for how badly the comment section og YouTube works.

[AlexVolker]

Great tut! I Need some help with formatting number to text. Excel in the olden days allowed my to convert a number to text with a format like this 0## ### ####. Now for the life of me I cannot get this to work anymore. Please point me in the right direction. Regards Alex

[ExcelIsFun]

Try Built-in Text Number format:

1) Ctrl + 1 (open Format Cells dialog box)

2) Number tab

3) On Left click on Text.

I hope I understood your question correctly.

[AlexVolker]

@ExcelIsFun Thanks for the prompt reply, In the old versons of excell I was able to format a number such as a cell number to start with 0 and then break the number down to have a space after the third and sixth digit via the custom text field in format number using 0## ### ####. Simelarly I could achieve the same effect with text(A1,"0## ### ####"). Now the result is "0 ### ### ###. I cant get rid of the space after the zero and make the number look like a cell number anymore. Any solution?

[aman1245singh]

Hi sir, you know what, when the basic video number 17 started I thought for the first time your speed will heavily overcome my understanding lol. But, thanks for the videos again and you rock :)

[ExcelIsFun]

I am glad that the video helps!

[russkeyuk]

Hello, each time I go to sort the data all of the column headers eg date,region are also sorted. How do I stop this from happening. Regards Russell

[ExcelIsFun]

You can:

1) format headers with a different format than the rest of the data

2) Use the Sort dialog box, then check the check box that says "my data has headers in the first row".

[Thahzeeb]

Thanks for posting such great videos!!!

I just had two doubts after watching this video: 1) After all the sorting, how do I come back to its original state, i.e. to the unsorted data. 2) Why didn't excel sort the row header? In other words, how did excel know that the data starts from the second row?

It would very nice of you if you could just help me with these doubts.

[ExcelIsFun]

1) You would have to have an extra column with the numbers 1 to whatever, then sort that.

2) As long as the headers are formatted differently that the rest of the data, Excel is programmed to know that the first row are headers. If you use the Sort dialog box instead of the sort buttons, there is a check box that you can check to tell Excel that the headers are in the first row.

[Thahzeeb]

@ExcelIsFun Thanks a lot Mike.

[ExcelIsFun]

You are welcome!

[effuseakay]

I would like to thank you for your generous help! This is so nice of your to put up all these videos. Excel Is Fun actually! thank you so much!

[ExcelIsFun]

You are welcome!

[ddcdpkhb]

I am so much thankful to you man. I practiced your sheets and learned so much. Again thanks and you must keep up the GOOD Work ...... Regards.

[ExcelIsFun]

You are welcome! I am glad that they help - that is why i put them out there. Remember to rate all videos 5 stars!

After this series, go on to watch the Highline Excel Class series - it takes off from the end of this series and goes into more advanced Excel...

[cumnkisme]

you are truly a gift from heaven

thank you very much

rhea bruna

[ExcelIsFun]

You are welcome very much!

[lexingtonlp]

You are really making a difference in this world. I cannot begin to tell you how helpful these videos are. Your teaching is superb sir! Greatly appreciated!

[ExcelIsFun]

You are welcome!

[TheJlt1953]

Hi Mr. ExcelisFun,

Please show me how to download the excel file that goes with this video so I can study. On your webside, I see : many Excel series, I tried with some of those series but failed. The data I had not the same as yours on Screen.

I watched 3 videos 18, 19, 20 and could not download the right excel files.

Please help. This is my first time to know your

website. Thank you very much.

[ExcelIsFun]

Search for and watch this video title:

Search For Excel Videos Download Excel Files excelisfun

It shows how to do all that and more!

[SFDSea]

Your videos are awesome. Love them

[ExcelIsFun]

I am glad that you love them! I love to make them - so it works out!

[macyomi]

Man you have save me so much time..thanks keep up the good work.

[ExcelIsFun]

Dear macyomi,

I am glad that the videos help to save time!

--excelisfun

[ExcelIsFun]

Dear junting1988,

If you keep watching, I'll keep making videos!

--excelisfun

[alainaubrey]

thanks dude....it help me alot!!

[ExcelIsFun]

Dear alainaubrey,

I am glad that the videos help!

--excelisfun