Asked By
Mitchel7
510 points
N/A
Posted on - 05/12/2011
Hi,
I am using Microsoft Office Excel 2007, but I do not have enough skills in Excel. That's my main problem – I do not know how to classify different data and sort them according to my wants.
Mainly I need help in the field of Filtering Data. How does it work? Moreover, I will be glad if anyone tells me how I can write formulas to sort my data easily.
Excel is a very important software for me, as I have to work with it regularly. Can anyone tell me the meaning of those formulas used in Excel?
I want to know how those formulas work. I have tried to understand this but I couldn't. Those formulas can make my work easy, so it's very important for me to know how these formula work.
So please someone tell me about it and I will be glad.
Answered By
Harry
200 points
N/A
#92080
How to Sorting, Filtering and use Formulas on Excel 2007
Hi Mitchel7,
In MS Excel there is a feature to sort data. So you do not need to worry about working with formulas.
-
Go to the Data tab, in the 'Sort and Filter' group you will see a feature called 'Sort'.
-
First you have to select your data grid.
-
Then you can select the feature sort or right click the selected data grid and select the sort on the menu appears.
There are few options available in sorting feature like as:
-
 Sort A to Z
-
 Sort Z to A
-
 Custom sorting
It is better to use custom sort when you have multiple sorting criterions (columns).
In my grid, there is a Name column and Math Marks column. I selected the whole grid with the headings and selected the custom sort feature. In the dialog box, I ticked the My data has headers and it unselected my headers as you see.
Then I Sort the 'Name' with alphabetical order and then the 'Marks' with ascending order. So now you see the Names columns got alphabetical order and marks of 2 Sam's got ascending order. If you want more advanced knowledge you can point you mouse to the Sort feature and press F1.
Filtering is another way of doing sorting. But not exactly same because this filter do more than sorting. It is mostly used when presenting the data dynamically.
You can see the Filter feature in the 'Sort and Filter' group.
1. First you must select your data grid with the headings.
2. Then select the Filter feature. So you can see each of the heading got arrow keys.
3. When you select the arrow key in a heading, you can order the whole grid according to that columns' ascending/ descending/ A to Z/ Z to A order or customized order.
In here, I will filter the Science Marks more than 50. So the whole grid is ordering according to my filtering criteria.
For example if you want to present someone about the students' marks, you need only the whole mark sheet and this filtering feature applied to it. By clicking this arrow key in the heading you can show each time who are the best scores in each subject.
I hope you got the basic idea of sorting and filtering. As I said before it is better to press F1 and go through it. There are in built features in Excel to reduce the mess with formulas. So it is better to know those features before using formulas.
How to Sorting, Filtering and use Formulas on Excel 2007
Hi Mitchel7,
Sorting and filtering are great ways to manage your data in Excel, without the use of formula or macro. You can use filtering of data when you want to specify the rows that you want to display and at the same time hide those rows that don't meet your selections.
Note that you can also sort your data upon filtering. Sort data alphabetically (from A-Z or Z-A), or sort according to font color. When filtering , you can set to filter by text, by numbers, by dates or times, by blanks or non blanks, by cell color, by font color, etc.
To start filtering, you have to:
-
 Select first the column or columns that you want to filter.
-
 On the Data tab, select Filter icon and you will see a drop down icon on your selected column or columns.
-
 From this drop down you can now set your criteria for filtering. Note that after you filtered your data, those rows that doesn't meet your criteria will not be displayed and therefore cannot be sought.
To be able to search all the data, you have to:
-
 Clear all filters to redisplay all the rows. You can also find the Sort function on the Data tab menu on your Excel window. From there you can click the Sort icon.
-
 Uncheck the "My data has headers" if you want to sort by column letters (Column A, Column B, etc).
-
 Set and choose your selections.
-
 Under the label "Column" is the column or columns that you want to sort.
-
 Under the "Sort On" label is your options if you want to sort by values, by font color, by cell color, or by cell icon.
-
 Under the "Order" label is your choice as to whether you want to sort alphabetically from A-Z, Z-A or you can create your custom list.
Creating Excel formula is recommended for a much complicated managing of your data.
-
 Type first, an equal sign on a cell where you want to execute the formula.
-
 Initially, you have a command in your mind just type the first letter of it. For example you want to trim on some parts of your data.
-
 After typing the equal sign hit the first letter of the word "Trim" which is letter "T" (or just any command you have in mind). You will now have a drop down list of all possible commands beginning with letter "T". You can start from there and the Excel will guide you along the way.
If still you can't make an Excel formula, just click on the question mark icon (blue color) at the top of your Excel window. Type the word "formula" on the search box. Just click on a topic you want to learn about creating Excel formula.
I hope this helps.
probinsyana07.
