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:
1. Flash Fill
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.
2. Advanced Filter
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.
3. Use Query Editor to perform regular cleansing tasks
Query Editor is a separate program for cleansing data and which is accessible from the Data Tab in Excel. You can connect directly to a range of data sources such as SQL, Text and Azure outside your Excel workbook as well as using the dataset on the current worksheet.
Once your dataset is loaded into Query Editor, you have access to a much wider range of cleansing tools to help prepare your data for analysis. It offers all the usual options such as Replace Values, Remove Duplicates or Split Columns as well as tools you wouldn’t see on the standard Excel Ribbon such as Unpivoting, Append and Merge, Remove Blank Rows and many more.
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.
4. Array Functions
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.
Our workplace Data Analytics training programs not only help learners to build confidence with best practices on programs such as Excel, Power BI and Tableau, but also empowers learners to extract data insights that drive real value at their organizations.
More blogs, webinars and news
About the author
Yvonne will make your data sing, help you tell data-driven stories and support you in making better data-informed business decisions.View Yvonne Gamble’s profile