Tag Archives: choose your skill

Roadmap

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.

Using this site

This site wishes to be a place where people interested in spreadsheet solutions can easily work together and find an answer, whether they are plain beginners or experienced users.
Direct interaction and sharing knowledge is possible by using 2 main areas:
– the first blog level available at “Basic blog”, where you can find articles created by all contributors and, of course, where anybody can comment
– The Forums area, where you can choose the level of the discussions you wish for.
This is a subscription area (free !). You have to be logged to post in it.
Also, a main Wiki page needs to be built, possibly with your help, -also!

HOW TO CHOOSE A LEVEL(keeps you on same page) Beginner / Intermediate / User / Expert
Alternatively, you can just go directly to a page with some first advice for each category:
Beginners-page / Intermediate users page / Skilled users page / Experts page

A good thing for a start, would be to begin the search at the closest level to you.
I don’t know which that is, it is all up to you, but I know what took me to get from zero to what I know now (there is a lot I still don’t know!).
If growing older is of any good, then I had the advantage that I was growing together with spreadsheet solutions (there’s more than Excel !).
Of course they are growing faster : just take a look on Wikipedia !
Don’t worry, though – finally they’re all the same. I really mean it : Spreadsheets are easy !
However, for some of you, I will have to convince them of the contrary: firstly, spreadsheet is complex and extremely powerful as a tool !
I know, somewhere amongst you there are a few who still think that spreadsheet programs are just a ‘smart’ replacement of a pocket calculator.
Please, bear and browse my examples ! For these few ones : prepare to be amazed !
You can indeed choose to browse from any level (and I will even hope you’ll do that), but , please note some recommendations for selecting a right place to start (they are symbolically named as categories, just to make a difference):


1) Just arrived here – is for people with no experience(in spreadsheets, of course) at all or just starting.
This is a level recommended also to clarify things overlooked.
I found out, at a moment when I considered myself a smart-guy in Excel,that I was not familiar with some ‘basic’ operations !
Never too late to learn. Start here !
Posts for you will bear the same tag(just arrived…) or “beginner”,”no experience”,”starter”.
Don’t worry if some posts will have “power user” or “specialist” tags along with the ones above : some information could be simply for everyone. Go to start of page…


2) Novices– that means that you are (please note that between all the categories, there are some mixed zones – you could be ‘novice to user’ for example):
– familiar with the ‘look’, writing and formatting data, you know most of the usual handles, you mostly use formulas to sum, count and multiply.
You use cells references in formulas instead of writing numbers and you know to extend series of data or formulas.
– possibly(but not necessarily for a pure ‘novice’) acquainted with relative references and the absolute ones( when the $ symbol is applied to cells locations),conditional operations (SUMIF, for ex.), sorting and using subtotals.

Start here !
Posts for you will bear the same tag(novices) or
“rookie”,”average user”. Go to start of page…


3) Users– you already have ‘the taste’ of such tools.
– As you know that working on spreadsheets is saving time and reduces (or at least controls) errors, you are already thinking on how to solve increasingly complicated matters with these tools.
– you use indirect references in formulas (VLOOKUP and HLOOKUP mainly),nested formulas, you are a master of IFs and ROUNDs you use references to other sheets, workbooks and you already have gathered worksheets loaded with sensible informations, that could be named small (or even large)databases
– you use hyperlinks, instead of subtotal button you might prefer Pivot Tables, you chart at ease. You know Conditional Formatting of cells.Probably people would love your reports, already ! You have the experience of how changing one formula or inserting some data in an ‘error-safe’ sheet is messing up your nicely nested formulas.
– perhaps, you use names in formulas and you found out that they can be used independently in a Workbook.
Start here !
Posts for you will bear the same tag(users) or “good user”,”user at ease”. Go to start of page…


4) Specialist– you know how to handle spreadsheets and you still wonder how many unused capabilities you could find. Of course next step can only be EXPERT, but what would an expert look for, here ?
Please, forgive me if I enumerate some (random) knowledge and skills, but you understand that this is just to show some difference
– you are at ease with data (you use functions like MATCH, INDIRECT and OFFSET to build formulas)
– some of your solutions use integration with other sets of data or programs
– you could prepare spreadsheets solutions for others and you use Forms
and probably VBA and macros to extend your tools. You might use already Array Formulas. Collecting data from internet, emailing, reporting tools for other support databases, controlling data accuracy, all kind of complex solutions are what you are now achieving. You rely on specialists forums whenever you face a new challenging problem.