Reading Time: 4 minutes
Microsoft Excel can store large amounts of data and run some seriously powerful queries. It has advanced analysis tools to slice and dice your data and run calculations to return simple answers to complex questions.
You can run ‘What If’ queries, Averages, Sums and Counts, ‘Count Ifs’, Max and Mins and a few hundred others. You can join data in columns with Concatenate and run VlookUps to find and include data, and all of that before you even start designing and charting. You can explore possible outcomes and pull reports from pivot tables and attractive, high-quality charts for clear reading and easy analysis. These various Excel functions can be utilised when importing through Codis Excelerator.
Data loves Excel! It is useful when you:
- Have mainly numeric data
- Need a flat or nonrelational view of your data instead of a relational database that uses multiple tables
- Regularly run calculations and statistical comparisons on your data
- Use Pivot Tables to view hierarchical data in a flexible layout
- Create charts regularly and use the charting formats that are available
- Emphasise your data using conditional formatting icons, data bars, charts, and colours
- Perform sophisticated what-if analysis operations on your data, such as statistical, engineering, and regression analysis
- Keep track of items in a simple list
If you are serious about the design view side and want your answers displayed in compelling, colourful charts, then head over to our Power BI pages
Sage 200 Importing
Importing CSV files into Sage 200 is easy. It’s a simple tool that works with just a few clicks.
The first step is to construct your CSV file, ensure your worksheet is in the correct format, here is a template for the correct formatting (it will automatically download). Some of the columns are compulsory, so ensure those are populated; all of the columns have a character length restriction, so ensure you have the correct amount of data in each cell. There are also formatting rules, binary, text or number. Not too complicated, once your data is correct you’re ready to import.
In the Sage 200 menu, there is a ‘Utilities’ menu for each category. For example, to import a Nominal Ledger file you will need to follow the below areas in the menu:
- Nominal Ledger
- Import Ledger File
- Select the button to first ‘validate and import records’
- Click ‘OK’
- That will launch the explorer view on your PC hard drive, navigate to the saved CSV file
- Select the file and a message will tell you that the validation was successful
Repeat the process, navigating to the saved CSV file once more, this time clicking to Validate and Import records. If there are errors in the data you will get a message, and a full report, pointing you towards the problem. The issue could be a problem with the CSV format or missing fields within Sage 200.
Ok, so that was easy enough. The data is uploaded and everything looks good. Let’s have a look at how that works in Codis Excelerator.
Codis Excelerator Importing
The first thing to note, there’s a separate Codis Excelerator template for every ledger. Nominal Ledger, Sales Ledger, Stock Control, Cashbook Payments, Cashbook receipts. Even Purchase Orders and Sales Orders, which Sage 200 does not have an import tool for.
When you purchase Excelerator, only the modules you need will be activated and available to you. When you launch Excel you will see the Excelerator tab.
In this Nominal Journal example, you’ll see the columns to complete. Need more? Click the ‘Designer‘ button in the Excelerator Tab and select the additional columns you need.
You can even customise your own spreadsheet templates using the Designer tool and put them in any order you prefer, which makes it easy if you are cutting and pasting from one spreadsheet onto the Excelerator form.
So, in steps, that would be:
- Launch Excel
- Capture your information – it can be copied and pasted from a different Excel worksheet.
- Click ‘Validate’
- Click ‘Save to Sage’
- Click OK
Wait, that’s it?
Although you need to be logged into Sage to ‘Save to Sage’, you won’t be processing in Sage. In fact, you won’t see Sage at all when you use Excelerator. Any data in your spreadsheet will be transferred to Sage with 1 click. The first few times you use Excelerator, you’ll be tempted to go and check it’s all in Sage, I know I was!
If you regularly export to Excel and import it into Sage 200 as a CSV file, then Excelerator is a time-saving tool seriously worth considering.