Tag: Autofilter and Sort in Excel

Posted on

Be More Productive With Excel’s SORT function (2 of 4)

This blog is part 2 of a series that looks at the amazing functionality of some of the most popular Office 365 Excel functions. In part 1 the focus was on the FILTER function. Building on this, we will explore how useful the SORT function is in arranging data more efficiently.

sort

What is the SORT function

The SORT function is based on the standard way of sorting in Excel. It works by extracting the sorted rows from the source data and populating these rows to another sheet. As the sorting process does not affect the source data, the sorted data can be re-sorted without having to return to the original dataset. Thereby increasing your efficiency.

How is the SORT function different from other functions?

The SORT function is one of a number of Office 365 functions that behaves differently to all other Excel functions. With the SORT function, the results automatically ‘spill’ into all available cells below. In contrast, all other functions require you to copy the result down manually.

How to apply the SORT function

Let’s take some financial data where all records relating to ‘London’ need to be sorted (see below)

filter

  1. Copy the source data headings into another sheet and select the cell below the first heading – see below:

sort

sort

  • The 1st part is the ‘ARRAY’ or range of source data for selection
  • The 2nd part is the SORT INDEX which is a number assigned to the position of the field to be sorted from the left (e.g. 3 for ‘BudgetDepartment’)
  • The 3rd part is the SORT ORDER which is 1 for ascending or -1 for descending

The following data is now sorted by BudgetDepartment:

filter

Sometimes the date field will return dates as an unformatted number e.g. 43160. If this happens, simply reformat the date column back to ‘date’ (use Ctrl 1 to quickly reset the date formatting)

Another problem that could arise is if there are not enough free rows to populate the results. If any data is ‘blocking’ this space, then you will get a #SPILL error:

To remove the spill error, simply delete the data that is blocking the spill. Other Office 365 functions such as UNIQUE and FILTER also have this ‘spill’ feature.

Conclusion

The Excel SORT function is an amazing tool that allows you to sort data whilst leaving the original source data untouched. Consequently, the SORT function can help improve your efficiency and productivity when you need to refer back to your unsorted lists.

Posted on

Be More Productive With Excel’s FILTER function (1 of 4)

How often have you found when you filter Excel data, problems arise in getting it to work? This can happen when you are sharing workbooks or the source data changes frequently. The solution lies in Office 365’s FILTER function. This blog is part 1 of a series that explores the amazing functionality of some of the most popular Office 365 Excel functions starting with the FILTER function.

What is the FILTER function

The FILTER function is based on the standard way of filtering in Excel. It works by extracting the filtered rows from the source data and populating these rows to another sheet. As the filtering process does not affect the source data, we can share workbooks more efficiently. If you have ever struggled using the VLOOKUP to populate specific data into other sheets think again. Why not use the FILTER function instead? It does the same job but, unlike the VLOOKUP, it can return multiple ‘filtered’ rows based on an initial search. Plus, it’s easier to use. So, what’s not to like!

 

How is the FILTER function different from other functions?

The FILTER function is one of several Office 365 functions that behaves differently to all other Excel functions. With the FILTER function, the results automatically ‘spill’ into all available cells below. In contrast, all other functions require you to copy the result down manually.

How to apply the FILTER function

Let’s take some financial data where we will need to filter all records relating to ‘Rent’ (see below)

filter

  1. Copy the source data headings into another sheet and select the cell below the first heading – see below:

filter

  • The 1st part is the ‘ARRAY’ or range of source data to be selected
  • The 2nd part is the criteria TO INCUDE in the filter
  • The 3rd part (‘IF EMPTY’) is optional and returns an alternate answer if the filter criteria in part 2 does not find a ‘match’ e.g. if there are no records for ‘Rent’ then ‘not found’ is returned in the cell – if this 3rd part was not put in and the criteria was IT equipment, i.e.. not in the list, this would produce an error – see below:

function

By inserting the 3rd part “not found”, the above error will be converted to this text

As ‘Rent’ is in the list in column A, we get the following filtered results:

One problem that could arise is if there are not enough free rows to populate the results. If any data is ‘blocking’ this space, then you will get a #SPILL error:

filter

To remove the spill error, simply delete the data that is blocking the spill. Other Office 365 functions such as UNIQUE and SORT also have this ‘spill’ feature.

Conclusion

The Excel FILTER function is a great alternative to the VLOOKUP as it is easier to use and can return multiple results based on an initial search. Consequently, the function can help you to improve your efficiency and productivity when you are handling large sets of data.