Efficiency secret 1 - name your cells!Very few people name their variables. They rather use the cell reference, such as "AZ12". It's a terrible idea for two reasons:
- if you move your cells, you mess us with your formulas
- your formulas are unreadable.
- "= labor_cost * discount + overhead" rather than
- "= C11 * E17 + B11".
The figure below shows you where you can name a cell or a set of cells. In the picture, if you want to name the set of selected cells "y", then just type "y" in the box designated by "column name" in the figure. To manage the names, click on "Formulas" > "Name manager" and the box depicted at the bottom of the figure will open.
|English: A spreadsheet of my construction showing how named variables are employed (Photo credit: Wikipedia)|
Efficiency secret 2 - design the template of your excel sheets on paper first
Efficiency secret 3 - divide and conquer
Efficiency secret 4 - Do not repeat yourselfI often see documents where all cells in a column use the same "vlookup" formula to get a parameter and do something with it. Why the hell do you want to repeat a long vlookup formula in ten cells? It's much simpler to write the formula once only in another cell, name this cell (see efficiency secret 1) and use the cell name in your column to replace the vlookup.
With this method, your formulas will look simpler and will be much easier to understand.
Efficiency secret 5 - Regroup data in a single database, then lookupIt's a good practice to structure and regroup all your data in a database sheet and then do your calculations on other sheets. This method forces you to look for values in the database, thanks to lookup formula (e.g., vloopup). I'll give you the key to heaven: a formula to lookup a value in a two dimensional table, given the line header and the collumn header.
Let's take an example. Imagine you have a database like this:
A B C D
1 Grey White Rose
2 Elephants common rare none
3 Mice common common none
Then, your formula for two-dimensional search in an excel sheet would look like this:
Pay attention to the range of cells in the formula:
- B2:D3 is the block containing the values (common, rare, none),
- A2:A3 is the collumn containing the animal names
- B1:D1 is the line containing the color names