Top 5 # Xem Nhiều Nhất Bỏ Filter Trong Excel Mới Nhất 4/2023 # Top Like

One of the most valuable features when analyzing data in Excel is the ability to filter your data in place. The hours of work that it has saved me made me choose AutoFilter as the Excel tip for my first newsletter almost 6 years ago. But I decided it was worth revisiting since more than half of my current subscribers weren’t around then and a few versions of Excel have gone by since then. I’ll be using Excel 2010 for this example.

In addition to the selection check boxes, there will be a menu option for Text Filter, Number Filter, or Date Filter depending on the format of that column. For text columns you’ll find options such as Equals, Does Not Equal, Begins with, etc. Numeric columns will have options including Equals, Does Not Equal, Greater/Less Than, Between, and even Top 10. Date column options include Equals, Before, After, Between, Tomorrow, Next Week, etc. All three also have a choice for Custom Filter that allows you to enter two and/or filter conditions.

The obvious benefit is that you can dynamically change which records you are looking at without actually deleting, or even rearranging your data. But there are some other very valuable uses for AutoFilter.

First, you can use AutoFilter to locate empty cells. In addition to filtering for any specific entries, you can choose to see blanks. This will let you easily identify areas of missing data in your list.

AutoFilter is also very useful for finding certain types of inconsistent or incorrect data. For example, if you have a column for State that should only include two letter postal abbreviations, scrolling through the AutoFilter options will let you easily spot entries such as N.Y. or Okla. And a date with the year entered as 2105 instead of 2015 will stand out at the bottom of the filter choices. Once you find the errors you can filter on them so they are easily corrected.

If you watch the video you’ll see all of this demonstrated plus a couple of other really useful AutoFilter tricks.

It’s Price Book publishing week for one of my clients, and we’ve been making lists, and checking them twice. Or 3 or 4 times, or more!

When comparing the new prices to the previous prices, an Excel AutoFilter comes in handy. You can select the same product or model in each workbook, and easily compare item details. Yes, the widget prices went up a bit this year, so that’s why the assembled parts cost a bit more.

Record Count in the Status Bar

Sometimes when you select records with an AutoFilter, the record count appears in the Status Bar, at the bottom left. In this example, I was working with a small table, with 50 records, and only one column had a formula.

I selected File Folder in the Product column, and the Status Bar showed that 3 of the 50 records contained that product. So far, so good.

Status Bar Shows Filter Mode

Then I added another record to the table, and selected a different product from the AutoFilter drop down list. This time the Status Bar showed the rather unhelpful message, “Filter Mode”, instead of the record count.

Excel 2007 seems to handle this better, but in Excel 2003, and earlier versions, you might see “Filter Mode” if there are more than 50 formulas in the list.

When you apply an AutoFilter, the formula recalculate. If there are lots of formulas to calculate, Excel shows a “Calculating %” message in the Status Bar, so you’ll have something to entertain you while you wait.

Unfortunately, the “Calculating %” message interferes with the record count message in the Status Bar. If the record count message is interrupted, it shows the “Filter Mode” message instead.

You can’t change this behaviour, but there are a couple of workarounds that you can use to find the record count.

Use AutoCalc Instead

If the Status Bar shows “Filter Mode”, you can get the record count from the AutoCalc feature instead.

You’ll see the count of visible numbers in the AutoCalc area of the Status Bar.

Use the SUBTOTAL Function

If you’d rather have the record count show up automatically, you can use the SUBTOTAL function. It ignores the filtered rows, and calculates based on the visible rows only.

For example, with numbers in column D, this formula, with 2 as the first argument, will calculate the COUNT of visible numbers:

=SUBTOTAL(2,D:D)

If you want to count items in a column that contains text, use 3 as the first argument, and subtract 1 from the result, to account for the heading cell.

=SUBTOTAL(3,B:B)-1

Watch the Excel AutoFilter Video

In this very short video you can see my Excel AutoFilter experiment, and watch the Filter Mode message appear in the Status Bar.

There are no ruggedly handsome math teachers in this video, but it’s fun-filled and action-packed!

There are more Excel AutoFilter Tips on my Contextures website. ___________

As you can see, the order dates, order numbers, prices, etc. are all out of order. Let’s get started on running some sorting and filtering techniques.

Sorting Data

