With out any doubt the most common mistake I have observed Excel users make when developing their shiny new spreadsheets is that they do not set up their data the way Excel basically expects it to be set up.
Excel expects data to be structured in the most effective way so it can carry out calculations and any analysis as easily and efficiently as feasible. The format that excel expects works most efficiently with is all related information to be in one continuous table.
In earlier versions of Excel, the computer software could accommodate 256 Columns in one single sheet and 65,536. In Excel 2007 this has been massively increased to 1 million rows and 16,384 columns. This in itself gives an indication not only the prospective power of this piece of software but also that information really should be set up as a table with column headings going across the initial row of the table and the related information cascading directly and continuously underneath the column headings.
It is this simple principle ( if followed) that gives Excel its power, and there are some fundamental guidelines to follow.
1. Set out your data in 1 continuous table - in this way you can take benefit of the plenty of potent tools that excel has to supply.
2. Attempt not to unnecessarily spread date over several tables, sheets or workbooks and maintain your table as fundamental as possible.
3. Do not be tempted to insert bank cells to represent repeating data ( I have seen it completed!). This can have devastating effects on information analysis and calculation when you push Excel to analyse your information.
4. Continually attempt to sort your information in logical order when you can. Excel is actually potent, but it is no harm and very good practice to give Excel as much of a head commence when it is using its look up and reference formulas. This sort of uncomplicated behavior can increase Excels speed in analysis and calculations.
In straightforward terms, if you don't follow the first three items in the above you you seriously limit the capability of Excel to analyse your data, by ruling out the efficient use of Pivot Tables, Sub Totals and worksheet formulas.
If you can stay away from unnecessary complications in your Excel tables, then it is full prospective can be utilised, and the calculations will move faster and extra efficiently lowering frustration and get you faster outcomes.
To recap on the initial Post in this series is How To Create A Brilliant Spreadsheet - Preparing Vs Implementing the 80/20 Split
The second article in this series is- How To Produce A Brilliant Spreadsheet- Focus On Formatting.
The last in this series will be How To Generate A Brilliant Spreadsheet- Formula Ideas Post 4.
Excel expects data to be structured in the most effective way so it can carry out calculations and any analysis as easily and efficiently as feasible. The format that excel expects works most efficiently with is all related information to be in one continuous table.
In earlier versions of Excel, the computer software could accommodate 256 Columns in one single sheet and 65,536. In Excel 2007 this has been massively increased to 1 million rows and 16,384 columns. This in itself gives an indication not only the prospective power of this piece of software but also that information really should be set up as a table with column headings going across the initial row of the table and the related information cascading directly and continuously underneath the column headings.
It is this simple principle ( if followed) that gives Excel its power, and there are some fundamental guidelines to follow.
1. Set out your data in 1 continuous table - in this way you can take benefit of the plenty of potent tools that excel has to supply.
2. Attempt not to unnecessarily spread date over several tables, sheets or workbooks and maintain your table as fundamental as possible.
3. Do not be tempted to insert bank cells to represent repeating data ( I have seen it completed!). This can have devastating effects on information analysis and calculation when you push Excel to analyse your information.
4. Continually attempt to sort your information in logical order when you can. Excel is actually potent, but it is no harm and very good practice to give Excel as much of a head commence when it is using its look up and reference formulas. This sort of uncomplicated behavior can increase Excels speed in analysis and calculations.
In straightforward terms, if you don't follow the first three items in the above you you seriously limit the capability of Excel to analyse your data, by ruling out the efficient use of Pivot Tables, Sub Totals and worksheet formulas.
If you can stay away from unnecessary complications in your Excel tables, then it is full prospective can be utilised, and the calculations will move faster and extra efficiently lowering frustration and get you faster outcomes.
To recap on the initial Post in this series is How To Create A Brilliant Spreadsheet - Preparing Vs Implementing the 80/20 Split
The second article in this series is- How To Produce A Brilliant Spreadsheet- Focus On Formatting.
The last in this series will be How To Generate A Brilliant Spreadsheet- Formula Ideas Post 4.