How to Sort in Excel: A Simple Guide to Organizing Data

When it comes to Excel, here's a good rule to live by: If you find yourself doing something manually, there's probably an easier way.

Whether you're trying to remove duplicates, do simple calculations, or sort your data, you can almost always find a workaround that'll help you get it done with just a click (or two) of a button.

But if you're not a power user, it's easy to overlook these shortcuts.And before you know it, something as simple as organizing a list of names in alphabetical order can suck up a ton of your time.

Luckily, there is a workaround for that. In fact, there are a few different ways to use Excel's sorting feature that you may not know about. Let's check them out below, starting with the basics.

For this first set of instructions, we'll be usingMicrosoft Excel 2017 for Mac. But don't worry -- while the location of certain buttons might be different, the icons and selections you have to make are the same across most earlier versions of Excel.

1. Highlight the rows and/or columns you want sorted.

To sort a range of cells in Excel, first click and drag your cursor across your spreadsheet to highlight all of the cells you want to sort -- even those rows and columns whose values you're not actually sorting by.

For example, if you want to sort column A, but there's data associated with column A in columns B and C, it's important to highlight all three columns to ensure the values in Columns B and C move along with the cells you're sorting in Column A.

In the screenshot below, we're going to sort this sheet by thelast nameof Harry Potter characters. But thefirst name和houseof each person needs to go with each last name that gets sorted, or each column will become mismatched when we finish sorting.

2. Navigate to 'Data' along the top and select 'Sort.'

一旦突出显示了要排序的所有数据，请沿顶部导航栏选择“数据”选项卡（您可以在上面的第一步中在屏幕截图的顶部看到此按钮）。此选项卡将在其下方扩展一组新的选项，您可以在其中选择“排序”按钮。如下所示，该图标上有一个“ A-Z”图形，但是您不仅可以按字母顺序排列。

3. If sorting by column, select the column you want to order your sheet by.

当您点击上面显示的“排序”按钮时，将出现设置窗口。在这里，您可以配置想要分类的内容以及想要如何对其进行排序。

If you're sorting by a specific column, click "Column" -- the leftmost dropdown menu, shown below -- and select the column whose values you want to be your sorting criteria. In our case, it'll be "Last Name."

4. If sorting by row, click 'Options' and select 'Sort left to right.'

如果您想按特定行进行排序，而不是列，请单击窗口底部的“选项”，然后选择“从左到右排序”。一旦执行此操作，排序设置窗口将重置，并要求您选择要在最左边的下拉列表中进行排序的特定“行”（当前说“列”）。

This sorting system doesn't quite make sense for our example, so we'll stick with sorting by the "Last Name" column.

5. Choose what you'd like sorted.

你don't just have to sort by the value of each cell. In the middle column of your Sort settings window, you'll see a dropdown menu called "Sort On." Click it, and you can choose to sort your sheet by different characteristics of each cell in the column/row you're sorting by. These options include cell color, font color, or any icon included in the cell.

6. Choose how you'd like to order your sheet.

In the third section of your Sort settings' window, you'll see a dropdown bar called "Order." Click it to select how you'd like to order your spreadsheet.

默认情况下，您的排序设置窗口将建议按字母顺序排序（我们将在下一个过程中向您展示快捷方式）。但是，您也可以从z到a以及通过自定义列表进行排序。虽然您可以创建自己的自定义列表，但是有一些预设列表可以立即通过数据对数据进行排序。我们将在几分钟之内通过自定义列表进行详细讨论如何以及为什么您可以按自定义列表进行排序。

To Sort by Number

If your spreadsheet includes a column of numbers, rather than letter-based values, you can also sort your sheet by these numbers. To do that, you'll select this column in the leftmost "Columns" dropdown menu. This will change the options in the "Order" dropdown bar so that you can sort from "Smallest to Largest" or "Largest to Smallest."

7. Click 'OK.'

Click "OK," in your Sort settings window, and you should see your list successfully sorted according to your desired criteria. Here's what our Harry Potter list now looks like, organized by last name in alphabetical order:

Sometimes you may have a list of data that has no organization whatsoever. Maybe you exported a list of your marketing contacts or blog posts. Whatever the case may be, you might want to start by alphabetizing the list -- and there's an easy way to do this that doesn't require you to follow each step outlined above.

To Alphabetize on a Mac

