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.


Download Practice Workbook


5 Useful Methods to Organize Data for Analysis in Excel

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.

how to organize data in Excel for analysis

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


1. Applying Format Settings to Organize Data in Excel

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.

how to organize data in Excel for analysis using Format

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

how to organize data in Excel for analysis using Format

Read More: How to Organize Information in Excel (2 Circumstances)


2. Sorting Data

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.

how to organize data in Excel for analysis using Sort bar

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

how to organize data in Excel for analysis using Sort bar

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.

how to organize data in Excel for analysis using Sort bar

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

how to organize data in Excel for analysis using Sort bar

  • 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.

how to organize data in Excel for analysis using Sort bar


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.

 using Sort bar

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

 using Sort bar

Read More: How to Organize Things Alphabetically in Excel (4 Methods)


3. Highlighting Important Data

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.

how to organize data in Excel for analysis using Highlighter

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.

how to organize data in Excel for analysis using Highlighter

Read More: How to Organize Things Alphabetically in Excel (4 Methods) 


4. Hiding Unnecessary Data

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 Rows.

how to organize data in Excel for analysis by hiding

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

hiding rows


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.

how to organize data in Excel for analysis using 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.

using Filter

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

using Filter

  • 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.

using Filter

Read More: How to Organize Raw Data in Excel (6 Easy Methods)


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.

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. For more information, kindly visit ExcelDemy.


Related Articles

Towhid
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo