How To Sort In Excel

--- Bài mới hơn ---

  • How To Use The Excel Sort Function
  • How To Alphabetize In Excel: Sort Alphabetically Columns And Rows
  • How To Filter And Sort Cells By Color In Excel 2022, 2013 And 2010
  • How To Sort Data By Color In Excel?
  • 3 Ways To Sort By Color In Excel
  • You can sort your Excel data on one column or multiple columns. You can sort in ascending or descending order.

    One Column

    To sort on one column, execute the following steps.

    Result:

    Multiple Columns

    To sort on multiple columns, execute the following steps.

    The Sort dialog box appears.

    2. Select Last Name from the ‘Sort by’ drop-down list.

    4. Select Sales from the ‘Then by’ drop-down list.

    Result. Records are sorted by Last Name first and Sales second.

    --- Bài cũ hơn ---

  • How To Use The Excel Subtotal Function
  • Hàm Sum Trong Excel – Cách Sử Dụng Hàm Tính Tổng Qua Ví Dụ
  • Bỏ Túi Cách Sắp Xếp Tên Theo Thứ Tự Abc Trong Excel Hay Nhất Bạn Nên Biết!
  • Sử Dụng Hàm Max() Và If() Để Đánh Số Thứ Tự Tự Động Trong Excel
  • Hướng Dẫn Cách Đảo Ngược Danh Sách Dữ Liệu Trong 1 Cột / Hàng Trong Excel 2007 2010 2022
  • Revert To Original Sort Order In Excel

    --- Bài mới hơn ---

  • Cách Sử Dụng Hàm Sắp Xếp Trong Excel
  • Hướng Dẫn Sử Dụng Cơ Bản Chức Năng Sort Trong Bài Thi Mos
  • Hướng Dẫn Cách Sắp Xếp Dữ Liệu Trong Excel Theo Màu Chi Tiết
  • Cách Sắp Xếp Dữ Liệu Theo Hàng Ngang Trong Google Sheets
  • Hướng Dẫn Cách Tính Điểm Trung Bình Trong Excel Dễ Hiểu Nhất
  • Bottom line: Learn how to retain and revert to the original sort order of a range or table in Excel.

    Skill level: Beginner

    Video Tutorial

    Often times we receive a data set that does not have a column of sequential numbers or dates. If we sort one or more columns in the range, it can be difficult or impossible to revert back to the original sort order.

    One way to solve this problem is to add a column of sequential numbers to the data set. I typically refer to this as an index column.

    After sorting the data you can then go back and sort the index column to restore the original sort order.

    Setting up the Index Column

    It’s important to setup the index column BEFORE you apply any sorting to the data.

    Here are the steps to create the index column:

    1. Type a 1 in a blank column to the right of the data range/table.
    2. Select Series from the Auto Fill Options menu to create a sequential list of numbers 1,2,3,…
    3. IMPORTANT: If you are NOT using an Excel Table then you will need to reapply the filters to include this new column. This must be done BEFORE you sort the other columns so that the index column is included in the sort.

    If you are using an Excel Table then you do not have to worry about step 4. The new column will automatically be included in the table as long as you create it directly to the right of the last column. See the video above for more details.

    Checkout my video on a Beginner’s Guide to Excel Tables to learn more about the benefits of using this awesome feature of Excel.

    Here is another method for using the fill handle.

    Checkout my article on Fill Handle Hacks to learn more about this technique.

    Other Techniques for Retaining Sort Order?

    My friend Robbie asked me this question about retaining sort order the other day, which sparked the idea for this post, and this index column method is the one I’ve always used.

    I’ve also mentioned it before in my article on how to pvent Excel from freezing when deleting rows.

    However, I’m curious to know if you have other techniques or methods to solve this problem. Is there a different or better way?

    --- Bài cũ hơn ---

  • How Do You Sort By Color In Microsoft Excel?
  • How To Sort By Color In Excel (In Less Than 10 Seconds)
  • How To Sort By Color In Excel * Productivity Portfolio
  • Sự Khác Nhau Giữa Hàm Sumif Và Sumifs
  • Cách Dùng Hàm Sumif Và Sumifs Qua Ví Dụ Cụ Thể 2022
  • How To Sort Data By Color In Excel?

    --- Bài mới hơn ---

  • 3 Ways To Sort By Color In Excel
  • How To Allow Sorting And Filter Locked Cells In Protected Sheets?
  • Hướng Dẫn Sử Hàm Chức Năng Atan Trong Excel.
  • Calculating Coefficient Of Variation In Excel
  • Hướng Dẫn Sử Dụng Hàm Ln Trong Excel
  • How to sort data by color in excel?

    When you using a worksheet, sometimes you may fill the rows or cells with various colors to make the worksheet much readable. And sometimes you want to sort the cells by color in Excel. In this case, you can use the sort function to sort the data by color quickly as follows:

    Office Tab Enable Tabbed Editing and Browsing in Office, and Make Your Work Much Easier…

    Read More… Free Download…

    Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by

    80%

    • Reuse Anything:

      Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.

    • More than 20 text features:

      Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.

    • Merge Tools

      : Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.

    • Split Tools

      : Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.

    • Paste Skipping

      Hidden/Filtered Rows; Count And Sum

      by Background Color

      ; Send Personalized Emails to Multiple Recipients in Bulk.

    • More than

      300 powerful features;

      Works with Office

      2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.

    Read More… Free Download…

    1. Select the range that you want to sort the data by color.

    The Best Office Productivity Tools

    Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by

    80%

    • Reuse:

      Quickly insert

      complex formulas, charts

       and anything that you have used before;

      Encrypt Cells

      with password;

      Create Mailing List

      and send emails…

    • Super Formula Bar

      (easily edit multiple lines of text and formula);

      Reading Layout

      (easily read and edit large numbers of cells);

      Paste to Filtered Range

    • Merge Cells/Rows/Columns

      without losing Data; Split Cells Content;

      Combine Duplicate Rows/Columns

      … Prevent Duplicate Cells;

      Compare Ranges

    • Select Duplicate or Unique

      Rows;

      Select Blank Rows

      (all cells are empty);

      Super Find and Fuzzy Find

      in Many Workbooks; Random Select…

    • Exact Copy

      Multiple Cells without changing formula reference;

      Auto Create References

      to Multiple Sheets;

      Insert Bullets

      , Check Boxes and more…

    • Extract Text

      , Add Text, Remove by Position,

      Remove Space

      ; Create and Print Paging Subtotals;

      Convert Between Cells Content and Comments

    • Super Filter

      (save and apply filter schemes to other sheets);

      Advanced Sort

      by month/week/day, frequency and more;

      Special Filter

      by bold, italic…

    • Combine Workbooks and WorkSheets

      ; Merge Tables based on key columns;

      Split Data into Multiple Sheets

      ;

      Batch Convert xls, xlsx and PDF

    • More than

      300 powerful features

      . Supports Office/Excel

      2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features

      30-day free trial. 60-day money back guarantee.

    Read More… Free Download… Purchase… 

    Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

    • Enable tabbed editing and reading in Word, Excel, PowerPoint

      , Publisher, Access, Visio and Project.

    • Open and create multiple documents in new tabs of the same window, rather than in new windows.
    • Increases your productivity by

    Read More… Free Download… Purchase… 

     

    --- Bài cũ hơn ---

  • How To Filter And Sort Cells By Color In Excel 2022, 2013 And 2010
  • How To Alphabetize In Excel: Sort Alphabetically Columns And Rows
  • How To Use The Excel Sort Function
  • How To Sort In Excel
  • How To Use The Excel Subtotal Function
  • How To Sort By Color In Excel * Productivity Portfolio

    --- Bài mới hơn ---

  • How To Sort By Color In Excel (In Less Than 10 Seconds)
  • How Do You Sort By Color In Microsoft Excel?
  • Revert To Original Sort Order In Excel
  • Cách Sử Dụng Hàm Sắp Xếp Trong Excel
  • Hướng Dẫn Sử Dụng Cơ Bản Chức Năng Sort Trong Bài Thi Mos
  • Why Use Color

    Perhaps, at one-time, color sorting and filtering wasn’t available, but you can do it now. The tutorial below can be used for font colors or cell color. For these instructions, I’ll be using Microsoft Office 365. However, the steps can be used in older Excel versions.

    It’s probably my laziness, but I find it useful to change a cell’s background color. I might use this when I’m prioritizing or grouping a list. I need to highlight a cell and choose a background color.

    In my screen snap above, I’ve applied the cell background color to Column C. However, some people might pfer using color across a row. In the second screenshot below, I used conditional formatting. Regardless of whether you’re using columns or rows, the process is the same. It also works with Excel tables.

    How to Sort by Color in Excel

    1. Tick the My data has headers checkbox in the top-right if your worksheet uses them.
    1. Select your first fill color and keep the Order value as On Top.
    2. Add in your other color levels. If you pfer, you can also use the On Bottom option.

    You should now see your sorted list by color. You’re not limited to just 3 colors, but you can keep adding if they’re on your sheet. You also don’t need to enter the last color as Excel will put it on the bottom. In my case, that was ” No Cell Color.”

    1. Select the column you wish to filter that has color.
      Select your cell color from the side menu.

    The same process works if you wish to sort by font color.

    How to Filter by Color in Excel

    • Excel allows you to sort and filter by color
    • The color can be either the cell background color or font color
    • You can place colors on top or bottom
    • The system knows which colors you’ve used
    • No cell color is for normal cells
    • You can add multiple color sorts
    • Sort rules are processed in order

    Some people pfer to filter instead of sort. This is great if you want to focus in on certain items. Even better, the procedure is shorter.

    In my example, I was using a background color. However, when I added colored text, Excel adjusted the options. On the side menu for Filter by Color, you would also see Filter by Font Color.

    Quick Takeaways

    Disclaimer: Images from Amazon Product Advertising API. I may receive an affiliate commission on these products if you buy. Updated: 2022-04-17

    --- Bài cũ hơn ---

  • Sự Khác Nhau Giữa Hàm Sumif Và Sumifs
  • Cách Dùng Hàm Sumif Và Sumifs Qua Ví Dụ Cụ Thể 2022
  • Hướng Dẫn Cách Sử Dụng Hàm Sumif Trong Google Sheets Thông Qua Ví Dụ
  • Một Số Hàm Quan Trọng Trong Kế Toán Bằng Excel
  • Hướng Dẫn Cách Dùng Hàm Sumifs Đơn Giản Qua Bài Tập
  • Sắp Xếp Công Thức Trong Excel Sử Dụng Các Hàm Sort Và Sortby

    --- Bài mới hơn ---

  • Cách Sắp Xếp Theo Ngày Trong Excel Online
  • Tiện Ích Sắp Xếp Tiếng Việt Trong Excel
  • Code Vba Công Cụ Xóa
  • Hướng Dẫn Cách Dùng Hàm Tính Tổng Sum Trong Excel Hiệu Quả Khi Xử Lý Số Liệu
  • Hướng Dẫn Sử Dụng Hàm Sumif Trong Excel
  • Tìm hiểu cách sử dụng hàm SORT và SORTBY để trả về phạm vi được sắp xếp dựa trên một hoặc nhiều tiêu chuẩn.

    File sử dụng hàm Dynamic Array mới chỉ duy nhất có trên phiên bản mới nhất của Office 365. Bao gồm cả desktop và web app phiên bản Excel.

    Trong bài viết này chúng ta hãy nhìn vào 2 hàm Dynamic Array, SORT SORTBY. Hai hàm này thích hợp trong việc hiển thị danh sách được xếp loại theo thứ tư hiệu suất hoặc thứ tự thời gian (ngày, giờ).

    Trong trường hợp ví dụ của chúng ta, chúng ta sẽ sắp xếp nhân viên dựa theo thời gian họ dành thời gian tại văn phòng (thời lượng) trong tuần. Hàm sẽ xếp loại nhân viên những ai dành nhiều thời gian nhất và ai dành ít thời gian nhất tai văn phòng.

    Tuy nhiên, trước khi sắp xếp, tôi sẽ giới thiệu nhanh cho bạn biết làm cách nào để thêm tất cả thời lượng với nhau với mỗi nhân viên bằng cách sử dụng hàm SUMIF.

    Đối số thứ hai là tiêu chí mà hàm sẽ tìm. Đối với đối số này, chúng ta có thể xác định phạm vi tràn chúng ta đã tạo chỉ liệt kê các nhân viên đối với phòng ban chúng ta đã xác định. Trong ví dụ, tham chiếu phạm vi tràn sẽ là A2#.

    Cuối cùng, đối số phạm vi tổng hợp của chúng ta là cột Thời lượng. Đối số này báo Excel thêm tất cả thời lượng cho nhân viên mà chúng ta vừa xác định. Công thức cuối cùng sẽ như sau:

    Bây giờ chúng ta vừa tổng hợp tất cả thời gian của mỗi nhân viên, chúng ta có thể sắp xếp nhân viên bằng số giờ mà họ đã làm việc.

    Để sử dụng hàm SORTBY, chúng ta chỉ cần xác định dữ liệu cho 3 đối số. Đối số thứ nhất là Array, đây là set giá trị mà chúng ta muốn hàm trả về. Trong trường hợp của chúng ta đó là danh sách nhân viên, được xác định bời phạm vi tràn A2#.

    Các metric khác trên sheet thường đơn giản và được liệt kê bên dưới.

    Nguồn: Excel Campus

    --- Bài cũ hơn ---

  • Cách Sắp Xếp Họ Và Tên Trong Excel Theo Thứ Tự Chữ Cái
  • Cách Sắp Xếp Dữ Liệu Trong Excel
  • Cách Hoán Đổi Vị Trí Các Cột Trong Excel
  • Cách Sắp Xếp Các Cột Excel Theo Màu Sắc
  • Cách Sắp Xếp Các Cột Excel Của Bạn Theo Màu Sắc
  • Sorting And Filtering Data With Excel

    --- Bài mới hơn ---

  • Làm Cách Nào Để Lọc Mọi Hàng Khác (Hàng Chẵn
  • How To Sort And Filter Data In Excel
  • How To Freeze Panes In Excel
  • How To Freeze A Row In Excel So It Always Stays On The Screen, Even When You Scroll Away
  • 8 Cách Làm Tròn Số Trong Excel Với Hàm Round, Roundup
  • As you can see, the order dates, order numbers, prices, etc. are all out of order. Let’s get started on running some sorting and filtering techniques.

    Sorting Data

    • Go down to the Sort option – when hovering over Sort the sub-menu will appear
    • Select Expand the selection

    The whole table has now adjusted for the sorted column. Note: when the data in one column is related to the data in the remaining columns of the table, you want to select Expand the selection. This will ensure the data in that row carries over with sorted column data.

    Filtering Data

    The filter feature applies a drop down menu to each column heading, allowing you to select specific choices to narrow a table. Using the above example, let’s say you wanted to filter your table by Company and Salesperson. Specifically, you want to find the number of sales Dylan Rogers made to Eastern Company.

    To do this using the filter you would:

    • Go to the Data tab on Excel ribbon
    • Select the Filter tool
    • Select Eastern Company from the dropdown menu
    • Select Dylan Rogers from the Salesperson dropdown menu

    Boom – you now have the exact number of sales Dylan Rogers made to Eastern Company.

    The Sort & Filter Tool

    In the following GIF, we can see how the Custom Sorting tool can be used to sort date ranges or price ranges.

    But notice how this example is either/or. What if you wanted to sort by date and by price? This where the Custom Sort option really comes in handy. After selecting your first sorting conditions, you can add a level to get event more accurate data:

    As you can see, Excel offers a variety of sorting and filtering tools to help you refine your data and keep it organized. We hope you found today’s tips useful. Now go out there and get your data sorted!

    Use Learn Excel Now to help with all your Excel questions and training needs.  We’re not just experts in Excel, there is content, free resources, and training courses available for Word, Outlook and more.

     

    --- Bài cũ hơn ---

  • Cách Sử Dụng Excel 2022 – Tính Năng Sort Và Filter
  • Excel Autofilter Shows Filter Mode – Contextures Blog
  • Chọn Lọc Ô Cell Theo Màu Sắc Trong Excel
  • Excel Filter For Date Range 2 Ways – Contextures Blog
  • Hướng Dẫn Khóa Một Vùng Dữ Liệu Trong Excel Bằng Mật Khẩu
  • How To Use The Excel Sort Function

    --- Bài mới hơn ---

  • How To Alphabetize In Excel: Sort Alphabetically Columns And Rows
  • How To Filter And Sort Cells By Color In Excel 2022, 2013 And 2010
  • How To Sort Data By Color In Excel?
  • 3 Ways To Sort By Color In Excel
  • How To Allow Sorting And Filter Locked Cells In Protected Sheets?
  • The SORT function sorts the contents of a range or array in ascending or descending order with a formula. The result from SORT is a dynamic array of values that will “spill” onto the worksheet into a range. If values in the source data change, the result from SORT will update automatically. 

    The SORT function takes four arguments: array, sort_index, sort_order, and by_col. The first argument, array, is the range or array to be sorted. This is the only required argument. By default, the SORT function will sort values in ascending order using the first column in array. Use the optional arguments sort_index and sort_order to control which column to sort by, and the order to sort by (ascending or descending). Sort_index should be a number corresponding to the column (or row) used for sorting. For example, to sort by the third column in a range of data, use 3 for sort_index. The optional sort_order argument determines sort direction. Use 1 for ascending order and -1 for descending order. By default, the SORT function will sort data vertically by rows. To sort a range horizontally by columns, set the fourth argument, by_col, to TRUE.

    Basic Examples

    To sort a range by the first column in ascending order:

    =

    SORT

    (

    range

    )

    // sort by column 1, ascending

    =

    SORT

    (

    range

    ,

    1

    ,

    1

    )

    // sort by column 1, ascending

    To sort a range by the first column in descending order:

    =

    SORT

    (

    range

    ,

    1

    ,

    -

    1

    )

    // sort by column 1, descending

    To sort a range by the second column in descending order:

    =

    SORT

    (

    range

    ,

    2

    ,

    -

    1

    )

    // sort by column 1, ascending

    To sort a range horizontally (by column) using values in row 1 in descending order, set the fourth argument, by_col, to TRUE or 1:

    =

    SORT

    (

    range

    ,

    1

    ,

    -

    1

    ,

    TRUE

    )

    =

    SORT

    (

    range

    ,

    1

    ,

    -

    1

    ,

    1

    )

    Example – sort by score

    In the example shown above, data includes names in column B and scores in column C. In cell E5, the SORT function is used to sort the data by score in descending order:

    =

    SORT

    (

    B5:C14

    ,

    2

    ,

    -

    1

    )

    // sort by scores in descending order

    The SORT function extracts all values, sorted in descending order by score, and results “spill” into the range E5:F14. To sort by score in ascending order, omit sort_order or to 1 like this:

    =

    SORT

    (

    B5:C14

    ,

    2

    )

    // sort by score in ascending order

    =

    SORT

    (

    B5:C14

    ,

    2

    ,

    1

    )

    // sort by score in ascending order

    Sort by more than one level

    Unlike the SORTBY function, the SORT function does not provide a way to sort data by more than one level. However, by using array constants for sort_index and sort_order, it can be done. This video explains how.

    SORT is a new function available in Excel 365 only.

    --- Bài cũ hơn ---

  • How To Sort In Excel
  • How To Use The Excel Subtotal Function
  • Hàm Sum Trong Excel – Cách Sử Dụng Hàm Tính Tổng Qua Ví Dụ
  • Bỏ Túi Cách Sắp Xếp Tên Theo Thứ Tự Abc Trong Excel Hay Nhất Bạn Nên Biết!
  • Sử Dụng Hàm Max() Và If() Để Đánh Số Thứ Tự Tự Động Trong Excel
  • How To Sort Lists In Excel 2003

    --- Bài mới hơn ---

  • Cách Xác Định Đơn Giá Theo Điều Kiện Trên Excel
  • Hướng Dẫn Các Cách Tính Tiền Điện Trong Excel 2022
  • Cách Ứng Dụng Hàm Int Và Mod Cho Lễ Tân Và Các Công Việc Khác
  • Hướng Dẫn Cách Cộng Trừ Nhân Chia Trong Excel
  • Cách Tách Họ Tên Trong Excel Dễ Dàng Và Đơn Giản Nhất
  • Use these tips to pvent problems when sorting in Excel. How to sort two or more columns, sort by row, sort in a Custom Order. For Excel 2007 and later, see the Sorting Data Basics page

    Using the Sort Buttons

    In Excel, it’s easy to sort your data by using the Sort buttons on the toolbar. But, be careful, or one column may be sorted, while others are not.

    1. Select one cell in the column you want to sort.

    Sort Data by One Column

    1. Select all the cells in the list.

      This is the safest approach to sorting. In most cases, you can select one cell and Excel will correctly detect the rest of the list — but it’s not 100% certain. Some of the data may be missed.

    2. From the Sort by dropdown, select the column you want to sort.Note: If the dropdown is showing Column letters instead of headings, change the setting forMy list has, fromNo header row to Header row.
    3. Select to sort in Ascending or Descending order

    Sort Data by 2-3 Columns

    1. Select all the cells in the list.
    2. From the Sort by dropdown, select the first column you want to sort.
    3. Select to sort in Ascending or Descending order
    4. From the Then by dropdown, select the second column you want to sort.
    5. Select to sort in Ascending or Descending order
    6. From the Then by dropdown, select the third column you want to sort.
    7. Select to sort in Ascending or Descending order

    Sort Data by 4+ Columns

    Occasionally, you may need to sort by more than three columns. For example, in a mailing list, you may want to sort by Country, Region, City, and Name. To do this, you can sort the list multiple times, starting with the least important sort.

    In the mailing list, there are four columns to sort. Name and City are the least important fields in the sorting process, so they can be sorted first.

    Excel will retain what it can of this sort while you sort by the remaining fields.

    1. From the Sort by dropdown, select Country.
    2. From the Then by dropdown, select Region.

    After sorting, the list is sorted by Country, then by Region, then by City and finally by Name.

    More Tutorials

    Sort Data With Macros Sorting Data Basics Sort a Row in Excel Numbers Don’t Sort Correctly

    --- Bài cũ hơn ---

  • Hướng Dẫn Cách Sử Dụng Công Thức Sumif Trong Excel Qua Những Ví Dụ Cụ Thể
  • Hàm Sumif Trong Kiểm Toán
  • Cách Dùng Hàm Sumif Để Tính Tổng Có Điều Kiện Trong Excel
  • Tải Các Tiện Ích Excel Add
  • Tổng Hợp Các Tiện Ích Excel Add
  • 3 Ways To Sort By Color In Excel

    --- Bài mới hơn ---

  • How To Allow Sorting And Filter Locked Cells In Protected Sheets?
  • Hướng Dẫn Sử Hàm Chức Năng Atan Trong Excel.
  • Calculating Coefficient Of Variation In Excel
  • Hướng Dẫn Sử Dụng Hàm Ln Trong Excel
  • Hocthue.net: Các Hàm Thống Kê Trong Excel Phải Biết Và Có Ví Dụ
  • There are several ways to sort data in Microsoft Excel. Learn how to use conditional sorting in Excel to sort by font color, cell background color, or icon color.

    Instructions in this article apply to Excel for Microsoft Office 365, Excel 2022, Excel 2022, and Excel 2013 for Windows and Mac.

    Select a Range to Be Sorted in Excel

    Before data can be sorted, Excel needs to know the exact range to sort. Excel can automatically include related data in a range so long as there are no blank rows or columns within the selected area. Blank rows and columns between areas of related data are okay. Excel then determines if the data area has field names and excludes those rows from the records to be sorted.

    Allowing Excel to select the range to be sorted is fine for small amounts of data. However, for large areas of data, the easiest way to ensure that the correct range is selected is to highlight it before sorting.

    If the same range is to be sorted repeatedly, the best approach is to give the range a name. If a name is defined for the range to be sorted, type the name in the Name Box, or select it from the associated drop-down list. This way, Excel automatically highlights the correct range of data in the worksheet.

    Any sorting requires the use of sort order. When sorting by values, there are two possible sort orders: ascending and descending. However, when sorting by colors, no such order exists, so you must manually define the color sort order.

    How to Sort by Cell Background Color in Excel

    In the example below, the records of students age 20 and younger are highlighted in red. To sort the data by cell background color so that the red entries appear on top:

    1. Highlight the range of cells to be sorted (cells A2 to D11 in the example).

    2. Select the Sort on drop-down arrow and choose ​Cell Color.

      Clear the My data has headers check box so that the first row doesn’t get cut off.

    3. Select the Order drop-down arrow and choose Red.

      When Excel finds different cell background colors in the selected data, it adds those colors to the Order drop-down list in the dialog box.

    4. Choose On Top from the drop-down list next to the sort order box so that the red cells will be at the top of the list, then select OK.

    5. The four records with red backgrounds are grouped together at the top of the data range.

      When working with calculations, you can make negative numbers in Excel appear red by default to help those numbers stand out more.

    How to Sort by Font Color in Excel

    In the example below, the records of students enrolled in nursing programs appear in red, and those enrolled in science programs are blue. To sort the data by font color:

    1. Highlight the range of cells to be sorted (cells A2 to D11 in the example).

    2. Select the Sort on drop-down arrow and choose ​Font Color.

      Clear the My data has headers check box so that the first row doesn’t get cut off.

    3. Select the Order drop-down arrow, then choose Red.

      When Excel finds different font colors in the selected data, it adds those colors to the Order drop-down list in the dialog box.

    4. Choose On Top from the drop-down list next to the sort order box so that the red entries will be at the top of the list.

    5. Select Add to add a second sort level.

    6. Use the same settings as the first sort level, but this time select the Order drop-down arrow and choose Blue.

    7. Select OK to sort the data and close the dialog box.

    8. The two records with the red font color are grouped together at the top of the data range, followed by the two blue records.

    How to Sort by Icon in Excel

    Icon sets offer an alternative to regular conditional formatting options that focus on the font and cell formatting changes. The example below contains dates and temperatures that have been conditionally formatted with the stoplight icon set based on the daily maximum temperatures.

    Follow these steps to sort the data so that records displaying the green icons are grouped first, followed by the yellow icons, and then the red icons:

    1. Highlight the range of cells to be sorted (cells A2 to B31 in the example).

    2. Select the Column drop-down arrow, then choose the column containing the conditional icons (Temperature in the example).

      Due to the way conditional formatting with icons works, you can leave the My data has headers check box selected.

    3. Select the Sort on drop-down arrow, then choose Conditional Formatting Icon.

    4. Select the Order drop-down arrow, then choose Green.

    5. Choose On Top from the drop-down list next to the sort order box so that the green icon entries will be at the top of the list.

    6. Select Add to add a second sort level.

    7. Use the same settings as the first sort level, but this time select the Order drop-down arrow and choose Yellow.

    8. Select Add to add a third sort level, then use the same settings as the first two levels, but this time select the Order drop-down arrow and choose Red.

    9. Select OK to sort the data and close the dialog box.

    10. The records with the green icon are grouped together at the top of the data range, followed by the records with the yellow icon, and then those with a red icon.

    --- Bài cũ hơn ---

  • How To Sort Data By Color In Excel?
  • How To Filter And Sort Cells By Color In Excel 2022, 2013 And 2010
  • How To Alphabetize In Excel: Sort Alphabetically Columns And Rows
  • How To Use The Excel Sort Function
  • How To Sort In Excel
  • How To Randomly Sort A List In Excel

    --- Bài mới hơn ---

  • How To Generate Random Value From A Given List In Excel?
  • Excel Formula: Random List Of Names
  • Hướng Dẫn Cách Chọn Tên Ngẫu Nhiên Trong Excel Đơn Giản Nhất
  • Cách Sắp Xếp Ngẫu Nhiên Nội Dung Của 1 Cột Trong Excel
  • Generate Random Numbers In Excel
  • Have you ever needed to take a list and randomly sort it? How would you do it?

    It’s not as intuitive as you think. We’re so used to sorting lists in

    alphabetical order or from smallest to largest, but this idea of randomly

    sorting a list is not very common in Excel. Let’s jump right into it and show

    you how to randomly sort a list in Excel.

    Sample Data

    Say we have some data that we’d like to sort:

    Normally, we could sort by Movie title, the date that it opened (as it’s shown

    in the picture), or by the total amount it grossed. But what if we wanted to

    “shuffle” this list? How could we go about doing that?

    By the way, this is the same data that we used in the

    Sum the Top 5 Values

    post.

    You can also copy and paste this table to follow along:

    Movie

    Date Opened

    Total Gross

    Deadpool

    2/12/16

    $363,070,709

    Zootopia

    3/4/16

    $341,268,248

    Batman v Superman: Dawn of Justice

    3/25/16

    $330,360,194

    The Jungle Book (2016)

    4/15/16

    $364,001,123

    Captain America: Civil War

    5/6/16

    $408,084,349

    Finding Dory

    6/17/16

    $486,295,561

    The Secret Life of Pets

    7/8/16

    $368,384,330

    Suicide Squad

    8/5/16

    $325,100,054

    Rogue One: A Star Wars Story

    12/16/16

    $532,177,324

    Sing

    12/21/16

    $270,329,045

    Does this article help you? If so, please consider supporting me with a coffee ☕️

    Randomly Sort a List in Excel

    To sort a list randomly in Excel, first you need to add a new column to your

    data. When using Excel Tables, you can

    simply type in a new column name at the next available table header and it will

    be automatically joined to your table.

    Here, we type in “Sort Order” and pss Enter.

    Next, we need a way to randomly sort the list. We can use the RAND() function

    in Excel to help us with that. The RAND() function will return a number at

    random between 0 and 1.

    After we add the formula, we can sort by that column. This will randomize the

    list for us.

    Also, each time the RAND() function is calculated, you get a different number.

    This is why you see the table has all different numbers.

    Keep Shuffling

    Another cool tip is that if you’re not happy with the initial shuffling of the

    list, you can simply keep sorting between Ascending and Descending to keep

    shuffling the list. This works because when you sort the list, it recalculates

    the RAND() function, giving a new number each time.

    Keeping the List Order

    But what if you don’t want the RAND() function to keep recalculating? What if

    you want to keep the sort order?

    If you want to keep the sort order, you can simply save the values from RAND()

    before sorting again.

    --- Bài cũ hơn ---

  • How To Randomize A List In Excel: Sort Randomly Cells, Rows And Columns
  • Những Phím Tắt Hay Dùng Trong Excel, Dân Văn Phòng Không Nên Bỏ Qua
  • 99 Phím Tắt Trong Excel
  • How To Use The Excel Roundup Function
  • Hàm Chia Lấy Số Dư Mod Và Chia Lấy Số Nguyên Quotient Trong Excel
  • Web hay
  • Links hay
  • Push
  • Chủ đề top 10
  • Chủ đề top 20
  • Chủ đề top 30
  • Chủ đề top 40
  • Chủ đề top 50
  • Chủ đề top 60
  • Chủ đề top 70
  • Chủ đề top 80
  • Chủ đề top 90
  • Chủ đề top 100
  • Bài viết top 10
  • Bài viết top 20
  • Bài viết top 30
  • Bài viết top 40
  • Bài viết top 50
  • Bài viết top 60
  • Bài viết top 70
  • Bài viết top 80
  • Bài viết top 90
  • Bài viết top 100