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

By studying how to organize data in Excel for analysis you can learn basic data organizing techniques. Especially when you make a database of any research or business documents. There are many ways in Excel that you can use to organize data for analysis. In this article, we’ll discuss how to organize data in Excel for analysis.

Based on many criteria, we will organize the Sales Report information in various ways. In a sales record there are various parameters like Quantity, Unit Price etc. We’ll let you walk through the process of organizing data by adopting various ways and sorting them. To facilitate this, we have prepared a dataset based on Product ID, Category, Quantity, Unit Price, and Cost of some companies in California. The dataset looks like this.

Now, let’s discuss various methods and ways to organize this dataset.

1. Applying Format Settings to Organize Data in Excel for Analysis

At the beginning, you can find information in the spreadsheet more quickly and easily with good formatting by skipping over fewer cells. You will draw your focus to the most important cells by formatting them.

We have monetary values in columns E and F. We want to display the cells in these columns in Accounting format. The steps are listed below.

As shown in the illustration below, choose the cells in columns E and F.
Select the $ sign under the Number group on the Home tab.

Your Unit Price and Cost are in Accounting format as the output.

Read More: How to Organize Raw Data in Excel

2. Sorting Data for Analysis

You can arrange data in Excel’s Data Sorting function in accordance with your needs. You can sort data in a variety of ways, including alphabetically, in increasing order, by color, from left to right, and in other ways as well. Let’s sort our data by Quantity and by Cost. You need to add a Sort option in the Heading cells of column 4.

Firstly, select any heading suppose cell M4 and then click Sort bar.

As a result, you’ll see that sorted labels are now available in the headings of the C4 column.

Now, let’s discuss Sorting by Quantity & Sorting by Price.

2.1. Sorting Data by Quantity

Suppose, you need to sort Quantity in Ascending Orders from lowest to highest.

Firstly, you need to select any of the sorting labels from A4 to F4. Then a Sort bar will appear.

Now, select Quantity and Smallest to Largest like in the picture below in the Sort by and Order group respectively. And then click OK.

Finally, you’ll find the output in the column of Quantity from cell M4 to M15 like this where the values are now in ascending order.

2.2. Sorting Data by Cost

Similarly as before, if you want to sort Cost in descending order, you need to click on any of the sort labels from cell K4 to O4 and select Cost and Largest to Smallest in the Sort by and Order bar respectively.

Eventually, after clicking OK, you’ll find the Cost column is in descending order.

3. Highlighting Important Data in Excel for Analysis

Based on their value, you can highlight cells in Excel. You can use Conditional Formatting to improve the visualization of your data. To do this, you need to follow the steps below.

Select cells in the F5:F15 range.
Then, go to the Home tab and select Conditional Formatting > Color Scales > Red – White Color Scale.

Your chosen cells immediately take the colors below. In the Red-White Color Scale, the cell with the highest value is colored dark red, and as the value decreases, the color of the cell gradually fades and then again takes on a lighter green to darker green color.

4. Hiding Unnecessary Data in Excel

You can hide unimportant data for better analysis in your Excel sheet.

You can do this by following the steps below.

Firstly, select the cells that you want to hide. Here, we have selected Row 6 and Row 10.
Secondly, go to the Home tab > click on Cells > click Format > choose Hide & Unhide > choose Hide Rows.

As a result, you’ll find that Row 6 and Row 10 have been hidden like this.

5. Using Filter Option to Organize Data in Excel

The Filter feature in Excel is a useful tool that enables us to display only the values that we define. Based on the filtered result, you can later edit, copy, chart, or print only the viewable values. Prior to using the Filter, the data range must first have the Filter added.

For this, first select all the cells that you want to filter.
Secondly, click Data > click Filter.

Use the Filter to then modify the data as necessary.

Suppose you want to Filter the Cost column and want to keep values that are greater than 100000.

Here first, click on Filter labeling of Cost i.e. cell F4.
Secondly, click Number Filters > click Greater Than Or Equal To.

Eventually, a Custom AutoFilter will appear. Put 100000 in the box shown below.

Finally, click OK. As a result, you will find that only values in the Cost column which are greater than 100000 are present and other rows containing Cost values lower than this disappear. So row numbers 5, 7, 8, 9, 10, 11, 12, 13, 14 have been hidden through filtering.

Things to Remember

Using Format settings, you can format not only Accounting format but also any type like Text or Percentage, Numerical type and so on.
You can hide both rows and columns to organize data for analysis by following the same procedure of row hiding here.
Every procedure we have described here has so many wing options. You can choose any of these options according to your desired formatting.

Download Practice Workbook

Organising Data.xlsx

Conclusion

We appreciate you reading this article and hope you found it useful. If you have any questions or recommendations, do let us know in the comment area.

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

Get FREE Advanced Excel Exercises with Solutions!