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 examples on how to organize raw data in Excel.
How to Organize Raw Data in Excel: 6 Suitable Examples
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.
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.
- You can see your Unit Price and Sales in Accounting format.
- 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.
- Now, you can see the whole 3 columns are formatted as your preferred formatting.
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.
- Then, go to the Home tab and select Conditional Formatting > Colour Scales > Red – White Color Scale.
- 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.
- Similarly, use the same method for the cells of column G with the Green – White Color Scale.
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.
- At this moment, a Sort Warning dialog box opens. Here, Expand the selection is selected automatically. Click on Sort.
- We can see that our data range is sorted in descending order according to the Sales amount.
Read More: How to Organize Data in Excel from Lowest to Highest
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.
- 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.
- Instantly, a Custom AutoFilter dialog box appears. In the upper-right box, write down 10000 and click OK.
- We can clearly see that all the Sales amount showing are greater than $10,000.
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.
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.
- Go to the Home tab. In the Cells group, select Format > Hide & Unhide > Hide Rows.
- We can see that our selected rows aren’t showing in the worksheet now. They are hidden now.
Read More: How to Organize Data for Analysis in Excel
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,"")
In this table, just the values related to Apple are shown.
Download Practice Workbook
You may download the following Excel workbook for better understanding and practice yourself.
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.
Related Articles
- How to Organize Information in Excel
- How to Organize Things Alphabetically in Excel
- How to Organize Time in Excel
- How to Organize Sales Leads in Excel
- How to Organize Expenses in Excel
<< Go Back to Organize Data in Excel | Data Analysis with Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
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…