Signup as a Tutor

As a tutor you can connect with more than a million students and grow your network.

Microsoft Excel Training

Microsoft Excel Training

Trending Questions and Lessons

Follow 41,263 Followers

Ask a Question

Feed

All

All

Lessons

Discussion

Lesson Posted on 22/12/2017 IT Courses/MS Office Software Training/Microsoft Excel Training/Basic Excel IT Courses/MS Office Software Training/Microsoft Excel Training/Advanced Excel

How To Create Cutom List And Its Advantages?

Vidyesh

I have passion to teach and experience in Biz Application Development . I have 7 years of experience...

You must have observed when you are typing Month names, Excel suggests the next Month Name. Also, you can drag the cursor using the anchor at the right bottom of cell and the series of month names can be filled into dragged cells. This is applicable for Weekday Names too. Excel helps to autofill the... read more

 

 

You must have observed when you are typing Month names, Excel suggests the next Month Name. Also, you can drag the cursor using the anchor at the right bottom of cell and the series of month names can be filled into dragged cells.

This is applicable for Weekday Names too. Excel helps to autofill the series of data.

 

Consider a scenario, where I have to prepare data of Fruits. The fruit list is limited to 6-7 items. This list I will be requiring in my various Excel Worksheets.  

Is there any way in Excel using that I can get series automatically in any Worksheet?

Let’s understand the method:

 

Click on File → Options. Select Advanced option.

 
 

Go to General section. Click on Edit Custom List button.

You can view existing custom lists. In this section you can  view Month Names, Weekday names as a list.

I will add my list of fruits as a custom list here.

I click on List Entries section and type in names in the sequence as I desire

Banana, Chiku, Orange.

After completing the list I click on, Add button

 

 

Alternate method can be, I have already typed the list. I will select the Range for Import list from cells.

Click on Import button.

Once I have a my list created as a Custom List, I can use it in any worksheet.

The Custom list is also useful in data sorting. I have list of fruits in alpabatical order. This list I want to sort in my Custom sequence. i.e. Banana, Chiku etc.

As I have defined the sequence as a cutom list, I can easily sort the list in customised sequence.

  • Select the list for Sorting.
  • Select Sort option from Data menu.
  • Select Fruit as sort column. In Order dropdown Select Custom List.
  • Select Custom list of fruits.
  • Click OK in Custom List. Click OK in Sort option.

The list will be sorted in customised sequence.

 

You can apply Custom list suitable to your needs. 

e.g.

Bank Names, Categories of Product, Employee Grades etc.

Go ahead & try it!

   

 

read less
Comments
Dislike Bookmark

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

Excel Keyboard Shortcuts Daily Usefull Keys

Ninety-Nine Excel Training Academy

99Excel Training Academy is the trusted and internationally recognized training provider in India With...

Excel KeyBoard Shortcuts Daily Usefull keys: CLTR A-Z Shift SELECT.ETC ALT All Tool Keys CLTR A Select All Shift+F11 Add New Sheet CLTR C Copy ALT + E + L Delete To Sheet CLTR D Down Fill ALT + O + H, H Hide Sheet CLTR N New Workbook ALT + O + H , U Unhide... read more
Excel KeyBoard Shortcuts Daily Usefull keys:
CLTR A-Z      
Shift SELECT.ETC      
ALT All Tool Keys      
CLTR A  

Select All

Shift+F11

Add New Sheet

CLTR C Copy ALT + E + L

Delete To Sheet

CLTR

Down Fill

ALT + O + H, H

Hide Sheet

CLTR N

New Workbook

ALT + O + H , U

Unhide Sheet

CLTR R

Right Fill

ALT + O + H, T

Tab Color

CLTR S Save ALT + O + H, R

Rename Sheet

CLTR V Paste ALT + E + M

Move & Copy

CLTR X Cut ALT + O + C + A

Auto Fit Column Width

CLTR Y Redo F4

