Sunday, April 12, 2015

Automated Data Column Sorting in Excel

Automated Data Column

Sorting in Excel

Single-Column Sorting Automated With Formulas in Excel

A single column of numeric data can be sorted by using formulas or with the Data Sorting Tool. Using formulas is a much better solution because data sorted by formulas will be automatically resorted if the data is changed or new data is added. The Data Sorting Tool, just like all of the other Data and Data Analysis ToolPak Tools, must be re-run manually when a new sort is required. Unlike Data Tools, formulas automatically recalculate their output when their inputs are changed.

 

Descending Sort Using Formulas in Excel

The following image shows the implementation of a descending sort using formulas in Excel.

The formula can be typed into the first cell and then copied all the way down, in this case, to cell D200.

The LARGE() formula is explained as follows:

LARGE(data range, k) returns the kth largest value in the data range. The position count, starting at 1 at the top, is implemented by the following:

ROW()-ROW($D$3)

ROW() is the number of the current row. If this formula is in cell D4, ROW() = 4 since cell D4 is in the 4th row.

ROW(D3) = 3 since cell D3 is in the 3rd row.

In this case, ROW()-ROW($D$3) = 4 – 3 = 1

If the formula is in cell D5, ROW()-ROW($D$3) = 5 – 3 = 2

Note that the data range in the formula extends from cell B4 all the way down to cell B200. Empty cells in this range are ignored by the sort because of the If-Then-Else statement.

Automated Data Column Sorting in Excel - Automated Descending Sort in Excel
(Click On Image To See a Larger Version)

 

Ascending Sort Using Formulas in Excel

A descending sort can be converted into an ascending sort by substituting the word SMALL for LARGE in the formula as follows:

Automated Data Column Sorting in Excel - Automated Ascending Sort in Excel
(Click On Image To See a Larger Version)

 

New or Changing Data

The advantage of sorting with formulas instead of the Data Sorting Tool is the formulas will automatically resort the data if any data is changed or additional data is added. The following changes were made to the data:

-6 was changed to -7 (colored orange)

New data points 11, 13, and 14 were added (colored light blue)

The data is automatically resorted after these changes and additions. The Data Analysis Sorting Tool would have to be re-run manually to resort the data.

Automated Data Column Sorting in Excel - Automated Sorting When Adata Are Added or Changed
(Click On Image To See a Larger Version)

 

Multi-Column Sorting With the Data Sorting Tool in Excel

Multiple columns of data that require secondary or tertiary sorts require the use of the Data Sorting Tool. Alphabetic data can also be sorted using this tool. Below are 3 columns of data that will be sorted using a primary sort of Column 3, then a secondary sort of Column 2, and finally a tertiary sort of Column 1.

Sorting Multicolumn Data in Excel - Raw Data
(Click On Image To See a Larger Version)

The first step is to highlight the data including the column headers if they are there as follows:

Sorting Multicolumn Data in Excel - Selecting Data To Be Sorted AlongWith Column Headers
(Click On Image To See a Larger Version)

The Data Sorting Tool is found under the Data tab as follows:

Sorting Multicolumn Data in Excel - Data Sorting Tool Location Under Data Tab
(Click On Image To See a Larger Version)

Clicking on Sort brings up the following dialogue box. The column headers show up because they were highlighted with the original data in Step 1.

Sorting Multicolumn Data in Excel - Sorting Tool Dialogue Box
(Click On Image To See a Larger Version)

The following completed dialogue is set up to perform a primary sort of Column 3, then a secondary sort of Column 2, and finally a tertiary sort of Column 1.

Sorting Multicolumn Data in Excel - Sorting Tool Dialogue Box Completed
(Click On Image To See a Larger Version)

Clicking OK performs that sort as follows:

Sorting Multicolumn Data in Excel - Sorted Data
(Click On Image To See a Larger Version)

 

Excel Master Series Blog Directory

 

Click Here To See a List Of All

Statistical Topics And Articles In

This Blog

 

You Will Become an Excel Statistical Master!

