How to Organize Data for Analysis in Excel (5 Useful Methods)

We have prepared a dataset containing Product ID, Category, Quantity, Unit Price, and Cost of some companies. Let’s organize the dataset through some common options (Note that these are only a few of the methods you can use, so feel free to experiment).

how to organize data in Excel for analysis

Method 1 – Applying Format Settings to Organize Data in Excel for Analysis

We have monetary values in columns E and F. We’ll display the cells in these columns in Accounting format.

Select all the cells in columns E and F.
Select the $ sign under the Number group on the Home tab.

The Unit Price and Cost cells will be in the Accounting format.

Read More: How to Organize Raw Data in Excel

Method 2 – Sorting Data for Analysis

Let’s sort our data by Quantity and by Cost.

Select a heading cell and click on Sort (from the Data tab).

You’ll see that sorted labels are now available in the headings of the C4 column.

Case 2.1 – Sorting Data by Quantity

We’ll sort the items by Quantity in ascending order (from lowest to highest).

Select any of the sorting labels from A4 to F4. A Sort window will appear.

Select Quantity and Smallest to Largest like in the picture below in the Sort by and Order field, respectively.
Click OK.

Here are the results.

Case 2.2 – Sorting Data by Cost

We’ll use a copy of the dataset from K to O columns. To sort the Cost in descending order:

Click on any of the sort labels from cell K4 to O4.
Select Cost and Largest to Smallest in the Sort by and Order field, respectively.

Click OK.

Method 3 – Highlighting Important Data in Excel for Analysis

Select cells in the F5:F15 range.
Go to the Home tab and select Conditional Formatting.
Choose Color Scales and select the fourth option, Red to White to Green Color Scale.

The cell with the highest value is colored dark red. As the value decreases, the color gradually fades and then again takes on a green color for the lowest values.

Method 4 – Hiding Unnecessary Data in Excel

Select the cells that you want to hide. We have selected Row 6 and Row 10.
Go to the Home tab and click on Cells.
Click Format, choose Hide & Unhide, and select Hide Rows.

Row 6 and Row 10 have been hidden.

Method 5 – Using the Filter Option to Organize Data in Excel

Select all the cells that you want to filter.
Go to Data and click Filter.

Use the Filter to then modify the data as necessary. Suppose we want to Filter the Cost column and want to keep values that are greater than 100,000.