Microsoft Excel Training

Advanced Excel

Trending Questions and Lessons

Follow 36,780 Followers

Feed

All

All

Lessons

Discussion

Lesson Posted on 24/09/2017 IT Courses/MS Office Software Training/Microsoft Excel Training/Advanced Excel

Slicer in Excel

Rakesh R

I work for a reputed MNC as HR Manager. Started my career as MIS specialist and currently managing CoE...

Have you every tried Slicer option in Excel? There are lot many features in excel which go unnoticed even if they are visibleAs you know, one of the powerful tool in excel is Pivot table which summarizes your data and we know the drag and drop features of it. Use Slicer option in Pivot table instead... read more

Have you every tried Slicer option in Excel? There are lot many features in excel which go unnoticed even if they are visible
As you know, one of the powerful tool in excel is Pivot table which summarizes your data and we know the drag and drop features of it.

Use Slicer option in Pivot table instead of Report Filter to filter down categories. Slicer works exactly the same way the Report Filter works however, slicer will provide you a front-end UI lists with button click option for easy use. This helps the user to quickly select the required categories just by clicking them, which also looks appealing.

To insert a slicer, execute the following steps.
1. Click any cell inside the pivot table.
2. On the Analyze / Options tab, in the Filter group, click Insert Slicer.

Hold Ctrl button to select multiple categories

Thanks!!!
Rakesh

read less
Comments 1
Dislike Bookmark

Asked on 23/09/2017 IT Courses/MS Office Software Training/Microsoft Excel Training/Advanced Excel

How to learn Excel online for free?

Answer

Lesson Posted on 30/08/2017 IT Courses/MS Office Software Training/Microsoft Excel Training/Advanced Excel

Benefits Of E-Learning

Edusmart Skills

EduSmart Skills is an online platform where both the teacher and student come face to face online to...

From an organizational point of view, e-learning courses can be conducted by trainers in one location and it can benefit employees across continents in different time zones. This can bring down training costs considerably. Sans the boundaries of a structured schedule to dictate the learning, you get... read more

From an organizational point of view, e-learning courses can be conducted by trainers in one location and it can benefit employees across continents in different time zones.  This can bring down training costs considerably. Sans the boundaries of a structured schedule to dictate the learning, you get the much-needed flexibility to pursue and accomplish your learning goals. Even a person who has a busy timetable or one who travels often can benefit from it. Since e-learning breaks from the confinement of a traditional classroom, you can choose to learn from anywhere.

Other than the advantage of being able to enroll for courses from anywhere in the world, online learning courses are more affordable. While some e-learning platforms are easier to navigate than others, it would be easier if organizations also provide a tech teaching tool so that all employees are brought up to speed on using the learning application. Then the employees can pick up the training material and move at their own pace; working it around their regular tasks, workload, and deadlines.

While learning through the Internet allows for personal freedom, it can lead to isolation where interaction is not possible. It can often be demotivating. Those with low motivation can fall behind. The E-learning trainer must keep these factors in mind and encourage constant feedback.

E-learning trainers must ensure that the learning modules allow for time to reflect on the content, reflection, and implementation. The competency of the trainer lies in choosing the most ideal method of training, based on the trainee group he/she is addressing. Use of well-prepared visuals and structured presentation is critical. Stimulate engagement with the trainees.

read less
Comments
Dislike Bookmark

Looking for Microsoft Excel Training classes

Find best Microsoft Excel Training classes in your locality on UrbanPro.

FIND NOW

Lesson Posted on 26/03/2017 IT Courses/MS Office Software Training/Microsoft Excel Training IT Courses/MS Office Software Training/Microsoft Excel Training/Advanced Excel IT Courses/MS Office Software Training/Microsoft Excel Training/Excel VBA & Macro Training

How to install Analysis ToolPak in Excel

Dheeraj Kushwaha

I am having Sound knowledge of Adobe Creative cloud CC 2018. I deals in many creative domain such as...

