Mastering Data Arrangement in Microsoft Excel: Discover the 7 Most Effective Techniques

Quick Links

It’s easy to sort data in Excel alphabetically or numerically. But maybe you want to sort based on color, formatting, or a list like months of the year. We’ll show you different ways to sort data in Excel.

You can use the quick sort for those basic options or create a custom sort that gives you more flexibility. Whatever your data, be it text or numbers, take a look at these various ways you can sort it.

A to Z Sorting

If you want to sort by text in alphabetical order or numbers like currency or decimals in numerical order, you can do this in Excel in just a few clicks.

Select the data you want to sort and open the Sort tool one of these ways:

On the Home tab, select “Sort & Filter” in the Editing section of the ribbon. At the top of the pop-up box, choose “Sort A to Z” or “Sort Z to A.”
On the Data tab, select “A - Z” or “Z - A” in the Sort section of the ribbon.

Your data is then sorted in the order you picked, either alphabetically from A to Z or the reverse. This option also sorts numbers the same way, from lowest to highest or the opposite.

You can also use the A to Z sorting when you create a custom sort which we’ll describe next.

SwifDoo PDF 2-Year Plan

Row Sorting

For more advanced options in Excel like sorting by row instead of column, you can create a custom sort.

Select your data, go to the Data tab, and click “Sort” in the Sort & Filter section of the ribbon. Then, click “Options” at the top of the Sort box.

Forex Robotron Basic Package

In the small pop-up box, mark the Sort Left to Right option, check the box for Case Sensitive at the top if you like, and click “OK.”

You can then continue setting up your sort by row with the drop-down boxes. Depending on the options you pick in the first drop-down boxes, you’ll be able to sort from A to Z, top to bottom, or another option.

Color Sorting

Also using the custom sort option in Excel, you can sort by color . This allows you to sort your data by the cell or font color. Select your data and then “Sort” on the Data tab to open the Sort box.

Choose the following in the drop-down boxes:

Sort by: Pick the column or row.
Sort on: Pick either “Cell Color” or “Font Color” depending on what you want to sort. Then, use the additional box that appears to select the color.
Order: Pick “On Top” or “On Bottom” per your preference.

Click “OK” when you finish, and you’ll see your data sorted by your chosen color.

Conditional Formatting Icon Sorting

If you take advantage of displaying icons for your data in Excel based on conditional formatting, you can use this as a sort option too. Select your data and then “Sort” on the Data tab to open the Sort box.

Choose the following in the drop-down boxes:

Sort by: Pick the column or row.
Sort on: Pick “Conditional Formatting Icon” and then use the additional box that appears to pick the icon.
Order: Pick “On Top” or “On Bottom” per your preference.

Click “OK” when you finish, and you’ll see your data sorted by your chosen conditional formatting icon.

KoolReport Pro is an advanced solution for creating data reports and dashboards in PHP. Equipped with all extended packages , KoolReport Pro is able to connect to various datasources, perform advanced data analysis, construct stunning charts and graphs and export your beautiful work to PDF, Excel, JPG or other formats. Plus, it includes powerful built-in reports such as pivot report and drill-down report which will save your time in building ones.

It will help you to write dynamic data reports easily, to construct intuitive dashboards or to build a whole business intelligence cockpit.

KoolReport Pro package goes with Full Source Code, Royal Free, ONE (1) Year Priority Support, ONE (1) Year Free Upgrade and 30-Days Money Back Guarantee.

Developer License allows Single Developer to create Unlimited Reports, deploy on Unlimited Servers and able deliver the work to Unlimited Clients.

List Sorting

One convenient sorting option many don’t realize exists in Excel is list sorting. For instance, you may want to sort by day of the week or month of the year. Select your data and then “Sort” on the Data tab to open the Sort box.

Choose the following in the drop-down boxes:

Sort by: Pick the column or row containing the list items.
Sort on: Pick “Cell Values.”
Order: Pick “Custom List.” Then choose an existing list in the Custom Lists section and click “OK.”

You’ll see the list appear in the Order drop-down menu in the Sort box. Click “OK” to sort your data by the list.

You’ll then see your data sorted by your chosen list.

New List Sorting

Another way to sort using a list is with a new custom list. This is convenient if you haven’t had time to create the custom list in Excel yet, because you can make it with the sort tool. Select your data and then “Sort” on the Data tab to open the Sort box.

Choose the following in the drop-down boxes:

Sort by: Pick the column or row containing the list items.
Sort on: Pick “Cell Values.”
Order: Pick “Custom List.” Select “New List,” click “Add,” and then enter each list item in the List Entries box. Be sure to enter them in the order you want them. Click “OK” when you finish.

You’ll see the list in the Sort box’s Order drop-down menu. Click “OK” to sort your data by this list.

You’ll then have your data sorted by the new custom list you just created.

Glary Utilities PRO - Premium all-in-one utility to clean, speed up, maintain and protect your PC

Multi-Level Sorting

If you’d like to sort by more than one column or row in Excel, you can add another level when you create a custom sort . For example, you might sort alphabetically by name first and color second. And, you can add levels to any of the above sorting methods if you like.

In the Sort box, set up your first sort option with the drop-down boxes. Then, choose “Add Level” and set up the secondary sort you want to use.

Keep in mind that, Excel sorts your data based on this list of levels from top to bottom. So if needed, you can rearrange them. Select a level and then use the arrow buttons to move that level up or down.

When you finish, click “OK” to apply the sort for your multiple levels.

Sorting your data in Microsoft Excel helps you see it the way you need to for analysis or review. So keep these sorting methods in mind and also look at how to sort by date in Excel .

Win Able

Mastering Data Arrangement in Microsoft Excel: Discover the 7 Most Effective Techniques