Top 8 # Xem Nhiều Nhất Bỏ Nút Filter Trong Excel Mới Nhất 6/2023 # Top Like |

Using Autofilter In Excel 2010

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.

Excel Autofilter Shows Filter Mode – Contextures Blog

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:


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.


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. ___________

Sorting And Filtering Data With Excel

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.

Tạo Nút Bấm Trong Excel Để Chạy Các Lệnh Lập Trình Tự Động

Trong Excel cho phép bạn tạo ra các nút bấm, hoặc các điều khiển để có thể thực thi các câu lệnh nâng cao. Các lệnh này được lập trình bằng ngôn ngữ VBA, và được gọi là các câu lệnh Macro

1 Bật thanh công cụ nâng cao dành cho nhà phát triển nhấn vào File/ Chọn Options

Mặc định Excel chưa có thanh công cụ Developer, bạn phải bật thanh công cụ này lên một lần duy nhất. Các lần sau Excel tự động ghi nhớ và bạn có thể sử dụng

2 Hộp thoại hiện lên chọn Customize Ribbon/ đánh dấu chọn vào nút Developer

3 Lúc này trên thanh công cụ của Excel sẽ có thêm một Tab Developer

Trên thanh công cụ của Excel sẽ xuất hiện thêm một Tab mới đó là Developer, trong này có các tùy chọn để lập trình câu lệnh Macro

4 Để tạo nút nhấn đầu tiên bạn chọn Tab Developer/ Chọn Insert/ Chọn biểu tượng nút bấm ở dưới mục Form Control

Ngoài việc tạo ra các nút bấm, bạn có thể tạo ra nhiều nút lệnh khác như: danh sách, dấu chọn, chúng tôi

5 Lúc này con trỏ chuột sẽ hiện dấu mũi tên +, bạn vẽ nút nhấn tại vị trí bất kỳ trên file excel

6 Hộp thoại hiện lên bạn cần đặt tên cho lệnh Maco sẽ thực thi, hoặc để tên mặc định của excel tự phát sinh. Sau đó nhấn OK

Kết quả

7 Để nút nhấn có thể thực thi lệnh tự động khi được nhấn vào

Các bạn nhấn chuột phải vào nút bấm, chọn Assign Macro…

8 Hộp thoại mới hiện lên Chọn New

9 Màn hình để gõ lệnh code VBA hiện lên

Các bạn thực hiện các câu lệnh lập trình VBA vào khu vực con trỏ chuột đang nháy. Ở đây, tôi thực hiện một lệnh xuất ra câu thông báo “xin chào macro” hiện lên khi nút được nhấn

10 Nhấn vào nút lưu, đóng cửa sổ và kiểm tra kết quả

Kiểm tra kết quả khi nhấn vào nút vừa vẽ ở bước trên, sẽ xuất hiện hộp thoại xin chào

Danh sách các bài học Lập trình lệnh VBA trong Excel miễn phí của Tin Học Sao Việt