Lock To Cell

LTR Undo F4

Repeat Last Action

CLTR Page Up Sheet Move ALT+ I + C

Insert Column

CLTR Page Down Sheet Move ALT+ I + R Insert Row
CLTR Home Select A1    
CLTR End Last Cell    

 

read less
Comments
Dislike Bookmark

Asked on 06/12/2017 IT Courses/MS Office Software Training/Microsoft Excel Training/Advanced Excel

How to learn Excel online for free?

Answer

Looking for Microsoft Excel Training classes

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

FIND NOW

Lesson Posted on 20/11/2017 Functional Training/Business Analysis Training IT Courses/MS Office Software Training/Microsoft Excel Training/Excel VBA & Macro Training

Eight Bug Reduction Tips: VBA

Gaurav C.

I am an analytic professional with more than 13 years of experience in advanced analytics, problem solving,...

You cannot completely eliminate bugs in your programs, but there are a few tips that will help you keep them to a minimum: i. Use an Option Explicit at the beginning of your module: Doing so will require that you define the data type for every variable that you use. It’s a bit more work, but you’ll... read more

You cannot completely eliminate bugs in your programs, but there are a few tips that will help you keep them to a minimum:

i. Use an Option Explicit at the beginning of your module: Doing so will require that you define the data type for every variable that you use. It’s a bit more work, but you’ll avoid the common error of misspelling a variable name.. And there’s a nice side benefit: Your routines will often run faster.

ii. Format your code with indentation: Using indentation to delineate code segments is quite helpful. If you have several nested For Next loops, for example, consistent indentation will make it much easier to keep track of them all.

iii. Be careful with On Error Resume Next: This statement causes Excel to ignore any errors and continue. In some cases, using this statement will cause Excel to ignore errors that shouldn’t be ignored. Your may have bugs and not even realize it.

iv. Use lots of comments: Nothing is more frustrating than revisiting code that you wrote six months ago, and not having a clue as to how it works. Adding a few comments to describe your logic can save you lots of time down the road.

v. Keep your subroutines and functions simple: Writing your code in smaller modules, each of which has a single, well-defined purpose, makes it much easier to debug them.

vi. Use the macro recorder to help you identify properties and methods: If I can’t remember the name or syntax of a property or method, it’s often quicker to simply record a macro and look at the recorded code.

vii. Consider a different approach: If you’re having trouble getting a particular routine to work correctly, you might want to scrap the idea and try something completely different. In most cases, Excel offers several alternative methods of accomplishing the same thing.

viii. Understand Excel’s debugger: Although it can be a bit daunting at first, you’ll find that Excel’s debugger is an excellent tool. Invest some time and get to know it.

read less
Comments
Dislike Bookmark

Lesson Posted on 20/11/2017 IT Courses/MS Office Software Training/Microsoft Excel Training/Basic Excel Functional Training/Business Analysis Training

Excel Keyboard Shortcuts

Gaurav C.

I am an analytic professional with more than 13 years of experience in advanced analytics, problem solving,...

Our day to day work involves excel and to complete our work fast we should have adequate knowledge of keyboard shortcuts. I am going to give you simple but useful tip which will helps you to navigate excel and perform tasks much quicker without touching the mouse. Use arrow keys in combination of Ctrl/Shift... read more

Our day to day work involves excel and to complete our work fast we should have adequate knowledge of keyboard shortcuts. I am going to give you simple but useful tip which will helps you to navigate excel and perform tasks much quicker without touching the mouse.

Use arrow keys in combination of Ctrl/Shift key to traverse your Excel sheet:

  • Ctrl + Down arrow: Data Region Down.
  • Ctrl + Up arrow: Data Region Up.
  • Ctrl + Right arrow: Data Region Right.
  • Ctrl + Left arrow: Data Region Right.

Use Shift key with the above combination to select the data e.g. Ctrl+Shift+Down arrow to select the data range from current cell to last filled cell of the data region.

