In this article
7 years back, when I started my career, I was introduced to Microsoft Excel; not that I was not aware of the same, but I never had to use it in college, so I knew the bare minimum i.e. just adding/deleting data.
However, that was not enough for a job, it would take me forever to understand the formulas or let alone the formatting. This all would at least take 30% of the day. Fortunately, I met a guy who was working as a Data Analyst there, and to this day, I have never seen someone working this fast on Excel. And that was my journey to use Excel not only professionally but personally as well, from planning my complete budget to making sure I am saving money every month.
Cut short to 2020, when I joined an organization who were using Google Service, which means no Microsoft Excel but Google sheet/ Spreadsheet, and I was kind of fine with that. Along with that, we had to work on Macbook instead of a Windows laptop which does not have an Alt Key, and trust me “ALT” key has been one of the major reasons the transition was not smooth.
But when you can see how much impact on the job you lose when manual work increases, it is nothing but frustrating.
This article is a part of the productivity hacks series, which aims to help in reducing operational work so that you can focus on the creative side of your job/life. The below posts focus on Google sheet however we will be adding the hacks for different tools as well.
Features and Formulas in Spreadsheet: Intermediate Level
Below are a few features/formulas which will make you an intermediate in Spreadsheet (Note that these are applicable to Excel as well):
Accessing Tool Finder without using your mouse ever
If you are a Windows and Microsoft Excel user, you know how easy your life is when you can access the whole menu(ribbon) with an Alt Key Shortcut. In Spreadsheet, the way of accessing the menu, which I learned last year, is not just limited to Mouse :/ But we do have the shortcut to access the menu.
Using ALT Key + / for Windows or Option Key + / gives you the same super superpower as accessing Ribbon in Excel. Just press the shortcut key, and you can add new columns/rows, apply borders, removing duplicates. When I had no idea about these shortcuts, I would be using the mouse, which was frustrating as I was used to keyboard shortcuts.
Ditching the Vlookup for Xlookup
Now don’t get me wrong, I agree Vlookup is usually the first complicated formula that we learn. However, it has its own limitations; because of the same, we would be using a combination of the Index and Match formula. However, that changed when Xlookup was introduced.
With Vlookup, you can only search your key in the first leftmost column of the data range, which is not how data is all the time, and there are chances of error if you make changes in the structure of the data; Xlookup you do not have to worry about changing the structure.
FormulaXLOOKUP(search_key, lookup_range, result_range) to replace Vlookup(search_key, range, index, [is_sorted]).
Example:
XLOOKUP("Apple", A2:A, E2:E) to replace VLOOKUP("Apple", A2:E, 5, FALSE)
Using Regex Formula:
Regex is something that looks too difficult, but the basics can be learned quite easily; I will write a different post altogether focusing on using Regex Formulas.
Data like roll number, user id, and series number usually have a pattern. For instance, Series Number can be different, but it can be a mix like 5 Digits + Model Number. So in Series number, we know that 5 Digits is a common pattern that can be helpful.
The regex formula for the same would be
this will extract all the common 5-digit patterns from the said columns. There are, for sure different ways to fetch the data, like using a mix of Index and Split Formulas, but this is the easiest.
Here is an example:
If you are curious enough about spreadsheets, excel spreadsheets, or google excel, there is this super easy course that can help you in learning Regular Expressions.
