Introduction
Excel has become such a popular tool that it is no longer limited to Microsoft only. Competitors have designed their own version of Excel, with Google sheet becoming a new favorite with professionals. Having excel skills is a mandate for most corporate roles today, so we will help you figure out the top excel skills that you need to master in order to get ahead in your career.
Excel Tricks and Tips
- Pivot Tables
- Conditional Formatting
- Absolute References
- Extend formula across/down
- Print Opimization
- VLookup
- & and CONCATENATE
- Index-match
- Upper, lower, proper
Pivot Tables
Pivot tables are used to summarise, reorganize, group, sort, count and arrange your data. What’s more – you can even change rows into columns and vice versa using pivot tables. In short, you can use this feature to arrange, rearrange and display your data according to your requirement, making it easier to analyze. This is a go-to feature for every professional who needs to make reports or analyze performance.
Time taken to learn Pivot Table – 240 mins
Conditional Formatting
One of the most versatile and important features of Excel, conditional formatting is all about finding patterns in your data. You can highlight cells and instantly find patterns in the entire spreadsheet that unites the data or sets it apart. This feature again is a great help for those looking to derive reports out of data.
Time taken to learn conditional formatting – 180 mins
Absolute References
Absolute reference is a feature that allows the user to lock values in the spreadsheet so that rows and columns don’t get changed when they are copied. In the case of locking a column or row, you can use the dollar symbol in front of the number or letter. Absolute reference helps you in replicating the formulas across the spreadsheet. These locked values can be changed easily using the F4 toggling button.
Time taken to learn absolute reference – 180 mins
Extend formula across/down
The reason excel is so easy to use is because of its scalability. The extend formula allows you to drag any value/formula across the excel sheet to replicate it without any manual effort. All you need to do is click on the plus sign (+) and drag it down to copy the formula across cells.
Time taken to learn extend formula – 5 mins
Print Opimization
Printing excel can be a bit of a problem but a simple trick could help solve it. Instead of using Crtl+P to send the file to print, use the print preview screen for optimized results. This option will ensure your margins are adjusted, the sheet fills your page and headers if any are in place.
Time taken to learn Print optimization –
VLookup
Vlookup, short for vertical lookup is a function that searches for any said value within a range of values. Using vlookup you can make excel lookup any value in a column or a table array and return the same value from a different column in the same row. Vlookup is an extremely useful function frequently used by professionals who use excel to calculate and create reports.
Time taken to learn Vlookup – 15 mins
& and CONCATENATE
& and Concatenate are two excel functions that you can use to combine cells easily. The ‘&’ function allows you to combine two cell values and present them as one. For example, if you have different components of a name in separate cells (salutation, name, surname etc), using ‘&’ you can put them together. If you want to scale this activity up, you can do so using the concatenate feature. Basically, excel helps you in automating a lot of tasks.
Time taken to learn & and concatenate – 15 mins
Index-match
Using the index-match feature, you can search for any value in a table within excel. Index matching makes it easier to look up values across rows and columns and helps you present data in a filtered manner. It is actually a continuation of the vlookup function.
Time taken to learn Index match- 45 mins
Upper, lower, proper
A simple yet important trick to ensure correct sentence case is to use the ‘upper’, ‘lower’, and ‘proper’ functions in excel. These three are basically there to correct the casing of sentences, titles, etc. Using them will help you format your data properly and present them in a consistent manner.
Time taken to learn upper, lower, proper – 180 mins
Hope these tips will help you get a better understanding of excel functions and how you can use this tool. You can also check out the various Online Courses offered by Great Learning and upskill today. Great Learning offers courses with mentor-support and placement assistance.
