As our Transaction Pro Importer and Transaction Pro Exporter products allow you to import and export transactions to/from Microsoft Excel into/from QuickBooks I am using Excel every day. As a result I thought I would share some tips and common features that I use.
- Drop Down Lists. This can be helpful when you are having users doing data entry and you want to be sure that they use a valid customer, item etc. Read how to create a drop down list on the Microsoft website.
- Round Formulas. This can be helpful when importing journal entries as the QuickBooks API will round each line which can cause out of balance situations. Read more about this formula here.
- Left/Right Function. Can be used to easily parse data from the beginning or end of the cell. For instance if your file contains both the account number and the account name you can use a formula to extract only the account number. Read how to use this function here.
- Concatenate Function. We have a concatenate function within the Transaction Pro Importer which you can read about here. Perhaps you need to combine fields that are on two different sheets in your import file and you could use this function in Excel to combine the fields into one cell. Read how to do this here.
- Fill Data Automatically Feature. Our Transaction Pro Importer requires a RefNumber on every line and if you do not have a column that does this you can use the Fill Data Feature in Excel. Read how easy this is to do on the Microsoft website.
- IF Statement Function. An example of how I use the IF function is to add a QuickBooks sales tax item to my import file. Often times the e-commerce websites do not provide any tax information other than the amount. What I do is use an IF statement to look at either the amount, rate or state to determine what the sale tax item should be. Some help to get you started with this function can be found here.
- Pivot tables. These tables can be used in Excel to summarize data or to rotate the axis in Excel if your data is in the incorrect format. There are so many articles on this so I would just suggest doing a Google search to find what works best for you.
- Paste special transpose. This is new to Office 2013 and is helpful if you need to switch the orientation of the axes (eg make the rows into columns). Read more on the Microsoft website.
- Changing the entry key behavior. By default in Excel if you hit enter the cursor will move down a row. Often times I want to more across a row. Read how to change this in Excel here.
- Find and Replace. This can be helpful if there is some data entry errors in Excel. For instance many people spell my last name like the fruit, Magno, instead of Magno. I could do a find and replace to correct this spelling in my import file. Read more on how to use this feature here.
If you need some help with these or other Excel features or functions there is an entire Excel Course over at School of Bookkeeping that you should take a look at. What other Excel features or functions do you find useful? Would love to hear from you in the comment below.