How to organize data in a spreadsheet

Spreadsheet software is an interactive software for analyzing, organizing ,charting and storing data. The software works by manipulating data stored in cells that make up rows and columns. A column is a cluster of cells arranged vertically while a row is a horizontally arranged cell cluster.

Data types in a spreadsheet software

For a spreadsheet software to work effective, it has to classify inputted data into data types that it recognizes and is able to process, the spreadsheet software recognizes 4 data types namely:

Dates and Times: Date data is usually a combination of any day,month and year data, so it can be day,month ; month, year ; day, month,year combinations . Time data is a breakdown of the day data where it combines seconds,minutes,hours. The software is able to recognize days of the week, months of the year automatically. It is able convert from one type to the other e.g seconds to minutes, minutes to hours.

Texts : These are basically combination of letters,numbers and symbols represented in data. The numbers in texts can not be used in calculations as they are enclosed in literals (which means a notation that represents a fixed value in a code) e.g '2' or "2345", numbers like this are not recognized by the software as numbers.

Values: These are regular numbers ,not enclosed in literals and so can be used in calculations. They can also be derivatives of a calculation

Formulas: These are instruction to the software, usually a combination of text and number put together in a manner that the software will decipher and have the task done just as requested.

Rules on how to organize data in a Spreadsheets.

Spreadsheets work better when data is in a tabular form. A good data table should :

a) Have a header row showing column labels

b) Have rows and columns

c) Each cell in the column must have data matching column labels

d) Each cell in the row may not have the same type of data because the row cells combine to make a record.

e) There should be no gaps between data in row and column cells

f) There should be no spaces in data inputted in the cells

The diagram below shows an annotated data table where all the guidelines mentioned above are met. The first row is the header row with the column labels, column data in line with column label, no gaps in row and column data, row cells combining to make a record.

 

Data Formating

This means presenting data in a manner that the spreadsheet software can interpret. Since data is made up of text and numbers, formating applies to both.

Text formating is changing the appearance of text, by changing font, position, color, weight etc.

Number Formating is merely instructing the software on how to interpret number data. Number data can be:

a) Percentage

b) Financial

c) Scientific

d) Decimal

e)Fractions

f) Rounded

Date and Tine Formating is an extension of number formating because dates and year in computing are number data, it can be:

a) Second

b)Minute

c)Hour

d)Day in numbers not letters

e)Date

f)Month

g)Year

Number formating can be done in two ways. The first way using the graphic user interface by right-clicking on a cell and selecting the 'Format Cells' option, and selecting the format of choice from the Category. Date and Time formating can be done this way as well.

The other method is to use the formula 'TEXT' to specify what format the data should be in. The syntax for the formula is:

TEXT(Cell to be formated,Format"Format Pattern")

For example, TEXT(now(),Format('ddd'), gives what day it is in full, Monday, Tuesday whatever the day is.

Formula formating points to a different cell and formats its content while GUI (Graphic User Interface) formating can convert contents of the same cell.

The software is able to choose a format from the data inputted in a cell. e.g 12/04 typed in a cell will be displayed as 12th April. Data that has been put in the right way is easier to process into Information, for Spreadsheet newbies a good knowledge of data formating is a good place to start.