Hello and welcome back to the Excel Tip of the Week! This week we have a General User post in which we consider the impact of one of the ICAEW's "20 Principles for Good Spreadsheet Practise". We’re going to take some time to talk in more depth about Principle #5: “Before starting, satisfy yourself that a spreadsheet is the appropriate tool for the job.” It’s an important consideration in all our working lives: using the right tools for each job. Excel is a powerful program, with a lot of features and useful characteristics. But it isn’t the best or only solution to every problem. Here are some things to consider:
What’s the problem I am trying to solve?
Why isn’t Excel the best choice?
What might be a better solution?
The workbook is mostly text, either just text or including e.g. images and tables.
Excel is not a precise replicator of text and doesn’t have many of the most useful text-related options.
The document will need to be printed legibly and presentably
Fine-tuning printing options is difficult in Excel.
The data will need to be very large but little calculation is needed
Excel can slow down considerably with large data sets.
A database program like Access could be used; however most of the time Excel will be the best choice still.
A form is wanted that multiple people can fill out and then have results collated.
Whilst sending Excel templates to multiple respondents isn’t hard, collating results can be challenging.
Could use InfoPath in conjunction with a SharePoint site, or use a free web service like SurveyMonkey.
The file needs to be shared remotely between several users.
Excel does support this but needs an intermediary sharing platform e.g. a SharePoint site.
Try to use Excel if possible.
If security is appropriately considered, a Google Drive spreadsheet could be appropriate.
The task is a common business task, e.g. bookkeeping or calendar management
Excel can replicate the functionality needed, but will need to be made from the ground up
A dedicated software package, e.g. NAV or CaseWare for accounting
The end product needs to be shown in a presentation
Excel spreadsheets can be made visually appealing but it takes a lot of work
Extracting data from another program or system
Data doesn’t always import cleanly
Consider if the data needs to be exported or if the work could be done in the original program. If import is necessary, pasting the exported data into Notepad first can help with formatting problems
The file will be sent to someone with an old version of Excel
.xlsx files (used by Excel 2007 onwards) aren’t perfectly accessible by users of Excel 2003 or prior
Use Excel, but save the file as a .xls file to make sure that it will come out as you expect.
Generally, the important idea is to understand the strengths of Excel and the challenges of the work you’re doing, and understand how well those line up. Excel is strong in manipulating and summarising data, performing calculations, and creating interlinked sets of data. If that’s the kind of task you have, then Excel is going to be a strong tool. Previous post - SUMIF, COUNTIF, SUMIFS, COUNTIFS TOTW index Next post - FLOOR, CEILING, MROUND This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business. There is also an online forum where you can ask questions and share ideas with other community members.
Another excellent piece David, thank you