Showing posts with label Microsoft Excel. Show all posts
Showing posts with label Microsoft 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 HIGHLIGHT ROW AND COLUMN OF ACTIVE CELL USING VBA

How To Hightlight Row and Column Active Cell Using VBA In Excel

How to Highlight Row and Column of Active Cell Using VBA? 

If you have a large Data in Spreadsheet and you may want to highlight the select Cell's Row and Column so you can read your data very easily, HERE I can introduce you Interesting VBA Trick to highlight the row and column of the current cell select. when the cell is changed, the row and column highlight Automatically.

How to Highlight Row Using VBA


The Following these step can help you automatically highlight the row of the select cell. Follow the steps

Goto The Developer Tab on the Ribbon and click Visual Studio Button. Shortcut key is (Alt+F11)
Paste The Code In VBA Module 

Row Color Select Cell Using VBA


VBA CODE:

Option Explicit
Private LastAddress As String
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If LastAddress <> "" Then
Range(LastAddress).EntireRow.Interior.ColorIndex = xlNone
End If
ActiveCell.EntireRow.Interior.ColorIndex = 7
LastAddress = Target.Address
End Sub

Change Color Index Number To Change Color on Sheet

How to Highlight Column Using VBA

The Following these step can help you automatically highlight the column of the select cell. Follow the steps

Goto The Developer Tab on the Ribbon and click Visual Studio Button. Shortcut key is (Alt+F11)
Paste The Code In VBA Module 

Column Color Using VBA



VBA CODE:

Option Explicit
Private LastAddress As String
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If LastAddress <> "" Then
Range(LastAddress).EntireColumn.Interior.ColorIndex = xlNone
End If
ActiveCell.EntireColumn.Interior.ColorIndex = 7
LastAddress = Target.Address
End Sub


Change Color Index Number To Change Color on Sheet

How to Highlight Column and Row Using VBA


The Following these step can help you automatically highlight the column and row of the select cell. Follow the steps

Goto The Developer Tab on the Ribbon and click Visual Studio Button. Shortcut key is (Alt+F11)
Paste The Code In VBA Module


Column And Row Color Using VBA In Excel



VBA CODE:

Option Explicit
Private LastAddress As String
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If LastAddress <> "" Then
Range(LastAddress).EntireRow.Interior.ColorIndex = xlNone
Range(LastAddress).EntireColumn.Interior.ColorIndex = xlNone
End If
ActiveCell.EntireRow.Interior.ColorIndex = 7
ActiveCell.EntireColumn.Interior.ColorIndex = 5
LastAddress = Target.Address
End Sub


Watch Video For Easy Learning 






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

TOP 5 EXCEL FORMULAS IN EXCEL TO CHANGE YOUR LIFE


Top 5 Micorsoft Excel Functions


Excel Is Fun And Fun is Life. In this Post we have to learn top 5 most useful function in Microsoft Excel who can change your job life.

1. how to use index function with match function 

 

Index With Match Function

2. how to use subtotal function in excel 

 



Subtotal Function 

3. how to use upper lower and proper function 

 




4. how to use AverageIfs Function

 


Sumifs Countifs and Aveageifs 

5. how to use Rept Fucntion 



Rept function

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


HOW TO ENABLE DEVELOPER TAB ON THE RIBBON IN EXCEL 2016

Enable Develper Tab Cover


Developer Tab

The developer tab is the toolbar that has the button to all related VBA Editors,  Record Macro, Create Form and ActiveX control buttons, Check box, Textbox, Scroll bar etc.....

How to Enable Developer Tab on Ribbon in Excel 2013/2016

Step 1 is Click File Button Top left On the screen.

Enable Develper tab

and click second last Button click OPTION Button. When you insert Excel option Popup window click Customize Ribbon and check Developer

Excel Option in Excel 2016

last and final step to click Ok Button and you developer Tab is On Ribbon or Toolbar

Finally Show Developer Tab in Excel 2016

 Watch Video for easy learning



HOW TO DELETE EMPTY ROWS USING VBA IN MICROSOFT EXCEL

 
DELETE EMPTY ROWS WITH VBA CODE IN EXCEL

How To Delete Empty Rows With VBA 

Excel Open
Press Alt + F11 to open the visual basic editor
Goto Insert New Module
Copy the Code and Paste it in the new module.

Before

Before Using VBA Code

Delete Empty Rows Using VBA


After

After Using Code

VBA Delete Rows

Paste This Code


