Show Formulas

It's very rare to find an effective excel sheet without formulas. However you will only see the output of the formulas in the cell, unless you click on that specific cell. You can use the 'Show Formula'  

Here is an example of a sheet I just created: 

This is how the sheet looks when I select 'Show formula':

When you select a cell with a formula, you will notice that it highlights all the cells that the formula depends on:

This function is very useful when you are reviewing an excel worksheet. It can be used to find any errors or manipulations that may exist in the sheet, which would otherwise be very difficult to discover. Take a look at the next screenshot:


In the above, you might not discover the 'minor' errors and adjustments made to the formulas by just looking at the sheet, but it does contain them, and they are not minor. This is what the work sheet looks like when I click 'show formulas.'

You will immediately notice the various errors and manipulations that have been made in the worksheet. Some of values in the price column are not even formulas, but just numbers, there have been minor additions made to the formulas, the wrong VAT rate has been used and formulas referencing wrong cells have been used. It may even be difficult to notice that the SUM function have not be input correctly unless you select the cell and see that the first cell have been left out.

Now just imagine what would happen if you give such an invoice (forget above how an actual invoice should look like, just the calculations) and that customer discovers the errors at some point (though, I highly doubt they would really mind). In fact, in one of the future posts, I will show how to make an invoice that is at least not easily to manipulate.

Sheets with minimum data and simple formulas like the one shown in the example above can be easy to audit even without the use of show formulas, however it can be harder to audit it when larger sheets with more data and complex formulas are involved.

I guess that this post is becoming longer than I planned to because of the screen shots, so that's all for now. Be sure to drop any questions or comments below.

Random Numbers

Ms excel provides a very easy way in which you can generate random numbers. There are two formulas which can be used. 

1. RAND 

This formula generates a random number between 0 and 1. 

As you can see above, the formula is very easy to use. All you need to do is to select the formula and press enter, and it will give you a random number. 

It might not sound very useful, but if you consider that it can generate the random number to as many as 15 decimal places, it can help you in generating as many random numbers you want. To increase and decrease the decimal places, simply click on these two: 

2. RANDBETWEEN 

 This formula generates a random number between 2 specific numbers. 

=RANDBETWEEN(bottom,top) 

The above is what you get when you select the formula. 

So if you want a random number between 1 to 10, then you input the formula as: =RANDBETWEEN(1,10) 

Note that both the numbers are inclusive. 

Also you will only get whole numbers as shown in the example above. If there is a way to get it in decimals, I am yet to find out how. 

I find RANDBETWEEN much more useful than the RAND formula since if I want to select a particular random item from a numbered list than I just have to choose the bottom and top numbers and generate the random item, as opposed to using the RAND function and getting values that are way out of range. However, the RAND function becomes very useful when you want to sort a list randomly. If you don't yet know how, you can always experiment, but I will post a detailed example of this in the future. 

 One very important thing to note about these two formulas is that the values will keep on changing every time you make a change in the workbook or even if you just close it and open it again. So how do you make sure you that after you get your random value, you keep that value and have proof that you used random numbers to generate it and not just kept a number that came to your mind? Since I want to keep my posts small and simple, I will show you an example of how to do this after a couple of other posts which shows how to use some other excel features which you will need to know to accomplish the above. 

Posts you can expect in future (you are only able to see this because I have not posted them yet):

•  Using the RAND function to randomly sort lists. 
• Documenting that you have actually used the random numbers formula to pick out the random item.

 If you have any question or comment, just drop it below and I will get back to you as soon as I can.

Intoducing the Tutorials Collections

This is the second collection I am making. In the first post of Scraps Collector I had mentioned that I will post tutorials in that blog, but I thought that it would be a good idea to separate it since I am planning to post a lot of tutorials and the tutorials will clutter up the scraps collection.

There is one thing I would like to declare before I start posting the tutorials. I am no expert at the tutorials that I will be posting. I am still learning most of this things (including blogging), but that may be a good thing, since that would mean that there will be an opportunity of more discussion as you suggest a better way of doing something.

Most of the posts will be related to excel as I use it for my work purposes and therefore have learnt a lot of features, tips and tricks in excel. However, I am also learning coding, web and development and so you can expect tutorials on those too. You will also miscellaneous tutorials over here. I will also post any projects that I may be working on over here. If you have any questions about excel or just trying a better way of doing something, we can share our ideas, and hopefully become better at this. There is a project (if it can be called that) in excel I have made using very basic excel formulas which I will post soon.

Hope you enjoy learning with me!