Some more useful shortcuts:

  • Ctrl+ Space Bar: Selects the entire column.
  • Shift+ Space Bar: Selects the entire row.
  • Ctrl + PageUp: Previous sheet.
  • Ctrl + PageDown: Next sheet.
  • Ctrl + Home: Selects cell A1 of the active sheet.
  • Ctrl + 1: Opens Format Cell window.
  • Ctrl+Shift+1: Changes the cell format to Number.
  • Ctrl+Shift+2: Changes the cell format to Time.
  • Ctrl+Shift+3: Changes the cell format to Date.
  • Ctrl+Shift+4: Changes the cell format to Dollar.
  • Ctrl+Shift+5: Changes the cell format to Percentage.
  • Ctrl+Shift+6: Changes the cell format to Scientific.

And finally Ctrl + Z to undo what ever mess you have done the excel sheet while practicing these shortcuts.

read less
Comments
Dislike Bookmark

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

Dget-Extract From A Database A Single Record That Matches The Condition We Specify

Daniel L

I am working as a Quality Consultant and Freelancer Corporate Trainer for Basic, Intermediate and Advanced...

Syntax: DGET(Database, Field, Criteria) The DGET function syntax has the following arguments: Database Required: The range of cells that makes up the list or database. A database is a list of related data in which rows of related information are records, and columns of data are fields. The first row... read more

Syntax:

DGET(Database, Field, Criteria)

The DGET function syntax has the following arguments:

  • Database Required: The range of cells that makes up the list or database. A database is a list of related data in which rows of related information are records, and columns of data are fields. The first row of the list contains labels for each column.

  • Field Required: Indicates which column is used in the function. Enter the column label enclosed between double quotation marks, such as "Age" or "Yield," or a number (without quotation marks) that represents the position of the column within the list: 1 for the first column, 2 for the second column, and so on.

  • Criteria Required: The range of cells that contains the conditions that you specify. You can use any range for the criteria argument, as long as it includes at least one column label and at least one cell below the column label in which you specify a condition for the column.

Remarks:

  • If no record matches the criteria, DGET returns the #VALUE! error value.

  • If more than one record matches the criteria, DGET returns the #NUM! error value.

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

Tree

Age

Yield

Profit

Height

="=Apple"

     

<16

="Pear"

       

Tree

Age

Yield

Profit

 

Apple

20

14

$105

 

Pear

12

10

$96

 

Cherry

14

9

$105

 

Apple

15

10

$75

 

Pear

8

8

$77

 

Apple

9

6

$45

Result:

 

=DGET(A5:E11, "Yield", A1:A3) - #Num!

 

=DGET(A5:E11, "Yield", A1:F3) - 10

 

 

 

 

   
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 10/10/2017 IT Courses/MS Office Software Training/Microsoft Excel Training

How do I type new line inside a single cell? I don't want to go to another cell but there should be a...

Rahul Saha

Microsoft Advance Excel and PowerPoint Trainer

Press Alt + Enter and you will be on a new line within a cell
Answers 33 Comments
Dislike Bookmark

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

Looking for Microsoft Excel Training classes

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

FIND NOW

Answered on 02/11/2017 IT Courses/MS Office Software Training/Microsoft Excel Training/Excel VBA & Macro Training IT Courses/MS Office Software Training/Microsoft Excel Training/Advanced Excel

What is an Excel Macro?

Excel Training - Excelschooling.com

Excel Macro also called as VBA (Visual Basic Application). If you are good in Visual basic you can do more automation process in Excel. If you are not good in VB , you can try Record macro feature in Excel. Where it create programme based on your activities.
Answers 1 Comments 2
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

Questions 121

Lessons 106

Total Shares  

Related Topics

Top Contributors

Connect with Expert Tutors & Institutes for Microsoft Excel Training

x

Ask a Question

Please enter your Question

Please select a Tag