The Analysis ToolPak is an Excel add-in program that provides data analysis tools for financial, statistical and engineering data analysis. To load the Analysis ToolPak add-in, execute the following steps. 1. Click on the green File tab. The File tab in Excel 2010 or later replaces the Office Button... read more

The Analysis ToolPak is an Excel add-in program that provides data analysis tools for financial, statistical and engineering data analysis.

To load the Analysis ToolPak add-in, execute the following steps.

1. Click on the green File tab. The File tab in Excel 2010 or later replaces the Office Button (or File Menu) in previous versions of Excel.

2. Click on Options.

Click Options in Excel

3. Under Add-ins, select Analysis ToolPak and click on the Go button.

Click Analysis ToolPak

4. Check Analysis ToolPak and click on OK.

Check Analysis ToolPak

5. On the Data tab, you can now click on Data Analysis.

Click Data Analysis

The following dialog box below appears.

6. For example, select Histogram and click OK to create a Histogram in Excel.

Select Histogram

read less
Comments
Dislike Bookmark

Lesson Posted on 23/03/2017 IT Courses/MS Office Software Training/Microsoft Excel Training IT Courses/MS Office Software Training/Microsoft Excel Training/Basic Excel IT Courses/MS Office Software Training/Microsoft Excel Training/Advanced Excel

Create a Grading in Excel using Formulas (All versions)

Bhavna Philipose

I have worked in the S/W (IT) industry with 12 yrs of experience in the Training Industry and the rest...

