Excel worksheets are great tools for recording data.
By Helen Bradley
Excel worksheets are great tools for recording data. While some people use worksheets to record thousands of entries at a time, all some of us need is help recording and organizing the sort of day to day stuff that crosses our desks. Even if all you do is to work with small quantities of data, any help you can get is work you don’t have to do and it’s generally less prone to error.
One typical use for a worksheet is to record data under a series of headings. For example, if you record your general expenditure by type or record petty cash expenditure in a worksheet, Excel can do the work of posting it to the appropriate column for you.
This not only reduces the chance of a posting error but it also makes the task simpler if you have so many headings that they scroll off the right of the screen. The basis of this week’s sample worksheet is an IF function – if you’ve never written one before, this is a great way to get going with them. This worksheet can be created in any version of Excel.
SET UP THE WORKSHEET
To create a self posting worksheet which can be adapted for use in a number of situations, type these entries into these cells in a new worksheet, this will give you the basic structure you can build on yourself later on:
F2 1
G2 2
H2 3
I2 4
A3 Date
B3 Paid to
C3 Check/Voucher
D3 Amount
E3 Code
F3 Stationery
G3 Postage
H3 Parking
I3 Sundry
Create four sample entries so you have some data to work with:
Entry 1:
Date: 11/8/2004
Paid to: USPS
Voucher: 2045
Amount: 9.50
Code: 2
Entry 2:
Date: 11/8/2004
Paid to: Chevron – Gas
Voucher: 2046
Amount: 40.00
Code: 4
Entry 3:
Date: 11/9/2004
Paid to: Downtown parking
Voucher: 2047
Amount: 95.00
Code: 3
Entry 4:
Date: 11/9/2004
Paid to: Cinema tickets – staff party
Voucher: 2049
Amount: 150.00
Code: 4
While you could post each individual entry to the appropriate column, Excel can do it for you if you write an IF function to do the work. Type this formula into cell F4 and copy and paste it into cells F4:I7:
=IF($E4=F$2,$D4,0)
Ignore all the zeros for now. You’ll see that all the figures from column D have been posted to the correct columns in the worksheet. One single IF function did all the work for you.
UNDERSTANDING THE IF FUNCTION
The IF function takes a single test and performs one of two alternative tasks depending on whether the condition is true or false. This means that the test you use must be able to be answered as true or false. The syntax of the If function is therefore:
=IF(logical_test,value_if_true,value_if_false)
If you look up the IF function in Help, you will see that the Logical_test and the value_if_true are compulsory arguments – you must provide them. The Value_if_false argument is optional. If you omit it, the cell will show the word FALSE if the condition is not met.
Our formula uses the IF function to test and see if the number in column F matches the code for a particular column and which we’ve stored in row 2 of the worksheet. If it is a match then the figure from column D is copied to the current cell, if not, then the value 0 is placed in the cell. If you omit the second argument, the word FALSE appears instead of the 0 – neither are satisfactory but the zero is better as you’ll see soon.
NEATENING THINGS UP
When a formula like this one returns unwanted zeros, you can stop these from displaying if you do one of two things. You can turn zero display off by choosing Tools, Options, View tab and disable the Zero Values checkbox. When you do, all zero values disappear – if you omit the final argument of the IF function, the resulting FALSE entries won’t be hidden which is a good reason for including the argument even though it is optional!
Another formatting option is to format the zeros so they are hidden. To do this, select the area with the posted data in it (F4:17) and choose Format, Cells, Number tab and from the Category list scroll to the bottom and choose Custom. In the Type box type this entry, and click OK:
#,##0.00_);(#,##0.00);
Custom number formats let you to control how numbers are displayed. Here the code #,##0.00_) controls the display of positive numbers which will be formatted to show two numbers after the decimal point and with at least one number in front of it. The underscore bracket ‘_)’ combination adds a space after the number the size of a right closed bracket. This ensures that positive values line up neatly with negative numbers if they appear in the same column.
The code (#,##0.00) controls the display of negative numbers which are displayed in the same way as positive numbers but inside brackets.
When you create a custom number format you can have four formats and each is separated from the next by a semicolon. You’ll notice we’ve provided three formats – the final one of the three we’ve used controls how zero numbers are displayed. In this example format, the numbers aren’t displayed because there is no format provided for them. If you want to provide a custom format for text, you can use a fourth optional format option in your custom format code. Note that these custom formats are different from the Conditional formats which can be applied using the Format, Conditional formatting dialog – they’re not as powerful and the formatting alternatives are quite limited but they suffice for our purposes here.
You can adapt this example worksheet to many purposes. Add more column headings and assign a number to each heading in the row above the heading. Then copy the formulas across and down the worksheet as required.
