How to Sort in Excel and Keep Your Data Organized

I have been sorting knowledge in Excel for years, even earlier than I joined HubSpot in 2011. And contemplating that content marketing tools usually exported unordered knowledge with out a logical order, it is protected to say I do know a factor or seven about protecting knowledge tidy in Excel.

Whereas Excel and its data-sorting strategies have improved through the years, utilizing it may possibly nonetheless show overwhelming to some. 

However do not let Excel intimidate you. Excel is highly effective, and its sorting operate can simplify your data-sorting workflow. Let me stroll you thru the alternative ways you may type in Excel so you can begin using Excel like a pro.

Desk of Contents

How To Kind in Excel

As an expert spreadsheet software program, Excel provides a number of methods to type knowledge. You need to use “Kind,” add a “Filter,” or flip your dataset right into a “Desk.”

Whereas all of those options allow you to set up your knowledge, I want utilizing the “Kind” software as a result of it kinds the information with out including any additional parts.

I’ll be utilizing Microsoft Excel 365 for Home windows on this information, however the steps will likely be related for earlier Workplace variations and Microsoft Excel for Mac.

1. Spotlight the rows and/or columns you wish to be sorted.

To type a spread of cells in Excel, click on and drag your cursor throughout your spreadsheet to focus on all of the cells you wish to type — even these rows and columns whose values you are not sorting.

Excel often detects the datasets I wish to type even when I don’t do that and simply choose a cell contained in the dataset. Nonetheless, I want doing it this solution to have extra management over the information I’m sorting since I’ve seen Excel embody additional rows or columns that weren’t a part of the unique dataset.

Word the screenshot beneath. Whereas I intend to type this sheet by click on price to see how every weblog subject is performing, I am highlighting the opposite columns so each different element in regards to the weblog subjects matches the clicking price entry that will get sorted. In any other case, there may very well be a mismatch between columns once I end sorting.

2. Navigate to knowledge alongside the highest and choose Kind.

When you’ve highlighted all the information you wish to type, choose the “Knowledge” tab alongside the highest navigation bar.

This tab will broaden a brand new set of choices beneath it, the place you may choose the “Kind” button. The icon has an “A-Z” graphic, as you may see beneath, however you may type it in additional methods than simply alphabetically.

3. If sorting by column, choose the column you wish to order your sheet by.

A setting window will seem when you hit the “Kind” button. That is the place you may configure what you‘d like sorted and the way you’d prefer to type it.

If you happen to’re sorting by a selected column, click on the leftmost dropdown menu — proven beneath “Column” — and choose the column whose values you wish to be your sorting standards. In my instance, it’s going to be Click on Fee.

4. If sorting by row, click on Choices and choose Kind left to proper.

If you happen to’d quite type by a selected row than a column, click on “Choices” on the prime of the window and choose “Kind left to proper.” (If you happen to’re on a Mac, you’ll discover this button on the backside of the window.)

When you do that, the Kind settings window will reset and ask you to decide on the precise row you’d prefer to type by within the leftmost dropdown (the place it at the moment says “column”).

This sorting system doesn‘t fairly make sense for my instance, so we’ll keep on with sorting by the Click on Fee column.

5. Select what you’d like sorted.

You possibly can type cells by greater than their worth.

Within the center column of your Kind settings window, you may see a dropdown menu known as “Kind On.”

Click on it, and you’ll select to type your sheet by totally different traits of every cell within the column/row you are sorting by. These choices embody cell shade, font shade, or any icon included within the cell.

I usually lean on “Kind On” to type mission duties by their statuses — accomplished, pending, or overdue. That lets me see overdue duties on the prime. I’ll dig deeper into that additional beneath.

6. Select the way you’d prefer to order your sheet.

Within the third part of your Kind settings window, you may see a dropdown bar known as “Order.” Click on it to pick the way you’d prefer to order your spreadsheet.

By default, your Kind settings home windows will recommend sorting relying on the column content material. If the column accommodates numbers, it’ll recommend “Smallest to Largest.” However for those who select a column with letter-based values, it’ll recommend “A to Z.”

As I’m sorting by Click on Fee, I get “Smallest to Largest.” However since I wish to see extremely performing blogs on the prime, I’ll change the order to “Largest to Smallest.”

