Organize Data in Excel: A Complete Guide

In this Excel tutorial, we have discussed how to
-Organize data using Sort feature.
-Apply Filter feature to organize data.
-Highlight important data.
-Hide unnecessary data.
-Utilize Excel Table to organize data.
-Organize data using templates.

While preparing this article we have used Microsoft 365, but the methods in this article are also applicable in other versions.

We often need to organize data in Excel while working for easy data management, better data analysis, better visualization, and working more efficiently.

Download Practice Workbook

You can download the workbook used to demonstrate methods in this article from here.

Organize Data in Excel.xlsx

How to Organize Data in Excel

We will be using the following dataset to describe how to organize data in Excel. The dataset is representing some employees’ information about a company.

1. How to Organize Data by Using Excel Sort Feature

In Excel, sorting is the process of organizing data in a certain order depending on the values in one or more columns. It allows you to arrange data in either ascending or descending order, making it simpler to identify and process data. Excel has several sorting features, including sorting by a single column or multiple columns, as well as sorting based on alphabetical order, numerical values, dates, or custom criteria. Let’s have a look at some of the uses of Excel sort feature.

1.1 Sorting Single Column

Let’s assume, you want to sort the employee names alphabetically A to Z.

Click on column header Name.
Follow these steps: Home >> Editing >> Sort & Filter >> Sort A to Z.

You can see that the names of all the employees are arranged in alphabetical order.

1.2 Sorting Multiple Columns

In Excel, you can organize your data by sorting multiple columns as well. Let’s say, here, first you want to arrange your data by sorting employees’ ages by smallest to largest. Then you want it by sorting their cities in alphabetical A to Z order. After that, you want to sort by their names in alphabetical A to Z order.

First, select anywhere in your dataset.
Follow these steps: Data >> Sort & Filter >> Sort.

A dialog-box Sort will appear.
In the Column section, click on the Sort by drop-down menu then select Age.
Select Smallest to Largest from the Order group.
After that, click on Add Level.

Select City from the Then by drop-down.
Select A to Z from the Order column as shown.
Click on Add Level again.

Select Name from the Column section.
Select A to Z from the Order group.
Click on OK.

The final output will be as follows.

1.3 Sorting from Left to Right

Yow can sort your data from left to right as well.

Select cell B4 which is Employee ID header.
Go to the Data tab.
Click on the Sort & Filter menu.
Select Sort.

From the Sort dialog box, click on Options.
Then the Sort Options dialog box will appear.
Select the Sort left to right option.
Click on OK.

Now, from the Row sections Sort by drop-down menu, select Row 4 as our dataset starts from row 4.
Select the alphabetical order you want from the Order column.
We have selected A to Z.
Click on OK.

Data will be sorted from left to right.

1.4 Sorting With Levels

Suppose, from the following dataset, you want to arrange your data by sorting those with levels. You want to arrange those employees’ information first who are from Germany, then Spain, then France and lastly from Portugal.

First, select your dataset.
Go to the Data tab.
Click on the Sort & Filter option.
Select Sort.

Select Country from the Column section.
Then from the Order column, select Custom List.

Type Germany, Spain, France, Portugal in the List entries box.
Click on Add.
Then click on OK.

Click on OK again.

Your dataset will be organized as follows.

1.5 Sorting with Conditional Formatting

Let’s say, you want to highlight those salaries which are greater than $40,000 with Light Red color.
Select range H5:H22 which is basically the Salary column.
Follow these steps: Home >> Conditional Formatting >> Highlight Cells Rules >> Greater Than.

Now, a dialog-box titled Greater Than will appear.
Type down 40000 and select a color format as you want. Here, we have selected Light Red Fill.
Click on OK.

And the cells which have values greater than 40000 are highlighted as follows.

Again, you want to highlight salaries less than $30,000 with Light Yellow color and salaries that are between $30,000 and $40,000 with Green color.
You can highlight those by using the Less Than and Between options from the Conditional Formatting feature like previously.

Our dataset will look like below.

Now, we want to arrange the information of employees based on salary with a sequence of Light Yellow, Green color, Light Red color.
Select our dataset.
Take this action: Data >> Sort & Filter >> Sort.

Select Salary from the Column section.
Select Cell Color from the Sort On group.
Then select Light Yellow color from the Order section.
After that, click on Add Level.

Now again, select Salary from the Column section as shown below.
Select Cell Color and Green color.
Click on Add Level.

Again, select Salary, Cell Color and Light Red color.
Finally, click on OK.

And your data is organized as follows.

1.6 Case Sensitive Sorting

Let’s assume, your dataset is as follows where there are names that start with both lowercase and uppercase letters.
Now you want to sort your data by case sensitive.

Select your dataset.
Adopt these steps: Data >> Sort & Filter >> Sort.

