My job relies heavily on data. To be successful, I need to be able to track and sort copious amounts of information which can get a bit tedious and overwhelming. However, over my career, I have learned a few tricks to make things a bit easier. If your job requires you to manage excessive amounts of information, these tricks might help you improve your process!
Excel VLOOKUP function
This is one of the most useful functions within Excel, but it is also one of the least understood. VLOOKUP retrieves information from a table array based on a unique identifier. One of the constraints of this function is that the unique identifier needs to be the leftmost column in the table, and I’ve seen multiple people struggle with this when creating their VLOOKUP functions.
To explain the VLOOKUP function further, look at the below table:
Using the table above, imagine that you need to retrieve the price for ground meat. This is where VLOOKUP comes to your rescue.
In cell D6, the formula is =VLOOKUP(A6,Table2[#All],3,FALSE) which means that we will search product with id in cell A6 in the table of grocery store products (table 1). Then we will provide the column number that contains the value in which we are interested.
Excel INDEX-MATCH function
INDEX and MATCH functions within Excel can be used together to look up a value in a big table of data and return a corresponding value in that table. It is like VLOOKUP function, but it is more flexible.
For example, take a look at the following data:
First, we will use the MATCH function to find the row within the table that matches the unique identifier that we are searching for. For example, in C9 cell we added the formula =MATCH(C8,B3:B6,0) that returns row 3 when we search for employee id 30.
Then, we will use the INDEX function to retrieve the data of a particular row and column. For example, in cell C10 we added the formula=INDEX(B3:D6,C9,2)- that returns the first name which is Lisa. Similarly, you can get other columns from the row as needed.
Another important tip when working with the MATCH function is that you can do substring searches by concatenating an asterisk as a wildcard character.
For example, in the following sheet the value to search is in the G8 cell with the value set to “om” and to search the row, we will use the MATCH function in cell G9 set to =MATCH(“*” & G8 & “*”,C3:C6,0). In this case, we will first concatenate the wildcards and search the term “*om*” in the table and the best match is “Homer” so the row id returned is 2.
Multiple Desktops in Windows 10
One of the coolest things I recently learned is that Windows 10 has a feature to simulate multiple desktops. This is very useful especially when you need to use multiple programs or apps and want to group them based on different tasks.
How to open a virtual desktop:
- Open the new Task View pane by clicking the Task View button(two overlapping rectangles) on the taskbar, or by pressing the Windows Key + Tab.
- In the Task View pane, click New desktop to add a virtual desktop.
- If you have two or more desktops already open, the “Add a desktop” button will appear as a gray tile with a plus symbol.
You can also quickly add a desktop without entering the Task View pane by using the keyboard shortcut Windows Key + Ctrl + D. And if you want to switch between desktops open the Task View pane and click on the desktop you want to switch to. You can also quickly switch desktops without going into the Task View pane by using the keyboard shortcuts Windows Key + Ctrl + Left Arrow and Windows Key + Ctrl + Right Arrow. You can add an unlimited number of virtual desktops, Very cool, right?
Sometimes It’s Better to Use The Command Prompt
In my daily job I need to review information from multiple links and opening them one by one is time-consuming. One solution that has helped me is to open a command prompt and automate this process by preparing a list of commands with all the links that I will need to open and then simply execute them.
For example, open the command prompt by using the keyboard shortcut Windows Key + R and type CMD. When the command prompt opens, paste the list of commands and they will be executed in batch:
The result is that all the pages will be loaded in your browser at once. This is helpful when you have several links to load at once.
Copy vertically
We usually copy and paste information horizontally, but if for any reason you need to copy something vertically, Visual Studio Code is the solution.
Just add the cursor at the beginning of the text you need to copy.
Then press the keyboard keys Shift + Alt and hold them as you select the vertical info to copy using the mouse:
Then you can release the Shift + Alt keys. At this point, the vertical selection is completed, and you can simply copy and paste as needed.
Keep in mind that not all text editors support vertical selection, and this is why we use VS code today, but other text editors also have this capability.
With a little bit of luck, you will find one or two tricks in here that can help you manage information easily and quickly.
