5 Excel Tips & Tricks you absolutely need
Through this article, we will review 5 of the most useful Excel ‘Tips & Tricks’ that we apply every day in the scope of missions for which Headmind’s clients trust our expertize. Therefore, this is not just a copy of your typical advices from the Web that are – most of the time – out of context … This article is a real feedback from consultants based on concrete experience in the field.
Obviously, this list is not exhaustive so it is up to you to be curious and discover what Excel has to offer in practice!
1. Create data groups by combining IF() formulas
Let’s take it easy first.
Many basic Excel formulas such as SUM()/SUMIFS(), COUNT()/COUNTIFS(), AVERAGE(), IF(), ISERROR(), VLOOKUP(), FIND(), MID() … are already very useful individually. Some of us use them every day. Remember that you can always optimize their effect by combining these formulas together:
Example:
=IF(logical_test_1; [value_if_true_1]; IF(logical_test_2; [value_if_true_2]… ; IF(logical_test_n; [value_if_true_n]; [value_if_false_n])))
This combination of IF() formulas will help you to create easily new groups and categories, which will help you to filter more easily or to better visualize you data in a Pivot Table, for instance.
2. Build filter flags with IF(), ISERROR() and VLOOKUP() formulas
Combining IF(), ISERROR() and VLOOKUP() formulas will help you – based on a list of references (table_array) – to identify and filter easily the data that you are looking for and you want to flag (“in”) in a database.
Example:
=IF(ISERROR(VLOOKUP(lookup_value; table_array; column_index_num; [range_lookup])); “out”; “in”)
This trick is especially useful when you are doing analysis within a large database and you don’t want to keep selecting again and again multiple references in the data filter.
3. Make your input data usable
Sometimes – not to say often – you have to work with data sources (i.e. applications) that are not built for Data Analysis purpose. Therefore, you will get file formats with data looking quite unusable, such as an XML:
On top of that, you lay have such a big volume of data that it makes it impossible to retrieve manually the info that you have to analyze, even if you had one month of free time.
In this case, Ctrl + H and Text to Columns in Excel can be some useful friends if you follow the next steps:
1) Open an Excel Workbook and select the column in which you put your input data:
Let’s say that you are interested in retrieving the data in the tag <GenInfoSortCode>.
2) Click on Ctrl + H:
a. Replace “<GenInfoSortCode>” by “µ”.
b. Replace “</GenInfoSortCode>” by “µ” as well.
Note: You can chose another character than “µ”, the important point here is to use a special and unique character that doesn’t risk to be found in other data (otherwise, they will be split in other columns as well).
3) Open the menu Data > Click on Text to Columns: this functionality gives you the possibility to split your data into several columns.
4) Select the option ‘Delimited’ as data type > Next
5) Select the option ‘Other’ as Delimiter > Finish
All the data of the tag <GenInfoSortCode> are now isolated in the column B of your Excel Sheet!
You can do the same process for as many tags as you want and create a usable data table for your analysis.
4. VBA: Recording a Macro
“VBA” stands for “Visuals Basic for Application”. It’s a programming language to create and automate specific tasks and functions within Microsoft Office programs such as Excel but also Word, PowerPoint, Outlook … A program written in VBA is called a Macro.
As it’s about ‘programming’, we know that many people can get afraid when you start talking about VBA in Excel. However, the basic concepts and logic are very easy to learn and we take the bet that once you’ll get used to it, you won’t be able to do without it!
First, let’s try something simple
1) Open an Excel Workbook.
2) Open the menu Developer > Click on Record Macro > give a name to your Macro (ex: “Macro_Record”). You can also define a Shortcut key to your Macro (ex: Ctrl + Maj + M).
Note: If the menu Developer is not available, go to menu File > Options > Customize Ribbon > Main Tabs > Select the option Developer > OK
3) In your active Excel Sheet, do any action of your choice : calculate a sum of figures, put a cell in color, insert a new shape, select a column and delete it …
4) Open the menu Developer > Click on Stop Recording
You just created a new Macro! Now you can launch it anytime you want by:
- Using your defined Short key (ex: Ctrl + Maj + M).
Or
- Open the menu Developer > Click on Macros > Select your Macro > Click on Run
Normally, your macro should (re)execute the exact same action(s) that you recorded (from your ‘Record’ click until your ‘Stop’ click).
Note:
- There is no real intelligence behind this Recording functionality. Therefore, it’s common to have some unexpected results when you relaunch your Macro. However, this functionality is still very useful to get a draft of Macro that you can upgrade afterwards.
- To open and edit your Macro, you can : Open the menu Developer > Click on Macros > Select your Macro > Click on Edit
This kind of trick with VBA can be very helpful when you have to repeat time-consuming actions of Data Cleaning, for instance. In case of you have to clean data in files with the same structure, don’t hesitate to record your cleaning process in a Macro and reapply it as much as you want. You will save a considerable amount of time!
5. VBA: Creating a new Excel formula – MultiVlookup
If you didn’t know it yet, here is the info: VBA also gives you the possibility to create new formulas in Excel!
Have you ever been slowed down or blocked in your work due to the limit of the standard formulas available in Excel? VBA can help you to solve this kind of frustrating issue by creating dedicated Functions.
For instance, you know that the VLOOKUP() formula can only get the corresponding value of the FIRST matched reference that it will find in the selected array. But what if the same reference corresponds to multiple values?
Below is a Function code that will allow you to gather ALL different corresponding values (separated by “;”) in one Cell. To use this new formula – we will call it MultiVlookup – in your Excel spreadsheets, please follow the next steps:
1) Open the Excel Workbook in which you want to use this new formula.
2) Open the menu Developer > Select Visual Basic
Note: If not available, go to menu File > Options > Customize Ribbon > Main Tabs > Select the option Developer > OK
3) In the List Project – VBAProject > Right Click on the VBAProject (Excel_Workbook.xlsx) correspond to your open Excel Workbook > Insert > Module
4) Copy/Paste the code below into a Ms Word document and then Copy/Paste into the blank Macro window:
Function MultiVlookUp(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer)
Dim i As Long
Dim Result As String
Result = “”
For i = 1 To LookupRange.Columns(1).Cells.Count
If LookupRange.Cells(i, 1) = Lookupvalue Then
If Not (Result Like “*” & LookupRange.Cells(i, ColumnNumber) & “*”) Then
Result = Result & LookupRange.Cells(i, ColumnNumber) & “;”
End If
End If
Next i
MultiVlookUp = Left(Result, Len(Result) – 1)
End Function
Then go back to you Excel Workbook and feel free to use it as a normal Excel formula, such as:
This kind of result is difficult to get with standard Excel formulas. Depending on your needs, it can help you to build a more useful and readable data synthesis than what you could get with a classical Pivot Table, for instance.
We hope you found these tips convenient and that they will be a benefit in your daily Excel use. Do not hesitate to share them with your friends or colleagues and get back to us in case it gave you other improvement ideas!