Monthly Archives: April 2013

Excel – Using Formulas for Simple Double Entry Bookkeeping

Do you require people to fill out a sheet such as expenses in a simple double entry format, but find that people add the costs to all the wrong columns? There is a simple way to control this with a combination of a formula, data validation and a bit of protection.

Let’s take a simple example;

People put in expense claims for the following categories: Travel, Hotel, Food, Misc

For each expense they need to add a short description and a value. The value then needs to be added to the correct column across the page, in effect creating a double entry table.

ScreenHunter_65 Apr. 08 11.38

The problem of course, is getting people to correctly add the value to the appropriate column and to make sure that all the columns add up correctly to match the grand total in column C.

First of all, control the category column. Add a data validation list based on the column headings and limit the list to those values.

ScreenHunter_65 Apr. 08 11.40

Add some standard currency formatting to your various monetary cells.

Now add a formula to auto-populate the correct columns once they have selected a category and entered a cost.

The formula to use here would be:  =IF(EXACT($A2,D$1)=TRUE,$C2,””)

This will test to see if the category selected in column A matches the column heading in columns D to G. If they match then it displays the value in column C, otherwise leaves the cell blank.

ScreenHunter_65 Apr. 08 11.47

Note, that until a value is entered in column C it will display £0.00, which is fine as it flags an incomplete cost and should prompt the user to enter a value.

The final step is to protect and lock columns D to G to prevent users randomly entering data in these cells. Make sure the cells in columns A to C are unlocked to allow editing once protection is applied.

ScreenHunter_65 Apr. 08 11.53

Make sure that the cells in columns D to G are both locked and hidden so that users cannot see the formulas (not essential but adds to the magic of the worksheet). Add a password to prevent users making changes and make sure you protect the workbook too to prevent anyone from removing the sheet or making changes to data validation rules etc.

Now when a user adds in their expenses, the values are automatically added to the correct column and because people cannot randomly add or edit values the totals for each column will add up to the grand total of all the expenses claimed.

From here you can extend your formulas to calculate net cost and VAT where applicable, and get those to automatically calculate based on selections I’ve now added to columns D and E.

ScreenHunter_65 Apr. 08 12.16

Advertisements

Excel – R1C1 Reference Style vs. A1

If you have ever strayed into the Options screens of Excel you may have noticed something called R1C1 reference style. You may have even tried it and when you saw that all the columns had changed from letters to numbers, panicked and switched it back.

However, the R1C1 style has some advantages over the standard A1 style, but it does take a bit of getting used to!

So is this R1C1 style new? Well in fact not at all. This cell reference style dates back to 1982 and the introduction of Multiplan as a rival to VisiCalc and Lotus 1-2-3. Multiplan was developed by Microsoft for Apple Macintosh and used this cell referencing style instead of what was to become the standard in spreadsheets – A1 style.

Over the next few years, Lotus 1-2-3 became “the” spreadsheet package. In order for Microsoft to steal Lotus users who were used to the A1 style they added the A1 style to Excel so that people who migrated across would find it familiar. Excel eventually overtook Lotus in 1993 as the industry standard for spreadsheets. The rest as they say is history.

But enough of the history lesson…how does R1C1 differ to A1?

The most obvious difference is the column references. Instead of letters you get numbers.

ScreenHunter_67 Apr. 22 13.42

The real difference comes when you want to write formulas. Let’s take a simple example adding two columns together. I am displaying the formulas so the differences are clearer.

First, the familiar A1 style:

ScreenHunter_67 Apr. 22 13.43

And now for R1C1….

ScreenHunter_58 Mar. 18 15.05

At first this looks quite horrific and ten times more complicated that the A1 style. Before I break down how it works, what do you notice between the two styles (other than the obvious one)?

Each formula is different in A1 style: A2+B2, A3+B3…etc.

Whereas using R1C1, they are all identical. So this potentially means that wherever you write a formula in that column it will be same, no need to think about which row or column you are in. This is particularly helpful when you are writing VBA code.

So how does it work?

Any numbers in square brackets refer to relative distance from the current cell. Unlike A1 which refers to columns followed by row number, R1C1 does the opposite: rows followed by columns (which does take some getting used to).

Positive numbers will refer to cells below and/or across to the right.

Negative numbers will refer to cells above and/or to the left.

For example R[2]C[3] is a cell 2 rows down and 3 columns to the right. R[-1]C[-4] is a cell 1 row up and 4 columns to the left. If no number is shown in brackets then you are referring to the same row or column i.e. R[3]C will be a cell 3 rows below the current cell in the SAME column.

R1C1 ref

So once you have the basics, they are actually not that bad. Note however, that you cannot use an A1 formula when displaying R1C1 style and vice-versa. Whichever system you use, Excel ‘translates’ the styles should you switch between them at any point.

The other difference between the two styles is absolute referencing. In the A1 style I need to add $ symbols all over the place. Granted I can use F4 to put them in place for me but it still needs to be done. In R1C1, there are no $. If I write R3C4 I am referring to $D$3. So if there are no brackets, it’s an absolute reference. This makes partial absolute references easier to enter too.

The R1C1 style definitely does have some neat advantages over A1, but we are so used to the A1 style that moving away from it is an alien concept. But if you ever have a chance, try playing with R1C1, especially if you progress to VBA, and I can guarantee that writing formulas will become a lot easier…the pain is only short-lived but it is worth the effort.

For a bit of further reading on R1C1 follow this link (http://tduhameau.wordpress.com/2012/09/27/the-beauty-of-the-r1c1-reference-style/)

Excel – Highlight Data with Data Validation and Conditional Formatting

When working with lists of information it is sometimes nice to be able to highlight rows of related information such as all rows for a specific product or salesperson. If you have the latest version of Excel then you have the benefit of SLICERS, but if not then you need a different approach to achieve this. By combining DATA VALIDATION and CONDITIONAL FORMATTING this can be done quite easily.

Let’s take this simple table showing regional sales by product line.

ScreenHunter_66 Apr. 19 09.38

I could use a filter and select the information I need but using a combination of DATA VALIDATION and CONDITIONAL FORMATTING will display it in the table itself whilst still leaving other data visible.

Create a couple of drop down lists using DATA VALIDATION using the values from your list to generate the values to select from. I have left a blank cell at the top of each list so that I can have an option to select ‘nothing’.

ScreenHunter_67 Apr. 19 09.38

Once I have those set up I can set about applying some CONDITIONAL FORMATTING based on the selections made in the two drop downs.

ScreenHunter_67 Apr. 19 09.39

I have set up three rules, one for each drop down list and one to highlight values that meet both selections. Note the use of partial absolute referencing in the formulas to make sure that when the rules are copied across the three columns the reference points remain constant i.e. always look in columns A and/or C.

Formulas are as follows:

=AND($A6=$B$1,$C6=$B$2) to highlight when both product & region criteria match

=$C6=$B$2 to highlight where the product line is matched

=$C6=$B$1 to highlight where the region is matched

ScreenHunter_65 Apr. 19 09.38

And there you have a simple way to highlight data in a list based on the user’s selections in drop down lists.