Go down to the Sort option – when hovering over Sort the sub-menu will appear

Select Expand the selection

The whole table has now adjusted for the sorted column. Note: when the data in one column is related to the data in the remaining columns of the table, you want to select Expand the selection. This will ensure the data in that row carries over with sorted column data.

Filtering Data

The filter feature applies a drop down menu to each column heading, allowing you to select specific choices to narrow a table. Using the above example, let’s say you wanted to filter your table by Company and Salesperson. Specifically, you want to find the number of sales Dylan Rogers made to Eastern Company.

To do this using the filter you would:

Go to the Data tab on Excel ribbon

Select the Filter tool

Select Eastern Company from the dropdown menu

Select Dylan Rogers from the Salesperson dropdown menu

Boom – you now have the exact number of sales Dylan Rogers made to Eastern Company.

The Sort & Filter Tool

In the following GIF, we can see how the Custom Sorting tool can be used to sort date ranges or price ranges.

But notice how this example is either/or. 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. We hope you found today’s tips useful. Now go out there and get your data sorted!

Use Learn Excel Now to help with all your Excel questions and training needs.  We’re not just experts in Excel, there is content, free resources, and training courses available for Word, Outlook and more.

Việc sử dụng Advanced Filter trong Excel thành thạo sẽ giúp bạn không cần phải thực hiện thủ công để tìm kiếm những giá trị phù hợp với điều kiện đặt ra mà tính năng này sẽ dựa vào điều kiện mà bạn thiết lập để lọc dữ liệu qua đó giúp bạn tăng hiệu suất khi làm việc với Excel.

CÁCH SỬ DỤNG ADVANCED FILTER TRONG EXCEL

Để sử dụng Advanced Filter, trước hết bạn cần thực hiện những yêu cầu sau:

– Tiêu đề bảng dữ liệu chỉ sử dụng 1 dòng duy nhất.– Trong bảng dữ liệu cần lọc không gộp bất kỳ ô nào.– Có ít nhất 3 dòng trống trên cùng bảng dữ liệu.

Giả sử bạn có bảng dữ liệu như hình dưới và bạn cần lọc với điều kiện Số tiền nộp lớn hơn hoặc bằng 15000.

Trước hết bạn cần tạo bảng điều kiện để lọc dữ liệu. Bạn chọn tiêu đề Số tiền nộp trong bảng rồi nhấn tổ hợp phím Ctrl + C để Copy.

Nếu lọc dữ liệu điều kiện OR thì sắp xếp theo chiều dọc. Nếu điều kiện AND thì xếp theo hàng ngang. Như vậy nếu cần lọc 2 điều kiện AND theo cùng 1 tiêu chí thì bạn phải dùng 1 tiêu đề cho 2 ô.

Với duy nhất 1 điều kiện thì bạn chỉ cần nhập điều kiện dưới ô tiêu đề của bảng điều kiện lọc là được.

Bạn nhấn chuột vào bảng dữ liệu chính rồi vào tab Data chọn Advanced.

Hộp thoại Advanced Filter xuất hiện với các mục tùy chỉnh khác nhau. Trong phần Action có 2 lựa chọn:

– Filter the list, in-place: Lọc và trả về kết quả ở chính bảng dữ liệu lọc.

– Copy to another location: Lọc và trả về kết quả lọc ở vị trí khác do bạn chọn.

Phần List Range chính là vùng dữ liệu bạn muốn lọc. Khi đặt chuột vào bảng chính và chọn chức năng Advanced Filter thì Excel sẽ tự động chọn bảng dữ liệu, bằng cách bao quanh bảng bằng đường nhấp nháy.

Nếu bạn thấy Excel chọn không đúng vùng dữ liệu thì bạn nhấn chuột vào nút nằm trong ô List Range rồi chọn lại vùng dữ liệu là được.

Tiếp tục cũng tại hộp thoại nhỏ này tại phần Criteria range, bạn chọn bảng phụ vừa tạo dùng để lọc dữ liệu.

Tiếp đến là cần tạo kết quả lọc ở một vị trí khác nên bạn sẽ nhấn vào nút Copy to another location, rồi nhấn vào 1 ô trống bất kỳ ngoài vùng dữ liệu. Cuối cùng, bạn nhấn OK để hoàn thành.