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

Total0

Share via

Knowing how to sort in Excel can feel like a superpower. But unfortunately, many go through the tedious route of sorting and organizing data manually.

However, like many operations on Excel, there’s a workaround to sorting and organizing data with a few clicks.

In this guide, we’ll show you the different ways to use Excel’s sorting feature that you may not know about — so you can start using Excel like a pro.

Let’s start with the basics.

Table of Contents

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

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

For example, suppose you want to sort column A, but there’s data associated with column A in columns B and C.

In that case, it’s essential 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 will sort this sheet by the last name of Harry Potter characters. But each person’s first name and house need 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.’

Once you’ve highlighted all the data you want to sort, select the “Data” tab along the top navigation bar (you can see this button on the top-right of the screenshot in the first step above).

This tab will expand a new set of options beneath it, where you can select the “Sort” button. The icon has an “A-Z” graphic, as you can see below, but you can sort it in more ways than just alphabetically.

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

A setting window will appear once you hit the “Sort” button. This is where you can configure what you’d like sorted and how you’d like to sort it.

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

If you’d rather sort by a specific row than a column, click “Options” at the bottom of the window and select “Sort left to right.”

Once you do this, the Sort settings window will reset and ask you to choose the specific “Row” you’d like to sort by in the leftmost dropdown (where it currently says “Column”).

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.

You can sort cells by more than their value.

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.

By default, your Sort settings window will suggest sorting alphabetically (which we’ll show you shortcuts for in the subsequent process below).

However, you can also sort from Z to A or by a custom list. While you can create your own custom list, there are a few preset lists you can sort your data by immediately. We’ll discuss how and why you might sort by custom list in a few minutes.

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. Select the column in the leftmost “Columns” dropdown menu to do that.

This selection 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:

How to Alphabetize in Excel

To alphabetize in Excel, highlight a cell in the column you want to sort by.

Click the Data tab along the top navigation to see buttons for sorting in forward or reverse alphabetical order.

Clicking either button will order your sheet according to the column of the cell you first highlighted.

Sometimes you may have a list of data that has no organization whatsoever. For example, say 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

Select a cell in the column you want to sort.
Click on the “Data” tab in your toolbar and look for the “Sort” option on the left.
If the “A” is on top of the “Z,” click that button once. If the “Z” is on top of the “A,” click on the button twice. Note: When the “A” is on top of the “Z,” your list will be sorted alphabetically. However, when the “Z” is on top of the “A,” your list will be sorted in reverse alphabetical order.

To Alphabetize on a PC

Select a cell in the column you want to sort.
Click on the “Data” tab in your toolbar. You will see Sort options in the middle.
Click on the icon above the word “Sort.” A pop-up will appear: If you have headers, make sure “My list has headers” is checked. If it is, click “Cancel.”
Click on the button that has the “A” on top and 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

What if you want to sort more than one column?

Let’s say you want to organize all your blog posts in a list by the month they were published. And you want to organize them by date and then by the blog post title or URL.

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

To Sort Multiple Columns on a Mac

Click on the data in the column you want to sort.
Click on the “Data” tab in your toolbar and look for the “Sort” option on the left.
Click on the small arrow to the left of the “A to Z” Sort icon. Then, select “Custom Sort” from the menu.
A pop-up will appear: If you have headers, make sure “My list has headers” is checked.
You will see five columns. Under “Column,” select the first column you want to sort from the dropdown menu. (In this case, it is “House.”)
Then, click on the “+” sign at the bottom left of the pop-up. Under where it says “Column,” select “Last Name” from the dropdown.
Check the “Order” column to ensure it says A to Z. Then click “OK.”

To Sort Multiple Columns on a PC

Click on the data in the column you want to sort.
Click on the “Data” tab in your toolbar. You will see “Sort” options in the middle.
Click on the icon above the word “Sort.” You will see a pop-up appear. Ensure “My data has headers” is checked if you have column headers.
You will see three columns. Under “Column,” select the first column you want to sort from the dropdown menu. (In this case, it is “House.”)
Then click “Add Level” at the top left of the pop-up. Under where it says “Column,” select “Last Name” from the dropdown.
Check the “Order” column to ensure it says A to Z. Then click “OK.”

Sorting in Custom Order

Sometimes you want to avoid sorting by A to Z or Z to A and instead sort by something else, such as months, days of the week, or some other organizational system.

