Microsoft Excel Functions that made me Excel Pro !
Microsoft excel and word are the most used software in schools, universities and later in career life. I have grown with using excel so much that now I use It for both personal & work-related purposes. Over the decade of learning and using it, I believe in constantly learning more and improving the usability of this software. I have discussed below few of the basic and life changing functions that can absolutely convert the type of your working style and improve the efficiency of your work.
1- Data Cleansing
Data cleansing function in Microsoft Excel is one of the best functions and I was not aware of this function the last year. Ever since I came across this function, I have been using them and they have reduced so much of my working time. If you are not aware of this, keep reading and start using immediately as I assure you the efficiency of these.
Major functions include:
- Clean Function: Cleaning large data sets and eliminating additional unnecessary characters in individual cells or a number of cells
- TEXT Function: Enable text data to be updated, manipulated, and checked to improve the usability of text data in excel. RIGHT function lets you pick a substring from a set and returns the number of characters from a series, beginning at the end of the string.
2- VLOOKUP & HLOOKUP
The VLOOKUP function is something that I am proud of myself when I learnt it first while working for an audit firm. It is a super detailed function with a huge usage advantage. When there is a ton of data and you want to find the details of a particular value, it is just not easy as human error can still occur if you are checking one by one cell. For this purpose, there are two functions that we can use, VLOOKUP and HLOOKUP means vertical lookup and horizontal lookup, respectively. Personally, I was only aware of VLOOKUP function until this year when I came across the HLOOKUP and the whole perspective of using these lookup formulas widen up my work flexibility.
The VLOOKUP function allows you to look for a value that you need to find in that Excel table or the selected data on the Excel sheet. I have been using this function for over a decade, it never fails to deliver in speedy way. It looks at a value in one column and finds its corresponding value on the same row in another column. This function is used for searching among structured datasets.
VLOOKUP stands for vertical, which means the data in the table must be arranged vertically, with data in rows.
=VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
=VLOOKUP (value you want to look up more information around, table you want to look up from, column you want to return from, false)
The HLOOKUP function allows you to search and retrieve data from a given table list. The “H” in HLOOKUP stands for “horizontal,” where in the first row of the table, lookup values emerge, heading horizontally to the right. HLOOKUP allows uncertain and correct matching, and support partial match seeking. In the first row of a table HLOOKUP looks for a number. It retrieves a value from the specified row in the match column. Using HLOOKUP in the first row of a table where the lookup values are placed. Using VLOOKUP in the first column of a table where the lookup values are placed.
=HLOOKUP (value, table, row_index, [range_lookup])
Where;
value — The value to look up.
table — The table from which to retrieve data.
row_index — The row number from which to retrieve data.
range_lookup — [optional] A Boolean to indicate exact match or approximate match. Default = TRUE = approximate match.
To ensure these functions are working without errors following must be taken care of before applying the formula.
• Table or data must be structured
• Always finds the first match
• Named ranges make VLOOKUP easier to read (and more portable)
- #N/A! error is displayed when text not found
3- Make changes in sub-sheets at the same time
One of the best functions in MS Excel is changing the data of excel sheets at the same time. The tip is to prepare the data of same nature in all the sub-sheets on same cell address. That means if you are preparing a data analysis of products sold and there are 5 sub-sheets in the excel file, distributing 5 different types of inventory details that were sold, common descriptions or data should be entered in the same cell address of all the sheets. Later if needed, you can change the data at once by following these steps:
- Select the 1st worksheet you want to edit, press shift & hold and select other sheets you want to edit.
-Make changes on the 1st worksheet & enter.
4- Total and Sub-total function
Most of the people are aware of total function or with the shortcut of “ALT+=”. However, if you are using filter and you want to have the total of only viewed cells, a subtotal function is highly useful in that case, specially.
In case you don’t remember these formulas or short cuts, there are button on the top bar of “Autosum”. If you are using this button on the table without filtering data, an auto total formula will be applied and in case a filter is applied, the formula of subtotal will be applied automatically.
In the Subtotal feature in Excel, you have the ability to take a big spreadsheet and split it down by parts ( i.e. subtotal by date or year) to help give you a clearer understanding of how the data is evaluated. This function can be fantastic and it will save you a lot of time or end up costing you the whole day if you don’t do it right.
