• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » General » 7 easy and powerful feature of Microsoft excel you may not know about

7 easy and powerful feature of Microsoft excel you may not know about

August 30, 2016 by techgoeasy Leave a Comment

Microsoft excel is used by almost everybody.Here are some of the cool Excel feature  which every body should be aware

  1.  Using Excel function to complete many task

Excel has powerful list of function to solve various problems and task.You can use sum,concatenate,average,if .The list goes on

excel_1

2) Auto Filter

This is one of the best function to use when playing with lot of data on the excel. You can filter any column and find the corresponding intersection very easily.

Just go to the top and click on filter, your excelsheet will converted into  filter form as shown below.

excel feature

3) Conditionally Format

We may be asked many time to present the data in such a form so that highlights are visible like  Who has the highest (or lowest) sales, what the top five are, etc.? Excel’s Conditional Formatting will do everything from put a border around the highlights to color coding the entire table. Use the Highlighted Cells Rules sub-menu to create more rules to look for things, such text that contains a certain string of words, recurring dates, duplicate values, etc. There’s even a greater than/less than option so you can compare number changes.

The simple operation is shown below, We colored  the row where E column is greater 2

excel feature

4) Great Excel Shortcut Keys

CNTRL B Bold the text in word  or bold the cell
CNTRL -k Add Hyperlink
CNTRL- S Save the file
CNTRL -1 Format the cell
CTRL+SHIFT+L Turn on/off the filter
F12 Save as
CNTRL + F2 Print preview
Shift + F11 Add new sheet
ALT+ ENTER Get a line break inside the cell
CNTRL+F4 Close the workbook
Ctrl+; Inserts today’s date.
Ctrl+Shift+: Inserts the current time (the colon is what is in a clock reading, like 12:00).
Ctrl+0 Hides the current column
Ctrl+9 Hides the current row
Ctrl+F6 Switches between open workbooks (that is, open Excel files in different windows).

5) Pivot Table

It is also one of the powerful feature in Microsoft excel to analyze and present the data

If you have well-organized source data, you can create a pivot table less than a minute. Here’s how:
1.Select any cell in the source data
2.On the Insert tab of the ribbon, click the PivotTable button
3.In the Create PivotTable dialog box, check the data and click OK
4.Drag a “label” field into the Row Labels area

Example

excel_4

excel_5

 

6) Using Macros

you can automate tasks in Excel by writing so called macros. First we need to enable developer toolbar to do it

Each Excel version has different ways to enable the developer toolbar

Excel 2010

In Excel 2010 you can display the developer toolbar the following way:

  1. Click the green File Button
  2. Press Options
  3. Make sure that the Customize Ribbon right menu item is selected.
  4. In the dropdown list called Customize the Ribbon, select All Tabs.
  5. In the group called Main Tabs, make sure that the option Developer is checked.

excel_6.PNG

Excel 2007

To display the developer ribbon, do the following:

  1. Click the Office Button
  2. Click the Excel Options button at the bottom of the dialog.
  3. Ensure that the Popular tab in the left menu is selected (se picture below)
  4. Check the option Show Developer tab in the Ribbon

excel_7

Now once the developer toolbar is visible , we can record any macro and then use it

 

7) Validate Data to Make Drop Downs

If you Creating a spreadsheet for others to use and wants that people should enter some valid values only then  it will be beneficial  to create a drop-down menu of selections to use in particular cells (so they can’t screw it up!), that’s easy. We can create this using the below steps

  1. Highlight the cell, go to the Data tab, and click Data Validation.
  2. Under “Allow:” select “List.”
  3. Then in the “Source:” field, type a list, with commas between the options. Or, you could click the button next to the Source field and go back into the same sheet to select a data series
  4. You can hide that data later, it’ll still work.

Example

excel_8

Hope you like these cool Excel feature and use it in your daily task .Please do provide the feedback

Filed Under: General

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • Oracle tkprof utility
  • What is Skew in Database?
  • how to find file based on size in linux
  • SQL trace, 10046 event in Oracle
  • How to use sed replace string in file

Copyright © 2023 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us