Showing posts with label learning excel. Show all posts
Showing posts with label learning excel. Show all posts

Top 20 Most useful Microsoft Excel Shortcut Keys

Excel HotKeys




In this Article we are learning Most useful and helpful Shortcut keys of Microsoft Excel 2007 / Microsoft Excel 2013 / Microsoft Excel 2016

Timeline Of Article

  1. Create a formula to sum all of the above cells
  2. Current Date
  3. Current Time
  4. Select Entire row
  5. Select Entire Column
  6. Hide Row
  7. Hide Column
  8. new Line In Cell
  9. Show hide Formula
  10. Save As
  11. Create Charte From Select Date
  12. Insert New Worksheet
  13. Move Forward Between worksheets in the same document.
  14. Move back between worksheets in the same documnet.
  15. Format Number in comma format
  16. Format number in date format
  17. Format number in currency format ($)
  18. Format number in percentage format
  19. Move to cell A1
  20. Switch between showing Excel formula or their values in cells
Learn Top 5 Most Useful Formula In Microsoft Excel 2013 / 2016

How we can use this shortcuts in spreadsheets

Watch video



HOW TO USE DATA VALIDATION IN EXCEL 2016

How to use Data Validation In Excel

What is Data Validation in Excel

Data Validation is a tool that help you control the kind of information that is entered in your worksheet  with data validation, you can
  • Provide users with a list of choices
  • Restrict entries to a specific type or size
  • Create Custom Setting

Note:


Data Validation is not foolproof. It can be circumvented by pasting data into the cell, or by Home Tab> Choosing Editing> Clear> Clear All


Type of Validation


  • Any Value

  • If you select Any value in data validation its mean you can input any text and numeric value.

  • Whole number

  • Set a range of numeric value and limits of value that can be entered in a cell or range. Select cell A1, click on Data Tab, click Data Validation Button on the ribbon, Open Data validation popup, Select whole number on settings tab, select data on your requirement like "between","not between","equal to ","not equal to"......!, Input Minimum and Maximum value for data limitation. Example Minimum Value is 0 and Maximum value is 10. Enter value 4 in cell A1 the value is entered without error, if you enter value 44 in cell A1, because the data validation setting you create for cell A1, you receive a stop Alert message.

  • Decimal

    Same Concept of Whole number but this setting is Decimal Value only


  • List

  • You can also create Drop Down list in excel using Data Validation. For Example
    You have a list of Department name in range A2:A10, Define a name for a cell or range you should create easy drop down list on cell  .Go to data validation and select the list (Note) Check on "Ignore blank" and "In-cell dropdown" in the source box enter the name with "=" sign Example " =product " =Department"  and click ok button.

Create Data Validation List in excel 2013 2016


create drop down list using data validation in excel

  • Text length       

  • You can also Create text length Valid using Data Validation. if you input Minimum Value is 8 digit and Maximum value is 12 digit in selected range or cell. if you input less then 8 digit value or maximum 12 digit value Data Validation show error on you screen.

  •   Custom

Input Message With Data Validation

You can also input Data Validation Message. Display a Message when cell is selected. you can create a text box to message display. you can inform user this cell is valid for text or number or date....! using input message.

Input Message Using Data Validation


Error Alert In Data Validation

  • Stop
User entering invalid data in a cell. A stop alert show two option. Retry Cancel and Help

  • Warning
User entering invalid data in a cell. Warning Alert message appears, user can click yes accept invalid entry, user can click No to edit invalid entry and user can click Cancel to remove invalid entry.

  • Information
User entering invalid data in a cell. information just inform that this data is invalid if user click Ok to accept invalid entry and click cancel to reject it...

INSERT CELL COLOR USING VBA IN EXCEL


INSERT CELL COLOR IN SPREADSHEET USING VBA EXCEL


If you want to insert cell color in your spreadsheet using VBA. We provide Very easy and understanding method to do it. Now open New blank workbook of Microsoft excel.

Goto Developer Tab on your ribbon and click visual basic button on left side.

Developer Tab In Excel

 Copy code and paste it on your visual basic window

Cell Color using VBA in Excel

Code

Sub Cell_Color()

Dim i As Integer

For i = 1 To 10
Cells(i, 1).Value = i
Cells(i, 2).Interior.ColorIndex = i
Next i

End Sub

After Run Macro you can see your spreadsheet like this

Insert Cell Colour Table in Excel using VBA

Easy Learning watch Video

HOW TO USE CONCATENATE TRIM LEN FUNCTION IN EXCEL

LEN TRIM AND CONCATENATE FUNCTION

HOW TO USE CONCATENATE FUNCTION IN EXCEL

This function concatenate / joins separate pieces of text into one item. This function is most use full function in excel for joined two or more than two texts in one cell

Syntax

=CONCATENATE(Text1,Text2,Text3...Text255)



HOW TO USE CONCATENATE FUNCTION IN EXCEL

=CONCATENATE(A12,B12)

In this Example Cell C12 Join A12 And B12. You can achieve the same result by using the & operator.


ALTERNATIVE CONCATENATE FUNCTION


HOW TO USE TRIM FUNCTION IN EXCEL

TRIM function removes unwanted spaces from a piece of text. The space before and after the text will be removed completely. Multiple spaces within the text will be trimmed to a single space.

Syntax 

=TRIM(TextToTrim)



HOW TO USE TRIM FUNCTION IN EXCEL

=TRIM(A4)

In this Example Cell B4 removed space before the text shown in cell A4. In Cell B5 Removed Multiple spaces within the text will be trimmed to a single space.

HOW TO USE LEN FUNCTION IN EXCEL 

Len function is used to count numbers of characters in a cell. It also includes spaces.

Syntax 

 =LEN(text)

HOW TO USE LEN FUNCTION IN EXCEL


Select Cells B20:B22 And type the formula =len(A20) Hit Ctrl + Enter to put the formula to all range.




Download Sample File Click Here