Tag

Microsoft 365

Browsing

Dot function is used to retrieve the required information from a data type in a simplified manner. It basically used to find some data without infusing any complex conditional calculations and it helps the user to find the information spontaneously.

Syntax

Basically a dot function contains two elements which includes a ” . ” notation.

Arguments

  • Cell generated with data type: This refers to the country/city/company name. probably you can give a reference.

Field name: This refers to the required data field. for example: Population, area etc.

Example using (.)Dot Function

While extracting the required data using field values, this is an alternative way of retrieving data. A simple self-constructed formula can be inserted in the formula tab to get the required data. This feature can be applied by following the steps as listed below:

To retrieve the data, the formula must be applied on the formula tab. If the user needs the population/area of the countries in the provided list.

  • Step 1: Select the cells in the worksheet for which you want to return the area/population then the user must enter ” . ” along with the data field, a drop-down will be listed from which you can select the list of field you want to return. In this case, it is the population
    Formula used,
    =B5:B9.Population
Dot function_1

The result will be projected as shown in the image.

Dot function_2

Dot function is available only in Microsoft 365.

Note: In case there exists data in place of spilled range, a #SPILL error will appear. In simple terms, when the resultant area is not empty or consists of any value a #SPILL error is returned. This indicates that the range where the result needs to spill down is not completely blank. 

To more on New Dynamic Array Formulas – click here

To know more about Rich Data Types in Excel please click here and don’t forget to subscribe to our youtube channel to get regular updates.

Ever thought!! how to filter a huge data set? Well, you are in the right place. In this article, we will be discussing everything you need to know about Filter. So, Let’s see how it works.

Filter function – Introduction

Basically, Filter function enables a user to instantly filter a huge data set, based on a condition and pull out the information to another location or somewhere in the same worksheet. In most cases, this function acts as a huge time saver. If you are quite familiar with Excel functions, you can use the filter function to filter the data based on the criteria.

The fastest way to access the filter function is by selecting the data tab in the ribbons and clicking on the Filter option. Alternatively, we can also use the shortcut key Shift+Ctrl+L. The Filter Functions are available only in Microsoft 365.

SYNTAX OF FILTER FUNCTION:

=FILTER (array, include, [if_empty])

Here we have some Electronic components under Products, Say suppose you want to filter only the Wireless Keyboard from the product list. The below steps will guide you to process the filtering.

FILTER FUNCTION_1

The steps are as follows:

