Showing posts with label Advance Excel. Show all posts
Showing posts with label Advance Excel. Show all posts

HOW TO USE IF FORMULA USING WITH COUNT, COUNTIFS AND SUM FORMULA


How To Use If Function With Count Function, Sum Function And Countifs Function

IF FORMULA

If formula is most useful and helpful formula in Microsoft Excel. This Formula Allow you to make logical comparison between two values. 

Say If Formula is:

IF(Something is True, then Do Something, Otherwise Do Something Else)


Introduce this Article

In this article we have cover 4 Different Example About If Function With 3 More Different Function, 


EXAMPLE 1:

Example 1 is commission computation. we have sale amount and target amount (Show in Picture). IF Target Amount is Greater then Sale amount. 5% Commission Allow on Excess Of Target Amount


Sales Commission Computation Using If Function


HOW TO DATE CONSOLIDATE 2 TABLE IN ONE

3 MORE EXAMPLES MUST WATCH VIDEO


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 ADVANCE FILTER IN EXCEL 2016

how to use advanced filter in excel 2016


Advance Filter

In this post we are learning how to use Advanced Filter tool in Excel 2016, Advance option for filtering using complex criteria. If the data you want to filter require complex criteria (such as "Salesman" = Uzair, "Product"=Pen, "Location"=East) you can use Advanced Filter dialog box.


How to use Advance Filter

Lets start to learning how to use Advance Filter and benefits of  Advance Filter as well as normal Sorting and Filtering.

I have a database of Salesman, Product, Unit, Amount and Location

Database

Database of Advance Filter learning

Range (A1:G3) is criteria for filtering.

Go to Data Tab on the ribbon and click on Advance Filter option in Sort and Filter Group.


When Advanced Filter Popup Window on your screen. Default check on Filter the list, in place, if you check on this option your data filtered on your original database and if you check copy to another location your filtering data on new range. In list range text box select your database like (A5:D1905) and in Criteria range text box select dummy data who you create for filtering like (A1:G3). Copy to text box Disable if you check on Copy to another location radio box Copy to option enable for select your new range. finally you click on OK button and your data is filtered. Must Watch Video for easy learning to how to use Advanced Filter.


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...

HOW TO CREATE TABLE IN EXCEL

HOW TO INSERT TABLE IN EXCEL

What is Excel Table?


All Later Version After Excel 2003, you can use the Table command to convert a list of data into a formatted Excel Table. Tables have many Advance Features.

Principle of a Good Table 

  • The list should have no blank Cell and no complete blank rows.
  • The list should have no two and more than two cell merging.
  • In the first rows of the table, each column should contain a short and unique Heading,
  • Each column  in the table should contain one type of data, Example "Date", "Rate", "Amount", "Text". 

How To Create Table In Excel 


1. Select your data that you prepared.
2. Click Insert Tab, On the Ribbon
3. Click Table Button, In the Tables Group ("Ctrl + T")

 
Insert Tab on the Ribbon

4. In the Create Table Dialog Box, Select your range Automatically, If not you can select it manually and Auto Check on " My table is Headers " to Create first row as a Header.

Create Table In Excel

5. Click Ok to Setting.

How to change Table name in Excel

When we create Table, an Excel Table is given default name, such as Table1, Table2 . . . !. You should change the meaning ful name such as SaleTable, Invertory, etc

  1. Select any cell in the table
  2. Click the Design Tab on The Ribbon
  3. Change Table name in the Properties Group
Change Table Name In Excel

Change Excel Tabel Style

When you create Excel Tabel on Table Command on the Ribbon's Insert Tab. The Tabel retains the default Table style is applied and any formatting that it currently has,

  1. Select any cell in the table
  2. Click the Design Tab on The Ribbon
  3. Click Quick styles on Table Styles Group
  4. Select Style on the list
Change Excel Table Style

Show Total Rows In Excel Tabel

After you create Excel Table. Its Very easy to show total Row To Sum, Count, Average, Max, Min... Builtin Table Tool

  1. Select any cell in the table
  2. Click the Design Tab on The Ribbon
  3. Check Total row in Table Style Options Group
Insert total Row In Excel

Watch Video For Easy Learning


HOW TO CREATE UNIQUE DYNAMIC LIST WITH ARRAY FORMULA IN EXCEL

Create Unique List Using Array Formula

How To Create Unique list Using Array Formula


We have value in column A but all values are more than one. how we can remove duplicate value and create a dynamic list using array formulas.

Create Dynamic Unique List Using Array Formula

Input this formula
=INDEX($A$2:$A$26,MATCH(0,COUNTIF($B$1:B6,$A$2:$A$26),0)) and Press Ctrl + Shift + Enter

 Blank row In Data

Blank Row In Data Remove Using Array Formula

In case A7 rows are blank in database unique list show one blank row on B6. how we can solve it, it's very easy with ISBLANK function. 

=INDEX($A$2:$A$26,MATCH(0,IF(ISBLANK($A$2:$A$26),1,COUNTIF($B$1:B2,$A$2:$A$26)),0))


Watch Video For Easy Learning

HOW TO USE INDEX / MATCH FUNCTION IN EXCEL

HOW TO USE INDEX AND MATCH FUNCTION IN EXCEL

Users Heavily use Vlookup or Lookup Function for Reference. I proved INDEX / MATCH much powerful and easy function against VLOOKUP. how to use INDEX and MATCH function.

How to use INDEX Function 

The INDEX Formula returns either the value from range or table.
The INDEX function have 2 Syntaxes

Syntax 

=INDEX(array,row_num,[column_num])

array: A Range of a table or Cells
row_num: The row number in the array to use to return the value.
column_number: Optionally, it is the column number in the array to use to return the value

How To Use Index Function In Excel

 in this example our required is sales value of code 1004 or product Book, in Cell C10 first syntax array is C2:C7 value. Second syntax is row_number after indexed code 1004 position is 4.

How to use MATCH Function

MATCH function looks for an item in a list and shows its position. it can be used with text and numbers. it can look for an exact match or an approximate match. 

Syntax


=MATCH(lookup_value,lookup_array,[match_type])

lookup_value. The value that you want to match in lookup_array.
lookup_array. The range of cells being searched.
match_type Optional. The number -1, 0, or 1.



The match-type either 0, 1 or -1

using 0 will look for an exact match. if no match is found the #N/A error will be shown.

using 1 will look for an exact match, or the next lowest number if  no exact match exists. if there is no match or next lowest number the error #N/A is shown. The list of values being examined must be sorted for this to work correctly 

using -1 will look for an  exact match, or the next highest number if no exact match exists. if there is no exact match or next highest number the error #N/A is shown. The list must be sorted for this to work properly. 

How To use match Function in Excel

INDEX With MATCH best alternative function of VLOOKUP, HLOOKUP or LOOKUP fucntions.

How To Use INDEX with Match Watch Complete Video For Easy Learning