Having knowledge of Microsoft Excel is an essential and added advantage when your job profile is related to data handling. Let’s check out some of the features and functions that can help you master Excel.
#1 Go to Special
This option is especially helpful when you need to navigate to and select a particular or a range of cells within the sheet.
To access this option, go to Home tab > Editing > Find and Select > Go To special
It opens up a dialog box consisting of a number of alternatives to choose from as per your requisites. Select the preferred option and press on the OK button to get your results.
#2 Data Analysis
Excel is here to make your job simpler with the Idea Button. This feature helps you to understand the data, analyze it and suggests to you various innovative and useful visual options like Trendline Charts, Frequency Distribution Chart and Pivot Tables to represent them as well as shows you a set of possible outcomes. To use this option, select the range of data you would like to analyze in the worksheet and select Home tab > Idea Button.
#3 Named Range
This feature is all about specifying a certain name to a single or a group of cells in the worksheet which helps you to identify and refer to whenever required, like using the name of the cell in a calculation in order to refer to its values present in the cell. To name a cell or cells, click on Formula tab > Define Names > Define name
A dialog box to define the name will appear. Fill the fields and click Ok. Now the selected cell that you have named can be easily referenced.
#4 Data Validation
This feature is unique and essential when you are dealing with data handling. It restricts you from what kind of data you can enter in your worksheet or the values one can insert. You can add dropdowns to help you with the validation.
Select the cells you want to apply, then select Data tab > Data Validation. A Data Validation dialog box opens up. Fill up the fields as per your requirement and click OK.
You will find 3 separate tabs which you can use to customize as you please. There is also an Error Alert option which will help you to display an error message each time an invalid value is inserted.
#5 Automatic Abbreviations
By using this feature of Excel, you can create your own list of abbreviations and assist you in typing smoothly, correctly and at a rapid pace.
You can create your list by selecting Home > Options
Excel Options dialog box opens up. Select Proofing on the left hand side panel. Now on the right hand side, under the AutoCorrect options heading, select the AutoCorrect options button.
The button opens up to another dialog box where you can customize your typing options.In the first tab named AutoCorrect, type your desired abbreviations under the Replace field and the full form under the With field. Click on the Add button at the bottom and click OK to confirm. And you're done. Each time you type the abbreviation, the full form will be replaced automatically, ultimately saving you time and energy.
#6 Consolidate
This is a very useful optionhandling large amounts of data and usingto use specific functions like Sun, Count, Average, etc. There's no more need to manually insert formulas or sought through individual worksheets. All you need to do is select the range of cells you need and consolidate them into your required area.
This is how we can use this option:
Suppose we have two sets of data that we need to add in our third sheet. Go to sheet 3, Data tab > Consolidate.
In the Consolidate dialog box, select the function you require, and browse the references, Add them to show in the All references section. Once done, click OK and you will get the results in the third sheet automatically. This process is so much easier and time saving as well.
#7 Conditional Formatting
This feature helps to highlight individual cells based on specific criteria and visually differentiate between data. To access this option, select Home tab > Conditional Formatting. You will come across various options which you can choose from and customise as per your requirements.
#8 Flash Fill
This feature helps in automatic data filling and assists to fill your data based on previous actions. Access it from Data tab > Flash Fill (shortcut: Ctrl+E). When you input a value in the first row, excel notices your pattern and fills up the following rows accordingly. This saves time and gets the job done accurately.
#9 Sparklines
These are small charts that you can easily insert in individual cells as per requirements.
Select Insert tab > Sparklines > Line / Column / Win-Loss. Click on any one type and it will open up a Create Sparklines dialog box where you can place the date and location of your sparkline insertion. Click Ok and it's done.
