chart close up data desk
Share on facebook
Share on twitter
Share on pinterest

Spreadsheets are a powerful tool for personal and business financing, but can be used for other purposes such a mail merging labels for Christmas cards or for mail drops.

Ledger

The Spreadsheet

The spreadsheet gets it’s name from a paper process of recording in ledger books. A sheet that is spread out could record in multiple columns how much has been spent or received and where the money went or came from in an easy to read format.

Each entry was recorded in chronological order from top to bottom in rows, with columns depicting each break down.

The electronic spreadsheet uses the same principle. Rows run left-right across the screen and are numbered sequentially, and columns run up-down on the screen and are lettered sequentially. The square where a row and column meet is called a cell.

chess board

Chess

In the game of Chess, the board is labelled from White (the Queen’s) perspective. If the player playing Black says: Pawn to G5, you can count to the location and see where the pawn is located.

Spreadsheets use a Black (King’s) perspective, which means that it is always top down.

Numbers

As far as Spreadsheets are concerned, everything that is a number is treated as a number. You can modify this behaviour by starting a cell entry with an apostrophe that tells the spreadsheet program that you have entered text. Dates are handled as text, although the software may handle them externally as a date.

Formulae

If you want to perform a calculation on a cell, you need to use a formula. In order to enter a formula, start with an equals sign like =. There are inbuilt functions in most spreadsheet programs that make working with spreadsheets easier.

Common Functions

Functions automate spreadsheets, they make it possible to automatically calculate the sum of a column. If figures change in the selected column range, the results automatically change too.

FunctionDescription
=sum(A1:A3)Sum (add by default) the values in cells A1, A2 and A3
=sum(A1-B1)Sum (subtract) the values in cell B1 from A1
=average(C1:C3)Calculate the average of the value in the cells C1, C2 and C3.
=D4Reference (return the value of) the contents of the cell D4 into this cell.

Useful Functions

Extract the first and last name from a full name field without modifying the field. Excel has a feature of turning text into columns, but that modifies the field by splitting it. It is also a manual process.

NeedFunctionDescription
Split the First Name from a Full Name cell G2=LEFT(G2,SEARCH(" ",G2)-1)The SEARCH function returns the number of characters from the start of the string in G2, and the LEFT function returns all the characters up to the space (because the -1 reduces the returned search result by 1)
Split the Last Name from a Full Name cell G2=RIGHT(G2,LEN(G2)-SEARCH(" ",G2,1))The SEARCH function returns the number of characters up to and including the space. This is deducted from the length of the string in G2, and the RIGHT function returns from the end of the string back to the given number, which excludes the space.
checksum

Checksum – A checksum shows up errors in columns.

NeedFunctionDescription
Check whether the contents of the cells C4 and D4 match, returning an error if they don't=IF(C4=D4,"","Error")The IF function checks the question and if it's valid, it returns the first variable, else the it returns the second variable. In this example, if C4 doesn't equal D4, then it returns Error. The cell text colour is set to red and the font set to bold.

Conclusion

The concept of the spreadsheet has improved how people have handled numbers around the world, and simplified ways people can find and correct mistakes. There are lots of areas that spreadsheets can be used. A combination of cells can be selected and used to create charts for instance.

Share this post with your friends

Share on facebook
Share on google
Share on twitter
Share on linkedin