1. 选择一个单元格的列排序。
2. Click on the "Data" tab in your toolbar and look for the "Sort" option on the left.
3. 如果“ a”位于“ Z”的顶部，则只需单击该按钮一次即可。如果“ z”在“ a”的顶部，请单击两次按钮。注意：当“ a”位于“ z”之上时，这意味着您的列表将按字母顺序排序。但是，当“ z”位于“ a”之上时，这意味着您的列表将按反向字母顺序排序。

To Alphabetize on a PC

1. 选择一个单元格的列排序。
2. Click on the "Data" tab in your toolbar. You will see Sort options in the middle.
3. 点击图标在我之上rd "Sort." A pop-up will appear: If you have headers, make sure "My list has headers" is checked. If it is, click "Cancel."
4. 点击按钮,上面有“A”和the "Z" on the bottom with an arrow pointing down. That will sort your list alphabetically from "A" to "Z." If you want to sort your list in reverse alphabetical order, click on the button that has the "Z" on top and the "A" on the bottom.

Sorting Multiple Columns

Sometimes you don't just want to sort one column, but you want to sort two. Let's say you want to organize all of your blog posts that you have in a list by the month they were published. First, you'd want to organize them by date, and then by the blog post title or URL.

In this example, I want to sort my list first by house, and then by last name. This would give me a list organized by each house, but also alphabetized within each house.

To Sort Multiple Columns on a Mac

1. Click on the data in the column you want to sort.
2. Click on the "Data" tab in your toolbar and look for the "Sort" option on the left.
3. 单击“ A到Z”等图标左侧的小箭头。然后，从菜单中选择“自定义排序”。
4. A pop-up will appear: If you have headers, make sure "My list has headers" is checked.
5. 你will see five columns. Under "Column" select the first column you want to sort from the dropdown menu. (In this case, it is "House.")
6. Then, click on the "+" sign at the bottom left of the pop-up. Under where it says "Column," select "Last Name" from the dropdown.
7. Check the "Order" column to make sure it says A to Z. Then click "OK."
8. Marvel at your beautiful organized list.

在PC上排序多个列

1. Click on the data in the column you want to sort.
2. Click on the "Data" tab in your toolbar. You will see "Sort" options in the middle.
3. 点击图标在我之上rd "Sort." You will see a pop-up appear. Make sure "My data has headers" is checked if you have column headers.
4. 你will see three columns. Under "Column" select the first column you want to sort from the dropdown menu. (In this case, it is "House.")
5. Then click on "Add Level" at the top left of the pop-up. Under where it says "Column" select "Last Name" from the dropdown.
6. Check the "Order" column to make sure it says A to Z. Then click "OK."
7. Marvel at your beautiful organized list.

Sorting in Custom Order

Sometimes you don't want to sort by A to Z or Z to A. Sometimes you want to sort by something else, such as months, days of the week, or some other organizational system.

In situations like this, you can create your own custom order to specify exactly the order you want the sort. (It follows a similar path to multiple columns but is slightly different.)

Let's say we have everyone's birthday month at Hogwarts, and we want everyone to be sorted first by Birthday Month, then by House, and then by Last Name.

To Sort in Custom Order on a Mac

1. Click on the data in the column you want to sort.
2. Click on the "Data" tab in your toolbar. You will see "Sort" all the way to the left.
3. 单击“ A到Z”等图标左侧的小箭头。然后，从菜单中选择“自定义排序”。
4. A pop-up will appear: If you have headers, make sure "My list has headers" is checked.
5. 你will see five columns. Under "Column," select the first column in your spreadsheet you want to sort from the dropdown menu. In this case, it is "Birthday Month."
6. Under the "Order" column, click on the dropdown next to "A to Z." Select the option for "Custom List."
7. 你will see a couple of options (month and day). Select the month list where the months are spelled out, as that matches the data. Click "OK."
8. Then click on the "+" sign at the bottom left of the pop-up. Under "Column," select "House" from the dropdown.
9. Click on the "+" sign at the bottom left again. Under "Column," select "Last Name" from the dropdown.
10. 检查“订单”列以确保“ house”和“姓氏”对z说。然后单击“确定”。
11. Marvel at your beautiful organized list.

To Sort in Custom Order on a PC

