The filter feature applies a drop down menu to each column heading, allowing you to select specific choices to narrow a table. Specifically, you want to find the number of sales Dylan Rogers made to Eastern Company. In the following GIF, we can see how the Custom Sorting tool can be used to sort date ranges or price ranges.
What if you wanted to sort by date and by price? This where the Custom Sort option really comes in handy. After selecting your first sorting conditions, you can add a level to get event more accurate data:. As you can see, Excel offers a variety of sorting and filtering tools to help you refine your data and keep it organized. Now go out there and get your data sorted! Use Learn Excel Now to help with all your Excel questions and training needs. Learn Excel Now helps you conquer the fear of Excel.
By providing self-paced and instructor-led training and free strategies and guides, we leave no Excel mystery unsolved. Learn Excel Now questions learnexcelnow. Click on the arrow next to another heading to further filter the data. To clear the filter, choose one of these options:. Watch this video for a demonstration of the filtering techniques described above.
This dialog box allows you to set a particular criteria, copy results to another location other location must be in the same sheet , and capture unique values.
In Excel , these are labeled Sort Smallest to Largest and vice versa. Under Column, choose the first column that you would like to sort. If you want to sort multiple columns, click the Add Level button. Under Sort On, choose how you would like to sort. You probably have a file cabinet with files arranged or sorted alphabetically. You probably keep a monthly budget that is, a budget sorted by month.
Excel's sorting options just give you a way to view data in different orders. Why is this useful? Suppose, this week, your boss wants a spreadsheet with three months' worth of expenditures sorted by date. Now, just last week, that same boss wanted the same information, but sorted by department.
Sure, you could have copied the data to a new workbook and copied and pasted the information until it was in the requested sort order, but that's not horribly efficient, especially considering the fact that Excel's sorting function can accomplish the same goal in a matter of seconds. One thing that you'll find in your sorting trial and tribulations is that having a heading for each column in your worksheet makes life a lot less confusing.
Why is this? When you tell Excel you want to sort your information, Excel asks you on which piece of data you want to sort. If you have column headings, Excel automatically populates a list of items on which you can sort i.
Department, Category, Date. If you do not have a column heading that is, you have just data , Excel says "Ok… would you like to sort by column A or column B? I've found it much easier to just assign column headings to my data so Excel instead asks, "Ok… would you like to sort by date or by amount? Before you open up the Sort option in Excel, make sure to select a cell that resides somewhere inside the data you want to sort.
Excel automatically figures out the boundaries of your table based on where Excel sees blank cells. So, if you have selected a cell outside your data, Excel will assume that there is no data to sort. In fact, if you do select any cell inside your table, when you open the Sort window, Excel highlights what it thinks is your entire data table!
I've shown this below in Figure B. I'll explain how to get to the Sort window next. Notice that cells A2 to E49 are selected, skipping the first row. When you have placed headings into your columns, Excel does not include them in the sort because if it did your headings would end up being sorted as data and placed elsewhere in the table. Now, to open the Sort window, from the menu bar, choose Data Sort. You'll get a window similar to the one in Figure C.
To view this sample budget spreadsheet by department and then by category, I use the values shown in the Sort window in Figure D. In Figure D , you can see that I sorted this table first by department and then by category.
The result of this sorting operation is shown below in Figure E. Notice in Figure E that all of the Departments are grouped together and, within each department, the categories are grouped together. Using the sort window is all well and good, but what if you just want to sort by a single column?
Perhaps you have data in no particular order, and you just want to sort it by date and be done with it. Excel provides with you a quick way to handle these super simple sorts. Take a look at Excel's shortcut toolbar. A little more than two thirds of the way across the toolbar, notice two buttons: one is labels AZ with a down arrow and another ZA with an up arrow. See Figure F. To use these buttons, select any cell inside the column on which you'd like to sort and then click one of these buttons.
If you click the AZ button, you'll sort that column ascending; clicking ZA results in a descending sort. For example, if you wanted to sort this sample spreadsheet ascending by date, select any cell in column A and click the AZ button. When you filter information in Excel, you effectively eliminate the extraneous information that you don't need.
Like sorting, you probably filter stuff in your office every day. For example, suppose you have an eight-inch high stack of invoices on your desk, and you need to pay the ones due to "Acme, Inc. To accomplish this, you probably go through the stack of invoices and pull out the ones for Acme, Inc. In effect, you've filtered the invoices so you have only the ones you need and don't have to deal with the rest right away.
As I mentioned with sorting, having headings in your columns is pretty useful for filters, too. Likewise, make sure that you select a cell somewhere inside your worksheet data in order to be able to use the Filtering function. Look back under Sorting if you need more clarification on these items. The best way to better explain filtering and how to use it is to jump right into the use of Excel's AutoFilter feature.
Unlike sorting, Excel's AutoFilter function does not have its own dialog window.
0コメント