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

Get FREE Advanced Excel Exercises with Solutions!

This article shows you how to organize raw data in Excel. Microsoft Office Excel comes with a number of features that make data management and analysis more simple. It’s important to organize raw data in a worksheet to get the most out of these features. Here, we will take you through 6 easy and convenient methods on how to organize raw data in Excel.


Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.


6 Methods to Organize Raw Data in Excel

There are numerous ways to present your data in Excel in a more pleasing manner.

Here, we have a Sales Report of some Sales reps containing their Product’s Name in column D, Unit Price in column E, Units sold in column F, and Sales amount in column G.

How to Organize Raw Data in Excel Dataset

We’d like to present the raw data in a more organized manner in order to make it more visually appealing.

This is how you do it!


1. Applying Formatting Settings

Good formatting makes it easier for your user to find information in the spreadsheet without having to go through each and every cell. Formatted cells will direct the viewer’s attention to the most significant cells. Follow the steps below.

Steps:

  • In columns E and G, we have monetary values. As a result, we’d like to show the cells in these columns in Accounting format. Select the cells of columns E and G like the picture below. Go to the Home tab and select $ sign in the Number group.

How to Organize Raw Data in Excel Applying Formatting

  • You can see your Unit Price and Sales in Accounting format.

How to Organize Raw Data in Excel Applying Formatting

  • Column F contains Units sold by respective Sales Reps. To make this formatted as Number, select the cells in the F5:F14 range. In the Number group, choose the Number format from the drop-down list.

How to Organize Raw Data in Excel Applying Formatting

  • Now, you can see the whole 3 columns are formatted as your preferred formatting.

How to Organize Raw Data in Excel Applying Formatting

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


2. Highlighting Cell of Necessary Data

You may Highlight Cells in Excel based on their value. To better visualize your data, you can utilize Conditional Formatting.

Steps:

  • Select cells in the E5:E14 range.

How to Organize Raw Data in Excel Using Highlight Cell

  • Then, go to the Home tab and select Conditional Formatting > Colour Scales > Red – White Color Scale.

How to Organize Raw Data in Excel Using Highlight Cell

  • Instantly, your selected cells look like the below image. In Red – White Color Scale, the cell with maximum value gets a dark red color, and eventually, the cell color becomes faded with decreasing values.

How to Organize Raw Data in Excel Using Highlight Cell

  • Similarly, use the same method for the cells of column G with the Green – White Color Scale.

How to Organize Raw Data in Excel Using Highlight Cell

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


3. Sorting Raw Data

Data Sorting in Excel allows users to sort data according to their needs. Data can be sorted in a number of different ways, including alphabetical order, increasing order, color sorting, left to right sorting, and more. We’ll sort our data so that sales are in descending order from highest to lowest.

Steps:

  • At the very beginning, select cells in the G5:G14 range. Then, go to the Home tab and select Editing > Sort & Filter > Sort Largest to Smallest.

How to Organize Raw Data in Excel Employing Sorting

  • At this moment, a Sort Warning dialog box opens. Here, Expand the selection is selected automatically. Click on Sort.

How to Organize Raw Data in Excel Employing Sorting

  • We can see that our data range is sorted in descending order according to the Sales amount.

How to Organize Raw Data in Excel Employing Sorting

Read More: How to Organize Data in Excel from Lowest to Highest (with Quick Steps)


4. Using Filter Option to Organize Raw Data in Excel

In Excel, a Filter is a valuable feature that allows us to display only the values that we specify. We can later modify, copy, chart, or print only the viewable values based on the filtered result. To use the Filter, first, we have to Add Filter to the data range. Then, use the Filter to manipulate data according to your need.

Steps:

  • Use the drop-down arrow beside the Sales header to use the Filter option.

How to Organize Raw Data in Excel Using Filter Option

  • Here we want to show only the rows having a Sales amount of $10,000 or greater than that. So, from the options, select Number Filters > Greater Than or Equal To.

How to Organize Raw Data in Excel Using Filter Option

  • Instantly, a Custom AutoFilter dialog box appears. In the upper-right box, write down 10000 and click OK.

Using Filter Option

  • We can clearly see that all the Sales amount showing are greater than $10,000.

Using Filter Option

Note: Here it is noticeable that, between row numbers 4 to 10, there are some row numbers missing. Because those have Sales amount lesser than $10,000. But those rows aren’t permanently deleted, they are just hidden.

Read More: How to Organize Sales Leads in Excel (with Quick Steps)


5. Hiding Unimportant Data

Also, you can hide data that you don’t wanna see or are unimportant. You can also unhide them whenever you want.

Steps:

  • Select rows 8 and 13 by clicking on those areas marked on the image below.

Using Hide Option

  • Go to the Home tab. In the Cells group, select Format > Hide & Unhide > Hide Rows.

Using Hide Option

  • We can see that our selected rows aren’t showing in the worksheet now. They are hidden now.

Using Hide Option


6. Implementing FILTER Function to Organize Raw Data in Excel

Another way to apply a Filter is to implement the FILTER function. Here we will get our values in another table. First, create the table with the same kind of headers. Suppose, you wanna see only the details about the product Apple. To do so, follow our steps below.

Steps:

  • At first, select cell B17. Write down the formula below and press ENTER.
=FILTER(B5:G14,D5:D14=D5,"")

Using FILTER Function

In this table, just the values related to Apple are shown.


Conclusion

Thank you for reading this article, we hope this was helpful. Please let us know in the comment section if you have any queries or suggestions. Please visit our website Exceldemy to explore more.


Related Articles

Shahriar Abrar Rafid

Shahriar Abrar Rafid

Hello! Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc in Engineering from the Bangladesh University of Engineering & Technology. I am a Naval Architecture and Marine Engineering graduate with a great interest in research and development. I love reading books & traveling. Always try to gather knowledge from various sources and implement them effectively in my work.

2 Comments
  1. I read your entire article. I liked it very much. I have a question, but not related to this topic.
    In my business, i’ve 5 sales person working for me in 3 different areas. Now, i wantto know the number of sales p with sales amount less than 350/week.
    Help me on this..

    • Hello DEANNA,

      First of all, many many thanks for your appreciation. At the end of the day, these kinds of words motivate us a lot. Now, get back to your query.

      For your convenience, download this practice workbook.

      Firstly, we’ve created a dataset as per your description. We’ve constructed an imaginary Weekly Sales Report of your company. Let’s see the following picture.

      Now, we’ll find out how many sales reps cannot cross the Sales Amount of $350 in a week.

      At first, go to cell D11 and enter the following formula into the cell.
      =COUNTIF(D5:D9,"<350")
      Secondly, press the ENTER key.

      Here, we can see that Excel returns 2 as result. Because two employees made sales amount below $350 which are Person 2 and Person 5. We think you wanted to know about this part.

      As a bonus, we’ll teach you one more trick. You can get a more specific answer with this. Guess, if you wanted to know how many sales reps made sales amount below $350 in a specific zone. Let’s say, in the West zone. Obviously, you can crack this too.

      Firstly, select cell D11 and write down the formula below.
      =COUNTIFS(D5:D9,"<350",C5:C9,"West")
      As usual, tap ENTER.

      The result is 1 because there is only one Sales Rep in the West zone with a Sales Amount less than $350 who is Person 2.
      To explore more about Excel, please visit our website Exceldemy: One-stop Excel solution provider…

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo