Statistics for everyone: Excel and Statistics(5), filter

Excel and Statistics, everybody should know. : Contents

filter

Filters are a very helpful tool when it comes to picking out the data you’ve created, and they’re helpful when you put them to good use. (Example; filter.xlsx) However, I also find it useful to find out if there is any incorrect data than to pick out data.

Select one cell in the data, and then click <FilterŒ> under <Data>.

Note the lowercase ‘m’ and the blank.

Click on the small triangleŽ next to the field name to see what they are, which doesn’t distinguish between lowercase m and uppercase M, and you can find (Blanks).

Select (Blanks) Then click <OK>.

You can only collect and view those that have no value, check the ID, etc., and find the missing value again. At this time, you can check that the filter is applied by the funnel instead of the triangle in the field name, and you can know the row number because it has the original row number‘.

If you open the filter in column 4, there should be only 0 and 1, but there is an English letter I’. The actually 1, 7, and I are well confused, and the numbers 0 and the alphanumeric O are similar in appearance and keyboard position, making it difficult to find the mistyped one. Numbers are arranged from small to large, so you can know the minimum and maximum values, and even find letters that look like numbers.

Especially if you have a habit of preferring <center-aligned> it’s more difficult to find, but <filter> finds it.

In summary, <filters> are useful when looking for mistyped values and empty cells, but they cannot look for capitalized letters or not.

To prevent such problems, it is recommended to use <data validation>.

Let’s open a ‘Arthritis.xlsx’ file. Based on the value in column E, I want to filter out what is ‘Male’ with <filter>.

When the filter is applied, the ‘triangle’ changes to a ‘funnel’ Œshape, and the row number changes. In other words, after 1, 6, 9, etc., the row numbers will cross several spaces, and there will be several rows hidden in between.

In this state, if you place the cursor in one of the cells and make a full selection (Ctrl A), the filtered cells will be selected.

If you copy and paste it into another sheet, only the filtered data, that is, the male data, will be picked out.

For numbers such as ‘Age’, the ‘Number Filter’ makes it easy to apply commonly used filters such as ‘greater than or equal to’ or ‘Above Average ‘.

You can also use ‘Text Filters’.11/16/2022

Excel and Statistics, everybody should know. : Contents

=================================================