5 Excel tips to take you from beginner to pro

Try implementing these tips and you will save a few hours helping you become a pro on your team.

  • May 9, 2021

Have you been assigned several projects where you have to retrieve some specific data from a large dataset? There may have been instances where you have to sum up the sales of a particular category, use the most famous VLOOKUP function to retrieve something, filter data to get some specific data points or you may need to copy and paste data from one sheet to another. You may have stumbled upon some alternatives in excel that will speed up your process but try these five tricks and you will see yourself saving hours.

The Index-Match combo function

This is one of our go-to functions and essentially substitutes the most famous and commonly used VLOOKUP. The logic follows:

INDEX("This is the column that has the output", MATCH("Select the value to match","column that has the value to match",0). In this case, 0 is equivalent to FALSE. In the below images, the first image of the dataset and the second one shows the steps of using the Index-Match function. The example below is related to retrieving the "status" of a particular title.

data-set

Index-match-formula

Select Visible Cells

While having a dataset filtered, you may want to copy only the cells with the filtered data and paste it into the desired location. The "select visible cells" function is exactly for this. This will help with copying only the visible cells avoiding accidents of copying all the hidden cells as well.

The famous "IF" function

Have you ever wanted to implement a function with some logic built into it? Example - "If cell A2 says "blue", then tag it as "sky" else tag it as "other". This video should help you with an IF function along with an AND function embedded into it.

if-function

VLOOKUP

This is the most basic function that every corporate company or even your own business will require you to know. Whether you are a Data Analyst, Director, or CEO. If you know this, you are already considered an excel addict. The way this function works is =VLOOKUP("this is the value that we want to lookup","this is the table that we want to look it up from","this is the column number that is needed as the output resides somewhere in this column", FALSE) - where FALSE means we want an exact match. 

In the below case, we want to lookup the status of the title "Spring up you bathroom under $120" article from the data set sheet. Starting from the column where the titles are listed, the status column is the 5th column (Remember, you always count the number of columns from the start of the selected range). 

vlookup-function

The + sign on the bottom right of a cell when you want to flash fill

After writing that function, you may be wondering how you can apply the same function to all the rows below. Simply double click that tiny green + sign that is on the bottom right corner of a cell (you will notice this sign once you select the cell).

plus-function

We hope these help you in saving time and delivering your projects before your due date. We are also always on the lookout for new functions every day and will publish a new lunch and learn on Microsoft Excel every now and then. Don't forget to swing by the lunch & learn section to be updated on more functions.

 

LATEST POSTS
post-thumb
  • Post By theEditr
  • Feb. 15, 2022
Face Globes - A beauty product by theEditr
post-thumb
  • Post By theEditr
  • May 31, 2022
From Paris the city of love
post-thumb
  • Post By theEditr
  • March 17, 2022
Your 10 day West Coast itinerary

TAGS
CATEGORY
SUBCATEGORY



You may also like