Excel Tips and Tricks to Take Your Career Ahead

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 favourite 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.

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 analyse. This is a go-to feature for every professional who needs to make reports or analyse performance. 

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. 

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.

Extend formula across/down

The reason excel is so easy to use is because of its scalability. The extend formula allows you 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. 

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 optimised results. This option will ensure your margins are adjusted, the sheet fills your page and headers if any are in place. 

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 look up any value in a column or a table array and return the same 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.

& 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. 

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.

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. 

Hope these tips will help you get a better understanding of excel functions and how you can use this tool. You can also check our the various Online Courses offered by Great Learning and upskill today. Quantum Analytics offers courses with mentor-support and placement assistance. Follow Quantum Analytics NG on LinkedIn.

____________________

Jonathan Osagie is a Data & Business Intelligence Analyst interested in understanding stories that data tells, actions it inspires through analysis & analytics, and the success it brings to businesses by aiding strategic decisions.

#BecomeGloballyInDemand