103 comments:

  1. Not really much of a formula user in excel because I only know the basics. But I used excel on a daily basis in completing my research paper assistance report.

    ReplyDelete
  2. To avoid such difficulties when writing homework, and to get help in compiling these files, you can contact our specialists

    ReplyDelete
  3. Thank you for this post. This is very interesting information for me.

    ReplyDelete
  4. Thanks for sharing such nice blog site list. nice collection of sites. Busybox My Boy apk Game Tuner

    ReplyDelete
  5. Thanks a lot for the article post.Really thank you! Much obliged.

    ReplyDelete
  6. Wonderful article, thanks for putting this together! This is obviously one great post. Thanks for the valuable information and insights you have so provided here.
    things to do

    ReplyDelete
  7. I don t have the time in the intervening time to fully study your site but I even have bookmarked it and additionally add your RSS feeds. I could be back in a day or two. thank you for a awesome site.
    Top SEO Companies in India
    Best SEO Companies in India
    Top SEO Company in India

    ReplyDelete
  8. Excellent information Providing by your Article, thank you for taking the time to share with us such a nice article. Amazing insight you have on this, it's nice to find a website that details so much information about different artists. Kindly visit the LiveWebTutors website we providing the best assignment help services in Australia.

    For More Info: Assignment Help Brisbane

    ReplyDelete

  9. Great, i must say and thanks for sharing this informative post.I am really impressed that there is so much information about this subject that have been uncovered and you’ve done your best, MyAssignmenthelp.co.uk is giving help to students who is stressed with their homework help and submit their assignment on time.we are already trusted by thousands of students who struggle to write their academic papers and also by those students who simply want online plagiarism checker to save their time and make life easy

    ReplyDelete
  10. I finally found great post here.I will get back here. I just added your blog to my bookmark sites. thanks.Quality posts is the crucial to invite the visitors to visit the web page, that's what this web page is providing.
    360DigiTMG data analytics courses
    business analytics course
    data science course

    ReplyDelete
  11. It is perfect time to make some plans for the future and it is time to be happy. I’ve read this post and if I could I desire to suggest you few interesting things or tips. Perhaps you could write next articles referring to this article. I want to read more things about it!
    data science course

    ReplyDelete
  12. I feel really happy to have seen your webpage and look forward to so many more entertaining times reading here. Thanks once more for all the details.
    Data Science Course in Hyderabad | Data Science Training in Hyderabad

    ReplyDelete
  13. You are in point of fact a just right webmaster. The website loading speed is amazing. It kind of feels that you're doing any distinctive trick. Moreover, The contents are masterpiece. you have done a fantastic activity on this subject!
    Business Analytics Course in Hyderabad | Business Analytics Training in Hyderabad

    ReplyDelete
  14. That’s a nice article, thank you for a great article. It helped me a lot. Keep it up Must Visit panda belgie

    ReplyDelete
  15. that’s a nice article, thank you for a great article. It helped me a lot. Keep it up Must Visit: Dell Printer helpdesk bellen.

    ReplyDelete
  16. I'm cheerful I found this blog! Every now and then, understudies need to psychologically the keys of beneficial artistic articles forming. Your information about this great post can turn into a reason for such individuals.data science courses

    ReplyDelete
  17. Standard visits recorded here are the simplest strategy to value your vitality, which is the reason why I am heading off to the site regularly, looking for new, fascinating information.
    iot training in noida

    ReplyDelete
  18. This was certainly one of my preferred web journals. Each post distributed impressed me.
    training provider in malaysia

    ReplyDelete
  19. I was looking at a portion of your posts on this site and I consider this site is really enlightening! Keep setting up..
    PMP Certification

    ReplyDelete
  20. I was looking at a portion of your posts on this site and I consider this site is really enlightening! Keep setting up..
    360DigiTMG big data analytics training in malaysia

    ReplyDelete
  21. I was just browsing through the internet looking for some information and came across your blog. I am impressed by the information that you have on this blog. It shows how well you understand this subject. Bookmarked this page, will come back for more.
    best institutes for digital marketing in hyderabad

    ReplyDelete
  22. Nice Information Your first-class knowledge of this great job can become a suitable foundation for these people. I did some research on the subject and found that almost everyone will agree with your blog.
    Cyber Security Course in Bangalore

    ReplyDelete
  23. Writing in style and getting good compliments on the article is hard enough, to be honest, but you did it so calmly and with such a great feeling and got the job done. This item is owned with style and I give it a nice compliment. Better!
    Cyber Security Training in Bangalore

    ReplyDelete
  24. High Technologies Solutions has conducted multiple Advanced Excel Corporate Training, VBA Macros Corporate Training batches in all over India. Further More Details Here-+91-9311002620 Or Visit Website- https://www.htsindia.com/Courses/Business-Analytics/adv-excel-training-course

    ReplyDelete
  25. The internet business industry develops quickly as everybody needs to open a store online to sell remarkable items. Indeed, even youngsters are likewise demonstrating enthusiasm for new businesses with setting up a store on the web. Among the numerous fields, style items and frill locate a colossal business. In any case, the opposition is exacting. besimple.com/

    ReplyDelete
  26. I wonder to read that a single column of numeric data can be sorted by using formulas or with the Data Sorting Tool. If you have more information about Descending Sort Using Formulas in Excel, kindly upload it in your upcoming post. Assignment Writing Service

    ReplyDelete
  27. This post is great. I really admire your post. Your post was awesome. data science course in Hyderabad

    ReplyDelete
  28. This is a wonderful article, Given so much info in it, Thanks for sharing. CodeGnan offers courses in new technologies and makes sure students understand the flow of work from each and every perspective in a Real-Time environment Augmented Reality Training. ,

    ReplyDelete
  29. Thank you so much for shearing this type of post.
    This is very much helpful for me. Keep up for this type of good post.
    please visit us below
    data science training

    ReplyDelete
  30. Seo company in Varanasi, India : Best SEO Companies in Varanasi, India: Hire Kashi Digital Agency, best SEO Agency in varanasi, india, who Can Boost Your SEO Ranking, guaranteed SEO Services; Free SEO Analysis.

    Best Website Designing company in Varanasi, India : Web Design Companies in varanasi We design amazing website designing, development and maintenance services running from start-ups to the huge players


    Wordpress Development Company Varanasi, India : Wordpress development Company In varanasi, india: Kashi Digital Agency is one of the Best wordpress developer companies in varanasi, india. Ranked among the Top website designing agencies in varanasi, india. wordpress website designing Company.

    E-commerce Website designing company varanasi, India : Ecommerce website designing company in Varanasi, India: Kashi Digital Agency is one of the Best Shopping Ecommerce website designing agency in Varanasi, India, which provides you the right services.

    ReplyDelete
  31. We are well established IT and outsourcing firm working in the market since 2013. We are providing training to the people ,
    like- Web Design , Graphics Design , SEO, CPA Marketing & YouTube Marketing.Call us Now whatsapp: +(88) 01537587949
    :Freelancing training in Bangladesh
    Free bangla sex video:careful
    good post outsourcing institute in bangladesh

    ReplyDelete
  32. What a really awesome post this is. Truly, one of the best posts I've ever witnessed to see in my whole life. Wow, just keep it up.
    Best Data Science Courses in Hyderabad

    ReplyDelete
  33. Nice & Informative Blog !
    If you are looking for the best accounting software that can help you manage your business operations. call us at QuickBooks Customer Support Number 1-877-751-0742.

    ReplyDelete
  34. Hey!! Great work. You have a very informative blog .You are doing well. Keep it up. We will also provide Quickbooks Customer Service Phone Number to alter Quickbook’s issues. If you have any issues regarding Quickbooks dial +1-877-948-5867 for getting instant help.

    ReplyDelete
  35. Get the digital marketing services in hyderabad digital marketing agency in hyderabad and adevertising agency in hyderabad solutions you need. Our online marketing services include PPC, SEO, social, and more". https://goviralll.com/packages.html

    ReplyDelete
  36. Nice & Informative Blog !
    Are you looking for the best ways on QuickBooks Error 80070057 ? We are here to help you. Call us at 1-855-977-7463 and get the best technical consultation to eliminate QuickBooks Error 8007 at an affordable rate.

    ReplyDelete
  37. Nice & Informative Blog !
    We understand your concern during these times. Thus, we at QuickBooks Technical Support Phone Number 1-855-550-7546 provide permanent resolution for QuickBooks issues.

    ReplyDelete
  38. Nice Blog !
    One such issue is QuickBooks Error 80070057. Due to this error, you'll not be able to work on your software. Thus, to fix these issues, call us at 1-855-977-7463 and get the best ways to troubleshoot QuickBooks queries.

    ReplyDelete
  39. Nice & Informative Blog !
    In case you are searching for the best technical services for QuickBooks, call us at QuickBooks Error 12029 1-855-977-7463 and get impeccable technical services for QuickBooks. We make use of the best knowledge for solving your QuickBooks issues.

    ReplyDelete
  40. Very informative content and intresting blog post.Data science course in Nashik

    ReplyDelete
  41. Hey! Mind-blowing blog. Keep writing such beautiful blogs. In case you are struggling with issues on QuickBooks software, dial QuickBooks Phone Number (877)948-5867. The team, on the other end, will assist you with the best technical services.

    ReplyDelete
  42. Nice & Informative Blog !
    QuickBooks is an easy-to-use accounting software that helps you manage all the operations of businesses. In case you want immediate help for QuickBooks issues, call us on QuickBooks Service Number 1-(855)-652-7978.

    ReplyDelete
  43. I see some amazingly important and kept up to length of your strength searching for in your on the site
    Best Data Science Courses in Hyderabad

    ReplyDelete
  44. I really happy found this website eventually. Really informative and inoperative, Thanks for the post and effort! Please keep sharing more such blog.
    However, if your faces some problems to Receiving Mail In AOL Desktop Gold or another issues, then it’s time to find the solution to fix your AOL Gold issues. So, you can Contact to Emails Custmer Care Toll-Free Number. Where AOL Email Experts are available 24×7 and ready to assist users with 100% resolution.

    ReplyDelete
  45. I am glad to discover this page. I have to thank you for the time I spent on this especially great reading !! I really liked each part and also bookmarked you for new information on your site.
    Data Science Training in Chennai

    ReplyDelete
  46. Great post i must say and thanks for the information. Education is definitely a sticky subject. However, is still among the leading topics of our time. I appreciate your post and look forward to more.
    Data Science Course in Bangalore

    ReplyDelete
  47. Fantastic Site with relevant information and content Shared was knowledgeable thank you.
    Data Science Courses Hyderabad

    ReplyDelete
  48. Great post i must say and thanks for the information. Education is definitely a sticky subject. However, is still among the leading topics of our time. I appreciate your post and look forward to more.
    Data Science Course in Bangalore

    ReplyDelete
  49. Nice Blog !
    Are you looking for extensive solutions for QuickBooks Error 12152? Do not worry at all. Just reach us via our QuickBooks Error Support Number and solve all your troubles related to QuickBooks in less time.

    ReplyDelete
  50. Thanks for posting the best information and the blog is very informative.Data science course in Faridabad

    ReplyDelete
  51. Get instant support on why is my printer printing blank pages and other errors like slow printing, both on Mac and Windows 10, at HP Printer Customer Service.

    ReplyDelete

  52. Impressive. Your story always brings hope and new energy. Keep up the good work.
    business analytics course

    ReplyDelete
  53. I am glad to discover this page. I have to thank you for the time I spent on this especially great reading !! I really liked each part and also bookmarked you for new information on your site.
    Data Science Training in Chennai

    ReplyDelete
  54. I am another client of this site so here I saw different articles and posts posted by this site,I inquisitive more enthusiasm for some of them trust you will give more data on this points in your next articles. data scientist certification

    ReplyDelete
  55. Nice & Informative Blog !
    QuickBooks Error PS032 is a highly disruptive error that can happen when you are downloading the updates of QuickBooks.Our experts are diverse and talented in fixing all the malicious errors of QuickBooks.

    ReplyDelete
  56. I just got to this amazing site not long ago. I was actually captured with the piece of resources you have got here. Big thumbs up for making such wonderful blog page!
    data analytics course in bangalore

    ReplyDelete
  57. I am really enjoying reading your well written articles. It looks like you spend a lot of effort and time on your blog. I have bookmarked it and I am looking forward to reading new articles. Keep up the good work.
    artificial intelligence course in pune

    ReplyDelete
  58. I just got to this amazing site not long ago. I was actually captured with the piece of resources you have got here. Big thumbs up for making such wonderful blog page!
    data analytics course in bangalore

    ReplyDelete
  59. I am really enjoying reading your well written articles. It looks like you spend a lot of effort and time on your blog. I have bookmarked it and I am looking forward to reading new articles. Keep up the good work.
    artificial intelligence course in pune

    ReplyDelete
  60. Nice Blog !
    Our team at QuickBooks Customer Service is customer-focused and offers quick services in this exceptional crisis.

    ReplyDelete
  61. I am a new user of this site, so here I saw several articles and posts published on this site, I am more interested in some of them, hope you will provide more information on these topics in your next articles.
    data analytics training in bangalore

    ReplyDelete
  62. I am a new user of this site, so here I saw several articles and posts published on this site, I am more interested in some of them, hope you will provide more information on these topics in your next articles.
    data analytics training in bangalore

    ReplyDelete
  63. Good Work, Nice blog with helpful information, if you face any kind of trouble using QuickBooks, contact immediately:QuickBooks Support phone number and get resolved with your issue.

    ReplyDelete
  64. Our legal team will assist with immigration issues and residence permits. We will also help you with the process of purchasing real estate and resolving any tax issues that arrise from your decision to live or do business in Europe. http://www.immigration-residency.eu/

    ReplyDelete
  65. Learn Digital Marketing with Best Trainers in Digital Brolly with Live Project.

    ReplyDelete

  66. I truly like your composing style, incredible data, thankyou for posting.

    business analytics course

    ReplyDelete
  67. Excellent Blog! I would like to thank for the efforts you have made in writing this post. I am hoping the same best work from you in the future as well. I wanted to thank you for this websites! Thanks for sharing. Great websites!
    Data Science Training in Bangalore

    ReplyDelete
  68. Nice Blog !
    QuickBooks Error 80070057 is an issue that appears on your screen without any prior invitation.Our technical team is highly skilled and offers you the best solutions so that you can quickly resolve the error.

    ReplyDelete
  69. I just got to this amazing site not long ago. I was actually captured with the piece of resources you have got here. Big thumbs up for making such wonderful blog page!
    artificial intelligence course in pune

    ReplyDelete
  70. Thanks for posting the best information and the blog is very helpful.Data science course in Varanasi

    ReplyDelete
  71. This content is really unique and amazing. This article really helpful and explained very well.

    Learn MIS Training Core to Advanced Level

    ReplyDelete
  72. Excellent Blog! I would like to thank for the efforts you have made in writing this post. I am hoping the same best work from you in the future as well.

    Get Learn AutoCAD Training with 20% Off

    ReplyDelete
  73. Excellent post. I was always checking this blog, and I’m impressed! Extremely useful info specially the last part, I care for such information a lot about Addon domain.

    Best Training Institute for SQL Server Training

    ReplyDelete
  74. I just got to this amazing site not long ago. I was actually captured with the piece of resources you have got here. Big thumbs up for making such wonderful blog page!
    artificial intelligence course in pune

    ReplyDelete
  75. This concept is a good way to enhance the knowledge. thanks for sharing. please keep it up.

    Oracle APPS DBA Training in Bangalore

    Oracle APPS DBA institutes in Bangalore

    ReplyDelete
  76. A really appreciable content you put in your blog and detailed information you provide helps me to enhance my knowledge and skills. Further More Information About Advance Excel Training Institute in Delhi So Contact Here-+91-9311002620 Or Visit Website- https://htsindia.com/Courses/business-analytics/adv-excel-training-course.

    ReplyDelete
  77. Thanks for posting these kinds of post its very helpful and very good content a really appreciable post apart from that if anyone looking for best Core and Advanced Java training institute in delhi so contact here +91-9311002620 visit https://www.htsindia.com/java-training-courses

    ReplyDelete
  78. I went over this website and I believe you have a lot of wonderful information.Thanks for sharing
    How to Fix the Common Verizon Email Problem .

    ReplyDelete
  79. Impressive. Your story always brings hope and new energy. Keep up the good work.
    Best Data Science courses in Hyderabad

    ReplyDelete
  80. Hey! What a wonderful blog. I loved your blog. QuickBooks is the best accounting software, however, it has lots of bugs like QuickBooks Error. To fix such issues, you can contact experts via QuickBooks Customer Support Number

    ReplyDelete