1. Click on the data in the column you want to sort.
2. Click on the "Data" tab in your toolbar. You will see "Sort" options in the middle.
3. 点击图标在我之上rd "Sort." You will see a pop-up appear: If you have headers, make sure "My list has headers" is checked.
4. 你will see three columns. Under "Column," select the first column you want to sort from the dropdown. In this case, it is "Birthday Month."
5. Under the "Order" column, click on the dropdown next to "A to Z." Select the option for "Custom List."
6. 你will see a couple of options (month and day), as well as the option to create your own custom order. Select the month list where the months are spelled out, as that matches the data. Click "OK."
7. 然后，单击弹出窗口左上方的“添加级别”。在“列”下，从下拉列表中选择“房屋”。
8. Click on the "Add Level" button at the top left of the pop-up again. Under "Column," select "Last Name" from the dropdown.
9. 检查“订单”列以确保“ house”和“姓氏”对z说。然后单击“确定”。
10. Marvel at your beautiful organized list.

Sorting a Row

Sometimes your data may appear in rows instead of columns. When that happens you are still able to sort your data with a slightly different step.

To Sort a Row on a Mac

1. Click on the data in the row you want to sort.
2. Click on the "Data" tab in your toolbar. You will see "Sort" all the way to the left.
3. 单击“ A到Z”等图标左侧的小箭头。然后，从菜单中选择“自定义排序”。
4. A pop-up will appear: Click on "Options" at the bottom.
5. Under "Orientation" select "Sort left to right." Then, click "OK."
6. 你will see five columns. Under "Row," select the row number that you want to sort from the dropdown. (In this case, it is Row 1.) When you are done, click "OK."

To Sort a Row on a PC

1. Click on the data in the row you want to sort.
2. Click on the "Data" tab in your toolbar. You will see "Sort" options in the middle.
3. 点击图标在我之上rd "Sort." You will see a pop-up appear.
4. Click on "Options" at the bottom.
5. Under "Orientation" select "Sort left to right." Then, click "OK."
6. 你will see three columns. Under "Row," select the row number that you want to sort from the dropdown. (In this case, it is Row 1.) When you are done, click "OK."

Sort Your Conditional Formatting

If you use conditional formatting to change the color of a cell, add an icon, or change the color of a font, you can actually sort by that, too.

In the example below, I've used colors to signify different grade ranges: If they have a 90 or above, the cell appears green. Between 80-90 is yellow. Below 80 is red. Here's how you'd sort that information to put the top performers at the top of the list. I want to sort this information so that the top performers are at the top of the list.

To Sort Conditional Formatting on a Mac

1. Click on the data in the row you want to sort.
2. Click on the "Data" tab in your toolbar. You will see "Sort" all the way to the left.
3. 单击“ A到Z”等图标左侧的小箭头。然后，从菜单中选择“自定义排序”。
4. A pop-up will appear: If you have headers, make sure "My list has headers" is checked.
5. 你will see five columns. Under "Column," select the first column you want to sort from the dropdown. In this case, it is "Grades."
6. Under the column that says "Sort On," select "Cell Color".
7. 在上一列中说“颜色/图标”中，选择绿色条。
8. Then click on the "+" sign at the bottom left of the pop-up. Repeat steps 5-6. Instead of selecting green under "Color/Icon," select the yellow bar.
9. Then click on the "+" sign at the bottom left of the pop-up. Repeat steps 5-6. Instead of selecting green under "Color/Icon," select the red bar.
10. 点击“OK”。

To Sort Conditional Formatting on a PC

1. Click on the data in the row you want to sort.
2. Click on the "Data" tab in your toolbar. You will see "Sort" options in the middle.
3. 点击图标在我之上rd "Sort." A pop-up will appear: If you have headers, make sure "My list has headers" is checked.
4. 你will see three columns. Under "Column" select the first column you want to sort from the dropdown. In this case, it is "Grades."
5. Under the column that says "Sort On," select "Cell Color".
6. In the last column that says "Order," select the green bar.
7. Click on "Add Level." Repeat steps 4-5. Instead of selecting green under "Order," select the yellow bar.
8. 再次单击“添加级别”。重复步骤4-5。而不是在“订单”下选择黄色，而是选择红色条。
9. 点击“OK”。

There you have it -- all the possible ways to sort in Excel. Ready to sort your next spreadsheet? Start by grabbing nine different Excel templates below, then use Excel's sorting function to organize your data as you see fit.