What roadmap should see a beginner in spreadsheets ?
You should see it as a map as there is more that 1 direction you could take.
I will try to explain the knowledge and the use of acquiring such skills. I will use ‘Excel’ to refer to spreadsheet calculations as it will definitely cover all of the considerations below :
- basic calculation should easily apply to ‘automating’ operations with a pocket calculator. That means that no matter what function such a pocket tool would have, Excel will have it.
The major difference is that you can define once some sequence of functions and then use it to all data you can put into it.
You can move data with a very simple gesture and you can display data and results easily in very powerful manners.
I will put, later on, some illustrative examples.
For this part, knowledge is summarized in : manipulating data, use of functions, formatting of cells.
Also you should begin to know the referencing system (how you address data at specific location – cell or range of cells). Second step will be to select specific data through functions so it would yield the needed result.
- using data in one or more spreadsheets as database from which you can easily summarize, select and display in very powerful manners .
This will suppose working with subtotals, Pivot Tables, charts. Also, referencing data from different spreadsheets or even workbooks is now necessary.
- as problems you wish to solve are getting more complicated, you will begin to learn how to find in large tables with data specific information.
This is why I repeated above ‘referencing data’. Most probably, in a first stage you will create tables of additional ‘intermediary data’ near the source tables and as you will learn better addressing data you will become more efficient at building long formulas (but smaller spreadsheets).
It is not a ‘must’ to build large formulas.
The beauty in Excel is that you can find simple and easy solutions – this should also be your guidance : there is almost no problem that couldn’t be solved.
You can handle numeric data, dates and text. Excel can be used for databases for which there is a constant need for flexibility in interpreting data. I personally use Access to structure and introduce data, but I use Excel for interpreting the data. I have a long experience in Access , but I know about nothing about its reports, as I always used Excel for reporting.
- another facility will be integrating Excel with other programs (mail, internet, etc.) or data sources ( databases).
This is mainly done directly enough by many programs, but this is definitely the point where you shall need to go a step further and learn to code (here it is a big problem in the difficulty to establish an universal language and each provider built his own solution.
This is probably the moment to ‘finally’ choose a program, or , better said a suite of programs to use)
- in an attempt to create ‘ready-made’ solutions for users and to restrict by forms the introduction of data, Excel has also some customizable forms.
As an Access user, I feel them to be more difficult to build, but you can build very powerful solutions with them . I will show an example where it would be difficult to think that the application was built in Excel.