If the default choices don’t suit your knowledge, you may as well use “Customized Record.” I’ll talk about how and why you may type by customized record additional beneath.

7. Click on OK.

Click on OK in your Kind settings window, and it is best to see your record efficiently sorted in response to your required standards.

As an illustration, right here’s what my sorted knowledge seems like:

Productiveness Tip: Use Kind A to Z and Kind Z to A Buttons

Whereas I exploit the Kind settings window to prepare sophisticated knowledge, I usually default to utilizing the “Kind A to Z” and “Kind Z to A” buttons once I’m working with smaller datasets, comparable to an inventory of fewer than 20 entries with 2-5 columns (e.g., an inventory of month-to-month blogs).

To make use of “Kind A to Z” and “Kind Z to A”:

1. Choose a cell within the column you wish to type.
2. Click on on the “Knowledge” tab in your toolbar.
3. Click on “Kind A to Z” if you wish to type in alphabetical order or ascending order. Alternatively, click on “Kind Z to A” if you wish to type in a reverse alphabetical order or in descending order.

When working with data surrounding content performance, I like utilizing these two choices to get beneficial insights, comparable to changing weblog items, high-traffic blogs, and unoptimized content material subjects.

Sorting A number of Columns

What if you wish to type multiple column?

As an illustration, I wish to set up my weblog subjects by their authors after which by the clicking price to see how every writer fared.

Right here’s how I’ll do it.

1. Click on on the information within the column to type.
2. Click on on the “Knowledge” tab within the toolbar.
3. Open the “Kind” choices.
4. Change the “Column” dropdown to the column I wish to type. (In my instance, I’ll change it to Writer.)
5. Click on “Add Stage” on the prime left of the pop-up, which can add a “Then by” row to the Kind settings window.
6. Use the dropdown record in entrance of “Then by” to pick the second column I wish to type by. (In my case, I’ll change it to Click on Fee.)
7. Test the “Order” column to make sure it exhibits the order I wish to see my knowledge in (alphabetically for authors and largest to smallest for click on charges) and click on “OK.”

Word: In case your knowledge has headers, guarantee “My knowledge has headers” is ticked.

Sorting in Customized Order

Generally, you wish to keep away from sorting by A to Z or Z to A and as an alternative type by one thing else, comparable to months, days of the week, or another organizational system.

In conditions like this, you may create your customized order to specify exactly the type you need.

As an illustration, since templates and infographics are sales assets, I usually type content material items by their kind to see how the totally different content material varieties carried out — I’ve added a column for Content material Sort to do that.

To type in customized order:

1. Click on on the information within the column you wish to type.
2. Click on on the “Knowledge” tab in your toolbar.
3. Click on on the “Kind” button.
4. Change the “Column” dropdown to the column you wish to type. I’ll use Content material Sort.
5. Within the “Order” column, click on the dropdown record and choose “Customized Record.”
6. If you happen to’re sorting by month or day, use the predefined lists to type your knowledge. In any other case, add your personal values. As an illustration, I went for “infographic, template, video, weblog.”
7. Click on “Add” so as to add the customized record, and click on “OK.”
8. Within the “Kind” settings window, Click on “OK” to type the information.

Sorting a Row

Generally, your knowledge might seem in rows as an alternative of columns. You possibly can nonetheless type your knowledge through the use of a barely totally different step when that occurs. Whereas uncommon, this function has confirmed helpful at any time when I used to be coping with month-to-month figures and needed to shortly see how we did in numerous months.

To type a row:

1. Click on on the information within the row you wish to type.
2. Click on on the “Knowledge” tab in your toolbar.
3. Click on on the “Kind” button.
4. Open “Choices” on the prime of the window (positioned on the backside of the window for those who’re on a Mac).
5. Below “Orientation,” choose “Kind left to proper.” Then, click on “OK.”
6. Within the “Row” column, choose the row quantity you wish to type from the dropdown. (I’m going with Row 5 to type by complete gross sales.) If you find yourself achieved, click on “OK.”

Sorting Utilizing Conditional Formatting

If you happen to use conditional formatting to alter the colour of a cell, add an icon, or change the font shade, you may type by that, too.

After I was a Product Advertising Supervisor at HubSpot, I relied on conditional formatting and sorting to see if any of the content material items had been overdue and required my consideration.