In situations like this, you can create your custom order to specify precisely the sort you want. (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

Click on the data in the column you want to sort.
Click on the “Data” tab in your toolbar. You’ll find “Sort” on your far left.
Click on the small arrow to the left of the “A to Z” Sort icon. Then, select “Custom Sort” from the menu.
A pop-up will appear: If you have headers, make sure “My list has headers” is checked.
You will see five columns. Under “Column,” select the first column you want to sort from the dropdown menu in your spreadsheet. In this case, it is “Birthday Month.”
Under the “Order” column, click on the dropdown next to “A to Z.” Select the option for “Custom List.”
You 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.”
Then click on the “+” sign at the bottom left of the pop-up. Next, under “Column,” select “House” from the dropdown.
Click on the “+” sign at the bottom left again. Then, under “Column,” select “Last Name” from the dropdown.
Check the “Order” column to make sure “House” and “Last Name” say A to Z. Then click “OK.”

To Sort in Custom Order on a PC

Click on the data in the column you want to sort.
Click on the “Data” tab in your toolbar. You will see “Sort” options in the middle.
Click on the icon above the word “Sort.” You will see a pop-up appear: If you have headers, make sure “My list has headers” is checked.
You will see three columns. Under “Column,” select the first column you want to sort from the dropdown. In this case, it is “Birthday Month.”
Under the “Order” column, click on the dropdown next to “A to Z.” Select the option for “Custom List.”
You will see a couple of options (month and day) and 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.”
Then, click “Add Level” at the top left of the pop-up. Then, under “Column,” select “House” from the dropdown.
Click on the “Add Level” button at the top left of the pop-up again. Then, under “Column,” select “Last Name” from the dropdown.
Check the “Order” column to make sure “House” and “Last Name” say A to Z. Then click “OK.”

Sorting a Row

Sometimes your data may appear in rows instead of columns. You can still sort your data with a slightly different step when that happens.

To Sort a Row on a Mac

Click on the data in the row you want to sort.
Click on the “Data” tab in your toolbar. You will see “Sort” on the left of your screen.
Click on the small arrow to the left of the “A to Z” Sort icon. Then, select “Custom Sort” from the menu.
A pop-up will appear: Click on “Options” at the bottom.
Under “Orientation,” select “Sort left to right.” Then, click “OK.”
You will see five columns. Under “Row,” select the row number 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

Click on the data in the row you want to sort.
Click on the “Data” tab in your toolbar. You will see “Sort” options in the middle.
Click on the icon above the word “Sort.” You will see a pop-up appear.
Click on “Options” at the bottom.
Under “Orientation,” select “Sort left to right.” Then, click “OK.”
You will see three columns. Under “Row,” select the row number 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 font color, you can 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

Click on the data in the row you want to sort.
Click on the “Data” tab in your toolbar. You will see “Sort” on the lefthand of your screen.
Click on the small arrow to the left of the “A to Z” Sort icon. Then, select “Custom Sort” from the menu.
A pop-up will appear: If you have headers, make sure “My list has headers” is checked.
You will see five columns. Under “Column,” select the first column you want to sort from the dropdown. In this case, it is “Grades.”
Under the “Sort On” column, select “Cell Color.”
In the last column that says “Color/Icon,” select the green bar.
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.
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.
Click “OK.”

To Sort Conditional Formatting on a PC

Click on the data in the row you want to sort.
Click on the “Data” tab in your toolbar. You will see “Sort” options in the middle.
Click on the icon above the word “Sort.” A pop-up will appear: If you have headers, make sure “My list has headers” is checked.
You will see three columns. Under “Column” select the first column you want to sort from the dropdown. In this case, it is “Grades.”
Under the “Sort On” column, select “Cell Color.”
In the last column that says “Order,” select the green bar.
Click on “Add Level.” Repeat steps 4-5. Instead of selecting green under “Order,” select the yellow bar.
Click on “Add Level” again. Repeat steps 4-5. Instead of selecting yellow under “Order,” select the red bar.
Click “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.

How to Sort in Excel: A Simple Guide to Organizing Data originally appeared on HubSpot.com

News

Marketing

Sales

Technology

SaaS Apps

Videos

Roundups

SEO

Business

Marketing Strategy

Email Marketing

View All