Sort Trong Excel Bị Lỗi / TOP #10 Xem Nhiều Nhất & Mới Nhất 8/2022 # Top View | Hoisinhvienqnam.edu.vn

## 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.

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
• ## Cách Sửa Lỗi Font Chữ Trong Excel, File Xls Bị Lỗi Phông

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

• Hàm Countif Và Countifs Trong Excel, Có Bài Tập Mẫu
• Cách Dùng Hàm Countifs Trong Excel
• Hướng Dẫn Cách Thêm Các Tiện Ích Vào Biểu Đồ Microsoft Excel (Phần 2)
• Thêm Chuỗi Dữ Liệu Vào Biểu Đồ
• Cách Chèn Nhanh Hàng (Hoặc Cột) Vào Bảng Tính Excel
• Trong bài viết sau đây, chúng tôi sẽ hướng dẫn bạn cách sửa lỗi font chữ trong Excel – một trong những lỗi phổ biến khi bạn sử dụng Excel. Với các bước khắc phục file xls bị lỗi phông này, bạn sẽ sử dụng Excel dễ dàng, hiệu quả hơn.

Trong quá trình làm việc, có rất nhiều bạn sử dụng Excel thường xuyên gặp phải lỗi font mà không biết cách sửa lỗi font thế nào. Nguyên nhân chủ yếu của việc lỗi font đó là khi bạn nhận file ở máy tính khác và máy tính đó không sử dụng cùng một bộ font chữ giống máy của bạn nên xảy ra hiện tượng lỗi font chữ. Để sửa lỗi này, Taimienphi mời bạn đọc tham khảo cách sửa lỗi font chữ trong Excel mà chúng tôi trình bầy ngay sau đây.

Hướng dẫn sửa lỗi font chữ trong Excel, file xls bị lỗi phông

Cách sửa lỗi font chữ trong Excel.

Trước tiên để sửa lỗi font chữ trong Excel các bạn cần phải tải về máy tính đầy đủ bộ font chữ và cập nhật phiên bản Unikey mới nhất.

Bước 1: Mở file Excel 2003, 2007, 2010 hay Excel 2022 cần khắc phục font chữ trong excel bị lỗi, chọn vùng cần sửa lỗi font sau đó copy chúng ( Ctrl + C ).

Bước 2: Phải chuột vào biểu tượng Unikey ở dưới thanh công cụ, chọn “Công cụ” hoặc bạn có thể nhấn tổ hợp phím Ctrl + Shift + F6 để mở Unikey Toolkit.

Bước 3: Tại đây, các bạn cần xác định đúng “font chữ nguồn” và “font chữ đích” để sửa lỗi, bạn cũng có thể dùng Đảo bảng mã để thay đổi, sau đó nhấn Chuyển mã.

Thông thường nếu bạn chuyển từ font chữ .VNTime sang Time New Roman thì cần chuyển từ nguồn TCVN 3(ABC) sang đích Unicode và ngược lại.

Cuối cùng, bạn mở một file Excel mới lên và Paste ( Ctrl + V). Kiểm tra kết quả, nếu chưa đạt bạn có thể Đảo bảng mã lại lần nữa để thực hiện lại.

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

• Cách Ẩn Và Khóa Công Thức Trong Excel 2010, 2013, 2022
• Cách Sử Dụng Hàm Product Trong Excel
• Các Hàm Cắt Chuỗi, Nối Chuỗi Trong Excel
• Các Hàm Cơ Bản Trong Excel Thường Sử Dụng Nhất
• Cách Sử Dụng Hàm Hyperlink Để Tạo Nhiều Liên Kết Khác Nhau
• ## 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…

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.

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

Create Mailing List

and send emails…

• Super Formula Bar

(easily edit multiple lines of text and formula);

(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);

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.

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.

--- 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.

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
• ## 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.

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.

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

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

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

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