Automated Journal Entries
Create a New Excel File
Start MS Excel. You will have a blank worksheet named "Book1". Click on the box above row 1 and to the left of column A. The entire workbook will change color to show it is active.
Set Cells to Text Format
Select "Format" from the menu above, and then select "Cells" from within the menu item selected.
You will have a box with several tabs. Select the "Number" tab. If not already at front the tab will be brought to the front after selecting. There will be a "Category" scroll box. Select "Text" and click "OK".
Enter Complete Cents
All of the cells are now set to "Text" format characteristics. Select the cell in row 1 column A to start entering your data. In this example the data follows the typical layout of DR Account, Reference 1, Amount, and CR Account. The important part of entering the data is that when you enter the dollar amounts you must enter it without "$" and without ",". You must enter the decimal "." and complete cents. So one thousand one hundred one dollars and one cents is entered as "1101.01". If the cents ends with a zero you must enter the zero.
- RIGHT - "500.50", "612.00"
- WRONG - "500.5", "612.", "612"
Select Autofit
Once you have entered all of your data you must save the spreadsheet as an ".XLS". Select "File" from the menu. Select "Save As" from within the menu item selected.
Save as XLS
Set the name to what has been assigned for your JEs. Be sure the "Save as type:" value is "Microsoft Excel Workbook (*.xls)". Click "Save".
When asked about saving the active worksheet versus multiple worksheets, click "OK".
Save as CSV
Now the file needs to be converted to a "CSV" so it may be transferred to the mainframe for processing. With the same page still in Excel, select "File" from the menu. Select "Save As" from within the menu item selected. Leave the name to what it is but click just to the left of ".xls" and delete the ".xls". Set the "Save as type:" value to "CSV (Comma delimited) (*.csv)". click "Save".
When asked to keep this format leaving out incompatible features, click "Yes".
You now have an "XLS" and a "CSV". Keep the "XLS" if you like for a backup. The "CSV" will need to be moved to your designated "Outgoing" folder for transfer to the mainframe. When you attempt to close your Excel workbook you will be asked if you want to save changes. Click "No".
Last Updated: February 17, 2012