Topic: Applying Grading /Ranking in Excel (All versions Let’s Say you need to rate the Sales with some grade text like POOR, AVERAGE, GOOD and EXCELLENT Based on a range of values as mentioned below: 0 to 1999 ---Poor 2000 to 2999 –Average 3000 to 3999---Good 4000 and 5000 or greater... read more

Topic: Applying Grading /Ranking in Excel (All versions

Let’s Say you  need to rate the Sales with some grade text like POOR, AVERAGE, GOOD  and EXCELLENT

Based on a range of values as mentioned below:

0 to 1999 ---Poor

2000 to 2999 –Average

3000 to 3999---Good

4000 and  5000 or greater – Excellent

Sales value

What is the ranking Using If?

What is the ranking Using Lookup?

0

Poor

Poor

1999

Poor

Poor

2000

Avg

Avg

2999

Avg

Avg

3000

Good

Good

3999

Good

Good

5000

Excellent

Excellent

5001

Excellent

Excellent

 We can use 2 commonly used functions in this scenario

  • IF (Nested)
  • Lookup

 Let's Understand the First method using If.

METHOD 1: IF  Function (NESTED)

Consider the Logic to be used here  in Plain English (pseudo Code)

Pseudo Code, for N text values of gradings always N-1 if's
 
if (salesvalue(a3-cell) >= 5000 then
  result = " Excellent"  - Value if true (fo this if)
else
if (salesvalue(a3-cell) >= 3000 then  value if false(for prev if)
result = "Good"   value if true (for this If)
else    
if (salesvalue(a3-cell) >= 2000 then  value if false(for prev if)
result = "Average" -value if true (for this if)
else         Default Case
result = "Poor"
End )))

 The Syntax of the IF function in Excel  is :

 =if(condition,what-action-if-true, what-action-if-false)

To Apply a nested if , using the above syntax and  the logic explained by the English Code ,In Excel use :

=if(a3>=5000,"Excellent",if(a3>=3000,"Good",if(A3>=2000,"Avg","poor")))

METHOD 2 : Lookup Function

Create a Tabular form in Excel  as a reference for Lookup Function

 

F

G

0

poor

2000

avg

3000

good

5000

excellent

 

Let’s says the Value to Lookup Value is in the Cell O2(the Sales Value to be Ranked)

The Lookup Function Syntax

=lookup(Lookup_Value,Lookup_Value_range,lookup_result_range)

Apply the Function Syntax as below

=LOOKUP(A3,F3:F6,G3:G6)

Let's Take Another Example

Example 2

 

Scores

Grade with IF

Grade With Lookup

0

Fail

Fail

34

Fail

Fail

35

pass

Pass

59

pass

Pass

60

A

A

74

A

A

75

A+

A+

90

Distinction

Distinction

91

Distinction

Distinction

   

 

Create a Table for Lookup function to lookup values

Column F                             Column G

0

Fail

35

Pass

60

A

75

A+

90

Distinction

 

I have Created A Named Range for frequently used cells  by selecting the columns and typing a name in the Name Box and press Enter

Named Ranges ====>

L_range(For Column F)

R_range(For Column G)

 

Consider the The Formula For  IF  as given below :

=IF(A13>=90,"Distinction",IF(A13>=75,"A+",IF(A13>=60,"A",IF(A13>=35,"pass",

"Fail"))))

 Consider the The Formula For  lookup  as given below :

=LOOKUP(A13,L_range,R_lookup)

So which Function is Simpler or Best to use  ? You may say lookup , well actually Depends on the usage

Keep it Simple.

When to use IF  

  • Use IF, when you need to formulate /evaluate the result, and when the grades are more than 7 – 15, you may have to write many nested ifs (N-1), this makes the formula longer and complex
  • You need to use Comparison operators in the test conditions
  • Need No order to evaluate, from Larget to smallest or Smallest to Largest Order will do, both orders if will work

 When to Use Lookup

 Use a Lookup, when you have smaller reference Tables Like we created above, typically 2 dimensional, 1  column to lookup and the other to return a result.

  • The Lookup column range must be always sorted in the Ascending order(smaller to larger)
  • Also, the Lookup Function, can be used In MACROS (very useful)
  • Lookup Function can use Arrays (so when your values are in the form of arrays, use lookup instead of if)

So the above Lookup Formula can also be written as below using an Array syntax (you don’t need to create a Table reference for lookup, in this case)

=LOOKUP(A3,{0,2000,3000,5000}, {"Poor","Average","Good","Excellent"})

Note: The Braces in red are the dataSets (Arrays),1 each for the lookup range and the Result Range

Some Other Functions, to Consider For Ranking /Grading

  • CHOOSE ()
  • RANK(2007 or older versions)
  • EQ(2010 onwards)

Hope you found this Lesson interesting and helpful.I would appreciate your comments and feedback

Next Lesson Upcoming Soon, On Choose, Ranking !!!

Happy Grading !!!
 

read less
Comments
Dislike Bookmark

Lesson Posted on 10/03/2017 IT Courses/MS Office Software Training/Microsoft Excel Training IT Courses/MS Office Software Training/Microsoft Excel Training/Advanced Excel

Common MS Excel Shortcuts to make life easier!

Shruti

Hi All,I am new to UrbanPro with a passion for learning and teaching analytical tools, MS Excel being the one of them. Excel is one of those master tools which seems untapped to its fullest potential; mastering it really helps you get insights into your data and helps you make meaningful decisions with... read more

Hi All,
I am new to UrbanPro with a passion for learning and teaching analytical tools, MS Excel being the one of them. Excel is one of those master tools which seems untapped to its fullest potential; mastering it really helps you get insights into your data and helps you make meaningful decisions with raw data. Here I am listing out few shortcuts which are really helpful if you have been using MS - Excel day in day out.

Keys                 Description

Alt+Enter          Useful when you want to continue writing text in the same cell

F7                    to activate a spellcheck on your worksheet

CTRL+ ;            to insert current date

CTRL+shift+;    to insert current time

shift+spacebar   select entire column

CTRL+spacebar  select entire row

CTRL+F6            to toggle between open workbooks

ALT+h+m+m     to merge cells

Hope you find this post useful.

Happy Learning!!

read less
Comments
Dislike Bookmark

Looking for Microsoft Excel Training classes

Find best Microsoft Excel Training classes in your locality on UrbanPro.

FIND NOW

Lesson Posted on 23/02/2017 IT Courses/MS Office Software Training/Microsoft Excel Training IT Courses/MS Office Software Training/Microsoft Excel Training/Advanced Excel

What are the function categories on Excel?

Talent Hub Training Institute

This is the institute where people can make them employable by our professional Training. we are not...

Function Categories There are many categories of functions in Excel. Each category has specific functions that pertain to that category. Financial: Performs common business calculations including accounting and finance. Date & Time: Performs functions involving date or time data. Math &... read more

Function Categories

There are many categories of functions in Excel. Each category has specific functions that pertain to that category.

  • Financial: Performs common business calculations including accounting and finance.
  • Date & Time: Performs functions involving date or time data.
  • Math & Trig: Performs simple to complex mathematical functions.
  • Statistical: Performs statistical analysis on ranges of data.
  • Lookup & Reference: Finds values in a corresponding table or list and incorporates the data into the calculation.
  • Database: Performs a function only on data that meets a criterion.
  • Text: Allows text to be manipulated within a calculation that also contains data.
  • Logical: Performs what-if analysis to see if a condition is true or false.
read less
Comments
Dislike Bookmark

Answered on 18/02/2017 IT Courses/MS Office Software Training/Microsoft Excel Training IT Courses/MS Office Software Training/Microsoft Excel Training/Advanced Excel

ICreative Solution

Hi Manoj, There are over 500+ formulas in Ms Excel. Ms excel is very vast application. There is no basic & advanced level in Excel but we have divided into it. That is depends on work type, even we can use basic functions/formulas in advanced problems. So it's totally depends on our need. We do provide... read more
Hi Manoj, There are over 500+ formulas in Ms Excel. Ms excel is very vast application. There is no basic & advanced level in Excel but we have divided into it. That is depends on work type, even we can use basic functions/formulas in advanced problems. So it's totally depends on our need. We do provide Ms Excel training with live real time examples from which learner/student can grab it fast and understands for the long period of time. Please go through with our profile & contact us for more details. Thanks, iCreative Solution read less
Answers 39 Comments
Dislike Bookmark

Lesson Posted on 01/02/2017 IT Courses/MS Office Software Training/Microsoft Excel Training IT Courses/MS Office Software Training/Microsoft Excel Training/Advanced Excel Excel VBA Training +1 IT Courses/MS Office Software Training/Microsoft Excel Training/Excel VBA & Macro Training less

VBA Tip: Repeating Emp Names N number of times using 4 different loops

Excelytics Vba Macros

Excelytics was established in the year 2014 by Microsoft Certified Professionals. Excelytics is a...

Hi All,This is a requirement from one of our student and here we are posting the question along with solution (this can be achieved using 4 different loops). In Column “J” there are few employee names which need to be repeated for “n” (this “n” is in cell “O1”)... read more

Hi All,
This is a requirement from one of our student and here we are posting the question along with solution (this can be achieved using 4 different loops). In Column “J” there are few employee names which need to be repeated for “n” (this “n” is in cell “O1”) number of times in column “L”. Check the below screenshot for understanding the requirement. 

Above requirement can be done using 4 different VBA loops. Here you go

Solving the above task using 1. For Next Loop 

Sub EmpName_Recurring1()
   Dim emp_loop As Integer
   Dim source_lr As Integer
   Dim destination_lr As Integer
   ThisWorkbook.Sheets("Task").Range("L2:L10000").ClearContents
   source_lr = ThisWorkbook.Sheets("Task").Cells(Rows.Count, "J").End(xlUp).Row
   For emp_loop = 1 To source_lr
   destination_lr = ThisWorkbook.Sheets("Task").Cells(Rows.Count, "L").End(xlUp).Row + 1
   Range(Cells(destination_lr, "L"), Cells(destination_lr + Range("Repeat_Count").Value - 1, "L")).Value =          Cells(emp_loop + 1, "J").Value
   Next emp_loop
   MsgBox "Task completed using For Next loop"

End Sub

Solving the above task using 2. For Each Loop

Sub EmpName_Recurring2()
   Dim rng As Range
   Dim source_lr As Integer
   Dim destination_lr As Integer
   ThisWorkbook.Sheets("Task").Range("L2:L10000").ClearContents
   source_lr = ThisWorkbook.Sheets("Task").Cells(Rows.Count, "J").End(xlUp).Row

For Each rng In Sheets("Task").Range("J2:J" & source_lr)
   destination_lr = ThisWorkbook.Sheets("Task").Cells(Rows.Count, "L").End(xlUp).Row + 1
   Range(Cells(destination_lr, "L"), Cells(destination_lr + Range("Repeat_Count").Value - 1, "L")).Value =          rng.Value
   Next rng
   MsgBox "Task completed using For Each loop"

End Sub

Solving the above task using 3. Do While Loop

Sub EmpName_Recurring3()
   Dim rng As Range
   Dim row_increment As Integer
   Dim destination_lr As Integer
   ThisWorkbook.Sheets("Task").Range("L2:L10000").ClearContents
   Do While Cells(row_increment + 2, "J").Value <> ""
   destination_lr = ThisWorkbook.Sheets("Task").Cells(Rows.Count, "L").End(xlUp).Row + 1
   Range(Cells(destination_lr, "L"), Cells(destination_lr + Range("Repeat_Count").Value - 1, "L")).Value =          Cells(row_increment + 2, "J").Value
   row_increment = row_increment + 1
Loop
   MsgBox "Task completed using Do While loop"

End Sub

Solving the above task using 4. Do Until Loop

Sub EmpName_Recurring4()
   Dim rng As Range
   Dim row_increment As Integer
   Dim destination_lr As Integer
   ThisWorkbook.Sheets("Task").Range("L2:L10000").ClearContents
Do Until Cells(row_increment + 2, "J").Value = ""
   destination_lr = ThisWorkbook.Sheets("Task").Cells(Rows.Count, "L").End(xlUp).Row + 1
   Range(Cells(destination_lr, "L"), Cells(destination_lr + Range("Repeat_Count").Value - 1, "L")).Value =         Cells(row_increment + 2, "J").Value
   row_increment = row_increment + 1
Loop
   MsgBox "Task completed using Do Until loop"

End Sub

read less
Comments
Dislike Bookmark

Looking for Microsoft Excel Training classes

Find best Microsoft Excel Training classes in your locality on UrbanPro.

FIND NOW

Answered on 05/08/2017 IT Courses/MS Office Software Training/Microsoft Excel Training IT Courses/MS Office Software Training/Microsoft Excel Training/Advanced Excel

Shafi

1. First you have to select the range of cells where you want to apply conditional formatting 2. You have to select conditional formatting from Home tab in Excel window 3. There will be many categories, You have to select right one for ex: highlight cell rules==.>greater than etc., a dialog box will... read more
1. First you have to select the range of cells where you want to apply conditional formatting 2. You have to select conditional formatting from Home tab in Excel window 3. There will be many categories, You have to select right one for ex: highlight cell rules==.>greater than etc., a dialog box will come 4. you have to give the value which should be highlight in ex: greater than text box, after that you have to select colors or formats or other wise if you want to choose custom color means; 5. You have to select custom format, from there choose any color from fill tab 6. Then press OK read less
Answers 11 Comments 1
Dislike Bookmark

About UrbanPro

UrbanPro.com helps you to connect with the best Microsoft Excel Training classes in India. Post Your Requirement today and get connected.

Overview

Lessons 39

Total Shares  

Related Topics

Top Contributors

Connect with Expert Tutors & Institutes for Advanced Excel

x

Ask a Question

Please enter your Question

Please select a Tag

UrbanPro.com is India's largest network of most trusted tutors and institutes. Over 25 lakh students rely on UrbanPro.com, to fulfill their learning requirements across 1,000+ categories. Using UrbanPro.com, parents, and students can compare multiple Tutors and Institutes and choose the one that best suits their requirements. More than 6.5 lakh verified Tutors and Institutes are helping millions of students every day and growing their tutoring business on UrbanPro.com. Whether you are looking for a tutor to learn mathematics, a German language trainer to brush up your German language skills or an institute to upgrade your IT skills, we have got the best selection of Tutors and Training Institutes for you. Read more