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!

52 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