Many people have Microsoft Office but have never used the spreadsheet application (Excel) that is part of it. Every now and again someone asks me what a spreadsheet is, so I’d like to give an overview. This is not a tutorial for financial or computer geeks: it’s just to give you an idea of whether you think spreadsheets could be worth investigating further.

A spreadsheet is a computer application that stores and calculates text and figures on documents that are like sheets of paper divided into rows and columns. These “sheets of paper” can then be saved in much the same way as Word documents.

Excel spreadsheet example with data

Excel Spreadsheet - figure 1

Each row and each column of the spreadsheet is labelled (with letters for columns and numbers for rows). Therefore, each individual box formed by the conjunction of a row and a column (called a cell) will have its own unique address – eg C2 or F19. Rows go down the page and columns go across the page.

Looking at Figure 1:-

  • The cell that is labelled C2 contains a piece of text (“Year 1”). It is actually possible to perform calculations on pieces of text but in most cases – as here – the text in the cell C2 simply labels the data that appears below it.
  • The cell C3 has a number in it (200), as do the rows below it.
  • The cell C7 contains a calculation. In this case, the calculation tells the spreadsheet to add up the contents of the cells in the rows above and to place the answer in the cell C7. The actual calculation placed in the cell in this case is “=sum(C3:C6)”.
  • The calculation in cell E3 tells the spreadsheet to subtract the contents of C3 from the contents of D3. The actual calculation is “C3-D3”.

For the sake of clarity I have colour-coded the cells in this example. Blue cells are text, orange cells are numbers, green cells are calculations. We enter text, numbers and dates just by typing in the data. To enter a calculation we begin by typing the “=” sign and then enter the formula.

Now, the beauty of spreadsheets is that having created this structure we can change any of the data and all of the formulae will be re-calculated immediately. So, for example, if we change the 200 in C3 to 500, then the totals immediately change as highlighted in yellow in figure 2 below:

Excel spreadsheet example with changed data

Excel Spreadsheet - figure 2

This means that we can create a structure that we want to use time and time again but only have to create that structure once. So, I might create the following structure (figure 3) and save it with the name of “expenditure template”:

Excel spreadsheet example with of template

Excel Spreadsheet - figure 3

This template has the text and calculations in place but no actual figures. When I want to put in figures I open this spreadsheet, enter my figures, and then save it with a different name (using the “save as” command) so that I still have my empty template available to repeat the process in the future and also have a saved copy of the spreadsheets that include my figures. I can, of course, do this as often as necessary (eg monthly).

Spreadsheets can range from the very simple to the enormously complicated. The calculations I showed above include just the instruction (known as a function) to “SUM” (ie “add”) the contents of some specified cells, and the simple arithmetic operation of substracting the contents of one cell from another. There are many in-built functions and operators that can handle, for instance, date arithmetic, statistical functions, logical comparisons etc, but you don’t need to be intimidated by all this power. It is fairly simple to grasp enough of the concepts and techniques to handle most daily requirements.

Something it’s difficult to appreciate in this static article is that it is easier to create the structure and the calculations than you might think at first. This is mainly for three reasons:

  • We can select the cells we wish to include in the calculation by “pointing” at them rather than manually typing in the cell co-ordinates.
  • Once we have created an initial calculation we can “copy” that calculation to other rows or columns where that makes sense. For instance, having created the calculation in E3 (where the calculation is “D3-C3”) we can just copy that calculation down to the next four rows. The program will automatically adjust the cell references (eg “D4-C4”, “D5-C5” etc) as it makes the copies.
  • Rows and cells can be inserted and deleted and the contents can be moved around as well as copied. The spreadsheet will automatically make changes in the calculations to adjust for these changes. This means that the design process can be very fluid: we don’t have to get it right first time.

A slightly different use of spreadsheets is to keep a kind of “database” of information (although I hesitate to use the word database as that has a more specifc meaning in computer terms). For instance, you could have a list of names, addresses, telephone numbers, email addresses etc in which each record (each person) is contained on one row and each different piece of information is in a different column (eg name, landline number, mobile number). This kind of list also has the advantage that in a modern spreadsheet application such as Excel, email addresses and website addresses are automatically recognised as links so you can click on them to create emails or go directly to websites (actually, the email part of that statement won’t work if you only have webmail on your computer).