Sub DeleteEmptyRows()
   Dim i As Integer
   Dim LastRow As Long

   LastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row

   Application.ScreenUpdating = False

   For i = LastRow to 1 Step -1
     If Application.WorksheetFunction.CountA(Rows(i)) = 0 Then
        Rows(i).Delete
     End If
   Next i

   Application.ScreenUpdating = True
End Sub
 

Watch Video For Easy Learning

 
 

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 MAKE COLUMN CHART IN EXCEL

Column Chart in Excel

Column Chart is a most useful chart in excel. using Microsoft Excel 2007 and above you can create a Column Chart. Column chart as one of the common charts group. Compare data points in one or more data series using column chart.

Column Chart 


Chart And Graphs


Select your Data and Goto Insert Tab on Ribbon
Select Insert Column Chart In Charts Group

 

Note 

You found your label is numeric value if yes, empty Cell A1 before you create a chart.

Easy Learning watch Video


FREE DOWNLOAD INVOICE SYSTEM TEMPLATE IN EXCEL

Free download Invoice Template In Excel


HOW TO USE INVOICE SYSTEM TEMPLATE 

This template is very easy to use. we can easily generate Invoice on PDF format. this is very basic invoice system to use easy for small company,  in this template, we have four options,

1, New Invoice
2, Client
3, Product
4, Setting
 Invoice System Template
 

New Invoice

In New Invoice Sheet Autonumbers generate Invoice number, replace with logo, to replace your own logo on invoice and show current date. after you entered data click on submit invoice button to save your file on PDF format.

Invoice Template format
 

Client and Product

In Client Sheet Data Entry Required Customer Name,  Telephone number, and Address. and in product database simply input your product detail rate and other like this.

Customer Detail In Invoice Templete Excel

 Setting

Setting option is very important option in this template. if your enter your invoice save location. invoice will save other wise shown error to "Wronge File Path" and also you input your company name and address and Tax percentage in setting option.

Invoice Templete In Excel Setting Option


 When you Feel Free Download File and have enjoy it...

HOW TO USE SUBTOTAL FUNCTION IN EXCEL

Subtotal Function In Excel


SUBTOTAL FUNCTION IN EXCEL


Subtotal is a most useful function against AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT,... more option. Subtotal has a ability to use a verity of functions, In additional, the subtotal function can either exclude and include values are not visible in rows. you can also use SUBTOTAL to find sum of filtered values.


Subtotal Function_num are


Function
Include hidden
Ignore hidden
AVERAGE
1
101
COUNT
2
102
COUNTA
3
103
MAX
4
104
MIN
5
105
PRODUCT
6
106
STDEV
7
107
STDEVP
8
108
SUM
9
109
VAR
10
110
VARP
11
111

Subtotal Function Vs Sum

you have two Cells A10 and A20 using sum formulas. If you apply one more sum  function in Cell A30 E.g =Sum(A1:A29) these two cell values include in results. but if do you use SUBTOTAL function in A10 and A20 cells and you also apply SUBTOTAL in cell A20 e.g =subtotal(109,A1:A29), these formula is not included A10 and A20 value in result.

Subtotal Ignore Hidden Rows

In case you have to filtered your data. SUBTOTAL ignores all cells that  are hidden. All cells will not be included in calculation that are filtered out.

Must Watch Video For Easy Learning and understanding

HOW TO SORT DATA USING A CUSTOM LIST IN EXCEL

How to Sort Data using A Custom List In Excel


Sort Data Using A Custom List

Why we use Sorted Data Using a Custom list. Normally We Sort data for sorting highest to lowest Z To A or Lowest to highest means A to Z. But this option are not solved our custom sorting because Name of Days and Month Names are not sequence in A to Z. Using custom list we can sort data by Months name of the years, Days name of the Week and you can create custom list any other characteristics that doesn't sort alphabetically. such as small, medium, large, extra-large, and Department Wise Employee name...!

How Using A Custom list

In this Example we could sort month in this workbook using custom list.

Unsorted Data In Excel

this is unsorted data of a month. When we use Custom list for sorting data by Month name:

Select Cell A1 "Month"
Goto Home
Click


Custom Sort button



Open Sort box. Under Sort by Combobox select the column we want to sort by. 
Under Sort by. Choose "Month"


Select Month By Sorted Data


Under order Select Custom List.


Under Order Select Custom List


Select what we want in the custom list NOTE custom list data and our worksheet data are same our data month name is Jan, Feb... and we Select January, February its wrong Selection




finally Click OK and Data are Sorted by Month name


Sorted Data using Custom List


Watch Video For Easy Learning