Organising Data in Excel

In Excel there are shortcuts to manual tasks.

Like removing duplicates, doing calculations or sorting data, can all be done in a few clicks.

Sometimes these shortcuts are easily overlooked when you don’t know how.

Even organising a list of names in alphabetical order can take up alot of time.

So in this blog post, we will learn a few different ways to use Excel’s sorting features.

Let’s check out the basics.

How to sort in Excel

• Select the rows and/or columns you want sorting
• Go to “Data” at the top and click “Sort”
• Sorting by columns: Click the column to order your sheet by
• Sorting by rows: Click “Options” and then “Sort left to right”
• Select what you want sorting by
• Pick how your sheet should be ordered
• Click “OK”

Note: Don’t worry about the versions or computer types because button locations may differ but icons and selections are the same.

Select the rows and/or columns you want sorting

To sort a range of cells in Excel, start by highlighting all of the cells you want to sort.

Even highling rows and columns with values you’re not sorting by.

For example, column A has data that is related to columns B and C, so highlighting all three columns makes sure the values in Columns B and C also get sorted with Column A.

Go to ‘Data’ at the top and click ‘Sort’

Then go to the “Data” tab at the top navigation bar.

When this tab expands to show new options, click the “Sort” button. 

Note: It’s the icon with “A-Z” on it, but you can sort in more than just alphabetically.

Sorting by columns: Click the column to order your sheet by

After clicking the “Sort” button, its settings will appear.

Here you can select what to sort and how to sort it.

For sorting by a specific column, click “Column” and select the value you want the column to be sorted by.

Sorting by rows: Click ‘Options’ and then ‘Sort left to right’

To sort by a specific row, click “Options” at the bottom of the window and select “Sort left to right.”

Then the Sort settings window will reset and you can select the specific “Row” to sort by.

Select what you want sorting by

You can sort by more than the value of each cell.

The middle column of the Sort settings window has a dropdown menu called “Sort On.”

Click to sort your sheet by different characteristics in each cell such as:

• Cell colour
• Font colour
• Any icon in the cell

Note: This option allows you to sort in the column or row you’re sorting by.

Pick how your sheet should be ordered

Also in the Sort settings’ window is a dropdown bar called “Order.”

Click to pick how your spreadsheet should be ordered.

By default, it’s suggested to sort alphabetically.

But it can be sorted Z to A and by a custom list.

There are also some preset lists you can sort your data by.

To Sort by Number

If your spreadsheet has a column of numbers, you can sort your sheet by these numbers.

Do this by selecting this column in the “Columns” dropdown menu.

This changes the “Order” dropdown bar to sort from “Smallest to Largest” or “Largest to Smallest.”

Click ‘OK’

Click “OK” to sort your spreadsheet by the options you selected.

Sort by the Alphabet in Excel

To sort by the alphabet in Excel, highlight a cell in the column to sort by.

Click the Data tab at the top to see options to sort in forward or reverse alphabetical order.

Clicking one of these buttons will order your sheet by the column of the cell you highlighted.

If you have a list of data that is not organised and you want to alphabetise the list.

There’s an easy way to do that without using the steps above:

Alphabetise on a Mac

• Highlight any cell of the column you want to sort by
• Click the “Data” tab and go to the “Sort” option on the left
• If the “A” is on top of the “Z,” click the button once
• If the “Z” is on top of the “A,” click the button twice

Note: “A” on top of the “Z,” means the list will be sorted in alphabetical order.

But “Z” on top of the “A,” means the list will be sorted in reverse alphabetical order.

Alphabetise on a PC

• Highlight any cell of the column you want to sort by
• Click the “Data” tab to see Sort options in the middle
• Click the icon above “Sort.”
• A pop-up will ask if you have headers
• Either check “My list has headers” or click “Cancel” if it’s already checked
• Click the button with the “A” on top and the “Z” with an arrow pointing down

Note: Doing so sorts your list alphabetically from “A” to “Z.”

To sort your list in reverse alphabetical order, click the button with the “Z” on top and the “A”.

Sorting Multiple Columns

Sometimes you want to sort two columns:

Sort Multiple Columns on a Mac

• Select the data in the column you want sorting
• Click the “Data” tab to see the “Sort” option on the left
• Click the small arrow on the left of the “A to Z” icon
• Select “Custom Sort” on the menu
• A pop-up will ask if you have headers
• Check “My list has headers”
• Then you’ll see five columns
• Under “Column” select the first column to sort by from the dropdown menu
• Click the “+” sign at the bottom left on the pop-up
• Go back to “Column” and select the other column to sort by from the dropdown
• Check the “Order” column says A to Z
• Finally, click “OK”