Some of the different spreadsheets that I have cover the following uses:

  • Comparing budget (or target) figures with actual figures.
  • Comparing expenditure between different time periods.
  • Keeping simple lists of items with values and their totals.
  • Analysing the results of Google AdWords advertising.
  • Computer support logs.
  • Costings.
  • Price lists.
  • Sales figures.

Some of these spreadsheets are “one-offs” that help with specific individual projects and others are repeated on a regular basis, with the structure evolving over time.

If you have requirements that you think could be helped by using the Excel spreadsheet program just give me a call. I can offer 1:1 basic computer training so that you can then develop your own spreadsheets and/or help with developing specific spreadsheet structures.

If you have an older version of Microsoft Office (Office 2003, Office XP, Office 2000), or just an individual component of one of those packages (eg Word or Excel), then you may have difficulty reading documents created by newer versions (Office 2007 and Office 2010). To put the boot on the other foot, you may have emailed a document of the newer type as an attachment, only to have the recipient tell you that they can not read it.

That is because the structure of the documents changed with the 2007 version.

File Extensions

Depending on how your installation of Windows is set up, you may or may not see the “file extension” of each file when you view a list of files in Windows Explorer. The file extension is the part of the file name that comes after the full stop. The file extension tells Windows what type of file it is and Windows maintains a list of which program is used with each file type (in Windows jargon, each file type is “associated” with a specific program).

In Office 2003 and before, Word files had a file extension of .doc (eg “Letter to Father Christmas.doc”). Excel spreadsheets were .xls files (eg “Scalextric Costs.xls”) and PowerPoint files were .ppt files (eg “Pitch to Father Christmas For A Scalextric Set.ppt”).

From Office 2007, Word files have become .docx, Excel files are .xlsx, and PowerPoint files are .pptx. These file types are not compatible with earlier versions of the programs.

Compatibility

If you have one of the newer versions of Office then you have no problem in opening, viewing and editing files created in an earlier version. However, if you have an earlier version you can not open files created in a later version.

Solutions

Save As

If you have a later version, and are preparing a document for opening on an earlier version, then the simplest solution is to create a version of the document that is in the format of the earlier version. To do this, open or create the document and then use the “save as” command instead of the normal “save” command.

Normally, the different ways of saving a file are as follows:

  • Use the shortcut key combination of Ctrl s (hitting the “s” key while simultaneously holding the Control key down).
  • Click on the little blue icon of the floppy disc that is probably visible on the top line of the screen.
  • Click on the “Office” button and then click on the “save” command.
Office Button

Office Button

Each of these methods will save the file in the newer format. What you need to do instead is to click on the “Office” button, then take the “save as” option, and then take the “..97-2003 document” sub-option (as illustrated). The recipient of your file will then be able to use it as if it had been created in the earlier version of the program.

file save-as dialogue box

 

Document Viewers

You can actually view and print documents prepared in Microsoft Office even if you do not have Office installed. This is achieved by downloading and installing free viewers made available by Microsoft. Click on the following links for the appropriate viewer (see note at the bottom of this post) :

Word Viewer
Excel Viewer
Powerpoint Viewer

Compatibility Viewer

The above viewers are just that – you can view or print documents but you can’t edit them. A better solution for users who have earlier versions of Office is to download the free “compatibility pack” from Microsoft. This is available at http://support.microsoft.com/kb/923505.

You do have to carefully follow the instructions that are appropriate for your version of Windows.

The Compatibility Pack is for Office 2000, Office XP, and Office 2003. If you still have Office 97, or a component of it, then you need to either use the viewer or ask the author of the document to provide a compatible version of it using the “save as” option as described above.


Note:

I’m having to give up my previous practice of always quoting hyperlinks in full as some of them are just too long. If you are viewing this as a post on the blog then, depending on your internet browser, you can probably see the full version of the link if you hover your mouse over the link and then look towards the bottom of the browser window. If you are viewing the newsletter version, then hovering your mouse over the link should show you the full address of the link.

© 2011-2017 David Leonard
Computer Support in London
Privacy Policy Suffusion theme by Sayontan Sinha