Excel Features and Formulas everyone MUST know !!!

Everyone out there is bombarded with the information available over the internet and not able to identify what really is IMPORTANT to us !!!

Yes…there are CHAOS everywhere

When you are using / starting to learn Excel you might be wondering what are those features or formulas (functions) in Excel I MUST learn to become master. This is similar to studying any subject. There would be some topics which are more important and have more weightage in exams. You would then first try to learn those topics and rest if time permits. Same should be with Excel as well.

People have written 1000 pages books, there infinite blogs, videos, podcasts, etc. out there on internet which talks everything about Excel. But hey stop !!!

Do you know what are the MOST IMPORTANT things in Excel?

I bet you would answer few of them and few of items I am going to list in this blog will be completely new to you.

This blog will just talk about what are the features and formulas everyone MUST know about Excel. And the I will provide relevant link to the best material already available on internet there. Yes, I will just briefly mention these features and formulas here & provide you a link to BEST material on internet.

So first thing you need to do…

Bookmark this page !!!

So that you do not need to save multiple pages / links anywhere else. This will be one single blog which will give you link to all the BEST material on these Excel MUST know features and Formulas.

 

I have divided this post into TWO separate sections:

  • Excel Features – These are built-in feature which can be used with just mouse click. No syntax or code to remember.
  • Excel Formulas – This will list all the formulas which you should know even if you woke up in midnight and start working on excel.

Each of above sections is then further divided into:

  • Basic
  • Intermediate
  • Mastery
  • Parallel tools to learn

So let’s get started

Here is list of EXCEL MUST FEATURES:

Basic Excel Features to know:

  1. Keyboard shortcuts
    1. Navigating in Excel
    2. Selecting data in Excel
  2. Golden Rules to follow in Excel
  3. Copy and Paste
    • Paste Special – Values only, Formulas, Formatting, Comments, etc.
    • Paste live picture
  4. Find and Replace with options using formulas, cell formatting, wildcards, etc.
  5. Sorting data with custom options – Columns and Rows, Case Sensitive, etc.
  6. Customize Excel ribbons and quick access toolbar
  7. Auto filters – correct method and using cell value
  8. Advanced filters
    • Multiple criteria
    • Unique items only
  9. Watch Window
  10. Compare workbook side by side
  11. Go To Special
  12. Fill Series of values
  13. Remove Duplicates
  14. Flash Fill
  15. Relative and Absolute referencing in Formulas

Above features are MUST know if you really want to achieve mastery in using Excel. These features are already available in Excel ribbon or can be added manually to ribbon if hidden..

After you are comfortable with above features you should move on to learn next topics.

Intermediate Excel features MUST know:

  1. Calculating Settings in excel – Manual or automatic
  2. Regularly used formulas – check formula section for this.
  3. Conditional Formatting
    • Data Bars
    • Heat Maps
    • Highest, lowest, equal to or other formula based formatting.
  4. Table feature
    • Structured formula reference in Table
    • Copying formula in Tables
    • Advantages and limitation of Table
  5. Text to columns
  6. Named Ranges
  7. Slicers in Table
  8. Built in Charts – Bar, Line, Combo, Waterfall, Funnel, Scatter, etc.
  9. Sparklines
  10. Formula Auditing
  11. Error checking
  12. Goal Seek
  13. 3D Consolidate
  14. Printing, Sharing and Protecting workbooks

Once you have reached this stage where you have learned about all these features you will feel more confident and work more effectively in Excel.

Now below features are things which will give you Mastery over Excel:

  1. Pivot Table
    • Calculated Fields / items
    • Get Pivot Data
    • Distinct Count, Average, Difference from first, % of total, etc.
    • Data Models in Excel 2013 onwards
  2. Customizing built in charts for interactivity using
    • Pivot Table
    • Slicers / Timeline Slicer
    • Form Controls
  3. Custom Number Formatting
  4. What if analysis using Data Tables & Scenarios
  5. Array Formulas
  6. Inquire tab for formula and workbook auditing

After having learned these features and reached this far I can bet that you will give tough competition to Excel pros you may know. But hey don’t stop here…!!! There are lot more thing you can still learn about Excel in parallel to these features which are listed here

  1. VBA (macros)
  2. Power Query – For Training
  3. Power Pivot
  4. Power BI

More on above topics in next blog.

 

So guys tell me what are other features you feel should be included in above list and what are the best sources to learn those features in comment section.

 

p.s.: relevant links and Excel formula list will be updated soon.

 

 

 

Leave a Reply

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