OmniDocExcel table data filtering method: conditional filtering and statistics
Explain the method of filtering table data: use automatic filter to find the filter button, use text, numbers, and date conditions to select rows that meet the requirements, and then count the number and total of the filter results. Suitable for readers who organize sales, lists and reports on a daily basis.
Key points of this article
- You can use automatic filtering for a single condition. The relationship between multiple column conditions is "and", and the relationship between multiple selections in the same column is "or".
- Before filtering, check whether the table header is repeated and whether there is a blank row in the data area, otherwise the filtering range will be disconnected.
- To count the number of filtered items, use the SUBTOTAL function. Ordinary counting functions will also count hidden rows.
- If dates and numbers are in text format, conditions such as "greater than" will miss rows. The format should be unified first and then filtered.
- The PDF table must be OCRed first and exported to Excel. The numbers must be checked item by item before filtering.
Many people have a sales detail or customer list with hundreds of lines in their hands. If they want to find records in a certain region, a certain month, or the amount exceeds a certain number, they can only look through the lines line by line with the naked eye. The method of filtering table data is to solve this problem: let the software only display rows that meet the conditions, temporarily hide the remaining rows, and then count or sum the remaining data.
This article is written for people who need to organize tables but don’t want to manually search for them every time. After reading, you will know where the filter button is, how to set a single condition and multiple conditions, how to count the number of records after filtering, and where to start when the results are incorrect. The operations in this article take common desktop spreadsheet software as an example, and the methods for online spreadsheets are generally the same.
How to filter table data? One sentence conclusion
When you only select data in one column, automatic filtering is enough; when there are many conditions that need to be met at the same time or when you choose one of the two, you can use a combination of multi-column filtering and multiple selection in the same column.
| your situation | Recommended practices | reason |
|---|---|---|
| Select data by only one column, such as a certain city | Automatically filter, check the specific value of the column | Fewer operations, the results are clear at a glance |
| By numerical range, if the amount is greater than 1000 | Choose greater than or between in the numeric condition | Avoid manual comparison, boundaries are less error-prone |
| Satisfies two columns at the same time, for example, the region is East China and the month is March | Set filters on two columns separately | There is an "and" relationship between multiple column conditions |
| Just satisfy any value, such as Product A or Product B | Check multiple values in the same column | There is an "OR" relationship between multiple selections in the same column |
| Need to know the number or total of filter results | Filter first, then use a function that only counts visible rows | Ordinary counting functions will also count hidden rows |
Before you start: Confirm these points first
- Is the header standardized?: Each column needs to have a unique and clearly written column name. There cannot be a whole row of blanks below the header, otherwise the software will split the data into several segments and the filtering range will be incomplete.
- Whether the format of the same column is uniform: Some dates are text and some are in date format. The "greater than a certain date" condition will miss text rows. You can pay attention to the alignment. Text is left-aligned by default, and numbers and dates are right-aligned by default. Inconsistent alignment often indicates mixed formats.
- Whether the number is really a number: If the amount contains the word "yuan", extra spaces or invisible characters, the software will usually treat it as text, and errors will occur in the summation and numeric conditions.
- Whether to keep the original data: Filtering only hides rows and does not delete data, but when copying the filtering results, only visible rows will be copied. Try to save a separate copy before proceeding.
- Whether the data comes from PDF: If the table only has a PDF version, it needs to be converted to table format first and then filtered. The PDF editor of the OmniDoc desktop client supports OCR recognition of scanned PDFs and exporting them to Excel. OCR results require manual verification, especially numbers and decimal points.
Steps to filter and count quantities in the table based on conditions
- Select any cell in the row where the header is located, or select the entire data area (including the header).
- Find the filter function in the toolbar and enable it. A drop-down arrow will appear on the right side of each column in the table header.
- Click the drop-down arrow of the column you want to filter, first uncheck all the boxes, and then check the specific value you need; if it is a number or date, select the conditions such as greater than, between, etc. and enter the value.
- If you need the second condition, repeat the previous step in another column, and the conditions in both columns will take effect at the same time.
- View the results: Only the rows that meet the conditions are displayed, and the row numbers will be skipped. The skipped rows are hidden rows.
- Count the number of items and totals: Enter the SUBTOTAL function in a blank cell. For example,
=SUBTOTAL(3,A2:A500)counts the number of text contents, parameter 2 counts the number of digits, and parameter 9 counts the sum. Such functions only count visible rows. - If you want to save the results, select the visible area, copy it, and paste it into a new worksheet. Only the rows displayed after filtering will be pasted.
- After the verification is completed, enable or disable the filtering function again to clear the filtering and restore all data.
Check each item after completion
| Check item | What do you think? | What if there's a problem? |
|---|---|---|
| Whether the number of rows is reasonable | Compare the results of the statistical function with the number of rows counted by the naked eye | Re-check the condition to see if a value is overchecked or missed. |
| Are dates and numbers identified? | Looking at the alignment, most of the text numbers are aligned to the left. | Re-filter after the unified format, and then see the change of the result. |
| Whether the data area is complete | See if there are blank lines or merged cells below the header | Unmerge and delete blank lines to ensure that areas are continuous before filtering |
| Is the total correct? | Compare the results of SUBTOTAL with the lines of manual sampling | Confirm the function parameters. Do not use a function that only counts all lines. |
| Numbers converted from PDF | Spot check several places to compare with the original PDF | Back to the original document correction, both OCR and machine translation results have to be checked manually. |
# which method is suitable for you?
- only once, with a small amount of data : you can use automatic filtering directly, and you don't have to write a formula after reading and clearing it. - Statistics need to be repeated regularly : put the SUBTOTAL formula in a fixed position, and re-filter the data after each update, and the statistical results will change accordingly. - only PDF version : first do OCR and export to a table, then filter, and check the key numbers one by one.
# frequently asked questions (FAQ)
1. What is the reason for the blank display after setting the condition?
Most of the conditions are too narrow, or the numbers and dates are in text format. Clear the filter, check the format column by column, and then reset the criteria.
2. Why does COUNTIF count more entries than the screening results?
COUNTIF counts all rows, whether they are hidden or not. To count the rows that are visible after filtering, use the SUBTOTAL function.
3. Can you filter by cell color?
Some versions of automatic filtering support filtering by color. If your software does not have this option, you can add a column next to it, mark the eligible rows with a formula, and then filter by this column.
4. After filtering out the results, can you directly delete the lines that do not match?
Direct deletion is not recommended. It is safer to copy the visible results to the new worksheet and check it correctly before processing the original table.
5. Can online forms do the same screening?
Most online table tools have filtering capabilities, and the entry is usually near the header. The location of the specific button varies from tool to tool, but the logic of the filtering is the same as the steps in this article.
# Summary
The method of table data filtering can be summed up in three steps: standardizing the header and format, setting conditions with automatic filtering, and then using SUBTOTAL to count visible rows and check the results. After taking these three steps, most of the daily selection and statistics can be fast and accurate.