A dialog box will appear titled Sort.
Select Name from the Column group.
Click on Options.

Mark the Case sensitive box.
Click on OK twice.

Finally, your data is sorted by case sensitive as follows.

2. How to Apply Filter to Organize Data

Using Excel’s built-in Filter feature, you can organize your data more quickly and efficiently.

First, select any header of your dataset.
Go through these steps: Home >> Editing >> Sort & Filter >> Filter.

And now you can use this feature.

2.1 Filter Based on a Single Condition

Assume, you want to arrange the employee names alphabetically A to Z.
Click on the arrow down icon beside the Name header.
Select Sort A to Z.

You can see that the names of all the employees are arranged in alphabetical order.

2.2 Filter Based on Multiple Columns and Conditions

Suppose, from the following dataset, you want to filter those employees’ information only who are from Germany, Female, and ages greater than 20. You can do this by utilizing Excel’s built-in Advanced Filter feature. Here, in the following dataset, our criteria range is J5:L6.

Go to the Data tab.
Click on the Advanced option.

A dialog box titled Advanced Filter will appear on your worksheet.
Mark Copy to another location.
Now, select range B4:H22 in the List range box.
Select range J5:L6 in the Criteria range box.
Select cell J8 in the Copy to box.
Now, click on OK.

And your desired result is as follows.

3. How to Highlight Important Data in Excel

Using Conditional Formatting feature, you can highlight your important data as well. Let’s say, you want to highlight those salaries which are greater than $40,000.

First, select range H5:H22 which is basically the Salary range.
Follow these steps: Home >> Conditional Formatting >> Highlight Cells Rules >> Greater Than.

Type down 40000 and select any color to highlight the match cells.
After that, click on OK.

And the cells are highlighted as follows.

4. How to Hide Unnecessary Data

You can hide unnecessary data using Excel’s Format tool. You can hide columns, rows, and even the entire sheet as well. Assume, you want to hide the Employee Id, Gender, and City columns from your dataset.

First, select all 3 columns.
Go through these steps: Home >> Format >> Hide & Unhide >> Hide Columns.

And you can see that those columns are now hidden from your dataset.

5. How to Utilize Excel Table to Organize Data

Using Excel’s Table feature, you organize your data in many ways. You can filter your dataset in many ways after creating a table.

Select range B4:H22.
Follow these steps: Home >> Tables >> Table.

Mark My table has headers.
Then click on OK.

And the table is created as follows.
The Filter feature is added to your table as well.

Now, let’s assume, you want only male employees’ information.
Click on the arrow down icon beside the Gender header.
First, uncheck the Select All box.
Then check the Male option.
And click on OK.

And the dataset is filtered as follows.

6. How to Organize Data Using Templates in Excel

Data will be easier to visualize and analyze if it is organized in Excel utilizing templates. You can download and use many types of templates to organize data in Excel.

Go to the File tab.

Click on New.
Here, you will find several templates.
You can even search for various types of templates and download them to use.

Read More: How to Organize Raw Data in Excel

Frequently Asked Questions

1. What is the best way to organize data in Excel?

In many ways, you can organize your data in Excel. The most used built-in Excel features for organizing data in Excel are Sort, Filter, Advanced Filter, etc. The best way to organize data in Excel? It depends on the pattern of your data and how you want it to be.

Let’s say, you have a large dataset with dates. So, for that, you can organize your data by sorting dates by oldest to newest or newest to oldest. Which should you choose? That depends on which one is more essential, the newest dates or the oldest. Presume again, you have a dataset with names and for that, you can organize those by sorting alphabetically.

2. What are the benefits of organizing data in Excel?

There are various advantages of organizing data in Excel. By making access and analysis simple, it improves readability and understanding. Sorting, filtering, and formula application are made easier and quicker with effective data organization.

When dealing with larger datasets, organized data enables quicker decision-making. Organized data improves one’s decision-making, data analysis, and management.

3. Is it possible to create tables in Excel to organize and analyze data?

To organize and analyze data, it is possible to create tables in Excel. You can create a table out of a range of data in Excel by using the Table feature. Automatic formatting, simple sorting, and filtering, built-in calculations, etc. are just a few advantages that tables have. You can effectively manage and organize your data using tables, which makes it simpler to evaluate and work with large datasets.

Conclusion

Organizing data in Excel is often essential to improve data management, analysis, and general productivity.

In this article, we have learned the use of several built-in tools to organize data in Excel. Excel Table, Sort, Filter, Advanced Filter, etc are some of those features that allow you to organize your data efficiently.

Hope, you have found whatever you were looking for. Visit our site ExcelDemy to explore more relevant articles.

Organize Data in Excel: Knowledge Hub

<< Go Back to Data Analysis with Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!