To point out the way it works, I’ve added one other column and 4 new weblog subjects to the pattern knowledge.

To type utilizing conditional formatting:

1. Click on on the information within the row you wish to type.
2. Click on on the “Knowledge” tab in your toolbar.
3. Click on on the “Kind” button.
4. Below “Column,” choose the column you wish to type. In my case, it’s Standing.
5. Within the “Kind On”, choose “Cell Coloration.”
6. Within the “Order column,” choose the pink bar.
7. Click on on “Add stage.” Repeat steps 4-6, and as an alternative of choosing the pink bar, choose the yellow bar.
8. Click on on “Add stage.” Repeat steps 4-6, and as an alternative of choosing the pink bar, choose the inexperienced bar.
9. Click on “OK.”

Sorting by Partial Values

Whereas regular sorting will get the job achieved more often than not, it’s possible you’ll encounter knowledge the place you wish to type the partial worth of a column. As an illustration, it’s possible you’ll wish to type a buyer record by final title the place you could have knowledge within the type of “First Identify Final Identify” (as in Rachel Leist).

I bumped into that problem up to now at any time when I attempted to check the competing domains that had date knowledge of their URLs. As a substitute of sorting the URLs in alphabetical order, Excel would type the URLs utilizing their date data — because the prior part of the URL stays the identical.

To bypass the problem, I divide the column with partial values into a number of columns. Then, I type the column with the specified worth to get the required knowledge.

Right here’s how one can type by partial values:

1. Spotlight the cells containing the related knowledge.
2. Click on on the “Knowledge” tab in your toolbar.
3. Click on “Textual content to Columns.”
4. Choose delimited if knowledge is split by commas, dashes, an area, or any symbols.
5. Tick the delimiters that apply and add particular symbols in Different if required. In my case, as I’m coping with a URL, I added a ahead slash (/) as a delimiter.
6. After you have the partial worth untangled, you should use “Kind A to Z” to “Kind Z to A” to type the information shortly. Alternatively, you should use the type dialog field to configure a kind order.

If you happen to’re on the lookout for extra tips like utilizing Textual content to Columns, try our Microsoft Excel guide for beginners.

Sorting a Vary Inside a Bigger Vary

If you happen to’re seeking to quickly manipulate the information and wish to discover totally different insights, you may as well type a subset of the information by sorting contained in the bigger vary.

To type a spread inside a bigger vary:

1. Spotlight the subset of the information you wish to type.
2. Click on on the “Knowledge” tab in your toolbar.
3. Use “Kind A to Z” to “Kind Z to A” to type knowledge. You’ll see a “Kind Warning” notifying you that MS Excel discovered bigger knowledge close to your choice.
4. Choose “Proceed with the present choice” and click on “Kind.”

Whereas sorting a spread with a bigger vary provides you a versatile solution to quickly tweak the dataset, I don’t suggest utilizing it in your essential doc — or a minimum of that’s what my not-so-tiny knowledge disasters have taught me.

It’s a must to restore to a earlier model to get rid of knowledge errors for those who lose monitor of the earlier association. Be taught from my errors and create a spare copy of the primary dataset to keep away from dropping your work. Or, if these particular insights are essential to your mission, add filters to cover/unhide entries with out affecting the underlying knowledge.

Frequent Points Surrounding Sorting in Excel

Whereas I’ve shared my tried and examined tricks to get you thru sorting error-free, errors can occur. So, listed here are a number of points I’ve confronted myself or seen others wrestle with:

• Incomplete sorting. Excel ignores the hidden rows and columns throughout sorting. So, for those who unhide the row or column after sorting, you may see incomplete sorting.
• Headers get sorted. If one of many header cells has no textual content, Excel will think about that header row as knowledge by default. So, in case your headers get sorted, examine if one of many header cells is empty.
• Knowledge adjustments after sorting. Excel recalculates the formulation after sorting. In case your dataset depends on random quantity era, you’ll face knowledge adjustments after sorting. Sometimes, you may keep away from that by changing the RAND operate entries with the output values.

There you could have it — I’ve listed all of the potential methods to type in Excel.

Able to put your sorting abilities to make use of? Seize one of many Excel templates beneath and set up your knowledge as you see match.