Advanced Excel Tutorial(How to learn more about excel)

Advanced Excel Tutorial – This post helps audience to learn advanced features in excel with guided description and practically done screenshots.

Searching for – Advanced Excel Tutorial..

Excel is a software application which has unlimited depth and complexity.It helps to manage and analyze various types of data.With the knowledge of excel a person knows how to gather,structure and present the data in a more impressive way. Similarly this article also focuses on how to learn more about excel and present an eye-catching data rather than a simple one.

In spite of Excel formulas there are more advanced features which can be used in your day to day official work.As most of us are aware that Excel is a major tool for data entry so having depth knowledge about this tool will improve your job opportunities.

So let’s start advanced excel tutorial !!!

PIVOT TABLE

Pivot Table automatically summarizes your data. They arrange and rearrange(or pivot) statistics in order to draw attention to useful information.Below is the data I will use for creating Pivot Table.

Advanced Excel Tutorial-Datasheet

Steps to create a Pivot Table

  • Click on Insert->Pivot Table
  • A pop-up appears asking to “Create Pivot Table” as shown below
How to learn more about excel?-Pivot Table
  • Select a table or range and then click on OK
  • After that Pivot Table will be created in new sheet with Pivot Table Fields present in right side of the sheet as shown in the below image.
Advanced Excel Tutorial-Pivot Table
  • Now drag and drop pivot table fields in Filters,Columns,Rows and Values areas based on the layout or summary of the data you want to see.
  • For instance, I want to view the total price by product type
  • So I will drag and drop “Product” field in Rows area and “Price” field in Values and then I will get summarized data as shown below.
How to learn more about excel?-Pivot Table Summary
  • Similarly, you can choose the other fields to add to report
  • After that drag and drop them in the respective areas based on your summary requirement.

DATA VALIDATION

Data Validation is used to make sure that users enter certain values into cell.It validates for numbers between certain values,specific text length etc.

In this example, we will restrict users to enter specific length of text

Advanced Excel Tutorial-Data Validation

Steps to create data validation rule

  • Select the cell where you want to create data validation rule
  • Click on Data->Data Validation
  • A pop-up displays for data validation
Data Validation Pop-Up
  • For instance,the text should be in between 0 and 30.
  • In Settings Tab,enter validation criteria.
Data Validation-Settings Tab
  • Go to Input Message Tab
  • Check “Show input message when cell is selected”
  • Enter Title and Input Message for text length validation.
Data Validation-Input Message
  • Click on Error Alert Tab
  • Check “Show error alert after invalid data in entered”
  • Enter Title and Error Message 
  • After that click on OK.
Data Validation-Error Alert

Let’s see Data Validation Result

Select the cell and it will display the input message to the user.

Data Validation Result-Input Message

Now try to enter text length more than 30

Data Validation Result-Error

CONDITIONAL FORMATTING

Conditional formatting enables you to apply special formatting to cells in spreadsheet that meet certain criteria.Excel has preset conditions as well as custom conditions. When you want to create your own rule use custom conditions.

Steps to use conditional formatting

  • Select the cell where you want to apply conditional formatting
  • Click on Home->Conditional Formatting->Highlight Cells Rules ->Greater Than
Advanced Excel Tutorial-Conditional Formatting
  • A pop-up appears to enter greater than some number and with required formatting.
  • After entering details,Click on OK
  • All the cells with value greater than “25000” will be highlighted with “Green Fill with Dark Green Text” as mentioned in settings.
Conditional Formatting-Highlight Cells

REMOVE DUPLICATE RECORDS

You can get unique records by removing the duplicate entries in the cell

  • First and foremost select the column from which you want to remove duplicate records
  • Copy and paste the same column data in different cell
  • Click on Data->Remove Duplicates
  • After that a popup appears to “Remove Duplicates”
  • Click on OK
Advanced Excel Tutorial-Remove Duplicates
  • All duplicate records will be removed
  • At the end list of unique records will be displayed as shown below
Unique Records

DROP DOWN LIST

Data validation tab also helps in creation of drop down list.

Steps to create drop down list

  • Click on Data->Data Validation
  • After that a pop up appears in that select “List” from the drop down
Advanced Excel Tutorial-Drop Down List
  • Select the source for list creation (For instance in this case :Cell K2-K20)
  • Click on OK
Drop Down List-Source Selection
  • Drop down list has created as shown in the below image
List Creation

TEXT TO COLUMNS

This feature of excel helps to separate the contents of one Excel cell into separate columns.

Advanced Excel Tutorial-Text To Columns

Steps to use text to columns

  • Select the cell you want to separate into columns
  • Click on Data->Text to Columns
Text to Columns-Tab
  • Choose the file type as Delimited
  • Click on Next
Convert Text to Columns Wizard - Step 1
  • Select Delimiters as “Other” – “#”
  • Click on Next
 Convert Text to Columns Wizard-Step 2
  • Select Column data format as General
  • Finally,click on Finish
Convert Text to Columns Wizard - Step 3

In this example delimiter is # so wherever that delimiter is present after that text will convert to columns.

Advanced Excel Tutorial-Text to Columns Result

In conclusion,all the above mentioned features are very useful.If anyone is interested to learn excel formulas please refer below link

Excel Formulas

22 thoughts on “Advanced Excel Tutorial(How to learn more about excel)”

  1. Valerie Joy Deveza

    This is very helpful! I’m not aware that there are such options available. This will make things a lot easier for me now. 🙂

  2. Extremely comprehensive and some of the tips are so useful for me. I am going to return to this post with and excel sheet open and follow the steps to learn – thatnks so much Pooja.

  3. Pingback: German Language - Where to learn German for beginners? - Unique Ideas

  4. Pingback: How to earn money from home - Unique Ideas

  5. Your post is filled with with so much useful information that many people will get benefits from it…… Keep it up……

  6. Thank you for sharing this information. I learned how to do this many years ago in college but I have since forgotten. I saved this as a Pin so I can refer to it again when needed. Thanks!
    ♥https://acaponeconnection.com

Leave a Comment

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

%d bloggers like this: