EXCEL TRICK – FORMAT YOUR DATA AS YOU WANT ☑️

VERY USEFUL TRICK WITH MUST LEARN FORMULAS

Excel sheet, we use every day at our workplace. Without Excel can not finish our work properly. And every Excel user need some tips and trick to do work perfectly. After learning some Excel tricks and formulas we can save our lots of time. I have listed here some top formulas and excel tricks for you.
In this segment we learn about Pivot Table, Text to Column, Vlookup formula, Transpose, Special filter, Go to Special and much more excel tricks.

PIVOT TABLE:

Pivot table is one of most powerful feature of Excel. By this you can filter and generate a significance data table with a summary. You will find it in Insert Tab of Excel. Just select your data and click on Insert and then Pivot.
You can modified your summarized report as you want.

TEXT TO COLUMN : 

Text to column is also a very useful excel feature. By this you can define or separate texts to another cells separately. You will find it in Data Tab of Excel. You can separate your text with it’s delimited or Fixed Width feature. Just select your excel column and goto data and text to column. 

VLOOKUP FORMULA:

Vlookup formula is most powerful formula of excel. By this you can search and find any number or text within a criteria. It is must learn formula of Excel. If you learn it correctly, you become master of Excel. 

GO TO SPECIAL :

Go to Special feature is also very useful excel feature. if you want to fill blank cells or do any other special things, it will help you lots. 
After that some more useful features of Excel is Transpose, Special filter etc.
You can see all these Excel tips and trick in above video.
For any other information please comment below.

REPLACE LEDGER AND STOCK ITEM IN ONE CLICK

CORRECT WRONG LEDGERS AND STOCK ITEMS WITH ONE CLICK

We all work hard and sometime we do wrong things and want to correct them. But in life we can not correct some things after do wrong. Some time same things we do wrong in our work and for correct it we take lots of time. And we know that the time is very precious. If we do some wrong entries in our Tally and want to correct it, it takes lots of time, and sometime it’s not possible to correct it manually. 
If you entered wrong ledger name or item name in voucher entry, don’t worry. I have something special for you. It can correct all your wrong ledgers and stock that you entered incorrectly.
This is the TDL file that correct your wrong ledgers and stocks of vouchers. 

If you want to know how it will work please watch the above video.

To know how to install the TDL file please follow the below instructions: 
1. Download the TDL file with below given download link : 
2. Write click on TDL file and go to property and then security section.
3. Copy the path of file that provided in Security section.
4. Open Tally and Press F12 and then F4
5. Paste the path (Alt+Ctrl+V) in blank area and press enter to save.
Now your file is ready to use.
Now you can go to your day book and use the instruction provided in above video.
Download with below download link: 

Make Your Own Entry Form without VBA Coding ☑️

VERY USEFUL ENTRY FORM

By this Excel trick you can make your own entry form without any VBA coding. It will be very useful to make data sheets within defined criteria. 
The data entry form is one of Excel’s built-in data tools. Just click on the Form icon, and Excel will do the rest.

The first step to using the data entry form is to add the Form icon to the Quick Access Toolbar so that we can use it.

Add the Form to the Quick Access Toolbar

  1. Click the left mouse button at the end of the Quick Access Toolbar to open the drop down menu.
  2. Choose More Commands from the list to open the Customize the Quick Access Toolbar 
  3. Click on down arrow at the end of the Choose commands from line to open the drop down menu.
  4. Choose All Commands from the list to see all the commands available in Excel in the left-hand pane.
  5. Scroll through this alphabetical list to find the Form command.
  6. Click on Add button between the command panes to add the Form command to the Quick Access Toolbar.
  7. Click OK.
Now you can use your form.
For use it just select the heading of your Excel data and click on Form.
For more information please watch the above video

GST Invoice – Auto Summary and PDF Generator ☑️

INVOICE IN EXCEL WITH AUTO SUMMARY AND PDF GENERATOR 

This is the VBA enabled Invoice that generate your invoice summary and also generate PDF of Invoice.
We all know that reporting is most important of an firm or company or any business. If you make invoices but not make that summary, you can not recognise your business fluctuations. So it is important to make a summary of your work. 
In this auto Invoice sheet you can select your products that you want to sale with buyer and consignee details and with auto sum calculations of Taxes. Also it has some useful formulas like, Data Validation, Vlookup, iferror, sumif  etc along with VBA codings. You can learn lots of with this sheet. 
It also generate a summary of invoices that you create with one click. You can also generate a PDF copy for mail or future use. 
It has all the feature that you want in an invoice. 

THIS IS THE SIMPLE VBA CODING THAT YOU NEED TO PASTE IN YOUR EXCEL VBA MODULE


Sub SUMMARY()
‘
‘ SUMMARY Macro
‘

‘
    Range(“G5”).Select
    Selection.Copy
    Sheets(“SUMMARY”).Select
    Range(“A1”).Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Range(“A1”).Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 1).Range(“A1”).Select
    Sheets(“INVOICE”).Select
    Range(“J5”).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets(“SUMMARY”).Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 1).Range(“A1”).Select
    Sheets(“INVOICE”).Select
    Range(“A6”).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets(“SUMMARY”).Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 1).Range(“A1”).Select
    Sheets(“INVOICE”).Select
    Range(“D8”).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets(“SUMMARY”).Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 1).Range(“A1”).Select
    Sheets(“INVOICE”).Select
    Range(“D9”).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets(“SUMMARY”).Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 1).Range(“A1”).Select
    Sheets(“INVOICE”).Select
    Range(“A12”).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets(“SUMMARY”).Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 1).Range(“A1”).Select
    Sheets(“INVOICE”).Select
    Range(“D14”).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets(“SUMMARY”).Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 1).Range(“A1”).Select
    Sheets(“INVOICE”).Select
    Range(“D15”).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets(“SUMMARY”).Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 1).Range(“A1”).Select
    Sheets(“INVOICE”).Select
    ActiveWindow.SmallScroll Down:=21
    Range(“N39”).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets(“SUMMARY”).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, 1).Range(“A1”).Select
    Sheets(“INVOICE”).Select
    ActiveWindow.SmallScroll Down:=0
    ActiveCell.Offset(0, -4).Range(“A1”).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets(“SUMMARY”).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, 1).Range(“A1”).Select
    Sheets(“INVOICE”).Select
    Range(“L39”).Select
    ActiveWindow.SmallScroll Down:=-6
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.SmallScroll Down:=6
    Sheets(“SUMMARY”).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, 1).Range(“A1”).Select
    Sheets(“INVOICE”).Select
    ActiveWindow.SmallScroll Down:=0
    Range(“O39”).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets(“SUMMARY”).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, 1).Range(“A1”).Select
    Sheets(“INVOICE”).Select
    ActiveWindow.SmallScroll Down:=-24
    Range(“G5”).Select
End Sub

FOR PDF GENERATOR USE THIS:

Sub SAVEPDF()
‘
‘ SAVEPDF Macro
‘

‘
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        “C:UsersHPDesktopTAXINVOICE NEW.pdf”, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        True
    Range(“G5”).Select
End Sub
ALSO YOU CAN CREATE MACRO BUTTONS FOR YOUR EXCEL SHEET.
FOR MORE DETAIL PLEASE WATCH THE ABOVE VIDEO
YOU CAN DOWNLOAD IT WITH BELOW DOWNLOAD BUTTON :

Tally ERP 9 – Add or Remove Multi Ledger Opening☑️

HOW TO CHANGE MULTIPLE OPENING BALANCE OF LEDGER

After finalising  a financial year we need to change the openings according to balance sheet.
And sometime we need to remove all openings, and enter all new openings in our ledgers.
If you want to remove or change multi ledger opening balances, you are in right place.

PROCESSOR TO CHANGE OR REMOVE MULTI OPENINGS :

1. Open Tally
2. Go to Account info – Ledgers – Alter (Under Multiple Ledger) or Type A+L+T 
3. Now you can select all item or particular a group to change opening
4. Now you see the all ledger openings.
5. You can change it with one by one.
6. To remove all openings Press Alt+Z
7. To save the sheet Press CTRL+A
You will find your all opening balances are change.
I hope this will help you. If want any more solution please comment below

For more information watch above video

GSTIN TRACKER – Make your own ☑️

NOW GST IN INDIA, TRACK CORRECT GSTIN

In VAT time we were search for TIN number, and there  also available lots of websites to search correct TIN number with address. But now we use GST. And we track GSTIN in gst.gov.in.
 In GST website you must have PAN or GST number to track it correctly. 
But now we can track GST details with our Excel sheet. Just make your parties GST List, It will show you the State and GST type instantly. To make this GST tracker sheet you need to put some simple formula with your GST list.

FORMULAS:

We can use below formulas : 
1. As we know the GST total characters are 15, so we can calculate number of characters with this formula : =IF(B3=””,””,LEN(B3))
There B3 is Cell address of your GST number.  
2. We can create GST State and there codes in second sheet  and put this formula to calculate GST state : =IF(B3=””,””,IFERROR(VLOOKUP(MID(B3,1,2),STATE!$A$2:$B$38,2,FALSE),FALSE))
There B3 is GST Cell address. It search in State sheet with range from A2 to B38.
3. Also we can create a list of GST Type in State sheet  Like :
      
A — Association of Persons (AOP)
B — Body of Individuals (BOI)
C — Company
F — Firm
G — Government
H — HUF(Hindu Undivided Family)
J — Artificial Juridical Person
L — Local Authority
P — Person
T — AOP (Trust)

And we use =IF(B3=””,””,IFERROR(VLOOKUP(MID(B3,6,1),STATE!$C$1:$D$38,2,FALSE),FALSE)) formula to track GST type.

You can see above video for more details.

Also can download GST tracker with below download Link :

Tally ERP 9 – How to See User Wise Entry

IF YOU HAVE TWO OR MORE WORKER, THIS IS FOR YOU

Tally have many interesting features. In this segment today we see user wise entries. With this feature we can see, which user work more or take more entries. It is very interesting and must use feature of Tally.

How to see Tally User Wise Voucher Entry : 

If you have two or more workers and created there separate User Id, you can use this feature.
How to use this feature, please follow these  instructions : 
1. First we need to crate users : 
    To create Users goto Tally – Press Alt+F3 – Security Control – Users and password
    Create here user type  and there User Id and Password.
    You can also decide users security type and password policy.
2.  To display report : 
     To see user wise entry report goto Tally – Display – Statements of Accounts – Tally Audit – Users
     You will find user wise report. When you enter in users, can see there vouchers entered by them.
You need to enable Tally Audit feature to showing this report.
For More detail watch the above video.
      
 

Tally ERP 9 – How to See Two or More Companies Balances in One Place☑️

IF YOU MAINTAIN MULTIPLE BRANCHES OR SISTER CONCERN, THIS IS FOR YOU

Sometime when our business increased and we create more branches or sister concerns and maintain different companies in Tally, we need to merge in one company to create balances sheet and profit loss account.
To merge two or more companies in one we can do two things : 
1 . Import other companies data into One.
2 . Display all company data in one place through balance sheet.
In first method we need  time and accuracy to set two or more companies in one, because of its opening, voucher quantity, duplicate ledgers , groups etc.
For this we can use third party software Like Excel2Tally  Data converter etc.
In second method we need to open all companies in one one tally and can follow the above video instructions. 
But in this method you can see balances only through balance sheet.
If you really want to merge two or more companies data into one the method will work perfectly.
(Take Tally data backup always before do any new things.)

Tally ERP 9 – Item Purchase History – Sale Time☑️

ITEM PURCHASE HISTORY AT VOUCHER ENTRY TIME

Tally is most amazing and easy to use Accounting Software. In Asia most countries use Tally for there business. And in India after GST coming it’s users increased more. 
Tally is very simple software and lass with great features. We can make it more simple and more useful by adding some TDL (Tally Definition Language) files. And today I have something special for your Tally.
By this TDL we can see Item purchase history at sale time or at entry time of vouchers.
It will help us to see it’s purchase cost along with discount and quantity etc.
For how it will work please watch the above video.

How to set TDL file in Tally : 

1. Download the TDL file with provided link
2. Select TDL file and right click on it – goto security – Copy the path
3. Open Tally – Press F12 – Press F4
4. Paste the path in blank cell (to paste use Alt+Ctrl+V) and press enter to save.
Now your TDL file is ready to use.
How to use this TDL file in voucher, Please watch the above video.
To download Item purchase history TDL go through below download link :

ADVANCE CHEQUE REGISTER☑️

IT IS VERY HELPFUL TO TRACK YOUR CHEQUES

In every business most of our transactions we do with cheques. And need to maintain a cheque register manually or computerised. If you use computer to maintain to follow cheques clearing etc, this for you. 
By this you can also maintain your payable  with payment priority. It will show you high priority, low priority for your vendor payments.
You can also use it to track your bank deposits, clearing etc.
It has all columns that you want to use. If you need more columns with this sheet you can add your own. By this cheque register you can do your work smoothly.
It is very simple to use. Just write your cheque details and click on small boxes, to track it’s clearing.
It also show you priority of cheques. It will be very helpful to track payments and receipts.
You can download it with below download button : 
ADVANCE CHEQUE REGISTER WILL BE VERY HELPFUL FOR YOUR BANKING