Sort Multiple Columns on a PC

• Select the data in the column you want sorting
• Click the “Data” tab to see the “Sort” options in the middle
• Click on the icon above “Sort”
• A pop-up will ask if your data has headers
• Check “My data has headers” if you have column headers
• Then you’ll see three columns.
• Under “Column” select the first column to sort by from the dropdown menu
• Then click “Add Level” at the top left of the pop-up.
• Go back to “Column” and select the other column to sort by from the dropdown
• Check the “Order” column says A to Z
• Finally, click “OK”

Sorting in Custom Order

Sometimes you want to sort by:

• Months
• Days of the week
• Other organisational systems

For this, you can create custom orders to specify the order you want to sort:

Sort in Custom Order on a Mac

• Select the data in the column you want sorting
• Click the “Data” tab to see the “Sort” options on the left
• Click the small arrow to the left of the “A to Z” icon
• Click “Custom Sort” on the menu
• A pop-up will ask if you have headers
• Check “My list has headers”
• Then you’ll see five columns
• Under “Column,” select the first column to sort by from the dropdown menu
• Under the “Order” column, click the dropdown next to “A to Z”
• Click “Custom List”
• You will see options for month, day and the option to create custom order
• Click your desired option and click “OK”
• Then to add more sort options, click the “+” sign at the bottom left of the pop-up
• Under “Column,” select another column to sort by from the dropdown
• Check the “Order” column says A to Z
• Finally, click “OK”

Sort in Custom Order on a PC

• Select the data in the column you want sorting
• Click the “Data” tab to see the “Sort” options in the middle
• Click the icon above “Sort.”
• A pop-up will ask if you have headers
• Check “My list has headers”
• Then you’ll see three columns
• Under “Column,” select the first column you want to sort by from the dropdown
• Under the “Order” column, click on the dropdown next to “A to Z”
• Click “Custom List”
• You will see options for month, day and the option to create your own custom order
• Click your desired option then click “OK”
• Then, to add more options click “Add Level” at the top left of the pop-up
• Under “Column,” select another column to sort by from the dropdown
• Check the “Order” column says A to Z
• Finally, click “OK”

Sorting a Row

Sometimes your data appears in rows instead of columns:

Sort a Row on a Mac

• Select the data in the row you want sorting
• Click the “Data” tan to see the “Sort” options on the left
• Click the small arrow to the left of the “A to Z” icon
• Click “Custom Sort” from the menu
• A pop-up will show then click “Options” at the bottom
• Under “Orientation” select “Sort left to right”
• Now, click “OK”
• Then, you’ll see five columns
• Under “Row,” select the row number to sort by from the dropdown
• Finally, click “OK”

Sort a Row on a PC

• Select the data in the row you want sorting
• Click the “Data” tab to see the “Sort” options in the middle
• Click the icon above “Sort”
• A pop-up will show then click “Options” at the bottom
• Under “Orientation” select “Sort left to right”
• Now, click “OK”
• Then, you’ll see three columns
• Under “Row,” select the row number to sort by from the dropdown
• Finally, click “OK”

Sort Your Conditional Formatting

You can sort by conditional formatting, when it’s used to change font or cell colours or add icons:

To Sort Conditional Formatting on a Mac

• Select the data in the row you want sorting
• Click the “Data” tab to see the “Sort” options to the left
• Click the small arrow to the left of the “A to Z” icon
• Click “Custom Sort” from the menu
• A pop-up will ask if you have headers
• Check “My list has headers”
• Then, you’ll see five columns
• Under “Column,” select the first column to sort by from the dropdown
• Under the “Sort On” column, select “Cell Colour”
• And in the “Colour/Icon” column, select the colour to sort by
• Then to add more sort options, click the “+” sign at the bottom left of the pop-up
• Repeat steps 5-6, selecting different options under “Color/Icon”
• Finally, click “OK”

To Sort Conditional Formatting on a PC

• Select the data in the row you want sorting
• Click the “Data” tab to see the “Sort” options in the middle
• Click the icon above “Sort”
• A pop-up ask if you have headers
• Check “My list has headers”
• Then, you’ll see three columns
• Under “Column” select the first column to sort by from the dropdown
• Under the “Sort On” column, select “Cell Colour”
• And in the “Order” column, select the colour to sort by
• Then to add more sort options, click “Add Level”
• Repeat steps 4-5, selecting different options under “Order”
• Finally, click “OK”

Conclusion

Now you know all the ways to sort in Excel.

And you’re ready to sort your next spreadsheet.

So start using these Excel sorting functions to organise your data.

Now it’s over to you.

Tell me which Excel function is the one you most use?

Let me know in the comment section below.