EXCEL – How to Add a file or Invoice in Summary List

In Excel all official work is possible that you can think. It’s all about your great effort and your hard work with cleverness. Some time excel saves our life. So lets go with this thing.

We all normally use excel for reporting and maintain a summary of our work. 
Sometime we need to open some files to show the detail of our summary and need to attached files in our worksheet. It will be an Invoice, Photo or other file format.
In Excel you can attach these file easily with your summary sheet or in a cell.
It will become very useful for your data presentation. 
How it will possible
It is very process. Lets follow these steps : 
1. Open your summary sheet in Excel
2. Go to insert tab
3. You see text between links and symbol (Excel 2016)
4. Click on small arrow under Text
5. There you will find Object
6. Click on it
7. An Object window will open
8. Click on Create from file
9. Click on Browse   
10. Choose the file that you want to insert
11. Now you can select your showing format by clicking on Link to file or Display as icon
12. You can set file area by drag its corners.
Also you can see the above video.
If you want Invoice generator that shows in video can download it from link that provided in video description.
or You can comment your  email id in below comment section to get file.
Thanking You!

Tally Shortcuts – You don’t Know || Part-2

THESE SHORTCUT KEYS MAKE YOU BEST IN TALLY ACCOUNTING

Why we use Tally instead of other software, because in Tally we can do fastest entries other than another software. After know these shortcut keys, you can make an impression in your office.
All the shortcuts are very useful and make you a successful Tally operator.

GO THROUGH THE BELOW SHEETS. IT IS JUST EXCEL SHEET. ALSO YOU CAN  DOWNLOAD IT.
IF YOU WANT TO KNOW ABOUT SOME MORE SPECIAL KEYS, WATCH THE ABOVE VIDEO

Tally ERP 9 – HSN And GST Details in Voucher Entry ☑️

SEE THE HSN CODE AND GST % IN VOUCHER ENTRY

The Goods and Services Tax (GST) now running in India and  levied at multiple rates ranging from 0 per cent to 28 per cent. GST Council finalised a four-tier GST tax structure of 5%, 12%, 18% and 28%, with lower rates for essential items and the highest for luxury and de-merits goods that would also attract an additional cess.
In India mostly businesses use Tally software for accounting. And in Tally some features are hidden, they not show at voucher entry time. When we take sales voucher entry, required Item name, there GST rate and GST amount etc. But for an item, there rates are hidden and not shows at entry time. 
By this GST detail TDL you can see all GST rates and HSN codes of Items and this will be very helpful for you.

You can download GST details at voucher entry TDL with below download link :

After download fallow these steps to install in Tally : 
1. Copy the TDL file path (right click on TDL file – Property – Security – Select path and copy)
2. Open Tally – Press F12
3. Goto Product and Features
4. Press F4
5. Paste the path in blank area (Alt+Ctrl+V to paste) and press enter.
Now your file ready to use. When you go to your sales or purchase vouchers and enter stock item name, there shows Item HSN and it’s GST percentage. 
For more detail please watch the above video

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.