For marketers
who love technology
Home » , , » Top 5 efficiency secrets with Microsoft Excel

Top 5 efficiency secrets with Microsoft Excel


Microsoft Excel is a wonderful tool. But must people use Excel without ever learning how to use it well. And Excel can be a nightmare if you do not follow a few rules. These secret rules are not easy to find out and I have had to discover them by myself along the years. But you are lucky: I will share them with you!


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:
  1. if you move your cells, you mess us with your formulas
  2. your formulas are unreadable.
Now, with named cells, your formulas with look like this:
  1.  "= labor_cost * discount + overhead" rather than 
  2. "= C11 * E17 + B11". 
I do not think I need to explain further why formula 1 is better and more readable than formula 2...

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 show...
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

English: Plot of y=x 2 made in Microsoft Excel...
So many people dive directly into excel instead of taking the good old pen and paper to think about what they want to do and how it should look like in the end. Jumping directly into excel causes terrible wastes of time! Especially, when others will use your excel doc: if you do not agree on the expected result (the shape of your excel file) there is a good chance that you will find out after hours of work that you did not understand correctly the expectations, and,thus, have to restart from scratch. It would be painful and your would feel stupid, wouldn't you?

Efficiency secret 3 - divide and conquer

Some people design very complex formulas. This method increases exponentially the risk of mistakes. A much better way to proceed is to calculate intermediate results in other cells and then put the pieces together in a simplified formula. Advantages: you can more easily check the correctness of an intermediate result and of your final formula.

Efficiency secret 4 - Do not repeat yourself

I 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 lookup

It'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:

=INDEX(B2:D3;EQUIV("Elephant";A2:A3;0);EQUIV("Rose";B1:D1;0))

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

SHARE

About Gilles

0 comments :

Post a Comment