Step 1: Type =FILTER(
Step 2: In the following example, we use the formula = FILTER(B4: D18, C4: C18=G3,) to return all records for a wireless keyboard.
Step 3: Click Enter and you can see the data filtered.

FILTER FUNCTION_2

You can FILTER based on multiple criteria with the help of (*) operator.

To read more related articles click here

Subscribe to our YouTube channel for regular updates.

Practise Files

Looking for specific information? Are large datasets making it difficult to find the information? Thankfully, Excel made it easy for us to narrow down the search with the help of a massive tool Filter or AutoFilter. This article will help you understand the role of Filter in Excel.

tutorial of filer in excel

What is Filter in Excel?

  • Filter in excel is the easiest way to display the information which is relevant to us from the large dataset and hides all other data from our view.
  • Excel can filter rows by format, value, or based on given criteria.
  • By applying a filter, we can even copy, edit, print only the visible cell /rows without changing the entire list.

Key Difference between Excel Filter and Excel Sort –

Generally, many of us think filter and sort are the same, but there is an immense difference between them. The following are the key difference

  1. Excel Filter – when we filter our data it shows only the data which we need, remaining data are temporarily hidden from our view. For example to display only “alphabets” from the list (4,2,8, a, b,1,9) to (a,b).
  2. Excel Sort – when we sort our data, the entire array /table is rearranged, for example arranging a list in ascending order (8, 2, 3) to (2, 3, 8).

Common ways to add a filter in Excel:

  1. Be on the data, Go to Data Tab>> Click Filter under Sort & Filter option.
filter button on data tab
     2.   Be on the data, Go to Home Tab >> Click Sort & Filter > Filter.
shot & filter option

   3.   Press Ctrl +Shift +L key to add or remove the filter.

Pro tip: Ensure that datasets have a header row with the column names. A filter cannot be applied without a heading.

How to apply a filter in Excel?

  1. Be on the column where you want to filter and click, the drop-down arrow.
    Shortcut Key: Press Alt+ Down arrow key to display the drop-down list.
  2. Uncheck the Select all box.
  3. Check the boxes for filtering data and click OK.
filter in excel process

Note: It also allows you to filter multiple columns.

How to use a filter in excel?

Excel Filter provides a bundle of advanced tools that helps to filter specific data types such as text, numbers, and dates.
Let’s have a quick look at each option and see the steps to create a filter that suits your data type.

Text filters –

To filter a column based on the text-value, Excel filter provides plenty of advanced options such as

  1. Text filters that begins with or ends with a specific character(s).
  2. Text filters that contains or does not contain a given character or word.
  3. Text filters that equal or does not equal a specified character that exactly matches.

Excel enables the Text Filters option only when your column contains text values. The below image shows the entire text filter option.

text filters in excel

For example, let’s filter the rows which contain the “Keyboard” word.

  1. In the drop-down menu, select the text filters that Contains.
    Note:
    you can select any option as per requirement.
contains option in text filters

2.   A Custom AutoFilter dialogue box pops-up on your screen. In the box to the right of the filter, you can either enter the text, copy-paste text, or select the text from the dropdown lists and click OK.

custom autofilter dialogue box with contains option

3.   This Feature display cells which contains “Keyboards”.

custom autofilter result box

Filter column with Multiple criteria 

1. Goto text Filters >> Does not contain

does not contain option in text filters

2. A custom filter dialogue box pops up on your screen under that select And/Or button depending on your need, whether both or either criterion should be true
3. Select the comparison operator from the drop-down for the second criteria, type or select the value text from the drop-down lists. In this example, we will filter rows which “Does not contains” Keyboard and Home theatre.

custom autofilter with does not contain and does not end with

4. This function applies a filter by specified criteria.
The output looks like the below image, if you see clearly under the rows section there will be double lines which indicate some of the rows are hidden due to the applied filter.

result of custom autofilter

How to create a filter with wildcard characters?

The following are the wildcard characters that can be used for creating a filter.

Wild characters Description Example
? (Question mark) Used to match the single Character ?one filters “done” and “gone”
* (asterisk) Used to match any sequence of characters Mobile* filters “Mobile phone” and “Mobile charger”
~(tilde)
followed by *, ?,or ~
Used to get the cell which contains a question mark, asterisk, or tilde. When~? filters “When?”

Number Filter 

Number filter helps to manipulate numeric data in a variety of ways as follows:

  1. Filters a number i.e. equal or not equal to the given number.
  2. Filters a number which is greater than, less than, or between the specified numbers.
  3. Filters top 10 or bottom 10 values.
  4. Filters above average or below average values.

The below image is the overview of the Number filter.

number filters option

For example, to filter the data that contains the amount less than 2,000.

  1. Be on the numerical data to enable number filter, then click the dropdown in the column header, and point to number filters.
  2. In the drop-down menu, select the Number filters >> less than.
number filters option with less than

3. A Custom AutoFilter dialogue box pops-up on your screen. In the box to the right of the filter, you can either enter the number, copy-paste number, or select the number from the dropdown list and click OK.

custom autofilter dialogue box for less than

4. With the help of the specified criteria filter results in the output as below.

custom autofilter output

Date Filters 

Excel date filter provides a bunch of choices that allows you to filter records for a certain period.

By default, Excel data filter will group all dates in a given column with a hierarchy of years, months, and days. Date filters allow to display or hide data for a day, week, month, quarter, year, before or after a specified date, or between two dates. The Below image represents the entire date filter options.

date filter option

Generally, Excel filter by date works with a single click. For instance, to filter rows containing records for the current month, click Date filter>>This Month.

Assume that you own a computer accessories shop in the center of the city and you want to know the items sold between 1 st to 5th April of 2019. This can be done by applying a date filter, below are the steps to be followed

  1. Be on the date columns to enable date filter, then click the dropdown in the column header, and point to date filters.
  2. In the drop-down menu, select the Date filters >> Between.
between option in date filter

3. A Custom AutoFilter dialogue box pops-up on your screen. By default, Excel suggests using “is after or equal to” and ” is before or equal to” comparison operators. (Here, I have given 1-apr-19 to 5-apr-19)and click OK.

custom dialogue box for date filter
date filter output

Filter by colour

Excel can filter your data if it is formatted manually or through conditional formatting. The following are the most commonly used format in excel.

  1. Filter based on cell colour.
  2. Filter based on font colour.
  3. Filter based on cell icon.

How to copy data after applying filter?

  1. Select any filtered Column or cell and then press Ctrl + A to which selects filtered data including column header.
  2. Press Alt +; to select only visible cells ignoring hidden rows or Go to Home tab >> Editing group >> Find & Select > > Go to Special> Visible Cells only.
  3. Now you can copy and paste the visible data.

How to remove the filter?

To remove all filters from the worksheet.

Go to the Data tab > Sort & Filter group and click Clear or press Shift+ Ctrl+ L to apply or remove the filter.

filter removing option

Filter Function 

If you are quite familiar with Excel functions, you can use the filter function to filter the data based on the criteria.
In the following example, we use the formula = FILTER(B4:D18,C4:C18=G3,) to return all records for a wireless keyboard.
You can FILTER based on multiple criteria with the help of (*) operator.

filter function

To know more on Excel go through our Articles

Don’t miss out on free sessions on Excel and more. To register Click here.

Subscribe to our YouTube channel for regular updates.

Sorting our life might be difficult, but sorting an Excel sheet is a piece of cake. We always like to be sorted and not cluttered, so is it with data. Here are a few interesting concepts about ‘Sort’. Happy Sorting!

full guide to sort feature in excel

Sort in MS Excel

Sorting refers to presenting the data in a defined order. For example, let’s take a list of names such as Ram, Shyam, Aravind, and Shrey and we want them in ascending order. We can achieve the same using Sort in Excel, the result would be Aravind, Ram, Shrey, and Shyam i.e. a sorted list. Sorting can be done for a single column or multiple columns. Moreover, you can sort data based on a single criterion or multiple criteria. Sorting helps to analyze and detect duplicate values.

How to sort in excel?

Below are the two most common ways to sort data in excel. One from the Home Tab and other from the Data Tab.

1.Click on Home Tab >>Sort & Filter.

sort & filter option in home tab

2. Under Data Tab>>Sort & Filter

ascending order and descending order features in sort filter

The A to Z represents sorting in Ascending order and Z to A represents sorting in Descending order.

Now let us understand the different types of sorting.

Sorting Based on Criteria

Single Criteria Sorting-

This is the normal type of sorting, i.e. based on one criterion. In simple terms, the data is sorted based on one condition. Remember while sorting in ascending order numbers come first and then letters.

Let’s look at an example. Considering the below data, we wish to sort the data based on the Name in alphabetical order.

single criteria sorting example

Step 1 – Select any cell in the Name column.

Step 2– Go to Data tab in the ribbon>>Sort AZ .

sorting az from data tab

Below is an image showing the sorted data. We have highlighted the Name column for ease of identification.

sorted data output

Now, what if we wish to sort the data based on some other field/criteria. Say for the example Date and not Name. Simply select any cell in the Date column and then click on AZ or ZA ↓. Now your data will be sorted based on Date.

In the above example, we sorted the data based on one criterion. What if we wish to sort the data based on more than one condition? Let’s understand how can we achieve it using Multiple Criteria Sorting.

Multiple Criteria Sorting-

As the name suggests, our data is sorted based on multiple criteria.

Consider the same data set as above. Now let’s sort the data by Date and then by the Amount. Follow the steps as below:

Step 1– Select the data and then click on Data Tab>> Sort.

sort option in data tab

On clicking Sort, a dialogue box appears as below.

putting value in dialogue box

Step 2– Under Sort by Select ‘Date’ and under Sort On select Cell Values. Then under Order select Oldest to Newest.

  1. Sort by: Based on which field you want to sort. In this case, we firstly want to sort the data by Date.
  2. Sort On: Based on what are we going to sort. There are various options like Cell Colour, Font Colour, etc but for our example, we select Cell Values.
  3. Order: There are various options like A to Z or Z to A for text, Smallest to Largest or Largest to Smallest for numbers, Oldest to Newest or Newest to Oldest for a date. For this example, we select the Oldest to Newest.

Step 3 – Click on Add Level to apply the next criteria. Now select Amount under Sort by, Cell Values under Sort On and lastly Smallest to Largest in the Order.

applying criteria in dialogue box

Step 4– Click OK.

Below is the result. Firstly the data is sorted based on ‘Date’ in ascending order and then by the ‘Amount’ in smallest to largest order.

multiple criteria sorting result

The first criteria entered is called the primary criteria. In the above example, Date is the primary criteria followed by Amount. The data is first sorted based on primary criteria followed by other criteria in sequence.

Sort by column (top to bottom)

In all the above examples we had sorted our data in column order. Let’s look at an example. Considering the same data set as above, follow the steps to sort by column.

Step 1– Select the data and then click on Data Tab>> Sort.

Step 2– Select the Options as shown in the below image.

options tab in sort tab

On clicking the Options button we get the following pop up.

sort options dialogue box

Notice by default the sorting was done based on column (top to bottom).

Step 3– Under Sort by select ‘Amount’ and under Sort On select Cell Values. Then under order select Smallest to Largest. In summary, we are sorting the data based on Amount in ascending order.

Bellow image shows the sorted data. We have highlighted using a colour.

top to bottom sorted data

Sort by Row (Left to right)

Consider the below dataset. Let’s sort the data based on 11th row from Smallest to Largest.

sort left to right

Step 1– Select any cell within the data and click on Data>> Sort.

Step 2 – Go to Options>> Sort left to right.

sort options dialogue box left to right

Step 3– Under Sort by select the row you want to sort. In our case, we select the 11th row.

Step 4– Under Sort On select Cell Values.

Step 5– Under Order of sorting, select Smallest to Largest.

Sort dialogue box, row, sort on, order

Step 6 – Click OK.

Below image shows the result. We have highlighted the 11th row for easier understanding.

left to right sorted data result

Case sensitive-When you click on Options, Case sensitive option is displayed on the top.

case sensitive option in sort options

If your data has both upper and lower case, you need to be careful while sorting. If you want the data to be sorted based on case sensitivity, then you can select the case sensitive box. For example, if you have one item which u write both in upper and lower cases such as ‘Cash’ and ‘cash’. Then it will sort the item with a Lower Case first and then the item with Upper Case will appear next.

My data has headers

sort data has headers

The dialog box displays ‘My data has headers’ in the rightmost corner. If our table as headers than we can select the checkbox or else if there are no headers you can uncheck the box. What happens if your table has headers and you choose not to checkmark the box? This will sort your heading along with your data.

Let us understand the SORT ON options in detail. Why so? Because it will help us sort based on Cell Colour, Font Colour, Conditional Formatting Icon and more.

The Sort On has the following options:

  1. Cell Values– Sorts based on the values in the cell.
  2. Cell Colour– Sorts based on colour preferences.
  3. Font Colour– Sorts based on font colour.
  4. Conditional Formation Icon– Sorts based on conditions provided.
Sort on values

Sort based on Cell Values

Let us consider the following table and let’s say we want to sort the data based on Cell Values. What this means is the values in the cell are considered for sorting.

sort based on cell value

Sorting by Cell Value facilitates sorting a list of texts, numbers, or a date-time format. You can choose to sort the Cell Values either in ascending or descending order or based on custom lists.

Sort based on Cell Colour

Below is a data set with one column having coloured cells i.e. the Payment Method column.

Sort based on Cell Colour

Follow the steps to Sort based on Colour.

Step 1– Go to Data Tab>>Sort option.

Step 2– Under Sort by select Payment Method and under Sort On select Cell Colour.

Step 3– A new tab will appear towards the right. This allows you to select the first colour you want to retrieve based on the sorting. After which you can select whether the colour is to be on top or at the bottom.

Step 4– You can continue adding more levels deciding the order of colour based upon which the data will be sorted.

sort value by cell colour

Step 5– Click OK. And the data will be sorted based on Cell Colour.

sort results based on cell colours

Sort based On Font Colour

Below is a data set in which the text colour is different under the Payment Method. To sort the data based on the Font Colour follow the steps as below.

sort based on font colour

Step 1– Go to Data Tab>>Sort option.

Step 2– Under Sort by select the column. In this case, we select the Payment Method as the text under it has coloured font.

Step 3– In the dialogue box add the preference of the font colour you want.

dialogue box preference of font colour

Step 4– And click OK. Your data will be sorted based upon the Font Colour.

result based on sorted font colour

Sort based on Conditional Formatting Icon

Many a time datasets consist of icons produced by Conditional Formatting in Excel. You can sort the data based on these icons in a specific order.

The following data consists of icons and we wish to sort them in the order of red circle, orange circle and finally green.

sort based on conditional formatting icon

Step 1– Go to Data Tab>> Sort .

Step 2– A dialog box with multiple options will appear. Under Sort by select Icons and under Sort On select Conditional Formatting Icon. Now under the Order select the Icon based on your priority. In this case, we want the red circle to show first, so we select it and next to it select the option On top.

Step 3– Then you can add another level and mention the next Icon you want to sort. As per our example select the Yellow/orange and lastly Add another Level and let that be green.

add level in sort box

Click OK, the result will be displayed as:

result of sorted conditional formating icon

Sort Function and SortBy Function

Note: These functions are available only in Microsoft 365 and above.

Sort Function

The Sort Function sorts the data in a range or array. The result is a dynamic array of values.

Let’s look at an example to understand better. Below is our data.

sort function data in array

In this example, we want to sort the Name in ascending order. The steps are as follows:

  1. Type =SORT(
  2. Then select the range we want to sort. In our example, we will select the range as B8: B17 which is the Name column.
  3. Hit Enter and the data will be sorted in ascending order.
data sorted in ascending order

NOTE: If any changes are made to the source list after sorting, the same will reflect in the array of values.

SortBy Function

The SortBy Function allows you to sort the data based on the values in a corresponding array or range.

Let’s understand with an example. Consider the below data which we wish to first Sort by the ‘Date’ followed by ‘Amount’.

sort the data based on the values
  1. Type =SORTBY(
  2. The first argument is selecting the entire array or the data. In our case, it is B8: E16
  3. The next argument is to select the array or range to sort on. In this example, we wish to sort the Date first, so we select the range B8: B16
  4. The next argument is the sort order denoted by 1 or -1 for ascending or descending order.
  5. Similarly, we add the data range E8: E16 and sort it in ascending order by writing 1.
  6. Similarly, we can add more arguments.

The output is as below.

sortby function result

Sorting in Power Query

To sort the data in Power Query, firstly load the data into Power Query Editor.

Let look at an example. The below image shows data in the Power Query Editor and we will be sorting the Customer Name column in Descending order.

sorting in power query

To the right of the Name column, there is a dropdown box. Click on it and you will get various options. From them, you can select Sort Ascending or Sort Descending as per requirement.

Sort Ascending or Sort Descending

In our example, we select Descending. Below is the output.

power query result

To clear sort, you can click on ‘Clear Sort’ option from the drop down.

Sorting a Pivot Table

After creating a Pivot Table let’s say we want to sort the data in ascending order. You can find a dropdown box in the right corner of each column. If you click on the dropdown box you will be able to find the ‘Sort option’. You can select the basis you want to sort on as ascending or descending. To know more about the sorting option click here.

To get more insights on MS Excel, Subscribe to our Youtube channel.