A spreadsheet is a simple and popular general-purpose piece of home or office software. It may have started as a visible calculator and a simple way to produce ledgers and financial accounts but now it’s used for many more purposes…the only limitation is your imagination in filling up those rows, columns and grid of little squares with numbers, words, formulas & calculations, reports, graphs, tables & diagrams etc.
Cells
A cell is the smallest atomic element in the spreadsheet. It contains a single piece of information, such as a number, a date, monetary amount or percentage. It can also contain a text label, or even a whole block of text. In fact spreadsheets are so flexible that they can sometimes look more like word processor documents, databases, forms or slideshow presentations!
Each cell (or multiple cells in rows, columns and tables) can have specific formatting to change its appearance or behaviour. These might include aligning the contents, setting border lines, background colours, or making sure the contents confirm to a particular type or pattern, sometimes called a regular expression.
Examples might look something like this:
- Date format dd/mm/yyyy
Type or ‘enter’ 23/6/16 or even 23 June into a formatted cell and the spreadsheet will convert and display as an English-style date with century, 23/06/2016
- Currency format £#,##0.00;[Red]-£#,##0.00
A sterling/GBP amount rounded to 2 decimal places (pence), coloured red if negative and with comma-separated thousands, e.g. £1,342.62 or -£42.00
Don’t be worried about the details of this; spreadsheets can be as simple or sophisticated as you need although, as with most things in life, the more effort you put in the better the results can be. For example, each cell can also contain conditional rules, mathematical formula and pre-built functions, or you can create your own complex rules and conditions. These formulae can provide a very rich pseudo-language, which is much-loved by expert users…any excuse to avoid IT development costs!
Each cell has a unique address, in the example above ‘B2’, which allows it to be positioned spatially in what is effectively a large virtual piece of squared paper. Each cell can then be referenced in calculations and formulae and linked logically with other cells.
Columns
Let me work through a simple conventional* example; cells in a vertical line normally contain values of the same type and represent a set or a series of values reading from top to bottom – column A and row 1 start at the far top-left of the spreadsheet. The example below has dates (an independent variable) on the left with some related or dependent variables to the right. These could be sales figures (units sold) or website visitors for example. I have created a simple table and line graph with added total SUM(…) and AVERAGE(…) built-in functions.
*The spreadsheet will not enforce any such conventions…so it’s good practice to be consistent, explain where appropriate what is going on to readers, including your future self! Column headings (labels) and annotation can be useful reminders.
What made spreadsheets such a ground-breaking product when they first came out, and became the killer app of the time was the immediacy and interactively, compared with say a piece of program code that needed writing, compiling and testing to work. Change the value in a cell and all the related formatting and formulae will refresh and tables and charts are redrawn. Powerful stuff!
Example table and line graph
Rows
Cells in a horizontal line may hold a single record for an entity or thing of interest. An example of this might be, ‘The monthly sales and performance for widget A/department B/channel C‘. A row then contains all the data or attributes that describe this thing, not unlike an entry in a database.
Subsequent rows progressively show other instances, in this example a monthly series, or data for other widgets/departments/channels.
Tables, charts, workbooks
A grid with a number of rows and columns is called a table.
(Ed. spreadsheet, workbook and worksheet tend to be used interchangeably, the latter referring to individual tabbed pages in a spreadsheet document)
A simple x/y table may be adequate to present and understand the raw data. However, it can also be used as input into graphs and charts; lines, scatter, pie or bar charts can provide a rich and powerful visualisation of the data and embedded in dashboards, analytics and management reports.
If the data set is too big and complex to read then pivot tables could provide a powerful tool to summarise, aggregate, compare, and plot multiple variables against each other. It also lets you turn columns into rows and vice versa. Exploring pivot tables is beyond the scope of this introduction, but the key to creating a pivot table with some meaningful analysis is to start with a question, such as;
‘What is the breakdown of total gross sales by product line within a region?’ or,
‘How many online course registrations were received per month compared with the marketing spend by media/channel?’
Now with a bit or practice you can start to build the appropriate indexes and axes, and magically pull an answer out of the data. Your manager will love it!
It’s not all about the numbers
Earlier I mentioned spreadsheets used for purposes other than to hold, present and manipulate numerical data. One of the biggest commercial applications in most offices is to manage electronic lists, such as tasks, products (inventories), registers or logs etc.
Here is a simple example:
All IT and software projects need to be aware of defects, i.e. where things don’t work as expected or specified, normally as a by-product of testing. Each defect is a single atomic problem or issue that works very well as a row/entry in a table. The columns represent some attribute of the defect, such as the date it was raised, and subsequently resolved, a textual description, an owner, category and priority etc.
Once each item is logged (new row filled in at the bottom of the table) it can be updated, actively managed, reported-on and possibly audited to answer the questions like, ‘how are we doing?’ and ‘how well did we do?’, respectively.
Other features in spreadsheets provide useful management tools, including:
- Filter columns; for example only look at ‘priority 3’ defects
- Sort columns to show oldest [open] item first
- Hide rows or columns to aid readability
- Create formulas and graphs for example; analysis of mean time for resolution, breakdown of categories
- Snap-shot/dashboard of the current open issues
- Weekly summary of new issues raised, issues resolved, work in progress etc.
I hope you found this introduction useful and it has given you a taste for the potential value and power of spreadsheets and why they such a common must-have tool in your office suite.
@ITelementary
(c) 2016 IT elementary school
Leave a Reply