Excel is one of the most widely used software programs for Data Analysis. VLookups, Pivot Tables and Conditional Formatting are the staple diet of anyone who regularly uses Excel.

However, there are many other features available which can help you be more productive and efficient as you work with the data.

Here are just 4 lesser known tools which every Data Analyst should be familiar with:

Flash Fill will detect a pattern in what you are typing, based on the values in the adjacent column and fills it down the rest of your column. It will often do the job of a more complex Text Function to convert case, extract characters from the left/right of a text string, or concatenate several text strings together. Start by typing the value you want and as you start into the second cell, the fill values should appear. Simply press RETURN to fill.

Advanced Filter allows you to perform more complex searches. You first need to set up a criteria range where you will indicate what values you are looking for and which columns you are searching in. Entering criteria this way, offers more flexibility than the standard AutoFilter. For example, you can search using OR across columns – so we could search for any rows which have over 10,000 in either column B OR column C.

Even the regular tools have additional features. For example, there are so many ways you can Split Columns: Digit to Non-Digit, Uppercase to Lowercase or use a standard delimiter but specify which occurrence, so split on the space character but the last occurrence only.

From Query Editor you can also profile your data – this is really useful to help identify where the issues are so you can then resolve them, for example, with one click you can discover what percentage of a column contains errors, blanks or valid data.

If you are using Office 365 or any version of Excel from 2019 onwards, you will have access to a host of new functions, including Dynamic Array Functions. Array functions have been around for a long time but these new ones are much more dynamic and are one of the biggest changes to Excel formula and function writing in many years. Here are just 2 new array functions which make extracting and filtering data much more dynamic.

UNIQUE – creates a unique list of values from a column or range. Traditionally we would have copied and pasted the list and then used Remove Duplicates to a unique list, but that is not dynamic, so if the original list updates, then the unique list still remains the same, whereas the formula will automatically update.

FILTER – will filter a range of data based on criteria and extract the results to a new location. This provides you with a dynamic way to filter and extract a subset of data, so if the original data updates, the filtered subset will update also.