Organize Data in Excel: A Complete Guide

We often need to organize data in Excel while working for easy data management, better data analysis, better visualization, and more efficiency. Here’s an overview of how you can easily sort data.

Overview image of Organize data in Excel

Download the Practice Workbook

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, which uses employee information.

Dataset to organize data in Excel

Method 1 – Organize Data by Using the Sort Feature

Case 1.1 – Sorting a Single Column

Let’s sort the employee names alphabetically A to Z.

Click on column header Name.
Go to Home, then to Editing.
Click on Sort & Filter and select Sort A to Z.

Using sort feature to organize data in Excel

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

Sorted single column

Case 1.2 – Sorting Multiple Columns

Let’s arrange the data by age, then by city, then by name.

Select anywhere in your dataset.
Go to Data, then choose Sort & Filter and select Sort.

Using Sort feature to organize data based on multiple columns

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.
Click on Add Level.

Selecting age from column section and selecting order then adding level

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

Selecting city, A to Z, then adding level again

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

Selecting name, A to Z, and clicking on OK

The final output will be as follows.

Dataset sorted by multiple columns with custom sort feature

Case 1.3 – Sorting from Left to Right

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

Using sort feature to organize data from left to right

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

Marking sort left to right from sort options

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

Selecting Row 4 to sort from left to right

Data will be sorted from left to right.

Dataset sorted from left to right

Case 1.4 – Sorting with Levels

Let’s arrange your data by sorting those with levels. Let’s arrange those employees in the following order: Germany, Spain, France, Portugal.

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

Using sort feature to organize data with levels

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

Sort based on custom list of country

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

Add custom list in Excel

Click on OK again.

Clicking on OK

Your dataset will be organized as follows.

Dataset sorted with levels

Case 1.5 – Sorting with Conditional Formatting

Let’s highlight salaries which are greater than $40,000 with a Light Red color.

Select the range H5:H22.
Go to Conditional Formatting then select Highlight Cells Rules and choose Greater Than.

Using conditional formatting feature to sort data in Excel

A dialog box titled Greater Than will appear. Input 40000 and select a color format that you want. We have selected Light Red Fill.
Click on OK.

Typing 40000 and selecting color then clicking on OK

The cells which have values greater than 40,000 are highlighted as follows.

Specific Cells highlighted with light red color

If 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, use the Less Than and Between options from the Conditional Formatting menu.

Using less than and between options from conditional formatting feature

Our dataset will look like below.

Salaries are highlighted with different colors

Let’s arrange the dataset based on the formatting colors: Light Yellow, Green, Light Red.
Select the dataset.
Go to Data and select Sort & Filter, then choose Sort.

Using sort feature to organize data in Excel

Select Salary from the Column section.
Select Cell Color from the Sort On group.
Select Light Yellow color from the Order section.
Click on Add Level.

Selecting salary, cell color, light yellow color then adding level

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

Selecting salary, cell color, green color then adding another level

Select Salary, Cell Color and Light Red color.
Click on OK.

Selecting salary, cell color, light red color then clicking on OK

The data is organized.

Dataset sorted with conditional formatting

Case 1.6 – Case-Sensitive Sorting

Let’s assume there are names that start with both lowercase and uppercase letters. You want to sort your data by case.

Dataset for the example of case sensitive sorting

Select your dataset.
Go to Data and choose Sort & Filter, then select Sort.

Using sort feature to organize data with case sensitivity

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

Selecting name from column section then clicking on Options

Mark the Case sensitive box.
Click on OK twice.

Marking Case sensitive then clicking OK twice

Your data is sorted by case as follows.

Dataset sorted by case sensitive

Method 2 – Apply a Filter to Organize Data

Select any header of your dataset.
Go to Home and Editing, then select Sort & Filter and choose Filter.

Applying filter feature to dataset to organize data in excel

You can use the filters as needed.

Filter added to the dataset

Case 2.1 – Filter Based on a Single Condition

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

Dataset is organized alphabetically A to Z

Case 2.2 – Filter Based on Multiple Columns and Conditions

Let’s show the female employees from Germany with ages greater than 20. The criteria are in a separate table to the right.

Dataset to filter based on multiple criteria to organize data in Excel

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

Using Advanced filter feature to organize data in Excel

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

Selecting ranges in Advanced filter dialog-box

Here are the results.

Dataset filtered based on multiple criteria

Method 3 – Highlight Important Data in Excel

Let’s highlight those salaries which are greater than $40,000.

Select the range H5:H22.
Go to Conditional Formatting, then select Highlight Cells Rules and choose Greater Than.

Using conditional formatting feature to organize data in Excel

Insert 40000 in the first box and select any color to highlight the match cells.
Click on OK.

Typing 40000 then selecting color then clicking on ok

The cells are highlighted as follows.

Specific cells are highlighted

Method 4 – Hide Unnecessary Data

Let’s hide the Employee ID, Gender, and City columns from your dataset.

Select all 3 columns.
Go to Home and Format, then select Hide & Unhide and choose Hide Columns.

Using Hide Columns feature

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

Selected Columns are hidden

Method 5 – Use an Excel Table to Organize Data

Select the range B4:H22.
Go to Tables and select Table.

Creating Table out of dataset

Mark My table has headers.
Cllick on OK.

Marking My table has headers then clicking on Ok

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

Table created from dataset

Let’s assume you want only male employees’ information.
Click on the arrow down icon beside the Gender header.
Uncheck the Select All box.
Check the Male option.
Click on OK.

Select only male employee

The dataset is filtered as follows.

Dataset is filtered

Method 6 – Organize Data Using Templates in Excel

Go to the File tab.

Clicking on File tab

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

Go to the New option

Read More: How to Organize Raw Data in Excel

Frequently Asked Questions

What is the best way to organize data in Excel?

The best way to organize data in Excel depends on the pattern of your data and how you want it to be.

What are the benefits of organizing data in Excel?

When dealing with larger datasets, organized data enables quicker decision-making and analysis.

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

You can create a table out of a range of data in Excel by using the Table feature. It supports automatic formatting, simple sorting, and filtering, built-in calculations, etc. You can effectively manage and organize your data using tables, which makes it simpler to evaluate and work with large datasets.

Organize Data in Excel: Knowledge Hub

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

Get FREE Advanced Excel Exercises with Solutions!